복합 인덱스 순서·커버링 인덱스로 옵티마이저가 인덱스를 최대한 쓰게 만든다.

인덱스 설계 심화와 옵티마이저 활용

입문 강좌에서는 단일 컬럼 인덱스를 만들고, 쿼리가 빨라지는 효과를 직접 확인해 보았습니다. 그런데 실무에서 마주치는 쿼리는 대부분 여러 컬럼을 조합한 조건, 정렬, 집계를 동시에 요구합니다. 단일 인덱스 하나로는 이 모든 요구를 충족하기 어렵고, 오히려 잘못 설계된 인덱스가 옵티마이저를 혼란시켜 성능을 떨어뜨리기도 합니다.

이번 레슨에서는 복합 인덱스의 컬럼 순서가 왜 중요한지, 커버링 인덱스로 테이블 액세스를 아예 없애는 방법은 무엇인지, 그리고 인덱스가 예상치 못하게 무력화되는 패턴을 진단하고 대처하는 방법을 심도 있게 다룹니다. 옵티마이저가 내리는 선택을 이해하고, 필요할 때 그 선택을 적절히 유도하는 것이 목표입니다.

학습 목표

복합 인덱스의 선두 컬럼(leftmost prefix) 규칙을 이해하고 컬럼 순서를 올바르게 결정할 수 있다.
커버링 인덱스를 설계해 테이블 랜덤 I/O를 제거하고 쿼리 성능을 극적으로 높일 수 있다.
**선택도(cardinality)**와 통계 정보가 옵티마이저 결정에 미치는 영향을 설명할 수 있다.
함수 기반 인덱스를 활용하고, 인덱스가 무력화되는 패턴을 사전에 진단할 수 있다.
ORDER BY·GROUP BY를 인덱스로 처리해 filesort를 제거하고, 인덱스 힌트를 상황에 맞게 사용할 수 있다.

복합 인덱스의 컬럼 순서와 leftmost prefix 규칙

복합 인덱스(composite index)는 두 개 이상의 컬럼을 하나의 B-Tree 구조에 묶어 저장합니다. InnoDB는 인덱스 키를 왼쪽 컬럼부터 순서대로 정렬하기 때문에, 왼쪽부터 연속된 컬럼 조합(leftmost prefix) 에 해당하는 조건만 인덱스를 탈 수 있습니다.

-- 예시 테이블: 주문 내역
CREATE TABLE orders (
    order_id   BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id    INT          NOT NULL,
    status     VARCHAR(20)  NOT NULL,
    created_at DATETIME     NOT NULL,
    amount     DECIMAL(12,2) NOT NULL
);

-- 복합 인덱스: (user_id, status, created_at) 순서
CREATE INDEX idx_user_status_created
    ON orders (user_id, status, created_at);

위 인덱스가 활용되는 경우와 그렇지 않은 경우를 비교해 보겠습니다.

-- ✅ 선두 컬럼 user_id 사용 → 인덱스 활용
SELECT * FROM orders WHERE user_id = 100;

-- ✅ 선두 + 두 번째 컬럼 → 인덱스 활용
SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';

-- ✅ 세 컬럼 모두 → 인덱스 최대 활용
SELECT * FROM orders
WHERE user_id = 100 AND status = 'PAID' AND created_at >= '2025-01-01';

-- ❌ 선두 컬럼 누락 → 인덱스 미사용 (풀 테이블 스캔)
SELECT * FROM orders WHERE status = 'PAID' AND created_at >= '2025-01-01';

-- ⚠️ 중간 컬럼 건너뜀 → user_id로만 트리 범위 탐색, created_at는 범위 경계 결정에는 못 쓰이지만
--    MySQL 5.6+의 ICP(Index Condition Pushdown)로 인덱스 항목 단계에서 필터링 가능 (Using index condition)
SELECT * FROM orders WHERE user_id = 100 AND created_at >= '2025-01-01';

⚠️ 주의 범위 조건(>, <, BETWEEN, LIKE 'prefix%')이 등장하는 컬럼 이후의 컬럼은 인덱스 범위 탐색에는 사용되지 않습니다. 등치 조건(=, IN)이 많은 컬럼을 앞쪽에 배치하고, 범위 조건 컬럼은 뒤에 두는 것이 기본 전략입니다.

컬럼 순서 결정 기준

