|
执行计划：
- +----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
- | 1 | PRIMARY | o | index | | PRIMARY | 8 | | 24 | Using where; Using temporary |
- | 2 | DEPENDENT SUBQUERY | | | | | | | | Impossible WHERE noticed after reading const tables |
- | 3 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |
- +----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
重写为 JOIN 之后，子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED，执行速度大大加快，从7秒降低到2毫秒。
- UPDATE operation o
- JOIN (SELECT o.id,
- o.status
- FROM operation o
- WHERE o.group = 123
- AND o.status NOT IN ( 'done' )
- ORDER BY o.parent,
- o.id
- LIMIT 1) t
- ON o.id = t.id
- SET status = 'applying'
（编辑：衡阳站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
