记录了下在日常工作中 MySQL开发过程中的一些基本规范，不断完善中。

基本规范

mysql5.5之前默认的存储的引擎是MYISAM，目前默认的是innodb（innodb好处支持事务，行级锁，高并发下性能更好，对多核，大内存硬件支持更好）

【强制】禁止使用线上库表做压力测试。

【推荐】注意控制单表数据量的大小，数据量特别大(通常数据量超过上亿条，占用空间超过 10G)会造成写入性能降低，表结构修改，备份和恢复都会有很大的性能隐患，需要应用根据业务特点，预估业务数据增涨速度，用历史数据归档和分库分表等手段来控制数据量大小。

【推荐】单条记录的大小不要超过8kb。

• 从索引角度来看，InnoDB的页块大小默认为16KB。这意味着每个页块中至少可以存放两行数据，这是为了保持B+树结构的有效性。因此，单行数据的大小受到了这个限制，通常不应超过页块大小的一半，即8KB。如果单行数据超过了这个限制，就会导致每个页块中只能存放一行数据，这会降低B+树的效率，使得索引的性能下降（每个页中只有一条数据，整个树就成了一条双向链表）。
• 从硬盘扇区大小的角度来看，硬盘的扇区大小通常为4KB，这意味着单条记录的大小不应超过扇区大小。如果单条记录过大，查询时可能会跨越多个扇区，增加磁盘I/O的开销，从而降低查询性能。

字段设计规范

通用

【强制】每个表必须有主键，小型系统可以使用MySQL本身的自增主键，大型系统、海外异地多机房场景、数据分库分表时使用公司统一的idgenerator。另外，考虑到未来数据膨胀的可能，主键 id 如果类型没有特殊要求，必须使用bigint unsigned，禁止使用int。

• 自增id：优点是简单，缺点是分表会有主键重复问题，并且如果对外漏出容易被外部感知到内部存储数据的数量，从而造成安全风险
• uuid：优点是使用uuid作为分片建可以使数据在分表时分布更加均匀，缺点是在innoDB引擎下，索引的底层是B+树，非叶子节点存储指针，只有叶子节点才会存储数据，一般主键也被叫做聚族索引（索引值和数据是紧密相连的)，如果使用uuid的话存储数据是无序的，这样查询效率会降低。如果在中间页中插入新的数据或者是修改数据，因为uuid是无序的，因此可能会破坏底层B+树的结构，可能会发生页分裂，这样就大幅度的增加了与硬盘的IO，同时也会出现数据碎片化，数据在物理存储上的布局不再连续，这会影响查询性能。

【强制】除唯一索引字段外，字段必须设置为NOT NULL，并为字段提供默认值，避免出现理解问题造成非预期结果。字符型的默认值为空字符值串’’，数值型默认值为 0，逻辑型的默认值为 0。

Mysql 允许唯一索引列插入多个NULL值，根据NULL的定义，NULL表示的是未知，因此两个NULL比较的结果既不相等，也不不等，结果仍然是未知。使用Null也会出现一些非预期结果

• 在进行count(filed)统计某列的记录数的时候，如果含有NULL值，系统会自动忽略掉不统计，但是空值是会进行统计的。
• NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空

【强制】每个字段和表必须提供清晰的注释。

【强制】创建表时必须包含审计字段『数据创建时间』、『数据更新时间』。

【强制】更新数据表记录时，必须同时更新记录对应的『数据更新时间』字段值为当前时间。

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

学生表和成绩表之间的关系是典型的主表（学生表）和从表（成绩表）关系，其中学生表中的学生ID（student_id）作为主键，在成绩表中作为外键。当更新学生表中的学生ID时，如果成绩表中的学生ID也需要相应更新，这就是级联更新。级联更新适用于单机低并发环境，因为它可能会引起强阻塞，并且在高并发的情况下可能导致数据库更新风暴，影响系统的性能和稳定性。此外，外键的存在也可能会影响数据库的插入速度，因为在插入记录时需要检查外键约束。在分布式和高并发集群环境下，级联更新通常不是最佳选择，因为它可能导致不必要的性能问题和系统风险。在这种情况下，通常会采用其他方式来处理数据一致性和关联更新的问题，例如应用程序层面的处理或者采用异步更新的方式。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致;多表关联查询时， 保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。

