Index | Young Three의 실험실

1. MySQL Index

이번 글에서는 MySQL에서 인덱스를 직접 생성, 조회, 삭제하는 방법과 인덱스가 실제로 사용되는지 확인하는 방법을 알아보겠습니다.

기본 개념 참고

인덱스에 대한 기본적인 내용은 Index 문서를 참고해주세요.

2. CREATE INDEX - 인덱스 생성하기

가장 기본이 되는 인덱스 생성 명령어는 CREATE INDEX입니다.
인덱스를 생성하면 데이터베이스는 지정한 컬럼의 값들을 읽어서 정렬한 뒤, 인덱스 구조를 디스크에 저장합니다.

2.1 기본 문법

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

인덱스 이름: 생성할 인덱스에 고유한 이름을 붙여줍니다. 보통 idx_테이블명_컬럼명과 같은 규칙으로 지으면 관리하기 편합니다.
테이블 이름: 인덱스를 생성할 대상 테이블입니다.
컬럼: 인덱스를 구성할 컬럼입니다. 하나 이상의 컬럼을 지정할 수 있습니다.

2.2 실습 예제

items 테이블에서 특정 item_name으로 상품을 검색하는 경우가 많다고 가정하고, item_name 컬럼에 대한 인덱스를 생성해 보겠습니다.

CREATE INDEX idx_items_item_name ON items (item_name);

인덱스 생성 과정

이 쿼리를 실행하면 데이터베이스는 items 테이블의 모든 item_name 값을 읽어 정렬한 뒤, idx_items_item_name 인덱스를 디스크에 생성합니다. 테이블의 데이터가 많을수록 이 작업은 시간이 오래 걸릴 수 있습니다.

3. SHOW INDEX - 테이블의 인덱스 정보 확인하기

테이블에 어떤 인덱스들이 생성되어 있는지 확인하려면 SHOW INDEX 명령어를 사용합니다.
이 명령어를 통해 테이블에 걸려있는 모든 인덱스의 정보를 한눈에 볼 수 있습니다.

3.1 items 테이블 인덱스 확인

SHOW INDEX FROM items;

실행 결과

Table	Non_unique	Key_name	Column_name	Cardinality
items	0	PRIMARY	item_id	25
items	1	fk_items_sellers	seller_id	10
items	1	idx_items_item_name	item_name	25

결과를 자세히 살펴보겠습니다.
Key_name: 인덱스의 이름입니다. 우리가 방금 만든 idx_items_item_name이 보입니다.
Column_name: 해당 인덱스가 어떤 컬럼을 기반으로 만들어졌는지 보여줍니다.
PRIMARY와 fk_items_sellers: 우리는 item_name 컬럼에 대한 인덱스만 만들었는데 PRIMARY와 fk_items_sellers라는 인덱스가 이미 존재합니다.
Non_unique: 1이면 중복 값을 허용하는 인덱스, 0이면 중복을 허용하지 않는 고유 인덱스(UNIQUE 또는 PRIMARY KEY)를 의미합니다.
Cardinality: 인덱스에 저장된 유니크한 값의 개수에 대한 추정치입니다. 이 값이 높을수록 중복도가 낮다는 의미이며, 인덱스의 성능이 좋다고 판단할 수 있습니다.

자동 생성되는 인덱스

MySQL에서는 PRIMARY KEY(기본 키)나 FOREIGN KEY(외래 키) 제약조건을 설정하면, 해당 컬럼에 대해 자동으로 인덱스를 생성합니다. item_id는 기본 키이므로 PRIMARY 인덱스가, seller_id는 외래 키이므로 fk_items_sellers 인덱스가 이미 존재했던 것입니다. (UNIQUE 제약조건도 마찬가지로 인덱스가 생성됩니다.)

3.2 sellers 테이블 인덱스 확인

SHOW INDEX FROM sellers;

실행 결과

Table	Non_unique	Key_name	Seq_in_index	Column_name	Cardinality
sellers	0	PRIMARY	1	seller_id	10
sellers	0	seller_name	1	seller_name	10

PRIMARY: seller_id 기본 키로 인덱스가 자동 생성되었습니다.
seller_name: seller_name에 UNIQUE 제약조건으로 인덱스가 자동 생성되었습니다.

유니크 제약조건에 인덱스를 자동 생성하는 이유

유니크 제약조건은 컬럼 내 데이터의 유일성을 보장해야 합니다. 따라서 새로운 데이터를 삽입(INSERT)하거나 기존 데이터를 수정(UPDATE)할 때마다, 입력하려는 값이 테이블에 이미 존재하는지 빠르게 확인해야 합니다. 만약 인덱스가 없다면 이 중복 검사를 위해 매번 풀 테이블 스캔이 발생하여 쓰기 성능이 크게 저하될 것입니다.

인덱스 자동 생성 정리

MySQL은 PRIMARY KEY(기본 키), FOREIGN KEY(외래 키), UNIQUE 제약조건에 대해서 인덱스를 자동 생성합니다.

4. DROP INDEX - 인덱스 삭제하기

시간이 지나 더 이상 사용하지 않거나, 오히려 쓰기 성능에 방해가 되는 인덱스는 삭제해야 합니다.
DROP INDEX 명령어로 간단하게 삭제할 수 있습니다.

4.1 기본 문법

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

4.2 실습 예제

방금 만들었던 idx_items_item_name 인덱스를 삭제해 보겠습니다.

DROP INDEX idx_items_item_name ON items;

인덱스 삭제 효과

