MySQL实战：分页如何优化？

为什么大表深分页会变慢？

SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;

这条 SQL 的执行原理是：

1. 数据库利用索引（如果有的话）或者全表扫描，定位到满足条件的第 1,000,010 条记录
2. 抛弃前面的 1,000,000 条记录，只返回最后的 10 条

性能瓶颈

1. 巨大的“回表”开销： 如果你使用的是普通索引，MySQL 每读取一条记录，都需要拿着主键 ID 去聚簇索引里找整行数据（这个过程叫回表）。为了执行这条 SQL，数据库可能需要回表 100 万次，进行大量的随机 I/O，只为了最后白白扔掉它们。
2. CPU 与内存白白浪费： 即使使用了覆盖索引，不需要回表，数据页的读取、排序和丢弃依然会消耗大量的内存和 CPU 资源。

使用书签

用书签记录上次取数据的位置，过滤掉部分数据

优化前

SELECT * FROM orders LIMIT 1000000, 10;

优化后

-- 假设上一页最后一条记录的 id 是 1000000
SELECT * FROM orders WHERE id > 1000000 LIMIT 10;

需要有一个连续且有索引的字段（如 id 或 create_time）

延迟关联

延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据

SELECT id, name, description FROM film ORDER BY name LIMIT 100,5;

id是主键值，name上面有索引。这样每次查询的时候，会先从name索引列上找到id值，然后回表，查询到所有的数据。可以看到有很多回表其实是没有必要的。完全可以先从name索引上找到id（注意只查询id是不会回表的，因为非聚集索引上包含的值为索引列值和主键值，相当于从索引上能拿到所有的列值，就没必要再回表了），然后再关联一次表，获取所有的数据

因此可以改为

SELECT film.id, name, description FROM film 
JOIN (SELECT id from film ORDER BY name LIMIT 100,5) temp
ON film.id = temp.id

倒序查询

假如查询倒数最后一页，offset可能回非常大

SELECT id, name, description FROM film ORDER BY name LIMIT 100000, 10;

改成倒序分页，效率是不是快多了？

SELECT id, name, description FROM film ORDER BY name DESC LIMIT 10;

业务上的优化

限制最大翻页数：用户很少会查看页数很大的数据

不显示总页数：大表 count(*) 极其缓慢，可以只提供下一页或者加载更多按钮，既省去了count(*)，又避免了深分页