选择优化的数据类型

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

• 更小的通常更好。

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。(如果系统不是很忙或者存储的数据量不多，或者是在可以轻易修改设计的早期阶段，那之后修改数据类型也比较容易)。

• 简单就好

简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。这里有两个例子：1.应该使用MySQL内建的类型而不是字符串来存储日期和时间；2.应该用整型存储IP地址。

• 尽量避免 NULL

很多表都包含可为NULL(空值)的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。

当然也有例外，例如值得一提的是，InnoDB使用单独的位(bit)存储NULL值，所以对于稀疏数据有很好的空间效率。但这一点不适用于MyISAM。

整数类型

有两种类型的数字：整数(whole number)和实数(real number)。如果存储整数，可以使用这几种整数类型:TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间。它们可以存储的值的范围从到，其中N是存储空间的位数。

整数类型有可选的 UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如TINYINT. UNSIGNED可以存储的范围是0～255，而TINYINT的存储范围是-128 ~127。

你的选择决定MySQL是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用64位的BIGINT整数，即使在32位环境也是如此。(一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算)。

MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围,只是规定了MySQL的一些交互工具（例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分﹔也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字) 。例如，DECIMAL(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。在Mysql5.0和更高版本中，DECIMAL最多65个数字。

有多种方法可以指定浮点列所需要的精度，这会使得MySQL 悄悄选择不同的数据类型，或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型，不指定精度

---

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节,相比 FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型：MySQL使用DOUBLE作为内部浮点计算的类型。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL一一例如存储财务数据。 但在数据量比较大的时候，可以考虑使用BTGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

从 MySQL 4.1开始，每个字符串列可以定义自己的字符集和排序规则，或者说校对规则（collation)。这些东西会很大程度上影响性能。

VARCHAR和CHAR类型

VARCHAR 和CHAR是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是怎么存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。下面的描述假设使用的存储引擎是InnoDB或者MyISAM。如果使用的不是这两种存储引擎，请参考所使用的存储引擎的文档。

先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意，存储引擎存储CHAR或者VARCHAR值的方式在内存中和在磁盘上可能不一样，所以MySQL服务器从存储引擎读出的值可能需要转换为另一种存储格式。下面是关于两种类型的一些比较。

VARCHAR

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间)。有一种情况例外，如果 MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。

VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。

VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。 如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。

下面这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

在5.0或者更高版本，MySQL在存储和检索时会保留末尾空格。但在4.1或更老的版本，MySQL会剔除末尾空格。

CHAR

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格（在MySQL 4.1和更老版本中VARCHAR也是这样实现的——也就是说这些版本中 CHAR和VARCHAR在逻辑上是一样的，区别只是在存储格式上)。CHAR值会根据需要采用空格进行填充以方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

与CHAR 和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。填充也不一样：MySQL填充BINARY采用的是\0（零字节）而不是空格，在检索时也不会去掉填充值

当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL 比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT，SMALLTEXT,TEXT，MEDIUMTEXT，LONGTEXT；对应的二进制类型是TINYBLOB，SMALLBLOB，BLOB,MEDIUMBLOB，LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

---

BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

---

MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用ORDER BY SUSTRING(column,length)。

MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。(关于这个主题下一章会有更多的信息。)

使用枚举（ENUM）代替字符串类型

有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。下面有一个例子：

这三行数据实际存储为整数，而不是字符串。可以通过在数字上下文环境检索看到这个双重属性:

另外一个让人吃惊的地方是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的:

一种绕过这种限制的方式是按照需要的顺序来定义枚举列。另外也可以在查询中使用FIELD()函数显式地指定排序顺序，但这会导致MySQL无法利用索引消除排序。

枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表末尾添加元素，这样在MySQL 5.1中就可以不用重建整个表来完成修改。

由于MySQL把每个枚举值保存为整数，并且必须进行查找才能转换为字符串，所以枚举列有一些开销。通常枚举的列表都比较小，所以开销还可以控制，但也不能保证一直如此。在特定情况下，把 CHAR/VARCHAR列与枚举列进行关联（即join，多表查询）可能会比直接关联 CHAR/VARCHAR列更慢。如果不是必须和VARCHAR列进行关联，那么转换这些列为ENLM就是个好主意。这是一个通用的设计实践，在“查找表”时采用整数主键而避免采用基于字符串的值进行关联。

然而，转换列为枚举型还有另一个好处：会减少存储表的空间。在某些情况下，即使可能出现 ENUM和VARCHAR进行关联的情况，这也是值得的。同样，转换后主键存储空间也会变小。因为这是InnoDB表，如果表上有其他索引，减小主键大小会使非主键索引也变得更小。

日期和时间类型

MySQL 可以使用许多类型来保存日期和时间值，例如YEAR和DATE。MySQL 能存储的最小时间粒度为秒(MariaDB支持微秒级别的时间类型)。但是MySQL也可以使用微秒级的粒度进行临时运算，我们会展示怎么绕开这种存储限制。

大部分时间类型都没有替代品，因此没有什么是最佳选择的问题。唯一的问题是保存日期和时间的时候需要做什么。MySQL提供两种相似的日期类型:DATETIME和TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些场景，一个比另一个工作得好。让我们来看一下。

DATETIME

这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示DATETINE值，例如“2008-01-16 22:37:08”。这是ANSI标准定义的日期和时间表示方法。

TIMESTAMP

就像它的名字一样，TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多﹔只能表示从1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。

MysQL 4.1以及更新的版本按照DATETIME的方式格式化TIMESTAMP的值，但是MySQL 4.0以及更老的版本不会在各个部分之间显示任何标点符号。这仅仅是显示格式上的区别，TIMESTAMP的存储格式在各个版本都是一样的。

TIMESTAMP显示的值也依赖于时区。MySQL服务器、操作系统，以及客户端连接都有时区设置。

因此，存储值为0的TIMESTAMP在美国东部时区显示为“1969-12-31 19:00:00”，与格林尼治时间差5个小时。有必要强调一下这个区别:如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。

TIMESTAMP也有DATETIME没有的特殊属性。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值,MySQL则设置这个列的值为当前时间。在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值（除非在UPDATE语句中明确指定了值)。你可以配置任何TIMESTAMP列的插入和更新行为。最后，TIMESTAMP列默认为NOT NULL，这也和其他的数据类型不一样。

除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比 DATETIME空间效率更高。有时候人们会将Unix时间截存储为整数值，但这不会带来任何收益。用整数保存时间截的格式通常不方便处理，所以我们不推荐这样做。

如果需要存储比秒更小粒度的日期和时间值怎么办? MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分。这两种方式都可以，或者也可以使用MariaDB替代MySQL。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

BIT

在MySQL 5.0之前，BIT是TINYINT的同义词。但是在MySQL 5.0以及更新版本，这是一个特性完全不同的数据类型。下面我们将讨论BIT类型新的行为特性。

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位，依此类推。BIT 列的最大长度是64个位。

BIT的行为因存储引擎而异。MyISAM 会打包存储所有的BIT列，所以17个单独的BIT列只需要17个位存储（假设没有可为NULL的列)，这样MyISAM只使用3个字节就能存储这17个BIT列。其他存储引擎例如Memory和InnoDB，为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。

MySQL把BIT当作字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的“0”或“1”。 然而，在数字上下文的场景中检索时，结果将是位字符串转换成的数字。如果需要和另外的值比较结果，一定要记得这一点。例如，如果存储一个值b'00111001'(二进制值等于57）到BIT(8)的列并且检索它，得到的内容是字符码为57的字符串。也就是说得到ASCII码为57的字符“9”。但是在数字上下文场景中，得到的是数字57:

所以，尽量避免使用BIT。如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的 CHAR(0)列。该列可以保存空值(NULL)或者长度为零的字符串（空字符串)。

SET

如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间，并且MySQL有像FIND_IN_SET()和 FIELD()这样的函数，方便地在查询中使用。它的主要缺点是改变列的定义的代价较高：需要ALTER TABLE，这对大表来说是非常昂贵的操作（但是本章的后面给出了解决办法)。一般来说，也无法在SET列上通过索引杳找。

在整数列上进行按位操作

一种替代SET的方式是使用一个整数包装一系列的位。例如，可以把8个位包装到一个TINYINT中，并且按位操作来使用。可以在应用中为每个位定义名称常量来简化这个工作。

比起SET,这种办法主要的好处在于可以不使用 ALTER TABLE改变字段代表的“枚举”值,缺点是查询语句更难写,并且更难理解（当第5个bit位被设置时是什么意思?)。一些人非常适应这种方式，也有一些人不适应，所以是否采用这种技术取决于个人的偏好。

