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

2023-10-16 16:58
文章标签 mysql 常见 优化 规则 汇总 案例 database 启发 soar

本文主要是介绍soar 启发规则汇总 常见 MySQL 优化案例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

前言

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

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

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

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

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

摘自： https://github.com/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 启发规则汇总 常见 MySQL 优化案例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

原文地址:
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.chinasem.cn/article/220276。

相关文章

如何解决Druid线程池Cause:java.sql.SQLRecoverableException:IO错误:Socket read timed out的问题

如何解决Druid线程池Cause:java.sql.SQLRecoverableException:IO错误:Socket read timed out的问题

《如何解决Druid线程池Cause:java.sql.SQLRecoverableException:IO错误:Socketreadtimedout的问题》：本文主要介绍解决Druid线程... 目录异常信息触发场景找到版本发布更新的说明从版本更新信息可以看到该默认逻辑已经去除总结异常信息触发场景复
阅读更多...
MyBatis编写嵌套子查询的动态SQL实践详解

MyBatis编写嵌套子查询的动态SQL实践详解

《MyBatis编写嵌套子查询的动态SQL实践详解》在Java生态中,MyBatis作为一款优秀的ORM框架,广泛应用于数据库操作,本文将深入探讨如何在MyBatis中编写嵌套子查询的动态SQL,并结... 目录一、Myhttp://www.chinasem.cnBATis动态SQL的核心优势1. 灵活性与可
阅读更多...
Python struct.unpack() 用法及常见错误详解

Python struct.unpack() 用法及常见错误详解

《Pythonstruct.unpack()用法及常见错误详解》struct.unpack()是Python中用于将二进制数据（字节序列）解析为Python数据类型的函数,通常与struct.pa... 目录一、函数语法二、格式字符串详解三、使用示例示例 1：解析整数和浮点数示例 2：解析字符串示例 3：解
阅读更多...
MySQL 表的内外连接案例详解

MySQL 表的内外连接案例详解

《MySQL表的内外连接案例详解》本文给大家介绍MySQL表的内外连接,结合实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录表的内外连接（重点）内连接外连接表的内外连接（重点）内连接内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我
阅读更多...
MySQL的ALTER TABLE命令的使用解读

MySQL的ALTER TABLE命令的使用解读

《MySQL的ALTERTABLE命令的使用解读》：本文主要介绍MySQL的ALTERTABLE命令的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、查看所建表的编China编程码格式2、修改表的编码格式3、修改列队数据类型4、添加列5、修改列的位置5.1、把列
阅读更多...
Mybatis嵌套子查询动态SQL编写实践

Mybatis嵌套子查询动态SQL编写实践

《Mybatis嵌套子查询动态SQL编写实践》：本文主要介绍Mybatis嵌套子查询动态SQL编写方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、实体类1、主类2、子类二、Mapper三、XML四、详解总结前言MyBATis的xml文件编写动态SQL
阅读更多...
解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

《解决mysql插入数据锁等待超时报错:Lockwaittimeoutexceeded;tryrestartingtransaction》：本文主要介绍解决mysql插入数据锁等待超时报... 目录报错信息解决办法1、数据库中执行如下sql2、再到 INNODB_TRX 事务表中查看总结报错信息Lock
阅读更多...
MySQL启动报错：InnoDB表空间丢失问题及解决方法

MySQL启动报错：InnoDB表空间丢失问题及解决方法

《MySQL启动报错：InnoDB表空间丢失问题及解决方法》在启动MySQL时,遇到了InnoDB:Tablespace5975wasnotfound,该错误表明MySQL在启动过程中无法找到指定的s... 目录mysql 启动报错：InnoDB 表空间丢失问题及解决方法错误分析解决方案1. 启用 inno
阅读更多...
MySQL 安装配置超完整教程

MySQL 安装配置超完整教程

《MySQL安装配置超完整教程》MySQL是一款广泛使用的开源关系型数据库管理系统（RDBMS）,由瑞典MySQLAB公司开发,目前属于Oracle公司旗下产品,：本文主要介绍MySQL安装配置... 目录一、mysql 简介二、下载 MySQL三、安装 MySQL四、配置环境变量五、配置 MySQL5.1
阅读更多...
MySQL 添加索引5种方式示例详解(实用sql代码)

MySQL 添加索引5种方式示例详解(实用sql代码)

《MySQL添加索引5种方式示例详解(实用sql代码)》在MySQL数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中,下面给大家分享MySQL添加索引5种方式示例详解(实用sql代码),... 在mysql数据库中添加索引可以帮助提高查询性能，尤其是在数据量大的表中。索引可以在创建表时定义，也可
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2