MySQL 开发规范

发布于 2022-09-22 23:12:18 字数 11585 浏览 133 评论 0

说明：[1] 强制 [2] 建议 [3] 参考。

基础规范

[2] 尽量不在数据库中做计算。

数据库的长处在数据的存储与检索，JAVA程序的长处在计算。
计算包括：字段类型转换、SQL函数等。

命名与注释规范

[1] 表名、字段名必须使用小写字母或数字每个单词之间用 _ 分割，禁止出现数字开头，禁止两个下划线中间只出现数字。

数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
正例：aliyun_admin，rdc_config，level3_name。反例：AliyunAdmin，rdcConfig，level_3_name。

[1] 表名不使用复数名词。

表名应该仅仅表示表里面的实体，不表示其数量，对应于 JAVA 类名也是单数形式，符合表达习惯。

[1] 禁用保留字，如 desc、range、match、delayed、order 等，请参考 MySQL 官方保留字。

[1] 主键索引名为 pk_字段名，唯一索引名为 uk_字段名，普通索引名则为 idx_字段名。

[1] 表注释和字段注释必须书写。

如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
枚举类型字段，必须说明各个枚举值的含义，比如 性别 0 无 1 男 2 女。

[1] id 的命名，在本表中叫 id，在引用表中叫 xyz_id。

例如用户表中的 id，就是用户 id，但是在订单表中，就使用 user_id 来表示用户 id。

建表规范

[1] 表存储引擎必须使用 InnoDB，表字符集默认使用 utf8mb4。

utf8 通用，无乱码风险，汉字3字节，英文1字节。
utf8mb4 是 utf8 的超集，有存储4字节例如表情符号时，使用它。
比如微信昵称中，很多人就有表情符号
utf8 编码时，注意字符统计函数的区别，亲自试验:

select length("轻松工作");           -- 返回为12
select character_length("轻松工作"); -- 返回为4

create table t_xyz (
    id unsigned bigint auto_increment primary key,
    -- 业务字段
   update_time datetime on update current_timestamp not null default current_timestamp comment '更新时间',
   create_time datetime not null default current_timestamp comment '创建时间'
)engine = innodb default charset = utf8mb4 comment ='表用途注释';

[1] 表字段类型 varchar(n)，必须满足 n >= 86

5.7 以后，方便后续在线无锁扩容，ALTER TABLE t1 CHANGE COLUMN name name VARCHAR(256), ALGORITHM=INPLACE, LOCK=NONE;
一个 utf8mb4 字符占4个字节，n >= 86 可以保证 varchar 使用2个字节来存储长度

[2] 表必备三字段：id, create_time, update_time。

其中id必为主键，类型为char(27)，由应用程序使用 ksuid 算法（无全局协调、固定27位、时间排序、BASE62编码）生成。
create_time, update_time的类型均为datetime类型(MySQL 5.7以上版本支持)。
create_time, update_time在增删改的时候，不需要处理（自动处理），只需要在查询时使用。

[1] 禁止使用存储过程，视图，触发器，Event。

对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，
不要交到数据库层调试，排错，迁移都比较困难，扩展性较差。

表设计规范

[1] 表必须有主键，推荐使用 UNSIGNED 整数为主键。

删除无主键的表，如果是 row 模式的主从架构，从库会挂住。

[2] 禁止使用外键，如果要保证完整性，应由应用程式实现。

外键使得表之间相互耦合，影响 update/delete 等 SQL 性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈。
以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。
如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。
外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；
外键影响数据库的插入速度。

[2] 建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据。

[2] 单表行数超过500万行或者单表容量超过 2GB，才推荐进行分库分表。

如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

列设计规范

[1] 在不同的库/表中，要保证所有存储相同数据的列名和列类型必须一致。

[1] 表中的列不要太多，尽量做到冷热数据分离，减小表的宽度。

减少表的宽度，可以让一页内存中容纳更多的行，进而减少磁盘IO，更有效的利用缓存。

[2] 经常一起使用的列尽量放到一个表中，避免过多的关联。

[2] 建议在表中建个3个预留字段，命名为 perserve1, preserve2, prserve3，类型为 varchar(128) null。

修改列的类型会所锁表。修改一个字段类型的成本要高于增加一个字段。

[2] 谨慎在数据库中存储图片、文件等。

[2] 谨慎使用 blob、text 及 enum 类型。

[3] 根据业务区分使用 int/bigint，分别会占用 1/4/8 字。

[3] 根据业务区分使用 char/varchar。

