MySQL原理简介—9.MySQL索引原理

大纲

1.磁盘数据页的存储结构

2.没有索引数据库如何搜索数据

3.在表中插入数据时如何进行页分裂

4.如何设计主键索引及如何根据主键索引查询

5.索引的物理存储结构

6.更新数据时自动维护的聚簇索引是什么

7.针对主键之外的字段建立的二级索引

8.插入数据时如何维护不同索引的B+树

9.完整的MySQL的B+树索引原理总结

10.联合索引使用规则

11.在SQL里进行排序时如何能使用索引

12.在SQL里进行分组的时候如何才能使用索引

13.回表查询对性能的损害以及什么是索引覆盖

14.设计索引的原则

15.设计索引的具体案例

1.磁盘数据页的存储结构

数据库所有的数据都会存放到磁盘上的文件，数据在文件里存放的物理格式就是数据页，大量的数据页会按顺序一页一页存放的，两两相邻的数据页之间会采用双向链表的格式互相引用。

数据页在磁盘文件里就是一段数据，可能是二进制或某特殊格式的数据。数据页里包含两个指针：一个指针指向自己上一个数据页的物理地址，一个指针指向自己下一个数据页的物理地址。

在数据页内部存储的一行一行数据，也就是往表里插入的一行一行数据。数据页里的每一行数据都会按照主键大小进行排序存储，同时每一行数据都有指针指向下一行数据的位置，组成单向链表。

LRU链表、flush链表和free链表都是双向链表，数据页之间组成双向链表，数据行之间组成单向链表。

2.没有索引数据库如何搜索数据

(1)数据页在磁盘文件中的物理存储结构

(2)查询数据的情形分析

(1)数据页在磁盘文件中的物理存储结构

数据页之间组成双向链表，数据页内部的数据行组成单向链表。数据行会根据主键从小到大排序进行存储，每个数据页都会有一个页目录，页目录中存放的是每一个数据行的主键和所在槽位的映射关系。数据行会被分散存储到不同的槽位里，一个槽位会有多条数据行。

(2)查询数据的情形分析

情形一：

假设要根据主键查找某个表的一条数据，而且此时该表并没有几条数据，该表总共就一个数据页。那么查找时就会首先到数据页的页目录根据主键进行二分查找，然后通过二分查找在目录里迅速定位到主键对应的数据是在哪个槽位。接着到对应的槽位里，遍历槽位里的每一行数据，这样就能快速找到那个主键对应的数据。

情形二：

假设要根据非主键的其他字段查找某个表的一条数据：这时就没有办法通过页目录的主键来进行二分查找了，只能进入到数据页里，依次遍历单向链表里的每一个数据行来查找，这样性能就很差。

情形三：

上述只是假设只有一个数据页的情况，如果有很多数据页又该如何查询？当有大量数据页的情况下，而且又没有建立任何索引，那么无论是根据主键查询还是根据其他字段进查询，都只能全表扫描。

所谓全表扫描就是：首先将第一个数据页从磁盘上读取到内存Buffer Pool的缓存页里。然后在该缓存页里，如果是根据主键查询，就在其页目录进行二分查找；如果是根据其他字段查询，则只能遍历缓存页中数据行的单向链表来查找；如果第一个数据页没找到，只能根据数据页的双向链表去找下一个数据页。然后读取到Buffer Pool的缓存页里，按照同样方法继续。依此类推，直到找到为止。

在没有任何索引时，不管如何查找数据，都是一个全表扫描的过程。首先根据双向链表依次把磁盘上的数据页加载到Buffer Pool的缓存页中，然后在Buffer Pool的缓存页内部来查找数据，或是二分查找页目录，或是遍历数据页中数据行的单向链表，来找出需要的那条数据。

3.在表中插入数据时如何进行页分裂

(1)数据页的存储和查询

(2)数据页的内部构成

(3)页分裂与主键值

(4)页分裂、主键值与数据移动示例

(1)数据页的存储和查询

数据页之间会组成双向链表，数据页内部的数据行会组成单向链表，每个数据页会根据数据行的主键和槽位构建一个页目录。

在没有索引的情况下，所有的数据查询在物理层面都是全表扫描。也就是首先依次遍历每个数据页，然后扫描其内部的每个数据行。

(2)数据页的内部构成

正常情况下往一个表插入一些数据后，都会进入到一个数据页里。在数据页内部，这些数据会组成一个单向链表，如下图示：

可以看到，数据页里面就是一行一行的数据。刚开始第一行是行类型为2的起始行，代表最小的一行。然后起始行有一个指针指向了下一行数据，每一行数据都有自己每个字段的值，每一行会通过一个指针不停的指向下一行数据。其中普通数据行的行类型为0，最后一行是行类型为3的结束行，代表最大的一行。这就是一个典型的数据页内部的情况。

