1、 深入研究索引之前，先来看看磁盘数据页的存储结构

前面我们已经给大家把MySQL数据库的部分内核原理，更新语句的执行原理，事务原理以及锁原理，都 初步的讲给大家听了，同时还穿插了一些相关的数据库性能优化的案例，相信现在大家已经对数据库执 行增删改语句的原理有了较为深入的理解了。

但是今天在深入研究索引之前，我们需要先来看看磁盘上的数据文件中的数据页的物理存储结构，因为 后续研究索引的物理存储结构以及使用原理的时候，都是跟数据页的物理存储结构是有很大关联的。

其实之前大家都知道，数据库最终所有的数据（包括我们建的各种表以及表里的数据）都是要存放在磁 盘上的文件里的，然后在文件里存放的物理格式就是数据页，那么大量的数据页在磁盘文件里是怎么存 储的呢？ 首先大家要明白的一点是，大量的数据页是按顺序一页一页存放的，然后两两相邻的数据页之间会采用 双向链表的格式互相引用， 其实一个数据页在磁盘文件里就是一段数据，可能是二进制或者别的特殊格式的数据，然后数据页里包 含两个指针，一个指针指向自己上一个数据页的物理地址，一个指针指向自己下一个数据页的物理地 址，大概可以认为类似下面这样。

上面那段示例数据，当然不能完全认为是MySQL数据库的磁盘文件里的存储格式，但是我这里就是给你 看一些类似的东西，其实MySQL实际存储大致也是类似这样的，就是每个数据页在磁盘文件里都是连续 的一段数据。 然后每个数据页里，可以认为就是DataPage打头一直到 || 符号的一段磁盘里的连续的数据，你可以认 为每一个数据页就是磁盘文件里这么一段连续的东西。 然后每个数据页，都有一个指针指向自己上一个数据页在磁盘文件里的起始物理位置，比如 linked_list_pre_pointer=15367，就是指向了上一个数据页在磁盘文件里的起始物理位置，那个15367 可以认为就是在磁盘文件里的position或者offset，同理，也有一个指针指向自己下一个数据页的物理 位置。

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

2、 假设没有任何索引，数据库是如何根据查询语句搜索数据的？

上一次我们给大家讲解了数据页在磁盘文件中的物理存储结构，大家应该目前都知道数据页之间是组成 双向链表的，然后数据页内部的数据行是组成单向链表的，而且数据行是根据主键从小到大排序的。 然后每个数据页里都会有一个页目录，里面根据数据行的主键存放了一个目录，同时数据行是被分散存 储到不同的槽位里去的，所以实际上每个数据页的目录里，就是这个页里每个主键跟所在槽位的映射关 系，

所以假设你要根据主键查找一条数据，而且假设此时你数据库里那个表就没几条数据，那个表总共就一 个数据页，那么就太简单了！首先就会先到数据页的页目录里根据主键进行二分查找（PS：不知道二分 查找是什么的同学，建议去网上查一下，这是大学最基础算法） 然后通过二分查找在目录里迅速定位到主键对应的数据是在哪个槽位里，然后到那个槽位里去，遍历槽 位里每一行数据，就能快速找到那个主键对应的数据了。每个槽位里都有一组数据行，你就是在里面遍 历查找就可以了。 但是假设你要是根据非主键的其他字段查找数据呢？ 那就尴尬了，此时你是没办法使用主键的那种页目录来二分查找的，只能进入到数据页里，根据单向链 表依次遍历查找数据了，这就性能很差了。 好，那么现在假如我们有很多数据页呢？ 对了，一个表里往往都是有大量数据的，可能有多达成百上千个数据页，这些数据页就存放在物理磁盘 文件里 所以此时是如何查询数据的呢？ 之前就有不少同学在后台评论区提问过这个问题，这里我们可以先给大家解释一下，假设你要是没有建 立任何索引，那么无论是根据主键查询，还是根据其他字段来条件查询，实际上都没有什么取巧的办 法。 你一个表里所有数据页都是组成双向链表的吧？好，有链表就好办了，直接从第一个数据页开始遍历所 有数据页，从第一个数据页开始，你得先把第一个数据页从磁盘上读取到内存buffer pool的缓存页里 来。 然后你就在第一个数据页对应的缓存页里，按照上述办法查找，假设是根据主键查找的，你可以在数据 页的页目录里二分查找，假设你要是根据其他字段查找的，只能是根据数据页内部的单向链表来遍历查 找，

假设第一个数据页没找到你要的那条数据呢？ 没办法，只能根据数据页的双向链表去找下一个数据页，然后读取到buffer pool的缓存页里去，然后按 一样的方法在一个缓存页内部查找那条数据。 如果依然还是查找不到呢？ 那只能根据双向链表继续加载下一个数据页到缓存页里来了，以此类推，循环往复。 不知道大家看到这个过程有什么感想没有？有没有觉得，你似乎是在做一个数据库里很尴尬的操作：全 表扫描？ 对了，其实上述操作过程，就是全表扫描，在你没有任何索引数据结构的时候，无论如何查找数据，说 白了都是一个全表扫描的过程，就是根据双向链表依次把磁盘上的数据页加载到缓存页里去，然后在一 个缓存页内部来查找那条数据。 最坏的情况下，你就得把所有数据页里的每条数据都得遍历一遍，才能找到你需要的那条数据，这就是 全表扫描！

3、索引的设计原理

1、 基于主键的索引是如何设计的，以及如何根据主键索引查询？

现在是这样的，假设我们有多个数据页，然后我们想要根据主键来查询数据，那么直接查询的话也是不 行的，因为我们也不知道主键到底是在哪里，是不是？

现在假设你要搜id=4的数据，你怎么知道在哪个数据页里？没有任何证据可以告诉你他到底是在哪个数 据页里啊！ 所以假设还是这个样子的话，你也就只能全表扫描了，从第一个数据页开始，每个数据页都进入到页目 录里查找主键，最坏情况下，所有数据页你都得扫描一遍，还是很坑的。 所以其实此时就需要针对主键设计一个索引了，针对主键的索引实际上就是主键目录，这个主键目录 呢，就是把每个数据页的页号，还有数据页里最小的主键值放在一起，组成一个索引的目录

