MySQL 查询优化实战

索引设计只是优化的第一步。即使拥有完美的索引，一条低效的 SQL 查询仍然可能让数据库陷入困境。本章将从查询执行管道的源码入手，逐一拆解 JOIN 算法、子查询变换、分页瓶颈、排序分组优化、批量 DML 策略，最终以三个真实案例和 20 条反模式清单收尾。每个主题都附带 EXPLAIN 输出和可量化的性能对比。

1. 查询执行管道

1.1 从 SQL 文本到结果集的完整路径

当一条 SQL 到达 MySQL Server 时，它依次经过以下阶段：

1.2 Parser 阶段

MySQL 使用 Bison（YACC 变体）定义 SQL 语法规则（sql/sql_yacc.yy，超过 17000 行）。Parser 将 SQL 文本转换为 Parse Tree（内部结构体 SELECT_LEX / Query_block）。

这个阶段只检查语法是否合法，不检查表或列是否存在。例如 SELECT * FORM users 会在 Parser 阶段报错（FORM 不是关键字），但 SELECT * FROM nonexistent_table 会通过 Parser。

1.3 Preprocessor 阶段

Preprocessor（也叫 Resolver）负责语义分析：

• 名称解析：检查表、列、函数是否存在；解析别名引用
• 权限检查：当前用户是否有 SELECT/UPDATE 等权限
• 类型推导：确定表达式返回类型；插入隐式类型转换
• 通配符展开：SELECT * 替换为具体列列表
• 视图展开：视图定义合并到查询树（MERGE 算法）或创建派生表（TEMPTABLE 算法）

1.4 Optimizer 阶段

优化器是 MySQL 最复杂的模块，分为两个子阶段：

逻辑优化（RBO 规则）

• 常量折叠：WHERE 1=1 AND id > 5 → WHERE id > 5
• 谓词下推：将 WHERE 条件尽早应用到最内层表
• 子查询变换：IN 子查询 → semi-join；NOT IN → anti-join
• 外连接消除：LEFT JOIN + WHERE 条件使其等价于 INNER JOIN 时自动转换
• 冗余排序消除：如果 ORDER BY 列与索引顺序一致则跳过 filesort
• 条件简化：WHERE a > 5 AND a > 3 → WHERE a > 5

物理优化（CBO 代价模型）

MySQL 8.0 使用代价模型来评估不同执行计划的开销。代价由两部分组成：

优化器的核心决策包括：

1. 访问路径选择：全表扫描 vs 索引扫描 vs 索引范围扫描 vs ref 访问
2. JOIN 顺序：对 N 张表的 JOIN，理论上有 N! 种排列。当 N ≤ optimizer_search_depth（默认 62，实际限制约 7-8 张表穷举）时穷举搜索，否则使用贪心启发式
3. JOIN 算法：NLJ / BNL / Hash Join / BKA+MRR
4. 子查询策略：物化、FirstMatch、LooseScan 等

1.5 Executor 阶段 (Iterator Model)

MySQL 8.0 将传统的 handler 调用方式重构为 Iterator（迭代器）模型：

每个 Iterator 实现 Init() 和 Read() 方法，上层 Iterator 调用下层的 Read() 拉取一行，处理后返回给更上层——这就是经典的"火山模型"（Volcano Model）。EXPLAIN ANALYZE 输出的就是这棵迭代器树的执行统计。

2. JOIN 算法深度剖析

2.1 Nested Loop Join (NLJ)

嵌套循环连接是 MySQL 最基本的 JOIN 算法，也是使用索引时的首选算法。其逻辑等价于：

-- 伪代码：Nested Loop Join
for each row r1 in outer_table:          -- 驱动表（外层循环）
    for each row r2 in inner_table:      -- 被驱动表（内层循环）
        if r1.join_key == r2.join_key:   -- 通过索引查找
            output (r1, r2)

关键要点：

• 驱动表执行全表扫描（或索引范围扫描），对结果集中的每一行，通过索引在被驱动表中查找匹配行
• 如果被驱动表的 JOIN 列上有索引，每次查找是 O(log n)，总代价是 O(M * log N)（M = 驱动表行数，N = 被驱动表行数）
• 优化器会选择结果集较小的表作为驱动表（小表驱动大表原则）
• 当被驱动表有合适索引时，NLJ 的效率非常高

2.2 Block Nested Loop (BNL)

当被驱动表的 JOIN 列上没有索引时，每次内层循环都需要全表扫描被驱动表。如果驱动表有 M 行、被驱动表有 N 行，则需要扫描被驱动表 M 次，总 I/O 代价为 M * N — 这是灾难性的。

BNL 的优化思路是：将驱动表的行批量装入 JOIN Buffer（内存缓冲区），然后一次全表扫描被驱动表，与 buffer 中的所有行进行比较。这样只需扫描被驱动表 ceil(M / buffer_capacity) 次。

-- 伪代码：Block Nested Loop
while join_buffer is not full:
    read rows from outer_table into join_buffer

    for each row r2 in inner_table:       -- 全表扫描被驱动表
        for each row r1 in join_buffer:   -- 内存中逐行比较
            if r1.join_key == r2.join_key:
                output (r1, r2)

    clear join_buffer

调优参数：

• join_buffer_size：默认 256KB，最大 4GB。增大可减少被驱动表扫描次数
• 注意：该 buffer 是 per-join-per-thread 分配的。一个多表 JOIN 查询中每个 join step 都可能分配一个，高并发下谨慎增大
• 最佳实践：如果某个查询频繁使用 BNL，应优先考虑添加索引而非增大 buffer

2.3 Hash Join (MySQL 8.0.18+)

Hash Join 是 MySQL 8.0.18 引入的革命性特性，专门解决无索引等值 JOIN 的性能问题。它的效率远超 BNL：

-- 伪代码：Hash Join
-- 1. Build Phase（构建阶段）
hash_table = {}
for each row r1 in smaller_table:       -- 选择较小的表
    hash_table[hash(r1.join_key)] = r1  -- 构建哈希表

-- 2. Probe Phase（探测阶段）
for each row r2 in larger_table:        -- 扫描较大的表
    if hash(r2.join_key) in hash_table: -- O(1) 查找
        for each r1 in hash_table[hash(r2.join_key)]:
            output (r1, r2)

Hash Join 的内存管理：

• 哈希表大小受 join_buffer_size 限制
• 当哈希表超出内存限制时，MySQL 会 spill to disk（溢出到磁盘临时文件）
• 溢出策略：Grace Hash Join — 将 build 和 probe 输入按哈希分区写入磁盘，然后逐分区处理
• MySQL 8.0.18 首次发布时仅支持内存中的 Hash Join，8.0.20 开始支持磁盘溢出

