[MySQL] MySQL 성능 개선의 핵심, 인덱스 이해하기

인덱스란?

---

특정 컬럼을 기준으로 데이터를 빠르게 조회하기 위해 사용하는 자료구조이다.

 

일반적으로 인덱스는 B-Tree 구조로 구성되어있어 이진 탐색 방식을 활용하여 데이터를 검색하는 방식이다.인덱스를 생성하면 원본 테이블을 기준으로 새롭게 정렬된 노드 형태를 띄게되며, 자신이 정한 특정 컬럼값들만 저장하고, 그 값이 있는 row를 가리키는 포인터를 가지게 된다.

 

CREATE INDEX idx_salary ON employee(salary);

위와 같은 인덱스를 생성할 경우 아래와 같은 B-Tree 구조를 띄게 된다.

salary를 기준으로 정렬된 인덱스 테이블이 생기게 되는 것이아니라 노드 구조를 생성한다는 것이다.

각 노드에는 salary 값만 들어있게 되고 각 leaf 노드에는 실제 row의 주소 또는 실제 row 전체가 들어있다.

엔진	인덱스 종류	내부 구조
InnoDB	기본 PK 인덱스 (클러스터드)	B+Tree, row data 직접 포함
InnoDB	보조 인덱스 (secondary)	B+Tree, leaf에 PK pointer 포함

 

지정된 컬럼값 중심의 트리구조를 띄며, 정렬과 이진 트리 방식으로 되어있기 때문에 빠르게 검색을 수행할 수 있다.

 

클러스터링 인덱스와 비클러스터링 인덱스

---

클러스터링 인덱스는 데이터 자체를 정렬하여 저장하는 방식으로 MySQL에서는 Primary Key가 클러스터링 인덱스로 설정되어있으며 물리적으로 데이터가 정렬되어 저장된다.

 

기본적으로 테이블을 생성하여 우리가 PK를 지정해주면 해당 PK의 값을 기준으로 오름차순 정렬된걸 볼 수 있다.

 

즉 원본 데이터 자체가 정렬되는 인덱스를 보고 클러스터링 인덱스라고 부른다. PK(Primary Key)를 클러스터링 인덱스라고 생각해도 된다.  클러스터링 인덱스는 PK밖에 없기 때문이다.

 

비클러스터링 인덱스는 원본 데이터와 별도로 정렬된 자료구조를 의미한다.

 

실제 데이터 즉 PK로 생성된 테이블이 정렬되는 것이 아닌, 인덱스 테이블만 정렬된다. CREATE INDEX로 생성한 인덱스 테이블은 모두 비클러스터링 인덱스이다.

 

조회 속도를 높여주지만 쓰기 작업시 추가로 인덱스를 업데이트 해야하므로 성능 저하의 원인이 될 수 있기 때문에, 너무 많은 인덱스를 생성하게 되면 오히려 쓰기 작업 성능이 저하 될 수 있다.

 

그 이유는 원본 테이블에 새로운 데이터가 삽입된다거나 수정 및 삭제가 일어날 경우 이는 인덱스에서도 똑같은 작업이 일어남과 동시에 정렬작업이 추가적으로 일어나기 때문에 불필요하게 많은 인덱스는 오히려 DB 병목현상을 증가시키는 원인이 되기도 한다.

커버링 인덱스란?

---

SQL문 실행 시 필요한 모든 데이터를 인덱스만으로 처리할 수 있는 경우를 말한다.

CREATE INDEX idx_name_age ON users(name, age);

SELECT name, age FROM users WHERE name = '박미나';

 

위와 같이 name과 age 순으로 인덱스를 생성하고, 해당 select 발생 시킬 경우 인덱스에서 생성한 컬럼들로만 조회를 하게되므로 테이블을 직접 조회하지 않아도 데이터를 반환 할 수 있게 되는데, 이는 디스크 접근을 최소화하여 성능을 극대화 할 수 있는 방법중에 하나이다.

EXPLAIN 명령어

---

SQL 실행 계획을 분석하는데 사용되는 명령어이다.

실행 계획을 통해 인덱스가 제대로 사용되고 있는지, 아니면 다른 방식으로 데이터를 조회해오는지를 확인할 수 있는 명령어이다.

 

EXPLAIN SELECT * FROM users WHERE name = '박미나';

type, possible_keys, key, rows, Extra 등등의 정보가 나오게 되는데

• type → 조회 방식을 뜻한다 (ALL, index, ragne, ref, const)
• possible_keys → 사용 가능한 인덱스 목록을 뜻한다.
• key → 실제로 사용된 인덱스를 뜻한다.
• rows → 조회된 행의 개수를 뜻하며 해당 값이 낮을 수록 효율적이다.
• Extra → 추가 정보를 뜻하며 Using index, Using filesort 등등 여러가지 정보를 나타내며, 해당 정보를 보고 커버링 인덱스인지 아니면 디스크 내의 정렬 기능을 사용중인지를 확인할 수 있다.

