京东-数据研发运营部-MySQL开发规范.pptx

MySQL基础规范 开发规范 数据研发与运营部 / 王伟 目 录 CONTENTS 一、基本规范 二、命名规范 三、库表设计规范 四、索引设计规范 五、字段设计规范 六、SQL语句设计规范 七、行为规范 引言 线上的数据库故障，很大一部分来自对数据库的使用操作不规范，在开发过程中遵循一定的规范，可以有效地减低数据库故障率、提高数据库的稳定性、高效性、安全性。 1、当前线上MySQL的版本有5.5.14/5.5.38/5.6.20，默认采用MySQL5.5.38的版本。 2、DDL/DML操作申请必须走ITSV流程；授权默认授增删改查的权限，需要申请人直属领导审批即可，其他特殊权限至少需要申请人所属部门总监以上领导审批，然后发邮件到mysql@即可。 3、线上做大的变动，特别是对MySQL数据库调整较大的，在上线前后要通知DBA观察数据库整体情况，以防访问量陡增造成数据库性能急剧下降给业务带来影响。 1、基本规范 1、使用INNODB存储引擎 5.5以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存，ssd等硬件支持更好。 2、MySQL字符集使用UTF8 统一，不会有转换产生乱码风险。 3、所有表都需要添加注释，单表数据量建议控制在1000万以内。 4、不在数据库中存储图片、文件等大数据。 5、禁止在线上做数据库压力测试。 6、禁止从测试、开发环境直连线上数据库。 2、基本规范 1、库名、表名、字段名必须使用小写字母并采用下划线分割，禁止使用中横线。 2、库名、表名、字段名禁止超过32个字符，须见名知意，统一规范、 易于辨识。 3、库名、表名、字段名禁止使用MySQL保留关键字。 4、临时库、临时表名必须以tmp为前缀并以日期为后缀。 5、备份库、备份表名必须以bak为前缀并以日期为后缀 3、库表设计规范 1、不建议使用分区表 MySQL的分区表实际性能不是很好，且管理维护成本较高。 2、拆分大字段和访问频率低的字段，分离冷热数据。 3、用HASH进行散表，表名后缀使用十进制数，下标从0开始。 首次分表尽量多的分，避免二次分表，二次分表的难度和成本较高。 4、按日期时间分表需符合YYYY[MM][DD][HH]格式。 5、采用合适的分库分表策略。 6、单表字段数控制在20个以内 4、索引设计规范 索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间 1、单张表中索引数量不超过5个。 2、单个索引中的字段数不超过5个。 3、innodb主键推荐使用自增列，主键不应该被修改，字符串不应该做主键，如果不指定主键，innodb会使用唯一且非空值索引代替。 4、如果是复合索引，区分度最大的字段放在索引前面。 5、核心SQL优先考虑覆盖索引。 6、区分度最大的字段放在索引前面。 7、避免冗余或重复索引： 合理创建联合索引（避免冗余），index(a,b,c)相当于index(a)、index(a,b)、index(a,b,c)。 4、索引设计规范 8、不在低基数列上建立索引，例如‘性别’。 9、不在索引列进行数学运算和函数运算。 10、尽量不要使用外键。 对父表和子表的操作会相互影响，降低可用性。 11、不使用%前导的查询，如like“%xxx”，无法使用索引。 12、不使用反向查询，如not in / not like 无法使用索引，导致全表扫描全表扫描。 13、索引不是越多越好，按实际需要进行创建。 在线上创建表的时候，一定要考虑好表的索引，这样避免线上MySQL性能问题带来的故障以及降低后续维护成本。没有索引的建表语句DBA不予通过审核。 5、字段设计规范 1、禁止使用TEXT、BLOB类型。 2、将字符转化为数字。 3、使用TINYINT来代替ENUM类型 4、字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量。 5、如果可能的话所有字段均定义为not null。 6、使用UNSIGNED存储非负整数。 同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255。 7、INT类型固定占用4个字节存储。 8、使用TIMESTAMP存储时间。 因为TIMESTAMP使用4字节，DATETIME使用8个字节,同时TIMESTAMP具有自动赋值以及自动更新的特性。 9、禁止在数据库中存储明文密码。 6、SQL语句设计规范 1、尽量避免相同语句由于书写格式的不同，而导致多次语法解析。 2、避免隐式转换，会导致索引失效。 3、禁止使用存储过程、触发器、视图等，让数据库做最擅长的事，降低业务耦合度。 4、避免使用大表的join。 5、避免在数据库中进行数学运算，容易将业务逻辑和DB耦合在一起，MySQL不擅长数学运