Hash Join 触发条件（MySQL 8.0.18+）：

1. 等值 JOIN 条件（a.id = b.id）— 8.0.20+ 也支持非等值条件和笛卡尔积
2. 被驱动表的 JOIN 列上没有可用索引（否则优化器倾向于 NLJ）
3. optimizer_switch 中 block_nested_loop=on（默认 on）

2.4 Batched Key Access (BKA) + Multi-Range Read (MRR)

BKA 是对 NLJ 的增强，解决的是随机 I/O 问题。标准 NLJ 对被驱动表执行随机索引查找，如果数据不在 Buffer Pool 中，每次查找都是一次随机磁盘 I/O。BKA 的思路是：

1. 从驱动表批量读取 join key，放入 JOIN Buffer
2. 对这批 key 排序（按被驱动表主键顺序）
3. 使用 MRR（Multi-Range Read）接口批量提交给存储引擎
4. 存储引擎将随机读转换为顺序读（或合并相邻页的读取请求）

启用 BKA+MRR：

-- BKA 默认关闭，需要手动启用
SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';

-- mrr_cost_based=off 强制使用 MRR（否则优化器可能认为代价不值得）
-- 也可以通过 hint 在单条查询中启用：
SELECT /*+ BKA(t2) MRR(t2) */ *
FROM orders t1 JOIN users t2 ON t1.user_id = t2.id;

2.5 JOIN 算法性能对比

3. 子查询优化

3.1 相关子查询 vs 非相关子查询

理解两者的区别是优化的前提：

-- 非相关子查询：只执行一次
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE country = 'CN');

-- 相关子查询：外层 orders 每行都触发一次子查询
SELECT *, (SELECT name FROM users u WHERE u.id = o.user_id) AS user_name
FROM orders o;

3.2 子查询物化 (Materialization)

优化器可以将子查询的结果集物化为临时表（materialized temporary table），然后用这个临时表参与 JOIN：

-- 原始查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);

-- 优化器内部可能执行为：
-- 1. 执行子查询，将结果存入临时表 (materialized_subquery)
-- 2. SELECT * FROM orders WHERE user_id IN (SELECT id FROM materialized_subquery)
-- 临时表会自动去重并建立哈希索引（适合等值查找）

物化的好处是子查询只执行一次，缺点是需要额外的内存/磁盘存储临时表。EXPLAIN 中你会看到 MATERIALIZED 字样。

3.3 Semi-Join 变换

Semi-join 是 MySQL 5.6+ 引入的一项关键优化。当 IN 或 EXISTS 子查询满足条件时，优化器会将其转换为 semi-join，从而可以使用更高效的 JOIN 算法。Semi-join 的语义是：对于外层每一行，只要内层有至少一行匹配即可，无需返回内层的实际数据。

MySQL 实现了四种 semi-join 策略：

-- 原始查询
SELECT * FROM employees e
WHERE e.dept_id IN (SELECT d.id FROM departments d WHERE d.budget > 1000000);

-- EXPLAIN 输出: FirstMatch(e)
-- 对于 employees 的每一行，在 departments 中找到第一个匹配就返回

-- 场景：departments.id 对应多行（一对多的反向查询）
SELECT * FROM departments d
WHERE d.id IN (SELECT dept_id FROM employees WHERE salary > 100000);

-- 如果 employees 上有 idx(dept_id)，LooseScan 可以跳跃扫描：
-- 遇到 dept_id=1 的第一行就输出，跳过 dept_id=1 的其余行
-- 直接跳到 dept_id=2 的第一行...

-- EXPLAIN 输出: MATERIALIZED
-- 子查询结果存入内存临时表 → 建立哈希索引 → 外层逐行 lookup
-- 适合子查询结果集不大、且需要去重的场景

-- 内部执行过程：
-- 1. 将 IN 子查询转换为普通 INNER JOIN
-- 2. JOIN 可能产生重复（因为内层一行可能匹配多行外层行）
-- 3. 结果写入临时表，以外层 PK 为唯一键去重
-- EXPLAIN: Start temporary / End temporary

3.4 EXISTS vs IN vs JOIN 对比

-- 方式 1: IN 子查询
SELECT * FROM orders o
WHERE o.user_id IN (SELECT u.id FROM users u WHERE u.country = 'CN');

-- 方式 2: EXISTS
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.country = 'CN');

-- 方式 3: JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.country = 'CN';

-- MySQL 8.0 的优化器通常会将三者转换为相同的执行计划 (semi-join)
-- 但在 MySQL 5.7 及更早版本，IN 子查询可能不会被优化，执行计划差异很大

3.5 Anti-Join 模式 (NOT EXISTS / NOT IN / LEFT JOIN WHERE NULL)

-- 需求："找出没有下过订单的用户"

-- 方式 1: NOT EXISTS（推荐）
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 方式 2: NOT IN（危险：如果 orders.user_id 有 NULL，结果可能为空集！）
SELECT * FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

-- 方式 3: LEFT JOIN + WHERE NULL
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

3.6 子查询改写实例

-- BEFORE: 每行执行一次子查询（10万行 = 10万次子查询）
SELECT o.id, o.amount,
    (SELECT u.name FROM users u WHERE u.id = o.user_id) AS user_name
FROM orders o;

-- AFTER: 一次 JOIN
SELECT o.id, o.amount, u.name AS user_name
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;
-- 提升：800ms → 45ms

-- BEFORE: MySQL 5.7 可能不优化 IN 子查询
SELECT * FROM products p
WHERE p.category_id IN (
    SELECT c.id FROM categories c WHERE c.status = 'active'
);

-- AFTER: EXISTS 强制相关子查询优化路径
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM categories c WHERE c.id = p.category_id AND c.status = 'active'
);
-- MySQL 5.7 提升显著；MySQL 8.0 两者等价

-- BEFORE: 计算每个员工工资占部门总工资的百分比
SELECT e.*,
    e.salary / (SELECT SUM(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id) AS pct
FROM employees e;

-- AFTER: 窗口函数（MySQL 8.0+）
SELECT e.*,
    e.salary / SUM(e.salary) OVER (PARTITION BY e.dept_id) AS pct
FROM employees e;
-- 从 N 次子查询 → 单次扫描，提升 10-50 倍

-- BEFORE: 嵌套派生表，难以阅读
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
) AS order_totals
JOIN (
    SELECT user_id, COUNT(*) AS cnt FROM returns GROUP BY user_id
) AS return_counts ON order_totals.user_id = return_counts.user_id;