이 쿼리를 실행하면 idx_items_item_name 인덱스를 구성하던 데이터 구조가 디스크에서 완전히 사라집니다. 이제 item_name을 조건으로 검색하면 다시 풀 테이블 스캔이 발생할 것입니다. 물론, 원본 items 테이블의 데이터에는 아무런 영향이 없습니다.

5. EXPLAIN - 인덱스가 정말 사용되는지 확인하는 법

인덱스를 만들었다고 해서, 데이터베이스가 모든 SELECT 문에 항상 그 인덱스를 사용하는 것은 아닙니다.
데이터의 분포나 쿼리의 형태에 따라, 데이터베이스 옵티마이저는 인덱스를 사용하는 것보다 풀 테이블 스캔이 더 빠르겠다고 판단할 수도 있습니다.
우리가 만든 인덱스가 실제로 쿼리에 사용되는지 확인하려면 EXPLAIN이라는 명령어를 쿼리문 앞에 붙여보면 됩니다.

쿼리 옵티마이저

데이터베이스에는 쿼리를 어떤 방식으로 최적화해서 실행할지 계획하는 기능이 있는데, 이것을 쿼리 옵티마이저(최적화기)라 합니다. 사용할 수 있는 인덱스가 여러 개 있다면 어떤 인덱스를 사용할지도 선택합니다.

5.1 인덱스가 없을 때

idx_items_item_name 인덱스가 없을 때 실행 결과를 확인해보겠습니다.

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

실행 결과

id	type	key	rows	filtered	Extra
1	ALL	NULL	25	10.00	Using where

실행 결과를 통해 데이터베이스가 우리의 쿼리를 어떻게 실행할지 계획했는지, 즉 '실행 계획'을 엿볼 수 있습니다.
각 항목이 무엇을 의미하는지 자세히 살펴보겠습니다.

type: ALL

가장 중요하게 봐야 할 부분입니다.
type은 데이터베이스가 테이블에 어떻게 접근할지를 나타냅니다.
ALL은 **풀 테이블 스캔(Full Table Scan)**을 의미합니다.
즉, items 테이블의 처음부터 끝까지 모든 데이터를 하나씩 다 읽어서 조건에 맞는 데이터를 찾는다는 뜻입니다.

풀 테이블 스캔의 위험성

지금은 데이터가 25개뿐이라 문제가 없지만, 실무에서처럼 데이터가 수백만 건에 달한다면 심각한 성능 저하를 일으키는 주범이 됩니다. ALL이라고 표시되면 풀 테이블 스캔을 의미합니다. 즉, 테이블의 모든 행을 처음부터 끝까지 다 읽었다는 뜻입니다. 인덱스를 제대로 사용했다면 ref, range 등 다른 값이 표시됩니다. ref는 = 조건이나 JOIN에서 인덱스를 사용했다는 의미입니다. range는 범위 검색(BETWEEN, >, <, >=, <= 등)에서 인덱스를 사용했다는 의미입니다.

key: NULL

key는 쿼리를 실행할 때 사용한 인덱스를 보여줍니다. 이 값이 NULL이라는 것은 어떤 인덱스도 사용하지 못했다는 것을 명확하게 알려줍니다.
item_name 컬럼으로 데이터를 찾고 있지만, 해당 컬럼에 인덱스가 없기 때문에 당연한 결과입니다.

rows: 25

rows는 옵티마이저가 쿼리를 처리하기 위해 탐색할 것으로 예측하는 행의 수입니다.
현재 items 테이블의 전체 데이터가 25개이므로, 결국 테이블 전체를 다 훑어보겠다고 말하는 것과 같습니다.
이 값이 작을수록 효율적인 쿼리라 할 수 있습니다.

예측 정보

실제 실행하는 게 아니라 데이터베이스 나름의 통계 데이터를 기반으로 하는 예측 정보입니다. 따라서 정확하게 맞는 것은 아닙니다.

filtered: 10.00

filtered는 테이블에서 읽어온 행들 중에서 WHERE 조건으로 필터링되고 난 후, 최종적으로 남을 것으로 예측되는 행의 비율입니다.
여기서는 25개의 행을 모두 읽은 후, 그중 10%인 2.5개 정도의 행이 item_name = '게이밍 노트북' 조건을 만족할 것이라고 예측하고 있습니다.

Extra: Using where

데이터를 가져온 후에 WHERE 절의 조건(item_name = '게이밍 노트북')을 사용해 필터링 작업을 수행했다는 의미입니다.
만약 인덱스를 효율적으로 사용했다면, 처음부터 조건에 맞는 데이터만 골라서 가져왔을 것입니다.
하지만 인덱스가 없으니 일단 모든 데이터를 다 가져와서, 그 후에 조건에 맞는지 일일이 비교하는 비효율적인 방식으로 일하고 있음을 보여줍니다.

6. 인덱스와 동등 비교

데이터베이스에서 인덱스는 다음 세 가지 상황에 사용됩니다.
- 동등 비교(=)
- 범위 검색(BETWEEN, >, <, >=, <=, LIKE 등)
- ORDER BY를 통한 정렬 작업
type: ref는 동등 비교(=) 조건이나 JOIN에서 인덱스를 사용했다는 의미입니다.
EXPLAIN을 사용해서 동등 비교를 확인해보고, 인덱스가 있을 때와 없을 때의 차이를 살펴보겠습니다.

6.1 인덱스가 있을 때

먼저 items 테이블의 item_name 컬럼에 인덱스를 생성합니다.

CREATE INDEX idx_items_item_name ON items (item_name);

