参考

表设计规范

  1. 数据库名称使⽤⼩写字⺟并⽤下划线分割（MySQL ⼤⼩写敏感，名称要⻅名知意，最好不超过32字符）

  2. 数据库名称禁⽌使⽤MySQL保留关键字（如 desc、range、match、delayed 等，请参考 MySQL官⽅保留字 ）

  3. 没有特殊要求，所有的表必须使⽤ InnoDB 存储引擎（MySQL8默认）

  4. 数据库和表的字符集尽量统⼀使⽤ utf8mb4（字符集必须统⼀，避免由于字符集转换产⽣的乱码，汉字utf8下占3个字节）

  5. 临时库表必须以 tmp_ 为前缀并以⽇期为后缀

  6. 备份库和库必须以 bak_ 为前缀并以⽇期为后缀

  7. 多个表中相同的列（如用户 ID 字段，统一为 user_id，类型为 varchar(19)），列名和列类型均⼀致

  8. 表和字段都要加上注释，描述清楚

  9. 表命名必须带上业务模块前缀：例如 sys_开头(系统前缀)

  10. 所有表必需字段：创建时间，创建者，最后更新时间，更新人，备注

    1
    2
    3
    4
    5
    6
    ALTER TABLE `表名`
    ADD COLUMN `create_by`  varchar(32) NULL COMMENT '创建人',
    ADD COLUMN `create_time`  datetime NULL COMMENT '创建时间' AFTER `create_by`,
    ADD COLUMN `update_by`  varchar(32) NULL COMMENT '修改人' AFTER `create_time`,
    ADD COLUMN `update_time`  datetime NULL COMMENT '修改时间' AFTER `update_by`,
    ADD COLUMN `remark`  varchar(1000) NULL COMMENT '备注' AFTER `update_time`;

  11. 主键必须为 id，类型为 varchar(19)

  12. 每个 InnoDB 表必须有⼀个主键，选择⾃增 id（不能使⽤更新频繁的列作为主键，不使用 UUID,MD5,HASH,字符串列作为主键）

  13. 禁⽌使⽤物理外键，建议使⽤逻辑外键

  14. 外键字段命名：{关联表名}+“_”+ {关联字段名}，例如 order_main_id

  15. 选择符合存储需要的最⼩数据类型

  16. 尽量把所有的字段定义为NOT NULL（索引NULL需要额外的空间来保存，所以需要暂⽤更多的内存，进⾏⽐较和计算要对 NULL值做特别的处理）

  17. 优先选择存储最⼩的数据类型（varchar(N),N代表的是字符数，⽽不是字节数，N代表能存储多少个汉字）

  18. 使⽤ timestamp 或 datetime 类型来存储时间

  19. 同财务相关的⾦额数据，采⽤decimal类型（不丢失精度，禁⽌使⽤ float 和 double）

  20. 是否xxx 字段: iz_* char(1) 1表示是 0表示否，（禁用 is_, 代码生成实体有问题 )

  21. 状态、类型等字段，使用 char 类型，长度一般为1或2就够用了。避免数字类型的一些问题；如果需要考虑性能建议用 int 类型（禁用 tinyint 类型，需要兼容其他数据库）

  22. 状态位: 表中只有一个状态字段时，使用 status。多个状态字段时，使用*_status char 状态字段必须加注释说明每个值代表含义

    示例一：’xxx类型，系统字典 xxx_type’

    示例二：’是否，1-是 0-否’

  23. 字段命名，多单词采用下划线分隔 例如：school_id

  24. 字符串类型字段，varchar 类型长度不允许超过 1000（过长转库会变类型）

  25. 大文本尽量少用，字段类型采用 text、longtext，禁用 blob 系列类型（必须用要确认）

  26. 禁止使用逻辑删除字段，使用回收站表实现逻辑删除和还原

  27. 乐观锁字段， update_count 使用 int 类型

  28. 尽量控制单表数据量的⼤⼩在 500w 以内，超过 500w 可以使⽤历史数据归档，分库分表来实现（500万⾏并不是MySQL数据库的限制。过⼤对于修改表结构，备份，恢复都会有很⼤问题。MySQL没有对存储有限制，取决于存储设置和⽂件系统）

  29. 谨慎使⽤MySQL分区表（分区表在物理上表现为多个⽂件，在逻辑上表现为⼀个表），建议使⽤物理分表的⽅式管理⼤数据

  30. 谨慎选择分区键，跨分区查询效率可能更低

  31. 尽量做到冷热数据分离，减⼩表的宽度（MySQL限制最多存储4096列，⾏数没有限制，但是每⼀⾏的字节总数不能超过 65535。列限制好处：减少磁盘io，保证热数据的内存缓存命中率，避免读⼊⽆⽤的冷数据）

  32. 禁⽌在表中建⽴预留字段（⽆法确认存储的数据类型，对预留字段类型进⾏修改，会对表进⾏锁定）

  33. 禁⽌在数据中存储图⽚，⽂件⼆进制数据（使⽤⽂件服务器）

SQL查询规范

  1. 在代码中写分⻚查询逻辑时，若 count 为 0 应直接返回，避免执⾏后⾯的分⻚语句
  2. 禁⽌ select * 查询（消耗更多的 cpu 和 io 及⽹络带宽资源，另外⽆法使⽤覆盖索引）
  3. 避免使⽤双 % 号和 like，搜索严禁左模糊或者全模糊（如果需要请⽤搜索引擎来解决。索引⽂件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么⽆法使⽤此索）
  4. 建议使⽤预编译语句进⾏数据库操作
  5. 禁⽌跨库查询（为数据迁移和分库分表留出余地，降低耦合度，降低⻛险）
  6. in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内
  7. 禁⽌使⽤ order by rand（）进⾏随机排序
  8. 禁⽌ where 从句中对列进⾏函数转换和计算（例如：where date（createtime）=‘20160901’ 会⽆法使⽤createtime列上索引。改成 where createtime>=’20160901’ and createtime <’20160902’）
  9. 尽量使⽤ union all 代替 union
  10. 拆分复杂的⼤ SQL 为多个⼩ SQL（ MySQL⼀个 SQL 只能使⽤⼀个 CPU 进⾏计算）
  11. 尽量避免使⽤⼦查询，可以把⼦查询优化为 join 操作（⼦查询的结果集⽆法使⽤索引，⼦查询会产⽣临时表操作，如果⼦查询数据量⼤会影响效率，消耗过多的CPU及IO资源）
  12. 对于⼤表使⽤ pt—online-schema-change 修改表结构（避免⼤表修改产⽣的主从延迟，避免在对表字段进⾏修改时进⾏锁表）
  13. 超过三个表禁⽌ join
  14. 需要 join 的字段，数据类型必须绝对⼀致
  15. SQL 性能优化的⽬标：⾄少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好
  16. 使⽤ ISNULL() 来判断是否为 NULL 值

索引规范

  • 索引命名
    • 主键索引命名为：pk_表名缩写_字段名（索引要求全库唯一，为兼容多数据库），示例 pk_test_id
    • 唯一索引命名为：uk_表名缩写_字段名，示例 uk_test_name
    • 普通索引命令为： idx_表名缩写_字段名（表名缩写： 下划线分隔单词首字母组合）idx_test_name
  • 限制每张表上的索引数量，建议单表索引不超过5个（索引会增加查询效率，但是会降低插⼊和更新的速度）
  • 避免建⽴冗余索引和重复索引（冗余：index（a,b,c) index(a,b) index(a)）
  • 禁⽌给表中的每⼀列都建⽴单独的索引
  • 索引，查询频率高的字段加索引（单字段索引 、组合索引、唯一索引）
  • 区分度最⾼的列放在联合索引的最左侧
  • 尽量把字段⻓度⼩的列放在联合索引的最左侧
  • 多表关联查询时，保证被关联的字段需要有索引
  • 在 varchar 字段上建⽴索引时，必须指定索引⻓度，没必要对全字段建⽴索引，根据实际⽂本区分度决定索引⻓度即可
  • 如果有 order by 的场景，请注意利⽤索引的有序性。order by 最后的字段是组合索引的⼀部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

其它规范

  1. 禁⽌使⽤不含字段列表的insert语句（不允许 insert into t values（‘a’，‘b’，‘c’）不允许）
  2. 对于程序连接数据库账号，遵循权限最⼩原则
  3. 超过 100 万⾏的批量写操作，要分批多次进⾏操作（⼤批量操作可能会造成严重的主从延迟，binlog ⽇志为 row 格式会产⽣⼤量的⽇志，避免产⽣⼤事务操作）
  4. 禁⽌在线上做数据库压⼒测试
  5. 禁⽌从开发环境，测试环境直接连⽣产环境数据库