直接就可以到主键目录里去搜索，比如你要找id=3的数据，此 时就会跟每个数据页的最小主键来比，首先id=3大于了数据页2里的最小主键值1，接着小于了数据页8 里的最小主键值4。 所以既然如此，你直接就可以定位到id=3的数据一定是在数据页2里的！ 假设你有很多的数据页，在主键目录里就会有很多的数据页和最小主键值，此时你完全可以根据二分查 找的方式来找你要找的id到底在哪个数据页里！

而大家都知道我们的数据页都是一坨一坨的连续数据放在很多磁盘文件里的，所以只要你能够根据主键 索引定位到数据所在的数据页，此时假设我们有别的方式存储了数据页跟磁盘文件的对应关系，此时你 就可以找到一个磁盘文件。 而且我们假设数据页在磁盘文件里的位置也就是offset偏移量，你也是可以知道的，此时就可以直接通 过随机读的方式定位到磁盘文件的某个offset偏移量的位置，然后就可以读取连续的 一大坨数据页了！

2、 索引的页存储物理结构，是如何用B+树来实现的？

上一次我们给大家说了主键索引的目录结构，只要在一个主键索引里包含每个数据页跟他最小主键值， 就可以组成一个索引目录，然后后续你查询主键值，就可以在目录里二分查找直接定位到那条数据所属 的数据页，接着到数据页里二分查找定位那条数据就可以了

但是现在问题来了，你的表里的数据可能很多很多，比如有几百万，几千万，甚至单表几亿条数据都是 有可能的，所以此时你可能有大量的数据页，然后你的主键目录里就要存储大量的数据页和最小主键 值，这怎么行呢？
所以在考虑这个问题的时候，实际上是采取了一种把索引数据存储在数据页里的方式来做的 ， 也就是说，你的表的实际数据是存放在数据页里的，然后你表的索引其实也是存放在页里的，此时索引 放在页里之后，就会有索引页，假设你有很多很多的数据页，那么此时你就可以有很多的索引页，

但是现在又会存在一个问题了，你现在有很多索引页，但是此时你需要知道，你应该到哪个索引页里去 找你的主键数据，是索引页20？还是索引页28？这也是个大问题 于是接下来我们又可以把索引页多加一个层级出来，在更高的索引层级里，保存了每个索引页和索引页 里的最小主键值， 现在就好了，假设我们要查找id=46的，直接先到最顶层的索引页35里去找，直接通过二分查找可以定 位到下一步应该到索引页20里去找，接下来到索引页20里通过二分查找定位，也很快可以定位到数据应 该在数据页8里，再进入数据页8里，就可以找到id=46的那行数据了。 那么现在问题再次来了，假如你最顶层的那个索引页里存放的下层索引页的页号也太多了，怎么办呢？ 此时可以再次分裂，再加一层索引页

不知道大家有没有发现索引页不知不觉中组成了多个层级，搞的是不是有点像一棵树？ 没错了，这就是一颗B+树，属于数据结构里的一种树形数据结构，所以一直说MySQL的索引是用B+树 来组成的，其实就是这个意思。 我们就以最简单最基础的主键索引来举例，当你为一个表的主键建立起来索引之后，其实这个主键的索 引就是一颗B+树，然后当你要根据主键来查数据的时候，直接就是从B+树的顶层开始二分查找，一层 一层往下定位，最终一直定位到一个数据页里，在数据页内部的目录里二分查找，找到那条数据。 这就是索引最真实的物理存储结构，采用跟数据页一样的页结构来存储，一个索引就是很多页组成的一 颗B+树。

3、 更新数据的时候，自动维护的聚簇索引到底是什么？

上一次我们给大家讲了一下基于主键如何组织一个索引，然后建立索引之后，如何基于主键在索引中快 速定位到那行数据所在的数据页，再如何进入数据页快速到定位那行数据， 首先呢，现在假设我们要搜索一个主键id对应的行，此时你就应该先去顶层的索引页88里去找，通过二 分查找的方式，很容易就定位到你应该去下层哪个索引页里继续找

比如现在定位到了下层的索引页35里去继续找，此时在索引页35里也有一些索引条目的，分别都是下层 各个索引页（20，28，59）和他们里面最小的主键值，此时在索引页35的索引条目里继续二分查找， 很容易就定位到，应该再到下层的哪个索引页里去继续找，

我们这里看到，可能从索引页35接着就找到下层的索引页59里去了，此时索引页59里肯定也是有索引 条目的，这里就存放了部分数据页页号（比如数据页2和数据页8）和每个数据页里最小的主键值 此时就在这里继续二分查找，就可以定位到应该到哪个数据页里去找

接着比如进入了数据页2，里面就有一个页目录，都存放了各行数据的主键值和行的实际物理位置 此时在这里直接二分查找，就可以快速定位到你要搜索的主键值对应行的物理位置，然后直接在数据页 2里找到那条数据即可了。 这就是基于索引数据结构去查找主键的一个过程，那么大家有没有发现一件事情，其实最下层的索引 页，都是会有指针引用数据页的，所以实际上索引页之间跟数据页之间是有指针连接起来的， 另外呢，其实索引页自己内部，对于一个层级内的索引页，互相之间都是基于指针组成双向链表的，

