面试题：MySQL复杂场景下的分页优化

Question

假设存在一张非常大的数据表，包含数百万条记录，表结构为`(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), age INT)`。现在需要分页查询年龄大于30岁的用户，每页显示100条记录，当翻到第1000页时，如何优化SQL查询以提高查询效率？请给出优化后的SQL语句并解释优化思路。

Answer 1

1. 优化思路：
   • 常规的分页查询LIMIT在偏移量很大（如第1000页，偏移量为99900）时性能会急剧下降，因为数据库需要从第一条记录开始扫描到偏移量位置，再取指定数量的记录。
   • 可以利用覆盖索引和上一页的最后一条记录的主键值来优化。先通过覆盖索引快速定位到上一页最后一条记录的主键，然后基于此主键继续往后查询，避免全表扫描大量数据。
2. 优化后的SQL语句： 假设上一页最后一条记录的主键值为last_id，则优化后的SQL如下：

SELECT id, name, age
FROM your_table_name
WHERE age > 30 AND id > last_id
ORDER BY id
LIMIT 100;

这里your_table_name是实际的数据表名。通过id > last_id条件，利用主键索引快速定位后续数据，ORDER BY id确保数据顺序，LIMIT 100限制返回的记录数。