【limit优化】MySQL延迟关联性能优化方法

2019独角兽企业重金招聘Python工程师标准>>>

一.业务

      假设业务某个场景中，需要查询多次查询数据，sql如下：

startNum = i * 500;
select id,content from test_table order by update_date asc limit " + startNum + ",500";

      limit n,m定义为：从第n行开始选择m条记录

      查询表的数据量大致有36w左右，该sql是一个非常典型的排序+分页查询：order by col limit n,offset m , MySQL 执行此类sql时需要先扫描到N行，然后再去取 M行。对于此类大数据量的排序操作，取前面少数几行数据会很快，但是越靠后，sql的性能就会越差，因为N越大，MySQL 需要扫描不需要的数据然后在丢掉，这样耗费大量的时间。

     针对limit 优化有很多种方式，
1、前端加缓存，减少落到库的查询操作
2、优化SQL【策略1】
3、使用书签方式 ，记录上次查询最新/大的id值，向后追溯 M行记录。【策略3】
4、使用Sphinx 搜索优化。【策略2】
对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作，何谓"延迟关联" ：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

二.优化策略1：把n变成一个条件【延迟关联】      

startNum = i * 500;
select a.id,a.content from test_table a,
(select id,content from test_table order by update_date asc limit " + startNum + ",500")b 
where a.id=b.id;

      从以上sql可以看出，我们把limit n,m变成了一个条件，在外层加了一个联表查询。可能有朋友会问，这样处理不也需要先查n笔，再往后面查m笔吗？其实这样说的也没错。只是，把n变成了一个条件这种处理方式叫做“延迟关联”，它是先通过主键关联查出来的，并不是先去查n.m。这样效率会高一点。具体效率是否提高，可以explain一下这个sql，看下sql的执行计划就清楚了。

三.优化策略2：让n走索引     

select id,content from test_table 
where xxx=n 
order by update_date asc 
limit m; 其中 xxx=n 为一个有索引的字段;

      索引不依赖于项目框架，可以说跟框架没有半毛钱关系，只要写的是sql且能提交到数据库去执行，条件走索引，就会走索引。走索引效率会提高。     

四.业务策略3：记录上次查询的最大id，向后追溯M行记录

endNum = (i + 1)*500;
select id,content from test_table 
where id > 
(select id,content from test_table order by id asc limit endNum,1)  
limit 500

      这种方式与原sql对比，原sql需要跨越大量数据块并取出，优化后基本通过直接根据索引字段定位，才取出相应内容，效率自然大大提升。

转载于:https://my.oschina.net/xsh1208/blog/496192