案例分析 mysql子查询,DEPENDENT SUBQUERY特别小心
发布时间：2022-03-21 11:05:41 所属栏目：MySql教程 来源：互联网
导读：案例分析:开发提了个订正update数据的sql,一开始没注意看,就直接跑了,结果跑了半天,没动静,以为是在等锁,看线程状态是running的,那说明是没堵的,那就奇怪,为什么会跑半天,因为select的结果集很快的,说明索引是没问题,于是中断了update,准备分析下. 先看看u
|
案例分析:开发提了个订正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看起来没什么问题.然后查看了执行计划: 点击(此处)折叠或打开 mysql> explain -> 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' -> )); +----+--------------------+---------------+------------+-----------------+-------------------+---------+---------+------+----------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+---------------+------------+-----------------+-------------------+---------+---------+------+----------+----------+------------------------------+ | 1 | UPDATE | product_model | NULL | index | NULL | PRIMARY | 8 | NULL | 22101522 | 100.00 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | fast_trade | NULL | unique_subquery | PRIMARY,out_index | PRIMARY | 62 | func | 1 | 5.00 | Using where | +----+--------------------+---------------+------------+-----------------+-------------------+---------+---------+------+----------+----------+------------------------------+ 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进行了一次执行计划: 点击(此处)折叠或打开 mysql> explain select * from product_model -> 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' -> )); +----+-------------+---------------+------------+-------+-------------------+-----------+---------+--------------------------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+-------------------+-----------+---------+--------------------------------------+------+----------+--------------------------+ | 1 | SIMPLE | fast_trade | NULL | range | PRIMARY,out_index | out_index | 194 | NULL | 18 | 100.00 | Using where; Using index | | 1 | SIMPLE | product_model | NULL | ref | biz_no | biz_no | 62 | yjf_commonproducts.fast_trade.biz_no | 1 | 100.00 | NULL | +----+-------------+---------------+------------+-------+-------------------+-----------+---------+--------------------------------------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.01 sec) mysql> show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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'))) | + 执行select后,发现mysql自己把sql优化了,优化成join了,难怪速度很快.那为什么update不会优化勒???? 先留个坑吧...有时间在慢慢解释,涉及到尼玛mysql的底层优化结构.反正就是万年巨坑. 既然已经发现了是子查询的问题,那就改sql吧. 最开始我试了下把in 改成exists,结果,呵呵: 点击(此处)折叠或打开 mysql> explain -> update product_model -> set content = replace(content, '"productStatus":"INIT"', '"productStatus":"DEDUCT_HOLD"') -> where exists ( -> select * from fast_trade where product_model.biz_no=fast_trade.biz_no and fast_trade.merchant_order_no in ( -> '000500101ghwpjtdbw00', -> '000500101ghwpzu1tp00', -> '000500101ghwq01plh00', -> '000500101ghwq08t2p00', -> '000500101ghwq1apyt00', -> '000500101ghwq5jkfo00', -> '000500101ghwqqjisd00', -> '000500101ghwrq0erl00', -> '000500201ghngy24r000', -> '000500201ghwphg9r100', -> '000500201ghwpzm1jx00', -> '000500201ghwpzpfe100', -> '000500201ghwpztlup00', -> '000500201ghwpzui1100', -> '000500201ghwq0991p00', -> '000500201ghwr45qh200', -> '000500201ghwr64mxx00', -> '000500201ghwri2nkp00' -> )); +----+--------------------+---------------+------------+--------+-------------------+---------+---------+-----------------------------------------+----------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+---------------+------------+--------+-------------------+---------+---------+-----------------------------------------+----------+----------+------------------------------+ | 1 | UPDATE | product_model | NULL | index | NULL | PRIMARY | 8 | NULL | 22108891 | 100.00 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | fast_trade | NULL | eq_ref | PRIMARY,out_index | PRIMARY | 62 | yjf_commonproducts.product_model.biz_no | 1 | 5.00 | Using where | +----+--------------------+---------------+------------+--------+-------------------+---------+---------+-----------------------------------------+----------+----------+------------------------------+ 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' ); 点击(此处)折叠或打开 mysql> explain -> 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' -> ); +----+-------------+-------+------------+-------+-------------------+-----------+---------+-----------------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-------------------+-----------+---------+-----------------------------+------+----------+--------------------------+ | 1 | SIMPLE | b | NULL | range | PRIMARY,out_index | out_index | 194 | NULL | 18 | 100.00 | Using where; Using index | | 1 | UPDATE | a | NULL | ref | biz_no | biz_no | 62 | yjf_commonproducts.b.biz_no | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+-------------------+-----------+---------+-----------------------------+------+----------+--------------------------+ 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_modelG *************************** 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) （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