关于mysql优化:你真的对MySQL数据类型了解吗

MySQL 反对的数据类型十分多，抉择正确的数据类型对于取得高性能至关重要。本文将介绍 MySQL 的数据类型，以及通过数据类型简略介绍对应的开发标准。

注：在本章节中所提到的严格模式，指的是 STRICT_TRANS_TABLES 和 STRICT_ALL_TABLES 两个中的一个启用或者都启用。

1 . 抉择优化的数据类型

MySQL 反对的数据类型有很多，抉择正确的数据类型对于取得高性能至关重要。咱们在抉择数据类型上，有几个简略的准则。

• 更小的通常更好

个别状况下，应该尽量应用能够正确存储数据的最小数据类型。例如，只须要存 100 以内的整数，TINYINT 更好。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，解决时须要的 CPU 周期也更少。

然而要确保没有低估须要存储的值的范畴。因为在 schema 中的多个中央减少数据类型的范畴是一个十分耗时和苦楚的操作。如果无奈确定哪个数据类型是最好的，就抉择你认为不会超过范畴的最小类型（如果零碎不是很忙或者存储的数据量不多，或者是在能够轻易批改设计的晚期阶段，这时候批改数据类型比拟容易）。

• 简略就好

简略数据类型的操作通常须要更少的 CPU 周期。例如，整型比字符操作代价更低，因为字符集和校对规定（排序规定）使字符比拟比整型比拟更简单。比方：应该应用 MySQL 内建的类型（DATE，TIME，DATETIME）而不是字符串来存储日期和工夫；以及应该用整型存储 IP 地址。

• 尽量避免 NULL

很多表都蕴含可为 NULL（空值）的列，即便应用程序并不需要保留 NULL 也是如此。这是因为可为 NULL 是列的默认属性（如果定义表构造时没有指定列为 NOT BULL，默认都是容许为 NULL 的）。通常状况下，最好指定列为 NOT NULL，除非真的须要存储 NULL 值。

如果查问中蕴含可为 NULL 的列，对 MySQL 更难优化。因为可为 NULL 的列使得索引、索引统计和值比拟都更简单。可为 NULL 的列会应用更多的存储空间，在 MySQL 里也须要非凡解决。当可为 NULL 的列被索引时，每个索引记录须要一个额定的字节，在 MyISAM 里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为 NULL 的列改为 NOT NULL 带来的性能晋升比拟小，所以（调优时）没有必要首先在现有 schema 中查找并批改掉这种状况，除非确定这会导致问题。然而，如果打算在列上建索引，就应该尽量避免设计成可为 NULL 的列。

当然也有例外，例如，InnoDB 应用独自的位（bit）存储 NULL 值，所以对于稠密数据（很多值为 NULL, 只有少数行的列有非 NULL 值）有很好的空间效率。但这一点不适用于 MyISAM。

下一步是抉择具体类型。很多 MySQL 的数据类型能够存储雷同类型的数据，只是存储的长度和范畴不一样、容许的精度不同，或者须要的物理空间（磁盘和内存空间）不同。雷同大类型的不同子类型数据有时也有一些非凡的行为和属性。

例如，DATETIME 和 TIMESTAMP 列都能够存储雷同类型的数据：工夫和日期，准确到秒。然而 TIMESTAMP 只应用 DATETIME 一半的存储空间，并且会依据时区变动，具备非凡的自动更新能力。另一方面，TIMESTAMP 容许的工夫范畴要小得多，有时候它的非凡能力会成为阻碍。

2 . 整数类型

整数类型是数据库中最根本的数据类型，包含整数和实数。如果存储整数，能够应用：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。别离应用 8、16、24、32、64 位存储空间。它们能够存储的值的范畴从 -2(N-1) 到 2(N-1)-1, 其中 N 是存储空间的位数。

MySQL 能够为整数类型指定宽度。例如 INT(11)，对大多数利用这是没有意义的：它不会限度值的非法范畴，只是规定了用来显示字符的宽度。对于存储和计算来说，INT(1) 和 INT(20) 是雷同的。