이제 EXPLAIN으로 쿼리 실행 계획을 확인해 보겠습니다.

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

실행 결과

id	type	possible_keys	key	rows	filtered	Extra
1	ref	idx_items_item_name	idx_items_item_name	1	100.00	NULL

인덱스를 생성한 후의 실행 계획은 이전과 확연히 달라진 것을 볼 수 있습니다. 하나씩 분석해 보겠습니다.

type: ref

이전의 ALL과 비교했을 때 가장 극적인 변화입니다.
type이 ref라는 것은, 인덱스를 사용해 동등 비교(=) 조건으로 데이터를 찾았다는 의미입니다.
ref는 'reference(참조)'의 약자로, 인덱스를 통해 조건에 맞는 데이터를 매우 효율적으로 참조해서 가져왔다는 뜻입니다.
풀 테이블 스캔(ALL)과는 비교할 수 없을 정도로 빠른 접근 방식입니다.

possible_keys

현재 쿼리에서 사용 가능한 인덱스의 후보입니다.
지금은 idx_items_item_name 하나만 있지만 현재 쿼리에서 사용가능한 인덱스를 모두 보여줍니다.
이 후보들 중에 선택되어 사용될 인덱스가 다음 key 항목에 나타납니다.

key: idx_items_item_name

이전에는 NULL이었던 이 값에 우리가 방금 생성한 인덱스의 이름(idx_items_item_name)이 명확하게 표시됩니다.
이것은 옵티마이저가 이 쿼리를 실행하는 데 idx_items_item_name 인덱스를 사용했음을 보여주는 직접적인 증거입니다.

rows: 1

이 또한 엄청난 변화입니다. 인덱스가 없을 때는 테이블 전체 행의 수인 25를 스캔할 것으로 예측했지만, 이제는 단 1개의 행만 읽으면 된다고 예측합니다.
마치 책의 맨 뒤에 있는 찾아보기를 통해 '게이밍 노트북'이라는 단어가 있는 페이지를 바로 찾아가는 것과 같습니다.

예측값

EXPLAIN은 실제 SQL 쿼리를 실행하는 것이 아닙니다. 여기서 rows는 예측값입니다. 따라서 환경에 따라 1이 아닌 다른 값이 나올 수도 있습니다.

filtered: 100.00

인덱스를 통해서 찾은 1개의 행을 100% 선택한다는 뜻입니다.

Extra: NULL

이전에 표시되었던 Using where가 사라졌습니다.
이는 인덱스 단계에서 이미 모든 검색 조건이 충족되었기 때문에, 데이터를 가져온 후 별도의 필터링 작업이 필요 없었다는 것을 의미합니다.
그만큼 작업이 더 단순하고 효율적으로 처리된 것입니다.

인덱스 사용의 핵심

item_name 컬럼에 인덱스를 생성하자 데이터베이스 옵티마이저는 풀 테이블 스캔이라는 비효율적인 방법을 버리고, 인덱스를 사용해 단 하나의 행만 읽어오는 매우 효율적인 실행 계획을 세웠습니다. 이것이 바로 우리가 인덱스를 사용하는 핵심적인 이유입니다.

6.2 데이터가 적을 때 주의사항

샘플 데이터가 적을 때

테이블에 데이터가 몇 만 건 이상이면 인덱스를 사용하는 게 이득이지만, 지금처럼 샘플 데이터가 너무 적은 경우 데이터베이스는 환경에 따라 인덱스를 사용하지 않고, 그냥 풀 테이블 스캔을 선택할 수도 있습니다. 예를 들어 2페이지 정도의 작은 책이라면 색인을 찾기보다 그냥 책을 바로 보는 게 원하는 결과를 더 빨리 얻을 수도 있기 때문입니다. 이럴 때 인덱스를 강제로 적용하려면 다음과 같이 FORCE INDEX를 사용하면 됩니다.

EXPLAIN SELECT * FROM items FORCE INDEX (idx_items_item_name)
WHERE item_name = '게이밍 노트북';

이 방법을 사용하면 쿼리 옵티마이저가 최적의 인덱스를 선택할 수 없기 때문에 실무에서는 권장하지 않습니다. 꼭 필요하다면 주의해서 사용해야 합니다.

7. 인덱스와 범위 검색

앞에서 type이 range일 때 범위 검색(BETWEEN, >, <, >=, <=, LIKE 등)에 인덱스가 사용된다고 설명했습니다.
이번에는 items 테이블의 price 컬럼을 사용하여 범위 검색(BETWEEN)에 인덱스가 어떻게 사용되는지 EXPLAIN으로 확인해 보겠습니다.

7.1 인덱스가 없을 때

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

실행 결과

id	type	key	rows	filtered	Extra
1	ALL	NULL	25	11.11	Using where

price 컬럼에 인덱스가 없는 상태에서 범위 검색을 실행한 결과, 비효율적인 실행 계획이 세워졌습니다.
type: ALL: 풀 테이블 스캔이 발생했습니다.
- 데이터베이스는 price가 50000에서 100000 사이인 상품을 찾기 위해 items 테이블의 모든 상품 데이터를 처음부터 끝까지 하나씩 확인해야만 합니다.
key: NULL: price 컬럼을 조건으로 사용했지만, 이 컬럼에는 인덱스가 없으므로 사용된 인덱스가 없다는 의미의 NULL이 표시됩니다.
rows: 25: 테이블의 전체 행 수인 25가 표시됩니다. 풀 테이블 스캔을 하므로 당연한 결과입니다.
filtered: 11.11: 스캔한 25개의 행 중에서 WHERE price BETWEEN 50000 AND 100000 조건을 만족하는 행은 약 11.11%일 것이라고 옵티마이저가 예측하고 있습니다.
Extra: Using where: 데이터를 가져온 후에 WHERE 절의 조건을 사용해 필터링 작업을 수행했다는 의미입니다.
풀 테이블 스캔 상태에서 실제 쿼리를 실행해서 결과를 확인해보겠습니다.

