我有一个非常大的表,每天包含大约 300 万条记录。
以下查询太慢了
EXPLAIN SELECT *
FROM summary_by_to_days_range
WHERE(record_date BETWEEN '2019-03-12' AND '2019-03-15')
AND unit_id = 1148210
AND enum_key IN (9, 10, 38, 311)
GROUP BY unit_id, record_date
ORDER BY record_date DESC;
结果如下:
+---------+----------+-------------+---------------+---------+-----------------------------------------------------+
| rows | filtered | Extra | possible_keys | key | partitions |
+---------+----------+-------------+---------------+---------+-----------------------------------------------------+
| 9072566 | 4 | Using where | PRIMARY | PRIMARY | from20190312,from20190313,from20190314,from20190315 |
+---------+----------+-------------+---------------+---------+-----------------------------------------------------+
与
EXPLAIN SELECT *
FROM summary_by_to_days_range
WHERE(record_date IN ('2019-03-12','2019-03-13','2019-03-14','2019-03-15'))
AND unit_id = 1148210
AND enum_key IN (9, 10, 38, 311)
GROUP BY unit_id, record_date
ORDER BY record_date DESC;
效果要好得多:
+------+----------+-------------+---------------+---------+-----------------------------------------------------+
| rows | filtered | Extra | possible_keys | key | partitions |
+------+----------+-------------+---------------+---------+-----------------------------------------------------+
| 16 | 100 | Using where | PRIMARY | PRIMARY | from20190312,from20190313,from20190314,from20190315 |
+------+----------+-------------+---------------+---------+-----------------------------------------------------+
我不明白为什么..我正在提供 PK 值,唯一的区别是日期之间子句!
表架构
`CREATE TABLE summary_by_to_days_range (
`record_date` date NOT NULL,
`unit_id` int(11) NOT NULL,
`enum_key` int(11) NOT NULL,
`str_value` varchar(200) DEFAULT NULL,
PRIMARY KEY (`record_date`,`unit_id`,`enum_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(record_date))
(PARTITION START_h VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION from20181231 VALUES LESS THAN (737425) ENGINE = InnoDB,
PARTITION from20190101 VALUES LESS THAN (737426) ENGINE = InnoDB,
.
.
PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB)`
我还尝试按键、按范围列按 DAYOFYEAR() 的哈希进行分区,所有这些都得到了同样令人失望的结果。
任何人?
通常,使用"分区键"的列启动PRIMARY KEY
效率低下。 毕竟,首先是"分区修剪";为什么要过滤同样的东西?
通常,使用将在"范围"测试中使用的列启动任何复合索引是低效的。 这是微妙的,但我认为这解释了你所看到的差异。 使用IN
(您的第二个查询),可以使用 PK 中的更多字段,从而运行得更快。
而且,不,优化器足够聪明,可以理解"日期"的工作原理。 否则,它可以像执行第一个查询一样快地执行第二个查询。 (这粗略地解决了某些评论。
(供参考)
WHERE record_date BETWEEN '2019-03-12' AND '2019-03-15'
AND unit_id = 1148210
AND enum_key IN (9, 10, 38, 311)
`record_date` date NOT NULL,
PRIMARY KEY (`record_date`,`unit_id`,`enum_key`)
PARTITION BY RANGE (TO_DAYS(record_date))
让我们按如下方式解决我的两个评论。
更改为
PRIMARY KEY(unit_id, enum_key, record_date)
有了这个PK,你的任何一个SELECTs
都会
- 修剪到 4 个分区(和以前一样)
- 快速过滤到所需的
unit_id
。 (我怀疑这是大部分效率所在。 - 跳过行以获得
enum_key
- 检查
record_date
是否正确。
我很高兴设置start
和future
分区。 (也许你已经读过这篇文章了。
注意:拥有超过 50 个分区可能有些低效。 如果您有(或将拥有)更多,请考虑使用每周或每月分区。 这会对我的PK产生轻微影响,但直到第4步。
至于PARTITION BY HASH
...我发现使用它没有性能提升。 (或者至少没有其他方式无法实现的。