MySQL 索引深度剖析：从 B+ 树原理到生产级优化实践

┌─────────────────────────────────────────────────────────────┐
│  File Header (38 bytes)                                     │
│  ├─ FIL_PAGE_SPACE: 表空间 ID                               │
│  ├─ FIL_PAGE_OFFSET: 页号（4 字节，最大 2^32 页 ≈ 64TB）     │
│  ├─ FIL_PAGE_PREV: 上一页指针（双向链表）                    │
│  ├─ FIL_PAGE_NEXT: 下一页指针                               │
│  └─ FIL_PAGE_LSN: 最后修改的 LSN（用于 Recovery）           │
├─────────────────────────────────────────────────────────────┤
│  Page Header (56 bytes)                                     │
│  ├─ PAGE_N_DIR_SLOTS: Page Directory 槽位数                 │
│  ├─ PAGE_HEAP_TOP: 堆顶位置（空闲空间起始）                  │
│  ├─ PAGE_N_HEAP: 记录数（含已删除）                         │
│  ├─ PAGE_FREE: 删除记录链表头                                │
│  ├─ PAGE_GARBAGE: 删除记录总字节数（碎片）                   │
│  ├─ PAGE_N_RECS: 实际记录数                                 │
│  ├─ PAGE_MAX_TRX_ID: 最大事务 ID（二级索引）                 │
│  ├─ PAGE_LEVEL: B+ 树层级（0 表示叶子页）                    │
│  └─ PAGE_INDEX_ID: 索引 ID                                  │
├─────────────────────────────────────────────────────────────┤
│  Infimum Record (13 bytes) - 最小虚拟记录                    │
├─────────────────────────────────────────────────────────────┤
│  Supremum Record (13 bytes) - 最大虚拟记录                   │
├─────────────────────────────────────────────────────────────┤
│  User Records - 用户记录（按主键排序的链表）                  │
│  ┌─────────────────────────────────────────────────────┐    │
│  │ Record Header (5 bytes)                             │    │
│  │ ├─ delete_mask (1 bit): 是否已删除                  │    │
│  │ ├─ min_rec_mask (1 bit): B+ 树非叶子节点最小记录    │    │
│  │ ├─ n_owned (4 bits): 该组拥有的记录数               │    │
│  │ ├─ heap_no (13 bits): 堆中位置（0=Infimum,1=Supremum）│   │
│  │ ├─ record_type (3 bits): 0=普通,1=非叶子节点,2=Infimum,3=Supremum │
│  │ └─ next_record (16 bits): 下一条记录偏移量          │    │
│  ├─────────────────────────────────────────────────────┤    │
│  │ [Row Data]                                          │    │
│  │ ├─ 变长字段长度列表（逆序）                          │    │
│  │ ├─ NULL 值列表（逆序）                               │    │
│  │ ├─ 记录头信息（5 bytes，如上）                       │    │
│  │ ├─ Row ID (6 bytes, 隐藏主键)                        │    │
│  │ ├─ Transaction ID (6 bytes)                         │    │
│  │ ├─ Roll Pointer (7 bytes) → undo log                │    │
│  │ └─ 实际列数据                                        │    │
│  └─────────────────────────────────────────────────────┘    │
├─────────────────────────────────────────────────────────────┤
│  Free Space - 空闲空间                                      │
├─────────────────────────────────────────────────────────────┤
│  Page Directory - 页目录（稀疏索引）                         │
│  ├─ 每个槽（slot）2 字节，指向组内最大记录的偏移              │
│  ├─ 默认每组 4-8 条记录，二分查找定位组                       │
│  └─ 槽数量 ≈ 记录数 / 4                                     │
├─────────────────────────────────────────────────────────────┤
│  File Trailer (8 bytes)                                     │
│  ├─ 旧校验和（4 bytes，兼容老版本）                          │
│  └─ 新校验和（4 bytes，CRC32）                               │
└─────────────────────────────────────────────────────────────┘

-- 创建一个示例表
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age TINYINT,
    created_at DATETIME
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

