MySQL深分页查询中select *与select主键结果不一致问题排查

问题背景 #

• MySQL表t_order共有160万条记录，order_id是主键，order_status设为二级索引，且已统计该字段的数据分布情况。

执行对比情况 #

SQL 1 #

select order_id from t_order where order_status > 3002 order by order_status limit 349000,10;

• 执行结果：返回的order_id为1498660至1498669
• 执行计划：使用了idx_order_status二级索引

SQL 2 #

select * from t_order where order_status > 3002 order by order_status limit 349000,10;

• 执行结果：返回的order_id为1746820至1746829
• 执行计划：未使用idx_order_status索引

问题解答 #

哪个结果正确？ #

SQL 1的结果是正确的。它严格按照SQL语句要求，基于order_status的顺序进行排序和分页，完全匹配查询意图。

原因分析 #

1. 索引选择的成本差异：
   • SQL 1只查询order_id，而InnoDB的二级索引idx_order_status本身就包含主键order_id（结构为(order_status, order_id)），直接走索引就能拿到所有需要的数据，无需回表查询全量记录，MySQL会选择这条最优路径，此时数据是按order_status有序排列后分页的。
   • SQL 2要查询所有字段，若走二级索引，需先从索引中拿到符合条件的order_id，再逐个回表获取整条记录。当偏移量349000很大时，MySQL评估回表成本过高，就会放弃二级索引，转而扫描主键索引（全表扫描）。主键索引按order_id顺序存储，扫描后先过滤order_status <=3002的记录，再在内存中按order_status排序分页，这种逻辑和走二级索引的排序逻辑不一致，最终得到不同的结果。
2. 排序的底层逻辑差异：
   • 走二级索引时，数据本身已按order_status排好序，分页取的是符合条件的第349001至349010条记录。
   • 走主键索引全表扫描时，先按order_id顺序读取记录，过滤后再排序，虽最终也是按order_status排序，但初始读取顺序不同，加上大偏移量放大了差异，导致结果偏差。

解决方法 #

1. 强制使用二级索引：
   在SQL 2中添加force index(idx_order_status)，强制MySQL走二级索引，确保排序和分页逻辑与SQL 1一致：

   select * from t_order force index(idx_order_status) where order_status > 3002 order by order_status limit 349000,10;
   

2. 优化分页逻辑，避开大偏移量：
   大偏移量的limit性能差且易出逻辑问题，换成基于主键的分页方式更可靠。先通过SQL 1拿到分页后的最后一个order_id，再以此为条件查询全量数据：

   -- 先获取分页范围内的最后一个order_id
   select order_id from t_order where order_status > 3002 order by order_status limit 349000,10;
   -- 假设最后一个order_id是1498669，再查询全量数据
   select * from t_order where order_status > 3002 and order_id > 1498669 order by order_status limit 10;
   

   这种方式既保证结果正确，也能提升查询性能。
3. 更新表统计信息，辅助MySQL做正确选择：
   更新表的统计信息，让MySQL更准确评估索引成本，可能会自动选择走二级索引：

   analyze table t_order;
   

   若更新后仍不走索引，再使用强制索引的方法即可。

内容的提问来源于stack exchange，提问作者muzi li