Type	설명
ALL	풀 테이블 스캔. 인덱스를 사용하지 않고 모든 데이터를 조회. 가장 비효율적.
index	풀 인덱스 스캔. 인덱스를 처음부터 끝까지 검색. 효율적이지만 최선은 아님.
const	Primary Key 또는 Unique Key로 단일 행을 정확히 조회. 매우 빠름.
range	인덱스를 사용해 범위 조회 (BETWEEN, IN, LIKE). 효율적.
ref	비고유 인덱스 사용. 중복이 있는 값이라도 인덱스를 사용하여 검색.

 

Extra Value

키워드	처리 위치	처리 방식
Using where	서버	row 가져온 후 조건 직접 검사
Using filesort	서버	메모리/디스크에 정렬 버퍼 사용
Using temporary	서버	중간 결과를 임시 테이블로 저장
Using join buffer	서버	조인 조건을 메모리 버퍼에 저장 후 루프
Range checked...	서버	row마다 조건 적용, 비효율적
Full scan on NULL key	서버	인덱스 사용 못함 → 풀스캔
Materialize	서버	서브쿼리 결과를 캐싱 테이블에 저장

인덱스 사용 유무	EXTRA 출력	해석
없음	Using where	전체 스캔 후 서버에서 조건 검사
인덱스 있음	Using index condition; Using where	조건 일부는 인덱스에서, 일부는 서버에서 평가
커버링 인덱스 있음	Using index; Using where	테이블 접근 없이 인덱스 + 서버 조건 검사
커버링 인덱스 + 정확 조건	Using index	인덱스만으로 모든 처리 완료 (최고 성능)

 

인덱스 활용을 막는 잘못된 SQL 작성법

---

간혹 인덱스를 설정했음에도 불구하고, 인덱스를 사용하지 않고 조회를 해오는 경우도 있다.

 

이는 인덱스로 설정한 컬럼들을 모두 사용하지 않았다거나, 인덱스 컬럼을 가공한 경우이다.

SELECT * FROM users WHERE YEAR(birth_date) = 2000;

해당 쿼리 같은 경우 YEAR라는 함수를 사용한다. 이는 인덱스 컬럼인 birth_date를 함수를 사용함으로써 가공한 경우에는 인덱스를 사용하지 않는다. 해당 경우 적절한 부등호를 사용하는 것으로 변경하여야 한다.

 

 

CREATE INDEX idx_dept_name ON users(department, name);
SELECT * FROM users WHERE name = '박미나';  -- 인덱스 사용 불가

해당 경우에는 인덱스 생성시 department, name으로 컬럼을 생성했는데 정작 조회 쿼리부분에서는 인덱스 컬럼을 사용하지 않았기 때문에 작동하지 않는다.

 

성능을 개선하기 위해서는 가장 중요한 것은 인덱스 활용을 최적화 하는 것이다.

너무 많은 인덱스를 생성하는 것 또한 안되며, EXPLAIN 혹은 EXPLAIN ANALYZE 명령어를 통해 실행 계획을 분석하는 것이 바람직하다.

 

적절한 인덱스를 선정하는 방법은 Where, Join, OrderBy에 사용되는 컬럼들을 EXPLAIN 명령어를 사용하여 type은 어떻게 되는지 rows는 어떻게 되는지를 꼼꼼하게 따져가며 선정해야 한다.

Where에 인덱스 걸기 vs OrderBy에 인덱스 걸기

---

실험 결과 order by 의 특징상 모든 데이터를 바탕으로 정렬을 해야하기 때문에 인덱스 풀 스캔 또는 테이블 풀 스캔을 활용할 수 밖에 없다.

 

이 때문에 order by 문보다 where문에 있는 컬럼에 인덱스를 걸었을 때 성능이 향상되는 경우가 많다.

→ where문에 있는 컬럼에 인덱스를 걸면 전체 데이터를 바탕으로 데이터를 조회하기 전에 먼저 필터링해서 데이터의 양을 줄여서 가지고 오기 때문에 order by 문에 인덱스를 걸어 전체 데이터를 정렬하는 것보다 이 작은 양의 데이터를 정렬시키는 것이 효율적이다.\

 

결론

---

우리가 인덱스를 통해 얻고자 하는 것은 “내가 원하는 데이터를 더 빠르게 가지고 오는 것”이다.

그러기 위해서는 더 적은 컬럼을 검사해야 하는 것이며, Where 조건을 빠르게 만족시키는 것이 본질이다.