MySQL详解

0. 目录

0. 目录
1. MySQL组件结构
2. 日志
- 2.1 日志介绍
- 2.2 两阶段提交
  - 2.2.1 两阶段提交必要性
  - 2.2.2 两阶段提交redolog和binlog的关联原理
3. 事务隔离
4. 索引
5. explain结果解析
6. 锁
7. 慢查询
- 7.1 开启方式
- 7.2 字段分析
8. 缓存
9. Join操作性能解析
10. 分页查询问题
11. MySQL的LRU算法优化

1. MySQL组件结构

MySQL分为Server层和存储引擎层。
其中Server层是MySQL自有组件，所有跨存储引擎的功能都在此处实现，如存储过程/触发器/视图等。
存储引擎层则提供了统一接口，具体引擎插件式可选。

1.1 连接器

负责客户端的连接建立/登录验证/权限查询/连接管理等。

MySQL8.0默认长连接，超时后连接器会主动断开连接。此时客户端使用会提示 Lost connection to MySQL server during query。
MySQL长连接需要定期断开重连或者使用mysql_reset_connection初始化资源，避免长连接中的临时对象持续占用内存。

1.2 分析器

针对SQL语句做词法分析和语法分析。若不符合语法，会提示You have an error in your SQL syntax。

1.3 优化器

负责选择索引或者join时表的连接顺序，以求更好的查询效率。

1.4 执行器

执行前针对SQL涉及的表进行权限验证。
调用存储引擎接口执行操作。

2. 日志

Mysql默认使用InnoDB引擎，故有binlog日志及redolog日志。

以update T set c=c+1 where ID=2为例，过程如下。

2.1 日志介绍

数据的实际落盘实际在流程图之后，所以其实redolog和binlog都是写前日志。

写前日志（WAL）

写前日志指的是在实际数据修改之前，先将相应的修改操作记录到日志文件中的一种策略。

关键点：

记录先于修改：在执行实际的数据修改操作之前，相关的修改操作被记录到一个日志文件中。这确保了修改的记录在数据实际写入磁盘之前已经被持久化。
持久性保证：通过写前日志，即使在数据实际写入磁盘之前系统崩溃，可以通过重新应用日志来还原数据。这提供了数据的持久性保证。
事务一致性：写前日志通常与事务处理系统一起使用，确保事务的原子性、一致性、隔离性和持久性（ACID属性）。在提交事务之前，相关的修改操作先被写入日志，然后才被写入实际的数据文件。
崩溃恢复：写前日志是数据库系统进行崩溃恢复的基础。在系统重新启动时，可以通过重新应用写前日志来还原到最后一致性状态。

2.1.1 redolog

记录内容

redolog记录数据的物理修改：如表空间号/数据页号/偏移量/修改值等。

写入时机

redo log在事务执行过程中不断写入，属于图中redolog prepare状态。

作用

用于保证持久性和崩溃恢复。

日志大小

redolog的文件大小固定，循环写日志。
比如MySQL8.0中，redolog默认大小128MB，2个文件。InnoDB会循环地写这两个文件。

# 默认 Redo Log 文件大小
innodb_log_file_size = 128M
# 默认 Redo Log 文件数量
innodb_log_files_in_group = 2

工作原理

write pos是写入数据还未落盘的位置，check point是已落盘数据的位置。故，绿色是可写入的日志大小，黄色是写入redolog待落盘的数据大小。
当write pos追上check point一圈时，说明此时redolog空间已耗尽，需要阻塞等待redolog数据落盘。

2.1.2 binlog

记录内容

binlog记录数据的逻辑修改：statement 格式记录sql语句；row格式会记录行的内容，包括更新前和更新后。

写入时间

提交事务时才写入。

作用

用于逻辑备份、复制和分析。

日志大小

binlog可以追加写入，一定大小后会切到下一个文件。

2.2 两阶段提交

2.2.1 两阶段提交必要性

由于binlog和redolog需要同时使用，所以需要保证两个日志的一致性。

假设不同时间点实例崩溃，来说明两阶段提交的可靠性：

redolog prepare完成，binlog未完成
崩溃恢复发现binlog中无对应事务，会回滚redolog。
redolog prepare完成，binlog完成
崩溃恢复发现binlog中有对应事务，会将redolog对应事务状态改为commit，恢复数据。
redolog commit完成，binlog完成
崩溃恢复直接恢复数据。

