• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL开发规范-持续更新

MySQL 彭东稳 7年前 (2017-01-20) 22012次浏览 已收录 3个评论

一、基础规范

  • 统一使用utf8mb4字符集和utf8mb4_general_ci字符排序规则,utf8mb4字符集是utf8的超集,而且还支持emoji字符。
  • 表存储引擎使用InnoDB存储引擎,默认就是。
  • 隔离级别使用READ-COMMITTED(binlog格式使用ROW)。
  • 统一命名规范,默认全小写,禁用关键字和合理使用前缀。
  • 严禁在数据库中明文存储用户密码、身份证、信用卡号(信用卡PIN码)等核心机密数据,务必先行加密。
  • 涉及精确金额相关用途时,建议扩大N倍后,全部转成整型存储(例如把分扩大百倍),避免浮点数加减出现不准确问题。
  • 特别注意做ALTER TABLE CHANGE/MODIFY操作时,要写全相关属性信息,避免原属性信息丢失。
  • 在表创建初期,应预留一些扩展字段,比如2个varchart,2个int,自行根据业务选择组合。避免后面表变大之后,需要做增加字段的操作。
  • 尽量不要在数据库做运算,如 order by rand(),md5() 等,将运算逻辑放到应用中实现。
  • 禁止使用存储过程和函数、视图、触发器、外键约束和事件,如有用到要报备DBA。应尽量把计算放到业务层,而不是数据库层面;因为这些功能的使用增加了数据库的维护难度,另外并发量大的情况下会拖死数据库可能,而在业务层面计算则可以横向扩展服务器。
  • 禁止存储大文件或者图片。数据库适合做小事务高并发,业务模型简单的数据处理;图片或文件存储尽可能使用对象存储或文件存储服务器,数据库中只存储URL。
  • 平衡范式不冗余,尽量做到适当冗余,效率优先。
  • 拒绝 3B 操作,Big Transaction,Big SQL,Big Batch。
  • 各环境进行隔离,避免混用风险:dev->qa->stag->prod。有条件下各个环境最好能形成一个pipeline,保证结构一致性。
  • 生产数据安全隐私为首重,要求项目数据闭环和数据范围可控制。

二、库设计规范

  • 库名统一小写,只能使用字母加下划线,禁用MySQL关键字和合理使用前缀。
  • 库统一默认字符集设置为UTF8MB4,有特殊需求说明。

三、表设计规范

  • 命名统一规范,大小写都行,只能使用字母加下划线,禁用MySQL关键字和合理使用前缀。
  • 表名、列名、索引名的长度不大于64个字节。
  • 如果建立的是临时表,则必须要以tmp_为前缀。
  • 统一使用InnoDB存储引擎(默认为InnoDB存储引擎)。
  • 统一使用utf8mb4字符集和utf8mb4_general_ci字符排序规则,utf8mb4字符集是utf8的超集,而且还支持emoji字符。
  • 表必须要有注释信息,不宜太长,能说明信息即可。
  • 存储整型数据时,如没有特殊需求,建议加上UNSIGNED,扩大存储范围。
  • 建议每个字段都设置NOT NULL,且有DEFAULT值(字符型定义为default ”,数值型定义为0,浮点型定义为0.00)。
  • 表必须要设置主键,建议使用自增列作为主键,避免字符型、UUID等作为主键,提升写入性能,降低二级索引空间大小。
  • 如果遇到BLOB、TEXT字段,则尽量拆出去,再用主键做关联。单行记录太大会导致大量的binlog空间生成,DDL缓慢。
  • 字符类型建议采用varchar数据类型(InnoDB建议用varchar替代char,除非特定长度使用char)。
  • 列的类型不能使用ENUM,可使用TINYINT替换。
  • 禁止使用Foreign key(特殊需求报备)。
  • 禁止使用分区表(特殊需求报备)。
  • 表的字段数控制在 20~50 个以内。
  • 单实例表个数控制在3w以内,单表行数1000w~2000w内,提升表的访问速度及DDL变更时间。
  • 时间类型建议采用为datetime/timestamp数据类型。
  • 创建表时必须要用createTime和updateTime字段,数据类型为Datetime;默认值可以为”createTime datetime DEFAULT CURRENT_TIMESTAMP”或”updateTime datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”。

四、索引设计规范

  • 应用上线前需要根据访问SQL添加合适的索引。
  • 索引名字不能是PRIMARY(留给主键索引使用的)。
  • 不仅仅是select需要添加索引,update和delete语句也需要添加。
  • 普通索引必须要以idx_为前缀,后面跟字段名称(太长的字段取三个字符标识),如idx_trade(单列)或idx_tra1_tra2(多列)。
  • 唯一索引必须要以uniq_idx_为前缀,后面跟字段名称(太长的字段取前三个字符标识),如uniq_idx_trade(单列)或uniq_idx_tra1_tra2(多列)。
  • 避免对每个查询单独创建一个索引,尽量使用组合索引完成索引优化,特别是遇到GROUP BY,ORDER BY条件时,尽可能用组合索引解决。
  • 注意组合索引的顺序,最左原则,根据SQL条件调整索引字段顺序,遵循结果集少的字段放在最左边。
  • 索引的列数最好不能超过5个。
  • 索引个数最好不要超过5-7个索引。
  • 索引长度不能超过766字节。
  • 避免创建无效索引和冗余索引,按需创建索引。

