数据库使用规范(索引规范，SQL规范，表设计规范等)-CSDN博客

本文链接：https://blog.csdn.net/USTC_Zn/article/details/94356505

一.基础规范

(1) 使用InnoDB存储引擎

(2) 库、表、列字符集使用utf8mb4,utf8mb4兼容utf8且可以存储表情字符。

(3) 建议所有表、所有列都需要添加注释

(4) 不在数据库中存储图，文件等大数据，可以将大对象放到磁盘上，数据库中存储它的路径

(5) 禁止在线上做数据库压力测试 (可在预发布环境)

(6) 禁止线下开发环境直连线上数据库主库

二.命名规范

(1) 库名.表名.字段名必须使用小写字母或数字，禁止出现数字开头，禁止连个下划线中间只出现数字，使用下划线“_”分割，分表使用后缀为 “_xx"，例如"order_01,order_99"

(2) 库名.表名.字段名禁止超过32个字符。须见名知意

(3) 库名.表名.字段名禁止使用MySQL保留字

(4) 临时库.表名必须以tmp为前缀，并以日期为后缀

(5) 备份库.表必须以bak为前缀，并以日期为后缀

三.库.表.字段开发设计规范

(1) 按日期时间分表需符合YYYY[MM][DD][HH]格式

(2) 对日志型表选择分区表策略

(3) 建议不使用TEXT.BLOB类型

(4) 建议所有字段均定义为NOT NULL,默认值为空的写法为NOT NULL DEFAULT '' 而不是 NOT NULL DEFAULT NULL，禁止使用NULL字段 NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效，详情请查看：https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

(5) 使用UNSIGNED存储非负整数（存储的范围更大了）

(6) 关于datetime、timestamp类型的异同点。

相同点：

两者的最小精度都为小数点后6位

不同点：

1.存储范围的问题：timestamp类型的存储范围较小，为'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC；datetime类型的存储范围较大，为'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
2.时区的问题：timestamp会随server端时区的变化而变化，datetime不会

(7) 使用INT UNSIGNED存储IPV4,通过MySQL函数inet_ntoa和inet_aton来进行转化。Ipv6地址目前没有转化函数，需要使用DECIMAL或者两个bigINT来存储。

(8) 建议使用VARCHAR存储大小写敏感的变长字符串

(9) 禁止在数据库中存储明文密码，把密码加密后存储

(10) 不允许使用ENUM （插入非法值的时候，默认会插入一个空值）

(11) 所有的表(特殊情况除外,如:日志表)必须要有创建时间、更新时间两列，前者表示主动创建，后者表示被动更新【强制】

create_time datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'

update_time datetime(0) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间')

(12)可以适当的添加冗余列,减少表关联，提高查询效率,通过注释表明哪张是主表

(13) 多表关联导致查询语句性能低下，进行join不超过3个表。超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致;多表关联查询时，保证被关联的字段需要有索引。【强制】

说明:即使双表 join 也要注意表索引、SQL 性能。

(14) 数据库查询语句where条件范围要非常少，不要超过10条记录返回。（建议）

(15) 业务数据表不允许包含数据物理硬删除操作，若业务存在数据删除操作需求，必须采用逻辑删除，数据表中增加数据逻辑删除标记is_deleted,确实需要进行物理删除的时候需要经过部门leader同意后DBA方可操作。
（备注：其中is_deleted=’0’为否，is_deteled=’1’为是）

(16) varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。【强制】

(17) 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。【参考】

（18）对于内容重复的大字段如text、varchar(500)等列，可以新建冗余表，通过表关联的方式进行查询，提升查询效率，减少磁盘空间使用。

四.索引规范

1.索引的数量要控制：
(1) 单个索引中的字段数不超过5个（建议3个以内）

(2) 单张表中索引数量不超过5个

2.主键准则
(1) 表必须有主键

(2) 不使用更新频繁的列作为主键

(3) 尽量不选择字符串列作为主键

(4) 不允许UUID MD5 HASH这些作为主键

(5) 默认使用非空的唯一键作为主键

(6) 建议选择自增列作为主键，定义为 int/bigint unsigned auto_increment comment '主键'

3.重要的SQL必须被索引，比如：
(1) UPDATE.DELETE语句的WHERE条件列

(2) ORDER BY.GROUP BY.DISTINCT的字段

4.多表JOIN的字段注意以下(涉及到多表JOIN的需求,提前提交到DBA处审核)：
(1) 区分度最大的字段放在前面

(2) 核心SQL优先考虑覆盖索引

(3) 避免冗余和重复索引

(4) 索引要综合评估数据密度和分布以及考虑查询和更新比例

5.索引禁忌
(1) 不在低基数列上建立索引，例如“性别”

(2) 不在索引列进行数学运算和函数运算

(3) 创建索引前先查看表结构，避免创建冗余索引

(4) 在 varchar 字段上建立索引时，指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。【建议】

说明:索引的长度与区分度是一对矛盾体，一般对字符串类型数据，区分度达到 90%及以上即可，可以使用 count(distinct left(列名, 索引长度))/count(distinct 列名)的区分度来确定。

(5) 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。【强制】

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

(6) 建组合索引的时候，区分度最高的在最左边。【强制】

正例:如果 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a索引即可。

说明:存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即索引 idx_d_c。

6.尽量不使用外键
(1) 不得使用外键与级联，一切外键概念必须在应用层解决【强制】

说明:以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群;级联更新是强阻塞，存在数据库更新风暴的风险;外键影响数据库的插入速度。

(2) 对父表和子表的操作会相互影响，降低可用性

7.索引命名：非唯一索引以 idx_字段1_字段2命名，唯一索引以uk_字段1_字段2命名，索引名称必须全部小写【建议】

8.新建的唯一索引必须不能和主键重复

9.索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率

10.反复查看与表相关的SQL，符合最左前缀的特点建立索引，例如：一个索引idx_test(n1,n2,n3)，where n1= ;where n1= and n2=;where n1= and n3=;where n1= and n2= and n3=,这四种情况会使用到该索引。多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量。【强制】

11.能使用唯一索引就要使用唯一索引，提高查询效率

12.使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary

13.SQL变更需要确认索引是否需要变更并通知DBA

五.SQL规范

(1) sql语句尽可能简单,尽力避免使用JOIN

(2) 事务要简单，整个事务的时间长度不要太长(多事务，小事务原则) ，单事务数据更改粒度为2000行，单事务提交时间应小于1S

(3) 避免使用触发器.函数.存储过程,如果临时使用存储过程进行批量操作，每个commit之后应该sleep 1s(根据情况设置)，避免由于主库大量写入导致的主从延迟问题。

(4) 避免在数据库中进行数学运算

(5) 在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。

说明: 1)增加查询分析器解析成本。2) 增减字段容易与 resultMap 配置不一致。3) 无用字段增加网络消耗，尤其是 text 类型的字段。

(6) limit分页注意效率。Limit越大，效率越低。可以改写limit，比如例子改写：

select id from t limit 10000, 10; => select id from t where id > 10000 limit10; SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10; => SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10. => SELECT * FROM table inner JOIN(SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as t USING(id)

(7) 使用union all替代union

(8) 避免使用大表的JOIN

(9) 对数据的更新要打散后批量更新，不要一次更新太多数据，一次每次更新不超过2000条记录

(10) 减少与数据库的交互次数

(11) 注意使用性能分析工具 Sql explain / show profile

(12) 防止因字段类型不同造成的隐式转换，导致索引失效。如id int，使用where id='1';

(13) IN条件里面的数据数量要少,尽量不用,IN条件里边的数量应不超过20

(14) 能不用NOT IN就不用NOT IN，不会把NULL给查出来

(15) 在SQL语句中，禁止使用前缀是%的like

(16) 不使用负向查询，如not in/like

(17) 关于分页查询：程序里建议合理使用分页来提高效率limit，limit较大要配合子查询使用

例如: 先快速定位需要获取的 id 段，然后再关联: