慢查询就像数据库里的 “慢车”，容易拖慢整体速度。多表关联查询更麻烦：数据量大时像在海量文件里翻找，逻辑冗余又会做无用功，很容易卡壳。下面以订单关联查询为例，从逻辑到索引、实际应用场景，我们一起来优化慢查询：（篇幅所限，不延伸，不拓展；所有代码块和表格均可左右滚动）

一、原始查询

-- 原始查询：直接关联三张表并在最后过滤条件，存在性能问题
-- 从orders表（别名o）、users表（别名u）、products表（别名p）关联查询
-- 关联条件：orders表的user_id等于users表的id，orders表的product_id等于products表的id
-- 过滤条件：订单状态为已支付（PAID）、用户年龄大于25岁、产品价格大于80
SELECT * 
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' 
    AND u.age > 25 
    AND p.price > 80;

解析：这条SQL语句的作用是从多个关联表中查询符合特定条件的数据。具体来说，它通过JOIN关键字将orders（订单表，别名o）、users（用户表，别名u）和products（产品表，别名p）进行关联——订单表与用户表通过o.user_id = u.id关联（即订单的用户ID对应用户表的ID），订单表与产品表通过o.product_id = p.id关联（即订单的产品ID对应产品表的ID）；然后通过WHERE子句筛选出满足“订单状态为‘已支付’（o.status = 'PAID'）、用户年龄大于25岁（u.age > 25）、产品价格大于80（p.price > 80）”这三个条件的所有字段数据（SELECT *）。最终查询结果会包含这些符合条件的订单、对应用户及对应产品的全部信息。

问题：原始查询的执行计划中，预估扫描行数可能非常大，这是导致查询慢的主要原因之一。具体为：

• SELECT * 效率低：返回所有字段，增加网络传输量和解析成本。
• WHERE 条件后置：未提前过滤数据，多表关联时数据量过大，严重拖慢查询。
• 缺少合适索引：多表JOIN的连接字段和过滤字段无索引，连接效率极差。
• 数据行数级联放大：多表JOIN时未过滤的数据会导致中间结果集膨胀，浪费I/O资源。

二、基础优化（逻辑优化）

通过提前过滤数据、精简字段、优化JOIN顺序提升性能，包含三种实现方式：

1、子查询写法

-- 主查询：只选取需要的字段（订单id、用户名、产品名、订单金额）
-- 关联三个子查询的结果集，每个子查询已提前过滤并筛选字段
SELECT o.id, u.username, p.name, o.amount FROM (
    -- 子查询1：过滤orders表，只保留状态为已支付的记录，并只选取需要的字段
    SELECT id, user_id, product_id, amount
    FROM orders
    WHERE status = 'PAID'
) o JOIN (
    -- 子查询2：过滤users表，只保留年龄大于25的用户，并只选取需要的字段
    SELECT id, username
    FROM users
    WHERE age > 25
) u ON o.user_id = u.id JOIN (
    -- 子查询3：过滤products表，只保留价格大于80的产品，并只选取需要的字段
    SELECT id, name
    FROM products
    WHERE price > 80
) p ON o.product_id = p.id;

解析：这段 SQL 代码采用子查询写法优化数据处理。主查询从三个子查询结果集获取订单 ID、用户名、产品名和订单金额。每个子查询先过滤数据：订单表保留已支付记录，用户表筛选年龄大于 25 的用户，产品表选取价格超过 80 的产品，再关联所需字段。这种方式通过子查询提前对每张表进行过滤（缩表），仅保留需要的字段和数据，代码结构简单，减少后续JOIN的数据量，适合简单过滤逻辑。不过，过多嵌套会降低代码可读性，需根据实际情况权衡使用。

2、CTE（公用表表达式）写法

-- 定义CTE（公用表表达式）：将过滤后的结果集命名，方便后续关联
-- CTE1：过滤orders表，命名为filtered_orders
WITH filtered_orders AS (
    SELECT id, user_id, product_id, amount
    FROM orders
    WHERE status = 'PAID'
), 
-- CTE2：过滤users表，命名为filtered_users
filtered_users AS (
    SELECT id, username
    FROM users
    WHERE age > 25
), 
-- CTE3：过滤products表，命名为filtered_products
filtered_products AS (
    SELECT id, name
    FROM products
    WHERE price > 80
) 
-- 主查询：关联三个CTE，只选取需要的字段
SELECT o.id, u.username, p.name, o.amount FROM filtered_orders o JOIN filtered_users u ON o.user_id = u.id JOIN filtered_products p ON o.product_id = p.id;