SELECT * FROM items WHERE price BETWEEN 50000 AND 100000;

실행 결과

item_id	seller_id	item_name	category	price
3	2	프리미엄 게이밍 마우스	전자기기	80000
6	5	고급 가죽 지갑	패션	70000
8	6	캐시미어 스웨터	패션	95000
14	2	인체공학 키보드	전자기기	90000
19	7	친환경 주방 세트	생활용품	60000

결과 정렬 순서

쿼리 실행 결과를 보면 item_id 순서로 정렬된 것을 확인할 수 있습니다. 테이블은 item_id 순서(테이블에 데이터가 물리적으로 저장된 순서)대로 정렬되어 있기 때문에 풀 테이블 스캔 과정에서 WHERE 조건을 만족한 순서대로 결과가 나옵니다.

하지만 이 순서를 데이터베이스가 보장하는 것은 아닙니다. 그냥 내부 실행 과정에 따라서 이 순서가 되었을 뿐입니다. 만약 item_id 조건으로 정렬해야 한다면 ORDER BY item_id를 추가하는 것을 권장합니다.

7.2 인덱스가 있을 때

이제 items 테이블의 price 컬럼에 인덱스를 생성하겠습니다.

CREATE INDEX idx_items_price ON items (price);

인덱스를 생성하면 price 컬럼의 값 순서로 인덱스가 만들어집니다.
이제 EXPLAIN으로 쿼리 실행 계획을 확인해 보겠습니다.

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

실행 결과

id	type	key	rows	filtered	Extra
1	range	idx_items_price	5	100.00	Using index condition

price 컬럼에 인덱스를 생성하자, 쿼리 실행 계획이 이전과는 완전히 다르게 매우 효율적으로 변경된 것을 확인할 수 있습니다.

type: range

가장 눈에 띄는 변화는 type이 ALL에서 range로 바뀐 점입니다.
이는 데이터베이스가 인덱스를 사용해 특정 범위의 데이터를 스캔했음을 의미합니다.
즉, idx_items_price 인덱스에서 price가 50000 이상인 지점을 찾은 뒤, 100000을 초과하는 지점이 나올 때까지만 순차적으로 인덱스를 읽었다는 뜻입니다.

key: idx_items_price

쿼리 실행에 idx_items_price 인덱스가 사용되었음을 명확히 보여줍니다.
옵티마이저는 price 컬럼에 대한 범위 검색에 이 인덱스를 사용하는 것이 가장 효율적이라고 판단한 것입니다.

rows: 5

옵티마이저가 스캔할 것으로 예측하는 행의 수가 **5**로 크게 줄었습니다.
인덱스가 없을 때는 테이블 전체인 25개 행을 모두 스캔해야 했지만, 이제는 인덱스를 통해 조건에 맞는 5개의 데이터만 읽으면 된다는 것을 알고 있습니다.

filtered: 100.00

인덱스를 통해서 스캔한 5개의 행을 100% 선택한다는 뜻입니다.

Extra: Using index condition

이 부분도 중요한 최적화 정보입니다. 인덱스 정보만으로 WHERE 조건절을 최대한 필터링한 후, 조건을 만족하는 데이터의 전체 행만 가져왔다는 뜻입니다.

7.3 인덱스 범위 검색 분석

인덱스의 범위 검색(range)는 매우 효율적으로 작동합니다.
처음 한 번만 찾고, 이후에는 별도의 탐색 과정 없이 연속해서 결과를 구할 수 있습니다.
작동 순서를 확인해보겠습니다.

인덱스 범위 검색 작동 순서

먼저 인덱스의 price 항목에서 50000원 이상인 조건을 찾습니다. 이 조건은 이진 탐색의 원리를 사용하므로 매우 빨리 찾을 수 있습니다. 여기서는 60000원을 찾습니다.
price가 순서대로 정렬되어 있기 때문에 인덱스의 바로 다음 행으로 넘어가서 100000원을 초과했는지 확인합니다. 그리고 인덱스의 다음 행으로 넘어가면서 이 과정을 반복합니다.
- 다음 행은 70000원입니다. 조건에 부합하므로 결과의 대상이 됩니다.
- 다음 행은 80000원입니다. 조건에 부합하므로 결과의 대상이 됩니다.
- 다음 행은 90000원입니다. 조건에 부합하므로 결과의 대상이 됩니다.
- 다음 행은 95000원입니다. 조건에 부합하므로 결과의 대상이 됩니다.
- 다음 행은 120000원입니다. 조건에 부합하지 않습니다.
100000원 초과 항목을 발견했으므로 탐색을 종료합니다.

만약 이 인덱스가 없다면, 데이터베이스는 items 테이블 전체를 스캔하여 조건에 맞는 행을 찾아야 할 것입니다.
인덱스가 범위 검색에서도 쿼리 성능을 크게 향상시킬 수 있다는 것을 알 수 있습니다.

7.4 쿼리 실행 결과

실제 쿼리를 실행해서 결과를 확인해 보겠습니다.

SELECT * FROM items WHERE price BETWEEN 50000 AND 100000;

실행 결과