五、SQL规范

  • 尽可能将复杂SQL进行拆分分解,保证高并发或者放到应用中去实现。
  • 避免使用大事务造成执行锁定时间,同时导致备库只读节点数据延迟。
  • 打散大批量更新拆分成小事务批量执行,避开业务高峰。
  • 所有新业务上线或者业务变更而引入的新SQL都必须要重新评估。

1. 插入语句检查项

  • 必须指定插入列表,也就是要对哪几个列指定插入值,如insert into t (id,id2) values(…);。
  • 创建表时如果createTime字段没有加“DEFAULT CURRENT_TIMESTAMP”,那么insert时必须带上createTime=now()。
  • 必须指定值列表,与上面对应的列,插入的值是什么,必须要指定。
  • 插入列列表与值列表个数相同,上面二者的个数需要相同,值列表长度要与表列数相同。
  • 插入指定的列列表中,同一个列不能出现多次。
  • 不要在脚本中、脚本后出现COMMIT, ROLLBACK等事务控制语句。
  • 插入数据避免使用replace语句,可能会造成唯一键数据重复,增加死锁,且replace锁复杂。详情:唯一键约束失效

2. 更新、删除语句检查项

  • update或delete必须要有where条件。
  • update或delete大事务操作时要分段执行。
  • 禁用update/delete … limit N结构SQL。
  • 使用where in(),in子句中元素建议小于500。
  • 影响行数大于10000条的需注明。
  • 创建表时如果updateTime字段没有加“DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”,那么update时必须带上SET updateTime=now()。
  • 不要在脚本中、脚本后出现COMMIT,ROLLBACK等事务控制语句。

3. 查询语句

  • 同数据类型的列值比较,应用传入与数据库定义保持一致,避免隐式类型转换。
  • 多表 Join 时注意比较字段类型和字符集的一致,避免隐式转换。
  • Join 查询时,一定要在 Join 字段上创建索引,避免嵌套查询。
  • 禁止负向查询,以 like % 开头的模糊查询,无法使用索引。
  • 避免对索引列进行函数和数值计算,会导致索引失效。
  • Load data导数据,避免使用insert….select造成备库延迟。
  • 禁用 select * 查询所有字段数据,只查询需要的字段数据,尽可能走覆盖索引。
  • OLTP业务尽可能避免多表JOIN查询,或子查询,会产生临时表或嵌套查询,消耗较多内存和CPU。
  • 尽可能少使用 OR 条件,应尽可能使用 IN 查询。
  • 避免使用大SQL、大事务,或事务中等待用户动作行为。

– 大事务/SQL会导致主从复制延时。

– MySQL的单个SQL只能在一个CPU上运行。

– 影响数据库的并发性能,事务持有的锁等资源只在事务rollback/commit时才能释放。

– 存在比较长的readview(快照),如果持续时间几十分钟,数据库响应时间可能聚降(mvcc,undo)。

对于插入、更新、删除语句,需要开发提供对应的注释信息,以及语句回滚脚本。语句注释信息如下所示:

有注释信息,以及这个操作会影响多少行数据,好让执行人员做对比,如有问题立即反馈开发人员。

<延伸>

http://seanlook.com/2016/05/11/mysql-dev-principle-ec/


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (4)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!

(3)个小伙伴在吐槽
  1. “不要在脚本中、脚本后出现COMMIT, ROLLBACK等事务控制语句”,这个是为什么呢?脚本中进行了删除和插入操作,如果没有commit,会导致执行失效。
    duohappy2018-02-28 13:45 Windows 10 | Chrome 64.0.3282.186
  2. “统一使用utf8mb4字符集和utf8mb4_general_ci字符排序规则,utf8mb4字符集是utf8的超集”,很重要,最近有一个踩坑经历,在windows mysql5.7,存入emoji表情和特殊字符,直接报错,在linux mysql5.5,存入同样的字符,字符变成了问号。(第一个回复有一个特殊字符,特殊字符及后面的内容被截取掉了,请问是怎么实现的呢?)
    duohappy2018-02-28 12:45 Windows 10 | Chrome 64.0.3282.186
  3. “统一使用utf8mb4字符集和utf8mb4_general_ci字符排序规则,utf8mb4字符集是utf8的超集”,很重要,最近有一个踩坑经历,在windows mysql5.7,存入emoji表情和特殊字符如
    duohappy2018-02-28 12:44 Windows 10 | Chrome 64.0.3282.186