mysql数据库规范

1.建表规范

1.1 强制项

1.表名和字段名必须使用小写字母或数字,禁止出现数字开头,使用下划线'_'连接,同时禁止两个下划线中间只出现数字
# 说明: mysql在windows下不区分大小写,但在linux下默认区分大小写,因此数据库名,表名,字段名,都不允许出现任何大写字母,避免节外生枝
# 正例: aliyun_admin,rdc_config,level3_name
# 反例: AliyunAdmin,rdcConfig,level_3_name
2.表名不使用复数名词
# 说明: 表名应该仅仅表示表里面的实体内容,不应该表示实体数量
3.禁用保留字,如desc,range,match,delayed等,请参考mysql官方保留字
4.表名必须要以模块名作为前缀,便于后缀跟踪
# 正例: mall_truck_coupon,s4_shop_purchase_code
# 反例: truck_coupon,shop_purchase_code
# 各模块表名前缀如下:
# mall=>商城/营销,s4=>服务,pub=>公共,iot=>车联网,bd=>大数据,forum=>社区论坛,misc=>无法分类的表
# 例外情况: 如果某个模块有自己独立的数据库,当该模块在自己的数据库中建表时,不需要添加前缀
# 示例: 以sany_iot数据库为例,该数据库下与车联网模块相关的表,可以不加iot前缀.如果在sany_iot数据库创建与社区模块相关的表,则需要添加forum前缀
5.表和字段都要添加中文备注说明
6.表的备注中,要有创建人的中文名
# 正例:
CREAT TALBE `mall_cart` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
   `user_id` bigint(20) NOT NULL COMMENT '用户ID',
   `goods_id` bigint(20) NOT NULL COMMENT '商品ID',
   `product_id` bigint(20) NOT NULL COMMENT '产品ID',
   PRIMARY KEY (`id`)
) DEFAULT CHARSET=utfmb4 COMMENT='[张三]购物车';
# 反例:
CREAT TALBE `mall_cart` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
   `user_id` bigint(20) NOT NULL COMMENT '用户ID',
   `goods_id` bigint(20) NOT NULL COMMENT '商品ID',
   `product_id` bigint(20) NOT NULL COMMENT '产品ID',
   PRIMARY KEY (`id`)
) DEFAULT CHARSET=utfmb4 COMMENT='购物车';
7.所有表都要有这些字段(针对多对多的中间关联表除外):id,create_time,creator,modify_time,mender,字段说明如下:

8.布尔类型使用tinyint(1),对应关系为: 0=>false 1=>true
9.枚举类型统一使用tinyint(1),不使用mysql的enum类型
10.表达是与否概念的字段,必须使用is_xxx的方式命名,数据类型是unsigned tinyint(1),备注为(0:xxx 1:xxx)
# 说明: 任何字段如果为非负数,必须是unsigned,状态种类一般非常少,为节约空间,使用tinyint(1)即可
# 正例: 表达逻辑删除的字段名is_deleted,(0:未删除 1:已删除)
11.如果存储的字符串长度几乎相等,使用char定长字符串类型
12.varchar是可变长字符串,不预先分配存储空间,长度不要超过5000,如果存储长度大于此值,定义字段类型为text,独立出来一张表,用主键来对应,避免影响其它字段索引效率
13.小数类型为decimal,禁止使用float和double
# 说明: float和double在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确的结果,而如果存储的数据范围超过decimal的范围,建议将数据拆成整数和小数分开存储
# 对常用的百分比和金额的要求如下:
# 百分比: 总长度6位,整数2位,小数4位,即0.5065=>50.65%,1.255=>125.5%
# 金额: 总长度16位,整数14位,小数2位
14.原则上不允许物理删除数据,当确实需要做物理删除时,必须经过评审
15.禁止使用触发器和mysql作业
16.涉及与用户交互业务,禁止通过存储过程来实现,因为存储过程,触发器完成业务虽然高效,但它非常容易被遗忘,相关业务后续需要修改时,存储过程完成的部分逻辑很难被想起来

1.2 推荐项

1.库名与应用名称尽量一致
2.表名和字段名长度限制在30字符以内
3.表名不要加数据info,record,list,data这样画蛇添足式的后缀
# 正例: s4_sap_delivery_flow,mall_preorder_payment
# 反例: s4_sap_delivery_flow_record,mall_preorder_payment_data
4.表的命名最好是'业务名称_表的作用'的组合
# 正例: alipay_task,force_project,trade_config
5.运维人员根据运维需要所创建的表,表名格式为:{原表名}_{功能缩写}{日期},说明如下:
# {日期}: 生成该表的日期,不包含时间点,年份只取最后两位
# {功能缩写}如下:

6.所有表字符集均使用uft8mb4,字符排序使用utf8mb4_unicode_ci
# 正例:
CREATE TABLE `mall_cart` (
    `id` bigint(20) NOT NULL AUTO_INCREMENt COMMENT '主键',
    `user_id` bigint(20) NOT NULL COMMENT '用户ID',
    `goods_id` bigint(20) NOT NULL COMMENT '商品ID',
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='[张三]购物车';
# 反例:
CREATE TABLE `mall_cart` (
    `id` bigint(20) NOT NULL AUTO_INCREMENt COMMENT '主键',
    `user_id` bigint(20) NOT NULL COMMENT '用户ID',
    `goods_id` bigint(20) NOT NULL COMMENT '商品ID',
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='[张三]购物车';
7.表间只使用逻辑关联,不建数据库级别的外键
8.如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释
9.要修改字段名时,增加一个新字段,并将旧字段的数据迁移到新字段上来,待版本发布验证OK后,再删除旧的字段
10.通过字段名来区分时间类型
# date: 代表日期类型,比如reserve_date = 2018-08-12
# time: 代表时间戳类型,一般php项目使用int(10)表示,比如delivery_time = 1568269861
11.字段允许适当冗余,以提高查询性能,但必须考虑数据一致,冗余字段应遵循以下规则:
   11.1 不是频繁修改的字段
   11.2 不是varchar超长字段,更不能是text字段
   # 正例: 商品类目名称使用频率高,字段长度短,名称基本一成不变,可在相关联的表中冗余存储类目名称,避免关联查询
12.单表行数超过500万行或者单表容量超过2GB,才推荐进行分库分表
   # 说明: 如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表
13.对于存储过程,函数等程序块都要有异常处理部分,在异常部分的最后都要设置OTHERS异常情态处理器,以提高程序的自检能力,格式如下:
BEGIN
    ...
EXCEPTION
WHEN exception_name1 THEN
    ...
WHEN exception_name2 THEN
    ...
WHEN OTHERS THEN
    ...
END;
14.对于子程序,触发器,包等带名的程序块,要使用结束标识,如下:
CREATE OR REPLACE PROCEDURE XXXsp_XXX IS
...
BEGIN
...
END XXXsp_XXX;
# 最后一行XXXsp_XXX是可选的,规范要求写上,与块开始的CREATE相对应

1.3 参考项

# 合适的字符存储长度,不但节约数据库表空间,节约索引存储,更重要的是提升检索速度
# 正例: 如下表,其中无符号值可以避免误存负数,且扩大了表示范围

2.索引规范

1.业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引
# 说明: 不要以为唯一索引影响了insert速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生
2.超过三个表禁止join,需要join的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引
# 说明: 即使双表join也要注意表索引,SQL性能
3.在varchar字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可
# 说明: 索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20的索引,区分度会高达90%以上,可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定
4.页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决
# 说明: 索引文件具有B-Tree的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引
5.如果有order by的场景,请注意利用索引的有序性,order by最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort的情况,影响查询性能
# 正例: where a=? and b=? order by c; 索引: a_b_c
# 反例: 索引中有范围查找,那么索引有序性无法利用,如: WHERE a>10 ORDER BY b; 索引a_b无法排序
6.利用覆盖索引来进行查询操作,避免回表
# 说明: 如果一本书需要知道第11章是什么标题,会翻开第11章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用
# 正例: 能够建立索引的种类分为主键索引,唯一索引,普通索引三种,而覆盖索引只是一种查询的一种效果,用explain的结果,extra列会出现: using index
7.利用延迟关联或者子查询优化超多分页场景
# 说明: MySQL并不是跳过offset行,而是取offset+N行,然后返回放弃前offset行,返回N行,那当offset特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写
# 正例: 先快速定位需要获取的id段,然后再关联:
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
8.SQL性能优化的目标: 至少要达到range级别,要求是ref级别,如果可以是consts最好
# 说明:
    8.1 consts单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据
    8.2 ref指的是使用普通的索引(normal index)
    8.3 range对索引进行范围检索
    # 反例: explain表的结果,type=index,索引物理文件全扫描,速度非常慢,这个index级别比较range还低,与全表扫描是小巫见大巫
9.建组合索引的时候,区分度最高的在最左边。
  # 正例: 如果 where a=? and b=?,如果a列的几乎接近于唯一值,那么只需要单建idx_a索引即可
  # 说明: 存在非等号和等号混合时,在建索引时,请把等号条件的列前置,如: where c>? and d=?那么即使c的区分度更高,也必须把d放在索引的最前列,即索引idx_d_c
10.防止因字段类型不同造成的隐式转换,导致索引失效
11.索引与存储过程名称模板
# 普通索引: idx_{table}_{column},{table}为表名,当表名太长时,可使用缩写
# 正例: idx_user_register_phone,idx_wallet_request_biz_no
# 反例: user_register_phone,wallet_request_biz_no
# 唯一索引: uniq_{table}_{column}
# 存储过程: proc_{功能描述},比如proc_archive_payment_before_3month(将3个月之前的支付流水存档)
# 关于表名与字段名的缩写,项目内常见的如下:
# biz=>business(业务),no=>number(编号),info=>information(信息,详情),organization=>org(组织),i18n=>international(国际化)
12.创建索引时避免有如下极端误解:
    12.1 宁滥勿缺,认为一个查询就需要建一个索引
    12.2 宁缺勿滥,认为索引会消耗空间,严重拖慢更新和新增速度
    12.3 抵制惟一索引,认为业务的惟一性一律需要在应用层通过'先查后插'方式解决

3.SQL语句

1.不要使用count(列名)或count(常量)来替代count(*),count(*)是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关
# 说明: count(*)会统计值为NULL的行,而count(列名)不会统计此列为NULL值的行
2.count(distinct col)计算该列除NULL之外的不重复行数,注意count(distinct col1, col2)如果其中一列全为NULL,那么即使另一列有不同的值,也返回为0
3.当某一列的值全是NULL时,count(col)的返回结果为0,但sum(col)的返回结果为NULL,因此使用sum()时需注意NPE问题
# 正例: 可以使用如下方式来避免sum的NPE问题
SELECT IF(ISNULL(SUM(g)), 0, SUM(g)) FROM table;
4.使用ISNULL()来判断是否为NULL值
# 说明: NULL与任何值的直接比较都为NULL
    4.1 NULL<>NULL的返回结果是NULL,而不是false
    4.2 NULL=NULL的返回结果是NULL,而不是true
    4.3 NULL<>1的返回结果是NULL,而不是true
5.在代码中写分页查询逻辑时,若count为0应直接返回,避免执行后面的分页语句
6.不得使用外键与级联,一切外键概念必须在应用层解决
# 说明: 以学生和成绩的关系为例,学生表中的student_id是主键,那么成绩表中的student_id则为外键,如果更新学生表中的student_id,同时触发成绩表中的student_id更新,即为级联更新;外键与级联更新适用于单机低并发,不适合分布式,高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度
7.禁止使用存储过程,存储过程难以调试和扩展,更没有移植性
8.数据订正(特别是删除,修改记录操作)时,要先select,避免出现误删除,确认无误才能执行更新语句
9.in操作能避免则避免,若实在避免不了,需要仔细评估in后边的集合元素数量,控制在1000个之内
10.如果有国际化需要,所有的字符存储与表示,均以utf-8编码,注意字符统计函数的区别
# 说明:
SELECT LENGTH("轻松工作"); # 返回为 12
SELECT CHARACTER_LENGTH("轻松工作"); # 返回为 4
# 如果需要存储表情,那么选择utf8mb4来进行存储,注意它与utf-8编码的区别
11.TRUNCATE TABLE比DELETE速度快,且使用的系统和事务日志资源少,但TRUNCATE无事务且不触发trigger,有可能造成事故,故不建议在开发代码中使用此语句
# 说明: TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同

4.ORM映射

1.在表查询中,一律不要使用*作为查询的字段列表,需要哪些字段必须明确写明
# 说明:
# 1.增加查询分析器解析成本
# 2.增减字段容易与resultMap配置不一致
# 3.无用字段增加网络消耗,尤其是text类型的字段
2.不要写一个大而全的数据更新接口,不管是不是自己的目标更新字段,都进行update
# 反例: update table set c1=value1, c2=value2, c3=value3;这是不对的,执行SQL时,不要更新无改动的字段
# 1.易出错
# 2.效率低
# 3.增加binlog存储