[DB Optimization#11] WHERE 문 튜닝

SQL의 WHERE 조건은 단순히 필터 역할을 넘어 쿼리 성능의 핵심 요소입니다.
특히, 조건절에 어떤 컬럼이 들어가느냐, 그리고 그 컬럼에 인덱스가 있느냐에 따라 쿼리 속도가 수배에서 수십 배까지 차이 날 수 있습니다.

 

이번 글에서는 WHERE절 조건을 중심으로 SQL 튜닝 실습을 진행해보겠습니다.

 

실습: Sales 부서이면서 최근 3일 이내에 가입한 유저 조회

데이터셋 준비

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 100만 건 더미 데이터 생성
SET SESSION cte_max_recursion_depth = 1000000;

INSERT INTO users (name, department, created_at)
WITH RECURSIVE cte (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')),
    CASE 
        WHEN n % 10 = 3 THEN 'Sales'
        ELSE 'Other'
    END,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND)
FROM cte;

 

기본 조회

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

→ 조회 시간은 약 0.294s이고, 실행 계획에서 type = ALL인 것과 더불어 searching row 수가 약99만건에 달하는 것을 알 수 있습니다.

 

이를 통해 해당 쿼리를 실행할 때 Full Table Scan을 했다는 것을 알 수 있습니다. → 튜닝 필요

 

 

지금부터 여러 컬럼에 인덱스를 적용하면서 성능 비교를 해보도록 하겠습니다.

 

1. created_at 기준 인덱스

CREATE INDEX idx_created_at ON users(created_at);

→ 조회 시간은 약 0.025s이고, 실행 계획에서 type이 range로 변경되었으며, searching row 수도 약 1,000개 정도로 대폭 적어진 것을 확인할 수 있었습니다.

 

2. department 기준 인덱스

ALTER TABLE users DROP INDEX idx_created_at; -- created_at 인덱스 제거

CREATE INDEX idx_department ON users(department);

→ 조회 시간은 약 0.285s이고, 실행 계획에서 type이 ref로 변경되었으며, searching row 수는 191,314로 type=ALL보다 적어지긴 했지만, created_at 보다 좋지 않은 성능을 보이고 있습니다.

 

row 수를 통해 이러한 차이는 인덱스로 설정된 department 컬럼의 중복성이 created_at보다 높기 때문이라는 점으로 추측할 수 있습니다.

 

 

3. created_at, department 기준 인덱스

ALTER TABLE users DROP INDEX idx_department; -- 기존 department 인덱스 제거, created_at 인덱스만 추가할 수 있지만 이해 편의상 기존 인덱스 제거 후 두 인덱스 추가

CREATE INDEX idx_created_at ON users(created_at);
CREATE INDEX idx_department ON users (department);

두 컬럼 모두 인덱스로 설정됨

→ 조회 시간은 약 0.021s이고, 실행 계획에서 type이 range로 변경되었으며, searching row 수도 약 1,000개 정도로 적어진 것을 확인할 수 있었습니다.

 

지금까지 가장 좋은 성능을 보이고 있습니다.

 

그러면 이 인덱스로 설정해야 할까요?

 

아닙니다. 

 

EXPLAIN을 보시면 possible_keys에서는 created_at과 department가 key로 인식이 되었지만, 정작 실제 사용된 인덱스는 우측의 key에서 보이다시피 created_at만 사용되었습니다.

이는 MySQL의 옵티마이저에서 자체적으로 유용한 인덱스로 created_at만 사용하는 것으로 판단을 내렸다는 내용입니다.

 

따라서, 이러한 경우에서는 실질적으로 성능 향상에 유의미한 created_at 컬럼만 사용하는 것이 효율적입니다

 

 

4. created_at, department 멀티 컬럼 인덱스

ALTER TABLE users DROP INDEX idx_department; 
ALTER TABLE users DROP INDEX idx_created_at;

CREATE INDEX idx_created_at_dept ON users(created_at, department);
-- 또는
CREATE INDEX idx_dept_created_at ON users(department, created_at);

→ 조회 시간은 약 0.022s이고, 실행 계획에서 type이 range로 변경되었으며, searching row 수도 약 1,000개 정도로 이전 결과와 크게 차이나지 않는 것을 알 수 있었습니다.

 

→ 멀티 컬럼 인덱스가 항상 더 빠른 것은 아니며, 단일 컬럼 인덱스만으로도 충분할 수 있습니다.