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。

相关文章

详解nginx 中location和 proxy_pass的匹配规则

详解nginx 中location和 proxy_pass的匹配规则

《详解nginx中location和proxy_pass的匹配规则》location是Nginx中用来匹配客户端请求URI的指令,决定如何处理特定路径的请求,它定义了请求的路由规则,后续的配置（如... 目录location 的作用语法示例：location /www.chinasem.cntestproxy
阅读更多...
SpringBoot首笔交易慢问题排查与优化方案

SpringBoot首笔交易慢问题排查与优化方案

《SpringBoot首笔交易慢问题排查与优化方案》在我们的微服务项目中,遇到这样的问题：应用启动后,第一笔交易响应耗时高达4、5秒,而后续请求均能在毫秒级完成,这不仅触发监控告警,也极大影响了用户体... 目录问题背景排查步骤1. 日志分析2. 性能工具定位优化方案：提前预热各种资源1. Flowable
阅读更多...
C/C++错误信息处理的常见方法及函数

C/C++错误信息处理的常见方法及函数

《C/C++错误信息处理的常见方法及函数》C/C++是两种广泛使用的编程语言,特别是在系统编程、嵌入式开发以及高性能计算领域,：本文主要介绍C/C++错误信息处理的常见方法及函数,文中通过代码介绍... 目录前言1. errno 和 perror()示例：2. strerror()示例：3. perror(
阅读更多...
Ubuntu中远程连接Mysql数据库的详细图文教程

Ubuntu中远程连接Mysql数据库的详细图文教程

《Ubuntu中远程连接Mysql数据库的详细图文教程》Ubuntu是一个以桌面应用为主的Linux发行版操作系统,这篇文章主要为大家详细介绍了Ubuntu中远程连接Mysql数据库的详细图文教程,有... 目录1、版本2、检查有没有mysql2.1 查询是否安装了Mysql包2.2 查看Mysql版本2.
阅读更多...
基于SpringBoot+Mybatis实现Mysql分表

基于SpringBoot+Mybatis实现Mysql分表

《基于SpringBoot+Mybatis实现Mysql分表》这篇文章主要为大家详细介绍了基于SpringBoot+Mybatis实现Mysql分表的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录基本思路定义注解创建ThreadLocal创建拦截器业务处理基本思路1.根据创建时间字段按年进
阅读更多...
Python3.6连接MySQL的详细步骤

Python3.6连接MySQL的详细步骤

《Python3.6连接MySQL的详细步骤》在现代Web开发和数据处理中,Python与数据库的交互是必不可少的一部分,MySQL作为最流行的开源关系型数据库管理系统之一,与Python的结合可以实... 目录环境准备安装python 3.6安装mysql安装pymysql库连接到MySQL建立连接执行S
阅读更多...
SpringBoot3实现Gzip压缩优化的技术指南

SpringBoot3实现Gzip压缩优化的技术指南

《SpringBoot3实现Gzip压缩优化的技术指南》随着Web应用的用户量和数据量增加,网络带宽和页面加载速度逐渐成为瓶颈,为了减少数据传输量,提高用户体验,我们可以使用Gzip压缩HTTP响应,... 目录1、简述2、配置2.1 添加依赖2.2 配置 Gzip 压缩3、服务端应用4、前端应用4.1 N
阅读更多...
Go标准库常见错误分析和解决办法

Go标准库常见错误分析和解决办法

《Go标准库常见错误分析和解决办法》Go语言的标准库为开发者提供了丰富且高效的工具,涵盖了从网络编程到文件操作等各个方面,然而,标准库虽好,使用不当却可能适得其反,正所谓工欲善其事,必先利其器,本文将... 目录1. 使用了错误的time.Duration2. time.After导致的内存泄漏3. jsO
阅读更多...
springboot循环依赖问题案例代码及解决办法

springboot循环依赖问题案例代码及解决办法

《springboot循环依赖问题案例代码及解决办法》在SpringBoot中,如果两个或多个Bean之间存在循环依赖（即BeanA依赖BeanB,而BeanB又依赖BeanA）,会导致Spring的... 目录1. 什么是循环依赖？2. 循环依赖的场景案例3. 解决循环依赖的常见方法方法 1：使用 @La
阅读更多...
MySQL双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2