(3)页分裂与主键值

一.什么时候会出现页分裂

在InnoDB的一个表里不停的插入数据时，会涉及到一个页分裂的过程。正是由于页分裂，才产生了一个个数据页。

假设不停往表里插入数据，那么刚开始会不停往一个数据页插入数据。插入的数据越多，这个数据页就越大，这时就需要一个新的数据页。

但是此时会遇到一个问题，就是索引运作的一个核心基础，即要求后一个数据页的主键值都大于前面一个数据页的主键值。如果数据的主键是自增的，那这一点是可以保证的，因为新插入后一个数据页的主键值一定都大于前一个数据页的主键值。

但有时主键并不是自增长的，所以可能会出现如下的情况：后一个数据页的数据行里，有的主键是小于前一个数据页的主键值。比如第一个数据页里有一条数据的主键是10，第二个数据页里出现一条数据的主键值是8，这时就有问题。所以此时就会出现一个过程，叫做页分裂。

二.什么是页分裂

就是万一主键不是自增而是自定义的，那么在增加一个新的数据页时，会把前一个数据页里主键值较大的数据行，移动到新的数据页里。然后把新插入的主键值较小的数据行，移动到上一个数据页里。从而保证新数据页里的主键值一定比上一个数据页里的主键值都大。

(4)页分裂、主键值与数据移动示例

假设新数据页里，有两条数据的主键值明显小于上一个数据页的主键值：第一个数据页有1、5、6三条数据，第二个数据页有2、3、4三条数据。如下图示：

由于第二个数据页里的主键值比第一个数据页里的两个主键5和6小，于是这时就会出现页分裂行为。也就是会把第二个数据页里的两条数据2和3移动到第一个数据页中，同时把第一个数据页里的两条数据5和6移动到第二个数据页中。

4.如何设计主键索引及如何根据主键索引查询

(1)数据页分裂的过程

(2)根据主键索引查询的过程

(3)基于主键的索引是如何设计的

(4)如何根据主键索引查询

(1)数据页分裂的过程

在不停往表里插入数据时，会创建一个一个的数据页。如果数据主键不是自增，就可能会出现数据行跨页移动。以此保证下一个数据页主键值都大于上一个数据页主键值。

(2)根据主键索引查询的过程

如果要查找主键id=4的数据，由于不知道在哪个数据页，只能全表扫描。从第一个数据页开始，遍历每个数据页都进入到其页目录里查找主键。最坏的情况下，所有数据页都得遍历一遍。

(3)基于主键的索引是如何设计的

此时需针对主键设计一个索引，针对主键的索引实际上就是主键目录。主键目录会把每个数据页的页号，还有数据页里最小的主键值放在一起，从而形成一个主键的目录，如下图示：

(4)如何根据主键索引查询

有了主键目录，查询时就可以直接到主键目录去查找了。通过和最小主键值进行对比，就可以快速定位数据在哪个数据页中，之后便可以到对应的数据页中，根据主键值找出要找的数据。类似这种主键目录，就可以认为是索引目录。

5.索引的物理存储结构

既然索引目录里会包含每个数据页和它的最小索引值。那么查询主键值时，通过二分查找索引目录就能快速定位对应的数据页。接着进入对应的数据页的页目录，继续二分查找就能快速定位所查数据。

但是现在问题来了，表里的数据可能很多，比如有几百万甚至上千万。此时有大量数据页，于是索引目录就要存储大量的数据页和最小索引值。那么应该如何存储索引目录？

MySQL将索引目录存储在专门的数据页上，这些数据页称为索引页；

如果有很多数据页，此时就需要很多索引页。为了快速查找索引页的数据，可从索引页的基础上多加一个层级出来。在更高的索引层级里，保存每个索引页和索引页里的最小主键值。

现在假设要查找id=46：首先可以到最顶层的索引页35里去找，直接通过二分查找定位到下一步应该到索引页20里去找。接着到索引页20里通过二分查找也能很快定位到数据应该在数据页8里。再进入数据页8里，就可以根据页目录+二分查找定位id=46的那行数据。

假如最顶层的那个索引页35存放的下层索引页的页号太多了，怎么办？此时可以再次进行页分裂，增加一层索引页：

其实这就是一棵B+树，所以才说MySQL的索引是用B+树来组成的。当数据库为一个表的主键建立索引后，这个主键的索引就是一棵B+树。当数据库需要根据主键来查询数据时，会从B+树的顶层开始二分查找。一层一层往下定位，最终一直定位到一个数据页里，然后在数据页内部的目录里继续二分查找，最后找到那条数据。

以上就是索引的物理存储结构：

一.采用和数据页一样的索引页来存储索引数据；

二.一个索引就是很多索引页组成的一棵B+树；

6.更新数据时自动维护的聚簇索引是什么

(1)基于索引数据结构去查找主键的过程

