第一章 引言

在你刚接触 MySQL 的时候，可能会发现：查询一张小表的数据几乎是“秒出”，但一旦表的数据量达到百万甚至上亿行，查询性能就会急剧下降。为什么会这样？核心原因就在于索引的使用与设计。

索引在数据库中扮演的角色，可以类比为一本书的目录：

• 如果没有目录，你要找到一本书里某个知识点，就只能从第一页一页往下翻，直到找到结果。

• 有了目录，你只需要先找到目录页，定位章节，再快速跳转到对应的内容。

这就是索引的本质：提升数据查询效率。

然而，索引并不是越多越好，也不是随意添加就能解决问题。很多时候，如果设计不合理，索引反而会拖慢写入性能，甚至在查询中失效，造成“看似有索引，实则无效”的尴尬局面。

在实际工作中，你可能会遇到以下问题：

1. 为什么我明明加了索引，查询还是很慢？

2. 联合索引是不是要包含所有查询字段？

3. 索引失效有哪些典型场景？

4. 如何通过EXPLAIN分析索引的执行效果？

本系列文章将从以下几个层面展开：

1. 索引原理 —— 深入剖析B+Tree、主键索引、二级索引、哈希索引与全文索引。

2. 核心特性 —— 理解回表、覆盖索引、最左前缀原则，以及MySQL 5.6后引入的索引下推（ICP）。

3. 高级优化 —— 探索索引合并、索引跳跃和常见的索引失效场景。

4. 设计实践 —— 如何在实际项目中选择单列索引或联合索引，如何避免冗余索引。

5. 实战案例 —— 通过慢查询日志优化和错误的索引设计案例，掌握真正的调优方法。

你将学会的不仅是如何创建索引，更重要的是如何设计高效的索引策略，让数据库在处理大规模数据时依然保持流畅。

在接下来的章节中，我们将从索引的本质与分类开始，逐步构建一个完整的MySQL索引知识体系。

第二章 索引的本质与分类

要理解 MySQL索引的威力与局限，必须先搞清楚它的底层结构与分类。只有掌握这些原理，才能在实际业务中合理选择索引类型，从而避免“盲目加索引”带来的性能陷阱。

本章我们将从 B+Tree数据结构、主键索引与二级索引的区别、哈希索引与全文索引的适用场景 三个角度，全面剖析索引的本质。

---

2.1 B+Tree数据结构详解

MySQL最常见的索引结构是 B+Tree，特别是在 InnoDB 引擎中，几乎所有的索引（包括主键和二级索引）都采用了B+Tree。

为什么选择B+Tree而不是二叉树？

• 二叉查找树 在极端情况下会退化成链表，查询效率大幅下降。

• AVL树（平衡二叉树） 查找效率高，但维护平衡的代价过高。

• B-Tree 允许每个节点存储多个元素，减少了树的高度。

• B+Tree 在B-Tree基础上优化：

  1. 只有叶子节点存储数据，非叶子节点只存储键值用于索引。

  2. 叶子节点通过链表连接，支持范围查询，效率极高。

形象类比：如果把数据库看作一本书，B+Tree 就像是目录页（非叶子节点）+ 实际内容页（叶子节点），并且所有内容页之间有书签串联，方便顺序阅读。

B+Tree的查找过程

假设我们要查找 id=20 的数据：

1. 从根节点开始，根据范围定位到某个子节点。

2. 进入对应子节点，继续查找，直到叶子节点。

3. 在叶子节点找到精确的值或判断不存在。

其时间复杂度大约是 O(log n)，而由于B+Tree的高度一般很低（百万级数据大约3-4层），所以查找效率非常稳定。

示例：InnoDB中的主键索引（聚簇索引）

-- 创建一个测试表
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_name (name)
) ENGINE=InnoDB;

-- 插入数据
INSERT INTO users (id, name, age) VALUES
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 35);

在 InnoDB 中：

• PRIMARY KEY (id) 会生成一个 聚簇索引，B+Tree 的叶子节点直接存放整行数据。

• INDEX idx_name (name) 是一个 二级索引，叶子节点存放的是 name + 主键id，查询时可能会触发 回表。

---

2.2 主键索引 vs 二级索引

