soar 启发规则汇总 && 常见 MySQL 优化案例

admin管理员组

文章数量:1579362

前言

最近偶然翻翻一些博客，发现依然有一些介绍 mysql 常见优化场景的东西，甚是有趣。想起了之前在公司做的 SQL 规范相关工作。独乐了不如众乐乐，独学习不如众分享，跟大家分享下自己在这个环节的一些心得。

之前无非是根据一些经验和书籍，列出常见的场景。直到有一次看到了小米的开源工具，SOAR，简直是被震惊的感觉。这个工具通过是 SQL 语法树的分析，结合小米 DBA 多年经验的总结，进行了一系列启发规则的校验。最后给出 SQL 的优化建议，甚是好用。

当然，本篇文章不会介绍 SOAR 的具体使用，我们来聊聊那些 DBA 总结出来的启发规则。根据启发规则，大家也能解决平时遇到的相关 SQL 问题。

关于 SOAR 的使用和二次开发，如果大家有兴趣，欢迎留言。如果有需要，可以和博主交流~~

---

-------- 美丽的分割线 -------

---

摘自： https://github/XiaoMi/soar/blob/master/doc/heuristic.md

这是小米 soar 的默认启发规则汇总，也是 DBA 多年精华总结。熟读各个案例，对于一般的 MySQL 优化有很高的帮助。
如果你不喜欢太理论的东西，或者没时间去深入，举一反三学习也未尝不可。

---

启发式规则建议

建议使用 AS 关键字显示声明一个别名

• Item:ALI.001
• Severity:L0
• Content:在列或表别名(如"tbl AS alias")中, 明确使用 AS 关键字比隐含别名(如"tbl alias")更易懂。
• Case:

select name from tbl t1 where id < 1000

不建议给列通配符’*'设置别名

• Item:ALI.002
• Severity:L8
• Content:例: "SELECT tbl.* col1, col2"上面这条 SQL 给列通配符设置了别名，这样的SQL可能存在逻辑错误。您可能意在查询 col1, 但是代替它的是重命名的是 tbl 的最后一列。
• Case:

select tbl.* as c1,c2,c3 from tbl where id < 1000

别名不要与表或列的名字相同

• Item:ALI.003
• Severity:L1
• Content:表或列的别名与其真实名称相同, 这样的别名会使得查询更难去分辨。
• Case:

select name from tbl as tbl where id < 1000

修改表的默认字符集不会改表各个字段的字符集

• Item:ALT.001
• Severity:L4
• Content:很多初学者会将 ALTER TABLE tbl_name [DEFAULT] CHARACTER SET ‘UTF8’ 误认为会修改所有字段的字符集，但实际上它只会影响后续新增的字段不会改表已有字段的字符集。如果想修改整张表所有字段的字符集建议使用 ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
• Case:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

同一张表的多条 ALTER 请求建议合为一条

• Item:ALT.002
• Severity:L2
• Content:每次表结构变更对线上服务都会产生影响，即使是能够通过在线工具进行调整也请尽量通过合并 ALTER 请求的试减少操作次数。
• Case:

ALTER TABLE tbl ADD COLUMN col int, ADD INDEX idx_col (`col`);

删除列为高危操作，操作前请注意检查业务逻辑是否还有依赖

• Item:ALT.003
• Severity:L0
• Content:如业务逻辑依赖未完全消除，列被删除后可能导致数据无法写入或无法查询到已删除列数据导致程序异常的情况。这种情况下即使通过备份数据回滚也会丢失用户请求写入的数据。
• Case:

ALTER TABLE tbl DROP COLUMN col;

删除主键和外键为高危操作，操作前请与 DBA 确认影响

• Item:ALT.004
• Severity:L0
• Content:主键和外键为关系型数据库中两种重要约束，删除已有约束会打破已有业务逻辑，操作前请业务开发与 DBA 确认影响，三思而行。
• Case:

ALTER TABLE tbl DROP PRIMARY KEY;

不建议使用前项通配符查找