우선순위	기준	설명
1	등치 조건 컬럼 우선	`=`, `IN` 조건이 있는 컬럼을 앞에 배치
2	선택도 높은 컬럼 우선	고유값이 많은 컬럼이 앞에 올수록 조기 필터링 효율 증가
3	범위 조건 컬럼 뒤로	범위 조건 이후 컬럼은 인덱스 탐색에서 제외됨
4	ORDER BY 컬럼 고려	정렬에 쓰이는 컬럼을 인덱스에 포함하면 filesort 제거 가능

커버링 인덱스로 테이블 액세스 제거하기

InnoDB에서 인덱스를 통해 행을 찾으면, 인덱스 리프 노드에 저장된 Primary Key 값을 이용해 실제 데이터 페이지를 한 번 더 읽습니다. 이를 랜덤 I/O라고 부르며, 데이터가 많을수록 이 비용이 지배적이 됩니다.

커버링 인덱스(covering index) 는 쿼리가 필요로 하는 모든 컬럼이 인덱스 안에 포함되어 있어, 테이블 페이지를 전혀 읽지 않아도 되는 인덱스입니다. EXPLAIN 결과의 Extra 컬럼에 Using index가 표시되면 커버링 인덱스가 작동 중입니다.

-- 커버링 인덱스 설계 예시
-- 쿼리: 특정 사용자의 최근 결제 주문 목록 조회 (order_id, created_at, amount 필요)
CREATE INDEX idx_covering_user_orders
    ON orders (user_id, status, created_at, amount);

-- 이 쿼리는 테이블을 전혀 읽지 않고 인덱스만으로 결과 반환
SELECT order_id, created_at, amount
FROM orders
WHERE user_id = 100 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

-- EXPLAIN으로 커버링 인덱스 확인
EXPLAIN SELECT order_id, created_at, amount
FROM orders
WHERE user_id = 100 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20\G

-- 출력 예시 (주요 컬럼)
key:   idx_covering_user_orders
Extra: Using index   ← 커버링 인덱스 작동

💡 TIP InnoDB 세컨더리 인덱스의 리프 노드에는 항상 Primary Key 값이 포함됩니다. 따라서 SELECT 절에 PK 컬럼을 추가해도 커버링 인덱스 조건이 깨지지 않습니다.

커버링 인덱스의 한계

커버링 인덱스는 인덱스 크기를 늘립니다. 인덱스가 너무 많은 컬럼을 포함하면 인덱스 자체의 삽입·수정 비용이 증가하고, 버퍼 풀 효율도 낮아집니다. 자주 실행되는 무거운 읽기 쿼리에 한정해 적용하는 것이 바람직합니다.

인덱스 선택도(Cardinality)와 통계 정보의 영향

선택도(selectivity) 는 인덱스 컬럼의 고유값 수를 전체 행 수로 나눈 비율입니다. 값이 1에 가까울수록 인덱스 효율이 높고, 0에 가까울수록 인덱스를 사용해도 얻는 이점이 적습니다.

-- 컬럼별 고유값 수 (cardinality) 확인
SELECT
    column_name,
    COUNT(DISTINCT column_name) AS cardinality,
    COUNT(*)                    AS total_rows,
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM orders
GROUP BY column_name; -- 실제로는 각 컬럼을 직접 지정

-- 예: user_id vs status 선택도 비교
SELECT
    COUNT(DISTINCT user_id)  AS user_id_cardinality,
    COUNT(DISTINCT status)   AS status_cardinality,
    COUNT(*)                 AS total
FROM orders;

-- 통계 정보 확인 (information_schema)
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

MySQL 옵티마이저는 통계 정보를 기반으로 인덱스 사용 여부를 결정합니다. 대량 INSERT/DELETE 이후 통계가 오래된 경우, 옵티마이저가 잘못된 실행 계획을 선택할 수 있습니다.

-- 통계 정보 수동 갱신
ANALYZE TABLE orders;

-- InnoDB 통계 영속화 설정 확인
SHOW VARIABLES LIKE 'innodb_stats_persistent%';

-- 샘플 페이지 수 늘리기 (더 정확한 통계, 단 ANALYZE 시간 증가)
ALTER TABLE orders STATS_SAMPLE_PAGES = 50;

⚠️ 주의 status 컬럼처럼 고유값이 5개뿐인 컬럼에 단독 인덱스를 만들면, 옵티마이저는 전체 행의 20%를 읽어야 할 때 인덱스보다 풀 스캔이 낫다고 판단할 수 있습니다. 선택도가 낮은 컬럼은 선택도가 높은 다른 컬럼과 조합한 복합 인덱스로 설계하는 것이 효과적입니다.

함수 기반 인덱스와 인덱스 무력화 패턴 진단