在 InnoDB 中，索引分为 聚簇索引（Clustered Index） 和 二级索引（Secondary Index）。

1. 主键索引（聚簇索引）

• 每张表 必须且只能有一个 聚簇索引。

• 默认情况下，PRIMARY KEY 就是聚簇索引。

• 叶子节点存放的是 整行记录。

• 如果没有定义主键，InnoDB会选择：

  1. 唯一且非空的索引；

  2. 如果没有，则生成一个隐藏的 row_id。

优点：

• 主键查询性能极高。

• 按主键排序的范围查询效率非常好。

缺点：

• 如果主键过大（如UUID），会导致聚簇索引占用更多空间。

2. 二级索引

• 叶子节点存放的是 索引列值 + 主键值。

• 查询时若只用二级索引，通常需要 回表 去聚簇索引查找完整数据。

示例：主键索引与二级索引查询差异

-- 主键索引查询（直接命中）
EXPLAIN SELECT * FROM users WHERE id = 2;

-- 二级索引查询（可能触发回表）
EXPLAIN SELECT * FROM users WHERE name = 'Bob';

EXPLAIN解读：

• 主键查询会直接使用 PRIMARY 索引，效率最高。

• 二级索引查询则可能出现 type=ref，Extra字段中若提示 Using index，说明是覆盖索引，否则可能要回表。

---

2.3 哈希索引与全文索引的适用场景

除了B+Tree，MySQL中还有 哈希索引 和 全文索引，它们在不同场景下发挥重要作用。

1. 哈希索引（Hash Index）

• 原理：通过哈希函数将键值映射为哈希表中的位置，查询效率接近 O(1)。

• 特点：

  1. 只支持 等值查询（= 或 IN），不支持范围查询。

  2. 顺序访问效率差。

  3. 哈希冲突需要链表解决。

• 存储引擎支持：

  • Memory引擎 默认使用哈希索引。

  • InnoDB 也会在某些情况下内部使用自适应哈希索引（Adaptive Hash Index, AHI）。

示例：

-- 使用 Memory 引擎创建表（默认哈希索引）
CREATE TABLE sessions (
  session_id CHAR(64) PRIMARY KEY,
  user_id INT
) ENGINE=MEMORY;

适合场景：缓存、临时表、唯一值查找。

---

2. 全文索引（Fulltext Index）

• 原理：基于倒排索引，将文档中的单词拆分，建立映射关系，适合做全文搜索。

• 支持语法：

  CREATE FULLTEXT INDEX idx_content ON articles(content);
  SELECT * FROM articles WHERE MATCH(content) AGAINST('mysql 索引');
  

• 存储引擎支持：MySQL 5.6 开始 InnoDB 也支持全文索引。

适用场景：

• 文章内容检索

• 日志系统关键字搜索

• 类似搜索引擎的“模糊查询”

对比 LIKE 查询：

• LIKE '%keyword%' 无法使用普通索引，只能全表扫描。

• 全文索引 能够大幅提升检索性能。

---

小结

在本章中，你学习了：

1. B+Tree索引 是MySQL的核心，支持范围查询与排序，性能稳定。

2. 主键索引（聚簇索引） 与 二级索引 的区别在于存储内容和是否回表。

3. 哈希索引 适合等值查询，全文索引 适合大文本检索。

在实际设计中，大多数业务查询基于B+Tree索引，而哈希索引和全文索引则作为补充工具，适合特定场景。

第三章 核心索引特性解析

在上一章我们了解了索引的底层原理和分类，本章将深入探讨 索引的核心特性，包括 回表、覆盖索引、联合索引的最左前缀原则以及索引下推（ICP）。这些概念看似抽象，但它们决定了查询是否能真正利用索引，直接影响SQL的性能表现。

---

3.1 回表操作与避免策略

什么是回表？

在 InnoDB 引擎中：

• 聚簇索引 的叶子节点存储整行数据。

• 二级索引 的叶子节点存储 索引列 + 主键值。

当你通过二级索引查找数据时，可能需要 再次回到聚簇索引查找整行数据，这就是 回表。

类比：你通过目录（索引）找到了书中某一页的页码（主键），但为了看完整的内容，你还得再翻到那一页。

