欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL慢查询优化之Limit优化

程序员文章站 2022-05-03 15:11:10
...

数据源

数据源是4000000万条员工数据

MySQL慢查询优化之Limit优化

基于自增主键进行分页

1.一般方法

select ename,job from emp limit 1000000,20

MySQL慢查询优化之Limit优化

2.延迟关联优化方法

优化思路:

  • 对于数据量较大数据表,可以建立主键和索引字段建立索引表,通过索引表查询相应的主键,在通过主键查询数据量的数据表;
select ename,job from emp inner join (select empno from emp limit 1000000,20) as lim using(empno)

MySQL慢查询优化之Limit优化

3.利用上一次limit查询主键值

select * from emp where empno >1000000 limit 20

MySQL慢查询优化之Limit优化

对以上三种limit分页方法执行100次查询,计算每种查询的平均执行时间

MySQL慢查询优化之Limit优化

对比速度下来第一种方法速度最慢平均需要时间0.748,第二种次之平均耗时0.599,第三种0.125,对比下来第三种性能是最快的

基于非自增主键的Limit

如果员工数据表主键不是自增字段而是uuid之类的又会发生什么变化.

比如现在有这么一个需求:
查询 2017-11-19时间点之后入职的第100001~到100020的20条数据

一般方法

select * from emp where hiredate >'2017-11-19' limit 100000,20;

延迟关联

select * from emp inner join (select empno from emp where hiredate >'2017-11-19' limit 100000,20)as emmid using(empno);

对上述sql分别执行1000次

MySQL慢查询优化之Limit优化

由于hiredate不像主键一样是唯一的,所以我们不能使用基于主键的第三种分页方法

我发现第一种方法要比第二种方法运行的要快,快了接近0.05s,为了找出原因,我查询了执行计划
MySQL慢查询优化之Limit优化

发现第一种查询方法使用了一种mrr的执行计划
我将该mrr计划关闭后再次测试了1000次一般查询方式
MySQL慢查询优化之Limit优化

这一次一般方法比优化过后的方法慢了0.017s

当利用二级索引作为分页条件时候并且使用mrr情况下,没有必要使用延迟关联进行优化