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

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

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

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

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

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

2. 优化关联顺序

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

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

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

3. 减少关联数据量

1. -- 先过滤小表再关联
   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;

4. 覆盖索引优化

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

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

5. 使用临时表

1. -- 对小表预查询创建临时表
   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;

6. 批处理技术

1. -- 分批次处理关联查询
   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;

7. 大表分区优化

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

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

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

8. 反范式化设计

1. -- 增加冗余字段避免关联
   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;

2. 用空间换时间

3. 需维护数据一致性

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

1. -- 确保启用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;

2. MySQL 8.0.18+ 支持

3. 无索引时性能更好

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

1. 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;