┌─────────────────────────────────────────────────────────────┐
│  变长字段长度列表（逆序存储）                                │
│  ├─ email 长度（假设 16）: 0x10                             │
│  ├─ name 长度（假设 8）: 0x08                               │
│  └─ 变长字段数 = 所有 VARCHAR/TEXT/BLOB 字段               │
├─────────────────────────────────────────────────────────────┤
│  NULL 值位图（逆序，每列 1 bit）                            │
│  ├─ bit 0: id (NOT NULL) → 0                               │
│  ├─ bit 1: name (NOT NULL) → 0                             │
│  ├─ bit 2: email → 1 (NULL) 或 0 (NOT NULL)                │
│  ├─ bit 3: age → 1 (NULL) 或 0 (NOT NULL)                  │
│  └─ bit 4: created_at → 1 (NULL) 或 0 (NOT NULL)           │
├─────────────────────────────────────────────────────────────┤
│  记录头信息（5 bytes）                                       │
├─────────────────────────────────────────────────────────────┤
│  隐藏列                                                      │
│  ├─ DB_ROW_ID (6 bytes): 无主键时生成                       │
│  ├─ DB_TRX_ID (6 bytes): 最后修改的事务 ID                   │
│  └─ DB_ROLL_PTR (7 bytes): 回滚指针 → undo 记录              │
├─────────────────────────────────────────────────────────────┤
│  实际列数据（按定义顺序）                                    │
│  ├─ id (8 bytes BIGINT)                                     │
│  ├─ name (8 bytes VARCHAR)                                  │
│  ├─ email (16 bytes VARCHAR 或不存在如果是 NULL)             │
│  ├─ age (1 byte TINYINT 或不存在如果是 NULL)                 │
│  └─ created_at (5 bytes DATETIME 或不存在)                   │
└─────────────────────────────────────────────────────────────┘

记录链表（按主键排序）:

Infimum → R1 → R2 → R3 → R4 → R5 → R6 → R7 → R8 → Supremum

Page Directory（槽数组）:
┌─────┬─────┬─────┐
│  S0 │  S1 │  S2 │
└─────┴─────┴─────┘
   ↓     ↓     ↓
  Infimum  R4    R8  (每组最大记录)

查找 R6 的过程:
1. 二分查找 Page Directory: S0=Infimum < R6 < S2=R8 → 落在 S1 组
2. 从 S1 指向的 R4 开始线性扫描: R4 → R5 → R6 (找到)

时间复杂度: O(log(n/4)) + O(4) ≈ O(log n)

slot_count = (n_recs + 8 - 1) / 4  // 向上取整，每组最多 8 条

分裂前（16KB 已满）:
[1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31]

插入 16（中间位置插入）：

1. 申请新的空页（Page B）
2. 计算分裂点（约 50% 处）
3. 将 [17, 19, 21, 23, 25, 27, 29, 31] 移动到新页
4. 插入 16 到原页
5. 更新双向链表指针

分裂后:
Page A: [1, 3, 5, 7, 9, 11, 13, 15, 16]  →→  Page B: [17, 19, 21, 23, 25, 27, 29, 31]

6. 向上层父节点插入指向 Page B 的索引项（key=17, page_no=B）

-- 查看页分裂次数
SHOW STATUS LIKE 'Innodb_pages_%';

-- 更详细的统计（MySQL 8.0）
SELECT
    event_name,
    count_star,
    sum_timer_wait/1000000000000 as wait_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE '%innodb%page%';

-- 1. 使用自增主键（顺序插入，只在页尾追加）
CREATE TABLE t (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ...
);

-- 2. 预留页空间（FILLFACTOR 概念，MySQL 不支持，但可通过预插入实现）
-- 对于已知会有大量插入的表，可预先插入一些占位数据后删除

-- 3. 避免随机主键（如 UUID）
-- 坏：UUID 导致随机插入，频繁页分裂
-- 好：使用 AUTO_INCREMENT 或时间戳+自增组合

-- 4. 调整页大小（较少用，需重建实例）
-- 对于纯读表，可以使用更大的页（32KB/64KB）减少树高度

-- 表结构
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    user_id INT UNIQUE,
    balance DECIMAL(10,2),
    status TINYINT,
    INDEX idx_status (status)
);

-- 案例 1: 主键查询加锁
SELECT * FROM accounts WHERE id = 100 FOR UPDATE;
-- 只在聚簇索引（主键索引）的 id=100 记录上加 X 锁

-- 案例 2: 唯一索引查询加锁
SELECT * FROM accounts WHERE user_id = 1000 FOR UPDATE;
-- 1. 在二级索引 idx_user_id 的 user_id=1000 记录上加 X 锁
-- 2. 回表到聚簇索引，对 id=? 的记录加 X 锁
-- 共持有 2 个锁

-- 案例 3: 非唯一索引查询加锁
SELECT * FROM accounts WHERE status = 1 FOR UPDATE;
-- 1. 在 idx_status 的 status=1 的所有记录上加 X 锁（可能有多个）
-- 2. 对每个匹配的记录，回表到聚簇索引加 X 锁
-- 3. 同时，为了防止幻读，还会在间隙加 Gap 锁

-- 事务 A
BEGIN;
SELECT * FROM accounts WHERE id > 100 AND id < 200 FOR UPDATE;

-- 锁的范围：
-- 1. 对 id ∈ (100, 200) 的所有记录加 X 锁
-- 2. 对间隙 (100, next_record) 和 (prev_record, 200) 加 Gap Lock
-- 3. 阻止其他事务在 (100, 200) 范围内插入新记录

-- 事务 B（阻塞）
INSERT INTO accounts (id, ...) VALUES (150, ...); -- 等待事务 A 释放间隙锁

索引记录: [10] [20] [30] [40]

Next-Key Lock on 20 覆盖范围:
(-∞, 10] [10, 20] (20, 30]
         ↑ 锁定这个区间

实际锁定的范围：(10, 20] 左开右闭

-- 表: CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT, INDEX idx_a(a));
-- 数据: (1, 1, 1), (2, 2, 2), (3, 3, 3)

-- 事务 A                    -- 事务 B
BEGIN;                      BEGIN;
DELETE FROM t WHERE a = 1;  DELETE FROM t WHERE a = 2;
-- 锁住 idx_a 的 a=1 记录   -- 锁住 idx_a 的 a=2 记录
-- 锁住 id=1 的聚簇索引     -- 锁住 id=2 的聚簇索引

UPDATE t SET b = 100        UPDATE t SET b = 200
WHERE id = 2;               WHERE id = 1;
-- 等待事务 B 释放 id=2 锁   -- 等待事务 A 释放 id=1 锁
-- ↓ 死锁发生 ↓

-- 表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id INT,
    order_no VARCHAR(32),
    status TINYINT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    INDEX idx_user_status (user_id, status, created_at)
);

-- 非覆盖索引查询
SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- 执行过程：
-- 1. 在 idx_user_status 找到匹配记录
-- 2. 对每条记录，通过主键回表获取完整数据
-- 3. 总共访问：N 个二级索引页 + N 个聚簇索引页

-- 覆盖索引查询
SELECT user_id, status, created_at FROM orders
WHERE user_id = 100 AND status = 1;
-- 执行过程：
-- 1. 在 idx_user_status 找到匹配记录
-- 2. 直接返回索引中的数据
-- 3. 总共访问：N 个二级索引页（可能 1-2 个页）

-- 策略 1: 查询列全部包含在索引中
-- 原索引
CREATE INDEX idx_user ON orders(user_id);
-- 查询
SELECT user_id, status, created_at FROM orders WHERE user_id = ?;
-- 需要回表获取 status 和 created_at

-- 优化后（覆盖索引）
CREATE INDEX idx_user_status_ctime ON orders(user_id, status, created_at);
-- 完全覆盖查询，无需回表

-- 策略 2: 利用最左前缀原则的覆盖索引
-- 索引: (a, b, c)
-- 覆盖查询: SELECT a, b FROM t WHERE a = ?
-- 不覆盖: SELECT a, b, d FROM t WHERE a = ? (d 不在索引中)