如果存储的字符串长度几乎相等，使用char定长字符串类型。
字段长度固定，或者长度近似的业务场景，适合使用 char，能够减少碎片，查询性能高。
字段长度相差较大，或者更新较少的业务场景，适合使用 varchar，能够减少空间。
varchar 类型虽然在硬盘上是动态长度的，但是在 jdbc 驱动的内存中占用的空间是固定的最大长度，在临时表和内存表中的 varchar，也是固定的最大长度。
varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，若大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

对象	年龄区间	类型	字节
人	150岁之内	unsigned tinyint	1
龟	数百岁	unsigned smallint	2
恐龙化石	数千万岁	unsigned int	4
太阳	约50亿年	unsigned bigint	8

[3] 根据业务区分使用日期时间类型 date/datetime/timestamp。禁止使用字符串替代。

datetime 占5字节，timetamp 占4字节，存储年使用 year，存储日期使用 date，存储时间使用 datetime。
date 类型以 YYYY-MM-DD 格式显示日期，支持范围从 1000-01-01 到 9999-12-31。
datetime 类型以 YYYY-MM-DD HH:MM:SS 格式显示日期，支持范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59
timestamp 类型以 YYYY-MM-DD HH:MM:SS 格式显示日期，支持范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC.

[2] 把字段定义为 not null 并设默认值。

null 的列使用索引，索引统计，值都更加复杂，MySQL 更难优化。
null 需要更多的存储空间。
null 只能采用 is null 或者 is not null，而在 =/!=/in/not in 时有大坑。
非字符类型字段，建议都设置成 not null default 0 的形式，然后在 Java 代码中使用 Primitive 类型变量（比如 int/long 等）而不是包装类型（例如：Integer/Long 等），例如：

create table t_xyz (
-- ...
`cycle_value` bigint(20) not null default 0 comment '限次周期',
)engine = innodb default charset = utf8mb4 comment ='表用途注释';

[3] 小数类型为 decimal，禁止使用 float 和 double。

float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。

[3] 使用 varchar(20) 存储手机号，不要使用整数。

牵扯到国家代号，可能出现 +/-/() 等字符，例如 +86。
手机号不会用来做数学运算。
varchar 可以模糊查询，例如 like ‘138%’。

[3] 使用 int 来代替 ENUM/状态。

ENUM 增加新值要进行 DDL 操作。

[1] 禁止使用 tinyint。

获取字段类型时，字段 tinyint(1) 的类型被当做 boolean 类型进行了返回。导致 java 中 Integer 类型无法进行强转（这个不同版本的驱动默认值不一样）。
同数据库转换类型不一样。

[3] ENUM/状态字段，在状态超过3种时，使用中文形容词，并且维护状态流转图，比如：新创建->待付款->待发货等。

[3] 对于某些布尔含义的字段（比如可见性、是否匿名等），直接使用 int，1 表示 true，0 表示 false。

[3] 表示范围 Range 的字段，采用左闭右开的原则。

比如开始时间为2018年04月19日00:00:00，到期时间为2018年04月20日00:00:00，表示范围为[2018年04月19日00:00:00, 2018年04月20日00:00:00)，
判断有效期代码为'2018年04月19日00:00:00' <= now() and now() < '2018年04月20日00:00:00'。JAVA中也同样采取左闭右开的原则。

[3] 字段允许适当冗余，以提高查询性能，但必须考虑数据一致。

冗余字段应遵循：
不是频繁修改的字段。
不是varchar超长字段，更不能是text字段。
正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。

[1] 字段不允许指定字符集。

索引规范

[2] 单张表索引数量建议控制在5个以内。

互联网高并发业务，太多索引会影响写性能。
生成执行计划时，如果索引太多，会降低性能，并可能导致 MySQL 选择不到最优索引。
异常复杂的查询需求，可以选择ES等更为适合的方式存储。

[2] 组合索引字段数不建议超过5个。

如果5个字段还不能极大缩小 row 范围，八成是设计有问题

[2] 不建议在频繁更新的字段上建立索引。

[2] 非必要不 join 查询，如果要 join 查询，被 join 的字段必须类型相同，并建立索引。

踩过因为 join 字段类型不一致，而导致全表扫描的坑么？

[2] 理解组合索引最左前缀原则，避免重复建设索引，如果建立了（a、b、c），相当于建立了 (a)、(a,b)、(a,b,c)。

建组合索引的时候，区分度最高的在最左边。
正例：如果 where a=? and b=? ，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>? and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

SQL 规范

[1] 禁止使用 select *，只获取必要字段。

