MySQL 开发规范

发布于 2022-09-22 23:12:18 字数 11585 浏览 4 评论 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] 表必备三字段:idcreate_timeupdate_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 tinyint1
数百岁unsigned smallint2
恐龙化石数千万岁unsigned int4
太阳约50亿年unsigned bigint8

[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。

参考

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

列表为空,暂无数据

关于作者

0 文章
0 评论
0 人气
更多

推荐作者

淹不死的鱼

文章 0 评论 0

zhangMack

文章 0 评论 0

爱的故事

文章 0 评论 0

linces

文章 0 评论 0

早乙女

文章 0 评论 0

鸵鸟症

文章 0 评论 0

    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击“接受”或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。