Mysql调优之Schema与数据类型优化、范式、主键

Mysql调优之Schema与数据类型优化、范式、主键

本次主要是针对数据类型、范式建模、合理使用反范式、合理拆分字段这几个模块对sql进行调优，良好的表设计相比较sql语句调优效果更加的明显。

1. 数据类型

mysql支持的数据类型有如下几种：

1、数值类型

2、日期时间类型

3、字符串类型

4、总结

1. 应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型。
2. 整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂。
3. 使用mysql自建类型而不是字符串来存储日期和时间。
4. NULL列对mysql来说很难优化，会使得索引、索引统计和值比较都更加复杂，但是改为not null带来的性能提升比较小，可以忽略。
5. char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串
6. varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性
7. text不设置长度，当不知道属性的最大长度时，适合用text
8. 按照查询速度：char>varchar>text

2. 合理使用范式和反范式

1、范式

范式的优点：

1. 当数据较好的范式化后，很少或者没有重复的数据。
2. 范式化的数据比较小，可以放在内存中，操作比较快。
3. 范式化的更新通常比反范式要快。

范式的缺点：

1. 需要进行关联。

2、反范式

反范式的优点：

1. 所有的数据都在同一张表中，可以避免关联。

反范式的缺点：

1. 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失。

4. 主键的选择

代理主键：与业务无关的，无意义的数字序列（事物额外属性）
自然主键：事物属性中的自然唯一标识（事物自带属性）
推荐使用代理主键：

1. 它们不与业务耦合，因此更容易维护。
2. 一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本。

5. 适当的数据冗余

上面介绍了范式与反范式的优点和缺点，在实际开发中可以适当的使用反范式来减少关联，减少内存消耗。
比如在某些大的系统设计的时候，订单模块会把产品名称冗余到购物车表中，在查看购物车时，可以直接通过单表查询到数据，相比较购物车表关联产品表，查询会更快。
总结：用空间换时间

6. 适当拆分

这种方式主要用于一些较大的数据，比如text或者varcahr，但是基本用不到，我们可以把这种字段单独拆到一张表中，需要的时候通过join关联，基本使用不到这个字段，所以在查询的时候就会减少IO次数，也能提高在IO中的命中率。