第四章 Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计 schema,这往往需要权衡各种因素。
例如，反范式的设计可以加快某些类型的查询，但同 时可能使另一些类型的査询变慢。
比如添加计数表和汇总表是一种很好的优化査询的方式， 但这些表的维护成本可能会很高。MySQL独有的特性和实现细节对性能的影响也很大。
本章和聚焦在索引优化的下一章，覆盖了 MySQL特有的schema设计方面的主题。假设读者已经知道如何设计数据库，所以本章既不会介绍如何入门数据库设计，也不 会讲解数据库设计方面的深入内容。这一章关注的是MySQL数据库的设计，主要介绍 的是MySQL数据库设计与其他关系型数据库管理系统的区别。如果需要学习数据库设 计方面的基础知识，建议阅读 Clare Churcher 的 Beginning Database Design （Apress 出 版社）一书。
本章内容是为接下来的两个章节做铺垫。在这三章中，我们将讨论逻辑设计、物理设计 和査询执行，以及它们之间的相互作用。这既需要关注全局，也需要专注细节。还需要 理解整个系统以便弄清楚各个部分如何相互影响。如果在阅读完索引和查询优化章节后 再回头来看这一章，也许会发现本章很有用，很多讨论的议题不能孤立地考虑。

4.1选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管 存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

更小的通常更好。

一般情况下，应该尽量使用可以正确存储数据的最小数据类型注’。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就 选择你认为不会超过范围的最小类型。（如果系统不是很忙或者存储的数据量不多, 或者是在可以轻易修改设计的早期阶段，那之后修改数据类型也比较容易）O

简单就好

简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低, 因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。这里有两个例子: 一个是应该使用MySQL内建的类型注2而不是字符串来存储日期和时间，另外一个 是应该用整型存储IP地址。稍后我们将专门讨论这个话题。

尽量避免NULL

很多表都包含可为NULL （空值）的列，即使应用程序并不需要保存NULL也是如此, 这是因为可为NULL是列的默认属性注3。通常情况下最好指定列为NOT NULL,除非真 的需要存储NULL值。
如果査询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在 MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MylSAM里甚至还可能导致固定大小的索引（例如只有一个整数列的 索引）变成可变大小的索引。
通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有 必要首先在现有schema中査找并修改掉这种情况，除非确定这会导致问题。但是, 如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。
当然也有例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据注4有很好的空间效率。但这一点不适用于MyISAMo在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。这通 常是很简单的，但是我们会提到一些特殊的不是那么直观的案例。

下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储 的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。 相同大类型的不同子类型数据有时也有一些特殊的行为和属性。
例如，DATETIME和TIMESAMP列都可以存储相同类型的数据：时间和日期，精确到秒。DATETIME 一半的存储空间，并且会根据时区变化，具有特殊的自 动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会 成为障碍。

本章只讨论基本的数据类型。MySQL为了兼容性支持很多别名，例如INTEGER. BOOL, 以及NUMERIC它们都只是别名。这些别名可能令人不解，但不会影响性能。如果建表 时采用数据类型的别名，然后用SHOW CREATE TABLE检査，会发现MySQL报告的是基 本类型，而不是别名。

4.1.1整数类型

有两种类型的数字：整数(whole number)和实数(real number) o
如果存储整数，可以使用这几种整数类型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT0分别使用8, 16, 24, 32, 64位存储空间。它们可以存储的值的范围从-2(n-1)到2(n-1)-1,其中N是 存储空间的位数。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。 例如TINYINT. UNSIGNED可以存储的范围是0_{255,而TINYINT的存储范围是-128}127。
有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况 选择合适的类型。
你的选择决定MySQL是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用 64位的BIGINT整数，即使在32位环境也是如此。(一些聚合函数是例外，它们使用 DECIMAL或DOUBLE进行计算)。
MySQL可以为整数类型指定宽度，例如INT(ll),对大多数应用这是没有意义的：它不 会限制值的合法范围，只是规定了 MySQL的一些交互工具(例如MySQL命令行客户端) 用来显示字符的个数。对于存储和计算来说，INT(l)和INT(29)是相同的。

