作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我已经实现了这个查询:
SELECT
evt.userId, evt.storeId, COUNT(1) AS totalVisits
FROM
Event evt
WHERE
evt.timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
AND
evt.subtype = 2
AND
userID IS NOT NULL
GROUP BY userId, storeId
HAVING totalVisits>16;
事件表有数百万条记录。列时间戳为 DATETIME,其他列为 INT。这张表访问频率很高,有很多索引。
一开始这个查询执行了 10 多分钟。我通过添加一个新的索引来解决这个问题
ALTER TABLE Event
ADD INDEX `Event_timestamp_subtype_userId_storeId` (`timestamp` ASC, `subType` ASC, `userId` ASC, `storeId` ASC);
这很好用,不到 2 秒我就得到了结果。
我遇到的问题是当我更改条件 INTERVAL 30 DAY 时。如果我设置 INTERVAL 50 DAY(例如),MYSQL 不会使用我创建的索引。相反,它使用另一个仅覆盖两列的索引。
解释命令:
EXPLAIN EXTENDED SELECT
evt.userId, evt.storeId, COUNT(1) AS totalVisits
FROM
Event evt
WHERE
evt.timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 50 DAY) AND NOW()
AND
evt.subtype = 2
AND
evt.userID IS NOT NULL
GROUP BY userId, storeId
HAVING totalVisits>16;
解释输出:
+----+-------------+-------+------------+------+------------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------+---------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------+---------+----------+---------------------------------------------------------------------+
| 1 | SIMPLE | evt | NULL | ref | Event_userId_index,Event_subType_storeId_index,Event_timetamp_index,Event_timestamp_subtype_userId_storeId | Event_subType_storeId_index | 3 | const | 7375964 | 25.00 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+------+------------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------+---------+----------+---------------------------------------------------------------------+
因此,如果设置一个 50 天的条件,查询是不可行的。如何使此查询独立于参数值使用正确的索引?
我正在使用 mysql 服务器 5.7.23
谢谢!
问候
最佳答案
你有“很多索引”。霰弹枪无济于事。您是否有这些索引,其中的列按给定的顺序?
INDEX(subtype, timestamp)
INDEX(subtype, userID)
优化器可能希望将其中任何一个用于 WHERE
。而且,由于它不能使用所有 WHERE
(因为有 2 个范围),它不会到达 GROUP BY
中的列。
第一列(subtype
)用=
测试;这很简单。
第二列是一个“范围”,所以这是它可以处理的最后一件事。
将这些索引中的每一个都变成一个“覆盖”索引可以有一个小的改进:
INDEX(subtype, timestamp, storeID, userID)
INDEX(subtype, userID, timestamp, storeID)
现在处理只需要查看索引的 BTree,而不必在该 BTree 和包含数据的 BTree 之间来回切换。
(前两列按特定顺序排列;其他两列可以交换。)
如果这是一个“巨大”的表(数百万行),我们可以讨论另一个优化,因为您实际上需要一个二维索引。
关于MYSQL GROUP BY 和 WHERE 索引,带有时间戳列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52896571/
我是一名优秀的程序员,十分优秀!