select * 会增加 cpu/io/内存/带宽的消耗。
指定字段能有效利用索引覆盖。
尽量列出所有字段，特别是单表 id 查询时，这样映射到一个完整的 JavaBean，避免贫血。
指定字段查询，在表结构变更时，能保证对应用程序无影响。
以 ID（主键/唯一索引）查询时，带上所有的列名（满血模型，可以工具辅助）。

[1] insert 必须指定字段，禁止使用 insert into t_xyz values()。

指定字段插入，在表结构变更时，能保证对应用程序无影响。

[2] 隐式类型转换会使索引失效，导致全表扫描。

[2] 禁止在 where 条件列使用函数或者表达式。

导致不能命中索引，全表扫描。

[2] 禁止负向查询以及用双 % 号或前置 % 号的查询条件，这样无法利用到索引。

导致不能命中索引，全表扫描。

[2] 避免使用子查询，子查询会产生临时表，临时表没有任何索引，数据量大时严重影响效率。建议把子查询转化成关联查询。

[2] 禁止大表 join 和子查询。

[3] 超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。
防止因字段类型不同造成的隐式转换，导致索引失效。

[2] 同一个字段上的 or 必须改写为 in，in 的值必须少于 50 个。

[3] in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。

[3] 应用程序必须捕获 SQL 异常。

方便定位线上问题。

[2] 根据 ID（作为主键或者唯一索引）在表中进行查找时，不带其他非关键条件。

其它条件在代码中再次进行判断，这样可以本条 SQL 的重用率。比如：

-- 推荐写法
select card_no, user_id, state, available_balance from t_member_card where card_id = '#cardid#';

-- 不推荐写法
select card_no, user_id, state, available_balance from t_member_card where card_id = '#cardid#' and state = '正常';

[3] 在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

[2] 在不需要去重的情况下，要使用 UNION ALL 代替 UNION。

NULL相关

[1] count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。详见 count 说明。

[1] 当某一列 col 的值全是 NULL 时，count(col) 的返回结果为0，但 sum(col) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。

-- 可以使用如下方式来避免sum的NPE问题
select coalesce(sum(g), 0) from table;

[1] 使用 ISNULL() 来判断是否为 NULL 值。

说明：NULL 与任何值的直接比较都为 NULL。
1） NULL<>NULL 的返回结果是 NULL，而不是 false。
2） NULL=NULL 的返回结果是 NULL，而不是 true。
3） NULL<>1 的返回结果是 NULL，而不是 true。

编程相关

[1] 不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。

resultClass=”Hashtable”，会置入字段名和属性值，但是值的类型不可控。

[2] 尽量减少同数据库的交互次数，数据库更适合处理批量操作。

例如查询用户及用户的会员卡信息，先查询用户，然后根据用户id列表批量查询出用户的会员卡，而不是每个用户id都单独查询一次。
INSERT INTO VALUES(),(),()，合并插入。

安全相关

[2] 去掉 where 1=1 这样无意义或恒真的条件，如果遇到 update/delete 或遭到 sql 注入就恐怖了。

[1] SQL中不允许出现 DDL 语句。一般也不给予 create/alter 这类权限。

运维规范

[1] 数据订正（特别是删除、修改记录操作）时，要先 select 检查并备份，避免出现误删除，确认无误才能执行更新语句。

[3] truncate table 比 delete 速度快，且使用的系统和事务日志资源少，但 truncate 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。

truncate table 在功能上与不带 where 子句的 delete 语句相同。

[3] 涉及到较为复杂的数据升级操作时，建议编写临时用的专门的数据升级代码，在升级时执行一次，后续删除之。

防止遗漏执行。

[3] 临时表以 tmp_ 为前缀并以日期为后缀，如 tmp_t_xyz_20190219; 备份表以 bak_ 为前缀并以日期为后缀，如 bak_t_xyz_20190219。

[3] delete,update 语句改成 select 再 explain。

select 最多导致数据库慢，写操作才是锁表的罪魁祸首

[3] 任何新的 select,update,delete 上线，都要先 explain，看索引使用情况。

尽量避免 extra 列出现：Using File Sort，Using Temporary，rows 超过 1000 的要谨慎上线。

explain 解读

type：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
possible_keys：指出 MySQL 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
key：表示 MySQL 实际决定使用的键（索引）。如果没有选择索引，键是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX
ref：表示选择 key 列上的索引，哪些列或常量被用于查找索引列上的值
rows：根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort：MySQL 中无法利用索引完成的排序操作称为文件排序

[3] 大批量更新，如修复数据，避开高峰期，并通知 DBA。直接执行 sql 的由运维或 DBA 同事操作。

[2] 定期查看并分析慢 SQL。