MySQL SQL优化案例：相关子查询(dependent subquery)优化

超、凢脫俗 2022-08-19 08:14 347阅读 0赞

原始语句：

SELECT
      t1.*
    FROM
      t_payment_bank_account_info t1
    WHERE
      EXISTS (
        SELECT
          1
        FROM
          t_payment_account_dtl t2
        WHERE
          t1.account_no = t2.account_no
        AND t2.parent_account_no = '7311810182600115231'
        AND t2.txn_Date >= '2015-12-23'
        AND t2.account_no != t2.opp_acc_no
      );

执行计划

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4552 | Using where |
    |  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL | 7924 | Using where |
    +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

语句在hotfix环境运行时间：14 rows in set (27.98 sec)

**第一个问题：**select \* 语句在生产环境严格禁止，需明确指明查询字段。

**第二个问题：**相关子查询，尤其是使用不到索引时效率或非常低，可改写成join方式。

select t1.*
    from t_payment_bank_account_info t1
    join t_payment_account_dtl t2
    using(account_no)
    where t2.parent_account_no = '7311810182600115231'
        AND t2.txn_Date >= '2015-12-23'
        AND t2.account_no != t2.opp_acc_no
    group by 需要查询的字段;

（因为join时内表中的一条记录可能跟外表中的多条记录匹配，所以最终会比使用相关子查询的方式多出一些重复的记录结果，故使用group by去重复，当然也可以使用distinct关键字，两者原理相同。如果重复值对于最终需求并没有什么影响则可以移除该从句以避免分组、排序造成的临时表和文件排序等额外开销，提高查询效率）

执行计划：

+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
    +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
    |  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4552 | Using temporary; Using filesort |
    |  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 7924 | Using where; Using join buffer  |
    +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

语句在hotfix环境运行时间：14 rows in set (2.67 sec)

**第三个问题：**到这里优化还没有结束，看到上述执行计划中有Using join buffer 出现，这是MySQL内部的一个优化，可大大减少join时的IO开销。但如果能在join字段上添加适当索引的话，性能还能更加显著的提升。

那么是要在t\_payment\_account\_dtl表还是在t\_payment\_bank\_account\_info表的account\_no字段添加索引呢？

可以看一下每个表中account\_No字段的筛选度：

mysql> select count(*) from t_payment_account_dtl;
    +----------+
    | count(*) |
    +----------+
    |     7594 |
    +----------+
    1 row in set (0.04 sec)
    
    mysql> select count(distinct(account_No)) from t_payment_account_dtl;
    +-----------------------------+
    | count(distinct(account_No)) |
    +-----------------------------+
    |                          75 |
    +-----------------------------+
    1 row in set (0.00 sec)
    
    mysql> select count(distinct(account_No)) from t_payment_bank_account_info\G
    *************************** 1. row ***************************
    count(distinct(account_No)): 4753
    1 row in set (0.00 sec)
    
    mysql> select count(*) from t_payment_bank_account_info\G
    *************************** 1. row ***************************
    count(*): 4789
    1 row in set (0.01 sec)

发现t\_payment\_bank\_account\_info表account\_no字段筛选度较高，那么在该表添加索引。((inner)join时MySQL会自动根据索引情况选择哪个表做内部表那个表做外部表)

mysql> alter table t_payment_bank_account_info add index idx_account_no(account_no);
    Query OK, 0 rows affected (0.14 sec)
    Records: 0  Duplicates: 0  Warnings: 0

在看执行计划

mysql> desc select t1.*  from t_payment_bank_account_info t1 join t_payment_account_dtl t2 using(account_no) where t2.parent_account_no = '7311810182600115231'     AND t2.txn_Date >= '2015-12-23'     AND t2.account_no != t2.opp_acc_no   group by 需要查询的字段;
    +----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+
    | id | select_type | table | type | possible_keys  | key            | key_len | ref                       | rows | Extra                                        |
    +----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | t2    | ALL  | NULL           | NULL           | NULL    | NULL                      | 7924 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | t1    | ref  | idx_account_no | idx_account_no | 99      | dcf_payment.t2.account_No |   22 | Using where                                  |
    +----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+
    2 rows in set (0.00 sec)

发现可以使用刚才创建的索引，hotfix执行时间只需14 rows in set (0.01 sec)

这里MySQL选择t2做内部表t1做外部表，join阶段对于t2中的每条记录依次从t1的索引中进行查找。

