[DB] 인덱스 생성 · 조회 · 삭제 및 실행 계획 보기(+ 인덱스로 정렬 작업 성능 개선하기)

인프런 <김영한의 실전 데이터베이스 - 기본편> 강의를 토대로 정리한 내용입니다.

 

---

- 목차 -

1. 인덱스란?

2. 인덱스 사용하기

   1) 인덱스 생성, 조회, 삭제

   2) 인덱스 실행 계획 보기

3. 인덱스가 사용되는 상황

   1) 동등 비교

   2) 범위 검색

    3) LIKE 범위 검색

4. 인덱스로 ORDER BY 성능 개선하기

인덱스란?

인덱스란 특정 컬럼(들)의 데이터를 기반으로 생성되는, 원본 테이블과는 별개의 특수한 자료구조이다. 인덱스를 사용하면 풀 테이블 스캔을 사용하지 않아서 빠른 검색이 가능하다. 그 이유는 인덱스 내부의 데이터는 항상 정렬된 상태를 유지하기 때문이다.

🔎 풀 테이블 스캔(Full Table Scan)이란?
풀 테이블 스캔은 말 그대로 테이블을 처음부터 끝까지 탐색한다는 뜻이다. 따라서 데이터가 많으면 많을수록 검색 시간이 정비례해서 늘어난다. 찾으려는 데이터가 우연히 첫 번째 행에 있다면 한 번의 비교만으로 끝나겠지만, 맨 마지막 행에 있다면 데이터의 수만큼 비교를 해야하는 상황이 된다. 이런 풀 테이블 스캔은 느린 검색의 원인이 되고, 매우 비용이 높은 작업이므로 서비스의 핵심 기능에서 가급적 풀테이블 스캔이 발생하지 않도록 설계해야 한다.

 

만약 item_name 컬럼에 인덱스를 생성하고 다음 쿼리를 실행하면 데이터베이스는 어떻게 동작할까?

SELECT * FROM items WHERE item_name = '게이밍 노트북';

1. 풀 테이블 스캔 대신, item_name 기반의 인덱스를 찾아간다.
2. 정렬되어 있기 때문에 '게이밍 노트북'이라는 값을 빠르게 찾아낸다.
3. 찾아낸 인덱스 항목에서 실제 데이터의 행 위치 값을 확인한다.
4. 원본 테이블의 해당 위치로 점프해서 단번에 원하는 데이터를 가져온다.

이런 식으로 인덱스를 사용해 기본적인 성능 최적화를 할 수 있다.

 

인덱스는 트리 자료 구조를 사용하기 때문에 이런 빠른 검색이 가능한데, 이전 발행 글 중 이진 탐색 트리에 대해 정리한 글이 있어 첨부한다. 핵심은 데이터를 입력하는 시점에 정렬해서 보관한다는 점이다. 따라서 검색 시에도 찾으려는 데이터보다 작은 값은 왼쪽, 큰 값은 오른쪽으로 가면서 빠르게 찾아낼 수 있다.

[CS 발표_07] 완전 이진 트리, 포화 이진 트리, 이진 탐색 트리란?

인덱스 사용하기

인덱스 생성, 조회, 삭제

인덱스를 생성, 조회, 삭제하는 명령어는 다음과 같다.

CREATE INDEX 인덱스이름 ON 테이블이름(컬럼1, 컬럼2, ...);

SHOW INDEX FROM 테이블이름;

DROP INDEX 인덱스이름 ON 테이블이름;

인덱스 실행 계획 보기

아래와 같이 인덱스를 만들었을 때, 해당 인덱스가 진짜 사용되고 있는지 어떻게 알 수 있을까?

CREATE INDEX idx_items_item_name ON items (item_name);

 

인덱스를 만들었다고 무조건 그 인덱스가 사용되는 것은 아니다. 쿼리 옵티마이저가 풀 테이블 스캔이 더 빠르겠다고 판단하면 사용되지 않을 수도 있다. 인덱스가 정말 사용되는지 확인하려면 다음과 같이 EXPLAIN 명령어를 사용하면 된다.

EXPLAIN SELECT * FROM items WHERE item_name = '게이밍 노트북';

 

우선, 인덱스가 없을 때는 다음과 같은 결과가 나온다. 

인덱스가 사용되고 있지 않을 때

