MySQL 数据库 ​

建表规约 ​

1. 【强制】库的名称格式：业务系统名称_子系统名

2. 【强制】分库名称命名格式是库通配名_编号，编号从0开始递增。

   正例：wenda_001以时间进行分库的名称格式是“库通配名_时间”

3. 【强制】采用分库策略的，库的数量不能超过1024

4. 【强制】采用分表策略的，表的数量不能超过4096

5. 【强制】单个分区表中的分区（包括子分区）个数不能超过1024。

6. 【推荐】库名与应用名称尽量一致。

7. 【推荐】同一模块使用的表名尽量使用统一前缀。

8. 【推荐】相关模块的表名与表名之间尽量提现 join 的关系

   正例：user表和user_login表

9. 【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint (1表示是，0 表示否)。 说明: 任何字段如果为非负数，必须是 unsigned。 正例: 表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。

10. 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。 说明: MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。 正例: aliyun_admin，rdc_config，level3_name 反例: AliyunAdmin，rdcConfig，level_3_name

11. 【强制】表名不使用复数名词。 说明:表名应该仅仅表示表里面的实体内容，不应该表示实体数量。

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

13. 【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。

14. 【强制】表必备三字段: id，create_time，update_time。 说明: 其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time，update_time 的类型均为 datetime 类型，如果要记录时区信息，那么类型设置为 timestamp。另外datetime占用8字节，timestamp仅占用4字节，但是范围为1970-01-01 00:00:01到2038-01-01 00:00:00。更为高阶的方法，选用int来存储时间，使用SQL函数unix_timestamp()和from_unixtime()来进行转换。

15. 【强制】主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。 说明: pk_即 primary key; uk_即 unique key; idx_即 index 的简称。

16. 【强制】小数类型为 decimal，禁止使用 float 和 double。 说明: 在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。

17. 【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

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

    说明：MySQL server层规定一行所有文本最多存65535字节，因此在utf8字符集下最多存21844个字符，超过会自动转换为mediumtext字段。

19. 【强制】在数据库中不能使用物理删除操作，要使用逻辑删除。 说明: 逻辑删除在数据删除后可以追溯到行为操作。不过会使得一些情况下的唯一主键变得不唯一，需要根据情况来酌情解决。

20. 【推荐】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间。

21. 【推荐】业务中IPv4地址字段推荐使用int类型，不推荐用char(15)。因为int只占4字节，可以用如下函数相互转换，而char(15)占用至少15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空间。 SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);

22. 【推荐】不推荐使用enum，set。 因为它们浪费空间，且枚举值写死了，变更不方便。推荐使用tinyint或smallint。

23. 【推荐】不推荐使用blob，text等类型。它们都比较浪费硬盘和内存空间。在加载表数据时，会读取大字段到内存里从而浪费内存空间，影响系统性能。建议和PM、RD沟通，是否真的需要这么大字段。Innodb中当一行记录超过8098字节时，会将该记录中选取最长的一个字段将其768字节放在原始page里，该字段余下内容放在overflow-page里。不幸的是在compact行格式下，原始page和overflow-page都会加载。

24. 【推荐】存储金钱的字段，建议用int，程序端乘以100和除以100进行存取。因为int占用4字节，而double占用8字节，空间浪费。

25. 【推荐】表的命名最好是遵循“业务名称_表的作用”。 正例:alipay_task / force_project / trade_config / tes_question

26. 【推荐】表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。

    说明：使用 NULL 值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。

27. 【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

28. 【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循:

    • 不是频繁修改的字段。

    • 不是唯一索引的字段。

    • 不是 varchar 超长字段，更不能是 text 字段。

      说明：反范式设计：把经常需要 join 查询的字段，在其他表里冗余一份。

      正例: 各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取。如user_name属性在user_account，user_login_log等表里冗余一份，减少 join 查询。

29. 【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。 说明:如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

30. 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。 正例:无符号值可以避免误存负数，且扩大了表示范围:

    对象	年龄区间	类型	字节	表示范围
    人	150岁之内	tinyint unsigned	1	无符号值：0到255
    龟	数百岁	smallint unsigned	2	无符号值：0到65535
    恐龙化石	数千万年	int unsigned	4	无符号值：0到约43亿
    太阳	约50亿年	bigint unsigned	8	无符号值：0到约10的19次方