【推荐】不要设计过宽的表，一个表的字段应该控制在100个以内，同时注意数据的冷热分离。

MySQL在设计上有一些限制，其中包括每个表最多存储4096列以及每一行数据的大小不能超过65535字节（64KB）。这些限制对于数据库设计和性能优化都有一定的影响。在设计数据库时，需要考虑表的宽度，即表中的列数。过多的列可能导致表的宽度过大，从而增加将表加载到内存缓冲池中所需的内存量。这可能降低热数据的内存缓存命中率，并且也会增加磁盘I/O的开销，因为MySQL需要更多地从磁盘读取数据。为了减少磁盘I/O并提高热数据的内存缓存命中率，可以考虑以下几点：

• 垂直分割表：将表拆分成多个相关性较低的表，以减少单个表的列数。这样可以减小每个表的宽度，更有效地利用缓存。
• 水平分割表：将表按行进行分割，将不经常使用的列或者大型数据类型（如BLOB或TEXT）拆分到单独的表中，只在需要时进行关联查询。这样可以减小单行数据的大小，降低内存消耗和磁盘I/O。

【推荐】为了查询性能，字段可以适当冗余，但必须考虑数据一致性。冗余字段应避免是超长字符串类型，也尽量避免频繁修改

数值类型

【强制】表示状态字段(0-255)的使用 TINYINT UNSIGNED，禁止使用「枚举」类型，注释必须说明每个枚举值的含义等。

【强制】表示boolean类型的都使用TINYINT(1)（mysql本身是没有boolean类型的）在用gorm自动生成dal层代码的时候，DO对象的字段就是boolean类型，其余时候都使用TINYINT(4)

说明：TINYINT(m),这个括号里面的数值并不是表示使用多大空间存储（都是1字节），而是最大显示宽度，并且只有字段指定zerofill时有用，没有zerofill，(m)就是无用的,例如id BIGINT ZEROFILL NOT NULL，所以建表时就使用默认就好了，不需要加括号了，除非有特殊需求。

【强制】小数类型为 decimal，禁止使用 float 和 double。

说明：float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。 所以，金额相关类型数据必须使用decimal类型

类型	字节	表示范围
tinyint	1	无符号值: 0～255；有符号值： -128~127
smallint	2	无符号值: 0～65536；有符号值： -32768~32767
mediumint	3	无符号值: 0～16777215；有符号值： -8388608~8388607
int	4	无符号值: 0～4294967295；有符号值： -2147483648~2147483647
bigint	8	无符号值: 0~((2³²×²)-1)；有符号值： -(2³²×²)/2 ~ (2³²×²)/2-1

时间类型

【强制】时间型字段使用 DATETIME 类型，并在字段的注释中记录下对应的时区，时区的转换依赖应用程序来处理。禁止使用 TIMESTAMP 类型，否则在支持跨单元数据迁移时需要改造。

字符串类型

【推荐】合理设置字符存储长度，不但节约数据库表空间、节约索引存储，同时提升检索速度。比如字符串 VARCHAR(N)，是可变长字符串， 其中 N 表示字符个数，请尽量减少 N 的大小，长度不要超过 5000。如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

【推荐】避免使用 Blob 或 Text 类型，Blob 和 Text 类型所存储的数据量较大，删除和修改操作容易在数据表里产生大量的碎片。

【强制】所有的字符存储与表示，均用utf8mb4格式，兼容emoji。同时，字段字符集与表保持一致，不单独设置字符集。

命名规范

【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint ( 1表示是，0表示否)。

正例：是否过期的字段名 is_expired，1 表示过期，0 表示未过期。

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

tips：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母 正例：task_status, first_name 反例：TaskStatus，FirstName

【强制】表名不使用复数名词。

说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量。

【强制】禁用保留字，如desc、range、match 等，请参考 MySQL 官方保留字。

【强制】主键索引名为 pk_字段名，唯一索引名为 uk_字段名，普通索引名则为 idx_字段名。

