mysql - 优化的SQL查询

Question

表架构

对于这两个表，CREATE 查询如下:

表1:(file_path_key、dir_path_key)

create table Table1(file_path_key varchar(500), dir_path_key varchar(500), primary key(file_path_key)) engine = innodb;

示例，file_path_key =/home/playstation/a.txt
dir_path_key =/home/playstation/

表2:(file_path_key，hash_key)

create table Table2(file_path_key varchar(500) not null, hash_key bigint(20) not null, foreign key (file_path_key) references Table1(file_path_key) on update cascade on delete cascade) engine = innodb;

目标:

Given a hash value *H* and a directory string *D*, I need to find all those 
hashes which equal to *H* from Table2, such that, the corresponding file entry 
doesn't have *D* as it's directory.

在这种特殊情况下，Table1 有大约 40,000 个条目，Table2 有 5,000,000 个条目，这使得我当前的查询非常慢。

select distinct s1.file_path_key from Table1 as s1 join (select * from Table2 where hash_key = H) as s2 on s1.file_path_key = s2.file_path_key and s1.dir_path_key !=D;

Answer 1

子选择确实不必要地减慢了查询速度。

您应该删除它并用一个简单的联接替换它，将所有与非联接相关的条件下推到 WHERE 子句中。

您还应该在 Table1.dir_path_key 和 Table2.hash_key 列上添加索引:

ALTER TABLE Table1
  ADD INDEX dir_path_key dir_path_key(255);

ALTER TABLE Table2
  ADD INDEX hash_key (hash_key);

尝试这样的查询:

select distinct s1.file_path_key 
from Table1 as s1 
join Table2 as s2 on s1.file_path_key = s2.file_path_key
where s1.dir_path_key !=D
and s2.hash_key =H;