查询优化

画了一个图，不知道对不对。



创建 curl-format.txt 文件，写入如下配置：
```txt
time_namelookup:    %{time_namelookup}\n
time_connect:       %{time_connect}\n
time_appconnect:    %{time_appconnect}\n
time_pretransfer:   %{time_pretransfer}\n
time_posttransfer:  %{time_posttransfer}\n
time_redirect:      %{time_redirect}\n
time_starttransfer: %{time_starttransfer}\n
———\n
time_total:         %{time_total}\n
```

访问网站进行测试：
```shell
$ curl -w "@curl-format.txt" -o /dev/null -s https://www.baidu.com
time_namelookup:    0.004719
time_connect:       0.018401
time_appconnect:    0.063975
time_pretransfer:   0.064062
curl: unknown --write-out variable: 'time_posttransfer'
time_posttransfer:  
time_redirect:      0.000000
time_starttransfer: 0.077520
———
time_total:         0.077630
```

curl 报错了，说没有 time_posttransfer 变量，原因是当前使用的 curl 版本较低，等系统pkg可以更新时再修复这个测试吧。

![](https://s2.loli.net/2024/08/30/E7Rnx43LZFpBfh1.png)
## 参考

- [curl manpage](https://curl.se/docs/manpage.html#-w)

使用 curl 分析请求耗时

.DS_Store 文件是 macOS 系统的 Finder 自动生成的文件，存储了文件夹的显示属性，如图标位置、窗口大小、背景颜色等。



禁止 .DS_store 生成：
```shell
$ defaults write com.apple.desktopservices DSDontWriteNetworkStores -bool TRUE
```

恢复 .DS_store 生成：
```shell
$ defaults delete com.apple.desktopservices DSDontWriteNetworkStores
```

## 参考

- [On the origins of DS_store](https://www.arno.org/on-the-origins-of-ds-store)
- [.DS_Store](https://zh.wikipedia.org/wiki/.DS_Store)

.DS_store 到底是什么东西？

一本书的目录就是它的索引。



## 索引基础

### 索引的类型

不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

#### B-tree索引

InnoDB使用的是其变种 B+tree。B-tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

建立在B-tree结构（准确地说是B+tree）上的索引
![图7-1：建立在B-tree结构（准确地说是B+tree）上的索引](https://s2.loli.net/2024/08/26/1mCg2GJDxHlRhEc.png)

数据存储示例
```sql
CREATE TABLE People (
 last_name varchar(50) not null,
 first_name varchar(50) not null,
 dob date not null,
 key(last_name, first_name, dob)
);
```

（最上面一行有问题，Angelina < Cuba，应该在前面）
![图7-2：B-tree（从技术上来说是B+tree）索引树中的部分条目示例](https://s2.loli.net/2024/08/26/8FBzaQnGTLUNsxD.png)

索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。

InnoDB存储引擎有一个被称为自适应哈希索引的特性。当InnoDB发现某些索引值被非常频繁地被访问时，它会在原有的B-tree索引之上，在内存中再构建一个哈希索引。可以通过参数关闭这个特性。

todo: 书中说可以使用非前缀。
可以使用B-tree索引的查询类型。

**全值匹配**

指索引中的列全部出现在 where 条件中。

**匹配最左前缀**

指索引中从左到右顺序的列出现在 where 条件中。

**匹配列前缀**

指 where 的某个条件是某个索引的第一个字段，并且是该字段值的前缀。

**匹配范围值**

指 where 指定的两个限定范围的条件都是某个索引的第一个字段。

**精确匹配某一列而范围匹配另外一列**

即索引的第一列全匹配，第二列是范围匹配。

**只访问索引的查询**

即覆盖索引。

因为索引树中的节点是有序的，所以除了按值查找，索引还可以用于查询中的ORDER BY操作（按顺序查找）。

**B-tree索引的限制**

- 如果不是按照索引的最左列开始查找，则无法使用索引。
- 不能跳过索引中的列。
- 如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查找。

#### 全文索引

FULLTEXT是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

相关概念
- 停用词
- 词干
- 复数
- 布尔搜索

### 索引的优点

- 索引大大减少了服务器需要扫描的数据量。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机I/O变为顺序I/O。

## 高性能的索引

### 前缀索引和索引的选择性

可以对字段的前一部分进行索引，可以减少空间占用，但是会降低选择性。索引的选择性指不重复的索引值（也称为基数，cardinality）和数据表的记录总数（＃T）的比值。选择性越大查询效率越高。对于BLOB、TEXT或很长的 VARCHAR 类型，必须使用前缀索引，因为 MySQL 不支持这些列的完整内容索引。

设计索引时可以使前缀索引的选择性接近完整列的选择性来优化索引。这需要测试不同的前缀长度的选择性。但是需要注意数据分布不均匀的情况。

创建前缀索引
```sql
ALTER TABLE tb_name ADD KEY (column(7));
```

MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描。

### 多列索引

一个常见的错误是对多个列单独建立索引，这在大部分情况下不能提升性能。因为 MySQL 引入了一种叫索引合并（index merge）的策略，其同时使用那些单独的索引进行扫描，并将结果合并。

这种算法有三个变种：
- OR条件的联合（union）
- AND条件的相交（intersection）
- 组合前两种情况的联合及相交

索引合并的问题在于
- 当优化器需要对多个索引做联合操作时（通常有多个OR条件），通常需要在算法的缓存、排序和合并操作上耗费大量CPU和内存资源，尤其是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
- 更重要的是，优化器不会把这些操作计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接进行全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响并发的查询，但如果单独运行这样的查询则往往会忽略对并发性的影响。

可以通过参数optimizer_switch来关闭索引合并功能，还可以使用IGNORE INDEX语法让优化器强制忽略掉某些索引，从而避免优化器使用包含索引合并的执行计划。

### 选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询语句，同时，还需要考虑如何更好地满足排序、分组和范围查询操作的需要。

经验法则
- 将选择性最高的列放到索引最前列。
- 考虑如何避免大量随机I/O和排序。

### 聚簇索引

聚簇索引不是新的的索引类型，而是一种存储方式。在 InnoDB 中聚簇索引就是 B-tree，但是会在叶子页中存储数据行。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况。）

聚簇索引的数据分布。中间的节点页没有数据，叶子节点存储了数据。
![图7-3：聚簇索引的数据分布](https://s2.loli.net/2024/08/28/f6ewlhZiUSxtdBu.png)

MySQL 内置的存储引擎都不支持选择用于聚簇的索引，InnoDB 根据主键聚簇数据。如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。这样做的缺点在于，所有需要使用这种隐藏主键的表都依赖一个单点的“自增值”，这可能会导致非常高的锁竞争，从而出现性能问题。

聚簇数据的优点
- 相关联的数据保存在一起，如果需要查询这些数据，磁盘 I/O 会很少。
- 聚簇索引将索引和数据保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点
- 聚簇索引的优势在于提高了I/O的性能，如果数据能够全部放入内存，那么就没什么优势了。
- 插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。
- 更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更新的行移动到新的位置。
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂（page split）的问题。页分裂会导致表占用更多的磁盘空间。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
- 二级索引（非聚簇索引）可能比想象中的要更大，因为二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找，而不是一次。

二级索引的叶子节点存储的是主键的值，所以查询数据需要访问两次索引。

```sql
CREATE TABLE layout_test (
 col1 int NOT NULL,
 col2 int NOT NULL,
 PRIMARY KEY(col1),
 KEY(col2)
);
```

InnoDB表layout test的主键物理存储分布
![图7-4：InnoDB表layout test的主键物理存储分布](https://s2.loli.net/2024/08/28/gy9HUWJVtxe63uO.png)

InnoDB表layout test的二级索引分布
![图7-5：InnoDB表layout test的二级索引分布](https://s2.loli.net/2024/08/28/ZBSIcRyqai8thKC.png)

**代理键（surrogate key）**

这种主键的值和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入的，对于根据主键做联接操作的性能也会更好。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID作为聚簇索引会很糟糕：它使得聚簇索引的插入变得完全随机，这就是最糟糕的情况，数据本身没有任何聚集特性。

**顺序的插入 vs 随机插入**

向聚簇索引顺序地插入索引值
![图7-6：向聚簇索引顺序地插入索引值](https://s2.loli.net/2024/08/28/iVn86eB2ShdjMCo.png)

向聚簇索引中插入无序的记录
![图7-7：向聚簇索引中插入无序的记录](https://s2.loli.net/2024/08/28/POxT8sBSRclQdKX.png)

随机插入的缺点
- 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者还没有被加载到缓存中，InnoDB在插入之前不得不先找到，并从磁盘将目标页读取到内存中。这将导致大量的随机I/O。
- 因为写入是乱序的，所以InnoDB不得不频繁地做页分裂操作，以便为新记录分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个。
- 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

在把这些随机值载入聚簇索引后，最好做一次OPTIMIZE TABLE来重建表并优化页的填充情况。
```sql
OPTIMIZE TABLE tb_name;
```

**什么时候按主键顺序插入反而会更糟**

对于高并发的工作负载，在InnoDB中按主键顺序插入可能会造成明显的写入竞争。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。

### 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。需要注意的是，只有B-tree索引可以用于覆盖索引。当然，二级索引的叶子节点包含主键值，所以在查询列出现主键也可以覆盖。

覆盖索引的优点
- 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。
- 因为索引是按照列值的顺序存储的（至少在单页内如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
- InnoDB的二级索引在叶子节点中保存了记录的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。

### 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。如果在EXPLAIN的输出结果中，type列的值为“index”，则说明MySQL使用了索引扫描来做排序。

如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录都回表查询一次对应的记录。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。

只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。如果查询需要联接多张表，则只有当ORDER BY子句引用的字段全部在第一个表中时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，MySQL需要执行排序操作，而无法利用索引排序。

例子
```sql
CREATE TABLE rental (
 ...
 PRIMARY KEY (rental_id),
 UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
 KEY idx_fk_inventory_id (inventory_id),
 KEY idx_fk_customer_id (customer_id),
 KEY idx_fk_staff_id (staff_id),
 ...
);
```

可以使用索引排序的例子
```sql
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;
... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;
```

不能使用索引排序的例子
```sql
-- 索引中的列都是按正序排序的, 下面的 inventory_id 使用逆序了
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
-- 引用了一个不在索引中的列
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
-- WHERE和ORDER BY中的列无法组合成索引的最左前缀
... WHERE rental_date = '2005-05-25' ORDER BY customer_id;
-- 在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列排序
... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;
-- 这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询
... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;
```

### 冗余和重复索引

MySQL允许在相同列上创建多个相同的索引，这会影响性能同时也浪费磁盘空间。
- 重复索引是指在相同的列上按照相同顺序创建的相同类型的索引。应该避免创建这样的重复索引，发现以后应该立即移除。
- 冗余索引和重复索引不同，如果创建了索引（A，B），再创建索引（A）就是冗余索引。另一种冗余索引是，将一个索引扩展为（A，ID），其中ID是主键，因为主键列已经包含在二级索引中了。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引。但有时候出于性能方面的考虑也需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

### 未使用的索引

没什么用，应该删除。查找未使用的索引：
```sql
SELECT * FROM sys.schema_unused_indexes;
```

## 维护索引和表

维护表有三个主要目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

**找到并修复损坏的表**

如果你遇到了古怪的问题，可以尝试运行CHECK TABLE来检查是否发生了表损坏（有些存储引擎不支持）。可以使用REPAIR TABLE命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。如果存储引擎不支持，可通过一个不做任何操作（n o-o p）的ALTER操作来重建表，例如，将表的存储引擎修改为当前的引擎。
```sql
ALTER TABLE <table> ENGINE=INNODB;
```

此外，还可以将数据导出再导入一次。不过，如果损坏的是系统区域，或者是表的“行数据”区域，而不是索引，那么上面的办法就没有用了。在这种情况下，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能地恢复数据。

**更新索引统计信息**

MySQL的优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息，以解决这个问题。

可以使用SHOW INDEX FROM命令来查看索引的基数（cardinality）。
```sql
SHOW INDEX FROM tb_name\G
```

索引统计信息何时更新
- InnoDB会在表首次打开
- 执行ANALYZE TABLE
- 表的大小发生非常大的变化时
- InnoDB在打开某些INFORMATION_SCHEMA表
- 使用SHOW TABLE STATUS和SHOW INDEX
- 在MySQL客户端开启自动补全功能的时候
- 使用SHOW INDEX查看索引统计信息

如果有大量的表，会带来性能问题，可以关闭innodb_stats_on_metadata参数来避免。

**减少索引和数据的碎片**

B-tree索引可能会产生碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。根据设计，B-tree索引需要随机磁盘访问才能定位到叶子页，所以随机访问总是不可避免的。

表的数据存储也可能发生碎片化。有三种类型的数据碎片：
- 行碎片（Row fragmentation）这种碎片指的是数据行被存储在多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
- 行间碎片（Intra-row fragmentation）是指逻辑上顺序的页或者行，在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响。
- 剩余空间碎片（Free space fragmentation）
- 指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。对于那些不支持OPTIMIZE TABLE的存储引擎，可以通过一个不做任何操作（no-op）的ALTER TABLE操作来重建表。
```sql
ALTER TABLE <table> ENGINE=<engine>;
```

## 小结

总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找，尽可能地使用数据内部顺序从而避免额外的排序操作，并尽可能地使用索引覆盖查询。

## 参考

- [深入了解 B-Tree 和 B+Tree 的区别](https://cloud.tencent.com/developer/article/1849828)
- [Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/)
- [B+树索引的使用](https://juejin.cn/book/6844733769996304392/section/6844733770046636045)
- [mysql show index from table 各参数含义](https://blog.csdn.net/t18112925650/article/details/109258363)

高性能的索引

一些字段类型方面的使用建议。



## 整数

- TINYINT 1B
- SMALLINT 2B
- MEDIUMINT 3B
- INT 4B
- BIGINT 8B

MySQL 允许为整数类型指定宽度，例如 INT(11)，但这没什么用，只是规定一些交互工具用来显示字符的个数。

## 实数

- FLOAT 4B
- DOUBLE 8B
- DECIMAL(M,D) 占用 max(M,D)+2 字节

由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL。可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

## 字符串类型

**VARCHAR**

VARCHAR 用于存储变长字符串，比 CHAR 更节省空间。VARCHAR 需要额外的空间记录字符串的长度：列最大长度 <=255B，则使用 1B 存储长度，否则使用 2B。
- 注意是字符的长度，像 UTF-8，每个字符可能有不同的字节数

如果 VARCHAR 列更新后超过原位置的空间，则需要额外的工作。对于 InnoDB 来说需要分割页面来容纳行。其他存储引擎可能不在原位置更新数据。

VARCHAR 适用于：
- 字符串列的最大长度远大于平均长度
- 列的更新很少，所以碎片不是问题
- 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

**CHAR**

CHAR 使用固定长度存储字符串。当存储 CHAR 时，MySQL 会删除尾随空格。如果需要比较，值会用空格填充。

CHAR 适用于：
- 存储非常短的字符串
- 所有值的长度几乎都相同
- 经常修改，因为不容易出现碎片

**BINARY 和 VARBINARY**

它们存储的是二进制字符串。二进制字符串与常规字符串非常相似，但它们存储的是字节而不是字符。填充也不同：MySQL填充BINANRY用的是`\0`而不是空格，并且在检索时不会去除填充值。

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

**使用 VARCHAR(5) 和 VARCHAR(200) 存储 'hello' 的空间开销是一样的。那么使用更短的列有什么优势吗？**

事实证明有很大的优势。较大的列会使用更多的内存，因为MySQL通常会在内部分配固定大小的内存块来保存值。这对于使用内存临时表的排序或操作来说尤其糟糕。在利用磁盘临时表进行文件排序时也同样糟糕。最好的策略是只分配真正需要的空间。

**BLOB和TEXT类型**

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

与其他类型不同，MySQL 把每个 BLOB 和 TEXT 值当作一个具有标识的对象来处理。当 BLOB 和 TEXT 值太大时，InnoDB 会使用其他存储区域，此时每个值在行内需要1~4字节的空间。

BLOB和TEXT家族之间的唯一区别是，BLOB类型存储的是二进制数据，没有排序规则或字符集，但TEXT类型有字符集和排序规则。MySQL对BLOB和TEXT列的排序与其他类型不同：它只对这些列的最前max_sort_length字节而不是整个字符串做排序。

创建 blob 索引时需要指定长度，否则会报`BLOB/TEXT column 'blob_column' used in key specification without a key length`错误。
```sql
CREATE INDEX blob_column_IDX USING BTREE ON test.my_tb (blob_column(10));
```

也就是说，MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序。

不要在数据库中存储图片。

**ENUM**

ENUM 在 MySQL 内部使用整数存储，占用1或2字节，可以替代字符串类型。尽量避免存储`ENUM('1','2','3')`这样会造成混乱的值。ENUM 类型根据内部的整数值进行排序，而非字面字符串。可以使用`FIELD()`函数显式指定排序顺序，但这会导致MySQL无法利用索引消除排序。

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

连接VARCHAR和ENUM列的速度

| 测试                 | QPS |
| ------------------ | --- |
| VARCHAR 连接 VARCHAR | 2.6 |
| VARCHAR 连接 ENUM    | 1.7 |
| ENUM 连接 VARCHAR    | 1.8 |
| ENUM 连接 ENUM       | 3.5 |

虽然ENUM类型在存储值的方式上非常有效，但更改ENUM中的有效值会导致需要做schema变更。

## 日期和时间类型

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

**DATETIME**

可存储的时间范围从1000年到9999年，精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。占用8字节的空间。

**TIMESTAMP**

TIMESTAMP类型存储自1970年1月1日格林尼治标准时间（GMT）午夜以来经过的秒数——与UNIX时间戳相同。占用4字节空间。只能表示从1970年到2038年1月19日。

相关函数
- FROM_UNIXTIME() 将 UNIX 时间戳转为日期
- UNIX_TIMESTAMP() 将日期转为 UNIX 时间戳

时间戳显示的值依赖于时区。MySQL服务器、操作系统和客户端连接都有时区设置。

另外一个相关的话题是，是否应该使用整数来存储时间，但是这个问题没有答案，一切取决于需求。

## 位压缩数据类型

MySQL有几种使用值中的单个位来紧凑地存储数据的类型。所有这些位压缩类型，不管底层存储和处理方式如何，从技术上来说都是字符串类型。

**BIT**

BIT 用于存储位数据，长度范围1~64位。MySQL在处理时会将BIT视为字符串类型，而不是数字类型。
```shell
mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;
+ - + - - - +
| a | a + 0 |
+ - + - - - +
| 9 |    57 |
+ - + - - - +
```

**SET**

SET 也是用于存储位数据，在定义 SET 类型的列时类似 ENUM，但是对于某行记录的 SET 类型的值来说，其可以在不同位上存储多个所定义值。
```shell
mysql> CREATE TABLE acl (
    -> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
    -> );
mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
```

## JSON 数据类型

使用 json 存储数据还是展开为列也是个看需求的问题，不过 josn 在存储空间方面确实不如展开后的列。

## 主键的类型

主键的类型应在满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。选择类型后，要确保在所有相关表中使用相同的类型，包括是否为UNSIGNED。混合不同的数据类型可能导致性能问题，即使没有性能影响，在进行比较操作时，隐式类型转换也可能会产生难以发现的错误。

- 最好是使用整数，但要注意整数意外耗尽所导致的系统停机问题。
- 对于标识符来说，ENUM和SET类型通常是糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM和SET列适用于保存订单状态或产品类型等信息。
- 如果可能，应避免使用字符串类型作为标识符的数据类型，因为它们很消耗空间，而且通常比整数类型慢。

对于完全“随机”的字符串要非常小心，如MD5()、SHA1()或UUID()生成的字符串。这些函数生成的新值会任意分布在很大的空间内，这会减慢INSERT和某些类型的SELECT查询的速度：
- 因为插入的值会写到索引的随机位置，所以会使得INSERT查询变慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
- SELECT查询也会变慢，因为逻辑上相邻的行会广泛分布在磁盘和内存中。
- 对于所有类型的查询，随机值都会导致缓存的性能低下，因为它们会破坏引用的局部性，而这正是缓存的工作原理。如果整个数据集都是“热的”，那么将任何特定部分的数据缓存到内存中都没有任何好处，而且如果工作集比内存大，缓存就会出现大量刷新和不命中。

如果存储通用唯一标识符（UUID）值，则应该删除破折号，或者更好的做法是，使用UNHEX()函数将UUID值转换为16字节的数字，并将其存储在一个BINARY（16）列中。可以使用HEX()函数以十六进制格式检索值。

## 特殊数据类型

**IP 地址**

IP 地址实际上是32位无符号整数，应该使用 UNSIGNED INT。MySQL提供了INET_ATON()和INET_NTOA()函数来在这两种表示形式之间进行转换。

## 表设计中的陷阱

**太多的列**

MySQL的存储引擎API通过在服务器和存储引擎之间以行缓冲区格式复制行来工作；然后，服务器将缓冲区解码为列。将行缓冲区转换为具有解码列的行数据结构的操作代价是非常高的。

**太多的联接**

MySQL限制每个联接有61个表。一个粗略的经验法则是，如果需要以高并发性快速执行查询，那么每个查询最好少于十几个的表。

**过度使用 ENUM**

```sql
CREATE TABLE ... (
 country enum(','0
','1','2',...,'31')
```

**变相的 ENUM**

如果这里真和假两种情况不会同时出现，那么毫无疑问应该使用ENUM列而不是SET列。
```sql
CREATE TABLE ...(
 is_default set('
Y','N') NOT NULL default 'N'
```

**NULL不是虚拟值**

当需要表示未知值时，不要太害怕使用NULL。在某些情况下，使用NULL比使用某个虚拟常数更好。
```sql
CREATE TABLE ... (
 dt DATETIME NOT N
ULL DEFAULT '0000-00-00 00:00:00'
```

还有一个相关的细节，MySQL会对NULL值进行索引，而Oracle则不会。


## 总结

- 尽量避免在设计中出现极端情况，例如，强制执行非常复杂的查询或者包含很多列的表设计（很多的意思是介于有点多和非常多之间）。
- 使用小的、简单的、适当的数据类型，并避免使用NULL，除非确实是对真实数据进行建模的正确方法。
- 尝试使用相同的数据类型来存储相似或相关的值，尤其是在联接条件中使用这些值时。
- 注意可变长度字符串，它可能会导致临时表和排序的全长内存分配不乐观。
- 如果可能的话，尝试使用整数作为标识符。
- 避免使用一些传统的MySQL技巧，例如，指定浮点数的精度或整数的显示宽度。
- 小心使用ENUM和SET类型。它们很方便，但也可能被滥用，有时还很棘手。另外最好避免使用BIT类型。

## 参考

- [MySQL 数据类型](https://www.runoob.com/mysql/mysql-data-types.html)
- [Chapter 13 Data Types](https://dev.mysql.com/doc/refman/8.4/en/data-types.html)