select * fom table where col='刘' limit a,b
执行逻辑
先从普通索引树查找到 a+b个索引和主键,然后回聚簇索引查找a+b个主键对应叶子节点的行数据,相当于回表了a+b次,最后执行limit 语句,筛选出b条返回。A无效回表a次。
优化limit使用子查询覆盖索引减少回表操作
select * fom table t1 inner join (select id fom table where col='刘' limit a,b) t2 on t1.id= t2.id
执行逻辑
子查询通过覆盖索引直接查出来a+b个索引和主键,因为只查出来主键字段,所以不回表(覆盖索引),然后limit直接取b条主键,最后到聚簇索引中查询主键为这b条的数据,相当于回表查询只查询了b条数据,相比之前减少了a条主键数据查询,效率大大提升。
原理:
语句一 因为limit关键字最后执行,所以先会到普通索引文件中查找a+b个索引和主键,然后回表(聚簇索引)a+b次到叶子节点去查找行数据,然后把查询出来的a+b条行数据筛选出b条返回。
语句二 首先执行子查询,会从普通索引树查找a+b条索引字段和主键,因为子查询查出来的字段是主键,无需回表(覆盖索引),然后执行limit筛选出b条索引和主键,使用这b条主键到聚簇索引树中查找这b条主键对应的行数据,相当于只回表了b次,减少了a次回表,大大提升了性能。