这是《高性能 MySQL（第三版）》第四章《Schema 与数据类型优化》的读书笔记。

1. 选择优化的数据类型

数据类型的选择原则：

越小越好：选择满足需求的最小类型。注意，增大数据类型的范围是耗时操作，尽量避免。简单：整型比字符操作代价更低。尽量避免 NULL 列：列最好指定为 NOT NULL，除非确实可以是 NULL。如果查询中包含可为 NULL 的列，会使索引、索引统计和值比较都更复杂。可为 NULL 的列占用更多存储空间，在 MySQL 里也需要特殊处理。如果可为 NULL 的列被索引，每个索引记录需要一个额外的字节，在 MyISAM 里甚至可能导致固定大小的索引变成可变大小的索引。 把 NULL 列改为 NOT NULL 列带来的性能提升比较小，但是用于索引的列最好是 NOT NULL。另外，InnoDB 使用单独的位（bit）存储 NULL 值，包含很多 NULL 值的稀疏数据的空间效率比较高。

首先选择大类型：数字、字符串、时间、布尔值等。然后选择具体类型。

1.1 整数类型

可用的整型数据有：

TINYINT：8 bit。SMALLINT：16 bit。MEDIUMINT：24 bit。INT：32 bit。BIGINT：64 bit。

所有的整数类型都支持 UNSIGNED 属性，表示无符号数据，例如 TINYINT UNSIGNED。有符号和无符号整型数据存储空间一样，性能一样。

MySQL 中可以为整数类型指定宽度，例如 INT(11)，这个宽度只是规定了 MySQL 交互工具用来显示字符的个数，不影响存储和计算。

1.2 实数类型

可用的实数数据有：

浮点类型： FLOAT：32 bit。不精确类型，支持浮点运算。DOUBLE：64 bit。不精确类型，支持浮点运算。DECIMAL：最多允许 65 个数字，例如 DECIMAL(33,32)，DECIMAL(65,0)。可以存储精确的小数，也可以存储比 BIGINT 还大的整数，支持精确计算。因为 CPU 不支持对 DECIMAL 的直接计算，所以 MySQL 服务器自身实现了 DECIMAL 的高精度计算。

MySQL 支持精确类型（DECIMAL），也支持不精确类型（浮点类型，即 FLOAT 和 DOUBLE）。

FLOAT、DOUBLE 和 DECIMAL 类型都可以指定精度。例如 DOUBLE(12,3) 表示最多 9 位整数、3 位小数。DECIMAL(18,9) 表示小数点两边各存储 9 个数字，一共使用 9 个字节（小数点占一个字节）。

DECIMAL 需要的存储空间大，计算开销高，如果数据量很大，可以考虑用 BIGINT 替代 DECIMAL。例如金额需要保证 0.001 的精度，则可以将所有金额乘以 1000 后取整存入 BIGINT 类型的字段，从而避免浮点数计算不准确和 DECIMAL 精确计算代价高的问题。

1.3 字符串类型

MySQL 中每个字符串列可以定义自己的字符集和排列规则（校对规则，collation）。

1.3.1 VARCHAR 和 CHAR 类型

MySQL 中最重要的字符串类型，其在内存和磁盘中的存储方式跟存储引擎相关。这里假设用的是 InnoDB 或 MyISAM 引擎。

VARCHAR：变长字符串，节省空间（但是如果用 ROW_FORMAT = FIXED 创建表，每行都会定长存储，浪费空间）。需要 1 个或 2 个额外字节记录字符串长度（如果列的最大长度小于等于 255 字节则只需要 1 个字节，否则需要 2 个字节）。例如，对于 latin1 字符集，一个 VARCHAR(10) 的列需要 11 个字节的存储空间，VARCHAR(1000) 的列需要 1002 个字节的存储空间。 在执行 UPDATA 操作时，如果需要增大行的长度，需要额外工作。如果磁盘当前页的空间不够，不同存储引擎的处理方式不同。MyISAM 会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。InnoDB 会把过长的 VARCHAR 存储为 BLOB。CHAR：定长字符串，分配固定空间，如果字符串长度小于存储空间，则剩余空间填充空格。对于单字节字符集，CHAR(1) 只需要一个字节，而 VARCHAR(1) 则需要两个字节（包括记录长度的一个字节）。VARBINARY：二进制变长字符串，存储字节码而不是字符。BINARY：二进制定长字符串，存储字节码而不是字符，使用 \0（零字节）填充而不是空格，且检索时不会去掉填充值。

