千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，案例分析:开发提了个订正update数据的sql,一开始没注意看,就直接跑了,结果跑了半天,没动静,以为是在等锁,看线程状态是running的,那说明是没堵的,那就奇怪,为什么会跑半天,因为selec
千家信息网最后更新 2025年11月07日案例分析: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安全错误 数据库的锁怎样保障安全 服务器的安全日志 长沙网络安全产业园工程 网络技术专家面试问题 在html中从数据库获取值 数据库监听在哪放着 中文数据库如何查找主题词 山东女子学院网络安全专业 北京大学网络安全活动 周口网络技术产品介绍 网络安全霍兰德代码 中国名家数据库网站 数据库三级模式概念逻辑物理 易查询 无数据库 全局数据库名填什么 全国党员信息管理系统服务器 河南新一代网络技术咨询报价 医疗信息化软件开发业务流程 华为网络安全大赛 草料二维码后台数据库修改 互联网生物科技骗局 was 数据库 戴尔服务器硬盘技术参数 平安产险网络安全宣传 高软件开发出路在哪里 服务器电源系统 真实魔兽怀旧服服务器 成都市樟琰栋软件开发工作室 永劫无间开个服务器多少钱 廊坊固安cmmi软件开发 基本核心的系统软件开发

相关文章

0