数据库设计规范

• 数据库命名规范
• 基本设计规范
• 索引设计规范
• 字段设计规范
• sql开发规范
• 数据库操作行为规范

命名规范

库名，表名：

• 必须用小写字母，并用下划线分割
• 不能使用保留关键字
• 要见名识义，不超过32个字符

eg：xj_userdbb 香江的user数据库

eg：user_account 用户账号表

临时库表：必须是以tmp为前缀，并以日期为后缀

备份库，备份表：必须以bak为前缀，并以日期为后缀

所有存储相同数据的列名和列类型必须一致（为了清晰，以及防止索引失效，从而影响性能）

基本设计规范

• 所有表必须使用InnoDB存储引擎
• 数据库和表的字符集统一使用UTF8（兼容更好，避免乱码）
• 所有表和字段都需要添加注释（方便维护）
• 单表数量的大小，控制在500万行内（数据归档，分库分表）
• 谨慎使用mysql分区表（建议不用分区表）show plugins 找有没有 partition
• 冷热数据分离，减小表的宽度（mysql最多存4096列）
• 禁止在表中建立预留字段（难以见名识义和确认数据类型）
  • 对预留字段类型的修改，会对全表进行锁定（十分影响并发性能）
• 禁止在数据库中存储图片，文件等二进制数据库
• 禁止在生产数据库进行压测
• 禁止在开发，测试环境直接连接生产数据库

索引设计规范

• 每张表索引不超过5个
• 禁止给表中的每一列都建立单独的索引
• 每个InnoDB表必须有一个主键（使用自增ID）
  • 不能用更新频繁的列作为主键，不能用多列主键
  • 不用UUID，MD5，HASH，字符串列作为主键

怎么建立索引

1. select，update，delete语句的where从句中的列
2. 包含order by，group by，distinct中的字段
3. 多表join的关联列

如何选择索引列的顺序

1. 区分度最高的列放在联合索引的最左侧
2. 尽量把字段长度小的列放在联合索引的最左侧
3. 使用最频繁的列放在联合索引的最左侧

避免建立冗余索引和重复索引，eg：primary key(id)，index（id），unique index（id）

对于频繁的查询优先考虑使用覆盖索引（包含了所有查询字段的索引）

• 查询商品库存
• 所有查询字段就是where里面的列，再加上select里面的列，和order by，group by里面的列
• 避免InnoDB表进行索引的二次查找
• 可以把随机IO变为顺序IO加快查询效率
• 尽量避免使用外键，（不建议使用外键约束，但一定要在表与表之间的关联键上建立索引）
  • 外键用于保证数据的参照完整性，但是建议在业务端实现数据一致性，而不是依赖外键

字段设计规范

优先选择符合存储需要的最小的数据类型

• 将字符串转化为数字类型存储（ip转化为整型数据）
  • INET_ATON('255.255.255.255') = 4294967295
  • INET_NTOA(4294967295) = '255.255.255.255'
• 对于非负数据，优先用无符号类型
• varchar(N)里面的N是字符数，不是字节数。eg：255就是255个中文字符
• 字符的长度越长，浪费的内存越多

避免使用TEXT、BLOB数据类型

避免使用枚举类型

尽量将列定义为not null

要用timestap和datetime存储时间

• timestap：1970~2038
• 超出的范围用datetime

财务相关金额数据，用decimal类型

sql开发规范

使用预编译语句进行数据库操作

• 减少生成执行计划，防止sql注入

避免数据类型的隐式转换

• where id = '111'，id列是整型的，参数是字符串，就会导致索引失效

充分利用表上已经存在的索引

• 避免使用前置%号的查询条件，a like '%123'
• 一个sql只能利用到复合索引中的一列进行范围查询
• 使用left join和not exists来优化not in操作

程序连接不同的数据库要使用不同是数据库账号，禁止跨库查询

禁止使用select *

禁止使用不含字段列表的insert语句

• 错误：insert into t values ('a','b','c');
• 正确：insert into t(c1,c2,c3) values ('a','b','c');

避免使用子查询，将子查询优化成join操作

• 子查询的结果无法使用索引
• 子查询会产生临时表，消耗cpu和io

避免使用join关联太多表，一般三张

减少同数据库的交互次数

• 一次查100条数据库分页10页，而不是一次查1页，查十次

使用in代替or（in里面不超过500个）

禁止使用order by rand()进行随机排序

• 会把表中所有符合条件的数据都加载到内存中jin
• 推荐在程序中获取一个随机值，然后从数据库中获取数据

禁止在where从句中对列进行函数转换和计算

• 对列进行函数转换和计算导致无法使用索引，比如where data(createtime) = '20160901'
• 改成where createtme >= '20160901' and createtime < '20160902'

在明显不会用重复值的情况下，使用UNION ALL而不是UNION

• UNION会把所有数据放到临时表中后再进行去重操作
• UNION ALL不会再对结果集进行去重操作

拆分复杂sql为多个小的sql

• mysql一个sql只能使用一个cpu计算
• 多个sql就能使用多个cpu计算

数据库操作行为规范

超过100万行的批量写操作（update，delete，insert），要分批多次进行操作

• 大批量操作会造成严重主从延迟
• binlog日志为row格式会产生大量日志
• 产生大事务的操作，对其他连接造成影响

对于大表使用pt-online-schema-change修改表结构

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

对于程序连接数据库账号，要遵循权限最小原则

• 程序使用的所有账号，不得有drop权限

数据库设计

• 逻辑设计
• 物理设计（表名，字段名，字段类型）