MySQL查询速度慢，EXPLAIN显示正在使用临时；使用文件排序

debugcn 发表于 Dev

瑞典人

该查询：

EXPLAIN SELECT ppi_loan.customerID,
               loan_number,
               CONCAT(forename, ' ', surname) AS agent,
               name,
               broker,
              (SELECT timestamp
               FROM ppi_sar_status
               WHERE history = 0
                   AND (status = 10 || status = 13)
                   AND ppi_sar_status.loanID = ppi_loan.loanID) AS ppi_unsure_date,
              fosSent,
              letterSent,
              (SELECT timestamp
               FROM ppi_ques_status
               WHERE status = 1 
                   AND ppi_ques_status.loanID = ppi_loan.loanID
               ORDER BY timestamp DESC LIMIT 1) AS sent_date,
               ppi_ques_status.timestamp
FROM ppi_loan
LEFT JOIN ppi_assignments ON ppi_assignments.customerID = ppi_loan.customerID
LEFT JOIN italk.users ON italk.users.id = agentID
LEFT JOIN ppi_ques_status ON ppi_ques_status.loanID = ppi_loan.loanID
JOIN ppi_lenders ON ppi_lenders.id = ppi_loan.lender
JOIN ppi_status ON ppi_status.customerID = ppi_loan.customerID
JOIN ppi_statuses ON ppi_statuses.status = ppi_status.status
   AND ppi_ques_status.status = 1
   AND ppi_ques_status.history = 0
   AND (cc_type = '' || (cc_type != '' AND cc_accepted = 'no'))
   AND ppi_loan.deleted = 'no'
   AND ppi_loan.customerID != 10
GROUP BY ppi_loan.customerID, loan_number

非常慢，这是EXPLAIN查询的所有结果

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY ppi_ques_status ref loanID,status,history   status  3   const   91086   Using where; Using temporary; Using filesort
1   PRIMARY ppi_loan    eq_ref  PRIMARY,customerID  PRIMARY 8   ppimm.ppi_ques_status.loanID    1   Using where
1   PRIMARY ppi_lenders eq_ref  PRIMARY PRIMARY 4   ppimm.ppi_loan.lender   1   Using where
1   PRIMARY ppi_assignments eq_ref  customerID  customerID  8   ppimm.ppi_loan.customerID   1   
1   PRIMARY users   eq_ref  PRIMARY PRIMARY 8   ppimm.ppi_assignments.agentID   1   
1   PRIMARY ppi_status  ref status,customerID   customerID  8   ppimm.ppi_loan.customerID   6   
1   PRIMARY ppi_statuses    eq_ref  PRIMARY PRIMARY 4   ppimm.ppi_status.status 1   Using where; Using index
3   DEPENDENT SUBQUERY  ppi_ques_status ref loanID,status   loanID  8   func    1   Using where; Using filesort
2   DEPENDENT SUBQUERY  ppi_sar_status  ref loanID,status,history   loanID  8   func    2   Using where

为什么要扫描这么多行，为什么要“使用临时；使用文件排序”？我无法删除任何子查询，因为我需要它们产生的所有结果

阴影

正如评论中已经提到的，查询缓慢的主要原因是您似乎仅具有单列索引，而您将需要多列索引来覆盖联接，过滤器和分组依据。

另外，您的查询还有另外两个问题：

即使group by只在2个字段上，select列表中也会列出其他几个字段，而不受汇总功能的影响，例如min()。MySQL确实允许在某些sql模式设置下运行此类查询，但是它们仍然违反sql标准，并且可能会产生意想不到的副作用，除非您真的知道自己在做什么。
您ppi_loan在联接条件下的表上有过滤器，联接条件是的左表left join。由于左联接的性质，这些记录不会从结果集中删除，但MySQL不会联接它们上的任何值。这些标准应移至该where子句。

我将创建的索引：

ppi_sar_status：关于loanID，状态，历史记录字段的多列索引-我会考虑将其移至join部分，因为该表不存在
ppi_ques_status：关于loanID，状态，时间戳记字段的多列索引-这将支持子查询和联接。记住，子查询在说明中也有文件排序。
ppi_loan：至少要在customerID上使用多列索引来支持该group by子句的loan_number字段，因此应避免使用文件排序。您可以考虑根据对索引的选择性将其他字段添加到连接条件中。

我也不确定为什么联接中有最后2个状态表，因为您没有从中检索任何值。如果重新使用这些表来消除某些记录，请考虑使用exists()子查询而不是联接。在联接中，MySQL需要从所有联接的表中获取数据，而在exists()子查询中，它将仅检查结果集中是否存在至少1条记录，而不从基础表中检索任何实际数据。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。