MySQL 开发规范

Dec 16, 2018 00:00 · 1926 words · 4 minute read MySQL SQL


  1. 所有的数据库对象名称必须使用小写字母并用下划线分割（MySQL 大小写敏感，名称要见名知意，最好不超过32字符）
  2. 所有的数据库对象名称禁止使用 MySQL 保留关键字（如 desc、range、match、delayed 等，请参考 MySQL官方保留字）
  3. 临时库表必须以 tmp 为前缀并以日期为后缀（tmp_）
  4. 备份库和库必须以 bak 为前缀并以日期为后缀（bak_）
  5. 所有存储相同数据的列名和列类型必须一致（在多个表中的字段如 user_id，它们类型必须一致）
  6. 所有的表必须使用 innoDB（innoDB 好处支持失误，行级锁，高并发下性能更好，对多核，大内存，SSD 等硬件支持更好）
  7. 数据库和表的字符集尽量统一使用 utf8
  8. 所有表和字段都要添加注释 COMMENT ，从一开始就进行数据字典的维护
  9. 尽量控制单表数据量的大小在 500w 以内，超过 500w 可以使用历史数据归档，分库分表来实现（500万行并不是 MySQL 数据库的限制。过大对于修改表结构，备份，恢复都会有很大问题。MySQL 没有对存储有限制，取决于存储设置和文件系统）
  10. 谨慎使用 MySQL 分区表（分区表在物理上表现为多个文件，在逻辑上表现为一个表）
  11. 谨慎选择分区键，跨分区查询效率可能更低
  12. 建议使用物理分表的方式管理大数据
  13. 尽量做到冷热数据分离，减小表的宽度（MySQL 限制最多存储4096列，行数没有限制，但是每一行的字节总数不能超过65535。列限制好处：减少磁盘 IO，保证热数据的内存缓存命中率，避免读入无用的冷数据）
  14. 禁止在表中建立预留字段（无法确认存储的数据类型，对预留字段类型进行修改，会对表进行锁定）
  15. 禁止在数据中存储图片，文件二进制数据（使用文件服务器）
  16. 禁止在线上做数据库压力测试
  17. 禁止从开发环境，测试环境直接连生产环境数据库
  18. 限制每张表上的索引数量，建议单表索引不超过5个（索引会增加查询效率，但是会降低插入和更新的速度）
  19. 避免建立冗余索引和重复索引（冗余：index(a,b,c) index(a,b) index(a)）
  20. 禁止给表中的每一列都建立单独的索引
  21. 每个 innoDB 表必须有一个主键，选择自增 ID（不能使用更新频繁的列作为主键，不适用 UUID、MD5、HASH、字符串列作为主键）
  22. 区分度最高的列放在联合索引的最左侧
  23. 尽量把字段长度小的列放在联合索引的最左侧
  24. 尽量避免使用外键（禁止使用物理外键，建议使用逻辑外键）
  25. 优先选择符合存储需要的最小数据类型
  26. 优先使用无符号的整形来存储
  27. 优先选择存储最小的数据类型（varchar(N)，N 代表的是字符数，而不是字节数，表示能存储多少个汉字）
  28. 避免使用 Text 或是 Blob 类型
  29. 避免使用 Enum 数据类型（修改 Enum 值需要使用 ALTER 语句，Enum 类型的 ORDER BY 操作效率低，需要额外操作，禁止使用书值作为 Enum 的枚举值）
  30. 尽量把所有的字段定义为 NOT NULL （索引 NULL 需要额外的空间来保存，所以需要暂用更多的内存，进行比较和计算要对 NULL 值做特别的处理）
  31. 使用 timestamp 或 datetime 类型来存储时间
  32. 同财务相关的金额数据，采用 decimal 类型（不丢失精度，禁止使用 float 和 double）
  33. 避免使用双 % 号和 like，搜索严禁左模糊或者全模糊（如果需要请用搜索引擎来解决，索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索）
  34. 建议使用预编译语句进行数据库操作
  35. 禁止跨库查询（为数据迁移和分库分表留出余地，降低耦合度，降低风险）
  36. 禁止 SELECT * 查询（消耗更多的 CPU 和 IO 及网络带宽资源，无法使用覆盖索引）
  37. 禁止使用不含字段列表的 INSERT 语句
  38. IN 操作能避免则避免，若实在避免不了，需要仔细评估 IN 后边的集合元素数量，控制在1000个之内
  39. 禁止使用 ORDER BY RAND() 进行随机排序
  40. 禁止 WHERE 从句中对列进行函数转换和计算
  41. 尽量使用 UNION ALL 代替 UNION
  42. 拆分复杂的大 SQL 为多个小 SQL（MySQL 一个 SQL 只能使用一个 CPU 进行计算）
  43. 尽量避免使用子查询，可以把子查询优化为 JOIN 操作（子查询的结果集无法使用索引，子查询会产生临时表操作，如果子查询数据量大会影响效率，消耗过多的 CPU 及 IO 资源）
  44. 超过100万行的批量写操作，要分批多次进行操作（大批量操作可能会造成严重的主从延迟，binlog 日志为 row 格式会产生大量的日志，避免产生大事务操作）
  45. 对于程序连接数据库账号，遵循权限最小原则
  46. 超过三个表禁止 JOIN（需要 JOIN 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。即使双表 JOIN 也要注意表索引、SQL 性能。）
  47. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
  48. SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好
  49. 使用 ISNULL() 来判断是否为 NULL 值
  50. 禁止物理删除（即直接删除），只做逻辑删除，用 deleteFlag 做逻辑删除，如果删除，则为1，不删除则为0
  51. 如果有 ORDER BY 的场景，请注意利用索引的有序性。ORDER BY 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。