item_id	seller_id	item_name	category	price
19	7	친환경 주방 세트	생활용품	60000
6	5	고급 가죽 지갑	패션	70000
3	2	프리미엄 게이밍 마우스	전자기기	80000
14	2	인체공학 키보드	전자기기	90000
8	6	캐시미어 스웨터	패션	95000

여기서 주목할 점은 결과가 price 순서로 정렬되었다는 것입니다. 가장 오른쪽의 price 컬럼을 확인해보세요.
인덱스가 없을 때는 item_id 순서(테이블에 데이터가 물리적으로 저장된 순서)로 결과가 나왔습니다.
idx_items_price 인덱스를 사용한 후에는 인덱스 키인 price를 기준으로 정렬된 결과가 나왔습니다.
이는 데이터베이스가 idx_items_price 인덱스를 price 순서대로 스캔하면서 조건에 맞는 item_id를 찾고, 그 item_id를 사용해 원본 테이블에서 데이터를 가져왔기 때문입니다.
하지만 이 순서를 데이터베이스가 항상 보장하는 것은 아닙니다.
그냥 내부 과정의 결과에 따라서 이 순서가 되었을 뿐입니다.
만약 price 조건으로 정렬해야 한다면 ORDER BY price를 추가하는 것을 권장합니다.

8. 인덱스와 LIKE 범위 검색

LIKE 절에서 인덱스를 사용하려면, 와일드카드(%)가 검색어의 뒤쪽에 위치해야 합니다.
WHERE item_name LIKE '게이밍%': 인덱스 사용 가능 ✅
WHERE item_name LIKE '%게이밍': 인덱스 사용 불가 ❌
WHERE item_name LIKE '%게이밍%': 인덱스 사용 불가 ❌

와일드카드 위치의 중요성

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

8.1 LIKE 검색 성공 예제: 와일드카드가 뒤에 오는 경우

"상품 이름이 '게이밍'으로 시작하는 모든 상품을 찾아보자."
이 쿼리는 와일드카드(%)가 검색어 뒤에 붙어있으므로, item_name으로 정렬된 인덱스를 효율적으로 활용할 수 있습니다.

SELECT * FROM items WHERE item_name LIKE '게이밍%';

실행 결과

item_id	seller_id	item_name	category	price
10	8	게이밍 노트북	전자기기	1500000
24	2	게이밍 의자	전자기기	200000

실행 계획을 확인해보겠습니다.

EXPLAIN SELECT * FROM items WHERE item_name LIKE '게이밍%';

실행 결과

id	type	key	rows	filtered	Extra
1	range	idx_items_item_name	2	100.00	Using index condition

여기서도 인덱스의 범위 검색(range)을 사용할 수 있습니다.
왜냐하면 글자가 정렬되어 있기 때문입니다.
처음 한 번만 찾고, 이후에는 별도의 탐색 과정 없이 연속해서 결과를 구할 수 있습니다.

작동 순서

먼저 인덱스의 item_name 항목에서 게이밍으로 시작하는 조건을 찾습니다. 이 조건은 이진 탐색의 원리를 사용하므로 매우 빨리 찾을 수 있습니다. 여기서는 '게이밍 노트북'을 찾습니다.
item_name이 순서대로 정렬되어 있기 때문에 인덱스의 바로 다음 행으로 넘어가서 '게이밍'으로 시작하는지 확인합니다. 그리고 인덱스의 다음 행으로 넘어가면서 이 과정을 반복합니다.
- 다음 행은 '게이밍 의자'입니다. '게이밍'으로 시작하는 조건에 부합하므로 결과의 대상이 됩니다.
- 다음 행은 'SQL 마스터 가이드'입니다. '게이밍'으로 시작하는 조건에 부합하지 않습니다.
'게이밍'으로 시작하지 않는 항목을 발견했으므로 탐색을 종료합니다.

국어사전 원리

이것이 가능한 이유는 국어사전에서 '게'로 시작하는 단어를 찾는 것과 원리가 같기 때문입니다. 쉽게 이야기해서 글자순으로 정렬되어 있기 때문입니다. 데이터베이스는 idx_items_item_name 인덱스에서 '게이밍'으로 시작하는 첫 번째 위치를 빠르게 찾은 뒤, '게이밍'으로 시작하지 않는 단어가 나올 때까지 인덱스를 순차적으로 읽기만 하면 됩니다.

8.2 LIKE 검색 실패 예제: 와일드카드가 앞에 오는 경우

"상품 이름에 '게이밍'이 포함된 모든 상품을 찾아보자."
이 쿼리는 실무에서 검색 기능을 만들 때 매우 흔하게 사용됩니다.
하지만 와일드카드(%)가 검색어 앞에 붙어있기 때문에 인덱스의 장점을 활용할 수 없습니다.

SELECT * FROM items WHERE item_name LIKE '%게이밍%';

실행 결과

item_id	seller_id	item_name	category	price
3	2	프리미엄 게이밍 마우스	전자기기	80000
10	8	게이밍 노트북	전자기기	1500000
24	2	게이밍 의자	전자기기	200000

'프리미엄 게이밍 마우스'도 추가로 포함되었습니다.
실행 계획을 분석해보겠습니다.

EXPLAIN SELECT * FROM items WHERE item_name LIKE '%게이밍%';

실행 결과

id	type	key	rows	filtered	Extra
1	ALL	NULL	25	11.11	Using where

실행 계획이 인덱스가 없던 시절로 돌아갔습니다.
type: ALL: 풀 테이블 스캔이 발생했습니다.
key: NULL: idx_items_item_name 인덱스가 있음에도 불구하고 사용되지 않았습니다.

