SQL 구문과 인덱스 성능개선

게시 2025/03/23 업데이트 2025/03/23

By Not_just_code

6 분읽는 시간

SQL 구문과 인덱스 성능개선

개인 학습용 글로, 내용이 불완전하거나 부정확할 수 있습니다.

사전 작업

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

-- 높은 재귀(반복) 횟수를 허용하도록 설정 -- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.) 
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')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성  
    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 'Operations'  
        WHEN n % 10 = 7 THEN 'IT'  
        WHEN n % 10 = 8 THEN 'Customer Service'  
        WHEN n % 10 = 9 THEN 'Research and Development'  
        ELSE 'Product Management'  
        END AS department,  -- 의미 있는 단어 조합으로 부서 이름 생성  
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성  
FROM cte;

-- 잘 생성됐는 지 확인  
SELECT COUNT(*) FROM users;  
SELECT * FROM users LIMIT 10;

데이터 조회해서 성능 측정

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

해당 쿼리 조회시 평균 200ms

인덱스 생성

  
CREATE INDEX idx_created_at_department ON users (created_at, department);

멀티 컬럼 인덱스로 카디널리티가 높은 순으로 생성
- created-at은 카디널리티가 높다
- department는 카디널리티가 낮다
  - 부서의 개수는 10개

인덱스 추가 후 데이터 조회해서 성능 측정

해당 쿼리 조회 시 평균 25ms

EXPLAIN 확인

type: 인덱스 레인지 스캔
rows: 1058

EXPLAIN ANALYZE 확인 및 해석

-> Index range scan on users using idx_created_at_department over ('2025-03-20 13:28:33' <= created_at AND 'Sales' <= department), with index condition: ((users.department = 'Sales') and (users.created_at >= <cache>((now() - interval 3 day))))  (cost=476 rows=1057) (actual time=2.62..3.12 rows=124 loops=1)

EXPLAIN ANALYZE의 모든 내용을 자세하게 분석할 실력이 아직 되지 않아서 Claude에게 물어봤다.

over ('2025-03-20 13:28:33' <= created_at AND 'Sales' <= department) 부분은 두 컬럼 모두 인덱스 범위 탐색에 사용되고 있음을 나타냅니다.
또한 key_len의 값이 408로 상당히 큰 값이다. 이 값을 통해 인덱스의 사용 범위를 추론할 수 있다. key_len 값이 408이라는 것은 인덱스의 두 컬럼인 created_at과 department 모두가 인덱스 검색에 사용되고 있다는 것을 의미한다.

멀티 컬럼 인덱스를 사용할 때는 다음과 같은 규칙이 존재한다.

’=’ 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.
멀티 컬럼 인덱스 시 범위 검색 컬럼을 뒤에 둬야 한다.

하지만 현재 인덱스 컬럼 순서는 (created_at, department)로 범위 검색하는 create_at 컬럼은 앞쪽에 있다.

이론적으로는 다음과 같다.

B-Tree 인덱스에서 범위 조건을 만나면, 해당 조건 이후 인덱스 컬럼은 효율적으로 사용할 수 없다.
범위 조건은 여러 값에 걸쳐 있어서, 그 다음 컬럼의 정렬 순서를 활용할 수 없기 때문이다.

옵티마이저에 의해 created_at, department 두 컬럼 모두 사용되었음을 알 수 있지만, 정말로 효율적인 인덱스 활용인지는 모르겠다.

범위 검색 컬럼을 뒤에 둔 인덱스 생성 및 성능 측정

  
ALTER TABLE users DROP INDEX idx_created_at_department;  
CREATE INDEX idx_department_created_at ON users (department, created_at);

대략 20ms
(created_at, department) 순서로 생성한 인덱스를 탄 쿼리는 25ms였다.

별 차이가 없다.

EXPLAIN & EXPLAIN ANALYZE확인

EXPLAIN ANALYZE

-> Index range scan on users using idx_department_created_at over (department = 'Sales' AND '2025-03-20 14:02:49' <= created_at), with index condition: ((users.department = 'Sales') and (users.created_at >= <cache>((now() - interval 3 day))))  (cost=40.8 rows=90) (actual time=0.28..1.63 rows=90 loops=1)

EXPLAIN

(department, created_at) 인덱스는 rows가 90이다.
(created_at, department) 인덱스는 rows가 1058이다.

rows는 SQL문 수행을 위해 접근하는 데이터의 모든 행의 수(데이터 액세스 수)이다. 이 값을 줄이는게 SQL 튜닝의 핵심이다.

결론

’=’ 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.
멀티 컬럼 인덱스 시 범위 검색 컬럼을 뒤에 둬야 한다.
(A, B) 순으로 카디널리티가 높아도 A가 범위 검색한다면 (B, A)로 범위 검색 컬럼을 뒤에 둬야 한다.

참고 문서

https://www.inflearn.com/course/%EB%B9%84%EC%A0%84%EA%B3%B5%EC%9E%90-mysql-%EC%84%B1%EB%8A%A5%EC%B5%9C%EC%A0%95%ED%99%95-sql%ED%8A%9C%EB%8B%9D/dashboard
https://www.percona.com/blog/correct-index-choices-for-equality-like-query-optimization/
https://www.brainbackdoor.com/infra-workshop/sql-improvement

개인공부

db sql index