解析：这段 SQL 代码使用 CTE（公用表表达式）优化查询。通过WITH关键字定义三个 CTE：filtered_orders筛选已支付订单，filtered_users保留年龄大于 25 的用户，filtered_products选取价格超 80 的产品。主查询直接关联这些命名结果集，获取所需字段。CTE 与子查询逻辑相似，但通过WITH关键字将过滤逻辑前置，使代码层次分明，提升复杂查询的可读性，便于团队理解和维护。这种方式适用于需多次复用相同过滤结果的场景，兼顾性能与可维护性。

3、视图（View）写法

-- 创建视图1：存储orders表的过滤逻辑（状态为已支付），只包含需要的字段
CREATE VIEW view_filtered_orders AS SELECT id, user_id, product_id, amount FROM orders WHERE status = 'PAID';
-- 创建视图2：存储users表的过滤逻辑（年龄大于25），只包含需要的字段
CREATE VIEW view_filtered_users AS SELECT id, username FROM users WHERE age > 25;
-- 创建视图3：存储products表的过滤逻辑（价格大于80），只包含需要的字段
CREATE VIEW view_filtered_products AS SELECT id, name FROM products WHERE price > 80;

-- 查询视图：直接关联三个视图，获取需要的结果，复用已定义的过滤逻辑
SELECT o.id, u.username, p.name, o.amount FROM view_filtered_orders o JOIN view_filtered_users u ON o.user_id = u.id JOIN view_filtered_products p ON o.product_id = p.id;

解析：这段 SQL 代码使用视图（View）优化数据查询。通过创建三个视图：view_filtered_orders筛选已支付订单，view_filtered_users保留年龄大于 25 的用户，view_filtered_products选取价格超 80 的产品，将过滤逻辑存储为虚拟表。主查询直接关联这些视图获取所需字段，复用已定义的过滤规则。视图适合多场景复用相同过滤条件，减少重复代码，提升开发效率。但需注意，视图是虚拟表，每次查询会重新执行底层逻辑，未实际存储数据，适合过滤规则固定、需频繁使用的场景，可提高代码可维护性。

4、基础优化小结

三、进阶优化

1、索引优化

索引是联表查询的关键，针对过滤和连接字段创建合理索引可大幅提升效率。但索引并不是越多越好，过多的索引会增加写操作的开销，并占用额外的存储空间。因此，需要根据实际的查询需求来创建索引，并定期维护索引。

（1）基础索引

-- 1. 为orders表的过滤字段和连接字段建复合索引
-- 用途：快速过滤status='PAID'的订单，并加速与users、products的连接
CREATE INDEX idx_orders_status_userid_productid ON orders(status, user_id, product_id);

-- 2. 为users表的过滤字段建索引
-- 用途：快速过滤age>25的用户
CREATE INDEX idx_users_age ON users(age);

-- 3. 为products表的过滤字段建索引
-- 用途：快速过滤price>80的产品
CREATE INDEX idx_products_price ON products(price);

解析：这段SQL为三个表创建索引以优化查询：为orders表创建复合索引idx_orders_status_userid_productid，按“过滤字段（status）在前、连接字段（user_id、product_id）在后”排序，既快速筛选PAID状态订单，又加速与users、products表的连接；为users表建idx_users_age索引，快速过滤25岁以上用户；为products表建idx_products_price索引，高效筛选价格超80的产品。

（2）覆盖索引（避免回表查询）

-- 为orders表建覆盖索引：包含查询所需的所有字段（过滤+连接+结果字段）
CREATE INDEX idx_orders_cover ON orders(status) INCLUDE (id, user_id, product_id, amount);
-- 说明：
-- - status用于过滤，INCLUDE后的字段是子查询需要返回的结果（id, user_id等）
-- - 数据库可直接从索引获取数据，无需回表查询原表，减少I/O操作
-- - MySQL不支持INCLUDE，可将字段加入复合索引：(status, id, user_id, product_id, amount)
-- - MySQL中，覆盖索引需要将所有字段包含在复合索引中，正确写法：CREATE INDEX idx_orders_cover ON orders(status, id, user_id, product_id, amount);

-- 为users表建覆盖索引
CREATE INDEX idx_users_cover ON users(age) INCLUDE (id, username);

-- 为products表建覆盖索引
CREATE INDEX idx_products_cover ON products(price) INCLUDE (id, name);

