# 选择优化的数据类型

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

更小的通常更好

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

但是要确保没有低估需要存储的值的范围，因为在 schema 中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。（如果系统不是很繁忙或则数据量不是很多，修改数据类型也比较容易）
简单就好

简单数据类型的操作通常需要更少的 CPU 周期。

例如：整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型更复杂。

如：应该使用 MySQL 内置的日期类型，而不是使用字符串来存储、应该用整型存储 IP 地址。
尽量避免 NULL

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

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

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

例外的是：InnoDB 使用单独的位（bit）存储 NULL 值，所以对于稀疏数据（大部分数据为 NULL）有很好的空间效率。但这一点不适用于 MyISAM。

本章只讨论基本的数据类型。MySQL 为了兼容性支持很多别名，例如 INTEGER、BOOL 以及 NUMERIC，他们只是别名，不会影响性能。建表时使用别名，然后用 SHOW CREATE TABLE 检查，会发现 MySQL 报告的是基本类型，而不是别名。

# 整数类型

两种类型：整数（whole number）和实数（real number）

整数类型	使用位空间	占用字节
TINYINT	8	1
SMALLINT	16	2
MEDIUMINT	24	3
INT	32	4
BIGINT	64	8

他们可存储值的范围是 -2^(N-1)^ 到 2^(N-1)^-1，N 是存储空间的位数。

整数类型可选 UNSIGNED 属性，表示不允许负值（无符号整数），正数范围大致提高了一倍。存储空间不变，性能不变。

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

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

# 实数类型

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

列类型	存储空间	是否精确类型
fload	4 个字节	否
double	8 个字节	否
decimal	每 4 个字节存 9 个数字，小数点占一个字节	是

FLOAT 和 DOUBLE 类型支持使用标准浮点运算进行近似运算（依赖平台的浮点数具体实现），CPU 直接支持原生浮点计算，但是不支持对 DECIMAL 的直接计算，相对而言浮点运算明显更快。

浮点和 DECIMAL 类型都可以指定精度。MySQL 5.0+ DECIMAL 类型允许最多 65 个数字，它只是一种 存储格式，在计算中 DECIMAL 会转换为 DOUBLE 类型。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL，例如财务数据。但在数据量比较大的时候，也可以考虑使用 BIGINT 代替，把小数位数去掉，比如 1.05 元，存储为 105 分，这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。

# 字符串类型

MySQL 支持多种字符串类型，每种类型还有很多变种，每个字符串列可以自定义自己的字符集和排序规则，这些东西会很大程度上影响性能。

类型	范围	说明
`Char(M)`	M=1~255 个字符	固定长度
`VarChar(M)`	一行中所有 varchar 类型的列所占用的字节数不能超过 65535 个字节；比如 UTF8mb4 一个字符占用 4 个字节，那么 varchar(10) = 40 字节	存储可变长度的 M 个字符
TinyText	最大长度 255 个字节	可变长度
Text	最大长度 65535 个字节	可变长度
MediumText	最大长度 16777215 个字节	可变长度
LongText	最大长度 42949667295 个字节	可变长度
Enum	集合最大数目为 65535	只能插入列表中的值

# VARCHAR 和 CHAR 类型

varchar 和 char 是两种最主要的字符串类型。很难精确解释这些值是怎么存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。

下面的讨论的前提是使用 InnoDB 或 MyISAM 引擎.

存储引擎存储 varchar 和 char 值的方式在内存中和在磁盘上可能不一样，所以 MySQL 服务器从存储引擎读出的值可能需要转换为另一种存储格式。下面是关于两种类型的比较。

# VARCHAR

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

TIP

笔者提示：上述讲解的其实是 MySQL 服务器与客户端交互协议里面的解析规则，预计后面可能还会有很多类似协议里面的知识点。

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

下面这些情况使用 VARCHAR 是合适的：

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

对于 InnoDB 来说，更灵活，它可以吧过长的 VARCHAR 存储为 BLOB。

# CHAR

CHAR 类型是定长的，当存储 CHAR 值时，会被 自动删除所有的末尾空格，而且会根据需要采用 空格进行填充 以方便比较。

CHAR 适合存储很短的字符串，或则所有值都接近同一个长度，比如 MD5 值。

对于经常变更的数据，CHAR 也比 VARCHAR 更好，因为定长类型不容易产生碎片。

对于非常短的列，CHAR 比 VARCHAR 在存储空间上也更有效率。（VARCHAR 需要额外字节存储长度）

并非所有的存储引擎都会按照相同的方式处理定长和变长字符串。Memory 引擎只支持定长的行，即使有变长字段也会根据最大长度分配最大空间。不过，填充和截取空格的行为在不同存储引擎都是一样的，因为这是在 MySQL 服务器层进行处理的。

# BINARY 和 VARBINARY

他们与 CHAR 和 VARCHAR 类似，他们存储的是二进制字符串，存储的是字节码而不是字符，填充也不一样：MySQL 填充 BINARY 采用的是 \0(零字节) 而不是空格，在检索时也 不会去掉填充值。

当需要存储二进制数据，并且希望 MySQL 使用字节码而不是字符进行比较时，这些类型比较有用。在比较上，按字节比较，因此比字符比较简单得多，所以也更快

# 慷慨是不明智的

使用 VARCHAR(5) 和 VARCHAR(200)存储 hello 的空间是一样的。那么使用更短有什么优势？

更长的列会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

所以最好的策略是只分配真正需要的空间。

# BLOB 和 TEXT 类型

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

实际上他们分别属于两组不同的数据类型家族：

字符类型：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT
二进制类型：TINBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB

与其他类型不同，MySQL 把每个 BLOB 和 TEXT 值当做一个独立的对象处理。存储引擎在存储时通常 会做特殊处理。当 BLOB 和 TEXT 值太大时，InnoDB 会使用专门的「外部」存储区域来进行存储，此时每个值在行内需要 1~4 个字节存储一个指针，然后在外部存储区域存储实际的值。

BLOB：存储的是二进制数据，没有排序规则或字符集
TEXT：存储的是字符串，有排序规则或字符集

对于排序也与其他类型不同：它只对每个列的最前 max_sort_length 字节而不是整个字符串做排序。

MySQL 不能将 BLOB 和 TEXT 列全部长度的字符串进行索引，也不能使用这些索引消除排序（下一章节会有更多的信息）

# 磁盘临时表和文件排序

因为 Memory 引擎不支持 BLOB 和 TEXT 类型，如果使用了 BLOB 和 TEXT 列并且需要使用隐式临时表，将不得不使用 MyISAM 磁盘临时表，即使只有几行数据也是如此。

这会导致严重的性能开销。即使配置 MySQL 将临时表存储在内存块设备上，依然需要需要昂贵的系统调用。

最好的办法是避免使用 BLOB 和 TEXT，如果无法避免，可以使用 SUBSTRING(column,length) 将列值转换为字符串（在 ORDER BY 子句中也适用），这样就可以使用内存临时表了。但是要确保截取的字符串足够短，不会使临时表的大小超过 max_heap_table_size 或 tmp_table_size，超过以后 MySQL 会将内存临时表转换为 MyISAM 磁盘临时表。

最坏的情况下的长度分配对于排序的时候也是一样的，所以这一招对于内存中创建大临时表和文件排序，以及在磁盘上创建大临时表和文件排序这两种情况都很有帮助。

例如：假设有一个 1000 完行的表，占用几个 GB 的磁盘空间。其中有一个 utf8 的字符集的 VARCHAR(1000) 列。每个字符最多使用 3 个字节，最坏情况下需要 3000 字节的空间。如果在 ORDER BY 中用到这个列，并且查询扫描整个表，为了排序就需要超过 30GB 的临时表。

如果 EXPLAIN 执行计划的 Extra 列包含「Using temporary」，则说明这个查询使用了隐式临时表。

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

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

下面是有一个例子：

create table enum_test
(
    e enum ('fish','apple','dog') NOT NULL
);

insert into enum_test(e)
values ('fish'),
       ('apple'),
       ('dog');

