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만 건의 랜덤 데이터 삽입
-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
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 정도의 시간이 걸린다.
실행 계획 조회해보기
# 실행 계획
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인걸 보니 풀 테이블 스캔을 했다. 비효율적이다. rows가 996,810인 걸 보니 전체 데이터를 대부분 액세스 했다는 걸 추측할 수 있다.
→ 이 때, 액세스한 데이터의 개수는 1e+6(= 10의 6제곱 = 1,000,000)개 이다.
액세스한 1,000,000개의 데이터 중 department = ‘Sales’와 created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)을 만족하는 데이터를 필터링해온다.
→ 조건을 만족한 데이터의 개수는 rows는 114개이다.
성능 개선을 위한 인덱스 추가
인덱스를 추가하는 방법이 3가지가 있다.
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);
기존 200ms 응답 속도에서 20ms로 줄어들었으니 약 10배 정도의 성능이 향상됐다.
type이 range로 나온걸로 봐서 인덱스 레인지 스캔을 했다. 그리고 rows가 1043이다. 1043개의 데이터에 액세스를 한 것이다.
-> Filter: (users.department = 'Sales')
(cost=520 rows=115) (actual time=0.151..8.31 rows=100 loops=1)
-> Index range scan on users using idx_created_at
over ('2024-06-29 06:30:15' <= created_at),
with index condition: (users.created_at >= <cache>((now() - interval 3 day)))
(cost=520 rows=1043) (actual time=0.0541..8.12 rows=1043 loops=1)
idx_created_at 인덱스를 활용해 인덱스 레인지 스캔을 했다.
→ created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)을 만족하는 데이터를 뽑아낸다.
→ 이 때, 액세스한 데이터의 개수는 1043개(rows=1043)이다.
액세스한 1043개의 데이터 중 users.department = ‘Sales’을 만족하는 데이터를 필터링해온다.
→ 조건을 만족하는 데이터 개수는 100개이다. (rows=100)
[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);
기존 200ms 응답 속도에서 140ms로 성능이 조금 향상됐다.
type이 ref인 걸로 봐서는 비고유 인덱스로 조회를 했다는 걸 알 수 있다. 여기까지는 크게 문제가 없는데 rows가 191,314(추정값)로 크게 잡혀있다. 즉, 데이터에 191,314번 액세스 했다는 뜻이다. 데이터 액세스를 많이 하면 할수록 시간이 오래 걸려 성능에 안 좋은 영향을 미친다. 이런 이유 때문에 created_at에 인덱스를 걸었을 때보다 성능이 덜 향상됐다. EXPLAIN ANALYZE를 통해서도 확인해보자.
-> Filter: (users.created_at >= <cache>((now() - interval 3 day)))
(cost=8900 rows=63765) (actual time=3.49..121 rows=114 loops=1)
-> Index lookup on users using idx_department (department='Sales')
(cost=8900 rows=191314) (actual time=0.32..111 rows=100000 loops=1)
idx_department 인덱스를 활용해 department = ’Sales’를 만족시키는 데이터를 뽑아낸다.
→ 이 때, 액세스한 데이터의 개수는 100,000개이다. (rows=100000)
액세스한 100,000개의 데이터 중 users.created_at >= <cache>((now() - interval 3 day))을 만족하는 데이터를 필터링해온다.
→ 조건을 만족하는 데이터 개수는 114개이다.
[created_at, department 컬럼 둘 다 인덱스 생성]
# 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);
기존 200ms 응답 속도에서 20ms로 줄어들었으니 약 10배 정도의 성능이 향상됐다.
하지만 실행 계획의 결과를 보면 key(실제 사용한 인덱스)에 idx_created_at 밖에 없다. 그 이유는 인덱스가 idx_department, idx_created_at 이렇게 둘 다 있지만 idx_created_at만 사용해서 조회하는 게 빠르다고 판단한 것이다.
-> Filter: (users.department = 'Sales')
(cost=515 rows=219) (actual time=0.411..10.3 rows=114 loops=1)
-> Index range scan on users using idx_created_at
over ('2024-06-29 07:11:17' <= created_at),
with index condition: (users.created_at >= <cache>((now() - interval 3 day)))
(cost=515 rows=1143) (actual time=0.123..10.1 rows=1143 loops=1)
created_at 컬럼 인덱스만 생성한 결과와 동일하다.
따라서 실질적으로 성능 향상에 큰 효과가 있는 created_at 컬럼에 한해서만 인덱스를 생성하는 게 효율적이다.
⭐
[이것만은 꼭 기억해두자!]
데이터 액세스(rows)를 크게 줄일 수 있는 컬럼은 중복 정도가 낮은 컬럼이다. 따라서 중복 정도가 낮은 컬럼을 골라서 인덱스를 생성해라.
✅ 멀티 컬럼 인덱스 고려하기
(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);
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);
약 30ms 정도 걸린다.
(department, created_at) 멀티 컬럼 인덱스 생성
ALTER TABLE users DROP INDEX idx_created_at_department;
CREATE INDEX idx_department_created_at ON users (department, 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);
약 30ms 정도 걸린다.
위 2가지 멀티 컬럼 인덱스를 활용해서 설정해봤지만, created_at 인덱스만 걸었을 때와 크게 성능 차이가 없다. 이런 경우에는 굳이 멀티 컬럼 인덱스를 사용하지 않고 단일 컬럼에만 인덱스를 적용시키는 게 낫다.
⭐
[이것만은 꼭 기억해두자!]
- ’단일 컬럼에 설정하는 일반 인덱스’를 설정했을 때와 ‘멀티 컬럼 인덱스를 설정했을 때’의 성능 차이가 별로 나지 않는다면, 멀티 컬럼 인덱스를 사용하지 말고 일반 인덱스를 활용하자.
