Skip to content

MySQL 使用规范

目的

规范对 MySQL 的维护和使用，避免使用不当对业务造成的不利影响， 指导大家在日常开发和维护中如何发挥云数据库 MySQL 最优性能。

权限管理规范

  1. 对业务代码使用的账号，只授权 DML（SELECT、UPDATE、INSERT、DELETE）权限。
  2. 提供只读权限账号供大家日常使用，鉴于目前如果禁止可写账号使用可能会引起较多麻烦， 所以平时可以使用业务代码中使用的可写账号，但请谨慎处理。
  3. 只有运维人员或组长可以操作全权限账号。

库表设计规范

  1. 每张表必须有主键，尽量使用自增ID做主键，而不是使用业务相关的字段做联合主键。 因为业务相关的字段逻辑唯一性难以长时间得到保证。
  2. 库表全部使用 utf8mb4_unicode_ci 字符集，避免复杂汉字和 emoji 的显示乱码。
  3. 使用 Decimal 类型存储小数，通常指定精度为四位小数。
  4. 避免在数据库中存储大段文本或二进制文件，避免使用 text/blob 类型。 将这些内容保存成本地磁盘文件，数据库中保存其索引信息即可。 因为这些数据几乎无法索引且会增大查询时的磁盘 IO。 一定要使用的，把这些字段放到子表中。
  5. 禁止使用外键。数据的逻辑完备性由代码保证。 外键在频繁的业务迭代中会增加维护成本，且会降低插入性能，在大并发下容易产生死锁。
  6. 所有字段定义为 NOT NULL 并加上默认值，NULL 会给 SQL 开发带来很多坑导致走不了索引， 对 NULL 计算时只能用 IS NULL 和 IS NOT NULL 来判断。
  7. 不使用存储过程、触发器、函数、event、视图等高级功能。相应的逻辑通过业务代码实现。 数据库专注于数据存储，这些高级特性会导致维护成本增加，可移植性，可拓展性下降。
  8. 数据库中时间的存储应保存为格林威治时间，方便国际业务的拓展。 统一使用 DATETIME 存储时间，即使不用 TIME 部分。
  9. 所有库表都应该有 create_time, modify_time, delete_time 三个字段。
  10. 库表的命名遵守 snake-case，尽可能不超过12个字符，禁止超过32个字符。
  11. 主键通常不考虑 bigint，因为如果 int 都不够用的话，大概率这个表的性能也不会好到哪去。 如果怕不够用，可以考虑使用 unsigned int。
  12. 所有表和字段都需要添加注释。
  13. 禁止明文存储密码。

索引设计规范

  1. 单表的索引数建议不超过 5 个，单个索引中的字段数建议不超过 5。 太多就起不到过滤作用了，索引也占空间，管理起来也耗资源。
  2. 枚举类字段不允许出现索引内，因为过滤性太差。
  3. 对 varchar 字段上建索引时，指定索引长度，选择区分度足够高的长度。 可以用 count(distinct left(列名, 索引长度))/count(*)来看索引区分度。 但如果使用索引来排序的话，不能使用前缀索引。
  4. 避免冗余索引，两个索引(a,b) (a)同时存在，则(a)属于冗余索引。
  5. 对单个索引来说，建复合索引的时候，区分度最高的列放索引的在最左边。 有多个复合索引的时候，尽可能把公共字段放在前面。
  6. 存在非等号和等号混合判断条件时，必须把等号条件的列前置，否则走不到索引。
  7. 避免多个范围条件，对于范围条件查询，MySQL无法再使用范围列后面的其它索引列。
  8. 关注排序的性能消耗，如果有filesort，应该考虑创建排序索引。
  9. 针对 like 查询使用全文索引。没办法使用全文索引的，禁止使用前缀是%的 like
  10. 非唯一索引以 idx_ 命名，唯一索引以 uniq_ 命名，全文索引以 ft_ 命名。
  11. 关注索引的过滤性。哪怕是基于索引的条件过滤，如果优化器意识到总共需要扫描的数据量超过30%时， 就会直接改变执行计划为全表扫描，不再使用索引。

日常操作规范

  1. 所有线上 DDL 操作提交数据库修改审批，由运维人员或组长评估并操作。
  2. 目前我们的业务高峰期在下午4点到凌晨2点间，这段时间非必需或紧急情况的，不允许 DDL 操作， 是否必需或紧急情况由 Larry 决定。
  3. 同样的，在业务高峰期内，除非必需或紧急情况的，不允许刷数据或批量操作数据， 是否必需或紧急情况由 Larry 决定。
  4. DDL 操作和优化普及使用 pt 工具。
  5. 刷数据脚本每操作 200 条数据，sleep 0.1s。 刷数据脚本刷真实用户之前使用测试数据刷一遍预估时间，合理安排刷数据时段。
  6. 数据误操作后可以使用腾讯云提供的快速回档功能，回档的表名是原库表_bak。
  7. 删除数据禁止直接 DELETE，应该通过设置 delete_time 来标记删除状态。 如果没有 delete_time 字段，应该有类似 status 这样的字段来标记删除。
  8. 不要在查询中使用一些及时性函数，因为这种查询是无法缓存。
  9. 按需查询，没有必要的情况下不用 SELECT *，多余的字段会浪费宝贵的缓存空间和带宽。
  10. 使用事务时控制事务大小，大的事务可能会产生大量锁等待，回滚也会造成压力。
  11. 事务应及时提交或回滚，否则会造成无谓的锁等待。建议代码在 finally 里写上 rollback， 这样如果已经正常提交了并不会有什么负面影响，如果忘记提交，会及时回滚。
  12. 禁止连表查询。
  13. 避免子查询。如果了使用子查询，子查询应尽可能过滤避免临时数据集过大。
  14. 分页使用 keyset，而不是 offset。
  15. 避免负方向查询，例如 not，not in，!= 等，因为此类查询很难被索引优化。
  16. 尽可能避免使用 OR，使用 IN 代替，IN 的个数控制在 1000 内，超过了通过别的办法查询。
  17. 禁止在逻辑循环中操作数据库，要尽可能减少与数据库的交互。 如果有很难避免的情况，一定写明注释，方便后人知晓优化。