就是说假设你把索引页和数据页综合起来看， 他们都是连接在一起的，看起来就如同一颗完整的大的B+树一样，从根索引页88开始，一直到所有的 数据页，其实组成了一颗巨大的B+树。 在这颗B+树里，最底层的一层就是数据页，数据页也就是B+树里的叶子节点了！ 所以，如果一颗大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么此时我们就可以称这颗 B+树索引为聚簇索引！ 也就是说，上图中所有的索引页+数据页组成的B+树就是聚簇索引！ 其实在InnoDB存储引擎里，你在对数据增删改的时候，就是直接把你的数据页放在聚簇索引里的，数 据就在聚簇索引里，聚簇索引就包含了数据！比如你插入数据，那么就是在数据页里插入数据。 如果你的数据页开始进行页分裂了，他此时会调整各个数据页内部的行数据，保证数据页内的主键值都 是有顺序的，下一个数据页的所有主键值大于上一个数据页的所有主键值 同时在页分裂的时候，会维护你的上层索引数据结构，在上层索引页里维护你的索引条目，不同的数据 页和最小主键值。 然后如果你的数据页越来越多，一个索引页放不下了，此时就会再拉出新的索引页，同时再搞一个上层 的索引页，上层索引页里存放的索引条目就是下层索引页页号和最下主键值。 按照这个顺序，以此类推，如果你的数据量越大，此时可能就会多出更多的索引页层级来，不过说实 话，一般索引页里可以放很多索引条目，所以通常而言，即使你是亿级的大表，基本上大表里建的索引 的层级也就三四层而已。 这个聚簇索引默认是按照主键来组织的，所以你在增删改数据的时候，一方面会更新数据页，一方面其 实会给你自动维护B+树结构的聚簇索引，给新增和更新索引页，这个聚簇索引是默认就会给你建立的。

4、 针对主键之外的字段建立的二级索引，又是如何运作的？

上一次我们已经给大家彻底讲透了聚簇索引这个东西，其实聚簇索引就是innodb存储引擎默认给我们创 建的一套基于主键的索引结构，而且我们表里的数据就是直接放在聚簇索引里的，作为叶子节点的数据 页，

而且我们现在也对基于主键的数据搜索非常清晰了，其实就是从聚簇索引的根节点开始进行二分查找， 一路找到对应的数据页里，基于页目录就直接定位到主键对应的数据就可以了，这个其实很好理解。 但是接着我们又会提另外一个疑惑了，那就是如果我们想要对其他的字段建立索引，甚至是基于多个字 段建立联合索引，此时这个索引结构又是如何的呢？

今天就给大家讲讲对主键外的其他字段建立索引的原理。

其实假设你要是针对其他字段建立索引，比如name、age之类的字段，这都是一样的原理，简单来 说，比如你插入数据的时候，一方面会把完整数据插入到聚簇索引的叶子节点的数据页里去，同时维护 好聚簇索引，另一方面会为你其他字段建立的索引，重新再建立一颗B+树。 比如你基于name字段建立了一个索引，那么此时你插入数据的时候，就会重新搞一颗B+树，B+树的叶 子节点也是数据页，但是这个数据页里仅仅放主键字段和name字段，

大家注意，这可是独立于聚簇索引之外的另外一个索引B+树了，严格来说是name字段的索引B+树，所 以在name字段的索引B+树里，叶子节点的数据页里仅仅放主键和name字段的值，至于排序规则之类 的，都是跟以前说的一样的。 也就是说，name字段的索引B+树里，叶子节点的数据页中的name值都是按大小排序的，同时下一个 数据页里的name字段值都大于上一个数据页里的name字段值，这个整体的排序规则都跟聚簇索引按 照主键的排序规则是一样的。 然后呢，name字段的索引B+树也会构建多层级的索引页，这个索引页里存放的就是下一层的页号和最 小name字段值，整体规则都是一样的，只不过存放的都是name字段的值，根据name字段值排序罢 了，

所以假设你要根据name字段来搜索数据，那搜索过程简直都一样了，不就是从name字段的索引B+树 里的根节点开始找，一层一层往下找，一直找到叶子节点的数据页里，定位到name字段值对应的主键 值。 然后呢？此时针对select from table where name=’xx’这样的语句，你先根据name字段值在name字 段的索引B+树里找，找到叶子节点也仅仅可以找到对应的主键值，而找不到这行数据完整的所有字段。 所以此时还需要进行“回表”，这个回表，就是说还需要根据主键值，再到聚簇索引里从根节点开始，一 路找到叶子节点的数据页，定位到主键对应的完整数据行，此时才能把select 要的全部字段值都拿出 来。 因为我们根据name字段的索引B+树找到主键之后，还要根据主键去聚簇索引里找，所以一般把name 字段这种普通字段的索引称之为二级索引，一级索引就是聚簇索引，这就是普通字段的索引的运行原 理。 其实我们也可以把多个字段联合起来，建立联合索引，比如name+age 此时联合索引的运行原理也是一样的，只不过是建立一颗独立的B+树，叶子节点的数据页里放了 id+name+age，然后默认按照name排序，name一样就按照age排序，不同数据页之间的name+age值 的排序也如此。 然后这个name+age的联合索引的B+树的索引页里，放的就是下层节点的页号和最小的name+age的 值，以此类推，所以当你根据name+age搜索的时候，就会走name+age联合索引的这颗B+树了，搜索 到主键，再根据主键到聚簇索引里去搜索。 以上，就是innodb存储引擎的索引的完整实现原理了，其实大家一步一步看下来，会发现索引这块知识 也没那么难，不过就是建立B+树，根据B+树一层一层二分查找罢了，然后不同的索引就是建立不同的 B+树，然后你增删改的时候，一方面在数据页里更新数据，一方面就是维护你所有的索引。 后续查询，你就要尽量根据索引来查询。

5、 插入数据时到底是如何维护好不同索引的B+树的？

之前我们已经给大家彻底分析清楚了MySQL数据库的索引结构了，大家都知道不同索引的结构是如何 的，大致是如何建立的，然后搜索的时候是如何根据不同的索引去查找数据的。 那么今天我们来给大家彻底讲清楚，你在插入数据的时候，是如何维护不同索引的B+树的。 首先呢，其实刚开始你一个表搞出来以后，其实他就一个数据页，这个数据页就是属于聚簇索引的一部 分，而且目前还是空的 此时如果你插入数据，就是直接在这个数据页里插入就可以了，也没必要给他弄什么索引页

