MySQL 设计和使用规范

## 1. 库表字段设计规范

- 【必须】库/表/字段的字符集必须保持一致
- 【必须】**所有表必须要有主键，主键不能使用更新频繁的列**，以定长类型作为主键或者联合主键的第一字段
- 【必须】不使用 UUID MD5 HASH 这些值作为主键(一般使用连续递增的值作为主键，比如自增 ID) 【必须】存在自增列的表，自增列上必须存在一个单独的索引，若在复合索引中，自增列必须置于第一位
- 【建议】建表时应建立好合适的索引，并考虑未来的需求建立保留索引，避免上线后数据量增大再来创建新索引
- 【建议】建表时可以适当考虑未来功能需求，设计一些保留字段，避免频繁的 DB 增加字段 【建议】避免使用临时表
- 【建议】表中避免使用外键
- 【必须】**库名、表名、字段名避免使用 MySQL** **保留字**(如: BACKUP/CACHE/CODE 等) 详细参看官方文档 8.0 版本https://dev.mysql.com/doc/refman/8.0/en/keywords.html
- 【必须】所有字段均定义为 NOT NULL ，并设置 default 值 (NULL 字段很难优化查询，NULL 字段的索引需要额外空间，NULL 字段的复合索引无效)
- 【建议】避免单表太多字段, 最好不超过 32 个，尤其是版本不断迭代的情况下，如果字段过多，建议适当考虑分表而不是加字段
- 【建议】加字段尽量在表的末尾增加，避免使用 after/before 类 alter 操作
- 【建议】尽可能避免单个 blob 过大，实在不行请分表
- 【建议】如果使用到 text/blob 类型，单表避免使用超过 8 个 blob 字段；如果 blob 字段内容较多，程序考虑做压缩 (blob 超过 8 个建议使用 row_format=dynamic 行格式来存储)
- 【建议】整型字段, 如果数据量会很大,请使用 BIGINT,避免字段溢出(流水 ID/道具 ID 等)
- 【建议】日期字段建议用 DATETIME
- 【建议】强烈建议使用 TINYINT 来代替 ENUM 类型
- 【建议】遵循精确原则，用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数
- 【建议】建议使用 INT UNSIGNED 存储 IPV4
- 【建议】使用 UNSIGNED 存储非负整数
- 【必须】所有表/字段均应使用 comment 来描述此表/字段所代表的真正含义
- 【必须】禁止在数据库中存储明文密码，把密码在应用层做加密后存储(非对称算法最佳)
- 【必须】数据库中不允许存储视频或者照片等大文件，可以将大对象放到磁盘或者其他对象存储上

## 2. 索引规范

- 【必须】单表中索引数量不超过 5 个，过多的索引会影响 UPDATE/INSERT/DELETE 操作
- 【必须】单个索引中的字段数不超过 5 个
- 【必须】单表中的索引数禁止超过字段数
- 【必须】重要的 SQL 必须被索引，核心 SQL 优先考虑覆盖索引，避免回表 (说明：索引包含所有满足查询需要的数据的索引就是覆盖索引)
- 【必须】禁止重复索引(一个字段上建立多个索引) 例如表含有 `primary key a` `uniq index a` `index a`，则字段 a 被建了 3 个重复索引
- 【必须】禁止冗余索引(多个索引的前缀列相同，或在联合索引中包含了主键的索引) 例如表有索引 idx1(a,b,c)、idx2(a,b)，则 idx2 为冗余索引，因为 idx1 已经包含 idx2
- 【必须】`UPDATE/DELETE` 语句的 `WHERE` 条件列；`ORDER BY、GROUP BY、DISTINCT`的字段；多表关联的 JOIN 字段都应该要有索引
- 【必须】区分度最大的字段放在前面，符合最左前缀的特点建立索引
- 【必须】不在索引列进行数学运算和函数运算，否则无法使用索引/导致全表扫描。例子: `SELECT name FROM class WHERE ABS(score) > 80;`
- 【必须】不在低基数列上建立索引，例如“性别”、布尔值的列
- 【必须】索引字段的默认值不能为 NULL ，要改为其他的默认值，因 NULL 值对查询效率影响很大
- 【知会】对字符串使用前缀索引，前缀索引长度不超过 8 个字符，但在 ORDER BY 或 GROUP BY 中使用不到前缀索引。建立前缀索引的语法：`ALTER TABLE table_name ADD KEY(column_name(prefix_length));`
- 【知会】尽量不在 BLOB/TEXT 等字段上建立索引，且 BLOB 和 TEXT 类型的列只能创建前缀索引
- 【知会】合理创建联合索引(避免冗余)，减少维护索引的 IO 开销
- 【知会】使用不等于 (!=、<>、not in 、not like 等) 的时候，MYSQL 无法使用索引
- 【知会】使用 LIKE 操作的时候 where 条件以%开始 (如‘%abc’)时，MYSQL 无法使用索引，使用通配符尽量不要放在开头
- 【知会】多表关联中 join 条件字段类型不一致的时候无法使用索引

## 3. SQL 语句设计规范

- 【必须】MySQL 不擅长数学计算，不要把大计算量的 SQL 放在 MySQL 中执行。 如：统计/计算类操作避免从 DB 中直接计算
- 【必须】单次查询的结果集行数不要过多(控制在几百几千行)，如果结果集确实很多，注意控制查询频率(避免对 DB 机器的流量/IO/CPU 等产生压力)
- 【必须】避免使用 `CREATE table AS SELECT * FROM ...`的操作
- 【必须】程序层避免使用 `INSERT INTO … SELECT *`
- 【必须】不要用 `SELECT * FROM`，查询哪几个字段就 SELECT 几个字段
- 【必须】业务代码中事务及时提交，避免产生没必要的锁等待
- 【必须】SQL 中使用到 OR 的改写为用 IN()，且 in 的值最好不超过 1000 个
- 【必须】SQL 中避免隐式转换，会导致索引失效(这是一个大坑)，如：数值类型禁止加引号；字符串类型必须加引号
- 【必须】充分利用前缀索引，而且要符合最左前缀原则
- 【必须】不使用%前导的查询，如`like “%abc"`，会导致索引失效
- 【必须】不使用负向查询，如 NOT IN、NOT LIKE、!=、<>会导致无法使用索引，引起全表扫描，并且会把 NULL 查出来
- 【必须】UPDATE、DELETE 语句必须使用 WHERE 条件
- 【必须】清理历史数据时，不要一次清理过多，建议使用`DELETE … LIMIT N`分批清理
- 【必须】统计行数使用`COUNT(*)`，避免使用 COUNT(app)这样的操作
- 【必须】INSERT 语句必须指定字段列表，禁止使用`INSERT INTO TABLE()`
- 【必须】禁止使用`ORDER BY RAND()`
- 【建议】避免使用 JOIN 联表操作
- 【建议】避免使用触发器、函数、存储过程、event 等
- 【建议】如果确认合并的两个结果集中不包含重复的数据，那么请使用 UNION ALL 而不是 UNION
- 【建议】减少与数据库交互次数，尽量采用批处理 SQL 语句
- 【必须】防止 SQL 注入，程序应对传入的参数进行严格校验，正确过滤字符，而且避免使用动态拼装 SQL