数据库设计规范

近段时间看了个mysql规范的学习视频。分享分享

1. 数据库命名规范
2. 数据库基本设计规范
3. 数据库索引设计规范
4. 数据库字段设计规范
5. 数据库SQL开发规范
6. 数据库操作行为规范

数据库命名规范

规范一：所有数据库对象名称必须使用小写字母并用下划线分割。
原因： mysql大小写敏感，全部命名小写方便开发。
例子：不同数据库名DbName 和 dbname是两个不同数据库。

规范二：所有数据库对象名称禁止使用MySQL保留关键字。
原因：语句识别不了，如果硬要使用，需要使用转移符号标注。
例子：select id,from,age from table;mysql他会查询报错。
参考：http://dev.mysql.com/doc/refman/5.7/en/keywords.html。

规范三：数据库对象的命名要能做到见名识义，并且不要操作32个字符。
原因：占空间并且不好维护。
例子：用户数据库mc_userdb(慕课用户的db)；用户账号表user_account()。

规范四：临时表必须以tmp为前缀并以日期为后缀。

规范五：备份表，备份表必须以bak为前缀以日期为后缀。

规范六：所有存储相同数据的列名和列类型必须一致。
原因：可以加快查询，因为大部分相同的列名，是用来关联的，查询的时候，关联字段会通过对比统一，如果两个字段的属性不同，mysql会转换同一类型作比较。

数据基本设计规范

规范一：所有表必须使用Innodb引擎。
原因：MySQL5.6以后为默认引擎，支持事物，行级锁，很好的恢复性，高并发性能更好。

规范二：数据库和表的字符集统一使用UTF8。
原因：统一字符集可以避免由于字符集转换产生的乱码。
注意：MySQL中utf8字符集汉字占用3个字节，ASCII码占用1个字节。

规范三：所有表和字段都需要添加注释。
原因：从一开始就进行数据字典的维护。

规范四：尽量控制单标数据的大小，建议控制在500万以内。
注意：500万并不是MySQL数据库的限制，限制取决于存储设置和文件系统。j

** 规范五**：谨慎使用MySQL分区表。
原因：分区表在物理上表现为多个文件，在逻辑上表现为一个表。
例如：分区订单表，以订单号作为分区标识，如果你查找某个人的订单，可能就需要遍历所有分区，这样物理分表方式管理大数据表更好。（看情况而定）

规范六：尽量做到冷热数据分离，减少表的宽度。
原因：减少磁盘IO，保证热数据的内存缓存命中率。磁盘IO影响MySQL性能是很大的。利用更有效的缓存，避免读入无用的冷数据。
注意：Mys限制最多存储4096列。

规范七：禁止在表中简历预留字段。
原因：预留字段的命名很难做到见名识义，无法确定存储的数据类型，对预留字段类型的修改，会对表进行锁定，加列比修改开销更大。

规范八：禁止在数据库中存储图片，文件等二进制数据。
原因：IO耗时长。

规范九：禁止在线上做数据库压力测试。
原因：上线环境影响正常业务。生成垃圾数据。

规范十：禁止从开发环境链接线上环境，需要分离连接。

索引设计规范

规范一：限制每张表的索引数量，建议单张表索引不超过5个
原因：索引并不是越多越好，索引可以提高效率同样可以减低效率
注意：InnoDB按照主键索引的顺序来组织表的，如果没主键，会使用第一个非空作主键，没有的话，会生成一个6个字节的列作隐藏主键。不要使用uuid，md5，hash，字符串列作为主键，索引顺序会乱，导致IO操作变多。
建议：常见索引列1、夺标join的关联列，2、select或update

规范二：联合索引区分度最高的列放在联合索引的最左侧

规范三：联合索引尽量把字段长度小的列放在联合索引的最左侧

规范四：联合索引使用最频繁的列放到联合索引的左侧（基于二和三）

规范五：对于频繁的查询有限考虑使用覆盖索引
注意：覆盖索引就是把频繁查询的列也写进索引，那么MySQL就不需要在执行二次查询，直接使用索引查询到的信息返回给用户。

规范六：尽量避免使用外键
原因：外键会影响父表和子表的写操作从而减低性能。
注意：不建议使用外键约束，建议使用索引；并且实现关联可以在业务逻辑上实现。

数据库字段设计规范

