再有人问你MySQL是如何查询数据的，请把这篇文章甩给他！-技术圈

△Hollis, 一个对Coding有着独特追求的人△

这是Hollis的第 339 篇原创分享

作者 l zyz1992

来源 l Hollis（ID：hollischuang）

上一篇我们说到了关于MySQL的索引的原理，主要说的是 MySQL 对于索引的字段是怎么去维护的，我们再来简单的回顾下：

MySQL 对于主键索引的维护是最简单的，就是根据主键去维护一个 B+ 树，因为主键的特点一般是递增的，也就是说是有序的，所以 MySQL 在维护的时候只需要将记录依次往数据页中追加即可，数据页满了就继续添加到下一个数据页。且每一条记录是完整的，即所有的列的值都维护。

但是对于非主键索引，在维护 B+ 树的时候，会根据联合索引的字段依次去判断。

假设联合索引为：name + address + age，那么 MySQL 在维护该索引的 B+ 树的时候，首先会根据 name 进行排序，name 相同的话会根据第二个 address 排序，如果 address 也一样，那么就会根据 age 去排序，如果 age 也一样，那么就会根据主键字段值去排序（主键不可能是一样的），且对于非主键索引，MySQL 在维护 B+ 树的时候，仅仅是维护索引字段和主键字段。

另外 B+ 树的结构大致是这样子的：

这里数据的维护过程就不再详细赘述了，不清楚的朋友可以看上一篇文章

今天，我们就来一起看看对于 MySQL 的查询有哪些基本的原则。

因为只要在了解原则的基础之上，才能够写出符合预期的 SQL，才能知道自己的 SQL 到底有没有使用到索引。这是一个最最基本的原则。

本文因为讲的是一些原则，所以很多东西不是很好画图，但是能画我一定给大家画。

等值匹配原则

我们现在已经知道了如果是【主键索引】，在插入数据的时候是根据主键的顺序依次往后排列的，一个数据页不够就会分裂到另外一个数据页，然后再通过索引页来维护数据页。

数据页之间是通过双向链表来维护的，索引页如果过多就会往上分裂（就像上面这张图），以此类推，这样就形成了由组件组成的 B+ 树结构，即【聚簇索引】

但是问题是我们不仅建立了主键索引，同时也建立了非主键索引，那这时候非主键索引是如何维护的呢？

因为对于主键索引是不可能重复的，所以在保存到数据页的时候是直接追加插入的（我们默认主键是自增的）

至于非主键一般是可以重复的，假设此时某个联合索引字段的值真的都是一样的，那该怎么办？

那就像上面开头说的，此时只能按照主键字段来排序，这就是为什么非主键索记录在保存的时候还保存一个主键字段的作用。

另外刚刚上面也说了，索引如果建立太多会占用太多的空间，因为MySQL 会为每个索引维护一颗 B+ 树，毕竟非主键的字段一方面不一定是递增的，另一方面可能是重复的。所以就基于这点，那些频繁增删的字段一定不适合来做索引。

好了，我们还是要回到刚刚说的 name+age 的联合索引假设我们现在有一条这样的 SQL

SELECT * FROM student WHERE name='wx' AND age=1

像这种 WHERE 后面的条件是联合索引的并且是联合索引中的字段的顺序排列的，且全是使用等于号条件，我们称这种为：等值匹配；这个是非常重要的一个原则。

最左前缀匹配原则

假设现在有这样的几条记录：

classId=1,name=wx,age=1,id=1; 
classId=1,name=xq,age=2,id=2; 
classId=1,name=wx,age=1,id=3; 
classId=2,name=zs,age=3,id=4;

根据上面说的（classId,name,age）联合索引他们是这样子保存在数据页中的。

首先根据 classId 字段值排序。

如果 classId 字段值一样，那么就根据第二个 name 字段值排序。

如果name 字段值也一样，那么就根据 age 字段值一样，如果 age 字段值也一样，那么就根据主键字段值排序。

然后在查找的时候，因为你现在条件是 calssId 和 name，所以 MySQL 是能够通过 classId 很快定位到一批数据的。

因为这个条件就是 MySQL 维护 B+ 树的第一条件（即先根据 classId 排序），然后同理，name 是MySQL 维护B+树的第二个条件（即根据 name 排序），所以此时哪怕你 age 条件不添加，使用到索引classId 和 name 的索引一定是没问题的，但是如果你这么查询

SELECT * FROM student WHERE  age=1

这样子就不行了，因为 MySQL 会根据你建立的联合索引。

