千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2024-12-12 作者：千家信息网编辑
千家信息网最后更新 2024年12月12日，案例分析:开发提了个订正update数据的sql,一开始没注意看,就直接跑了,结果跑了半天,没动静,以为是在等锁,看线程状态是running的,那说明是没堵的,那就奇怪,为什么会跑半天,因为selec
千家信息网最后更新 2024年12月12日案例分析: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安全错误 数据库的锁怎样保障安全 上海房价数据库 hp175x打印服务器 数据库开发原理与应用 数据库查询和数据操纵实验 网络安全宣传片10月13日 网络安全与保密第一版 数据库数据模式的主键和外键 信息与网络技术算不算计算机专业 中专与大专计算机网络技术 青春与网络安全的故事 网络安全属于什么工程师 软件开发公司选址原因 游戏软件开发公司面试题 网络安全为人民谁提出来的 微信添加好友服务器繁忙什么意思 局域网服务器 c类 网络安全日教案格式 公安网络安全管理部署会议 数据库模型的建立过程 南京数据库卷尺定制技术指导 易语言模拟服务器协议 又多网络技术有限公司 赛纳打印科技有限公司软件开发 佛山信息软件开发咨询 软件开发工程师c 招聘 服务器常见的安全设施 北京工行软件开发部怎样 mlb美职棒数据库 四川安防子母钟服务器云主机 从事安卓系统开发软件开发

相关文章

0