示例

-- 通过二级索引查找
EXPLAIN SELECT * FROM users WHERE name = 'Alice';

执行流程：

1. 通过 idx_name（二级索引）找到 name='Alice' 对应的主键 id=1。

2. 再回到聚簇索引，根据 id=1 找到完整数据行。

EXPLAIN输出（简化版）：

type: ref
key: idx_name
Extra: Using where

这里 Extra 没有出现 Using index，说明需要回表。

---

如何避免回表？ —— 覆盖索引

如果查询的字段 全部包含在二级索引中，那么就不需要回表了，这就是 覆盖索引。

-- 查询只涉及索引列，不需要回表
EXPLAIN SELECT name FROM users WHERE name = 'Alice';

EXPLAIN输出：

type: ref
key: idx_name
Extra: Using index

此时 Using index 表示命中了覆盖索引，避免了回表。

---

优化建议

1. 如果只需要索引列，尽量写成 覆盖索引查询。

2. 在高并发系统中，覆盖索引可以大幅减少IO开销。

3. 不要盲目使用 SELECT *，它往往导致回表。

---

3.2 覆盖索引的实现原理

原理解析

覆盖索引（Covering Index）就是：索引包含了查询所需的全部字段，这样存储引擎只需要扫描索引本身，而无需访问数据行。

示例

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引查询
EXPLAIN SELECT name, age FROM users WHERE name = 'Bob';

EXPLAIN输出：

type: ref
key: idx_name_age
Extra: Using index

这里 Extra=Using index 表示查询仅依赖索引即可返回结果。

优势

• 避免回表，大幅减少磁盘IO。

• 对于只读查询性能提升显著。

注意事项

• 覆盖索引并不适合所有场景。

• 如果列过多，建立大宽度索引会占用大量空间，反而得不偿失。

---

3.3 联合索引的最左前缀原则

什么是最左前缀原则？

在MySQL中，如果建立了联合索引 (col1, col2, col3)，那么查询时必须满足 从最左边的列开始匹配，索引才能生效。

即：

• 可以使用 (col1)

• 可以使用 (col1, col2)

• 可以使用 (col1, col2, col3)

• 但不能直接使用 (col2) 或 (col3)

类比：如果字典目录是按「姓氏 → 名字 → 拼音」排序的，你必须先知道姓氏，才能用目录快速查找。

---

示例

-- 创建联合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 合法使用（命中最左前缀）
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age = 25;

-- 非法使用（不能直接跳过name）
EXPLAIN SELECT * FROM users WHERE age = 25;

结果：

• 前两条查询会使用 idx_name_age_city。

• 最后一条由于没有 name 条件，索引会失效，变成全表扫描。

---

常见误区

1. 误区一：联合索引必须包含所有字段才能生效

   • 错！只要满足最左前缀，就能利用部分索引。

2. 误区二：OR 条件也能用索引

   • 一般情况下 OR 会导致索引失效，除非使用 索引合并（后续章节讲解）。

---

3.4 索引下推（Index Condition Pushdown, ICP）

什么是索引下推？

在MySQL 5.6之前，索引只能过滤索引列，非索引列条件必须等回表后再判断。这样会产生大量不必要的回表。

从MySQL 5.6开始，支持 索引下推优化：在存储引擎层提前判断部分条件，减少回表次数。

---

示例

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 查询条件包含索引列和非索引列
EXPLAIN SELECT * FROM users 
WHERE name = 'Alice' AND age > 25;

执行过程：

1. 通过 name='Alice' 命中二级索引。

2. 以前：所有 name='Alice' 的行都会回表，再判断 age>25。

3. 现在（ICP）：在索引层就能判断 age>25，只回表满足条件的记录。

---

优势

• 显著减少回表次数。

• 提升复杂条件查询的效率。

版本说明

• MySQL 5.6 引入 ICP。

• MySQL 8.0 在更多场景下支持下推优化（如表达式条件）。

---

小结

在本章你学习了：

1. 回表 是二级索引常见现象，覆盖索引 能有效避免。

2. 联合索引必须遵循最左前缀原则，否则会失效。

3. 索引下推（ICP） 能减少不必要的回表，大幅提升性能。

