高性能可扩展MySQL数据库设计及架构优化 电商项目（慕课）第1章 数据库开发规范的制定
课程准备： MySQL 5.7版本+SQLyog
数据库设计规范
- 数据库命名规范
所有的数据库对象名称必须使用小写字母并用下划线分割（数据库名 表名 列名...) 大小写敏感
所有的数据库对象名称禁止使用MySQL保留关键字
所有的数据库对象命名做到见名识义，最多不超过32个字符
例如：用户数据库 mc_userdb
用户账号表 user_account
临时库表必须以tmp_为前缀以日期为后缀
备份库、备份表以bak_为前缀并以日期为后缀
所有存储相同数据的列名和列类型必须一致
- 数据库基本设计规范 如存储引擎的选择，字符集选择
MySQL5.5使用之前Myisam（默认存储引擎）所有表必须使用Innodb存储引擎，（特殊需求：列存储除外，空间数据） 5.6以后的默认Innodb存储引擎，支持事务，行级锁，更好的恢复性，高并发下性能更好。
数据库和表的字符集统一使用UTF8，兼容性更好，避免乱码。
所有表和字段都要添加注释COMMENT，从一开始就进行数据字典的维护
尽量控制单表数据量的大小，建议控制在500万行内，但500万行不是MySQL数据库的限制。过大对于修改表结构，备份，恢复都会有很大问题。MySQL没有对存储有限制，取决于存储设置和文件系统。
可以使用历史数据归档，分库分表等手段来实现控制数据量大小
谨慎使用MySQL分区表
分区表在物理上表现为多个文件，在逻辑上表现为一个表
谨慎选择分区键，跨区查询效率可能更低
建议采用物理分表的方式管理大数据
尽量做到冷热数据分离，减小表的宽带
MySQL限制最多存储4096列，且每行不能超过65535字节。足够大了
减少磁盘IO，保证热数据的内存缓存命中率
更有效的利用缓存，避免读入无用的冷数据
经常一起使用的列放在一个表中
禁止在表中建立预留字段
其实很牵强，无法确认预留字段的数据类型
对预留字段类型进行修改，会对表进行锁定
禁止在数据库中存储图片，文件等二进制数据
使用文件服务器
禁止在线上数据库压力测试
禁止从开发环境，测试环境直接连生产环境数据库
- 数据库索引设计规范 索引列选择的常规方式，索引优化建议及技巧
"双刃剑"
避免建立冗余索引和重复索引
冗余：index（a,b,c) index(a,b) index(a)
限制每张表的索引数量，建议单张表索引不超过5个
索引不是越多越好！索引可以提高效率同样也可以降低效率
索引可以增加查询效率，但同样也会降低插入和更新效率
禁止给表中的每一列都建立单独的索引
Innodb是按照哪个索引的顺序来组织表的呢？答案：主键
每个Innodb表必须有一个主键（或者非空唯一的字段或自动生成的但是性能不大好）
不使用更新频繁的列作为主键，不能用多列做主键（联合索引）
不适用UUID,MD5,HASH,字符串列作为主键
建议主键使用自增ID值
常见索引列建议：select update delete语句的where从句的列
包含order by group by distinct中的字段 组成联合索引
多表join的关联列
如何选择索引列的顺序：
区分度最高（重复率低）的列放在联合索引的最左侧
字段长度小的列放在联合索引的最左侧
使用最频繁的列房到联合索引的左侧
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
尽量避免使用外键
不建议使用外键约束，但一定在表与表之间的关联键上建立索引
- 数据库字段设计规范 如何选择列的字段类型，与性能息息相关
优先选择符合需要的最小数据类型
比如：字符串转数字类型存储 INET_ACTION('255.255.255.0')=4294967295
INET_NTOA(4294967295)='255.255.255.0'
对于非负的数据来说，要优先使用无符号整型来存储 UNSIGNED
VARCHAR(N)中的N代表的是字符数，而不是字节数
使用UTF8存储汉字VARCHAR(255)=765个字节
过大的定义长度会消耗更多的内存
避免使用TEXT,BLOG数据类型，使用varchar（）或者把BLOB或者TEXT列分离到单独的扩展表中。
避免使用枚举ENUM数据类型
尽可能把所有列定义为NOT NULL
由于索引NULL列需要额外的空间来保存，所以要占用更多的空间
进行比较和计算时要对null值做特别的处理
使用timestamp或者datetime存储时间类型 timestamp更小4字节，另一个8字节但有范围限制
财务相关的金额类数据，使用decimal数据类型（精准浮点）float和double属于非精准浮点类型
- 数据库SQL开发规范
避免使用双%号 和 like
使用left join 或not exists来优化not in操作
禁止跨库查询 为数据迁移和分库分表留出余地，降低耦合度，降低风险
禁止使用select *
消耗更多的cpu和io以及网络带宽资源
无法使用覆盖索引
减小表结构变更带来的影响
禁止使用不含字段列表的insert语句
insert into t values（‘a’，‘b’，‘c’）；不允许
insert into t （c1，c2，c3）values（‘a’，‘b’，‘c’）；
避免使用子查询，可以把子查询优化为join操作（一般情况）；
因为子查询的结果集无法使用索引
子查询会产生临时表，如果子查询数据量大会严重影响效率
消耗过多的cpu以及io资源
避免使用join关联太多的表，意见不超过5个
每join一个表多占用一部分内存（join_buffer_size)
会产生临时表操作，影响查询效率
减少同数据库的交互次数
数据库更适合批量操作
合并多个相同的操作到一起，可提高处理效率
例如：alter操作 add和change一起
使用in代替or in里的值不要超过500个
禁止使用order by rand（）进行随机排序
禁止where从句中对列进行函数转换和计算
例如：where date（createtime）=‘20160901’ 会无法使用createtime列上索引
改成 where createtime>='20160901'
and createtime <'20160902'
尽量 union all 代替 union
拆分复杂的大SQL为多个小SQL
MySQL一个SQL只能使用一个CPU进行计算
- 数据库操作行为规范 运维人员
对于大表修改使用pt-online-schema-change修改表结构
避免大表修改产生的主从延迟
避免在对表字段进行修改时进行锁表
禁止为程序使用的账号赋予super权限
对于程序连接数据库账号，遵循权限最小原则
