复合索引加速join的前提是被正确使用：右表on字段须全在单个复合索引最左前缀，顺序须与on中等值条件一致；函数、隐式转换、范围条件后置及低区分度字段置首均导致失效。

复合索引能加速 JOIN，但前提是它被真正用上——绝大多数慢 JOIN 问题不是没建索引，而是索引建得不对、用得不对，或者被隐式转换悄悄绕过了。

为什么单列索引对多字段 ON 条件基本无效

MySQL 和 PostgreSQL 的优化器不会自动合并多个单列索引去匹配 ON t1.a = t2.x AND t1.b = t2.y 这类条件。哪怕 t2.x 和 t2.y 各自都有索引，优化器通常只选其一，另一个字段靠全表扫描后过滤，EXPLAIN 里 key_len 显示的长度往往只对应单个字段。

• 必须为右表（被驱动表）单独建复合索引，例如 CREATE INDEX idx_t2_xy ON t2(x, y)
• 字段顺序必须严格匹配 ON 中等值条件的出现顺序：如果写的是 ON t2.x = ? AND t2.y = ?，索引就得是 (x, y)；写成 (y, x) 就大概率只用上 y，x 被跳过
• 验证是否生效：看 EXPLAIN 的 Extra 列，出现 Using where; Using index 才算覆盖命中；若只有 Using index 或压根没出现，说明索引没被完整识别

复合索引字段顺序怎么排：先看 ON，再看 WHERE，别凭感觉

顺序不是由“哪个字段更重要”决定，而是由查询中字段的使用方式和位置决定。最左前缀原则是硬约束，不能靠优化器重排条件来迁就。

• ON 中的等值字段必须放最左，且顺序一致。例如 ON o.user_id = u.id AND o.tenant_id = u.tenant_id → 索引必须是 (id, tenant_id) 或 (tenant_id, id)，具体选哪个取决于哪个字段在 ON 里先出现
• WHERE 中的等值条件（如 u.status = 'active'）可以追加在后面，形成 (id, tenant_id, status)
• 范围条件（如 u.ctime > '2026-01-01'）必须放最后，因为一旦出现范围，其后的字段就无法用于索引查找
• 别把低区分度字段（如 status 只有 3 个值）放最左，SHOW INDEX FROM users 查看 Cardinality，如果远低于总行数，这个字段就不适合作首列

哪些操作会让复合索引在 JOIN 中彻底失效

哪怕索引建得完全正确，只要在 ON 或 WHERE 里动了字段，整个索引就掉线了。这类问题在 EXPLAIN 里常表现为 type: ALL 或 key: NULL，容易误判为“没建索引”。

• ON UPPER(t1.code) = UPPER(t2.code) → 函数作用于索引列，无法走索引
• ON t1.user_id = t2.uid，但 t1.user_id 是 INT，t2.uid 是 BIGINT → 类型不一致，触发隐式转换，两边索引都失效
• ON DATE(t1.created_at) = '2026-09-29' → 放弃索引，应改写为 t1.created_at >= '2026-09-29' AND t1.created_at
• 外键字段没手动建索引 → ALTER TABLE 加完外键约束后，必须额外执行 CREATE INDEX，约束 ≠ 索引

LEFT JOIN 下复合索引更容易被忽略

LEFT JOIN 对右表索引更保守：即使你建了完美匹配的复合索引，只要左表返回行数太多（比如没加强过滤的 WHERE），优化器可能直接跳过右表索引，退化为 Using join buffer (Block Nested Loop)。

• 确保左表有强过滤条件，例如 WHERE u.created_at > '2026-08-01'，让驱动表 rows 显著下降
• 右表的 ON 字段必须全部落在同一复合索引的最左前缀上，不能分散在多个索引里
• 盯紧 EXPLAIN 的 Extra 列：一旦出现 Using join buffer (Block Nested Loop)，说明右表已退化为全表扫描，不是数据量问题，是索引没被识别或没生效
• INNER JOIN 至少还可能触发 index_merge，但 LEFT JOIN 基本不指望这个