最近无意间看到一个MySQL分页优化的测试案例，并没有很是详细地说明测试场景的环境下，给出了一种经典的方案，
因为现实中许多环境都不是牢靠稳定的，能总结出来通用性的做法可能说是纪律，是要思量很是多的场景的，
同时，面临可以或许到达优化的方法要追究其原因，同样的做法，换了个场景，达不到优化结果的，还要追究其原因。
小我私家对此场景在不消环境暗示猜疑，然后本身测试了一把，公然发明一些问题，同时也证实了一些预期的想法。
本文就MySQL分页优化，从最最简朴的环境出发，来做一个简朴的阐明。

另：本文测试情况是最最低设置的云处事器，相对来说处事器硬件情况有限，不外对付差异的语句（写法）应该是“平等的”

20170916增补:

想想用脚趾头就能大白，

1，假如分页排序字段是聚积索引，完全没须要对索引分页再查询数据，因为索引就是数据自己。
2，假如长短聚积索引，劳务派遣管理系统，先对索引分页，然后再操作索引去查询数据，先分页索引确实可以淘汰扫描的范畴
3，假如常常凭据2中的方法查询，也就是凭据非聚积索引排序查询，那么为什么不在该列上成立聚积索引呢。

MySQL经典的分页“优化”做法

MySQL分页优化中，有一种经典的问题，在查询越“靠后”的数据越慢（取决于表上的索引范例，对付B树布局的索引，SQL Server中也一样）
select * from t order by id limit m,n。
也即跟着M的增大，查询同样多的数据，会越来越慢
面临这一问题，于是就发生了一种经典的做法，雷同于（可能变种）如下的写法
就是先把分页范畴内的id单独找出来，然后再跟基表做关联，最后查询出来所需要的数据
select * from t
inner join (select id from t order by id limit m,n)t1 on t1.id = t.id

这种做法是不是老是生效的，可能说是在什么环境下后者才气到到达优化的目标？有没有做了改写之后无效甚至变慢的环境？ 

与此同时，绝大大都查询都是有筛选条件的，
假如有筛选条件的环境，
sql语句就酿成了select * from t where *** order by id limit m,n
假如如法炮制，改写成雷同
select * from t
inner join (select id from t where *** order by id limit m,n )t1 on t1.id = t.id
在这种环境下，改写后的sql语句还能到达优化的目标吗？

测试情况搭建

　　测试数据较量简朴，通过存储进程轮回写入测试数据，测试表的InnoDB引擎表。

苏州软件公司 是为什么" src="http://www.importnew.com/https:/images2015.cnblogs.com/blog/380271/201706/380271-20170613183605384-2001475149.png" />

苏州软件公司 是为什么" src="http://www.importnew.com/https:/images2015.cnblogs.com/blog/380271/201706/380271-20170613222248134-36346542.png" />

这里要留意的是日志写入模式必然要修改成innodb_flush_log_at_trx_commit = 2，不然默认环境下，500w数据，预计一天都写不完，这个与日志写入模式有关，就不多说了，

苏州软件公司 是为什么" src="http://www.importnew.com/https:/images2015.cnblogs.com/blog/380271/201706/380271-20170613222909228-921166556.png" /> 

分页查询优化的缘由

首先照旧先看一下这个经典的问题，分页的时候，越“靠后”查询相应越慢的环境

测试一：查询第1-20行的数据，0.01秒

　　苏州软件公司 是为什么" style="font-weight: normal;" src="http://www.importnew.com/https:/images2015.cnblogs.com/blog/380271/201706/380271-20170613221045040-99664001.png" width="840" height="364" />

同样是查询20行数据，查询相对“靠后”的数据，好比这里的从4900001-4900020行数据的环境，用时1.97秒。

苏州软件公司 是为什么" src="http://www.importnew.com/https:/images2015.cnblogs.com/blog/380271/201706/380271-20170613221321603-1269200276.png" width="844" height="357" />

从中可以看到，查询条件稳定的环境下，越往后查询，查询效率越低，可以简朴领略成：同样搜索20行数据，越是靠后的数据，查询价钱越大。
至于为什么后一种效率较低，后头会逐步阐明。

　　测试情况是centos 7 ，mysql 5.7，昆山软件开发，测试表的数据是500W

　　苏州软件公司 是为什么" src="http://www.importnew.com/https:/images2015.cnblogs.com/blog/380271/201706/380271-20170613181302025-1613751570.png" width="329" height="220" />

重现经典分页“优化”，当没有筛选条件，排序列为聚积索引的时候，并不会有所改进

这里来比拟以下两种写法在聚积索引列作为排序条件时候的机能

select * from t order by id limit m,n。
select * from t
inner join (select id from t order by id limit m,n)t1 on t1.id = t.id

第一种写法：

select * from test_table1 order by id asc limit 4900000,20;测试功效见截图，执行时间为8.31秒

苏州软件公司 是为什么" src="http://www.importnew.com/https:/images2015.cnblogs.com/blog/380271/201706/380271-20170613182657275-1210706524.png" width="833" height="356" />

第二种改写后的写法：

select t1.* from test_table1 t1
inner join (select id from test_table1 order by id limit 4900000,20)t2 on t1.id = t2.id;执行时间为8.43秒

苏州软件公司 是为什么" src="http://www.importnew.com/https:/images2015.cnblogs.com/blog/380271/201706/380271-20170613182818384-1326262068.png" width="829" height="365" />