(2)聚簇索引是什么

(1)基于索引数据结构去查找主键的过程

假设要查找一个主键id对应的行，则数据库会先去查找顶层的索引页88。然后通过二分查找的方式，很容易定位到应该去下层哪个索引页里去继续找。

假设定位到了下层的索引页35，在索引页35里也有一些索引条目。这些索引条目分别是下层索引页(20,28)和它们的最小主键值。

此时进入索引页35里二分查找，可定位到应该到下层的哪个索引页查找。假设从索引页35接着找，就找到应该到下层的索引页28里去继续查找。

此时索引页28里肯定也是有索引条目的，里面存放了部分数据页页号。假设索引页28存放了数据页2和数据页8以及每个数据页最小的主键值。

于是在索引页28继续二分查找，就可定位到应该到哪个数据页里去找。假设需要进入数据页2，而数据页2里就会有一个页目录。在数据页2的页目录里存放了各行数据的主键值和行的实际物理位置。

于是继续在数据页2的页目录里二分查找，就可以快速定位到要查找的主键值对应的数据行的物理位置，最后便可以在数据页2里找到对应的数据。这就是基于索引数据结构去查找主键的过程。

(2)聚簇索引是什么

其实最下层的索引页，都会有指针引用数据页的，所以实际上索引页之间跟数据页之间会通过指针连接起来。在索引页内部，同一个层级的索引页互相之间会基于指针组成双向链表，就和数据页之间可以组成双向链表一样。

假设把索引页和数据页综合起来看，它们都是连接在一起的，看起来就如同一棵完整的大的B+树一样。从根索引页88开始，一直到所有的数据页，会组成一棵巨大的B+树。在这棵树里最底层的一层就是数据页，而数据页就是B+树里的叶子节点。

所以如果一棵大的B+树索引数据结构里，叶子节点就是数据页自己本身。那么此时我们就可以称这棵B+树索引为聚簇索引。因此上图中所有的索引页+数据页组成的B+树，就是聚簇索引。

一般来说，即便是亿级的大表，所建的索引的层级也就三四层而已。这个聚簇索引默认是按主键来组织的，所以数据库在增删改数据的时候，会更新数据页，同时自动维护B+树结构的聚簇索引，新增和更新索引页。

7.针对主键之外的字段建立的二级索引

(1)聚簇索引和主键搜索

(2)对主键外的其他字段建立索引的原理

(1)聚簇索引和主键搜索

聚簇索引就是Innodb存储引擎默认创建的一个基于主键的索引结构，而且表里的数据默认就是直接放在聚簇索引里的。聚簇索引的叶子节点就是数据页自己本身。

对主键数据的搜索，其实就是从聚簇索引的根节点开始进行二分查找。一直找到对应的数据页里，最后基于页目录来二分查找定位到数据行。

(2)对主键外的其他字段建立索引的原理

一.该索引也维护B+树来存放索引和主键字段

二.该索引也按索引值排序及构建多层级索引页

三.按该索引查询完整数据要回表查询主键索引

四.联合索引的排序规则和查询

一.该索引也维护B+树来存放索引和主键字段

假设需要对其他字段建立索引，比如name字段。那么数据库就需要维护一个聚簇索引和一个name字段的索引，即要维护聚簇索引的B+树，也要维护name字段索引的B+树。

所以插入数据时，会先把完整数据插入到聚簇索引的叶子节点的数据页。再把部分数据(主键 + name)插入到name字段索引的叶子节点的数据页。

二.该索引也按索引值排序及构建多层级索引页

需要注意：name字段索引是独立于聚簇索引之外的一棵索引B+树。

name字段的索引B+树里的叶子节点的数据页只放主键和name字段的值。name的排序规则和主键的排序规则一样，也按name值的大小进行排序，也就是下一个数据页的name字段值要大于上一个数据页的name字段值。

然后name字段的索引B+树也会构建多层级的索引页，这个索引页里存放的就是下一层的页号和最小name字段值。

三.按该索引查询完整数据要回表查询主键索引

如果要根据name字段来搜索数据，也会从name字段的索引B+树里的根节点开始查找，一层一层往下找，一直找到叶子节点的数据页为止。

但找到叶子节点的数据页，也仅仅是找到对应的主键值，还不能找到这行数据完整的所有字段，所以这时候还需要进行回表查询。

这个回表，指的是还需要根据主键值，到聚簇索引里从根节点开始查找。一直找到叶子节点的数据页，才能定位到主键对应的完整数据行。

由于根据name字段索引B+树找到主键后，还要根据主键去聚簇索引找，所以一般把name字段这种普通字段的索引称为二级索引。相对应的，一级索引就是聚簇索引。

四.联合索引的排序规则和查询

