命名规范
- 库名、表名、字段名必须使用小写字母并采用下划线分割; 库名、表名、字段名支持最多32个字符,统一规范、易于辨识以及减少传输量不要超过32;
- 库名、表名、字段名禁止使用MySQL保留关键字; 临时库、临时表名必须以tmp为前缀并以日期为后缀;
- 备份库、备份表名必须以bak为前缀并以日期为后缀; 索引建立:唯一索引UNIQ_字段1_字段2,普通和联合索引 IDEX_字段1_字段2,第一个索引列必须在最前面,要不索引不会生效。
基本规范
- 使用INNODB存储引擎 以后的默认引擘,支持事务,行级锁,更好的恢复性,高并发下性能更好,对多核,大内存,ssd等硬件支持更好;
- 表字符集使用UTF8 utf8字符集,如果是汉字,占3个字节,但ASCII码字符还是1个字节;统一,不会有转换产生乱码风险;
- 所有表都需要添加注释,单表数据量建议控制在500万以内; 不在数据库中存储图片、文件等大数据; 禁止在线上做数据库压力测试;
- 禁止从测试、开发环境直连线上数据库;
库表设计规范
- 禁止使用分区表,MySQL的分区表实际性能不是很好,且管理维护成本较高 拆分大字段和访问频率低的字段,分离冷热数据采用合理的分库分表策略,推荐使用HASH进行分表,表名后缀使用十进制数,下标从0开始
- 首次分表尽量多的分,避免二次分表,二次分表的难度和成本较高 按日期时间分表需符合YYYY[MM][DD][HH]格式
- 单表字段数控制在20个以内 一条完整的建表语句中应包含必要的字段、主键、合理的索引(综合代码中所有的条件语句创建合理的索引)
索引设计规范
- 索引是一把双刃剑,它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间
- 单张表中索引数量不超过5个;
- 单个索引中的字段数不超过5个;
对字符串使用前缀索引,前缀索引长度不超过10个字符;如果有一个CHAR(200)列,如果在前10个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10个字符进行索引能够节省大量索引空间,也可能会使查询更快; - 表必须有主键,不使用UUID、MD5、HASH作为主键,尽量不选择字符串列作为主键;主键建议选择自增id或者数字的唯一id;
- 创建复合索引时区分度较大的字段放在最前面;不在低区分度的字段上创建索引,例如‘性别‘;
- 避免冗余或重复索引
- 合理创建联合索引(避免冗余),index(a,b,c)相当于index(a)、index(a,b)、index(a,b,c);
- 索引不是越多越好,按实际需要进行创建
- 每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能
- 不在索引列进行数学运算和函数运算;
- 尽量不要使用外键
- 外键用来保护参照完整性,可在业务端实现,对父表和子表的操作会相互影响,降低可用性;
- 不使用%前导的查询,如like“%xxx”,无法使用索引;
- 不使用反向查询,如not in / not like
- 无法使用索引,导致全表扫描
- 全表扫描导致buffer pool利用降低
字段设计规范
- 尽可能不要使用TEXT、BLOB类型
- 删除这种值会在数据表中留下很大的"空洞",可以考虑把BLOB或TEXT列分离到单独的表中
- 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
- 浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;浮点数的缺点是会引起精度问题
将字符转化为数字 - 使用TINYINT或VARCHAR(N)来代替ENUM类型
- 字段长度尽量按实际需要进行分配,不要随意分配一个很大的容量
VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N;
VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存; - 如果可能的话所有字段均定义为not null
- 使用UNSIGNED存储非负整数
- 同样的字节数,存储的数值范围更大。如tinyint有符号为-128-127,无符号为0-255
- 使用TIMESTAMP存储时间. 因为TIMESTAMP使用4字节,DATETIME使用8个字节,同时TIMESTAMP具有自动赋值以及自动更新的特性.
- 使用INT UNSIGNED存储IPV4
- 使用VARBINARY存储大小写敏感的变长字符串
- 禁止在数据库中存储明文密码
SQL设计规范
- 使用预编译语句prepared statement
只传参数,比传递SQL语句更高效,一次解析,多次使用,降低SQL注入概率
尽量避免相同语句由于书写格式的不同,而导致多次语法分析 - 避免隐式转换
会导致索引失效,如select userid from table where userid=’ 1234’ - 充分利用前缀索引
必须是最左前缀,不可能同时用到两个范围条件 - 避免使用存储过程、触发器、EVENTS等
让数据库做最擅长的事,降低业务耦合度,为sacle out、shading留点余地,避开BUG - 避免使用大表的join
- MySQL最擅长的是单表的主键/二级索引查询
Join消耗较多的内存,产生临时表 - 避免在数据库中进行数学运算
容易将业务逻辑和DB耦合在一起,MySQL不擅长数学运算和逻辑判断
无法使用索引 - 拒绝大SQL,拆分成小SQL
充分利用query cache
充分利用多核CUP
使用in代替or,in的值不超过1000个 - 禁止使用order by rand()
因为ORDER BY rand()会将数据从磁盘中读取,进行排序,会消耗大量的IO和CPU,可以在程序中获取一个rand值,然后通过在从数据库中获取对应的值,使用union all而不是union,程序应有捕获SQL异常的处理机制 - 禁止单条SQL语句同时更新多个表
不使用select * from消耗cpu和IO、消耗网络带宽,无法使用覆盖索引,减少表结构变更带来的影响
行为规范
- 批量导入、导出数据必须提前通知DBA协助观察;
- 批量更新数据,如update,delete操作,需要DBA进行审查,并在执行过程中观察服务负载等各种状况;
- 禁止在从库上执行后台管理和统计类的功能查询; 禁止有super权限的应用程序账号存在;
- 产品出现非数据库导致的故障时及时通知DBA协助排查; 促销活动或上线新功能必须提前通知DBA进行流量评估;
- 数据库数据丢失,及时联系DBA进行恢复; 对单表的多次alter操作必须合并为一次操作,相同类型的写操作合并为一条语句;
- 不在MySQL数据库中存放业务逻辑;
- 重大项目的数据库方案选型和设计必须提前通知DBA参与;、对特别重要的库表,提前与DBA沟通确定维护和备份优先级;
- 不在业务高峰期批量更新、查询数据库; 提交线上建表需求,必须详细注明所有相关SQL。
SQL检查规范【重要】
- 表要有主键
- 表要有备注
- 表不能使用外键
- 限制表名大小写
- 限制表存储引擎
- 限制表使用分区设置
- 表需要包含某些列
- 限制表字符集
- 限制表校验规则
- 表名不能是关键字
- 限制表索引的数量
- 限制表字段的数量
- 限制建表自增初始值
- 字段名不能是关键字
- 限制字段名大小写
- 不能设置列的字符集
- 限制列不能使用部分数据类型
- 列要有注释
- 限制char类型字段长度
- 限制varchar类型字段长度
- 限制列都不可空(not null)
- 限制自增列名字为id
- 限制自增列为无符号
- 限制修改表重命名列
- 限制修改表删除列
- 索引需要设置名字
- 限制Unique索引名格式
- 限制普通索引名格式
- 限制单个索引包含列的个数
- 限制主键包含列的个数
- 限制主键列类型
- 限制修改表删除主键
- 不能使用float/double类型
- 每个列都要有默认值
- 不能设置列的校验集
- 修改表新增字段要求可空
- 修改表新增非空字段时要明确指定默认值
- 限制创建库的字符集
- 限制主键列必须自增
- 限制使用视图
- 限制使用触发器
- 限制使用事件
- 限制使用存储过程
- 限制使用自定义函数
- 修改表字符集建议用alter table convert语法
- 修改表禁止更改数据类型
- 修改表将可空字段改为非空时要明确指定默认值
- 不能使用enum类型(建议用tinyint/char代替)
- 字段禁用zerofill属性
- 修改表禁止删除索引
- 插入语句建议指定insert字段列表
- 插入语句中insert字段名不能重复
- 插入语句不能为not null列插入null值
- 插入语句中insert字段列表要和值列表匹配
- 插入语句限制一条insert values的总行数
- 插入语句检测insert的表/字段是否存在
- 插入语句不建议使用sysdate()函数
- update/delete语句限制多表关联的数量
- update/delete语句建议指定where条件
- update/delete语句检测where条件是否包含子查询
- update/delete语句限制limit数据量大小
- update/delete语句检测多表关联语法是否完整(join遗漏on子句)
- update/delete语句不能有order by子句
- update语句检测set多个列之间的分隔符(and非法)
- update多表时检测set的列是否指定表前缀
- update语句检测涉及表/字段是否存在
- update语句检测是否更新了主键
- update语句检测是否更新了唯一键
- update语句建议同时更新表上的“修改时间”列
- update语句不建议更新表上的“创建时间”列
- select语句建议指定where条件
- 查询DMLDML-插入DML-更新DML-删除
- select语句不建议使用order by rand()
- 查询DMLDML-插入DML-更新DML-删除
- select语句不建议对常量进行group by
- 查询DMLDML-插入DML-更新DML-删除
- select语句不建议对常量进行order by
- 查询DMLDML-插入DML-更新DML-删除
- select语句不建议对不同的表group by或order by
- 查询DMLDML-插入DML-更新DML-删除
- select语句不建议order by多个字段使用不同方向排序
- 查询DMLDML-插入DML-更新DML-删除
- select语句不建议group by或order by表达式或函数
- 查询DMLDML-插入DML-更新DML-删除
- select语句不建议使用union
- 查询DMLDML-插入DML-更新DML-删除
- select语句限制多表关联的数量
- 查询DMLDML-插入DML-更新DML-删除
- select语句限制limit的offset大小
- 查询DMLDML-插入DML-更新DML-删除
- select语句不建议使用having子句
- 查询DMLDML-插入DML-更新DML-删除
- where条件中不建议索引字段包含数学运算或函数运算
- 查询DMLDML-插入DML-更新DML-删除
- where条件中不建议使用前通配符查找
- 查询DMLDML-插入DML-更新DML-删除
- where条件中检测没有通配符的like语句
- 查询DMLDML-插入DML-更新DML-删除
- where条件中不建议使用反向查询(not in / not like)
- 查询DMLDML-插入DML-更新DML-删除
- where条件中限制in子句包含元素个数
- 查询DMLDML-插入DML-更新DML-删除
- where条件中检测字段是否存在隐式类型转换
- 查询DMLDML-插入DML-更新DML-删除
- where条件中检测是否通过“or”操作符连接过滤条件