整数类型有两个属性：UNSIGNED 和 ZEROFILL。

整数类型有可选的 UNSIGNED 属性，示意不容许负值，这大抵能够使负数的下限进步一倍。例如 TINYINT UNSIGNED 能够存储的范畴是 0 ~ 255，而 TINYINT 的存储范畴是 -128 ~ 127。

ZEROFILL 是数字前须要填充一些 0 值的时候应用的。例如‘0001’，‘0002’，比方咱们常常看到的股票代码。在应用 ZEROFILL 参数时，MySQL 会主动为该列增加 UNSIGNED 属性。ZEROFILL 也只是一个显示属性，底层存储仍是一般整数。如果存储的数据长度超过 ZEROFILL 定义的宽度时，此数据会被残缺的显示进去而不进行 0 的填充；如果存储的数据长度小于设定的宽度，则主动填充 0。

3 . 实数类型

实数是带有小数局部的数字。然而，它们不只是为了存储小数局部；也能够应用 DECIMAL 存储比 BIGINT 还大的整数。MySQL 既反对准确类型，也反对不准确类型。

FLOAT 和 DOUBLE 类型是用来示意近似数值的数据类型，应用规范的浮点运算进行近似计算。单精度浮点数（FLOAT）应用 4 个字节存储，双精度浮点数（DOUBLE）应用 8 个字节存储。

MySQL 容许非标准语法：FLOAT(M,D),DOUBLE(M,D)。M 和 D 别离示意精度和标度。M 是数据的总长度，D 是小数点后的保留长度。比方，定义为 FLOAT(7,4) 的一个列能够显示为 -999.9999。MySQL 在保留值时会进行四舍五入。因而在 FLOAT(7,4) 列内插入 999.00009 的近似后果是 999.0001。

如果插入值的精度高于理论定义的精度，零碎会主动进行四舍五入解决，使插入的值合乎咱们的定义。所以在一些须要准确小数的状况下，比方：财务工资类型这种场景，请不要应用 FLOAT 和 DOUBLE。

从 MySQL 8.0.17 开始，不倡议应用非标准语法，并且在未来的 MySQL 版本中将删除对 FLOAT(M,D) 和 DOUBLE(M,D) 的反对。

SQL 规范容许在关键字 FLOAT 前面的括号内用来指定精度（但不能为指数范畴），就是 FLOAT(p)。FLOAT(p) 中的 p 也是示意精度（以位数示意），但 MySQL 只应用该值来确定列的数据类型为 FLOAT 或 DOUBLE。当 0≤p≤24 时，MySQL 把它当成 FLOAT 类型，当 25≤p≤53 时，MySQL 把它当成 DOUBLE 型。

咱们发现：此种类型的 FLOAT 只能保障前 6 位整数不四舍五入，而 FLOAT(M,D) 则不会这样。

DECIMAL 和 NUMERIC 类型存储准确的数值类型，比方财务数据、足球比赛中的赔率等等。在 MySQL 中，NUMERIC 是以 DECIMAL 来实现的。因而无关 DECIMAL 的阐明同样实用于 NUMERIC。

MySQL 中 DECIMAL 以二进制格局存储值。每 4 个字节存 9 个数字。这种存储形式对于整数与小数局部是离开存储的。每 9 个数字须要 4 个字节，剩下的数字所需的存储空间如下所示：

图 4 -1

举例来说，DECIMAL(18,9) 小数点两边各有 9 个数字，因而整数和小数局部别离各须要 4 个字节，小数点自身占 1 个字节。DECIMAL(20,6) 有 14 个整数和 6 个小数，整数局部中的 9 个数字须要 4 个字节，剩下的 5 个数字须要 3 个字节；小数局部 6 个数字须要 3 个字节。

在 DECIMAL 列申明中，能够（通常是）指定精度和小数位数。例如：salary DECIMAL(5,2)。其中，5 是精度，2 是小数位数。精度示意值存储的有效位数，小数位数示意小数点后能够存储的位数。

