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。

相关文章

Mysql数据库聚簇索引与非聚簇索引举例详解

Mysql数据库聚簇索引与非聚簇索引举例详解

《Mysql数据库聚簇索引与非聚簇索引举例详解》在MySQL中聚簇索引和非聚簇索引是两种常见的索引结构,它们的主要区别在于数据的存储方式和索引的组织方式,：本文主要介绍Mysql数据库聚簇索引与非... 目录前言一、核心概念与本质区别二、聚簇索引（Clustered Index）1. 实现原理（以 Inno
阅读更多...
sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符

sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符

《sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符》：本文主要介绍sqlserver、mysql、oracle、pgsql、sqlite五大... 目录一、转义符1.1 oracle1.2 sqlserver1.3 PostgreSQL1.4 SQLi
阅读更多...
MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2