此外也可以把多个字段联合起来，建立联合索引，比如name + age。此时联合索引的运行原理也是一样的，只不过是新建一棵独立的B+树。这棵新的B+树的叶子节点的数据页里会存放id + name + age。

联合索引name + age的排序规则是默认按name排序，name一样就按age排序。不同数据页之间的name + age值的排序也是如此。

在这个name + age的联合索引的B+树的索引页里，放的就是下层节点的页号和最小的name + age的值。所以根据name + age查询时，就会使用name + age联合索引这棵B+树。搜索到主键之后再根据主键到聚簇索引里搜索。

以上就是Innodb存储引擎的索引完整实现原理。

8.插入数据时如何维护不同索引的B+树

(1)MySQL插入数据时聚簇索引的维护过程

(2)MySQL插入数据时二级索引的维护过程

(1)MySQL插入数据时聚簇索引的维护过程

一.创建表时只有一个数据页

二.只有一个数据页时的查询

三.根页分裂 -> 根页成为索引页

四.数据页分裂 -> 索引页分裂

一.创建表时只有一个数据页

首先一个表完成创建后，它就是一个数据页。这个数据页属于聚簇索引的一部分，而且当前还是空的。此时MySQL插入数据，会直接往这个数据页里插入，不用新增索引页。

二.只有一个数据页时的查询

这时这个初始的数据页其实就是一个根页。由于每个数据页内默认有一个基于主键的页目录，所以可根据主键来查。查询时直接在这唯一的数据页中，通过页目录来查询即可。

三.根页分裂 -> 根页成为索引页

随后MySQL会往表插入越来越多的数据，此时数据页满了，就会新增两个数据页，把根页里的数据都拷贝到新的两个数据页上，并且会按照主键值大小进行拷贝，让两个新的数据页能根据主键值排序，确保第二个数据页的主键值都大于第一个数据页的主键值。

拷贝完成后根页会升级为索引页，这个索引页里放的是两个新增数据页的页号和它们里面最小的主键值。如下图示：唯一一个数据页(根页)满了之后，根页会成为索引页并引用两个数据页。

四.数据页分裂 -> 索引页分裂

之后MySQL继续往表里插入数据，然后数据页会不停的进行页分裂，分裂出越来越多的数据页。此时唯一的一个索引页(根页)里面的索引条目也越来越多，当这个索引页也放不下时，这个索引页也会分裂成两个索引页。原来唯一的根页会往上提一个层级，然后引用这个两个索引页。

五.以此类推

当插入的数据继续增多，数据页越来越多，根页指向的索引页也会不停的分裂，索引页也越来越多。当根页下面的索引页数量太多时，一个根页已经放不下所有的索引页了。这时会导致根页也分裂成多个索引页，根页再次往上提一个层级。

以上就是插入数据时，聚簇索引的维护过程。

(2)MySQL插入数据时二级索引的维护过程

假设name字段有索引，那么刚开始插入数据时：首先会在聚簇索引的唯一数据页里插入数据，然后会在name字段索引B+树的唯一数据页里插入数据。随后插入的数据越来越多，name字段索引B+树的唯一数据页也会分裂，分裂过程和聚簇索引一样。

所以MySQL插入数据时，本身就会自动维护各个索引的B+树。在name字段索引B+树的索引页中，除存放页号和最小name字段值外，每个索引页还会存放最小name字段值对应的主键值。

9.MySQL的B+树索引原理总结

(1)聚簇索引的叶子节点才是数据页

(2)索引页或数据页之间组成双向链表 + 页内记录组成单向链表

(3)高效查找原因

(4)二级索引的维护

(5)基于二级索引的查找与回表

(6)建立索引的好处和坏处

B+树索引的数据结构、排序规则、插入时索引页的形成过程、基于B+树的查询原理、不同字段的索引有独立B+树、回表的过程。

(1)聚簇索引的叶子节点才是数据页

默认情况下MySQL建立的聚簇索引都是基于主键值来组织的。聚簇索引的叶子节点都是数据页，里面放的就是插入的完整数据。

(2)索引页或数据页之间组成双向链表 + 页内记录组成单向链表

数据页或索引页里的记录会组成一个单向链表，按数据大小有序排列。数据页或索引页之间会组成一个双向链表，按数据大小有序排列。

(3)高效查找原因

正是因为这个有序的B+树索引结构，才能让MySQL查找数据时，从B+树的根节点开始，按数据值大小通过二分查找一层一层往下高效查。

(4)二级索引的维护

如果针对主键外的字段建立索引，那么本质上就是为那个字段的值重新建立另外一棵B+树索引。这棵索引B+树的叶子节点也是数据页，存放的只有字段的值和主键值。而每层索引页存放的都是下层索引页或数据页的引用。

(5)基于二级索引的查找与回表