规范语法要求 DECIMAL(5,2) 可能存储具备五位数字和两位小数的任何值。因而能够存储在 salary 列中的值的范畴是从 -999.99 到 999.99。

在规范语法中，语法 DECIMAL(M) 等价于 DECIMAL(M,0)。MySQL 也反对这种变体。默认的 M 是 10。

如果小数位数是 0，表明 DECIMAL 不含小数局部。

小数点和正数的‘-’符号不包含在 M 中。DECIMAL 反对的 M 为 65，D 是 30。如果调配给此类型的值小数点后位数超过指定的标度 D 容许的范畴，值将按标度 D 进行转换（准确的行为是特定于操作系统的，然而通常是将其截断为容许的位数）。

DECIMAL 列不存储结尾的 +、- 和 0 数字。如果你向 DECIMAL(5,1) 的列中插入 +0003.1，MySQL 会存储为 3.1。对于正数，‘-’字符不会被存储。

NUMERIC 和 FIXED 都是 DECIMAL 的同义词。

咱们能够通过试验来看下 DECIMAI 数据类型。

官网介绍的 DECIMAL 是高精度类型，但当产生截断时，也会呈现四舍五入的景象。所以在设置精度和标度的时候要足够长，不让它产生截断数据的操作。

因为 CPU 不反对对 DECIMAL 的间接计算，所以在 MySQL5.0 及更高版本中，MySQL 服务器本身实现了 DECIMAL 的高精度计算。相对而言，CPU 间接反对原生浮点计算，所以浮点运算显著更快。

因为须要额定的空间和计算开销，所以应该尽量只在对小数进行准确计算时才应用 DECIMAL，例如存储财务数据。但在数据量比拟大的时候，能够思考应用 BIGINT 代替 DECIMAL，将须要存储的货币单位依据小数的位数乘以相应的倍数即可。假如要存储财务数据准确到万分之一分，则能够把所有金额乘以一百万，而后将后果存储在 BIGINT 中，这样能够同时防止浮点存储计算不准确和 DECIMAL 准确计算代价高的问题。

4 . 位类型

位类型用来保留 BIT 值。BIT(M) 示意容许存储 M 位数值，M 范畴从 1 到 64。

如果要特地表明是位值，能够应用 b ’value’ 的形式。value 由 0 和 1 组成。比方，b’111’ 和 b ’10000000’ 示意 7 和 128。

如果为 BIT(M) 调配的值的长度小于 M 位，在值的右边用 0 填充。例如，为 BIT(6) 列调配一个值 b ’101’，实际上，成果与调配 b ’000101’ 雷同。

上面来看一些试验：

5 . 字符串类型

字符串类型是在数据库种存储字符串的数据类型。字符串类型包含 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。

对于字符串列（CHAR、VARCHAR 和 TEXT 类型）的定义，MySQL 以字符单位解释长度标准。对于二进制字符串列（BINARY、VARBINARY 和 BLOB 类型）的定义，MySQL 以字节为单位解释长度标准。

图 4 -2 字符串类型的存储需要（latin1 字符集为例）

5.1 CHAR() 和 VARCHAR

CHAR 和 VARCHAR 是日常应用最多的字符类型。CHAR 和 VARCHAR 类型的申明，其长度指的是要存储的最大字符数，而不是字节数。比方，CHAR(30) 最多可包容 30 个字符。

一个定义为 CHAR 列的长度被固定在创立时申明的长度。长度能够是 0 到 255 之间的任何值。CHAR 存储值时，它们会用空格右填充到指定的长度。当 CHAR 被检索到的值，拖尾的空格被删除，除非启用了 PAD_CHAR_TO_FULL_LENGTH 的 SQL 模式。

VARCHAR(M) 列中的值是可变长度的字符串。长度能够是 0 到 65535 之间的值，只存储字符串理论须要的长度。一个 CARCHAR 列的无效最大长度取决于最大行大小（65535 字节，所有列共享）和所应用的字符集。

与 CHAR 相比，VARCHAR 应用额定的 1~2 字节来存储值的长度（字符串自身的长度）。如果列的最大长度（即 M）小于或等于 255，则应用 1 字节示意，否则就是 2 字节。

如果未启用严格的 SQL 模式，并且为 CHAR 或 VARCHAR 列调配的值超过了列的最大长度，则该值将被截断并生成正告。对于非空格字符的截断，能够应用严格的 SQL 模式从而产生谬误（而不是正告）并阻止该值的插入。

对于 VARCHAR 列，无论应用哪种 SQL 模式，插入前都会截断超出列长度的尾随空格，并生成正告。对于 CHAR 列，无论 SQL 模式如何，都将以静默形式从插入值中截断多余的尾随空格。

VARCHAR 值在存储时不会填充。依据规范 SQL，在存储和检索值时保留尾随空格。

CHAR 和 VARCHAR 跟字符集编码有密切联系。比方，当存储英文字母或数字时，无论 latin1、gbk 或 utf8 字符集，1 个字符占用 1 个字节；当存储汉字时，latin1 字符集不反对存储汉字，gbk 字符集下 1 个汉字占用 2 个字节，utf8 字符集下 1 个汉字占用 3 个字节

表 7 -1 latin1 字符集存储字节表

表 7 -2 gbk 字符集存储字节表

表 7 -3 utf8 字符集存储字节表

注：当字符个数超过定义的长度时仅在不应用严格模式时实用；如果 MySQL 在严格模式在运行，则不会存储超过列长度的值，而是间接报错。

CHAR 和 VARCHAR 的应用场景：

1. VARCHAR 节俭了存储空间，所以对性能也有帮忙。然而，因为行是变长的，在 update 时可能使行变得比原来更长，这就导致须要做额定的工作。如果一个行占用的空间增长，并且在页内没有更多的空间能够存储，在这种状况下，不同的存储引擎解决形式是不一样的。例如，MyISAM 会将行拆成不同的片段存储，InnoDB 则须要决裂页来使行能够放进页内。上面这种状况应用 VARCHAR 是适合的：字符串列的最大长度比均匀长度大很多；列的更新很少，所以碎片不是问题；应用了像 UTF- 8 这样简单的字符集，每个字符都应用不同的字节数进行存储。
2. CHAR 适宜存储很短的字符串，或者所有值都靠近同一个长度。例如，CHAR 非常适合存储明码的 MD5 值，因为这是一个定长的值。对于常常变更的数据，CHAR 也比 VARCHAR 更好，因为定长的 CHAR 类型不容易产生碎片。对于十分短的列，CHAR 比 CARCHAR 在存储空间上也更有效率。例如应用 CHAR(1) 来存储只有 Y 和 N 的值，如果采纳单字节字符集只须要一个字节，然而 VARCHAR(1) 却须要两个字节，因为还有一个记录长度的额定字节。

5.2 BINARY() 和 VARBINARY

BINARY 和 VARBINARY 与 CHAR 和 VARCHAR 类型还是有点相似的，不同的是，BINARY 和 VARBINARY 存储的是二进制的字符串，而非字符型字符串。也就是说，BINARY 和 VARBINARY 没有字符集的概念，对其排序和比拟都是依照二进制的值进行。

BINARY(M) 和 VARBINARY(M) 中的 M 指的是字节长度，而非 CHAR(M) 和 VARCHAR(M) 中的字符长度。对于 BINARY(10)，可存储的字节固定为 10，对于 CHAR(10)，可存储的字节视字符集的状况而定。

如果未启用严格的 SQL 模式，并且为 BINARY 或 VARBINARY 列调配的值超过了列的最大长度，则该值将被截断并生成正告。对于截断的状况，能够应用严格的 SQL 模式从而产生谬误（而不是正告）并阻止该值的插入。

BINARY 存储值时，它们会用 0x00(零字节) 右填充到指定的长度。并且在检索的时候，拖尾的 0x00 填充字节不会被删除。所有字节在比拟中都无效，包含 ORDER BY 和 DISTINCT 操作。0x00 和空格在比拟中是不同的，并且 0x00 会先于空格进行排序。