一个高效的SQL，往往依赖于对索引特性的深刻理解。

第四章 高级优化技术

在前几章我们已经掌握了索引的基本原理与特性，本章将进一步探讨 高级优化技术，包括 索引合并、索引跳跃扫描（Index Skip Scan, ISS）、索引失效场景。这些优化手段往往出现在复杂查询或大规模数据场景中，理解它们有助于你在实际项目中避免“明明有索引，却查询很慢”的尴尬。

---

4.1 索引合并的三种类型（AND/OR/UNION）

在某些场景下，一个查询可能会涉及多个条件字段，而这些字段各自有索引，但并没有建立联合索引。此时，MySQL会尝试 索引合并（Index Merge），利用多个单列索引共同完成一次查询。

类比：你想找一本“作者=张三 且 出版社=清华”的书，如果目录里分别有“作者索引”和“出版社索引”，那么索引合并就是把两份名单交叉对比，最终得到结果。

---

1. AND 索引合并

原理：当查询条件中有 AND 且每个条件字段都有单独索引时，MySQL会对多个索引结果取交集。

示例：

-- 为 name 和 age 分别建立索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);

-- 查询条件涉及两个单列索引
EXPLAIN SELECT * FROM users WHERE name='Alice' AND age=30;

执行过程：

1. 扫描 idx_name，得到所有 name='Alice' 的主键集合。

2. 扫描 idx_age，得到所有 age=30 的主键集合。

3. 对两个结果集取交集。

EXPLAIN输出关键：

key: idx_name, idx_age
Extra: Using intersect(idx_name, idx_age)

---

2. OR 索引合并

原理：当查询条件中有 OR，MySQL可以对多个索引结果取并集。

示例：

EXPLAIN SELECT * FROM users WHERE name='Alice' OR age=30;

执行过程：

1. 扫描 idx_name 获取主键集合。

2. 扫描 idx_age 获取主键集合。

3. 对两个结果集合并。

EXPLAIN输出关键：

key: idx_name, idx_age
Extra: Using union(idx_name, idx_age)

---

3. UNION 索引合并

这是 AND/OR 合并的一种混合形式，查询条件较复杂时可能出现。

示例：

EXPLAIN SELECT * FROM users 
WHERE (name='Alice' AND age=30) OR city='Beijing';

MySQL可能会先做 (idx_name ∩ idx_age) 的交集，再和 idx_city 做并集。

---

索引合并的优缺点

优点：

• 不需要建立联合索引，也能利用多个单列索引。

• 对临时查询很有帮助。

缺点：

• 结果集的合并需要额外开销。

• 性能通常 不如联合索引。

优化建议：

• 如果某个查询非常频繁，建议建立 联合索引，避免依赖索引合并。

• 索引合并更适合 临时性、低频查询。

---

4.2 索引跳跃扫描（Index Skip Scan, ISS）

在 MySQL 8.0 之前，如果你在联合索引中“跳过”了最左列，索引通常会失效。
从 MySQL 8.0 开始，引入了 索引跳跃扫描（Index Skip Scan, ISS），弥补了这一问题。

---

工作原理

假设有联合索引 (f1, f2)，如果查询条件是：

SELECT f1, f2 FROM t1 WHERE f2 > 40;

在 MySQL 8.0 中，执行计划可能显示：

Using where; Using index for skip scan

这意味着 MySQL 会针对每一个 f1 的唯一值，构造出形如：

f1 = X AND f2 > 40

的子查询进行范围扫描，然后将这些子查询的结果合并返回。

---

应用场景

1. 低选择性的第一列

   • 当联合索引的第一列只有少量不同的值时（例如性别只有男女），ISS 可以显著减少扫描的数据量。

2. 非最左前缀匹配

   • 即使查询条件没有覆盖到联合索引的最左列，只要后面的列足够有选择性，ISS 也可以帮助提高查询速度。

---

示例

-- 创建联合索引
CREATE INDEX idx_f1_f2 ON t1(f1, f2);

-- 使用非最左前缀列进行过滤
EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

EXPLAIN输出：

type: range
key: idx_f1_f2
Extra: Using where; Using index for skip scan

