WHERE 문이 사용된 SQL 튜닝하기 ②

어떤 컬럼에 인덱스를 걸어야 할까?

WHERE 문이 사용된 SQL 튜닝하기 에서 WHERE 문에 사용된 컬럼에 인덱스를 걸면 좋다고 했다

하지만 자주 사용되고 WHERE 절이 사용된 쿼리를 튜닝할 때 어떤 컬럼에 인덱스를 거는 것이 효율적일지 판단해야 한다. 이를 위해서 먼저 하나씩 테스트를 해보며 실행 계획을 확인해 볼 수 있다.

MariaDB 예제

-- 테이블 생성
CREATE TABLE users(
 id INT AUTO_INCREMENT PRIMARY KEY,
 NAME VARCHAR(100),
department VARCHAR(100),
created_at TIMESTAMP DEFAULT current_timestamp
);

-- 가짜 데이터 100만개 생성
INSERT INTO users(NAME, department, created_at)
WITH RECURSIVE cte(n) AS (
    SELECT 1 AS n UNION ALL SELECT n+1 from cte WHERE n < 1000000 
)
SELECT CONCAT('User', LPAD(cte.n, 7, '0')) AS NAME,
case
    when n%10=1 then 'engineering'
    when n%10=2 then 'marketing'
    when n%10=3 then 'sales'
    when n%10=4 then 'finance'
    when n%10=5 then 'hr'
    when n%10=6 then 'it'
    when n%10=7 then 'cs'
    when n%10=8 then 'rnd'
    when n%10=9 then 'operations'
ELSE 'pm'
END AS department,
TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*3650) DAY) + INTERVAL FLOOR(RAND()*86400) SECOND) AS created_at -- 최근 10년 내에 생성된 날짜 랜덤
FROM cte;

다음의 쿼리를 튜닝해본다고 하자.

SELECT * FROM users WHERE department = 'sales' and created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

① created_at 컬럼에 인덱스 설정

users 테이블에 아무런 인덱스가 설정되어 있지 않았을 때 (PK 제외) 먼저 created_at 컬럼에 인덱스를 설정하면 보통 0.016초가 걸리고 type: range 스캔을 한다.

② department 컬럼에 인덱스 설정

users 테이블에 아무런 인덱스가 설정되어 있지 않았을 때 (PK 제외) department 컬럼에 인덱스를 설정하면 보통 1.xxx초가 걸리고 type: ALL 이 나온다. (MySQL에서는 department가 비고유 값이기 때문에 ref가 나올 수 있음)

③ created_at, department 각각 따로 인덱스 설정

created_at, department 컬럼 모두 각각 인덱스를 설정했을 경우(멀티 인덱스 x) 실행 계획 결과 실제로 사용한 인덱스 키는 created_at 만 사용하였다. 따라서 두 컬럼 모두 인덱스를 설정할 필요가 없었다
→ created_at만 인덱스 설정하는 것이 효율적

④ created_at, department 멀티 컬럼 인덱스 설정

두 컬럼을 순서와 상관없이 멀티 컬럼 인덱스를 설정했을 때 보통 0.000~0.015초가 걸리고 type: range가 나온다. 멀티 컬럼 인덱스 설정의 컬럼 순서와 상관없이 created_at만 걸었을 때와 차이가 거의 없음
→ created_at만 인덱스 설정하는 것이 효율적

어떤 컬럼에 인덱스를 거는 것이 효율적일지, 몇개의 인덱스를 설정하는 것이 좋을지 정답은 없다.

직접 실행해보고 실행 계획을 분석해보며 최적의 답을 찾아가는 것이 중요하다.

★ 단일 컬럼에 설정하는 인덱스를 설정했을 때와 멀티 컬럼 인덱스를 설정했을 때의 성능 차이가 별로 나지 않는다면, 멀티 컬럼 인덱스를 사용하지 말고 일반 인덱스를 활용하는 것이 좋다.