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

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

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

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

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

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

• Content: 表或列的别名与其真实名称相同，这样的别名会使得查询更难去分辨。

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

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

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

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

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

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

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

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

• Content: 例如 "％foo"，查询参数有一个前项通配符的情况无法使用已有索引。

10、没有通配符的 LIKE 查询

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

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

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

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

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

15、避免使用模式匹配

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

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

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

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

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

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

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

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

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

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

• Content: SQL 逻辑上可能存在错误；最多只是一个无用的操作，不会更改查询结果。

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

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

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

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

31、建议为表添加注释

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

32、将复杂的裹脚布式查询分解成几个简单的查询

• Content: SQL 是一门极具表现力的语言，您可以在单个 SQL 查询或者单条语句中完成很多事情。但这并不意味着必须强制只使用一行代码，或者认为使用一行代码就搞定每个任务是个好主意。通过一个查询来获得所有结果的常见后果是得到了一个笛卡儿积。当查询中的两张表之间没有条件限制它们的关系时，就会发生这种情况。没有对应的限制而直接使用两张表进行联结查询，就会得到第一张表中的每一行和第二张表中的每一行的一个组合。每一个这样的组合就会成为结果集中的一行，最终您就会得到一个行数很多的结果集。重要的是要考虑这些查询很难编写、难以修改和难以调试。数据库查询请求的日益增加应该是预料之中的事。经理们想要更复杂的报告以及在用户界面上添加更多的字段。如果您的设计很复杂，并且是一个单一查询，要扩展它们就会很费时费力。不论对您还是项目来说，时间花在这些事情上面不值得。将复杂的意大利面条式查询分解成几个简单的查询。当您拆分一个复杂的 SQL 查询时，得到的结果可能是很多类似的查询，可能仅仅在数据类型上有所不同。编写所有的这些查询是很乏味的，因此，最好能够有个程序自动生成这些代码。SQL 代码生成是一个很好的应用。尽管 SQL 支持用一行代码解决复杂的问题，但也别做不切实际的事情。
这是一条很长很长的 SQL，案例略。

• Content: 将查询的 HAVING 子句改写为 WHERE 中的查询条件，可以在查询处理期间使用索引。

• Content: 主键是数据表中记录的唯一标识符，不建议频繁更新主键列，这将影响元数据统计信息进而影响正常的查询。

• Content: 当表结构变更时，使用 * 通配符选择所有列将导致查询的含义和行为会发生更改，可能导致查询返回更多的数据。

39、建议修改自增 ID 为无符号类型

40、请为列添加默认值

• Content: 请为列添加默认值，如果是 ALTER 操作，请不要忘记将原字段的默认值写上。字段无默认值，当表较大时无法在线变更表结构。

• Content: 建议对表中每个列添加注释，来明确每个列在表中的含义及作用。

42、表中包含有太多的列

• Content: 为首先变长字段存储空间小，可以节省存储空间。其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

44、建议使用精确的数据类型

• Content: 实际上，任何使用 FLOAT, REAL 或 DOUBLE PRECISION 数据类型的设计都有可能是反模式。大多数应用程序使用的浮点数的取值范围并不需要达到 IEEE 754 标准所定义的最大 / 最小区间。在计算总量时，非精确浮点数所积累的影响是严重的。使用 SQL 中的 NUMERIC 或 DECIMAL 类型来代替 FLOAT 及其类似的数据类型进行固定精度的小数存储。这些数据类型精确地根据您定义这一列时指定的精度来存储数据。尽可能不要使用浮点数。

45、不建议使用 ENUM 数据类型

• Content: ENUM 定义了列中值的类型，使用字符串表示 ENUM 里的值时，实际存储在列中的数据是这些值在定义时的序数。因此，这列的数据是字节对齐的，当您进行一次排序查询时，结果是按照实际存储的序数值排序的，而不是按字符串值的字母顺序排序的。这可能不是您所希望的。没有什么语法支持从 ENUM 或者 check 约束中添加或删除一个值；您只能使用一个新的集合重新定义这一列。如果您打算废弃一个选项，您可能会为历史数据而烦恼。作为一种策略，改变元数据 —— 也就是说，改变表和列的定义 —— 应该是不常见的，并且要注意测试和质量保证。有一个更好的解决方案来约束一列中的可选值：创建一张检查表，每一行包含一个允许在列中出现的候选值；然后在引用新表的旧表上声明一个外键约束。