4.1.2实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分、也可以使用 DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算是 怎么计算的，则需要研究所使用的平台的浮点数的具体实现。
DECIMAL类型用于存储精确的小数。在MySQL 5.0和更高版本，DECIMAL类型支持精确 计算。MySQL 4.1以及更早版本则使用浮点运算来实现DECIAML的计算，这样做会因为 精度损失导致一些奇怪的结果。在这些版本的MySQL中，DECIMAL只是一个“存储类型”。
因为CPU不支持对DECIMAL的直接计算，所以在MySQL 5.0以及更高版本中，MySQL 服务器自身实现了 DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所 以浮点运算明显更快。
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的 最大位数。这会影响列的空间消耗。MySQL 5.0和更高版本将数字打包保存到一个二进 制字符串中（每4个字节存9个数字）。例如，DECIMAL（18,9）小数点两边将各存储9个 数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节, 小数点本身占1个字节。
MySQL 5.0和更高版本中的DECIMAL类型允许最多65个数字。而早期的MySQL版本中 这个限制是254个数字，并且保存为未压缩的字符串（每个数字一个字节）。然而，这些（早 期）版本实际上并不能在计算中使用这么大的数字，因为DECIMAL只是一种存储格式； 在计算中DECIMAL会转换为DOUBLE类型。
有多种方法可以指定浮点列所需要的精度，这会使得MySQL悄悄选择不同的数据类型, 或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型, 不指定精度。 j
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字 节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样, 能选择的只是存储类型；MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替 Hi9> decimal,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财 务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里, 这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

4.1.3字符串类型

MySQL支持多种字符串类型，每种类型还有很多变种。这些数据类型在4.1和5.0版本
发生了很大的变化，使得情况更加复杂。从MySQL 4.1开始，每个字符串列可以定义自 己的字符集和排序规则，或者说校对规则(collation)(更多关于这个主题的信息请参考 第7章)。这些东西会很大程度上影响性能。

VARCHAR 和 CHAR 类型

VARCHAR和CHAR是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是怎么 存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。下面的描述假设使用的存 储引擎是InnoDB和/或者MylSAM。如果使用的不是这两种存储引擎，请参考所使用 的存储引擎的文档。
先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意，存储引擎存储CHAR或者 VARCHAR值的方式在内存中和在磁盘上可能不一样，所以MySQL服务器从存储引擎读 出的值可能需要转换为另一种存储格式。下面是关于两种类型的一些比较。

VARCHAR

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型 更节省空间，因为它仅使用必要的空间(例如，越短的字符串使用越少的空间)。有 一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用 定长存储，这会很浪费空间。
VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或 等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集， 一个VARCHAR(1O)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息。
VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在 UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用 的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引 擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新 数据。
下面这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多； 列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符 都使用不同的字节数进行存储。
在5.0或者更高版本，MySQL在存储和检索时会保留末尾空格。但在4.1或更老 的版本，MySQL会剔除末尾空格。
IrmoDB则更灵活，它可以把过长的VARCHAR存储为BLOB,我们稍后讨论这个问题。

CHAR

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储 CHAR值时，MySQL会删除所有的末尾空格(在MySQL 4.1和更老版本中VARCHAR 也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区 别只是在存储格式上)。CHAR值会根据需要采用空格进行填充以方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。两如，CHAR非常适 合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比 VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比 VARCHAR在存储空间上也更有效率。例如用CHAR(l)来存储只有Y和N的值，如果 采用单字节字符集注5只需要一个字节，但是VARCHAR(l)却需要两个字节，因为还有 一个记录长度的额外字节。

4.2 MySQL schema设计中的陷阱

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

太多的列

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

太多的关联

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

全能的枚举

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