-- 策略 3: 延迟关联（Deferred Join）优化深分页
-- 原查询（深分页性能差）
SELECT * FROM orders
WHERE status = 1
ORDER BY created_at DESC
LIMIT 1000000, 10;

-- 优化：先覆盖索引查出 ID，再关联
SELECT o.*
FROM orders o
JOIN (
    SELECT id FROM orders
    WHERE status = 1
    ORDER BY created_at DESC
    LIMIT 1000000, 10
) tmp ON o.id = tmp.id;
-- 子查询使用覆盖索引 idx_status_ctime(id)，只扫描索引
-- 大幅减少 I/O

-- 假设表结构
CREATE TABLE logs (
    id BIGINT PRIMARY KEY,
    app_id INT,           -- 100 个不同值
    level TINYINT,        -- 5 个不同值 (DEBUG/INFO/WARN/ERROR/FATAL)
    user_id INT,          -- 1000000 个不同值
    created_at DATETIME,
    message TEXT
);

-- 问题：如何设计 (app_id, level, user_id) 的索引顺序？

-- 顺序：区分度低的在前
CREATE INDEX idx_wrong ON logs(level, app_id, user_id);

-- 查询: WHERE app_id = 5 AND level = 3
-- 只能用到 level 列，app_id 无法使用（违反最左前缀）

-- 步骤 1: 计算各列选择性
SELECT
    COUNT(DISTINCT app_id) / COUNT(*) as app_id_sel,
    COUNT(DISTINCT level) / COUNT(*) as level_sel,
    COUNT(DISTINCT user_id) / COUNT(*) as user_id_sel,
    COUNT(*)
FROM logs;
-- 结果: app_id_sel = 0.0001, level_sel = 0.0000005, user_id_sel = 1

-- 步骤 2: 分析查询模式
-- Q1: WHERE app_id = ? AND user_id = ? (最频繁)
-- Q2: WHERE app_id = ? AND level = ? ORDER BY created_at
-- Q3: WHERE user_id = ?

-- 步骤 3: 设计索引
-- 对于 Q1：CREATE INDEX idx_app_user ON logs(app_id, user_id, created_at);
-- 对于 Q2：CREATE INDEX idx_app_level_ctime ON logs(app_id, level, created_at);
-- 对于 Q3：CREATE INDEX idx_user ON logs(user_id);

-- 步骤 4: 合并索引（减少索引数量）
-- 观察：Q1 和 Q2 都用到 app_id，可以合并
CREATE INDEX idx_optimal ON logs(app_id, level, user_id, created_at);

-- 验证各查询是否能使用该索引：
-- Q1: WHERE app_id = ? AND user_id = ? → 使用 app_id（等值），跳过 level，user_id（等值）✓
-- Q2: WHERE app_id = ? AND level = ? ORDER BY created_at → 完美匹配 ✓
-- Q3: WHERE user_id = ? → 无法使用（缺少最左列 app_id）✗
-- 需要额外索引：CREATE INDEX idx_user ON logs(user_id);

-- 索引: INDEX (a, b)
-- 查询: WHERE b = 2

-- MySQL 8.0.13+ 会自动优化为：
-- 1. 找出所有不同的 a 值
-- 2. 对每个 a 值，执行 WHERE a = ? AND b = 2
-- 3. 合并结果

-- 查看执行计划
EXPLAIN SELECT * FROM t WHERE b = 2;
-- Extra: Using index for skip scan

-- 表结构
CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT,
    price DECIMAL(10,2),
    brand_id INT,
    status TINYINT,
    INDEX idx_cat_price_brand (category_id, price, brand_id)
);

-- 查询 1：范围查询在中间
SELECT * FROM products
WHERE category_id = 1
  AND price BETWEEN 100 AND 200
  AND brand_id = 5;

-- 索引使用情况：
-- category_id: 等值匹配 ✓
-- price: 范围匹配 ✓
-- brand_id: 无法使用索引 ✗（被范围查询阻断）

-- 优化方案 1：调整列顺序，等值查询在前
DROP INDEX idx_cat_price_brand ON products;
CREATE INDEX idx_cat_brand_price ON products(category_id, brand_id, price);