중간 검색의 한계

국어사전에서 중간에 '게이밍'이라는 글자가 들어간 단어를 찾으려면 어떻게 해야 할까요? '가'부터 '힣'까지 모든 단어를 하나씩 다 훑어보는 수밖에 없습니다. 데이터베이스도 마찬가지입니다. item_name의 시작 글자를 알 수 없으므로, 정렬된 인덱스는 아무런 도움이 되지 않습니다. 결국 데이터베이스는 테이블의 모든 데이터를 처음부터 끝까지 스캔하는 최악의 방법을 선택하게 됩니다.

LIKE 검색 정리

LIKE도 범위 검색을 사용할 수 있습니다. 단 인덱스를 사용하려면 반드시 와일드카드(%)를 뒤에 사용해야 합니다.

8.3 실무 팁: 전문 검색 (Full-Text Search)

이처럼 LIKE '%검색어%' 방식은 데이터가 많아질수록 성능이 심각하게 저하되어 실제 서비스에서는 사용하기 어렵습니다.
이런 '내용 검색' 또는 '포함 검색' 문제를 해결하기 위해 데이터베이스는 **전문 검색(Full-Text Search)**이라는 특수한 기능을 제공합니다.
전문 검색 인덱스는 B-Tree 인덱스와는 달리, 텍스트를 단어(토큰) 단위로 쪼개서 인덱싱하는 방식입니다.
이를 통해 텍스트 중간에 있는 단어도 매우 빠르게 검색할 수 있습니다.
만약 쇼핑몰에서 상품명 검색 기능을 구현해야 한다면, LIKE 대신 MATCH ... AGAINST 구문을 사용하는 전문 검색 기능을 도입하는 것이 해결 방법입니다.
필요하다면 'FullText Search'라는 키워드로 검색해 보세요.

9. 인덱스와 정렬

데이터베이스에서 정렬(ORDER BY) 작업은 생각보다 비용이 많이 드는 무거운 작업 중 하나입니다.
왜냐하면 조건에 맞는 데이터를 모두 찾은 후에, 그 결과를 서로 비교하면서 순서에 맞게 다시 정렬해야 하기 때문입니다.
찾은 데이터가 수십, 수백만 건이라면 이 데이터를 정렬 알고리즘을 사용해서 정렬해야 하는데, 이 정렬 과정에서 엄청난 부하가 발생할 수 있습니다.
하지만 우리에게는 인덱스가 있습니다. 인덱스는 이미 데이터가 특정 순서로 정렬된 자료구조입니다.
그렇다면 이 정렬된 인덱스를 활용해서 ORDER BY 작업의 성능을 획기적으로 개선할 수 있지 않을까요?

filesort의 위험성

ORDER BY가 인덱스를 잘 활용하면, 별도의 정렬 과정 없이 이미 정렬된 인덱스를 순서대로 읽기만 하면 되므로 매우 빠르게 동작합니다. 데이터베이스는 이 과정에서 filesort라는 별도의 정렬 작업을 생략할 수 있게 됩니다.

여기서 filesort라는 이름만 보고 파일 시스템을 사용한다고 오해하면 안 됩니다. 실제로는 메모리나 디스크를 사용해 정렬하는 내부 프로세스를 의미합니다. 우리의 목표는 바로 이 비효율적인 filesort를 피하는 것입니다.

EXPLAIN SELECT * FROM items ORDER BY stock_quantity;

인덱스를 활용하지 않는 위 쿼리를 보면 filesort를 확인할 수 있습니다.

실행 결과

id	table	type	key	rows	filtered	Extra
1	items	ALL	NULL	25	100.00	Using filesort

Extra 항목에 Using filesort를 확인할 수 있습니다.
데이터를 모두 찾은 후에 stock_quantity를 기준으로 정렬 작업이 추가됩니다.

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

가장 이상적인 상황은 WHERE 절의 조건과 ORDER BY 절의 정렬 기준이 같아서, 인덱스 하나로 검색과 정렬을 모두 해결하는 경우입니다.
앞서 price로 범위 검색을 했던 쿼리에 ORDER BY price를 추가해서 실행 계획을 확인해 보겠습니다.
우리는 이미 price 컬럼에 idx_items_price 인덱스를 만들어 두었습니다.

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

실행 결과

id	type	key	rows	filtered	Extra
1	range	idx_items_price	5	100.00	Using index condition

이 실행 계획을 자세히 분석해 보겠습니다.
type: range: idx_items_price 인덱스를 사용해 특정 범위(50000 ~ 100000)를 효율적으로 스캔했습니다.
key: idx_items_price: idx_items_price 인덱스가 사용되었습니다.
Extra: Using index condition: WHERE 조건 필터링에 인덱스를 사용했습니다.

filesort가 없는 이유

여기서 가장 중요한 것은 Extra 컬럼에 앞서 설명한 별도의 정렬 작업인 Using filesort가 없다는 점입니다. 왜 없을까요?

데이터베이스 옵티마이저는 idx_items_price 인덱스가 이미 price 순서로 정렬되어 있다는 사실을 알고 있습니다. 따라서 WHERE 조건에 맞는 데이터를 찾기 위해 인덱스를 스캔하는 것만으로도 자연스럽게 price 순서로 정렬된 결과를 얻을 수 있습니다. 즉, 별도의 정렬 작업을 할 필요가 전혀 없는 것입니다.

SELECT * FROM items WHERE price BETWEEN 50000 AND 100000;

