1. 옵티마이저와 인덱스 선택

• 컬럼에 인덱스를 생성하면, 해당 컬럼을 조건으로 사용하는 모든 WHERE 절의 성능이 향상될 것이라고 기대하기 쉽습니다.
• 하지만 항상 그렇지는 않습니다. 데이터베이스의 **옵티마이저(Optimizer)**는 쿼리를 실행하기 전에 여러 실행 가능한 방법을 평가하고, 그중 가장 비용이 적게 드는, 즉 가장 효율적이라고 판단되는 방법을 선택합니다.
• 이 과정에서 옵티마이저는 인덱스를 사용하는 것이 오히려 비효율적이라고 판단하면, 인덱스가 존재하더라도 과감히 포기하고 **테이블 전체를 스캔(Full Table Scan)**하는 방법을 선택할 수 있습니다.

기본 개념 참고

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

1.1 인덱스 손익분기점

• 옵티마이저가 인덱스 사용 여부를 결정하는 핵심 기준은 바로 **'손익분기점'**입니다.
• 여기서 손익분기점이란, 인덱스를 통해 데이터를 읽는 비용이 테이블 전체를 직접 읽는 비용보다 높아지는 지점을 의미합니다.
• 인덱스를 사용하는 비용
  • 인덱스 탐색 비용 + 인덱스에서 찾은 주소로 테이블에 접근하는 비용 (랜덤 I/O)
• 풀 테이블 스캔 비용
  • 테이블 전체를 순차적으로 읽는 비용 (순차 I/O)
• 일반적으로 전체 데이터의 약 20~25% 이상을 조회해야 하는 쿼리는 인덱스를 통해 테이블의 각 행에 개별적으로 접근하는 것보다, 차라리 테이블 전체를 순차적으로 스캔하는 것이 더 효율적이라고 알려져 있습니다.
• 즉, 조회하려는 데이터의 양이 손익분기점을 넘어가면 옵티마이저는 인덱스 사용을 포기합니다.

1.2 왜 랜덤 I/O가 더 느릴까?

• 랜덤 I/O가 순차 I/O보다 느린 이유는 데이터를 찾는 과정에서 발생하는 시간 때문입니다.
• 이를 HDD, SSD 디스크를 예로 들어 책 읽기에 비유해보겠습니다.
• 자세한 내용은 아래 문서를 참고해주세요.
  • Mass Storage Structure

순차 I/O (Sequential I/O) - 책을 1페이지부터 순서대로 읽기

• 데이터가 저장된 위치를 한 번 찾으면, 그 이후로는 순서대로 쭉 읽기만 하면 됩니다.
• HDD의 경우 디스크의 헤드가 거의 움직이지 않고 연속된 데이터를 읽어오므로 작업이 매우 빠르고 효율적입니다.
• SSD의 경우 "여기서부터 100개 읽어와" 라는 하나의 큰 명령으로 처리할 수 있습니다.

랜덤 I/O (Random I/O) - 책의 여러 페이지를 순서 없이 찾아 읽기

• 5페이지를 읽은 후 200페이지를 읽고, 다시 45페이지를 읽는 것과 같습니다.
• HDD의 경우 읽어야 할 데이터가 디스크의 여러 곳에 흩어져 있어, 데이터를 읽을 때마다 디스크 헤드가 물리적으로 해당 위치까지 이동해야 합니다. 이렇게 데이터의 위치를 찾는 데 걸리는 시간(탐색 시간, Seek Time)이 추가되기 때문에 순차 I/O에 비해 느립니다.
• SSD의 경우 찾아야 하는 데이터가 100개라면 "이거 읽어와", "저거 읽어와" 라는 100개의 작은 명령을 각각 처리해야 합니다. 작은 명령을 여러 번 처리하는 것은 SSD 컨트롤러에 더 많은 오버헤드(부하)를 줍니다.

랜덤 I/O와 순차 I/O의 차이

데이터베이스에서 인덱스를 통해 테이블의 여러 행에 접근하는 것은, 인덱스에 저장된 주소에 따라 디스크의 여러 위치를 오가야 하는 랜덤 I/O를 유발할 수 있습니다. 반면, 테이블 전체를 스캔하는 것은 처음부터 끝까지 데이터를 읽는 순차 I/O에 해당합니다.

이 때문에 조회할 데이터가 아주 많을 경우, 여러 번의 랜덤 I/O를 수행하는 것보다 한 번의 순차 I/O가 더 빠를 수 있습니다.

