MySQL 구조 파악 및 SQL 튜닝의 시작

MySQL의 성능을 많이 잡아먹는 부분이 무엇인지 어떤 요인이 주로 문제인지 파악할 있어야 하고, 그 원인이 무엇인지는 MySQL 구조를 알아야 파악할 수 있다.

 

MySQL의 아키텍처

- MySQL의 아키텍처를 간단하게 표현하면 위와 같다. 아키텍처에서 SQL문이 어떻게 실행되는 지 과정을 알아보면

 

 

비전공자도 이해할 수 있는 MySQL 성능 최적화 입문 - Inflearn

 

 

1. 클라이언트가 DB에 SQL 요청을 보냄 -> MySQL 엔진에서 옵티마이저가 SQL문을 분석한 뒤에 빠르고 효율적으로 데이터를 가져올 수 있는 계획을 세운다. 어떤 순서로 테이블에 접근할 지, 인덱스를 사용할 지 어떤 인덱스를 사용할 지 등을 결정한다.

(옵티마이저가 세운 계획은 완벽하지 않다. SQL 튜닝이 필요하다.)

 

2. 옵티마이저가 세운 계획 바탕으로 스토리지 엔진에서 데이터를 가져온다. -> DB 성능에 문제가 생기는 대부분의 원인은 스토리지 엔진으로부터 데이터를 가져올 때 발생 - > 데이터를 찾기가 어려워서 오래 걸리거나 가져올 데이터가 너무 많아 오래 걸린다. -> SQL 튜닝의 핵심은 스토리지 엔진으로부터 되도록 데이터를 찾기 쉽게 바꾸고, 적은 데이터를 가져오도록 바꾸는 것을 말함.

 

3. 옵티마이저가 세운 계획을 바탕으로 스토리지 엔진에서 데이터를 가져옴 -> DB 성능에 문제가 생기는 대부분의 원인은 스토리지 엔진으로부터 데이터 가져올 때 발생. 데이터를 찾기가 어려워서 오래 걸리거나, 가져올 데이터가 너무 많아서 오래 걸림 -> SQL 튜닝의 핵심은 스토리지 엔진으로부터 데이터를 찾기 쉽게 바꾸고, 적은 데이터를 가져오도록 바꾸는 것을 말한다.

 

4. MySQL 엔진에서 정렬, 필터링 등의 마지막 처리를 한 뒤에 클라이언트에게 SQL 결과를 응답한다.

 

 

SQL 튜닝의 핵심은 인덱스를 적절하게 활용하여 DB 성능이 개선되게끔 하는것이다.

• 스토리지 엔진에서 데이터를 찾기 쉽게 바꾸기
• 스토리지 엔진으로부터 가져오는 데이터의 양 줄이기

 

 

인덱스란

• 인덱스의 정의를 찾아보면 아래와 같다 -> 인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조를 뜻함
• 데이터를 빨리 찾기 위해 특정 컬럼을 기준으로 미리 정렬해놓은 표이다.

 

인덱스를 직접 설정해보고 성능 측정하여 비교해보자.

 

users 테이블을 임시로 생성하고

DROP TABLE IF EXISTS users; # 기존 테이블 삭제

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

 

 

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')),   -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    FLOOR(1 + RAND() * 1000) AS age    -- 1부터 1000 사이의 랜덤 값으로 나이 생성
FROM cte;

-- 잘 생성됐는 지 확인
SELECT COUNT(*) FROM users;

 

 

 

 

데이터를 조회 - SQL문 실행 시 걸리는 소요 시간 측정해보면 대략 200ms 정도 걸린다.

SELECT * FROM users
WHERE age = 23;

 

인덱스 설정하기

인덱스 생성하고

CREATE INDEX idx_age ON users(age);

인덱스 조회
SHOW INDEX FROM users;

 

 

데이터 다시 조회해보고 SQL문 실행 소요 시간 측정해보면

SELECT * FROM users
WHERE age = 23;

 

 

인덱스를 적용 전과 후의 성능 차이가 200ms -> 35ms로 5배 정도 넘게 차이가 난다.

 

 

 

인덱스를 직접 생성하게 되면 나이를 기준으로 정렬해놓은 표를 가지고 있어서 나이를 기준으로 데이터를 조회할 때 훨씬 빠르게 찾을 수 있다.

 

 

<출처 : 박재성 비전공자도 이해할 수 있는 MySQL성능 최적화 입문 / 실전 (SQL 튜닝편)>

https://www.inflearn.com/course/%EB%B9%84%EC%A0%84%EA%B3%B5%EC%9E%90-mysql-%EC%84%B1%EB%8A%A5%EC%B5%9C%EC%A0%95%ED%99%95-sql%ED%8A%9C%EB%8B%9D/dashboard

비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전 (SQL 튜닝편) 강의 | JSCODE 박재성 - 인프런