-- 现在：
-- category_id: 等值 ✓
-- brand_id: 等值 ✓
-- price: 范围 ✓（最后）

-- 优化方案 2：如果 price 范围查询和高选择性 brand_id 组合频繁，考虑冗余索引
CREATE INDEX idx_cat_price ON products(category_id, price);  -- 用于纯价格筛选
CREATE INDEX idx_cat_brand_price ON products(category_id, brand_id, price);  -- 用于品牌+价格筛选

-- 索引: (a, b)

-- IN 等值查询 - 可以使用 b
SELECT * FROM t WHERE a IN (1, 2, 3) AND b = 4;
-- 执行方式：分别执行 (a=1 AND b=4) OR (a=2 AND b=4) OR (a=3 AND b=4)
-- 都可以使用 b

-- 但 IN 元素过多会退化为范围扫描
SELECT * FROM t WHERE a IN (1, 2, ..., 1000) AND b = 4;
-- 可能只使用 a，b 无法使用

-- 表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id INT,
    status TINYINT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    INDEX idx_user_status_ctime (user_id, status, created_at)
);

-- 案例 1: ORDER BY 优化
SELECT * FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 10;

-- 没有合适索引时：Using where; Using filesort（内存排序，数据量大时创建临时表）
-- 有 idx_user_status_ctime 时：Using index condition（直接使用索引顺序）

-- 关键点：ORDER BY 列必须紧跟在 WHERE 等值条件列之后
-- 索引: (user_id, status, created_at)
-- WHERE user_id = ? ORDER BY created_at → 无法使用索引排序（status 在中间）
-- WHERE user_id = ? AND status = ? ORDER BY created_at → 可以使用索引排序 ✓

-- 案例 2: 多列排序
SELECT * FROM orders
WHERE user_id = 100
ORDER BY status ASC, created_at DESC;

-- 索引: (user_id, status, created_at)
-- 结果：Using filesort（排序方向不一致）

-- 优化：创建反向索引（MySQL 8.0 支持 DESC 索引）
CREATE INDEX idx_user_status_ctime_desc ON orders(user_id, status, created_at DESC);
-- 或使用：
CREATE INDEX idx_user_status_desc_ctime_desc ON orders(user_id, status DESC, created_at DESC);

-- 案例 3: GROUP BY 优化
SELECT status, COUNT(*) FROM orders
WHERE user_id = 100
GROUP BY status;

-- 有索引 (user_id, status) 时：
-- 按索引顺序扫描 user_id=100 的所有记录，status 已经是有序的
-- 无需临时表：Using index for group-by

-- 设计索引时同时考虑 WHERE、ORDER BY、GROUP BY

-- 查询模式：
-- SELECT * FROM orders
-- WHERE user_id = ? AND status = ?
-- ORDER BY created_at DESC
-- LIMIT ?

-- 最佳索引：
CREATE INDEX idx_optimal ON orders(user_id, status, created_at DESC);

-- 验证：
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC
LIMIT 10;
-- Extra: Using index condition（无 filesort）

-- 陷阱 1: 隐式字符集转换
-- 表: name VARCHAR(50) CHARACTER SET utf8mb4
SELECT * FROM users WHERE name = '张三';
-- 如果连接字符集是 latin1，会发生隐式转换，导致索引失效

-- 陷阱 2: 多表 JOIN 的字符集/排序规则不一致
-- table_a.name utf8mb4_general_ci
-- table_b.name utf8mb4_unicode_ci
SELECT * FROM table_a JOIN table_b ON table_a.name = table_b.name;
-- 会发生字符集转换，索引失效

-- 陷阱 3: 数据类型隐式转换（不止是字符串 vs 数字）
-- 表: id VARCHAR(20)
SELECT * FROM t WHERE id = 123;
-- 实际执行: WHERE CAST(id AS SIGNED) = 123，索引失效

-- 陷阱 4: NOT IN 和 <> 的优化器选择
SELECT * FROM orders WHERE status != 0;
-- 如果 status 只有 0 和 1，且数据分布 1:1，优化器可能选择全表扫描

