지금까지 진행해 온 프로젝트들은 실제 운영 환경에서 장기간 서비스를 하며 대규모 데이터가 축적된 형태는 아니었기 때문에, DB 성능 저하를 직접적으로 경험해 볼 기회는 많지 않았다.
하지만 최근 프로젝트 고도화를 준비하면서 성능 최적화에 대한 관심이 생겼고, 더미 데이터를 활용해 DB 성능을 개선하는 방법을 학습하며 관련 경험을 쌓고 있다.
나는 DBA가 아닌 백엔드 개발자이지만, 최근 기술 면접에서는 인덱스나 실행 계획과 같은 개념, 혹은 DB 성능 최적화 경험에 대해 질문을 받는 경우도 충분히 있을 수 있다고 생각한다.
또한 DBA가 없는 조직에 입사하게 되거나, DBA와 협업하더라도 기본적인 이해와 소통 능력은 백엔드 개발자가 갖추어야 할 필수 소양이라고 판단해 본격적으로 학습을 시작했다.
현업에서 MySQL이나 Oracle과 같은 관계형 데이터베이스를 사용하다 보면, 서비스가 성장하면서 데이터가 점점 쌓이고 그로 인해 성능 저하를 경험하는 상황은 한 번쯤 반드시 마주하게 된다.
DB 성능이 저하되면 가장 먼저 체감하는 것은 서비스를 이용하는 사용자들이다. 로딩 지연은 곧 사용자 경험의 저하로 이어지고, 이는 서비스 이탈로 연결될 수 있으며, 결국 회사 입장에서는 큰 손실로 이어질 수 있다. (실제로 나조차도 메이플 랜드를 할 때 렉이 심해서 접은 적이 있다.)
성능을 떨어뜨리는 요인들은 다양하겠지만 대표적으로 3가지 경우가 있다.
- 동시 사용자 수의 증가
- 데이터 양의 증가
- 비효율적인 SQL문 작성
DB 성능을 개선하는 다양한 방법에는 아래와 같다.
그중에서도 SQL 튜닝이 추가적인 시스템 구축/관리 없이 개선 가능하다 (=비용적으로 유리하다.)
- SQL 튜닝
- 캐싱 서버 활용(Redis 등)
- 레플리케이션 (Master/Slave 구조)
- 샤딩
- 스케일업 (CPU, Memory, SSD 등 하드웨어 업그레이드)
MySQL 아키텍쳐
- 클라이언트가 DB에 SQL요청을 보낸다
- MySQL 엔진에서 옵티마이저가 SQL문을 분석하고, 요구하는 데이터들을 효율적으로 가져올 수 있는 방안을 계획한다.
- 계획에는 어떤 순서로 어떤 테이블에 접근할지, 인덱스를 사용할지, 어떤 인덱스를 사용할 지 등이 포함된다.
- 옵티마이저가 세운 계획을 바탕으로 스토리지 엔진에서 데이터를 가져온다.
- MySQL엔진에서 정렬, 필터링 등 마지막 처리를 한 후 클라이언트에게 결과를 응답한다.
단, 옵티마이저가 세운 계획이 완벽하지 앉아서 스토리지엔진으로부터 데이터를 가져올 때 데이터를 찾기 어렵거니, 가져올 데이터가 너무 많아서 오래 걸리는 경우가 발생할 수 있으므로 SQL 튜닝으로 쉽게, 적은 데이터를 가져올 수 있도록 바꿔주어야 한다.
인덱스 활용
인덱스란?
데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료구조
= 데이터를 빨리 찾기 위해 특정 컬럼을 기준으로 미리 정렬 해놓은 표지만 시스템 내부적으로 생성되므로 눈으로 확인하는 것은 어렵다. (PK는 직접 확인이 가능하다.)
성능 개선은 이전값과 이후값을 정확히 측정하는 것이 중요하다.
기본으로 설정되는 인덱스 (PK)
PK가 있는 테이블은 기본적으로 PK를 기준으로 정렬되어 저장하는 특징이 있다.
PK는 인덱스의 일종이기 때문이다. 또한 유일하게 원본 데이터가 정렬되므로 클러스터링 인덱스라고 불린다.
UNIQUE 제약조건을 추가하면 자동으로 생성되는 인덱스(UNIQUE)
UNIQUE 제약 조건은 인덱스의 원리를 활용하기 때문에 자동으로 고유 인덱스가 생성된다.
즉 Unique 옵션을 사용하면 인덱스가 같이 생성되기 때문에 조회 성능이 향상된다.
멀티컬럼 인덱스(Multi Column Index)
첫번째 컬럼을 기준으로 1차 정렬되고, 인덱스의 두번째 컬럼은 첫번째 컬럼을, 세번째 컬럼은 두번째 컬럼을 의존해 정렬된다. 1차 정렬된 인덱스는 일반 인덱스처럼 활용될 수 있으니 중복으로 만들지 않아야 하고, 두번째나 세번째 컬럼은 인덱스를 재활용할 수 없다. 따라서 순서를 소분류 → 중분류 → 대분류 컬럼 순으로 구성해야 한다. (데이터 중복도가 낮은 컬럼이 앞쪽으로 오는게 좋은 경우가 많다.)
커버링 인덱스(Covering Index)
조회에 필요한 모든 컬럼을 갖고 있는 인덱스를 말한다.
인덱스에 없는 값에 접근할 때는 실제 테이블에 접근해야하지만, 인덱스 안에 있는 컬럼을 조회할 때는 인덱스에만 접근을 한다. 이경우 조회 속도가 빠르다.
무식하게 많은 인덱스를 생성한다면?
조회성능은 증가하지만 쓰기작업의 성능이 떨어지게 된다. 수정시 원본 테이블과 인덱스 표의 데이터를 둘다 수정해야하므로 느려질 수 밖에 없다. 데이터가 많이 쌓여있을 수록 점점 삽입 속도가 느려진다. 그러니 최소한의 인덱스만을 사용하기 위해 고려해야 한다.
실행 계획
실행계획(EXPLAIN)이란
옵티마이저가 SQL문을 어떤 방식으로 처리할 지를 계획한 것으로 비효율적으로 처리하는 방식이 있는지 점검하고, 더 효율적인 방법으로 실행되게 튜닝하면 된다.
-- 실행계획 조회하기 : EXPLAIN 키워드 사용
EXPLAIN SELECT * FROM 테이블명
-- 실행계획 단계별 조회: ANALYZE 키워드 사용
EXPLAIN ANALYZE SELECT * FROM 테이블명
| 항목 | 설명 |
|---|---|
id | 실행 순서 |
type | 조회 방식 |
possible_keys | 사용 가능한 인덱스 |
key | 실제 사용한 인덱스 |
ref | 조인 컬럼 |
rows | 접근 데이터 수 (줄일수록 튜닝 효과) |
filtered | WHERE 조건으로 걸러진 행 비율 |
Extra | 부가 정보 |
| type | 방식 | 설명 | 효율 |
|---|---|---|---|
ALL | 풀 테이블 스캔 | 인덱스 미사용, 처음부터 끝까지 전체 탐색 | 최하 |
index | 인덱스 풀 스캔 | 인덱스 테이블 전체 탐색 | 낮음 |
ref | 비고유 인덱스 스캔 | 유니크하지 않은 인덱스 컬럼으로 조회, 동일값 여러 건 반환 가능 | 보통 |
range | 인덱스 레인지 스캔 | 부등호 / LIKE / IN 등 범위 조회, 범위가 크면 비효율 | 보통 |
const | 단건 직접 접근 | 고유 인덱스 / PK로 1건 바로 조회 | 최상 |
효율 순서 : ALL < index < ref ≈ range < const
