面试题：MySQL LIMIT分页查询优化之大数据集

Question

假设存在一张拥有千万级数据量的订单表`orders`，包含字段`order_id`（主键自增）、`order_time`等。现在要实现根据下单时间进行分页查询，每页展示20条数据。要求尽可能优化查询性能，写出具体的SQL语句，并阐述优化思路。

Answer 1

SQL语句

-- 假设按order_time升序分页
SELECT order_id, order_time
FROM orders
WHERE order_time > (
    SELECT order_time
    FROM (
        SELECT order_time
        FROM orders
        ORDER BY order_time
        LIMIT 20 OFFSET (页码 - 1) * 20
    ) AS subquery
)
ORDER BY order_time
LIMIT 20;

优化思路

1. 索引优化：在order_time字段上创建索引，因为查询条件和排序都依赖这个字段，索引可以大大加快数据的查找和排序速度。

CREATE INDEX idx_order_time ON orders (order_time);

2. 避免全表扫描：上述SQL通过子查询获取到上一页最后一条数据的order_time，然后在主查询中使用WHERE order_time >来过滤数据，避免了全表扫描，特别是在大数据量的情况下，极大提升了查询效率。而如果直接使用LIMIT 偏移量, 数量的方式，随着偏移量增大，性能会急剧下降，因为数据库需要从第一条记录开始计算偏移量。
3. 分页算法优化：采用基于时间戳的分页方式，相较于传统的基于偏移量的分页，在大数据量场景下更具优势，减少了数据库扫描的数据量。