Mysql 大表limit查询优化原理实战

1 大表查询无条件优化&原理(入门)

优化前( 查询耗时 114.1s)

select * from link_exec_task limit 80000, 10   # 查询耗时 114.1s

优化后( 查询耗时 0.121s)

select * from link_exec_task a
INNER JOIN (select id from link_exec_task limit 80000, 10) b on a.id = b.id   #0.121s

原理(索引覆盖+非聚簇索引)：
观察以下sql 查询计划

查询优化原理：：
主键ID形成的索引是聚簇索引(B+树)，叶子节点存的是记录(数据)；而普通索引字段形成的索引是非聚簇索引，非聚簇索引的叶子节点里面存的是记录的ID。

查找第8w条记录，聚簇索引和非聚簇索引 查找对比：

聚簇索引方式：由于聚簇索引-数据页里面的数据项存的是完整的数据记录，故而一个数据页里面的数据项占用空间大，即一个数据页只能存少数的数据项，即一次磁盘IO查询出来的数据页只包含少数的数据项。因此当limit 偏移量很大时，就得通过多次的磁盘IO来查找对应的偏移量的记录，很慢！
非聚簇索引方式：由于非聚簇索引-数据页里面的数据项存的是记录的ID，故而一个数据页里面的数据项占用空间很小，即一个数据页可以存更多的数据项，即一次磁盘IO查询出来的数据页只包含很多个数据项。因此当limit 偏移量很大时，只select id的情况下，只需通过少次数的磁盘IO就能快速找到对应偏移量的记录ID，很快~。

故而上面就算我查询哪怕没有用到 status字段进行查询，Innodb执行引擎也会借助status索引字段使用的非聚簇索引，来快使找到第8w条记录的ID。

问1：为什么借助的是status索引字段，而不使用其他非聚簇索引字段？
答：我的理解是 会挑一个索引字段空间占用最小的字段，在该表的索引字段中，就status字段的空间占用最小。因为这里借助非聚簇索引的目的是找出数据页里面的id值，而不关心目录页里面的索引字段值，由于目录页存的内容是索引字段值和数据页的地址，故而索引字段越小，那么一个目录页能存放更多的目录项，这些目录页又可以指向数据页的地址，故而可以通过更少次数的IO来找到更多的数据页。

问2：子查询里面可能select id吗？我还能select 其他吗？
答：正常情况下不行，例如你select * ，那么此时你的查询耗时会和优化前查询耗时一样，因为此时你的查询不是覆盖索引查询，由于非聚簇索引里面没有你要select的内容，故而innodb直接就不借助非聚簇索引找第8w条记录的ID；而是直接从聚簇索引里面找出第8w条记录，故而速度会变得很慢。
当然，如果你select 的字段均包含在某个非聚簇索引树里面，那么此时还是会使用覆盖索引，还是走非聚簇索引，查询速度也会快。

问3：上文的业务逻辑是查询第8W条数据之后的10条记录，假设使用聚簇索引进行扫描，那么只需扫描数据页下的8w条数据项；那么innodb借助了status非聚簇索引进行查询第8w条记录之后的10个ID，是否也只需要扫描8w条数据项？
答：一般不止。因为在聚簇索引中，数据页里面的数据项(完整数据记录)的ID是升序的。但是在非聚簇索引中，索引结构是根据索引字段值进行排序，所以数据页里面的数据项的ID是无序的。所以我的理解是，innodb需要把非聚簇索引所有数据页里面所有的数据项的ID都给查询出来，然后再根据ID进行排序，最终返回ID第8w大的10条ID记录。

2 大表查询带 条件 优化&原理(进阶)

经过上文的学习，我们找到大表主要可以利用 非聚簇索引+覆盖索引 的方式进行目标记录ID的快速查找。

select * from link_exec_task a
INNER JOIN (select id from link_exec_task limit 80000, 10) b on a.id = b.id 

这一节主要讲 “select id from link_exec_task limit 80000, 10” 这部分 加条件和排序 的优化。因此下面的示例也是针对该部分的sql进行改造。

2.1 where 后面的查询字段只有一个时，要求该字段是索引字段

以下文SQL为例：
假设status字段不是索引字段：那么innodb就无法找到 包含status字段的非聚簇索引树，故而只能利用聚簇索引树进行数据过滤，而聚簇索引的一次磁盘IO只能扫描到少数量的数据项(上一节的知识)，故而该SQL查询非常耗时！
假设status字段是索引字段：那么innodb就可以找到 status字段对应的非聚簇索引树，根据该非聚簇索引快速找到符合查询条件的数据页，由于非聚簇索引的数据页里面的数据项只有记录ID(上一节的知识)，即一个数据页可以包含大量的数据项。故而只需要少量的数据页就能表示符合条件的数据项，故而通过少次数的磁盘IO就能把对应的数据页给查找出来，故而该SQL查询非常快！

select id from link_exec_task where status=3  limit 80000, 10

2.2 where 后面的查询字段有多个时，尽量让查询字段为索引字段且字段值基数大

注：字段值基数大是指 字段值重复率低，越低越好。例如ID的基数大，因为ID不能重复。基数越大，越有利于查找树的快速查找。

（1）以下文SQL为例，假设status字段是索引字段，而scene_type字段不是索引字段。status字段值基数大、小 时的查询耗时:
 经过status过滤后，此时会根据符合status条件的这些ID进行回表，然后根据ID对应的所有记录，然后再根据 scene_type = 1 进行过滤，筛选和合适的ID。如果status字段基数很大，那么回表次数会很少，此时总体查询耗时短；反之，回表次数会很多，从而造成查询耗时急剧加长。

select id from link_exec_task where status=3 and edge_id = '04001' limit 80000, 10

（2）以下文SQL为例，假设status字段是索引字段，而edge_id 字段也是索引字段。
此时innodb会进行优化分析，有可能将这两个索引进行合并，即索引合并index_merge，也有可能只使用其中一个索引。
 如果使用了索引合并index_merge：那么查询速度会很快，因为此时innodb会使用status和edge_id 这两个非聚簇索引树，分别根据查询条件进行过滤，过滤出两组ID集合后，再对ID集合取交集(因为条件是and方式)，此过程无需回表，就可以找出符合条件的ID集合。
 如果只使用其中一个索引：那么查询耗时就取决于作为索引的那个字段值基数大不大，如果基数大，那么耗时短，否则耗时就很长，具体原因见上文（1）。

select id from link_exec_task where status=3 and edge_id = '04001' limit 80000, 10

问：什么时候会使用索引合并，什么时候只会使用其中一个索引。
答：和你字段范围、字段的存储内容、条件查询内容有关。理论上，表数据量大 + limit offert偏移量大 情况下，索引合并 会比 “只使用其中一个索引” 的查询速度快很多。但有时候innodb就是会选择“只使用一个索引”，导致优化后查询速度反而变慢，这玩意儿感觉也有点捉摸不透，让人蛋疼。

---

相同查询条件，查询内容不一样，实际使用的索引可能也不一样。

---

条件里面有多个索引，只实际使用一个聚簇索引可能导致的查询急剧下降：
见下图：

---

3 大表查询带 排序 优化&原理(进阶)

假设当前表中，有id主键字段，也有creat_time索引字段，前端需要展示最新记录，那么此时就必须使用id或者create_time 进行倒序排序，具体使用哪个字段呢？选择合适排序字段能让你的查询速度天差地别。

3.1 没有where 条件，那么推荐使用 order by create_time desc。

结合下图的explain结果：
见下图无where条件时，使用id和create_time进行倒序排序，时间相差甚大，这是为什么？
如果没有where条件且使用id进行倒序排序时：由于id没有专门的非聚簇索引树，所以innodb使用的是聚簇索引查找，从聚簇索引里面的最后一个数据页的数据项从后晚前找，直到找到第8w条数据项之后的10条记录id。上文已经说过，聚簇索引的数据页里面的数据项是完整的数据记录，即一个数据项占用空间很大，一个数据页只能包含少量的数据项，故而需要查找大量的数据页，即通过大量的磁盘IO 才能把这8w条数据项所在数据页给扫描出来，速度很慢。

