Case study: MySQL subquery, DEPENDENT SUBQUERY is very careful 04/27 Update SLTechnology News&Howtos

Case study: MySQL subquery, DEPENDENT SUBQUERY is very careful

2025-04-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Case study: development proposed a revised update data sql, at the beginning did not pay attention to look, directly ran, the result ran for a long time, no movement, thought to be waiting for the lock, to see the thread state is running, that shows that there is no blocking, that is strange, because the select result set is fast, indicating that the index is no problem, so interrupted the update, ready to analyze.

First, take a look at the statement of 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'

))

Analyze the update statement: replace the value of one field and associate another table with a subquery. This sql seems to be all right. Then look at the execution plan:

Click (here) to collapse or open

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'

->)

+-+- +-+

+-+- +-+

+-+- +-+

2 rows in set (0.00 sec)

As a result, I was scared to death. No one can calm down when I see DEPENDENT SUBQUERY. At the sight of 22101522, I almost freaked out.

DEPENDENT SUBQUERY some people may not be very clear, a little popular science. Paraphrase the official saying: the first SELECT in a subquery depends on the result of the external query. In human terms, it means that the query mode of the subquery depends on the external query results. Using this example is to select * from product_model first and get a result set, which in this case is 22101522 rows. Then each row of the result matches fast_trade, that is, more than 20 million rows of product _ model do a federated query with 18 rows of fast_trade. In a word, it is necessary to perform more than 20 million select matching operations.

You're scared out of your wits. I really don't understand how mysql's update is optimized.

To further verify, I changed update to select and carried out an implementation plan:

Click (here) to collapse or open

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'

->)

+-+- -+

+-+- -+

| | 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.01sec)

Mysql > show warnings

+- -- +

+- - - - - - - - -- +

| | Note | 1003 | / * select#1 * / select `yjf_ commonproducts`.`product _ model`.`id`product `id`, `yjf_ commonproducts`.`product _ model`.`raw _ update_ time`AS `raw_update_ time`, `yjf_ commonproducts`.`product _ model`.`raw _ add_ `timeproducts`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)

Damn it. After implementing select, I found that mysql itself optimized sql into join, no wonder the speed is very fast. Then why won't update optimize it? Save a hole for now. There is time to explain slowly, involving the underlying optimization structure of Nima mysql. Anyway, it is a huge pit for ten thousand years.

Now that you've found the problem with the subquery, change it to sql.

At first, I tried to change in to exists, and as a result, hehe:

Click (here) to collapse or open

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'

->)

+-+- -- +

+-+- -- +

+-+- -- +

2 rows in set, 1 warning (0.00 sec)

Update has no optimization for similar subqueries, so let's honestly change it to 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'

);

Click (here) to collapse or open

Mysql > explain

-> update product_model a minute fastening 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 | 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.01sec)

The result is obvious, so there is no need to say much about it.

To sum up:

The subquery of mysql has always been a pit. Although 5.7has been optimized, there are still many defects. Try to use fewer subqueries.

In addition, before executing sql, try to explain to see if the result set is acceptable. If you see SUBQUERY, DEPENDENT SUBQUERY, or Using temporary,Using join buffer in the result set, optimize quickly, add the index and change the sql. With regard to the result set of explain, here is just an example to illustrate that optimization is a long and arduous process!

Finally, information about the structure of the table is attached for reference:

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 'modification time'

`raw_add_ time`timestamp NOT NULL DEFAULT '0000-00-0000: 00 COMMENT' creation time'

`biz_ No`varchar (20) NOT NULL COMMENT 'business serial number'

`content`mediumtext NOT NULL COMMENT 'product model content'

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

+ -- +

+ -- +

| | product_model | 0 | PRIMARY | 1 | id | A | 20473816 | 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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.