Subquery子查询索引怎么建

在数据库中，子查询（Subquery）本身不能“直接建索引”，索引是建在表上的列上的；能否利用索引，取决于子查询里的 过滤条件、连接条件、以及子查询是否能被优化为 JOIN。

下面按常见场景说明子查询相关索引怎么建 / 怎么用。

一、最基本原则

子查询用到的列，要建索引

常见需要建索引的列：

• WHERE 中的过滤列
• JOIN 的关联列
• IN / EXISTS 里的比较列
• ORDER BY / GROUP BY 中的列

二、IN 子查询（最常见）

SELECT * FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE status = 1
);

索引建议

• users.status → 可选（低区分度可不加）
• users.id → 主键（默认有索引）
• orders.user_id → 必须建索引

CREATE INDEX idx_orders_user_id ON orders(user_id);

✅ 优化器通常会把 IN 子查询 转成 JOIN

三、EXISTS 子查询

SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM users u
  WHERE u.id = o.user_id AND u.status = 1
);

索引建议

• orders.user_id → 建索引
• users.id → 主键
• users.status → 可选

CREATE INDEX idx_orders_user_id ON orders(user_id);

✅ EXISTS 本质就是 JOIN

四、关联子查询（Correlated Subquery）

SELECT *
FROM orders o
WHERE o.amount > (
  SELECT AVG(amount)
  FROM orders o2
  WHERE o2.user_id = o.user_id
);

索引建议

CREATE INDEX idx_orders_user_id ON orders(user_id);

✅ 子查询按 user_id 分组计算，索引能加速

五、FROM 子查询（派生表）

SELECT *
FROM (
  SELECT user_id, COUNT(*) c
  FROM orders
  GROUP BY user_id
) t
WHERE t.c > 10;

索引建议

CREATE INDEX idx_orders_user_id ON orders(user_id);

⚠️ 注意：

• 派生表结果集本身没有索引
• 尽量让子查询内部用索引

六、子查询优化建议（比建索引更重要）

1. 能用 JOIN 就别用子查询
2. 避免 SELECT *
3. 避免 NOT IN（用 NOT EXISTS）
4. 大表子查询先过滤再 JOIN

✅ 改写示例：

-- 不推荐
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE age > 30);

-- 推荐
SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.age > 30;

七、总结一句话

子查询索引 = 子查询里“用到的列”建索引
重点在：WHERE / JOIN / GROUP BY 用到的字段

如果你愿意，可以把具体 SQL + 表结构发出来，我可以直接帮你设计索引并判断是否该改成 JOIN。