读高性能MySQL（第4版）笔记07_优化数据类型（下）

2023-09-13 07:08 由躺柒发表于 #数据库

1. ENUM（枚举）

1.1. MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到1或者2字节中

1.2. 转换为ENUM会使表变小

1.3. 如果表中有其他索引，减少主键大小也会使这些非主键索引小得多

1.4. ENUM列可以存储一组预定义的不同字符串值

1.5. ENUM字段是根据内部整数值排序的，而不是根据字符串本身

1.6. 查询中使用FIELD()函数显式地指定排序顺序，但这会导致MySQL无法利用索引消除排序

1.7. MySQL将每个枚举值存储为整数，并且必须进行查找以将其转换为字符串表示，因此ENUM列有一些开销

1.8. 将CHAR/VARCHAR列联接到ENUM列可能比联接到另一个CHAR/VARCHAR列更慢

1.9. 通常的设计实践是使用带有整数主键的“查找表”，以避免在联接中使用字符串

1.10. 更改ENUM中的有效值会导致需要做schema变更

2. 日期和时间类型

2.1. MySQL可以存储的最小时间粒度是微秒

2.2. DATETIME

2.2.1. 从1000年到9999年，精度为1微秒

2.2.2. 以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间

2.2.3. 与时区无关

2.2.4. 8字节的存储空间

2.2.5. 以可排序、无歧义的格式显示DATETIME值

2.2.6. ANSI表示日期和时间的标准方式

2.2.7. 保留日期和时间的文本表示

2.3. TIMESTAMP

2.3.1. 自1970年1月1日格林尼治标准时间（GMT）午夜以来经过的秒数

2.3.1.1. 与UNIX时间戳相同

2.3.2. 从1970年到2038年1月19日

2.3.2.1. 会遇到2038年的问题

2.3.2.1.1. 使用带符号的32位INT，可以表达直到2038年的时间

2.3.2.1.2. 使用无符号的32位INT，可以表达直到2106年的时间

2.3.2.1.3. 使用64位，还可以超出这些范围

2.3.3. 时间戳显示的值依赖于时区

2.3.3.1. MySQL服务器、操作系统和客户端连接都有时区设置

2.3.3.2. 存储值0的TIMESTAMP在美国东部标准时间（EST）中显示为1969-12-31 19：00：00，与格林尼治标准时间（GMT）差5小时

2.3.4. 4字节的存储空间

2.3.5. FROM_UNIXTIME()函数来将UNIX时间戳转换为日期

2.3.6. UNIX_TIMESTAMP()函数将日期转换为UNIX时间戳

2.3.7. 保留与所使用时区相关的值

2.3.8. TIMESTAMP的行为规则很复杂，并且在不同的MySQL版本中会发生变化，因此你应该验证数据库的行为是否符合需要。在对TIMESTAMP列进行更改后，通常最好检查SHOW CREATE TABLE命令的输出

2.3.9. 特殊属性

2.3.9.1. 当插入一行记录时没有指定第一个TIMESTAMP列的值，MySQL会将该列的值设置为当前时间

2.3.9.2. 当更新一行记录时没有指定第一个TIMESTAMP列的值，MySQL默认也会将该列的值更新为当前时间

2.3.9.3. 可以为任何TIMESTAMP列配置插入和更新行为

2.3.9.4. TIMESTAMP列在默认情况下为NOT NULL，这也和其他的数据类型不一样

3. 位压缩数据类型

3.1. 从技术上来说都是字符串类型

3.2. BIT

3.2.1. 可以使用BIT列存储一个或多个true/false值

3.2.2. 视为字符串类型，而不是数字类型

3.2.3. 避免使用这种类型

3.2.3.1. 建议使用TINYINT

3.3. SET

3.3.1. 如果需要存储多个true/false值，可以考虑使用MySQL原生的SET数据类型

3.3.2. 以一组打包的位的集合来表示的

3.3.3. 更有效地利用存储空间

3.3.4. FIND_IN_SET()和FIELD()等函数，使其易于在查询中使用

3.3.5. 替代方法是使用整数作为二进制位的打包集合

3.3.5.1. 可以在不使用ALTER TABLE的情况下更改字段表示的“枚举”

