mysql - 优化MySQL GROUP BY/ORDER BY以计算集合交集

这种情况在某种程度上已简化以使问题更加清楚。
我的情况涉及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-20秒）。请注意，平滑密钥分配并不是真正的选择-记录数据本身分布不均。

解决记录问题的联接似乎并不是问题的核心-我只是出于上下文考虑。如果我只想这样做，我仍然会看到相同的问题：

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;

解释输出：

*************************** 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

有没有一种方法可以重组此表或查询以加快此操作的速度？

最佳答案

您是否尝试过将非聚集索引（索引）添加到这些字段？除了主键声明在某些SQL引擎中执行的一些隐式聚集索引创建以外，我还没有看到Keys在过去自动执行此操作。