首先是根据 classId 查询，然后是根据 name，然后再根据 age。

如果你直接跳过前面的两个字段，那么这样子跟全表扫描是没有区别的，因为MySQL 此时根本就无法确认 age 在哪里，只能一个一个去扫描了。

同理，如果你WHERE 条件后面是 classId=xx，然后是 age=xx 此时这种情况下 classId 是可以使用到索引的，因为 B+ 树维护的第一个字段就是 classId。

但是 age 却无法使用到索引查询了，因为 name 是无法定位的，所以此时只能是根据满足 classId 的记录再做一次全扫描。这规则叫：最左前缀匹配原则；

如果你想不明白最左匹配原则，那我来做个类比再来介绍下，我们假设classId,name,age，这三个组成的联合索引就好比是三层楼，classId是第一层,name是第二层，age是第三层。

假设你想要到第三层，是不是必须要要从第一层开始爬，然后是第二层，然后是第三层；你可以就爬到第一层，剩下两层不爬也没关系，这就对应你可以就使用 classId来做等值查询，剩下的字段不使用都没关系；

同理，你可以从第一层爬，然后再爬到第二层，不爬第三层，这就好比是你使用 classId,name去查询一样，亦或者你依次从第一层爬到第二层再爬到第三层都是可以的，也就是你使用classId,name,age这三个字段依次去做等值查询。到此这一切都是 OK 的。

但是如果你不想爬第一层，你想跳过第一层，直接从第二层开始爬，可能吗？

显然是不可能的，这也就是说查询的时候跳过 classId 直接查询name，这样子就根本无法使用到索引。调过 name 查询age 也是同理，直接跳过一二层直接从第三层开始，也就是说调过classId 和name直接查询age也是无法使用到索引的.

这下你应该彻底明白最左匹配的原则了吧？以下的原则最基础的条件就是需要满足：最左前缀匹配原则。

范围查找规则

范围查找规则，相信这个也是大家最经常使用的原则了，例如像下面的SQL

SELECT * FROM student WHERE classId &gt; 1 AND classId &lt; 4

因为此时由联合索引（classId,name,age）构建出来的 B+ 树中的数据是根据 classId,name,age 去排序的。

所以此时是能够根据 classId 查询到一个范围中的数据的，虽然他们可能不在同一个数据页中，但是我们说过了，数据页之间是通过双向链表进行连接的。所以此时针对 classId 的范围查找依旧是能走索引的。继续看如果条件是这样子的

SELECT * FROM student WHERE classId &gt; 1 AND classId &lt; 4 and name &gt; a AND name &lt; x

你是不是觉得前面的 classId 是符合范围查找的，然后在查询出来的结果中继续范围查找 name。

但实际上并不是这样子的，因为我们说了联合索引（classId,name,age）是按照 calssId、name、age 依次去排序的，因为此时 classId 的顺序确定以后，是不需要根据 name 排序的，也就是说在 classId 的范围内 name 是无序的，听不明白？没关系，看我画图

现在我们依次插入的数据是上面的四条，因为在插入的时候是可以直接根据 classId 就能够确定下这四条记录的顺序了。

所以此时是根本不会去管后面的 name 或者是 age 是什么顺序的，或许你可能觉得如果他们的 class Id 一样呢？

好，我就来一点一点排除你心中的疑惑，看下面这张图

我们假设第三条记录的 calssId 和第二条记录的 classId 字段值是一样的，那这个时候才会去根据 name 判断。

结果发现 aa 是小于 zz 的，这样就会把 name 值更小的排在前面，但是为什么我上面还会说按照 calssId 排序好了以后 name 是无序的呢？

因为我们说的第二种情况（classId 相同）是属于特殊情况，我们不能使用特殊的情况来下一般性的结论，age 同理。

所以记住了：针对于范围查找只要联合索引的最左侧列有效，其他的都无法使用到索引（既然无法使用到索引，那么只能是走全表扫描）

等值匹配+范围查找

假设我们有这样的一条 SQL

SELECT * FROM student WHERE classId = 1 AND name &gt; a AND name &lt; x

首先 calssId 是走索引的，其次 name 也是走索引的。

为什么？你怎么前后说的有矛盾？刚刚才说了范围的之后第一个列才能走索引，现在却说 name 也走索引，name 明明是第二列。

听我慢慢道来，首先范围查找只有第一个列走索引单纯针对的范围查找，具体原因我已经详细的解释了，但是现在如果使用联合索引中的第一个条件去做等值匹配，第二个去使用范围查询走索引是没问题的，看下面的图