ORDER BY를 제외하고 같은 쿼리를 실행해서 결과를 확인해 보겠습니다.

실행 결과

item_id	seller_id	item_name	category	price
19	7	친환경 주방 세트	생활용품	60000
6	5	고급 가죽 지갑	패션	70000
3	2	프리미엄 게이밍 마우스	전자기기	80000
14	2	인체공학 키보드	전자기기	90000
8	6	캐시미어 스웨터	패션	95000

결과가 price 순서로 정렬되어 있는 것을 확인할 수 있습니다.
가장 오른쪽 price 컬럼을 확인해 보세요.
ORDER BY price를 명시하지 않았음에도 인덱스를 스캔한 순서 덕분에 이미 결과가 정렬된 것입니다.

ORDER BY 최적화의 핵심

이것이 인덱스를 활용한 ORDER BY 최적화의 핵심입니다. WHERE 절과 ORDER BY 절이 동일한 인덱스를 효율적으로 사용할 수 있다면, 데이터베이스는 정렬을 생략하고, 가장 빠른 방식으로 쿼리를 처리합니다.

9.2 인덱스를 역방향으로 조회하는 경우

ORDER BY를 사용할 때 항상 오름차순(ASC)으로만 정렬하는 것은 아닙니다.
쇼핑몰에서는 최신 상품이나 가격이 높은 상품을 먼저 보여주는 것처럼, 내림차순(DESC) 정렬도 매우 흔하게 사용됩니다.
그렇다면 ORDER BY price DESC처럼 내림차순 정렬을 사용하면 filesort가 발생할까요?
결론부터 말하면, 단일 컬럼 인덱스에서는 filesort 없이 효율적인 처리가 가능합니다.
데이터베이스 옵티마이저는 인덱스를 거꾸로 읽는, 즉 **역방향 스캔(Backward Index Scan)**을 할 수 있기 때문입니다.
price가 비싼 순서대로 상품을 조회하는 쿼리의 실행 계획을 살펴보겠습니다.

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

실행 결과

id	type	key	rows	filtered	Extra
1	range	idx_items_price	5	100.00	Using index condition; Backward index scan

실행 계획의 Extra 컬럼을 주목하세요. Using filesort는 없지만, **Backward index scan**이라는 새로운 구문이 등장했습니다.

Backward index scan

옵티마이저가 idx_items_price 인덱스를 끝에서부터 앞으로, 즉 역순으로 스캔했음을 의미합니다. 인덱스는 양방향 탐색이 가능합니다. 따라서 price가 높은 값부터 낮은 값 순서로 인덱스를 탐색하여 WHERE 조건에 맞는 데이터를 찾습니다. 이 과정에서 이미 정렬 순서가 만족되므로 별도의 filesort 작업이 필요 없습니다.

이처럼 인덱스를 역방향으로 스캔하는 것만으로도 filesort를 피할 수 있으므로 매우 효율적인 방식입니다.

정방향 스캔이 역방향 스캔보다 미세하게 더 빠름

정방향 인덱스 스캔이 미세하게 더 빠른 이유는 컴퓨터 하드웨어의 '미리 읽기(Prefetching)' 기능 때문입니다.

컴퓨터는 데이터를 정방향(1, 2, 3...)으로 읽을 것을 예측하고, 다음 데이터를 미리 준비해 둡니다. 이 방식으로 하드웨어가 최적화되어 있어 효율이 가장 높습니다.

하지만 이 성능 차이는 미미해서 실무에서는 거의 무시해도 됩니다. ORDER BY에서 filesort를 피하는 것이 수백 배 더 중요합니다.

10. 내림차순 인덱스 (Descending Index)

역방향 스캔은 효율적이지만, 여기서 한 걸음 더 나아가 정렬 방향과 일치하는 인덱스를 직접 만들어 줄 수도 있습니다.
MySQL 8.0 버전부터는 내림차순 인덱스(Descending Index) 생성을 정식으로 지원합니다.
내림차순 인덱스는 데이터 자체를 처음부터 내림차순으로 정렬하여 저장하는 인덱스입니다.
기존 idx_items_price 인덱스를 삭제하고, price에 대한 내림차순 인덱스를 새로 만들어보겠습니다.

-- 기존 오름차순 인덱스 삭제
DROP INDEX idx_items_price ON items;

-- price 컬럼에 내림차순 인덱스 생성
CREATE INDEX idx_items_price_desc ON items (price DESC);

(price DESC) 부분을 보세요. 인덱스를 내림차순으로 정렬된 상태로 만드는 것입니다.
이제 이 내림차순 인덱스가 있는 상태에서 다시 ORDER BY price DESC 쿼리의 실행 계획을 확인해 보겠습니다.

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

실행 결과

id	type	key	rows	filtered	Extra
1	range	idx_items_price_desc	5	100.00	Using index condition

key 컬럼에서 우리가 새로 만든 idx_items_price_desc 인덱스가 사용된 것을 볼 수 있습니다.
가장 주목할 점은 Extra 컬럼에서 Backward index scan이 사라지고 Using index condition만 남았다는 것입니다.

내림차순 인덱스의 장점

이는 옵티마이저가 더 이상 인덱스를 '거꾸로' 읽는 수고를 할 필요가 없어졌음을 의미합니다. 쿼리가 요구하는 정렬 순서(DESC)와 인덱스의 정렬 순서(DESC)가 완벽하게 일치하므로, 인덱스를 자연스러운 순서(정방향)로 스캔하기만 하면 됩니다. 이것이 ORDER BY 절을 최적화하는 가장 이상적인 방법입니다.

