分页查询的优化

1.创建索引

首先需要在查询字段和排序字段上创建合适的索引，否则数据库引擎就会扫描整张表，降级为filesort

2.分页优化：记住上次位置

比如：

SELECT * FROM orders ORDER BY created_at DESC LIMIT 10000, 10;

实际上MySQL得先扫描前10000条记录，然后丢掉，再返回后面10条。这就像你在书店找第10001本书，得先把前面的全数一遍。

方案：记住上次位置

SELECT * FROM orders WHERE id > last_id ORDER BY id ASC LIMIT 10;

这样，MySQL可以直接从last_id开始找，不用再从头扫描，性能杠杠的。

如何获取每次开始查找的位置呢?

答案是：从上一次结果中获取最大的那条记录（也就是最后一条记录）

注意：这种方案的适用前提是 -> 查询条件一定要是有序的

上述方案对于某些具有唯一性约束的数据来说效果是十分显著的；

但某些数据可能存在分页截断的风险，比如在某批分页中最后一条数据的id=1001，下一批第一条数据的id=1001，这样就就造成了数据的不完整性

优化：可以结合子查询对上述SQL进行优化

SELECT * FROM orders WHERE id in ( SELECT id FROM orders WHERE id > last_id  GROUP BY id ORDER BY id ASC LIMIT 10 );

这样就保证了数据的完整性；

3.文件排序优化

当ORDER BY不能直接使用索引进行排序时，MySQL会使用排序算法。会先把需要排序的文件加载到缓存中，因此适当增加sort_buffer_size缓存大小能优化排序性能。