CREATE TABLE ...(
 COUNTRY ENUM('','1'.'2','3'....,'51')

这种模式的schema设计非常凌乱。这么使用枚举值类型也许在任何支持枚举类型的 数据库都是一个有问题的设计方案，这里应该用整数作为外键关联到字典表或者査 找表来査找具体值。但是在MySQL中，当需要在枚举列表中增加一个新的国家时 就要做一次ALTER TABLE操作。在MySQL 5.0以及更早的版本中ALTER TABLE是一 种阻塞操作；即使在5.1和更新版本中，如果不是在列表的末尾增加值也会一样需 要ALTER TABLE (我们将展示一些骇客式的方法来避免阻塞操作，但是这只是骇客
'的玩法，别轻易用在生产环境中)。

变相的枚举

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

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

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

非此发明(Not Invent Here)的 NULL

我们之前写了避免使用NULL的好处，并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时，也不一定非得使用NULL也许可以使用0、某 个特殊值，或者空字符串作为代替。
但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULLO 在一些场景中，使用NULL可能会比某个神奇常数更好。从特定类型的值域中选择一 个不可能的值，例如用-1代表一个未知的整数，可能导致代码复杂很多，并容易引入bug,还可能会让事情变得一团糟。处理NULL确实不容易，但有时候会比它的替
代方案更好。
下面是一个我们经常看到的例子:
CREATE TABLE …（ dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00*
伪造的全0值可能导致很多问题（可以配置MySQL的SQL_MODE来禁止不可能的日期， 对于新应用这是个非常好的实践经验，它不会让创建的数据库里充满不可能的值）。
值得一提的是，MySQL会在索引中存储NULL值，而Oracle则不会。

4.3范式和反范式

对于任何给定的数据通常都有很多种表示方法，从完全的范式化到完全的反范式化，以 及两者的折中。在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

4.3.1范式的优点和缺点

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

• 范式化的更新操作通常比反范式化要快。
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。 还是前面的例子：在非范式化的结构中必须使用DISTINCT或者GROUP BY才能获得 一份唯一的部门列表，但是如果部门(DEPARTMENT)是一张单独的表，则只需要简 单的査询这张表就行了。
  范式化设计的schema的缺点是通常需要关联。稍微复杂一些的査询语句在符合范式的 schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策 略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

4.3.2反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。
如果不需要关联表，则对大部分査询最差的情况一一即使表没有使用索引——是全表扫 描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O
单独的表也能使用更有效的索引策略.
主要问题是关联，使得需要在一个索引命又排序又过滤。如果采用反范式化组织数据， 将两张表的字段合并一下，并且增加一个索引(account type, published),就可以不 通过关联写出这个查询。

4.3.3混用范式化和反范式化

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？
事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实 世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的 ##### schema、缓存表，以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在 MySQL 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更 简单。
在我们的网站实例中，可以在user表和message表中都存储account_type字段，而不 用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时 候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地 获取数据。
但是现在更新用户的账户类型的操作代价就高了，因为需要同时更新两张表。至于这会 不会是一个问题，需要考虑更新的频率以及更新的时长，并和执行SELECT査询的频率进 行比较。
另一个从父表冗余一些数据到子表的理由是排序的需要。例如，在范式化的schema里通 过作者的名字对消息做排序的代价将会非常高，但是如果在message表中缓存author, name字段并且建好索引，则可以非常高效地完成排序。缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（像很多论坛做的），可以 每次执行一个昂贵的子査询来计算并显示它；也可以在user表中建一个num messages 列，每当用户发新消息时更新这个值。

4.5加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表 结构操作的方法是用新的结构创建一个空表，从旧表中査出所有数据插入新表，然后删 除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索 引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚 至数天才能完成。
MySQL 5.1以及更新版本包含一些类型的“在线”操作的支持，这些功能不需要在整个 操作过程中锁表。最近版本的InnoDB注”也支持通过排序来建索引，这使得建索引更快 并且有一个紧凑的索引布局。
一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL 操作时有用的技巧，但这是针对一些特殊的场景而言的。对常见的场景，能使用的技巧 只有两种：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服 务的主库进行切换；另外一种技巧是“影子拷贝气影子拷贝的技巧是用要求的表结构 创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。也有一些工具 可以帮助完成影子拷贝工作：例如，Facebook数据库运维团队(https://launchpad.net/ mysqlatfacebook)的 Konline schema changew 工具、Shlomi Noach 的 openark toolkit (http:// code.openark.org/),以及 Percona Toolkit (http://www.percona.com/software/) o 如果使 用Flexviews (参考4.4.1节)，也可以通过其CDC工具执行无锁的表结构变更。
不是所有的ALTER TABLE操作都会引起表重建。
HOW STATUS显示这个语句做了 1 000次读和1 000次插入操作。换句话说，它拷贝了整 <3F| 张表到一张新表，甚至列的类型、大小和可否为NULL属性都没改变。
理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的文件中， 所以可以直接修改这个文件而不需要改动表本身。然而MySQL还没有采用这种优化的 方法，所有的MODIFY COLUMN操作都将导致表重建。

总结

良好的schema设计原则是普遍适用的，但MySQL有它自己的实现细节要注意。概括来 说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人 应该也同样会喜欢简单的原则：

• 尽量避免过度设计，例如会导致极其复杂査询的schema设计，或者有很多列的表设 计（很多的意思是介于有点多和非常多之间）。
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可 能地避免使用NULL值。
• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
• 尽量使用整型定义标识列。
• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
• 小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成 陷阱。最好避免使用BITO
  范式是好的，但是反范式（大多数情况下意味着重复数据）有时也是必需的，并且能带 来好处。第5章我们将看到更多的例子。预先计算、缓存或生成汇总表也可能获得很大 的好处。Justin Swanhart的Flexviews工具可以帮助维护汇总表。
  最后，ALTER TABLE是让人痛苦的操作，因为在大部分情况下，它都会锁表并且重建整 张表。我们展示了一些特殊的场景可以使用骇客方法;但是对大部分场景，必须使用其 他更常规的方法，例如在备机执行ALTER并在完成后把它切换为主库。本书后续章节会 有更多关于这方面的内容。
  索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。 这是索引的基本功能，除此之外，本章还将讨论索引其他一些方面有用的属性。
  索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响 愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但 当数据量逐渐增大时，性能则会急剧下降注七
  不过，索引却经常被忽略，有时候甚至被误解，所以在实际案例中经常会遇到由糟糕索 引导致的问题。这也是我们把索引优化放在了靠前的章节，甚至比査询优化还靠前的原 因。
  索引优化应该是对査询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数 量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最 优”的索引经常需要重写査询，所以，本章和下一章的关系非常紧密。