mysql - 使用 Case 查询更有效的分组依据

Question

我有以下查询构建一个记录集，该记录集在饼图中用作报告。

它不是经常运行，但是当它运行时需要几秒钟，我想知道是否有任何方法可以提高它的效率。

SELECT
  CASE
    WHEN (lastStatus IS NULL)     THEN 'Unused'
    WHEN (attempts > 3 AND callbackAfter IS NULL)   THEN 'Max Attempts Reached'
    WHEN (callbackAfter IS NOT NULL AND callbackAfter >  DATE_ADD(NOW(), INTERVAL 7 DAY)) THEN 'Call Back After 7 Days'
    WHEN (callbackAfter IS NOT NULL AND callbackAfter <= DATE_ADD(NOW(), INTERVAL 7 DAY)) THEN 'Call Back Within 7 Days'
    WHEN (archived = 0)     THEN 'Call Back Within 7 Days'
    ELSE 'Spoke To'
  END AS statusSummary,
  COUNT(leadId) AS total
FROM
  CO_Lead
WHERE
  groupId = 123
  AND
  deleted = 0
GROUP BY
  statusSummary
ORDER BY
  total DESC;

我有一个 (groupId, deleted) 的索引，但我不确定将任何其他字段添加到索引中是否有帮助(如果可以，我如何决定哪个应该先走？callbackAfter 因为它用得最多？)

该表大约有 500,000 行(但从现在起一年内会增加 10 倍。)

我唯一能想到的另一件事是将它分成 6 个查询(将 WHEN 子句移到 WHERE 中)，但这会使它花费 3 倍的时间。

编辑:

这是表定义

CREATE TABLE CO_Lead (
  objectId                             int UNSIGNED       NOT NULL AUTO_INCREMENT,
  groupId                              int UNSIGNED       NOT NULL,
  numberToCall                         varchar(20)        NOT NULL,
  firstName                            varchar(100)       NOT NULL,
  lastName                             varchar(100)       NOT NULL,
  attempts                             tinyint            NOT NULL default 0,
  callbackAfter                        datetime           NULL,
  lastStatus                           varchar(30)        NULL,
  createdDate                          datetime           NOT NULL,
  archived                             bool               NOT NULL default 0,
  deleted                              bool               NOT NULL default 0,
  PRIMARY KEY (
    objectId
  )
) ENGINE = InnoDB;
ALTER TABLE CO_Lead ADD CONSTRAINT UQIX_CO_Lead UNIQUE INDEX (
  objectId
);
ALTER TABLE CO_Lead ADD INDEX (
  groupId,
  archived,
  deleted,
  callbackAfter,
  attempts
);
ALTER TABLE CO_Lead ADD INDEX (
  groupId,
  deleted,
  createdDate,
  lastStatus
);
ALTER TABLE CO_Lead ADD INDEX (
  firstName
);
ALTER TABLE CO_Lead ADD INDEX (
  lastName
);
ALTER TABLE CO_Lead ADD INDEX (
  lastStatus
);
ALTER TABLE CO_Lead ADD INDEX (
  createdDate
);

Answer 1

注意事项:

1. 如果 leadId 不能为 NULL，则将 COUNT(leadId) 更改为 COUNT(*)。它们在逻辑上是等价的，但大多数版本的 MySQL 优化器都没有那么聪明地识别这一点。
2. 删除两个冗余的 callbackAfter IS NOT NULL 条件。如果 callbackAfter 满足第二部分，则无论如何都不能为 null。
3. 将查询分成 6 个部分并为每个部分添加适当的索引可能会受益 - 但根据 CASE 中的条件是否重叠，您可能会得到错误或正确的结果。

可能的重写(注意不同的格式并检查这是否返回相同的结果，它可能不会!)

SELECT
    cnt1 AS "Unused"
  , cnt2 AS "Max Attempts Reached"
  , cnt3 AS "Call Back After 7 Days"
  , cnt4 AS "Call Back Within 7 Days"
  , cnt5 AS "Call Back Within 7 Days"
  , cnt6 - (cnt1+cnt2+cnt3+cnt4+cnt5) AS "Spoke To"
FROM
  ( SELECT
      ( SELECT COUNT(*)  FROM CO_Lead
        WHERE groupId = 123 AND deleted = 0
          AND lastStatus IS NULL
      ) AS cnt1
    , ( SELECT COUNT(*)  FROM CO_Lead
        WHERE groupId = 123 AND deleted = 0
          AND attempts > 3 AND callbackAfter IS NULL
      ) AS cnt2
    , ( SELECT COUNT(*)  FROM CO_Lead
        WHERE groupId = 123 AND deleted = 0
          AND callbackAfter >  DATE_ADD(NOW(), INTERVAL 7 DAY)
      ) AS cnt3
    , ( SELECT COUNT(*)  FROM CO_Lead
        WHERE groupId = 123 AND deleted = 0
          AND callbackAfter <= DATE_ADD(NOW(), INTERVAL 7 DAY)
      ) AS cnt4
    , ( SELECT COUNT(*)  FROM CO_Lead
        WHERE groupId = 123 AND deleted = 0
          AND archived = 0
      ) AS cnt5
    , ( SELECT COUNT(*)  FROM CO_Lead
        WHERE groupId = 123 AND deleted = 0
      ) AS cnt6
  ) AS tmp ;

如果它确实返回了正确的结果，您可以添加用于每个子查询的索引:

对于子查询 1:(groupId, deleted, lastStatus)

对于子查询 2、3、4:(groupId, deleted, callbackAfter, attempts)

对于子查询 5:(groupId, deleted, archived)

---

另一种方法是保留您的查询(仅注意上面的注释 1 和 2)并添加一个广泛的覆盖索引:

 (groupId, deleted, lastStatus, callbackAfter, attempts, archived)