인덱스가 무력화되는 대표 패턴

가장 흔한 실수는 WHERE 조건에서 인덱스 컬럼에 함수나 연산을 적용하는 것입니다.

-- ❌ 컬럼에 함수 적용 → 인덱스 무력화
SELECT * FROM orders WHERE YEAR(created_at) = 2025;
SELECT * FROM orders WHERE DATE(created_at) = '2025-06-01';
SELECT * FROM orders WHERE UPPER(status) = 'PAID';
SELECT * FROM orders WHERE amount * 1.1 > 10000;
SELECT * FROM orders WHERE user_id + 0 = 100;  -- 불필요한 연산

-- ✅ 함수를 값 쪽으로 이동 → 인덱스 활용
SELECT * FROM orders
    WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

SELECT * FROM orders
    WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02';

SELECT * FROM orders WHERE status = 'PAID';  -- 애플리케이션에서 대문자로 전달

SELECT * FROM orders WHERE amount > 10000 / 1.1;
SELECT * FROM orders WHERE user_id = 100;

-- ❌ 암묵적 타입 변환 → 인덱스 무력화 (문자열 컬럼 = 숫자)
-- phone이 VARCHAR인데 숫자로 비교하면 MySQL이 컬럼 쪽을 숫자로 변환 → 인덱스 무력화
SELECT * FROM orders WHERE phone = 821012345678;  -- 컬럼 변환으로 phone 인덱스 미사용

-- ✅ 같은 비교라도 문자열 리터럴을 쓰면 컬럼 변환 없이 인덱스 활용
SELECT * FROM orders WHERE phone = '821012345678';

-- ✅ 숫자 컬럼 vs 문자열 리터럴은 상수만 숫자로 변환 → 인덱스 정상 사용
-- user_id가 INT일 때 '100'은 100으로 변환되며 컬럼은 그대로이므로 인덱스가 유지됨
SELECT * FROM orders WHERE user_id = '100';  -- 상수만 변환, user_id 인덱스 사용

-- ❌ LIKE 앞쪽 와일드카드 → 인덱스 미사용
SELECT * FROM orders WHERE status LIKE '%AID';

-- ✅ LIKE 뒤쪽 와일드카드는 인덱스 활용 가능
SELECT * FROM orders WHERE status LIKE 'PA%';

함수 기반 인덱스 (MySQL 8.0+)

MySQL 8.0부터는 함수 기반 인덱스(functional index) 를 지원합니다. 컬럼 자체가 아닌 표현식에 인덱스를 만들 수 있습니다.

-- 이메일의 도메인 부분에 인덱스 생성
CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email   VARCHAR(255) NOT NULL
);

-- 함수 기반 인덱스: 이메일 도메인 추출
CREATE INDEX idx_email_domain
    ON users ((SUBSTRING_INDEX(email, '@', -1)));

-- 인덱스 활용 쿼리
SELECT * FROM users
WHERE SUBSTRING_INDEX(email, '@', -1) = 'gmail.com';

-- 날짜 컬럼에서 연도만 추출하는 함수 기반 인덱스
ALTER TABLE orders
    ADD INDEX idx_order_year ((YEAR(created_at)));

-- 이제 아래 쿼리도 인덱스 활용 가능
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

💡 TIP 함수 기반 인덱스는 내부적으로 가상 컬럼(generated column)을 생성합니다. 쿼리의 WHERE 절에서 인덱스 생성 시 사용한 표현식과 정확히 동일한 표현식을 사용해야 인덱스가 적용됩니다.

ORDER BY · GROUP BY를 인덱스로 처리하고 filesort 제거하기

MySQL은 인덱스 순서와 쿼리의 정렬 순서가 일치하면 별도의 정렬 작업 없이 인덱스 순서로 행을 반환합니다. EXPLAIN Extra에 Using filesort가 없는 상태가 목표입니다.

filesort가 발생하는 조건

-- 인덱스: (user_id, status, created_at)

-- ✅ 인덱스 순서 그대로 → filesort 없음
SELECT * FROM orders
WHERE user_id = 100
ORDER BY status, created_at;

-- ✅ DESC 방향이 일관적이면 인덱스 역방향 스캔 → filesort 없음
SELECT * FROM orders
WHERE user_id = 100
ORDER BY status DESC, created_at DESC;

-- ❌ 혼합 방향 → filesort 발생 (MySQL 8.0 이전)
SELECT * FROM orders
WHERE user_id = 100
ORDER BY status ASC, created_at DESC;

