背景
我们在开发的过程中使用分页是不可避免的, 通常情况下我们的做法是使用 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 中做的真实的实验:
image1
上图是没有优化过的 sql, 执行时间为 2s 多. 经过优化后如下:
image2
执行时间为 0.3s, 性能有了大幅度的提升. 虽然做了优化, 但是随着偏移量的增加, 性能也会随着下降, MySQL 官方虽然也给出了
其他的解决方案, 但是在实际开发中很难使用.
有的同学可能会问, 能不能使用 IN 嵌套子查询, 而不使用 INNER JOIN 的方式, 答案是不可以, 因为 MySQL 在子查询中不能使用 LIMIT.
MySQL 分页优化就先介绍到这里了.
来源: https://www.qcloud.com/developer/article/1343256