MySQL 开发规范

一、建表规范

  1. 【强制】库名、表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字
  2. 【强制】。表必须有主键（复合主键，无主键需要说明原因），统一使用应用生成的全局唯一id，类型为bigint且单调递增不推荐使用数据库auto_increment。
  3. 【强制】禁用数据库保留字段，如desc、range、match、delayed等，具体参考官方文档https://dev.mysql.com/doc/refman/5.7/en/keywords.html
  4. 【强制】表必备3个字段：id，create_time，modify_time。说明：id必为主键，类型为unsigned bigint，单调递增。create_time，modify_time类型均为datetime（时间字段为了追溯数据的变化）
  5. 【强制】如果存储字符串长度确定，使用char定长字符串类型
  6. 【强制】varchar是可变长字符串，不预先分配空间。长度不能超过5000，如果需要存储长度大于5000，需定义为text字段，
  7. 【强制】TEXT类型由于会强制生成磁盘临时表，需要拆分到单独的表中，用主键来对应。避免影响表中其他字段索引效率
  8. 【强制】禁用外键约束，禁用触发器，禁用视图，禁用存储过程
  9. 【强制】表默认存储引擎为innodb，默认字符集为utf8（需要支持emoji请指定utf8mb4），默认校对规则为utf8_general_ci
  10. 【强制】禁用default NULL，业务上也尽量不使用NULL值。字符串默认值可用default ''代替，数值可用default 0代替
  11. 【强制】小数类型使用decimal，禁用float和double
  12. 【强制】如果表数据量大，且数据容易过期，需要定时清理的考虑使用分区表。分区表限制：需要添加part_time(时间戳)字段作为分区键，并使用(id,part_time)作为复合主键。唯一索引也必须包含分区键
  13. 【强制】禁止在数据库中存储图片
  14. 【推荐】表中字段，和表本身都应该带有comment说明
  15. 【推荐】库名与应用名应尽可能一致，表的命名最好是加上“业务名称_表的作用”
  16. 【推荐】字段允许适度冗余，以提高性能。但不要冗余频繁修改的字段，以及varchar超长字段或text字段
  17. 【推荐】合适的字段长度，不但节约数据，索引的存储空间，更重要的是提高数据检索速度。原则：够用的基础上尽可能小的字段长度
  18. 【推荐】单表字段数控制在20个以内，最好不超过50个
  19. 【推荐】单表行数超过1000万行或者单表容量超过2GB，才考虑进行分库分表

二、表变更规范

  1. 【强制】对于同一个表的多个字段进行更改，新增操作，需要合并成一条。如： alter table tb1 add column col1 xxx, add column col2 xxx;
  2. 【强制】新增字段必须追加在末尾（不允许指定新增字段位置）。并带有comment说明字段
  3. 【强制】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释
  4. 【强制】提交表变更语句前，需检查语法和标点符号是否正确，标点符号一定要用英文半角，每个语句以分号';'结尾
  5. 【强制】线上环境不允许变更表名及列名，否则影响数据采集
  6. 【强制】线上环境不允许修改字段顺序，否则影响数据采集
  7. 【强制】线上环境不允许删除表，不允许删除字段。如有特殊需求，需要说明原因

三、索引规范

  1. 【强制】对于同一个表的多个删除或增加索引的操作，必须合并成一条，对于索引的修改，先新建后删除。如：alter table tb1 add index xxx，drop index xxx;
  2. 【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建唯一索引
  3. 【强制】业务库禁止超过3个表的join，join的字段必须类型一致并且加索引
  4. 【强制】where条件中必须使用和过滤字段完全一致的数据类型，如字符串类型需要加''引号，数值类型不加。避免因为类型转换导致无法走索引
  5. 【强制】where条件中等式右边禁止使用函数，否则导致索引失效
  6. 【强制】禁止左模糊和全模糊，如果需要请用=走搜索引擎解决
  7. 【强制】创建组合索引时，区分度高的字段放在最左边
  8. 【推荐】对于组合索引，语句必须遵循最左前缀原则，禁止直接跳过前缀最左边字段。
  9. 【推荐】varchar字段建索引可以指定字段长度，没必要整个字段建立索引。根据实际文本区分度决定索引长度：count(distinct left(列名, 索引长度))/count(*) 计算索引区分度，大于90%即可
  10. 【推荐】查询返回较少字段的情况下可以考虑建立覆盖索引来进行查询操作，避免回表
  11. 【推荐】利用延迟关联或者子查询优化超多分页场景
  12. 【推荐】order by 多个字段的时候必须顺序一致，不能一个升序一个降序。order by 的字段通常可以考虑作为组合索引的一部分，放在索引组合顺序的最后，避免filesort

四、SQL规范

  1. 【强制】不要使用count(列名)或count(1)代替count()。count()会统计值为NULL的行，而count(列名)则不会统计此列为NULL的行.如果需要统计NULL行, 推荐使用count(*)>count(1)≈count(id)>count(列名)
  2. 【强制】count(distinct col)计算该列除NULL之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0
  3. 【强制】使用ISNULL()来判断是否为NULL。注意：NULL和任何值直接比较都为NULL，比如 NULL = NULL 返回false
  4. 【强制】在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
  5. 【强制】数据订正时，删除和修改记录时，要先 select，避免出现误删除，确认无误才能执行更新语句。
  6. 【强制】大批量数据（百万级别）删除时，必须分批执行。比如delete from tb1 where status=1 limit 10000
  7. 【强制】大表全表删除不要使用delete。提交申请由DBA执行truncate删除
  8. 【强制】表查询中，一律不使用select * 作为查询的字段列表。需要哪些字段必须明确写明。
  9. 【强制】INSERT 语句书写必须显式指定字段名称。
  10. 【推荐】事务不要滥用（MySQL每条语句自动提交事务）。事务会影响数据库的 QPS，另外使用事务的地方需 要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
  11. 【推荐】如果limit语句包含有order by 来作为分页查找，可以改变逻辑。每次获取上一页的最大/最小值 作为查询条件比如：SELECT id,create_time FROM live_room ORDER BY id ASC limit 318000,1000;改为：SELECT id,create_time FROM live_room WHERE id > 上一页的最大值 ORDER BY id ASC limit 1000
  12. 【推荐】查询中用 inner join 代替 in 、not in 、exists、not exists等谓词
执行器
逻辑删除