JOIN触发磁盘临时表是因为驱动表结果超出join_buffer_size，被迫落盘；根本原因是关联字段无索引、类型不一致或优化器选错驱动表导致BNL算法启用。
为什么JOIN会触发磁盘临时表？
MySQL在执行JOIN时，如果关联字段没索引、或索引不匹配（比如类型隐式转换）、或WHERE + JOIN条件组合导致优化器选错驱动表，就可能走Block Nested-Loop（BNL）算法。这时MySQL会把驱动表结果暂存在内存的join_buffer里；一旦超出join_buffer_size（默认256KB），就会落盘到/tmp或tmpdir下的临时文件——你看到的“磁盘临时表溢出”，本质是排序/哈希中间结果撑爆了内存缓冲区。
常见现象包括：Creating sort index、Copying to tmp table on disk、慢查询日志里出现Using temporary; Using filesort。
关键判断点：用EXPLAIN FORMAT=TREE看执行计划里是否有materialize或hash_join回退到BNL；用SHOW STATUS LIKE 'Created_tmp_disk_tables'确认是否真在写磁盘。
哪些索引能真正降低JOIN排序成本？
不是所有索引都管用。重点盯住三类字段的组合覆盖性：
- JOIN ON子句两侧字段必须都有独立索引，且数据类型严格一致（比如
INT对INT，不能VARCHAR(10)对VARCHAR(20)隐式截断） - 如果JOIN后带
ORDER BY或GROUP BY，优先建联合索引，把JOIN列放前面，排序列放后面——例如SELECT * FROM A JOIN B ON A.id = B.a_id ORDER BY B.created_at，B表上建INDEX idx_a_id_created (a_id, created_at) - WHERE条件里的字段如果和JOIN列无关，但又高频过滤，单独建索引；若和JOIN列有逻辑组合（如
WHERE status = 1 AND a_id IN (...)），考虑扩展为INDEX idx_status_a_id (status, a_id)
注意：单列索引对多条件JOIN帮助有限；LIKE '%xxx'开头的条件无法走索引，JOIN前先用子查询或物化CTE过滤掉无效行。
如何验证索引生效且没触发磁盘临时表？
加FORCE INDEX强制走新索引只是第一步，得看实际执行路径是否避开临时表：
执行前先设SET SESSION tmp_table_size = 64*1024*1024;和SET SESSION max_heap_table_size = 64*1024*1024;（调大内存临时表上限，排除误判）；再开SET SESSION optimizer_trace = "enabled=on";跑SQL，最后查SELECT * FROM information_schema.OPTIMIZER_TRACE，重点看steps里join_execution部分是否含"using_filesort": false和"using_temporary_table": false。
更直接的方法：在slow_log里搜该SQL，确认Rows_examined显著下降，且没有tmp_table_on_disk字样。
JOIN字段类型不一致时的修复动作
这是最隐蔽也最高发的原因。比如A表user_id INT，B表user_id VARCHAR(20)，即使两边值都是数字，MySQL也会把INT转成VARCHAR做比较，导致B表索引失效。
修复分三步：
- 查类型差异：
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('A', 'B') AND COLUMN_NAME = 'user_id'; - 统一类型（推荐改VARCHAR为BIGINT）：
ALTER TABLE B MODIFY user_id BIGINT UNSIGNED;（注意外键约束要先删再加） - 重建索引：
DROP INDEX idx_user_id ON B; CREATE INDEX idx_user_id ON B(user_id);
改完立刻用EXPLAIN对比——原来走ALL的B表扫描，应该变成ref或eq_ref；rows值应从几万降到个位数。
临时表溢出从来不是孤立问题，它总是暴露底层索引设计或数据模型的断裂点。最常被忽略的是JOIN后LIMIT的位置：如果写成SELECT ... JOIN ... LIMIT 10，MySQL仍会先算完整结果再截断；必须用派生表或CTE提前限制驱动表规模。
