共计 915 个字符,预计需要花费 3 分钟才能阅读完成。
一、背景
我们在开发的过程中使用分页是不可避免的,通常情况下我们的做法是使用 limit 加偏移量:select * from table where column=xxx order by xxx limit 1,20。当数据量比较小时(100 万以内),无论你翻到哪一页,性能都是很快的。如果查询慢,只要在 where 条件和 order by 的列上加上索引就可以解决。但是,当数据量大的时候(小编遇到的情况是 500 万数据),如果翻到最后几页,即使加了索引,查询也是非常慢的,这是什么原因导致的呢?我们该如何解决呢?
二、limit 分页原理
当我们翻到最后几页时,查询的 sql 通常是:select * from table where column=xxx order by xxx limit 1000000,20。查询非常慢。但是我们查看前几页的时候,速度并不慢。这是因为 limit 的偏移量太大导致的。MySQL 使用 limit 时的原理是(用上面的例子举例):
MySQL 将查询出 1000020 条记录。
然后舍掉前面的 1000000 条记录。
返回剩下的 20 条记录。
上述的过程是在《高性能 MySQL》书中确认的。
三、解决方案
解决的方法就是尽量使用索引覆盖扫描,就是我们 select 后面检出的是索引列,而不是所有的列,而且这个索引的列最好是 id。然后再做一次关联查询返回所有的列。上述的 sql 可以写成:
SELECT
*
FROM
table t
INNER JOIN (
SELECT
id
FROM
table
WHERE
xxx_id = 143381
LIMIT 800000,20
) t1 ON t.id = t1.id
我们在 mysql 中做的真实的实验:
上图是没有优化过的 sql,执行时间为 2s 多。经过优化后如下:
执行时间为 0.3s,性能有了大幅度的提升。虽然做了优化,但是随着偏移量的增加,性能也会随着下降,MySql 官方虽然也给出了
其他的解决方案,但是在实际开发中很难使用。
有的同学可能会问,能不能使用 IN 嵌套子查询,而不使用 INNER JOIN 的方式,答案是不可以,因为 MySql 在子查询中不能使用 LIMIT。
MySQL 分页优化就先介绍到这里了。
: