1. 서론: 왜 내가 만든 색인은 실행 계획에서 무시될까?
데이터베이스 기반의 백엔드 애플리케이션을 개발하다 보면 쿼리 성능 저하라는 장벽에 필연적으로 부딪히게 됩니다. 이 문제를 해결하기 위해 개발자들은 WHERE 조건절에 사용되는 컬럼에 색인(Index)을 추가하여 검색 속도를 개선하고자 합니다. 하지만 기대와는 다르게 실행 계획(Execution Plan)을 조회해보면, 옵티마이저가 공들여 만든 색인을 무시하고 type: ALL 즉, 전체 테이블 스캔(Full Table Scan)을 수행하는 경우가 빈번하게 발생합니다.
이러한 상황에서 많은 개발자들이 가장 먼저 떠올리는 '쉬운 해결책'은 쿼리에 FORCE INDEX나 USE INDEX와 같은 힌트(Hint)를 추가하여 데이터베이스가 강제로 특정 색인을 타도록 만드는 것입니다.
그렇다면 과연 FORCE INDEX를 사용하는 것이 올바른 접근일까요? 이 블로그 포스트에서는 데이터베이스 옵티마이저가 색인을 외면하는 수학적/구조적 원리를 분석하고, 강제 힌트 사용의 위험성, 그리고 내가 만든 색인이 의도한 대로 작동하게 만들기 위해 왜 그래야 하는지 그 근본적인 최적화 원리를 깊이 있게 파헤쳐 봅니다.
2. 옵티마이저는 왜 색인을 무시하는가? 디스크 I/O의 수학적 이해
관계형 데이터베이스 시스템(RDBMS)의 쿼리 최적화기(Query Optimizer)는 철저하게 비용 기반(Cost-Based)으로 동작합니다. 여러 실행 경로 중에서 가장 예상 실행 비용이 낮은 경로를 선택하는 방식입니다 [1].
옵티마이저가 보조 색인(Secondary Index)의 사용을 포기하는 가장 큰 이유는 물리적인 디스크 I/O 비용 때문입니다. B+-Tree 구조의 보조 색인을 통해 데이터를 조회할 때 발생하는 수학적 비용 산출 공식은 다음과 같습니다 [2]:
이 공식에서 핵심이 되는 변수들의 의미는 다음과 같습니다.
•
: 인덱스 트리의 높이 (탐색 깊이)
•
: 조건에 일치하여 실제로 디스크에서 가져와야 하는 레코드의 수
•
: 디스크 블록 전송 시간
•
: 디스크 탐색 시간 (디스크 암을 이동시키는 Seek Time)
보조 색인의 리프(Leaf) 노드에는 실제 데이터가 들어있는 것이 아니라, 데이터가 위치한 디스크 블록의 주소나 기본 키(Primary Key)가 들어 있습니다. 따라서 인덱스에서 조건에 맞는 항목을 찾을 때마다, 실제 레코드 데이터를 읽기 위해 매번 디스크 헤드를 무작위로 움직이는 랜덤 I/O(Random I/O)가 발생합니다.
즉, 일치하는 레코드 수()가 전체 데이터의 특정 비율을 초과하게 되면, 번의 무거운 랜덤 탐색 비용()이 누적되어 기하급수적으로 커집니다. 이 경우 옵티마이저는 "인덱스를 타고 수만 번 디스크를 이리저리 긁느니, 차라리 디스크 헤드를 한 방향으로만 둔 채 처음부터 끝까지 순차 스캔(Sequential Scan)으로 통째로 읽는 것이 더 싸다"라고 판단하게 됩니다.
3. FORCE INDEX의 함정: 왜 지양해야 할까?
옵티마이저가 전체 스캔을 선택했을 때 FORCE INDEX를 삽입하면 즉각적으로 색인을 타게 만들 수는 있습니다. 하지만 이는 장기적인 관점에서 매우 위험한 기술 부채(Technical Debt)가 됩니다.
1.
데이터 분포의 동적 변화: 데이터베이스의 데이터는 고정되어 있지 않습니다. 오늘 기준으로는 조건에 맞는 데이터가 100건뿐이라 인덱스를 강제하는 것이 빠를 수 있습니다. 하지만 1년 뒤 해당 데이터가 1,000,000건으로 불어난다면 어떻게 될까요? 앞선 수학적 공식에 의해 랜덤 I/O 비용이 통제 불능 상태로 치솟지만, 힌트가 하드코딩되어 있어 옵티마이저는 전체 스캔으로 실행 계획을 전환하지 못하고 시스템 장애를 유발할 수 있습니다.
2.
버전 업그레이드와 옵티마이저의 진화: 데이터베이스 버전이 올라감에 따라 옵티마이저의 알고리즘은 더욱 똑똑해집니다. 명시적인 힌트는 데이터베이스가 스스로 더 나은 접근 경로를 탐색할 수 있는 자율성을 박탈합니다.
FORCE INDEX는 옵티마이저의 버그가 확실하거나, 비즈니스 로직상 데이터의 분포가 절대 변하지 않는다는 확신이 있을 때만 최후의 수단으로 사용해야 합니다.
4. 색인이 의도대로 작동하지 않을 때 점검해야 할 4가지
그렇다면 강제 힌트를 쓰지 않고 어떻게 쿼리를 튜닝해야 할까요? 다음 4가지 핵심 원칙을 통해 색인 설계 자체를 교정해야 합니다.
4.1. 인덱스 선택도 (Index Selectivity) 검증
선택도는 전체 레코드 수 대비 고유한 값의 비율을 뜻하며, 다음 공식으로 계산됩니다 [3].
주민등록번호나 이메일처럼 중복이 없는 컬럼은 선택도가 1에 가까워 인덱스 효율이 극대화됩니다. 반면 '성별'이나 '주문 상태' 같은 컬럼은 선택도가 매우 낮습니다. 전체 데이터의 약 10~20% 이상을 반환하는 쿼리의 경우, 옵티마이저는 선택도가 낮다고 판단하여 색인을 무시합니다. 이럴 때는 단일 컬럼 색인을 포기하고 선택도가 높은 다른 컬럼과 결합하여 다중 컬럼 인덱스를 구성해야 합니다.
4.2. 커버링 인덱스 (Covering Index) 유도
쿼리에 SELECT *를 남용하고 있지 않은지 확인하십시오. 보조 색인에 매칭되는 데이터가 많아서 랜덤 I/O가 부담된다면, 아예 실제 데이터 블록으로의 접근을 원천 차단하는 방법을 쓸 수 있습니다.
쿼리의 SELECT, WHERE, ORDER BY에 사용되는 모든 컬럼을 포함하는 결합 색인을 만들면, 옵티마이저는 디스크 블록 접근 없이 B-Tree 인덱스 구조 내부만 읽고 응답을 반환합니다. 이를 커버링 인덱스라 부르며, EXPLAIN 실행 계획의 Extra 열에 Using index라고 표시됩니다 [4]. 랜덤 I/O 비용 산출식에서 과정 자체를 소멸시키므로 극적인 성능 최적화가 이루어집니다.
4.3. 옵티마이저를 방해하는 숨은 형변환과 가공
컬럼의 데이터 타입과 조건절의 데이터 타입이 불일치하면 인덱스를 탈 수 없습니다.
예를 들어 VARCHAR 타입의 user_id 컬럼에 대해 WHERE user_id = 12345처럼 숫자형으로 조회하면, 데이터베이스 내부적으로 문자열을 숫자로 변환하는 숨겨진 캐스팅이 발생합니다. 또는 WHERE DATE(created_at) = '2023-10-01'처럼 인덱스가 걸린 컬럼을 함수로 가공하면 원래의 B-Tree 정렬 구조가 깨져 색인이 무효화됩니다. 조건절 우변의 상수값을 컬럼 타입에 맞게 가공해야 합니다.
4.4. 정렬을 위한 인덱스와 filesort 방지
쿼리에 ORDER BY가 포함되어 있다면, 옵티마이저는 필터링 비용과 정렬 비용을 저울질합니다. 인덱스 순서와 정렬 순서가 어긋나면, 데이터베이스는 메모리의 sort_buffer_size 공간에 데이터를 퍼올려 무거운 외부 정렬 연산을 수행해야 하며, 이는 filesort라는 실행 계획 경고로 나타납니다 [5]. 동등 비교(=) 조건 컬럼을 인덱스의 선두에 두고, 정렬이 필요한 컬럼을 후행에 배치하여 인덱스가 자연스럽게 정렬된 결과를 내어주도록 ORDER BY 성능을 튜닝해야 합니다.
5. 다양한 데이터베이스에서의 공통적인 접근법
색인 무시 현상은 MySQL에만 국한되지 않습니다. Oracle, PostgreSQL, 심지어 NoSQL 데이터베이스에서도 옵티마이저가 존재하는 한 발생할 수 있는 보편적인 컴퓨터 과학의 문제입니다.
•
PostgreSQL: PostgreSQL의 쿼리 플래너 역시 비용 기반이며, EXPLAIN ANALYZE를 통해 실제 스캔 비용을 비교할 수 있습니다. 색인을 타지 않는다면 통계 정보가 오래된 것일 수 있으므로 VACUUM ANALYZE 명령어를 통해 최신 데이터 분포 메타데이터를 플래너에게 제공해야 합니다.
•
Oracle: Oracle의 CBO(Cost-Based Optimizer)는 히스토그램(Histogram)을 사용하여 데이터의 편향성(Skew)을 이해합니다. 특정 값이 비정상적으로 많을 때, Oracle은 그 값을 조회할 때는 전체 스캔을, 적은 값을 조회할 때는 인덱스 스캔을 동적으로 선택합니다.
•
MongoDB (NoSQL): 스키마가 없는 MongoDB 역시 B-Tree 계열의 인덱스를 사용하며 explain()을 통해 COLLSCAN(전체 스캔) 여부를 확인합니다. MongoDB 튜닝의 핵심인 ESR 규칙 (Equality, Sort, Range)은 관계형 데이터베이스의 결합 인덱스 설계 원리와 완벽하게 동일합니다.
어떤 데이터베이스를 사용하든, 인덱스가 무시될 때 강제로 태우는 문법을 찾기보다는 엔진의 비용 모델(Cost Model)이 왜 전체 스캔을 더 싸다고 계산했는지 데이터의 분포와 I/O 아키텍처를 되짚어 보아야 합니다.
6. 결론: 똑똑하게 옵티마이저와 타협하기
관계형 데이터베이스 시스템은 수십 년간 전 세계의 천재적인 엔지니어들이 갈고닦은 고도의 알고리즘 집약체입니다. 옵티마이저가 색인을 외면할 때는 대부분 그럴 만한 타당한 물리적, 수학적 근거가 존재합니다.
FORCE INDEX는 마치 증상을 가라앉히기 위해 원인을 무시하고 진통제만 투여하는 것과 같습니다. 데이터베이스 성능 최적화의 진정한 비밀은 데이터베이스를 억지로 통제하는 것이 아니라, 실행 계획을 분석하여 인덱스 선택도를 높이고, 커버링 인덱스를 활용하며, 디스크 I/O의 본질적인 탐색 비용을 줄이는 방향으로 테이블 구조와 쿼리 형태를 리팩토링하는 데 있습니다. 옵티마이저가 기쁜 마음으로 여러분의 색인을 선택할 수 있도록, 데이터베이스의 관점에서 친절한 쿼리를 작성해 보시기 바랍니다.
참고문헌
[1] Database-System-Concepts-7th-Edition — Regardless of the way the query is written, it is the job of the optimizer to find the least-cost plan for the query. To find the least costly query-evaluation plan, the optimizer needs to generate al-ternative plans that produce the same result as the given expression and to choose the least costly…
[2] Database-System-Concepts-7th-Edition — A4 Secondary B+-tree Index, Equality on Key (hi + 1) ∗ (tT + tS) This case is similar to clustering index. A4 Secondary B+-tree Index, Equality on Non-key (hi + n) ∗ (tT + tS) (Where n is the number of records fetched.) Here, cost of index traversal is the same as for A3, but each record may be on a…
[3] O'Reilly.High.Performance.MySQL.3rd.Edition.Mar.2012 (1) — Indexing Strategies for High Performance | 159 Here’s another example of a common mistake: mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10; Prefix Indexes and Index Selectivity Sometimes you need to index very long character columns, which makes your indexes large and slow. O…
[4] O'Reilly.High.Performance.MySQL.3rd.Edition.Mar.2012 (1) — 178 | Chapter 5: Indexing for High Performance select_type: SIMPLE table: inventory type: index possible_keys: NULL key: idx_store_id_film_id key_len: 3 ref: NULL rows: 4673 Extra: Using index Index-covered queries have subtleties that can …
[5] O'Reilly.High.Performance.MySQL.3rd.Edition.Mar.2012 (1) — 12. If you need to sort in different directions, a trick that sometimes helps is to store a reversed or negated value. 182 | Chapter 5: Indexing for High Performance UNIQUE KEY rental_date (rental_date,inventory_id,customer_id), KEY idx_fk_inventory_id (inventory_id), KEY idx_fk_customer_id (c…
