数据库操作规范_生产环境数据库操作规范

数据库命名规范

• 所有数据库对象名称必须小写。因为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权限
• 对于程序连接数据库帐号，遵循权限最小原则