这表明 MySQL 使用了 索引跳跃扫描 来避免全表扫描。

---

优缺点

优点：

• 在缺少完整的最左前缀时，依然能利用索引。

• 大幅优化了部分联合索引的使用场景。

缺点：

• 对于第一列基数很大（唯一值很多）的场景，ISS 反而会导致性能下降。

• 不能替代合理的联合索引设计。

---

4.3 索引失效的典型场景分析

即使有索引，MySQL也可能选择 不使用索引，典型场景包括：

1. 使用 LIKE 模糊匹配（前缀通配符）

-- 索引失效
EXPLAIN SELECT * FROM users WHERE name LIKE '%Alice%';

-- 索引有效
EXPLAIN SELECT * FROM users WHERE name LIKE 'Alice%';

原因：%Alice% 前缀不确定，无法利用B+Tree排序。

---

2. 对索引列进行函数或计算

-- 索引失效
EXPLAIN SELECT * FROM users WHERE YEAR(create_time) = 2023;

-- 优化方案：改写查询
EXPLAIN SELECT * FROM users 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

---

3. 隐式类型转换

-- age 是 INT 类型，字符串导致索引失效
EXPLAIN SELECT * FROM users WHERE age = '30';

优化：确保类型一致。

---

4. 使用 OR 条件（未触发索引合并）

-- 可能导致全表扫描
EXPLAIN SELECT * FROM users WHERE name='Alice' OR age=30;

---

5. 范围查询导致后续列失效

-- 索引 (name, age, city)
EXPLAIN SELECT * FROM users 
WHERE name='Alice' AND age>20 AND city='Beijing';

由于 age>20 是范围查询，city 列无法继续利用索引。

---

小结

在本章中，你学习了：

1. 索引合并（AND、OR、UNION），能在没有联合索引的情况下利用多个单列索引，但性能通常不如联合索引。

2. 索引跳跃扫描（ISS） 是 MySQL 8.0 的新特性，在非最左前缀条件下依然能利用联合索引。

3. 索引失效的典型场景 包括模糊匹配、函数计算、类型转换、范围查询等。

记住：MySQL 8.0 的 索引跳跃扫描 并不是万能的，它更像是一种补救机制，合理设计索引仍然是核心。

第五章 索引设计实践

前面几章我们主要从 原理、分类、特性、优化技术 等角度理解了索引的工作机制，本章将落地到 实际设计层面，探讨如何结合业务查询模式来设计合理的索引。一个良好的索引设计，往往能在 性能、存储空间、维护成本 之间取得平衡。

---

5.1 单列索引与联合索引的取舍

单列索引

• 优点：简单、维护成本低、适合只针对单一字段的高频查询。

• 缺点：当查询条件涉及多个字段时，往往需要依赖 索引合并，性能通常不如联合索引。

示例：

CREATE INDEX idx_age ON users(age);

适合查询：

SELECT * FROM users WHERE age = 30;

---

联合索引

• 优点：能覆盖多字段查询，性能通常比索引合并高。

• 缺点：设计不合理可能造成浪费，比如 idx_a_b_c 只对满足最左前缀的查询有效。

示例：

CREATE INDEX idx_name_age_city ON users(name, age, city);

能高效支持：

SELECT * FROM users WHERE name='Alice';
SELECT * FROM users WHERE name='Alice' AND age=30;
SELECT * FROM users WHERE name='Alice' AND age=30 AND city='Beijing';

---

实践建议

1. 如果查询条件只涉及单字段，单列索引更合适。

2. 如果查询条件经常涉及多个字段，且字段组合固定，联合索引更优。

3. 不要盲目为每个字段都建索引，那样会导致 写入开销过大。

---

5.2 索引列的选择原则

设计索引时，要优先考虑以下几个维度：

1. 高选择性（Cardinality 高）

• 选择性 = 不同值个数 / 总记录数。

• 选择性越高，索引过滤效果越好。

示例：

• 性别（男/女）选择性低，不适合单独建索引。

• 用户ID 唯一值多，适合建索引。

---

2. 常用查询条件列

• 频繁出现在 WHERE、JOIN ON、ORDER BY、GROUP BY 中的列，优先考虑建索引。