现在我们首先定位的是 classId=1 这些记录，这些一定是确定的，但是在 MySQL维护 B+ 树的时候，是没法根据 classId=1 的记录来直接进行排序的。

因为此时的 classId 都是1，换句话说，三条记录的 classId 都是1，MySQL 根本无法确定谁在前面，谁在后面。

所以此时就需要根据 name 去继续判断，结果也就是上面图的样子。

根据 name 发现是能够确定记录顺序的，所以在 classId 等于 1 的记录中的所有的 name 都是有序的。

这就是为什么等值后面可以范围的原因（但是一条贯穿始终的原则是：必须是满足最左匹配原则，也就是前面的记录必须是确定的，这样子才能继续对后面的数据判断）。此时此刻你是不是想大声的喊一句

到此为止，你现在是否能够根据建立的索引来判断你的 SQL 是否使用到了索引，使用到了哪些索引了呢。

是不是想赶紧的写几个SQL 试试？

别急，一定要看完总结部分。

Order By + limit 优化

上面说道的一些都是最最基本的查询的一些原则，但是想要实际运用，这里是必须要学习的，因为我们平时写sql的时候绝对离不开分页。

而分页基本是也排序组合使用的，所以我们也将这个放在一起在说。

假设现在对name，age，adderss 这三个字段创建联合索引，且在查询的时候 SQL 语句是这样子的:

SELECT name,age,address FROM student ORDER BY  name,age,address LIMIT 10

你如果这么写那 MySQL 就能明白了啊，你是想根据 name,age,address联合索引进行排序，然后在取前10条记录，且取的记录的字段在维护联合索引的 B+ 树中都是有的，那么此时就需要再去进行回表到聚簇索引中查询了。

另外 ORDER BY后面的字段的方式一定要一致，也就是说要么全是升序，要么全是降序，不能有的升序有的降序。

说白了就是一般对什么字段排序就对哪些字段建立索引，但是升序降序不要混用。

其实对于 MySQL 的优化看到这里相信大家或多或少也发现了，优化真的没有所以的规律和套路，因为最好的优化是结合实际的业务区做调整。没有一蹴而就的方式和一劳永逸的方法。

分组查询优化

其实分组查询优化和上面的Order By + limit 优化差不多，基本是一个道理，例如有这样的 SQL

SELECT count(*) FROM student GROUP BY NAME

如果不对 NAME 建立索引，那么就是将所有的数据查询出来，放在一个临时文件中，然后按照分组的字段将数据一组一组的分好。

然后再去执行聚合操作（这里就是count(*)操作），这样子很显然效率是很低的，所以我们肯定是需要对 NAME 去建立索引的。

这是不是不明白为什么需要会有临时文件？

根据 group by 的语义逻辑，是按照name去做统计，因为此时name并没有索引，所以按照name去分组首先需要得到一个根据name排序的数据啊，所以我们就需要有一个临时表，来记录并统计结果。

也就是说我们需要的不就是一个排好序的结果吗？那直接对name建立索引就可以了。

假设我们是根据name建立好了索引，因为此时name已经是被排好序的了，这个时候就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序。

也就是说，如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果。

结束语

索引查询基本原则总结

我们一般写 SQL 基本都是【等值 + 范围】的，这个是最常见的条件搜索，像这样子的情况一定要建立好索引，建立索引的根本依据就是要明白【MySQL 是如何帮我们维护非主键索引的】。

MySQL 是如何帮我们维护非主键索引的

其实在开头我已经强调过了，但是为了让大家在巩固下，我这里在来说一遍。

对于主键索引，MySQL 就是根据主键字段进行排序（一般主键字段我们都设置为自增的，否则真的是在给自己找麻烦，假设主键不是自增的，这还会导致页分裂的发生，这样就很降低性能了）；

而对于非主键索引（我们一般指联合索引）MySQL 同样会为我们维护一个B+ 树，只不过这颗B+的叶子结点（即数据页）上面的保存的数据仅仅是索引字段数据和主键数据。

假设有联合索引 name、address、age，这样在插入数据的时候，MySQL 首先会根据name进行排序，name一样就根据address 排序，address 字段值一样再根据 age 字段值排序。

age 字段值还一样，就根据主键字段排序。

这也是为什么会维护主键字段的原因。

另外为什么对于非主键字段只维护索引列？因为聚簇索引（通过维护主键字段的B+树）中已经有全部记录的值，如果其他的索引再维护所有的字段，这样就是在浪费空间。

点个 在看 
喜欢是一种感觉
在看是一种支持
↘↘↘