# mysql 쿼리 튜닝

DB 성능을 개선하는 법

1. SQL 튜닝

2. 캐싱 서버 활용 (Redis 등)

3. 래플리케이션 (Master/Slave 구조)

4. 샤딩

5. 스케일업 (CPU, Memory, SSD 등 하드웨어 업그레이드)

SQL 튜닝은 기존의 시스템 변경없이 성능을 개선할 수있는 가성비 있는 방법이다.

근본적인 문제를 해결하는 방법이다. SQL 자체가 비효율적으로 작성 됐다면 아무리 시스템적으로 성능을 개선한다고 하더라도 한계가 있다.

MySql의 아키텍처

클라이언트가 DB에 SQL 요청을 보낸다.
MySQL 엔진에서 옵티마이저가 SQL문을 분석한 뒤 빠르고 효율적으로 데이터를 가져올 수 있는 계획을 세운다. 어떤 순서로 테이블에 접근할 지, 인덱스를 사용할 지, 어떤 인덱스를 사용할 지 등을 결정한다. (옵티마이저가 세운 계획은 완벽하지 않다. 따라서 SQL 튜닝이 필요하다.)
옵티마이저가 세운 계획을 바탕으로 스토리지 엔진에서 데이터를 가져온다. (DB 성능에 문제가 생기는 대부분의 원인은 스토리지 엔진으로부터 데이터를 가져올 때 발생한다. 데이터를 찾기가 어려워서 오래 걸리거나, 가져올 데이터가 너무 많아서 오래 걸린다. SQL 튜닝의 핵심은 스토리지 엔진으로부터 되도록이면 데이터를 찾기 쉽게 바꾸고, 적은 데이터를 가져오도록 바꾸는 것을 말한다.)
MySQL 엔진에서 정렬, 필터링 등의 마지막 처리를 한 뒤에 클라이언트에게 SQL 결과를 응답한다.

SQL 튜닝의 핵심

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

그럼 이 2가지를 어떻게 해결할 수 있을까?

여러가지 방법이 많지만 가장 많이 활용되는 방법이 인덱스 활용이다.

인덱스(Index)란?

정의: 인덱스(Index)는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조를 뜻한다.

그냥 "데이터를 빨리 찾기 위해 특정 컬럼을 기준으로 미리 정렬해놓은 표" 라고 기억하자.

MySql 쿼리

테이블 생성

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

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

더미데이터 생성

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
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;

인덱스 생성

# 인덱스 생성
# CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);
CREATE INDEX idx_age ON users(age);

인덱스 조회

# SHOW INDEX FROM 테이블명;
SHOW INDEX FROM users;

기본키 (Primary Key, PK)

테이블에서 특정 데이터를 식별하기 위한 키를 보고 기본키(Primary Key, PK)라고 부른다.

대부분의 경우에 테이블을 생성할 때 PK를 설정한다.

PK의 특징 중 하나는 PK를 기준으로 정렬을 해서 데이터를 보관한다.

PK에는 인덱스가 기본적으로 적용된다(클러스터링 인덱스).

PK 포함된 테이블 만들기

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

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

클러스터링 인덱스

pk의 값을 바꾸고 정렬해도 pk기준으로 자동 정렬해서 보여준다.

왜냐하면 PK가 **인덱스**의 일종이기 때문이다.

이렇게 "원본 데이터 자체가 정렬되는 인덱스"를 보고 "클러스터링 인덱스"라고 부른다.

"PK(Primary Key) = 클러스터링 인덱스"라고 생각해도 된다.

클러스터링 인덱스는 PK(Primary Key) 밖에 없기 때문이다.

고유인덱스 (Unique Index)

제약 조건을추가하면 자동으로 생성되는 인덱스(UNIQUE)

MySQL, 오라클은 UNIQUE 제약 조건을 추가하면 자동으로 인덱스가 생성된다.

이는 제약 조건의 무결성을 보장하기 위한 필수 동작이다.

인덱스는 무조건 좋은가

아니다. 인덱스는 읽기 성능(SELECT) 을 향상시키는 대신, 쓰기 성능(INSERT, UPDATE, DELETE) 에는 비용을 발생시킨다.

왜 쓰기 성능이 나빠지는가

인덱스가 존재하면 다음 작업이 추가로 발생한다.

INSERT

→ 테이블에 데이터 삽입

→ 해당 컬럼을 포함한 모든 인덱스에도 키 값 삽입

→ 인덱스 구조(B-Tree 등) 재배치 발생 가능

UPDATE

→ 인덱스 컬럼이 변경되면

→ 기존 인덱스 값 삭제 + 새로운 값 삽입

DELETE

→ 테이블 데이터 삭제

→ 인덱스에서도 해당 키 제거

즉, 인덱스 개수만큼 추가 작업이 발생한다.

언제 인덱스가 오히려 독이 되는가

쓰기 비중이 매우 높은 테이블 (로그, 이력 테이블)

