行业资讯

案例分析:mysql子查询,DEPENDENT SUBQUERY特别小心

发布时间：2020-08-19 10:35:32 作者：yangjustins
来源：ITPUB博客 阅读：727


案例分析:开发提了个订正update数据的sql,一开始没注意看,就直接跑了,结果跑了半天,没动静,以为是在等锁,看线程状态是running的,那说明是没堵的,那就奇怪,为什么会跑半天,因为select的结果集很快的,说明索引是没问题,于是中断了update,准备分析下.
先看看update的语句:
update product_model
set content = replace(content, '"productStatus":"INIT"', '"productStatus":"DEDUCT_HOLD"')
where biz_no in (
select biz_no from fast_trade where merchant_order_no in (
'000500101ghwpjtdbw00',
'000500101ghwpzu1tp00',
'000500101ghwq01plh00',
'000500101ghwq08t2p00',
'000500101ghwq1apyt00',
'000500101ghwq5jkfo00',
'000500101ghwqqjisd00',
'000500101ghwrq0erl00',
'000500201ghngy24r000',
'000500201ghwphg9r100',
'000500201ghwpzm1jx00',
'000500201ghwpzpfe100',
'000500201ghwpztlup00',
'000500201ghwpzui1100',
'000500201ghwq0991p00',
'000500201ghwr45qh200',
'000500201ghwr64mxx00',
'000500201ghwri2nkp00'
));


分析update语句:替换一个字段的值,用了子查询关联另外一张表.这个sql看起来没什么问题.然后查看了执行计划:


点击(此处)折叠或打开

  1. mysql> explain
  2.     -> update product_model
  3.     -> set content = replace(content, '"productStatus":"INIT"', '"productStatus":"DEDUCT_HOLD"')
  4.     -> where biz_no in (
  5.     -> select biz_no from fast_trade where merchant_order_no in (
  6.     -> '000500101ghwpjtdbw00',
  7.     -> '000500101ghwpzu1tp00',
  8.     -> '000500101ghwq01plh00',
  9.     -> '000500101ghwq08t2p00',
  10.     -> '000500101ghwq1apyt00',
  11.     -> '000500101ghwq5jkfo00',
  12.     -> '000500101ghwqqjisd00',
  13.     -> '000500101ghwrq0erl00',
  14.     -> '000500201ghngy24r000',
  15.     -> '000500201ghwphg9r100',
  16.     -> '000500201ghwpzm1jx00',
  17.     -> '000500201ghwpzpfe100',
  18.     -> '000500201ghwpztlup00',
  19.     -> '000500201ghwpzui1100',
  20.     -> '000500201ghwq0991p00',
  21.     -> '000500201ghwr45qh200',
  22.     -> '000500201ghwr64mxx00',
  23.     -> '000500201ghwri2nkp00'
  24.     -> ));
  25. +----+--------------------+---------------+------------+-----------------+-------------------+---------+---------+------+----------+----------+------------------------------+
  26. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  27. +----+--------------------+---------------+------------+-----------------+-------------------+---------+---------+------+----------+----------+------------------------------+
  28. | 1 | UPDATE | product_model | NULL | index | NULL | PRIMARY | 8 | NULL | 22101522 | 100.00 | Using where; Using temporary |
  29. | 2 | DEPENDENT SUBQUERY | fast_trade | NULL | unique_subquery | PRIMARY,out_index | PRIMARY | 62 | func | 1 | 5.00 | Using where |
  30. +----+--------------------+---------------+------------+-----------------+-------------------+---------+---------+------+----------+----------+------------------------------+
  31. 2 rows in set (0.00 sec)