-- AFTER: CTE（MySQL 8.0+）
WITH order_totals AS (
    SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
),
return_counts AS (
    SELECT user_id, COUNT(*) AS cnt FROM returns GROUP BY user_id
)
SELECT * FROM order_totals
JOIN return_counts ON order_totals.user_id = return_counts.user_id;

-- BEFORE: 扫描表 3 次
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'pending') AS pending,
    (SELECT COUNT(*) FROM orders WHERE status = 'shipped') AS shipped,
    (SELECT COUNT(*) FROM orders WHERE status = 'delivered') AS delivered;

-- AFTER: 单次扫描
SELECT
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered
FROM orders;
-- 从 3 次全表扫描 → 1 次全表扫描

4. 分页优化

4.1 LIMIT 大偏移量问题

分页查询是 Web 应用中最常见的查询模式之一。但传统的 LIMIT offset, count 在偏移量很大时会成为严重的性能瓶颈：

-- 第 1 页：很快
SELECT * FROM articles ORDER BY created_at DESC LIMIT 0, 20;
-- 扫描 20 行，返回 20 行

-- 第 5000 页：极慢！
SELECT * FROM articles ORDER BY created_at DESC LIMIT 100000, 20;
-- MySQL 必须先扫描（并排序）100020 行，然后丢弃前 100000 行，只返回 20 行
-- 那 100000 行的扫描工作完全浪费了

根本原因：InnoDB 不支持"从索引的第 N 个位置直接开始"。它必须从头开始遍历，逐行跳过前 N 行。偏移量 N 越大，浪费的 I/O 越多。

4.2 方案一：延迟关联 (Deferred JOIN)

核心思路：先通过覆盖索引只取出主键 ID，再用这些 ID 回表取完整数据。

-- 原始查询（慢）
SELECT * FROM articles
ORDER BY created_at DESC LIMIT 100000, 20;

-- 延迟关联（快）
SELECT a.* FROM articles a
INNER JOIN (
    SELECT id FROM articles
    ORDER BY created_at DESC LIMIT 100000, 20
) AS tmp ON a.id = tmp.id;

-- 为什么更快？
-- 子查询 "SELECT id FROM articles ORDER BY created_at DESC"
-- 如果有索引 idx_created_at，则走覆盖索引扫描（只读索引页，不读数据页）
-- 索引页比数据页小得多，扫描 10 万个索引条目远快于 10 万行完整数据
-- 最后只用 20 个 ID 回表取数据

4.3 方案二：游标分页 (Cursor-based Pagination)

游标分页（也叫 Keyset Pagination）完全避免使用 OFFSET，通过记住上一页最后一条记录的位置来定位下一页：

-- 第 1 页
SELECT * FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 假设最后一行：created_at = '2024-03-15 10:30:00', id = 98765

