MySQL开发规范-持续更新

一、基础规范

• 统一使用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)