如何优化以下查询？

Question

我有两个表: maskedindextable和dictionarytable。

字典表有四列：

ID (primary key)
filelocation
dictionaryEncryptedIndex
dictionaryEncryptedWord

值如下所示：

ID | filelocation | dictionaryencryptedindex | dictionaryEncryptedWord
0 | c:/test.txt | 0x01 | EAD64zef6z
1 | c:/test.txt | 0x02 | 5ez4f
2 | c:/test.txt | 0x03 | ze654f
3 | c:/john.txt | 0x01 | 6ze5f4
4 | c:/john.txt | 0x02 | ze5f68z4f
5 | c:/john.txt | 0x03 | EAD64zef6z 
6 | c:/smith.txt | 0x01  | er6g4
7 | c:/smith.txt | 0x02 | z6e58g4
8 | c:/smith.txt | 0x03  | a64zef6z
9 | c:/laura.txt | 0x01  | EAD64zef6z

该表有70万个值

maskedindextable有四列：

ID (primary key)
filelocation
maskedIndexEncryptedIndex
maskedIndexEncryptedValue

值如下所示：

ID | filelocation | maskedIndexEncryptedIndex | maskedIndexEncryptedValue
0 | c:/test.txt | 0x01 | 5z1ef
1 | c:/test.txt | 0x02 | e6rh546er4g
2 | c:/test.txt | 0x03 | z6ef548ezf
3 | c:/john.txt | 0x01 | (y48try68
4 | c:/john.txt | 0x02 | iu47k
5 | c:/john.txt | 0x03 | 6tkj
6 | c:/smith.txt | 0x01  | 6ez5r48
7 | c:/smith.txt | 0x02 | 6i5
8 | c:/smith.txt | 0x03  | 6e5rg
9 | c:/laura.txt | 0x01  | ze654f

此表还包含70万个值

表dictionarytable中的dictionaryencryptedindex和filelocation组合对应于表掩码in中的maskedIndexEncryptedIndex和filelocation。我想找到对应的maskedindexEncrypted值，它的加密字是EAD64zef6z。目前，我使用的是以下查询：

SELECT DISTINCT MaskedIndexTable.filelocation, 
       dictionaryencryptedindex,
       maskedindexencryptedvalue
FROM MaskedIndexTable 
INNER JOIN DictionaryTable 
        ON MaskedIndexTable.maskedIndexEncryptedIndex = 
           DictionaryTable.dictionaryEncryptedIndex  
WHERE MaskedIndexTable.Id IN  
(SELECT DictionaryTable.Id 
 FROM  DictionaryTable 
 WHERE  `dictionaryEncryptedWord` LIKE 'EAD64zef6z') 
 AND  `dictionaryEncryptedWord` LIKE 'EAD64zef6z';

但是这个查询运行的非常慢(需要3秒)。有人知道如何优化我的查询吗？

Answer 1

更简单的语句如何？

SELECT dt.filelocation, dictionaryencryptedindex, maskedindexencryptedvalue
FROM MaskedIndexTable mit
INNER JOIN DictionaryTable  dt
  ON mit.maskedIndexEncryptedIndex = dt.dictionaryEncryptedIndex
  AND mit.filelocation = dt.filelocation 
WHERE dt.dictionaryEncryptedWord = 'EAD64zef6z';

如果不使用通配符，则不需要LIKE。此外，还可以在JOIN语句的ON子句中添加多个条件。

您将需要WHERE和JOIN列上的索引，即DictionaryTable.dictionaryEncryptedWord、DictionaryTable.filelocation、DictionaryTable.dictionaryEncryptedIndex、MaskedIndexTable.filelocation和MaskedIndexTable.maskedIndexEncryptedIndex

Answer 2

根据我的经验，每当查询速度较慢时，WHERE子句中提到的列都没有索引。

尝试索引WHERE子句中出现的所有列。

Answer 3

乍一看，WHERE子句中的子select似乎没有任何作用。您还使用了LIKE而不是简单的=。试试这个：

SELECT DISTINCT MIT.filelocation, 
   dictionaryencryptedindex,
   maskedindexencryptedvalue
FROM MaskedIndexTable MIT
    INNER JOIN DictionaryTable DT
    ON MIT.maskedIndexEncryptedIndex = 
       DT.dictionaryEncryptedIndex  
WHERE dictionaryEncryptedWord = 'EAD64zef6z';