背景

因为工作岗位的原因，负责制定了关于后端组数据库的规约规范，作为所有产品线的规范，历经几版的修改，最终形成下边的文本，规范在整个后端执行也有大半年的时间，对于整个团队在开发阶段就减少不恰当的建表语句、错误SQL、错误的索引有积极的意义，故分享出来给大家参考。

下边分为建表规约、SQL规约、索引规约三个部分，每部分的每一条都有强制、建议两个级别，大家在参考时，根据自己公司的情况来权衡。

 

一、建表规约

【强制】（1） 存储引擎必须使用InnoDB

解读：

• InnoDB支持事物、行级锁、并发性能更好
• CPU及内存缓存页优化使得资源利用率更高。

【强制】（2）每张表必须设置一个主键ID，且这个主键ID使用自增主键（在满足需要的情况下尽量短），但是业务一定不能依赖这个自增的特性。

解读：

• 1、由于InnoDB组织数据的方式决定了需要有一个主键，而且若是这个主键ID是单调递增的可以有效提高插入的性能，避免过多的页分裂、减少表碎片提高空间的使用率。
• 2、而在分库分表环境下，则需要统一来分配各个表中的主键值，从而避免整个逻辑表中主键重复。
• 3、在mysql中有多种可能性将导致自增主键出现“空洞”，例如事务回滚、批量“预申请”、唯一键冲突等等原因

【强制】（3）创建库、表时必须使用utf8mb4字符集，排序规则为utf8mb4_general_ci，因为我们的数据已经统一设置为utf8mb4，因此表和字段的字符集可以不再额外设置，保持默认与数据库配置相同即可。

解读：

• 1、在Mysql中的UTF-8并非“真正的UTF-8”，它的最大长度仅为三个字节，而utf8mb4”才是真正的“UTF-8”，四个字节能够正确存储Emoji符号。
• 2、避免了当关联查询时，因为字符集的不相同，导致无法使用**Index Nested-Loop Join**算法来优化查询，效率极低。

> CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
复制代码

【强制】(新增)（4）必须包含create_time、update_time数据列，且update_time字段由数据库自动维护。

解读：

• 1、按此规范设计的表，极大的方便BI部门进行增量数据同步，减少同步任务的数据量。
• 2、方便我们根据创建时间、更新时间来定位问题。

create_time TIMESTAMP NOT NULL COMMENT '创建时间',
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间，默认当前时间’,
复制代码

【强制】(修改)（5） 数据库表、表字段必须加入中文注释，若是type类型字段，最好将各种类型的含义也添加上。

解读：

• 1、当团队来新人或者是其他人需要使用到这些表，良好的注释可以帮助他人快速理解字段含义，提高开发效率。

【强制】（6） 库名、表名、字段名均小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用。

解读：约定

【建议】（7）单表列数目应该小于30，若超过则应该考虑将表拆分

解读：

• 1、单表列数太多使得Mysql服务器处理InnoDB返回数据之间的映射成本太高

【建议】（8）字段尽量定义为NOT NULL并且提供默认值；在满足业务场景的前提下，字段长度越短越好

解读：

• 1、NULL的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
• 2、NULL这种类型Msql内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
• 3、NULL值需要更多的存储空，无论是表还是索引中每行中的NULL的列都需要额外的空间来标识
• 4、常见字段类型的长度（以下字段长度均为不为空时的字段长度）
  • tinyint 1字节；smallint 2字节；int 4字节；bigint 8字节；
  • date 3字节；datetime 8字节；timestamp 4字节；
  • 字符类型括号中定义的数字为字符数，即varchar(32)可以存放32个数字或者汉字
  • 字符类型长度跟字符集有关（其中utf8占用3字节，utf8mb4占用4字节）

【建议】（9）如果存储的字符串长度几乎相等，使用CHAR定长字符串类型。

解读：

• 1、能够减少空间碎片，节省存储空间。
• 2、例如像MD5密码串、手机号、身份证号等等定长字符串就应该使用CHAR类型

【建议】（10）在一些场景下，考虑使用TIMESTAMP代替DATETIME。

解读：

