如何用WHERE子句实现多列主键MySQL表的高效分页查询

对于含复合主键（ifk1, ifk2）的InnoDB表，要实现高效分批遍历，核心是利用主键的有序性，以上一页最后一条数据的主键值作为查询条件，精准定位下一页的起始位置，避免无WHERE条件的LIMIT分页带来的性能损耗。

具体实现步骤 #

1. 第一页查询
   直接按主键顺序取第一批数据（如果你的数据中ifk1和ifk2都大于0，也可以保留原WHERE条件，但显式排序更规范）：

   SELECT * FROM testtable 
   ORDER BY ifk1, ifk2 
   LIMIT 2;
   

   执行后，记录结果中最后一条数据的ifk1和ifk2值，假设为last_ifk1和last_ifk2。

2. 后续分页查询
   利用复合主键的排序规则（先按ifk1升序，ifk1相同时按ifk2升序），构造WHERE条件筛选出所有排在(last_ifk1, last_ifk2)之后的数据：

   SELECT * FROM testtable 
   WHERE (ifk1 > last_ifk1) OR (ifk1 = last_ifk1 AND ifk2 > last_ifk2)
   ORDER BY ifk1, ifk2 
   LIMIT 2;
   

   每次执行完当前页查询后，更新last_ifk1和last_ifk2为当前页最后一条数据的主键值，循环执行直到查询结果为空，说明遍历完成。

原理说明 #

InnoDB的主键是聚簇索引，数据本身就是按主键顺序存储的，因此上述WHERE条件可以直接利用主键索引快速定位起始位置，避免了传统LIMIT offset, size中offset过大时的全表扫描问题，大数据量下性能稳定。

注意事项 #

• 必须始终记录上一页的最后一组主键值，作为下一页查询的条件；
• 如果需要降序遍历，只需将条件中的>改为<，ORDER BY改为ORDER BY ifk1 DESC, ifk2 DESC即可；
• 显式指定ORDER BY子句，避免因MySQL优化器的隐式排序导致结果顺序不符合预期。

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