1.3 예시 1: 인덱스를 사용하는 효율적인 범위 검색

• 먼저, 옵티마이저가 인덱스를 사용하는 것이 효율적이라고 판단하는 경우를 확인해보겠습니다.
• items 테이블의 price 컬럼에는 idx_items_price 인덱스가 생성되어 있습니다.

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

실행 결과

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

• type: range: 옵티마이저는 idx_items_price 인덱스를 사용해 특정 범위만 스캔했습니다.
• rows: 5: 조회할 데이터가 5건으로 예상됩니다. 이는 전체 25건 중 20%에 해당하므로, 손익분기점을 넘지 않습니다.
• key: idx_items_price: 따라서 옵티마이저는 idx_items_price 인덱스를 사용하는 효율적인 실행 계획을 세웠습니다.

1.4 예시 2: 인덱스를 포기하는 비효율적인 범위 검색

• 이번에는 WHERE 절의 범위를 훨씬 더 넓게 잡아보겠습니다.
• 기존에 50000 ~ 100000으로 검색했던 것을 1000 ~ 200000으로 검색 범위를 확 넓혔습니다.

EXPLAIN SELECT * FROM items WHERE price BETWEEN 1000 AND 200000;

실행 결과

id	type	possible_keys	key	rows	filtered	Extra
1	ALL	idx_items_price	NULL	25	76.00	Using where

• 실행 계획이 완전히 달라졌습니다.

possible_keys: idx_items_price

• 옵티마이저는 idx_items_price 인덱스를 사용할 수 있다는 것을 알고 있었습니다.
• possible_keys는 이 쿼리에서 사용할 수 있는 인덱스 후보입니다.

key: NULL

• 하지만 최종적으로 인덱스를 사용하지 않기로 결정했습니다.

type: ALL

• 결국 선택된 방법은 풀 테이블 스캔입니다.

filtered: 76.00

• 옵티마이저는 이 쿼리가 전체 데이터(25건)의 약 76%, 즉 19건 정도를 반환할 것이라고 예측했습니다.
• 이 정도면 손익분기점을 훌쩍 넘는 수치입니다.
• 옵티마이저는 19건의 데이터를 찾기 위해 인덱스를 읽고, 다시 테이블에 19번의 개별적인 접근(Random I/O)을 하는 것보다, 그냥 테이블 전체(25건)를 한 번에 쭉 읽는 것(Sequential I/O)이 더 저렴하다고 판단한 것입니다.
• 이처럼 인덱스는 만능이 아닙니다.
• WHERE 절에 인덱스가 걸린 컬럼을 사용하더라도, 조회하려는 데이터의 범위가 너무 넓어 손익분기점을 넘어가면 옵티마이저는 인덱스를 사용하지 않을 수 있습니다.
• 따라서 쿼리 튜닝을 할 때는 EXPLAIN을 통해 옵티마이저가 실제로 인덱스를 사용하고 있는지 반드시 확인하는 습관을 들여야 합니다.

여러 인덱스가 있다면?

선택할 수 있는 인덱스 후보가 여러 개 있다면 옵티마이저는 그 중에서 가장 효율적으로 작동하는 인덱스를 선택합니다. 물론 이 경우에도 풀 테이블 스캔이 가장 효율적이라고 판단하면 풀 테이블 스캔을 선택할 수 있습니다.

:::

1.5 데이터가 많이 부족하다면?

• 데이터 자체가 많이 부족하다면 옵티마이저는 풀 테이블 스캔을 선택할 가능성이 있습니다.
• 테이블에 데이터가 몇 건 없다면, 테이블 전체를 순차적으로 읽는 비용이 인덱스를 탐색하고 테이블에 접근하는 비용보다 훨씬 저렴하기 때문입니다.
• 1,000페이지짜리 두꺼운 책에서는 색인(인덱스)을 보고 원하는 페이지를 찾아가는 것이 빠릅니다. 하지만 단 3페이지짜리 얇은 소책자에서 특정 내용을 찾을 때는, 굳이 색인을 볼 필요 없이 그냥 1페이지부터 빠르게 훑어보는 것이 더 효율적입니다.
• 마찬가지로 옵티마이저도 테이블이 몇 페이지 되지 않을 정도로 작다면, 굳이 인덱스를 사용하는 복잡한 과정을 거치지 않고 테이블 전체를 직접 스캔하는 것이 더 효율적이라고 판단합니다.

