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

iiingkeep·2024년 7월 20일

mysql 데이터베이스 데이터베이스튜닝 성능최적화 성능측정 인덱스 최적화 튜닝

Database

목록 보기

3/21

인덱스를 생성해 데이터베이스 튜닝하기

목표

100만개의 데이터를 삽입한 테이블에서 특정 컬럼을 기준으로 조회시 인덱스를 생성하기 전과 후의 성능을 비교 해 본다.

성능 측정 수치

소요시간(ms)

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

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

과정

데이터베이스 생성
실습을 위해 새로 연결한 MySQL 데이터베이스에 'tuning'이라는 새로운 데이터베이스 생성
Database name, Charset, Collation을 입력 후 확인 클릭
DBeaver에 MySQL 데이터베이스를 연결하는 방법은 아래 포스팅에서 확인 가능하다.
(https://velog.io/@iiingkeep/DBeaver-DBeaver%EC%97%90-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4MySQL-%EC%97%B0%EA%B2%B0%ED%95%98%EA%B8%B0)

데이터베이스에 'users' 새 테이블 생성

create table users(
		 id int auto_increment primary key,
		 name varchar(100),
		 age int
)

select * from users;

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

SET SESSION cte_max_recursion_depth = 1000000; 

INSERT INTO users (name, 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() * 1000) AS age
FROM cte;
	
	
SELECT COUNT(*) FROM users;

마지막 코드를 실행시켜 확인해보면 100만개의 데이터가 들어가 있는 것을 확인 할 수 있다.

성능 테스트를 위해 Result set fetch size를 높여 20000으로 바꿔준다.
이는 조회시 최대로 보여주는 결과값의 갯수이다.
인덱스 생성 전 성능 테스트
성능 테스트를 하기 전, 소요시간을 확인하기 위해 쿼리 관리자를 먼저 띄운다.

나이(age)가 23인 유저 조회. 5번 조회하여 각각 나온 소요시간으로 평균치를 계산한다.
```
SELECT * FROM users
WHERE age = 23;
```
422 / 400 / 412 / 395 / 424 (ms)의 결과값이 나와 평균적으로 410.6ms의 시간 소요

age 컬럼에 인덱스 생성 및 확인

CREATE INDEX idx_age ON users(age);

SHOW INDEX FROM users;

인덱스 생성 후 성능 테스트
다시 나이(age)가 23인 유저를 5번 조회하여 소요시간의 평균 계산
```
SELECT * FROM users
WHERE age = 23;
```
39 / 42 / 38 / 34 / 34 (ms)의 결과값이 나와 평균적으로 37.4ms의 시간 소요