假设要根据非主键字段的索引来查找一行完整的数据。首先会基于非主键字段的索引B+树，快速查找到那个值所对应的主键值。然后再根据这个主键值，去主键的聚簇索引B+树里，进行回表查询，即重新从根节点开始查找那个主键值。最后找到主键值对应的完整数据。

(6)建立索引的好处和坏处

好处是不需要全表搜索，可以将查询性能提升得很高。因为可以直接根据某个字段的索引B+树来进行高效查找数据。

坏处有两点，分空间上和时间上两个方面。空间上要给很多字段创建索引，创建很多棵索引B+树，很耗费磁盘空间。时间上在增删改时，每次都需要维护各个索引的数据有序性。因为每个索引B+树都要求页内按照索引值大小排序，索引页之间有序。而不停的增删改会导致数据页不停分裂、不停增加索引页，很耗费时间。

所以一般不建议一个表里设置太多索引。

10.联合索引使用规则

(1)规则一：等值匹配规则

(2)规则二：最左侧列匹配规则

(3)规则三：最左前缀匹配规则

(4)规则四：范围查找规则

(5)规则五：等值匹配 + 范围匹配的规则

(6)如何判断联合索引的字段是否还在生效

联合索引中的数据页或索引页是按照联合索引的字段顺序进行排序的。先根据第一个字段排序，再根据第二个字段排序，依此来建立顺序关系。

(1)规则一：等值匹配规则

在where语句中的字段名称和联合索引的字段完全一样，而且都是基于等号的等值匹配，那么肯定会用上联合索引。

即便where语句里的字段顺序和联合索引里的字段顺序不一致，MySQL也会自动优化按联合索引的字段顺序去找。

(2)规则二：最左侧列匹配规则

假设联合索引是key(class_name, student_name, subject_name)，那么不一定要在where语句里根据这三个字段来查。其实只要根据最左侧的部分字段，也是可以利用索引进行查询的。在联合索引的B+树里，必须先按class_name查，再按student_name查，不能跳过前面两个字段，直接按最后一个subject_name来查。由如下例子可知，联合索引最好是区分度大的排前面。

//可以利用上联合索引来查
mysql> select * from student_score where class_name = '' and student_name = '';
//不能利用联合索引来查
mysql> select * from student_score where subject_name = '';
//只有class_name的值可以在联合索引里搜索，剩下的subject_name没法利用联合索引搜索
mysql> select * from student_score where class_name = '' and subject_name = '';

(3)规则三：最左前缀匹配规则

一.假如使用的是 like '1%' 查询

//查找所有1开头的班级的分数，那么也是可以用到联合索引的
mysql> select * from student_score where class_name like '1%';

那么这也是可以用到联合索引的，因为在联合索引的B+树里，都是按class_name排序的。所以要是给出class_name的确定的最左前缀就是1，然后后面给的一个模糊匹配符号，那也可以基于索引来查找。

二.假如使用的是 like '%班' 查询

在左侧用一个模糊匹配符，那么就没法用联合索引。

(4)规则四：范围查找规则

使用如下范围查找，也可以利用上联合索引：

//可以用上联合索引
mysql> select * from student_score where class_name > '1班' and class_name < '5班';

因为索引的最下层的数据页都是按顺序组成双成双向链表的，所以完全可以先找到'1班'对应的数据页，再找到'5班'对应的数据页。两个数据页中间的那些数据页，就全都是在所查范围内的数据。但是如下的语句只有class_name是可以基于联合索引来查，student_name的范围是没法使用到联合索引的：

//只有class_name是可以使用联合索引，student_name的范围是没法使用到联合索引
mysql> select * from student_score where class_name > '1班' and class_name < '5班' and student_name > '';

所以如果使用的where语句里有范围查询，那么只有对联合索引最左侧的列进行范围查询才能用到索引。

(5)规则五：等值匹配 + 范围匹配的规则

mysql> select * from student_score where class_name = '1班' and student_name > '' and subject_name < '';

该语句可以使用class_name在联合索引里精准定位到一堆数据。然后这堆数据里的student_name都是按照顺序排列的，所以student_name > ''也基于索引来查找，但接下来的student_name < ''是不能用索引的。

(6)如何判断联合索引的字段是否还在生效

可以根据，前面的字段按照索引筛选后，得出的结果是否还能按后面的字段顺序排列，来判断后面的字段的索引是否有效。

11.在SQL里进行排序时如何能使用索引

当SQL语句里使用where语句进行数据过滤和筛选时，从联合索引最左侧的字段开始去使用，保证前一个字段的筛选结果能按当前字段顺序排列，这样当前字段就能用上索引树。

当SQL语句里使用order by语句进行排序的时候：尽量按照联合索引的字段顺序去进行order by排序，从而利用上联合索引B+树里的数据有序性。

但要注意，按多个字段排序时，这多个字段要么都是升序要么都是降序。不能出现一个字段升序一个字段降序，否则只能先基于where语句筛选出数据，然后放到内存或临时磁盘文件，接着再通过排序算法按照某个字段来进行排序。

