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

iiingkeep·2024년 7월 24일

Sql튜닝 db dbeaver mysql sql 데이터베이스 성능개선 성능최적화 인덱스

Database

목록 보기

8/21

WHERE문이 사용된 SQL문 튜닝1

목표

100만개의 데이터를 삽입한 테이블에서 최근 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 created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY); 
  
  EXPLAIN SELECT * FROM users
		WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

created_at 컬럼에 대해 인덱스 생성

CREATE INDEX idx_created_at ON users (created_at);

		SHOW INDEX FROM users;

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

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

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

결과

인덱스 생성 전

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

인덱스 생성 후

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

결론

조회에 필요한 created_at 컬럼에 대해 인덱스를 생성하여 성능 개선을 시도하니 인덱스를 생성하기 전보다 인덱스를 생성한 후의 소요시간이 약 19분의 1로 단축되는 것을 확인했다.

또한 실행 계획을 조회해봄으로써 소요시간이 단축된 요인을 알 수 있었는데, 인덱스 생성 전에 풀 테이블 스캔으로 거의 100만개에 달하는 모든 데이터에 접근하여 조회하던 것에 비해 인덱스를 생성한 후 그 인덱스를 스캔하여 접근한 데이터 수가 확연히 줄어든 것을 볼 수 있었다. 심지어 접근한 데이터 수와 조회한 데이터 수가 같음으로써 접근한 데이터에서 버려지는 데이터 하나 없이 모든 데이터가 조회되었음을 알 수 있었다.