案例分析:开发提了个订正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_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)