数据库设计规范

数据库设计的实现逻辑

1、数据结构设计

逻辑设计 -> 物理设计

2、 实际工作中

逻辑设计 + 物理设计

3、 物理设计

表名 + 字段名 + 字段类型

数据库设计规范设计规范主要包括以下内容：

数据库命名规范

1、所有的数据库对象名称必须使用小写字母并用下划线分割，主要是防止不同的操作系统产生的错误
2、所有的数据库对象名称必须禁止使用数据库保留字
3、数据库对象的命名要能简明释义，并且最好不要超过32个字符
例如：用户数据库 mc_userdb 用户账号表 user_account
临时表必须以tmp 为前缀并以日期为后缀
备份库，备份表必须以bat 为前缀，并以日期为后缀

数据库基本设计规范

1、所有表必须使用innODB存储引擎 ，支持事务，行级锁、更好的恢复性，高并发下性能更佳
2、数据库和表字符集同意使用utf-8，统一字符集避免产生字符集转换产生乱码
3、MySQL中utf-8字符集汉字占用3个字节
4、所有表和字段都需要添加注释，保证后期的维护
5、尽量控制单表的数据量的大小，建议控制在500万以内，修改表结构。备份、恢复数据都会产生很大的问题，5百万并不是MySQL数据库的限制，MySQL最多可以储存多少条数据取决于储存设置和文件系统，解决方法：可以使用历史数据的归档，分库分表等手段来控制数据量的大小
6、谨慎使用MySQL分区表
：分区表在物理上表现为多个文件，在逻辑上表现为一个表
：谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据，但是这样的复杂度更高
7、尽量做到冷热数据分离，减少表列数
：减少磁盘IO，保证热数据的内存缓存命中率
：利用更有下的利用缓存，避免读入无用的冷数据
：经常一起使用的列放到一个表中
8、禁止在表中建立预留字段
：预留字段的命名很难做到见名识义
：预留字段无法确认储存的数据类型，所以无法选择合适的类型
：对预留字端类型的修改，会对表进行锁定
9、禁止在数据库中存储图片，文件等二进制数据，禁止在线上做数据库压力测试，禁止从开发环境，测试环境连生产环境的数据库

总结

所有表字段必须使用innODB存储引擎
所有表字段都要有备注信息，并使用utf-8字符集
要做到控制表的大小，并把冷热数据进行分离
禁止使用预留字段及在数据库表中存储大的二进制

数据库索引设计规范

索引对数据的查询性能来说是非常重要的，好的而索引可以提供数据的性能，反之，降低数据库的性能
1、不要滥用索引
限制每张表上的索引的数量，建议单张表的索引的数量超过五个，当然，索引的列的数量与列成正比
2、每个innODB表必须有一个主键
：不要使用更新频繁的列作为主键，不使用多列主键
：不用UUID,MD5,HASH,字符串列作为主键
：主键建议选择使用自增ID作为主键
3、常见索引列建议
：select 、update、delete语句中where从句中的列
：包含order by 、group by 、distinct 的字段
：多表join 的关联列
4、如何选择索引列的顺序
索引列的顺序，数据库是从左到右的顺序来使用的，联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。
当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引, 联合索引的话, 它往往计算的是第一个字段(最左边那个), 这样往往会走错索引. 如:
索引Index_1(Create_Time, Category_ID), Index_2(Category_ID)
：区分度最高的列放在联合索引的最左侧
：尽量把字段长度最小的列放在联合索引的最左侧
：使用最频繁的列放在联合索引的左侧

5、避免建立冗余的索引和重复索引
：重复索引：例如：primary key(is) 、index(id)、unique index(id)
：冗余索引 例如：index(a,b,c) 、index(a,b),index(a)
6、对于频繁查询的索引优先考虑使用覆盖索引
覆盖索引：就是包含所有的查询索引
好处：避免innODB表进行索引的二次查询，可以把随机IO变为顺序IO，可以加快查询的效率
7：尽量避免使用外键
不建议使用外键约束，但一定在表与表之间的关联键上建立索引

总结

每个innODB 表都需要一个主键
限制表上的索引的数量，避免建立重复和冗余的索引
注意合理选择复合索引键值的顺序

数据库字段设计规范

字段类型的选择，会直接影响数据库的性能。

