Limit常用于分页处理,时长会伴随order by从句使用,因此大多时候回使用Filesorts这样会造成大量的IO问题。 例子: 需求:查询影片id和描述信息,并根据主题进行排序,取出从序号50条开始的5条数据。
select film_id,description from sakila.film order by title limit 50,5;
执行的结果:
在查看一下它的执行计划:
对于这种操作,我们该用什么样的优化方式了? 优化步骤1: 使用有索引的列或主键进行order by操作,因为大家知道,innodb是按照主键的逻辑顺序进行排序的。可以避免很多的IO操作。
select film_id,description from sakila.film order by film_id limit 50,5;
查看一下执行计划
那如果我们获取从500行开始的5条记录,执行计划又是什么样的了?
explain select film_id,description from sakila.film order by film_id limit 500,5\G
随着我们翻页越往后,IO操作会越来越大的,如果一个表有几千万行数据,翻页越后面,会越来越慢,因此我们要进一步的来优化。
优化步骤2 记录上次返回的主键, 在下次查询时使用主键过滤。(说明:避免了数据量大时扫描过多的记录) 上次limit是50,5的操作,因此我们在这次优化过程需要使用上次的索引记录值,
select film_id,description from sakila.film where film_id >55 and film_id<=60 order by film_id limit 1,5;
查看执行计划:
结论:扫描行数不变,执行计划是很固定,效率也是很固定的
注意事项: 主键要顺序排序并连续的,如果主键中间空缺了某一列,或者某几列,会出现列出数据不足5行的数据;如果不连续的情况,建立一个附加的列index_id列,保证这一列数据要自增的,并添加索引即可。