一. 数据库设计规范

1. 数据库命名规范

规范	原因

所有数据库对象名称必须使用小写字母并用下划线分隔 | Mysql是对大小写敏感的
所有数据库对象名称禁止使用Mysql保留关键字 | 对SQL查询造成影响,关键字查询地址
数据库对象的命名要能做到见名识义,并且最好不要超过32个字符 | 易于开发和维护
临时库表必须以tmp_为前缀并以日期为后缀 | 易于区分临时库表和建立时间
备份库表必须与bak_为前缀并以日期为后缀 | 易于区分备份库表和建立时间
所有存储相同数据的列名和列类型必须一致 | 易于数据库性能,例如:逻辑外键

2. 数据库基本设计规范

规范	原因

所有表必须使用InnoDB存储引擎 | 1. 支持事务,行级锁,更好的恢复性,高并发下性能更好
2. Mysql5.5之前的默认存储引擎是Myisam,5.6的默认引擎是InnoDB
数据库和表的字符集统一使用UTF8 | 1. 兼容性好
2. 避免字符集转换产生的乱码
3. Utf8字符集汉字占用3个字节,ASCII码占用1个字节
所有表和字段都需要添加注释 | 1. 使用comment从句添加表和列的备注
2. 易于数据字典的维护
尽量控制单表数据量的大小,建议控制在500万行以内 | 1. 500万不是Mysql数据的限制,限制取决于存储设置和文件系统
2. 易于备份恢复,性能
3.可以用历史数据归档,分库分表等手段来控制数据量的大小
谨慎使用Mysql分区表 | 1. 分区表在物理上表现为多文件,在逻辑上表现为一表
2. 谨慎选择分区键,跨分区查询效率可能更低
3. 建议采用物理分表的方式管理大数据
尽量做到冷热数据分离,减小表的宽度 | 1. 减小磁盘IO,保证热数据的内存缓存命中率
2. 利用更有效的利用缓存,避免读入无用的冷数据
3. 经常一起使用的列放到一个表中
禁止在表中建立预留字段 | 1. 预留字段的命名很难做到见名识义
2. 预留字段无法确认存储的数据类型,所以无法选择合适的类型
3. 对预留字段类型的修改,会对表进行锁定
禁止在数据库中存储图片,文件等二进制数据 | 对数据库IO性能的影响
禁止在线上做数据库压力测试 | 对正常的业务产生影响和产生垃圾数据
禁止从开发环境,测试环境直连生成环境数据库 | 某些错误操作会影响生产环境的数据

3. 索引设计规范

规范	原因

限制每张表上的索引数量,建议单张表索引不超过5个 | 索引可以提高效率同样可以降低效率(提高查询,降低插入和更新)
禁止给表中的每一列都建立单独的索引 | 设计良好的联合索引比每一列上的单独索引效率要高出很多
避免冗余的索引 | 如：primary key(id)，index(id)，unique index(id)
避免重复的索引 | index(a,b,c)，index(a,b)，index(a)
对于频繁的查询优先考虑使用覆盖索引 | 1. 覆盖索引：就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引
2. 避免Innodb表进行索引的二次查询
3. 可以把随机IO变成顺序IO加快查询效率
尽量避免使用外键约束 | 1. 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引
2. 外键可用于保证数据的参照完整性，但建议在业务端实现
3. 外键约束会影响父表和子表的写操作从而降低性能

常见索引的建议

建议SELECT,UPDATE,DELETE语句的WHERE从句中的列
多表JOIN的关联列
包含在ORDER BY,GROUP BY,DISTINCT中的字段

索引列的顺序的选择

规范	原因

区分度最高的列放在联合索引的最左侧 | 区分度=列中不同值的数量/列的总行数
尽量把字段长度小的列放在联合索引的最左侧 | 字段长度越小，一页能存储的数据量越大，IO性能也就越好
使用最频繁的列放到联合索引的左侧 | 以比较少的建立一些索引

4. 主键的设计(暂时未知完美的解决方案)

InnoDB是按照主键索引顺序来组织表的,所以每个InnoDB表必须有一个主键
不使用更新频繁的列作为主键,不使用多列主键
不使用多列主键。不使用UUID、MD5、字符串列作为主键。
最好选择值的顺序是连续增长的列作为主键

5. 数据库字段设计规范

规范	原因