规范一：优先选择符合存储需要的最小的数据类型
注意：varchar（N）中的N代表的是字符数，而不是字节数。使用utf8存储汉字VARCHAR(255)=765g个字节。
例子：
1、将字符串转化为数字类型存储；inet_aton(‘255.255.255.255’) = 4294967295;inet_ntoa(4294967295) = ‘255.255.255.255’。
2、对于非负数据采用无符号整形存储；有符号和无符号占用的内存空间前者是后者的两倍；

规范二：避免使用text、blob数据类型
建议1：建议把blob或是text分离到单独的扩展表中，text和blob只能使用前缀索引。

规范三：避免使用enum数据类型
原因：enum数据类型，存储形式内部是以整形存储的。使用order by操作率低，需要转为整形在进行排序。修改enum值需要使用alter语句，多列的修改，出现的IO操作会变多，而且会出现操作失误的风险。

规范四：尽可能把所有列定义为not null
原因：索引null列需要额外的空间保存，所以要占用更多的空间；进行比较和计算时要对null值做特别的处理；null存储内存比not null要大。

规范五：使用timestamp或datetime类型存储时间
原因：字符串存储要十一个字节，但datetime只需要八个，无法使用日期函数进行计算和比较。

规范六：财务相关的金额类数据，必须使用decimal类型
注意：占用空间由定义的宽度决定。

数据库SQL开发规范

规范一：建议使用预编译语句进行数据库操作
原因：减少网络带宽，一次解析多次使用，只穿参数，比传递sql更快，避免sql注入；避免数据类型的隐式转换；
例如：select id from table where id = ‘113’;

规范二：充分利用表上已经存在的索引
例如：避免使用双%号的查询条件；一个sql只能利用到符合索引中的一列进行范围查询（联合索引范围查询字段放右侧）；使用left join或not exists来优化not in操作；

规范三：程序连接不同的数据库使用不同的账号，禁止跨库查询
原因：为数据库迁移和分库分表留出余地；降低业务耦合度；避免权限过大导致出的风险；

规范四：禁止使用select *查询
原因：消耗内存；无法使用覆盖索引；当新增新的列时，出现不必要的错误；

规范五：避免使用子查询，可以把子查询有华为join操作
原因：子查询的结果集无法使用索引；子查询会阐释临时表，如果查询数据量大则严重影响效率；消耗过多的CPU的IO资源；（如果子查询是简单的查询可以适当使用）

规范六：避免使用JOIN关联太多的表
原因：每Join一个表会多占用一部分内存；会产生临时表操作，影响查询效率；MYSQL最多允许关联61个表，建议不超过5个；

规范七：减少通数据库的交互次数
原因：数据库更适合处理批量操作；合并多个相同的操作到一起，可以提高处理效率；

规范八：使用in代替or
原因：in的值不要超过500个；in操作可以有效的利用索引；

规范九：禁止使用order by rand（）进行随机排序
原因：会把表中所有符合条件的数据装载到内存中进行排序；会消耗大量CPU和IO及内存操作；推荐在程序中获取一个随机值，然后从数据库中获取随机数据。

规范十：where从句中禁止对列进行函数转换和计算
原因：对列进行函数转换或计算会导致索引失效；
例子：where date（createtime）=‘20160901’和where createtime>=‘20160901’ and createtimr<'20160902’是一样的效果；

规范十一：在明显不会有重复值时使用union all而不是union
原因：union会把所有数据放到临时表中后会进行去重操作；union all不会对数据进行操作；

规范十二：拆分复杂的大SQL为多个小SQL；
原因：MYSQL一个sql只能使用一个cpu进行计算；sql拆分后可以通过并执行来提高处理效率；

数据库操作行为规范

规范一：超100万行的批量写操作，要分批多次进行操作
原因：大批量操作可能会造成严重的主从延迟；binlog日志为row格式时会产生大量日志；避免产生大事务操作；

规范二：对大表数据库使用pt-online-schema-change修改表结构
原因：避免达标修改产生的主从延迟；避免在对表字段进行修改时进行锁表；pt-online-schema-change是在修改表时，会生成一个新表，然后再从旧表中复制数据到新表， 每新增一条会带有一个时间很短的时间锁，分批次进行修改。

规范三：禁止为程序使用的账号赋予super权限
原因：当达到最大连接数限制时，还允许1个有super权限的用户连接；super权限只能留给DBA处理问题的账号使用。

规范四：对于程序连接数据库账号，遵循权限最小原则
原因：程序使用数据库账号只能在一个DB下使用，不准跨库；程序使用的账号原则上不准有drop权限。（系统几乎不会用上drop等操作，处理数据维护）