【阅读笔记】MySQL数据库存储类型选择

摘自：《高性能MySQL》第四版

原则

更小的通常更好
一般来说，尽量使用能够正确存储和表示数据的最小数据类型。更小的数据类型通常更快，因为它们占用的磁盘、内存和CPU缓存的空间更少，并且处理时需要的CPU周期也更少。

简单为好
简单数据类型的操作通常需要更少的CPU周期。例如，整型数据比字符型数据的比较操作代价更低，因为字符集和排序规则（collation）使字符型数据的比较更复杂

尽量避免存储NULL
即使应用程序本身并不需要存储NULL（缺失值），很多表也包含可为NULL的列，这是因为NULL可以是列的默认属性。通常情况下最好指定列为NOT NULL，除非明确需要存储NULL值。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理

 tips

DATETIME和TIMESAMP列可以存储相同类型的数据：时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，还会根据时区变化，而且具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。

MySQL为了兼容性支持很多别名，例如，INTEGER（映射到INT）、BOOL（映射到TINYINT）和NUMERIC（映射到DECIMAL）。

类型介绍

整数类型
有两种类型的数字：整数（whole number）和实数（real number，带有小数部分的数字）。

如果存储整数，可以使用这几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT。它们分别使用8、16、24、32和64位存储空间。可以存储的值的范围从-2（N-1）到2（N-1）-1，其中N是存储空间的位数。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。

例如，TINYINT UNSIGNED可以存储的值的范围是0～255，而TINYINT的值的存储范围是-128～127。

有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据数据实际范围选择合适的类型。
你的选择决定了MySQL在内存和磁盘中保存数据的方式。然而，整数计算通常使用64位的BIGINT整数。（一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算。）
MySQL可以为整数类型指定宽度，例如，INT（11），这对大多数应用毫无意义：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如，MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT（1）和INT（20）是相同的。

 

 

实数类型
实数是带有小数部分的数字。然而，它们不仅适用于带小数的数字，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。如果你需要知道浮点运算是怎么计算的，则需要研究平台的浮点数的具体实现方式。
有两种方式可以指定浮点列所需的精度，这可能会导致MySQL以静默方式选择不同的数据类型，或者在存储值时对其进行近似处理。这些精度说明符是非标准的，因此我们建议只指定数据类型，不指定精度。
浮点类型通常比DECIMAL使用更少的空间来存储相同范围的值。FLOAT列使用4字节的存储空间。DOUBLE占用8字节，比FLOAT具有更高的精度和更大的值范围。与整数类型一样，你只能选择存储类型；MySQL会使用DOUBLE进行浮点类型的内部计算。
由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL——例如，存储财务数据。但在一些大容量的场景，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

 

字符串类型

VARCHAR和CHAR类型
VARCHAR和CHAR是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是如何存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。下面的描述假设使用的存储引擎是InnoDB。如果不是InnoDB，请参考所使用的存储引擎的文档。

VARCHAR
VARCHAR用于存储可变长度的字符串，是最常见的字符串数据类型。它比固定长度的类型更节省空间，因为它仅使用必要的空间（即，更少的空间用于存储更短的值）。
VARCHAR需要额外使用1或2字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1字节表示，否则使用2字节。假设采用latin1字符集，一个VARCHAR（10）的列需要11字节的存储空间。VARCHAR（1000）的列则需要1002个字节，因为需要2字节存储长度信息。
VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变长度的，在更新时可能会增长，这会导致额外的工作。如果行的增长使得原位置无法容纳更多内容，则处理行为取决于所使用的存储引擎。例如，InnoDB可能需要分割页面来容纳行。其他一些存储引擎也许不在原数据位置更新数据。
下面这些情况使用VARCHAR是合适的：字符串列的最大长度远大于平均长度；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
InnoDB更为复杂，它可以将过长的VARCHAR值存储为BLOB。

CHAR
CHAR是固定长度的：MySQL总是为定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL删除所有尾随空格。如果需要进行比较，值会用空格填充。
CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们的长度总是相同的。对于经常修改的数据，CHAR也比VARCHAR更好，因为固定长度的行不容易出现碎片。对于非常短的列，CHAR也比VARCHAR更高效；设计为只保存Y和N的值的CHAR（1）在单字节字符集中只使用1字节，但VARCHAR（1）需要2字节，因为还有一个记录长度的额外字节。

 

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串与常规字符串非常相似，但它们存储的是字节而不是字符。填充也不同：MySQL填充BINANRY用的是\0（零字节）而不是空格，并且在检索时不会去除填充值。
当需要存储二进制数据，并且希望MySQL将值作为字节而不是字符进行比较时，这些类型非常有用。字节比较的优势不仅仅是大小写不敏感。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单得多，因此速度更快。

 

BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
实际上，它们分别属于两组不同的数据类型家族：

字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT；

二进制类型是TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。
与其他数据类型不同，MySQL把每个BLOB和TEXT值当作一个具有自己标识的对象来处理。存储引擎通常会专门存储它们。当BLOB和TEXT值太大时，InnoDB会使用独立的“外部”存储区域，此时每个值在行内需要1～4字节的存储空间，然后在外部存储区域需要足够的空间来存储实际的值。
BLOB和TEXT家族之间的唯一区别是，BLOB类型存储的是二进制数据，没有排序规则或字符集，但TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列的排序与其他类型不同：它只对这些列的最前max_sort_length字节而不是整个字符串做排序。如果只需要按前面少数几个字符排序，可以减小max_sort_length服务器变量的值。
MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序。

tips:

 

如果可以避免的话，不要在数据库中存储像图像这样的数据。相反，应该将它们写入单独的对象数据存储，并使用该表来跟踪图像的位置或文件名。

 

 

日期和时间类型

MySQL中有很多数据类型用以支持各种各样的日期和时间值，比如YEAR和DATE。MySQL可以存储的最小时间粒度是微秒

 

DATETIME
这种类型可以保存大范围的数值，从1000年到9999年，精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。这需要8字节的存储空间。
默认情况下，MySQL以可排序、无歧义的格式显示DATETIME值，例如，2008-01-16 22：37：08。这是ANSI表示日期和时间的标准方式。

TIMESTAMP
顾名思义，TIMESTAMP类型存储自1970年1月1日格林尼治标准时间（GMT）午夜以来经过的秒数——与UNIX时间戳相同。TIMESTAMP只使用4字节的存储空间，所以它的范围比DATETIME小得多：只能表示从1970年到2038年1月19日。

MySQL提供FROM_UNIXTIME()函数来将UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为UNIX时间戳。
时间戳显示的值依赖于时区。MySQL服务器、操作系统和客户端连接都有时区设置。
因此，存储值0的TIMESTAMP在美国东部标准时间（EST）中显示为1969-12-31 19：00：00，与格林尼治标准时间（GMT）差5小时。有必要强调一下这个区别：如果存储或访问多个时区的数据，TIMESTAMP和DATETIME的行为将很不一样。

前者保留与所使用时区相关的值，而后者保留日期和时间的文本表示。
TIMESTAMP还有DATETIME没有的特殊属性。默认情况下，当插入一行记录时没有指定第一个TIMESTAMP列的值，MySQL会将该列的值设置为当前时间。当更新一行记录时没有指定第一个TIMESTAMP列的值，MySQL默认也会将该列的值更新为当前时间。可以为任何TIMESTAMP列配置插入和更新行为。最后，TIMESTAMP列在默认情况下为NOT NULL，这也和其他的数据类型不一样。