高性能可扩展mysql（电商数据库设计构思）

数据库设计规范（统一）

1. 数据库命名规范
2. 数据库基本设计规范（存储引擎的选择，字符类型的选择）
3. 数据库索引设计规范（索引列的选择，索引的优化技巧）
4. 数据库字段设计规范（列的字段类型）
5. SQL开发规范（开发人员使用，指导编写优质sql）
6. 数据库操作行为规范（运维人员使用，减少数据库故障）

数据库命名规范

- 关键字查询：https://dev.mysql.com/doc/refman/5.7/en/keywords.html
- 对于一些小的字典表，为了恢复方便，直接在数据库中复制一份（不必导出成sql文件）
- 可以使用关键字创建，但是查询时就会报错，所以还需要使用“括起来。
- 如果两个表上的关联列数据类型不同，关联时就会进行隐式数据类型转换，造成列上的数据类型失效，查询效率降低

数据库基本设计规范

Mysql5.5使用之前MyIsam是默认存储引擎

- Innodb无法满足的需求：列存储，空间数据
- UTF-8兼容性更好
- 对不同字符集的比较操作，比较前进行字符集的转换，所以使得列上的索引失效，带来性能问题
- 单表数据量较大，修改表结构，备份，恢复都会有很大的问题
- mysql并没有对单表最多存储设置限制。限制取决于存储设置和文件系统（32位系统单个文件最大不能超过2G）
- 历史数据归档主要用于日志文档（增长迅速），分库分表用于业务上
- 分区表大家误认为存储在不同的物理文件中，可以更好的利用磁盘IO。实际上分区表的不同分区文件最好存储在不同的磁盘阵列上。而我们把所有分区文件放在一起，IO的性能优化的效果并不能那么好
- mysql每个表限制最多存储4096列，并且每一行的数据大小不能超过65535个字节
- 表越宽，把表装入内存缓冲池占用内存也就越大，消耗更多IO
- select * ->读入无用的冷数据
- 垂直拆分
- 对于目前的mysql,修改一个字段的成本远远大于增加一个字段

数据库索引设计规范

• mysql优化器在选择如何优化查询时，会根据统一信息对可以用到的每个索引进行评估，生成一个最好的执行计划。如果索引过多，增加sql优化器执行的时间，降低SQL查询的性能
• 索引可以增加查询效率，同样也会降低插入和更新的效率
• mysql5.6之前，一个查询只能使用一个索引，之后，合并索引（远远没有使用一个联合索引查询性能好）
• Innodb是一种索引组织表，数据存储的逻辑顺序和索引顺序相同
• Innodb是按照主键索引顺序来组织表的
• 如果没有主键，mysql会选择第一非空唯一索引来选择主键，如果没有非空唯一索引，mysql会自动生成一个占6个字节的主键（性能不好）
• 覆盖索引：包含了所有查询字段的索引
• Innodb是使用聚集索引顺序来存储的。二级索引（叶子节点所保存的是行的主键信息），在查找到相应的键值之后，还要通过主键进行二次查询，获取数据。而在覆盖索引中，二级索引的键值就可以获取所有的数据了，避免了二次查询，减少了相关的IO操作
• mysql在建立外键时，会自动的在外键上建立索引。
• 不建议使用外键约束，但一定在表与表之间的关联键上建立索引
• 使用其他方法来保证父表与子表的唯一性

索引建立经验
建立索引的目的：减少磁盘IO，过滤出更少的数据
索引建立在哪？

• 推荐联合索引（从左到右的顺序使用）
• mysql对关联操作的唯一方式只有一种，嵌套循环的关联方式（性能对关联列索引依赖很大）

如何选择索引列的顺序？
注意合理选择符合索引键值的顺序

区分度：列中唯一值的数量，用列中不同数据的数量和表的总行数的比例（接近于1）来计算，区分度最大的就是主键（1）和唯一索引的列

数据库字段设计规范

• 优先选择符合存储需要的最小的数据类型
• 对于非负型的数据来说，要优先使用无符号整型来存储（自增ID，主键……）
  
  • 无符号对于有符号来说，可以多出一倍存储空间
  • 
• 
• 避免使用TEXT,BLOB数据类型（性能很差）
  
  • TinyText,Text(64k),MidumText,LongText
  • 内存临时表不支持TEXT,BLOB，只能使用磁盘临时表（排序）
  • 还要进行二次查询
  • 只能使用前缀索引（索引类型是有长度限制的）
  • TEXT的列上不能有默认值
  • 建议把这种表分离到单独的扩展表中
• 避免使用ENUM（枚举）类型
  
  • 字符串类型，内部以整数类性进行存储（MAX 65535）
  • 字符串转化整数很方便
  • 修改ENUM值需要使用ALTER语句（原数据锁，增加误操作几率）
  • ENUM类型的ORDER BY操作效率低，整型需要转化成字符串，需要额外操作（隐式转换，无法使用索引）
  • 禁止使用数值做为ENUM的枚举值
• 尽可能把所有列定义为NOT NULL
  
  • 
• 使用TIMESTAMP（4字节）或DATETIME（8字节）类型存储时间
  
  • 
  • 
• 财务相关的金额类数据，必须使用decimal类型
  
  • 精准浮点：decimal。非精准浮点：float,double
  • 四个字节存储9个数字（小数点占用一个字节）
  • 

SQL开发规范

• 预编译(一次解析，多次使用)
  
  • 重复使用执行计划，减少sql编译所需要的时间（相同语句可以一次解析，多次使用，提高处理效率）
  • 避免动态sql带来sql注入的问题
  • 只传参数，比传递SQL语句更高效
  • 
• 避免数据类型的隐式转换（where中列类型和参数类型不一致时）
  
  • 导致索引失效
  • 
• 充分利用表上已经存在的索引
  
  • 
• 对数据库扩展考虑
  
  • 
  • 
    
    • 修改表结构，select * 会把修改的列也读取到，这会影响到mysql的稳定运营
  • 
  • 
    
    • 产生慢查询
  • 
• 
• 
• 
• 
• 
• 

数据库操作行为规范