《互联网MySQL开发规范》

时间：2022-06-11 13:16:39

一、基础规范

  • 使用 INNODB 存储引擎
  • 表字符集使用 UTF8 
  • 所有表都需要添加注释
  • 单表数据量建议控制在 5000W 以内
  • 不在数据库中存储图⽚、文件等大数据
  • 禁止在线上做数据库压力测试
  • 禁⽌从测试、开发环境直连数据库

二、命名规范

  • 库名表名字段名必须有固定的命名长度，12个字符以内
  • 库名、表名、字段名禁⽌止超过32个字符。须见名之意
  • 库名、表名、字段名禁⽌止使⽤用MySQL保留字
  • 临时库、表名必须以tmp为前缀，并以⽇日期为后缀
  • 备份库、表必须以bak为前缀，并以日期为后缀

三、库、表、字段开发设计规范

  • 禁⽌使用分区表
  • 拆分大字段和访问频率低的字段，分离冷热数据
  • 用HASH进行散表，表名后缀使用十进制数，下标从  0  开始
  • 按日期时间分表需符合 YYYY[MM][DD][HH] 格式
  • 采用合适的分库分表策略。例如千库十表、十库百表等
  • 尽可能不使用 TEXT 、 BLOB 类型
  • 用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数
  • 越简单越好：将字符转化为数字、使用 TINYINT 来代替 ENUM 类型
  • 所有字段均定义为 NOT NULL
  • 使用 UNSIGNED 存储非负整数
  • INT类型固定占用 4  字节存储
  • 使用 timestamp 存储时间
  • 使用 INT UNSIGNED 存储 IPV4
  • 使用 VARBINARY 存储大小写敏感的变长字符串
  • 禁止在数据库中存储明文密码，把密码加密后存储
  • 用好数值类型字段
  • 存储ip最好用 int 存储而非 char(15)
  • 不允许使用 ENUM
  • 避免使用 NULL 字段， NULL 字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效
  • 少用 text/blob ， varchar 的性能会比 text 高很多，实在避免不了blob，请拆表
  • 数据库中不允许存储大文件，或者照片，可以将大对象放到磁盘上，数据库中存储它的路径

四、索引规范

1、索引的数量要控制：

  • 单张表中索引数量不超过5个
  • 单个索引中的字段数不超过5个
  • 对字符串使⽤用前缀索引，前缀索引长度不超过8个字符
  • 建议优先考虑前缀索引，必要时可添加伪列并建立索引

2、主键准则

  • 表必须有主键
  • 不使用更新频繁的列作为主键
  • 尽量不选择字符串列作为主键
  • 不使用 UUID   MD5   HASH 这些作为主键(数值太离散了)
  • 默认使⽤非空的唯一键作为主键
  • 建议选择自增或发号器

3、重要的SQL必须被索引，比如：

  • UPDATE 、 DELETE 语句的 WHERE 条件列
  • ORDER BY 、 GROUPBY 、 DISTINCT 的字段

4、多表JOIN的字段注意以下

  • 区分度最大的字段放在前面
  • 核⼼SQL优先考虑覆盖索引
  • 避免冗余和重复索引
  • 索引要综合评估数据密度和分布以及考虑查询和更新比例

5、索引禁忌

  • 不在低基数列上建立索引，例如“性别”
  • 不在索引列进行数学运算和函数运算

6、尽量不使用外键

  • 外键用来保护参照完整性，可在业务端实现
  • 对父表和子表的操作会相互影响，降低可用性

7、索引命名：非唯一索引必须以idx字段1字段2命名，唯一所以必须以 uniq 字段 1 字段 2 命名，索引名称必须全部小写

8、新建的唯一索引必须不能和主键重复

9、索引字段的默认值不能为 NULL ，要改为其他的 default 或者空。 NULL 非常影响索引的查询效率

10、反复查看与表相关的SQL，符合最左前缀的特点建立索引。多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量

11、能使用唯一索引就要使用唯一索引，提高查询效率

12、研发要经常使用 explain ，如果发现索引选择性差，必须让他们学会使用hint

五、SQL规范

  • SQL语句尽可能简单，大的SQL想办法拆成小的SQL语句(充分利用QUERYCACHE和充分利用多核CPU)
  • 事务要简单，整个事务的时间长度不要太长
  • 避免使用触发器、函数、存储过程
  • 降低业务耦合度，为s acle out 、 sharding 留有余地
  • 避免在数据库中进⾏数学运算(MySQL不擅长数学运算和逻辑判断)
  • 不要用 SELECT * ，查询哪几个字段就SELECT这几个字段
  • SQL中使用到OR的改写为用IN()(OR的效率没有IN的效率高)
  • in里面数字的个数建议控制在 1000 以内

  • LIMIT分页注意效率。LIMIT越大，效率越低。可以改写 LIMIT ，比如例子改写：

    SELECT id FROM t LIMIT 10000, 10; => SELECT id FROM t WHERE id > 10000 LIMIT 10;

  • 使用 UNION ALL 替代  UNION

  • 避免使用大表的 JOIN
  • 使用 GROUP BY 分组、自动排序
  • 对数据的更新要打散后批量更新，不要一次更新太多数据
  • 减少与数据库的交互次数

  • 注意使用性能分析工具

    SQL explain / showprofile / mySQLsla

  • SQL语句要求所有研发，SQL关键字全部是大写，每个词只允许有一个空格

  • SQL语句不可以出现隐式转换，比如 SELECT id FROM TABLE WHERE id='1'
  • IN条件里面的数据数量要少，我记得应该是500个以内，要学会使用EXIST代替IN，EXIST在一些场景查询会比IN快
  • 能不用 NOT IN 就不用 NOTIN ，坑太多了。。会把空和NULL给查出来
  • 在SQL语句中，禁止使用前缀是%的LIKE
  • 不使用负向查询，如 NOT IN /  LIKE
  • 关于分页查询：程序里建议合理使用分页来提高效率LIMIT，OFFSET较大要配合子查询使用
  • 禁止在数据库中跑大查询
  • 使⽤预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率
  • 禁止使 ORDER BY RAND()
  • 禁⽌单条SQL语句同时更新多个表

六、流程规范

  • 所有的建表操作需要提前告知该表涉及的查询SQL；
  • 所有的建表需要确定建立哪些索引后才可以建表上线；
  • 所有的改表结构、加索引操作都需要将涉及到所改表的查询SQL发出来告知DBA等相关人员；
  • 在建新表加字段之前，要求研发至少要提前3天邮件出来，给DBA们评估、优化和审核的时间
  • 批量导入、导出数据必须提前通知DBA协助观察
  • 禁止在线上从库执行后台管理和统计类查询
  • 禁止有超级权限的应用程序账号存在
  • 推广活动或上线新功能必须提前通知DBA进⾏行流量评估
  • 不在业务高峰期批量更新、查询数据库

相关文章