关联查询的关联字段不建索引就不行
您说得对！关联字段不建索引，关联查询性能会很差，但“不行”指的是性能不行，而不是语法不行。
⚠️ 无索引关联的风险
1. 性能灾难场景
SQL
-- 假设table_a有100万行，table_b有1000万行
SELECT a.*, b.*
FROM table_a a
JOIN table_b b ON a.id = b.a_id; -- 无索引
-- 执行计划：笛卡尔积扫描
-- 复杂度：O(n×m) = 100万 × 1000万
-- 结果：数据库卡死或超时
2. 有索引 vs 无索引对比
| 场景 | 有索引的关联查询 | 无索引的关联查询 |
|---|---|---|
| 执行计划 | 嵌套循环/哈希连接 | 全表扫描/笛卡尔积 |
| 复杂度 | O(n log m) | O(n×m) |
| 100万×1000万 | 秒级完成 | 可能超时/崩溃 |
| 资源消耗 | 内存可控 | 内存溢出、磁盘IO暴涨 |
🔧 必须建立索引的场景
1. 主键-外键关系（必须）
SQL
-- 主表
CREATE TABLE users (
id INT PRIMARY KEY, -- 主键自动有索引
name VARCHAR(100)
);
-- 从表
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
INDEX idx_user_id (user_id) -- 必须手动创建
);
-- 关联查询
SELECT u.name, o.*
FROM users u
JOIN orders o ON u.id = o.user_id; -- o.user_id必须有索引
2. 多表关联（每个关联字段都要索引）
SQL
-- 三表关联
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id -- 索引必须
JOIN products p ON o.product_id = p.id -- 索引必须
JOIN categories c ON p.category_id = c.id; -- 索引必须
📊 索引策略
1. 单列索引（最常见）
SQL
-- 基本索引
CREATE INDEX idx_table_b_a_id ON table_b(a_id);
-- 包含多列的复合索引
CREATE INDEX idx_table_b_a_id_status ON table_b(a_id, status);
2. 覆盖索引（性能最佳）
SQL
-- 如果查询只用到某些列，创建覆盖索引
CREATE INDEX idx_table_b_covering ON table_b(a_id, column1, column2);
-- 查询时直接从索引获取数据，避免回表
SELECT a_id, column1, column2
FROM table_b
WHERE a_id = 123;
🚨 不建索引的例外情况
1. 小表（几百行以内）
SQL
-- 配置表、字典表等小表可以不建索引
SELECT a.*, b.name
FROM main_table a
JOIN config_table b ON a.type = b.code; -- b表只有50行
2. 全表扫描更快时
SQL
-- 当需要大部分数据时，全表扫描可能更快
-- table_b有1000行，需要关联900行
SELECT a.*, b.*
FROM table_a a
JOIN table_b b ON a.id = b.a_id
WHERE b.status = 1; -- 如果status=1占90%
3. 临时表/中间结果
SQL
-- 临时表的数据量很小
WITH temp_table AS (
SELECT id FROM large_table WHERE create_date > '2024-01-01'
)
SELECT t.*, d.detail
FROM temp_table t
JOIN detail_table d ON t.id = d.ref_id; -- 临时表通常不大
💡 最佳实践建议
1. 建索引优先级
SQL
-- 1. 主键（自动）
CREATE TABLE table_a (id INT PRIMARY KEY);
-- 2. 外键（手动）
ALTER TABLE table_b ADD INDEX idx_foreign_key (a_id);
-- 3. 常用查询字段
CREATE INDEX idx_query ON table_b(status, create_time);
-- 4. 覆盖索引
CREATE INDEX idx_covering ON table_b(a_id, col1, col2);
2. 监控无索引关联
SQL
-- 查找无索引的外键
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME IS NOT NULL
AND TABLE_NAME NOT IN (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE INDEX_NAME != 'PRIMARY'
);
-- 慢查询日志中查找
-- 执行计划中看到 "Using where; Using join buffer"
3. 索引维护
SQL
-- 定期分析索引使用情况
SELECT
index_name,
COUNT_READ,
COUNT_FETCH
FROM information_schema.INDEX_STATISTICS
WHERE table_schema = 'your_db';
-- 删除无用索引
DROP INDEX unused_index ON table_name;
📈 性能测试对比
SQL
-- 测试1：无索引关联
EXPLAIN SELECT COUNT(*)
FROM large_table a
JOIN large_table b ON a.parent_id = b.id; -- parent_id无索引
-- 结果：type=ALL, rows=1000000, Extra=Using where; Using join buffer
-- 测试2：有索引关联
CREATE INDEX idx_parent_id ON large_table(parent_id);
EXPLAIN SELECT COUNT(*)
FROM large_table a
JOIN large_table b ON a.parent_id = b.id;
-- 结果：type=ref, rows=1, Extra=Using index
🎯 总结
- 95%的情况：关联字段必须建索引
- 例外情况：
- 小表（< 1000行）
- 一次查询需要大部分数据
- 临时表/中间结果集
- 核心建议：
SQL
-- 关联查询前，先检查索引 SHOW INDEX FROM table_b; -- 无索引时，先创建索引再关联 ALTER TABLE table_b ADD INDEX idx_foreign (a_id);
最终建议：关联查询前，养成检查索引的习惯。没有索引的关联查询，在生产环境中是灾难性的，特别是数据量大时。