解析：覆盖索引包含查询所需的所有字段，数据库无需访问原表，直接从索引获取数据，效率最优。这段 SQL 为三个表设计覆盖索引以优化查询效率：为 orders 表创建idx_orders_cover索引，以过滤字段status为主键，通过INCLUDE子句包含结果字段（id、user_id 等），MySQL 环境下可转为复合索引；为 users 表建idx_users_cover索引，以过滤字段age为主键，包含结果字段 id、username；为 products 表建idx_products_cover索引，以过滤字段price为主键，包含结果字段 id、name。覆盖索引使数据库直接从索引获取数据，避免回表查询，减少 I/O 操作。

2、JOIN顺序手动优化（引导执行计划）

-- 假设过滤后的数据量：filtered_products（最小） < filtered_users < filtered_orders
-- 调整JOIN顺序：先连接最小的表，逐步扩大范围
SELECT o.id, u.username, p.name, o.amount 
FROM (SELECT id, name FROM products WHERE price > 80) p  -- 数据量最小的表放最前
JOIN (SELECT id, user_id, product_id, amount FROM orders WHERE status = 'PAID') o 
  ON p.id = o.product_id  -- 先连接products和orders
JOIN (SELECT id, username FROM users WHERE age > 25) u 
  ON o.user_id = u.id;  -- 最后连接users

解析：这段SQL通过手动调整JOIN顺序优化查询性能，其核心逻辑是将过滤后数据量最小的表放在JOIN最前面，以减少后续连接的处理行数。具体为：

1. 子查询过滤：先通过WHERE子句对各表进行过滤，如products表过滤出price>80的数据，orders表过滤出status='PAID'的数据，users表过滤出age>25的数据。
2. JOIN顺序：假设过滤后数据量关系为filtered_products < filtered_users < filtered_orders，则先连接products和orders表，再连接users表。这样可让最小的数据集先参与连接，逐步扩大结果集，降低内存和I/O消耗。
3. 注意事项：实际优化时需结合执行计划，综合考虑过滤后数据量、索引使用情况等因素，避免仅依据理论数据量排序。例如，若某表虽原始数据量大，但过滤后数据极少，也应优先参与连接。通过EXPLAIN分析各表过滤后的实际数据量及索引使用效率，才能精准优化JOIN顺序。

3、执行计划分析（验证优化效果）

-- 分析优化后SQL的执行计划
EXPLAIN 
SELECT o.id, u.username, p.name, o.amount 
FROM (SELECT id, user_id, product_id, amount FROM orders WHERE status = 'PAID') o 
JOIN (SELECT id, username FROM users WHERE age > 25) u ON o.user_id = u.id 
JOIN (SELECT id, name FROM products WHERE price > 80) p ON o.product_id = p.id;

指标解读：

• type：连接类型，ref（索引查找）、range（范围扫描）优于 ALL（全表扫描）。
• key：实际使用的索引，若为 NULL 说明未用到索引，需检查索引设计。
• rows：预估扫描行数，数值越小越好，说明过滤有效。
• Extra：出现 Using index 表示使用了覆盖索引，效果最佳。

说明：除了EXPLAIN，还可以使用其他工具来分析执行计划，例如MySQL的Performance Schema、慢查询日志等。

4、分库分表（超大数据量场景）

当单表数据量达千万级以上，可通过分库分表拆分数据：

-- 示例：按status分表存储orders（仅逻辑示例，实际需中间件支持）
-- 1. 拆分orders表为orders_paid（status='PAID'）和orders_unpaid（其他状态）
CREATE TABLE orders_paid LIKE orders;  -- 已支付订单表
CREATE TABLE orders_unpaid LIKE orders;  -- 未支付订单表

-- 2. 查询时直接访问拆分后的表，避免全表扫描
SELECT id, user_id, product_id, amount FROM orders_paid;  -- 无需再过滤status，直接获取数据

解析：分表后单表数据量减少，查询效率大幅提升，适合超大数据量场景（需中间件如Sharding-JDBC支持）。但分库分表会带来一些复杂性，例如数据一致性、跨库查询等问题，需要仔细评估是否适合使用分库分表。

5、临时表缓存（高频查询场景）

-- 1. 创建临时表存储过滤后的orders数据
CREATE TEMPORARY TABLE temp_orders AS 
SELECT id, user_id, product_id, amount FROM orders WHERE status = 'PAID';

-- 2. 为临时表建索引，加速连接
CREATE INDEX idx_temp_orders_userid ON temp_orders(user_id);