适合使用 VARCHAR 的场景：

字符串列的最大长度比平均长度大得多列很少更新，所以碎片不是问题使用 UTF-8 字符集，每个字符用不同的字节数进行存储

适合使用 CHAR 的场景：

短字符串定长字符串，例如密码的 HASH 值经常变更的字符串，不易产生碎片

注意：CHAR 类型的字符串在存储时，会自动截断字符串末尾的空格：

MariaDB [foo]> CREATE TABLE char_test(char_col CHAR(10)); Query OK, 0 rows affected (0.04 sec) MariaDB [foo]> INSERT INTO char_test(char_col) VALUES -> ('string1'), (' string2'), ('string3 '); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [foo]> SELECT CONCAT("'", char_col, "'") FROM char_test; +----------------------------+ | CONCAT("'", char_col, "'") | +----------------------------+ | 'string1' | | ' string2' | | 'string3' | +----------------------------+ 3 rows in set (0.00 sec)

VARCHAR 类型的字符串在存储时，则不会截断字符串末尾的空格：

MariaDB [foo]> CREATE TABLE varchar_test(varchar_col VARCHAR(10)); Query OK, 0 rows affected (0.04 sec) MariaDB [foo]> INSERT INTO varchar_test VALUES -> ('string1'), (' string2'), ('string3 '); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [foo]> SELECT CONCAT("'", char_col, "'") FROM varchar_test; +----------------------------+ | CONCAT("'", char_col, "'") | +----------------------------+ | 'string1' | | ' string2' | | 'string3 ' | +----------------------------+ 3 rows in set (0.00 sec)

1.3.2 BLOB 和 TEXT 类型

BLOB：二进制方式存储大数据的字符串类型，没有字符集和排序规则。具体的类型有：TINYBLOB, SMALLBLOB, BLOB, MIDIUMBLOB, LONGBLOB TEXT：字符方式存储大数据的字符串类型，有字符集和排序规则。具体的类型有：TINYTEXT, SMALLTEXT, TEXT, MIDIUMTEXT, LONGTEXT

在 MySQL 中，每个 BLOB 和 TEXT 类型的值都是独立对象。当值太大时，InnoDB 还会使用专门的外部存储区域来存储，此时行内只需 1-4 个字节存储指针。

1.3.3 ENUM 枚举类型代替字符串

使用枚举类型可以节省存储空间。MySQL 内部将每个枚举类型的字段存储为整数，并在表的 .frm 文件中保存“数字-字符串”映射关系的“查找表”。

MariaDB [foo]> CREATE TABLE enum_test( e ENUM('dog', 'fish', 'apple') NOT NULL); Query OK, 0 rows affected (0.17 sec) MariaDB [foo]> desc enum_test; +-------+----------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------------+------+-----+---------+-------+ | e | enum('dog','fish','apple') | NO | | NULL | | +-------+----------------------------+------+-----+---------+-------+ 1 row in set (0.01 sec) MariaDB [foo]> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

MySQL 的枚举字段存储的实际上是数字而不是字符串：

MariaDB [foo]> SELECT e + 0 FROM enum_test; <-------实际存储整数值 +-------+ | e + 0 | +-------+ | 2 | | 1 | | 3 | +-------+ 3 rows in set (0.00 sec) MariaDB [foo]> SELECT e FROM enum_test; <-------取数据时自动转为字符串 +-------+ | e | +-------+ | fish | | dog | | apple | +-------+ 3 rows in set (0.00 sec)

