常见数据库设计规范

数据库命名规范

1、所有数据库对象名称必须使用小写字母 + 下划线分割；（因为MySQL是大写敏感）；

2、所有数据库对象名称禁止使用MySQL的保留关键字，如：SELECT、FROM等；

3、数据库对象命名做到见名知意，长度最好不要超过32个字符；如：user_account；

4、临时表必须以tmp_为前缀，并以日期为后缀；备份库、备份表必须以bak_为前缀并以日期为后缀；这样方便查看和删除操作；

5、所有存储相同数据的列名和列类型必须一致；避免在表联查时索引失效；

数据库基本设计规范

1、所有表必须使用Innodb存储引擎，MySQL5.5之前是Myisam（默认存储引擎）情况，5.6之后默认是Innodb；Innodb是支持事务、行级锁。（二者区别？）

2、数据库和表的字符集统一使用UTF-8；字符集统一，避免字符转换乱码；UTF-8中汉字占用3个字节，ASCII码占1个字节；

3、所有表和字段都需要添加注释，comment定义；一开始进行数据字典的维护；

4、尽量控制控制单表数据量的大小，建议在500万以内（分库分表实现）；方便修改表结构、备份、恢复时有问题；单个文件表大小限制是2GB（32位系统）；

5、谨慎使用MySQL分区表，分区表是在物理上表现为多个文件，在逻辑上表现为一个表（充分利用IO，快）；谨慎选择分区表，跨分区查询效率可能更低；

6、尽量做到冷热数据分离，减少表的宽度（限制最多存储4096列），减少磁盘IO，保证热数据的内存缓存命中率；利用更有效的利用缓存，避免读入无效的冷数据；不要使用Select *；垂直拆分，经常一起使用的列放到一个表中；

7、禁止在表中建立预留字段，无法确定其数据类型、字段名等，修改字段类型会锁表，按需增加字段更好；

8、禁止在数据库中存储图片，文件等二进制数据，影响性能；

9、禁止在线上做数据库压力测试；在测试环境压力测试来评估生产环境；

10、禁止从开发环境，测试环境自连生产环境数据库；各环境分开；

数据库索引设计规范（索引---双刃剑）

1、限制每张表上的索引数量，建议每张表索引不超过5个，不要滥用索引；

2、每个Innodb表必须有一个主键；主键顺序和数据存储顺序是一致的；建议不要用更新频繁的列作为主键，不使用UUID，MD5，HASH，字符串作为主键；建议选择自增id作为主键；

3、常见索引列的建议：select、update、delete语句的where从句中的列；包含在order by、group by、distinct的字段；多表JOIN的关联列；

4、选择索引列的顺序：索引是从左到右的顺序来使用的，区分度最高（唯一率）的列放在联合索引的最左侧；尽量把字段长度小的列放在联合索引的最左侧；使用最频繁的列放到联合索引的左侧；

5、避免建立冗余索引（？）和重复索引（？）；

6、对于频繁的查询优先考虑使用覆盖索引（就是包含了所有查询字段的索引）；好处：避免Innodb表进行索引的二次查找；可以把随机IO变为顺序IO加快查询率；

7、尽量避免使用外键约束，在表与表之间的关联键上建立索引；逻辑外键替代物理外键；

数据库字段设计规范

1、优先选择存储需要的最小的数据类型；将字符串转化为数字类型存储；如IP地址转化：INET_ATON('192.163.2.12')=43262344，INET_NTOA(43262344)='192.163.2.12'；

对于非负型的数据来说，要优先使用无符号整型来存储；如：SIGNED INT 范围：-2147483648~2147483647，UNSIGNED INT 范围：0~4294967295；

VARCHAR(N)中N指的是字符数，不是字节数；

使用UTF8存储汉字Varchar(255)=765个字节，（即：1个汉字=3个字节）

过大的长度会消耗更多的内存； 

避免使用TEXT、BLOG数据类型；建议把这些类型的列分离到单独的扩展表中；且只能使用前缀索引；

避免使用ENUM数据类型，

2、尽量把所有列定义为NOT NULL；索引NULL列需要额外的空间来存储，所以要占用更多的空间；

3、不建议使用字符串存储日期类型的数据，无法使用日期函数和比较，字符串会占用更多的空间；建议使用TIMESTAMP或DATETIME类型存储日期；

TIMESTAMP范围：1970-01-01 00:00:01~2038-01-19 03:14:07，占用4个字节，超出范围使用DATETIME；

4、财务xiang相关的金额类数据，必须使用decimal类型；大小由其宽度确定，可存储比BigInt更大的整型数据，非精准类型：float、double；

数据库SQL开发规范

1、建议使用预编译语句（PREPARE）进行数据库操作，可以重复使用执行计划（一次编译，多次运行），减少sql编译的时间，还可以避免sql注入；

2、避免数据类型的隐式转换，可能导致索引失效；

3、充分利用表存在的索引：

避免使用双%、左%的查询条件，如a like ‘%123%’；

一个sql只能利用到复合索引中的一列进行范围查找；

使用left join或者not exists来优化not in操作；

4、程序链接不同的数据库使用不同的账号，禁止跨库查询，目的：为数据库迁移和分库分表留出余地，降低业务耦合度，避免权限过大而产生的安全风险；

5、禁止使用select * ，必须使用select <字段列表>查询；因为消耗更多的CPU、IO、网络带宽资源，无法使用覆盖索引；可减少表结构变更带来的影响；

6、禁止使用不含字段列表的INSERT语句，减少表结构变更带来的影响；

7、避免使用子查询，可以把子查询优化为join操作；因为子查询的结果集无法使用索引，子查询会产生临时表操作，太大时会影响效率，消耗的CPU和IO资源；

8、避免使用join关联太多的表，最多61个表，建议不超过5个；

9、减少同数据库的交互次数，合并和批量操作可以提高效率，；

10、使用in代替or，in的指不要超过500个，in操作可以利用索引，

11、禁止使用order by rand()进行随机排序，会把表中所有符合条件的数据装载到内存中进行排序；推荐在程序中获取一个随机值，然后从数据库中获取数据的方式；

12、where从句中禁止对列进行函数转换和计算，会导致索引失效；

13、在明显不会有重复值时使用UNION ALL而不是UNION，UNION会把所有数据放到临时表中后再进行去重操作，UNION ALL不会对结果集进行去重操作；

14、拆分复杂的大SQL为多个小SQL；

数据库操作行为规范

1、超100万行的批量写操作，要分批多次进行操作；可能导致严重的主从延迟，会产生大量的日志；避免产生大事务操作；

2、对于大表使用pt-online-schema-change修改表结构，避免延迟；

3、禁止为程序使用的账号赋予super权限；

4、对于程序链接数据库账号，遵循权限最小原则；一般不能有drop权限；