以下测试使用MariaDB，版本为10.0.36，InnoDb版本为5.6.39-83.1。

以下分页查询优化针对OLTP交易场景。

以下对索引的分析方法可参考“MySQL索引分析除了EXPLAIN还有什么方法”。

使用limit offset方式进行分页查询时，存在以下问题：

当使用limit offset方式进行分页查询时，偏移量对应的行数也会被读取，不会被跳过，当偏移量较大时，读取效率较低。

例如查询语句使用“limit offset, row_count”，则查询时需要读取的行数至少为offset + row_count。

此外，在进行查询时，通常会使用二级索引作为条件，在获取整行数据时，需要回表，回表的记录数至少为offset + row_count。

以下用于测试的数据库表为test_table_log、test_table_log2，两个表的字段相同，索引不同，如下所示：

以上两个表行数为500万行，数据相同，type范围为[‘1’,‘10’]，cust_info范围为[‘1000’,‘2000’]，status范围为[‘1’,‘3’]，other1固定为’1’，other2范围为[‘1000’,‘2000’]，create_time、update_time可看作插入时的时间。

以下进行测试时，在执行查询操作前，会重启数据库，防止MySQL在查询时使用缓冲池中的数据。

分页查询常见场景包括但不限于以下场景：

进行分页查询时，当查询语句中的WHERE、ORDER BY等条件涉及多个字段时，需要使用联合索引查询。

例如查询时需要限定客户信息（或其他区分度高的字段，以下统称“客户信息”）、时间范围，并根据时间进行排序。如客户查询自己的交易记录。

进行分页查询时，当查询语句中的WHERE、ORDER BY等条件只涉及一个字段时，需要使用单列索引查询。

例如查询时仅需要限定时间范围，并根据时间进行排序（不限定客户信息）。如内部管理系统查询，或对账时获取前一天交易数据的处理。

示例语句如下所示：

当limit的offset增大时，查询耗时及检查行数变化如下所示：

示例语句如下所示：

当limit的offset增大时，查询耗时及检查行数变化如下所示：

以上指定“limit 4000000,100”时，EXPLAIN查看显示未使用索引idx_ttl2_time进行查询，进行全表扫描，使用filesort。

针对limit offset分页查询的问题，可以通过以下两种方式进行优化：

使用join，首先查询满足条件记录的主键，再与原表进行join，查询以上主键对应的整行数据，可以减少查询时需要回表的记录数。

示例如下：

最后可以省略“order by a.create_time”，减少一次不必要的filesort排序操作。因为查询test_table_log2表的主键id字段执行的查询操作结果是按create_time排序的，inner join后的结果也是以create_time排序。以上查询操作对create_time进行升序或降序排序时，上述结论均成立。

升序排序与降序排序均支持。

以上示例中，通过test_table_log2表的二级索引idx_ttl2_all(cust_info,create_time,type,status)，根据cust_info、create_time字段查询满足条件记录的主键id字段，该阶段的查询不需要回表。

当查询到满足条件的10条记录的主键id字段后，从test_table_log2表查询整行的数据，该阶段的查询需要回表，即有10条记录查询时需要回表。

优化前的SQL语句“select * from test_table_log2 where cust_info=‘1881’ order by create_time limit 4000,10;”，有4010条记录查询时需要回表。

对比可知，上述优化方法可以减少回表次数，提高查询效率。

以上优化方法存在以下限制：

查询条件中（包括WHERE、ORDER BY、GROUP BY等）不能出现使用的索引字段或主键之外的其他字段，否则查询主键的过程中需要回表，优化会失效。

例如上述示例中，查询条件中只能出现idx_ttl2_all索引包含的cust_info、create_time、type、status字段，及主键id字段，否则优化会失效。

使用join减少回表次数，不能减少查询需要检查的行数。支持非连续/连续分页时查询。查询返回的行数固定（最后一次查询除外）。

每次查询时，需要将上次查询的最后一条记录作为本次查询的起始范围（首次查询不需要指定）。

以上指定的时间，为上一次查询的最后一条记录的时间。每次查询时需要记录上一次查询最后一条记录的对应数据。

升序排序与降序排序均支持。

以上示例中，limit不需要指定offset，每次分页查询需要检查的行数接近（与实际数据分布有关）。