-- 3. 关联临时表查询
SELECT o.id, u.username, p.name, o.amount 
FROM temp_orders o 
JOIN (SELECT id, username FROM users WHERE age > 25) u ON o.user_id = u.id 
JOIN (SELECT id, name FROM products WHERE price > 80) p ON o.product_id = p.id;

解析：临时表缓存过滤结果，适合高频重复查询，减少重复计算，但仅在当前会话有效，不适合实时性要求极高的场景。临时表只存在于当前会话中，如果会话断开，临时表就会被删除。因此，临时表适合用于会话级别的缓存，不适合用于全局缓存。

四、JOIN优化总结

以上JOIN优化方法需结合实际业务场景（数据量、查询频率、实时性要求）选择，原则是：“减少数据扫描范围”、“降低连接开销”、“利用索引加速”。

五、实际场景优化

以下是实际业务场景，将结合逻辑优化、精准索引设计与 JOIN 顺序调整等方法，通过前置过滤缩表、合理建索引、优化连接顺序，形成可直接落地的 JOIN 查询优化方案。

场景一：电商平台「已支付订单关联用户和商品」查询优化

业务需求：查询25岁以上用户购买的价格>80元的已支付订单，返回订单ID、用户名、商品名、订单金额。
原始问题：数据量较大（orders表1000万行，users表500万行，products表10万行），原始查询耗时10秒+。
优化方案
步骤1：逻辑优化（提前缩表+精简字段）
采用CTE写法，先过滤再连接，减少数据量：

WITH filtered_orders AS (
    -- 提前过滤已支付订单，只保留需要的字段
    SELECT id, user_id, product_id, amount 
    FROM orders 
    WHERE status = 'PAID'  -- 过滤条件前置
), filtered_users AS (
    -- 提前过滤25岁以上用户
    SELECT id, username 
    FROM users 
    WHERE age > 25 
), filtered_products AS (
    -- 提前过滤价格>80的商品
    SELECT id, name 
    FROM products 
    WHERE price > 80 
)
-- 关联过滤后的小表，只查必要字段
SELECT o.id, u.username, p.name, o.amount 
FROM filtered_orders o
JOIN filtered_users u ON o.user_id = u.id  -- 连接字段仅用过滤后的数据
JOIN filtered_products p ON o.product_id = p.id;

步骤2：索引优化（支撑快速过滤和连接）
针对过滤和连接字段创建索引，加速数据筛选：

-- 1. orders表：覆盖过滤+连接+结果字段（复合索引+覆盖索引）
CREATE INDEX idx_orders_status_cover ON orders(status, user_id, product_id) 
INCLUDE (id, amount);  -- MySQL用：(status, user_id, product_id, id, amount)

-- 2. users表：过滤字段+连接字段+结果字段
CREATE INDEX idx_users_age_cover ON users(age, id) 
INCLUDE (username);  -- 先过滤age，再连接id，最后返回username

-- 3. products表：过滤字段+连接字段+结果字段
CREATE INDEX idx_products_price_cover ON products(price, id) 
INCLUDE (name);  -- 先过滤price，再连接id，最后返回name

步骤3：JOIN顺序调整（小表优先）
分析过滤后的数据量：filtered_products（约2万行）< filtered_users（约150万行）< filtered_orders（约300万行），调整JOIN顺序：

-- 先连接最小的products表，再连接users表，减少中间结果集
SELECT o.id, u.username, p.name, o.amount 
FROM filtered_products p
JOIN filtered_orders o ON p.id = o.product_id  -- 先连小表
JOIN filtered_users u ON o.user_id = u.id;

步骤4：执行计划验证
用EXPLAIN确认优化效果：

EXPLAIN 
-- 上述CTE查询语句...

优化效果：查询耗时从10秒+降至0.8秒，性能提升12倍+。效果参考：

+ 优化效果：在过滤条件命中索引的前提下，测试环境耗时从12.3s降至0.82s（1亿行数据，NVMe SSD）
+ 核心影响因素：
+   - 索引未命中时仍可能超8秒
+   - HDD硬盘场景下约1.5秒

场景二：高频重复查询「近7天热销商品订单」优化

业务需求：运营部门每5分钟查询一次近7天已支付订单中，销量前10的商品（关联商品和用户信息），支持实时数据看板。
原始问题：高频查询导致数据库CPU占用率高（30%→80%），重复执行过滤逻辑浪费资源。

