MySQL慢查询优化之Limit优化
程序员文章站
2022-05-03 15:11:10
...
数据源
数据源是4000000万条员工数据
基于自增主键进行分页
1.一般方法
select ename,job from emp limit 1000000,20
2.延迟关联优化方法
优化思路:
- 对于数据量较大数据表,可以建立主键和索引字段建立索引表,通过索引表查询相应的主键,在通过主键查询数据量的数据表;
select ename,job from emp inner join (select empno from emp limit 1000000,20) as lim using(empno)
3.利用上一次limit查询主键值
select * from emp where empno >1000000 limit 20
对以上三种limit分页方法执行100次查询,计算每种查询的平均执行时间
对比速度下来第一种方法速度最慢平均需要时间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次
由于hiredate不像主键一样是唯一的,所以我们不能使用基于主键的第三种分页方法
我发现第一种方法要比第二种方法运行的要快,快了接近0.05s,为了找出原因,我查询了执行计划
发现第一种查询方法使用了一种mrr的执行计划
我将该mrr计划关闭后再次测试了1000次一般查询方式
这一次一般方法比优化过后的方法慢了0.017s
当利用二级索引作为分页条件时候并且使用mrr情况下,没有必要使用延迟关联进行优化