优先选择符合存储需要的最小的数据类型 | 1. 列的字段越大，建立索引时所需要的空间也就越大差
2. 将字符串转换成数字类型存储
3. VARCHAR(N)的N代表的是字符数,而不是字节数
4. 使用UTF8存储汉字Varchar(255)=765个字节
避免使用TEXT,BLOB数据类型 | 1. Mysql内存临时表不支持TEXT、BLOB这样的大数据类型
2. TEXT或BLOB类型只能使用前缀索引
3. 如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询
避免使用ENUM类型 | 1. 修改ENUM值需要使用ALTER语句
2. ENUM类型的ORDER BY操作效率低，需要额外操作
3. 禁止使用数值作为ENUM的枚举值
尽可能把所有列定义为NOT NULL | 1. 索引列需要额外的空间来保存
2. 进行比较和计算时要对NULL值做特别的处理
使用TIMESTAMP或DATETIME类型存储时间 | 可以用日期函数进行计算和比较
同财务相关的金额类数据必须使用decimal类型 | 在计算时不会丢失精度

6. 数据库SQL开发规范

规范	原因

建议使用预编译语句进行数据库操作 | 预编译语句可以重复使用这些计划，减少SQL编译所需要的时间
避免数据类型的隐式转换 | 隐式转换会导致索引失效.
充分利用表上已经存在的索引 | 1. 避免使用双%号的查询条件。如a like %123%，（如果无前置%,只有后置%，是可以用到列上的索引的）
2. 一个SQL只能利用到复合索引中的一列进行范围查询.如：有 a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到，在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧。
3. 使用left join或 not exists来优化not in操作,因为not in 也通常会使用索引失效
数据库设计时，应该要对以后扩展进行考虑 |
程序连接不同的数据库使用不同的账号，禁止跨库查询 | 1. 为数据库迁移和分库分表留出余地
2. 降低业务耦合度
3. 避免权限过大而产生的安全风险
禁止使用SELECT * 必须使用SELECT <字段列表> 查询 | 1. 消耗更多的CPU和IO以网络带宽资源
2. 无法使用覆盖索引
3. 可减少表结构变更带来的影响
禁止使用不含字段列表的INSERT语句 | 如：insert into values (a,b,c);应使用insert into t(c1,c2,c3) values (a,b,c);
避免使用子查询，可以把子查询优化为join操作 | 1. 通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化
2. 子查询的结果集无法使用索引
3. 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大
4. 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询
避免使用JOIN关联太多的表 | 1. 关联缓存，缓存的大小可以由join_buffer_size参数进行设置
2. Mysql最多允许关联61个表，建议不超过5个
减少同数据库的交互次数 | 1. 数据库更适合处理批量操作
2. 合并多个相同的操作到一起，可以提高处理效率
对应同一列进行or判断时，使用in代替or | in的值不要超过500个in操作可以更有效的利用索引，or大多数情况下很少能利用到索引。
禁止使用order by rand 进行随机排序 | 会消耗大量的CPU和IO及内存资源
WHERE从句中禁止对列进行函数转换和计算 | 对列进行函数转换或计算时会导致无法使用索引,例如:不推荐:where date(create_time)=’20190101’. 推荐:where create_time >= ‘20190101’ and create_time < ‘20190102’
在明显不会有重复值时使用UNION ALL而不是UNION | UNION ALL不会再对结果集进行去重操作,UNION会把两个结果集的所有数据放到临时表中后再进行去重操作
拆分复杂的大SQL为多个小SQL | 大SQL：逻辑上比较复杂，需要占用大量CPU进行计算的SQL
一个SQL只能使用一个CPU进行计算

7. 数据库操作行为规范

规范	原因

超100万行的批量写（UPDATE、DELETE、INSERT）操作，要分批多次进行操作 | 1. binlog日志为row格式时会产生大量的日志
2. 大批量操作可能会造成严重的主从延迟
3. 产生大事务操作,从而导致大量的阻塞
对于大表使用pt-online-schema-change修改表结构 | 1. 避免大表修改产生的主从延迟
2. 避免在对表字段进行修改时进行锁表
禁止为程序使用的账号赋予super权限 | 当达到最大连接数限制时，还运行1个有super权限的用户连接super权限只能留给DBA处理问题的账号使用
对于程序连接数据库账号，遵循权限最小原则 | 程序使用数据库账号只能在一个DB下使用，不准跨库
程序使用的账号原则上不准有drop权限

8. 逻辑设计

宽表模式

将对象的所有属性存储在一个表里
存在的问题
1. 数据冗余：相同的数据在一个表中出现多次
2. 数据更新异常：修改一行中某列的值时，用同时要修改多行数据，如：用户角色，职位等
3. 数据插入异常：部分数据由于缺失主键信息而无法写入表中
4. 数据删除异常：删除某一数据时不得不删除另一个数据，如：删除类型
应用场景
1. 适合存储的数据报表应用