《高性能|高性能 MySQL（五）（设计表结构时，如何选择数据类型会更高效（）） mysql|数据库|database|MySQL》读书

文章图片

??个人主页：水滴V2
支持水滴：点赞、收藏?、留言

大家好，我是水滴~~
MySQL 支持的数据类型有很多，在设计表结构时，选择正确的数据类型可以获得更高的性能。如果你还不知道如何选择，那么希望这篇文章能帮到你。
选择数据类型的原则在选择数据类型时，一般要遵循下面三个原则。
选择更小的

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。

更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。前提条件时，选择的数据类型能够在存储值的范围内。
例如，在为“年龄”字段选择类型时，使用tinyint unsigned类型就能满足人类的需求（tinyint只占一个字节，其无符号的数据范围是：0~255）。
选择简单的

尽量选择简单的数据类型，因为它们的操作通常需要更少的 CPU 周期。

例如，字符型之间的比较，比整形之间的比较更复杂，这会影响到排序的效率。
尽量避免 NULL

通常情况下，应尽量指定列为 NOT NULL，特别是索引列，除非真的需要存储 NULL 值。

如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要特殊处理。
当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节。所以，如果计划在列上建索引，应尽量避免设计成可为 NULL 的列。
常用数据类型下面列举一些常用数据类型，只有了解了它们的特性，才能帮助我们做正确的选择。
整数类型
MySQL 中有 5 种整数类型：tinyint、smallint、mediumint、int和bigint，它们分别占用 8、16、24、32 和 64 位存储空间。它们可以存储值的范围可以用下面公式表示，其中 N 是存储空间的位数：
? 2 ( N ? 1 ) 到 2 ( N ? 1 ) ? 1 -2^{(N-1)}到2^{(N-1)}-1 ?2(N?1)到2(N?1)?1
整数类型可以使用unsigned属性，表示为无符号整形（不允许负值），这大致可以使正数的上限提高一倍。无符号存储值的范围可以使用下面公示表示：
0 到 2 N ? 1 0到2^{N}-1 0到2N?1
下表为每个整数类型的存储范围：

《高性能|高性能 MySQL（五）（设计表结构时，如何选择数据类型会更高效（））

文章图片

有符号与无符号类型的存储空间和性能是相同的，所以可以根据实际情况选择合适的类型。对整数类型指定宽度是没有意义的，不会影响存储范围。例如，int(1)和int(10)的存储和计算是相同的。

如果你能大体知道数据的范围，可以根据实际情况选择数据类型；
bigint适用于数据量巨大的场景，比如自增主键；
当你不知道该选择哪个类型，就使用int类型。

实数类型
实数是带有小数部分的数字。MySQL 有 3 种实数类型：float、double和decimal。其中float和double是浮点类型，分别占用 32 和 64 位存储空间；decimal支持高精度计算，存储空间依据列的设置，最高可设置 65 位。
值得注意的是，对于浮点型使用unsigned属性，只是不可使用负数，并没有增加正数的存储范围，所以意义不大。
下表为每个实数类型的存储范围：

文章图片

float和double是标准的浮点类型，CPU直接支持原生浮点计算，所以浮点运算速度会很快。但其最大的缺点是精度问题，造成数据偏差。
【《高性能|高性能 MySQL（五）（设计表结构时，如何选择数据类型会更高效（））】dicimal类型使用二进制格式存储，CPU 不支持对该类型的直接运算，它的高精度计算是由 MySQL 服务器自身实现，对小数计算精确。由于dicimal类型需要额外的空间和计算开销，所以相对会慢一些。

当计算的数据量大，并且对小数的精度要求没那么高时，可以使用float和double。
如果对小数进行精确计算时，比如财务数据，请使用dicimal类型。实际在业务中，基本上也都是使用dicimal类型。

字符串类型
MySQL 支持多种字符串类型，我们分别来介绍一下吧.
CHAR 和 VARCHAR 类型 char和varchar是最主要的两种字符串类型。
char用于存储定长的字符串，适合存储很短的字符串，或者所有值都接近同一长度，比如 MD5 值；varchar用于存储可变长字符串，应用最为广泛。
对于经常变化的数据，char比varchar更好，因为定长的char类型不容易产生磁盘碎片；而varchar在变更时，如果新数据比原来的更长，这就导致需要做一些额外的工作，来找到适合的存储空间。

文章图片

varchar在存储时，只使用必要的空间。也就是说，越短的字符串使用的存储空间也越少。
但也不能盲目的设置更大的长度，更长的列消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值。所以最好还是按需分配。

TEXT 类型文本类型用于存储大的字符集，MySQL 有4种文本类型：tinytext、text、mediumtext和longtext，它们可存储的最大长度不同，可以根据实际情况选择。
文本类型也可以进行排序，但它只对每个列的最前max_sort_length字节做排序。

文章图片

BINARY 和 VARBINARY 类型 binary和varbinary类似于char和varchar，不同的是它们存储的是二进制字符串。
二进制字符串跟常规字符串非常相似，但二进制字符串存储的是字节码而不是字符。

MySQL 比较 binary字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较会比字符比较简单很多，所以速度更快。

BLOB 类型 blob是一个二进制大对象，它有4种类型：tinyblob、blob、mediumblob和longblob，同样它们可存储的最大长度不同，可以根据实际情况选择。
blob类型与text类型非常相似，只不过blob存储的是二进制，而非字符。

文章图片

日期和时间类型
MySQL 有多种类型来保存日期和时间：year、date、datetime和timestamp。
其中year用于存储年份，date用户存储日期，也就是：年-月-日。
datetime和timestamp两个类型最常用，都是用于存储时间，并且它们的精度都是秒，下面看下它们的区别：

datetime能保存大范围的值，从1001年到9999年。它把日期和时间封装成格式为YYYYMMDDHHMMSS的整数，与时区无关，使用8个字节的存储空间。
timestam存储的是时间戳，即从1970年1月1日午夜以来的秒数，它和 UNIX 时间戳相同。它只使用4个字节的存储空间，所以它的范围比datetime小，只能存储从1970年到2038年的数据。并且timestamp显示的值依赖于时区。