• type : ALL 은 풀 테이블 스캔을 의미한다. 인덱스를 제대로 사용했다면 ref, range 등 다른 값이 표시된다.
• key : NULL 은 쿼리를 실행할 때 어떤 인덱스도 사용하지 못했다는 뜻이다.
• rows : 25 는 옵티마이저가 쿼리를 처리하기 위해 탐색할 것으로 예측하는 행의 수이다. 현재 item 테이블의 전체 데이터가 25개이므로, 모든 행을 탐색할 것으로 예측하고 있다.
• filtered : 10.00 은 25개의 행을 모두 읽고 그중 10%인 2.5개 정도의 행이 WHERE절 조건을 만족할 것이라고 예측하고 있음을 의미한다.
• Extra : Using where 은 데이터를 가져온 후에 WHERE절의 조건을 사용해 필터링 작업을 수행했다는 의미다. 인덱스를 사용하면 NULL이 나온다.

다음은 idx_items_item_name 이라는 인덱스를 만든 후 실행한 결과다. 항목 결과가 꽤 달라진 것을 볼 수 있다.

인덱스가 사용될 때

• type : ref 는 = 조건이나 JOIN에서 인덱스를 사용했다는 의미다. 만약 range라면 범위 검색(BETWEEN, >, <, >= 등)에서 인덱스를 사용했다는 의미다.
• key : idx_items_item_name 는 쿼리 실행에 idx_items_item_name 인덱스가 사용되었음을 명확히 보여준다.
• rows : 1 을 보면, 옵티마이저가 쿼리를 처리하기 위해 단 한 개의 행만 탐색할 것으로 예측하고 있다. 이 값이 작을수록 효율적인 쿼리라고 할 수 있다.
• filtered : 100.00 은 인덱스를 통해서 스캔한 1개의 행을 100% 선택한다는 뜻이다.
• Extra : NULL 은 데이터를 가져온 후 별도의 필터링 작업이 필요 없다는 뜻이다.

인덱스가 사용되는 상황

동등 비교

동등 비교의 경우, 위에서 EXPLAIN  명령어를 설명할 때 함께 설명했다.

type이 ref 라면 인덱스를 사용해 동등 비교(=) 조건으로 데이터를 찾았다는 의미다.

범위 검색

범위 검색 역시 비슷한데, type이 range 라면 범위 검색(BETWEEN , > , < , LIKE 등)에 인덱스가 사용된다는 의미다.

범위 검색 인덱스가 어떻게 사용되는지 알아보자.

 

먼저 인덱스 없이 다음 EXPLAIN 명령어를 실행한다.

EXPLAIN SELECT * FROM items WHERE price BETWEEN 50000 AND 100000;

 

결과는 다음과 같다.

범위 검색에서 인덱스가 사용되고 있지 않을 때

• type : ALL 을 보면 풀 테이블 스캔이 발생한 것을 알 수 있다.
• key : NULL 은 price 컬럼을 조건으로 사용했지만, 해당 컬럼에 인덱스가 없어 인덱스를 사용하지 않았다는 의미다.
• rows : 25 는 테이블의 전체 행 수를 예측한 수치로, 풀 테이블 스캔을 했기 때문이다.
• filtered : 11.11 은 전체 행 중에 WHERE price BETWEEN 50000 AND 100000 조건을 만족하는 행은 약 11.11% 일 것이라고 예측한다는 의미다.
• Extra : Using where 은 데이터를 가져온 후에 WHERE 절의 조건을 사용해 필터링했다는 의미다. 인덱스가 없기 때문에 모든 데이터를 다 가져온 후, 조건에 맞는지 일일이 비교하는 방식으로 골라내고 있다.

위와 같이 조건절에 사용되는 컬럼에 인덱스가 없으면 풀 테이블 스캔을 피할 수 없다.

다음은 idx_items_price 인덱스를 만들어 다시 실행 계획을 보자.

CREATE INDEX idx_items_price ON items (price);

 

결과는 다음과 같다.

범위 검색에서 인덱스가 사용되고 있을 때

• type : range 은 인덱스를 사용해 특정 범위의 데이터를 스캔했다는 의미다. 
• key : idx_items_price 는 쿼리 실행에 idx_items_price 인덱스가 사용되었다는 의미다.
• rows : 5 를 보면 기존 25에서 5로 확 줄어들었는데, 옵티마이저가 인덱스를 통해 조건에 맞는 5개의 데이터만 스캔할 것이라고 판단하고, 그 행 수를 5개로 예측한 것이다.
• filtered : 100.00 는 인덱스를 통해서 스캔한 5개의 행을 100% 선택한다는 의미다.
• Extra : Using index condition 는 인덱스 정보만으로 WHERE 조건절을 최대한 필터링한 후, 조건을 만족하는 데이터의 전체 행만 가져왔다는 의미다.

실제 조회 결과

 

