SQL之索引

左手的ㄟ右手 2022-05-28 10:59 154阅读 0赞

SQL索引在数据库优化中占有一个非常大的比例， 一个好的索引的设计，可以让你的效率提高几十甚至几百倍，在这里将带你一步步揭开他的神秘面纱。

1.1 什么是索引？

SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间

下面举两个简单的例子：

图书馆的例子：一个图书馆那么多书，怎么管理呢？建立一个字母开头的目录，例如：a开头的书，在第一排，b开头的在第二排，这样在找什么书就好说了，这个就是一个聚集索引，可是很多人借书找某某作者的，不知道书名怎么办？图书管理员在写一个目录，某某作者的书分别在第几排，第几排，这就是一个非聚集索引

字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处，拼音查询法就是聚集索引，部首查询就是一个非聚集索引.

看了上面的例子，下面的一句话大家就很容易理解了：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

1.2 索引的存储机制

首先，无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下,举个例子，如果我们将字典的汉字随即打乱，没有前面的按照拼音或者部首查询，那么我们想找一个字，按照顺序的方式去一页页的找，这样效率有多底，大家可以想象。

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，其实理解起来非常简单，还是举字典的例子：如果按照拼音查询，那么都是从a-z的，是具有连续性的，a后面就是b，b后面就是c， 聚集索引就是这样的，他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2,2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页，（这里只是举个例子），他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。

原理明白了，那他们是怎么存储的呢？在这里简单的说一下，聚集索引就是在数据库被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针，其实就有点和堆栈差不多的感觉了

1.3 什么情况下设置索引

<table style="margin:0px;padding:0px;border-spacing:0px;border:1px solid #C0C0C0;color:rgb(57,57,57);font-family:'PingFang SC', 'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:14px;background-color:rgb(255,255,255);"> 
 <tbody style="margin:0px;padding:0px;"> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">动作描述</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">使用聚集索引&nbsp;</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;使用非聚集索引</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;外键列</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;主键列</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;列经常被分组排序(order&nbsp;by)</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;返回某范围内的数据</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;不应</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;小数目的不同值</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;不应</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;大数目的不同值</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;不应</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;频繁更新的列</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">不应&nbsp;</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;频繁修改索引列</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;不应</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;应</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;"> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;一个或极少不同值</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;不应</td> 
 <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">&nbsp;不应</td> 
 </tr> 
 </tbody> 
</table>

建立索引的原则：

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引

9) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

10) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

1.4 如何创建索引

1.41 创建索引的语法：

CREATE \[UNIQUE\]\[CLUSTERED | NONCLUSTERED\]  INDEX  index\_name

ON \{table\_name | view\_name\} \[WITH \[index\_property \[,....n\]\]

说明：

UNIQUE: 建立唯一索引。

CLUSTERED: 建立聚集索引。

NONCLUSTERED: 建立非聚集索引。

Index\_property: 索引属性。

UNIQUE索引既可以采用聚集索引结构，也可以采用非聚集索引的结构，如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。

1.42 删除索引语法：

DROP INDEX table\_name.index\_name\[,table\_name.index\_name\]