最近常在 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 命令,刚刚安装还没细研究
...