面试官：请问多表联查时，该如何进行性能优化？

好记忆不如烂笔头，能记下点东西，就记下点，有时间拿出来看看，也会发觉不一样的感受.

有兄弟留言，咨询我如何处理 多表联查时，可能会出现性能问题、维护困难等情况，结合多年的工作经历，给出如下几种优化思路及其详细说明，具体如下：
 

目录

一、拆分 SQL

二、使用临时表

三、使用冗余字段

四、用好索引

五、修改查询语句

六、减少结果集

七、修改数据库配置

八、引入大数据工具

九、汇总表

一、拆分 SQL

1. 介绍 ：将多张表 Join 的复杂 SQL 拆解成多个简单的 Join 语句，在应用代码中对结果进行组合。

2. 使用方式 ：分析原 SQL 中的表 Join 关系，找出可拆分部分。例如，对于包含多个 Join 的 SQL，可以将涉及不同业务逻辑或关联条件不同的部分拆分成两个或多个 SQL。

3. 注意事项 ：需确保应用代码能够正确合并多个 SQL 查询结果，避免因数据不一致或合并错误导致的问题。

4. 使用样例 ：原 SQL 为 SELECT t1.id ,t1.a,t2.b,t3.c,t4.d FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <=1000 JOIN test4 t4 ON t1.c=t4.c; ，可以拆分为：

   • SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b;

   • SELECT t1.id ,t1.a,t4.d FROM test1 t1 JOIN test4 t4 ON t1.c=t4.c;

   然后在业务代码中组合这两个查询结果。

5. 优化建议 ：适用于表关联复杂、业务逻辑可拆分的情况，尤其是当单个 SQL 中包含多个不同业务逻辑的 Join 时，可有效降低查询复杂度和性能开销。

二、使用临时表

1. 介绍 ：对于数据量较大但实际查询结果集较小的表，可先将需要的数据筛选出来放入临时表，再与其他表进行 Join 操作。

2. 使用方式 ：先创建临时表，定义合适的字段和索引，然后插入所需数据，再对临时表进行 Join 操作。

3. 注意事项 ：临时表的创建和使用需考虑性能开销，尤其是数据量较大时，创建和插入数据的过程可能会影响系统性能。同时要注意临时表的清理，避免占用过多资源。

4. 使用样例 ：若 test3 表数据量大，但查询只用到 id <=1000 的部分，可以创建临时表：

   • CREATE TEMPORARY TABLE temp_t3 (id TINYINT PRIMARY KEY, b VARCHAR(20), INDEX(b)) ENGINE=INNODB;

   • INSERT INTO temp_t3 SELECT id, b FROM test3 WHERE id <=1000;

   • 然后进行查询：SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN temp_t3 t3 ON t1.b=t3.b;

5. 优化建议 ：适用于某个表的数据量较大，但实际查询中只用到其中一小部分数据的情况，可有效减少 Join 操作的数据量，提高查询效率。

三、使用冗余字段

1. 介绍 ：将一些常用字段冗余到其他表中，减少表之间的 Join 操作。

2. 使用方式 ：确定需要冗余的字段，在表结构中添加该字段，并通过 SQL 语句更新数据，同时在后续数据更新时维护冗余字段的值。

3. 注意事项 ：冗余字段会导致数据冗余，增加存储空间的占用，同时也需要考虑数据一致性的问题，在更新数据时要同步更新冗余字段。

4. 使用样例 ：将 test4 表的 d 字段冗余到 test1 表中的 t4c 字段，SQL 语句如下：

   • 在 test1 表中添加字段 t4c：ALTER TABLE test1 ADD COLUMN t4c TINYINT(3) DEFAULT NULL;

   • 更新数据：UPDATE test1 t1 JOIN test4 t4 ON t1.c=t4.c SET t1.t4c=t4.d;

   • 查询时可只查询 test1、test2、test3 表：SELECT t1.id ,t1.a,t2.b,t3.c,t1.t4c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <=1000;

5. 优化建议 ：适用于对查询性能要求较高且能接受一定程度数据冗余的场景，可有效减少表关联次数，提高查询速度，但需权衡冗余带来的存储和维护成本。

四、用好索引

1. 介绍 ：索引能加快查询速度，在 Join 操作中合理使用索引可显著提升性能。

2. 使用方式 ：为 Join 操作的关联字段建立索引，如主键索引、普通索引、唯一索引等。同时，可根据查询条件和查询列创建复合索引。

3. 注意事项 ：索引虽能提高查询速度，但过多的索引会影响数据的插入、更新和删除操作的性能。另外，要避免索引失效的情况，如关联字段的数据类型不一致、使用函数或表达式等。

4. 使用样例 ：对于 Join 语句 SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c; ，可为 test1 表的 a、b、c 字段创建复合索引：ALTER TABLE test1 ADD INDEX (a, b, c); ，同时为 test2 表的 a、b、c 字段创建索引：ALTER TABLE test2 ADD INDEX (a, b, c);

5. 优化建议 ：在 Join 操作中，只要涉及到关联查询的字段，都应该考虑创建合适的索引，尤其是查询条件中频繁使用的字段。同时，要定期分析和优化索引的使用情况，确保其发挥最大效用。

五、修改查询语句

1. 介绍 ：通过改变查询语句的写法来优化性能，例如使用 EXISTS 或 IN 语句替代部分 Join 操作。