-- 第 2 页（使用上一页最后一行的值作为游标）
SELECT * FROM articles
WHERE (created_at, id) < ('2024-03-15 10:30:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 或者更兼容的写法：
SELECT * FROM articles
WHERE created_at < '2024-03-15 10:30:00'
   OR (created_at = '2024-03-15 10:30:00' AND id < 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

优势：

• 无论翻到第多少页，性能恒定（始终只扫描 20 行）
• 不受并发插入/删除影响（不会漏行或重复行）
• 天然适合无限滚动（Infinite Scroll）场景

劣势：

• 无法直接跳到第 N 页（必须从第 1 页逐页翻）
• 需要前端传递游标参数（而不是页码）
• 排序字段必须有索引，且排序字段 + ID 组合必须唯一

4.4 方案三：覆盖索引 + 排序字段

如果查询只需要少量列，可以创建覆盖索引将所有需要的列包含在索引中，避免回表：

-- 只需要标题和时间
SELECT id, title, created_at FROM articles
ORDER BY created_at DESC LIMIT 100000, 20;

-- 创建覆盖索引
ALTER TABLE articles ADD INDEX idx_cover (created_at DESC, id, title);

-- 现在整个查询在索引中完成，无需回表
-- EXPLAIN: Using index（覆盖索引）

4.5 分页方案性能对比

5. ORDER BY 与 GROUP BY 优化

5.1 Filesort 算法

当 MySQL 无法通过索引直接获得有序结果时，就需要执行 filesort（文件排序）。尽管名字中有"file"，但 filesort 优先在内存中进行，只有当数据量超过 sort_buffer_size 时才会溢出到磁盘。

MySQL 实现了两种 filesort 算法：

5.2 sort_buffer_size 调优

• 默认值：256KB（MySQL 8.0）
• 溢出行为：当排序数据超过 sort_buffer_size 时，MySQL 使用 merge sort — 将数据分成多个 sorted run 写入磁盘临时文件，最后合并
• 判断是否溢出：查看 SHOW STATUS LIKE 'Sort_merge_passes'，该值持续增长说明 sort_buffer 不够
• 调优建议：全局不要设太大（per-thread 分配），建议 1-8MB。对于特定大排序查询可以 session 级别设置

-- Session 级别增大 sort buffer
SET SESSION sort_buffer_size = 4 * 1024 * 1024;  -- 4MB

-- 查看 filesort 临时文件使用情况
SHOW STATUS LIKE 'Sort%';
-- Sort_merge_passes: 溢出到磁盘的合并次数（越低越好）
-- Sort_rows: 排序的总行数
-- Sort_scan: 全表扫描排序次数
-- Sort_range: 范围扫描排序次数

5.3 利用索引避免 Filesort

最好的排序优化是完全避免排序。如果 ORDER BY 的列与索引顺序一致，MySQL 直接从索引中按序读取，无需 filesort：

-- 索引：INDEX idx_created (created_at)

-- 可以利用索引排序（无 filesort）
SELECT * FROM articles WHERE status = 'published'
ORDER BY created_at DESC LIMIT 20;
-- 前提：优化器选择了 idx_created 索引

-- 无法利用索引排序的情况
SELECT * FROM articles WHERE status = 'published'
ORDER BY created_at DESC, title ASC;
-- 混合 ASC/DESC 在 MySQL 5.7 无法使用索引
-- MySQL 8.0 支持 DESC 索引：INDEX idx_mix (created_at DESC, title ASC)

-- 联合索引的排序规则
-- INDEX idx_ab (a, b)
-- ORDER BY a, b        → 可以用索引 ✓
-- ORDER BY a DESC, b DESC → 可以用索引 ✓（反向扫描）
-- ORDER BY a ASC, b DESC  → 5.7 不行，8.0 可以（需要 DESC 索引）
-- ORDER BY b, a        → 不行（顺序不匹配）✗
-- WHERE a = 1 ORDER BY b → 可以 ✓（a 是等值条件，b 是排序列）

5.4 Loose Index Scan (松散索引扫描)

对于 GROUP BY 查询，如果满足条件，MySQL 可以使用 Loose Index Scan，避免扫描所有行：

-- INDEX idx_dept_salary (dept_id, salary)

-- 找每个部门的最高薪资
SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id;

-- Loose Index Scan 执行过程：
-- 1. 跳到 dept_id=1 的第一条记录
-- 2. 因为索引按 (dept_id, salary) 排列，最后一条就是最大值
-- 3. 直接跳到 dept_id=2 的区域，取最后一条
-- 4. 以此类推...
-- 只需要访问每个组的边界行，跳过组内大量中间行

-- EXPLAIN: Using index for group-by

Loose Index Scan 的条件：

• 查询只涉及单个表
• GROUP BY 使用索引的最左前缀列
• 只使用 MIN() / MAX() 聚合函数
• 查询中不能有其他非 GROUP BY 列（除了聚合函数中的列）

5.5 ORDER BY + LIMIT 优化

当 ORDER BY 与 LIMIT 一起出现时，MySQL 使用优先队列（堆排序）而不是完整排序：

-- 只需要前 10 行
SELECT * FROM articles ORDER BY score DESC LIMIT 10;

-- 优化器会使用优先队列（最小堆/最大堆）：
-- 维护一个大小为 10 的堆
-- 扫描过程中，只保留当前 top 10
-- 时间复杂度：O(N * log K)，其中 K=10
-- 内存使用：只需存储 K 行
-- 远好于完整排序 O(N * log N)

6. COUNT(*) 优化

6.1 为什么 InnoDB 的 COUNT(*) 慢？

与 MyISAM 不同，InnoDB 不会缓存表的精确行数。这是因为 InnoDB 的 MVCC（多版本并发控制）机制：

• 每个事务看到的行数可能不同（取决于事务隔离级别和行的版本）
• 被标记为删除但尚未 purge 的行对某些事务可见、对其他事务不可见
• 因此不可能维护一个全局精确的行数计数器

执行 SELECT COUNT(*) FROM big_table 时，InnoDB 必须扫描一个完整的索引（选择最小的二级索引）来统计行数。对于 1 亿行的表，这可能需要 30-60 秒。

6.2 COUNT(*) vs COUNT(1) vs COUNT(col)

6.3 优化方案

方案一：近似计数

-- 从 information_schema 获取近似值（误差可达 40-50%）
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

-- 使用 EXPLAIN 获取估算值（更快，误差通常 10-30%）
EXPLAIN SELECT COUNT(*) FROM orders;
-- rows 列就是优化器估算的行数

方案二：缓存计数

-- 方法 A：Redis 计数器
-- 每次 INSERT 时 INCR, DELETE 时 DECR
-- 优点：实时，极快
-- 缺点：与数据库不一致的风险（事务回滚、进程崩溃）

-- 方法 B：数据库计数表
CREATE TABLE table_counts (
    table_name VARCHAR(64) PRIMARY KEY,
    row_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 使用触发器自动维护
DELIMITER //
CREATE TRIGGER orders_insert AFTER INSERT ON orders
FOR EACH ROW BEGIN
    UPDATE table_counts SET row_count = row_count + 1 WHERE table_name = 'orders';
END //
CREATE TRIGGER orders_delete AFTER DELETE ON orders
FOR EACH ROW BEGIN
    UPDATE table_counts SET row_count = row_count - 1 WHERE table_name = 'orders';
END //
DELIMITER ;

-- 查询计数：O(1)
SELECT row_count FROM table_counts WHERE table_name = 'orders';

方案三：汇总表

-- 对于带条件的 COUNT（如按天统计），使用汇总表
CREATE TABLE daily_order_stats (
    stat_date DATE PRIMARY KEY,
    total_count INT NOT NULL DEFAULT 0,
    pending_count INT NOT NULL DEFAULT 0,
    shipped_count INT NOT NULL DEFAULT 0
);

-- 定时任务（每小时 / 每天）刷新
INSERT INTO daily_order_stats (stat_date, total_count, pending_count, shipped_count)
SELECT DATE(created_at),
    COUNT(*),
    COUNT(CASE WHEN status = 'pending' THEN 1 END),
    COUNT(CASE WHEN status = 'shipped' THEN 1 END)
FROM orders
WHERE DATE(created_at) = CURDATE()
ON DUPLICATE KEY UPDATE
    total_count = VALUES(total_count),
    pending_count = VALUES(pending_count),
    shipped_count = VALUES(shipped_count);

方案四：覆盖索引加速带条件的 COUNT

-- 统计特定状态的订单数
SELECT COUNT(*) FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';

-- 如果有覆盖索引 idx(status, created_at)，COUNT 只扫描索引，不回表
-- EXPLAIN: Using index（覆盖索引扫描）
ALTER TABLE orders ADD INDEX idx_status_date (status, created_at);

7. 大批量 UPDATE/DELETE 优化

7.1 为什么大批量 DML 是个问题？

一条 DELETE FROM orders WHERE created_at < '2020-01-01' 看起来简单，但如果影响 500 万行，会带来一系列问题：

• 长事务：所有删除在一个事务内完成，undo log 暴涨，回滚代价极大
• 锁竞争：InnoDB 行锁（实际是 next-key lock）持有时间过长，阻塞其他 DML
• 主从延迟：主库执行 30 分钟的大事务，binlog 传到从库后也需要 30 分钟重放
• Buffer Pool 污染：大量页被修改，可能挤出热数据

7.2 分批处理

-- 分批删除（推荐方式）
SET @batch_size = 5000;
SET @affected = 1;

WHILE @affected > 0 DO
    DELETE FROM orders
    WHERE created_at < '2020-01-01'
    ORDER BY id
    LIMIT @batch_size;

    SET @affected = ROW_COUNT();

    -- 可选：每批之间暂停，给从库追赶的时间
    -- SELECT SLEEP(0.5);
END WHILE;

-- 实际操作中通常用应用层循环：
-- Python 示例
-- while True:
--     cursor.execute("""
--         DELETE FROM orders WHERE created_at < '2020-01-01' ORDER BY id LIMIT 5000
--     """)
--     if cursor.rowcount == 0:
--         break
--     connection.commit()
--     time.sleep(0.5)  # 控制主从延迟

要点：

• ORDER BY id LIMIT N 确保每批删除的是确定的行集，避免重复扫描
• 每批之间 COMMIT，释放锁和 undo log
• 批大小 1000-10000 行为宜，太小则网络往返多，太大则事务太长
• 每批之间 SLEEP 给从库追赶的窗口

7.3 分区交换删除

如果表按日期分区，可以用分区交换（ALTER TABLE ... EXCHANGE PARTITION）实现瞬间"删除"：

-- 前提：orders 表按月分区
-- 分区定义
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    ...
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 要删除 2023 年 1 月的数据
-- 方法 1: DROP PARTITION（瞬间完成，但分区定义也丢失）
ALTER TABLE orders DROP PARTITION p202301;

-- 方法 2: EXCHANGE + DROP（保留分区定义）
CREATE TABLE orders_archive LIKE orders;
ALTER TABLE orders_archive REMOVE PARTITIONING;
ALTER TABLE orders EXCHANGE PARTITION p202301 WITH TABLE orders_archive;
-- 数据现在在 orders_archive 中，orders 的 p202301 分区为空
-- 可以慢慢处理 orders_archive（归档或删除）
DROP TABLE orders_archive;  -- 或者归档到冷存储

7.4 pt-archiver 增量归档

# Percona Toolkit 的 pt-archiver 工具
# 自动分批删除/归档，支持主从延迟监控

# 删除旧数据（不归档）
pt-archiver \
    --source h=localhost,D=mydb,t=orders \
    --where "created_at < '2020-01-01'" \
    --limit 5000 \
    --commit-each \
    --check-slave-lag h=slave-host \
    --max-lag 5 \
    --purge

# 归档到另一个表
pt-archiver \
    --source h=localhost,D=mydb,t=orders \
    --dest h=localhost,D=archive_db,t=orders_archive \
    --where "created_at < '2020-01-01'" \
    --limit 5000 \
    --commit-each

# --check-slave-lag: 当从库延迟超过 --max-lag 秒时自动暂停
# --limit: 每批处理行数
# --commit-each: 每批提交一次

7.5 大批量 UPDATE 技巧

-- 分批 UPDATE（与 DELETE 类似）
-- 示例：将所有过期用户标记为 inactive
UPDATE users SET status = 'inactive'
WHERE last_login < '2023-01-01' AND status = 'active'
ORDER BY id
LIMIT 5000;

-- 使用 JOIN 的批量更新（跨表更新）
UPDATE orders o
JOIN order_items oi ON o.id = oi.order_id
SET o.total = (SELECT SUM(price * qty) FROM order_items WHERE order_id = o.id)
WHERE o.id BETWEEN 1 AND 5000;

-- 注意：大批量 UPDATE 如果修改了索引列，代价更高
-- 因为每个被修改的索引条目都需要：删除旧值 + 插入新值（change buffer 可以优化）

8. 查询改写模式

以下是 15+ 种常见的查询改写模式，每种都附带改写前后的对比和改写原因：

-- BEFORE: OR 条件无法同时使用两个索引
SELECT * FROM users WHERE email = '[email protected]' OR phone = '13800138000';
-- 可能全表扫描（除非有 index_merge）

-- AFTER: 拆成两条查询分别走索引
SELECT * FROM users WHERE email = '[email protected]'
UNION ALL
SELECT * FROM users WHERE phone = '13800138000' AND email != '[email protected]';
-- 第一条走 idx_email, 第二条走 idx_phone

-- BEFORE: MySQL 5.7 可能物化子查询或走 DEPENDENT SUBQUERY
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);

-- AFTER:
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip = 1);

-- BEFORE: 嵌套子查询
SELECT * FROM (SELECT user_id, SUM(amount) s FROM orders GROUP BY user_id) t WHERE t.s > 10000;

-- AFTER: CTE 更清晰，可复用
WITH user_totals AS (
    SELECT user_id, SUM(amount) AS s FROM orders GROUP BY user_id
)
SELECT * FROM user_totals WHERE s > 10000;

-- BEFORE: 每行一次子查询
SELECT e.*, (SELECT COUNT(*) FROM employees e2
    WHERE e2.dept_id = e.dept_id AND e2.salary > e.salary) + 1 AS salary_rank
FROM employees e;

-- AFTER: 窗口函数
SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
FROM employees e;

-- BEFORE: 3 次全表扫描
SELECT
    (SELECT COUNT(*) FROM orders WHERE status='pending') AS c1,
    (SELECT COUNT(*) FROM orders WHERE status='shipped') AS c2,
    (SELECT COUNT(*) FROM orders WHERE status='done') AS c3;

-- AFTER: 1 次扫描
SELECT
    SUM(status='pending') AS c1,
    SUM(status='shipped') AS c2,
    SUM(status='done') AS c3
FROM orders;

-- BEFORE: UNION 自动去重（需要排序或哈希去重）
SELECT user_id FROM orders WHERE created_at > '2024-01-01'
UNION
SELECT user_id FROM returns WHERE created_at > '2024-01-01';

-- AFTER: 如果业务不需要去重，使用 UNION ALL 避免排序
SELECT user_id FROM orders WHERE created_at > '2024-01-01'
UNION ALL
SELECT user_id FROM returns WHERE created_at > '2024-01-01';
-- 如果确实需要去重，在外层用 DISTINCT 可能更灵活

-- BEFORE: phone 是 VARCHAR，但传入数字值
SELECT * FROM users WHERE phone = 13800138000;
-- MySQL 将 phone 列隐式转换为数字来比较 → 索引失效！

-- AFTER: 使用正确的类型
SELECT * FROM users WHERE phone = '13800138000';
-- 走索引

-- BEFORE: 两个表字符集不同，JOIN 时需要隐式转换
-- users.name 是 utf8mb4, old_logs.user_name 是 utf8
SELECT * FROM users u JOIN old_logs l ON u.name = l.user_name;
-- EXPLAIN 会显示 Using join buffer (无法走索引)

-- AFTER: 统一字符集
ALTER TABLE old_logs MODIFY user_name VARCHAR(100) CHARACTER SET utf8mb4;
-- 或者使用 CONVERT 临时解决（但仍然无法走索引）
-- SELECT * FROM users u JOIN old_logs l ON u.name = CONVERT(l.user_name USING utf8mb4);

-- BEFORE: 前缀模糊匹配无法使用 B+Tree 索引
SELECT * FROM articles WHERE title LIKE '%优化%';

-- AFTER: 全文索引 (InnoDB, MySQL 5.6+)
ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;

SELECT * FROM articles WHERE MATCH(title) AGAINST('优化' IN BOOLEAN MODE);
-- 或者迁移到 Elasticsearch 处理复杂全文检索

-- BEFORE: 函数作用于列，索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2024-03-15';

-- AFTER 方案 A: 改写为范围条件
SELECT * FROM orders
WHERE created_at >= '2024-03-15 00:00:00'
  AND created_at <  '2024-03-16 00:00:00';

-- AFTER 方案 B: 函数索引 (MySQL 8.0.13+)
ALTER TABLE orders ADD INDEX idx_date ((DATE(created_at)));
-- 原查询无需修改即可走索引

-- BEFORE: NOT IN 有 NULL 陷阱且可能性能差
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist);