46、当需要唯一约束时才使用 NULL，仅当列不能有缺失值时才使用 NOT NULL

时，也就是说这列中的每一个值都必须存在且是有意义的。使用 NULL 来表示任意类型不存在的空值。 当您将一列声明为 NOT NULL 时，也就是说这列中的每一个值都必须存在且是有意义的。

49、为列指定了字符集

• Content: 建议列与表使用同一个字符集，不要单独指定列的字符集。

50、BLOB 类型的字段不可指定默认值

• Content: varchar 是可变长字符串，不预先分配存储空间，长度不要超过 255，如果存储长度过长 MySQL 将定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

• Content: 太多 DISTINCT 条件是复杂的裹脚布式查询的症状。考虑将复杂查询分解成许多简单的查询，并减少 DISTINCT 条件的数量。如果主键列是列的结果集的一部分，则 DISTINCT 条件可能没有影响。

• Content: 当表已经有主键时，对所有列进行 DISTINCT 的输出结果与不进行 DISTINCT 操作的结果相同，请不要画蛇添足。

56、避免在 WHERE 条件中使用函数或其他运算符

• Content: 虽然在 SQL 中使用函数可以简化很多复杂的查询，但使用了函数的查询无法利用表中已经建立的索引，该查询将会是全表扫描，性能较差。通常建议将列名写在比较运算符左侧，将查询过滤条件放在比较运算符右侧。也不建议在查询比较条件两侧书写多余的括号，这会对阅读产生比较大的困扰。

操作需要扫描大量的行才能获取精确的结果，性能也因此不佳。有时候某些业务场景并不需要完全精确的 COUNT 值，此时可以用近似值来代替。EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行 EXPLAIN 并不需要真正去执行查询，所以成本很低。

58、使用了合并为可空列的字符串连接

• Content: 在一些查询请求中，您需要强制让某一列或者某个表达式返回非 NULL 的值，从而让查询逻辑变得更简单，担忧不想将这个值存下来。使用 COALESCE () 函数来构造连接的表达式，这样即使是空值列也不会使整表达式变为 NULL。

62、不建议使用触发器

• Content: 触发器的执行没有反馈和日志，隐藏了实际的执行步骤，当数据库出现问题是，不能通过慢日志分析触发器的具体执行情况，不易发现问题。在 MySQL 中，触发器不能临时关闭或打开，在数据迁移或数据恢复等场景下，需要临时 drop 触发器，可能影响到生产环境。

63、不建议使用存储过程

• Content: 存储过程无版本控制，配合业务的存储过程升级很难做到业务无感知。存储过程在拓展和移植上也存在问题。

64、不建议使用自定义函数

• Content: 不建议使用自定义函数

65、不建议对等值查询列使用 GROUP BY

• Content: 表连接的时候混用逗号和 ANSI JOIN 不便于人类理解，并且 MySQL 不同版本的表连接行为和优先级均有所不同，当 MySQL 版本变化后可能会引入错误。

67、同一张表被连接两次

• Content: 相同的表在 FROM 子句中至少出现两次，可以简化为对该表的单次访问。

69、不建议使用排它 JOIN

• Content: 太多的 JOIN 是复杂的裹脚布式查询的症状。考虑将复杂查询分解成许多简单的查询，并减少 JOIN 的数量。

71、将嵌套查询重写为 JOIN 通常会导致更高效的执行和更有效的优化

• Content: 一般来说，非嵌套子查询总是用于关联子查询，最多是来自 FROM 子句中的一个表，这些子查询用于 ANY, ALL 和 EXISTS 的谓词。如果可以根据查询语义决定子查询最多返回一个行，那么一个不相关的子查询或来自 FROM 子句中的多个表的子查询就被压平了。

