HikariTish

1
执行过程（以 idx_age 二级索引为例）：
2

3
① 遍历 idx_age 的 B+ 树 → 找出所有 age=20 的主键 ID（共 100010 个）
4
         │
5
② 拿着 100010 个主键 ID → 逐行回表到聚簇索引 → 查出 100010 行完整数据
6
         │                         ↑
7
         │               ← 100010 次随机磁盘 I/O！← 性能灾难
8
         │
9
③ 丢弃前 100000 行 → 只返回最后 10 行给客户端
10

11
核心问题：前 10 万行的回表全是无用功，纯属 I/O 浪费。

1
┌─────────────────────────────────────────────────────────────┐
2
│              深度分页 = 大量无用回表                            │
3
│                                                             │
4
│  idx_age 二级索引                聚簇索引（主键）               │
5
│  ┌──────────────────┐           ┌──────────────────┐        │
6
│  │ age=20, id=1     │──回表──→  │ id=1, 完整行数据   │ ← 丢弃  │
7
│  │ age=20, id=7     │──回表──→  │ id=7, 完整行数据   │ ← 丢弃  │
8
│  │ age=20, id=12    │──回表──→  │ id=12, 完整行数据  │ ← 丢弃  │
9
│  │ ... ×100000 次   │  ...      │ ...              │ ← 丢弃  │
10
│  │ age=20, id=99901 │──回表──→  │ id=99901, 完整行  │ ✅ 返回  │
11
│  │ age=20, id=99902 │──回表──→  │ id=99902, 完整行  │ ✅ 返回  │
12
│  └──────────────────┘           └──────────────────┘        │
13
│                                                             │
14
│  100010 次回表 → 100000 次白做 → 只有最后 10 次有意义          │
15
└─────────────────────────────────────────────────────────────┘

1
-- ❌ 原始写法：10 万次无用回表
2
SELECT * FROM user WHERE age = 20 LIMIT 100000, 10;
3

4
-- ✅ 延迟关联：回表次数从 100000 → 10
5
SELECT t1.* FROM user t1
6
INNER JOIN (
7
    SELECT id FROM user WHERE age = 20 LIMIT 100000, 10
8
) t2 ON t1.id = t2.id;

1
延迟关联的执行路径：
2

3
① 子查询：遍历 idx_age → 找到 100010 个主键 id
4
   → 但只取了最后 10 个 id（不需要回表，id 就在索引叶子节点里！）
5
   → Extra: Using index（覆盖索引）✨
6

7
② 外层查询：拿着 10 个 id → 回表 10 次 → 查出完整行
8

9
回表次数：100010 → 10，性能提升上万倍

1
-- 第一页
2
SELECT * FROM user WHERE age = 20 ORDER BY id LIMIT 10;
3
-- 假设最后一行的 id = 100
4

5
-- 第二页（用上次的 id 作为游标）
6
SELECT * FROM user WHERE age = 20 AND id > 100 ORDER BY id LIMIT 10;

1
NLJ 执行过程：
2

3
  驱动表 A（外层循环）          被驱动表 B（内层循环）
4
  ┌──────────────────┐        ┌──────────────────────┐
5
  │ 遍历 A 的每一行    │        │ 利用 B 的索引树       │
6
  │ row₁ ────────────┼──────→ │ 精确查找匹配行         │
7
  │ row₂ ────────────┼──────→ │ 精确查找匹配行         │
8
  │ row₃ ────────────┼──────→ │ 精确查找匹配行         │
9
  │ ...              │        │ ...                  │
10
  └──────────────────┘        └──────────────────────┘
11

12
  复杂度：O(A_rows × log B_rows) —— 因为内层走索引，每次是 B+ 树查找

1
BNL 执行过程：
2

3
  ① 把驱动表 A 的数据分批加载进 join_buffer（内存）
4
  ② 全表扫描被驱动表 B，在内存中与 join_buffer 批量比对
5
  ③ 重复直到 A 的所有数据都比对完
6

7
  复杂度：O(A_rows × B_rows) —— 但比对发生在内存中，比磁盘随机 I/O 快

