如何优化这个SQL查询？(电影数据库)

Question

我使用的是MySQL，由于某些原因，执行这样的查询需要花费很多时间：

SELECT 
  DISTINCT (Movies.id) AS id, 
  Movies.UnMoID AS UnMoID, 
  Movies.runtime AS runtime
FROM 
  Movies
INNER JOIN Acted ON Acted.UnMoID = Movies.UnMoID
INNER JOIN Actors ON Acted.UnMoActorID = Actors.UnMoActorID
INNER JOIN Directed ON Directed.UnMoID = Movies.UnMoID
INNER JOIN Directors ON Directed.UnMoDirectorID = Directors.UnMoDirectorID
WHERE 
  Actors.name LIKE '%spiderman%'
  OR Directors.name LIKE '%spiderman%'
  OR Movies.originalTitle LIKE '%spiderman%'
  OR Movies.englishTitle LIKE '%spiderman%'
  OR Movies.alsoKnownAs LIKE '%spiderman%'
  OR Movies.taglines LIKE '%spiderman%'
  OR Movies.plot LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20
GROUP BY Movies.id
ORDER BY added DESC

结果是：“在6.5320秒内执行了查询，得到了2个结果。”

针对此特定查询的解释给出：

id  select_type  table     type   possible_keys          key            key_len  ref                            rows  Extra
1   SIMPLE       Movies    ALL    UnMoID                 NULL           NULL     NULL                           925   Using temporary; Using filesort
1   SIMPLE       Directed  ref    UnMoID,UnMoDirectorID  UnMoID         62       Movies.Movies.UnMoID             1    
1   SIMPLE       Directors eq_ref UnMoDirectorID         UnMoDirectorID 62       Movies.Directed.UnMoDirectorID   1    
1   SIMPLE       Acted     ref    UnMoID,UnMoActorID     UnMoID         62       Movies.Movies.UnMoID            34   
1   SIMPLE       Actors    eq_ref UnMoActorID            UnMoActorID    62       Movies.Acted.UnMoActorID         1   Using where

分析信息：

......
Creating tmp table    0.000032
Sorting for group     0.000007
executing             0.000005
Copying to tmp table  6.324177
Sorting result        0.000027
Sending data          0.000019
......

在最坏的情况下，执行查询最多需要20秒，每次执行查询的大部分时间都花在Copying to tmp table上。那么，我可以做些什么来优化我的查询并获得合理的查询时间呢？

我有以下内容的索引：

Movies.id                 PRIMARY
Movies.UnMoID             UNIQUE
Movies.runtime            INDEX
Acted.UnMoID              INDEX
Acted.UnMoActorID         INDEX
Actors.UnMoActorID        UNIQUE
Actors.name               UINDEX
Directed.UnMoID           INDEX
Directed.UnMoDirectorID   INDEX
Directors.UnMoDirectorID  UNIQUE
Directors.name            INDEX
Movies.originalTitle      INDEX
Movies.englishTitle       INDEX
Movies.alsoKnownAs        INDEX
Movies.taglines           INDEX
Movies.plot               INDEX

Answer 1

我认为慢的原因是因为你的病情太宽了。相反，我会尝试进行简单的查询，并将它们与UNION结合起来，这样也可以消除重复项。

所以，像这样的东西可能是有益的(我不能真正测试这个声明，因为我没有您的数据库)，因为我假设每个单独的查询都会相当快，并且不会有很多要合并的记录：

(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies INNER JOIN Actors ON Acted.UnMoActorID = Actors.UnMoActorID
WHERE Actors.name LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies INNER JOIN Directed ON Directed.UnMoID = Movies.UnMoID
INNER JOIN Directors ON Directed.UnMoDirectorID = Directors.UnMoDirectorID
WHERE Directors.name LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.originalTitle LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.englishTitle LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.alsoKnownAs LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.taglines LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.plot LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
ORDER BY added DESC

您需要在查询结果中包括要排序或筛选的字段。

如果您有一个支持子选择的引擎，您可以将验证和年龄验证的过滤条件从单个查询移到公共包装器查询中，这样做的好处主要是消除了重复，从而实现了维护：

 SELECT id, UnMoID, runtime FROM
 (SELECT ... ) Q   -- the above query including all necessary fields
 WHERE Movies.validated =1
 AND Movies.ageCertificate <=20
 ORDER BY added DESC

Answer 2

索引对前导通配符的LIKE搜索没有帮助。您可能需要考虑实现full-text search。

Answer 3

与MYSQL类似的搜索速度非常慢，而且全文搜索功能只在MyISAM中可用。您可以尝试使用具有优化索引的read slave来优化内容，但您不会获得太多好处。

我会把你的问题转移到像SPHINX或SOLR(data importer for MYSQL)这样的搜索引擎。