高性能MySQL（第三版）第四章：Schema与数据类型优化

4.1 选择优化的数据类型

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

• 更小的通常更好
  一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快。因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。但是要确保没有低估需要存储的值的范围，因为在schema中的增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择不会超过范围的最小类型。
• 简单就好
  简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。这里有两个例子：一个是应该使用MySQL内建的类型而不是字符串来存储时间和日期，另一个是应该用整型存储IP地址。
• 尽量避免NULL
  很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性（TIMESTAMP除外），然而通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。
  如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引统计和值比较更加复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊的处理。当可为NULL的字段被索引时，每个索引记录需要一个额外的字节，在MyASIM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
  通常把可为NULL的列改为NOT NULL 带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中查找并修改这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计为NULL的列。当然也有一些例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据（很多值为NULL，只有少数行是非NULL）有很好的空间效率。但这一点不适用于MyISAM。

在为列选择数据类型时，第一步需要确定适合的大类型，如：数字、字符串、时间等。然后，才选择具体的类型，比如数字类型有：TINYINT、INT、BIGINT，字符串类型有：VARCHAR、CHAR，时间类型有：DATETIME、TIMESTAMP。如果我们要保存年龄，年龄是数字类型的，INT和BIGINT都可以保存，但通常情况下，INT就已经绰绰有余了。

很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类数据类型有时也有一些特殊的行为和属性。

例如，DATETIME和TIMESTAMP列都可以存储相同类型的数据：时间和日期，精确到秒。然而，TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。

4.1.1 整数类型

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

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

有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

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

4.1.2 实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DEMICAL存储比BIGINT还要大的整数。MySQL既支持精确类型，也支持不精确类型。浮点数类型在存储同样范围的值时，通常比DEMICAL使用更少的空间。FLOAT使用4个字节，DOUBLE使用8个字节，DOUBLE比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型，MySQL使用DOUBLE作为内部浮点计算的类型。

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

4.1.3 字符串类型

MySQL支持多种字符串类型，每种类型还有很多变种。VARCHAR和CHAR是两种最主要的字符串类型，但很难解释这些值是如何存储在磁盘和内存中，因为这跟存储引擎的实现有关。但我们可以介绍如果在存储引擎是InnoDB或MyISAM对字符串的存储。

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这样复杂的字符集，每个字符都使用不同的字节数进行存储。

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

如果觉得CHAR类型的行为有些难以理解，我们可以通过一些例子来说明。首先我们创建一张只有一个CHAR(10)字段的表，并往里面插入一些值：

mysql> create table test1(age tinyint(1));
Query OK, 0 rows affected (0.09 sec)
 
mysql> INSERT INTO char_test(char_col) VALUES('string1'), ('  string2'), ('string3 ');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

当检索这些值的时候，会发现sring3末尾的空格被截断了。

mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;
+----------------------------+
| CONCAT("'", char_col, "'") |
+----------------------------+
| 'string1'                  |
| '  string2'                |
| 'string3'                  |
+----------------------------+
3 rows in set (0.01 sec)

如果用VARCHAR(10)字段存储相同的值，可以得到如下结果：

mysql> CREATE TABLE