-- AFTER:
SELECT u.* FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL;

-- BEFORE: 返回所有列，即使只需要几列
SELECT * FROM orders WHERE user_id = 42;

-- AFTER: 可以命中覆盖索引
SELECT id, amount, status FROM orders WHERE user_id = 42;
-- 如果有索引 idx(user_id, amount, status)，则走覆盖索引，无需回表

-- BEFORE
SELECT * FROM articles ORDER BY id DESC LIMIT 100000, 20;

-- AFTER
SELECT * FROM articles WHERE id < 上一页最后的id ORDER BY id DESC LIMIT 20;

-- BEFORE: 跨表删除
DELETE o, oi FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at < '2020-01-01';
-- 锁竞争严重

-- AFTER: 先子表再父表（或用外键 CASCADE）
DELETE FROM order_items WHERE order_id IN (
    SELECT id FROM orders WHERE created_at < '2020-01-01'
) LIMIT 5000;
-- 然后
DELETE FROM orders WHERE created_at < '2020-01-01' LIMIT 5000;

-- REPLACE = DELETE + INSERT（触发删除触发器，重新分配 AUTO_INCREMENT）
-- INSERT ... ON DUPLICATE KEY UPDATE = 真正的 UPDATE（保留原行 ID）

-- 推荐使用 INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO daily_stats (stat_date, cnt) VALUES ('2024-03-15', 100)
ON DUPLICATE KEY UPDATE cnt = VALUES(cnt);
-- 而不是
REPLACE INTO daily_stats (stat_date, cnt) VALUES ('2024-03-15', 100);

