이 쿼리 :
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 by2 개의 필드에만있는 경우에도 .NET select과 같은 집계 함수의 적용을받지 않고 다른 여러 필드가 목록에 나열 됩니다 min(). MySQL은 이러한 쿼리가 특정 SQL 모드 설정에서 실행되도록 허용하지만 여전히 SQL 표준에 위배되며 실제로 수행중인 작업을 알지 않는 한 예기치 않은 부작용이 발생할 수 있습니다.
ppi_loan의 왼쪽 테이블 인 조인 조건 의 테이블에 대한 필터가 있습니다 left join. 왼쪽 조인의 특성으로 인해 이러한 레코드는 결과 집합에서 제거되지 않지만 MySQL은 해당 레코드의 값을 조인하지 않습니다. 이러한 기준은 where조항 으로 이동해야합니다 .
내가 만들 인덱스 :
ppi_sar_status : loanID, status, history 필드에 대한 다중 열 인덱스-이 테이블이 없기 때문에 조인 섹션으로 이동하는 것이 좋습니다.
ppi_ques_status : loanID, status, timestamp 필드의 다중 열 인덱스-하위 쿼리와 조인을 모두 지원합니다. 하위 쿼리에는 Explain에 파일 정렬도 있습니다.
ppi_loan : 최소한 customerID에 대한 다중 열 인덱스, loan_number 필드는 group by절 을 지원하므로 파일 정렬을 최소한으로 피합니다. 이 인덱스에 대한 선택성을 기반으로 조인 기준에 다른 필드를 추가하는 것을 고려할 수 있습니다.
또한 조인에서 값을 검색하지 않기 때문에 마지막 2 개의 상태 테이블이있는 이유도 잘 모르겠습니다. 이러한 테이블을 사용하여 특정 레코드를 제거 exists()하는 경우 조인 대신 하위 쿼리를 사용하는 것이 좋습니다. 조인에서 MySQL은 조인 된 모든 테이블에서 데이터를 가져와야하는 반면, exists()하위 쿼리에서는 기본 테이블에서 실제 데이터를 검색하지 않고 결과 집합에 최소 1 개의 레코드가 있는지 확인합니다.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다