[MariaDB] 인덱스를 못 타는 문제

문제.

 5분 단위의 로우 데이터에서 날짜별 시간 당 누락(NULL) 건수를 조회하기 위한 쿼리를 사용했다.

오타를 막기위해 날짜를 변수로 사용하니, 쿼리 시간이 기존 2초에서 기하급수적으로 늘어났다.

 

기본 쿼리

-- 시간별 NULL(누락) 데이터 조회
EXPLAIN SELECT SUBSTR(a.CREATED_DATE, 9, 2) as 시간, COUNT(*) as 건수
FROM (SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE('20241110000000', '%Y%m%d%H%i%s'), INTERVAL n * 5 MINUTE), '%Y%m%d%H%i%s') AS CREATED_DATE 
      FROM numbers) a
LEFT JOIN (SELECT DISTINCT itd.CREATED_DATE
                 FROM total_data td
                 WHERE td.CREATED_DATE BETWEEN '20241110000000' AND '20241110235500') b 
ON a.CREATED_DATE = b.CREATED_DATE
WHERE b.CREATED_DATE IS NULL 
GROUP BY SUBSTR(a.CREATED_DATE, 9, 2)
ORDER BY a.CREATED_DATE;

 

쿼리 소요 시간

24 row(s) fetched - 2s, on 2024-11-25 at 14:49:06

 

 

변수 사용 쿼리

SET @INPUTMMDD = '20241101';

EXPLAIN SELECT SUBSTR(a.CREATED_DATE, 9, 2) as 월, count(*) as 건수
FROM (SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(@INPUTMMDD, '000000'), '%Y%m%d%H%i%s'), INTERVAL n * 5 MINUTE), '%Y%m%d%H%i%s') AS CREATED_DATE 
      FROM numbers) as a
LEFT JOIN (SELECT DISTINCT td.CREATED_DATE
    	FROM total_data itd
        WHERE td.CREATED_DATE BETWEEN CONCAT(@INPUTMMDD, '000000')  AND CONCAT(@INPUTMMDD, '235500') as b 
ON a.CREATED_DATE = b.CREATED_DATE
WHERE b.CREATED_DATE IS NULL 
GROUP BY SUBSTR(a.CREATED_DATE, 9, 2)
ORDER BY a.CREATED_DATE;

 

쿼리 소요 시간

측정 불가

 

 

MariaDB에서는  EXPLAIN, DESCRIBE, DESC 키워드를 통해 실행 계획을 분석할 수 있다. 

 

id	테이블이 조인되는 순서를 나타내는 시퀀스 번호입니다.
select_type	이 테이블은 어떤 종류 SELECT에서 나온 것인가요?
table	테이블의 별칭 이름. 하위 쿼리에 대한 구체화된 임시 테이블은 <subquery#>로 명명됩니다.
type	테이블에서 행을 찾는 방법(조인 유형).
possible_keys	테이블의 행을 찾는 데 사용할 수 있는 테이블의 키
key	행을 검색하는 데 사용되는 키의 이름입니다. NULL키가 사용되지 않았습니다.
key_len	사용된 키의 바이트 수(다중 열 키의 일부만 사용하는 경우 표시).
ref	키 값으로 사용되는 참조입니다.
rows	각 키 조회에서 테이블에서 찾을 수 있는 행의 수에 대한 추정치입니다.
Extra	이 조인에 대한 추가 정보입니다.

 

기본 쿼리의 EXPLAIN

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	numbers	ALL	[NULL]	[NULL]	[NULL]	[NULL]	288	Using temporary; Using filesort
2	PRIMARY	<derived3>	ref	key0	key0	43	func	1	Using where
3	DERIVED	td	range	idx_created_date	idx_created_date	42	[NULL]	12747	Using where; Using index for group-by

 

 

변수 사용 쿼리의 EXPLAIN

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	numbers	ALL	[NULL]	[NULL]	[NULL]	[NULL]	288	Using temporary; Using filesort
2	PRIMARY	<derived3>	ref	key0	key0	43	func	1	Using where
3	DERIVED	td	range	[NULL]	idx_created_date	42	[NULL]	1864600	Using where; Using index for group-by

 

• possible_keys가 NULL로 변한 것과 조회 rows가 12747 -> 1864600 로 늘어난 것으로 인덱스를 못탄다고 추정이 된다.
• 그럼 무엇 때문에 index를 못타는 걸까?

 

해결 시도 

1. CONCAT() 제거 및 변수 제거

• CONCAT() 함수를 제거해봤지만 차이가 없었다.
• 변수를 제거하니 정상 쿼리와 같은 속도가 나왔다.

2. 인덱스 힌트

• 옵티마이저가 인덱스를 못탈 때, 옵티마이저에게 인덱스를 힌트로 주거나 강제할 수 있는 방법이 있다.
• USE INDEX
• FORCE INDEX
• IGNORE INDEX

예시

CREATE INDEX Name ON City (Name);
CREATE INDEX CountryCode ON City (Countrycode);
EXPLAIN SELECT Name FROM City USE INDEX (CountryCode)
WHERE name="Helsingborg" AND countrycode="SWE";

 

결과

id	select_type	table	type possible_keys	key	 key_len	ref 	rows Extra
1	  SIMPLE	City	ref 	CountryCode CountryCode	3	const	14	Using where

 

• 하지만 위 방법으로도 인덱스를 못찾았다.

 

 

3. td 테이블에서 인덱스로 잡는 것이 날짜값이므로 변수를 가져오는 CONCAT() 함수를 DATE_FORMAT으로 묶기

 

변경된 쿼리

SET @INPUTMMDD = '20241101';

SELECT SUBSTR(a.CREATED_DATE, 9, 2) as 월, count(*) as 건수
FROM (SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(@INPUTMMDD, '000000'), '%Y%m%d%H%i%s'), INTERVAL n * 5 MINUTE), '%Y%m%d%H%i%s') AS CREATED_DATE 
      FROM numbers) as a
LEFT JOIN (SELECT DISTINCT itd.CREATED_DATE
    	FROM total_data itd
        WHERE td.CREATED_DATE BETWEEN DATE_FORMAT(CONCAT(@INPUTMMDD, '000000'), '%Y%m%d%H%i%s')  AND DATE_FORMAT(CONCAT(@INPUTMMDD, '235500'), '%Y%m%d%H%i%s')) as b 
ON a.CREATED_DATE = b.CREATED_DATE
WHERE b.CREATED_DATE IS NULL 
GROUP BY SUBSTR(a.CREATED_DATE, 9, 2)
ORDER BY a.CREATED_DATE;

 

소요 시간

3 row(s) fetched - 0.347s, on 2024-11-25 at 15:18:37

 

• 옵티마이저가 변수가 들어오면서 날짜형식인 인덱스를 못탔던 것으로 보인다. 
• 인덱스가 날짜 형태일 때는 꼭 날짜형식으로 맞춰주자

---

참고 

- https://mariadb.com/kb/en/explain/

- https://dbknowledge.tistory.com/77

- https://mariadb.com/kb/en/index-hints-how-to-force-query-plans/