【BigHereo 6】-----index的三生三世

谁借莪１个温暖的怀抱￠ 2022-06-10 08:45 114阅读 0赞

![Center][]

要了解所有,必须先了解下面的几个问题,才能更好的深入学习:

1,索引的那几种?

2,常用的索引 index都怎么用?

3,索引有哪些常见的坑?

4,索引都能干些什么?

5,索引到底是什么个

<table style="margin:0px; padding:0px; border-collapse:collapse; border-spacing:0px; color:rgb(51,51,51); font-family:&quot;Fira Sans&quot;,&quot;Helvetica Neue&quot;,Helvetica,Arial,sans-serif"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 8px">Index</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 28px">1&nbsp;basic(基础)</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 48px">1.1&nbsp;有四种索引</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.1.1&nbsp;1,普通</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.1.1.1&nbsp;create index &lt;索引名称&gt;on tablename(列的表名)</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.1.1.2&nbsp;alert table tablename add index&lt;索引的名字&gt;</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.1.2&nbsp;2,唯一</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.1.2.1&nbsp;确保索引唯一性</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.1.2.2&nbsp;在索引名称前加:unique</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.1.3&nbsp;3,主键</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.1.3.1&nbsp;索引的东西能唯一表示表的每一行</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.1.3.2&nbsp;是唯一索引的特殊</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.1.4&nbsp;4,聚集</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.1.4.1&nbsp;非聚集索引,nonclustered</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.1.4.2&nbsp;聚集索引</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 108px">1.1.4.2.1&nbsp;逻辑顺序和物理顺序一致</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 108px">1.1.4.2.2&nbsp;clustered</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 48px"> 1.2&nbsp;index怎么干</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.2.1&nbsp;操作</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.2.1.1&nbsp;建立索引情况</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 108px">1.2.1.1.1&nbsp;1,定义主键数据列一定要建立索引</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 108px">1.2.1.1.2&nbsp;2,定义外键的列</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 108px">1.2.1.1.3&nbsp;3,经常查询列</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 108px">1.2.1.1.4&nbsp;4,指定范围要快速,频繁查列</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 108px">1.2.1.1.5&nbsp;5,常用where字句列</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.2.1.2&nbsp;删除drop index&lt;索引名&gt;</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 48px"> 1.3&nbsp;index常见坑</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.3.1&nbsp;<a href="http://blog.csdn.net/xluren/article/details/32746183" style="margin:0px; padding:0px; background-color:transparent; color:rgb(79,187,250); text-decoration:none" rel="nofollow">http://blog.csdn.net/xluren/article/details/32746183</a></td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.3.2&nbsp;1,索引不会包含NULL值列</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.3.3&nbsp;2,少用长索引</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.3.4&nbsp;3,索引列排序</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 88px">1.3.4.1&nbsp;where多个字句,orderby 是不会用到索引的</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.3.5&nbsp;4,like语句,"%aa%"不会用索引,"aa%"可以用</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">1.3.6&nbsp;5,不要在列上进行运算</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 48px">1.4&nbsp;english questions </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 28px">2,&nbsp;index能干什么(why)</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 48px">2.1&nbsp;优点</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">2.1.1&nbsp;1,快速读取数据</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">2.1.2&nbsp;2,保证数据记录唯一性</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">2.1.3&nbsp;3,加速实现表与表参照完整性连接</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">2.1.4&nbsp;4,使用order by, group by检索,减少全表排序和分组</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 48px">2.2&nbsp;缺点</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">2.2.1&nbsp;1,索引也占空间</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 68px">2.2.2&nbsp;2,索引也要动态维护,降低维护速度</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 28px"> 3&nbsp;What</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 48px">3.1&nbsp;是一种数据结构</td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px 0px 0px 48px">3.2&nbsp;是某表一列或多列值集合和指向物理标识值的逻辑指针</td> 
 </tr> 
 </tbody> 
</table>

[Center]: /images/20220610/db7aea5f810e4f2eaad999a56c04305e.png