前言

最近有位小伙伴去大厂面试，被问到一个很刁钻的问题：“假如生产环境有一条SQL，join了10张表，查询耗时超过30秒，你如何一步步排查并优化性能？”

这个问题的难点不在于“如何写SQL”，而在于排查思路的系统性和多层次优化手段的组合。

今天这篇文章专门跟大家一起聊聊这个话题，希望对你会有所帮助。

更多项目实战在Java突击队网：susan.net.cn

一、用这3步锁定瓶颈

当发现一条10表JOIN的SQL很慢时，千万别直接改SQL，而是按下面流程逐步定位：

1.1 使用EXPLAIN分析执行计划

执行EXPLAIN查看每条关联的访问类型。重点关注：

• type列：出现ALL（全表扫描）、index（全索引扫描）需要优化；理想是ref、eq_ref或const。
• rows列：估算扫描行数，明显偏大的表考虑加索引。
• Extra列：出现Using temporary（使用临时表）、Using filesort（文件排序）是性能大敌。

示例：一条orders join users join products的SQL

EXPLAIN SELECT o.id, o.amount, u.name, p.title
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'PAID';

如果EXPLAIN显示users表的type=ALL，说明user_id没有索引——这是最直接的优化点。

1.2 查看真实SQL执行时间分布

SET profiling = 1;
-- 执行你的慢SQL
SELECT ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

结果会显示每个阶段（sending data、creating sort index等）的耗时，帮你判断是IO瓶颈还是CPU/排序瓶颈。

1.3 检查数据库参数配置

• join_buffer_size：太小会导致多次扫描。
• tmp_table_size/max_heap_table_size：太大会导致磁盘临时表。
• innodb_buffer_pool_size：是否足够容纳热数据。

二、为什么join 10张表会慢？

MySQL（InnoDB）中，多表JOIN默认采用Nested Loop Join：从第一张表（驱动表）取出一行，然后循环去下一张表匹配；重复这个过程直到所有表关联完。

时间复杂度 ≈ 扫描驱动表行数 × 每张关联表索引扫描成本。

若驱动表有10万行，每张关联表索引扫描成本为1ms，10张表总成本 = 10万 × (10 × 1ms) = 1000秒。

如果MySQL选择Hash Join（MySQL 8.0.18引入）且所有关联条件都能用上索引，性能会大幅提升，但仍受限于内存和构建哈希表的开销。

三、从SQL到架构的7种武器

下面从低成本、易改动的SQL层到高成本、长效的架构层，逐级给出具体解决方案，每一种都配完整的示例代码。

武器一：索引优化（最立竿见影）

确保每个ON和WHERE条件中的列都有索引。

对于LEFT JOIN，右表关联列必须索引。联合索引要遵循最左前缀原则。

示例： 原SQL

SELECT o.order_no, u.name, p.product_name, c.category_name
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
WHERE o.create_time > '2026-01-01'
  AND u.vip_level > 2
  AND c.status = 'ACTIVE';

排查方法：EXPLAIN发现orders表没有用到create_time索引，users表使用全表扫描。

优化方案：

-- 给orders表加复合索引
ALTER TABLE orders ADD INDEX idx_create_user (create_time, user_id);
-- 给users表加索引
ALTER TABLE users ADD INDEX idx_vip (vip_level);
-- 给categories表加索引
ALTER TABLE categories ADD INDEX idx_status (status);

这样每个关联都能使用索引，从全表扫描变为ref或range访问。

优点：简单直接，对业务代码零侵入。
缺点：索引过多会影响写入性能，需权衡。
适用场景：关联列选择性好，即重复值少。

武器二：调整JOIN顺序

让小表驱动大表。

Nested Loop Join中，驱动表的行数决定了循环次数。

让结果集最小的表做驱动表。

示例：订单表1000万行，用户黑名单表只有100行。查询“黑名单用户的订单”：

-- 原SQL：可能以大表orders驱动
SELECT o.* FROM orders o JOIN blacklist b ON o.user_id = b.user_id;

优化方案：通过STRAIGHT_JOIN强制小表驱动

SELECT STRAIGHT_JOIN o.* FROM blacklist b JOIN orders o ON b.user_id = o.user_id;

验证：使用EXPLAIN查看第一行是否为blacklist（rows≈100）。

优点：不改变业务逻辑，仅调整顺序。
缺点：需要了解数据分布，不恰当使用可能反而变慢。
适用场景：驱动表与关联表数据量悬殊明显。

武器三：拆分JOIN + 应用层组装

