《高性能 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()函数来格式化为十六进制的格式

评论