MySQL JOIN 搜索太多行

Question

我的查询有 8,566 个结果。但是 EXPLAIN 显示一个表搜索 233,190 行，我不明白为什么。它使用 'status' 键显示问题表 (ppi_sar_status)，但查询在不同的键 'loanID' 上连接到该表

View the EXPLAIN results

SELECT DISTINCT 
      ppi_loan.loanID, 
      ppi_loan.lender, 
      ppi_loan.customerID, 
      ppi_loan.agreementNo, 
      loan_number, 
      ppi_lenders.name, 
      ppi_status.status, 
      ppi_statuses.description, 
      ppi_loan.broker, 
      (SELECT sarSent 
            FROM ppi_sar 
            WHERE ppi_sar.customerID = ppi_loan.customerID 
                AND ppi_sar.lender = ppi_loan.lender 
            ORDER BY sarSent DESC 
            LIMIT 1) as sarSent, 
      (SELECT COUNT(DISTINCT(groupID)) 
            FROM ppi_mdrs 
            WHERE ppi_mdrs.customerIDfk = ppi_loan.customerID 
                AND ppi_mdrs.lender = ppi_loan.lender 
                AND sent_to_lender = '0000-00-00 00:00:00') AS mdrs_sent, 
      (SELECT sent 
            FROM ppi_mdrs 
            WHERE ppi_mdrs.customerIDfk = ppi_loan.customerID 
                AND ppi_mdrs.lender = ppi_loan.lender 
            ORDER BY sent DESC 
            LIMIT 1) AS mdr_last_sent,
      mobilePhone, 
      homePhone,
      title, 
      firstName, 
      lastName, 
      loaSent 
   FROM 
      ppi_loan 
         JOIN ppi_sar_status 
            ON ppi_loan.loanID = ppi_sar_status.loanID
         JOIN ppi_customer 
            ON ppi_loan.customerID = ppi_customer.customerID
         JOIN ppi_lenders 
            ON ppi_loan.lender = ppi_lenders.id
         JOIN ppi_status 
            ON ppi_loan.customerID = ppi_status.customerID
            JOIN ppi_statuses 
               ON ppi_status.status = ppi_statuses.status
         LEFT JOIN ppi_mdrs 
            ON ppi_loan.customerID = customerIDfk  
            AND ppi_loan.lender = ppi_mdrs.lender
   WHERE 
          ppi_loan.customerID != 10 
      AND ppi_status.status != 9 
      AND ppi_status.status != 32 
      AND ppi_status.status != 54 
      AND ppi_status.status != 58 
      AND ppi_status.status != 59 
      AND ppi_status.status != 61 
      AND ppi_status.status != 69 
      AND ppi_status.status != 60 
      AND ppi_status.history = 0 
      AND ppi_loan.customerID 
         IN (SELECT customerID 
                FROM ppi_status 
                WHERE (status = 5 || status = 6 || status = 79) 
                  AND timestamp > '2015-04-01' 
                  AND ppi_status.customerID = ppi_loan.customerID)
      AND ppi_sar_status.status = 16 
      AND ppi_sar_status.history = 0 
      AND (cc_type = '' || (cc_type != '' AND cc_accepted = 'no'))
      AND ppi_loan.deleted = 'no'

Answer 1

这是由于 WHERE 子句:

ppi_sar_status.status = 16

我可能会因为这样说而受到很多批评，但也许您应该尝试使用索引提示。 https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

(但不要忘记阅读注意事项 http://www.mysqldiary.com/the-battle-between-force-index-and-the-query-optimizer/ )