基于临时磁盘文件来排序，MySQL里叫做filesort。

12.在SQL里进行分组的时候如何才能使用索引

假设要执行一个类似如下的语句：

mysql> select count(*) from table group by xxx;

似乎需要把所有的数据都放到一个临时磁盘文件加上部分内存，按照指定字段分成一组一组，然后对每一组都执行一个聚合函数，这样会涉及大量的磁盘交互，性能很差。

所以通常而言，对于group by后的字段，最好也是按照联合索引里的最左侧的字段开始，按顺序排列开来。这样就可以完美的利用索引来直接提取一组一组的数据，然后针对每一组的数据执行聚合函数即可。

其实group by和order by用上索引的原理和条件都是一样的。如果在group by后的字段顺序和联合索引中最左侧开始的字段顺序一致，那么就可以充分利用索引树里已经完成排序的特性。

所以进行表设计时，通常设计两三个常用的索引，覆盖常见的where筛选、order by排序和group by分组的需求，保证常见SQL都能用上索引，系统跑起来不会有太大的查询性能问题。

13.回表查询对性能的损害以及什么是索引覆盖

(1)按索引字段查询非索引字段的值需要回表

(2)回表操作可能不使用联合索引而用全表扫描

(3)有限制条数的回表操作还是会使用联合索引

(4)索引覆盖会在索引树上获取数据无需回表

(1)按索引字段查询非索引字段的值需要回表

一般我们自己建的索引不管是单列索引还是联合索引：一个索引就对应着一棵独立的索引B+树，其节点仅包含索引里的字段的值和主键值。

所以即使根据索引树按照条件找到了需要的数据，这些数据也仅仅是索引里的几个字段的值和主键值。万一查询的是其他非索引里的字段，那还需要进行回表操作，也就是根据主键到聚簇索引里把所需字段提取出来。

(2)回表操作可能不使用联合索引而用全表扫描

比如，类似如下的查询语句：

mysql> select * from table order by xx1,xx2,xx3;

可能先从联合索引的索引树里按照顺序取出所有数据，然后每条数据都根据主键去聚簇索引查找，这样性能也不高。甚至有时MySQL的执行引擎可能会认为：这种语句相当于把联合索引和聚簇索引都扫描一遍，还不如不去使用联合索引，而直接全表扫描。

(3)有限制条数的回表操作还是会使用联合索引

但是类似如下这样的语句：

mysql> select * from table order by xx1,xx2,xx3 limit 10;

MySQL的执行引擎就会知道，需要扫描联合索引的索引树拿到10条数据。然后对10条数据在聚簇索引里查找10次，那么还是会使用联合索引的。

(4)索引覆盖会在索引树上获取数据无需回表

索引覆盖不是一种索引，而是基于索引查询的一种方式。需要的字段直接在索引树里就能提取出来，不需要回表到聚簇索引，这样的查询方式就是索引覆盖。

(5)总结

尽量在SQL里指定只需要的几个字段，不要将非索引里的字段也查出来。这样就能利用起索引覆盖的方式，不用回表查聚簇索引。即便需要回表查聚簇索引，尽量使用limit来限定回表聚簇索引的次数。

14.设计索引的原则

(1)设计的索引尽量包含上where、order by、group by里的字段

(2)根据离散度来选择索引字段，尽量使用基数较的、值多的字段

(3)一个表的索引不要太多，最好两三个联合索引能覆盖掉全部查询

(4)查询枚举字段时比如性别可通过in的方式改造成使用联合索引

(5)经常使用范围查询的字段最好可以放在联合索引最后面

(6)针对低基数字段的查询可以使用id设计辅助索引来处理

(1)设计的索引尽量包含上where、order by、group by里的字段

设计两三个联合索引，要尽量包含where、order by、group by里的字段。尽量让where、order by、group by后的字段顺序，都能命中联合索引。

(2)根据离散度来选择索引字段，尽量使用基数较的、值多的字段

这样才能发挥出B+树快速二分查找的优势。同时，尽量对字段类型较小的字段建立索引，如tinyint、varchar(255)。如果要建立索引的字段的值太大，可以取字段值的前20个字符建立索引。

比如：字段name是varchar(255)类型，但需要基于字段name建立索引。那么可以建立这样的索引key my_index(name(20),age,course)，不过这种索引只能用在where条件下，不能用在order by和group by条件。

但即便字段基数较低，却频繁查询的字段，也可放到联合索引的最左侧。比如性别字段、省市字段等。

(3)一个表的索引不要太多，最好两三个联合索引能覆盖掉全部查询

索引太多必然导致对数据进行增删改时性能很差。此外主键也最好自增，不要使用UUID之类的主键。

