SQL优化案例：相关子查询优化

快来打我* 2024-02-18 22:10 81阅读 0赞

原始语句：

1.  `SELECT`
2.  `t1.*`
3.  `FROM`
4.  `t_payment_bank_account_info t1`
5.  `WHERE`
6.  `EXISTS (`
7.  `SELECT`
8.  `1`
9.  `FROM`
10. `t_payment_account_dtl t2`
11. `WHERE`
12. `t1.account_no = t2.account_no`
13. `AND t2.parent_account_no = '7311810182600115231'`
14. `AND t2.txn_Date >= '2015-12-23'`
15. `AND t2.account_no != t2.opp_acc_no`
16. `);`

执行计划

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

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

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

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

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

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

执行计划：

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

语句在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字段的筛选度：

1.  `mysql> select count(*) from t_payment_account_dtl;`
2.  `+----------+`
3.  `| count(*) |`
4.  `+----------+`
5.  `| 7594 |`
6.  `+----------+`
7.  `1 row in set (0.04 sec)`
8.  
9.  `mysql> select count(distinct(account_No)) from t_payment_account_dtl;`
10. `+-----------------------------+`
11. `| count(distinct(account_No)) |`
12. `+-----------------------------+`
13. `| 75 |`
14. `+-----------------------------+`
15. `1 row in set (0.00 sec)`
16. 
17. `mysql> select count(distinct(account_No)) from t_payment_bank_account_info\G`
18. `*************************** 1. row ***************************`
19. `count(distinct(account_No)): 4753`
20. `1 row in set (0.00 sec)`
21. 
22. `mysql> select count(*) from t_payment_bank_account_info\G`
23. `*************************** 1. row ***************************`
24. `count(*): 4789`
25. `1 row in set (0.01 sec)`

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

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

在看执行计划

1.  `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 需要查询的字段;`
2.  `+----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+`
3.  `| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |`
4.  `+----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+`
5.  `| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 7924 | Using where; Using temporary; Using filesort |`
6.  `| 1 | SIMPLE | t1 | ref | idx_account_no | idx_account_no | 99 | dcf_payment.t2.account_No | 22 | Using where |`
7.  `+----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+`
8.  `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做内部表性能可能会更好。

我们尝试一下

1.  `mysql> alter table t_payment_account_dtl add index idx_account_no(account_no);`
2.  
3.  `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 需要查询的字段;`
4.  `+----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+`
5.  `| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |`
6.  `+----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+`
7.  `| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 7924 | Using where; Using temporary; Using filesort |`
8.  `| 1 | SIMPLE | t1 | ref | idx_account_no | idx_account_no | 99 | dcf_payment.t2.account_No | 22 | Using where |`
9.  `+----+-------------+-------+------+----------------+----------------+---------+---------------------------+------+----------------------------------------------+`
10. `2 rows in set (0.00 sec)`

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

发表评论取消回复

表情：

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

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

相关阅读

相关 Oracle SQL查询优化失败案例

在Oracle SQL查询优化中，可能会遇到各种问题导致优化失败。以下列举几个常见的案例： 1. **数据量过大**：如果表中有大量的数据，直接全表扫描，优化效率会非常低。

向右看齐/ 2025年01月11日 03:42/ 0 赞/ 4 阅读

相关 Oracle SQL查询优化失败案例

在Oracle SQL查询优化过程中，可能会遇到各种问题导致优化失败。以下是一些常见的案例： 1. **复杂查询**：如果查询涉及大量连接、子查询和聚合函数，优化器可能难以找

野性酷女/ 2024年12月25日 22:03/ 0 赞/ 36 阅读

相关 Oracle SQL查询优化案例分析

Oracle SQL的查询优化是一个复杂的过程，涉及诸多因素。以下会通过一个具体的案例进行详细分析。案例：假设有如下两个表： Table1 (ColumnA, Colum

深碍√TFBOYSˉ_/ 2024年12月20日 12:51/ 0 赞/ 38 阅读

相关 sql优化相关

**MySQL的查询过程：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLm...

喜欢ヅ旅行/ 2024年04月18日 03:14/ 0 赞/ 72 阅读

相关 SQL优化案例：相关子查询优化

原始语句： 1. `SELECT` 2. `t1.` 3. `FROM` 4. `t_payment_bank_account_info t1` 5. `WHERE

快来打我*/ 2024年02月18日 22:10/ 0 赞/ 82 阅读

相关一文终结SQL 子查询优化

![48a67bcf1b7eb2892040fd93caa752cf.png][] 来源：https://ericfu.me/subquery-optimization 子

不念不忘少年蓝@/ 2022年10月23日 09:59/ 0 赞/ 187 阅读

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

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

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

相关 sql语句where in子查询优化

好好理解好好理解转载请注明出处。作者：wuxiwei 出处：[http://www.cnblogs.com/wxw16/p/6105624.html][http

比眉伴天荒/ 2022年05月23日 03:53/ 0 赞/ 439 阅读

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

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

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

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

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

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