1、字段优先选择符合存储需要的最小数据类型，对于非负整形，要优先使用无符号整形来存储，无符号相对于有符号可以多出一倍的存储空间
：将字符串转化为数字类型存储
例子：将IP地址转化为整数
INET_ATON(‘255.255.255.255’) = 4294967295;
INET_NTOA(4294967295) = 255.255.255.255;
：VARCHAR(N)中的是字符数，而不是字节数
：使用UTF-8存储汉字，varchar(255) = 765个字节
：过大的长度会消耗更多的内存

2、 避免使用TEXT、BLOB数据类型
： 如果使用，建议把BLOB或是TEXT列分离到单独的扩展表中
：TEXT 或者BLOB类型只能使用前缀索引
前缀索引： 选择索引列的最左n个字符来建立索引。这样就大大节约了索引空间，进而提高索引效率。
缺点：MySQL无法使用前缀索引做ORDER BY 、GROUP BY 和覆盖扫描。
3、避免使用ENUM数据类型
：修改ENUM值需要使用ATER语句
：ENUM类型的order by 操作效率低，需要额外操作
：禁止使用数值作为ENUM的枚举值
4、尽可能把所有列定义为 非空
：索引null列需要额外的空间来保存，所以，要占用更多的空间
：进行比较和计算时要对NUll值做特别的处理

数据库SQL开发开发规范

1、建议使用预编译语句进行数据库操作
：只传参数比传递SQL语句更高效
：相同语句可以一次解析多次使用，提高处理效率
：防止SQL注入
2、避免数据类型的隐式转换
：隐式转换会导致索引失效
例子：
selec * from table where ID = ‘123123’；
3、充分使用表上的已经存在的索引
：避免使用双%号的查询条件，如 name ike %繁%；
：一个SQL只能利用到复合索引中的一列进行范围查询
：使用left join 或 not exits 来优化not in 操作
4、数据库设计时，应该要考虑以后数据库的扩展
5、程序连接不同的数据库使用不同的账号，禁止跨库查询

：为数据库分库分表和迁移留出空间
：降低业务的耦合度
：权限过大的风险
6、禁止使用select * ，必须使用select < 字段名> 查询
：消耗更多的CPU和IO以及网络宽带资源
7、避免使用子查询，可以把子查询优化为join操作
：子查询的结果集无法使用索引
：子查询会产生临时表操作，如果子查询数据量很大，则严重影响效率
：消耗过多的CPU及IO资源
8、避免使用join太多的表
：每关联一个表会多占用一部分内存（join_buffer_size）
:会产生临时表操作，影响查询效率
：MySQL最多允许关联61个表，但建议不超过5个
9、减少同数据库的交互次数
：数据库更适合处理批量操作
：合并多个相同的操作到一起没可以提高处理效率
10、使用in 代替or
:in 的值不要超过500
:in操作可以有效利用索引
11 、禁止使用order by rand() 进行随机排序
：会把表中所有符合条件的数据加载到内存中进行排序
：会消耗大量的CPU和IO及内存资源
：推荐在程序中获取一个随机值，然后从数据库中获取数据的方式
12、where从句中禁止对列进行函数转换和计算
：对列进行函数转换或计算会导致无法使用索引
例子：
where date(createtime) = “20170302”
13、在明显不会有重复值是使用union all 而不是union
：union 会把所有数据放到临时表表中后再进行去重操作
：union all 不会再对结果集进行去重操作
14、拆分复杂的SQL分为多个小SQL
MySQL 一个SQL只能使用一个CPU进行计算
SQL拆分可以通过并行执行来提高处理效率

数据库操作行为规范

1、超过一百万行的批量写操作，要分批多次进行操作
：大量的操作可能会造成严重的主从延迟
：binlog日志为row格式时，会产生大量的日志
：避免产生大事务操作
2、对大表数据结构的修改一定要慎重，会造成严重的锁表操作。尤其时生产环境没事不能忍受的
：对于大表使用pt-online-schema-change 修改表结构
3、禁止为程序使用的账号赋予SUPER权限，当连接数限制时，还允许1个有super 权限的用户连接，super权限只能留给DBA处理问题的账号使用
4、对于程序连接数据库账号，遵循权限最小原则
：程序使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号原则上不准有drop权限