WHERE 문이 사용된 SQL 튜닝하기

한번에 너무 많은 데이터를 조회하는 SQL문 튜닝 방법 에서 언급했듯이 WHERE나 LIMIT을 사용하면 속도를 좀더 개선할 가능성이 높아진다고 했다. 하지만 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,
-- 최근 10년 내에 생성된 날짜 랜덤
TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*3650) DAY) + INTERVAL FLOOR(RAND()*86400) SECOND) AS created_at
FROM cte;

다음 쿼리를 통해 3일 이내에 생성된 데이터만 조회한다고 해보자.

-- 보통 0.3xx초
SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY); 
 -- type: full table scan
analyze SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

created_at 컬럼에 인덱스 설정이 되지 않았을 경우 보통 0.3xx초가 걸린다.

실행계획을 조회했을 때도 ALL(full table scan) 이 적용된다.

이제 created_at 컬럼을 인덱스 설정하여 성능을 개선해보자

CREATE INDEX idx_created_at ON users3 (created_at);

-- 평균 0.015~0.016초 걸림 
SELECT * FROM users3 WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);
-- type: range(index range scan), rows 갯수도 1047개 → 성능 향상 가능성 높아짐
analyze SELECT * FROM users3 WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

소요되는 시간이 0.3xx초에서 0.015~0.016초로 줄었다.

실행 계획 type도 range로 바뀌었고 rows 개수도 1047개로 줄었다. 성능이 개선된 것을 확인할 수 있다.