2.2.2 两阶段提交redolog和binlog的关联原理

redolog和binlog有一个共同的数据字段XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

3. 事务隔离

事务特性ACID

Atomicity 原子性
Consistency 一致性
Isolation 隔离性
Durability 持久性

3.1 事务隔离级别

读未提交
事务还没提交时，其变更就能被其他事物看到。
读提交
事务提交后，其变更就能被其他事物看到。
可重复读
事务执行过程中看到的数据，总跟这个事务在启动看到的数据一致。
串行化
对于同一行记录，写会加写锁，读会加读锁。若事务冲突便阻塞等待前一个事务将锁释放后再执行。

举个例子。在不同隔离级别下，V1/V2/V3读出来的值不相同。

读未提交：V1=2，V2=2，V3=2
读提交：V1=1，V2=2，V3=2
可重复读：V1=1，V2=1，V3=2
串行化：V1=1，V2=1，V3=2。 V2也是1的原因是，事务A获取了读锁，会阻塞事务B的[将1改成2]动作，因为事务B需要获取写锁。

3.2 视图机制

针对RC和RR，事务会创建视图，访问的时候以视图的逻辑结果为准。

RU：没有视图的概念，直接返回最小行数据
RC：每个select都会创建最新的ReadView
RR：事务中的第一个select请求才创建ReadView
Serial：通过锁来实现数据访问，没有视图的概念

3.3 MVCC实现原理

3.3.1 相关概念

先了解一下MVCC的相关其他概念。

3.3.1.1 事务ID

事务每次开启时，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。

3.3.1.2 隐藏字段

InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer及一个可能的隐藏列row_id。

trx_id
记录操作该行数据事务的事务ID
roll_pointer
回滚指针，指向当前记录行的undo log信息
row_id
单调递增的行ID，占用6个字节。表中不存在主键及非NULL的UNIQUE键时便会自动创建作为隐藏主键。

其中，trx_id和roll_pointer都和MVCC实现有关。

3.3.1.3 undo log

回滚日志，存储的老版本数据。

表记录修改之前，会先把原始数据拷贝到undo log里，如果事务回滚，即可以通过undo log来还原数据。或者如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。

InnoDB对undo log的管理采用段的方式，也就是回滚段（rollback segment）。每个回滚段记录了 1024 个 undo log segment ，每个事务只会使用一个回滚段，当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段。

生成时间点

insert/update/delete(本质也是update，只是更新一个特殊的删除位字段)操作时，都会产生undo log。

MySQL处于性能考虑，数据会优先从磁盘加载到Buffer Pool中，在更新Buffer Pool中数据之前，会优先将数据记录到undo log中。
记录undo log日志，MySQL不会直接去往磁盘中的xx.ibdata文件写数据，而是会写在undo_log_buffer缓冲区中，因为工作线程直接去写磁盘太影响效率了，写进缓冲区后会由后台线程去刷写磁盘。

删除时间点

undo log作用于事务，所以删除时间点也和事务有关。

针对于insert undo log，因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log在事务提交后就没有用，就会直接删除。
针对于update undo log，该undo log需要支持MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，有专门的purge线程进行删除。当没有事务引用对应版本的undo log后，便会清理。

类型

insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被删除。

作用

事务回滚时，保证原子性和一致性。
如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本(用于MVCC快照读)。

版本链

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表。

具体参考undo log多版本链实现ReadView机制

3.3.1.4 MVCC视图定义

启动事务的瞬间，将所有活跃事务ID加到活跃事务数组中。其中

低水位：数组里面事务 ID 的最小值
高水位：当前系统里面已经创建过的事务 ID 的最大值加 1

这个视图数组和高水位，就组成了当前事务的一致性视图。

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况：
- a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
- b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

3.3.1.5 长事务

缺点

长事务会导致系统存在古老视图，提交前其undolog都必须保留，会占用大量存储空间。
长事务会导致线程占用的内存过大
长事务会持续性地占用MDL读锁，若是遇到修改表结构等获取MDL写锁操作会将其阻塞，写锁又会阻塞后续读请求的MDL读锁获取，导致MySQL卡死。

事务启动方式