거의 조회되지 않는 컬럼에 인덱스를 건 경우

중복도가 매우 높은 컬럼 (예: Y/N, 상태값)

필요 이상으로 많은 인덱스를 가진 경우

멀티 컬럼 인덱스 (Multiple-Column Index)

2 개 이상의 컬럼을 묶어서 설정하는 인덱스.

데이터를 빨리 찾기 위해 2개 이상의 컬럼을 기준으로 미러 정렬해놓은 표

생성방법

CREATE INDEX idx_부서_이름 ON users (부서, 이름);

주의점

위와 같이 멀티 컬럼 인덱스를 만들면 부서인덱스는 만들 필요가없다.

하지만 이름컬럼 인덱스로는 사용할 수 없다.

따라서 멀티 컬럼 인덱스에서 일반 인덱스 처럼 활용할 수 있는 건 처음에 배치된 컬럼들 뿐이다.

멀티 컬럼 인덱스 순서

1. 가장 중요한 핵심 원칙 (Leftmost Prefix Rule)
멀티 컬럼 인덱스는 왼쪽부터 순서대로 조건이 사용될 때만 효력이 있다.
INDEX (A, B, C)
사용 가능:
WHERE A = ?
WHERE A = ? AND B = ?
WHERE A = ? AND B = ? AND C = ?
WHERE A = ? AND B > ?
사용 불가 또는 비효율:
WHERE B = ?
WHERE C = ?
WHERE B = ? AND C = ?
즉, 앞 컬럼이 빠지면 뒤 컬럼은 무력화된다.

2. 컬럼 순서 결정의 기본 전략
1) WHERE 절에 가장 자주 등장하는 컬럼을 앞에 둔다
인덱스는 결국 조건 필터링을 빠르게 하기 위한 구조이다.
자주 조회 조건에 사용되는 컬럼 → 앞
가끔 사용되는 컬럼 → 뒤

2) 카디널리티(선택도)가 높은 컬럼을 앞에 둔다
값의 종류가 많은 컬럼이 앞에 올수록 효과가 크다.
중복도가 높은 컬럼(Y/N, 상태값 등)은 뒤로 미룬다.

-- 좋음
INDEX (user_id, status)

-- 나쁨
INDEX (status, user_id)

3) = 조건 → 범위 조건(>, <, BETWEEN, LIKE) 순서
범위 조건이 등장하면 그 뒤 컬럼은 인덱스 탐색에 사용되지 않는다.

-- 추천
INDEX (user_id, created_at)

WHERE user_id = ?
  AND created_at >= ?

-- 비추천
INDEX (created_at, user_id)

3. ORDER BY / GROUP BY 고려

ORDER BY 최적화

INDEX (user_id, created_at DESC)

다음 쿼리는 정렬 없이 인덱스 스캔 가능:

WHERE user_id = ?
ORDER BY created_at DESC

※ WHERE 조건 컬럼 → ORDER BY 컬럼 순서가 기본이다.

GROUP BY

- GROUP BY A, B → INDEX (A, B) 가 유리
- 순서가 다르면 인덱스 효과 감소

4. 실무 기준 정리 (우선순위)

멀티 컬럼 인덱스 순서는 다음 우선순위로 결정한다.
1) WHERE 절에서 항상 사용되는 컬럼
2) 카디널리티 높은 컬럼
3) = 조건 컬럼
4) 범위 조건 컬럼
5) ORDER BY / GROUP BY 컬럼

5. 마지막 핵심 요약
- 멀티 컬럼 인덱스는 순서가 절반 이상을 결정한다.
- 왼쪽부터 조건이 끊기면 뒤는 무용지물이다.
- 무조건 많이 넣는 것이 아니라, 대표 쿼리 기준으로 설계한다.
- 반드시 EXPLAIN 으로 실제 사용 여부를 확인해야 한다.

커버링 인덱스(Covering Index)

SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스를 커버링 인덱스 (Covering Index)라고 한다.

위와 같은 users 테이블이 있고,

name 인덱스가 있다고 가정하자. 그리고 아래 2개의 SQL문을 실행해야 한다고 가정하자.

SELECT id, created_at FROM users;
SELECT id, name FROM users;

1번째 SQL문을 보면 `id`, `created_at`라는 컬럼만 조회한다고 하더라도 실제 테이블의 데이터에 접근해야 한다.

하지만 2번째 SQL문에서는 `id`, `name` 컬럼은 실제 테이블에 접근하지 않고 인덱스에만 접근해서 알아낼 수 있는 정보들이다. 따라서 실제 테이블에 접근하지 않고 데이터를 조회할 수 있다. 실제 테이블에 접근하는 것 자체가 인덱스에 접근하는 것보다 속도가 느리다.

이 상황에서 **SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스**를 보고 **커버링 인덱스(Covering Index)**라고 표현한다.

실행계획