31. 【参考】库名、表名、字段名控制在32个字符以内。

    说明：库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符。

索引规约 ​

1. 【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。 说明: 不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的;另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

2. 【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致;多表关联查询时，保证被关联的字段需要有索引。 说明: 即使双表 join 也要注意表索引、SQL 性能。

3. 【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。 说明: 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名，索引长度)) / count(*) 的区分度来确定。

4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 说明:索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

5. 【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE；MEMORY表可以根据需要选择HASH或者BTREE类型索引。

6. 【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 filesort 的情况，影响查询性能。 正例: where a = ? and b = ? order by c; 索引: a_b_c 反例: 索引如果存在范围查询，那么索引有序性无法利用，如:WHERE a > 10 ORDER BY b; 索引 a_b 无法排序。

7. 【推荐】利用覆盖索引来进行查询操作，避免回表。 说明: 如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗?目录浏览一下就好，这个目录就是起到覆盖索引的作用。 正例: 能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现:using index。

8. 【推荐】利用延迟关联或者子查询优化超多分页场景。 说明: MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大 的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。 正例: 先快速定位需要获取的 id 段，然后再关联: SELECT t1.* FROM 表1 as t1 , (select id from 表1 where 条件 LIMIT 100000 , 20) as t2 where t1.id = t2.id

9. 【推荐】SQL 性能优化的目标: 至少要达到 range 级别，要求是 ref 级别，如果可以是 const 最好。 说明:

   • consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。

   • ref 指的是使用普通的索引(normal index)。

   • range 对索引进行范围检索。 反例: explain 表的结果，type = index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。

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

11. 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

12. 【推荐】单个表上的索引个数不能超过7个。

13. 【推荐】建表或加索引时，保证表里互相不存在冗余索引。对于MySQL来说，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。

14. 【参考】创建索引时避免有如下极端误解:

    • 索引宁滥勿缺。认为一个查询就需要建一个索引。

    • 吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。

    • 抵制唯一索引。认为唯一索引一律需要在应用层通过“先查后插”方式解决。

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 IFNULL(SUM(column) , 0) FROM table;

4. 【强制】使用 ISNULL() 来判断是否为 NULL 值。 说明: NULL 与任何值的直接比较都为 NULL。

   • NULL<>NULL 的返回结果是 NULL，而不是 false。

   • NULL=NULL 的返回结果是 NULL，而不是 true。

   • NULL<>1 的返回结果是 NULL，而不是 true。 反例: 在 SQL 语句中，如果在 null 前换行，影响可读性。 select * from table where column1 is null and column3 is not null; 而 ISNULL(column) 是一个整体，简洁易懂。从性能数据上分析，ISNULL(column) 执行效率更快一些。

5. 【强制】对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行。

   说明：因为alter table会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。

6. 【强制】代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

7. 【强制】不得使用外键与级联，一切外键概念必须在应用层解决。 说明: (概念解释)学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群;级联更新是强阻塞，存在数据库更新风暴的风险;外键影响数据库的插入速度。

8. 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

9. 【强制】数据订正(特别是删除或修改记录操作)时，要先 select，避免出现误删除的情况，确认无误才能执行更新语句。

10. 【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名(或表名)进行限定。 说明: 对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名(或表名)，并且操作列在多个表中存在时，就会抛异常。 正例: select t1.name from first_table as t1, second_table as t2 where t1.id = t2.id; 反例: 在某业务中，由于多表关联查询语句没有加表的别名(或表名)的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常: Column 'name' infield list is ambiguous。

11. 【推荐】SQL 语句中表的别名前加 as，并且以 t1、t2、t3、...的顺序依次命名。 说明:

    • 别名可以是表的简称，或者是依照表在 SQL 语句中出现的顺序，以 t1、t2、t3 的方式命名。

    • 别名前加 as 使别名更容易识别。 正例: select t1.name from first_table as t1 , second_table as t2 where t1.id = t2.id;

12. 【推荐】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。

13. 【建议】减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。

    说明：order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

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

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