1
2
3
4
5
6
7
8
9

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

select e + 0
from enum_test;

1
2

e + 0
1
2
3

所以尽量避免使用数字作为枚举常量，这个双重属性很容易导致混乱。另外，枚举字段是按照内部存储的整数 而不是定义的字符串 排序的

select e
from enum_test order by e;

1
2

e
fish
apple
dog

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

select e
from enum_test order by field(e,'apple','dog','fish');

1
2

e
apple
dog
fish

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

由于 MySQL 把每个枚举保存为整数，并且必须进行查找才能转换为字符串，所有枚举有一些开销。通常枚举的列表都比较小，所以开销还可以控制，但是也不能保证一直如此。在特定情况下，把 char/varchar 列与枚举列进行关联可能会比直接关联 char/varchar 列更慢。

为了说明这个情况，我们对一个应用中的一章表进行了基础测试，看看在 MySQL 中执行上面说的关联的速度如何，该表有一个很大的主键：

create table webservicecalls
(
    day     date          not null,
    account smallint      not null,
    service varchar(10)   not null,
    method  varchar(50)   not null,
    calls   int           not null,
    items   int           not null,
    time    float         not null,
    cost    decimal(9, 5) not null,
    updated datetime,
    primary key (day, account, service, method)
) engine = InnoDB;

1
2
3
4
5
6
7
8
9
10
11
12
13

该表有 11 万行数据，只有 10MB 大小，所以可以完全载入内存。service 列包含了 5 个不同的值，平均长度为 4 个字符，method 列包含了 71 个值，平均长度为 20 个字符。

将这张表结构复制一张表名为 webservicecalls_enum，把 service 和 method 列改为 enum 类型。然后用主键列关联这两个表，下面是所使用的查询语句：

select sql_no_cache count(*)
from webservicecalls
         join webservicecalls_enum using (day, account, service, method);

1
2
3

我们使用 varchar 和 enum 分别测试了这个语句，结果如下

测试	QPS
varchar 关键 varchar	2.6
varchar 关联 enum	1.7
enum 关联 varchar	1.8
enum 关联 enum	3.5

结果显而易见了，所以在查找表时采用整数主键而避免采用这种基于字符串的值进行关联。

转换列为枚举类型还有另外一个好处。根据 show table status 命令输出结果中 Data_length 列的值，把这两列转换为 ENUM 可以让表的大小缩小 1/3。同样，转换后主键也只有原来的一半大小了。因为这是 InnoDB 表，如果表上有其他索引，减少主键大小会使主键索引也变得更小。后续会解析该问题。

# 小结

枚举有如下的优点：

相对于 varchar/char 来说占用空间更小
枚举关联枚举性能更高

缺点：

枚举关联 varchar 性能较差
排序按枚举的定义顺序排列，而不是字符串的顺序
列实际存储的是整数，查询时需要转换

# 日期和时间类型

注意：笔者发现在字符串类型的支持上，mysql 5.7 之前和之后差别还是挺大的。比如这里时间格式和支持的精度，5.7 都支持微妙了，这里只能支持到秒级。所以不再搬运 MySQL 8 中的总结性表格过来了

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

# DATETIME

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

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

# TIMESTAMP

保存了从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，和 UNIX 时间戳相同。只使用 4 个字节的存储空间，因此它的范围比 DATETIME 小得多，只能表示从 1970 年到 2038 年。提供了

FROM_UNIXTIME（） 函数把 Unix 时间戳转换为日期
UNIX_TIMESTAMP() 函数把日期转换为 Unix 时间戳。

4.1 以及更新的版本按照 DATETIME 的方式格式化 TIMESTAMP 的值。它显示的值也依赖于时区，MySQL 服务器、操作系统，以及客户端连接都有时区设置。

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

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

# 位数据类型

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

# BIT

在 MySQL 5.0 之前，BIT 和 TINYINT 的同义词，在 5.0 之后，这是一个特性完全不同的数据类型。

可以使用 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.

create table bitest(a bit(9));
insert into bitest values (b'00111001');
select a,a + 0 from bitest;