第二章：MySQL 索引优化与高级应用

1. 索引失效的常见场景

即使创建了索引，某些情况下索引可能无法生效，导致查询性能下降。以下是常见失效场景：

1.1 不符合最左前缀原则

• ​​组合索引​​必须从最左列开始使用。
  示例：索引为(a, b, c)
  • ✅ 有效：WHERE a=1 AND b=2
  • ✅ 有效：WHERE a=1 ORDER BY b
  • ❌ 无效：WHERE b=2（未使用最左列a）

1.2 对索引列使用函数或计算

• 对索引列进行运算或函数操作会导致索引失效。
  示例：

  -- ❌ 失效：对索引列使用函数
  SELECT * FROM users WHERE YEAR(create_time) = 2023;-- ✅ 优化：改为范围查询
  SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

1.3 使用LIKE左模糊匹配

• LIKE以通配符开头（如%value）会导致索引失效。
  示例：

  -- ❌ 失效：左模糊
  SELECT * FROM products WHERE name LIKE '%apple%';-- ✅ 有效：右模糊（如`apple%`）
  SELECT * FROM products WHERE name LIKE 'apple%';

1.4 隐式类型转换

• 索引列与查询条件的数据类型不一致时，可能触发隐式转换，导致索引失效。
  示例：

  -- ❌ 失效：字符串列用数字查询（假设`id`是字符串类型）
  SELECT * FROM users WHERE id = 100;

1.5 使用OR连接非索引列

• 如果OR两侧的列不全是索引列，索引可能失效。
  示例：

  -- ❌ 失效：`age`无索引
  SELECT * FROM users WHERE name = 'Alice' OR age = 25;

---

2. 索引优化技巧

2.1 覆盖索引（Covering Index）

• ​​定义​​：查询的字段全部包含在索引中，无需回表查询数据行。
  示例：

  -- 创建索引
  CREATE INDEX idx_name_age ON users (name, age);-- ✅ 覆盖索引：只需返回索引列
  SELECT name, age FROM users WHERE name = 'Alice';

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

• ​​功能​​：在存储引擎层提前过滤数据，减少回表次数（MySQL 5.6+支持）。
  示例：

  -- 索引：`(name, age)`
  SELECT * FROM users WHERE name LIKE 'A%' AND age = 20;-- 无ICP：先根据`name LIKE 'A%'`回表，再过滤`age=20`  
  -- 有ICP：在存储引擎层直接过滤`name LIKE 'A%' AND age=20`，再回表

2.3 前缀索引（Prefix Index）

• ​​适用场景​​：对长文本列（如VARCHAR(255)）创建索引时，可只索引前几个字符以节省空间。
  示例：

  -- 对`email`列的前10个字符创建索引
  CREATE INDEX idx_email_prefix ON users (email(10));

---

3. 执行计划分析（EXPLAIN）

使用EXPLAIN命令分析查询是否命中索引，优化执行效率。

3.1 关键字段解读

字段	说明
​​type​​	访问类型（性能从优到差：const > eq_ref > ref > range > index > ALL）
​​key​​	实际使用的索引
​​rows​​	预估需要扫描的行数
​​Extra​​	额外信息（如Using index表示覆盖索引，Using filesort表示需要排序）

3.2 示例分析

EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age = 25;

• 如果key显示idx_name_age且type为ref，说明索引生效。
• 如果Extra显示Using where，表示存储引擎层过滤后仍需服务层进一步处理。

---

4. 索引与存储引擎

4.1 InnoDB 索引特点

• ​​聚簇索引​​：数据按主键顺序存储，主键索引的叶子节点直接包含行数据。
• ​​二级索引​​：叶子节点存储主键值，查询时需要回表（除非覆盖索引）。

4.2 MyISAM 索引特点

• ​​非聚簇索引​​：索引和数据分离，索引叶子节点存储数据行地址。
• ​​全文索引​​：支持FULLTEXT索引，适合文本搜索。

4.3 InnoDB vs MyISAM 索引对比

特性	InnoDB	MyISAM
索引类型	聚簇索引 + 二级索引	非聚簇索引
事务支持	✅	❌
行级锁	✅	表级锁
全文索引	✅（MySQL 5.6+）	✅

---

5. 索引设计的最佳实践

1. ​​优先使用组合索引​​：覆盖多个查询条件，减少索引数量。
2. ​​避免冗余索引​​：如已有(a, b)，再创建(a)是冗余的。
3. ​​定期分析慢查询​​：使用slow_query_log定位需要优化的查询。
4. ​​监控索引使用率​​：通过INFORMATION_SCHEMA.STATISTICS表分析未使用的索引。

---

6. 索引的常见误区

误区	解释
​​索引越多越好​​	过多索引会增加写开销和存储成本。
​​所有查询都能用索引加速​​	索引只对特定查询模式有效（如等值、范围查询）。
​​主键必须是自增ID​​	主键可以是任何唯一且非空的字段，但自增ID对插入性能更友好。