MySQL 中大小表关联查询如何优化

在 MySQL 中优化大小表关联查询是提高性能的关键。以下是针对不同场景的优化策略，按优先级排序：

1. 索引优化 - 最关键步骤

2. -- 大表关联字段必须建索引
   ALTER TABLE large_table ADD INDEX idx_join_key (join_key);
   -- 小表关联字段也建议建索引
   ALTER TABLE small_table ADD INDEX idx_join_key (join_key);
   

3. 确保大表的关联字段有索引（B+树索引）

4. 小表最好也有索引，特别是当小表并不"小"时

5. 优化关联顺序

6. -- 显式指定小表驱动大表
   SELECT /*+ STRAIGHT_JOIN */ 
          large_table.* FROM small_table 
   JOIN large_table ON small_table.id = large_table.small_id;
   

7. MySQL 通常自动选择小表作为驱动表

8. 使用 STRAIGHT_JOIN 强制关联顺序（需谨慎）

9. 减少关联数据量

10. -- 先过滤小表再关联 SELECT large_table.*FROM (   SELECT id FROM small_table WHERE condition = 'value') AS filtered_small   JOIN large_table ON filtered_small.id = large_table.small_id;

11. 覆盖索引优化

12. -- 创建包含查询列的复合索引 ALTER TABLE large_table ADD INDEX idx_cover (join_key, col1, col2);

13. 使索引包含所有查询字段，避免回表操作

14. 使用临时表

15. -- 对小表预查询创建临时表 CREATE TEMPORARY TABLE temp_small AS SELECT id, col1, col2 FROM small_table WHERE conditions; ALTER TABLE temp_small ADD INDEX idx_id (id); SELECT large_table.* FROM temp_small  JOIN large_table ON temp_small.id = large_table.small_id;

16. 批处理技术

17. -- 分批次处理关联查询 SET @batch_size = 1000; SET @offset = 0; WHILE @offset < (SELECT MAX(id) FROM small_table) DO   SELECT l.*    FROM (     SELECT id FROM small_table      WHERE id BETWEEN @offset AND @offset + @batch_size   ) AS batch  JOIN large_table l ON batch.id = l.small_id;      SET @offset = @offset + @batch_size; END WHILE;

18. 大表分区优化

19. -- 按关联键分区 ALTER TABLE large_table PARTITION BY HASH(join_key) PARTITIONS 16;

20. 对十亿级大表特别有效

21. 需结合分区键查询条件

22. 反范式化设计

23. -- 增加冗余字段避免关联 ALTER TABLE large_table ADD small_table_col VARCHAR(20); UPDATE large_table  JOIN small_table ON large_table.small_id = small_table.id SET large_table.small_table_col = small_table.value;

24. 用空间换时间

25. 需维护数据一致性

26. Hash Join 优化（mysql8.0特有）

27. -- 确保启用hash_join SET optimizer_switch='hash_join=on'; EXPLAIN FORMAT=TREE SELECT * FROM small_table s  JOIN large_table l ON s.id = l.small_id;

28. MySQL 8.0.18+ 支持

29. 无索引时性能更好

30. CTE 优化（mysql8.0特有）

31. WITH filtered_small AS (   SELECT id, name    FROM small_table    WHERE create_time > '2023-01-01')   SELECT l.* FROM filtered_small s   JOIN large_table l ON s.id = l.small_id;