说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。

【推荐】所有命名使用全名，有默认约定的除外，如果超过 20 个字符，可以使用缩写，尽量名字易懂简短，如information --> info、telephone --> phone 等 【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释

索引规范

【强制】业务上具有唯一特性的字段，必须建成唯一索引，其中唯一特性可能是多个字段的组合。

说明：唯一索引影响插入速度的损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，大概率有脏数据产生（比如绕开应用层代码直接提SQL工单插入）。

【推荐】在 varchar 字段上建立索引时，推荐指定索引长度，如果字段可能很长，确认是否有必要对全字段建立索引，这里需要根据实际文本的区分度决定索引长度。

说明：索引的长度与区分度之间确实存在一种平衡。索引的长度越长，能够存储的信息就越多，但同时也增加了索引的存储空间和维护成本。另一方面，索引的区分度表示索引在区分不同值方面的能力，区分度越高，索引就越能帮助数据库快速定位到所需的数据。因此，在设计索引时需要权衡索引长度和区分度，以达到最佳的性能和效率。对于字符串类型数据，可以通过使用 COUNT(DISTINCT ...) 函数方式来评估索引的区分度。通过对索引列进行 COUNT(DISTINCT ...) 操作，可以计算出不同值的数量，然后与总行数进行比较，得到区分度的估算。例如，可以使用 COUNT(DISTINCT LEFT(列名, 索引长度)) / COUNT(*) 来评估索引的区分度，其中 LEFT 函数用于截取字符串的前几个字符作为索引。一般来说，对于长度为 20 的索引，如果区分度可以达到 90% 以上

【推荐】文本搜索严禁左模糊或者全模糊。

说明：索引文件具有 B-Tree 最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

• 正例： where a=? and b=? order by c 索引：a_b_c
• 反例：索引中有范围查找，那么索引有序性无法利用，如： WHERE a>10 ORDER BY b；索引 a_b 无法排序

【推荐】有些业务场景下，可以合理利用覆盖索引来进行查询操作，避免回表可以提高性能。

说明： 能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的效果，用explain的结果，extra列会出现 using index。

【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。 正例：先快速定位需要获取的 id 段，然后再关联： SELECT a.* FROM table a, (select id from table where LIMIT 100000, 10 ) b where a.id=b.id

【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，const 最佳。

说明：

• consts 单表中最多只有一个匹配行（主键或者唯一索引）。
• ref 指的是使用普通的索引(normal index)。
• range 对索引进行范围检索。

【推荐】联合索引的查询条件是从左到右的顺序来实现的，使用建议如下：

• 区分度最高的列放在联合索引的最左侧
• 尽量把字段长度小的列放在联合索引的最左侧
• 使用最频繁的列放到联合索引的左侧
• 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如
  a>? and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
• 避免建立冗余索引和重复索引

举例（冗余索引）：有三个索引 index(a,b,c) index(a,b) index(a) ，如果 where a=? and b=? ，a 列几乎接近于唯一值，那么只需要单建 index(a,b,c) 索引即可

【建议】联合索引的字段数不能超过5个，单表的索引数量尽量控制在5个以内。

SQL规范

【强制】不要使用 count(列名)或 count(常量)来替代 count(*)，count()是 SQL92 定义的 标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明：

1. count(*)会统计包括值为 NULL 的所有行，而 count(列名)不会统计此列为 NULL 值的行。
2. count(distinct col) 计算该列除 NULL 之外的不重复行数，count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。

【推荐】数据删除和修改记录时，先 select，避免出现误删除，确认无误才能执行更新语句。

【推荐】in操作能避免则避免，实在避免不了，需仔细评估 in 后集合元素数，控制在1000个之内。

【推荐】不要写一个大而全的数据更新接口，不管是不是自己的目标更新字段，都进行update table set c1=value1,c2=value2,c3=value3

【推荐】线上系统避免使用 GROUP BY、DISTINCT 等语句的使用

【推荐】尽量避免或者拆分执行大事务。大事务的执行会给数据库稳定性带来很多问题。例如引起从库复制延迟、导致锁等待、系统脏数据 checkpoint 写入的性能抖动。