-- 陷阱 5: 日期范围的边界问题
SELECT * FROM logs WHERE DATE(created_at) = '2024-01-15';
-- 优化：
SELECT * FROM logs
WHERE created_at >= '2024-01-15 00:00:00'
  AND created_at < '2024-01-16 00:00:00';

-- 陷阱 6: 前缀索引与排序
CREATE INDEX idx_name_prefix ON users(name(10));
SELECT * FROM users WHERE name LIKE '张%' ORDER BY name;
-- 虽然可以用索引过滤，但 ORDER BY 需要全排序（Using filesort）
-- 因为前缀索引不知道第 11 位之后的内容

-- 问题：status 字段只有 0 和 1，但 99% 是 1，查询 WHERE status = 0
-- 索引 idx_status 的选择性很低（0.5），但实际查询时选择性很高（status=0 的记录很少）

-- 解决方案 1: 组合索引（低选择性 + 高选择性）
CREATE INDEX idx_status_created_at ON orders(status, created_at);

-- 解决方案 2: 条件索引（MySQL 不支持，但可用虚拟列变通）
-- 创建虚拟列标记需要查询的数据
ALTER TABLE orders ADD COLUMN is_pending TINYINT
    GENERATED ALWAYS AS (CASE WHEN status = 0 THEN 1 ELSE NULL END) STORED;
CREATE INDEX idx_is_pending ON orders(is_pending);
-- 查询: WHERE is_pending = 1

-- 解决方案 3: 分区表
-- 按 status 分区，status=0 的数据在一个小分区中

-- 问题查询（百万级数据深分页）
SELECT * FROM orders
WHERE status = 1
ORDER BY created_at DESC
LIMIT 1000000, 10;
-- 需要扫描 1000010 行，然后丢弃前 1000000 行

-- 上一页最后一条记录的 created_at 和 id
SELECT * FROM orders
WHERE status = 1
  AND (created_at < '2024-01-15 10:00:00'
       OR (created_at = '2024-01-15 10:00:00' AND id < 123456))
ORDER BY created_at DESC, id DESC
LIMIT 10;

-- 索引: INDEX (status, created_at, id)
-- 优势：O(log n) 时间复杂度，与页码无关
-- 劣势：不支持跳转到任意页

SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id FROM orders
    WHERE status = 1
    ORDER BY created_at DESC
    LIMIT 1000000, 10
) tmp ON o.id = tmp.id;

-- 子查询只用覆盖索引 idx_status_ctime_id，在索引中完成排序和分页
-- 只需回表 10 次

-- 对于超大数据量的 "跳到第 10000 页" 需求，改为估算
SELECT COUNT(*) FROM orders WHERE status = 1; -- 假设 1000 万
-- 第 10000 页 ≈ id > (1000万 / 每页10条 * 偏移) 的位置
-- 或者使用 ES 等搜索引擎处理翻页

-- 查看表的索引
SHOW INDEX FROM orders;

-- 冗余索引案例：
-- INDEX idx_a (a)
-- INDEX idx_ab (a, b)
-- idx_a 是冗余的，因为 idx_ab 可以替代它

-- 查找重复索引的查询
SELECT
    t.table_schema,
    t.table_name,
    t.index_name,
    GROUP_CONCAT(t.column_name ORDER BY t.seq_in_index) AS columns
FROM information_schema.statistics t
WHERE t.table_schema = 'your_database'
GROUP BY t.table_schema, t.table_name, t.index_name;

-- 使用 pt-duplicate-key-checker 工具（Percona Toolkit）
pt-duplicate-key-checker --host=localhost --user=root --password=xxx --database=your_db

-- 查找未使用的索引（MySQL 8.0）
SELECT
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
    AND count_star = 0
    AND object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY object_schema, object_name;

-- 查看 AHI 状态
SHOW ENGINE INNODB STATUS\G
-- -------------------------------------
-- INSERT BUFFER AND ADAPTIVE HASH INDEX
-- -------------------------------------
-- Hash table size 2267, node heap has 1 buffer(s)
-- Hash table size 2267, node heap has 1 buffer(s)
-- ...
-- 0.00 hash searches/s, 0.00 non-hash searches/s