在上边统计t1, t2行数的时候发现t2记录数是t1的近两倍，若果两表account\_no字段上都有索引，那么使用记录数较少的表t1做内部表性能可能会更好。

我们尝试一下

mysql> alter table t_payment_account_dtl  add index idx_account_no(account_no);
    
    mysql> desc select t1.*  from t_payment_bank_account_info t1 join t_payment_account_dtl t2 using(account_no) where t2.parent_account_no = '7311810182600115231'     AND t2.txn_Date >= '2015-12-23'     AND t2.account_no != t2.opp_acc_no   group by 需要查询的字段;
    +----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+
    | id | select_type | table | type | possible_keys  | key            | key_len | ref                       | rows | Extra                                        |
    +----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | t2    | ALL  | NULL           | NULL           | NULL    | NULL                      | 7924 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | t1    | ref  | idx_account_no | idx_account_no | 99      | dcf_payment.t2.account_No |   22 | Using where                                  |
    +----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+
    2 rows in set (0.00 sec)

发现并没有像我们想象的那样选择记录数较少的t1做内部表。而是使用了t2做内部表使用索引筛选度较高的t1做了外部表~

发表评论取消回复

表情：

评论列表（有 0 条评论，347人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql查询优化之IN子查询优化方法

项目中有需要,使用MySQL的in子查询,查询符合in子查询集合中条件的数据,但是没想到的是,MySQL的in子查询会如此的慢,让人无法接受,下面这篇文章主要给大家介绍了关于M

阳光穿透心脏的1/2处/ 2024年03月22日 20:05/ 0 赞/ 49 阅读

相关 mysql 子查询优化 in

在 in 里面用子查询时，explain查询效率时，有时候会有dependent subquery的情况，这时候就不推荐用in，而是用join去关联这张表，效率会更高一点。

淩亂°似流年/ 2024年03月22日 09:49/ 0 赞/ 84 阅读

相关 MySQL子查询优化思路

> 子查询的优化最重要的优化建议是尽可能使用关联查询来代替。 > > 因为子查询每次查询出来的结果会暂时存放在临时表里，也会进行IO，与其如此还不如直接使用join来进行关联

超、凢脫俗/ 2023年10月08日 11:52/ 0 赞/ 52 阅读

相关 MySQL SQL优化案例：相关子查询(dependent subquery)优化

原始语句： SELECT t1. FROM t_payment_bank_account_info t1 WHERE

超、凢脫俗/ 2022年08月19日 08:14/ 0 赞/ 348 阅读

相关 mysql in() 子查询优化

mysql in() 子查询优化 in 如果里面是一个子查询是不会使用索引的比如 select \ from user where id in

末蓝、/ 2022年06月05日 05:08/ 0 赞/ 283 阅读

相关 SQL优化案例-使用with as优化Subquery Unnesting（七）

使用 no\_unnest hint可以让执行计划产生filter，即不展开，但一般情况下使用unnest hint无法消除filter。如下SQL，找出库中非唯一索引，那

悠悠/ 2022年05月16日 14:23/ 0 赞/ 148 阅读

相关将SQL优化做到极致 - 子查询优化

摘要：编辑手记：子查询是SQL中比较重要的一种语法，恰当地应用会很大程度上提高SQL的性能，若用的不得当，也可能会带来很多问题。因此子查询也是SQL比较难优化的部分。今天一起

不念不忘少年蓝@/ 2022年04月06日 09:47/ 0 赞/ 462 阅读

相关 mysql关联查询sql优化

正确使用EXISTS和IN 如何正确使用EXISTS和IN 如何正确使用EXISTS和IN 当两张表关联查询时，要使用一张数据小的表关联数据大的表时，使用E

逃离我推掉我的手/ 2021年10月19日 07:22/ 0 赞/ 394 阅读

相关 SQL---关联子查询（correlated subquery）

`关联子查询`和`普通子查询`的区别在于： 1，`关联子查询`引用了外部查询的列。 2，执行顺序不同。对于普通子查询，先执行普通子查询，再执行外层查询；而对于关联子查询，先

刺骨的言语ヽ痛彻心扉/ 2021年06月26日 16:06/ 0 赞/ 728 阅读

相关 mysql数据库sql优化——子查询优化

1、什么是子查询、表关联查询：子查询：是指在主sql语句中的select或where子句中使用select查询语句；select a.name,(select b.nam

红太狼/ 2021年05月12日 11:51/ 0 赞/ 652 阅读