MySQL之Schema与数据类型优化(五)

Schema与数据类型优化

特殊类型数据

某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子。另外一个例子是一个IPv4地址。人们经常使用VARCHAR(15)列存储IP地址。然而，它们实际上是32位无符号整数。不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换

MySQL schema设计种的陷阱

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

• 1.太多的列。
  MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲种将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM的变长行结构实际和INnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。有一个CPU占用非常高的例子，发现客户使用了非常宽的表(数千个字段)，然而只有一小部分列会实际用到，这时转换的代价就非常高。如果计划使用数千个字段，必须意识到服务器的性能运行特征会有一些不同。
• 2.太多的关联
  所谓的"实体 - 属性 - 值"(EAV)设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联。不少EAV数据库最后超过了这个限制。事实上在许多关联少于61张表地情况下，解析和优化查询地代价也会成为MySQL的问题。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联
• 3.全能的枚举
  注意防止过度使用枚举(ENUM).下面是一个例子

CREATE TABLE ......(country enum('','1','2','3',..........,'31'))

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

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

CREATE TABLE ....(is_default set('Y', 'N') NOT NULL default 'N')

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

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

CREATE TABLE ... (dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00')

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

范式和反范式

对于任何给定的数据通常都有很多种表示方法，从完全的范式化到完全的反范式化，以及两者的折中。在范式化的数据库种，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库种，信息是冗余的，可能会存储在多个地方。下面以经典的"雇员，部门，部门领导"的例子开始:
如图所示，这个schema的问题是修改数据时可能发生不一致。假如Say Brown解人Accounting部门的领导，需要修改多行数据来反应这个变化，这是很痛苦的事并且容易引入错误。如果“Jones”这一行显示部门的领导跟"Brown"这一行的不一样，就没办法直到哪个是对的。这就像有句老话说的:“一个人有两块手标就永远不知道实践”。此外，这个设计在没有雇员信息的情况下就无法表示一个部门——如果我们删除了所有Accounting部门的雇员，我们就失去了关于这个部门本身的所有记录。要避免这个问题，我们需要对这个表进行范式化，方式是拆分雇员和部门项。拆分以后可以用下面两张表来分别存储雇员表:
这样设计的两张表符合第二范式，在很多情况下做到这一步已经足够好了，然而，第二范式只是许多可能的范式种的一种

在这个例子种我们使用姓(Last Name)作为主键，因为这是数据的"自然标识"。从实践来看，无论如何都不应该这么用。这既不能保证唯一性，而且用一个很长的字符串作为主键是很糟糕的主意。

范式的优点和缺点

当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处:

• 1.范式化的更新操作通常比反范式化要快
• 2.当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少地数据
• 3.范式化地表通常更小，可以更好地放在内存里，所以执行操作会更快
• 4.很少有多余地数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY 语句

还是前面地例子:在非范式化的结构种鄙视使用DISTINCT 或者GROUP BY 才能获得一份唯一的部门列表，如果部门(DEPARTMENT)是一张单独的表，则只需要简单的查询这张表就行了.
范式化设计的schema的缺点是通常需要关联，稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次的关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列放在不同的表种，而这些列如果在一个表种本可以属于同一个索引