mysql - 亿级表优化查询

Question

这感觉像是一个“为我做功课”的问题，但我真的被困在这里试图让这个查询对一个有很多行的表快速运行。 Here's a SQLFiddle显示架构(或多或少)。

我尝试过索引，试图获得能够显示所有必需列的内容，但没有取得太大成功。这是创建:

CREATE TABLE `AuditEvent` (
    `auditEventId` bigint(20) NOT NULL AUTO_INCREMENT,
    `eventTime` datetime NOT NULL,
    `target1Id` int(11) DEFAULT NULL,
    `target1Name` varchar(100) DEFAULT NULL,
    `target2Id` int(11) DEFAULT NULL,
    `target2Name` varchar(100) DEFAULT NULL,
    `clientId` int(11) NOT NULL DEFAULT '1',
    `type` int(11) not null,
    PRIMARY KEY (`auditEventId`),
    KEY `Transactions` (`clientId`,`eventTime`,`target1Id`,`type`),
    KEY `TransactionsJoin` (`auditEventId`, `clientId`,`eventTime`,`target1Id`,`type`)
)

和(一个版本的)select:

select ae.target1Id, ae.type, count(*)
from AuditEvent ae
where ae.clientId=4
    and (ae.eventTime between '2011-09-01 03:00:00' and '2012-09-30 23:57:00')
group by ae.target1Id, ae.type;

我最终也得到了“使用临时文件”和“使用文件排序”。我尝试删除 count(*) 并改用 select distinct，这不会导致“使用文件排序”。如果有一种方法可以加入以获取计数，这可能没问题。

最初，决定跟踪创建审计记录时存在的目标的 target1Name 和 target2Name。我也需要这些名称(最近的就可以)。

目前查询(上面，缺少 target1Name 和 target2Name 列)在约 2400 万条记录上运行大约 5 秒。我们的目标是数亿，我们希望查询继续沿着这些路线执行(希望将其保持在 1-2 分钟以内，但我们希望它更好)，但我担心一次我们达到了它不会达到的更大数据量(模拟额外行的工作正在进行中)。

我不确定获取额外字段的最佳策略。如果我将列直接添加到 select 中，我将丢失查询中的“使用索引”。我尝试了一个 join 回到表，它保持“使用索引”但需要大约 20 秒。

我确实尝试将 eventTime 列更改为 int 而不是日期时间，但这似乎并没有影响索引使用或时间。

Answer 1

正如您可能理解的那样，这里的问题是范围条件 ae.eventTime between '2011-09-01 03:00:00' and '2012-09-30 23:57:00' 这(一如既往)打破了 Transactions 索引的有效使用(即索引实际上仅用于 clientId 等式和范围条件的第一部分，索引是不用于分组)。

大多数情况下，解决方案是用相等性检查替换范围条件(在您的情况下，引入一个 period 列，将 eventTime 分组为句点并替换 BETWEEN 子句，带有 period IN (1,2,3,4,5))。但这可能会成为您餐 table 的开销。

您可能尝试的另一种解决方案是添加另一个索引(如果不再使用，可能会替换 Transactions):(clientId, target1Id, type, eventTime),并使用以下查询:

SELECT
  ae.target1Id,
  ae.type,
  COUNT(
    NULLIF(ae.eventTime BETWEEN '2011-09-01 03:00:00' 
                            AND '2012-09-30 23:57:00', 0)
  ) as cnt,
FROM AuditEvent ae
WHERE ae.clientId=4
GROUP BY ae.target1Id, ae.type;

这样，您将 a) 将范围条件移动到末尾，b) 允许使用索引进行分组，c) 使索引成为查询的覆盖索引(即查询不需要磁盘IO操作)

UPD1:不好意思，昨天没仔细看你的帖子，没注意到你的问题是检索target1Name和target2Name。首先，我不确定您是否正确理解Using index 的含义。没有Using index并不意味着查询没有使用索引，Using index意味着索引本身包含足够的数据来执行子查询(即索引正在覆盖)。由于 target1Name 和 target2Name 不包含在任何索引中，因此获取它们的子查询不会有 Using index。

如果您的问题只是如何将这两个字段添加到您的查询中(您认为这足够快)，那么只需尝试以下操作:

SELECT a1.target1Id, a1.type, cnt, target1Name, target2Name
FROM (
  select ae.target1Id, ae.type, count(*) as cnt, MAX(auditEventId) as max_id
  from AuditEvent ae
  where ae.clientId=4
      and (ae.eventTime between '2011-09-01 03:00:00' and '2012-09-30 23:57:00')
  group by ae.target1Id, ae.type) as a1
JOIN AuditEvent a2 ON a1.max_id = a2.auditEventId
;