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才可以。
为了方便演示在查询时没有写具体的字段,但在实际查询中尽量少用星,用哪个字段查哪个字段。