注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL数据库使用规范-持续更新

MySQL 彭东稳 4880次浏览 已收录 3个评论

一、基础规范

  • 统一使用utf8mb4字符集和utf8mb4_general_ci字符排序规则,utf8mb4字符集是utf8的超集。
  • 表存储引擎使用InnoDB,默认就是。
  • 隔离级别使用READ-COMMITTED(binlog格式使用ROW)。
  • 统一命名规范:默认全小写,禁用关键字和合理使用前缀。
  • 各环境进行隔离,避免混用风险:dev->test->staging->production。
  • 生产数据安全隐私为首重,要求项目数据闭环和数据范围可控制。

二、库设计规范

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

三、表设计规范

3.1 表属性检查项

  • 命名统一规范,大小写都行,只能使用字母加下划线,禁用MySQL关键字和合理使用前缀。
  • 表名、列名、索引名的长度不大于64个字节。
  • 如果建立的是临时表,则必须要以tmp为前缀。
  • 必须要指定建立innodb的存储引擎(默认为innodb存储引擎)。
  • 必须要指定utf8的字符集(如果不指定默认继承库字符集)。
  • 表必须要有注释信息,不宜太长。
  • 表必须要有一个主键(不需要则做特殊说明)。
  • 只能有一个自增长列(auto_increment)。
  • 核心业务表避免使用自增列,使用外部ID生成器。
  • 索引名字不能是Primay(留给主键索引使用的)。
  • 不要使用Foreign key(特殊需求报备)。
  • 表不能建立为分区表(特殊需求报备)。
  • 单实例表个数控制在3w以内,单表行数1000w~2000w内。
  • 创建表时必须要用createTime和updateTime字段,数据类型为Datetime;默认值可以为”createTime datetime DEFAULT CURRENT_TIMESTAMP”或”updateTime datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”。
  • 表增加、更改字段(ALTER TABLE)时不允许添加AFTER关键字,可能会导致从库复制异常。

3.2 列属性检查项

  • 不能设置列的字符集。
  • 列必须要有注释。
  • char长度大于20的时候需要改为varchar。
  • 列的类型不能是BLOB/TEXT,尽可能少用。如果列为BLOB/TEXT类型的,则这个列不能设置为NOT NULL。
  • 列的类型不能使用ENUM,可使用TINYINT替换。
  • 如果是timestamp类型的,则要必须指定默认值。
  • 除了大字段列之外,其余字段都必须设置not null和default值(字符型定义为default ”,数值型定义为0,浮点型定义为0.00)。

3.3 索引属性检查项

  • 索引名称必须要有名字。
  • 普通索引必须要以idx_为前缀,后面跟字段名称(太长的字段取三个字符标识),如idx_trade(单列)或idx_tra1_tra2(多列)。
  • 唯一索引必须要以uniq_idx_为前缀,后面跟字段名称(太长的字段取前三个字符标识),如uniq_idx_trade(单列)或uniq_idx_tra1_tra2(多列)。
  • 最好不能有外键存在。
  • 索引的列数最好不能超过5个。
  • 每个表必须要有一个显式主键,主键尽量用单字段,最好是数值类型。
  • 最好不要超过5个索引。
  • 索引中的列,不能重复。
  • 索引长度不能超过766。
  • 不能有重复的索引,名字及内容。
  • 注意组合索引的顺序,最左原则,根据SQL条件调整索引字段顺序。
  • 避免创建无效索引和冗余索引,按需创建索引。

四、SQL规范

4.1 插入语句检查项

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

4.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等事务控制语句。

4.3 查询语句

  • Where子句右值一定注意类型,避免隐式类型转换。
  • 多表Join时注意比较字段类型和字符集的一致,避免隐式转换。
  • 禁止负向查询,笔记%开头的模糊查询,无法使用索引。
  • 避免对索引列进行函数和数值计算,会导致索引失效。
  • 禁用select *,需要写出对应字段名,尽可能走覆盖索引。
  • 线上业务禁止大表JOIN查询,或子查询,会产生临时表,消耗较多内存和CPU。
  • 禁止使用OR条件,应尽可能使用IN查询。
  • 避免使用大SQL、大事务,或事务中等待用户动作行为。

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

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

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

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

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

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

五、应遵循规范

  • 在表创建初期,应预留一些扩展字段,比如2个varchart,2个int,自行根据业务选择组合。避免后面表变大之后,需要做增加字段的操作。
  • 禁止使用存储过程和函数、视图、触发器、外键约束和事件,如有用到要报备DBA。应尽量把计算放到业务层,而不是数据库层面;因为这些功能的使用增加了数据库的维护难度,另外并发量大的情况下会拖死数据库可能,而在业务层面计算则可以横向扩展服务器。
  • 禁止存储大文件或者图片。数据库适合做小事务高并发,业务模型简单的数据处理;图片或文件存储尽可能使用对象存储或文件存储服务器,数据库中只存储URL。

<延伸>

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


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (4)or分享 (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