개발 환경에서의 주의사항

이는 개발 환경에서 자주 발생하는 오해 중 하나입니다. 개발 중인 테이블에 소량의 테스트 데이터만 넣고 쿼리를 실행했을 때, EXPLAIN 결과에 type: ALL이 표시되어 '인덱스가 왜 작동하지 않지?'라고 생각할 수 있습니다. 하지만 이는 옵티마이저의 지극히 합리적인 판단일 가능성이 높습니다. 프로덕션 환경에서 데이터가 수만, 수백만 건으로 늘어나면, 옵티마이저는 다시 인덱스를 사용하는 효율적인 실행 계획을 선택하게 될 것입니다.

FORCE INDEX 사용

테스트 목적으로 인덱스를 강제로 적용하려면 다음과 같이 FORCE INDEX를 사용하면 됩니다.

SELECT * FROM my_table FORCE INDEX (idx_my_index) WHERE column = 'value';

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

2. 커버링 인덱스

• 이전 시간에 우리는 옵티마이저가 인덱스를 사용하기로 결정해도, 추가적인 작업이 필요하다는 것을 보았습니다.
• 프로세스를 다시 한번 복기해 보겠습니다.

1. 인덱스 스캔: idx_items_price 인덱스에서 WHERE 조건에 맞는 데이터(의 위치)를 찾습니다.
2. 테이블 데이터 접근: 인덱스에서 찾은 위치 정보(PK인 item_id)를 사용해, 원본 items 테이블에 접근해서 SELECT 절에서 요구하는 다른 컬럼(* 또는 item_name 등)의 데이터를 가져옵니다.

• 이 과정은 마치 책의 '찾아보기'에서 원하는 키워드와 페이지 번호를 찾은 뒤(1단계), 다시 그 페이지로 직접 넘어가서 내용을 읽는 것(2단계)과 같습니다.
• 여기서 2단계, 즉 원본 테이블에 다시 접근하는 과정은 랜덤 I/O를 유발하므로 비용이 발생합니다.
• 그렇다면 테이블에 접근하는 이 두 번째 단계를 아예 생략할 방법은 없을까요? 쿼리에 필요한 모든 데이터를 인덱스에서만 읽어서 끝낼 수는 없을까요?
• 이 질문에 대한 해답이 바로 **커버링 인덱스(Covering Index)**입니다.

2.1 커버링 인덱스란?

• 커버링 인덱스는 쿼리에 필요한 모든 컬럼을 포함하고 있는 인덱스를 말합니다.
• '커버링'이라는 이름 그대로, 인덱스 하나가 쿼리의 요구사항 전체를 '덮는다'는 의미입니다.
• 데이터베이스 옵티마이저는 쿼리를 실행할 때, 만약 특정 인덱스가 SELECT, WHERE, ORDER BY, GROUP BY 절에 사용되는 모든 컬럼을 가지고 있다면, 원본 테이블에 전혀 접근하지 않고 오직 인덱스만을 읽어서 쿼리를 처리합니다.
• 이는 디스크의 여러 곳을 오가는 비싼 랜덤 I/O 작업을 완전히 제거하고, 순차 I/O에 가까운 인덱스 스캔만으로 쿼리를 끝낼 수 있음을 의미합니다.
• 당연히 성능은 비약적으로 향상됩니다.

2.2 커버링 인덱스 적용 전 (일반 인덱스 사용)

• 쇼핑몰에서 가격이 50,000원에서 100,000원 사이인 상품들의 **가격(price)과 이름(item_name)**을 조회하는, 매우 흔한 쿼리가 있다고 가정하겠습니다.

SELECT item_id, price, item_name FROM items WHERE price BETWEEN 50000 AND 100000;

실행 결과

item_id	price	item_name
19	60000	친환경 주방 세트
6	70000	고급 가죽 지갑
3	80000	프리미엄 게이밍 마우스
14	90000	인체공학 키보드
8	95000	캐시미어 스웨터

• 현재 items 테이블에는 price 컬럼에 idx_items_price 인덱스가 걸려 있습니다.
• 이 상태에서 위 쿼리의 실행 계획을 확인해 보겠습니다.

EXPLAIN SELECT item_id, price, item_name 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

• 실행 계획을 분석해 보겠습니다.

key: idx_items_price

• price 조건을 위해 idx_items_price 인덱스를 사용했습니다.