MySQL 8.0부터는 내림차순 인덱스(descending index) 를 지원하여 혼합 방향 정렬도 filesort 없이 처리할 수 있습니다.

-- MySQL 8.0+: 혼합 방향 정렬을 위한 내림차순 인덱스
CREATE INDEX idx_status_asc_created_desc
    ON orders (user_id, status ASC, created_at DESC);

-- 이제 filesort 없이 처리 가능
SELECT order_id, status, created_at
FROM orders
WHERE user_id = 100
ORDER BY status ASC, created_at DESC;

GROUP BY와 인덱스

-- 인덱스: (user_id, status)
-- ✅ GROUP BY 컬럼이 인덱스 선두와 일치 → Using index
SELECT user_id, status, COUNT(*)
FROM orders
WHERE user_id IN (100, 101, 102)
GROUP BY user_id, status;

-- ❌ GROUP BY 컬럼 순서가 인덱스와 다름 → Using temporary; Using filesort
SELECT status, user_id, COUNT(*)
FROM orders
GROUP BY status, user_id;

💡 TIP GROUP BY에 집계 함수만 쓰고 ORDER BY NULL을 명시하면, MySQL이 불필요한 정렬을 건너뜁니다. 단, MySQL 8.0.13 이후로는 GROUP BY가 묵시적 정렬을 보장하지 않으므로 명시적 ORDER BY를 권장합니다.

인덱스 힌트: USE / FORCE / IGNORE INDEX

옵티마이저가 잘못된 인덱스를 선택하는 경우, 인덱스 힌트로 실행 계획을 유도할 수 있습니다. 그러나 힌트는 임시방편이므로 통계 갱신이나 스키마 개선을 병행해야 합니다.

-- USE INDEX: 이 인덱스들 중에서만 고려해 달라는 권고
SELECT *
FROM orders USE INDEX (idx_user_status_created)
WHERE user_id = 100 AND status = 'PAID';

-- FORCE INDEX: 풀 스캔보다 인덱스를 강하게 선호하도록 비용을 조정 (적용 불가 시 풀 스캔으로 폴백 가능)
SELECT *
FROM orders FORCE INDEX (idx_user_status_created)
WHERE user_id = 100 AND status = 'PAID';

-- IGNORE INDEX: 특정 인덱스를 제외하고 선택
SELECT *
FROM orders IGNORE INDEX (idx_legacy_status)
WHERE user_id = 100 AND status = 'PAID';

-- FOR ORDER BY: 정렬 전용으로 인덱스 범위 제한
SELECT *
FROM orders USE INDEX FOR ORDER BY (idx_user_status_created)
WHERE user_id = 100
ORDER BY status, created_at;

힌트 종류	강제성	사용 시기
`USE INDEX`	약 (권고)	옵티마이저 선택지를 제한하고 싶을 때
`FORCE INDEX`	강 (선호)	풀 스캔보다 인덱스를 강하게 선호시키고 싶을 때 (적용 불가 시 풀 스캔 폴백)
`IGNORE INDEX`	특정 제외	통계 오류로 잘못된 인덱스가 선택될 때

⚠️ 주의 FORCE INDEX는 옵티마이저의 비용 계산을 무시하므로, 데이터 분포가 바뀌면 오히려 성능이 나빠질 수 있습니다. 프로덕션 코드에 남겨두기 전에 반드시 데이터 규모가 다른 환경에서 재검증하세요.

불필요한 인덱스 탐지와 정리 전략

인덱스는 많을수록 좋은 것이 아닙니다. 인덱스가 늘어날수록 INSERT·UPDATE·DELETE의 비용이 증가하고, 버퍼 풀 공간을 소모합니다.

미사용 인덱스 탐지

-- MySQL 8.0+: performance_schema로 인덱스 사용 통계 확인
SELECT
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
  AND index_name IS NOT NULL
  AND index_name != 'PRIMARY'
ORDER BY count_read ASC;   -- 읽기 횟수가 0에 가까우면 미사용 후보

-- sys 스키마 활용 (더 편리한 뷰)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';

-- 중복 인덱스 탐지 (접두사 포함 관계인 인덱스)
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'mydb';

중복 인덱스 예시

-- ❌ 중복 인덱스 — idx_user 는 idx_user_status 의 접두사이므로 불필요
CREATE INDEX idx_user        ON orders (user_id);
CREATE INDEX idx_user_status ON orders (user_id, status);

