案例分析: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看起来没什么问题.然后查看了执行计划:




结果吓死哥了,看到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进行了一次执行计划:






艹.执行select后,发现mysql自己把sql优化了,优化成join了,难怪速度很快.那为什么update不会优化勒???? 先留个坑吧...有时间在慢慢解释,涉及到尼玛mysql的底层优化结构.反正就是万年巨坑.


既然已经发现了是子查询的问题,那就改sql吧.
最开始我试了下把in 改成exists,结果,呵呵:




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的子查询一直都是坑,虽然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)