MySQL 数据库设计 | 如何选择 MySQL 数据类型

在我的博客阅读本文会有更好的阅读体验哦！博客： www.lxiaocode.com
更多内容可以到我博客浏览： www.lxiaocode.com
B站算法动画： https://space.bilibili.com/22392939

《高性能 MySQL（第三版）》学习笔记

• 数字 - 整数 / 无符号类型 / 浮点数 / 高精度小数 / 金额存储
• 字符串 - 定长字符串 / 不定长字符串 / 长字符串 / 枚举
• 日期时间 - DATETIME / 时间戳 / 细粒度日期时间存储
• 数据类型选择 - 数据类型选择原则 / 标识符数据类型

一、MySQL 数据类型

1. 整数

在 MySQL 中存储整数可以使用 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 这几种整数类型，它们分别使用 8，16，24，32，64 位存储空间。选择整数类型只决定了 MySQL 如何存储数据，在整数计算中一般使用的是 64 位的 BIGINT 整数（一些聚合函数除外）。

• 整数类型有可选的 UNSIGNED 属性，表示无符号（不允许负值）。由于不需要存储负值，所以它会使正数的上限提高一倍。无符号和有符号类型使用相同的存储空间，具有相同的性能。
• 为整数指定宽度大多数是没有意义的，它不会限制值的合法范围，只是规定了 MySQL 的一些交互工具用来显示字符的个数。

2. 实数

在 MySQL 中支持浮点运算的 FLOAT、DOUBLE 类型，也支持进行高精度运算的 DECIMAL 类型。浮点类型在存储相同范围的值时，通常比 DECIMAL 使用更好空间。FLOAT 使用 4 个字节，DOUBLE 使用 8 个字节。和整型一样，选择浮点类型只决定了存储类型，在计算时使用 DOUBLE 类型作为内部浮点运算类型。

• 由于 CPU 不支持对 DECIMAL 直接进行计算，所以 MySQL 自身实现了 DECIMAL 的高精度运算。相对而言，CUP 支持直接的浮点运算，所以浮点运算明显更快。
• 因为需要额外的空间和计算开销，所以只在必要的精确计算时使用 DECIMAL 类型（例如：财务数据）。也可以使用 BIGINT 存储最小货币单位，避免使用不精确的浮点运算和高代价的 DECIMAL 精确运算。

3. 字符串

3.1 普通字符串

VARCHAR 和 CHAR 是两种最主要的字符串类型。VARCHAR 用于存储不定长字符串，因为它仅使用必要的空间，所以比定长字符串更节省空间。CHAR 用于存储定长字符串，总会根据定义的字符串长度分配足够的空间。

VARCHAR：

• 在不定长字符串中，需要使用额外空间存储字符串长度。当长度小于等于 255 字节则使用 1 个字节表示长度，否则使用 2 个字节。
• 由于值是不定长的，在 UPDATE 时可能导致需要的空间变大，并且没有更多的空间可以存储。导致了页分裂，产生碎片。
• MySQL 会保留 VARCHAR 字符串末尾的空格。

CHAR：

• 定长字符串，根据定义的字符串长度分配足够的空间。
• CHAR 在存储时会删除末尾所有空格，在需要时使用空格进行填充以方便比较。

**VARCHAR 使用场景：**在字符串的最大长度比平均长度大很多的时候、更新频率低的列、每个字符使用不同字节存储的复杂字符集（例如：UTF-8）。

**CHAR 使用场景：**很短的字符串、长度非常接近的字符串、更新频率高的列。

3.2 二进制字符串

BINARY 和 VARBINARY 类型用于存储二进制字符串，类似于 CHAR 和 VARCHAR 类型。

3.3 长字符串

TEXT 和 BLOB 是为存储数据很大的数据设计的字符串类型，分别存储字符和二进制。与其他类型不同，MySQL 把每个 TEXT 和 BLOB 值当作一个独立的对象处理。当存储的值太大时，InnoDB 会使用专门的“外部”存储区域来存储，此时每个值内存储一个1～4个字节的指针，然后外部存储区域存储实际的值。

• TEXT 数据类型家族：TINYTEXT、SMALLTEXT=TEXT、MEDIUMTEXT、LONGTEXT。
• BLOB 数据类型家族：TINYBLOB、SMALLBLOB=BLOB、MEDIUMBLOB、LONGBLOB。
• 在排序时只对每列最前 max_sort_length 字节进行排序，不会对整个字符串排序。

因为 Memory 引擎不支持 BLOB 和 TEXT 类型，所以查询时使用临时表时，需要使用 MyISAM 磁盘临时表。这会导致严重的性能开销，最好的解决方案就是避免使用 BLOB 和 TEXT。

3.4 枚举

可以使用枚举存储字符串，MySQL 存储枚举时非常紧凑。在表 .frm 文件中保存“数字 - 字符串”映射关系，在列中实际存储对应的整数。枚举类型排序是按照内部存储的整数进行排序的。

4. 日期时间

MySQL 能存储的最小时间粒度为秒（MariaDB 支持微秒级别的时间类型）。如果需要粒度更小的日期时间类型，可以使用 BIGINT 存储微秒级的时间戳，或者使用 MariaDB 代替 MySQL，它支持更细粒度的日期时间。

DATETIME：

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

TIMESTAMP：

• 和 UNIX 时间戳一样，保存从1970年1月1日以来的秒数。保存范围从1970年到2038年。
• 时间戳依赖于时区，使用4个字节存储。

通常应该尽量使用 TIMESTAMP，因为它的空间效率更高。

二、数据类型设计指导

1. 数据类型选择原则

• **更小的通常更好：**更小的数据类型通常更快。占用更少的磁盘、内存和CPU缓存；CPU 处理周期更少。

• **简单就好：**简单的数据类型操作代价更低。

• **尽量避免 NULL：**包含 NULL 的列，使索引、索引统计、值比较更复杂更难优化。需要更多的存储空间。

  InnoDB 使用单独的 bit 存储 NULL 值，所以对于稀疏数据（很多值为 NULL ，只有少数是非 NULL 值）有很好的空间效率。

2. 标识符数据类型建议

**标识符：**用于标识数据的列，常用于关联操作、外键、条件的列。

在考虑标识符列的数据类型时，不仅仅需要考虑数据类型，还要考虑这种数据类型是如何计算和比较的。

• 确保标识符在关联的表中，对应的列使用一样的类型，混用不同数据类型可能会导致性能问题。

• 在满足值范围的需求，并预留了未来增长的空间的前提下，应选择最小的数据类型

• **整数：**整数是最好的标识列类型，因为它很快并且可以自增。

• **字符串：**尽量避免使用字符串作为标识列，因为它很消耗空间，比整数类型慢。

• **随机字符串：**随机生成的字符串（例如：UUID），会导致 INSERT 和 SELECT 语句变得很慢。

  • 因为插入时会被随机写到索引的不同位置，导致页分裂、产生碎片、磁盘随机访问。
  • 由于逻辑相邻的行会被写到磁盘和内存的不同位置，导致 SELECT 语句很慢。

  如果存储UUID值，应该移除“-”符号，或使用UNHEX()函数转换UUID值为16字节的数字，存储在BINARY(16)的列中。检索时，使用HEX()函数来格式化为十六进制的格式