最近常在 SQL 中使用到 LIMIT ? ?;在执行 LIMIT 0, 1000 与 LIMIT 100000, 1000 时,查询速度明显有很大的区别,而且随着 LIMIT 的偏移量的增加,查询速度越来越慢。是否有办法对 SQL 中 LIMIT 查询进行优化呢?

   Google:MySQL LIMIT 优化,便找的了答案。

LIMIT 速度慢的原因

   LIMIT 100000, 1000 的意思扫描满足条件的 101000 行,扔掉前面的 100000 行,返回最后的 1000 行,问题就在这里。

LIMIT 优化思路

   1、尽可能从索引中直接获取数据,避免或减少直接扫描行数据的频率
   2、尽可能减少扫描的记录数,也就是先确定起始的范围,再往后取N条记录即可

LIMIT 优化示例

原始 SQL

-- 含条件
SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;
-- 不含 WHERE
SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

子查询优化 SQL

-- 采用子查询的方式优化,在子查询里先从索引获取到最大id,然后倒序排,再取10行结果集
-- 注意这里采用了2次倒序排,因此在取LIMIT的start值时,比原来的值加了10,即935510,否则结果将和原来的不一致
SELECT * FROM
(SELECT * FROM `t1` WHERE id >
( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1)
LIMIT 10) T
ORDER BY id DESC;

INNER 优化 SQL

-- 采用INNER JOIN优化,JOIN子句里也优先从索引获取ID列表,然后直接关联查询获得最终结果,这里不需要加10
SELECT * FROM `t1`
INNER JOIN ( SELECT id FROM `t1`
WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2
USING (id);

  1、要学着使用 EXPLAIN 对 SQL 进行优化调整
  2、推荐使用 INNER 优化 SQL
  3、发现了一个 ubuntu 中监控 MySQL 的 mytop 命令,刚刚安装还没细研究

sudo apt-get install mytop

  4、USING (id) 如果两个表的字段名都一样,那么可以用 using(字段名) 来协商条件,效果跟 on a.id = b.id 一样

Reference: