MySQL慢查询优化之Limit优化

数据源

数据源是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情况下，没有必要使用延迟关联进行优化