浅谈SQL优化入门：3、利用索引

0、写在前面的话

关于索引的内容本来是想写的，大概收集了下资料，发现并没有想象中的简单，又不想总结了，纠结了一下，决定就大概写点浅显的，好吧，就是懒，先挖个浅坑，以后再挖深一点。最基本的使用很简单，直接就写在这里吧。

索引是众所周知的可以提高查询的速度，且针对的是具体的字段，使用方式为：

（MySQL中，一般建立主键，数据库会自动建立其聚集索引；而其他字段的索引，若不具体指明则建立非聚集索引）

（也可以通过关键字 CLUSTERED 或 NONCLUSTERED 指定聚集索引或非聚集索引，因为本篇不涉及具体应用案例，故不展开）

CREATE [CLUSTERED | NONCLUSTERED] INDEX <索引名> ON <表名(关系名)>; 

e.g.
CREATE INDEX yearIndex ON movie(year);

4

 

1

CREATE [CLUSTERED | NONCLUSTERED] INDEX <索引名> ON <表名(关系名)>; 

2

3

e.g.

4

CREATE INDEX yearIndex ON movie(year);

而撤销索引：

DROP INDEX <索引名> ON <表名>;

e.g.
DROP INDEX yearIndex ON movie;

x

 

1

DROP INDEX <索引名> ON <表名>;

2

3

e.g.

4

DROP INDEX yearIndex ON movie;

---

1、索引

1.1 索引的概念

我们在数据库查询过程中，即使满足给定条件的记录很少，也需要把整个表关系扫描一遍，当关系非常大时，其开销是很大的。

例如，在电影表中查询迪斯尼公司2000年制作的电影：

SELECT *
FROM movie
WHERE studioName='Disney' AND year=2000;

 

1

SELECT *

2

FROM movie

3

WHERE studioName='Disney' AND year=2000;

假设电影表中有10000条记录，其中2000年制作的电影有200个，这之中制作公司为迪斯尼的只有10个。如果不采取措施，要实现该查询，我们就要把10000个记录都挨个进行检查是否满足条件。如果有某种方法能让我们只取出年份为2000年的200条记录，然后再去找制作公司为迪斯尼的，显然效率要高很多。

确实有这样的方法，就叫做索引，它是一种为表中的给定字段提供存取路径的数据结构。

所以什么是索引？上面这个解释完全不明白，我们还是形象一点说明，网友 elysee 在它的博客《数据库优化实践【索引篇】》中讲了一个故事，非常形象，但我总理解起来有些不妥，就按照自己的意思修改了一下，故事如下：

很久以前，在一个古城的的大图书馆中珍藏有成千上万本书籍，但书架上的书没有按任何顺序摆放，因此每当有人询问某本书时，图书管理员只有挨个寻找，每一次都要花费大量的时间。更糟的是图书馆的图书越来越多，图书管理员的工作变得异常痛苦。

有一天，图书馆来了一个聪明的小伙子，他看到图书管理员的痛苦工作后，想出了一个办法，他说：“你把所有书架，按照英文字母分成26个部分，每本书根据书名的字母顺序，放到相应的书架上去。比如《阿凡提》就放在A书架中，如果有《阿凡达》，也放在A书架，且根据书名字母顺序《阿凡达》（afd）放在《阿凡提》（aft）前面”，这样一来，如果有人指定了书籍的名字，那么图书管理员很快就可以找到它的位置了。

（图书按照书名的字母顺序放在对应的字母书架上，就像创建聚集索引，即表中的所有行会在文件系统上根据书名进行物理排序，当查询表中任一行时，数据库首先使用聚集索引找到对应的数据页，就像首先找到对应的字母书架一样；然后按顺序就可以找到目标行，就像找到书架上的书一样）

于是图书管理员开始分类整理放置，为此他花了整整一周时间，最后，他发现找书的效率确实大大提高了。

（在一个表上只能创建一个聚集索引，就像书只能按一种规则摆放一样）

但问题并未完全解决，有很多人想看某个作者的所有书，图书管理员无奈又只有扫描所有图书的作者，进行挨个寻找，时间又变得太长了，因此他向那个聪明的小伙子求助。

（这就好像你给book表增加了索引bookName，但除此之外没有建立其它索引，当使用bookAuthor进行检索时，数据库引擎又只要进行全表扫描，逐个寻找）

聪明的小伙告诉图书管理员，那就创建一个目录文档好了，文档中将图书按照作者分类，将书籍重新排列，并把作者的图书和图书对应的书架位置一起记录下来就好了，不用去动图书真正的位置。这样，一旦有人指定作者，那么根据作者分类的目录文档，就可以找到该作者的书了。