优化方案
步骤1：临时表缓存+索引优化
用临时表缓存近7天订单数据，避免重复过滤：

-- 1. 创建临时表并缓存近7天已支付订单（当前会话有效）
CREATE TEMPORARY TABLE temp_recent_orders AS 
SELECT id, user_id, product_id, amount, create_time 
FROM orders 
WHERE status = 'PAID' 
  AND create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 2. 为临时表创建连接索引
CREATE INDEX idx_temp_orders_productid ON temp_recent_orders(product_id);

步骤2：视图复用+JOIN优化
用视图封装商品和用户的过滤逻辑，减少代码重复：

-- 1. 创建商品和用户的过滤视图（复用逻辑）
CREATE VIEW view_hot_products AS 
SELECT id, name, price 
FROM products 
WHERE price > 50;  -- 热销商品通常价格>50

CREATE VIEW view_valid_users AS 
SELECT id, username 
FROM users 
WHERE status = 'ACTIVE';  -- 只保留活跃用户

-- 2. 关联临时表和视图，查询热销商品销量
SELECT p.name, COUNT(o.id) AS sales_count 
FROM temp_recent_orders o
JOIN view_hot_products p ON o.product_id = p.id
JOIN view_valid_users u ON o.user_id = u.id
GROUP BY p.name 
ORDER BY sales_count DESC 
LIMIT 10;

步骤3：分表存储历史数据（长期优化）
按月份分表存储orders表，减少单表数据量：

-- 1. 分表：orders_202507（7月数据）、orders_202506（6月数据）...
-- 2. 查询时直接访问当月表，避免扫描全表
CREATE TEMPORARY TABLE temp_recent_orders AS 
SELECT id, user_id, product_id, amount 
FROM orders_202507  -- 直接访问7月分表
WHERE status = 'PAID' 
  AND create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

优化效果：单查询耗时从2秒降至0.3秒，数据库CPU占用率从80%降至25%，满足高频查询需求。

场景三：超大数据量「年度订单报表生成」优化

业务需求：财务部门月底生成年度订单报表，需关联用户、商品、支付表（4张表），数据量超5000万行。
原始问题：查询超时（30秒+），全表扫描导致I/O瓶颈。

优化方案
步骤1：分库分表+索引
按年度分表存储订单数据，只查询目标年度表：

-- 1. 访问2024年度分表（orders_2024），避免扫描全量5000万行
SELECT id, user_id, product_id, pay_amount 
FROM orders_2024 
WHERE status = 'PAID';

步骤2：子查询缩表+覆盖索引
结合覆盖索引，避免回表查询：

-- 为分表创建覆盖索引（包含所有查询字段）
CREATE INDEX idx_orders_2024_cover ON orders_2024(
    status, user_id, product_id
) INCLUDE (id, pay_amount, create_time);

-- 子查询缩表后关联
SELECT o.id, u.username, p.name, o.pay_amount 
FROM (
    SELECT id, user_id, product_id, pay_amount 
    FROM orders_2024 
    WHERE status = 'PAID'
) o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;

步骤3：离线计算+物化视图（非实时场景）
报表非实时需求，用物化视图定期刷新数据：

-- PostgreSQL示例：创建物化视图，每天凌晨3点刷新
CREATE MATERIALIZED VIEW mv_2024_paid_orders AS 
SELECT o.id, u.username, p.name, o.pay_amount 
FROM orders_2024 o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'PAID';

-- 创建刷新任务
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_2024_paid_orders;  -- 每天执行一次

优化效果：报表生成时间从超时（30秒+）降至15秒内，且不占用业务高峰期数据库资源。

总结：实际业务场景优化思路

1、中小数据量场景：优先用「逻辑优化（CTE/子查询）+ 索引优化（复合+覆盖索引）」，快速见效。
2、高频查询场景：叠加「临时表缓存+视图复用」，减少重复计算，降低数据库压力。
3、超大数据量场景：结合「分库分表+物化视图」，从存储层拆分数据，适配大规模数据查询。

在实际应用中，需要根据具体的业务需求和技术栈来选择合适的优化方案，并进行充分的测试和验证。所有场景均需通过EXPLAIN分析执行计划，验证索引和JOIN逻辑是否生效，避免“优化无效”。

综上所述：优化慢 SQL 需兼顾逻辑精简与技术适配。从基础过滤到分库分表，核心是减少数据处理量。结合业务场景选择方案，并以执行计划验证，方能高效解决性能问题。

阅读原文：原文链接