千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2025-02-01 作者：千家信息网编辑
千家信息网最后更新 2025年02月01日，案例分析:开发提了个订正update数据的sql,一开始没注意看,就直接跑了,结果跑了半天,没动静,以为是在等锁,看线程状态是running的,那说明是没堵的,那就奇怪,为什么会跑半天,因为selec
千家信息网最后更新 2025年02月01日案例分析: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安全错误 数据库的锁怎样保障安全 数据库扫描工具 日文 数据库 乱码 火山软件开发破解 教你如何保护你的家庭网络安全 杭州顽途网络技术 郑州美橙科技云服务器 河南软件开发者网站有哪些 我的世界免登录服务器 哈尔滨网络技术有限公司 处理大量数据库用什么笔记本 公安部有关网络安全 dell服务器二手怎么样 ai人工智能软件开发 青岛定制软件开发外包公司 宝鸡微沸网络技术有限公司 软件开发的要求 没完没了下载软件开发 嵌入式软件开发的技术要求 广东荔支网络技术公司 荷叶山服务器 游戏服务器错误码是什么 有关网络安全教育的手抄报 软件开发建设网站 深圳市佑辰网络技术有限公司 火鸟数据库导出excel 网络安全建设会议纪要 关于中国全文期刊数据库的检索 刺激战场国际服用什么服务器 数据库语句计算两个日期差 俄罗斯网络安全投入

相关文章

0