MYSQL的UPDATE子查询,UPDATE时避免使用子查询
MYSQL的UPDATE子查询,UPDATE时避免使用子查询
刘景刘恒 发表于12个月前
MYSQL的UPDATE子查询,UPDATE时避免使用子查询
  • 发表于 12个月前
  • 阅读 1
  • 收藏 0
  • 点赞 0
  • 评论 0

移动开发云端新模式探索实践 >>>   

大概率

 

MYSQL的UPDATE子查询,UPDATE时避免使用子查询

近日写mysql储存过程的时候,有个SQL执行不动:

UPDATE t_csi_comment
SET is_valid = 0
WHERE
        comment_id IN (
                SELECT
                        comment_id
                FROM
                        (
                                SELECT
                                        *
                                FROM
                                        t_csi_comment
                                WHERE
                                        DATE_FORMAT(comment_time, '%Y%m%d') BETWEEN 20170425
                                AND 20170528
                                ORDER BY
                                        comment_id DESC
                        ) a
                GROUP BY
                        openid,
                        dlr_code
                HAVING
                        count(1) > 2
        );
很奇怪,按道理这条SQL的检索量小于10W,应该怎么慢也不会几分钟不动的地步。

单独执行子查询:

SELECT
        comment_id
FROM
        (
                SELECT
                        *
                FROM
                        t_csi_comment
                WHERE
                        DATE_FORMAT(comment_time, '%Y%m%d') BETWEEN 20170425
                AND 20170528
                ORDER BY
                        comment_id DESC
        ) a
GROUP BY
        openid,
        dlr_code
HAVING
        count(1) > 2;
结果比想象中的快,1秒都不到,EXPLAIN后检索量不到4W行。我就郁闷了。

EXPLANIN第一条update语句:



注意:select_type 里出现了 DEPENDENT SUBQUERY。

这意味着什么?——子查询取决于外面的查询,Mysql 先执行外查询,内查询根据这个查询结果(如执行计划里所述,38196 rows)的每一条记录组成新的查询语句后执行。多重子查询情况下,我已经不想去解析它是如何转换SQL了。 Mysql在这点上并不比人类聪明。

解决办法(子查询转换成联表查询):

UPDATE t_csi_comment a INNER JOIN
 (
        SELECT
                comment_id
        FROM
                (
                        SELECT
                                *
                        FROM
                                t_csi_comment
                        WHERE
                                DATE_FORMAT(comment_time, '%Y%m%d') BETWEEN 20170425
                        AND 20170528
                        ORDER BY
                                comment_id DESC
                ) a
        GROUP BY
                openid,
                dlr_code
        HAVING
                count(1) > 2
) b ON a.comment_id = b.comment_id;
SET a.is_valid = 0
        


毫秒级别完工。

按理说,越复杂的程序逻辑关系要越明朗,出现复杂SQL的几率要越低。但是总会有一块业务相对复杂多变,无法把控,或者就是整个系统的架构不够明朗,脱离不了复杂SQL。这是在UPDATE时发现的子查询问题,在其它SQL语句中肯定也会有所体现,这是Mysql的查询机制问题,子查询会让Mysql变笨。所以还是慎用子查询,各种复杂SQL下尽量先测试吧。
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。互联网+时代,时刻要保持学习,携手千锋PHP,Dream It Possible。

标签: php培训
  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 1
博文 172
码字总数 139067
×
刘景刘恒
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: