[ MySQL 최적화 ] WHERE문이 사용된 SQL문 튜닝

▤ 목차

• 최근 3일 이내에 가입한 유저 조회하기
• Sales 부서이면서 최근 3일 이내에 가입한 유저 조회하기
  • created_at 인덱스
  • department 인덱스
  • created_at 와 department 인덱스
  • 멀티 인덱스(created_at, department) (department, created_at)
  • 성능테스트 비교 표

 

최근 3일 이내에 가입한 유저 조회하기

데이터 조회

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

 

인덱스를 추가하기전

약 0.35초

tyep = ALL

인덱스 추가 후

CREATE INDEX idx_created_at ON users (created_at);

 

약 0.056초

type = range

 

created_at 에 인덱스를 추가함으로써 검색 타입이 ALL 에서 range 로 바뀌고 소요시간이 약 0.35 초 에서 0.056 초로 감소

 

Sales 부서이면서 최근 3일 이내에 가입한 유저 조회하기

데이터 생성

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
);

 

100만건 데이터 생성 후 데이터 조회해서 성능 측정

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

0.24초

 

실행계획 확인

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

#세부내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

type=ALL -> 모든 테이블을 검색

 

-> Filter: ((users.department = 'Sales') and (users.created_at >= <cache>((now() - interval 3 day))))  (cost=93877 rows=33224) (actual time=3.34..243 rows=115 loops=1)
    -> Table scan on users  (cost=93877 rows=996810) (actual time=0.148..191 rows=1e+6 loops=1)

 

성능개선 할 수 있는 방법들

1. created_at 컬럼을 기준으로 인덱스 추가

2. department 컬럼을 기준으로  인덱스 추가

3. department 와 created_at 두 컬럼에  인덱스 추가

4. 멀티 컬럼 인덱스 = (created_at, department) ? (department, created_at) ?

 

< created_at 컬럼을 기준으로 인덱스 추가 > 

CREATE INDEX idx_created_at ON users (created_at);

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

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

0.24초 에서 0.033 초로 소요시간 단축

type = range , 모든 테이블을 검색하지 않고 idx_created_at 인덱스 레인지 스캔을 함 데이터 접근 개수(rows)도 줄어들었다.

 

< department 컬럼을 기준으로 인덱스 추가 > 

ALTER TABLE users DROP INDEX idx_created_at; # 기존 created_at 인덱스 삭제
CREATE INDEX idx_department ON users (department);

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

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

약 0.13초 소요 created_at 컬럼을 기준으로 인덱스 추가 한 경우가 소요시간이 훨씬 짧음

ref 타입, = 비고유 인덱스로 조회를 했다 rows = 191,314 는 인덱스를 하기전 보다 좋아 졌지만 created_at 컬럼을 기준으로 인덱스 추가  한 경우보다 많은 데이터에 접근

 

 

<department 와 created_at 두 컬럼에  인덱스 추가>

# CREATE INDEX idx_department ON users (department); # 위에서 이미 추가함
CREATE INDEX idx_created_at ON users (created_at); # created_at 인덱스 추가

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

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

소요 시간은 idx_created_at 과 비슷

idx_created_at 과 똑같이 range 타입으로 일정 범위만 검색

 

-> Filter: (users.department = 'Sales')  (cost=498 rows=212) (actual time=0.0929..11.6 rows=114 loops=1)
    -> Index range scan on users using idx_created_at over ('2024-12-27 16:39:14' <= created_at), with index condition: (users.created_at >= <cache>((now() - interval 3 day)))  (cost=498 rows=1106) (actual time=0.062..11.3 rows=1106 loops=1)

디테일을 확인해 보면 idx_created_at 만 사용

 

결론적으로 idx_department 을 사용하지 않았다는 것은 department 컬럼에 한 인덱스가 필요가 없고 판단하여 idx_department는 의미 성능평가에서도 비슷하므로 created_at 컬럼에만 인덱스를 하는 것이 가장 효율적인 최적화 방법으로 볼수 있다.

 

[이것만은 꼭 기억해두자!] 

데이터 액세스(rows)를 크게 줄일 수 있는 컬럼은 중복 정도가 낮은 컬럼이다.

따라서 중복 정도가 낮은 컬럼을 골라서 인덱스를 생성해라.

 

 

< 멀티 컬럼 인덱스 >

공통 : 성능테스트

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

 

 

순서 (created_at, department) 

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

성능 테스트 결과 :  소요시간 약 0.0035 초

 

순서 (department, created_at)

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

 

성능 테스트 결과 : 소요시간 약 0.0033초

 

 

멀티 컬럼 인덱스를 사용했지만 created_at 만 인덱스를 설정했을 때의 성능과 크게 차이가 나지 않는다.

 

 ’단일 컬럼에 설정하는 일반 인덱스’를 설정했을 때와 ‘멀티 컬럼 인덱스를 설정했을 때’의 성능 차이가 별로 나지 않는다면 ?

▶ 멀티 컬럼 인덱스를 사용하지 말고 일반 인덱스를 활용하자.

 

비교한 인덱스 방식의 성능 테스트 비교 표

인덱스 방식	성능 테스트 (소요시간 (s))	rows
인덱스 없음	0.24	115
created_at	0.0033	114
department	0.14	114
created_at 와 department	0.0032	114
멀티 (created_at, department)	0.0035	109
멀티 (department, created_at)	0.0033	109

 

 

참고 강의

(인프런) 비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전