一、命名规范
-
库名、表名、字段名必须使用小写字母并采用下划线分割;
-
库名、表名、字段名禁 止超过32个字符,须见名知意;
-
库名、表名、字段名支持最多64个字符,统一规范、易于辨识以及减少传输量不要超过32;
-
库名、表名、字段名禁止使用MySQL保留关键字;
-
临时库、临时表名必须以tmp为前缀并以日期为后缀;
-
备份库、备份表名必须以bak为前缀并以日期为后缀;
二、基本规范
-
使用INNODB存储引擎
5.5版本以后的默认引擘,支持事务,行级锁,更好的恢复性,高并发下性能更好,对多核,大内存,ssd等硬件支持更好
-
表字符集使用utf8mb4( 5.5.3版本以上支持)
-
所有表都需要添加注释,单表数据量建议控制在1000万以内;
-
不在数据库中存储图片、文件等大数据;
-
禁止在线上做数据库压力测试;
-
禁止从测试、开发环境直连数据库;
三、库表设计规范
-
禁止使用分区表
MySQL的分区表实际性能不是很好,且管理维护成本较高
-
拆分大字段和访问频率低的字段,分离冷热数据
-
用HASH进行散表,表名后缀使用十进制数,下标从0开始
首次分表尽量多的分,避免二次分表,二次分表的难度和成本较高
-
按日期时间分表需符合YYYY[MM][DD][HH]格式
-
采用合适的分库分表策略,如千库十个表、十库百表等
-
单表字段数控制在20个以内
四、索引设计规范
索引是一把双刃剑,它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间
-
单张表中索引数量不超过5个
-
单个索引中的字段数不超过5个
对字符串使用前缀索引,前缀索引长度不超过10个字符;如果有一个
CHAR(200)列,如果在前10个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10个字符进行索引能够节省大量索引空间,也可能会使查询更快
-
表必须有主键
-
不使用更新频繁地列作为主键
-
尽量不选择字符串列作为主键
-
不使用UUID、MD5、HASH作为主键
-
默认使用非空的唯一键
-
主键建议选择自增或发号器重要的SQL必须被索引:
SELECT、UPDATE、DELETE语句的WHERE条件列ORDER BY、GROUP BY、DISTINCT的字段多表JOIN的字段
-
区分度最大的字段放在索引前面
-
核心SQL优先考虑覆盖索引
select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖
-
避免冗余或重复索引
合理创建联合索引(避免冗余),index(a,b,c)相当于index(a)、index(a,b)、index(a,b,c)
-
索引不是越多越好,按实际需要进行创建,每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能
-
不在低基数列上建立索引,例如‘性别’
-
不在索引列进行数学运算和函数运算
-
尽量不要使用外键
外键用来保护参照完整性,可在业务端实现, 对父表和子表的操作会相互影响,降低可用性INNODB本身对Online DDL的限制
-
不使用%前导的查询,如like“%xxx”,无法使用索引
-
不使用反向查询,如not in / not like
无法使用索引,导致全表扫描,全表扫描导致bufferpool利用降低
五、字段设计规范
-
尽可能不要使用TEXT、BLOB类型
删除这种值会在数据表中留下很大的"空洞",可以考虑把BLOB或TEXT列分离到单独的表中
-
用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;浮点数的缺点是会引起精度问题
-
将字符转化为数字
-
使用TINYINT来代替ENUM类型
-
字段长度尽量按实际需要进行分配,不要随意分配一个很大的容量
the best strategy is to allocate only asmuch space as you really need
VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择NVARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存
-
如果可能的话所有字段均定义为not null
-
使用UNSIGNED存储非负整数
同样的字节数,存储的数值范围更大。如tinyint有符号为-128-127,无符号为0-255
-
INT类型固定占用4个字节存储
-
使用TIMESTAMP存储时间
因为TIMESTAMP使用4字节,DATETIME使用8个字节,同时TIMESTAMP具有自动赋值以及自动更新的特性
-
使用INT UNSIGNED存储IPV4
-
使用VARBINARY存储大小写敏感的变长字符串
-
禁止在数据库中存储明文密码
六、SQL设计规范
-
使用预编译语句prepared statement
只传参数,比传递SQL语句更高效
一次解析,多次使用
降低SQL注入概率
-
尽量避免相同语句由于书写格式的不同,而导致多次语法分析
-
避免隐式转换
会导致索引失效,如selectuserid from table where userid=’ 1234’
-
充分利用前缀索引
必须是最左前缀
不可能同时用到两个范围条件
-
避免使用存储过程、触发器、EVENTS等
让数据库做最擅长的事
降低业务耦合度,为sacleout、shading留点余地
避开BUG
-
避免使用大表的join
MySQL最擅长的是单表的主键/二级索引查询
Join消耗较多的内存,产生临时表
-
避免在数据库中进行数学运算
容易将业务逻辑和DB耦合在一起
MySQL不擅长数学运算和逻辑判断
无法使用索引
-
减少与数据库的交互次数
Insert…on duplicate key update
Replace into、insert ignore、insert into values(),(),()…
Update…where id in (1,2,3,4)
Alter table tbl_name add column col1, addcolumn col2
-
拒绝大SQL,拆分成小SQL
充分利用querycache
充分利用多核CUP
-
使用in代替or,in的值不超过1000个
-
禁止使用order by , rand()
因为order by,rand()会将数据从磁盘中读取,进行排序,会消耗大量的IO和CPU,可以在程序中获取一个rand值,然后通过在从数据库中获取对应的值
-
使用union all而不是union
-
程序应有捕获SQL异常的处理机制
-
禁止单条SQL语句同时更新多个表
-
不使用select *
消耗cpu和IO、消耗网络带宽
无法使用覆盖索引
减少表结构变更带来的影响
七、行为规范
-
任何数据库的线上操作,必须走工单
-
禁止在主库上执行统计类的功能查询;
-
禁止有super权限的应用程序账号存在;
-
有大规模市场推广、运营活动必须提前通知DBA进行流量评估;
-
对单表的多次alter操作必须合并为一次操作;
-
不在MySQL数据库中存放业务逻辑;
-
重大项目的数据库方案选型和设计必须提前通知DBA参与;
-
数据必须有备份机制;
-
不在业务高峰期批量更新、查询数据库;