四、查询优化器的索引选择逻辑

当你执行一条带有WHERE条件的SQL时，MySQL优化器需要从多个可用索引中选择最优的一个。这个决策基于成本模型：

索引选择的核心因素：

1. 区分度（Cardinality）：索引列的不同值越多，区分度越高，过滤效果越好。如果性别字段只有2个值，建索引几乎没有意义——优化器可能直接选择全表扫描。

2. 扫描行数：优化器通过索引统计信息（sampling）估算需要扫描的行数。扫描行数越少，成本越低。

3. 是否需要回表：回表是随机I/O，如果回表次数太多，优化器可能放弃二级索引而选择主键索引全表扫描。

使用EXPLAIN命令可以查看优化器的选择：

EXPLAIN SELECT * FROM orders WHERE user_id = 1000 AND status = 'paid';

关注输出中的type列：system > const > eq_ref > ref > range > index > ALL。 ALL表示全表扫描，预示着性能问题。

五、覆盖索引：避免回表的利器

如果一个索引包含了查询所需的所有字段，就称为覆盖索引（Covering Index）。查询可以直接从索引中获取数据，无需回表，大幅提升性能。

实例：

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

-- 需要回表的查询（需要获取 amount，但索引中只有 user_id, status, id）
SELECT amount FROM orders WHERE user_id = 1000 AND status = 'paid';

-- 覆盖索引优化：扩展索引包含 amount
ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, amount);
-- 此时查询直接从索引拿到 amount，无需回表

EFPLAIN的Extra列出现"Using index"就表示使用了覆盖索引。但要注意，不要为了覆盖所有查询而创建过宽的索引——索引本身的存储开销和维护成本也不可忽视。

六、最左前缀原则的深层逻辑

复合索引遵循最左前缀原则：索引(a, b, c)可以支持a、(a,b)、(a,b,c)的查询，但不能跳过前面的列直接使用后面的列。

这不是人为限制，而是B+树的结构决定的。B+树按(a, b, c)的字典序排序。当你只按b查询时，数据在b维度上不是全局有序的（只是在每个a值内部按b有序），因此无法利用索引的有序性进行高效查找。

实战经验总结：

1. 等值列放前面，范围列放后面：因为等值查询会精确定位到一个子树，而范围查询会使后续列失去有序性。

2. 高区分度列优先：区分度高的列放前面，可以减少后续扫描的行数。

3. 考虑排序需求：如果查询中有ORDER BY，让排序列也参与索引，可以避免filesort。

七、索引失效的常见陷阱

即使建了索引，以下场景也会导致索引失效：

1. 对索引列进行函数运算：WHERE YEAR(created_at) = 2026会使created_at上的索引失效。改为WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'即可利用索引。

2. 隐式类型转换：如果字段是VARCHAR类型，查询时用了数字（WHERE phone = 13800138000），MySQL会做类型转换导致索引失效。改为WHERE phone = '13800138000'。

3. LIKE以通配符开头：LIKE '%keyword'无法利用索引，LIKE 'keyword%'可以。

4. OR条件：如果OR连接的列中有一个没有索引，MySQL可能会放弃所有索引。可以用UNION替代OR来保证索引利用。

5. IS NOT NULL：IS NULL可以利用索引（NULL在B+树中聚集存放），但IS NOT NULL通常不会走索引。

八、实战案例分析：从3秒到3毫秒

假设我们有一个订单表5000万行，查询某用户某时间范围的订单：

-- 原始查询（3秒）
SELECT id, amount, status FROM orders
WHERE user_id = 123456
  AND created_at >= '2025-01-01'
  AND created_at < '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 执行计划：key=NULL, type=ALL, rows=50000000, Extra=Using where; Using filesort


优化第1步：在user_id上建索引。执行时间降到200ms。但仍有filesort，因为排序用的是created_at，不是索引顺序。

优化第2步：建复合索引(user_id, created_at)。因为等值匹配user_id后，created_at是有序的，filesort消除。执行时间降到10ms。

优化第3步：建覆盖索引(user_id, created_at, amount, status)。Extra变成"Using index"，完全不需要回表。执行时间降到3ms。

从3秒到3毫秒，1000倍的性能提升，靠的就是对B+树索引原理的深入理解和逐步优化。

九、高级索引策略

索引下推（Index Condition Pushdown, ICP）：MySQL 5.6引入。对于复合索引(a, b, c)，查询条件中有a = 1 AND b LIKE 'prefix%' AND c > 10。没有ICP时，存储引擎按a=1和b的前缀匹配回表，然后server层过滤c>10。有了ICP后，存储引擎在索引层面就完成c>10的过滤，减少回表次数。

Multi-Range Read（MRR）：对于范围查询返回大量行时，MRR先将得到的主键排序，然后再按排序顺序回表。将随机I/O转为顺序I/O，提升磁盘读取效率。可通过optimizer_switch='mrr=on,mrr_cost_based=off'开启。

隐藏索引（Invisible Indexes）：MySQL 8.0支持将索引设为INVISIBLE。删除索引前，先设为INVISIBLE观察业务是否有影响。如果性能下降，立刻改回VISIBLE即可，无需重建索引。这为索引治理提供了安全的"灰度测试"能力。

十、索引设计的黄金法则

最后总结几条经过实践检验的索引设计原则：

1. 不是索引越多越好：每个索引都占用空间，并在INSERT/UPDATE/DELETE时维护。写频繁的表，每多一个索引就多一份写入开销。

2. WHERE + ORDER BY + SELECT 综合考虑：索引要同时服务过滤、排序和覆盖查询。

3. 利用慢查询日志和EXPLAIN做数据驱动：不要凭直觉建索引，通过慢查询日志定位性能瓶颈，用EXPLAIN验证优化效果。

4. 定期审查和清理无用索引： INFORMATION_SCHEMA.INDEX_STATISTICS和sys.schema_unused_indexes可以帮你发现从未使用的索引。

5. 理解业务查询模式：先搞清楚系统中最频繁、最关键的是什么查询，再为它们设计索引。性能优化的本质不是追求理论上的最优，而是在业务约束下的最佳平衡。

B+树只是数据库索引家族的一员。对于全文搜索，还有倒排索引；对于空间数据，有R-树；对于等值查询为主的场景，哈希索引更高效。理解B+树的原理，不仅是优化查询的工具，更是理解"如何用数据结构解决工程问题"的绝佳案例——毕竟，好的工程师不是记住所有答案，而是知道在工具箱里选用最合适的工具。