千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2026-11-02 作者：千家信息网编辑
千家信息网最后更新 2026年11月02日，案例分析:开发提了个订正update数据的sql,一开始没注意看,就直接跑了,结果跑了半天,没动静,以为是在等锁,看线程状态是running的,那说明是没堵的,那就奇怪,为什么会跑半天,因为selec
千家信息网最后更新 2026年11月02日案例分析:mysql子查询,DEPENDENT SUBQUERY特别小心

案例分析:开发提了个订正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)



很赞哦！
结果 查询 就是 分析 时间 问题 清楚 很快 结构 语句 还是 案例 案例分析 明显 淡定 漫长 艰巨 没什么 一句话 一行 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 互联网科技深圳 互联网 科技 世界互联网领先科技成果 贷财互联网科技 智慧科技互联网 互联网科技ppt模板 互联网科技logo 郑州华宇互联网科技 科技互联网创业计划书 互联网科技股有哪些 盛创互联网科技有限公司 西藏七维空间互联网科技有限公司 剑昆互联网络科技有限公司临桂 欧洲十大科技互联网公司 来此购互联网科技有限公司 智腾互联网科技有限公司 互联网金融科技元年 上海创道互联网络科技有限公司 众联互联网络科技 东风日产互联网科技公司叫什么 A股互联网金融科技公司 互联网科技有限公司经营范围 涨乐互联网科技 互联网的科技创新 互联网未来科技 上游互联网科技 南博望互联网科技有限公司 互联网企业科技类企业如何做账 信承诺互联网科技有限公司 互联网高科技行业有哪些企业

相关文章

0