开发日常拾遗（四十七）：深度分页优化、联合索引与回表、Join 优化、MySQL 与 Redis 主从复制、事务日志解析、IO 分类与 InnoDB 内部元信息解析

一、深度分页查询的优化

1. 问题描述

查询如下 SQL 的效率差异：

慢查询
```
SELECT * FROM tb_sku LIMIT 9000000, 10;
```
- 这条 SQL 从第 9,000,000 行开始取 10 行数据，MySQL 需要扫描和丢弃前面 9,000,000 条记录，效率低下。
优化后的查询
```
SELECT * FROM tb_sku t, 
(SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 10) a 
WHERE t.id = a.id;
```
- 优化后的 SQL 通过子查询，先快速定位第 9,000,000 条数据的 id，再用 id 进行关联，减少了扫描的数据量。

2. 深入分析

子查询 (SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 10) 仅扫描 id 列，数据量小，速度快。
通过主键 id 关联获取完整数据，避免了大范围的数据丢弃。

3. 笛卡尔积解释

表面上看，这种 JOIN 操作有可能生成笛卡尔积，但由于 id 是唯一的，最终的结果是精确匹配，不会生成多余的组合。
SQL 优化器会识别这种子查询模式，执行效率较高。

二、索引优化

1. 什么是联合索引

联合索引（Composite Index）：在一个索引中包含多个列，按照多列的组合值来建立索引。
例如，创建 (col1, col2, col3) 的联合索引，索引中的数据按照 col1 → col2 → col3 的顺序排列。

B+树结构中的区别

单一索引：每个索引仅对应一列。B+树的每个节点按单列的值进行排序。
联合索引：B+树的排序依据是多列组合的值，首先按第 1 列排序，如果第 1 列相同，则按第 2 列排序，依此类推。

最左前缀原则

联合索引遵循 最左前缀法则：
- 查询条件必须包含索引的最左边开始的连续列，才能命中索引。
- 例如，对于 (col1, col2, col3) 的联合索引，WHERE col1 = ? AND col2 = ? 可以使用索引，而 WHERE col2 = ? 无法命中索引。

2. 回表查询

回表查询是指索引查询后，还需要回到主表获取完整数据。

如果索引无法直接提供查询所需的所有列，则需要回表。
覆盖索引（Covering Index）可以避免回表，提高查询效率。

三、Join 优化

1. Join 的执行原理

Inner Join 和 Left Join 都是基于两张表进行匹配操作，本质上是一种笛卡尔积。
- Inner Join：只返回匹配的结果。
- Left Join：返回左表的所有记录，如果右表无匹配记录，填充为 NULL。

2. 小表驱动大表

在 Join 操作中，小表放在前面（驱动表），大表放在后面，可以更快定位和过滤大表中的数据，减少扫描范围。
- 驱动表：作为外层循环的表。
- 被驱动表：内层循环匹配的表。

3. SQL 优化原则

尽量使用 Inner Join，因为优化器能自动调整执行顺序，提高性能。
减少 Join 表的数量，防止生成过多的中间结果。

四、MySQL 与 Redis 主从复制、读写分离

1. MySQL 的主从复制与读写分离

主从复制：主库写入数据后，通过 binlog 传递给从库，从库执行相同的操作，保持数据一致。
读写分离：将读操作分配给从库，写操作分配给主库，提升并发能力。

2. Redis 的主从复制与读写分离

主从复制：主节点的数据会自动同步到从节点。
读写分离：通常用于高可用架构，主节点处理写操作，从节点处理读操作。
区别：
- Redis 是内存数据库，复制速度快，延迟较低。
- MySQL 主要是磁盘存储，复制和同步会受到 I/O 性能的限制。

五、事务日志（redo log 和 undo log）

1. redo log（重做日志）

记录 物理层面的修改（如把某页数据从 A 修改到 B）。
用于恢复已提交事务的数据，保证崩溃后数据一致性。
顺序写入磁盘，性能高。

2. undo log（回滚日志）

记录 逻辑层面的修改，用于在事务失败时回滚数据，恢复到之前的状态。
支持 MVCC（多版本并发控制），帮助生成一致性视图，避免脏读、幻读等现象。

3. 日志存储位置

redo log 和 undo log 都存储在 MySQL 的物理文件中，可以通过日志文件查看。

六、IO 操作

1. 磁盘 IO（Disk I/O）

数据在内存和磁盘之间进行传输。
速度较慢，特别是机械硬盘。
例子：
- 从磁盘读取大文件。
- 数据库写入磁盘文件。

2. 网络 IO（Network I/O）

数据在计算机与远程服务器之间传输。
速度受限于网络带宽和延迟。
例子：
- 通过 HTTP 请求获取网页内容。
- 远程访问数据库。

七、DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID 解释

1. DB_TRX_ID （Database Transaction ID）

事务 ID，表示每条记录由哪个事务插入或修改。
用途：用于 MVCC 实现一致性读，避免读取未提交数据。

2. DB_ROLL_PTR （Database Rollback Pointer）

回滚指针，指向 undo log，用于回滚和一致性读。
用途：实现事务回滚或生成数据的历史版本。

3. DB_ROW_ID （Database Row ID）

行 ID，用于标识表中的唯一行（在没有主键时自动生成）。
用途：作为聚簇索引的主键。