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

前言

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

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

1 整数类型

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

可以存储的值的范围从⑵^(N⑴)到2^(N⑴)⑴，其中N是存储空间的位数，比如TINYINT，它的存储范围就是⑴28⑴27。

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

有符号数和无符号数，使用相同的存储空间，具有相同的性能，因此可以根据实际情况选择。但请注意，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⑵	1
3⑷	2
5⑹	3
7⑼	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会删除所有末尾空格。

以下案例：

结果以下： 

另外，数据如何存储取决于存储引擎，比如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文件中保存“数字-字符串”映照关系的“查找表”。

以下案例：

测试实际存储的是数字：

结果以下： 

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

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

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

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

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

4 日期和时间类型

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

DATETIME类型能保存大范围的值，从1001年~9999年，精度为秒，占8个字节的长度。它将时间格式封装为YYYYMMDDHHMMSS的整数中，与时区无关。默许情况下，MySQL以一种可排序的格式显示DATETIME值，比如“2019⑴1⑴2 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。

以下案例：

使用以下查询：

结果以下： 

位类型的常见用法是：如果需要位一条数据保存许多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()用于这两种表示转换。

• 将IP地址转换为整型可使用 select inet_aton(ip) from xxx;
• 将整型转换为IP地址可使用 select inet_ntoa(intip) from xxx;

到此这篇关于深入理解MySQL数据类型的选择优化的文章就介绍到这了,更多相关MySQL数据选择优化内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！