然后呢，这个初始的数据页其实就是一个根页，每个数据页内部默认就有一个基于主键的页目录，所以 此时你根据主键来搜索都是ok没有问题的，直接在唯一 一个数据页里根据页目录找就行了。 然后你表里的数据越来越多了，此时你的数据页满了，那么就会搞一个新的数据页，然后把你根页面里 的数据都拷贝过去，同时再搞一个新的数据页，根据你的主键值的大小进行挪动，让两个新的数据页根 据主键值排序，第二个数据页的主键值都大于第一个数据页的主键值，

那么此时那个根页在哪儿呢？ 此时根页就升级为索引页了，这个根页里放的是两个数据页的页号和他们里面最小的主键值，所以此时 看起来如下图，根页就成为了索引页，引用了两个数据页。 接着你肯定会不停的在表里灌入数据，然后数据页不停的页分裂，分裂出来越来越多的数据页 此时你的唯一 一个索引页，也就是根页里存放的数据页索引条目越来越多，连你的索引页都放不下了， 那你就让一个索引页分裂成两个索引页，然后根页继续往上走一个层级引用了两个索引页

接着就是依次类推了，你的数据页越来越多，那么根页指向的索引页也会不停分裂，分裂出更多的索引 页，当你下层的索引页数量太多的时候，会导致你的根页指向的索引页太多了，此时根页继续分裂成多 个索引页，根页再次往上提上去去一个层级。 这其实就是你增删改的时候，整个聚簇索引维护的一个过程，其实其他的二级索引也是类似的一个原理 比如你name字段有一个索引，那么刚开始的时候你插入数据，一方面在聚簇索引的唯一的数据页里插 入，一方面在name字段的索引B+树唯一的数据页里插入。 然后后续数据越来越多了，你的name字段的索引B+树里唯一的数据页也会分裂，整个分裂的过程跟上 面说的是一样的，所以你插入数据的时候，本身就会自动去维护你的各个索引的B+树。 另外给大家补充一点，你的name字段的索引B+树里的索引页中，其实除了存放页号和最小name字段 值以外，每个索引页里还会存放那个最小name字段值对应的主键值 这是因为有时候会出现多个索引页指向的下层页号的最小name字段值是一样的，此时就必须根据主键 判断一下。 比如你插入了一个新的name字段值，此时他需要根据name字段的B+树索引的根页面开始，去逐层寻 找和定位自己这个新的name字段值应该插入到叶子节点的哪个数据页里去 此时万一遇到一层里不同的索引页指向不同的下层页号，但是name字段值一样，此时就得根据主键值 比较一下。 新的name字段值肯定是插入到主键值较大的那个数据页里去的。 好了，基本上讲到这里，大家应该对整个索引的数据结构，如何基于索引查询，插入的时候如何维护索 引B+树，都有了一个比较清晰地理解了

6、 一个表里是不是索引搞的越多越好？那你就大错特错了！

今天我们来稍微停一下脚步，做一个简单的关于索引知识的总结，然后再给大家分析一下索引的优点和 缺点。 首先呢，我们都知道，正常我们在一个表里灌入数据的时候，都会基于主键给我们自动建立聚簇索引， 随着我们不停的在表里插入数据，他就会不停的在数据页里插入数据，然后一个数据页放满了就会分裂 成多个数据页，这个时候就需要索引页去指向各个数据页 然后如果数据页太多了，那么索引页里里的数据页指针也就会太多了，索引页也必然会放满的，此时索 引页也会分裂成多个，再形成更上层的索引页。

默认情况下MySQL给我们建立的聚簇索引都是基于主键的值来组织索引的，聚簇索引的叶子节点都是数 据页，里面放的就是我们插入的一行一行的完整的数据了！ 在一个索引B+树中，他有一些特性，那就是数据页/索引页里面的记录都是组成一个单向链表的，而且 是按照数据大小有序排列的；然后数据页/索引页互相之间都是组成双向链表的，而且也都是按照数据 大小有序排列的，所以其实B+树索引是一个完全有序的数据结构，无论是页内还是页之间。 正是因为这个有序的B+树索引结构，才能让我们查找数据的时候，直接从根节点开始按照数据值大小一 层一层往下找，这个效率是非常高的。 然后如果是针对主键之外的字段建立索引的话，实际上本质就是为那个字段的值重新建立另外一颗B+树 索引，那个索引B+树的叶子节点，存放的都是数据页，里面放的都是你字段的值和主键值，然后每一层 索引页里存放的都是下层页的引用，包括页内的排序规则，页之间的排序规则，B+树索引的搜索规则， 都是一样的。 但是唯一要清晰记住的一点是，假设我们要根据其他字段的索引来搜索，那么只能基于其他字段的索引 B+树快速查找到那个值所对应的主键，接着再次做回表查询，基于主键在聚簇索引的B+树里，重新从 根节点开始查找那个主键值，找到主键值对应的完整数据。 以上就是我们之前给大家分析过的完整的MySQL的B+树索引原理了，包括B+树索引的数据结构，排序 规则，以及你插入的时候他形成的过程，基于B+树查询的原理，以及不同字段的索引是有独立B+树的 和回表查询过程，就给大家完整总结好了。 那么今天我们就站在这个总结的基础之上，给大家最后提一个结论，你在MySQL的表里建立一些字段对 应的索引，好处是什么？ 好处显而易见了，你可以直接根据某个字段的索引B+树来查找数据，不需要全表搜索，性能提升是很高 的。 但是坏处呢？索引当然有缺点了，主要是两个缺点，一个是空间上的，一个是时间上的。 空间上而言，你要是给很多字段创建很多的索引，那你必须会有很多棵索引B+树，每一棵B+树都要占 用很多的磁盘空间啊！所以你要是搞的索引太多了，是很耗费磁盘空间的。 其次，你要是搞了很多索引，那么你在进行增删改查的时候，每次都需要维护各个索引的数据有序性， 因为每个索引B+树都要求页内是按照值大小排序的，页之间也是有序的，下一个页的所有值必须大于上 一个页的所有值！ 所以你不停的增删改查，必然会导致各个数据页之间的值大小可能会没有顺序，比如下一个数据页里插 入了一个比较小的值，居然比上一个数据页的值要小！此时就没办法了，只能进行数据页的挪动，维护 页之间的顺序。 或者是你不停的插入数据，各个索引的数据页就要不停的分裂，不停的增加新的索引页，这个过程都是 耗费时间的。 所以你要是一个表里搞的索引太多了，很可能就会导致你的增删改的速度就比较差了，也许查询速度确 实是可以提高，但是增删改就会受到影响，因此通常来说，我们是不建议一个表里搞的索引太多的！ 那么怎么才能尽量用最少的索引满足最多的查询请求，还不至于让索引占用太多磁盘空间，影响增删改 性能呢？这就需要我们深入理解索引的使用规则了，我们的SQL语句要怎么写，才能用上索引B+树来查 询！