72、不建议使用联表删除或更新

• Content: 当需要同时删除或更新多张表时建议使用简单语句，一条 SQL 只删除或更新一张表，尽量不要将多张表的操作在同一条语句。

73、不要使用跨数据库的 JOIN 查询

• Content: 一般来说，跨数据库的 JOIN 查询意味着查询语句跨越了两个不同的子系统，这可能意味着系统耦合度过高或库表结构设计不合理。

74、建议使用自增列作为主键，如使用联合自增主键时请将自增键作为第一列

• Content: 建议使用自增列作为主键，如使用联合自增主键时请将自增键作为第一列

75、无主键或唯一键，无法在线变更表结构

• Content: 无主键或唯一键，无法在线变更表结构

76、避免外键等递归关系

存在递归关系的数据很常见，数据常会像树或者以层级方式组织。然而，创建一个外键约束来强制执行同一表中两列之间的关系，会导致笨拙的查询。树的每一层对应着另一个连接。您将需要发出递归查询，以获得节点的所有后代或所有祖先。解决方案是构造一个附加的闭包表。它记录了树中所有节点间的关系，而不仅仅是那些具有直接的父子关系。您也可以比较不同层次的数据设计：闭包表，路径枚举，嵌套集。然后根据应用程序的需要选择一个。

77、提醒：请将索引属性顺序与查询对齐

• Content: 如果为列创建复合索引，请确保查询属性与索引属性的顺序相同，以便 DBMS 在处理查询时使用索引。如果查询和索引属性订单没有对齐，那么 DBMS 可能无法在查询处理期间使用索引。

80、未指定主键或主键非 bigint

81、ORDER BY 多个列但排序方向不同时可能无法使用索引

82、添加唯一索引前请注意检查数据唯一性

• Content: 请提前检查添加唯一索引列的数据唯一性，如果数据不唯一在线表结构调整时将有可能自动将重复列删除，这有可能导致数据丢失。

83、全文索引不是银弹

• Content: 因为 SQL_CALC_FOUND_ROWS 不能很好地扩展，所以可能导致性能问题；建议业务使用其他策略来替代 SQL_CALC_FOUND_ROWS 提供的计数功能，比如：分页结果展示等。

85、不建议使用 MySQL 关键字做列名或表名

• Content: 当使用关键字做为列名或表名时程序需要对列名和表名进行转义，如果疏忽被将导致请求无法执行。

86、不建议使用复数做列名或表名

• Content: 表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。

87、不建议使用使用多字节编码字符 (中文) 命名

• Content: 为库、表、列、别名命名时建议使用英文，数字，下划线等字符，不建议使用中文或其他多字节编码字符。

• Content: 当主键为自增键时使用 INSERT ON DUPLICATE KEY UPDATE 可能会导致主键出现大量不连续快速增长，导致主键快速溢出无法继续写入。极端情况下还有可能导致主从数据不一致。

90、用字符类型存储 IP 地址

• Content: 字符串字面上看起来像 IP 地址，但不是 INET_ATON () 的参数，表示数据被存储为字符而不是整数。将 IP 地址存储为整数更为有效。

91、日期 / 时间未使用引号括起

92、一列中存储一系列相关数据的集合

• Content: 将 ID 存储为一个列表，作为 VARCHAR/TEXT 列，这样能导致性能和数据完整性问题。查询这样的列需要使用模式匹配的表达式。使用逗号分隔的列表来做多表联结查询定位一行数据是极不优雅和耗时的。这将使验证 ID 更加困难。考虑一下，列表最多支持存放多少数据呢？将 ID 存储在一张单独的表中，代替使用多值属性，从而每个单独的属性值都可以占据一行。这样交叉表实现了两张表之间的多对多关系。这将更好地简化查询，也更有效地验证 ID。

• Content: UPDATE/DELETE 操作使用 LIMIT 条件和不添加 WHERE 条件一样危险，它可将会导致主从数据不一致或从库同步中断。

• Content: 在一条 UPDATE 语句中，如果要更新多个字段，字段间不能使用 AND ，而应该用逗号分隔。