16. 【参考】因国际化需要，所有的字符存储与表示，均采用 utf8mb4 字符集，字符计数方法需要注意。 说明:

    • SELECT LENGTH("轻松工作");--返回为 12

    • SELECT CHARACTER_LENGTH("轻松工作");--返回为 4

    • 表情需要用 utf8mb4 来进行存储，注意它与 utf8 编码的区别。

17. 【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。 说明: TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

程序层DAO设计 ​

1. 【建议】新的代码不要用model，推荐使用手动拼SQL+绑定变量传入参数的方式。因为model虽然可以使用面向对象的方式操作db，但是其使用不当很容易造成生成的SQL非常复杂，且model层自己做的强制类型转换性能较差，最终导致数据库性能下降。
2. 【强制】前端程序连接MySQL或者redis，必须要有连接超时和失败重连机制，且失败重试必须有间隔时间。
3. 【建议】前端程序报错里尽量能够提示MySQL或redis原生态的报错信息，便于排查错误。
4. 【建议】对于有连接池的前端程序，必须根据业务需要配置初始、最小、最大连接数，超时时间以及连接回收机制，否则会耗尽数据库连接资源，造成线上事故。
5. 【建议】对于log或history类型的表，随时间增长容易越来越大，因此上线前RD或者DBA必须建立表数据清理或归档方案。
6. 【建议】在应用程序设计阶段，RD必须考虑并规避数据库中主从延迟对于业务的影响。尽量避免从库短时延迟（20秒以内）对业务造成影响，建议强制一致性的读开启事务走主库，或更新后过一段时间再去读从库。
7. 【建议】多个并发业务逻辑访问同一块数据（innodb表）时，会在数据库端产生行锁甚至表锁导致并发下降，因此建议更新类SQL尽量基于主键去更新。
8. 【建议】业务逻辑之间加锁顺序尽量保持一致，否则会导致死锁。
9. 【建议】对于单表读写比大于10:1的数据行或单个列，可以将热点数据放在缓存里（如mecache或redis），加快访问速度，降低MySQL压力。

ORM 映射 ​

1. 【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。 说明:

   • 增加查询分析器解析成本。

   • 增减字段容易与 resultMap 配置不一致。

   • 无用字段增加网络消耗，尤其是 text 类型的字段。

2. 【强制】更新数据表记录时，必须同时更新记录对应的 update_time 字段值为当前时间。

3. 【参考】事务会影响数据库的 QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

线上禁用语句 ​

1. 【强制】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。因为会导致主从不一致，导致数据错乱。建议加上order by PK。
2. 【强制】禁止使用关联子查询，如update t1 set … where name in(select name from user where…);效率极其低下。
3. 【强制】禁用procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源，降低数据库实例可扩展性。推荐都在程序端实现。
4. 【强制】禁用insert into …on duplicate key update…在高并发环境下，会造成主从不一致。
5. 【强制】禁止联表更新语句，如update t1,t2 where t1.id=t2.id…。

较为规范的建表语句 ​

CREATE TABLE user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) NOT NULL COMMENT '用户id',
  `username` varchar(45) NOT NULL COMMENT '真实姓名',
  `email` varchar(30) NOT NULL COMMENT '用户邮箱',
  `nickname` varchar(45) NOT NULL COMMENT '昵称',
  `avatar` int(11) NOT NULL COMMENT '头像',
  `birthday` date NOT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
  `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
  `user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',
  `create_time` timestamp NOT NULL COMMENT '用户记录创建的时间',
  `update_time` timestamp NOT NULL COMMENT '用户资料修改的时间',
  `user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='网站用户基本信息';

CREATE TABLE user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) NOT NULL COMMENT '用户id',
  `username` varchar(45) NOT NULL COMMENT '真实姓名',
  `email` varchar(30) NOT NULL COMMENT '用户邮箱',
  `nickname` varchar(45) NOT NULL COMMENT '昵称',
  `avatar` int(11) NOT NULL COMMENT '头像',
  `birthday` date NOT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
  `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
  `user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',
  `create_time` timestamp NOT NULL COMMENT '用户记录创建的时间',
  `update_time` timestamp NOT NULL COMMENT '用户资料修改的时间',
  `user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='网站用户基本信息';

参考资料 ​

1. Java开发手册（黄山版）. 2022-02-03

2. MySQL数据库设计规范. 2018-05-23 - 缺点是说明较少，很多规范不知道为什么制定