高性能mysql——数据类型

基础数据类型

• 整数
• 实数
• 字符串
• 日期
• 位数据

整数

整数共有以下几种类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。下面是他们的存储空间：

数据类型	存储空间（单位：位）
TINYINT	8
SMALLINT	16
MEDIUMINT	24
INT	32
BIGINT	64

同时也得出了每个整数类型的范围，比如，INT类型为正负20亿左右。

整数类型可以添加 UNSIGNED 标识，意为“无符号整数”，标识不允许有负数。这样证书类型的正数范围增加一倍，例如 UNSIGNED TINYINT 类型的范围是从0 ~ 255。无符号和有符号整数使用的存储空间相同， TINYINT 和 UNSIGNED TINYINT 均占用8位存储空间。

mysql可以为整数类型指定宽度，例如 INT(10)。这种方式对于存储和计算来说是无意义的，不会限制值的存储空间和数据范围，INT(1) 和 INT(10) 对存储和计算来说没有区别。这种方式只会修改某些mysql的交互工具（例如命令行客户端）用来显示字符的个数。

不同的数据类型决定了mysql如何在内存和硬盘中保存整数。但是对于整数的计算来说，一般都使用64位的 BIGINT 类型，即使是32位的环境。一些聚合函数会使用下文提到的 DECIMAL 或者 DOUBLE 进行计算。

这里还要插入一个说明，mysql的存储中没有布尔类型。实际建表中使用的 BOOLEAN 类型，会由mysql自动转换为 TINYINY(1) 。同时mysql中存在四个常量：true、false、TRUE、FALSE，它们分别代表1、0、1、0。下面简单实验一下：

alter table test add isOk boolean;
desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| isOk  | tinyint(1)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

insert into test(isOk) values(true);
select isOk from test;
+------+
| isOk |
+------+
|    1 |
+------+

BOOLEAN 类型是mysql为了兼容其他数据库语法而存在的别名，除此之外还有 INTEGER（对应 INT ） 、NUMERIC （对应浮点数中的 DECIMAL ） 等。在建表之后mysql会将别名自动转为真实的数据类型。

实数

实数是带小数的数字。有以下几种类型：FLOAT、DOUBLE和DECIMAL。其中，FLOAT和DOUBLE为浮点数，不精确，计算规则采用部署环境的浮点数计算规则；而DECIMAL可以保存精确小数。

DECIAML在mysql5.0及以后的版本中，支持精确计算（与上文精确保存区分），由mysql自己实现。而4.*版本及之前，采用浮点数计算规则来计算DECIMAL类型，不支持精确计算，只是一个保存类型。浮点数运算由cpu支持，所以速度比mysql自身的精确计算更快。

FLOAT占用4个字节，DOUBLE占用8个字节。与整数一样，FLOAT和DOUBLE只是存储类型，计算时统一采用DOUBLE。支持的精度为(P, D)，其中P为总有效数字，D为小数位数。如果没有指定，则会按照最大精度来显示。指定浮点数精度会影响mysql对数据的存储和计算，建议FLOAT和DOUBLE不指定精度。

就算指定了精度，FLOAT和DOUBLE在实际的保存和计算时，还是不精确的

而对于DECIMAL类型，精度会影响占用空间。mysql 5.0及以上版本使用二进制字符串保存DECIMAL类型，每4个字节保存9个数字，不足9位则取整。例如，DECIMAL(14, 9)，小数点前为 9 + 5，占用7个字节，小数点后占用4个字节，小数点本身1个字节，共12个字节，保存共14位精确数字。

虽然DECIMAL支持精确计算，但是相比浮点数，需要额外的空间和计算开销，所以一般只用在精确计算的列，例如涉及金钱计算。

有一种方法是将精确计算的类型定义为BIGINT，存到数据库时按照需要的精度乘上对应的倍数。
比如说计算精度为2位小数，则存入数据库前乘以100。

字符串

mysql中的字符串主要包括VARCHAR、CHAR、BLOG、TEXT这么几种类型。从mysql 4.1开始，字符串类型可以自己定义字符集和排序规则。因为字符串的实现与存储引擎相关，以下环境均为InnoDB。

VARCHAR

VARCHAR类型用于保存变长字符串，是最常见的字符串类型。因为变长只占用需要的空间，所以比定长字符串更节省空间。

有一种情况例外：创建表时设定 ROW_FORMAT=FIXED，则每一行都使用定长空间。

VARCHAR需要1到2个字节来记录字符串长度：如果列的最大长度（最大！）小于等于255字节，则使用1个字节，超出这个范围则使用2个字节。

在mysql 5.0.3之后，VARCHAR(n) 表示保存n个字符，在之前则表示n个字节。对于新版本，因为mysql非大字段长度最大为65535字节，所以实际使用中 VARCHAR(n) 能保存多少字符、占用多少空间需要根据指定的字符集来计算。
例如：VARCHAR(10) 使用gbk字符集，则最多可以保存10个字符，占用21个字节（因为有1个字节用于保存字符串长度）。如果使用gbk编码，则 VARCHAR(60000) 类型是不允许的，InnoDB会自动转为大字段 BLOG 。

因为VARCHAR是变长的，所以可以节省存储空间。但是，对于update来说，字符串可能会变的更长，导致原来的存储位置（叫做页，这个以后来说）放不下，存储引擎会做一些额外的工作（对于InnoDB来说是分裂页），会影响更新性能。

一般来说，以下场景更适合使用VARCHAR：

• 字符串的最大长度比平均长度大很多
• 字符串的更新很少，不存在碎片
• 使用了像是 UTF-8 之类的字符集，字符对应的字节不同。

对于MYSQL 5.0及更高版本，会在存储和检索时会保留末尾空格。之前的版本，会删除末尾空格。

不要因为VARCHAR是变长类型，就定义一个大值，能用VARCHAR(5)就不要VARCHAR(200)。虽然硬盘上的存储都使用所需要的空间，但是在内存中，mysql会分配固定大小的内存空间。大的VARCHAR定义会导致性能下降。

CHAR

CHAR类型为定长字符串。mysql会根据列定义分配对应的固定空间。当存储CHAR时，mysql会删除所有的末尾空格。但是，CHAR还是会根据需要填充空格，以方便比较。

CHAR适合存储很短的字符串，或者所有字符串接近一个长度。例如，可以使用**CHAR(1)**保存状态位。此外，CHAR定长的特性保证了更新字符串时不会出现碎片，update性能更好。

除了定长与截断空格的特性，CHAR与VARCHAR基本相同。

与CHAR和VARCHAR类似，有BINARY和VARBINARY类型，用于保存二进制字符串。两者的特性类似。但是二进制字符串保存字节码而不是字符，使用\0（零字节）填充而不是空格，在检索时也不会去掉充值。

BLOG和TEXT

这两个一起来讲，是专门为了存储大字符串的数据类型。BLOG保存二进制字符串，没有排序规则和字符集；TEXT保存普通字符字符串，有排序规则和字符集。这两个实际上是两个大字符串类型家族，如下：

BLOG	TEXT	大小范围
TINYBLOG	TINYTEXT	255byte
SMALLBLOG	SMALLTEXT	65k
BLOG	TEXT	65k
MEDIUMTEXT	MEDIUMTEXT	16M
LONGTEXT	LONGTEXT	4G

其中BLOG是SMALLBLOG的同义词，TEXT是SMALLTEXT的同义词。

与其他数据类型不同，mysql把每个BLOG和TEXT当做一个独立的对象处理，存储引擎会做特殊处理。当BLOG和TEXT值太大时，InnoDB会使用专门的“外部”存储空间，行内只保留一个1~4字节的指针，指向外部存储空间的实际值。

mysql对BLOG和TEXT进行排序的方式与其他数据类型不同：mysql只对最前面 max_sort_length 个字节而不是整个字符串做排序。mysql无法索引BLOG和TEXT全部的字符串，也不能使用这些索引消除排序。

ENUM （枚举）

如果某列的字符串类型仅有一定数量的不重复字符串，那么可以将该列定义为ENUM类型。mysql在内部会将每个ENUM保存为整数（从1开始自增），存储到一个或两个字节中，然后在表的配置文件.frm中保存“数字 - 字符串”映射关系。

这种数字映射字符串的方式，在增删改查中会体现出两面性：如果直接查询，则ENUM的数据表现为字符串；如果上下文为数字计算，那么ENUM的数据表现为实际存储的整数。

除了增删改查，ENUM体现整数性质的地方还有排序，这一点很奇怪：mysql对ENUM类型进行排序，排序规则为底层保存的整数，而不是定义ENUM时的字符串顺序。所以在定义ENUM类型时就要按照排序规则进行设定。

ENUM类型的另一个问题是，取值是有范围的。添加或删除字符串需要使用ALTER TABLE。mysql 5.1之后，针对在ENUM末尾添加枚举值进行优化，不会重建整个表，但是其他情况还是需要重建表。

ENUM还有个问题是，因为实际的保存类型是整数，必须查找对应关系才能转换为字符串，所以枚举有一定的性能损失。比如，CHAR/VARCHAR与ENUM进行关联，比起相同类型直接关联性能更差。

一个通用的设计实践：索引使用整数而不是字符串，可以减少由于字符集和编码类型引起的性能损失，速度更快。

日期和时间类型

mysql提供了以下几种日期时间类型：YEAR、DATA、TIME、DATATIME、TIMESTAMP，能保存的最小粒度为秒：

日期时间类型	格式
TEAR	年
DATA	年-月-日
TIME	时:分:秒
DATATIME	年-月-日 时:分:秒
TIMESTAMP	年-月-日 时:分:秒

对于一般的应用程序，主要使用两种类型：DATATIME或TIMESTAMP。两者的区别如下：

• DATACENTER：
  范围为1001年到9999年，精度为秒。把日期和时间封装在YYYYMMDDHHMMSS的整数内，与时区无关，使用8个字节空间存储。默认情况下，mysql使用标准ANSI格式展示（“2020-05-24 13:20:00”）。

• TIMESTAMP：
  保存了从1970年1月1日午夜（格林尼治时间）以来的秒数，与unix时间戳相同。TIMESTAMP使用4个字节的存储空间，相对的范围只有1970年到2038年。TIMESTAMP显示的值依赖时区设置，所以和DATACENTER在数据库保存相同数据时，展示上可能有区别，与mysql服务器、操作系统及客户端的时区配置有关。最后，TIMESTAMP默认为 NOT NULL，与其他数据类型不同。

如果需要存储比秒的粒度更小的时间，那就需要使用其他方式：BIGINT或者DOUBLE。

位数据

mysql中包含位类型：BIT和SET。

• BIT：
  在mysql 5.0之前，BIT和TINYINT是同义词。但是在5.0之后，BIT是全新的数据类型。
  BIT(n) 表示有n个位，最大有64个位。对于InnoDB，虽然是位类型，但是依然会使用一个足够小的整数类型来保存，并不能减小存储空间。
  另外，mysql会把BIT类型当做字符串。由此出现了一个两面性：直接查询时，结果是一个ASCII码对应的字符串；但是在数字计算中使用时，会表现为二进制的值。例如，保存一个 b'00111001'到BIT(8)并检索，会得到ASCII码为57（二进制的值）的字符“9”；但是如果在数学计算中，表现出来的值为57。为此，一般不推荐使用BIT类型。

如果一定要使用一个字节的类型，可以使用CHAR(0)。可以保存空值NULL或者空字符串（长度为零）。

• SET:
  SET使用一系列打包的位集合来表示，适合用来保存很多个true/false值，可以有效利用存储空间。但是修改定义需要 ALTER TABLE，代价较高。也无法在SET上通过索引查找。

有一种更为灵活的方式，就是使用整数类型，然后对整数进行位操作，<<或>>什么的。好处是更加灵活，坏处是需要自己维护对应的文档，并对位状态进行说明。
作者有一次对接使用整数位操作的数据，数据展示为1~7，但实际上的业务逻辑由3位的二进制01进行保存。因为没有对应文档，没有理解字段的含义，导致应用程序出现问题。没有文档，这种方式就是一个大坑