내용을 확인했다면 다음 과정을 위해 인덱스를 원래대로 다시 만들어두겠습니다.

-- 내림차순 인덱스 삭제
DROP INDEX idx_items_price_desc ON items;

-- price 컬럼에 오름차순 인덱스 생성
CREATE INDEX idx_items_price ON items (price);

(price) 마지막에 DESC가 없어야 합니다.

실무 팁

단일 컬럼 인덱스에서는 역방향 스캔과 내림차순 인덱스 간의 성능 차이가 크지 않을 수 있습니다. 하지만 ORDER BY category ASC, registered_date DESC처럼 여러 컬럼에 대해 서로 다른 정렬 순서(오름차순과 내림차순의 혼합)가 필요한 복잡한 쿼리에서는 내림차순 인덱스의 진가가 발휘됩니다. 이런 경우, 정렬 순서에 맞춰 정확하게 생성된 다중 컬럼 인덱스(복합 인덱스)는 쿼리 성능을 극적으로 향상시킬 수 있습니다. 다중 컬럼 인덱스(복합 인덱스)는 뒤에서 알아봅니다.

11. 다음 단계

이 문서에서는 MySQL 인덱스의 기본적인 생성, 조회, 삭제 방법과 단일 컬럼 인덱스를 활용한 쿼리 최적화에 대해 알아보았습니다.
더 깊이 있는 인덱스 활용을 위해 다음 문서들을 참고해주세요.

고급 인덱스 활용

Optimizer: 옵티마이저의 인덱스 선택 전략, 손익분기점, 커버링 인덱스를 통한 성능 최적화
Composite Index: 복합 인덱스의 왼쪽 접두어 규칙, 등호와 범위 조건의 순서, IN 절 활용 전략
Index Guidelines: 카디널리티 원칙, 인덱스 설계 가이드라인, 인덱스의 단점과 관리 전략, 실전 문제 풀이

1. MySQL Index​

2. CREATE INDEX - 인덱스 생성하기​

2.1 기본 문법​

2.2 실습 예제​

3. SHOW INDEX - 테이블의 인덱스 정보 확인하기​

3.1 items 테이블 인덱스 확인​

3.2 sellers 테이블 인덱스 확인​

4. DROP INDEX - 인덱스 삭제하기​

4.1 기본 문법​

4.2 실습 예제​

5. EXPLAIN - 인덱스가 정말 사용되는지 확인하는 법​

5.1 인덱스가 없을 때​

type: ALL​

key: NULL​

rows: 25​

filtered: 10.00​

Extra: Using where​

6. 인덱스와 동등 비교​

6.1 인덱스가 있을 때​

type: ref​

possible_keys​

key: idx_items_item_name​

rows: 1​

filtered: 100.00​

Extra: NULL​

6.2 데이터가 적을 때 주의사항​

7. 인덱스와 범위 검색​

7.1 인덱스가 없을 때​

7.2 인덱스가 있을 때​

type: range​

key: idx_items_price​

rows: 5​

filtered: 100.00​

Extra: Using index condition​

7.3 인덱스 범위 검색 분석​

인덱스 범위 검색 작동 순서​

7.4 쿼리 실행 결과​

8. 인덱스와 LIKE 범위 검색​

8.1 LIKE 검색 성공 예제: 와일드카드가 뒤에 오는 경우​

작동 순서​

8.2 LIKE 검색 실패 예제: 와일드카드가 앞에 오는 경우​

8.3 실무 팁: 전문 검색 (Full-Text Search)​

9. 인덱스와 정렬​

9.1 인덱스를 사용해 정렬까지 한 번에 처리하는 경우​

9.2 인덱스를 역방향으로 조회하는 경우​

10. 내림차순 인덱스 (Descending Index)​

11. 다음 단계​

고급 인덱스 활용​

1. MySQL Index

2. CREATE INDEX - 인덱스 생성하기

2.1 기본 문법

2.2 실습 예제

3. SHOW INDEX - 테이블의 인덱스 정보 확인하기

3.1 items 테이블 인덱스 확인

3.2 sellers 테이블 인덱스 확인

4. DROP INDEX - 인덱스 삭제하기

4.1 기본 문법

4.2 실습 예제

5. EXPLAIN - 인덱스가 정말 사용되는지 확인하는 법

5.1 인덱스가 없을 때

type: ALL

key: NULL

rows: 25

filtered: 10.00

Extra: Using where

6. 인덱스와 동등 비교

6.1 인덱스가 있을 때

type: ref

possible_keys

key: idx_items_item_name

rows: 1

filtered: 100.00

Extra: NULL

6.2 데이터가 적을 때 주의사항

7. 인덱스와 범위 검색

7.1 인덱스가 없을 때

7.2 인덱스가 있을 때

type: range

key: idx_items_price

rows: 5

filtered: 100.00

Extra: Using index condition

7.3 인덱스 범위 검색 분석

인덱스 범위 검색 작동 순서

7.4 쿼리 실행 결과

8. 인덱스와 LIKE 범위 검색

8.1 LIKE 검색 성공 예제: 와일드카드가 뒤에 오는 경우

작동 순서

8.2 LIKE 검색 실패 예제: 와일드카드가 앞에 오는 경우

8.3 실무 팁: 전문 검색 (Full-Text Search)

9. 인덱스와 정렬

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

9.2 인덱스를 역방향으로 조회하는 경우

10. 내림차순 인덱스 (Descending Index)

11. 다음 단계

고급 인덱스 활용