Mysql--子查询与直接join

目前需关联四张表进行查询，查询语句如下：

sql 1：使用多个子查询的方式

select
  a.alarm_id,
  a.product,
  a.psa,
  a.monitor_item_name,
  a.start_time,
  a.notify_time,
  a.end_time,
  a.respond_at,
  a.solver_id,
  a.solver_name,
  a.alarm_url,
  a.has_problem,
  b.content,
  d.name,
  d.reason,
  d.scheme
from
  (
    select
      alarm_id,
      product,
      psa,
      monitor_item_name,
      start_time,
      notify_time,
      end_time,
      respond_at,
      solver_id,
      solver_name,
      alarm_url,
      has_problem
    from
      alarm_lists
    where
      alarm_level=5
  ) a
  left join (
    select
      alarm_id,
      content
    from
      alarm_no_problems
  ) b on a.alarm_id=b.alarm_id
  left join (
    select
      problem_id,
      alarm_id
    from
      alarm_problem_relations
  ) c on a.alarm_id=c.alarm_id
  left join (
    select
      id,
      name,
      reason,
      scheme
    from
      alarm_problems
  ) d on c.problem_id=d.id

 

sql 2：直接join关联

SELECT
  a.alarm_id,
  a.product,
  a.psa,
  a.monitor_item_name,
  a.start_time,
  a.notify_time,
  a.end_time,
  a.respond_at,
  a.solver_id,
  a.solver_name,
  a.alarm_url,
  a.has_problem,
  b.content,
  d.name,
  d.reason,
  d.scheme
FROM
  alarm_lists a
LEFT JOIN alarm_no_problems b ON a.alarm_id = b.alarm_id
LEFT JOIN alarm_problem_relations c ON a.alarm_id = c.alarm_id
LEFT JOIN alarm_problems d ON c.problem_id = d.id
WHERE
  a.alarm_level = 5

现代数据库（如 MySQL 8.0+）的优化器通常会将外层 WHERE 条件下推到子查询中（称为 谓词下推），所以简单的过滤条件下，wher放在外层也是一样的效果，针对8.0以下版本，sql 2可变形为：

SELECT
  a.alarm_id,
  a.product,
  a.psa,
  a.monitor_item_name,
  a.start_time,
  a.notify_time,
  a.end_time,
  a.respond_at,
  a.solver_id,
  a.solver_name,
  a.alarm_url,
  a.has_problem,
  b.content,
  d.name,
  d.reason,
  d.scheme
FROM
  (
    SELECT
      alarm_id,
      product,
      psa,
      monitor_item_name,
      start_time,
      notify_time,
      end_time,
      respond_at,
      solver_id,
      solver_name,
      alarm_url,
      has_problem
    FROM
      alarm_lists
    WHERE
      alarm_level = 5
  ) a
LEFT JOIN alarm_no_problems b ON a.alarm_id = b.alarm_id
LEFT JOIN alarm_problem_relations c ON a.alarm_id = c.alarm_id
LEFT JOIN alarm_problems d ON c.problem_id = d.id

 

性能比较

场景子查询直接join
关联表字段较少 临时表较少，性能接近直接join 高效（尤其有索引时）
关联表字段较多 临时表较大，i/o和内存开销高 高效（避免临时表）
关联字段有索引 临时表无索引，可能全表扫描 高效（利用索引）
有复杂过滤条件 可提前过滤数据，减少后续计算 依赖优化器的谓词下推能力
关联表数据量大 子查询结果集小则高效，大则临时表开销显著 大数据量时依赖索引效率

 

最终建议

1、优先使用直接 JOIN，除非子查询能显著减少数据量（例如通过聚合或复杂过滤），否则直接 JOIN 原表更高效

2、确保所有 JOIN 字段（如 alarm_id、problem_id）和 WHERE 条件字段（如 alarm_level）均有索引

3、避免 SELECT *，子查询中尽量只选择必要的字段，减少临时表的大小，如 SELECT alarm_id, content FROM alarm_no_problems; 

4、使用 EXPLAIN 命令查看 SQL 的实际执行计划，确保优化器按预期工作，如 EXPLAIN SELECT ... FROM alarm_lists a LEFT JOIN ...; 

 

EXPLAIN

EXPLAIN 命令的作用是分析 SQL 语句的执行计划，它会显示 MySQL 优化器如何执行查询，包括表的读取顺序、使用的索引、JOIN 类型、扫描行数等关键信息。通过 EXPLAIN，可以快速诊断查询的性能瓶颈，并针对性地优化索引或 SQL 结构。

 

EXPLAIN 的核心用途

  1. 查看执行计划：了解 MySQL 如何解析和执行你的 SQL。

  2. 验证索引使用：检查是否用到了索引，是否存在全表扫描。

  3. 估算性能成本：通过扫描行数（rows）和 JOIN 类型（type）判断查询效率。

  4. 发现潜在问题：如临时表、文件排序（Using temporary、Using filesort）等。

 

输出字段解析

字段说明优化关注点
id 查询的序列号（多条子查询时区分执行顺序） 复杂查询的执行顺序是否合理
select_type 查询类型（如 SIMPLE、PRIMARY、SUBQUERY） 是否有多余的子查询或复杂操作
table 当前操作涉及的表名 确认表关联顺序是否最优
type JOIN 类型（性能关键指标，从优到劣排序）：
system > const > eq_ref > ref > range > index > ALL		 避免 ALL（全表扫描）和 index
possible_keys 可能使用的索引 检查是否漏掉了预期索引
key 实际使用的索引 确认索引是否生效
rows 预估需要扫描的行数 行数越小越好
Extra 额外信息（如 Using where、Using index、Using temporary） 发现潜在问题（如临时表、文件排序）

 

如上示例语句，假设 EXPLAIN 输出如下（简化版）：

id    select_type    table    type    possible_keys    key    rows    Extra
1    SIMPLE    a    ref    idx_alarm_level    idx_alarm_level    1000    Using where
1    SIMPLE    b    ref    idx_alarm_id    idx_alarm_id    1    NULL
1    SIMPLE    c    ref    idx_alarm_id    idx_alarm_id    5    NULL
1    SIMPLE    d    eq_ref    PRIMARY    PRIMARY    1    NULL

解读

  1. 主表 a：

    • type=ref，key=idx_alarm_level：成功使用索引过滤 alarm_level=5，扫描 1000 行。

  2. 表 b：

    • type=ref，key=idx_alarm_id：通过索引关联 a.alarm_id，每行匹配 1 条记录。

  3. 表 c 和 d：

    • c 使用索引关联，d 通过主键关联，效率较高。

  4. 无临时表或文件排序：Extra 列无警告信息。

优化建议

1、索引缺失时的修复

-- 确保以下索引存在
ALTER TABLE alarm_lists ADD INDEX idx_alarm_level (alarm_level);
ALTER TABLE alarm_no_problems ADD INDEX idx_alarm_id (alarm_id);
ALTER TABLE alarm_problem_relations ADD INDEX idx_alarm_id (alarm_id);
ALTER TABLE alarm_problem_relations ADD INDEX idx_problem_id (problem_id);

2、全表扫描的紧急处理

如果 type=ALL，优先为过滤条件（如 alarm_level）和 JOIN 字段添加索引

3、避免 Using filesort

如果排序无法利用索引（如 ORDER BY 未命中索引），考虑调整索引或业务逻辑

 

posted @ 2025-04-18 17:10  心恩惠动  阅读(28)  评论(0)    收藏  举报