mysql - 使用 indecies 进行 SELECT 优化

Question

我目前正在处理单词相似度的数据集。数据非常简单 - 你有单词对和相似度值。 (狗；猫；43000)

我将原始数据移至 MySQL 表，其结构如下:

word1_id: INT(11), Primary Key, Not Null

word2_id: INT(11), Primary Key, Not Null

value: INT(11), Not Null

创建表时，我还定义了一个索引(除了主键索引之外):

PRIMARY: BTREE, #1 word1_id, #2 word2_id

Word2: BTREE, #1 word2_id, #2 word1_id

有 50.000 个独特的单词。数据是完全静态的 - 一旦您第一次将其导入表中，就不会发生任何变化。 数据示例如下:

word1_id ; word2_id ; value

1 ; 2 ; 48971754

1 ; 3 ; 75997417

1 ; 4 ; 18285783

..

1 ; 50000 ; 127

2 ; 3 ; 1046254

2 ; 4 ; 268081

...

目标很简单:对于给定的目标单词 (int)，找到与其最相似的单词。

为此，表必须查找目标 INT(例如 436)位于第 1 列(436 ; 543 ; 475652)或第 2 列(72 ; 436 ; 934454)中的所有条目，并返回排序结果，基于第 3 列。

我的问题如下:

在第一列中查找目标 INT 时，过程很快(例如 0.1 秒)。

SELECT
    value, word2_id
FROM
    cooccurrence
    WHERE
        word1_id = (436)
ORDER BY value DESC;

但是，对基于第 2 列的 WHERE 语句执行同样的操作需要很长时间(例如 1.5 - 10 秒)

SELECT
    value, word1_id
FROM
    cooccurrence
    WHERE
        word2_id = (436)
ORDER BY value DESC;

问题:

1. 为什么基于第 2 列执行 WHERE 慢得多？到第 1 列。索引表不应该有“排序”版本吗基于两列的数据？

2. 这个表结构是解决这个问题的好方法吗？在那儿需要进行哪些明显的优化？

3. 最终目标是将距离(第 3 列)设为 FLOAT，并且添加包含年份的第四列 (INT)。然后，您将查看与目标最相似的单词列表多年来如何变化。这意味着数据(和表)的大小将急剧增加 - 例如从几GB到数百GB。这会改变吗事情在任何方面都相当重要？

Answer 1

PRIMARY KEY(word1_id, word2_id),
INDEX(word2_id, value, word1_id),
INDEX(word1_id, value, word2_id)

让我来剖析一下

SELECT
    value, word2_id
FROM
    cooccurrence
    WHERE
        word1_id = (436)
ORDER BY value DESC;

它是这样的，使用INDEX(word2_id, value, word1_id):

1. 找到该索引中最后一次出现的 word1_id = (436)。 (哪里)
2. 向后扫描。 (按值 DESC 排序)
3. 在每个项目上提供值、word2_id、(SELECT)

如果您只有大约 50K 字，请将 INT SIGNED 更改为 MEDIUMINT UNSIGNED。这将为此表每行节省 6 个字节。

一旦您添加了另一列并更改了查询，我所说的大多是不够的。让我们看看涉及 year 的 SELECT。

询问性能查询时，请提供EXPLAIN SELECT ...。这样，我们就可以指出您得到了什么线索。