2. 使用方式 ：当某个 Join 表只是用于判断数据行是否存在，而不需要使用表中的字段时，可以考虑改写为 EXISTS 或 IN 子查询。

3. 注意事项 ：虽然 EXISTS 和 IN 在某些情况下能提高性能，但它们的执行方式和性能表现可能因数据库版本和数据情况而异，需要具体分析和测试。

4. 使用样例 ：原 SQL 为 SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <=1000 JOIN test4 t4 ON t1.c=t4.c; ，若只需判断 test4 表中是否存在符合条件的记录，可改写为：

   • SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <=1000 WHERE EXISTS (SELECT id FROM test4 t4 WHERE t4.d=t1.d);

5. 优化建议 ：适用于需要判断某表中是否存在符合条件的记录，而不需要获取该表中具体字段数据的情况，可减少数据传输量和查询复杂度。

六、减少结果集

1. 介绍 ：通过各种方式减少查询返回的结果集，降低 Join 操作的处理量。

2. 使用方式 ：在查询中增加更严格的 where 条件，限制返回的数据量；对返回结果进行分页处理；只查询需要的列，避免查询过多无关列。

3. 注意事项 ：减少结果集的同时要确保查询结果能满足业务需求，避免因过度限制条件导致数据不准确或遗漏的情况。

4. 使用样例 ：

   • 增加 where 条件：SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <=1000 JOIN test4 t4 ON t1.c=t4.c WHERE t1.id >=100;

   • 分页查询：SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <=1000 JOIN test4 t4 ON t1.c=t4.c LIMIT 10 OFFSET 0;

   • 只查询需要的列：SELECT t1.id ,t2.b FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a;

5. 优化建议 ：在任何涉及多表 Join 的查询中，都应优先考虑减少结果集，这是提高查询性能的基础之一，可根据业务需求灵活运用各种限制条件和分页方式。

七、修改数据库配置

1. 介绍 ：调整 MySQL 的一些配置参数，如 join_buffer_size、tmp_table_size 等，以提升 Join 操作的性能。

2. 使用方式 ：在 MySQL 配置文件中修改相关参数值，然后重启 MySQL 服务使配置生效。

3. 注意事项 ：修改数据库配置参数可能会影响整个数据库的性能，尤其是对于老系统，可能会引发其他潜在问题，因此需要谨慎操作，并在修改前进行充分的测试和评估。

4. 使用样例 ：在配置文件中添加或修改以下参数：

   • join_buffer_size = 16M

   • tmp_table_size = 64M

5. 优化建议 ：一般不建议轻易修改数据库配置参数，除非对系统的性能瓶颈有明确的分析和定位，且经过充分测试后确定修改配置能带来显著的性能提升。

八、引入大数据工具

1. 介绍 ：当 Join 表的数据量极大且 MySQL 难以高效处理时，可以考虑引入大数据处理工具。

2. 使用方式 ：使用 ETL 工具将数据抽取到数据仓库（如 ClickHouse 等支持大规模数据处理的数据库），在数据仓库中进行数据加工和 Join 操作，然后将结果提供给应用系统。

3. 注意事项 ：引入大数据工具会增加系统的复杂性和数据时效性问题，数据从 MySQL 抽取到数据仓库可能存在一定的延迟，需要根据业务对数据实时性的要求进行权衡。

4. 使用样例 ：使用 ETL 工具定期将 test1、test2、test3、test4 表的数据抽取到 ClickHouse 中，然后在 ClickHouse 中执行 Join 查询，最后将查询结果返回给应用系统。

5. 优化建议 ：适用于数据量极大、对查询性能要求极高且对数据时效性要求相对不那么严格的大数据分析和统计场景，可充分发挥大数据工具的高性能计算能力。

九、汇总表

1. 介绍 ：通过定时任务将多表 Join 的结果预先计算好，存储到汇总表中，查询时直接查询汇总表。

2. 使用方式 ：创建汇总表，定义与原 Join 查询结果相同的字段结构，然后通过 SQL 语句定期将数据插入汇总表。

3. 注意事项 ：汇总表的数据更新可能存在延迟，需要根据业务需求确定数据更新的频率和时间间隔。同时，汇总表的数据存储和维护也需要考虑，避免占用过多存储资源。

4. 使用样例 ：创建汇总表：

   • CREATE TABLE test_join_result (id TINYINT(3) NOT NULL COMMENT '主键 ID', a VARCHAR(20) DEFAULT NULL, b VARCHAR(20) DEFAULT NULL, c VARCHAR(200) DEFAULT NULL, d TINYINT(3) DEFAULT NULL, e TINYINT(1) DEFAULT NULL, create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id)) ENGINE=INNODB DEFAULT CHARSET=utf8;

   • 定时执行插入数据的 SQL：INSERT INTO test_join_result (id, a, b, c, d) SELECT t1.id ,t1.a,t2.b,t3.c,t4.d FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <=1000 JOIN test4 t4 ON t1.c=t4.c;

   • 查询时直接查询汇总表：SELECT * FROM test_join_result;

5. 优化建议 ：适用于对数据实时性要求不高，且查询操作频繁、数据相对稳定的情况，可有效减轻数据库的实时计算压力，提高查询效率。

在实际应用中，可根据系统的具体情况、业务需求和性能瓶颈等因素，综合选择以上一种或多种优化方式进行组合使用，以达到最佳的优化效果。

相知不迷路，来者皆是兄弟，搜索微信公众号 ：“codingba” or “码出精彩” 交朋友，有更多资源