• 最近热搜工具：

    当前位置：首页 > mysql数据库

    ADADADADAD

    案例分析:mysql子查询,DEPENDENT SUBQUERY特别小心[ mysql数据库 ]

    mysql数据库 时间：2024-12-03 10:31:08

    作者：文/会员上传

    如何提高mysql查询效率

    简介：

    案例分析:开发提了个订正update数据的sql,一开始没注意看,就直接跑了,结果跑了半天,没动静,以为是在等锁,看线程状态是running的,那说明是没堵的,那就奇怪,为什么会跑半天

    以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。



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



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

    将本文的Word文档下载到电脑

    推荐度：

    下载
    热门标签: DEPENDENTsubquerymysql

    精品

    申请https需要什么条件

    热门推荐

    hadoop分布式数据库怎样管理

    2024-12-03

    ajax和数据库交互的方法是什么

    2024-12-03

    hadoop archive适合哪些应用场景

    2024-12-06

    hbase metadata能否备份恢复

    2024-12-25

    如何使用Kafka构建可扩展的日志收集系统

    2024-12-03

    大家都在看

    测速网 inhv.cn 皖ICP备2023010105号 E-mail：251442993@qq.com 网速测试 网速测试 五险一金计算器 大小写转换 房贷计算器 个税计算器 网站建设

    本站内容来源于网友提交及搜索引擎，如果我们的某些资料侵犯了您的合法权益或对您造成了任何程度的伤害，请及时联系我们，我们将在收到通知后第一时间处理该内容。