自增的主键可以保证聚簇索引不会频繁的产生页分裂。UUID的主键会导致聚簇索引频繁的页分裂，增删性能查。

(4)查询枚举字段时比如性别可通过in的方式改造成使用联合索引

某些查询语句即便只需要联合索引的部分字段作为条件去查询，也可以通过in语句的方式，把查询语句改造成可以使用联合索引。

比如key(city,sex,age)，虽然只需查where city = 'xxx' and age > xxx，也可以改造成where city = 'xxx' and sex in (0,1) and age > xxx。

也就是说，对于那些枚举字段，查询的时候也可以加进来作为条件。从而利用上联合索引，不然原来的语句是利用不上索引的。

(5)经常使用范围查询的字段最好可以放在联合索引最后面

从而保证范围查询字段前面的条件字段也可以用到索引，比如上面的age就是范围查询使用得多。

(6)针对低基数字段的查询可以使用id设计辅助索引来处理

比如：

mysql> select * from users where sex = 'man' limit x,y;

可以增加辅助字段score，然后建立索引key(sex, score)，查询时使用：

mysql> select * from users where sex = 'man' order by score limit x,y;

也可以使用id建立辅助索引：key_sex_id(sex,id)，查询时使用：

mysql> select * from users where sex = 'man' order by id limit x,y;

总之，尽量利用一两个复杂的多字段联合索引，抗下80%以上的查询。然后用一两个辅助索引抗下剩余20%的非典型查询。

15.设计索引的具体案例

(1)在大部分情况下where筛选和order by排序是没法都用到索引的

(2)where和order by索引冲突，通常让where使用索引来快速筛选数据

(3)可以把基数太低的字段(每次查询几乎用到)放到联合索引最左侧

(4)通过in语句的方式把查询语句改造成可以使用联合索引的查询语句

(5)必须把经常用做范围查询的字段放在联合索引的最后一个

(6)可以把一些复杂的查询条件转换为枚举字段

(7)通过辅助索引解决where条件里都是基数低的字段且还要排序后分页

(1)在大部分情况下where筛选和order by排序是没法都用到索引的

对于如下的SQL，假如就一个联合索引key(age, score)，那么where可以用上索引，但是排序是基于score字段，此时不能用索引；假如针对age和score分别设计了两个索引，SQL里如果基于age索引进行筛选就不能利用score索引进行排序。

mysql> select xxx from user_info where age between 20 and 25 order by score limit 1,10;

(2)where和order by索引冲突，通常让where使用索引来快速筛选数据

当出现索引冲突时，是针对where设计索引还是针对order by设计索引？

这个问题的本质就是：是让where语句先基于联合索引去筛选出一部分用户指定的数据，接着再把数据加载到内存或基于临时磁盘文件进行指定条件的排序，最后用limit语句拿到一页数据；还是让order by语句按照索引顺序去找，找的过程中再基于where的条件筛选出指定数据，然后再根据limit语句拿出一页数据。

其实一般都是让where条件使用索引来快速筛选出一部分指定数据，接着再进行排序，最后再从排序后的数据中拿出一页数据。因为基于索引进行where筛选能以最快速度筛选出需要的少部分数据，如果筛选出的数据量不是太大，那么后续排序和分页的成本就不会太大。

(3)可以把基数太低的字段(每次查询几乎用到)放到联合索引最左侧

前面说过，基数太低的字段最好别放到索引里。假如某个SQL的where条件需要用到这几个字段：省份、城市和性别，由于其基数太小而不包含到联合索引里。那么每次执行该SQL查询时，只能先把这几个字段放在where条件最后，然后用联合索引查出一部分数据，接着将这些数据加载到内存，再根据where条件的省份、城市和性别这几个字段进行过滤筛选。这样每次查询都得多这么一个步骤了。

与其如此，还不如就把省份、城市和性别字段，放在联合索引的最左侧。这样跟其他字段组合联合索引后，大部分查询都可以通过索引树把where条件指定的数据筛选出来。

可以把基数较低但是频繁查询(几乎每次查询都会指定)的几个字段，放到联合索引的最左侧，这样让每次查询时直接从索引树里进行筛选。

(4)通过in语句的方式把查询语句改造成可以使用联合索引的查询语句

假设查询时的where条件如下，而索引设计成(province, city, sex, age)；

where province=xx and city=xx and age between xx and xx

此时因为age不在索引里，所以就根本没法通过age在索引里进行筛选。但是如果把上述语句改写成如下，那也是没法让age用上索引去筛选的。因为city和age中间差了一个sex，此时不符合最左侧连续多个字段原则。

where province=xx and city=xx and age>=xx and age<=xx

针对这个问题，可以把where语句写成如下所示：这样就能让整个where语句里的条件全部都在索引树里进行筛选和搜索。

where province=xx and city=xx and sex in ('female', 'male') and age >=xx and age<=xx

假设在查询语句里还有一些频繁使用的条件，比如兴趣爱好和性格特点，而这个兴趣爱好和性格特点，往往都是有固定的一些枚举值。那么针对这些频繁使用的枚举值字段，也完全可以加入到联合索引里。从而设计成(province, city, sex, hobby, character, age)这样的联合索引。

此时假设出现了这样一个查询，按照省份、城市、性格和年龄进行搜索，那么SQL可以按如下in方式写：

where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx

也就是说，即使不需按性别和爱好筛选，但在SQL里可以对这两个字段用in语句，把所有枚举值都放进去。这样就能让province，city，character和age四个要筛选的字段用上索引。

(5)必须把经常用做范围查询的字段放在联合索引的最后一个

为什么一直强调age字段必须要放在联合索引的最后一个呢？因为索引规则中，假设where语句里有等值匹配还有范围匹配，此时必须先让联合索引最左侧开始的多个字段使用等值匹配，接着最后一个字段是范围匹配。

比如下面的语句，完全就是按照联合索引最左侧开始的。province、city、sex、hobby、character是联合索引最左侧的多个字段，它们都是等值匹配，而最后一个age字段使用的是范围匹配，这种就可以完全用上索引。

where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx

但假设在联合索引里把age放在中间位置，设计一个类似(province, city, sex, age, hobby, character)的联合索引。接着SQL写成如下所示：

where province=xx and city=xx and sex in(xx, xx) and age>=xx and age<=xx and hobby in (xx, xx, xx, xx) and character=xx

那么只有province, city, sex, age几个字段可以用上索引。因为在SQL里，一旦一个字段做范围查询用到了索引，那么这个字段接下来的条件就都不能用索引了。所以必须把经常用做范围查询的字段放在联合索引的最后一个，这样才能保证SQL里每个字段都能基于索引去查询。

(6)可以把一些复杂的查询条件转换为枚举字段

把索引设计成(province, city, sex, hobby, character, age)的原因总结：首先让最频繁查询的一些条件都放到索引里去。然后查询时如果有些字段是不使用的，可用in(所有枚举值)的方式写SQL。同时对范围查询的age字段必须放在最后一个，保证范围查询也用上索引。

接下来假设在查询时还有一个条件：要根据用户最近登录时间筛选最近7天登录过APP的用户，而用户表里有一个叫latest_login_time的字段。

要是在where条件里直接加入一个latest_login_time <= 7天内的语句，那么肯定是没法用上索引，因为这里会用一些计算或者函数来对比时间。而且假设where条件查询里还有age进行范围查询，那么根据前面提到：范围查询时也就只有第一个范围查询是可以用上索引，第一个范围查询后的其他范围查询是用不上索引的。

也就是说，即使索引设计成这样：

(province, city, sex, hobby, character, age, latest_login_time)

然后where语句写成如下这样：

where xx xxx and age>=xx and age<=xxx and latest_login_time>=xx；

虽然age和latest_login_time都在联合索引里，但按照规则，只有age范围查询可以用到索引，latest_login_time始终是用不到索引的。

所以此时有一个技巧，就是在设计表时，就必须考虑到这个问题。此时完全可以设计一个字段为does_login_in_latest_7_days，也就是该用户是否在最近7天内登录过APP。假设在7天内登录了这个APP，那么这个字段就是1，否则超过7天没登录，这个字段就是0。这样就把一个筛选时间字段的查询转换为了一个枚举值的字段。接下来的解决方案就简单化了，可以设计一个联合索引为：

(province, city, sex, hobby, character, does_login_in_latest_7_days, age)

然后查询时就在where条件里带上一个does_login_in_latest_7_days=1，最后再跟上age范围查询，就可以让where条件里的字段都用索引了。

(7)通过辅助索引解决where条件里都是基数低的字段且还要排序后分页

万一仅仅用联合索引里一些基数特别小的字段来筛选，如基于性别筛选。那么这样一下子就会筛选出所有女性，可能有上百万用户数据。接着还要磁盘文件进行排序再分页，那这个性能可能就会极差了。

因此可针对那种基数很低的字段 + 排序字段单独额外设计一个辅助索引，专门用于解决where条件里都是基数低的字段，且要进行排序后分页。

比如可以设计一个联合索引为(sex, score)，其中的score也可以换成id。那么对于如下SQL，使用之前设计的那个联合索引。那绝对是基本没法用上索引的，而且性能极差。

mysql> select xx from user_info where sex='female' order by score limit xx,xx;

但如果使用了上述设计的辅助索引(sex, score)后：因为where条件里的字段是等值匹配，而且还是等于某个常量值。所以虽然order by后跟的score字段是(sex, score)索引里的第二个字段，order by没有从索引最左侧字段开始排列，但可以使用索引来排序，所以这条SQL语句整体运行的效率是非常高的。