MySQL索引原来也没那么难

通过一个例子看看索引的威力

t_user表插入1百万条数据

查找name = user0，耗时213ms
select * from t_user where name = 'user0'

增加name字段的索引
ALTER TABLE t_user ADD INDEX index_name (name)

再次查找，耗时1ms

真厉害啊，但索引究竟是个啥？为什么可以加快数据库的检索速度呢？

首先说说MySQL默认引擎InnoDB的基本存储结构，Page(页)

* 数据是一条一条记录在页中的，组成一个单向链表。
* 多个数据页可以组成一个双向链表。

页与记录的关系如下图：

再说说如果没有用索引，数据库是怎么查找记录的？

比如:SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

1. 定位到记录所在的页。

2. 从所在的页内中查找相应的记录，这里又分两种情况：

   • 以主键为搜索条件，在数据页中有对主键列建立页目录，通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
   • 以其他列为搜索条件，在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上边的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的，如果一个表有一亿条记录，使用这种方式去查找记录那要等到猴年马月才能等到查找结果。

使用索引，加快了查找速度的原因

不用索引查询慢的根本原因是不能快速的定位到记录所在的页。
索引就是使用了B+树这种数据结构，将无序的数据变成了有序的数据，从而加快了查找速度。

可以看到叶子节点存储的是数据记录页，非叶子节点存储是目录项记录页。
另外还有的特点：

• 页内的记录是按照列的大小顺序排成一个单向链表。
• 各个存放用户记录的页也是根据页中记录的列大小顺序排成一个双向链表。
• 各个存放目录项的页也是根据页中记录的列大小顺序排成一个双向链表。

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过“目录”就可以很快地定位到对应的页上了！

一些索引相关知识点

聚集和非聚集索引

• 聚集索引就是以主键创建的索引，聚集索引并不需要我们在MySQL语句中显式的去创建，InnoDB存储引擎会自动的为我们创建聚集索引，叶子节点记录的是完整的一条数据，即主键 + 所有列的一条数据
• 非聚集索引就是以非主键创建的索引，非聚集索引在叶子节点存储的是主键和索引列，查询数据时，不能查找到完整的一条数据，必须再根据主键值去聚集索引生成的B+树中再查找一遍完整的数据。拿到主键再查找这个过程叫做回表，那为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不就好了么？因为把完整的用户记录放到叶子节点是可以不用回表，但是太占地方了呀～相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。

覆盖索引

上面的回表操作的原因是非聚集索引需要找到除了主键和索引列以外的字段数据，那么如果非聚集索引包含了满足查询语句中字段数据不就不需要回表了吗？就叫做覆盖索引。使用覆盖索引避免了回表的产生减少了树的搜索次数，显著提升性能。

联合索引

即对多个列建立索引如key index（a,b)。
即对于让B+树按照a和b列的大小进行排序，这个包含两层：

• 先把各个记录和页按照a列进行排序。
• 在记录的a列相同的情况下，采用b列进行排序

另外

• 建立联合索引只会建立1棵B+树。
• 为a和b列建立索引会分别以a和b列的大小为排序规则建立2棵B+树。

索引最左匹配原则

例如索引是key index（a,b,c),可以支持(a),(a,b),(a,c),(a,b,c)组合进行使用索引的查找，但不支持(b),(c),(b,c)进行查找。这就是mysql最左匹配原则，查询条件里面要有联合索引最左边的那个字段才会用到索引。这也对应了上述建立联合索引的B+树时，第一个字段是有序的，后续字段则是无序的。
联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。

参考:
MySQL的索引
数据库两个神器【索引和锁】