显式启动事务语句
需要手动提交。
begin 或 start transaction。回滚语句是 rollback；执行commit 则提交事务；执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销，同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。
非显式开启的事务
需要看autocommit参数，0需要手动提交，1自动提交。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
建议使用set autocommit=1，且通过显式语句的方式来启动事务。

查询长事务

information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

长事务处理

通过information_schema.innodb_trx表监控事务的持续时间
增加undo表空间
通过配置参数max_execution_time指定事务执行的最长时间
利用pt工具监控长事务。

4. 索引

4.1 索引数据结构

索引的数据结构是由存储引擎层实现的，故没有统一的索引标准。以InnoDB引擎为例。

create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

结果如图。id为主键索引，k为普通索引，都分别对应一颗B+树。其中，主键B+树叶子节点存储的是对应行数据，索引树叶子节点存储的是主键值。

索引选择B+树的原因

可以对比下常见的几种索引数据结构，可以发现为了减少磁盘的访问次数，B+树最为合适。

哈希表
优点：适合等值查询O(1）及更新
缺点：不适合范围查询
有序数组
优点：适合等值查询（二分法O(log(N))）和范围查询（二分法找到起始位置向后遍历到结束位置）
缺点：不适合更新，只适用于静态存储引擎
二叉搜索树
优点：更新和查询都是O(log(N))
缺点：一百万节点树高就20。机械硬盘时代，随机读一个数据块需要10ms，20个需要200ms。
N叉搜索树
以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。
B+树
在N叉树的基础上，叶子节点新增了一个指向下一个叶子节点的指针，可以支持范围遍历。

4.2 索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。

4.2.1 随机插入数据带来的页分裂问题

以上面这个图为例。
如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。

页分裂负面影响

需要申请数据页，并迁移部分数据，效率降低
页分裂后，涉及的新旧两个页面空间利用率也降低了差不多50%。（特别是如果有序插入，InnoDB只会保留10%空间用作冗余）

4.2.2 自增主键

插入数据时，尽量选择自增主键。

单机

针对于单机MYSQL来说，使用NOT NULL PRIMARY KEY AUTO_INCREMENT便是不错的选择。

AUTO_INCREMENT插入主键不连续的原因：

唯一键冲突插入失败
事务中插入后回滚
批量插入，执行插入sql时会按照2的n次方批量申请。若申请的值未能刚好用完，后续再插入时会导致不连续。

分库分表

由于分库分表后，主键ID可能重复。若是主键是业务数据的一部分，会造成问题，需要分布式ID。
分布式ID的生成方式常见有：

UUID（不推荐）字符串类型（（不含连接符）32个字符/128位/16字节：32个16进制的字母数字及4个连接符-），无法有序递增
1. 获取当前时间戳和机器的 MAC 地址；
2. 将当前时间戳转换为 UTC 时间，并计算出自 1582 年 10 月 15 日午夜（即格林威治标准时间 0 点）以来的纳秒数，将其存储在 UUID 的时间戳字段中；
3. 将机器的 MAC 地址哈希得到其中的 6 个字节作为 UUID 的节点字段；
4. 随机生成两个字节作为 UUID 的时钟序列字段；
5. 将时间戳、节点、时钟序列等信息组合起来，生成 UUID。
雪花算法（推荐）数字类型（64位/8字节），有序趋势递增（非严格，因为工作机器id原因）
- 第一位
  占用1bit，其值始终是0，没有实际作用。
- 时间戳
  占用41bit，精确到毫秒，总共可以容纳约69年的时间。
- 工作机器id
  占用10bit，其中高位5bit是数据中心ID，低位5bit是工作节点ID，做多可以容纳1024个节点。工作节点ID可以考虑使用ZK的临时顺序节点自动编号。
- 序列号
  占用12bit，每个节点每毫秒0开始不断累加，最多可以累加到4095，一共可以产生4096个ID。
防止时间回退导致的ID重复问题，可以针对不同工作节点对应的ZK临时节点存储对应的数据戳。在生成下一个ID时，判断下当前时间与上次时间的大小。

4.3 索引机制

利用覆盖索引、最左索引前缀、索引下推可以更好的优化索引效率及空间占用。

4.3.1 覆盖索引

索引已经覆盖了查询需求，不需要回表。
覆盖索引可以减少树的搜索次数，显著提升查询性能。

4.3.2 最左前缀原则

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
假设name列是索引列，使用模糊查询"name=张%"可以走索引，"%张"不能走索引的原因也在此。

4.3.2 索引下推

在执行查询时，将部分过滤条件推送到存储引擎层，减少数据的读取和传输，从而提高查询性能。
下推便是将部分过滤操作从server层推到存储引擎层，减少回表次数。

假设查询select * from user where name like '张%' and age=10 and ismale=1;。
无索引下推。

有索引下推。

可以看到，索引下推需要联合索引才能生效，不然索引树没有办法直接过滤。若无索引下推，需要回表4次，有了后只需要回表2次。

4.4 聚簇索引和非聚簇索引

4.4.1 聚簇索引（Clustered Index）：

定义
聚簇索引决定了表的物理排序方式，并将表的数据行存储在索引的叶子节点上。实际上，聚簇索引就是按照索引的顺序来组织表的数据。
特点
一个表只能有一个聚簇索引，因为表的数据行实际上就是按照聚簇索引的顺序存储的。
表的物理排序与聚簇索引的排序一致，因此聚簇索引对于范围查询和排序操作的性能提升较大。
由于数据行的物理存储顺序和聚簇索引一致，插入新数据可能导致数据页的分裂，影响性能。

4.2 非聚簇索引（Non-Clustered Index）：

定义
非聚簇索引将索引的键值与实际数据行的位置分开存储，索引的叶子节点保存了对应数据行的指针。
特点
一个表可以有多个非聚簇索引，每个非聚簇索引都独立于表的物理存储结构。
查询时，首先通过非聚簇索引找到对应数据行的位置，然后再通过指针访问实际数据行。
非聚簇索引对于范围查询和排序操作的性能可能较差，因为它需要在索引和数据之间跳跃。

5. explain结果解析

针对SQL语句，可以利用explain预分析实际执行的性能。

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4

5.1. Extra

Extra	含义
Using index	查询只需要使用索引就可以获取所需的数据，不需要回表操作。这通常是性能较好的情况。
Using where	查询使用了 WHERE 子句来过滤结果集。查询将先根据索引进行扫描，然后再使用 WHERE 条件过滤结果。
Using index condition	表示使用了索引下推。
Using temporary	查询需要使用临时表来存储中间结果。这通常意味着查询需要进行复杂的计算或操作，可能需要更多的资源。
Using filesort	查询需要对结果集进行排序操作，并且无法使用索引进行排序。这时，MySQL 将使用文件排序算法来对结果进行排序，可能需要更多的资源。
Using join buffer (Block Nested Loop)	表示查询中使用了连接操作，并且连接操作使用了连接缓冲区（join buffer）。这通常是性能较差的情况，可能需要优化查询语句或表结构。
Impossible WHERE	表示查询的 WHERE 子句的条件永远不可能为真，因此查询不会返回任何结果。
Select tables optimized away	表示查询不需要访问任何表，因为可以通过优化直接得到结果。

5.2 Type

Type	含义
ALL	全表扫描，性能最差。
index	全索引扫描，比全表扫描好一些，但性能仍然较差。
range	范围扫描，使用索引的一部分来查找符合条件的行。
index_subquery	使用子查询在索引中查找符合条件的行。
unique_subquery	类似于 index_subquery，但是在唯一索引中查找。
index_merge	合并多个索引的结果来查找符合条件的行。
ref_or_null	类似于 ref，但是可以查找值为 NULL 的行。
fulltext	全文搜索，使用全文索引来查找符合条件的行。
ref	使用索引来查找符合条件的行，性能较好。
eq_ref	类似于 ref，但是只查找唯一匹配的行。
const	只查找一个符合条件的行，性能最优。
system	系统表，只有一行，性能最优。

5.3 Ref

Ref	含义
const	使用一个常量值进行查找。这通常发生在主键或唯一索引的查找中，因为主键和唯一索引的值在表中是唯一的。
func	使用函数或表达式进行查找。这通常发生在对索引列应用函数或表达式时。
NULL	没有使用索引进行查找。这可能是因为查询中没有引用索引列，或者使用了全表扫描。
index	使用了覆盖索引（Covering Index）。在这种情况下，查询只需要访问索引，而不需要访问表中的数据行。
Column/Constant Value	使用该列或常量值进行查找。这通常发生在非主键或非唯一索引的查找中，因为这些索引的值可能不是唯一的。

5.4 rows

表示MySQL估算需要扫描的行数。
这是一个估计值。这个数值越小越好，因为扫描的行数越少，查询的性能就可能越高。
例如，如果rows的值为1000，那么MySQL估计需要扫描1000行数据以获取查询结果。

5.5 filtered

表示符合查询条件的数据所占的百分比。
这是一个估计值，表示在扫描的行数中，有多少行的数据满足查询的条件。这个数值越大越好，因为这意味着查询只需要处理更少的数据。例如，如果rows的值为1000，filtered的值为50%，那么估计有500行数据满足查询的条件。

结合rows和filtered字段的值，可以更具体了解查询的性能。
如果rows的值很小，同时filtered的值很大，那么查询的性能可能会比较高。相反，如果rows的值很大，同时filtered的值很小，那么查询的性能可能会比较低，可能需要考虑优化查询语句或表结构。

6. 锁

具体的锁查看锁：读写锁、全局锁、表锁、行锁及临键锁。

7. 慢查询

7.1 开启方式

set global slow_query_log=1;
set long_query_time=0;

7.2 字段分析

字段	示例值
Time	2023-10-02T04:58:36.987985
User@Host	root@localhost
Id	11
Query_time	0.008770
Lock_time	0.000011
Rows_sent	3
Rows_examined	10003
SET timestamp	1696222716
SQL查询语句	select word from words order by rand() limit 3;

8. 缓存

InnoDB Buffer Pool、Change Buffer、Redolog Buffer、Join Buffer、Sort Buffer

8.1 InnoDB Buffer Pool

缓存表和索引的热点数据页，包括聚簇索引和辅助索引，提升访问速度。

8.2 Change Buffer

Change Buffer属于InnoDB Buffer Pool中的一块缓存，用于提升写多读少情况下，提升普通索引（非唯一，因为唯一性索引需要读磁盘到内存检查唯一性）的处理效率。

假设执行insert into t(id,k) values(id1,k1),(id2,k2),id为主键、k为普通索引。那么InnoDB Buffer Pool、Change Buffer及Redolog Buffer之间的关系如图。

使用change buffer的时候，redolog中记录的则是数据添加到了change buffer中。

虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

8.2.1 Change Buffer和Redo Log的区别

参考Change Buffer 与 Redo Log的区别

8.3 Redolog Buffer

事务执行的过程中，便需要持续性地写入redolog，但是这个log在没有commit前不能落到磁盘中的redolog文件中，所以需要缓存到Redolog Buffer里。当事务执行commit时，才会启动事务的二阶段提交，走redolog prepare - binlog - redolog commit流程。

8.4 Join Buffer

线程缓存join操作过程的中间结果，减少磁盘IO和提升join操作性能。具体作用参考第9节的Join操作解析。

8.5 Sort Buffer

线程用于排序使用的缓存。
explain中的Extra字段是 “Using filesort”，表示需要排序。排序根据所需的内存和sort_buffer大小决定在内存中完成，还是使用外部排序（利用磁盘临时文件辅助归并排序算法）。

9. Join操作性能解析

不同的驱动表及等值字段选择，会导致优化器选择不同的join算法，而不同的算法之间效率不一。
常见的join算法有Simple Nested-Loop Join/Block Nested-Loop Join/Index Nested-Loop Join/Batched Key Access/Hash Join，尽量不让SQL走Simple Nested-Loop Join/Block Nested-Loop Join这两个效率低下的算法。

先给出2个表用作举例。

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;
create table t2 like t1;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1000 - i, i);
    set i=i+1;
  end while;
  set i=1;
  while(i<=100)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

t1表和t2表均有3个字段：id（主键）a（索引）b（普通字段）。
t1表插入了1000条数据，其中a的值是从1000递减到1，其他都是从1递增到1000；t2表插入了100条数据，均是从1递增到1000。

straight_join:左边的表为驱动表，右边的表为被驱动表，为了固定驱动顺序方便举例。

9.1 Simple Nested-Loop Join

SQL:select * from t1 straight_join t2 on (t1.b=t2.b);

简单嵌套循环算法：先查出驱动表的所有记录，针对每行驱动表的记录都去查询一边被驱动表的所有记录进行对比，匹配便加入结果集。

按照Simple Nested-Loop Join的算法，会查询1次t1表1000行记录，遍历t1表的每行记录时都会去查询t2表的100行记录，总共会扫描1000 * 100 = 100000次记录，并对比1000 * 100 = 100000次记录。

9.2 Block Nested-Loop Join（BNL）

SQL:select * from t1 straight_join t2 on (t1.b=t2.b);

9.2.1 BNL算法介绍

BNL算法是利用Join Buffer对Simple Nested-Loop Join进行的优化，减少了扫表的行数。

块嵌套循环算法：

扫描t1表的全部数据加入到Join Buffer中。若是 Join Buffer大小不足，便分多次部分加载，这便是Block。
针对t1表数据的每一个块，都需要扫描t2表的全部数据。每扫描一行便将其与Join Buffer中t1表的数据进行对比，匹配便加入结果集。

9.2.2 BNL效率分析

假设驱动表行数为M，被驱动表行数为N，Join Buffer支持的缓存最大行数为Z，那么扫描总行数为 M+M/Z*N。
故：

若是Join Buffer足够，那么大表小表谁当驱动表都一样。
若是Join Buffer不足，那么小表当驱动表扫描的行数会少点。

按照当前例子来看，若Join Buffer足够扫描1000+100=1100行，若Join Buffer假设最大支持600行需要（600 + 100）+ （400 + 100）= 1200行扫描。对比次数依然是（600 + 400） * 100 = 100000次，没有优化。

这里的小表大表指的不是表中的所有数据大小，而是参与join的结果集大小。

9.3 Index Nested-Loop Join（NLJ）

SQL:select * from t1 straight_join t2 on (t1.a=t2.a);

9.3.1 NLJ算法介绍

索引嵌套循环连接算法：连接的字段为被驱动表的索引。
过程成：

扫描t1的全部数据。
每扫描t1的一行，便通过t2表的索引查询，若匹配便加入结果集。

9.4.1 NLJ效率分析

针对驱动表，走全表扫描M行。针对被驱动表，走索引查询，效率为O(log N)。那么总体扫描行为M + 2MO(log N)。（2是因为要回表），对比次数则为M + N，同时优化了扫描次数及对比次数。
很明显，小表作为驱动表带来的收益是巨大的。

