이 글은 인프런의 MySQL 성능 최적화 입문/실전 (SQL 튜닝편) (박재성) 강의를 듣고 개인적으로 정리하는 글임을 알립니다.
MySQL 아키텍쳐
MySQL의 아키텍처를 간단하게 표현하자면 위와 같다.
- 클라이언트가 DB에 SQL 요청을 보낸다.
- MySQL 엔진에서 옵티마이저가 SQL문을 분석한 뒤 빠르고 효율적으로 데이터를 가져올 수 있는 계획을 세운다. 어떤 순서로 테이블에 접근할 지, 인덱스를 사용할 지, 어떤 인덱스를 사용할 지 등을 결정한다.
(옵티마이저가 세운 계획은 완벽하지 않다. 따라서 SQL 튜닝이 필요하다.) - 옵티마이저가 세운 계획을 바탕으로 스토리지 엔진에서 데이터를 가져온다.
(DB 성능에 문제가 생기는 대부분의 원인은 스토리지 엔진으로부터 데이터를 가져올 때 발생한다. 데이터를 찾기가 어려워서 오래 걸리거나, 가져올 데이터가 너무 많아서 오래 걸린다. SQL 튜닝의 핵심은 스토리지 엔진으로부터 되도록이면 데이터를 찾기 쉽게 바꾸고, 적은 데이터를 가져오도록 바꾸는 것을 말한다.) - MySQL 엔진에서 정렬, 필터링 등의 마지막 처리를 한 뒤에 클라이언트에게 SQL 결과를 응답한다.
MySQL 튜닝의 핵심
-스토리지 엔진에서 데이터를 찾기 쉽게 바꾸기
-스토리지 엔진으로부터 가져오는 데이터의 양 줄이기
이 두 가지를 해결할 수 있는 방법은 여러가지 방법이 많지만 가장 많이 활용되는 방법이 인덱스 활용이다.
인덱스(Index)의 개념
데이터베이스에서 인덱스는 책의 목차와 비슷한 역할을 한다고 보면 된다.
인덱스를 사용하면 특정 데이터를 더 빠르게 찾을 수 있게 된다.
데이터베이스에서 많은 양의 데이터가 저장되어 있을 때, 인덱스가 없다면 데이터베이스는 처음부터 끝까지 하나하나 데이터를 확인하면서 원하는 데이터를 찾아야 한다.
하지만 인덱스를 사용하면 훨씬 적은 데이터를 검색해서 원하는 결과를 빠르게 찾을 수 있다.
인덱스란 "데이터를 빨리 찾기 위해 특정 컬럼을 기준으로 미리 정렬해놓은 표"라고 할 수 있다.
인덱스를 직접 생성하게 되면 우리 눈에는 안 보이지만 왼쪽과 같은 표가 시스템 내부적으로 생성된다.
나이를 기준으로 정렬해놓은 표를 가지고 있기 때문에, 나이를 기준으로 데이터를 조회할 때 훨씬 빠르게 찾을 수 있었던 것이다.
인덱스의 개념
- 인덱스는 데이터베이스 테이블의 특정 열(컬럼)에 대한 추가적인 데이터 구조이다.
- 이 구조는 해당 열의 값들을 빠르게 조회할 수 있도록 정렬되어 있으며, 해당 값이 저장된 레코드가 테이블의 어느 위치에 있는지 정보를 담고 있다.
- 즉, 인덱스는 데이터를 조회할 때 사용하며, 특정 조건에 맞는 데이터를 빠르게 찾기 위해 사용된다.
인덱스의 동작
- 인덱스 생성: 특정 열에 대해 인덱스를 생성하면, 데이터베이스는 그 열의 값을 기반으로 빠르게 검색할 수 있는 자료 구조(예: B-tree)를 만든다.
- 데이터 조회 시 인덱스 사용: 데이터를 조회할 때 해당 열에 인덱스가 있다면, 데이터베이스는 인덱스를 먼저 확인해서 그 열에 해당하는 값이 저장된 정확한 위치를 빠르게 찾아간다.
- 인덱스 없는 조회: 인덱스가 없다면 데이터베이스는 테이블 전체를 처음부터 끝까지 스캔하며 원하는 데이터를 찾아야 한다. 이를 “Full Table Scan”이라고 한다.
인덱스의 장점
- 검색 성능 향상: 데이터를 빠르게 검색할 수 있다. 특히, 큰 테이블에서 특정 조건을 만족하는 데이터를 찾을 때 매우 유용하다.
- 정렬 및 그룹화 속도 향상: ORDER BY, GROUP BY와 같은 작업을 수행할 때도 인덱스를 활용하면 빠르게 처리할 수 있다.
인덱스의 단점
- 추가적인 저장 공간 필요: 인덱스 자체가 별도의 데이터 구조이기 때문에, 이를 저장할 추가 공간이 필요하다.
- 데이터 수정 시 성능 저하: 데이터를 INSERT, UPDATE, DELETE할 때, 인덱스도 함께 수정해야 하므로 성능이 떨어질 수 있다. 따라서 너무 많은 인덱스를 사용하면 오히려 성능이 저하될 수 있다.
- 인덱스는 주로 데이터 검색 시 유리하지만, 자주 변경되는 데이터를 다룰 때는 오히려 성능에 악영향을 미칠 수 있다.
언제 인덱스를 사용해야 하는가?
- 많은 조회가 이루어지는 열: 데이터 조회가 자주 발생하는 열에 인덱스를 사용하는 것이 좋다. 예를 들어, SELECT 쿼리에서 자주 조건으로 사용하는 열(WHERE 절에 자주 등장하는 열).
- 유일한 값이 많은 열: 인덱스는 중복 값이 많을수록 성능이 떨어질 수 있다. 따라서 유일한 값이 많을수록 인덱스 효과가 크다. 예를 들어, 고유한 ID를 저장하는 primary key 열.
- 정렬과 그룹화에 자주 사용되는 열: ORDER BY나 GROUP BY 절에서 자주 사용되는 열에도 인덱스를 사용할 수 있다.
인덱스 설정 방법
인덱스 생성
# 인덱스 생성
# CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);
CREATE INDEX idx_age ON users(age);
인덱스 조회
# SHOW INDEX FROM 테이블명;
SHOW INDEX FROM users;
인덱스의 종류와 자동 생성
인덱스의 종류
MySQL에서 사용되는 인덱스의 종류는 크게 클러스터형 인덱스와 보조 인덱스로 나뉜다.
이 두 개를 비유하면 클러스터형 인덱스는 영어 사전과 같은 책이다.
영어사전은 단어들이 알파벳 순서로 정렬되어 있다. 이 정렬된 상태 덕분에 우리는 특정 단어를 빠르게 찾을 수 있다. 예를 들어, “apple”을 찾으려면 사전의 앞부분에서부터 찾고, “zebra”는 사전의 뒷부분을 바로 열어 찾으면 된다.
이 사전의 알파벳 순서로 정렬된 방식이 클러스터 인덱스와 비슷하다.
보조 인덱스는 책의 목차와 비슷한 개념이다.
목차는 본문의 내용과는 별도로 위치 정보(페이지 번호)를 제공하는데, 목차에서 내용을 찾으면 바로 해당 페이지로 이동할 수 있다. 목차 자체는 본문과 별도로 관리된다.
클러스터 인덱스 (Clustered Index)
- 클러스터 인덱스는 테이블의 데이터 자체가 정렬되는 방식이다.
- 즉, 테이블의 행(row)들이 인덱스 기준으로 실제 데이터 파일에 정렬되어 저장된다.
- 하나의 테이블에 하나의 클러스터 인덱스만 존재할 수 있다, 왜냐하면 테이블 자체가 해당 인덱스를 기준으로 정렬되어 저장되기 때문이다.
- 클러스터 인덱스가 설정된 열(column)에 따라 데이터가 물리적으로 정렬된다. 이로 인해 해당 열로 데이터를 검색할 때 매우 빠르게 찾을 수 있다.
- Primary Key는 일반적으로 클러스터 인덱스로 사용된다. 예를 들어, 학생 테이블에서 학생 ID가 Primary Key라면, 테이블은 학생 ID 순서대로 정렬되어 저장된다.
보조 인덱스 (Non-clustered Index)
- 보조 인덱스는 테이블 데이터 자체와는 별도로 만들어진 인덱스 구조이다.
- 보조 인덱스는 테이블의 데이터와는 물리적으로 분리되어 있으며, 실제 데이터의 위치를 가리키는 포인터 역할을 한다.
- 하나의 테이블에 여러 개의 보조 인덱스를 생성할 수 있다. 각 보조 인덱스는 특정 열에 대해 데이터 조회 속도를 높여준다.
- 보조 인덱스를 통해 빠르게 검색한 후, 그 인덱스가 가리키는 위치로 가서 실제 데이터를 가져오는 방식이다.
- Unique 제약 조건이 걸린 컬럼은 일반적으로 보조 인덱스로 사용된다.
클러스터 인덱스와 보조 인덱스 비교
클러스터형 인덱스 | 보조 인덱스 |
테이블의 데이터 자체가 인덱스 기준으로 정렬됨 | 인덱스는 데이터와 분리되어 별도로 존재함 |
한 테이블에 하나만 존재 가능 | 여러 개의 보조 인덱스를 생성할 수 있음 |
데이터가 실제로 인덱스 순서대로 저장(정렬)됨 | 인덱스는 실제 데이터를 가리키는 포인터를 제공 |
주로 Primary Key에 사용 | 자주 조회되는 특정 컬럼에 사용 |
자동으로 생성되는 인덱스
-테이블 생성 시에 제약 조건 Primary Key 또는 Unique를 사용하면 자동으로 인덱스가 생성된다.
-Primary Key 컬럼에는 클러스터형 인덱스가 생성되고, Unique 컬럼에는 보조 인덱스가 생성된다.
인덱스를 많이 사용하면 무조건 좋은가?
인덱스의 자료구조는 B-Tree이다.
B-Tree는 인덱스를 구현하는 데 매우 효율적인 자료구조지만, 인덱스를 너무 많이 사용하면 다음과 같은 문제점이 발생할 수 있다
- 데이터 수정 시 성능 저하: 인덱스가 많을수록 데이터 수정 시 더 많은 B-Tree가 업데이트되어 성능이 떨어짐.
- 추가적인 저장 공간 필요: 인덱스가 많을수록 더 많은 B-Tree 구조를 저장해야 하므로 디스크 공간이 많이 사용됨.
- 인덱스 선택 문제: 너무 많은 인덱스가 있으면 최적의 인덱스를 선택하는 데 시간이 소요될 수 있음.
- Full Table Scan 발생 가능성: 너무 많은 인덱스가 있으면 오히려 인덱스를 사용하지 않고 테이블 전체를 스캔하는 경우도 발생할 수 있음.
인덱스는 최소한으로 사용하는 것이 중요하다.
인덱스를 많이 사용할수록 조회 속도는 빨라지나, 쓰기(삽입, 수정, 삭제) 속도는 느려지기 때문이다.
멀티 컬럼 인덱스(Multiple - Column Index)
멀티 컬럼 인덱스 개념
멀티 컬럼 인덱스는 여러 개의 컬럼(열)을 하나의 인덱스로 묶어서 관리하는 인덱스이다.
이를 통해 여러 컬럼에 걸친 데이터 조회를 최적화할 수 있다. 즉, 하나의 인덱스가 여러 컬럼을 포함함으로써, 특정 쿼리가 여러 컬럼을 동시에 검색할 때 성능을 높이는 역할을 한다.
멀티 컬럼 인덱스 생성
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
이름 VARCHAR(100),
부서 VARCHAR(100),
나이 INT
);
CREATE INDEX idx_부서_이름 ON users (부서, 이름);
멀티 컬럼 인덱스란, 2개 이상의 컬럼을 묶어서 설정하는 인덱스를 뜻한다. 즉, 데이터를 빨리 찾기 위해 2개 이상의 컬럼을 기준으로 미리 정렬해놓은 표이다.
아래와 같은 users 테이블이 있다고 가정하자.
이 때, 부서와 이름의 컬럼을 활용해 멀티 컬럼 인덱스를 만들면 아래와 같은 표가 시스템 내부에 생성된다. 부서를 기준으로 먼저 오름차순으로 정렬한 뒤, 같은 부서의 값을 가진 데이터들 사이에서 이름을 기준으로 오름차순 정렬을 한다.
위의 예시에서는 컬럼 2개를 가지고 인덱스를 생성했지만, 2개 이상의 컬럼을 가지고 인덱스를 생성할 수도 있다.
멀티 컬럼 인덱스 주의점
일반 인덱스처럼 활용 가능
부서를 기준으로 먼저 정렬이 되어 있고, 그 다음 같은 부서 내에서 이름을 기준으로 정렬되어 있다.
이런 구조로 되어 있기 때문에 부서 컬럼만 놓고 봤을 때는 부서 인덱스와 동일한 정렬 상태를 갖고 있다. 따라서 위의 멀티 컬럼 인덱스의 구조를 활용하면 부서의 인덱스를 활용하듯이 쓸 수도 있다.
SELECT * FROM users
WHERE 부서 = '운영';
위 SQL문을 봤을 때 부서 컬럼으로 인덱스를 생성할 경우 성능이 향상되리라 짐작할 수 있다. 하지만 부서, 이름 순으로 구성된 멀티 컬럼 인덱스를 이미 만들어 뒀기 때문에, 부서 컬럼의 인덱스를 따로 또 만들 필요는 없다.
일반 인덱스처럼 활용하지 못하는 경우도 존재
위에서 부서, 이름 순으로 멀티 컬럼 인덱스를 만들어뒀기 때문에, 부서 컬럼의 인덱스를 만든 것과 같은 역할도 같이 수행한다고 했다.
하지만 이 멀티 컬럼 인덱스로는 이름 컬럼의 인덱스처럼 활용할 수는 없다.
왜인지 아래 인덱스 표를 다시 한 번 확인해보자.
정렬을 자세히 잘 살펴보면 이름 기준으로 정렬이 되어 있지는 않다.
왜냐면 같은 부서를 가진 데이터끼리만 정렬을 시켰기 때문이다. 실제로 아래 SQL문을 실행시킬 때 인덱스를 활용하지 못한다.
SELECT * FROM users
WHERE 이름 = '이재현';
따라서 멀티 컬럼 인덱스에서 일반 인덱스처럼 활용할 수 있는 건 처음에 배치된 컬럼들뿐이다.
대분류 → 중분류 → 소분류 컬럼순으로 구성
멀티 컬럼 인덱스를 만들 때는 순서에 주의해야 한다.
왜냐하면 순서를 어떻게 정해서 인덱스를 만드느냐에 따라서 성능 차이가 나기 때문이다.
먼저 직관적으로 이해해보자. 10층짜리 회사에서 박미나를 찾아야 한다고 가정하자.
일반적으로 부서를 먼저 찾은 뒤에 박미나를 찾는게 훨씬 빠를 것이다. 이를 일반화해서 표현하자면 "대분류를 먼저 탐색한 뒤, 소분류를 탐색하는 게 빠르다."라고 할 수 있다. 컴퓨터도 이 특성이 동일하게 적용된다.
멀티 컬럼 인덱스에서도 배치한 컬럼의 순서대로 데이터를 탐색한다. (부서, 이름)의 순서대로 멀티 컬럼 인덱스를 구성했다면 먼저 일치하는 부서를 찾은 뒤, 일치하는 부서에서 이름을 찾는 식으로 처리한다.
따라서 멀티 컬럼 인덱스를 구성할 때는 데이터 중복도가 높은 컬럼이 앞쪽으로 오는 게 좋은 경우가 많다. 데이터 중복도가 높은 컬럼일수록 큼지막하게 분류가 되어 있다는 뜻이기도 하다.
(항상 그런 건 아니니 실행 계획과 SQL문 실행 속도를 측정해서 판단하도록 하자.)
- 멀티 컬럼 인덱스 컬럼의 순서는 매우 중요하다.
- 멀티 컬럼 인덱스에서 처음에 배치된 컬럼들은 일반 인덱스처럼 활용할 수 있다.
- 멀티 컬럼 인덱스를 구성할 때 데이터 중복도가 높은 컬럼이 앞쪽으로 오는 게 좋다.
커버링 인덱스(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)라고 표현한다.
'데이터베이스 > MySQL' 카테고리의 다른 글
[DB 성능 최적화] 기본 SQL 튜닝 (0) | 2024.08.23 |
---|---|
[DB 성능 최적화] 실행 계획(Explain) (0) | 2024.08.22 |
[MySQL] 인덱스(Index) (1) | 2023.12.06 |
[MySQL] 뷰(View) (1) | 2023.12.05 |
[MySQL] SQL 테이블 제약조건(기본키, 외래키, 고유키) (3) | 2023.12.04 |