深入理解MySQL数据类型的选择优化-数据库

深入理解MySQL数据类型的选择优化目录前言 1 整数类型 2 实数类型 3 字符串类型 3.1 VARCHAR和CHAR类型 3.1.1 最大长度 3.2 Binary和VarBinary类型 3.3 BLOB和TEXT类型 3.3 ENUM类型 4 日期和时间类型 5 位数据类型 6 选择优化的数据类型 6.1 特殊的数据类型前言

前言

介绍了MySQL的常用数据类型的基本特性，以及数据类型的选择优化。

MySQL数据类型是定义列中可以存储什么数据以及该数据实际怎样存储的基本规则，正确的选择数据库字段的字段类型对于数据库性能有很大的影响。

1 整数类型

整数类型有五种：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用1、2、3、4、8字节（8、16、24、32、64位）的存储空间。

可以存储的值的范围从-2^(N-1)到2^(N-1)-1，其中N是存储空间的位数，比如TINYINT，它的存储范围就是-128-127。

所有的整数类型都默认是有符号数，即可正可负。所有的整数类型都可以设置unsigned属性，这表示将该整数字段无符号化，即不允许负值，这种操作大致可以使正数的上限提高一倍，例如TINYINT，unsigned可以存储的范围是0-255。

有符号数和无符号数，使用相同的存储空间，具有相同的性能，因此可以根据实际情况选择。但请注意，MySQL数据库中unsigned数的操作结果都是unsigned的，因此如果执行了计算，并且结果是负数，那么最终结果将难以预测，慎用unsigned。

和整数的类型无关的是，整数的计算一般使用有64位的BIGINT整数，即使在32位的环境中即是如此。

MySQL可以为整数指定宽度，例如int(1)，但这对大多数应用都是无意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具如MySQL命令客户端）显示字符的个数，而不是指定的存储大小。int(1)和int(10)对存储和计算来说是相同的，但这个规则只适用于整数类型而不适用于varchar和char类型。

2 实数类型

实数就是带有小数部分的数字，然而它们不止是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。实数类型有三种：FLOAT、DOUBLE、DECIMAL。

实数都可以指定精度，例如DECIMAL(18,9)，这表示值存储的有效位数为18，并且小数点后可以存储的位数为9，因此整数部分的位数同样也是9。MySQL 在存储值时执行舍入，因此如果将 89.0009 插入FLOAT(6,3) 列，则近似结果为 89.001。

MySQL既支持精确类型，也支持不精确类型。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算，称为浮点数，FLOAT固定占4个字节，DOUBLE固定占8个字节，精度更高。和整数类型一样，浮点只能选择存储类型，在计算时，MYSQL5使用DOUBLE作为内部浮点计算的类型。

DECIMAL类型则用于存储精确的小数，在MYSQL5.0以及更高的版本中，DECIMAL类型支持精确计算。DECIMAL 的最大允许65个数字。DECIMAL列使用二进制格式进行存储，MYSQL5.0和更高版本中，会将数字打包保存到一个二进制字符串中：每个值的小数点占据一个字节，每个值的整数和小数部分的存储要求分别确定，每9个十进制的数字被打包为4个字节，剩余的需要另外打包：

剩下的数字	所需字节数
0	0
1-2	1
3-4	2
5-6	3
7-9	4

例如，DECIMAL (18, 9)小数点两边都有9位数，因此两边都需要4个字节，另外小数点本身占一个字节，一共需要九个字节。

CPU不支持DECIMAL的精确计算，精确计算是MySQL服务器自身实现的，因此需要额外的的空间和计算开销。CPU直接支持浮点运算，所以浮点运算更快，所以尽量只在需要对小数进行精确计算时使用DECIMAL类型（如与钱相关的数据）。

在数据量比较大的时候，可考虑使用BIGINT代替DECIMAL，此时只需根据小数位数乘以相应的倍数即可。比如，如果存储的财务数据精确到万分之一时，可以把所有的金额乘以一百万，然后将结果存入BIGINT里，这样就可以避免浮点存储计算不准确和DECIMAL精度计算代价高的问题。

3 字符串类型

MySQL支持多种字符串类型，主要的有：VARCHAR、CHAR、BLOB、TEXT、ENUM等。

3.1 VARCHAR和CHAR类型

VARCHAR和CHAR是两种最主要的数据类型，但是不同的存储引擎会将这两种数据类型以不同的样式存入磁盘和内存中，下面是InnoDB或者MyISAM存储引擎中VARCHAR和CHAR的介绍。

VARCHAR类型：

VARCHAR类型用于存储可变长度的字符串，是最常见的字符串数据类型，它比定长类型的CHAR更节省空间，因为它仅使用必要的空间。实际长度由最长的行的大小和使用的字符集确定。
VARCHAR需要一个或者两个额外的字节记录字符串长度，VARCHAR列的最大长度小于或等于255个字节时需要一个字节来记录，大于255时需要两个字节来记录。例如VARCHAR(10)的列需要11个字节的存储空间，VARCHAR(1000)的列则需要1002个字节。（字符串长度不是指的字节数，而是字符数，因此多字节字符会需要更多的空间存储，比如中文）。
虽然VARCHAR节省了存储空间，但是在UPDATE列时如果使得行变得比原来更长，这就可能导致需要额外的工作，如果一个行占用的空间增长，并且在页内没有更多空间可以存储时，InnoDB引擎就会做页分裂这个额外的操作来使行可以放进页内，而MyISAM则会将行拆成不同的片段存储。
如果字符串列的最大长度可能比平均长度大很多，并且列的更新很少，那么建议使用VARCHAR类型。
在MYSQL 5.0及更高版本，MYSQL在使用VARCHAR存储和检索时会保留字符串末尾的空格，但在4.1或更老的版本，MYSQL会trim剔除末尾空格。
如果使用InnoDB引擎，那么过长的VARCHAR会被存储为BLOB。

CHAR类型：

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

如下案例：

CREATE TABLE test(a VARCHAR(4), b CHAR(4));
INSERT INTO test VALUES ('ab ', 'ab ');
SELECT CONCAT(a, '+'), CONCAT(b, '+') FROM test;

结果如下：

另外，数据如何存储取决于存储引擎，比如Memeory引擎只支持定长的行，即使有变长字段，也会根据最大长度分配空间。但是对于填充和截取空格的行为在不同的存储引擎都是一样的，因为这是在MySQL服务器进行处理的。

另外，虽然VARCHAR(5)和VARCHAR(200)存储"hello"的磁盘空间开销是一样的，然是使用更短的列仍然有很大的优势。因为更长的列会消耗更多的内存，因为mysql通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表来进行排序或操作时特别糟糕，在利用磁盘临时表进行排序时也同样糟糕。因此最好的策略是只分配真正需要的空间。

3.1.1 最大长度

MySQL4.1版本之前，varchar(n)，指的是n个字节，如果存放UTF8类型字符时，只能存n/3个字符（每个字符占3个字节）。

MySQL4.1版本开始，varchar(n)，指的是n个字符，无论存放的是数字、字母还是汉字，都可以存放3个，但是字节数不能超过最大限制。

首先，一张表中所有字段的长度总和不超过65535字节。

CHAR类型最大长度为255个字符（字符数量，而非字节数量）。

而VARCHAR类型的最大长度则是可变的，需要计算，VARCHAR的最大长度还与字符集有关。如果一张表有VARCHAR和CHAR类型的字段分别一个，采用utf8mb4编码（一个字符最多占4个字节），那么所有字段一共最多65535/4=16383.75=16383个字符长度，如果其中CHAR类型的字段长度为5，那么VARCHAR类型的最大长度则是16378。

字符类型若为latin1，每个字符最多占1个字节，最大长度不能超过65532。
字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766。
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21844。
字符类型若为utf8mb4，每个字符最多占4个字节，最大长度不能超过16383。

3.2 Binary和VarBinary类型

Binary和VarBinary用于存储二进制字符串，存储的是字节码而非字符。

MySQL在填充Binary时用的是\0(零字节)而不是空格，在检索时也不会去掉填充值（这是特别需要注意的）。

MySQL在比较Binary字符串时，每次按一个字节，并且是根据该字节的数值进行比较；因此二进制比较比字符串的比较快。

3.3 BLOB和TEXT类型

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

实际上它们属于不同的数据类型家族：字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT，对应的二进制类型是 TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB和SMALLBLOB是同义词，TEXT和SMALLTEXT是同义词。

与其他类型不同，每个BLOB和TEXT值会被当成独立的对象对象。当数据很大时，InnoDB会专门使用“外部”存储区域存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

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

MySQL只会对BLOB和TEXT列中最前max_sort_length字节进行排序，而不是整个字符串。如果只排序前面一小部分字符，则可以减小max_sort_length的值，或者使用order by substring(列名，length)。

MYSQL不能将BLOB和TEXT列全部长度字符串进行索引，也不能用这些索引消除排序。

3.3 ENUM类型

有时可以使用ENUM代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。

ENUM的存储非常紧凑，会根据列表值的数量压缩到一个或两个字节中。

ENUM在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

如下案例：

-- 创建表
CREATE TABLE enum_test(
 e enum('fish','apple','dog') NOT NULL
)
-- 插入枚举数据
INSERT INTO enum_test (e) VALUES('fish'),('dog'),('apple');