7、 深入理解联合索引查询原理以及全值匹配规则

之所以讲解联合索引，那是因为平时我们设计系统的时候一般都是设计联合索引，很少用单个字段做索 引，原因之前讲过，我们还是要尽可能的让索引数量少一些，避免磁盘占用太多，增删改性能太差。 另外，单个字段的索引组织结构和查询原理，之前其实我们都讲解的很清楚了，没必要在重复了。 现在我们来假设一下，咱们有一个表是存储学生成绩的，这个表当然有id了，这个id是一个自增主键， 默认就会基于他做一个聚簇索引，这个就不用多说了。 然后呢，就是包含了学生班级、学生姓名、科目名称、成绩分数四个字段，平时查询，可能比较多的就 是查找某个班的某个学生的某个科目的成绩。 所以，我们可以针对学生班级、学生姓名和科目名称建立一个联合索引。

首先按照班级字段的值来排序，如果一样则按照学生姓名字段来排序，如果一样，则按照科目名称来排 序，所以数据页内部都是按照三个字段的值来排序的，而且还组成了单向链表。 然后数据页之间也是有顺序的，第二个数据页里的三个字段的值一定都大于上一个数据页里三个字段的 值，比较方法也是按照班级名称、学生姓名、科目名称依次来比较的，数据页之间组成双向链表。 索引页里就是两条数据，分别指向两个数据页，索引存放的是每个数据页里最小的那个数据的值，大家 看到，索引页里指向两个数据页的索引项里都是存放了那个数据页里最小的值！ 索引页内部的数据页是组成单向链表有序的，如果你有多个索引页，那么索引页之间也是有序的，组成 了双向链表。

好了，那么现在假设我们想要搜索：1班+张小强+数学的成绩，此时你可能会写一个类似下面的SQL语 句，select * from student_score where class_name=’1班’ and student_name=’张小强’ and subject_name=’数学’。 此时就涉及到了一个索引使用的规则，那就是你发起的SQL语句里，where条件里的几个字段都是基于 等值来查询，都是用的等于号！而且where条件里的几个字段的名称和顺序也跟你的联合索引一模一 样！此时就是等值匹配规则，上面的SQL语句是百分百可以用联合索引来查询的。

那么查询的过程也很简单了，首先到索引页里去找，索引页里有多个数据页的最小值记录，此时直接在 索引页里基于二分查找法来找就可以了，先是根据班级名称来找1班这个值对应的数据页，直接可以定 位到他所在的数据页， 然后你就直接找到索引指向的那个数据页就可以了，在数据页内部本身也是一个单向链表，你也是直接 就做二分查找就可以了，先按1班这个值来找，你会发现几条数据都是1班，此时就可以按照张小强这个 姓名来二分查找，此时会发现多条数据都是张小强，接着就按照科目名称数学来二分查找。 很快就可以定位到下图中的一条数据，1班的张小强的数学科目，他对应的数据的id是127

然后就根据主键id=127到聚簇索引里按照一样的思路，从索引根节点开始二分查找迅速定位下个层级的 页，再不停的找，很快就可以找到id=127的那条数据，然后从里面提取所有字段，包括分数，就可以 了。 上面整个过程就是联合索引的查找过程，以及全值匹配规则，假设你的SQL语句的where条件里用的几 个字段的名称和顺序，都跟你的索引里的字段一样，同时你还是用等号在做等值匹配，那么直接就会按 照上述过程来找。 对于联合索引而言，就是依次按照各个字段来进行二分查找，先定位到第一个字段对应的值在哪个页 里，然后如果第一个字段有多条数据值都一样，就根据第二个字段来找，以此类推，一定可以定位到某 条或者某几条数据！

8、 再来看看几个最常见和最基本的索引使用规则

今天我们来讲一下最常见和最基本的几个索引使用规则，也就是说，当我们建立好一个联合索引之后， 我们的SQL语句要怎么写，才能让他的查询使用到我们建立好的索引呢？ 下面就一起来看看，还是用之前的例子来说明。 上次我们讲的是等值匹配规则，就是你where语句中的几个字段名称和联合索引的字段完全一样，而且 都是基于等号的等值匹配，那百分百会用上我们的索引，这个大家是没有问题的，即使你where语句里 写的字段的顺序和联合索引里的字段顺序不一致，也没关系，MySQL会自动优化为按联合索引的字段顺 序去找。

现在看第二个规则，就是最左侧列匹配，这个意思就是假设我们联合索引是KEY(class_name, student_name, subject_name)，那么不一定必须要在where语句里根据三个字段来查，其实只要根据 最左侧的部分字段来查，也是可以的。 比如你可以写select from student_score where class_name=’’ and student_name=’’，就查某个学 生所有科目的成绩，这都是没有问题的。 但是假设你写一个select from student_score where subject_name=’’，那就不行了，因为联合索引 的B+树里，是必须先按class_name查，再按student_name查，不能跳过前面两个字段，直接按最后一 个subject_name查的。 另外，假设你写一个select * from student_score where class_name=’’ and subject_name=’’，那么 只有class_name的值可以在索引里搜索，剩下的subject_name是没法在索引里找的，道理同上。 所以在建立索引的过程中，你必须考虑好联合索引字段的顺序，以及你平时写SQL的时候要按哪几个字 段来查。

