SQL优化 – 分页查询性能提升方法

@创建时间 2020年03月20日 18:47:12
@更新时间 2023年04月04日 10:21:52

SELECT * FROM t
ORDER BY index_col
LIMIT 2010, 10;

MySQL默认全表扫描：扫描2010行后并丢弃前2000行，性能较差

1）分页尽量只查询所需列
2）忽略记录中的大字段：单页中应避免显示每条记录的CLOB、BLOB，应将大字段显示放入查看单条详情时较合理
3）分页数特别多时，限制最大偏移量

1）为查询列建立覆盖索引，可利用索引加快查询速度

SELECT index_col FROM t
ORDER BY index_col
LIMIT 2010, 10;

2）业务要求列不仅为覆盖索引时，可利用覆盖索引加快定位：由子查询定位索引范围，主查询根据范围过滤

SELECT * FROM t
INNER JOIN (SELECT index_col FROM t ORDER BY index_col LIMIT 2010, 10)
USING (index_col);

SELECT * FROM t
WHERE index_col >= (SELECT index_col FROM t ORDER BY index_col LIMIT 2010, 1)
LIMIT 10;

1）使用相邻页查询结果推导下一页的查询结果

SELECT * FROM t
ORDER BY index_col
LIMIT 2010, 10;
# 此时获取上页中末条数据的index_col值，假设为6
SELECT * FROM t
WHERE index_col > 6
ORDER BY index_col 10;

2）已知索引范围的，用BETWEEN AND快速定位

SELECT * FROM t
WHERE index_col BETWEEN 6 AND 8
ORDER BY index_col;

Botros S, Tinley J. High Performance MySQL[M]. 4th Edition. O’Reilly, 2022.