假如查询条件可以完全通过索引字段完成，则只需要读取二级索引中的10条记录，再对10条记录进行回表即可。

假如查询条件中包含了不在索引中的字段，则需要读取二级索引中的n条记录，再对n条记录进行回表，找到满足条件的10条记录（n大于等于10，与实际查询条件有关）。

限定查询起始数据，不使用offset，可以减少查询需要检查的行数。仅支持连续分页时查询，不支持非连续分页查询。查询返回的行数可能不固定。

某个时间点的数据可能分布在多个分页中，需要特殊处理，如下文所示。

需要考虑某个时间点的数据分布在多个分页的情况，需要确保查询时不漏数据，也不多数据。

例如每次查询10条记录，上一次查询的后x条记录，和下一次查询的前y条记录，都属于同一个时间点。

可以采用以下两种方式进行处理：

查询条件指定“[时间] 大于 [上一次查询的最后一条记录的时间]”（或小于），可能导致查询丢失数据，需要进行补充。

每次查询完毕后，若结果非空，再查询本次查询的最后一条最后一条记录的时间对应的全部记录，若最后一条记录等于当前查询的最后一条记录，则不需要进行补充；若有多条记录，则将剩余数据拼接到返回列表尾部。

是否需要补充与实际数据有关，每次分页查询需要查询两次数据库，返回数据记录数可能比分页数量多。

示例如下：

分页查询数量为3，第一次查询到主键为a、b、c记录，最后一条记录的时间为00:00:01，该时间还存在另一条主键为d的记录未查询到，需要再次查询时间为00:00:01的记录，排除主键为c及之前的记录，将剩余记录拼接到返回列表尾部。下次查询时间大于00:00:01的记录。

查询条件指定“[时间] 大于等于 [上一次查询的最后一条记录的时间]”，会导致查询到重复的数据，需要进行去重。

每次查询完毕后，若结果非空，从查询结果列表头部开始遍历，查找主键等于上一次查询的最后一条记录主键的记录，将此之前的记录全部删除

每次分页查询需要只查询一次数据库，但需要对查询结果进行去重，返回数据可能比分页数量少。

以上处理的前提条件是，每次查询的分页数量，需要大于单位时间内（与时间精度有关，例如一秒内或一毫秒内）可能出现的记录数量，否则查询到对应记录时可能出现死循环。

示例如下：

分页查询数量为3，第一次查询到主键a、b、c记录，最后一条记录的时间为00:00:01，第二次查询时间大于等于该时间的记录，查询到主键为b、c、d的记录，需要删除列表中主键为c及之前的记录再返回。下次查询时间大于等于00:00:02的记录。

当查询语句中的WHERE、ORDER BY等涉及多个字段时，需要为相应的字段创建联合索引。

例如当需要分页查询指定客户信息在指定时间段的记录并通过时间排序时，需要为客户信息与时间字段创建联合索引，而不是创建多个单列索引。

原因如下：

使用单列索引只有一个字段可以通过索引进行查找；使用联合索引可以使查询条件中的字段都能通过索引进行查找，可以减少查询时的检查范围。

使用单列索引时，排序条件可能需要使用filesort；使用联合索引时，排序可以通过索引完成，可以避免filesort排序。

以下为上述情况使用单列索引与联合索引的对比。

以下cust_info、create_time字段分别具有单列索引。

以下cust_info、create_time字段具有联合索引。

根据以上对比可知，当查询语句中的WHERE、ORDER BY等涉及多个字段时，使用单列索引进行查询时，需要通过filesort进行排序；使用联合索引时不需要。

升序排序：

降序排序：

以上Rows_examined结果5332，等于5222+100+10，5222为test_table_log表中cust_info='1881’的记录行数。

升序排序：

降序排序：

以上Rows_examined结果110，等于100+10。

根据以上对比可知，使用联合索引进行查询时，检查的行数更少，耗时更短。

以下为使用联合索引进行查询，支持非连续/连续分页时的优化方法。

以下指定的时间条件不会过滤掉数据，仅起示例作用。

以下查询条件中使用了不在idx_ttl2_all(cust_info,create_time,type,status)索引中的列other1，查询条件导致需要回表。

