mysql - 优化 SQL 查询以减少执行时间

Question

应用了所有过滤器的我的 SQL 查询返回 100 万(一百万)条记录。获取所有记录需要 76.28 秒.. 这是 Not Acceptable 。我怎样才能优化我的 SQL 查询，它应该花费更少的时间。我正在使用的查询是:

    SELECT cDistName , cTlkName, cGpName, cVlgName , 
           cMmbName , dSrvyOn 
      FROM sspk.villages 
 LEFT JOIN gps  ON nVlgGpID = nGpID
 LEFT JOIN TALUKS ON nGpTlkID = nTlkID   
 left JOIN dists ON nTlkDistID = nDistID
 LEFT JOIN HHINFO ON nHLstGpID = nGpID
 LEFT JOIN MEMBERS ON nHLstID = nMmbHhiID
 LEFT JOIN BNFTSTTS  ON nMmbID = nBStsMmbID
 LEFT JOIN STATUS ON nBStsSttsID = nSttsID
 LEFT JOIN  SCHEMES ON  nBStsSchID = nSchID
     WHERE (
               (nMmbGndrID = 1 and nMmbAge between 18 and 60) 
           or  (nMmbGndrID = 2 and nMmbAge between 18 and 55)
           )
      AND cSttsDesc like 'No, Eligible' 
      AND DATE_FORMAT(dSrvyOn , '%m-%Y') < DATE_FORMAT('2012-08-01' , '%m-%Y' )
 GROUP BY cDistName , cTlkName, cGpName, cVlgName , 
        DATE_FORMAT(dSrvyOn , '%m-%Y')

我在论坛和外部进行了搜索，并使用了一些给出的提示，但几乎没有任何区别。我在上面的查询中使用的连接在 Primary Key 和 Foreign key 上全部保留。任何人都可以建议我如何修改此 sql 以减少执行时间....

Answer 1

先生，您是 MySQL 的一个非常苛刻的用户!以您提到的速度从大规模连接的结果集中检索一百万条记录是每条记录 76 微秒。许多人会认为这是可以接受的表现。请记住，对于这种大小的结果集，您的客户端软件可能是一个限制因素:它必须消耗巨大的结果集并对其进行处理。

话虽这么说，但我发现了几个问题。

首先，重写您的查询，使每个列名都由表名限定。您将为自己和下一个维护它的人做这件事。您可以一目了然地看到您的 WHERE 条件需要做什么。

其次，考虑这个搜索标准。由于 OR，它需要两次搜索。

 WHERE (
           (MEMBERS.nMmbGndrID = 1 and MEMBERS.nMmbAge between 18 and 60) 
       or  (MEMBERS.nMmbGndrID = 2 and MEMBERS.nMmbAge between 18 and 55)
       )

我猜这些标准符合您的大部分人口——18-60 岁的女性和 18-55 岁的男性(猜测)。您能否将 MEMBERS 表放在 LEFT JOIN 列表的第一位？或者您可以在表中放置一个派生列(MEMBERS.working_age = 1 或类似列)吗？

同时尝试在 MEMBERS 上使用 (nMmbGndrID,nMmbAge) 的复合索引来加快速度。它可能有效也可能无效。

第三，考虑这个标准。

  AND DATE_FORMAT(dSrvyOn , '%m-%Y') < DATE_FORMAT('2012-08-01' , '%m-%Y' )

您已将函数应用于 dSrvyOn 列。这使该搜索无法使用索引。相反，试试这个。

  AND dSrvyOn >= '2102-08-01'
  AND dSrvyOn <  '2012-08-01' + INTERVAL 1 MONTH

如果您在 dSrvyOn 上有一个索引，这将对该索引进行范围搜索。我的评论也适用于您的 ORDER BY 子句中的函数。

最后，正如其他人提到的，不要使用 LIKE 来搜索 = 的位置。如果您想要可接受的性能，切勿使用 column LIKE '%something%'。