最近无意中看到一个MySQL分页优化的测试用例。没有特别指定测试场景，我给出了一个经典的解决方案，因为现实中很多情况是不固定的。总结一般的做法或规律，需要考虑很多场景。同时，面对能够实现优化的方法，还要追究其原因。同理，如果换了场景，也达不到优化的效果。还必须调查原因。我个人对这个场景的使用表示过怀疑，然后自己测试了一下，发现了一些问题，也证实了一些预想中的想法。本文将从最简单的情况入手，对MySQL分页优化做一个简单的分析。另：本文的测试环境是最好配置的云服务器。相对而言，服务器硬件环境有限，但对于不同的句子（文体）应该是“平等的”。分页排序字段是聚簇索引，不需要分页索引再查询数据，因为索引就是数据本身。如果是非聚集索引，先对索引进行分页，然后使用索引查询数据。先对索引进行分页确实可以缩小扫描范围。如果经常按照2中的方式查询，即按照非聚集索引排序查询，为什么没有在这一列呢？在其上创建聚集索引。MySQL经典的分页“优化”方法在MySQL的分页优化中，有一个经典的问题，查询的数据越慢越慢（取决于表上的索引类型，对于B树索引，SQLServer也same)select*fromtorderbyidlimitm,n表示随着M的增加，查询同样的数据量会越来越慢来面对这个问题，于是产生了一个经典的做法，类似（或变体）如下的写法是分别找出pagerange中的id，然后关联到基表，最后查询需要的数据或者后者在什么情况下才能达到优化的目的？有没有重写后无效甚至变慢的情况？同时，大多数查询都有过滤条件。如果有过滤条件，sql语句就变成了select*fromtwhere***orderbyidlimitm,n如果用同样的方式，改写成类似select*fromtinnerjoin(selectidfromtwhere***orderbyidlimitm,n)t1ont1.id=t.id这种情况下，重写后的SQL语句是否还能达到优化的目的呢？在测试环境中设置测试数据相对简单。通过存储过程循环写入测试数据，使用测试表的InnoDB引擎表。这里需要注意的是日志写入方式一定要改成innodb_flush_log_at_trx_commit=2，否则默认500w数据可能一天写不完。这个跟日志写入方式有关，就不多说了。分页查询优化的原因首先我们来看这个经典问题。分页时，查询响应越慢，越慢。测试一：查询1-20行数据，0.01秒查询20行数据，查询比较“晚”的数据，比如这里查询4900001-4900020行数据，耗时1.97秒。由此可见，在查询条件不变的情况下，查询的越远，查询效率越低。可以简单理解为：同样查找20行数据，越往后的数据，查询成本越大。至于为什么后者效率低，后面会慢慢分析。测试环境为centos7，mysql5.7，测试表数据500W重现经典分页“优化”。当没有过滤条件，排序列是聚簇索引时，不会提升。下面是Theperformanceselect*fromorderbyidlimitm,n当以聚簇索引列作为排序条件时，下面两种写法的对比。select*fromtinnerjoin(selectidfromtorderbyidlimitm,n)t1ont1.id=t.id***写法：select*fromtest_table1orderbyidasclimit4900000,20;测试结果见截图，执行时间8.31秒第二次重写写法：selectt1.*fromtest_table1t1innerjoin(selectidfromtest_table1orderbyidlimit4900000,20)t2ont1.id=t2.id;执行时间为8.43秒在这里很明显，通过经典的重写方式重写后，性能并没有什么提升，甚至还慢了一点。实际测试表明，两者在性能上没有明显的线性差异。两位楼主做过多次测试。我个人看到类似的结论必须进行测试。这东西不能靠，或者运气什么的。为什么效率能提高，为什么不能提高。那么为什么重写后的写法没有像传说中那样提升性能呢？是什么导致目前的rewrite未能达到提升性能的目的？后者提高性能的原因是什么？先看测试表的表结构。排序列上有索引，没问题。关键是排序列上的索引是主键（聚集索引）。为什么当排序列是聚簇索引时，相对“优化”后重写的sql不能达到“优化”的目的？当排序列是聚簇索引列时，两者都是顺序扫描表，实现满足条件的查询，虽然后者的数据先驱动子查询，再用子查询的结果驱动主表，但是子查询不改变“顺序扫描表，实现符合条件的数据查询”的做法。在目前的情况下，连重写的做法都显得多余。参考以下两个执行计划。第一张截图中执行计划的第一行与改写后的SQL执行计划的第三行（id=2的行）基本一致。当没有过滤条件，排序列是聚簇索引时的分页查询时，所谓的分页查询优化就显得多余了。目前两种查询上述数据的方式都非常慢。如果要查询上面的数据，应该怎么查询呢？这仍然取决于它为什么慢。首先，我们必须了解B数的平衡结构。以我自己的粗略理解，如下图，当查询到的数据是“later”时，其实是偏离了B-tree索引的方向，下面两张截图显示的目标数据，其实就是对的数据平衡树，没有所谓的“前”和“后”之分，“前”和“后”是相对的，或者说是从扫描的方向看的。“后”数据是从一个方向看，“前”数据是从一个方向看，前后不是绝对的。下面两张截图是B-tree索引结构的粗略表达。如果目标数据的位置是固定的，那么所谓的“后退”就是相对于从左到右；如果从右向左看，前面的所谓后面的数据其实是“更早的”。只要数据在前面，还是可以高效的找到这部分数据的。mysql应该也有类似sqlserver中正向（forwarded）和反向扫描（backward）的方法。如果对后面的数据使用反向扫描，应该可以很快找到这部分数据，然后对找到的数据再次排序（asc），结果应该是一样的，先看效果：结果是和上面的查询是一模一样的，这里只用了0.07秒，而前面两种写法都超过了8秒，效率上有几百倍的差距。至于为什么会这样，我想根据上面的解释，应该可以理解了，这里附上sql。如果经常查询所谓的后面的数据，比如Id比较大的数据，或者时间维度比较新的数据，可以使用索引向后扫描的方式实现高效的分页查询（这里请计算分页where数据所在，相同数据，起始“页码”正反序不同)select*from(select*fromtest_table1orderbyiddesclimit99980,20)torderbyid;当没有过滤条件，排序列是非聚集索引时，这里会改进对测试表test_table1做如下修改1，增加一个id_2列，2，在该字段上创建唯一索引，3，用对应的主键Id填充该字段 上面的测试是根据主键索引（聚集索引）进行排序的，现在按照非聚集索引进行排序，即按照新增的列id_2进行排序，并测试开头提到的两种分页方式。先看第一种写法select*fromtest_table1orderbyid_2asclimit4900000,20;执行时间1分钟多一点，暂且算60秒第二种写法selectt1.*fromtest_table1t1innerjoin(selectidfromtest_table1orderbyid_2limit4900000,20)t2ont1。id=t2.id;执行时间1.67秒从这种情况来看，也就是说当排序列是非聚集索引列时，后一种写法确实可以大大提高效率。几乎提高了40倍。那么是什么原因呢？我们先来看第一种写法的执行计划。可以简单理解为这条sql执行过程中扫描了全表后，再根据id_2排序，取出前20条数据。首先，全表扫描是一个非常耗时的过程，排序也是一个非常高的成本，所以性能很低。再来看看后者的执行计划。首先在sub-subquery中，在id_2上按照索引顺序进行扫描，然后使用符合条件的主键Id去查询表中的数据。这样就避免了查询大量数据然后重新排序（Usingfilesort）。如果了解sqlserver的执行计划，后者相对于前者，应该避免频繁的回表（sqlserver中调用keylookup或者bookmarklookup的过程，可以认为是子查询驱动外表查询满足条件。）处理20条数据为一批，一次性。实际上，只有在当前情况下，即排序列为非聚集索引列时，重写的SQL才能提高分页查询的效率。即便如此，这种方式“优化”过的分页语句与下面这种方式写出来的分页效率还是有很大差距的。正如您在上面看到的，返回了相同的数据。以下查询耗时0.07秒，仍然高于此处的1.67秒。2个数量级select*from(select*fromtest_table1orderbyiddesclimit99980,20)orderbyid;另一个我想提的问题是，如果常规的分页查询一定要有一定的顺序，那为什么不在这个列上建立聚簇索引呢。比如语句自增Id，或者时间+其他字段保证唯一性，mysql会自动在主键上建立聚簇索引。那么有了聚簇索引，“前”和“后”只是一个相对的逻辑概念。如果大部分时间想得到“返回”或者更新的数据，可以使用上面的写法。当有过滤器在一定条件的情况下，我想到了分页查询的优化。情况太复杂，很难总结出一个非常有代表性的案例，就不做太多测试了。select*fromtwhere***orderbyidlimitm,n1，例如选择条件本身效率很高，过滤后只剩下一小部分数据，所以重写SQL意义不大，因为过滤条件本身就可以很高效2.比如选择条件本身不是很有效（过滤后数据量还是很大），这种情况其实又回到了没有过滤条件的情况，就看如何sort,forwardorreverse等3、比如过滤条件本身影响不大（过滤后的数据量还是很大的），还有一个很实际要考虑的问题就是数据分布，也会影响执行SQL的效率（有SQLServer经验，MySQL应该相差不大）4.当查询本身比较复杂时，很难说某种方法能达到高效的目的。情况越复杂，就越难总结出一个通用的规律或方法。看着看着，很难下定论。在这里，对于查询加过滤条件的情况，我们就不一一分析了，但是可以肯定的是，实际场景中肯定没有固化的解决方案。另外，在查询当前页的数据时，可以通过上一页查询到的***值作为过滤条件，快速找到当前页的数据。当然没有问题，不过这是另外一种方法，本文不讨论。列表。在SQLServer下添加一个测试结果，如果是非聚集索引，如果查询排序列是单列索引，分页方式无法提高效率。createtableTestPaging(idintidentity(1,1),namevarchar(50),othervarchar(100))declare@iint=0while@i<100000begininsertintoTestPagingvalues(NEWID(),NEWID())set@i=@i+1endcreateindexidxonTestPaging(name)从执行从计划中可以看出，查询Id的子查询除非是符合索引，否则都是全表扫描。只有当表的数据比较大的时候才能提高效率（子查询索引扫描的代价比全表扫描的代价要小），不过话说回来，如果经常按照某一列进行排序分页，为什么不在此列上建立聚集索引？总结一下分页查询，越晚越慢，其实对于B树索引来说，前端和后端是一个逻辑上相对的概念，性能上的差异是基于B树索引结构和扫描方式.如果加上过滤条件，情况会变得更加复杂。这个问题的原理在SQLServer中是一样的。在SQLServer中也测试过，这里不再赘述。目前情况下，排序列、查询条件、数据分布都不是一成不变的，很难用特定的方法来“优化”。所以在做分页优化的时候一定要根据具体的场景具体分析，不一定只有一种方法。脱离实际场景得出的结论都是扯淡。只有搞清楚这个问题的来龙去脉，才能游刃有余。所以，我个人关于数据“优化”的结论一定要具体问题具体分析。非常忌讳总结出一套规则（规则1、2、3、4、5）来“套用”到别人身上。由于我也很优秀，所以我只是不敢总结一些教条。