测试实际存储的是数字：

select e+0 from enum_test;

结果如下：

枚举字段支持同时采用字符串和数值进行判断和计算，如上面的sql，比如和数值类型进行比较。采用数值时，使用的枚举字段的排序值，排序值从1开始！

需要注意的是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的！

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

枚举的字符串列表是固定的，添加或者删除枚举字符串必须使用ALTER TABLE这样的DDL语句，因此对于未来可能会改变的字符串，使用枚举不是一个好主意。

由于MySQL把每一个枚举值保存为整数，在检索时必须进行额外的查找才能转换为字符串，所以会有一定的开销。

4 日期和时间类型

MySQL有很多日期和时间类型，比如DATE、DATETIME、TIMESTAMP、TIME、YEAR。可以存储的最小时间粒度是秒。

DATETIME类型能保存大范围的值，从1001年~9999年，精度为秒，占8个字节的长度。它将时间格式封装为YYYYMMDDHHMMSS的整数中，与时区无关。默认情况下，MySQL以一种可排序的格式显示DATETIME值，比如“2019-11-12 13:56:45”。

TIMESTAMP类型的存储的是1970年1月1日午夜以来的秒数（格林威治标准时间），只使用了4个字节的存储空间，因此它的范围比DATETIME小得多：1970年~2038年。

TIMESTAMP列会默认设置为Not null属性，所以在插入数据时，若不给值就会默认为当前时间戳。

目前，MySQL没有提供合适的数据类型以存储比秒更小粒度的日期和时间格式，但可以使用其他方法，比如使用BIGINT存储微秒级别的时间戳，比如用DOUBLE存储秒之后的小数部分。

5 位数据类型

MySQL的所有位类型，不管底层存储和处理方式如何，从技术上来说都是字符串类型。常见位数据类型是BIT和SET。

在MySQL5.0之前，BIT是TINYINT的同义词，但5.0之后有了新的含义。

BIT(1)表示一个包含单个位的字段，BIT(2)则表示两个位的字段，最大长度为64位。

不同的存储引擎对BIT 有不同的存储方式。MyISAM会打包存储所有BIT列，比如17个单独的BIT列只需要17位存储，只需要3个字节的空间。而Memory和InnoDB等存储引擎，则是为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。

注意，MySQL将BIT当作字符串类型，而不是数字类型。如果检索BIT(1)的值时，结果是一个包含二进制0或1的字符串，而不是ASCII码的“0”或“1”。然而，在数字上下文中检索时，结果将是字符串转换成的数字。比如存储一个值为b‘00111001’（等于十进制57）到BIT(8)的列并直接检索它，得到的内容是“00111001，而如果检索的内容是“列+0”，那么得到的值是57。

如下案例：

CREATE TABLE `bits` (
  `bitss` bit(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `bits`(`bitss`) VALUES (b'00111001');
INSERT INTO `bits`(`bitss`) VALUES (b'111');

使用如下查询：

select bitss,bitss+0 from bits

结果如下：

位类型的常见用法是：如果需要位一条数据保存许多true/false值，则可合并这些列到一个SET数据类型的列。

6 选择优化的数据类型

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

更小的通常更好。一般情况下，应该尽量使用可以正确存储数据的最小数据类型，因为它们占更少的磁盘，内存和CPU缓存，处理时需要的CPU周期也更少。
简单就好。简单数据类型的操作通常需要更少的CPU周期，例如，整形比字符操作代价更低，因为字符集和校对规则（排序规则）是字符比较比整形比较更复杂。比如，使用MySQL内建类型（date、time、datetime）而不是字符串来存储日期和时间，另外一个是用整型来存储IP地址。
尽量避免NULL。通常在定义表字段时，如果没有指定列为NOT NULL，默认都是允许为NULL的。
- 如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值的比较都更复杂。可为NULL的列会占用更多的存储空间，当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
- 通常情况下最好指定列为NOT NULL，但一般把可为NULL的列改为NOT NULL带来的性能提升比较小，所以在调优时，没有必要首先对此下手。
- 但是对于NULL有一个例外是，InnoDB仅会使用单独的位(bit)存储NULL值（即是否为NULL的标记位），因此NULL值所以对于稀疏数据（大部分数据为NULL，只有少部分非NULL的数据）有很好的空间效率，但这不使适于MyISAM。

6.1 特殊的数据类型

IPv4地址经常被人用varchar(15)来存储，但是它实际上是一个32位无符号的整数，不是字符串，小数点将地址分四段的表示方式只是为了让人容易阅读，所以应该用无符号整型来存储IP地址（如果不使用无符号INT，则长度不够），MYSQL用INET_ATON() 和INET_NTOA()用于这两种表示转换。