커버드 쿼리 튜닝

같이 일하는 주니어 개발자가 다시 도움을 요청해왔다.

INSERT * SELECT을 처리하는데 시간이 오래 걸려서 lock time out이 나요!!

다음은 기존 쿼리의 일부분이다.

create index table1_index
    on table1 (post, status)

UNION ALL
....
table2.currency,
...
FROM table table1 
LEFT JOIN (SELECT column1, column2 FROM table2 GROUP BY column1) table2 ON table1.partner = table2.partner
WHERE table1.post IS NOT NULL
AND table1.status IN ('....')
  AND table1.complete >= DATE_FORMAT('2022-10-30', '%Y-%m-%d 00:00:00')
  AND table1.complete <= DATE_FORMAT('2022-10-30', '%Y-%m-%d 23:59:59')

IS NOT NULL로는 인덱스를 타지 못한다.

결국 처리 방법은 complete에 Index를 추가하든지, 아니면 데이터량이 적던지.. 키를 새로 찾아오던지였다.

SELECT 
....
    , (SELECT currency FROM table2 AS P WHERE table1.id = table2.id LIMIT 1)
....
FROM table AS table1
WHERE table1.create > DATE_SUB(DATE_FORMAT('2022-11-30', '%Y-%m-%d 00:00:00'), INTERVAL 2 MONTH) AND table1.post IS NOT NULL AND table1.status IN ('.....')
    AND table1.complete >= DATE_FORMAT('2022-11-30', '%Y-%m-%d 00:00:00')
    AND table1.complete <= DATE_FORMAT('2022-11-30', '%Y-%m-%d 23:59:59')

create 시간에 Index가 있으며, complete와 create는 2달 이상 차이나지 않는다는 회사 테이블 특수성을 고려해서 작업됨.

만약 저런 특수성이 없다면 Index를 만들어 쓰거나, 조합으로 키를 찾아야 함.

 

그래도 다음과 같은 인덱스가 존재했다.

create index INDEX on TABLE(processed, complete);

SELECT *
FROM table1 AS R
INNER JOIN
(
    SELECT *
    FROM
    (
        SELECT seq, complete
        FROM table1 USE INDEX (index)
        ORDER BY complete DESC
    ) AS R
    WHERE R.complete >= DATE_FORMAT('2022-11-30', '%Y-%m-%d 00:00:00')
       AND R.complete <= DATE_FORMAT('2022-11-30', '%Y-%m-%d 23:59:59')
) AS R2
ON R.seq = R2.seq
WHERE R.post IS NOT NULL
   AND R.status IN ('...');

index table 정보로 정렬 후 기본키로 가져온다.

이때 옵티마이저가 버전차이로 인해 상위버전보다 안 좋은건지 use index가 없으면 제거해서 속도가 3분이상으로 늘어남.