-- AHI 命中率 = hash searches / (hash searches + non-hash searches)
-- 如果命中率 > 90%，说明 AHI 很有效

-- 关闭 AHI（如果 CPU 成为瓶颈且写入很多）
SET GLOBAL innodb_adaptive_hash_index = OFF;

-- 查看 change buffer 状态
SHOW VARIABLES LIKE 'innodb_change_buffer%';
-- innodb_change_buffer_max_size = 25 （占缓冲池的 25%）
-- innodb_change_buffering = all （INSERT/DELETE/UPDATE 都缓冲）

-- MySQL 5.5: 创建索引会锁表（COPY 算法）
ALTER TABLE orders ADD INDEX idx_test (user_id); -- 阻塞读写

-- MySQL 5.6+: 在线 DDL（INPLACE 算法）
ALTER TABLE orders ADD INDEX idx_test (user_id), ALGORITHM=INPLACE, LOCK=NONE;
-- 允许并发读写

-- 查看 DDL 进度（MySQL 8.0）
SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter%';

-- pt-online-schema-change（Percona Toolkit，适用于所有版本）
pt-online-schema-change \
    --alter "ADD INDEX idx_test (user_id)" \
    --execute \
    --max-load Threads_running=25 \
    --critical-load Threads_running=50 \
    --chunk-size=1000 \
    D=your_db,t=orders

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    status TINYINT NOT NULL,  -- 0=待支付, 1=已支付, 2=已发货, 3=已完成
    pay_status TINYINT,       -- 支付状态
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    amount DECIMAL(10,2),
    -- 其他字段...
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_ctime (created_at)
);

-- 慢查询（平均 5 秒）
SELECT * FROM orders
WHERE user_id = 12345
  AND status IN (0, 1)
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN SELECT * FROM orders
WHERE user_id = 12345
  AND status IN (0, 1)
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 结果：
-- type: ref
-- key: idx_user
-- rows: 50000  （用户有 5 万订单）
-- Extra: Using where; Using filesort
-- 问题：只用了 idx_user，然后在内存中过滤和排序

-- 步骤 1: 创建复合索引
CREATE INDEX idx_user_status_ctime ON orders(user_id, status, created_at);

-- 验证
EXPLAIN SELECT * FROM orders
WHERE user_id = 12345
  AND status IN (0, 1)
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 结果：
-- type: range
-- key: idx_user_status_ctime
-- rows: 1000
-- Extra: Using index condition（无 filesort）
-- 查询时间：50ms

-- 如果业务只需要部分字段
SELECT id, user_id, status, created_at, amount FROM orders
WHERE user_id = 12345
  AND status IN (0, 1)
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 创建覆盖索引
CREATE INDEX idx_cover ON orders(user_id, status, created_at, amount);
-- Extra: Using index（完全覆盖）
-- 查询时间：5ms

CREATE TABLE feeds (
    id BIGINT PRIMARY KEY,
    author_id INT NOT NULL,
    content TEXT,
    created_at DATETIME NOT NULL,
    INDEX idx_author_ctime (author_id, created_at)
);

-- 查询：获取关注用户的 Feed（IN 列表可能很长）
SELECT * FROM feeds
WHERE author_id IN (100, 101, 102, ..., 500)  -- 400 个关注
  AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 方案 1: 限制 IN 列表长度，分多次查询在应用层合并
-- 每次查 50 个，查 8 次，在应用层归并排序

-- 方案 2: 使用临时表（适用于关注数极多的场景）
CREATE TEMPORARY TABLE tmp_following (user_id INT PRIMARY KEY);
INSERT INTO tmp_following VALUES (100), (101), ...;

SELECT f.*
FROM feeds f
JOIN tmp_following t ON f.author_id = t.user_id
WHERE f.created_at > '2024-01-01'
ORDER BY f.created_at DESC
LIMIT 20;

-- 方案 3: 使用推送模式（写扩散）替代拉取模式
-- 每个用户有一个时间线表，关注后发件箱写入粉丝收件箱