MySQL schema设计中的陷阱

虽然有一些普遍的好或坏的设计原则，但也有一些问题是由MySQL的实现机制导致的，这意味着有可能一些只在MySQL下发生的特定错误。以下我们讨论设计MySQL的schema的问题。这也许会帮助你避免这些错误，并且选择在MySQL特定实现下工作得更好的替代方案。

太多的列

MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。当我们研究一个CPU占用非常高的案例时，发现客户使用了非常宽的表（数千个字段），然而只有一小部分列会实际用到，这时转换的代价就非常高。如果计划使用数千个字段，必须意识到服务器的性能运行特征会有一些不同。

太多的关联

所谓的“实体-属性-值” （EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

全能的枚举

注意防止过度使用枚举（ENUM）。下面是我们见过的一个例子：

这种模式的schema设计的非常凌乱。这么使用枚举值类型也许在任何支持枚举类型的数据库都是一个有问题的设计方案，这里应该用整数作为外键关联到字典或者查找表来查找具体值。但是在MySQL中，当需要在枚举列表中增加一个新的国家时就要做一次ALTER TABLE操作。在MySQL5.0以及更早的版本中ALTER TABLE是一种阻塞操作；即使在5.1和更新版本中，如果不是在列表的末尾增加值也会一样需要ALTER TABLE。

变相的枚举

枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。有时候可能比较容易导致混乱。这是一个例子：

如果这里真和假两种情况不会同时出现，那么毫无疑问应该使用枚举列替代集合列。

非此发明（Not Invent Here）的NULL

我们之前写了避免使用NULL的好处，并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时，也不一定非得使用NULL。也许可以使用0、某个特殊值，或者空字符串作为替代。
但是遵循这个原则也不要走极端。当确实需要表示未知值也不要害怕使用NULL。在一些场景中，使用NULL可能会比某个神奇常数更好。从特定类型的值域中选择一个不可能的值，例如用-1代表一个未知的整数，可能导致代码复杂很多，并容易引入bug，还可能会让事情变得一团糟。处理NULL确实不容易，但有时候会比它的替代方案更好。
下面是一个我们经常看到的例子：

伪造的全0值可能导致很多问题（可以配置MySQL的SQL_MODE类禁止不可能的日期，对于新应用这是个非常好的实践经验，它不会让创建的数据库里充满不可能的值）。值得一提的是，MySQL会在索引中存储NULL值，而Oracle则不会。

作者：一任天然

来源：CSDN
原文：https://blog.csdn.net/yirentianran/article/details/79347321
版权声明：本文为博主原创文章，转载请附上博文链接！