• 1、这两种类型的都能表达"yyyy-MM-dd HH:mm:ss"格式的时间，TIMESTAMP只需要占用4个字节的长度，可以存储的范围为(1970-2038)年，在各个时区，所展示的时间是不一样的；
• 2、而DATETIME类型占用8个字节，对时区不敏感，可以存储的范围为(1001-9999)年。

【建议】（11）当心自动生成的Schema，尽量手动编写所有的Schema脚本。

解读：

• 1、各种数据库客户端其默认的字符集、字符排序等等可能不一致，这有可能导致一些字符集相关问题的产生。
• 2、使用手动编写的Schema可控性更高。

---

二、SQL规约

执行计划概述

我们可以通过explain + SQL可以查看语句的执行计划。下边只对我们比较关心的地方做重点叙述，简洁而又核心~

• Id：id 列数字越大越先执行，数字一样则从上而下执行
• Type：依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL，除了all之外，其他的type 都可以使用到索引，如果 type为all 就需要关注并优化SQL了。
• Key：查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个
• Key_len：处理查询的索引长度，前文中已经介绍过key_len的计算规则。ICP特性使用的索引不会计入
• Rows：这里是执行计划中估算的扫描行数，不是精确值
• Extra：如果你想要优化你的查询，那就要注意extra辅助信息中的using filesort和using temporary，这两项非常消耗性能，需要注意。

【建议】 (1) 为了充分利用缓存，不允许使用自定义函数、存储函数、用户变量。

解读：

• 1、如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果。

【强制】（2）在查询中指定所需的列，而不是直接使用“*”返回所有的列

解读：

• 1、读取不需要的列会增加CPU、IO、NET消耗
• 2、不能有效的利用覆盖索引

【强制】（3）避免使用属性隐式转换

解读：

• 1、什么是隐式转换呢？隐式转换就是当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。
• 2、在mysql这种使用字符串与数字比较时，两个参数都会被转换为浮点数再进行比较。
• 3、两个浮点数之间的比较不仅不会使用索引，甚至可能导致他们之间比较结果的不精确。

典型例子：

mysql> SELECT '1801537632024345812' = 1801537632024345812;
        -> 1
mysql> SELECT '1801537632024345812' = 1801537632024345813;
        -> 1
复制代码

【建议】（4）在WHERE条件的属性上不要使用函数或者表达式

解读：

• 1、Mysql无法自动解析这种表达式，无法使用到索引。
• 2、我们可以将数据查询到之后在应用层去做，不要交给Mysql

❌错误示范：

> select * from user where CHAR_LENGTH(mobile);
复制代码

【强制】（5）不允许使用%开头的模糊查询

解读：

• 1、根据索引的最左前缀原理，%开头的模糊查询无法使用索引，可以使用ES来做检索。

【建议】（6）最好不要干涉数据库的驱动表选择，让MySQL优化器自动选择最合适的表作为驱动表

解读：

• 1、在非外连接的情况下，我们可以使用STRAIGHT_JOIN的操作来自己选择驱动表，但是大多数情况下优化器根据过滤的数据集大小来选择一个合理的驱动表。

【建议】（新增）（7）不要使用【子查询 + in/not in】的写法，而是考虑使用join/left join关联查询代替。

解读：

• 1、在Mysql中的子查询实现相当糟糕，最糟糕的一类是相关性子查询（DEPENDENT SUBQUERY），它将相关的外层表压到子查询中，它认为这样可以更高效率地查找到数据行，但实际上并非如此。

---

索引规约

索引

建立索引的目的是为了减少扫描范围，提高查询速度。通过索引快速锁定数据范围，返回需要结果。一般情况下，如果单次扫描行数超过数据表总行数的一定比例（预估10%左右，官方并没有提供一个确定的数值），查询可能会放弃索引走全表扫描。因此必须要控制查询的数据范围，这也是一直强调的，如果不能限制数据查询范围，那么所有的优化都是徒劳的。 由于数据增删改都会额外维护索引信息，索引过多会降低数据表的DML速度，因此只在经常查询并且选择性高的列上创建索引。