于是图书管理员又花费时间赶紧整理了一个目录文档，果然有效，然后他又开始了新的思考，读者可能还会根据图书的其它属性来找书，如书的类型，是小说？诗歌？还是其他什么，于是他用这个办法为书的种类创建了目录，现在可以根据书名、作者和种类迅速找到图书了，图书管理员的工作变得轻松了。

故事到这里就结束了。其中上面提到了个概念，叫聚集索引，索引顺序和物理顺序相同，只能有一个，也就是这里的书名；相对的，还有非聚集索引，可以有多个，索引顺序和物理顺序没有关系，也就是后来的作者目录，种类目录。

1.2 索引的存储

要理解这两者的区别和含义，需要先来聊聊索引的存储。首先，一条索引记录包含：

• 键值（即你定义索引时指定的所有字段的值）
• 逻辑指针（指向数据页或者另一索引页）

另外，索引不论是聚集索引还是非聚集索引，都是数据库另外开辟的空间，并不是依附在原有数据上的。所以当创建索引时，数据库系统会分配一个索引页，每当你往表中插入一行数据，数据库系统也将插入一行索引记录。此时的索引页是根节点，如果满了，则会进行分裂，将原来指向数据页的逻辑指针，更换为指向子索引页的逻辑指针（如下图）。

1.3 索引的类型

1.3.1 聚集索引

所谓聚集索引，就是确定表中数据的物理顺序，如上面图书的按书名排列，又或者手机电话簿的联系人按姓氏排列等。它规定了数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。也就是，索引存储顺序和数据行的存储顺序是一致的。

如上图，我们在名字字段上建立聚集索引，当需要在根据此字段查找特定的记录时，数据库系统会根据特定的系统表查找的此索引的根，然后根据指针查找下一个，直到找到。例如我们要查询“green”，由于它介于[bennet,karsen]，据此我们找到了索引页1007，在该页中“green”介于[greane, hunter]间，据此我们找到叶结点1133（也即数据结点），并最终在此页中找以了目标数据行。

1.3.2 非聚集索引

我们说聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，所以在物理上它就没有所谓的顺序可言，也和物理存储顺序无关。

如上图，可以看到，非聚集索引的索引记录结构发生了一定的变化，它包括：

• 索引字段值（键值）
• 数据页的页指针，以及指针偏移量（相对聚集索引，新增的部分）
• 下一个索引页的指针

因为聚集索引是有顺序的，所以我们最终只需要指向索引页，按顺序就能找到，也正是如此，聚集索引最终索引页存储的是页指针，而不是行指针。

而非聚集索引，因为无序，那么意味着非聚集索引要为每一个数据行存储一条索引记录，才能进行准确查询。这里就有了“数据页的页指针，和指针偏移量”，类似于数据行的坐标，存储在索引记录中。也就是说，非聚集索引存储的是键值和其对应的数据坐标，又为了索引分页，所以也包含第三个部分，用来存储下一个索引页指针。

（就像之前那个故事，如果书籍已经按名字排序，你要找《朝花夕拾》，你知道在Z书架即可；如果书籍还是乱糟糟没有排序，而你手里有个作者分类的目录文档，那你要找鲁迅的《朝花夕拾》，意味着文档上就必须记录书籍具体的位置了）

---

2、汉语字典的例子

如果你还不明白关于“聚集索引和非聚集索引的区别”，这里还有一个形象的例子：

1）聚集索引

汉语字典，其正文本身就是一个聚集索引。

比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字。

同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，你不需要再去查其他目录来找到您需要找的内容。正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

2）非聚集索引

如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。

但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。

---

3、简单总结

• 索引可以提高查询效率
• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续

另外，众所周知的是，索引确实可以提高查询速度，但会影响插入、删除和更新的效率，正是因为数据变化的同时，索引也必须进行更新维护，消耗性能。所以不能盲目地创建索引，而是合理地创建和使用。

另外的另外，索引这部分的坑还是很深的，比如说如果是按照学分查找60-90之间的学生，那么在学分上创建有顺序的聚集索引是否是最优选择？又或者，如何正确使用聚集索引和非聚集索引？等等类似的问题，都涉及到索引的细节和深度理解。

现在自己只能大概理解一些表面意思，实际应用上，因为精力和工作应用暂时不涉及，也就不再继续展开了。

浅谈SQL优化入门，目前也就这3篇博文了，主要也是因为工作上一个SQL问题引发的，便以此展开对涉及知识点进行梳理，笔记于此。

整体来说这次体验有几个点，关于SQL优化的总结：

• 尽量使用显性连接
• 多利用EXPLAIN查看SQL执行顺序
• 使用小的结果集驱动大的结果集
• 合理使用索引

啊，编程路真是漫漫长！

---

4、参考链接

• SQL索引一步到位
• 数据库优化实践【索引篇】
• 数据库进阶系列之一：漫谈数据库索引
• 聚集索引和非聚集索引的区别