当10张表关联只是为了展示一个列表，且数据量不是天文数字时，可以在Java代码中分批查询，再用Stream合并。

示例：查询订单列表，需要关联用户、商品、地址、支付流水等6张表。

优化前（数据库大JOIN）：

SELECT o.id, o.amount, u.name, p.title, a.city, pay.status
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
LEFT JOIN address a ON o.address_id = a.id
LEFT JOIN payment pay ON o.pay_id = pay.id
WHERE o.create_time > '2026-01-01'
LIMIT 20;

优化后（应用层组装）：

// 1. 先查主订单，不JOIN任何表
List<Order> orders = orderMapper.selectList(
    new LambdaQueryWrapper<Order>()
        .gt(Order::getCreateTime, startTime)
        .last("limit 20")
);

if (orders.isEmpty()) return Collections.emptyList();

// 2. 提取关联ID集合
Set<Long> userIds = orders.stream().map(Order::getUserId).collect(Collectors.toSet());
Set<Long> productIds = orders.stream().map(Order::getProductId).collect(Collectors.toSet());
Set<Long> addressIds = orders.stream().map(Order::getAddressId).collect(Collectors.toSet());
Set<Long> payIds = orders.stream().map(Order::getPayId).collect(Collectors.toSet());

// 3. 批量查询关联表（注意分批，防止IN超过1000）
Map<Long, User> userMap = batchQuery(userIds, userMapper::selectBatchIds);
Map<Long, Product> productMap = batchQuery(productIds, productMapper::selectBatchIds);
Map<Long, Address> addressMap = batchQuery(addressIds, addressMapper::selectBatchIds);
Map<Long, Payment> payMap = batchQuery(payIds, payMapper::selectBatchIds);

// 4. 内存组装 (使用Stream)
orders.forEach(order -> {
    order.setUser(userMap.get(order.getUserId()));
    order.setProduct(productMap.get(order.getProductId()));
    order.setAddress(addressMap.get(order.getAddressId()));
    order.setPayment(payMap.get(order.getPayId()));
});
return orders;

// 辅助方法：IN分批，防止超过1000
private <T, ID> Map<ID, T> batchQuery(Set<ID> ids, Function<List<ID>, List<T>> mapper) {
    if (ids == null || ids.isEmpty()) return Collections.emptyMap();
    List<ID> idList = new ArrayList<>(ids);
    List<T> result = new ArrayList<>();
    for (int i = 0; i < idList.size(); i += 500) {
        List<ID> batch = idList.subList(i, Math.min(i + 500, idList.size()));
        result.addAll(mapper.apply(batch));
    }
    return result.stream().collect(Collectors.toMap(this::extractId, Function.identity()));
}

优点：数据库压力小，避免笛卡尔积；可分别优化每条查询。
缺点：代码量增多，需要处理批量查询的尺寸。
适用场景：主表数据量适中（<10万），关联表数据量不大且可独立查询。

武器四：活用临时表或衍生表

将多次使用的JOIN中间结果物化，减少重复计算。

示例：一个报表需要先统计每个用户的订单总额，再关联用户信息和等级表。

优化前：

SELECT u.name, u.level, stat.total
FROM users u
JOIN (
    SELECT user_id, SUM(amount) as total
    FROM orders
    GROUP BY user_id
) stat ON u.id = stat.user_id
WHERE u.status = 'ACTIVE';

优化后（使用临时表）：

-- 创建临时表存放用户订单总额
CREATE TEMPORARY TABLE tmp_user_stat (
    user_id BIGINT PRIMARY KEY,
    total DECIMAL(10,2),
    INDEX(user_id)
) ENGINE=InnoDB;

INSERT INTO tmp_user_stat (user_id, total)
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

-- 然后JOIN
SELECT u.name, u.level, t.total
FROM users u
JOIN tmp_user_stat t ON u.id = t.user_id
WHERE u.status = 'ACTIVE';

-- 用完可以删除（会话结束自动清理）
DROP TEMPORARY TABLE IF EXISTS tmp_user_stat;

优点：避免重复计算GROUP BY，索引友好。
缺点：需要额外存储空间和临时表管理。
适用场景：需要多次引用相同中间结果，或者中间结果集较大。

武器五：使用物化视图或汇总表

如果10表JOIN的查询相对固定（比如BI报表），可以定期通过ETL将结果存储到一张汇总表。

示例：每日销售报表，需要JOIN订单、用户、产品、地区、销售员等8张表。

优化方案：创建汇总表，每天凌晨跑一次。

