数据库设计规范（五大点）

一、表设计规范

1. 临时表使用tmp为前缀开头，时间戳结尾

2. 备份表使用bak为前缀开头，时间戳结尾

3. 所有表使用innodb储存引擎支持事务，行级锁，恢复性，高并发性能更好

4. 单表数据量控制在500万以内
   500万只是一个大致的标准。具体需要根据硬件的性能，以及表结构决定。500万内标准来自阿里《java开发规范》，其中也补充到。如果三年后数据库达不到500万，则不需要考虑分表分库

5. 禁止表中创建预留字段

   1. 不能见名知义
   2. 不能确认存储的数据类型
   3. 修改字段类型会锁表

6. 禁止存储图片和文件
   数据读写图片以及文件远不如文件系统处理的速度，备份，迁移数据库加剧耗时，数据库更擅长于存储数据，图片文件一般上传至云服务器中，将URL存储到数据库中更合理。

7. 禁止在线上做数据库压力测试

8. 避免使用外键约束

   1. 外键的优点：
      • 保证了相关联的表之间数据的完整性。
      • 方便级联操作
      • 保证数据完整性交给数据库，减少了程序代码量
   2. 外键的缺点：
      • 性能问题: 每次操作数据库，数据库会自行校验操作数据可行性
      • 并发问题: 每次校验数据完整，并发情况下容易导致死锁
      • 灵活性问题: 数据库迁移，或者数据库手动修改，因为外键校验数据完整性，数据库经常拒绝用户操作数据，需要梳理外键关系，再行操作。
        综上，建议将数据库外键逻辑使用程序代码实现。而不是交给数据库实现。

9. 创建表尽可能使用utf8或者utf8mb4字符集，校验字符集使用utf8_general_ci或者utf8mb4_general_ci。ci表示不区分大小写。utf8mb4_general_ci准确度稍差，但是校对速度较快

10. 添加注释，在建表语句comment中指定

二、字段设计规范

1. 优先选择符合储存需要的最小数据类型，尽量将字符串转为数字类型
   数字类型没有字符集以及校验字符集，占用的内存，cpu资源较少。如主机ip，业务编号，尽量使用数字存储

2. 避免使用TEXT，BLOB数据类型(查询排序时，启用io消耗资源，且只支持前缀索引)，建议分离到单独扩展表中

3. 避免使用enum数据类型，建议使用char或者tinyint

4. 尽量避免列为null值

   • 索引null列时需要额外的空间保存
   • 进行比较和计算时需要做额外的特殊处理

5. 表字段字符集和表保持一致，尽可能使用utf8或者utf8mb4字符集，校验字符集使用utf8_general_ci或者utf8mb4_general_ci

6. 添加注释，在建表语句comment中指定，数据字典字段最好说明状态值。即使有数据库文档

三、索引设计规范

1. 单表索引不超过5个，禁止给表中的每一列建立索引（降低mysql优化器评估效率。增加生成查询计划的时间。）

2. 不使用频繁修改的列作为主键，不使用uuid，md5，hash作为主键（不能保证下一行数据比上一行数据的主键值大。会导致数据逻辑调整增加io以及cpu资源的消耗）

3. 将索引加载where从句中的列，包含order by 、group by、distinct中的列，多表关联的join列表上

4. 如何选择索引的顺序

   1. 区分度最高的列放在联合索引的最左测
   2. 尽量把字段长度小的列放在最左测
   3. 使用最频繁的列放在最左侧

5. 避免建立冗余索引以及重复索引

四、sql开发规范

1. 使用预编译语句。可重复使用执行计划，防止sql注入

2. 避免数据类型的隐式转换（索引失效）

CREATE TABLE `test` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `username` varchar(255)  NULL DEFAULT NULL COMMENT '用户名',
        PRIMARY KEY (`ID`) USING BTREE
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4  ROW_FORMAT = Dynamic;

上面创建test表，更新符合id条件的username

    update test set username = '更新名称' where id = '1'

id = ‘1’，表设计中id为int类型，而更新sql中传的varchar类型，导致隐式转换
放弃主键索引，全面扫描，实际测试100万数据更新需40s左右。

3. 避免使用双%号查询（索引失效）
4. 使用left join 或者not exists优化not in （索引失效）
5. 禁止跨库查询
   1. 为数据库迁移和分库分表留出余地
   2. 降低业务耦合度
   3. 避免权限过大产生数据风险
6. 禁止使用select * 查询
   1. 效果更多的cpu io 以及网络资源
   2. 无法使用覆盖索引
7. 禁止使用不含字段列表的insert语句
8. 禁止使用子查询，可以把子查询改为join
   1. 子查询结果集不能使用索引
   2. 产生临时表操作，效果过多的cpu io资源
9. 避免使用过多的join，建议不超过5个
10. 减少同数据库的链接次数（数据库更适合处理批量操作）
11. 使用in代替or，in使用索引效率更高
12. 禁止使用order by rand()，消耗大量cpu io以及内存资源
13. where 从句中禁止对列进行函数转换和计算（索引失效）
14. 将明显不会有重复值时使用union all 而不是union（union会把所有数据放到临时表并去重）
15. 将复杂sql拆分为小sql

五、数据操作行为规范

1. 超过100万行的批量写操作，要分批多次进行操作

   1. 避免产生大事务操作
   2. 产生大量的日志
   3. 造成严重的主从延迟

2. 修改大数据表结构，使用pt-online-schema-change工具

----------------- 文章如有问题，请下方回复指出，感谢查阅😁 -----------------