-- BEFORE: 应用层多次查询
-- query1: SELECT AVG(salary) FROM emp WHERE dept='Eng';
-- query2: SELECT AVG(salary) FROM emp WHERE dept='Sales';
-- query3: SELECT AVG(salary) FROM emp WHERE dept='HR';

-- AFTER: 一次查询
SELECT
    AVG(CASE WHEN dept='Eng' THEN salary END) AS eng_avg,
    AVG(CASE WHEN dept='Sales' THEN salary END) AS sales_avg,
    AVG(CASE WHEN dept='HR' THEN salary END) AS hr_avg
FROM emp;

9. 查询性能分析

9.1 SHOW PROFILE（已弃用但仍有用）

-- 开启 profiling
SET profiling = 1;

-- 执行你要分析的查询
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

-- 查看概要
SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                               |
+----------+------------+---------------------------------------------------------------------+
|        1 | 0.00234    | SELECT * FROM orders WHERE user_id = 42 ORDER BY ...                |
+----------+------------+---------------------------------------------------------------------+

-- 查看详细分阶段耗时
SHOW PROFILE FOR QUERY 1;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       | 0.000012  |
| checking permissions           | 0.000005  |
| Opening tables                 | 0.000018  |
| init                           | 0.000025  |
| System lock                    | 0.000008  |
| optimizing                     | 0.000015  |
| statistics                     | 0.000042  |
| preparing                      | 0.000012  |
| Sorting result                 | 0.000003  |
| executing                      | 0.001850  | ← 主要耗时在执行阶段
| end                            | 0.000005  |
| query end                      | 0.000004  |
| closing tables                 | 0.000006  |
| freeing items                  | 0.000015  |
| cleaning up                    | 0.000003  |
+--------------------------------+-----------+

-- 查看 I/O 和 CPU 详情
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

9.2 Performance Schema

Performance Schema 是 MySQL 内置的性能监控框架，提供细粒度的查询级性能数据：

-- 查看最慢的 10 条查询（按总执行时间）
SELECT
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 3) AS total_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_sec,
    ROUND(MAX_TIMER_WAIT / 1e12, 3) AS max_sec,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent,
    ROUND(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 1) AS examine_per_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看特定查询的各阶段耗时
SELECT
    EVENT_NAME AS stage,
    ROUND(TIMER_WAIT / 1e9, 2) AS ms
FROM performance_schema.events_stages_history
WHERE NESTING_EVENT_ID = (
    SELECT EVENT_ID FROM performance_schema.events_statements_history
    WHERE SQL_TEXT LIKE '%your_query_pattern%'
    ORDER BY TIMER_START DESC LIMIT 1
)
ORDER BY TIMER_START;

9.3 sys 库

sys 库是 Performance Schema 的便利封装，提供人类可读的视图：

-- 最耗资源的查询语句
SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;

-- 全表扫描的查询
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- 使用临时表的查询
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

-- 排序量最大的查询
SELECT * FROM sys.statements_with_sorting ORDER BY sort_merged_passes DESC LIMIT 10;

-- 从未使用的索引（可以考虑删除）
SELECT * FROM sys.schema_unused_indexes;

-- 冗余索引
SELECT * FROM sys.schema_redundant_indexes;

9.4 优化器追踪 (optimizer_trace)

当你想理解优化器为什么选择了某个执行计划（或为什么不选择你期望的计划）时，optimizer_trace 是最终武器：

-- 开启 optimizer trace
SET optimizer_trace = 'enabled=on';

-- 执行查询
SELECT * FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending' ORDER BY o.created_at DESC LIMIT 10;

-- 查看完整的优化器决策过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 重点关注的 JSON 段落：
-- "join_optimization" → "rows_estimation"：各表各索引的行数估算
-- "considered_execution_plans"：评估过的 JOIN 顺序和对应代价
-- "chosen_plan"：最终选定的计划
-- "reconsidering_access_paths_for_index_ordering"：是否因 ORDER BY 改变了索引选择

-- 关闭（否则每条查询都记录，有性能影响）
SET optimizer_trace = 'enabled=off';

9.5 EXPLAIN ANALYZE 工作流

EXPLAIN ANALYZE（MySQL 8.0.18+）不仅显示执行计划，还会实际执行查询并收集每个操作的真实统计：

EXPLAIN ANALYZE
SELECT o.*, u.name FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 10\G

-- 输出示例：
-- -> Limit: 10 row(s)  (cost=45.2 rows=10) (actual time=0.85..0.92 rows=10 loops=1)
--     -> Sort: o.created_at DESC, limit input to 10 row(s) per chunk
--        (cost=45.2 rows=100) (actual time=0.85..0.89 rows=10 loops=1)
--         -> Nested loop join  (cost=45.2 rows=100)
--            (actual time=0.12..0.78 rows=100 loops=1)
--             -> Index lookup on o using idx_status (status='pending')
--                (cost=22.1 rows=100) (actual time=0.08..0.35 rows=100 loops=1)
--             -> Single-row index lookup on u using PRIMARY (id=o.user_id)
--                (cost=0.25 rows=1) (actual time=0.003..0.003 rows=1 loops=100)

-- 关键对比：
-- cost vs actual time：估算 vs 实际
-- rows (估算) vs rows (实际)：差异大说明统计信息不准确
-- loops：该操作被执行了多少次

10. 查询提示

10.1 传统索引提示

-- USE INDEX：建议优化器使用指定索引（优化器仍可能忽略）
SELECT * FROM orders USE INDEX (idx_user_date)
WHERE user_id = 42 AND created_at > '2024-01-01';

-- FORCE INDEX：强制使用指定索引（比 USE INDEX 更强硬）
SELECT * FROM orders FORCE INDEX (idx_user_date)
WHERE user_id = 42 AND created_at > '2024-01-01';

-- IGNORE INDEX：排除指定索引
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'pending' AND user_id = 42;

-- 用例：优化器因为统计信息不准而选错索引时

10.2 MySQL 8.0 优化器提示（Optimizer Hints）

MySQL 8.0 引入了标准化的 /*+ hint */ 语法，比传统索引提示更强大且粒度更细：

-- JOIN 顺序控制
SELECT /*+ JOIN_ORDER(u, o) */ *
FROM orders o JOIN users u ON o.user_id = u.id;
-- 强制 users 作为驱动表，orders 作为被驱动表

-- JOIN 算法控制
SELECT /*+ BNL(o) NO_BNL(u) */ *    -- 5.7 ~ 8.0.19
FROM orders o JOIN users u ON o.user_id = u.id;

SELECT /*+ HASH_JOIN(o) NO_HASH_JOIN(u) */ *  -- 8.0.18+
FROM orders o JOIN users u ON o.user_id = u.id;

-- 禁用 Index Condition Pushdown
SELECT /*+ NO_ICP(orders) */ *
FROM orders WHERE user_id = 42 AND amount > 100;

-- 合并/不合并派生表
SELECT /*+ MERGE(derived_t) */ * FROM (SELECT ...) AS derived_t;
SELECT /*+ NO_MERGE(derived_t) */ * FROM (SELECT ...) AS derived_t;

-- Semi-join 策略控制
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ *
FROM orders WHERE user_id IN (SELECT /*+ QB_NAME(subq1) */ id FROM users);

-- SET_VAR：在查询执行期间临时修改系统变量
SELECT /*+ SET_VAR(sort_buffer_size = 8388608) */ *
FROM articles ORDER BY score DESC LIMIT 100;

SELECT /*+ SET_VAR(join_buffer_size = 16777216) */ *
FROM big_table1 JOIN big_table2 ON ...;

-- 并行查询 (MySQL 8.0.14+, 企业版)
SELECT /*+ SET_VAR(innodb_parallel_read_threads = 4) */ COUNT(*) FROM big_table;

10.3 何时使用提示

• 合理使用：统计信息不准确导致选错索引；已知数据分布但优化器无法推断；特殊的分页查询需要强制索引
• 代码异味（Code Smell）：如果你大量使用 hints，说明底层可能有更根本的问题——索引设计不当、统计信息过时、查询结构需要重构
• 维护负担：hints 把执行计划决策从优化器转移到了应用代码中。表结构变更、数据量变化后，hints 可能变得有害
• 建议：先尝试 ANALYZE TABLE 更新统计信息，再考虑索引优化，最后才用 hints 作为临时 workaround

10.4 optimizer_switch 标志

-- 查看当前所有开关
SELECT @@optimizer_switch\G

-- 常用开关说明
SET optimizer_switch = '
    index_merge=on,                    -- Index Merge 优化
    index_merge_union=on,              -- Index Merge Union
    index_merge_sort_union=on,         -- Index Merge Sort-Union
    index_merge_intersection=on,       -- Index Merge Intersection
    engine_condition_pushdown=on,      -- 条件下推到存储引擎
    index_condition_pushdown=on,       -- ICP：索引条件下推
    mrr=on,                            -- Multi-Range Read
    mrr_cost_based=on,                 -- MRR 基于代价决策
    block_nested_loop=on,              -- BNL / Hash Join
    batched_key_access=off,            -- BKA（默认关闭）
    materialization=on,                -- 子查询物化
    semijoin=on,                       -- Semi-join 变换
    loosescan=on,                      -- LooseScan 策略
    firstmatch=on,                     -- FirstMatch 策略
    duplicateweedout=on,               -- DuplicateWeedout 策略
    subquery_materialization_cost_based=on, -- 基于代价选择物化
    use_invisible_indexes=off,         -- 是否使用不可见索引
    skip_scan=on,                      -- Skip Scan (8.0.13+)
    hash_join=on,                      -- Hash Join (8.0.18+)
    subquery_to_derived=off,           -- 子查询转派生表 (8.0.21+)
    prefer_ordering_index=on,          -- 优先使用排序索引 (8.0.21+)
    hypergraph_optimizer=off           -- 超图优化器 (8.0.31+, 实验性)
';

-- 单独修改某个开关
SET optimizer_switch = 'batched_key_access=on';

-- Session 级别修改，不影响其他连接

11. 真实案例

-- 原始查询（响应时间 3.5s）
SELECT * FROM products
WHERE title LIKE '%蓝牙耳机%'
  AND category_id = 42
  AND price BETWEEN 100 AND 500
  AND rating >= 4.0
ORDER BY sales_count DESC
LIMIT 100000, 20;

