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



## 索引基础

### 索引的类型

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

#### 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)

MySQL 数据类型

本文对应原书第5章-优化服务器设置。出乎意料的是，该章节建议正确地配置基本设置，并将更多的时间花在schema优化、索引和查询设计上。甚至有innodb_dedicated_server这种自动配置 MySQL 的选项。

限于本人水平不足，本文只是对相关配置的分类整理，列出的配置无法给出测试。文章的最后展示了自己使用的最小资源配置，仅供参考。



## 什么不应该做

**建立基准测试套件**

它需要大量的工作和研究，而且大多时候，潜在的回报非常小，因此是巨大的时间浪费。最好把这些时间花在其他事情上，比如检查备份、监控查询计划的变化等。

**按比率调优**

调优比率是一个经验法则。比如，InnoDB缓冲池命中率应该高于某个百分比，如果命中率过低，应该增加缓存大小。这是非常错误的建议。缓存命中率与缓存是太大还是太小无关。
- 命中率取决于工作负载。不管缓存有多大，有些工作负载根本不能被缓存
- 缓存命中是没有意义的。

**使用调优脚本**

“调优”服务器可能是一种惊人的时间浪费。在没有经过测试和理解的情况下进行应用也可能是危险的，因为它可能针对的是一种与你不同的情况，而你却没有理解。

**相信流行的内存消耗公式**

MySQL本身在崩溃时会输出的那个公式（没听说过，反正不建议用）。这个公式是很早之前的。这不是一个可靠的，甚至不是一个有用的方法来了解MySQL在最坏的情况下可以使用多少内存。事实是，你不能给MySQL的内存消耗设定上限。MySQL并不是一个严格控制内存分配的数据库服务器。

## 配置介绍

确定配置文件位置
```shell
$ mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
```

配置文件格式
- MySQL 配置文件是 INI 标准
- 配置项全部使用小写字符，单词之间用下划线或短横线（等效的）。

配置项是有作用域的
- 全局作用域（服务器）
- 会话作用域（连接）
- 对象作用域

能够在服务器运行时更改的配置称为动态配置变量，MySQL 重启后会失效。
- 如果在服务器运行时设置变量的全局值，则当前会话和其他现有会话的值将不受影响。
- MySQL 8.0引入了新的语法SET PERSIST，允许在运行时设置一次值，MySQL将把这个设置写入磁盘，以便在下次重启后继续使用该值。
- 动态设置变量可能会产生意想不到的副作用。在线更改设置时要小心，可能会导致服务器执行大量工作。

## 基本配置

下面是一个最小化的配置文件，基于 MySQL 8.0。虽然看起来配置项不多，但是已经超过了很多人的需要。
```ini
[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket  = /var/lib/mysql/mysql.sock
pid_file= /var/lib/mysql/mysql.pid
user    = mysql
port    = 3306
# INNODB
innodb_buffer_pool_size         = <value>
innodb_log_file_size            = <value>
innodb_file_per_table           = 1
innodb_flush_method             = O_DIRECT
# LOGGING
log_error         = /var/lib/mysql/mysql-error.log
log_slow_queries  = /var/lib/mysql/mysql-slow.log
# OTHER
tmp_table_size    = 32M
max_heap_table_size            = 32M
max_connections   = <value>
thread_cache_size = <value>
table_open_cache  = <value>
open_files_limit  = 65535
[client]
socket  = /var/lib/mysql/mysql.sock
port    = 3306
```

## 内存相关配置

### InnoDB 缓冲池

