[MySQL] 数据类型与性能

MySQL支持的数据类型非常多，选择正确的数据类型对性能至关重要，本文谈谈如何正确选择MySQL的数据类型。

整数

有以下几种整数：TINYINT, SMAILLINT, MEDIUMINT, INT, BIGINT，分别使用8, 16, 24, 32, 64位存储空间。它们都有一个可选的UNSIGNED属性，表示不允许负数。

你可以为整数类型指定宽度，如INT(13)，但对大多数应用来说是没有意义的，对于存储计算来说，INT(1)和INT(18)是相同的。

实数

实数是带有小数部分的数字，MySQL既支持精确类型，也支持不精确类型。

FLOAT和DOUBLE支持使用标准的浮点计算（即不精确类型），FLOAT使用4个字节，DOUBLE使用8个字节，有多种方法可以指定浮点列的精度，但这些精度都是非标准的，因此建议只指定数据类型，不指定精度。

DECIMAL用于存储精确的小数，它还可以指定小数点前后所允许的最大位数，如DECIMAL(20,2)表示小数点后面存储2个数字，小数点前面存储18个数字。很显然，DECIMAL比浮点数占用更多的空间，消耗更多的CPU，这也是为了精确表示所付出的代价。

因此，只有在必须使用精确小数的情景下（如财务）才使用DECIMAL，否则尽量使用浮点数。

字符串

MySQL支持的字符串类型比较复杂，且每个字符串列可以定义自己的字符集和排序规则。

VARCHAR：用于存储可变长字符串。它需要使用1或2个额外字节记录字符串长度：如果列最大长度<=255，则使用1个字节，否则使用2个字节，如varchar(100)使用1个字节，varchar(1000)使用2个字节。下面这些情况下使用VARCHAR更合适：字符串列的最大长度比平均长度大很多，且列的update很少，因为update操作可能导致行变长，可能会导致空间碎片。注意：对于VARCHAR，MySQL在存储时会保留末尾空格。

CHAR：用于存储定长字符串。注意：对于CHAR，MySQL在存储时会删除末尾空格。

与CHAR和VARCHAR相似的类型还有BINARY和VARBINARY，但它们存储的是字节码，因此他们没有字符集和排序规则的概念，严格区分大小写，比较大小时，按字节依次比较，因此性能更好。

TEXT和BLOB是为了存储更大的字符串而设计的，分别采用字符和二进制方式存储。

TEXT有：TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT，其中TEXT和SMAILLTEXT是同义词。

BLOB有：TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB，其中BLOB和SMALLBLOB是同义词。

与其它类型不同的是，MySQL把每个TEXT和BLOB当做一个独立的对象处理，当它们过大时，InnoDB会在专门的“外部”存储区域进行存储，行内需要1~4个字节存储指针。

日期和时间

DATETIME：能保存大范围的值，从1001年到9999年，精度为秒，它把时间和日期封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储。

TIMESTAMP：保存了从1970年1月1日午夜（格林威治标准时间）以来经历的秒数（精度也为秒），只使用4个字节，只能表示从1970年到2038年，且它的显示依赖于时区。

注意：MySQL目前还没有提供存储比秒更小粒度的日期和时间类型。

数据类型选择准则

MySQL数据类型的选择应遵循以下准则：

1）更小的通常更好。

一般情况下，应该尽量使用可以正确存储数据的最小数据类型，例如：如果只需要存0~200，则tinyint unsigned最好。

2）简单更好

简单数据类型操作通过需要更少的CPU周期，例如：整型比字符操作性能更好。因此：应该使用MySQL内建的日期和时间数据类型存储日期和时间，而不要用字符串代替；应该用无符号整型存储IP地址。

3）尽量避免索引列上的NULL

MySQL在建表时，默认情况下列的属性是允许NULL的，其实最好指定为NOT NULL，除非真的需要存储NULL值，因为可为NULL的列使得索引、索引统计和值比较更复杂，也使用更多的存储空间。特别是计划在列上建索引，应尽量避免设计成可为NULL的列。