-- ✅ idx_user 를 제거하면 동일한 효과 + 인덱스 유지 비용 절감
DROP INDEX idx_user ON orders;
-- idx_user_status 가 leftmost prefix 규칙에 의해 user_id 단독 조건도 커버

인덱스 삭제 전 체크리스트

sys.schema_unused_indexes 또는 performance_schema로 최소 1~2주 통계를 수집한다.
삭제 전 CREATE INDEX DDL을 별도 문서에 백업한다.
먼저 ALTER TABLE ... ALTER INDEX idx_name INVISIBLE; (MySQL 8.0+)로 인덱스를 비가시화(invisible)해 쿼리 영향을 테스트한다.
문제가 없으면 DROP INDEX로 최종 삭제한다.

-- MySQL 8.0+: 인덱스 비가시화 (삭제 없이 옵티마이저에서 제외)
ALTER TABLE orders ALTER INDEX idx_legacy_status INVISIBLE;

-- 문제 없으면 삭제
ALTER TABLE orders DROP INDEX idx_legacy_status;

-- 다시 가시화 (롤백)
ALTER TABLE orders ALTER INDEX idx_legacy_status VISIBLE;

💡 TIP 인덱스 비가시화(INVISIBLE INDEX)는 실제 인덱스 데이터를 유지하면서 옵티마이저에게만 숨기므로, 삭제했다가 되돌리는 비용 없이 안전하게 실험할 수 있습니다.

요약

복합 인덱스는 leftmost prefix 규칙에 따라 왼쪽 컬럼부터 연속으로만 사용됩니다. 등치 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 배치하세요.
커버링 인덱스는 SELECT·WHERE·ORDER BY에 필요한 컬럼을 모두 포함시켜 테이블 랜덤 I/O를 없애고, EXPLAIN Extra에서 Using index로 확인됩니다.
선택도(cardinality) 가 낮은 컬럼은 단독 인덱스보다 복합 인덱스의 후위 컬럼으로 활용하고, ANALYZE TABLE로 통계를 최신 상태로 유지하세요.
인덱스 컬럼에 함수·연산·암묵적 타입 변환을 적용하면 인덱스가 무력화됩니다. MySQL 8.0+의 함수 기반 인덱스로 일부 상황을 해결할 수 있습니다.
ORDER BY·GROUP BY를 인덱스 컬럼 순서와 일치시키면 filesort를 제거할 수 있으며, MySQL 8.0+에서는 혼합 방향 정렬도 내림차순 인덱스로 처리됩니다.
인덱스 힌트(USE/FORCE/IGNORE INDEX)는 임시방편이며, 근본 원인(통계 오류, 스키마 설계)을 함께 해결해야 합니다. INVISIBLE INDEX로 안전하게 삭제 효과를 시험한 뒤 불필요한 인덱스를 정리하세요.

연습문제

다음 쿼리가 자주 실행됩니다. products 테이블에 최적의 복합 인덱스를 설계하고 그 이유를 설명하세요.
```
SELECT product_id, name, price
FROM products
WHERE category_id = 5 AND is_active = 1
ORDER BY price ASC
LIMIT 20;
```
힌트 커버링 인덱스 관점에서 SELECT 절 컬럼까지 포함하는 것을 고려해 보세요.
다음 쿼리에서 인덱스가 제대로 활용되지 않는 이유를 찾고, 인덱스를 활용하도록 쿼리를 수정하세요. (created_at 컬럼에는 인덱스가 있습니다.)
```
SELECT * FROM logs WHERE DATE(created_at) = '2025-06-01';
```
힌트 함수를 컬럼이 아닌 값 쪽으로 이동시키는 방법을 생각해 보세요.
orders 테이블에 아래 두 인덱스가 존재합니다. 어느 인덱스가 중복이며, 왜 그런지 설명하고 안전하게 제거하는 SQL을 작성하세요.
```
CREATE INDEX idx_a ON orders (user_id);
CREATE INDEX idx_b ON orders (user_id, status, created_at);
```
힌트 leftmost prefix 규칙과 INVISIBLE INDEX를 활용한 2단계 삭제 전략을 생각해 보세요.
아래 쿼리의 EXPLAIN 결과에 Using filesort가 표시됩니다. 인덱스 하나를 추가해 filesort를 제거하는 방법을 제시하세요.
```
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE status = 'PAID'
GROUP BY user_id
ORDER BY total DESC;
```
힌트 ORDER BY total DESC는 집계 결과이므로 인덱스로 해결하기 어렵습니다. filesort를 줄일 수 있는 부분에 집중하세요.

💡 연습문제 풀이

불러오는 중…