示例：对于列 BINARY(3)，‘a’在插入的时候会变成‘a \0’，‘a\0’在插入的时候变成‘a\0\0’。两个插入的值在检索的时候放弃不变。

对于 VARBINARY，没有用于填充的插入，也没有剥离任何字节以进行检索。所有字节在比拟中都无效，包含 ORDER BY 和 DISTINCT 操作。0x00 和空格在比拟中是不同的，并且 0x00 会先于空格进行排序。

对于剥离尾随字节或比拟时疏忽它们的状况，如果一列具备要求唯一性的索引，则将仅尾随字节不同的值插入该列会导致反复值谬误。例如，如果表蕴含‘a’，则尝试插入‘a\0’会导致反复键谬误。

如果应用 BINARY 类型存储二进制数据并且要求检索的值与存储的值完全相同，则应认真思考上述填充和剥离的个性。以下示例阐明了 0x00 的右填充如何影响 BINARY 列的值比拟：

如果检索的值必须与存储的值雷同且没有填充，最好应用 VARBINARY 或 BLOB 数据类型来代替。

对于 CHAR 和 VARCHAR 来说，比拟的是字符自身存储的值；对于 BINARY 和 VARBINARY 来说，比拟的是二进制的值。

当须要存储二进制数据，并且心愿 MySQL 应用字节码而不是字符进行比拟时，这些类型是十分有用的。二进制比拟的劣势并不仅仅体现在大小写敏感上。MySQL 比拟 BINARY 字符串时，每次按一个字节，并且依据该字节的数值进行比拟。因而，二进制比拟比字符比较简单很多，所以也就更快。

5.3 BLOB 和 TEXT

BLOB 和 TEXT 都是为了存储很大的数据而设计的字符串数据类型，别离采纳二进制和字符形式存储。

BLOB 有 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB，L 指字节数。存储二进制字符串，没有字符集的概念，对其排序和比拟都是依照二进制的值进行。

TEXT 有 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，L 指字符数。和 VARCHAR 类似，存储字符串，它们具备 BINARY 以外的字符集，并且依据字符集的排序规定对值进行排序和比拟。

BLOB 和 TEXT 的相同点：

1. 两个数据类型都是大对象，用来存储一些超长的数据，比方：新闻、文件等
2. 存储和检索时都辨别大小写
3. 存储和检索时不填充、删除尾部空格
4. 存储时如果数据超过长度会被截断（严格模式下回绝存储并抛出谬误）
5. BLOB 和 TEXT 的不同点：
6. BLOB 存储二进制字符串，没有字符集
7. TEXT 存储字符，有字符集

在大多数状况下，能够将 BLOB 类型的列视为足够大的 VARBINARY 类型的列。同样，也能够将 TEXT 类型的列视为足够大的 VARCHAR 类型的列。然而，BLOB 和 TEXT 在以下几个方面又不同于 VARBINARY 和 VARCHAR：

1. 在 BLOB 和 TEXT 类型的列上创立索引时，必须指定索引前缀的长度；而 VARCHAR 和 VARBINARY 的前缀长度是可选的
2. BLOB 和 TEXT 类型的列不能有默认值
3. 排序时仅应用列的前 max_sort_length 个字节

另外，在一些存储引擎外部，比方 InnoDB 存储引擎，会将大的 VARCHAR 类型字符串（VARCHAR(65535)）主动转换为 TEXT。

5.4 ENUM

ENUM 是按字节存储。

ENUM 是一个字符串对象，其值是从定义时允许值的列表中抉择的值。这些值在表创立时在列定义时指定。具备以下长处：

1. 在列的一组可能值无限的状况下，压缩数据存储。你指定输出值的字符串会自动编码为数字
2. 可读的查问和输入。这些数字在查问后果中会转换成相应的字符串

ENUM 对象的大小由不同的枚举值的数目确定。枚举用一个字节时，最大能枚举 255 个元素；枚举用两个字节时，能枚举 256 到 65535 个元素。

