武汉网站建设网站开发2345网址导航中国最好
- 大偏移量查询缓慢?
- LIMIT: 会进行两步操作
- 性能消耗在哪里了?
- OFFSET操作
- 问题 2
- LIMIT 操作
- 如何优化?
大偏移量查询缓慢?
示例:(假设age字段有索引)
SELECT * FROM test WHERE age>18 LIMIT 10000000 ,10;
分析MySQL的 LIMIT 10000000 , 10
LIMIT: 会进行两步操作
- OFFSET:跳过多少行数据
- LIMIT: 取多少行数据
性能消耗在哪里了?
我们针对三部操作看性能消耗在哪里了
OFFSET操作
OFFSET是跳过多少行数据,
例子:
比如 OFFSET 10000000并不是直接从 10000001 开始计数,
而是整整要从第1个扫描到 10000001 个数据,然后开始计数)
注意区别
ORDER id OFFSET 10000000 是跳过 id 的前面的10000000 条数据从 10000001条开始,需要扫描到 10000001 条
WHERE id >10000000: 是直接定位到 id=10000000 的数据取大于它的数据
问题 2
直接 OFFSET 是全表扫描.
所以 OFFSET 越大,需要扫码的数据行数越多,消耗越大.
LIMIT 操作
LIMIT 10 操作本身是没太大消耗的,就是查询数据的时候只取多少条数据(这里是取 10 条),主要是前面的ORDER回表与OFFSET行数跨越的消耗.
如何优化?
主要有两条路线:
- 避免全表扫描
使用覆盖索引(子查询)去避免全表扫描
--普通分页(大)
--耗时: 1.6s
EXPLAIN SELECT * FROM `user` LIMIT 9000002,10;SELECT * FROM `user` LIMIT 9000002,10;
普通的 explain
ALL 表示全表扫描的,性能很低
--覆盖索引
--耗时: 944ms
EXPLAIN SELECT * FROM `user` a JOIN (SELECT id FROM `user` LIMIT 9000001,10
) AS b ON a.id = b.id;SELECT * FROM `user` a JOIN (SELECT id FROM `user` LIMIT 9000001,10
) AS b ON a.id = b.id;
覆盖索引分析
-
内部的子查询
(SELECT id FROM user LIMIT 9000001,10): 使用的 index(索引),而不是 ALL(全表扫描)性能高很多 -
后面的 join 因为只有 10 行数据,性能消耗并不高
-
a.id=b.id 使用的(eq_ref)并且只有 10 行数据,开销几乎可以忽略(相对于 LIMIT 9000001,10)
-
我们可以单独测试子查询的耗时
-- 子查询单独测试
--耗时: 1s
EXPLAIN SELECT id FROM `user` LIMIT 9000001,10;
SELECT id FROM `user` LIMIT 9000000,10;
子查询几乎占了查询命令中 100% 的时耗,其他的 join 操作几乎 0 时耗
结论:
1. 使用覆盖索引(index)查询比直接 全表查询性能优越非常多,(我这里是 40%)
2. 最耗时的还是 OFFSET 操作(60% 的性能无法跨越)
- 尽量不要使用 OFFSET 大偏移量查询,而是使用 where 快速定位.
--耗时: 19ms
SELECT * FROM `user` WHERE id> 9000004 LIMIT 10;
结论
- 使用 where 精确定位几乎0 损耗(性能比 覆盖索引的 OFFSET 高近50 倍)
参考:
https://juejin.cn/post/7270800456862466087
https://www.51cto.com/article/683765.html
https://blog.csdn.net/hellokitty_nba/article/details/123824417
https://juejin.cn/post/7094807113364406309