数据库设计规范（MySQL）

数据库设计规范（MySQL）

一、命名规范

1. 表名、字段名只能使用小写字母；数据库字段名的修改代价很大，因为无法进行预发布，

所以字段名称需要慎重考虑，必须见名知义，多个英语单词用下划线分隔，并且不要超过

32 个字符，不能使用保留字，不能使用简写拼音。

2. 表名不使用复数名词。说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数

量。

3. 所有表、字段都需要添加注释。

4. 数据库字符集指定为 utf8 或 utf8mb4，字符集默认校验规则为 utf8_general_ci 或

utf8mb4_general_ci。

5. 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 tinyint(2)。

6. 禁用保留字，如 desc、range、match、delayed，from，status 等，参考官方保留字。

7. 唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。说明：uk_即 unique key；idx_

即 index 的简称。

8.中间表用于保留中间结果集，名称必须以tmp_开头。备份表用于备份或抓取源表快照，名称必须以bak_开头。中间表和备份表定期清理。

二、类型选择

优先选择最小数据类型。

1. 字符串

文本数据尽量用varchar存储。因为varchar是变长存储，比char更省空间。MySQL server层规定一行所有文本最多存65535字节，长度不能超过 5000，如果存储长度大于此值，定义字段类型为 TEXT 或BLOB，而且必须独立出来一张表，用主键来对应，避免影响其它字段索引效率。

2. 整数

正负 100 以内的整数，使用 tinyint(2)来定义，可以存储从 -128 到 127 的整型数据，存储大小为 1 个字节。

正负 2,100,000,000 以内的整数，使用 int 来定义，可以存储从-2,147,483,648 到

2,147,483,647)的整型数据，存储大小为 4 个字节。

超过正负 2,100,000,000 以内的整数，使用 bigint 来定义，可以存储-

9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 的整型数据，存储大小为 8

个字节。

3. 浮点数

非精度要求的使用 float 和 double，对精度有高要求的，使用 decimal，decimal(a,b)，a 指

定的是 a 指定小数点左边可以存储的十进制数字的最大个数，最大精度 38，b 指定小

数点右边可以存储的十进制数字的最大个数，小数位数必须是从 0 到 a 之间的值，默认小数位

数是 0。

4. 日期时间

禁止使用字符串表示日期，除非只是显示，使用 timestamp 表示时间，超出时间则使用

datetime 存储时间，精度要求到微妙级别使用长度为 3。

三、开发公约

1. 数据库使用 mysql5.6 以上版本，默认使用 innoDB，统一字符使用 UTF-8。

2. 所有表、字段都需要添加注释说明。

3. 禁止在数据库中存储图片、文件等二进制数据。

4. 尽量控制表的字段数量，越少越好，字段允许适当冗余，来兼容特定查询场景，但是必须考虑数据一致性问题，且冗余的字段不能是频繁修改的字段，不能是 varchar 超长字段，更不能是 text 字段。

5. 不滥用索引，单表索引尽量不超过 5 个。

6. 禁止使用外键关联，在代码逻辑里约束。

7. 所有字段均定义为 NOT NULL，NULL 字段难于查询优化，NULL 字段的需要额外的存储

空间。

8. 尽可能不使用字符串去表述日期和数字。

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

10. 存储 ip 最好用 int 存储而非 char(15)或者 varchar(15)。

11. 使用 tinyint 类型或者 smallint 代替 enum 类型。

12. 禁止使用存储过程、函数、触发器，禁止使用；因为增加了程序耦合性，降低了数据库的可维护性，难以调试和扩展，更没有移植性。

13.涉及到嵌套事务时，要搞清楚事务的传播机制，建议查询spring transactional事务传播机制。

14. 每个表必须有必备字段：

id：唯一主键，只能使用 bigint(32)创建，可以使用阶段自增方式，如从 100001 开

始自增。

is_deleted：tinyint(2)类型，0 为有效状态，1 为无效状态，默认为 0。（根据自身业

务，物理删除可不用，逻辑删除按此规则）

create_time：datetime(3)类型，服务器创建时间，默认为当前时间。

update_time：datetime(3)类型，服务器更新时间，默认是当前时间，根据数据修改

做自动更新。

15. 选填字段：

create_by：创建人 id，系统默认为-1。

update_by：修改人 id，系统默认为-1。

四、索引规范

1.索引命名：

【强制】非唯一索引建议以 idx_字段 ，唯一索引建议以 unq_字段命名，索引名称全部小写；

2.索引的数量要控制：

(1) 【建议】单张表中索引数量尽量不超过 5 个，避免过多索引影响 update、insert、delete 的性能；

(2) 【建议】单个联合索引中的字段数尽量不超过 5 个；

(3) 【建议】对字符串使用前缀索引，前缀索引长度不超过 30 个字符，短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作；

(4) 【建议】建议优先考虑前缀索引，必要时可添加伪列并建立索引，多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，但也避免冗余索引；

3.主键准则：

(1) 【建议】一般建议每张表都要有自增主键 id bigint unsigned，且与业务无关，not null

auto_increment；对于预期数据量很多的表，建议预留分布式id字段，推荐使用 Twitter 的雪花算法(snowflake)及其衍生品生成全局自增 ID。

(2) 【强制】不使用更新频繁的列作为主键；

(3) 【强制】尽量不选择字符串列作为主键；

(4) 【高危】不使用 UUID MD5 HASH 这些作为主键；

4、索引禁忌：

(1) 【强制】不在区分度低的列上建立索引，例如“性别”，“类型”等字段；

(2) 【高危】不在索引列进行数学运算和函数运算，会导致索引失效而进行全表扫表；

5、禁止使用外键：

(1) 【高危】外键用来保护数据一致性和完整性，由应用端实现；

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

6、【强制】索引字段的默认值尽量不为 NULL，要改为其他的默认值或者空串；

7、【强制】能使用唯一索引尽量使用唯一索引，提高查询效率；

五、SQL 规范

(1) 【建议】SQL 语句尽可能简单，大的 SQL 想办法拆成小的 sql 语句，可充分利用多核 CPU；

(2) 【强制】事务要简单，整个事务的时间长度不要太长；应用程序中单次 INSERT 的行数不能超过1500 行，事务内禁止使用远程接口调用之类操作；因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等雪崩问题；

(3) 【高危】禁止使用触发器、函数、存储过程、外键；

(4) 【建议】表结构设计时，降低业务耦合度，为 scale out、sharding 留有余地；

(5) 【高危】避免在数据库中进行数学运算(MySQL 不擅长数学运算和逻辑判断)；

(6) 【高危】禁止用 select *，查询哪几个字段就 select 这几个字段；

(7) 【建议】in 里面数字的个数建议控制在 1000 以内；

(8) 【强制】除静态表或小表（1000 行以内），DML 语句必须有 where 条件，且使用索引查找；

(9) 【强制】禁止在更新类 SQL 语句中使用 join；

(10) 【强制】Limit 分页注意效率，Limit 越大，效率越低，关于分页查询，避免深度分页（比如:offset 较大要先查出主键，然后根据主键取出数据）；

(11) 【强制】对数据比较大的更新要分批次操作，不要一次更新太多数据；

(12) 【高危】SQL 语句不可以出现隐式转换，比如整型 id 主键 select id from tb where id='1'，索引会失效；

(13) 【高危】在 SQL 语句中，禁止使用负向查询，如 not in 和%前缀模糊查询，导致全表扫描；

(14) 【高危】禁止使用 order by rand()；

(15) 【高危】禁止单条 SQL 语句同时更新多个表，易造成死锁；

(16) 【高危】禁止在应用程序端显式加锁；

(17) 【高危】UPDATE、DELETE 语句禁止使用 LIMIT；

(18) 【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by可以利用key(a,b)；

(19)【建议】包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢；