MySQL性能优化---limit讲解和优化

mysql版本：5.6
操作系统：windows10

表结构：
id为主键，a为非唯一索引，索引名index_a

数据量：

查询1：

查询2：

对比上面的两个sql语句，查询目的一样，但是效率相差较大，现在就来从limit原理分析两条sql的差别。

explain查看sql1的执行计划：

通过type为all以及key为null可以看出虽然sql中有where a=1在索引上的条件查询，但是由于回表消耗的性能大于直接在主键索引上全表扫描，因此MySQL优化器放弃了index_a索引而走了全表扫描。

explain查看sql2的执行计划：

• id：表示执行的顺序，id越大越先执行，id一样的从上往下执行
• select_type：查询类型
  primary：表示最外层查询
  derived：派生表查询，即from字句中的子查询
• table：表名，< derived2 >表示用了临时表
• type
  all：全表扫描
  eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况
  index：遍历索引树查询，，通常发生在查询结果只包含索引字段时
• possible_keys：预计可用的索引
• key：实际用到的索引
• key_len：索引的长度
• ref：表示连接查询的连接条件
• rows：估计此次查询所需要读取的行数
• filtered：按表条件过滤的百分比
• extra：执行情况的说明
  using index：使用了覆盖索引

SQL语句的执行顺序：

(8) SELECT (9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

说明了各个字段及其值的含义、SQL语句的执行顺序之后，再来分析sql2的执行过程。

子查询select id from t as c limit 300000, 5使用索引index_a，并且使用覆盖索引。临时表b的结果实际只有5行。根据 join 《MySQL性能优化–JOIN原理和使用优化》原理，取临时表b中的每一条记录的id在表t上做连接程序，连接查询使用了主键索引。

slq2的执行过程使用到了索引index_a，index_a索引树的叶子节点只存放了a和id字段，因此要比主键索引小很多，这样能减少磁盘数据的IO调用。在索引index_a中取id值，组成临时表b，只有5行记录的表b和表t做连接查询，取表b的每一行记录的id到表t中查找，使用了主键索引，因此效率很高。

从SQL语句执行顺序可以看出，limit是最后一个执行，所以limit 300000,5并不是直接从第300000后的下一行开始取5条数据，因为MySQL也不知道第300000在哪里，因此还是需要从第一个数据开始依次向下取数据，直到取了300005条记录。

也就是说，sql2的执行并没有对主键索引进行全部扫描，而是对辅助索引index_a进行了扫描，取了300005行数据，然后取后5行组成临时表b，然后回到表t中取这5条数据的索引字段。

上面涉及到了主键索引和辅助索引页的调度问题，可以通过select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('index_a','primary') and TABLE_NAME like '%t%' group by index_name;查看指定索引在buffer pool中的数据页的数量。
重启MySQL，初始状态主键索引有11页，执行select * from t where a=1 limit 30000,5;之后，主键索引页明显增大，辅助索引页只有2页。

再次重启MySQL重置buffer pool中的数据页，可以看到这次主要调用的是辅助索引页，主键索引页的调用明显没有sql1语句执行后多。这也验证了上面的结论：sql1会使用全表扫描，这就会把所有的主键索引数据页调用一次，而主键索引的叶子几点包含了数据，因此这个索引比较大。而sql2先调用辅助索引取id，辅助索引值包含a和id，因此比较小。由id组成的5条记录的临时表和表t做连接查询，使用表t的主键索引做查询。