因而，MySQL 在存储枚举时十分紧凑，会依据列表值的数量压缩到一个或者两个字节中。MySQL 在外部会将每个值在列表中的地位保留为整数，并且在表的.frm 文件中保留“数字 - 字符串”映射关系的“查找表”。

咱们来看个例子：

创立一张表 enum_test，含有一个 ENUM 枚举列 e，并插入三行数据。

这三行数据理论存储为整数，而不是字符串。能够通过在数字上下文环境检索看到这个双重属性：

因而，如果应用数字作为 ENUM 枚举常量，这种双重性很容易导致凌乱。例如，ENUM(‘1’,’2’,’3’)。倡议尽量避免这么做。

另外一个比拟特地的中央是，枚举字段是依照外部存储的整数而不是定义的字符串进行排序的：

一种绕过这种限度的形式是依照须要的程序来定义枚举列。另外也能够在查问中应用 FIELD() 函数显示地指定排序程序，但这会导致 MySQL 无奈利用索引打消排序。

如果在定义时就是依照字母的程序，就没有必要这么做了。

枚举最不好的中央是，字符串列表是固定的，增加或删除字符串必须应用 ALTER TABLE。因而，对于一系列将来可能会扭转的字符串，应用枚举不是一个好主见，除非能承受只在列表开端增加元素。这样就能够不必重建整个表来实现批改。

当然枚举也有益处。比方上述图中这种表，如果将 100 万行’apple’插入此表须要 100 万字节的存储空间，而如果将字符串’apple’存储在 VARCHAR 列中则须要 500 万字节的存储空间。能够通过 SHOW TABLE STATUS 命令输入后果中的 Data_length 列的值，来察看表的放大水平。

看另一个例子。

当插入的值不合乎 ENUM 列定义时，会间接报错。注：这里小写的 f 插入会报错是因为校对规定是 utf8_bin，此校对规定会辨别大小写。

ENUM 有以下特点：

1. 须要提前定义取值范畴
2. 创立表时，表定义中 ENUM 成员值的开端空格会被主动删除
3. 检索到时，ENUM 将应用列定义中应用的字母大小写显示存储在列中的值。请留神，ENUM 能够为列调配一个字符集和排序规定。对于二进制或辨别大小写的归类，在为列调配值时思考字母大小写。
4. 如果在 ENUM 中插入有效值（即，在定义值列表中不存在的值），则会插入空字符串，而不是将其作为非凡谬误值。此字符串能够通过将数字值设为 0 来与失常的空字符串辨别开。如果启用了严格的 SQL 模式，则尝试插入有效的 ENUM 值将导致谬误。

1. 如果 ENUM 申明某列容许 NULL，则该 NULL 值为该列的有效值，默认值为 NULL。如果 ENUM 申明了列 NOT NULL，则其默认值是允许值列表的第一个元素。

5.5 SET

SET 是按字节存储。

SET 是具备零个或多个值的字符串对象，每个值都必须从创立表时指定的允许值列表中抉择。例如，指定为的列 SET(‘one’, ‘two’) NOT NULL 能够具备以下任何值：

‘’

‘one’

‘two’

‘one,two’

一个 SET 列最多可蕴含 64 个不同的成员。

SET 有以下特点：

1. 须要提前定义取值范畴
2. 创立表时，表定义中 SET 成员值的开端空格会被主动删除
3. 检索到后，SET 将应用列定义中应用的字母大小写来显示存储在列中的值。请留神，SET 能够为列调配一个字符集和排序规定。对于二进制或辨别大小写的归类，在为列调配值时思考字母大小写。
4. MySQL 以数字来存储 SET 值。存储值的位置对于第一个 SET 成员。如果在数字上下文中检索 SET 值，则检索到的值具备与组成列值的汇合成员绝对应的位汇合。能够通过如下形式来检索值：

mysql> SELECT set_col + 0 FROM tbl_name;**

对于指定的列 SET(‘a’,’b’,’c’,’d’)，成员具备以下十进制和二进制值：

图 4-3

一些 SET 的试验：