一个包装位的应用的例子是保存权限的访问控制列表(ACL)。每个位或者SET元素代表一个值，例如CAN_READ、CAN_WRITE，或者CAN_DELETE。如果使用SET列，可以让MySQL在列定义里存储位到值的映射关系﹔如果使用整数列，则可以在应用代码里存储这个对应关系。这是使用SET列时的查询:

选择标识符( identifier)

为标识列（identifier column)选择合适的数据类型非常重要。一般来说更有可能用标识列与其他值进行比较（例如，在关联操作中)，或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型（正如我们在本章早些时候所论述的一样，在相关的表中使用相同的数据类型是个好主意，因为这些列很可能在关联中使用) 。

当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。例如，MySQL在内部使用整数存储ENUM和SET类型，然后在做比较操作时转换为字符串。

一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误。这种错误可能会很久以后才突然出现，那时候可能都已经忘记是在比较不同的数据类型。

在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。例如有一个state_id列存储美国各州的名字，就不需要几千或几百万个值，所以不需要使用INT。TINYINT足够存储，而且比INT少了3个字节。如果用这个值作为其他表的外键,3个字节可能导致很大的性能差异。下面是一些小技巧。

整数类型

整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。

ENUM和SET类型

对于标识列来说，EMUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM和SET列适合存储固定信息，例如有序的状态、产品类型、人的性别。

举个例子，如果使用枚举字段来定义产品类型，也许会设计一张以这个枚举字段为主键的查找表（可以在查找表中增加一些列来保存描述性质的文本，这样就能够生成一个术语表，或者为网站的下拉菜单提供有意义的标签)。这时，使用枚举类型作为标识列是可行的，但是大部分情况下都要避免这么做。

字符串类型

如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。尤其是在MyISAM表里使用字符串作为标识列时要特别小心。MyISAM默认对字符串使用压缩索引，这会导致查询慢得多。在我们的测试中，我们注意到最多有6倍的性能下降。

对于完全“随机”的字符串也需要多加注意，例如MD5()、SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT 语句变得很慢：

• 因为插入值会随机地写到索引的不同位置，所以使得 INSERT语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。关于这一点第5章有更多的讨论。
• SELECT语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。

随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。如果整个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处﹔如果工作集比内存大，缓存将会有很多刷新和不命中。

如果存储UUID值，则应该移除“”符号﹔或者更好的做法是，用UNHEX ()函数转换UUID值为16字节的数字，并且存储在一个 BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。

MySQL schema设计中的陷阱

太多的列

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

太多的关联

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

全能的枚举

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

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

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

变相的枚举

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

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

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

非此发明(Not Invent Here)的NULL

我们之前写了避免使用NULL的好处，并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时，也不一定非得使用NULL。也许可以使用0、某个特殊值，或者空字符串作为代替。

但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。在一些场景中，使用NULL可能会比某个神奇常数更好。从特定类型的值域中选择一个不可能的值，例如用-1代表一个未知的整数，可能导致代码复杂很多，并容易引入bug，还可能会让事情变得一团糟。处理 NULL确实不容易，但有时候会比它的替代方案更好。

范式和反范式

范式的优点和缺点

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

• 范式化的更新操作通常比反范式化要快。
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。·范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。

• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。还是前面的例子﹔在非范式化的结构中必须使用DISTINCT或者GROUPBY才能获得一份唯一的部门列表，但是如果部门(DEPARTMENT)是一张单独的表，则只需要简单的查询这张表就行了。

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema 上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。

如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机IO。

单独的表也能使用更有效的索引策略。假设有一个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。如果是范式化的结构并且索引了发送日期字段published，这个查询也许看起来像这样:

要更有效地执行这个查询，MySQL需要扫描message表的published字段的索引。对于每一行找到的数据，将需要到user表里检查这个用户是不是付费用户。如果只有一小部分用户是付费账户，那么这是效率低下的做法。

主要问题是关联，使得需要在一个索引中又排序又过滤。如果采用反范式化组织数据，将两张表的字段合并一下，并且增加一个索引(account_type，published)，就可以不通过关联写出这个查询。这将非常高效:

混用范式化和反范式化

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

在我们的网站实例中，可以在user表和message表中都存储account_type字段，而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。

但是现在更新用户的账户类型的操作代价就高了，因为需要同时更新两张表。至于这会不会是一个问题，需要考虑更新的频率以及更新的时长，并和执行SELECT查询的频率进行比较。

另一个从父表冗余一些数据到子表的理由是排序的需要。例如，在范式化的schema里通过作者的名字对消息做排序的代价将会非常高，但是如果在message表中缓存author_name字段并且建好索引，则可以非常高效地完成排序。

缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（像很多论坛做的)，可以每次执行一个昂贵的子查询来计算并显示它﹔也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表（特别是为满足检索的需求时)。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地（例如，需要避免复杂、昂贵的实时更新操作)。

术语“缓存表”和“汇总表”没有标准的含义。我们用术语“缓存表”来表示存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据)。而术语“汇总表"时,则保存的是使用GROUP BY语句聚合数据的表(例如，数据不是逻辑上冗余的)。也有人使用术语“累积表（Roll-Up Table)”称呼这些表。因为这些数据被“累积”了。

仍然以网站为例，假设需要计算之前24小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案，可以每小时生成一张汇总表。这样也许一条简单的查询就可以做到，并且比实时维护计数器要高效得多。缺点是计数器并不是100%精确。

如果必须获得过去24小时准确的消息发送数量（没有遗漏)，有另外一种选择。以每小时汇总表为基础，把前23个完整的小时的统计表中的计数全部加起来，最后再加上开始阶段和结束阶段不完整的小时内的计数。假设统计表叫作msg_per_hr并且这样定义:

不管是哪种方法—--不严格的计数或通过小范围查询填满间隙的严格计数——都比计算message表的所有行要有效得多。这是建立汇总表的最关键原因。实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE操作有影响，所以一般不希望创建这样的索引。计算最活跃的用户或者最常见的“标签”是这种操作的典型例子。

缓存表则相反，其对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构，跟普通OLTP操作用的表有些区别。

例如，可能会需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表。一个有用的技巧是对缓存表使用不同的存储引擎。例如，如果主表使用InnoDB，用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文搜索。有时甚至可以把整个表导出MySQL，插入到专门的搜索系统中获得更高的搜索效率，例如Lucene或者Sphinx搜索引擎。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引(这会更加高效)。

当重建汇总表和缓存表时,通常需要保证数据在操作时依然可用。这就需要通过使用“影子表”来实现，“影子表”指的是一张在真实表“背后”创建的表。当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。例如，如果需要重建my_summary,则可以先创建my_summary_new，然后填充好数据，最后和真实表做切换:

如果像上面的例子一样，在将my_summary这个名字分配给新建的表之前将原始的my_summary表重命名为my_summary_old，就可以在下一次重建之前一直保留旧版本的数据。如果新表有问题，则可以很容易地进行快速回滚操作。

计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效，并且可以使用本节展示的一些更高级的技巧。

应该让事情变得尽可能简单,假设有一个计数器表，只有一行数据,记录网站的点击次数:

网站的每次点击都会导致对计数器进行更新:

问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行。要获得更高的并发更新性能，也可以将计数器保存在多行中，每次随机选择一行进行更新。这样做需要对计数器表进行如下修改:

然后预先在这张表增加100行数据。现在选择一个随机的槽（slot)进行更新:

要获得统计结果，需要使用下面这样的聚合查询:

一个常见的需求是每隔一段时间开始一个新的计数器（例如，每天一个)。如果需要这么做，则可以再简单地修改一下表设计:

在这个场景中，可以不用像前面的例子那样预先生成行，而用ON DUPLICATE KEYUPDATE代替:

如果希望减少表的行数，以避免表变得太大，可以写一个周期执行的任务，合并所有结果到0号槽，并且删除所有其他的槽:

加快Alter Table的操作速度

MySQL的 ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚至数天才能完成。

MySQL 5.1以及更新版本包含一些类型的“在线”操作的支持，这些功能不需要在整个操作过程中锁表。最近版本的InnoDB也支持通过排序来建索引，这使得建索引更快并且有一个紧凑的索引布局。

一般而言，大部分ALTER TABLE 操作将导致MySQL服务中断。我们会展示一些在DDL操作时有用的技巧，但这是针对一些特殊的场景而言的。对常见的场景，能使用的技巧只有两种：一种是先在一台不提供服务的机器上执行ALTER TABLE 操作，然后和提供服务的主库进行切换；另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构．创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

不是所有的 ALTER TABLE操作都会引起表重建。例如，有两种方法可以改变或者删除一个列的默认值（一种方法很快，另外一种则很慢)。假如要修改电影的默认租赁期限，从三天改到五天。下面是很慢的方式:

SHOW STATUS显示这个语句做了1 000次读和1000次插入操作。换句话说，它拷贝了整张表到一张新表，甚至列的类型、大小和可否为NULL属性都没改变。

理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。然而MySQL还没有采用这种优化的方法，所有的MODIFY COLUIN 操作都将导致表重建。

另外一种方法是通过ALTER COLUMN操作来改变列的默认值:

这个语句会直接修改.frm文件而不涉及表数据。所以，这个操作是非常快的。

只修改.frm文件

从上面的例子我们看到修改表的.frm文件是很快的，但MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让 MySQL做一些其他类型的修改而不用重建表。

下面这些操作是有可能不需要重建表的:

• • 移除（不是增加）一个列的AUTO_INCREMENT属性。
  • 增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字串值。

基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，像下面这样:

1．创建一张有相同结构的空表，并进行所需要的修改（例如增加 ENUM常量)。

2．执行 FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。

3．交换.frm文件．

4．执行UNLOCK TABLES来释放第⒉步的读锁。

例：

下面以给sakila.film表的rating列增加一个常量为例来说明。当前列看起来如下:

假设我们需要增加一个 PG-14的电影分级;

注意，我们是在常量列表的末尾增加一个新的值。如果把新增的值放在中间，例如PG-13之后，则会导致已经存在的数据的含义被改变:已经存在的R值将变成PG-14,而已经存在的NC-17将成为R，等等。

接下来用操作系统的命令交换.frm文件:

再回到MySQL命令行，现在可以解锁表并且看到变更后的效果了:

最后需要做的是删除为完成这个操作而创建的辅助表:

快速创建MyISAM索引

\

为了高效地载入数据到 MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引:

这个技巧能够发挥作用，是因为构建索引的工作被延迟到数据完全载入以后，这个时候已经可以通过排序来构建索引了。这样做会快很多，并且使得索引树的碎片更少、更紧凑。

不幸的是，这个办法对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效。MyISAM 会在内存中构造唯一索引，并且为载入的每一行检查唯一性。一旦索引的大小超过了有效内存大小，载入操作就会变得越来越慢。

在现代版本的InnoDB版本中，有一个类似的技巧，这依赖于InnoDB的快速在线索引创建功能。这个技巧是，先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。Percona Server可以自动完成这些操作步骤。

也可以使用像前面说的ALTER TABLE的骇客方法来加速这个操作，但需要多做一些工作并且承担一定的风险。这对从备份中载入数据是很有用的，例如，当已经知道所有数据都是有效的并且没有必要做唯一性检查时就可以这么来操作。

下面是操作步骤:

1．用需要的表结构创建一张表，但是不包括索引。

2．载入数据到表中以构建.MYD文件。

3．按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm 和.MYI文件。

4．获取读锁并刷新表。

5．重命名第二张表的.frm和 .MYI文件，让 MySQL认为是第一张表的文件。

6．释放读锁。

7．使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。

这个操作步骤对大表来说会快很多。

配置文件调优

mysql的配置文件名为my.cnf（window为my.ini），不同情况下配置文件参数设置也不相同，应该根据具体场景调优。

InnoDB中最重要的选项是：

innodb_buffer_pool_size：缓存用户表（实际数据row）及索引数据的最主要缓存空间，对 Innodb 整体性能影响也最大，默认为8MB，建议设为内存的70%~80%（MyISAM只缓存索引）

innodb_log_file_size：日志文件大小，默认为48MB，应该调大，至少有几百MB

总结

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

• 尽量避免过度设计，例如会导致极其复杂查询的schema 设计，或者有很多列的表设计(很多的意思是介于有点多和非常多之间)。
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。

• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。尽量使用整型定义标识列。
• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT。

范式是好的，但是反范式(大多数情况下意味着重复数据）有时也是必需的，并且能带来好处。第5章我们将看到更多的例子。预先计算、缓存或生成汇总表也可能获得很大的好处。Justin Swanhart 的 Flexviews工具可以帮助维护汇总表。

最后，ALTER TABLE是让人痛苦的操作，因为在大部分情况下，它都会锁表并且重建整张表。我们展示了一些特殊的场景可以使用骇客方法﹔但是对大部分场景，必须使用其他更常规的方法，例如在备机执行ALTER并在完成后把它切换为主库。本书后续章节会有更多关于这方面的内容。