数据库设计规范

命名规范

1、库名、表名、字段名必须使用小写字母并采用下划线分割；

2、库名、表名、字段名支持最多32个字符，统一规范、易于辨识以及减少传输量不要超过32；

3、库名、表名、字段名禁止使用MySQL/Oracle/PostgreSql/MongoDB等本数据库保留关键字；

4、临时库、临时表名必须以tmp为前缀并以日期为后缀；

5、备份库、备份表名必须以bak为前缀并以日期为后缀；

6、索引命名idx_字段1_字段名2，索引名的最大长度为30字节；

基本规范

1、使用INNODB存储引擎(Mysql)

  支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高；

2、必须使用UTF8或UTF8MB4字符集;

  万国码，无需转码，无乱码风险，节省空间；

3、表、字段必须有comments（中文注释）;

  中文注释信息必须保证完整、明确和准确；

  状态、标识等类型字段，必须列清全部中文标识，确保存储值与字段注释一致；

  表和字段含义发生变更时，comments（中文注释）必须做同步修改；

4、不在数据库中存储图片、文件等大体量数据；

5、禁止在生产环境做数据库压力测试(在预发版环境测试)；

6、禁止从测试、开发环境直连线上数据库；

7、禁止使用触发器、Event，大量数据处理禁止使用存储过程、视图;

库表设计规范

1、表必须有主键，例如自增主键

  a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用;

  b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率;

  c） 无主键的表删除，在row模式的主从架构，会导致备库夯住;

2、单实例表数目必须小于500

3、单表列数目必须小于30

4、按日期时间分表需符合YYYY[MM][DD][HH]格式

5、禁止使用外键，如果有外键完整性约束，需要应用程序控制

  外键用来保护参照完整性，可在业务端实现，对父表和子表的操作会相互影响，降低可用性，甚至会造成死锁。

索引设计规范

索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间

1、单张表中索引数量不超过5个

  索引不是越多越好，按实际需要进行创建，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能；

2、单个索引中的字段数不超过5个

  对字符串使用前缀索引，前缀索引长度不超过10个字符；如果有一个CHAR(200)列，如果在前10个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10个字符进行索引能够节省大量索引空间，也可能会使查询更快；

3、创建复合索引时, 必须把区分度高的字段放在前面

4、禁止在更新十分频繁、区分度不高的属性上建立索引

5、避免冗余或重复索引

  合理创建联合索引（避免冗余），index(a、b、c)相当于index(a)、index(a、b)、index(a、b、c)；

字段设计规范

1、禁止使用TEXT、BLOB类型

  会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能；

2、用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

  浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；浮点数的缺点是会引起精度问题

3、字段必须定义合适的数据类型

  只存储数字的字段定义成数字类型，只存储字符的字段定义成字符类型，只存储日期的字段定义成日期类型，以减少使用过程中的数据类型转换

4、禁止使用ENUM，可使用TINYINT代替

  a）增加新的ENUM值要做DDL操作

  b）ENUM的内部实际存储就是整数

5、字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量

  VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N；

  VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存；

6、尽量将所有字段均定义为not null且提供默认值

  a）null 的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化;

  b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多;

  c）null 值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识;

  d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录;

7、使用TIMESTAMP存储时间. 因为TIMESTAMP使用4字节，DATETIME使用8个字节,同时TIMESTAMP具有自动赋值以及自动更新的特性。

SQL设计规范

1、使用预编译语句prepared statement

  只传参数，比传递SQL语句更高效，一次解析，多次使用，降低SQL注入概率；

2、 尽量避免相同语句由于书写格式的不同，而导致多次语法分析

3、避免隐式转换

  会导致索引失效，如select userid from table where userid=’ 1234’；

4、禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性

5、避免使用两个大表的join, 禁止大表使用子查询

  MySQL最擅长的是单表的主键/二级索引查询，大表Join会产生临时表，消耗较多内存与CPU，极大影响数据库性能；

6、禁止在WHERE条件的属性上使用函数或者表达式

  该使用方式会导致数据库数据检索无法使用索引；

7、拒绝大SQL，拆分成小SQL

  充分利用query cache，充分利用多核CUP；

8、考虑使用limit N，少用limit M,N，特别是大表或M比较大的时候

9、减少或避免排序，尽量利用索引本身的有序

10、使用 union all 而不是 union

  尽量使用UNION ALL，减少使用UNION，因为UNION ALL不去重，而少了排序操作，速度相对比UNION要快，如果没有去重的需求，优先使用UNION ALL；

11、应用程序应有捕获SQL异常的处理机制

12、禁止使用全表查询，配置表和小表（数据总量小于1万条）例外。

  如果数据量比较小或认为不会超过10000条数据，也最好加上LIMIT限制；

13、禁止使用SELECT *，只获取必要的字段，需要显示说明列属性

  读取不需要的列会增加CPU、IO、NET消耗，不能有效的利用覆盖索引，减少表结构变更带来的影响；

14、同表的增删字段、索引合并一条DDL语句执行，提高执行效率，减少与数据库的交互。

15、禁止在sql中存在调用存储过程、复杂函数、视图；

  例：select id,存储过程(参数1....) from table;

16、只限oracle数据库：with as 也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的，而且必须要和其他sql一起使用才可以！