[DB] Maria DB 쿼리 튜닝 속도 개선 (인덱스 활용)

DB속도 개선을 하기 위해서는 실행 계획을 보고 인덱스를 타고 있는지 확인해야 한다. 그런데 무엇보다도 중요한건 쿼리가 효율적으로 돌아가는지 파악이 필요하다. 인덱스를 추가하기 전에 쿼리 부터 수정해야 할 필요가 있는지 확인하는게 중요하다.

 

회사에서 느리다고 하는 화면을 속도 개선해달라고 하는 요청때문에 쿼리를 뜯어보았다. 쿼리의 전체적인 부분을 이해하기 보다 그 전에 약 1000줄 되는 전체 SQL 문에서 느린 부분을 찾아내는것이 우선이다.

 

아래 쿼리가 느린 부분의 쿼리이다. 약 15초 정도 걸렸다.

SELECT
    htl1.`hotel_id`,
    DATE_ADD('2024-11-01', INTERVAL `num` DAY) AS `date`,
    SUM(
        CASE
            WHEN hcl1grp.`close_date` < DATE_ADD('2024-11-01', INTERVAL `num` DAY) THEN 1
            ELSE 0
        END
    ) AS total_room_count
FROM `t0_number` AS num0
INNER JOIN `t2_room` AS rom2
    ON  rom2.`deleted` IS NULL
    AND rom2.`state` = 1
INNER JOIN `t2_room_type` AS rtp2
    ON  rtp2.`type_id` = rom2.`type_id`
    AND rtp2.`deleted` IS NULL
    AND rtp2.`state` = 1
INNER JOIN `t1_hotel` AS htl1
    ON  htl1.`hotel_id` = rom2.`hotel_id`
    AND htl1.`hotel_id` = rtp2.`hotel_id`
    AND htl1.`hotel_id` IN ('100085','100149','100127','100167','100172','100156')
    AND htl1.`deleted` IS NULL
LEFT OUTER JOIN `t1_hotel_close` AS hcl1
    ON  hcl1.`hotel_id` = htl1.`hotel_id`
    AND hcl1.`close_state` = 1
    AND DATE_FORMAT(hcl1.`close_date`, '%Y-%m-%d') = DATE_ADD('2024-11-01', INTERVAL `num` DAY)
LEFT OUTER JOIN (
    SELECT
        hcl1.`hotel_id`,
        DATE_FORMAT(MIN(hcl1.`close_date`), '%Y-%m-%d') AS close_date
    FROM `t1_hotel_close` AS hcl1
    WHERE
        hcl1.`close_state` = 1
    AND hcl1.`hotel_id` IN ('100085','100149','100127','100167','100172','100156')
    GROUP BY
        hcl1.`hotel_id`
) AS hcl1grp
    ON  hcl1grp.`hotel_id` = htl1.`hotel_id`
WHERE
    `num` <= IFNULL(DATEDIFF('2024-12-31', '2024-11-01'), -1)
AND hcl1.`hotel_id` IS NULL
GROUP BY
    DATE_ADD('2024-11-01', INTERVAL `num` DAY)

 

쿼리를 찾아냈으면 EXPLAIN 으로 실행 계획은 본다.

 

실행계획을 보았는데 인덱스를 안타고 풀스캔을 하고 있었다.  그 이유는 아래 빨간색 박스 부분 때문이였다.

왜 안타냐면 DATE_FORMAT() 함수 때문에 모든 데이터가 저 함수를 거쳐서 변경이 우선 되어야 하기 때문에 인덱스가 있어도 탈 수가 없다. 우선 저 쿼리를 아래 처럼 바꿔주었다.

LEFT OUTER JOIN `t1_hotel_close` AS hcl1
	ON  hcl1.`hotel_id` = htl1.`hotel_id`
	AND hcl1.`close_state` = 1
	AND hcl1.`close_date` = CONCAT(DATE_ADD('2024-11-01', INTERVAL `num` DAY), ' 00:00:00.000')

 

비교가 되는 `close_date` 필드에 대한 함수가 걸려있지 않아 인덱스가 이제 있으면 탈 것 이다. 하지만 `close_date` 필드에 대한 인덱스가 아에 없어서 어쩔수 없이 인덱스를 추가 하였다.

ALTER TABLE t1_hotel_close ADD INDEX `fk_t1_hotel_close_02`(`hotel_id`, `close_state`, `close_date`);

 

위 처럼 다중 컬럼 인덱스를 추가하였고 인덱스를 타게 되어 풀스캔을 하지 않게 돼서 15초 걸리던게 바로 조회가 되도록 수정 되었다.