江苏龙网

  1. 主页 > 生活百科 > >

深入探讨 MySQL 的 order by 优化( 二 )

优化mysql


深入探讨 MySQL 的 order by 优化

文章插图
分析:
利用shid=6的进行索引查询记录到了MySQL的ICP特性，无排序操作 。
2.4 不能利用索引排序的分析
案例一 order by语句使用了多个不同的索引
SELECT * FROM t1 ORDER BY key1, key2;
深入探讨 MySQL 的 order by 优化

文章插图
分析:
因为sql使用了不同的索引列，在存储上顺序存在不一致的可能性，MySQL会选择排序操作 。
特例因为所有的辅助索引里面都包含主键id，当where 字段加上order by字段沟通完整的索引时，可以避免filesort的 。
深入探讨 MySQL 的 order by 优化

文章插图
案例二当查询条件使用了与order by不同的其他的索引，且值为常量，但排序字段是另一个联合索引的非连续部分时
SELECT * FROM t1 WHERE key2=constant ORDER BY keypart1, keypart3;
深入探讨 MySQL 的 order by 优化

文章插图
分析:
与案例一一致，key2 的顺序语句key1(key_part1)存储排序不一样的情况下，MySQL 都会选择filesort。
案例三order by 语句使用了和组合索引默认不同的排序规则
SELECT * FROM t1 ORDER BY keypart1 DESC, keypart2 ASC;
官方文档中提示使用混合索引排序规则会导致额外排序，其实我们创建索引的时候可以做 (keypart1 DESC, keypart2 ASC)
案例四当where 条件中利用的索引与order by 索引不同时，与案例二有相似性 。
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
深入探讨 MySQL 的 order by 优化

文章插图
分析:
案例的sql 利用了idxtype 索引，但是order 使用了shid，gid 字段，没有包含在idxtype 索引里面，故不能利用idx_type索引排序 。
案例五order by 字段使用了表达式
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
深入探讨 MySQL 的 order by 优化

文章插图

深入探讨 MySQL 的 order by 优化

文章插图
分析:order by 的字段使用函数，和在where条件中使用函数索引一样，MySQL都无法利用到索引 。
案例六
The query joins many tables,and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows.(This is the first table in the EXPLAIN output that does not have a const join type.)
当查询语句是多表连接，并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表.(这是EXPLAIN输出中的没有使用const联接类型的第1个表)
深入探讨 MySQL 的 order by 优化

文章插图
分析:
出现join的情况下不能利用索引其实有很多种，只要对a的访问不满足上面说的可以利用索引排序的情况都会导致额外的排序动作 。但是当where + order 复合要求，order by 有包含了其他表的列就会导致额外的排序动作 。
案例七sql中包含的order by 列与group by 列不一致。
深入探讨 MySQL 的 order by 优化

文章插图
group by 本身会进行排序的操作,我们可以显示的注让group by不进行额外的排序动作 。
案例八索引本身不支持排序存储 比如，hash索引 。
深入探讨 MySQL 的 order by 优化

文章插图

深入探讨 MySQL 的 order by 优化

文章插图
分析
hash 索引本身不支持排序存储，故不能利用到排序特性，将表转化为innodb再次查询，避免了filesort 。
案例九order by的索引使用部分字符串 比如 key idx_name(name(2))
深入探讨 MySQL 的 order by 优化

文章插图
三 老生常谈的优化策略为了提高order by 查询的速度，尽可能的利用索引的有序性进行排序，如果不能利用索引排序的功能，那么我们只能退而求其次优化order by相关的缓存参数 。
  1. 增加 sort_buffer_size 大小，建议sort_buffer_size要足够大能够避免磁盘排序和合并排序次数 。
  2. 增加 read_rnd_buffer_size 大小 。
  3. 使用合适的列大小存储具体的内容，比如对于city字段 varchar(20)比varchar(200)能获取更好的性能 。
  4. 将tmpdir 目录指定到os上面有足够空间的具有比较高iops能力的存储上 。

【深入探讨 MySQL 的 order by 优化】


推荐阅读


上一篇：怎么泡红茶 有三点很重要

下一篇：茶叶干泡法 泡茶新技能