옵티마이저가 SQL문을 어떤 방식으로 어떻게 처리할 지를 계획한 걸 의미한다. 이 실행 계획을 보고 비효율적으로 처리하는 방식이 있는 지 점검하고, 비효율적인 부분이 있다면 더 효율적인 방법으로 SQL문을 실행하게끔 튜닝을 하는 게 목표다.

실행 계획을 확인하는 방법

# 실행 계획 조회하기
EXPLAIN [SQL문]

# 실행 계획에 대한 자세한 정보 조회하기
EXPLAIN ANALYZE [SQL문]

실행 계획 조회

EXPLAIN SELECT * FROM users
WHERE age = 23;

- `id` : 실행 순서
- `select_type` : (처음에는 몰라도 됨)
- `table` : 조회한 테이블 명
- `partitions` : (처음에는 몰라도 됨)
- **`type` : 테이블의 데이터를 어떤 방식으로 조회하는 지 ⭐️⭐️⭐️**
- **`possible keys` : 사용할 수 있는 인덱스 목록을 출력 ⭐️**
- **`key` : 데이터 조회할 때 실제로 사용한 인덱스 값 ⭐️**
- `key_len` : (처음에는 몰라도 됨)
- `ref` : 테이블 조인 상황에서 어떤 값을 기준으로 데이터를 조회했는 지
- **`rows` : SQL문 수행을 위해 접근하는 데이터의 모든 행의 수 (= 데이터 액세스 수) ⭐️⭐️⭐️** **→ 이 값을 줄이는 게 SQL 튜닝의 핵심이다!**
- `filtered` : 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는 지 의미 → filtered의 값이 30이라면 100개의 데이터를 불러온 뒤 30개의 데이터만 실제로 응답하는데 사용했음을 의미한다. → filtered 비율이 낮을 수록 쓸데없는 데이터를 많이 불러온 것.
- **`Extra` : 부가적인 정보를 제공 ⭐️** → ex. `Using where`, `Using index`

실행 계획에 대한 자세한 정보 조회하기

EXPLAIN ANALYZE SELECT * FROM users
WHERE age = 23;

- `Table scan on users` : users 테이블을 풀 스캔했다.
    - `rows` : 접근한 데이터의 행의 수
    - `actual time=0.0437..0.0502`
        - `0.0437` (앞에 있는 숫자) : 첫 번째 데이터에 접근하기까지의 시간
        - `0.0502` (뒤에 있는 숫자) : 마지막 데이터까지 접근한 시간
- `Filter: (users.age = 23)` : 필터링을 통해 데이터를 추출했다. 필터링을 할 때의 조건은 `users.age = 23`이다.

users 테이블의 모든 데이터(7개)에 접근했다. 그러고 그 데이터 중 age = 23의 조건을 만족하는 데이터만 필터링해서 조회해왔다.

실행계획 type 컬럼

- all : 풀 스캔

- index : 풀 인덱스 스캔(Full Index Scan)

- const : 고유인덱스, 기본키를 사용할 경우, 가장 효율적인 방식

- range : 인덱스 레인지 스캔 (Index Range Scan), BETWEEN, 부등호(<, >, ≤, ≥), IN, LIKE 를 사용할 경우

이 방식은 인덱스를 활용하기 때문에 효율적인 방식이다. 하지만 인덱스를 사용하더라도 데이터를 조회하는 범위가 클 경우 성능 저하의 원인이 되기도 한다.

- ref : 비고유 인덱스를 활용하는 경우

비고유 인덱스를 사용한 경우 (= UNIQUE가 아닌 컬럼의 인덱스를 사용한 경우) type에 ref가 출력된다.

저작자표시 비영리 변경금지 (새창열림)

chunkind

# mysql 쿼리 튜닝

DB 성능을 개선하는 법

MySql의 아키텍처

SQL 튜닝의 핵심

인덱스 생성

인덱스 조회

기본키 (Primary Key, PK)

PK 포함된 테이블 만들기

클러스터링 인덱스

고유인덱스 (Unique Index)

인덱스는 무조건 좋은가

왜 쓰기 성능이 나빠지는가

언제 인덱스가 오히려 독이 되는가

멀티 컬럼 인덱스 (Multiple-Column Index)

멀티 컬럼 인덱스 순서

커버링 인덱스(Covering Index)

실행계획

티스토리툴바

# mysql 쿼리 튜닝

DB 성능을 개선하는 법

MySql의 아키텍처

SQL 튜닝의 핵심

인덱스 생성

인덱스 조회

기본키 (Primary Key, PK)

PK 포함된 테이블 만들기

클러스터링 인덱스

고유인덱스 (Unique Index)

인덱스는 무조건 좋은가

왜 쓰기 성능이 나빠지는가

언제 인덱스가 오히려 독이 되는가

멀티 컬럼 인덱스 (Multiple-Column Index)

멀티 컬럼 인덱스 순서

커버링 인덱스(Covering Index)

실행계획

'BackEnd Programming/mysql&maria' Related Articles

티스토리툴바