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 中的三种删除方式1.DELETE语句✅ 基本语法： 示例：2.TRUNCATE语句✅ 基本语
阅读更多...
MySQL中查找重复值的实现

MySQL中查找重复值的实现

《MySQL中查找重复值的实现》查找重复值是一项常见需求,比如在数据清理、数据分析、数据质量检查等场景下,我们常常需要找出表中某列或多列的重复值,具有一定的参考价值,感兴趣的可以了解一下... 目录技术背景实现步骤方法一：使用GROUP BY和HAVING子句方法二：仅返回重复值方法三：返回完整记录方法四：
阅读更多...
从入门到精通MySQL联合查询

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》：本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满
阅读更多...
MySQL查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...
Python中win32包的安装及常见用途介绍

Python中win32包的安装及常见用途介绍

《Python中win32包的安装及常见用途介绍》在Windows环境下,PythonWin32模块通常随Python安装包一起安装,：本文主要介绍Python中win32包的安装及常见用途的相关... 目录前言主要组件安装方法常见用途1. 操作Windows注册表2. 操作Windows服务3. 窗口操作
阅读更多...
mysql表操作与查询功能详解

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE
阅读更多...
MySQL中的锁机制详解之全局锁,表级锁,行级锁

MySQL中的锁机制详解之全局锁,表级锁,行级锁

《MySQL中的锁机制详解之全局锁,表级锁,行级锁》MySQL锁机制通过全局、表级、行级锁控制并发,保障数据一致性与隔离性,全局锁适用于全库备份,表级锁适合读多写少场景,行级锁(InnoDB)实现高并... 目录一、锁机制基础：从并发问题到锁分类1.1 并发访问的三大问题1.2 锁的核心作用1.3 锁粒度分
阅读更多...
MySQL数据库中ENUM的用法是什么详解

MySQL数据库中ENUM的用法是什么详解

《MySQL数据库中ENUM的用法是什么详解》ENUM是一个字符串对象,用于指定一组预定义的值,并可在创建表时使用,下面：本文主要介绍MySQL数据库中ENUM的用法是什么的相关资料,文中通过代码... 目录mysql 中 ENUM 的用法一、ENUM 的定义与语法二、ENUM 的特点三、ENUM 的用法1
阅读更多...
Python中re模块结合正则表达式的实际应用案例

Python中re模块结合正则表达式的实际应用案例

《Python中re模块结合正则表达式的实际应用案例》Python中的re模块是用于处理正则表达式的强大工具,正则表达式是一种用来匹配字符串的模式,它可以在文本中搜索和匹配特定的字符串模式,这篇文章主... 目录前言re模块常用函数一、查看文本中是否包含 A 或 B 字符串二、替换多个关键词为统一格式三、提
阅读更多...
MySQL count()聚合函数详解

MySQL count()聚合函数详解

《MySQLcount()聚合函数详解》MySQL中的COUNT()函数,它是SQL中最常用的聚合函数之一,用于计算表中符合特定条件的行数,本文给大家介绍MySQLcount()聚合函数,感兴趣的朋... 目录核心功能语法形式重要特性与行为如何选择使用哪种形式？总结深入剖析一下 mysql 中的 COUNT
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2