执行计划差别不大。

根据以上对比可知，使用联合索引查询，支持非连续/连续分页时，当查询条件不会导致回表时，优化后可以缩短查询耗时并减少检查行数；当查询条件会导致回表时，优化后无效果。

查询语句读取索引次数差别不大。

根据以上对比可知，使用联合索引查询，支持非连续/连续分页时，优化后若查询条件不会导致回表，InnoDB缓冲池中主键对应的索引页与记录数明显减少，即减少了回表次数。

以下为使用联合索引进行查询，仅支持连续分页时的优化方法。

为了简化查询操作，以下查询不考虑某个时间点的数据分布在多个分页内的情况。

以下查询条件中使用了不在idx_ttl2_all(cust_info,create_time,type,status)索引中的列other2，查询条件导致需要回表。

other2字段的范围为['1000,‘2000’]，以下查询条件指定other2>‘1900’，会过滤约90%的记录。

根据以上对比可知，需要检查的行估计值rows在优化后减少。

根据以上对比可知，使用联合索引查询，仅支持连续分页时，优化后可以缩短查询耗时并减少检查行数。

当查询条件会导致回表时，优化后也有效果，相比不会导致回表的情况，需要检查的行数更多，耗时更长（与数据实际分布有关）。

根据以上对比可知，使用联合索引查询，仅支持连续分页时，优化后查询语句读取索引次数明显变少。

根据以上对比可知，使用联合索引查询，仅支持连续分页时，优化后InnoDB缓冲池中二级索引及主键索引对应的索引页与记录数明显减少，即减少了查询过程中读取的行数及回表次数。

以下为使用单列索引进行查询，支持非连续/连续分页时的优化方法。

以下查询条件中使用了不在idx_ttl2_time(create_time)索引中的列other1，查询条件导致需要回表。

执行计划差别不大。

根据以上对比可知，使用单列索引查询，支持非连续/连续分页时，当查询条件不会导致回表时，优化后可以缩短查询耗时并减少检查行数；当查询条件会导致回表时，优化后无效果。

查询语句读取索引次数差别不大。

根据以上对比可知，使用单列索引查询，支持非连续/连续分页时，优化后若查询条件不会导致回表，InnoDB缓冲池中主键对应的索引页与记录数明显减少，即减少了回表次数。

以下为使用单列索引进行查询，仅支持连续分页时的优化方法。

为了简化查询操作，以下查询不考虑某个时间点的数据分布在多个分页内的情况。

以下为使用单列索引进行查询，仅支持连续分页时的优化方法。

以下查询条件中使用了不在idx_ttl2_time(create_time)索引中的列other2，查询条件导致需要回表。

other2字段的范围为['1000,‘2000’]，指定条件other2>‘1900’，会过滤约一半的记录。

执行计划差别不大。

根据以上对比可知，使用单列索引查询，仅支持连续分页时，优化后可以缩短查询耗时及检查行数。

当查询条件会导致回表时，优化后也有效果，相比不会导致回表的情况，需要检查的行数更多，耗时更长（与数据实际分布有关）。

根据以上对比可知，使用单列索引查询，仅支持连续分页时，优化后查询语句读取索引次数明显变少。

根据以上对比可知，使用单列索引查询，仅支持连续分页时，优化后InnoDB缓冲池中二级索引及主键索引对应的索引页与记录数明显减少，减少了查询过程中读取的行数及回表次数。

参考 https://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html 。

LIMIT会影响执行计划，指定的查询在使用/不使用LIMIT时，返回行的顺序可能会不同。

如果需要保证查询在使用/不使用LIMIT时，返回的行的顺序相同，需要在ORDER BY子名中包含其他的列，以使顺序确定。例如在ORDER BY中除了原有用于排序的字段外，还指定主键字段。

当需要判断是否存在指定条件的记录，但不需要知道具体数量时，可以使用limit 1代替count()方法，若存在多条满足条件的记录，可以减少查询时检查的行数。

参考 https://dev.mysql.com/doc/refman/5.6/en/update.html 、 https://dev.mysql.com/doc/refman/5.6/en/delete.html 。

UPDATE、DELETE语句支持使用limit限定行数，在对多表进行更新或删除操作时，不支持使用limit。