---

3. 避免冗余索引

如果有 idx_name_age，再建单列 idx_name 可能是冗余的（除非业务确实有场景只查 name）。

---

4. 适度的覆盖索引

有时可以通过设计覆盖索引避免回表，但要注意：索引字段过多会导致索引过大，占用内存和磁盘。

---

5.3 基于查询模式的索引设计

场景 1：点查/唯一查询

SELECT * FROM users WHERE id = 1001;

• 建立主键或唯一索引即可。

---

场景 2：范围查询

SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31';

• 对 create_time 建单列索引即可。

• 注意范围条件会阻断联合索引的后续列。

---

场景 3：多条件组合查询

SELECT * FROM users 
WHERE name='Alice' AND age=30 AND city='Beijing';

• 建立联合索引 (name, age, city)，利用最左前缀原则。

---

场景 4：排序与分组

-- 查询并排序
SELECT * FROM orders 
WHERE user_id=1001 
ORDER BY create_time DESC;

• 索引 (user_id, create_time) 可以避免额外的排序操作。

  -- 按地区分组
  SELECT city, COUNT(*) FROM users GROUP BY city;
  

• 对 city 建索引可以加速分组。

---

场景 5：覆盖索引优化

-- 按地区分组
SELECT city, COUNT(*) FROM users GROUP BY city;

• 建立 (name, age) 联合索引，能避免回表。

---

5.4 常见设计误区

1. 滥建索引

   • 每个字段都加索引，会导致写入性能下降。

2. 忽视更新开销

   • 每次 INSERT/UPDATE/DELETE 都需要维护索引，索引过多会显著拖慢写操作。

3. 错误的列顺序

   • 在联合索引 (a, b, c) 中，把低选择性的列（比如性别）放在前面，会大大降低索引利用率。

4. 忽略数据分布

   • 某些字段虽然出现在查询条件中，但值高度集中（如“status=1”占 95%），建索引意义不大。

---

5.5 实战案例

案例一：电商订单查询

需求：

• 用户经常根据 用户ID + 下单时间 查询订单。

• 偶尔会根据 订单状态 筛选。

索引设计：

-- 优先满足高频查询
CREATE INDEX idx_user_time ON orders(user_id, create_time);

-- 如果状态查询也很频繁，可以考虑三列索引
CREATE INDEX idx_user_time_status ON orders(user_id, create_time, status);

---

案例二：日志系统

需求：

• 经常按 时间范围 查询日志。

• 偶尔会按 用户ID + 时间范围查询。

索引设计：

-- 单列索引满足时间查询
CREATE INDEX idx_time ON logs(create_time);

-- 用户 + 时间 的复合查询
CREATE INDEX idx_user_time ON logs(user_id, create_time);

---

小结

在本章中，你学习了：

1. 单列索引 vs 联合索引 的取舍逻辑。

2. 选择索引列的原则：高选择性、常用查询条件、避免冗余。

3. 如何根据 查询模式 设计索引：点查、范围、组合、排序、分组、覆盖索引。

4. 常见设计误区，以及两个实战案例。

合理的索引设计，是数据库性能优化的第一步。索引不是越多越好，而是要 基于业务查询模式 精准设计。

第六章 实战中的索引优化案例

在理论和优化技术学习之后，最关键的一步是 把知识落地到实际场景中。很多开发者在日常工作中常常遇到 慢查询、索引设计不合理、查询计划无法命中索引 等问题，本章将通过 案例驱动 的方式，带你一步一步分析并解决问题。

---

6.1 慢查询日志分析与索引优化

6.1.1 什么是慢查询日志

慢查询日志（Slow Query Log）是 MySQL 提供的一个强大工具，用来记录 执行时间超过指定阈值 的 SQL 语句。

配置方法：

-- 开启慢查询日志（需有管理员权限）
SET GLOBAL slow_query_log = 1;

-- 设置阈值，默认10秒，这里改成1秒
SET GLOBAL long_query_time = 1;

-- 查看慢查询日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';

---

6.1.2 慢查询案例

假设有一个电商订单表 orders，结构如下：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL,
    create_time DATETIME NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    INDEX idx_user_time (user_id, create_time)
) ENGINE=InnoDB;