Extra: Using index condition

• WHERE 조건절을 필터링하는 데 인덱스를 효율적으로 사용했지만, 최종 데이터를 가져오기 위해서는 추가 작업이 필요하다는 의미를 내포합니다.
• 이 쿼리에서는 SELECT 절의 item_name 컬럼이 idx_items_price 인덱스에 포함되어 있지 않습니다.
• 따라서 옵티마이저는 다음과 같이 동작합니다.
  1. idx_items_price 인덱스를 스캔하여 price가 조건에 맞는 행의 item_id를 5개 찾습니다.
  2. 찾아낸 5개의 item_id를 사용해, items 테이블의 원본 데이터에 5번 접근하여 각각의 item_name을 가져옵니다. (5번의 랜덤 I/O 발생)
• 이처럼 인덱스에 포함되지 않은 컬럼(item_name)을 조회해야 하므로, 테이블 접근을 피할 수 없습니다.

MySQL 인덱스와 기본 키

MySQL의 인덱스는 테이블의 기본 키(PK, item_id)를 기본으로 포함합니다. 따라서 idx_items_price 인덱스를 사용하는 경우 item_id, price 두 컬럼의 값은 인덱스에서 바로 조회할 수 있습니다.

2.3 커버링 인덱스 적용 - 인덱스 컬럼만 조회하는 경우

• 앞서 idx_items_price 인덱스는 price 컬럼과 기본 키인 item_id를 포함하고 있다고 설명했습니다.
• 그렇다면 SELECT 절에서 item_name을 제외하고 item_id와 price만 조회한다면 어떻게 될까요?
• 이 경우, 쿼리에 필요한 모든 컬럼(item_id, price)이 idx_items_price 인덱스에 이미 포함되어 있으므로, 이 인덱스는 커버링 인덱스의 역할을 수행할 수 있습니다.

SELECT item_id, price FROM items WHERE price BETWEEN 50000 AND 100000;

실행 결과

item_id	price
19	60000
6	70000
3	80000
14	90000
8	95000

• 쿼리에 사용하는 item_id, price 컬럼이 idx_items_price 인덱스에 모두 있습니다.
• 따라서 items 원본 테이블에 접근할 필요가 없습니다.
• 결과적으로 items 원본 테이블에 접근하지 않고, idx_items_price 인덱스만 사용합니다.

EXPLAIN SELECT item_id, price FROM items WHERE price BETWEEN 50000 AND 100000;

실행 결과

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

• Extra 컬럼에 Using index가 추가된 것을 확인할 수 있습니다.

Extra: Using index

• 이 표시가 가장 중요합니다.
• 이것은 쿼리에 필요한 모든 데이터를 오직 인덱스에서만 읽어서 처리했음을 의미합니다.
• 옵티마이저는 idx_items_price 인덱스만 스캔하여 price와 item_id를 모두 얻었고, 원본 items 테이블에는 전혀 접근할 필요가 없었습니다.

Extra: Using where

• Using index와 함께 Using where가 표시되는 것을 볼 수 있습니다.
• 이는 인덱스 내에서 WHERE price BETWEEN ... 조건절을 사용해 불필요한 데이터를 필터링했음을 의미합니다.
• Using index와 함께 사용되는 Using where는 테이블에 접근한 후 필터링하는 것이 아니라, 인덱스 스캔 단계에서 효율적으로 필터링이 이루어졌음을 나타냅니다.

커버링 인덱스의 효과

이 실행 계획은 커버링 인덱스를 활용해 테이블 접근을 피했고(Using index), 인덱스 내에서 WHERE 절의 조건으로 필터링(Using where)을 수행한, 매우 효율적인 쿼리임을 보여줍니다.

• 이처럼 SELECT 절에 어떤 컬럼을 지정하느냐에 따라, 같은 인덱스라도 커버링 인덱스로 동작할 수도 있고 아닐 수도 있습니다.
• 하지만 우리가 원래 원했던 것은 item_name까지 함께 조회하는 것이었습니다.
• 이처럼 인덱스에 포함되지 않은 컬럼까지 조회하려면 어떻게 해야 할까요?
• 바로 이때, 쿼리에 필요한 컬럼을 모두 포함하는 새로운 인덱스를 직접 만들어주어야 합니다.

2.4 커버링 인덱스 적용 - item_name 추가