인덱스를 사용한 실제 결과를 보면 idx_items_price 인덱스를 사용한 후에는 인덱스 키인 price를 기준으로 정렬되었다는 것을 알 수 있다. 이처럼 인덱스를 사용하면 쿼리 결과의 정렬 순서가 달라질 수 있다. 그러나 이런 순서가 항상 보장되는 것은 아니므로, 정렬이 필요하면 ORDER BY 절을 추가하는 것이 권장된다.

LIKE 범위 검색

LIKE 절에서 인덱스 사용 시, 와일드카드(%)의 위치가 중요하다. 와일드카드(%)는 검색어의 뒤쪽에 와야 한다.

%가 앞에 있으면 시작점이 불분명해져 정렬된 인덱스를 활용할 수 없다.

• WHERE item_name LIKE '게이밍%' : 인덱스 사용 가능
• WHERE item_name LIKE '%게이밍' : 인덱스 사용 불가 → 풀 테이블 스캔 발생
• WHERE item_name LIKE '%게이밍%' : 인덱스 사용 불가 → 풀 테이블 스캔 발생

인덱스로 ORDER BY 성능 개선하기

인덱스는 정렬된 자료구조이기 때문에, 인덱스를 잘 활용한다면 정렬(ORDER BY) 작업의 성능을 개선할 수 있다. 데이터베이스는 이 과정에서 filesort라는 별도의 정렬 작업을 생략할 수 있게 된다.

💾 filesort란?
메모리나 디스크를 사용해 정렬하는 내부 프로세스를 의미한다.

 

인덱스를 활용하지 않은 다음 쿼리의 실행 계획을 보면 filesort를 확인할 수 있다.

EXPLAIN SELECT * FROM items ORDER BY stock_quantity;

 

결과는 다음과 같다.

Extra : filesort인 경우

• Extra : filesort 는 데이터를 모두 찾을 후에 stock_quantity를 기준으로 정렬 작업이 추가되었음을 보여준다.

인덱스로 정렬 처리하기

WHERE절과 ORDER BY절의 정렬 기준이 같으면 인덱스를 스캔하는 것만으로도 검색과 정렬을 한번에 해결할 수 있다.

 

price 컬럼에 idx_items_price 컬럼을 만들어 둔 상태에서, 다음 쿼리를 실행해보자.

EXPLAIN SELECT * FROM items WHERE price BETWEEN 50000 AND 100000 ORDER BY price;

 

결과는 다음과 같다.

인덱스를 사용해 정렬까지 한 번에 처리한 경우아

 

아까와 달리 Using filesort가 없어지고 Using index condition으로 바뀌었다. 데이터베이스 옵티마이저는 idx_items_price가 이미 price 순서로 정렬되어 있다는 것을 알고 있어서, 조건에 맞게 스캔만 해도 자연스럽게 price 순서로 정렬된 결과를 얻을 수 있다. 따라서 WHERE절과 ORDER BY절이 동일한 인덱스를 효율적으로 사용할 수 있다면, 데이터베이스는 정렬을 생략하고, 가장 빠른 방식으로 쿼리를 처리한다.

인덱스 역방향 스캔

데이터베이스 옵티마이저는 역방향 스캔(Backward Index Scan)이 가능하다. 따라서 오름차순 정렬이 아닌 내림차순 정렬을 하더라도 단일 컬럼 인덱스에서는 filesort 없이 처리할 수 있다.

EXPLAIN SELECT * FROM items WHERE price BETWEEN 50000 AND 100000 ORDER BY price DESC;

역방향 스캔을 한 실행 계획

• Extra : Using index; Backward index scan 은 옵티마이저가 인덱스를 역순으로 스캔했음을 의미한다. 인덱스는 양방향 탐색이 가능하기 때문에 WHERE 조건에 맞는 데이터를 찾는 과정에서 이미 정렬 순서가 만족되므로 별도의 filesort 작업이 필요없다.

내림차순 인덱스

MySQL 8.0 버전부터는 내림차순 인덱스(Descending Index)를 정식으로 지원하기 때문에, 정렬 방향과 일치하는 인덱스를 직접 만들 수 있다. 

 

기존 인덱스를 삭제하고, 다음과 같이 내림차순 인덱스를 만들 수 있다.

CREATE INDEX idx_items_price_desc ON items (price DESC);

 

결과는 다음과 같다. Extra 컬럼에서 Backward index scan이 사라진 것을 볼 수 있다. 이제 역방향 스캔 없이 정방향 스캔만하면 된다. 미세한 차이지만, 역방향 스캔이 정방향 스캔보다 좀 더 빠르기 때문에 이렇게 하면 가장 최적화된 버전이라고 할 수 있다.

내림차순 인덱스 적용 결과