为了使问题更清楚,这种情况有所简化。
我的情况涉及MySQL中的一组数据记录。
CREATE TABLE `records` (
`id` bigint(20) NOT NULL,
`property1` bigint(20) NOT NULL,
`property2` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `property1` (`property1`),
KEY `property2` (`property2`)
);从每条记录中,我们根据记录数据生成并存储可变数量的密钥(散列)。
CREATE TABLE `rkeys` (
`rKey` bigint(20) NOT NULL,
`rId` bigint(20) NOT NULL,
KEY `rKey` (`rKey`),
KEY `rId` (`rId`),
FOREIGN KEY (`rId`) REFERENCES `records` (`id`)
);(关键值是散列,可以更均匀地将它们分配到密钥空间中。)
例如,可能有500万条记录和5000万个密钥。
我试图做的是在关键集上进行模糊搜索 - 使用最常见的关键字将记录与数据库中的记录进行匹配。结果还需要根据记录表中的属性进行过滤。
我一直在处理的查询如下所示:
SELECT rkeys.rId, records.property1, SUM(1) as score
FROM rkeys, records
WHERE
(rKey = 10 OR rKey = 11 OR rKey = 13 OR rKey = 14) AND
rkeys.rId = records.id AND
records.property1 = 1 AND
records.property2 = 2
GROUP BY rId ORDER BY score DESC;如果具有任何给定密钥的记录数量相当小,则性能是可以的;问题是如果我击中出现在几千条记录(例如5000)中的密钥。突然之间,GROUP BY / ORDER BY的表现落在悬崖上(每次查询15-20s)。请注意,平滑密钥分发并非真正的选择 - 记录数据本身分布不均匀。
反对记录问题的联合似乎不是问题的核心 - 我只是将它包含在上下文中。我仍然看到同样的问题,如果我想要做的是这样的:
SELECT rId, SUM(1) as score
FROM rkeys
WHERE rKey = 10 OR rKey = 11 OR rKey = 13 OR rKey = 14
GROUP BY rId ORDER BY score DESC;EXPLAIN输出:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rkeys
type: index
possible_keys: rKey
key: rKey
key_len: 8
ref: NULL
rows: 1
Extra: Using where; Using temporary; Using filesort有没有一种方法可以重构此表或查询以加快此操作的速度?