database - 优化 mySQL 中的嵌入式 SELECT 查询

Question

好的，这是我现在正在一个有 45,000 条记录且大小为 65MB 的表上运行的查询...并且即将变得越来越大(所以我必须在这里考虑 future 的性能):

SELECT count(payment_id) as signup_count, sum(amount) as signup_amount
FROM payments p
WHERE tm_completed BETWEEN '2009-05-01' AND '2009-05-30'
AND completed > 0
AND tm_completed IS NOT NULL
AND member_id NOT IN (SELECT p2.member_id FROM payments p2 WHERE p2.completed=1 AND p2.tm_completed < '2009-05-01' AND p2.tm_completed IS NOT NULL GROUP BY p2.member_id)

正如您可能会或可能不会想象的那样 - 它会使 mysql 服务器陷入停顿...

它的作用是 - 它只是拉取注册的新用户数量，至少有一次“完成”付款，tm_completed 不为空(因为它只填充已完成的付款)，以及(嵌入式选择)该成员(member)之前从未有过“已完成”付款 - 这意味着他是新成员(member)(只是因为系统会重新开票等等，这是区分刚刚重新开票的现有成员(member)和新成员(member)的唯一方法第一次被计费)。

现在，是否有任何可能的方法来优化此查询以使用更少的资源或其他东西，并停止让我的 mysql 资源崩溃...？

我是否遗漏了任何信息来进一步澄清这一点？让我知道...

编辑:

这是该表上已有的索引:

PRIMARY PRIMARY 46757 payment_id

member_id INDEX 23378 member_id

payer_id INDEX 11689 payer_id

coupon_id INDEX 1 coupon_id

tm_added INDEX 46757 tm_added, product_id

tm_completed INDEX 46757 tm_completed, product_id

Answer 1

那些类型的 IN 子查询在 MySQL 中有点慢。我会这样改写:

SELECT COUNT(1) AS signup_count, SUM(amount) AS signup_amount
FROM   payments p
WHERE  tm_completed BETWEEN '2009-05-01' AND '2009-05-30'
AND    completed > 0
AND    NOT EXISTS (
           SELECT member_id
           FROM   payments
           WHERE  member_id = p.member_id
           AND    completed = 1
           AND    tm_completed < '2009-05-01');

检查“tm_completed IS NOT NULL”不是必需的，因为您的BETWEEN 条件暗示了这一点。

还要确保你有一个索引:

(tm_completed, completed)