• 이제 커버링 인덱스를 만들어 item_name을 포함하는 쿼리의 성능을 최적화해 보겠습니다.
• 이 쿼리에 필요한 컬럼은 WHERE 절의 price와 SELECT 절의 item_name입니다.
• 따라서 이 두 컬럼을 모두 포함하는 인덱스를 생성하면 됩니다.

복합 인덱스의 컬럼 순서

컬럼이 여러 개인 복합 인덱스에서 컬럼의 순서는 매우 중요합니다. WHERE 절에서 동등 비교나 범위 검색에 사용되는 컬럼을 가장 앞에 두어야 인덱스를 효율적으로 사용할 수 있습니다. 여기서는 price를 먼저 두고, 그 다음에 item_name을 둡니다.

복합 인덱스에 대한 부분은 Index-Composite 문서를 참고해주세요.

-- 기존 price 인덱스는 삭제하고, price와 item_name을 포함하는 새로운 복합 인덱스를 생성합니다.
DROP INDEX idx_items_price ON items;
CREATE INDEX idx_items_price_name ON items (price, item_name);

• 새로운 idx_items_price_name 인덱스는 price로 먼저 정렬되고, price가 같다면 item_name으로 다시 정렬된 구조를 가집니다.
• 이제 이 인덱스는 쿼리에 필요한 price와 item_name 정보를 모두 가지고 있습니다.
• 이 상태에서 다시 한번 동일한 쿼리의 실행 계획을 확인해 보겠습니다.

EXPLAIN SELECT item_id, price, item_name FROM items WHERE price BETWEEN 50000 AND 100000;

실행 결과

id	type	key	rows	filtered	Extra
1	range	idx_items_price_name	5	100.00	Using where; Using index

• Using index를 통해 인덱스만으로 쿼리가 실행되는 것을 확인할 수 있습니다.
• 이 쿼리는 이제 idx_items_price_name 인덱스만 읽고 끝나므로, 테이블 접근으로 인한 랜덤 I/O가 완전히 사라져 훨씬 빠르고 효율적으로 동작합니다.

SELECT item_id, price, item_name FROM items WHERE price BETWEEN 50000 AND 100000;

실행 결과

item_id	price	item_name
19	60000	친환경 주방 세트
6	70000	고급 가죽 지갑
3	80000	프리미엄 게이밍 마우스
14	90000	인체공학 키보드
8	95000	캐시미어 스웨터

• 실제 쿼리를 실행해 보면 결과는 이전과 동일합니다.
• 하지만 그 결과를 얻어오는 내부 과정의 효율성은 매우 큰 차이가 납니다.

2.5 커버링 인덱스의 장단점

장점

• 압도적인 SELECT 성능 향상: 테이블 접근을 위한 랜덤 I/O를 제거하여 조회 성능을 극적으로 개선합니다.
• 특히 COUNT 쿼리 최적화: SELECT COUNT(*)와 같은 쿼리에서 테이블 전체가 아닌, 크기가 훨씬 작은 인덱스만 스캔하여 결과를 빠르게 반환할 수 있습니다.

단점

• 저장 공간 증가: 인덱스는 원본 데이터와 별도의 저장 공간을 차지합니다. 인덱스에 포함되는 컬럼이 많아질수록 인덱스의 크기도 커집니다.
• 쓰기 성능 저하: INSERT, UPDATE, DELETE 작업 시, 테이블 데이터뿐만 아니라 인덱스도 함께 수정해야 합니다. 인덱스가 많고 복잡할수록 쓰기 작업에 대한 부하가 커집니다.

2.6 언제 사용해야 할까?

• 커버링 인덱스는 만능 해결책이 아니며, **읽기 성능과 쓰기 성능 사이의 트레이드오프(trade-off)**를 신중하게 고려해야 합니다.
• 조회(읽기)가 매우 빈번하고, 쓰기 작업은 상대적으로 적은 테이블에 적용하는 것이 가장 효과적입니다.
• SELECT 절에서 조회하는 컬럼의 개수가 적을 때 유리합니다. SELECT *처럼 모든 컬럼을 조회하는 쿼리는 커버링 인덱스의 이점을 누리기 어렵습니다. (모든 컬럼을 포함하는 인덱스를 만들 수는 있지만, 이는 사실상 테이블을 복제하는 것과 같아 매우 비효율적입니다.)
• 성능 저하가 발생하는 특정 쿼리를 튜닝하기 위한 '비장의 무기'로 사용하는 경우가 많습니다.