结果吓死哥了,看到DEPENDENT SUBQUERY 任何人都淡定不了了吧...在看到22101522,哥差点吓出翔...
DEPENDENT SUBQUERY  可能有的人不是很清楚,稍微科普下.转述官方说法:子查询中的第一个SELECT,取决于外面的查询结果.换成人话就是说:子查询的查询方式依赖于外面的查询结果.用这个例子就是,先select * from product_model,得到一个结果集,本例就是22101522行.然后这个结果的每一行在跟fast_trade进行匹配,也就是说.product_model的2千多万行都与fast_trade的18行进行一次联合查询.一句话说清楚就是要执行2千多万次select匹配操作.
吓出翔了吧... 实在是没搞懂mysql的update是怎么优化的.
为了进一步求证,我把update改成了select进行了一次执行计划:


点击(此处)折叠或打开

  1. mysql> explain select * from product_model
  2.     -> where biz_no in (
  3.     -> select biz_no from fast_trade where merchant_order_no in (
  4.     -> '000500101ghwpjtdbw00',
  5.     -> '000500101ghwpzu1tp00',
  6.     -> '000500101ghwq01plh00',
  7.     -> '000500101ghwq08t2p00',
  8.     -> '000500101ghwq1apyt00',
  9.     -> '000500101ghwq5jkfo00',
  10.     -> '000500101ghwqqjisd00',
  11.     -> '000500101ghwrq0erl00',
  12.     -> '000500201ghngy24r000',
  13.     -> '000500201ghwphg9r100',
  14.     -> '000500201ghwpzm1jx00',
  15.     -> '000500201ghwpzpfe100',
  16.     -> '000500201ghwpztlup00',
  17.     -> '000500201ghwpzui1100',
  18.     -> '000500201ghwq0991p00',
  19.     -> '000500201ghwr45qh200',
  20.     -> '000500201ghwr64mxx00',
  21.     -> '000500201ghwri2nkp00'
  22.     -> ));
  23. +----+-------------+---------------+------------+-------+-------------------+-----------+---------+--------------------------------------+------+----------+--------------------------+
  24. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  25. +----+-------------+---------------+------------+-------+-------------------+-----------+---------+--------------------------------------+------+----------+--------------------------+
  26. | 1 | SIMPLE | fast_trade | NULL | range | PRIMARY,out_index | out_index | 194 | NULL | 18 | 100.00 | Using where; Using index |
  27. | 1 | SIMPLE | product_model | NULL | ref | biz_no | biz_no | 62 | yjf_commonproducts.fast_trade.biz_no | 1 | 100.00 | NULL |
  28. +----+-------------+---------------+------------+-------+-------------------+-----------+---------+--------------------------------------+------+----------+--------------------------+
  29. 2 rows in set, 1 warning (0.01 sec)


  32. mysql> show warnings;
  33. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
  34. | Level | Code | Message |
  35. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  36. | Note | 1003 | /* select#1 */ select `yjf_commonproducts`.`product_model`.`id` AS `id`,`yjf_commonproducts`.`product_model`.`raw_update_time` AS `raw_update_time`,`yjf_commonproducts`.`product_model`.`raw_add_time` AS `raw_add_time`,`yjf_commonproducts`.`product_model`.`biz_no` AS `biz_no`,`yjf_commonproducts`.`product_model`.`content` AS `content` from `yjf_commonproducts`.`fast_trade` join `yjf_commonproducts`.`product_model` where ((`yjf_commonproducts`.`product_model`.`biz_no` = `yjf_commonproducts`.`fast_trade`.`biz_no`) and (`yjf_commonproducts`.`fast_trade`.`merchant_order_no` in ('000500101ghwpjtdbw00','000500101ghwpzu1tp00','000500101ghwq01plh00','000500101ghwq08t2p00','000500101ghwq1apyt00','000500101ghwq5jkfo00','000500101ghwqqjisd00','000500101ghwrq0erl00','000500201ghngy24r000','000500201ghwphg9r100','000500201ghwpzm1jx00','000500201ghwpzpfe100','000500201ghwpztlup00','000500201ghwpzui1100','000500201ghwq0991p00','000500201ghwr45qh200','000500201ghwr64mxx00','000500201ghwri2nkp00'))) |
  37. +




