MySQL LIMIT 查询优化

最近常在 SQL 中使用到 LIMIT ? ?，在执行 LIMIT 0, 1000 与 LIMIT 100000, 1000 时，查询速度明显有很大的区别，而且随着 LIMIT 的偏移量的增加，查询速度越来越慢。是否有办法对 SQL 中 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 一样

References

– EOF –