具有子选择的 MySQL 查询性能 - 具有数百万行的表

Question

我知道有很多关于 sql 查询性能改进的问题，但我无法使用这些问题的答案来提高我的查询性能(足够)。

因为我想要比 rsync 和 fslint 更灵活的东西，所以我编写了一个小的 java 工具来遍历文件树并将路径和校验和存储在 mysql 数据库中。

你会在这里找到我的表结构: http://code.google.com/p/directory-scanner/source/browse/trunk/sql/create_table.sql -起初我只有一张表，但后来我想如果我将目录路径中多余的很长的字符串移动到一个单独的地方并使其成为 1:n 关系，我可以节省很多空间

我已经定义了这两个索引:

CREATE INDEX files_sha1 ON files (sha1);
CREATE INDEX files_size ON files (size);

现在困扰我的问题是: http://code.google.com/p/directory-scanner/source/browse/trunk/sql/reporingQueries.sql

其中最糟糕的是最后一个，它应该很有可能总是返回一个空集(sha1 冲突和错误地插入多个文件):

SELECT 
    d.path, 
    d.id, 
    f.filename, 
    f.id, 
    f.size, 
    f.scandate, 
    f.sha1, 
    f.lastmodified 
FROM files f 
INNER JOIN directories d 
    ON d.id = f.dir_id 
WHERE EXISTS ( /* same sha1 but different size */ 
    SELECT ff.id 
    FROM files ff 
    WHERE ff.sha1 = f.sha1 
    AND ff.size <> f.size 
) 
OR EXISTS ( /* files with same name and path but different id */ 
    SELECT ff2.id 
    FROM files ff2 
    INNER JOIN directories dd2 
        ON dd2.id = ff2.dir_id 
    WHERE ff2.id <> f.id 
    AND ff2.filename = f.filename 
    AND dd2.path = d.path 
) 
ORDER BY f.sha1

只要我只有 20k 行(在创建索引之后)，它就可以在不到一秒内运行良好，但现在我有 750k 行，它运行了几个小时，mysql 完全耗尽了我的一个 cpu整个时间的核心。

此查询的 EXPLAIN 给出此结果:

id ; select_type ; table ; type ; possible_keys ; key ; key_len ; ref ; rows ; filtered ; Extra
1 ; PRIMARY ; d ; ALL ; PRIMARY ; NULL ; NULL ; NULL ; 56855 ; 100.0 ; Using temporary; Using filesort
1 ; PRIMARY ; f ; ref ; dir_id ; dir_id ; 4 ; files.d.id ; 13 ; 100.0 ; Using where
3 ; DEPENDENT SUBQUERY ; dd2 ; ALL ; PRIMARY ; NULL ; NULL ; NULL ; 56855 ; 100.0 ; Using where
3 ; DEPENDENT SUBQUERY ; ff2 ; ref ; dir_id ; dir_id ; 4 ; files.dd2.id ; 13 ; 100.0 ; Using where
2 ; DEPENDENT SUBQUERY ; ff ; ref ; files_sha1 ; files_sha1 ; 23 ; files.f.sha1 ; 1 ; 100.0 ; Using where

我的其他查询对于 750k 行也不是很快，但至少在 15 分钟或类似的时间内完成(但是，我希望它们也能处理数百万行..)

更新:感谢 radashk 的评论，但您建议的索引似乎是由 mysql 自动创建的 -->

"Table","Non_unique","Key_name","Seq_in_index","Column_name","Collation","Cardinality","Sub_part","Packed","Null","Index_type","Comment","Index_comment"
"files","0","PRIMARY","1","id","A","698397","NULL","NULL",,"BTREE",,
"files","1","dir_id","1","dir_id","A","53722","NULL","NULL",,"BTREE",,
"files","1","scanDir_id","1","scanDir_id","A","16","NULL","NULL","YES","BTREE",,
"files","1","files_sha1","1","sha1","A","698397","NULL","NULL","YES","BTREE",,
"files","1","files_size","1","size","A","174599","NULL","NULL",,"BTREE",,

更新 2:谢谢 Eugen Rieck!我认为你的回答是这个查询的一个很好的替代品，因为它很可能会返回一个空集，无论如何我只会选择数据来显示用户，以便稍后在另一个查询中描述问题。让我真的很高兴，如果有人也能看看我的其他查询，那就太好了:D

更新 3:Justin Swanhart 的回答启发了我采用以下解决方案:无需查询来检查无意中多次插入的目录和文件，只需像这样创建独特的约束:

ALTER TABLE directories ADD CONSTRAINT uc_dir_path UNIQUE (path);
ALTER TABLE files ADD CONSTRAINT uc_files UNIQUE(dir_id, filename);

但是，我想知道这会对插入语句的性能产生多大的负面影响，有人可以对此发表评论吗？

更新4:

ALTER TABLE directories ADD CONSTRAINT uc_dir_path UNIQUE (path);

不起作用，因为它太长了..

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

更新5:

好的，这是我要用来替换我在最初的问题中引用的查询的解决方案:

对于第一部分，查找 sha1 冲突，我将使用它:

SELECT sha1
FROM files
GROUP BY sha1
HAVING COUNT(*)>1
AND MIN(size)<>MAX(size)

如果它返回任何内容，我将使用另一个查询 WHERE sha1 = ? 来选择详细信息

我想如果定义了这个索引，这个查询将运行得最好:

CREATE INDEX sha1_size ON files (sha1, size);

为了验证不存在重复的目录，我将使用这个，因为他不允许约束(参见上面的 UPDATE4):

SELECT path
FROM directories
GROUP BY path
HAVING COUNT(*)>1

对于重复的文件，我将尝试创建此约束:

CREATE UNIQUE INDEX filename_dir ON files (filename, dir_id);

这运行得非常快(15 到 20 秒)，我不需要在它之前创建其他索引来使其更快。错误消息还包含我需要向用户显示问题的详细信息(这不太可能，因为我在插入之前检查了这些内容)

现在只有 5 个查询可以在更短的时间内执行；)感谢 Eugen 和 Justin 迄今为止的大力帮助!

UPDATE6:好吧，自从上次有人回复以来已经过去了几天，我将接受 Justin 的回答，因为那是对我帮助最大的回答。我将我从你们两个那里学到的东西整合到我的应用程序中，并在此处发布了 0.0.4 版:http://code.google.com/p/directory-scanner/downloads/detail?name=directory-scanner-0.0.4-jar-with-dependencies.jar

Answer 1

虽然我无法在不构建您的表和填充的情况下进行验证，但我会尝试类似的方法

-- This checks the SHA1 collisions
SELECT
  MIN(id) AS id,
FROM files
GROUP BY sha1
HAVING COUNT(*)>1
AND MIN(size)<>MAX(size)

-- This checks for directory duplicates
SELECT
  MIN(path) AS path
FROM directories
GROUP BY path
HAVING COUNT(*)>1

-- This checks for file duplicates
SELECT
  MIN(f.id) AS id
FROM files AS f
INNER JOIN files AS ff 
   ON f.dir_id=ff.dir_id
   AND f.filename=ff.filename
GROUP BY f.id
HAVING COUNT(*)>1

一个接一个地跑。

编辑

第三个查询是虚假的 - 对此感到抱歉