当你的SQL查询因为多表JOIN而变得缓慢时，问题往往不在SQL本身，而在于索引设计。关联查询的索引设计是性能优化的核心，它决定了数据库如何高效地连接多张表。本文将通过典型场景，带你逐步掌握为JOIN字段和过滤条件创建索引的方法，并教你如何通过执行计划验证优化效果。
场景一：两表JOIN，驱动表与被驱动表的索引策略
假设你有两张表：orders（订单表）和customers（客户表）。常见的查询是：
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE orders.status = 'paid';
这里，orders是驱动表（外层循环的表），customers是被驱动表。MySQL使用嵌套循环连接（Nested Loop Join），对于驱动表中的每一行，都会在被驱动表中查找匹配行。因此，被驱动表的连接字段（customers.id）必须有索引，否则每次查找都将全表扫描。同时，驱动表上的WHERE条件（orders.status）也应有索引，以减少驱动表的扫描行数。
具体操作：
- 在
customers.id上创建主键索引（通常已有），确保连接查找高效。 - 在
orders.status上创建二级索引，过滤掉大部分行。 - 如果查询还涉及
orders.customer_id，考虑创建复合索引（status, customer_id），覆盖过滤和连接字段。
注意：如果驱动表很小，可能无需在驱动表上创建索引，但通常建议为WHERE条件创建索引以减小驱动集。
场景二：三表JOIN，索引顺序与中间结果集
多表JOIN时，索引设计需考虑连接顺序。例如：
SELECT * FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id WHERE a.type = 'x';
MySQL优化器会选择成本最低的连接顺序，通常从过滤性最强的表开始。因此，a.type应建索引，b.a_id和c.b_id都应有索引。如果c表还有额外过滤条件，也需相应索引。
关键点：为每个连接字段创建索引，并为每个表上的WHERE条件创建索引。使用EXPLAIN查看执行计划，确认type列不是ALL（全表扫描），key列显示使用了索引。
场景三：非等值JOIN与范围条件的索引设计
有时JOIN条件不是等值，而是范围（如ON a.date < b.date）。这类查询无法使用索引进行等值匹配，只能通过索引扫描或全表扫描。此时，建议在范围字段上创建索引，但效果有限。更好的方法是重新设计查询，例如将范围条件转换为等值条件，或者使用冗余字段。
例如，要查询“每个客户最近一笔订单”，可以改用窗口函数或子查询，避免非等值JOIN。
验证索引效果：使用EXPLAIN分析执行计划
创建索引后，必须用EXPLAIN验证优化效果。关注以下列：
type：从ALL（全表扫描）变为ref或eq_ref，说明索引生效。key：实际使用的索引名称。rows：预估扫描行数，应大幅减少。Extra：如果出现Using temporary或Using filesort，可能需调整索引或查询。
例如，执行EXPLAIN SELECT ...，对比索引前后的rows和type。
常见误区与失败条件
- 误区：在驱动表上创建过多索引。驱动表索引过多会增加维护成本，且可能影响优化器选择。
- 误区：忽略复合索引的列顺序。复合索引（
a,b）只能用于a或a,b的查询，不能用于仅b的查询。 - 失败条件：连接字段类型不一致。如果
a.id是INT，b.a_id是VARCHAR，索引将失效，因为隐式类型转换。确保连接字段类型相同。 - 失败条件：函数包裹连接字段。如
ON FUNCTION(a.id) = b.id，会导致索引失效。避免在连接字段上使用函数。
小结与进一步优化
关联查询的索引设计核心是：为被驱动表的连接字段创建索引，为驱动表的WHERE条件创建索引，并利用复合索引覆盖多个条件。通过EXPLAIN验证，确保查询计划高效。对于复杂查询，还可考虑冗余字段或反范式设计，但需权衡写性能。
更多关于索引失效的排查方法，可参考数据库索引失效的常见原因与排查方法；了解InnoDB索引原理，可阅读MySQL InnoDB存储引擎索引原理与B+树慢查询优化实战。分析查询计划时，可参考如何分析SQL查询计划并优化索引设计。
参考资料
延伸阅读