如果没有where条件且使用ceate_time进行倒序排序时：由于create_time有专门的非聚簇索引树，此时innodb使用的是非聚簇索引查找，从ceate_time非聚簇索引里面的最后一个数据页的数据项从后晚前找，直到找到第8w条数据项之后的10条记录id。上文已经说过，非聚簇索引的数据页里面的数据项是记录ID，即一个数据项占用空间很小，一个数据页可以包含大量的数据项，故而只需查找少量的数据页，即通过少量的磁盘IO 就能把这8w条数据项所在数据页给扫描出来，速度很慢。

---

以下SQL查询耗时：27.166s

select id from link_exec_task ORDER BY id desc limit 100000, 10 # 查询耗时：27.166s

---

以下SQL查询耗时：0.143s

select id from link_exec_task ORDER BY id desc limit 100000, 10 # 查询耗时：0.143s

3.2 where 条件查询字段中，如果存在索引字段 ，且当前查询不需要回表，那么推荐使用 order by id desc

结合下图的explain结果：

看explain分析结果，再结合上文那些说明，其实你也应该懂了。
答：where 条件查询字段中，存在status索引查询字段 ，根据条件status=0 innodb使用status非聚簇索引树过滤掉数据后，拿到符合条件的记录ID集合，此时分以下两种情况：
假设当前是根据id进行排序排序： 将过滤后ID集合进行快速倒序排序，然后找出第10w条记录后面的10条记录ID，不需要回表，故而速度很快。
假设当前是根据create_time进行排序排序： 由于当前是create_time字段进行倒序排序，而且上一步status=0过滤后只有记录的ID集合，innodb执行引擎没办法对这些ID集合进行create_time倒序排序，故而只能拿着过滤后的ID集合，到聚簇索引树进行查找记录，即回表，根据ID对应的create_time值后，再根据ID所对应的Create_time值，对ID集合进行排序。由于我这张表中，status字段基数值不大，也就是status=0过滤后的ID集合还是很大，就会导致后面出现频繁回表，从而造成查询速度急剧下降。

---

以下SQL执行耗时：29.082s

select id from link_exec_task where status = 0 ORDER BY create_time desc limit 100000, 10

---

以下SQL执行耗时：0.057s

select id from link_exec_task where status = 0 ORDER BY id desc limit 100000, 10

---

3.查询字段加了索引，一定会用上吗（最左匹配原则-模糊搜索案例~ 会颠覆你的三观）

答案是不一定，innodb会根据 你当前 是否有使用limit分页、分页的偏移量大小、查询字段存储值基数大小、条件查询内容、排序字段 等 来决定当前使不使用 该查询字段索引。‘’

下面以 分页的偏移量大小、以及是否有使用limit分页 影响innodb查询方式进行演示：

 下这里我的name字段配置了普通索引，理论来说，使用最左匹配原则的模糊搜索就能使用到索引，但事实真的是这样吗？见下图。
 下面两个图中，我对name使用最左匹配原则的模糊搜索，但实际上却没有使用到name索引，也就是没有走非聚簇索引。而是走了主键索引，从而导致我的查询速度极速下降。 这innodb的优化还不如不优化！！！！

把limit偏移量调小：

查询耗时:22.153s

select id from link_exec_task where name like '定时%' order by id desc limit 0, 10 # 查询耗时 22.153s

---

把limit偏移量调大：

我把上面的sql limit偏移量调到5000，结果查询速度反而快了几百倍，见下图：
查询耗时:0.095s

select id from link_exec_task where name like '定时%' order by id desc limit 5000, 10 # 查询耗时 0.095s

---

上面说了，使用limit 偏移量小慢，偏移量大反而块。如果我直接把分页limit 给去掉，是怎么样子的呢？
直接把limit偏移量去掉:

查询耗时:0.075s

select id from link_exec_task where name like '定时%' order by id desc # 查询耗时 0.075s

总结：至此，我们明白了有时候我们使用 索引字段进行搜索，但实际不一定会使用到该索引，影响使用哪种索引的因素很多，具体使用哪种索引还是由innodb决定。要注意的是，innodb优化 有时候不一定会加快查询，反而导致查询速度变慢几十、甚至几百倍。例如像上文的 最左匹配原则的模糊搜索 理论上走普通字段的非聚簇索引更快，但是它有时候却使用了主键索引；有例如有时候 查询使用到了多索引字段查询，但实际没有使用索引合并，而是只使用了其中一个索引，造成查询速度变慢。