参考[MySQL中innodb_buffer_pool_size的配置](https://blog.csdn.net/javaanddonet/article/details/109517031)

### 线程缓存

**thread_cache_size**

指定了MySQL可以保存在缓存中的线程数。设置该值的一个好的方法是观察Threads_connected变量，并尝试将thread_cache_size设置得足够大，以处理工作负载中的典型波动。例如，如果Threads_connected通常保持在100到120之间，那么可以将缓存大小设置为20。如果停留在500到700之间，那么将缓存大小设置为200也足够大了。

## I/O 相关配置

InnoDB 的缓冲区和文件
![图5-1：InnoDB的缓冲区和文件](https://s2.loli.net/2024/08/24/AF2TZl3sXHnW7v6.png)

### InnoDB 事务日志

事务在提交是不是直接写到数据表空间的，而是先写入事务日志中，这样做可以将随机I/O转为顺序I/O，减小开销。但是事务日志使用循环缓冲区，当发生环绕时需要将之前的日志刷新到磁盘。

日志文件的总大小由innodb_log_file_size和innodb_log_files_in_group控制。

### 日志缓冲区

InnoDB修改数据时会将修改记录写入日志缓冲区，并将其保存在内存中。当缓冲区满了、事务提交时，或者每秒1次（这三个条件以先满足者为准），InnoDB会将缓冲区刷新到磁盘上的日志文件中。如果有大型事务，增加缓冲区大小（默认为1MB）有助于减少I/O。

**innodb_log_buffer_size**

设置控制缓冲区大小，通常不需要将缓冲区设置得太大。建议的范围是1～8MB，一般来说足够了，除非写入很多大的BLOB记录。

**innodb_flush_log_at_trx_commit**

用来控制日志缓冲区的刷新位置和刷新频率。
- 0: 每秒定时将日志缓冲区写入日志文件，并刷新日志文件，但在事务提交时不做任何操作。
- 1: 每次事务提交时，将日志缓冲区写入日志文件，并将其刷新到持久存储中。这是默认的（也是最安全的）设置；它保证你不会丢失任何已提交的事务，除非磁盘或操作系统“假装”进行刷新操作（没有将数据真正写入磁盘）。
- 2: 每次事务提交时都将日志缓冲区写入日志文件，但不执行刷新。InnoDB按计划每秒刷新1次。与0设置最重要的区别是，如果只是MySQL进程崩溃，设置为2不会丢失任何事务。但是，如果整个服务器崩溃或断电，仍然可能丢失事务。

了解将日志缓冲区写入日志文件和将日志刷新到持久存储之间的区别很重要。在大多数操作系统中，将缓冲区写入日志只是将数据从InnoDB的内存缓冲区移动到操作系统的缓存中，依然还是在内存中。

**innodb_flush_method**

该选项允许配置InnoDB与文件系统的实际交互方式。如果你使用的是类UNIX操作系统，并且RAID控制器有备用电池的写缓存，建议使用O_DIRECT。如果不是，则default或O_DIRECT都可能是最佳选择，具体取决于应用程序。

### InnoDB表空间

**innodb_file_per_table**

该选项允许你将InnoDB配置为每个表使用单独的文件。它将数据存储在数据库目录下的tablename.ibd文件中。这使得删除表时更容易回收空间。然而，将数据放在多个文件中实际上会导致更多的空间浪费，因为跟InnoDB单个表空间中的内部碎片相比，每个.ibd文件中都会有一些浪费的空间。

该选项的缺点（从8.0.23版本开始，这应该不再是一个问题了。）
- 删除表将在文件系统级别解除（删除）文件的链接，这在某些文件系统（ext3）中可能非常慢。
- 删除表空间需要InnoDB在查找属于该表空间的页面时锁定和扫描缓冲池，这在服务器的缓冲池很大时是非常慢的。

最后的建议是使用innodb_file_per_table并限制共享表空间的大小。

如果事务长时间保持打开状态（即使没有做任何工作），并且使用默认的可重复读取事务隔离级别，InnoDB将无法删除行的旧版本，因为未提交的事务仍需要能够看到它们。InnoDB将旧版本存储在表空间中，因此随着更多数据的更新，它将继续增长。相关配置如下。

**innodb_purge_threads**

设置后台清除操作的线程数。

**innodb_purge_batch_size**

设置从历史列表中清除一批解析和处理的undo log页数。

**innodb_max_purge_lag**
- 非0: 在InnoDB开始延迟更多修改数据的查询之前，可以等待清除的最大事务数。

其他 I/O 配置项

**sync_binlog**

选项控制MySQL如何将二进制日志刷新到磁盘，默认值是1，意味着MySQL将执行刷新并保持二进制日志的持久性和安全性。强烈推荐将其设置为1，不建议设置为任何其他值。

## 并发相关配置

旧版本的MySQL（低于5.7）面临许多高并发可伸缩性的挑战。所有的东西都在诸如缓冲池互斥锁之类的全局互斥锁上排队，导致服务器几乎停止运行。如果升级到较新版本的MySQL，在大多数情况下不需要限制并发性。

**innodb_thread_concurrency**

该变量限制了内核中同时可以有多少线程。
- 0表示对线程的数量没有限制。
- 建议设置为与可用CPU核数相同的值，然后根据需要调整大小。

**innodb_thread_sleep_delay**

如果内核中已经有超过允许数量的线程，则新的线程不能进入内核。InnoDB使用一个两阶段的过程来尝试让线程尽可能高效地进入内核。两阶段策略减少了操作系统调度器导致的上下文切换开销。线程首先休眠innodb_thread_sleep_delay指定的微秒数，然后再重试。如果仍然不能进入，它将进入一个等待线程队列，将控制权交给操作系统。

**innodb_concurrency_tickets**

一旦线程进入内核，InnoDB就有一定数量的“门票”，可以“免费”返回内核，而无须任何并发性检查。这限制了它在返回到其他等待的线程队列之前可以完成的工作量。innodb_concurrency_tickets选项控制“门票”的数量。除非有很多非常长时间运行的查询，否则很少需要更改这个选项。“门票”是根据查询而不是事务授予的。一旦查询完成，未使用的门票将被丢弃。

**innodb_commit_concurrency**

该变量控制着可以同时提交的线程数。表示允许同时提交任意数量的事务。

## 安全相关配置

**max_connect_errors**

相当于连接重试次数，超过后服务器将拒绝该客户端进行连接，除非刷新 Host Cache。如果启用了skip_name_resolve，则max_connect_errors选项将无效，因为其行为取决于主机缓存，而主机缓存被skip_name_resolve禁用。

**max_connections**

此设置类似于紧急刹车，以防止服务器被来自应用程序的大量连接压垮。注意观察随时间变化的max_used_connections状态变量。这是一个高水位线，可以显示服务器是否在某个时刻出现了连接高峰。如果到达max_connections，则客户端可能至少被拒绝了一次。

**skip_name_resolve**

当客户端连接服务器时，服务器会进行主机名检查，这需要反向DNS。如果DNS出现问题会出现连接超时。建议关闭该选项。

**sql_mode**

设置服务器SQL模式。模式会影响 MySQL 支持的 SQL 语法及其执行的数据验证检查。

**sysdate_is_now**

确保sysdate和now返回的日期是一样的。

**read_only和super_read_only**

read_only选项可防止未经授权的用户对副本进行更改，副本应仅通过复制而不是从应用程序接收更改。更严格的只读选项super_read_only，它甚至可阻止拥有SUPER权限的用户写入数据。启用此功能后，唯一可以将更改写入数据库的就是复制。

## 高级 InnoDB 配置

**innodb_autoinc_lock_mode**

参考[MySQL innodb_autoinc_lock_mode 详解](https://www.cnblogs.com/JiangLe/p/6362770.html)

**innodb_buffer_pool_instances**

在MySQL 5.5及更新的版本中，此设置将缓冲池划分为多个段，这可能是提高多核机器上MySQL在高并发工作负载下可伸缩性最重要的方法之一。多个缓冲池对工作负载进行分区，这样一些全局互斥体就不会成为争用热点。

**innodb_io_capacity**

参考[MYSQL io_capacity 哥俩，你调了吗？](https://cloud.tencent.com/developer/article/1596411)

**innodb_read_io_threads和innodb_write_io_threads**

这些选项控制有多少后台线程可用于I/O操作。

**innodb_strict_mode**

此设置使InnoDB在某些情况下抛出错误而不是警告，尤其是无效或可能导致危险的CREATE TABLE选项。

**innodb_old_blocks_time**

参考[Innodb存储引擎的几个小知识点](https://cloud.tencent.com/developer/article/1591792)

## 最小资源配置

自己也在低配置的 VPS 上用 Docker 搭建了 MySQL，以下是资源占用最小的配置，MySQL 版本为9.0，刚启动时内存占用150MB。

```ini
[mysqld]
default-time-zone='+08:00'

performance_schema=off

innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
max_connections=50
key_buffer_size=8
thread_cache_size=1
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000
table_open_cache=1000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8192
read_rnd_buffer_size=8192
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K

innodb_log_file_size=4194304
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
skip_name_resolve=ON
sync_binlog=0
skip-log-bin
```

MySQL 配置优化

本文是原书第1章的摘抄，大部分概念还是熟悉的，重点还是 InnoDB 本身。



## MySQL 逻辑架构

```mermaid
flowchart TB
	c[客户端] --> conn[连接/线程处理]
	
	subgraph server
	conn --> p[解析器]
	p --> o[优化器]
	end
	
	subgraph storage
	o --> s[存储引擎]
	end
```

连接管理与安全性
- 线程池
- 身份验证
- TLS

优化与执行
- 重写查询
- 表的读取顺序
- 选择合适的索引
- hint
- 从MySQL 5.7.20版本开始，查询缓存已经被官方标注为被弃用的特性，并在8.0版本中被完全移除。

## 并发控制

读写锁
- 共享锁（shared lock）/读锁（read lock）
- 排他锁（exclusive lock）/写锁（write lock）

锁的粒度
- 表锁（table lock）
- 行锁（row lock）
- 元数据锁，用于修改表名或者schema
- 8.0还引入了应用程序级别的锁

## 事务

**原子性（atomicity）**

一个事务必须被视为一个不可分割的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

**一致性（consistency）**

数据库总是从一个一致性状态转换到下一个一致性状态。如果事务最终没有提交，该事务所做的任何修改都不会被保存到数据库中。

**隔离性（isolation）**

通常来说（涉及隔离级别），一个事务所做的修改在最终提交以前，对其他事务是不可见的。

**持久性（durability）**

一旦提交，事务所做的修改就会被永久保存到数据库中。
- 持久性也分很多不同的级别。
- 不可能有100%的持久性保障。

### 隔离级别

**READ UNCOMMITTED（未提交读）**

在READ UNCOMMITTED级别，在事务中可以查看其他事务中还没有提交的修改。这个隔离级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他级别好太多，却缺乏其他级别的很多好处，除非有非常必要的理由，在实际应用中一般很少使用。

读取未提交的数据，也称为脏读（dirty read）。

**READ COMMITTED（提交读）**

READ COMMITTED满足前面提到的隔离性的简单定义：一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所做的任何修改对其他事务都是不可见的。这个级别仍然允许不可重复读（nonrepeatable read），这意味着同一事务中两次执行相同语句，可能会看到不同的数据结果。

大多数数据库系统的默认隔离级别是READ COMMITTED。

**REPEATABLE READ（可重复读）**

REPEATABLE READ解决了READ COMMITTED级别的不可重复读问题，保证了在同一个事务中多次读取相同行数据的结果是一样的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（phantom read）的问题。

所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（phantom row）。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

REPEATABLE READ是MySQL默认的事务隔离级别。

**SERIALIZABLE（可串行化）**

SERIALIZABLE是最高的隔离级别。该级别通过强制事务按序执行，使不同事务之间不可能产生冲突，从而解决了前面说的幻读问题。简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，除非需要严格确保数据安全且可以接受并发性能下降的结果。

ANSI SQL 的隔离级别

| 隔离级别             | 脏读  | 不可重复读 | 幻读  | 加锁读 |
| ---------------- | --- | ----- | --- | --- |
| READ UNCOMMITTED | 是   | 是     | 是   | 否   |
| READ COMMITTED   | 否   | 是     | 是   | 否   |
| REPEATABLE READ  | 否   | 否     | 是   | 否   |
| SERIALIZABLE     | 否   | 否     | 否   | 是   |

### 死锁

死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时，也可能会发生死锁。

```sql
-- 事务1
START TRANSACTION;
UPDATE StockPrice 
SET close = 45.50 WHERE stock_id = 4 and date = '2020-05-01';UPDATE StockPrice 
SET close = 19.80 WHERE stock_id = 3 and date = '2020-05-02';COMMIT;

-- 事务2
START TRANSACTION;
UPDATE StockPrice 
SET high = 20.12 WHERE stock_id = 3 and date = '2020-05-02';UPDATE StockPrice 
SET high = 47.20 WHERE stock_id = 4 and date = '2020-05-01';COMMIT;
```

InnoDB 检测到循环依赖后会立即返回错误，或者超过锁等待超时时间限制后直接终止查询（回滚）。应用程序在设计时需要考虑处理死锁，大多数情况下只需要重新从头开始执行被回滚的事务即可。

### 事务日志

事务日志用于提高事务的效率。

存储引擎更新数据的顺序是：
```mermaid
graph LR;
	mem[内存] --> tlog[事务日志] --> hdd[磁盘];
```

存储引擎只需要更改内存中的数据副本，而不用每次修改磁盘中的表，这会非常快。然后再把更改的记录写入事务日志中，事务日志会被持久化保存在硬盘上。

因为事务日志采用的是追加写操作，是在硬盘中一小块区域内的顺序I/O，而不是需要写多个地方的随机I/O，所以写入事务日志是一种相对较快的操作。

最后会有一个后台进程在某个时间去更新硬盘中的表。因此，大多数使用这种技术（write-ahead logging，预写式日志）的存储引擎修改数据最终需要写入磁盘两次。

### AUTOCOMMIT

默认情况下，单个INSERT、UPDATE或DELETE语句会被隐式包装在一个事务中并在执行成功后立即提交，这称为自动提交（AUTOCOMMIT）模式。通过禁用此模式，可以在事务中执行一系列语句，并在结束时执行COMMIT提交事务或ROLLBACK回滚事务。

如果设置了AUTOCOMMIT=0，则当前连接总是会处于某个事务中，直到发出COMMIT或者ROLLBACK，然后MySQL会立即启动一个新的事务。

还有一些命令，当在活动的事务中发出时，会导致MySQL在事务的所有语句执行完毕前提交当前事务。这些通常是进行重大更改的DDL命令，如ALTER TABLE，但LOCK TABLES和其他一些语句也具有同样的效果。

```sql
-- 设置 autocommit
SET SESSION AUTOCOMMIT=0;
SET GLOBAL AUTOCOMMIT=0

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

### 在事务中混合使用存储引擎

MySQL不在服务器层管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，混合使用多种存储引擎是不可靠的。

### 隐式锁定和显式锁定

InnoDB使用两阶段锁定协议（two-phase locking protocol）。在事务执行期间，随时都可以获取锁，但锁只有在提交或回滚后才会释放，并且所有的锁会同时释放。前面描述的锁定机制都是隐式的。InnoDB会根据隔离级别自动处理锁。

显示锁定
```sql
SELECT ... FOR SHARE -- MySQL8.0的新语句
-- 取代以前的 SELECT...LOCK IN SHARE MODE
SELECT ... FOR UPDATE
```

MySQL还支持LOCK TABLES和UNLOCK TABLES命令，这些命令在服务器级别而不在存储引擎中实现。建议除了在禁用AUTOCOMMIT的事务中可以使用之外，其他任何时候都不要显式地执行LOCK TABLES，不管使用的是什么存储引擎。

## 多版本并发控制

MySQL 的大多数事务型存储引擎都不是简单的使用行级锁，而是和 MVCC 结合使用。MVCC 的实现机制各不相同。MVCC 可以视为行级锁的变种，开销更低，实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的工作原理是使用数据在某个时间点的快照来实现的。MVCC 的实现变体包括乐观并发控制和悲观并发控制

MVCC仅适用于REPEATABLE READ和READ COMMITTED隔离级别。READ UNCOMMITTED与MVCC不兼容，是因为查询不会读取适合其事务版本的行版本，而是不管怎样都读最新版本。SERIALIZABLE与MVCC也不兼容，是因为读取会锁定它们返回的每一行。

## 复制

```mermaid
flowchart TB
	s[源节点] --二进制日志--> r1[副本节点1]
	s --二进制日志--> r2[副本节点2]
```

MySQL提供了一种原生方式来将一个节点执行的写操作分发到其他节点，这被称为复制。对于在生产环境中运行的任何数据，都应该使用复制并至少有三个以上的副本，理想情况下应该分布在不同的地区（在云托管环境中，称为region）用于灾难恢复计划。

多年来，MySQL中的复制变得十分复杂。全局事务标识符、多源复制、副本上的并行复制和半同步复制是一些主要的更新。

## 数据文件结构

在8.0版本中，MySQL将表的元数据重新设计为一种数据字典，包含在表的.ibd文件中。这使得表结构上的信息支持事务和原子级数据定义更改。

在操作期间，我们不再仅仅依赖information_schema来检索表定义和元数据，而是引入了字典对象缓存，减少 I/O。每个表的.ibd和.frm文件被替换为已经被序列化的字典信息（.sdi）。

## InnoDB引擎

InnoDB是MySQL的默认事务型存储引擎，它是为处理大量短期事务而设计的。
- InnoDB将数据存储在一系列的数据文件中，这些文件统被称为表空间（tablespace）。
- InnoDB使用MVCC来实现高并发性，并实现了所有4个SQL标准隔离级别。InnoDB默认为REPEATABLE READ隔离级别。
- 通过间隙锁（next-key locking）策略来防止在这个隔离级别上的幻读：InnoDB不只锁定在查询中涉及的行，还会对索引结构中的间隙进行锁定，以防止幻行被插入。

InnoDB 内部做的优化
- 从磁盘预取数据的可预测性预读
- 能够自动在内存中构建哈希索引以进行快速查找的自适应哈希索引（adaptive hash index）
- 用于加速插入操作的插入缓冲区（insert buffer）

作为事务型存储引擎，InnoDB可以通过一些机制和工具支持真正的在线“热”备份，包括Oracle专有的MySQL Enterprise Backup和开源的Percona XtraBackup。

从MySQL 5.6开始，InnoDB引入了在线DDL，它最初只支持有限的使用场景，但在5.7和8.0版本中引入了就地更改schema机制，允许在不使用完整表锁和外部工具的情况下进行特定的表更改操作，这大大提高了MySQL InnoDB表的可操作性。

### JSON 文档支持

JSON类型在5.7版本被首次引入InnoDB，它实现了JSON文档的自动验证，并优化了存储以允许快速读取。

InnoDB还引入了SQL函数来支持在JSON文档上的丰富操作。MySQL 8.0.7的进一步改进增加了在JSON数组上定义多值索引的能力。将常用访问模式匹配到可以映射JSON文档值的函数这一特性可以进一步加快对JSON类型的读取访问查询。

### 数据字典的变化

MySQL 8.0删除了基于文件的表元数据存储，并将其转移到使用InnoDB表存储的数据字典中。这给所有类似修改表结构这样的操作带来了InnoDB的崩溃恢复事务的好处。

### 原子 DDL

MySQL 8.0引入了原子数据定义更改。这意味着数据定义语句现在要么全部成功完成，要么全部失败回滚。这是通过创建DDL特定的Undo日志和Redo日志来实现的

## 参考

- [17.7 InnoDB Locking and Transaction Model](https://dev.mysql.com/doc/refman/8.4/en/innodb-locking-transaction-model.html)

MySQL 基本概念

我是个 CURD 工程师，属于第五等工程师。



## 什么是工程师文化

先看一些特征

### 地位高

一种职业受人尊敬，人为地强调它的地位高是没有用的，必须要有一种机制保证他们的收入很高，这样最优秀的人才有动力从事相应的工作。

2013年硅谷和美国主要IT公司软件工程师的基本收入与其他行业从业人员收入的对比
![2013年硅谷和美国主要IT公司软件工程师的基本收入与其他行业从业人员收入的对比](https://s2.loli.net/2024/08/17/vPpn5YdgI6KzM4L.png)

### 工作稳定

工程师的工作比较稳定，公司裁员时最后才可能裁撤到工程师。另外，他们的工作时间比较灵活，每年的休假比较多，很多人愿意做一辈子工程师，这使得美国公司里一线工程师的平均工龄比中国公司里同样职位的工程师要长很多，经验也自然更丰富。这一点在硅谷的公司里更加明显。

在硅谷的另一些公司，比如Facebook和苹果里，虽然看上去好的产品经理地位最高，其实他们大多是工程师出身。而在半导体公司，基本上只能让工程师们说了算，因为半导体相关的专业技术非专业人士看不懂，甚至半导体的销售常常也需要由主管工程的副总裁负责。

### Mens et manus

- 一个合格的工程师，必须能够自己动手解决问题，而不是让别人告诉他应该怎么做。
- 一个合格的工程师，至少应该能够独立地实现一项工程目标，不论目标大小。
- 与科学家不同，工程师不关心那些理论的东西，不会在遇到问题时从研究理论做起，也不会站在那里等到某个理论问题解决了之后才开始动手，而是在理论问题还没有解决时，设法绕过去，搞定实际问题。
- 灵活运用现有技术。
- 对新技术有非常强的好奇心，愿意把新技术运用到现有产品中。

改变世界的技术和最初发明的地点

| 以为是来自硅谷的发明 | 实际发明地点 |
| --- | --- |
|晶体管|贝尔实验室（新泽西）|
|计算机|宾夕法尼亚大学|
|机器人|英国|
|人工智能|美国东部大学（在达特茅斯提出，MIT 最初实现）|
|程序语言|英国|
|数据库|IBM 等|
|计算机游戏|MIT|
|游戏机|新罕布什尔|
|互联网|美国政府ARPA|
|PC|好利获得公司（即Olivetti，意大利）|
|WWW|欧洲核子中心（CERN）|
|搜索引擎|欧洲核子中心以及美国加州以外几所大学|
|社交网络|纽约|
|智能手机|日本Docomo（NTT的移动子公司）|
|电子邮件|MIT|
|可穿戴式计算机|MIT|
|太空技术|德国、苏联、美国的得克萨斯和洛杉矶等地|
|电动汽车|早期版本：爱迪生GE（纽约）；当代版原型：GM（底特律）|
|无人驾驶汽车|卡内基–梅隆大学|
|虚拟现实（VR）|MIT|

### 会动脑

硅谷工程师文化另一个重要特点是不仅要会动手，更要会动脑。

在很多公司，产品经理和工程师是两个独立的角色。在中国的一些互联网公司里，有着大量的产品经理，在个别项目上产品经理甚至比工程师还多。这样的搭配带来了两个方面的恶果，首先很多产品经理不得不去做那些本不该由他们来完成的细节设计，同时本该参与产品设计的工程师在设计上完全懒得动脑思考。

另外，产品经理可能会因为缺乏对工程技术的了解而提出不合理的要求，而工程师也会因为缺乏对产品的全面了解，做的东西达不到产品经理预想的要求。在硅谷的公司里，产品经理和工程师的比例非常低，在Google这样的公司或者半导体公司里，这个比例可能是1：20，甚至比1：100还低。

### 区别对待不同水平的工程师

苏联著名物理学家朗道（Lev Landau，1908—1968）提出的朗道等级能够比较清晰的解释工程师之间的差别。

工程师的五个等级
![工程师的五个等级](https://s2.loli.net/2024/08/17/7TbYWvsHuqO58PF.png)

第五等工程师，能够独立设计和实现一项功能的人。这是对工程师的基本要求，如果一个人只是懂一点工程实现的手段，需要别人告诉他怎么做，那最多算是助理工程师或者技工，不在我们讨论的工程师之列。

第四等的工程师就需要有点产品头脑，他们在做一件事之前，要知道所做出来的东西是否有用、易用，是否便于维护，是否性能稳定，等等。除了要具备产品设计方面的基本知识，还要具有一定的领导才能，能在整个产品的生命周期从头到尾将一个产品负责到底。这在很多硅谷的公司里，基本上是一个高级工程师所应有的基本素质。

第三等的工程师，可以做出行业里最好的产品。他们与第四等工程师有着质的差别，这不仅反映在技术水平、对市场的了解、对用户心理的了解以及组织能力等诸方面，而且也反映在悟性的差异上。

第二等的工程师是那些可以给世界带来惊喜的人。他们与第三四五等工程师的差别在于其工作的原创性以及对世界的影响力。当然，他们的工作不是科学研究，这一点和科学家毕竟不同。

第一等的工程师是开创一个全新行业的人。这些工程师不仅在技术和产品等各个方向上与第二等的工程师有了质的差别，而且在经验和管理上也是好手，他们通常也是企业家，并通过自己的产品改变了世界。

### 全社会对工程师的认可

这种认可不仅包括对工程师的尊重，以及给予他们较高的社会地位，还包括允许不同等级的工程师在收入上有巨大差异。如果拉大收入差距能够最大程度地发展生产力，而平均主义不能，那么采用前一种分配方式的地区无疑会获得更快的发展，硅谷就是这样的地区。

在硅谷的很多公司内部，不同层级、不同贡献的工程师之间，工资和奖金的差异其实不大，但是期权起到了调节他们收入的主要作用。这使得第二等工程师的收入真的可以比第三等工程师多出一个数量级，而第三等和第四等之间，收入可能也有数量级之差。

### 工程师文化的明确定义

简单地讲，工程师文化有两层含义，首先在于，它强调了一个机构或者一个社会以工程师为核心创造未来的价值。接下来，既然工程师是创造价值的核心，那么他们必须具有主动性，不断承担越来越多的使命。由于人的能力有大有小，贡献会有巨大的差距，这样也会直接导致他们的收入有巨大差距。只有正视这种差距，接受这种差距，才是信息时代应该有的思维。

## 硅谷工程师文化的形成原因

从历史上讲，欧美国家在历史上很少有大一统的帝国，官员的重要性就要小得多，专业人士的地位相对比较高。世界上很多国家和地区对工程师远比中国尊重和重视。

硅谷工程师文化也有它本身的成因，主要是公司所有权和分配制度与其他地方有所不同。员工特别是核心员工和公司都是一种契约关系，因此行政职务和汇报关系就不是那么重要了，但是这一点在中国目前还做不到。

## 参考

- [什么是工程师文化？](https://coolshell.cn/articles/17497.html)
- [Programmer's Day](https://en.wikipedia.org/wiki/Programmer%27s_Day)
- [33 | Facebook企业文化：工程师文化是创造力引擎](https://time.geekbang.org/column/article/162869)