mysql schema设计方法

淡淡的烟草味﹌ 2022-06-11 04:58 106阅读 0赞

## 1.**类型选择** ##

**使用简单类型**：简单数据类型的操作通常需要更少的CPU周期，例如，整型比字符操作代价更低。

**更小的类型：**一般情况下，尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为更小的类型占用更少的磁盘空间、内存和CPU缓存，处理时需要的CPU周期也更少。但要确保没有低估需要存储值的范围。

**避免NULL：**通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值或者表存储的是稀疏数据。可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，MySQL也需要特殊的处理。

**标识符选择：**整数类型通常是标识列最好的选择，因为它们很快并且可以使用AUTO\_INCREMENT。应该尽量避免使用字符串类型作为标识列，因为它们很耗空间，比数字类型慢。一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括UNSIGNED这样的属性。混用不同的数据类型可能导致性能问题，也可能导致很难发现的错误。

## 2.**设计中问题** ##

***太多的列：***

MySQL的存储引擎API工作时需要在服务器层和存储引擎之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列换成行数据结构的操作代价是非常高的。如果使用了非常宽的表，然而只有一小部分列会实际用到，这时转换的代价就非常高。

***太多的关联：***

所谓的“实体-属性-值”设计模式是一个常见的糟糕的设计模式，尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作只能有61张表，事实上单个查询超过12个表的关联MySQL解析和优化查询的代价就会成为问题。

***特殊的NULL：***

之前写了避免使用NULL的好处，并且建议尽可能的考虑替代方案。即使需要存储一个空值到表中时，也不一定使用NULL。也许可以使用0、某个特殊的值或者空字符串。但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。从特定类型的领域中选择一个不可能的值可能导致代码复杂很多，并容易引入bug。处理NULL确实不容易，但有时候会比它的替代方案更好。

## 3.范式化设计 ##

***范式的优点：***

1.范式化的更新操作通常比反范式化要快。当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。

2.范式化的表通常更小，可以更好的放在内存里，所以执行操作会更快。

3.很少有多余的数据意味着检索列表数据时更少需要使用DISTINCT或者GROUP BY

范式化的缺点通常需要关联。稍微复杂一些的查询语句可能需要至少一次的关联。这不但代价昂贵，也可能使一些索引策略无效。

***反范式化：***

反范式化因为数据都在一张表中，可以很好的避免关联。如果不需要关联表，则对大部分查询最差的情况是全表扫描。当数据比内存大时这可能比关联要快很多，因为这样避免了随机I\\O。反范式化的数据表需要[数据库设计][Link 1]者去保证数据的一致性，只有在范式化已经达到一定的满意水平并且所需的约束和规则都已经建立起来，才进行反范式化。

***混用范式：***

完全的范式化和完全的反范式化都是实验室里才有的东西，在真实世界中很少会这么极端地使用。在实际中经常需要混用，可以使用部分范式化的数据表、缓存表以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。可以使用触发器更新缓存值，这样使得实现这样的方案变得更简单。

## 4.其他辅助设计 ##

***缓存表和汇总表：***

“缓存表”表示存储那些可以比较简单从其他表获取数据但是每次获取的速度比较慢的表。例如可能会需要很多不同的索引组合来加速类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列表的缓存表。

“汇总表”保存的是使用GROUP BY语句聚合数据的表。实时计算统计值是很昂贵的操作，因为要么需要扫描表中大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE操作有影响。

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而有时也需要创建一张完全独立的汇总表或缓存表。在使用缓存表和汇总表时，必须决定实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保存表不会有很多碎片，以及有完成顺序组织的索引。

***物化视图：***

物化视图是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样在执行查询时就可以避免进行这些耗时的操作从而快速的得到结果。使用物化视图的目的是为了提高查询性能。物化视图需要占用存储空间。

许多数据库关联系统都提供了一个被称作物化视图的功能。MySQL并不原生支持物化视图，可以使用Justin Swanhart的开源工具Flexviews，也可以实现自己的物化视图。Flexviews比完全自己实现的解决方案要更精细，并且提供了很多不错的功能使得可以更简单地创建和维护物化视图。

[Link 1]: https://www.baidu.com/s?wd=%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YdnW-bnj0kn1N9mWnsnyNW0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPjmdnj01n1R3