艹.执行select后,发现mysql自己把sql优化了,优化成join了,难怪速度很快.那为什么update不会优化勒???? 先留个坑吧...有时间在慢慢解释,涉及到尼玛mysql的底层优化结构.反正就是万年巨坑.


既然已经发现了是子查询的问题,那就改sql吧.
最开始我试了下把in 改成exists,结果,呵呵:


点击(此处)折叠或打开

  1. mysql> explain
  2.     -> update product_model
  3.     -> set content = replace(content, '"productStatus":"INIT"', '"productStatus":"DEDUCT_HOLD"')
  4.     -> where exists (
  5.     -> select * from fast_trade where product_model.biz_no=fast_trade.biz_no and fast_trade.merchant_order_no in (
  6.     -> '000500101ghwpjtdbw00',
  7.     -> '000500101ghwpzu1tp00',
  8.     -> '000500101ghwq01plh00',
  9.     -> '000500101ghwq08t2p00',
  10.     -> '000500101ghwq1apyt00',
  11.     -> '000500101ghwq5jkfo00',
  12.     -> '000500101ghwqqjisd00',
  13.     -> '000500101ghwrq0erl00',
  14.     -> '000500201ghngy24r000',
  15.     -> '000500201ghwphg9r100',
  16.     -> '000500201ghwpzm1jx00',
  17.     -> '000500201ghwpzpfe100',
  18.     -> '000500201ghwpztlup00',
  19.     -> '000500201ghwpzui1100',
  20.     -> '000500201ghwq0991p00',
  21.     -> '000500201ghwr45qh200',
  22.     -> '000500201ghwr64mxx00',
  23.     -> '000500201ghwri2nkp00'
  24.     -> ));
  25. +----+--------------------+---------------+------------+--------+-------------------+---------+---------+-----------------------------------------+----------+----------+------------------------------+
  26. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  27. +----+--------------------+---------------+------------+--------+-------------------+---------+---------+-----------------------------------------+----------+----------+------------------------------+
  28. | 1 | UPDATE | product_model | NULL | index | NULL | PRIMARY | 8 | NULL | 22108891 | 100.00 | Using where; Using temporary |
  29. | 2 | DEPENDENT SUBQUERY | fast_trade | NULL | eq_ref | PRIMARY,out_index | PRIMARY | 62 | yjf_commonproducts.product_model.biz_no | 1 | 5.00 | Using where |
  30. +----+--------------------+---------------+------------+--------+-------------------+---------+---------+-----------------------------------------+----------+----------+------------------------------+
  31. 2 rows in set, 1 warning (0.00 sec)


update对于类似的子查询,全完没有优化,所以还是老老实实改成join吧...




update product_model a,fast_trade b
set a.content = replace(content, '"productStatus":"INIT"', '"productStatus":"DEDUCT_HOLD"')
where a.biz_no =b.biz_no and  b.merchant_order_no in (
'000500101ghwpjtdbw00',
'000500101ghwpzu1tp00',
'000500101ghwq01plh00',
'000500101ghwq08t2p00',
'000500101ghwq1apyt00',
'000500101ghwq5jkfo00',
'000500101ghwqqjisd00',
'000500101ghwrq0erl00',
'000500201ghngy24r000',
'000500201ghwphg9r100',
'000500201ghwpzm1jx00',
'000500201ghwpzpfe100',
'000500201ghwpztlup00',
'000500201ghwpzui1100',
'000500201ghwq0991p00',
'000500201ghwr45qh200',
'000500201ghwr64mxx00',
'000500201ghwri2nkp00'
);






