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

我会带着你远行 2023-06-19 02:16 5阅读 0赞

> mysql版本：5.6  
> 操作系统：windows10

表结构：  
id为主键，a为非唯一索引，索引名index\_a  
![在这里插入图片描述][20191203195405245.png]  
数据量：  
![在这里插入图片描述][20191203200423732.png]  
查询1：  
![在这里插入图片描述][20191203200529526.png]  
查询2：  
![在这里插入图片描述][20191203200701330.png]  
对比上面的两个sql语句，查询目的一样，但是效率相差较大，现在就来从limit原理分析两条sql的差别。

explain查看sql1的执行计划：  
![在这里插入图片描述][20191203201422246.png]  
通过type为all以及key为null可以看出虽然sql中有where a=1在索引上的条件查询，但是由于回表消耗的性能大于直接在主键索引上全表扫描，因此MySQL优化器放弃了index\_a索引而走了全表扫描。

explain查看sql2的执行计划：  
![在这里插入图片描述][20191203201736770.png]

*  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原理和使用优化》][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页。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70]  
再次重启MySQL重置buffer pool中的数据页，可以看到这次主要调用的是辅助索引页，主键索引页的调用明显没有sql1语句执行后多。这也验证了上面的结论：sql1会使用全表扫描，这就会把所有的主键索引数据页调用一次，而主键索引的叶子几点包含了数据，因此这个索引比较大。而sql2先调用辅助索引取id，辅助索引值包含a和id，因此比较小。由id组成的5条记录的临时表和表t做连接查询，使用表t的主键索引做查询。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70 1]

[20191203195405245.png]: https://img-blog.csdnimg.cn/20191203195405245.png
[20191203200423732.png]: https://img-blog.csdnimg.cn/20191203200423732.png
[20191203200529526.png]: https://img-blog.csdnimg.cn/20191203200529526.png
[20191203200701330.png]: https://img-blog.csdnimg.cn/20191203200701330.png
[20191203201422246.png]: https://img-blog.csdnimg.cn/20191203201422246.png
[20191203201736770.png]: https://img-blog.csdnimg.cn/20191203201736770.png
[MySQL_JOIN]: https://blog.csdn.net/IT_10/article/details/95916694
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191203205502498.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191203205728161.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw,size_16,color_FFFFFF,t_70