[MySQL 성능 최적화] 데이터베이스 튜닝 실습 3 - 멀티 컬럼 인덱스 생성 및 성능 측정

iiingkeep·2024년 7월 21일

Database

목록 보기

5/21

멀티 컬럼 인덱스를 생성해 데이터베이스 튜닝하기

목표

100만개의 데이터를 삽입한 테이블에서 멀티 컬럼 인덱스를 생성하기 전과 후의 데이터 조회 소요시간을 측정해 조회 성능을 비교한다.

성능 측정 수치

소요시간(ms)

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

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

과정

핵심 요약

users테이블에서 부서번호(dep)가 3이고 나이(age)를 기준으로 오름차순 정렬한 데이터 조회
부서번호(dep)와 나이(age) 컬럼에 대해 멀티 컬럼 인덱스를 생성
users테이블에서 부서번호(dep)가 3이고 나이(age)를 기준으로 오름차순 정렬한 데이터 조회

테이블 생성 및 확인

DROP TABLE IF EXISTS users;

 CREATE TABLE users (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100),
     dep INT,
     age INT
 );

 select * from users

users 테이블에 100만개의 데이터 삽입
부서번호는 1-5까지의 정수, 나이는 1-1000까지의 정수로 설정했다.

 SET SESSION cte_max_recursion_depth = 1000000; 

 -- 더미 데이터 삽입
 INSERT INTO users (name, dep, age)
 WITH RECURSIVE cte (n) AS
 (
   SELECT 1
   UNION ALL
   SELECT n + 1 FROM cte WHERE n < 1000000 
 )
 SELECT 
     CONCAT('User', LPAD(n, 7, '0')),   
     FLOOR(1 + RAND() * 5) AS dep,
     FLOOR(1 + RAND() * 1000) AS age    
 FROM cte;


 SELECT COUNT(*) FROM users;

멀티 컬럼 인덱스 생성 전 조회 성능 측정
: users테이블에서 부서번호(dep)가 3이고 나이(age)를 기준으로 오름차순 정렬한 데이터 조회
```
SELECT * FROM users
WHERE dep = 3 
ORDER BY age;
```
부서번호(dep)와 나이(age) 컬럼에 대해 멀티 컬럼 인덱스를 생성
```
CREATE INDEX idx_dep_age ON users (dep, age);

show index from users;
```
멀티 컬럼 인덱스 생성 후 조회 성능 측정
: users테이블에서 부서번호(dep)가 3이고 나이(age)를 기준으로 오름차순 정렬한 데이터 조회
```
SELECT * FROM users
 WHERE dep = 3
 ORDER BY age;
```