MySQL分页优化

认识 MySQL 分页

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数，LIMIT 接收一个或两个数字参数，参数必须是一个整数常量，如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数

举个简单的例子，分析下 SQL 查询过程

SELECT * FROM `order` WHERE create_at > '2020-01-01' ORDER BY create_at DESC LIMIT 1000000, 1

简单说明下上面 SQL 执行过程：

• 首先查询了表 order，进行过滤 create_at 条件，查询出展示列（涉及回表操作）进行排序以及 LIMIT
• LIMIT 1000000, 1 的意思是扫描满足条件的 1000001 行，然后扔掉前 1000000 行

MySQL 耗费了 大量随机 I/O 在回表查询聚簇索引的数据上，而这 1000000 次随机 I/O 查询数据不会出现在结果集中。如果系统并发量高一点，且每次查询扫描超过 1000000 行，想想性能会怎么样。

LIMIT 分页 OFFSET 越深，性能越差。

深分页优化

关于 MySQL 深分页优化常见策略如下：

• 子查询优化
• 延迟关联
• 书签记录

上面三点都能大大的提升查询效率，核心思想就是让 MySQL 尽可能扫描更少的页面，获取需要访问的记录后再根据关联列回原表查询所需要的列

子查询优化

子查询深分页优化语句如下：

SELECT * FROM `order` WHERE id >= (
	SELECT id FROM `order` WHERE create_at > '2020-01-01' ORDER BY create_at DESC LIMIT 1000000, 1
) LIMIT 1;

先查询主键 ID 省去了回表的操作，然后第二查询直接根据第一个查询的 ID 往后再去查就可以了。

延迟关联

延迟关联深分页优化语句如下：

SELECT a.* FROM `order` a
INNER JOIN (SELECT id FROM `order` WHERE create_at > '2020-01-01' ORDER BY create_at DESC LIMIT 1000000, 1) AS b ON b.id=a.id

思路以及性能与子查询优化一致，只不过采用了 JOIN 的形式执行。

书签记录

关于 LIMIT 深分页问题，核心在于 OFFSET 值，它会导致 MySQL 扫描大量不需要的记录行然后抛弃掉。

我们可以先使用书签记录获取上次取数据的位置，下次就可以直接从该位置开始扫描，这样可以 避免使用 OFFEST。

假设需要查询 3000000 行数据后的第 1 条记录，查询可以这么写：

SELECT * FROM `order` WHERE id > 1000000 ORDER BY create_at DESC LIMIT 1;

查询速度超级快，从性能上考虑碾压其它方式。不过这种方式需要获取结果集的最后一条的索引ID才可以。

为了方便演示在查询时没有写具体的字段，但在实际查询中尽量少用星，用哪个字段查哪个字段。