mysql的limit分页查询,会随着偏移量的增大,效率会急剧下降。
查询device表,偏移量分别是10,100,1000,10000,然后测试查询时间。
1 2 3 4 |
select * from tb_device limit 10, 20 0.016秒 select * from tb_device limit 100, 20 0.016秒 select * from tb_device limit 1000, 20 0.047秒 select * from tb_device limit 10000, 20 0.094秒 |
如果直接把offset设置为40W,足足需要三秒多。
1 |
select * from tb_device limit 400000, 20 3.229秒 |
那么,有什么优化方法呢?
利用表的覆盖索引来加速分页查询优化
查询的语句中,如果只包含了索引列,那么查询就会非常快,因为利用覆盖索引查询有算法优化,并且数据就在索引上,不需要再查找相关数据地址了,而且mysql中也有索引缓存。
id字段是主键,索引就是主键索引。我们可以把语句改成这样:
1 |
select id from tb_device limit 860000, 20 0.2秒 |
可以看到速度提升了100多倍。
那其实大多数场景,我们不光是只查询id的,那如果要 select * 该怎么办呢?
一种是 >= 的写法
1 |
SELECT * FROM tb_device WHERE ID > =(select id from tb_device limit 860000, 1) limit 20 |
另一种是 join 写法
1 |
SELECT * FROM tb_device a JOIN (select id from tb_device limit 866613, 20) b ON a.ID = b.id |
这两种写法其实原理一样,只要掌握了这些mysql大数据量分页查询时的优化方法,就再也不怕客户嫌查询慢了。
0