第三个规则，是最左前缀匹配原则，即如果你要用like语法来查，比如select * from student_score where class_name like ‘1%’，查找所有1打头的班级的分数，那么也是可以用到索引的。 因为你的联合索引的B+树里，都是按照class_name排序的，所以你要是给出class_name的确定的最左 前缀就是1，然后后面的给一个模糊匹配符号，那也是可以基于索引来查找的，这是没问题的。 但是你如果写class_name like ‘%班’，在左侧用一个模糊匹配符，那他就没法用索引了，因为不知道你 最左前缀是什么，怎么去索引里找啊？

第四个规则，就是范围查找规则，这个意思就是说，我们可以用select from student_score where class_name>’1班’ and class_name<’5班’这样的语句来范围查找某几个班级的分数。 这个时候也是会用到索引的，因为我们的索引的最下层的数据页都是按顺序组成双向链表的，所以完全 可以先找到’1班’对应的数据页，再找到’5班’对应的数据页，两个数据页中间的那些数据页，就全都是在 你范围内的数据了！ 但是如果你要是写select from student_score where class_name>’1班’ and class_name<’5班’ and student_name>’’，这里只有class_name是可以基于索引来找的，student_name的范围查询是没法用 到索引的！ 这也是一条规则，就是你的where语句里如果有范围查询，那只有对联合索引里最左侧的列进行范围查 询才能用到索引！

第五个规则，就是等值匹配+范围匹配的规则，如果你要是用select * from student_score where class_name=’1班’ and student_name>’’ and subject_name<’’，那么此时你首先可以用class_name在 索引里精准定位到一波数据，接着这波数据里的student_name都是按照顺序排列的，所以 student_name>’’也会基于索引来查找，但是接下来的subject_name<’’是不能用索引的。

所以综上所述，一般我们如果写SQL语句，都是用联合索引的最左侧的多个字段来进行等值匹配+范围 搜索，或者是基于最左侧的部分字段来进行最左前缀模糊匹配，或者基于最左侧字段来进行范围搜索， 这就要写符合规则的SQL语句，才能用上我们建立好的联合索引！

9、 当我们在SQL里进行排序的时候，如何才能使用索引？

之前我们已经给大家讲解了在SQL里使用where语句进行数据过滤和筛选的时候，在where语句里要如 何写才能用上我们建立好的索引，其实无论是哪条规则，总之，尽可能就是从联合索引最左侧的字段开 始去使用，就能用上索引树！ 那么今天我们来讲一下，当我们的SQL语句里使用order by语句进行排序的时候，如何才能用上索引 呢？

通常而言，就我们自己想象一下，假设你有一个select from table where xxx=xxx order by xxx这样 的一个SQL语句，似乎应该是基于where语句通过索引快速筛选出来一波数据，接着放到内存里，或者 放在一个临时磁盘文件里，然后通过排序算法按照某个字段走一个排序，最后把排序好的数据返回。 但是这么搞通常速度有点慢，尤其是万一你要排序的数据量比较大的话，还不能用内存来排序，如果基 于磁盘文件来排序，那在MySQL里有一个术语，叫做filesort，这速度就比较慢了。 通常而言，咱们尽量是最好别这么搞，尤其是类似于select from table order by xx1,xx2,xx3 limit 100这样的SQL语句，按照多个字段进行排序然后返回排名前100条数据，类似的语句其实常常见于分页 SQL语句里，可能需要对表里的数据进行一定的排序，然后走一个limit拿出来指定部分的数据。 你要是纯粹把一坨数据放到一个临时磁盘文件里，然后直接硬上各种排序算法在磁盘文件里搞一通排 序，接着按照你指定的要求走limit语句拿到指定分页的数据，这简直会让SQL的速度慢到家了！ 所以通常而言，在这种情况下，假设我们建立了一个INDEX(xx1,xx2,xx3)这样的一个联合索引，这个时 候默认情况下在索引树里本身就是依次按照xx1,xx2,xx3三个字段的值去排序的，那么此时你再运行 select * from table order by xx1,xx2,xx3 limit 100这样的SQL语句，你觉得还需要在什么临时磁盘文 件里排序吗？

显然是不用了啊！因为他要求也不过就是按照xx1,xx2,xx3三个字段来进行排序罢了，在联合索引的索 引树里都排序好了，直接就按照索引树里的顺序，把xx1,xx2,xx3三个字段按照从小到大的值获取前面 100条就可以了。 然后拿到100条数据的主键再去聚簇索引里回表查询剩余所有的字段。 所以说，在你的SQL语句里，应该尽量最好是按照联合索引的字段顺序去进行order by排序，这样就可 以直接利用联合索引树里的数据有序性，到索引树里直接按照字段值的顺序去获取你需要的数据了。 但是这里有一些限定规则，因为联合索引里的字段值在索引树里都是从小到大依次排列的 ，所以你在 order by里要不然就是每个字段后面什么都不加，直接就是order by xx1,xx2,xx3，要不然就都加DESC 降序排列，就是order by xx1 DESC,xx2 DESC,xx3 DESC。 如果都是升序排列，直接就从索引树里最小的开始读取一定条数就可以了，要是都是降序排列，就是从 索引树里最大的数据开始读取一定的条数就可以了，但是你不能order by语句里有的字段升序有的字段 降序，那是不能用索引的。 另外，要是你order by语句里有的字段不在联合索引里，或者是你对order by语句里的字段用了复杂的 函数，这些也不能使用索引去进行排序了。 所以说，今天的内容学完，那大家对于SQL语句的order by排序如何使用索引直接提取数据就心里有数 了，其实这一讲内容是很实用的，因为我们平时写一些管理系统最常见的分页语句的时候，往往就是 select * from table order by xxx limit xxx,xx这样的写法，按照某个字段自动排序，同时提取每一页的 数据，所以如果你可以在排序用上索引，那么可以说你的性能就会很高。

