使用索引选择优化

bvuwiixz  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(663)

我目前正在处理一个关于单词相似性的数据集。数据非常简单-你有一对单词和一个相似的值(狗;猫;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

有50000个独特的单词。数据是完全静态的—一旦您第一次将其导入表中,就不会有任何更改。数据示例如下:

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;

问题:
为什么基于第2列而不是第1列的where要慢得多呢。索引表不应该基于这两列对数据的版本进行“排序”吗?
这种表格结构是解决这个问题的好方法吗?是否有任何明显的优化?
最终的目标是将距离(第3列)作为浮点值,并添加包含年份的第四列(int)。然后,您将查看与某个目标最相似的单词列表随着时间的推移是如何变化的。这意味着数据(和表)的大小将急剧增加,例如从几GB增加到几百GB。这会不会在很大程度上改变事情?

u91tlkcl

u91tlkcl1#

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) :
查找最后出现的 word1_id = (436) 在那个索引里( WHERE )
向后扫描( ORDER BY value DESC )
在每个项目上,交付 value, word2_id , ( SELECT )
如果你只有5万个单词,从 INT SIGNEDMEDIUMINT UNSIGNED . 这将为此表的每行节省6字节。
一旦您添加了另一列并更改了查询,我所说的大部分内容将是不充分的。让我们看看 SELECT 包括 year .
询问性能查询时,请提供 EXPLAIN SELECT ... . 这样,我们就可以指出你得到了什么线索。

相关问题