• Item:ARG.001
• Severity:L4
• Content:例如 “％foo”，查询参数有一个前项通配符的情况无法使用已有索引。
• Case:

select c1,c2,c3 from tbl where name like '%foo'

没有通配符的 LIKE 查询

• Item:ARG.002
• Severity:L1
• Content:不包含通配符的 LIKE 查询可能存在逻辑错误，因为逻辑上它与等值查询相同。
• Case:

select c1,c2,c3 from tbl where name like 'foo'

参数比较包含隐式转换，无法使用索引

• Item:ARG.003
• Severity:L4
• Content:隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严重。
• Case:

SELECT * FROM sakila.film WHERE length >= '60';

IN (NULL)/NOT IN (NULL) 永远非真

• Item:ARG.004
• Severity:L4
• Content:正确的作法是 col IN (‘val1’, ‘val2’, ‘val3’) OR col IS NULL
• Case:

SELECT * FROM tb WHERE col IN (NULL);

IN 要慎用，元素过多会导致全表扫描

• Item:ARG.005
• Severity:L1
• Content: 如：select id from t where num in(1,2,3)对于连续的数值，能用 BETWEEN 就不要用 IN 了：select id from t where num between 1 and 3。而当 IN 值过多时 MySQL 也可能会进入全表扫描导致性能急剧下降。
• Case:

select id from t where num in(1,2,3)

应尽量避免在 WHERE 子句中对字段进行 NULL 值判断

• Item:ARG.006
• Severity:L1
• Content:使用 IS NULL 或 IS NOT NULL 将可能导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null;可以在num上设置默认值0，确保表中 num 列没有 NULL 值，然后这样查询： select id from t where num=0;
• Case:

select id from t where num is null

避免使用模式匹配

• Item:ARG.007
• Severity:L3
• Content:性能问题是使用模式匹配操作符的最大缺点。使用 LIKE 或正则表达式进行模式匹配进行查询的另一个问题，是可能会返回意料之外的结果。最好的方案就是使用特殊的搜索引擎技术来替代 SQL，比如 Apache Lucene。另一个可选方案是将结果保存起来从而减少重复的搜索开销。如果一定要使用SQL，请考虑在 MySQL 中使用像 FULLTEXT 索引这样的第三方扩展。但更广泛地说，您不一定要使用SQL来解决所有问题。
• Case:

select c_id,c2,c3 from tbl where c2 like 'test%'

OR 查询索引列时请尽量使用 IN 谓词

• Item:ARG.008
• Severity:L1
• Content:IN-list 谓词可以用于索引检索，并且优化器可以对 IN-list 进行排序，以匹配索引的排序序列，从而获得更有效的检索。请注意，IN-list 必须只包含常量，或在查询块执行期间保持常量的值，例如外引用。
• Case:

SELECT c1,c2,c3 FROM tbl WHERE c1 = 14 OR c1 = 17

引号中的字符串开头或结尾包含空格

• Item:ARG.009
• Severity:L1
• Content:如果 VARCHAR 列的前后存在空格将可能引起逻辑问题，如在 MySQL 5.5中 ‘a’ 和 'a ’ 可能会在查询中被认为是相同的值。
• Case:

SELECT 'abc '

不要使用 hint，如：sql_no_cache, force index, ignore key, straight join等

• Item:ARG.010
• Severity:L1
• Content:hint 是用来强制 SQL 按照某个执行计划来执行，但随着数据量变化我们无法保证自己当初的预判是正确的。
• Case:

SELECT * FROM t1 USE INDEX (i1) ORDER BY a;

不要使用负向查询，如：NOT IN/NOT LIKE

• Item:ARG.011
• Severity:L3
• Content:请尽量不要使用负向查询，这将导致全表扫描，对查询性能影响较大。
• Case:

select id from t where num not in(1,2,3);

一次性 INSERT/REPLACE 的数据过多

• Item:ARG.012
• Severity:L2
• Content:单条 INSERT/REPLACE 语句批量插入大量数据性能较差，甚至可能导致从库同步延迟。为了提升性能，减少批量写入数据对从库同步延时的影响，建议采用分批次插入的方法。
• Case:

INSERT INTO tb (a) VALUES (1), (2)

最外层 SELECT 未指定 WHERE 条件

• Item:CLA.001
• Severity:L4
• Content:SELECT 语句没有 WHERE 子句，可能检查比预期更多的行(全表扫描)。对于 SELECT COUNT(*) 类型的请求如果不要求精度，建议使用 SHOW TABLE STATUS 或 EXPLAIN 替代。
• Case:

select id from tbl

不建议使用 ORDER BY RAND()

• Item:CLA.002
• Severity:L3
• Content:ORDER BY RAND() 是从结果集中检索随机行的一种非常低效的方法，因为它会对整个结果进行排序并丢弃其大部分数据。
• Case:

select name from tbl where id < 1000 order by rand(number)

不建议使用带 OFFSET 的LIMIT 查询

• Item:CLA.003
• Severity:L2
• Content:使用 LIMIT 和 OFFSET 对结果集分页的复杂度是 O(n^2)，并且会随着数据增大而导致性能问题。采用“书签”扫描的方法实现分页效率更高。
• Case:

select c1,c2 from tbl where name=xx order by number limit 1 offset 20

不建议对常量进行 GROUP BY

• Item:CLA.004
• Severity:L2
• Content:GROUP BY 1 表示按第一列进行 GROUP BY。如果在 GROUP BY 子句中使用数字，而不是表达式或列名称，当查询列顺序改变时，可能会导致问题。
• Case:

select col1,col2 from tbl group by 1

ORDER BY 常数列没有任何意义

• Item:CLA.005
• Severity:L2
• Content:SQL 逻辑上可能存在错误; 最多只是一个无用的操作，不会更改查询结果。
• Case:

select id from test where id=1 order by id

在不同的表中 GROUP BY 或 ORDER BY

• Item:CLA.006
• Severity:L4
• Content:这将强制使用临时表和 filesort，可能产生巨大性能隐患，并且可能消耗大量内存和磁盘上的临时空间。
• Case:

select tb1.col, tb2.col from tb1, tb2 where id=1 group by tb1.col, tb2.col

ORDER BY 语句对多个不同条件使用不同方向的排序无法使用索引

• Item:CLA.007
• Severity:L2
• Content:ORDER BY 子句中的所有表达式必须按统一的 ASC 或 DESC 方向排序，以便利用索引。
• Case:

select c1,c2,c3 from t1 where c1='foo' order by c2 desc, c3 asc

请为 GROUP BY 显示添加 ORDER BY 条件

• Item:CLA.008
• Severity:L2
• Content:默认 MySQL 会对 ‘GROUP BY col1, col2, …’ 请求按如下顺序排序 ‘ORDER BY col1, col2, …’。如果 GROUP BY 语句不指定 ORDER BY 条件会导致无谓的排序产生，如果不需要排序建议添加 ‘ORDER BY NULL’。
• Case:

select c1,c2,c3 from t1 where c1='foo' group by c2

ORDER BY 的条件为表达式

• Item:CLA.009
• Severity:L2
• Content:当 ORDER BY 条件为表达式或函数时会使用到临时表，如果在未指定 WHERE 或 WHERE 条件返回的结果集较大时性能会很差。
• Case:

select description from film where title ='ACADEMY DINOSAUR' order by length-language_id;

GROUP BY 的条件为表达式

• Item:CLA.010
• Severity:L2
• Content:当 GROUP BY 条件为表达式或函数时会使用到临时表，如果在未指定 WHERE 或 WHERE 条件返回的结果集较大时性能会很差。
• Case:

select description from film where title ='ACADEMY DINOSAUR' GROUP BY length-language_id;

建议为表添加注释

• Item:CLA.011
• Severity:L1
• Content:为表添加注释能够使得表的意义更明确，从而为日后的维护带来极大的便利。
• Case:

CREATE TABLE `test1` (`ID`</

本文标签： 启发规则常见案例soar