9.4 Batched Key Access（BKA）

BKA算法是利用Join Buffer对NLJ算法在某些场景下进行的优化处理，但是依赖MRR算法，先介绍下MRR算法。

9.4.1 Multi-Range Read（MRR）

select * from t1 where a>=1 and a<=100;
此时t1表的a列值是1000到1，而id值是1到1000。普通sql查询的结果集默认按照a列排序，那么a列为1到100，聚簇索引id列就成了100到1，成了随机IO回表。
MRR便是利用read_rnd_buffer（非Join Buffer）在内存中将主键按照递增顺序排列，回表的时候便可以利用顺序读提升效率。
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

9.4.2 BKA

BKA算法便是利用Join Buffer替代read_rnd_buffer作缓存，在内存中将被驱动表的索引字段按递增顺序排序，并批量地针对被驱动表进行匹配。

9.5 Hash Join

SQL:select * from t1 straight_join t2 on (t1.b=t2.b);

9.5.1 Hash Join算法介绍

哈希连接算法会将驱动表和被驱动表涉及的关联字段进行哈希，并将驱动表的哈希结果及字段值进行哈希建表。
故：

扫描驱动表全表，将关联字段哈希化并建立哈希表
扫描被驱动表全表，将关联字段哈希化并通过哈希表查询。

9.5.1 Hash Join效率分析

针对于没有索引的字段，Hash Join的扫描行数也只有M+N，对比次数也只有M+N，唯一额外的资源消耗便是哈希化。
故若是没有索引，MySQL8.0一般会走Hash Join，有索引的话会走NLJ（BKA需要看InnoDB的MRR分析情况）

10. 分页查询问题

详见MySQL深分页场景下的性能优化。

11. MySQL的LRU算法优化

区域划分
在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。
图中 LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。

算法流程
以图中例子来说：

开始访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。
访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：
- 若这个数据页在 LRU 链表中访问的时间间隔超过了 1 秒，就把它移动到链表头部；
- 如果这个数据页在 LRU 链表访问的时间间隔短于 1 秒，位置保持不变。
  1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

posted @ 2023-02-27 22:39 kiper 阅读(40) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部