【建议】（1）避免在更新比较频繁、区分度不高的列上单独建立索引

解读：

• 1、区分度不高的列单独创建索引的优化效果很小，但是较为频繁的更新则会让索引的维护成本更高，且可能会导致查询效率下降（例如出现index_merge）
• 2、例如在type，status，state等列上添加索引效果不大

【建议】（2） JOIN的表不允许超过五个。需要JOIN的字段，数据类型必须绝对一致; 多表关联查询时，保证被关联的字段需要有索引。

解读：

• 太多表的JOIN会让Mysql的优化器更难权衡出一个“最佳”的执行计划（可能性为表数量的阶乘），同时要注意关联字段的类型、长度、字符编码等等是否一致。

【强制】（3）在一个联合索引中，若第一列索引区分度等于1，那么则不需要建立联合索引。

• 解读：索引通过第一列就能够完全定位的数据，所以联合索引的后边部分是不需要的。

【强制】(新增)（4）在准备创建索引时，如果当前表中已经存在相同 重复的索引会需要更多的存储空间和维护代价，可考虑删除重复索引

解读：

• 如索引 index(A)和index(A,B)是重复的，重复的索引需要更多的存储空间和维护代价，可考虑删除index(A)。

【建议】（5）建立联合索引时，将区分度更高的字段放在左边，同时在也需要注意是否存在重复的索引。

解读：

• 1、假设创建了一个联合索引index(A,B,C)，就相当于创建了index(A,B,C)，index(A,B)，index(A) 三个索引
• 2、区分度更高的列放在左边，能够在一开始就有效的过滤掉无用数据。提高索引的效率，相应我们在Mapper中编写SQL的WHERE条件中有多个条件时，需要先看看当前表是否有现成的联合索引直接使用，注意各个条件的顺序尽量和索引的顺序一致。

区分度计算参考：

select count(distinct col1)/count(*), count(distinct col2)/ count(*)  from table_name;
复制代码

【建议】（6）利用覆盖索引来进行查询操作，避免回表

解读：

• 1、覆盖查询即是查询只需要通过索引即可拿到所需DATA，而不再需要再次回表查询，所以效率相对很高。我们在使用EXPLAIN的结果，extra列会出现："using index"。这里也要强调一下若业务不需要所有字段信息，则尽量不要使用“SELECT * ”，否则几乎不可能使用到覆盖索引。

【建议】（7）在较长VARCHAR字段,例如VARCHAR(100)上建立索引时，应指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

解读：

• 1、使用前缀索引就无法利用到覆盖索引，所以归根到底还是应该尽量降低字段的长度。
• 2、索引的长度与区分度是一对矛盾体，一般对字符串类型数据，若长度为20的索引，区分度会高达90%以上，则可以考虑创建长度例为20的索引，而非全字段索引。

如何确定前缀索引区分度：

> SELECT COUNT(DISTINCT LEFT(lesson_code, 20)) / COUNT(*) FROM lesson;
复制代码

【建议】（8）如果有ORDER BY的场景，请注意利用索引的有序性。ORDER BY最后的字段是联合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

解读：

• 1、假设有查询条件为WHERE a=? and b=? ORDER BY c； 存在索引：a_b_c，则此时可以利用索引排序。
• 2、反例：在查询条件中包含了范围查询，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引a_b无法排序。

【建议】（9）在where中索引的列不能某个表达式的一部分，也不能是函数的参数。

解读：

• 即是某列上已经添加了索引，但是若此列成为表达式的一部分、或者是函数的参数，Mysql无法将此列单独解析出来，索引也不会生效。

【建议】（10）我们在where条件中使用范围查询时，索引最多用于一个范围条件，超过一个则只会有一个条件能够使用到索引。

解读：

• 1、Mysql能够推断多个范围条件里边过滤效率最高的那个条件，然后使用这个字段的索引。

【建议】（11）在多个表进行外连接时，表之间的关联字段类型必须完全一致

解读：

• 1、当两个表进行Join时，字段类型若没有完全一致，则加索引也不会生效，这里的完全一致包括但不限于字段类型、字段长度、字符集、collection等等