10、 当我们在SQL里进行分组的时候，如何才能使用索引？

今天我们接着上次的内容来谈谈在SQL语句里假设你要是用到了group by分组语句的话是否可以用上索 引，因为大家都知道，有时候我们会想要做一个group by把数据分组接着用count sum之类的聚合函数 做一个聚合统计。 那假设你要是走一个类似select count(*) from table group by xx的SQL语句，似乎看起来必须把你所 有的数据放到一个临时磁盘文件里还有加上部分内存，去搞一个分组，按照指定字段的值分成一组一组 的，接着对每一组都执行一个聚合函数，这个性能也是极差的，因为毕竟涉及大量的磁盘交互。 因为在我们的索引树里默认都是按照指定的一些字段都排序好的，其实字段值相同的数据都是在一起 的，假设要是走索引去执行分组后再聚合，那性能一定是比临时磁盘文件去执行好多了。 所以通常而言，对于group by后的字段，最好也是按照联合索引里的最左侧的字段开始，按顺序排列开 来，这样的话，其实就可以完美的运用上索引来直接提取一组一组的数据，然后针对每一组的数据执行 聚合函数就可以了。 其实大家会发现，这个group by和order by用上索引的原理和条件都是差不多的，本质都是在group by 和order by之后的字段顺序和联合索引中的从最左侧开始的字段顺序一致，然后就可以充分利用索引树 里已经完成排序的特性，快速的根据排序好的数据执行后续操作了。 这样就不再需要针对杂乱无章的数据利用临时磁盘文件加上部分内存数据结构进行耗时耗力的现场排序 和分组，那真是速度极慢，性能极差的。 所以学到这里，实际上大家应该已经理解了一点，那就是我们平时设计表里的索引的时候，必须充分考 虑到后续你的SQL语句要怎么写，大概会根据哪些字段来进行where语句里的筛选和过滤？大概会根据 哪些字段来进行排序和分组？ 然后在考虑好之后，就可以为表设计两三个常用的索引，覆盖常见的where筛选、order by排序和 group by分组的需求，保证常见的SQL语句都可以用上索引，这样你真正系统跑起来，起码是不会有太 大的查询性能问题了。 毕竟只要你所有的查询语句都可以利用索引来执行，那么速度和性能通常都不会太慢。如果查询还是有 问题，那就要深度理解查询的执行计划和执行原理了，然后基于执行计划来进行深度SQL调优。 然后对于更新语句而言，其实最核心的就是三大问题，一个是你索引别太多，索引太多了，更新的时候 维护很多索引树肯定是不行的；一个是可能会涉及到一些锁等待和死锁的问题；一个就是可能会涉及到 MySQL连接池、写redo log文件之类的问题。

11、 回表查询对性能的损害以及覆盖索引是什么？

通过之前的学习都知道，一般我们自己建的索引不管是单列索引还是联合索引，其实一个索引就对应着 一颗独立的索引B+树，索引B+树的节点仅仅包含了索引里的几个字段的值以及主键值。 即使我们根据索引树按照条件找到了需要的数据，那也仅仅是索引里的几个字段的值和主键值，万一你 搞了一个select 还需要很多其他的字段，那还得走一个回表操作，根据主键跑到主键的聚簇索引里去 找，聚簇索引的叶子节点是数据页，找到数据页里才能把一行数据的所有字段值提取出来。 所以其实大家可以思考一下，假设你是类似select from table order by xx1,xx2,xx3的语句，可能你 就是得从联合索引的索引树里按照顺序取出来所有数据，接着对每一条数据都走一个主键的聚簇索引的 查找，其实性能也是不高的。

有的时候MySQL的执行引擎甚至可能会认为，你要是类似select from table order by xx1,xx2,xx3的 语句，相当于是得把联合索引和聚簇索引，两个索引的所有数据都扫描一遍了，那还不如就不走联合索 引了，直接全表扫描得了，这样还就扫描一个索引而已。 但是你如果要是select from table order by xx1,xx2,xx3 limit 10这样的语句，那执行引擎就知道了， 你先扫描联合索引的索引树拿到10条数据，接着对10条数据在聚簇索引里查找10次就可以了，那么就 还是会走联合索引的。

其次的话，就是给大家讲解一个覆盖索引的概念，其实覆盖索引不是一种索引，他就是一种基于索引查 询的方式罢了。

他的意思就是针对类似select xx1,xx2,xx3 from table order by xx1,xx2,xx3这样的 语句，这种情况 下，你仅仅需要联合索引里的几个字段的值，那么其实就只要扫描联合索引的索引树就可以了，不需要 回表去聚簇索引里找其他字段了。 所以这个时候，需要的字段值直接在索引树里就能提取出来，不需要回表到聚簇索引，这种查询方式就 是覆盖索引。 也正是这样，所以在写SQL语句的时候，一方面是你要注意一下也许你会用到联合索引，但是是否可能 会导致大量的回表到聚簇索引，如果需要回表到聚簇索引的次数太多了，可能就直接给你做成全表扫描 不走联合索引了； 一方面是尽可能还是在SQL里指定你仅仅需要的几个字段，不要搞一个select *把所有字段都拿出来， 甚至最好是直接走覆盖索引的方式，不要去回表到聚簇索引。 即使真的要回表到聚簇索引，那你也尽可能用limit、where之类的语句限定一下回表到聚簇索引的次 数，就从联合索引里筛选少数数据，然后再回表到聚簇索引里去，这样性能也会好一些。

12、设计索引时考虑的因素