3.3.5.2. 查询更难编写和理解

4. JSON数据类型

4.1. 决定使用原生SQL还是JSON取决于在数据库中存储JSON的便捷性是否大于性能

4.2. 如果每天访问这些数据数百万次或数十亿次，速度差异就会累加起来

4.3. 使用SQL列的速度仍然更好于JSON列

5. 标识符

5.1. 引用行及通常使其唯一的方式

5.2. 为标识符列选择数据类型时，应该与联接表中的对应列保持一致

5.3. 在为标识符列选择类型时，不仅需要考虑存储类型，还需要考虑MySQL如何对该类型执行计算和比较

5.4. 在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型

5.5. 整数通常是标识符的最佳选择

5.5.1. 速度快

5.5.2. 自动递增

5.6. 对于标识符来说，ENUM和SET类型通常是糟糕的选择

5.6.1. ENUM和SET列适用于保存订单状态或产品类型等信息

5.7. 应避免使用字符串类型作为标识符的数据类型

5.7.1. 很消耗空间

5.7.2. 比整数类型慢

5.8. 对于完全“随机”的字符串要非常小心

5.8.1. MD5()、SHA1()或UUID()生成的字符串

5.9. 新值会任意分布在很大的空间内，这会减慢INSERT和某些类型的SELECT查询的速度

5.9.1. 插入的值会写到索引的随机位置，所以会使得INSERT查询变慢

5.9.2. 导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片

5.9.3. SELECT查询也会变慢，因为逻辑上相邻的行会广泛分布在磁盘和内存中

5.9.4. 对于所有类型的查询，随机值都会导致缓存的性能低下，因为它们会破坏引用的局部性，而这正是缓存的工作原理

5.10. 存储通用唯一标识符（UUID）值，则应该删除破折号

5.10.1. 更好的做法是，使用UNHEX()函数将UUID值转换为16字节的数字，并将其存储在一个BINARY（16）列中

5.10.2. 可以使用HEX()函数以十六进制格式检索值

6. 特殊数据类型

6.1. IPv4地址

6.1.1. 使用VARCHAR（15）列来存储

6.1.2. 实际上是32位无符号整数，而不是字符串

6.1.2.1. 小数点将地址分成四段的表示方法只是为了让人们阅读容易

6.1.2.2. 存储为无符号整数

6.1.3. INET_ATON()和INET_NTOA()函数来在这两种表示形式之间进行转换

6.1.4. 从VARCHAR（15）的约16字节缩减到无符号32位整数的4字节

7. schema设计中的陷阱

7.1. 太多的列

7.2. 太多的联接

7.2.1. MySQL限制每个联接有61个表

7.3. 全能的枚举

7.3.1. 要小心过度使用ENUM

7.4. 变相的枚举

7.4.1. ENUM列允许在列中保存一组已定义值中的单个值

7.4.2. SET列则允许在列中保存一组已定义值中的一个或多个值

7.4.3. 如果真和假两种情况不会同时出现，那么毫无疑问应该使用ENUM列而不是SET列

7.5. NULL不是虚拟值

7.5.1. 在表中存储事实上的“空值”，可以使用0、特殊值或空字符串作为代替

7.5.2. 当需要表示未知值时，不要太害怕使用NULL

7.5.3. 在某些情况下，使用NULL比使用某个虚拟常数更好

7.5.4. MySQL会对NULL值进行索引，而Oracle则不会

8. schema管理

8.1. 修改schema是数据库工程师必须承担的最常见任务之一

8.2. schema变更管理视为“数据存储平台”的一部分

8.3. 尽可能靠近现有的软件部署工具和工作流程

8.4. 应该使用能够集成针对schema更改的基本检测的工具，以确保满足一些基线需求

8.5. 如果所在的组织使用多种编程语言且发展迅速，请确保不会意外地引入人为瓶颈

8.6. Skeema是一个在跨多个环境的版本控制中管理schema更改的杰出开源解决方案

8.7. gh-ost是由GitHub的数据工程团队创建的，专门作为一种管理schema更改过程的解决方案，既不影响服务，也不使用触发器

8.7.1. 其使用的是二进制日志而不是触发跟踪变化，这是更安全的选择，所以不必担心触发器的性能影响