Mysql limit 终极优化

在系统分页操作时，通常使用LIMIT加上偏移量的办法实现，同时加上合适的order by，如果有对应的索引，效率会不错。否则Mysql需要做大量的排序。

 

• 优化分页查询最简单的办法就是尽量使用索引扫描，而不是查询所有的列。然后根据关联查询返回所需要的列。考虑下面的查询：

mysql > SELECT * FROM USER  LIMIT 27000080,20;

这个表非常大，查询一次平均7.230s，如下图：

 

那么这个查询最好改成下面这个样子：

mysql > SELECT * FROM USER INNER JOIN (  SELECT id FROM USER ORDER BY id LIMIT 27000080,20) AS lim USING(id);

这里的"延迟关联"将大大提升查询效率，查询一次平均5.637s，扫描尽可能少的记录。查询时间如下图：

 

• 将LIMIT查询转换为已知位置的查询，加入第一次查询是27000081 ~ 27000100的记录，那么下一次查询就可以基于上面的优化从27000100这个点开始：

mysql > SELECT * FROM USER INNER JOIN (  SELECT id FROM USER WHERE id > 27000100 ORDER BY id LIMIT 20) AS lim USING(id);

该查询的查询时间为0.031s，如下图：

 

其他方法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表只包含主键列和需要排序的列，还可以使用Sphinx优化搜索操作。Sphinx(http://www.sphinxsearch.com）是一个免费、开源的全文搜索引擎，完美与数据库结合。