关于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的试验：