MySQL调优之索引常见概念（最左前缀原则、索引下推等）

MySQL调优系列

MySQL调优之性能监控
MySQL调优系列之执行过程
MySQL调优之索引的数据结构
MySQL调优之索引常见概念（最左前缀原则、索引下推等）

回表、覆盖索引、最左前缀原则、索引下推

面试官：小伙子，回表是什么了解么？

你略加思索后，答道：了解的。假设我们的 staff 表有一个主键索引和 name 字段的索引。当我们执行下面这条 SQL 时

select * from staff where name = 'quintin'; 

会先查询到 name 索引上的 quintin ，然后得到这条记录的 id 为 3，然后去主键索引找到 id 为 3 的这条记录。因为主键索引的 B+ 树的叶子节点存的是整条记录的信息。
那么回到 主键索引 的这颗 B+ 树搜索的过程就是回表。但是我们可以通过覆盖索引来避免回表。

面试官：哦~那你说说什么是覆盖索引？

你略加思索后，答道：刚才我们说先查询到 name 索引上的 quintin ，然后得到这条记录的 id 为 3，然后去主键索引找到 id 为 3 的这条记录。但是如果我们执行下面这条SQL

select id from staff where name = 'quintin'; 

因为 id 在 name 索引上已经有了，就不需要回表了。这个过程就叫覆盖索引。

面试官：好，那我考考你。我现在有一张 staff 表，经常要按照 name 和 age 进行查询，大概就是下面有 4 条 SQL，你说一下要怎么建索引？

1:select * from staff where name = ? and age = ?;
2:select name from staff where age = ?;
3:select * from staff where name = ?; 
4:select * from staff where age = ? and name = ?;

你略加思索后，答道：可以建一个 (name，age) 的联合索引，再给 age 建一个索引。

面试官：哦～说说你的理由，为什么要这么建索引？

你略加思索后，答道：首先给 name，age 建联合索引可以提高第 1、4 条SQL的执行效率，同时根据最左前缀原则，第三条SQL也会走索引，提高查询效率。但是第二条SQL就不行了，不满足最左前缀原则，所以我再单独建一个 age 索引。

面试官：打断一下，你能和我解释下什么是最左前缀原则么？

你略加思索后，答道：可以的。简单来说就是从左向右依次匹配索引，左边匹配到了，才会继续匹配右边。但是，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、%like)等就不能进一步匹配了。因此，列的排列顺序决定了可命中索引的列数。我们这里第 2 条SQL执行时，会先匹配 age 索引，因为我们建的是 name,age 索引，第一个就匹配不上，所以不走 name,age 这个联合索引。

面试官：你说的有道理，那为什么第 4 条SQL也会走联合索引呢，按照你的说法，这不是不符合最左前缀了么。第 4 条 SQL 查询的顺序是 age、name，而你的索引建得是 name、age。

你略加思索后，答道：因为MySQL的优化器会自动优化 where 后 age 和 name 的顺序，所以这里 1、4 执行的时候，效果是一样的。

面试官：你说的有道理，那你为什么不建一个 age，name 索引，再单独加上一个 name 索引。这样也可以达到一样效果啊。

你略加思索后，答道：因为 age 索引占用的磁盘空间更小。

面试官：很好，最后问你以下，你知道什么是索引下推么？

你略加思索后，答道：了解过。比如这条SQL

select * from staff where name = ? and age = ?;

在判断 name 的时候，会直接把不符合条件的 age 一起过滤掉。这样就避免了，先查询一次name 再根据结果集查询符合条件的 age 。

参考资料

• MySQL官网

  • How MySQL Uses Indexes

• 敖丙 - 我以为自己对索引很了解，直到我遇到了阿里面试官