数据库命名规范
- 所有数据库对象名称必须小写。因为MySQL是大小写敏感的,Dbname和dbname是两个不一样的数据库
- 命名要做到见名识意,禁止使用MySQL保留关键字。如from,使用的话就会变成 select name,from from user_table;
- 临时表以tmp_开头,备份表以bak_开头并以时间戳结尾
- 所有存储相同数据的列名和列类型必须保持一致。否则在关联查询中会导致隐式的类型转换,使列索引失效
数据库基本设计规范
- 所有表必须使用Innodb存储引擎。5.6以后的默认引擎,支持事务,行级锁,更好的恢复性,高并发下性能更好
- 数据库和表的字符集统一使用UTF8。统一字符集可以避免由于字符串转换产生的乱码
- 所有的表和字段都需要添加注释。
- 尽量控制单表数据量的大小,建议控制在500万以内。
- 谨慎使用MySQL分区表。谨慎选择分区键,跨分区查询效率更低
- 尽量做到冷热数据分离,减少表的宽度。减少磁盘IO,更有效的利用缓存。
- 禁止在表中建立预留字段
- 禁止在数据库中存储图片,文件等二进制数据
- 禁止在线上数据库做数据库压力测试
- 禁止从开发环境,测试环境直接连生产环境数据库
索引设计规范
- 限制每张表上的索引数量,建议单张表索引不超过5个。禁止给表的每一列都建立单独的索引,索引过多导致写入缓慢
- 每个Innodb表必须有一个主键。不使用更新频繁的列作为主键,不使用多列主键。不使用UUID、MD5、字符串列作为主键
- 常见索引列建议。SELECT、UPDATE、DELETE语句的WHERE从句中的列,包含在ORDER BY 、GROUP BY、DISTINCT中的字段。多表JOIN的关联列
- 如何选择索引列的顺序。区分度最高的列放在联合索引的最左侧。尽量把字段长度小的列放在联合索引的最左侧。使用最频繁的列放到联合索引的左侧
- 避免建立冗余索引和重复索引。
- 对于频繁的查询优先考虑使用覆盖索引。避免Innodb表进行索引的二次查找。可以把随机IO变为顺序IO加快查询效率
- 尽量避免使用外键约束。但一定要在外键上建立索引。外键可用于保证数据的参照完整性,但会影响父表和子表的写操作从而降低性能
数据库字段设计规范
- 优先选择符合存储需要的最小的数据类型。将字符串转换为数据类型存储,如IP。无符号可以比有符号多出一倍的取值范围。VARCHAR(N)中的N表示的是字符数,而不是字节数。虽然存储是按照实际字符串长度,但读入内存时是按照N来读取,过大的定义会消耗更多的内存
- 避免使用TEXT、BLOB数据类型
- 避免使用ENUM数据类型。使用ORDER BY操作效率低。
- 尽可能把所有列定义为NOT NULL。索引NULL列需要额外的空间占用
- 使用TIMESTAMP或DATETIME类型存储时间。TIMESTAMP占用4字节和INT相同
- 同财务相关的金额类数据,必须使用decimal类型。decimal为精准浮点数,计算时不会失去精度
数据库SQL开发规范
- 建议使用预编译语句进行数据库操作。一次解析多次调用,同时可防范SQL注入
- 避免数据类型的隐式转换。隐式转换会导致索引失效。
- 充分利用表上已经存在的索引。使用left join 或 not exist 来优化not in 操作
- 连接不同的数据库使用不同的帐号,禁止跨库查询
- 禁止使用SELECT * 来进行查询,必须使用SELECT <字段列表>查询
- 禁止使用不含字段列表的INSERT语句
- 禁止使用子查询,可以把子查询优化为join操作。子查询返回的结果集无法使用索引
- 避免JOIN关联太多的表。建议不超过5个
- 减少同数据库的交互次数
- 使用 in 代替 or
- 禁止使用 order by rand()进行随机排序
- WHERE从句中禁止对列进行函数转换和计算。对列进行函数计算会导致无法使用索引
- 在明显不会有重复值的时候使用UNION ALL而不是UNION。UNION会把所有数据放到临时表中后再进行去重操作
- 拆分复杂的大SQL为多个小SQL
数据库操作行为规范
- 超100万行的批量写操作,要分批多次进行操作
- 对于大表使用pt-online-schema-change修改表结构。工具会创建一个临时表以及触发器,将数据先分批迁移到临时表,迁移完成后进行锁表、删表、重命名临时表使之成为主表
- 禁止为程序使用的帐号赋予super权限
- 对于程序连接数据库帐号,遵循权限最小原则