[MySQL 성능 최적화] 데이터베이스 튜닝 실습 7 - WHERE문이 사용된 SQL문 튜닝2

iiingkeep·2024년 7월 24일

Database

목록 보기

9/21

WHERE문이 사용된 SQL문 튜닝2

목표

100만개의 데이터를 삽입한 테이블에서 Sales 부서이면서 최근 3일 이내에 가입한 유저만 조회할 수 있도록 WHERE문이 사용된 SQL문을 튜닝해본다.

성능 측정 수치

소요시간(ms)

성능 개선 시 성능이 개선 됐는지 정확한 판단을 하기 위해 개선 이전의 수치와 개선 이후의 수치를 정확히 측정해서 비교할 것

쿼리를 여러 번 실행해 평균적으로 어느 정도의 시간이 소요되는지 측정

과정

테이블 생성 및 확인

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

 select * from users

users 테이블에 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,
     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
 FROM cte;


 SELECT COUNT(*) FROM users;

인덱스 생성 전 조회 성능 측정 및 실행 계획 조회

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

인덱스 생성 및 인덱스별 성능 측정과 실행계획 조회 - 5가지 경우의 수

created_at 컬럼을 기준으로 인덱스 생성
department 컬럼을 기준으로 인덱스 생성
created_at과 department 컬럼을 기준으로 각각 둘 다 인덱스 생성
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);

department 컬럼 기준

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

created_at과 department 컬럼 기준 모두 인덱스 생성

CREATE INDEX idx_department ON users (department);
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);

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

department, created_at 멀티 컬럼 인덱스

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

결과

인덱스 생성 전

평균 소요시간 601.2ms
type: ALL ➡️ Full Table Scan
rows: 996,810 ➡️ 996,810개의 데이터에 접근

created_at 컬럼 기준 인덱스 생성 후

평균 소요시간 26.6ms
type: range ➡️ Index Range Scan
possible_keys: idx_created_at ➡️ 사용 가능한 인덱스 목록
key: idx_created_at ➡️ 실제 사용한 인덱스
rows: 1,106 ➡️ 1,106개의 데이터에 접근

department 컬럼 기준 인덱스 생성 후

평균 소요시간 659.4ms
type: ref ➡️ 비고유 Index Scan
possible_keys: idx_department ➡️ 사용 가능한 인덱스 목록
key: idx_department ➡️ 실제 사용한 인덱스
rows: 191,314 ➡️ 191,314개의 데이터에 접근

created_at과 department 컬럼 기준 각각 인덱스 생성 후

평균 소요시간 30.2ms
type: range ➡️ Index Range Scan
possible_keys: idx_department, idx_created_at ➡️ 사용 가능한 인덱스 목록
key: idx_created_at ➡️ 실제 사용한 인덱스
rows: 1,106 ➡️ 1,106개의 데이터에 접근

(created_at,department) 멀티 컬럼 인덱스 생성 후

평균 소요시간 25ms
type: range ➡️ Index Range Scan
possible_keys: idx_created_at_department ➡️ 사용 가능한 인덱스 목록
key: idx_created_at_department ➡️ 실제 사용한 인덱스
rows: 1,105 ➡️ 1,105개의 데이터에 접근

(department, created_at) 멀티 컬럼 인덱스 생성 후

평균 소요시간 22.4ms
type: range ➡️ Index Range Scan
possible_keys: idx_department_created_at ➡️ 사용 가능한 인덱스 목록
key: idx_department_created_at ➡️ 실제 사용한 인덱스
rows: 96 ➡️ 96개의 데이터에 접근

결론

조건이 하나만 더 늘어나도 인덱스를 생성해 성능 개선이 가능한 경우의 수가 상당히 증가했는데, 각각의 경우의 수를 모두 실행해봄으로써 어떤 조건이 가장 효율적인지 직접 테스트 해 봤다.

먼저, created_at 컬럼은 생성된 바로 그 순간을 기록한 데이터이기 때문에 고유성이 짙다. 때문에 이 컬럼을 기준으로 인덱스를 생성했을 때 소요시간이 약 23분의 1로 단축되며 성능이 많이 개선되는 것을 확인 할 수 있었다.

반면 department 컬럼은 고유성이 옅다. 접근한 데이터 수도 created_at 컬럼을 기준으로 했을 때 보다 많았다. 의아한 점은 인덱스를 생성하기 전보다는 rows 값이 훨씬 줄었는데, 소요 시간은 향상되지 않았다는 것이다. 이 부분에 대해 더 알아 볼 필요가 있다.

또한 created_at과 department 컬럼 모두에 대해 각각 인덱스를 생성하거나 멀티 컬럼 인덱스를 적용한 결과 created_at 컬럼 기준 인덱스를 생성했을 때와 성능 차이가 거의 나지 않는다. 이런 경우 불필요하게 추가적인 인덱스 생성을 하지 않고 created_at 기준의 인덱스만 생성해 사용하면 되겠다.

결론적으로는 고유한 데이터가 많은 컬럼을 기준으로 인덱스를 생성하는 것이 가장 효율적으로 성능을 개선할 수 있는 방법임을 알게 됐다. 멀티 컬럼 인덱스를 사용하는 것이 더 좋을지는 아직 나만의 생각으로 추측하기 어려우므로 직접 테스트를 진행해 비교해보고 사용 유무를 판단해야겠다.