面试题：MySQL LIMIT分页查询优化之常规场景

Question

在MySQL中，当使用LIMIT进行分页查询时，例如`SELECT * FROM users LIMIT 10000, 10`，这种方式在数据量较大时性能较差。请描述一种常规的优化策略，并说明原理。

Answer 1

1. 优化策略：
   • 基于主键或其他有序索引进行分页。例如，假设users表有一个自增主键id，可以使用类似SELECT * FROM users WHERE id > (SELECT id FROM users LIMIT 10000, 1) LIMIT 10的查询。
2. 原理：
   • 当使用LIMIT offset, limit这种方式时，MySQL需要从第一条记录开始扫描，跳过offset条记录，然后再返回limit条记录。随着offset值的增大，扫描的数据量会线性增长，性能会变得很差。
   • 而基于主键或有序索引的方式，数据库可以直接定位到需要的记录位置。上述示例中，先通过子查询获取到第10000条记录的id，然后主查询通过WHERE id >条件直接从这个id之后开始获取10条记录，减少了扫描的数据量，从而提高了查询性能。