📑 MySQL 索引原理与查询优化面试题

📖 索引的定义

索引是帮助 MySQL 高效获取数据的数据结构，类似书籍的目录。

✅ 优点

• 大幅减少扫描的行数，提高查询效率。
• 通过有序存储支持 ORDER BY、GROUP BY。
• 可以帮助实现唯一性约束。

⚠️ 缺点

• 需要额外的存储空间。
• 写入、更新、删除时需要维护索引结构，降低写性能。
• 索引设计不当反而会拖慢查询。

🌳 B+ 树的优势

• 磁盘友好：每个节点可以存储大量 key，树高较低，减少磁盘 IO。
• 范围查询友好：叶子节点通过链表相连，支持顺序扫描和范围查询。
• 稳定性：非叶子节点只存储 key，不存储数据行，使得节点更紧凑。

B 树：数据分布在所有节点上，范围扫描不如 B+ 树高效。

哈希索引：只适合等值查询，不支持范围查询和排序。

🌲 聚簇索引

• InnoDB 中，主键索引的 B+ 树叶子节点存储整行数据，称为聚簇索引（clustered index）。

🌿 二级索引

• 非主键索引的叶子节点存储的是「主键值」，不是整行数据。

🔗 配合方式

• 通过二级索引定位到主键，再回到聚簇索引树根据主键取出整行记录（回表）。
• 如果查询列刚好被索引覆盖（覆盖索引），则无需回表，性能更好。

📚 覆盖索引（Covering Index）

如果一个查询所需的所有列都能在某个索引中直接获得，而不需要回表到聚簇索引，就称该索引为覆盖索引。

-- 联合索引 (user_id, status, created_at)
CREATE INDEX idx_user_status_created
ON order(user_id, status, created_at);

-- 查询只用到了索引中的列
SELECT user_id, status, created_at
FROM order
WHERE user_id = 1001 AND status = 'PAID';

上面的查询可以仅通过 idx_user_status_created 索引完成，无需回表，I/O 更少，性能更好。

🧩 最左前缀原则

对于联合索引 (a, b, c)，索引可以被用到的前缀是：

• (a)
• (a, b)
• (a, b, c)

但不能单独用 (b)、(c) 或 (b, c)。

常见错误用法

• 对 b、c 单独做查询，误以为联合索引也能用上。
• 在 a 上使用函数或表达式，如 WHERE LEFT(a, 3) = 'abc' 导致索引失效。
• 在 a 上使用不等号范围条件后，再对 b 做等值条件，b 可能无法使用索引。

⚠️ 常见索引失效场景

• 在索引列上使用函数或表达式，如 WHERE DATE(create_time) = '2024-01-01'。
• 在字符串列上没加引号，触发隐式类型转换。
• 联合索引未按照最左前缀使用。
• 在索引列前使用前置通配符：LIKE '%abc'。
• 对索引列进行了计算：WHERE age + 1 = 18。
• 使用 OR 混合条件，且其中一边没有索引。

🔍 EXPLAIN 关键字段

EXPLAIN SELECT * FROM user WHERE phone = '138...';

• type：访问类型，越靠近 const、ref 越好，ALL 为全表扫描。
• possible_keys：可能用到的索引。
• key：实际使用的索引。
• rows：预估需要扫描的行数。
• Extra：是否出现 Using filesort、Using temporary 等提示。

📦 Using filesort 含义

表示 MySQL 需要额外的排序步骤（可能在内存或磁盘），而不是利用索引有序性。

出现场景

• ORDER BY 的列未使用索引，或索引顺序与排序顺序不匹配。
• 联合索引中断了最左前缀（如中间列使用函数）。

优化思路

• 为 ORDER BY 列建立合适的索引，尽量让排序能走索引。
• 避免在 ORDER BY 列上使用表达式或函数。
• 限制返回行数（LIMIT），减轻排序压力。

✅ 适合建索引的情况

• 经常出现在 WHERE、JOIN、ORDER BY、GROUP BY 中的列。
• 高选择性（去重后行数接近总行数）的列。
• 外键列。

❌ 不适合建索引的情况

• 频繁更新的列，尤其是大量写入的场景。
• 低选择性列，如性别、状态（只有少数几个值）。
• 宽列（很长的字符串），会导致索引体积过大。

🧪 示例案例（可按此模板准备自己的故事）

1. 现象：订单列表接口响应缓慢，p99 超过 2 秒。
2. 排查：
   • 打开慢查询日志，定位到问题 SQL。
   • 使用 EXPLAIN 查看执行计划，发现 type=ALL，全表扫描，rows 超过百万。
3. 原因：WHERE 条件中的 user_id 无索引，还对 created_at 做了函数处理。
4. 优化：
   • 为 (user_id, created_at) 建联合索引。
   • 将 DATE(created_at) = '2024-01-01' 改为范围查询 created_at >= '2024-01-01 00:00:00' AND created_at < '2024-01-02'。
5. 结果：接口 p99 降到 100ms 以内，慢 SQL 日志清空。

🔎 用例分析

WHERE a = 1 AND b = 2 AND c = 3        -- ✅ 使用 (a,b,c)
WHERE a = 1 AND c = 3                  -- ✅ 使用 (a)
WHERE b = 2 AND c = 3                  -- ❌ 无法使用 (a,b,c)
WHERE a > 1 AND b = 2                  -- ✅ a 范围，b 可能部分使用
WHERE a LIKE 'abc%' AND b = 2          -- ✅ 支持前缀匹配
WHERE a LIKE '%abc' AND b = 2          -- ❌ a 无法走索引

核心原则：从最左列开始连续匹配，中间一旦断开，后面的列无法利用索引有序性。

🔍 模糊查询优化

• 使用前缀匹配 LIKE 'abc%'，可以用到 B+ 树索引。
• 避免 LIKE '%abc' 这类前置通配符，必要时可考虑倒排索引或搜索引擎。
• 可以使用覆盖索引减少回表。
• 对于高频搜索条件，可考虑额外维护冗余字段或关键字表。

🔗 JOIN 优化思路

• 确保 JOIN 字段两边都有索引，尤其是驱动表的关联列。
• 使用 EXPLAIN 查看 join order、驱动表和被驱动表。
• 控制参与 JOIN 的数据量，提前在子查询/临时表中过滤无用数据。
• 避免在 JOIN 字段上使用函数或类型转换。

📌 索引评审要点

• 每个索引都要有明确的“服务对象”SQL。
• 检查是否存在高度重复或功能重叠的索引。
• 结合慢查询日志、业务 QPS 找出最需要索引支持的语句。
• 定期清理长期不用的索引（可通过 performance_schema / information_schema 统计）。

✅ SQL 优化清单（可直接在面试中说）

• 是否有合适的索引？是否命中索引？
• 是否出现 Using filesort / Using temporary？
• 是否存在全表扫描、回表次数过多？
• 是否可以改写为覆盖索引？
• 是否可以通过冗余字段、预计算减少复杂表达式？
• 是否需要分库分表或归档历史数据？
• 是否可以通过缓存/异步化减轻数据库压力？