1
NLJ vs BNL 对比：
2

3
  NLJ（有索引）:
4
    驱动表 ──→ 每次拿一行 → 去被驱动表的索引树精确定位
5
    → 适合 OLTP 的点查场景
6

7
  BNL（无索引）:
8
    驱动表 ──→ 整批加载进 join_buffer → 全表扫描被驱动表 → 内存比对
9
    → 全表扫描不可避免，但内存比对比逐行回表快

1
SELECT * FROM orders o
2
JOIN users u ON o.user_id = u.id
3
WHERE o.status = 'paid' AND u.vip_level = 5;

1
判断驱动表的选择：
2

3
  orders 表 (100 万行)
4
    → WHERE status='paid' → 过滤后只剩 500 行 ✅ 小表！
5

6
  users 表 (10 万行)
7
    → WHERE vip_level=5 → 过滤后 8000 行
8

9
  → 优化器应该选择 orders 作为驱动表（500 行 < 8000 行）

1
架构图：
2

3
  用户请求第 5000 页
4
        │
5
        ↓
6
  ┌─────────────────┐
7
  │   业务服务       │
8
  │  计算 offset     │
9
  │  = 5000 × 10    │
10
  └────────┬────────┘
11
           │ ZRANGE key 50000 50010
12
           ↓
13
  ┌─────────────────┐
14
  │  Redis ZSET     │  ← 主键 ID 按创建时间 Score 排序
15
  │  [id1, id2, ...]│
16
  └────────┬────────┘
17
           │ 返回 10 个主键 ID
18
           ↓
19
  ┌─────────────────┐
20
  │  MySQL          │
21
  │  SELECT * FROM  │  ← 只用 IN 查询，不走 LIMIT
22
  │  table WHERE    │
23
  │  id IN (...)    │
24
  └─────────────────┘

1
架构图：
2

3
  ┌──────────┐    Binlog 准实时同步     ┌─────────────────┐
4
  │  MySQL   │ ──────────────────────→ │  Elasticsearch  │
5
  │  (写入)   │      (Canal 等组件)      │  (检索 + 分页)   │
6
  └──────────┘                         └────────┬────────┘
7
                                                 │
8
                                        用户搜索/分页请求
9
                                                 │
10
                                                 ↓
11
                                          返回目标页结果

1
┌──────────────────────────────────────────────────────────────┐
2
│                   复杂 SQL 调优全景                             │
3
├──────────────────────────────────────────────────────────────┤
4
│                                                              │
5
│   ① 深度分页                                                   │
6
│   ┌─────────────────────────────────────────────┐            │
7
│   │ LIMIT offset, count                          │            │
8
│   │   → 前 offset 行的回表 = 纯浪费               │            │
9
│   │   → 延迟关联：子查询覆盖索引取 ID → JOIN 回表  │            │
10
│   │   → 游标分页：AND id > last_max_id           │            │
11
│   │   → 架构兜底：Redis ZSET / ES                 │            │
12
│   └─────────────────────────────────────────────┘            │
13
│                                                              │
14
│   ② JOIN 连表                                                 │
15
│   ┌─────────────────────────────────────────────┐            │
16
│   │ 小表驱动大表                                  │            │
17
│   │   → NLJ：被驱动表关联字段有索引 → 内层走 B+ 树 │            │
18
│   │   → BNL：无索引 → join_buffer 全表比对        │            │
19
│   │   → "小表" = 过滤后的结果集，非原始表行数       │            │
20
│   └─────────────────────────────────────────────┘            │
21
│                                                              │
22
│   ③ COUNT 统计                                                │
23
│   ┌─────────────────────────────────────────────┐            │
24
│   │ COUNT(*) = COUNT(1) >> COUNT(id) > COUNT(字段)│            │
25
│   │   → MySQL 对 COUNT(*) 有专门优化              │            │
26
│   │   → 自动选最小二级索引扫描                     │            │
27
│   │   → COUNT(字段) 排除 NULL，且需提取字段值       │            │
28
│   └─────────────────────────────────────────────┘            │
29
│                                                              │
30
└──────────────────────────────────────────────────────────────┘