mysql - 为什么这个 MySQL 查询很慢？

Question

我有以下查询，所有相关列都已正确编入索引。 MySQL 版本 5.0.8。查询需要永远:

SELECT COUNT(*) FROM `members` `t` WHERE t.member_type NOT IN (1,2)
AND ( SELECT end_date FROM subscriptions s
WHERE s.sub_auth_id = t.member_auth_id AND s.sub_status = 'Completed'
AND s.sub_pkg_id > 0 ORDER BY s.id DESC LIMIT 1 ) < curdate( )

EXPLAIN 输出:

----+--------------------+-------+-------+-----------------------+---------+---------+------+------+-------------
id  | select_type        | table | type  | possible_keys         | key     | key_len | ref  | rows | Extra
----+--------------------+-------+-------+-----------------------+---------+---------+------+------+-------------
1   | PRIMARY            | t     | ALL   | membership_type       | NULL    | NULL    | NULL | 9610 | Using where
----+--------------------+-------+-------+-----------------------+---------+---------+------+------+-------------
2   | DEPENDENT SUBQUERY | s     | index | subscription_auth_id, | PRIMARY | 4       | NULL |    1 | Using where
    |                    |       |       | subscription_pkg_id,  |         |         |      |      |            
    |                    |       |       | subscription_status   |         |         |      |      |            
----+--------------------+-------+-------+-----------------------+---------+---------+------+------+-------------

为什么？

Answer 1

您的子选择引用父查询中的值。这被称为 correlated (dependent) subquery ，并且这样的查询必须对父查询中的每一行执行一次，这通常会导致性能不佳。将查询重写为 JOIN 通常会更快，例如这样

(注意:没有用于测试的示例架构，无法提前说明这是否会更快并且仍然正确，您可能需要稍微调整一下):

SELECT COUNT(*) FROM members t 
LEFT JOIN (
 SELECT sub_auth_id as member_id, max(id) as sid FROM subscriptions
 WHERE sub_status = 'Completed'
 AND sub_pkg_id > 0
 GROUP BY sub_auth_id
 LEFT JOIN (
  SELECT id AS subid, end_date FROM subscriptions
  WHERE sub_status = 'Completed'
  AND sub_pkg_id > 0
 ) sdate ON sid = subid
) sub ON sub.member_id = t.member_auth_id
WHERE t.member_type NOT IN (1,2)
AND sub.end_date < curdate( )

这里的逻辑是:

1. 对于每个成员，找到他的最新订阅。
2. 对于每个最新的订阅，找到它的结束日期。
3. 将这些 member-latest_sub_date 对加入成员列表。
4. 过滤列表。