某个查询非常慢：

SELECT * FROM orders 
WHERE status = 1 
AND create_time >= '2023-01-01' 
AND create_time < '2023-02-01';

慢查询日志显示此 SQL 平均耗时 3 秒以上，扫描行数超过 200万。

---

6.1.3 EXPLAIN 分析

执行计划：

EXPLAIN SELECT * FROM orders 
WHERE status = 1 
AND create_time >= '2023-01-01' 
AND create_time < '2023-02-01';

输出结果（简化）：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	orders	ALL	NULL	2000000	Using where

问题：

• type=ALL 表示全表扫描，没有使用索引。

• 原因是：现有索引 (user_id, create_time) 不适用于 status+create_time 的过滤条件。

---

6.1.4 索引优化

为 status+create_time 建立复合索引：

CREATE INDEX idx_status_time ON orders(status, create_time);

再次执行 EXPLAIN：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	orders	range	idx_status_time	50000	Using index condition

优化效果：

• 扫描行数从 200万 → 5万，查询耗时从 3秒 → 0.1秒。

• 说明新索引命中，MySQL 利用了 索引下推（ICP） 优化。

---

6.1.5 总结

• 问题根源：查询条件未命中合适的索引。

• 解决方案：建立 (status, create_time) 联合索引。

• 优化点：通过慢查询日志发现问题，再结合 EXPLAIN 分析，找到索引缺失。

---

6.2 联合索引设计误区案例

联合索引能提升多字段查询的性能，但设计不合理时，反而可能导致性能下降。

---

6.2.1 案例：错误的索引顺序

表 users：

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_city_age_name (city, age, name)
) ENGINE=InnoDB;

查询语句：

SELECT * FROM users WHERE name='Alice';

EXPLAIN 输出：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	users	ALL	NULL	100000	Using where

问题：

• 索引 (city, age, name) 无法用于 name='Alice' 的查询，因为没有满足 最左前缀原则。

• MySQL 被迫全表扫描。

---

6.2.2 优化后的索引设计

如果查询模式主要是按 name 搜索，应调整索引顺序：

CREATE INDEX idx_name_age_city ON users(name, age, city);

再次执行 EXPLAIN：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	users	ref	idx_name_age_city	10	Using index

效果：

• 扫描行数大幅减少。

• type=ref 表示使用索引等值查找。

---

6.2.3 误区总结

1. 误区一：索引字段顺序随意

   • 错误：随便把字段组合成联合索引。

   • 正确：按照 查询条件的使用频率 和 选择性高低 排序。

2. 误区二：认为索引能自动匹配任意列

   • 错误：以为有 idx_city_age_name 就能优化任何条件。

   • 正确：必须遵循 最左前缀原则。

3. 误区三：联合索引替代所有单列索引

   • 错误：把所有查询字段都组合成一个索引。

   • 正确：要考虑冗余和维护成本，合理拆分单列索引和联合索引。

---

6.3 案例综合对比：优化前后性能测试

我们对比两个场景的 QPS（Queries Per Second） 测试结果：

场景 1：未优化索引

• SQL：SELECT * FROM orders WHERE status=1 AND create_time BETWEEN '2023-01-01' AND '2023-02-01';

• 平均耗时：3 秒

• QPS：约 300

场景 2：优化索引后

• 新增 (status, create_time) 索引

• 平均耗时：0.1 秒

• QPS：约 9000

性能提升 30 倍！

图表描述：如果绘制柱状图，可以看到优化前的耗时是 3 秒的高柱，而优化后只有 0.1 秒，几乎贴近坐标轴。QPS 图表中，优化后曲线陡然上升，显示查询性能显著提升。

---

6.4 小结

1. 慢查询日志是定位性能瓶颈的利器，结合 EXPLAIN 可以快速发现索引问题。

2. 索引设计必须结合查询模式，错误的联合索引顺序可能让索引完全失效。

3. 性能对比实验 能量化索引优化的收益，帮助团队建立索引优化意识。

在实战中，你需要养成一个习惯：

• 开启慢查询日志

• 定期分析索引命中情况

• 避免盲目创建索引，而是根据实际查询模式优化