首先，我们在针对业务需求建立好一张表的结构之后，就知道这个表有哪些字段，每个字段是什么类型 的，会包含哪些数据 接着设计好表结构之后，接下来要做的，就是要设计表的索引，这个设计索引的时候，我们要考虑第一 点，就是未来我们对表进行查询的时候，大概会如何来进行查询？ 其实很多时候很多人可能说，你要让我刚设计完表结构就知道未来会怎么查询表，那我怎么可能知道 呢，实在是想不出来！ 好，那么没关系，此时我们完全可以在表结构设计完毕之后，先别急着设计索引，因为此时你根本不知 道要怎么查询表。 接着我们就可以进入系统开发的环节，也就是说根据需求文档逐步逐步的把你的Java业务代码给写好， 在写代码的过程中，现在一般我们都是用MyBatis作为数据持久层的框架的，你肯定会写很多的 MyBatis的DAO和Mapper以及SQL吧？ 那么当你系统差不多开发完毕了，功能都跑通了，此时你就可以来考虑如何建立索引了，因为你的系统 里所有的MyBatis的SQL语句都已经写完了，你完全知道对每一张表会发起些什么样的查询语句，对 吧？ 那么这个时候，第一个索引设计原则就来了，针对你的SQL语句里的where条件、order by条件以及 group by条件去设计索引 也就是说，你的where条件里要根据哪些字段来筛选数据？order by要根据哪些字段来排序？group by 要根据哪些字段来分组聚合？ 此时你就可以设计一个或者两三个联合索引，每一个联合索引都尽量去包含上你的where、order by、 group by里的字段，接着你就要仔细审查每个SQL语句，是不是每个where、order by、group by后面 跟的字段顺序，都是某个联合索引的最左侧字段开始的部分字段？ 比如你有一个联合索引是INDEX(a,b,c)，此时你一看发现有三个SQL，包含了where a=? and b=?， order by a,b，group by a这些部分，那么此时where、order by、group by后续跟的字段都是联合索 引的最左侧开始的部分字段，这就可以了，说明你的每个SQL语句都会用上你的索引了。 所以在设计索引的时候，首先第一条，就是要按照这个原则，去保证你的每个SQL语句的where、 order by和group by都可以用上索引。

但是在设计索引的时候还得考虑其他的一些问题，首先一个就是字段基数问题，举个例子，有一个字段 他一共在10万行数据里有10万个值对吧？结果呢？这个10万值，要不然就是0，要不然就是1，那么他 的基数就是2，为什么？因为这个字段的值就俩选择，0和1。 假设你要是针对上面说的这种字段建立索引的话，那就还不如全表扫描了，因为你的索引树里就仅仅包 含0和1两种值，根本没法进行快速的二分查找，也根本就没有太大的意义了，所以这种时候，选用这种 基数很低的字段放索引里意义就不大了。 一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分 查找的优势来。 其次的话，你尽量是对那些字段的类型比较小的列来设计索引，比如说什么tinyint之类的，因为他的字 段类型比较小，说明这个字段自己本身的值占用磁盘空间小，此时你在搜索的时候性能也会比较好一 点。 不过当然了，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这 种字段建立索引，哪怕多占用一些磁盘空间，那你也得去设计这样的索引，比较关键的其实还是尽量别 把基数太低的字段包含在索引里，因为意义不是太大。 那当然了，万一要是你真的有那种varchar(255)的字段，可能里面的值太大了，你觉得都放索引树里太 占据磁盘空间了，此时你仔细考虑了一下，发现完全可以换一种策略，也就是仅仅针对这个 varchar(255)字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里 而已。 此时你建立出来的索引其实类似于KEY my_index(name(20),age,course)，就这样的一个形式，假设 name是varchar(255)类型的，但是在索引树里你对name的值仅仅提取前20个字符而已。 此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据 name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引 提取出来完整的name字段值进行比对就可以了。 但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这 个排序是没法用上索引了！group by也是同理的。所以这里大家要对前缀索引有一个了解。 好了，同学们，今天给大家重点讲了索引字段的基数和前缀索引的知识，大家就记住两点，对于那种字 段基数很低的列尽量别包含到索引里去，没多大用； 另外就是对于那种比较长的字符串类型的列，可以设计前缀索引，仅仅包含部分字符到索引树里去， where查询还是可以用的 ，但是order by和group by就用不上了。

首先假设你设计好了一个索引，非常棒，接着你在SQL里这么写：where function(a) = xx，你给你的索 引里的字段a套了一个函数，你觉得还能用上索引吗？ 明显是不行了。所以尽量不要让你的查询语句里的字段搞什么函数，或者是搞个计算。 现在设计索引的时候需要注意的点都已经讲完了，其实就是好好设计索引，让你的查询语句都能用上索 引，同时注意一下字段基数、前缀索引和索引列套函数的问题，尽量让你的查询都能用索引，别因为一 些原因用不上索引了。 接着我们来看看索引设计好之后，接着你系统跑起来，有数据插入也有查询的情况，其实查询基本都能 走索引一般问题都不会太大的，但是插入就有点讲究了，之前也跟大家说过，其实你插入数据的时候， 他肯定会更新索引树。 你插入数据肯定有主键吧，那有主键就得更新聚簇索引树，你插入一条数据肯定会包含索引里各个字段 的值吧，那你的联合索引的B+树是不是也要更新？ 对了，你不停的增删改数据，就会不停的更新你的索引树。 所以因为你插入的数据值可能根本不是按照顺序来的，很可能会导致索引树里的某个页就会自动分裂， 这个页分裂的过程就很耗费时间，因此一般让大家设计索引别太多，建议两三个联合索引就应该覆盖掉 你这个表的全部查询了。 否则索引太多必然导致你增删改数据的时候性能很差，因为要更新多个索引树。 另外很关键一点，建议大家主键一定是自增的，别用UUID之类的，因为主键自增，那么起码你的聚簇 索引不会频繁的分裂，主键值都是有序的，就会自然的新增一个页而已，但是如果你用的是UUID，那 么也会导致聚簇索引频繁的页分裂。