99、永远不真的比较条件

• Content: 查询条件永远非真，如果该条件出现在 where 中可能导致查询无匹配到的结果。

100、永远为真的比较条件

• Content: SELECT INTO OUTFILE 需要授予 FILE 权限，这通过会引入安全问题。LOAD DATA 虽然可以提高数据导入速度，但同时也可能导致从库同步延迟过大。

103、不使用明文存储密码

• Content: 使用明文存储密码或者使用明文在网络上传递密码都是不安全的。如果攻击者能够截获您用来插入密码的 SQL 语句，他们就能直接读到密码。另外，将用户输入的字符串以明文的形式插入到纯 SQL 语句中，也会让攻击者发现它。如果您能够读取密码，黑客也可以。解决方案是使用单向哈希函数对原始密码进行加密编码。哈希是指将输入字符串转化成另一个新的、不可识别的字符串的函数。对密码加密表达式加点随机串来防御 “字典攻击”。不要将明文密码输入到 SQL 查询语句中。在应用程序代码中计算哈希串，只在 SQL 查询中使用哈希串。

• Content: 在执行高危操作之前对数据进行备份是十分有必要的。

• Content: BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，且性能开销较大，请检查是否有必要使用

• Content: 请检查整形是否有负数场景，如无特殊场景，建议使用 unsigned

110、'!=' 运算符是非标准的

111、库名或表名点后建议不要加空格

• Content: 当使用 db.table 或 table.column 格式访问表或字段时，请不要在点号后面添加空格，虽然这样语法正确。

112、索引起名不规范

• Content: 建议普通二级索引以 idx_为前缀，唯一索引以 uniq_为前缀。

113、起名时请不要使用字母、数字和下划线之外的字符

• Content: 以字母或下划线开头，名字只允许使用字母、数字和下划线。请统一大小写，不要使用驼峰命名法。不要在名字中出现连续下划线 '__'，这样很难辨认。

114、MySQL 对子查询的优化效果不佳

• Content: MySQL 将外部查询中的每一行作为依赖子查询执行子查询。 这是导致严重性能问题的常见原因。这可能会在 MySQL 5.6 版本中得到改善，但对于 5.1 及更早版本，建议将该类查询分别重写为 JOIN 或 LEFT OUTER JOIN。

• Content: 与去除重复的 UNION 不同，UNION ALL 允许重复元组。如果您不关心重复元组，那么使用 UNION ALL 将是一个更快的选项。

• Content: DISTINCT 关键字在对元组排序后删除重复。相反，考虑使用一个带有 EXISTS 关键字的子查询，您可以避免返回整个表。

117、执行计划中嵌套连接深度过深

• Content: MySQL 对子查询的优化效果不佳，MySQL 将外部查询中的每一行作为依赖子查询执行子查询。 这是导致严重性能问题的常见原因。

119、不建议在子查询中使用函数

• Content: MySQL 将外部查询中的每一行作为依赖子查询执行子查询，如果在子查询中使用函数，即使是 semi-join 也很难进行高效的查询。可以将子查询重写为 OUTER JOIN 语句并用连接条件对数据进行过滤。

120、不建议使用分区表

121、请为表选择合适的存储引擎

• Content: 建表或修改表的存储引擎时建议使用推荐的存储引擎，如：innodb

122、以 DUAL 命名的表在数据库中有特殊含义

• Content: DUAL 表为虚拟表，不需要创建即可使用，也不建议服务以 DUAL 命名表。

124、请使用推荐的字符集

125、不建议使用视图

126、不建议使用临时表

内容简介：MySQL 处理插入过程中的主键唯一键重复值的解决方法处理插入过程中的主键唯一键重复值的解决方法本篇文章主要介绍在插入数据到表中遇到键重复避免插入重复值的处理方法，主要涉及到IGNORE,ON DUPLICATEKEY UPDATE,REPLACE的相关知识，感兴趣的朋友一起学习吧本篇文章主要介绍在插入数据到表中遇到键重复避免插入重复值的处理方法，主要涉及到IGNORE,ON