-- 问题分析：
-- 1. LIKE '%...%' 无法使用 B+Tree 索引
-- 2. 多列过滤导致索引选择困难
-- 3. ORDER BY sales_count 与过滤条件的索引不一致 → filesort
-- 4. LIMIT 100000, 20 大偏移量扫描

-- EXPLAIN 输出：
-- type: ALL (全表扫描)
-- Extra: Using where; Using filesort

-- Step 1: 全文检索替代 LIKE
ALTER TABLE products ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;

-- Step 2: 联合索引覆盖过滤 + 排序
ALTER TABLE products ADD INDEX idx_cat_rating_sales (category_id, rating, sales_count);

-- Step 3: 重写查询
-- 使用全文检索 + 联合索引 + 延迟关联
SELECT p.* FROM products p
INNER JOIN (
    SELECT id FROM products
    WHERE MATCH(title) AGAINST('蓝牙耳机' IN BOOLEAN MODE)
      AND category_id = 42
      AND rating >= 4.0
      AND price BETWEEN 100 AND 500
    ORDER BY sales_count DESC
    LIMIT 100000, 20
) AS tmp ON p.id = tmp.id;

-- Step 4: 前端改为游标分页（最优）
SELECT p.* FROM products p
WHERE MATCH(title) AGAINST('蓝牙耳机' IN BOOLEAN MODE)
  AND category_id = 42
  AND rating >= 4.0
  AND price BETWEEN 100 AND 500
  AND (sales_count, id) < (上一页最后的sales_count, 上一页最后的id)
ORDER BY sales_count DESC, id DESC
LIMIT 20;

-- 优化结果：3.5s → 45ms（延迟关联）→ 8ms（游标分页）

-- 原始查询（响应时间 12s）
SELECT
    DATE(created_at) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refund_count
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY day;

-- 问题分析：
-- 1. DATE(created_at) 函数作用于列，即使有索引也用不上索引分组
-- 2. 扫描 30 天的所有行（可能几十万到上百万行）
-- 3. GROUP BY 需要临时表 + filesort

-- 添加覆盖索引
ALTER TABLE orders ADD INDEX idx_created_status_amount (created_at, status, amount);

-- 改写：避免 DATE() 函数
SELECT
    DATE(created_at) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    SUM(status = 'refunded') AS refund_count
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND created_at < CURDATE() + INTERVAL 1 DAY
GROUP BY DATE(created_at)
ORDER BY day;
-- 走覆盖索引的范围扫描，12s → 1.2s

-- 创建日级汇总表
CREATE TABLE daily_order_summary (
    summary_date DATE PRIMARY KEY,
    order_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
    refund_count INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 定时任务每小时刷新当天数据
INSERT INTO daily_order_summary (summary_date, order_count, total_amount, refund_count)
SELECT
    DATE(created_at),
    COUNT(*),
    SUM(amount),
    SUM(status = 'refunded')
FROM orders
WHERE created_at >= CURDATE()
  AND created_at < CURDATE() + INTERVAL 1 DAY
ON DUPLICATE KEY UPDATE
    order_count = VALUES(order_count),
    total_amount = VALUES(total_amount),
    refund_count = VALUES(refund_count);

-- 仪表盘查询：瞬间返回
SELECT * FROM daily_order_summary
WHERE summary_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY summary_date;
-- 1.2s → 0.5ms

-- 表结构
-- follows (follower_id, following_id): 500 万行
-- posts (id, user_id, content, created_at): 5000 万行

-- 原始查询（响应时间 8s）
SELECT p.* FROM posts p
JOIN follows f ON p.user_id = f.following_id
WHERE f.follower_id = 12345
ORDER BY p.created_at DESC
LIMIT 20;

-- 问题分析：
-- 1. 用户关注了 500 人 → JOIN 后可能匹配 50 万条动态
-- 2. 所有 50 万条都需要排序（filesort）
-- 3. 最后只取 20 条

-- EXPLAIN:
-- follows: ref (idx_follower), rows=500
-- posts: ref (idx_user_id), rows=1000 (估算每人 1000 条)
-- Extra: Using temporary; Using filesort

-- 在 posts 上创建覆盖 user_id + created_at 的联合索引
ALTER TABLE posts ADD INDEX idx_user_created (user_id, created_at);

-- 改写：每个关注者取 top 20，再合并排序
-- 但 MySQL 不支持 "per-group top N"（需要 LATERAL JOIN 或应用层处理）

-- 方案：利用 UNION ALL + 应用层合并
-- 对每个关注的用户取最近 20 条，应用层合并排序
-- 但关注 500 人就要 500 个查询...不现实

-- 创建用户动态收件箱表
CREATE TABLE feed_inbox (
    user_id BIGINT NOT NULL,        -- 接收者（follower）
    post_id BIGINT NOT NULL,        -- 动态 ID
    post_user_id BIGINT NOT NULL,   -- 发布者
    created_at DATETIME NOT NULL,   -- 动态创建时间
    PRIMARY KEY (user_id, created_at, post_id),
    INDEX idx_user_time (user_id, created_at DESC)
) ENGINE=InnoDB;

-- 发布动态时，异步写入所有 follower 的收件箱
-- INSERT INTO feed_inbox (user_id, post_id, post_user_id, created_at)
-- SELECT follower_id, NEW.id, NEW.user_id, NEW.created_at
-- FROM follows WHERE following_id = NEW.user_id;

-- 查询动态流：极快（覆盖索引 + 顺序扫描）
SELECT fi.post_id, p.content, p.created_at, u.name
FROM feed_inbox fi
JOIN posts p ON fi.post_id = p.id
JOIN users u ON fi.post_user_id = u.id
WHERE fi.user_id = 12345
ORDER BY fi.created_at DESC
LIMIT 20;

-- 8s → 3ms
-- 代价：写入放大（一条动态写入 N 个粉丝的收件箱）
-- 适合"读多写少"的社交场景
-- 大 V（百万粉丝）可以用拉模式或混合模式

-- 第 2 页（使用上一页最后一条的时间戳和 ID）
SELECT fi.post_id, p.content, p.created_at
FROM feed_inbox fi
JOIN posts p ON fi.post_id = p.id
WHERE fi.user_id = 12345
  AND (fi.created_at, fi.post_id) < ('2024-03-15 10:30:00', 98765)
ORDER BY fi.created_at DESC, fi.post_id DESC
LIMIT 20;
-- 恒定 3ms，无论翻到第几页

12. 反模式清单

以下是 20 个常见的查询反模式。每一条都可能导致严重的性能问题：

13. 常见问题