[MySQL 성능 최적화] 데이터베이스 튜닝 실습 9 - ORDER BY문이 사용된 SQL문 튜닝

iiingkeep·2024년 7월 25일

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

Database

목록 보기

11/21

ORDER BY문이 사용된 SQL문 튜닝

목표

100만개의 데이터를 삽입한 테이블에서 급여(salary)를 오름차순으로 정렬하여 100개의 데이터를 조회할 수 있도록 ORDER BY문이 사용된 SQL문을 튜닝해본다.

성능 측정 수치

소요시간(ms)

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

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

과정

테이블 생성 및 확인

DROP TABLE IF EXISTS users; 

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

users 테이블에 100만개의 데이터 삽입 및 확인

 SET SESSION cte_max_recursion_depth = 1000000; 

 INSERT INTO users (name, department, salary, 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, 
     FLOOR(1 + RAND() * 1000000) AS salary, 
     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
ORDER BY salary
LIMIT 100;

# 실행 계획 조회
EXPLAIN SELECT * FROM users
ORDER BY salary
LIMIT 100;

# 실행 계획 세부 내용 조회
EXPLAIN ANALYZE SELECT * FROM users
ORDER BY salary
LIMIT 100;

salary 컬럼에 대해 인덱스 생성

CREATE INDEX idx_salary ON users (salary);

SHOW INDEX FROM users;

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

# 성능측정
 SELECT * FROM users
 ORDER BY salary
 LIMIT 100;
 
 # 실행 계획 조회
 EXPLAIN SELECT * FROM users
 ORDER BY salary
 LIMIT 100;
 
 # 실행 계획 세부 내용 조회
 EXPLAIN ANALYZE SELECT * FROM users
 ORDER BY salary
 LIMIT 100;

결과

인덱스 생성 전

평균 소요시간 772.4ms
type: ALL ➡️ Full Table Scan
rows: 996,636 ➡️ 996,636개의 데이터에 접근
세부 내용: users 테이블에 대해 Full Table scan 진행 후 정렬한 뒤 100개 데이터 조회

인덱스 생성 후

평균 소요시간 34.8ms
type: index ➡️ Index Scan
key: idx_salary ➡️ 사용한 인덱스
rows: 100 ➡️ 100개의 데이터에 접근
세부 내용: users 테이블에 대해 Index Scan 진행 후 100개 데이터 조회

결론

ORDER BY문으로 정렬하여 조회하는 작업 시 거의 모든 데이터에 접근하여 정렬하는 작업이 필요하기 때문에 이는 시간이 오래 걸리는 작업이다. 실행 계획 세부 내용을 확인해보면 처음에 Full Table Scan을 진행하면서 모든 데이터에 접근하고, 661ms가 소요되는 것을 확인할 수 있었다. 이후에 정렬하는 작업을 따로 진행하며 213ms가 추가적으로 소요되어 총 874ms의 시간이 소요되었다.

하지만 salary 컬럼을 기준으로 인덱스를 생성한 뒤 조회하면 생성해둔 Index에 정렬되어있는 100개의 데이터에만 접근하여 76ms가 소요되었으며 따로 정렬하는 작업을 거치지 않아도 됐다. 이는 약 22분의 1로 소요 시간이 단축되는 효과를 보였다.

결론적으로, 데이터를 정렬하여 조회해야 할 경우 해당 컬럼을 기준으로 인덱스를 생성한 뒤 조회하는 것이 성능 측면에서 훨씬 효율적인 작업이라는 것을 확인 할 수 있었다.