-- 创建结果表
CREATE TABLE daily_sales_report (
    report_date DATE,
    product_id BIGINT,
    region VARCHAR(50),
    total_amount DECIMAL(12,2),
    order_count INT,
    PRIMARY KEY (report_date, product_id, region)
);

-- 存储过程或定时任务
INSERT INTO daily_sales_report
SELECT DATE(o.create_time), p.id, a.region, SUM(o.amount), COUNT(*)
FROM orders o
JOIN products p ON o.product_id = p.id
JOIN users u ON o.user_id = u.id
JOIN address a ON u.address_id = a.id
-- 还有4张表....
WHERE o.create_time >= CURDATE() - INTERVAL 1 DAY
  AND o.create_time < CURDATE()
GROUP BY DATE(o.create_time), p.id, a.region;

查询时直接查汇总表，毫秒级响应。

优点：查询接近瞬时，对OLAP友好。
缺点：数据有延迟（T+1），需要额外的维护任务。
适用场景：BI报表、运营看板等实时性要求不高的场景。

武器六：换用OLAP引擎

可以换成ClickHouse或Doris。

对于实时性要求不高的复杂分析查询，直接将数据同步到ClickHouse等列式数据库。

它们天生支持大宽表和多表星型模型关联，查询性能远超MySQL。

示例：使用ClickHouse的JOIN（需注意它的分布式特性）

-- ClickHouse中，将大表作为右表时建议使用GLOBAL JOIN
SELECT o.order_no, u.name, p.product_name
FROM orders_local o
GLOBAL JOIN users_local u ON o.user_id = u.id
GLOBAL JOIN products_local p ON o.product_id = p.id
SETTINGS join_algorithm = 'partial_merge';

优点：性能极致，支持PB级数据，压缩比高。
缺点：引入新组件，运维复杂，不支持事务和频繁更新。
适用场景：日志分析、用户行为分析、实时报表。

武器七：垂直拆分 + 读写分离

• 垂直分表：把订单中的文本型大字段、低频字段拆到扩展表，减少单行宽度。
• 读写分离：把复杂JOIN查询路由到从库，避免影响主库写入性能。

示例：将订单表拆为基础表（高频字段）和扩展表（大字段）。

-- 基础表
CREATE TABLE orders_basic (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    create_time DATETIME
);
-- 扩展表
CREATE TABLE orders_ext (
    order_id BIGINT PRIMARY KEY,
    remark TEXT,
    delivery_address TEXT,
    FOREIGN KEY (order_id) REFERENCES orders_basic(id)
);

查询常用字段时只查基础表，需要备注信息时再LEFT JOIN扩展表。

优点：减少单行IO，提升缓存命中率。
缺点：代码需要区分场景，增加复杂度。
适用场景：表中大字段访问频率低，但基础字段频繁查询。

更多项目实战在Java突击队网：susan.net.cn/project

四、优化手段优缺点及选用场景

优化手段	优点	缺点	适用场景
加索引	简单直接，快速见效	过多索引影响写入	关联列选择性好
调整JOIN顺序	不改SQL逻辑，成本低	需要了解数据分布	驱动表比从表小很多
拆分+应用层组装	彻底解耦，数据库压力小	代码复杂度增加	主表数据量中等，关联表多
临时表/衍生表	减少重复计算	额外存储，需管理清理	多次引用相同中间结果
物化视图/汇总表	查询极快	数据有延迟，存储成本高	BI报表，实时性要求不高
换用OLAP引擎	性能极致，支持大数据量	引入新组件，运维复杂	分析型查询
垂直拆分/读写分离	减少锁竞争，提升并发	架构改造大	写入频繁，查询复杂

五、总结

面试时你可以这样回答：

首先，我会用EXPLAIN分析执行计划，找出哪张表出现全表扫描或产生了临时表/文件排序。

然后针对性加索引或调整JOIN顺序。

如果SQL层面优化后仍慢，我会考虑业务拆分：比如应用层分多次查询+内存组装，或者将部分结果提前物化到临时表。

对于生产级别的BI报表，我更倾向将数据同步到ClickHouse这类专用数据库。

最后，如果业务允许，我会从源头减少JOIN表数量——比如在订单表里冗余必要的商品名称、用户昵称等，避免每次都要关联。

总之，优化没有银弹，要结合具体数据量和业务容忍度，组合使用索引、SQL改写、架构拆分等多种手段。

我也会用profile或performance_schema进一步定位是IO还是CPU瓶颈。