点击(此处)折叠或打开

  1. mysql> explain
  2.     -> update product_model a,fast_trade b
  3.     -> set a.content = replace(content, '"productStatus":"INIT"', '"productStatus":"DEDUCT_HOLD"')
  4.     -> where a.biz_no =b.biz_no and b.merchant_order_no in (
  5.     -> '000500101ghwpjtdbw00',
  6.     -> '000500101ghwpzu1tp00',
  7.     -> '000500101ghwq01plh00',
  8.     -> '000500101ghwq08t2p00',
  9.     -> '000500101ghwq1apyt00',
  10.     -> '000500101ghwq5jkfo00',
  11.     -> '000500101ghwqqjisd00',
  12.     -> '000500101ghwrq0erl00',
  13.     -> '000500201ghngy24r000',
  14.     -> '000500201ghwphg9r100',
  15.     -> '000500201ghwpzm1jx00',
  16.     -> '000500201ghwpzpfe100',
  17.     -> '000500201ghwpztlup00',
  18.     -> '000500201ghwpzui1100',
  19.     -> '000500201ghwq0991p00',
  20.     -> '000500201ghwr45qh200',
  21.     -> '000500201ghwr64mxx00',
  22.     -> '000500201ghwri2nkp00'
  23.     -> );
  24. +----+-------------+-------+------------+-------+-------------------+-----------+---------+-----------------------------+------+----------+--------------------------+
  25. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  26. +----+-------------+-------+------------+-------+-------------------+-----------+---------+-----------------------------+------+----------+--------------------------+
  27. | 1 | SIMPLE | b | NULL | range | PRIMARY,out_index | out_index | 194 | NULL | 18 | 100.00 | Using where; Using index |
  28. | 1 | UPDATE | a | NULL | ref | biz_no | biz_no | 62 | yjf_commonproducts.b.biz_no | 1 | 100.00 | NULL |
  29. +----+-------------+-------+------------+-------+-------------------+-----------+---------+-----------------------------+------+----------+--------------------------+
  30. 2 rows in set (0.01 sec)



结果就明显了吧,就不多说这个结果了.


总结一下:
    mysql的子查询一直都是坑,虽然5.7优化了一些,但还是缺陷很多,尽量少用子查询吧;
    另外,在执行sql前,都尽量的explain一下吧,看看结果集是否可接受.在结果集看到SUBQUERY , DEPENDENT SUBQUERY ,或者Using temporary,Using join buffer类似的,赶紧优化,该加索引的加,该改sql的改.关于explain的结果集,这里只是举例说明,优化是个漫长而艰巨的过程!


















最后附上表结构相关信息,以供参考:
mysql> show create table product_model\G
*************************** 1. row ***************************
       Table: product_model
Create Table: CREATE TABLE `product_model` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `raw_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `raw_add_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  `biz_no` varchar(20) NOT NULL COMMENT '业务流水号',
  `content` mediumtext NOT NULL COMMENT '产品模型内容',
  PRIMARY KEY (`id`),
  KEY `biz_no` (`biz_no`)
) ENGINE=InnoDB AUTO_INCREMENT=26469741 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)


mysql> show index from product_model;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| product_model |          0 | PRIMARY  |            1 | id          | A         |    20473816 |     NULL | NULL   |      | BTREE      |         |               |
| product_model |          1 | biz_no   |            1 | biz_no      | A         |    22101400 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)


mysql> show table status like 'product_model'\G
*************************** 1. row ***************************
           Name: product_model
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 22101455
 Avg_row_length: 4235
    Data_length: 93609525248
Max_data_length: 0
   Index_length: 1033895936
      Data_free: 7340032
 Auto_increment: 26469802
    Create_time: 2016-09-23 18:06:37
    Update_time: 2016-12-07 15:09:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)



推荐阅读：
  1. 终极版Python学习教程：一篇文章讲清楚Python虚拟环境
  2. 2019最新Python学习路线图：如何用Python创建微信机器人

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

dependent subquery mysql

上一篇：学习58同城的SEO高级思维：URL规划与内容建设

下一篇：vue-model实现简易计算器的方法

相关阅读

您好，登录后才能下订单哦！

密码登录
登录注册
获取短信验证码
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》
产品服务
地区划分
帮助支持
关于我们
行业资讯-文章归档 问答-问答归档
广州亿速云计算有限公司

7*24小时在线电话：400-100-2938

7*24小时在线QQ：800811969

Copyright © Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有
粤ICP备17096448号-1 粤公网安备 44010402001142号