枚举字段排序时，默认按照内部存储的整数进行排序。可以使用 FIELD() 函数显式指定排序顺序，但会导致 MySQL 无法利用索引消除排序：

MariaDB [foo]> SELECT e FROM enum_test ORDER BY e; <-------排序时，根据实际存储的整数值排序 +-------+ | e | +-------+ | dog | | fish | | apple | +-------+ 6 rows in set (0.06 sec) MariaDB [foo]> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish'); <-------自己制定排序方式 +-------+ | e | +-------+ | apple | | dog | | fish | +-------+ 6 rows in set (0.00 sec)

枚举字段如果需要增删可用的枚举值，则需要使用 ALTER TABLE。如果枚举类型添加字符串，MySQL 会自动修改字段中保存的整数值以匹配字符串。为了不重建整个表，最好在列表末尾新增加字符串：

MariaDB [foo]> ALTER TABLE enum_test MODIFY e ENUM('dog', 'fish', 'apple', 'banana'); Query OK, 6 rows affected (0.08 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [foo]> DESC enum_test; +-------+-------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------------------------------+------+-----+---------+-------+ | e | enum('dog','fish','apple','banana') | YES | | NULL | | +-------+-------------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [foo]> SELECT e + 0 FROM enum_test; <-------存储的整数值不受影响 +-------+ | e + 0 | +-------+ | 2 | | 1 | | 3 | +-------+ 3 rows in set (0.00 sec) MariaDB [foo]> ALTER TABLE enum_test MODIFY e ENUM('test', 'dog', 'fish', 'apple', 'banana'); Query OK, 6 rows affected (0.04 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [foo]> SELECT e + 0 FROM enum_test; <-------存储的整数值受影响 +-------+ | e + 0 | +-------+ | 3 | | 2 | | 4 | +-------+ 6 rows in set (0.00 sec) MariaDB [foo]> SELECT e FROM enum_test; <-------实际读出来的字符串不会变化 +-------+ | e | +-------+ | fish | | dog | | apple | +-------+ 6 rows in set (0.00 sec)

1.4 日期和时间类型

DATETIME：时间范围 1001 - 9999 年，精度范围 1 秒。内部把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关，使用 8 个字节的存储空间。MySQL 默认以可排序、无歧义的格式显示 DATETIME 的值，例如 “2018-04-26 16:51:05”。TIMESTAMP：时间范围 1970 - 2038 年，保存从 1970 年 1 月 1 日凌时开始的秒数，跟 UNIX 的时间戳一样。只使用 4 个字节的存储空间。 TIMESTAMP 显示的值依赖于时区，操作系统、MySQL 服务器及客户端都有时区设置。例如，存储值为 0 的 TIMESTAMP 在中国（东八区）显示为“1970-01-01 08:00:00”。如果跨时区访问或存储数据，TIMESTAMP 和 DATETIME 的行为完全不一样。DATE：占用 4 个字节。表示 年月日 YYYY-MM-DD，范围 1000 - 9999。TIME：占用 3 个字节。表示 时分秒 HH:MM:SS。YEAR：占用 1 个字节。表示 年 YYYY，范围 1901 - 2155。

TIMESTAMP 的规则相当复杂，且在不同版本的 MySQL 中会发生变化。如果需要跨时区的人使用，最好使用 TIMESTAMP。常见的特殊属性有：

创建表时，所有的 TIMESTAMP 字段默认为 NOT NULL，且第一个 TIMESTAMP 字段有默认值 CURRENT_TIMESTAMP。INSERT 插入数据时，如果没有指定第一个 TIMESTAMP 列的值，MySQL 会设置这个列的值为当前时间。UPDATE 更新数据时，MySQL 会默认更新第一个 TIMESTAMP 列的值为当前时间（除非明确指定值）。

MySQL 内置函数可以实现时间戳和日期的互相转换：

FROM_UNIXTIME()：将 UNIX 时间戳转为日期。UNIX_TIMESTAMP()：将日期转为 UNIX 时间戳。 MariaDB [foo]> CREATE TABLE t_test (id INT, t1 TIMESTAMP, t2 TIMESTAMP, d1 DATETIME, d2 DATETIME); Query OK, 0 rows affected (0.13 sec) MariaDB [foo]> DESC t_test; +-------+-----------+------+-----+---------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+-----------------------------+ | id | int(11) | YES | | NULL | | | t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | <------第一个 TIMESTAMP 字段会默认填充当前时间，且在更新数据时同步刷新 | t2 | timestamp | NO | | 0000-00-00 00:00:00 | | <------第二个 TIMESTAMP 字段会默认填充 0000-00-00 00:00:00 | d1 | datetime | YES | | NULL | | | d2 | datetime | YES | | NULL | | +-------+-----------+------+-----+---------------------+-----------------------------+ 5 rows in set (0.01 sec) MariaDB [foo]> CREATE TABLE time_test (id INT, stamp TIMESTAMP, time DATETIME); Query OK, 0 rows affected (0.18 sec) MariaDB [foo]> desc time_test; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | id | int(11) | YES | | NULL | | | stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | time | datetime | YES | | NULL | | +-------+-----------+------+-----+-------------------+-----------------------------+ 3 rows in set (0.02 sec) MariaDB [foo]> INSERT INTO time_test VALUES(1, null, '2018-04-27 09:27:00'); Query OK, 1 row affected (0.00 sec) MariaDB [foo]> SELECT * FROM time_test; +------+---------------------+---------------------+ | id | stamp | time | +------+---------------------+---------------------+ | 1 | 2018-04-27 09:28:25 | 2018-04-27 09:27:00 | <------插入时，TIMESTAMP 会默认填充当前时间 +------+---------------------+---------------------+ 1 row in set (0.00 sec) MariaDB [foo]> SELECT stamp + 0 FROM time_test; +----------------+ | stamp + 0 | +----------------+ | 20180427092825 | +----------------+ 1 row in set (0.00 sec) MariaDB [foo]> SELECT time + 0 FROM time_test; +----------------+ | time + 0 | +----------------+ | 20180427092700 | +----------------+ 1 row in set (0.00 sec) MariaDB [foo]> UPDATE time_test SET id = 2 WHERE id = 1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [foo]> SELECT * FROM time_test; +------+---------------------+---------------------+ | id | stamp | time | +------+---------------------+---------------------+ | 2 | 2018-04-27 09:30:02 | 2018-04-27 09:27:00 | <------更新时，TIMESTAMP 会默认更新为当前时间 +------+---------------------+---------------------+ 1 row in set (0.00 sec)

1.5 位数据类型

MySQL 中的位类型从技术上看都是字符串类型。

1.5.1 BIT（不建议用）

BIT 列中可以存储一个或多个 true/false 值。BIT(n) 定义了存储 n 个位的字段，BIT 列最大支持 64 个位。

BIT 的行为因存储引擎的不同而不同。MyISAM 会打包存储所有的 BIT 列，17 个单独的 BIT 列只需要 17 个位存储（假设没有可为 NULL 的列），只需要 3 个字节。假设存储引擎是 Memory 或 InnoDB，每个 BIT 列使用一个足够存储的最小整数来存放，无法节省存储空间。

MySQL 把 BIT 当做字符串类型，而不是数字类型。当检索 BIT(1) 的值时，结果是一个包含二进制 0 或 1 的字符串，而不是 ASCII 码的 0 或 1。但是在数字上下文的场景中，会自动将位字符转为对应的 ASCII 码。例如，对于存储了 b'00111001' 的 BIT(8) 列，正常检索时得到字符码为 57 的字符‘9’，但是在数字上下文场景中，得到数字 57：

MariaDB [foo]> CREATE TABLE bit_test(a BIT(8)); Query OK, 0 rows affected (0.01 sec) MariaDB [foo]> INSERT INTO bit_test VALUES(b'00111001'); Query OK, 1 row affected (0.02 sec) MariaDB [foo]> SELECT a, a + 0 FROM bit_test; +------+-------+ | a | a + 0 | +------+-------+ | 9 | 57 | +------+-------+ 1 row in set (0.00 sec)

1.5.2 SET

如果需要保存很多 true/false 值，可以合并这些列到一个 SET 数据类型，在 MySQL 内部以一系列打包的位的集合来表示。存储空间利用率高，使用方便（可以在查询中使用 FIELD() 和 FIELD() 函数）。缺点是改变列的定义时，代价较高（需要使用 ALTER TABLE），对于大表很麻烦。

1.6 选择标识符

标识列（identifier column）选择合适的数据类型很重要。标识列可以与其他值进行比较，或通过标识列寻找其他列。标识列也可以作为其他表中的外键。

选择标识列的类型时，既要考虑存储类型，也要考虑 MySQL 对这种类型怎么执行计算和比较。例如 MySQL 内部用整数存储 ENUM 和 SET 类型，在比较操作时转为字符串。

整数类型

整数是标识列最佳选择，快，且可以使用 AUTO_INCREMENT。

ENUM 和 SET 类型

标识列的糟糕选择。ENUM 和 SET 列适合存储固定信息，例如性别、产品类型。

字符串类型

尽量避免，慢，存储空间大。尤其是 MyISAM，会对字符串压缩索引。

1.7 特殊类型数据

例如低于秒级精度的时间戳。对于 IPv4 地址，可以使用无符号整数存储，MySQL 提供了 INET_ATON() 和 INET_NTOA() 函数进行转换。

2. MySQL schema 设计中的陷阱

太多列

MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。MyISAM 的变长行和 InnoDB 的行结构都需要转换。

太多关联

单个查询最多在 12 个表内做关联。

全能的枚举

变相的枚举

NULL

3. 范式和反范式

范式化数据库中，每个事实数据会出现且只出现一次。反范式化数据库中，信息是冗余的，可能存储在多处。

范式化的优点：

更新操作快较好的范式化可以减少数据冗余，修改简单范式化好的表更新冗余少的数据可以更少的使用 DISTINCT 或 GROUP BY 语句

范式化设计的 schema 的缺点是通常需要关联查询。

反范式化的优点：所有数据都在一张表中，避免了关联。

最常见的混用范式化和反范式化的例子是复制或缓存，在不同的表中存储相同的特定列。可以通过触发器更新冗余列。

4. 缓存表和汇总表

完全独立的缓存表和汇总表，可以存储少量冗余数据。

缓存表是实时维护的。

汇总表是定期重建的。

可以使用物化视图（MySQL 需要借助工具 Flexviews）和计数器表。

5. 加快 ALTER TABLE 操作的速度

MySQL 执行 ALTER TABLE 时，通常是用新的结构创建一个空表，从旧表中查出所有数据插入新表，再删除旧表。

大部分 ALTER TABLE 操作会中断 MySQL 服务。有几种方式可以避免停机：

主备结构时，可以在不提供服务的备用库执行操作，完成后再主备切换。影子拷贝：用新的表结构创建新表，然后通过重命名和删表操作交换两张表。可以使用 Facebook 的“online schema change”工具。

改变或删除列的默认值时，可以使用 ALTER COLUMN（不一定重建表）或 MODIFY COLUMN（需要重建表）。列的默认值存储在表的 .frm 文件中，

5.1 只修改 .frm 文件

有风险。。。

5.2 快速创建 MyISAM 索引

ALTER TABLE 时，先禁用索引，载入数据后再启用索引，可以高效载入数据。

转载于:https://www.cnblogs.com/kika/p/10851631.html