데이터베이스 인덱스 완전 정리 (MySQL/InnoDB 기준)

데이터베이스 성능 튜닝의 핵심은 인덱스를 이해하고 올바르게 설계하는 것입니다. 이 글에서는 MySQL InnoDB 기준으로 인덱스의 내부 구조부터 실무 설계 전략까지 모두 다룹니다.

---

1. 인덱스란? 왜 필요한가?

인덱스(Index)는 데이터베이스 테이블의 특정 컬럼 값과 해당 행의 물리적 위치를 매핑하는 별도의 자료구조입니다. 책의 색인(목차)과 같은 역할을 합니다.

인덱스가 없을 때

-- 1,000만 건 테이블에서 단 1건을 찾는 쿼리
SELECT * FROM orders WHERE order_id = 9999999;

인덱스가 없으면 MySQL은 테이블의 첫 번째 행부터 마지막 행까지 전부 읽어야 합니다. 이를 Full Table Scan(풀 테이블 스캔)이라고 합니다. 1,000만 건이면 디스크 I/O가 수십만 번 발생합니다.

인덱스가 있을 때

같은 쿼리에 인덱스가 있으면 B+Tree를 3~4번의 I/O만으로 원하는 행을 찾습니다. 수십만 번 vs 3~4번, 차이가 극명합니다.

인덱스의 트레이드오프

구분	인덱스 있음	인덱스 없음
SELECT (검색)	빠름	느림 (Full Scan)
INSERT	느림 (인덱스 갱신)	빠름
UPDATE	느림 (인덱스 갱신)	빠름
DELETE	느림 (인덱스 갱신)	빠름
디스크 공간	추가 공간 필요	없음

인덱스는 읽기 성능과 쓰기 성능을 맞바꾸는 구조입니다. 무조건 많이 만든다고 좋은 것이 아닙니다.

---

2. B+Tree 구조 상세 설명

MySQL InnoDB는 대부분의 인덱스를 B+Tree(B+ 트리) 구조로 저장합니다.

B-Tree vs B+Tree 차이

B-Tree (Balanced Tree)

• 모든 노드(내부 노드 + 리프 노드)에 실제 데이터 포인터가 존재
• 내부 노드에서도 데이터를 찾을 수 있음
• Range Scan 시 비효율적: 범위 검색 시 트리를 다시 올라갔다 내려가야 함

B+Tree

• 내부 노드(Internal Node): 키 값만 저장, 자식 노드로의 포인터만 보유
• 리프 노드(Leaf Node): 키 값 + 실제 데이터(또는 데이터 포인터) 저장
• 리프 노드들이 Linked List로 연결됨 — Range Scan에 매우 효율적
• MySQL InnoDB가 채택한 구조

리프 노드 연결 구조

                  [Internal Nodes]
                       [40]
                      /    \
               [20]           [60]
              /    \          /    \
         [10]      [30]   [50]      [70]
        /    \    /    \  /    \   /    \

[Leaf Nodes - Doubly Linked List]
[10,11]->[20,21]->[30,31]->[40,41]->[50,51]->[60,61]->[70,71]

리프 노드가 양방향 연결 리스트로 이어져 있어서, WHERE id BETWEEN 30 AND 60 같은 범위 쿼리를 수행할 때 리프 노드 레벨에서 선형으로 스캔할 수 있습니다.

페이지(블록) 단위 I/O

InnoDB는 디스크를 페이지(Page) 단위로 읽고 씁니다. 기본 페이지 크기는 16KB입니다.

+--------------------------------------------------+
|                InnoDB Page (16KB)                |
+--------------------------------------------------+
| Page Header (38 bytes)                           |
| Infimum Record (가장 작은 가상 레코드)            |
| User Records (실제 데이터 또는 인덱스 키)         |
|   [key1 | pointer1]                              |
|   [key2 | pointer2]                              |
|   [key3 | pointer3]                              |
|   ...                                            |
| Free Space                                       |
| Page Directory                                   |
| Page Trailer (8 bytes, 체크섬)                   |
+--------------------------------------------------+

내부 노드 페이지 하나에는 보통 수백~수천 개의 키가 들어갑니다. 리프 노드 페이지에는 실제 행 데이터가 들어가므로 더 적은 수의 레코드가 들어갑니다.

트리 높이와 성능 관계

B+Tree의 높이(Height)가 낮을수록 루트에서 리프 노드까지 거치는 페이지 수가 줄어 I/O가 감소합니다.

Height = 1: 루트 = 리프 (소규모)
Height = 2: 루트(1) -> 리프(n) — 수천~수만 건 커버
Height = 3: 루트(1) -> 내부(n) -> 리프(m) — 수백만~수억 건 커버

InnoDB 페이지 16KB 기준, 키가 8바이트(BIGINT) + 포인터 6바이트 = 14바이트라고 하면:

• 내부 노드 한 페이지에 약 16384 / 14 ≈ 1170개 키 저장 가능
• Height 3 트리: 1170 × 1170 × (리프당 레코드 수) 건을 3번의 I/O로 탐색

실제로 Height 3~4 수준이면 수억 건 테이블도 10번 이하 I/O로 검색이 가능합니다.

ASCII 다이어그램으로 B+Tree 구조

아래는 user_id에 인덱스가 있는 경우의 B+Tree 예시입니다.

Level 0 (Root - Internal Node)
+---------------------------+
|   [25]  |  [50]  |  [75] |
+---------------------------+
    |          |          |
    v          v          v

Level 1 (Internal Nodes)
+-------------+    +-------------+    +-------------+
| [10][15][20]|    | [30][40][45]|    | [60][65][70]|
+-------------+    +-------------+    +-------------+
  |  |  |  |        |  |  |  |        |  |  |  |
  v  v  v  v        v  v  v  v        v  v  v  v

Level 2 (Leaf Nodes - 양방향 연결 리스트)
+------+    +------+    +------+    +------+    +------+
|10,row|<-->|15,row|<-->|20,row|<-->|25,row|<-->|30,row|  ...
+------+    +------+    +------+    +------+    +------+
         (각 리프 노드는 실제 행 데이터 또는 PK 값 보유)

---

3. 클러스터드 인덱스 vs 논클러스터드(세컨더리) 인덱스

클러스터드 인덱스 (Clustered Index)

InnoDB에서 Primary Key(기본키)가 곧 클러스터드 인덱스입니다. 이는 매우 중요한 특성입니다.

• 테이블의 실제 행 데이터가 PK 순서대로 물리적으로 정렬되어 저장됨
• B+Tree의 리프 노드에 실제 행 데이터 전체가 저장됨
• 테이블 전체가 곧 하나의 B+Tree

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,  -- 이것이 클러스터드 인덱스
    name    VARCHAR(100),
    email   VARCHAR(200),
    age     INT
);

[Clustered Index B+Tree]
                    Root
                   [500]
                  /     \
           [250]           [750]
          /     \          /   \
    [100]  [200]  [300]  [600]  [800]

Leaf Nodes (실제 행 데이터 전체 포함):
+-----------------------------+   +-----------------------------+
|user_id=100|name=Kim|age=25  |<->|user_id=200|name=Lee|age=30  |<-> ...
+-----------------------------+   +-----------------------------+

PK가 없는 경우 InnoDB는 내부적으로 6바이트 숨겨진 Row ID를 클러스터드 인덱스 키로 사용합니다.

세컨더리 인덱스 (Secondary Index / Non-Clustered Index)

PK 이외의 모든 인덱스는 세컨더리 인덱스입니다.

CREATE INDEX idx_email ON users(email);

세컨더리 인덱스의 리프 노드에는 실제 행 데이터 대신 PK 값이 저장됩니다.

[Secondary Index on email]
Leaf Nodes:
+---------------------------+   +---------------------------+
|email=a@a.com | PK=100    |<->|email=b@b.com | PK=500    |<-> ...
+---------------------------+   +---------------------------+
                  |                              |
                  v (PK 값으로 클러스터드 인덱스를 다시 조회)
         [Clustered Index에서 PK=100 탐색 → 실제 행 반환]

2단계 조회 (Double Lookup / Key Lookup)

SELECT name, age FROM users WHERE email = 'kim@example.com';

1. idx_email 세컨더리 인덱스에서 email = 'kim@example.com' 탐색 → PK 값 획득
2. 획득한 PK 값으로 클러스터드 인덱스를 다시 탐색 → 실제 행(name, age) 반환

이 2단계 조회를 “PK 룩업” 또는 “Key Lookup”이라고 합니다. 성능에 영향을 줄 수 있으며, 커버링 인덱스로 회피 가능합니다(5장 참고).

ASCII 다이어그램: 두 구조 비교

[클러스터드 인덱스 (PK = user_id)]
+--------+
|  Root  |
+--------+
    |
+--------+    +--------+    +--------+
| Leaf 1 |<-->| Leaf 2 |<-->| Leaf 3 |
|id=1    |    |id=4    |    |id=7    |
|name=A  |    |name=D  |    |name=G  |
|email=..|    |email=..|    |email=..|
|age=20  |    |age=25  |    |age=30  |
+--------+    +--------+    +--------+
  (리프 = 실제 행 데이터)


[세컨더리 인덱스 (email)]
+--------+
|  Root  |
+--------+
    |
+----------+    +----------+    +----------+
| Leaf 1   |<-->| Leaf 2   |<-->| Leaf 3   |
|a@a.com   |    |d@d.com   |    |g@g.com   |
|PK=3      |    |PK=1      |    |PK=7      |
+----------+    +----------+    +----------+
     |               |               |
     v               v               v
  클러스터드 인덱스에서 PK로 실제 행 조회 (2차 룩업)

---

4. 인덱스 스캔 방식

Full Table Scan

-- 인덱스 없거나, 옵티마이저가 풀스캔이 낫다고 판단할 때
SELECT * FROM orders;

• 테이블의 모든 행을 순서대로 읽음
• 소규모 테이블이나 대부분의 행을 반환해야 할 때 더 효율적일 수 있음
• EXPLAIN의 type 컬럼: ALL

Index Range Scan

-- 범위 조건
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';
SELECT * FROM users WHERE age >= 20 AND age <= 30;

• B+Tree에서 시작 키를 찾은 후, 리프 노드 연결 리스트를 따라 범위 끝까지 스캔
• 가장 일반적이고 효율적인 인덱스 사용 방식
• EXPLAIN의 type 컬럼: range

B+Tree Range Scan (age BETWEEN 20 AND 30):

루트에서 age=20 탐색 (Tree Traversal)
    |
    v
[Leaf: age=20, PK=5] --> [age=21, PK=12] --> [age=25, PK=3] --> [age=30, PK=8] --> STOP
                                              (리프 링크드 리스트 선형 스캔)

Index Full Scan

-- ORDER BY 절에 인덱스 컬럼만 사용, 또는 COUNT(*)
SELECT COUNT(*) FROM users;  -- PK 인덱스 풀 스캔
SELECT user_id FROM users ORDER BY user_id;

• 인덱스의 리프 노드를 처음부터 끝까지 전부 스캔
• Full Table Scan보다 효율적 (인덱스가 테이블보다 작음)
• EXPLAIN의 type 컬럼: index

Index Unique Scan

-- PK 또는 UNIQUE 인덱스로 단 1건 조회
SELECT * FROM users WHERE user_id = 42;

• B+Tree를 루트에서 리프까지 한 경로만 탐색
• 최대 1건만 반환
• EXPLAIN의 type 컬럼: const 또는 eq_ref

Index Skip Scan (MySQL 8.0+)

-- 복합 인덱스 (gender, age)가 있을 때
-- gender 조건 없이 age로만 검색
SELECT * FROM users WHERE age = 25;
-- MySQL 8.0 이전: idx_gender_age 미사용 → Full Scan
-- MySQL 8.0+: Skip Scan으로 인덱스 활용 가능

복합 인덱스에서 첫 번째 컬럼을 건너뛰고 두 번째 컬럼부터 조건을 적용하는 방식입니다.

인덱스 (gender, age):
[F, 20] [F, 25] [F, 30] [M, 20] [M, 25] [M, 30]

Skip Scan: gender 값의 고유 목록을 먼저 구한 뒤
- gender='F' AND age=25 범위 스캔
- gender='M' AND age=25 범위 스캔
두 결과를 합침

• 첫 번째 컬럼의 고유값(Distinct Value) 수가 적을 때 효과적
• EXPLAIN의 Extra 컬럼: Using index for skip scan

Loose Index Scan

-- GROUP BY와 함께 MIN/MAX를 사용할 때
SELECT gender, MIN(age) FROM users GROUP BY gender;

• 각 그룹의 첫 번째 레코드만 읽는 방식으로 인덱스를 성기게(loosely) 스캔
• 대량의 데이터에서 집계 함수 성능을 크게 향상
• EXPLAIN의 Extra 컬럼: Using index for group-by

---

5. 커버링 인덱스 (Covering Index / Index Only Scan)

동작 원리

쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있으면, 클러스터드 인덱스(실제 행)를 조회하지 않고 인덱스만으로 결과를 반환할 수 있습니다. 이를 커버링 인덱스 또는 Index Only Scan이라고 합니다.

-- 테이블: users(user_id PK, name, email, age, created_at)
-- 인덱스: idx_email_name ON users(email, name)

-- 이 쿼리는 커버링 인덱스 사용 가능
SELECT name FROM users WHERE email = 'kim@example.com';
-- email → 인덱스 탐색, name → 인덱스 리프에 이미 있음. 클러스터드 인덱스 조회 불필요!

-- 이 쿼리는 커버링 인덱스 불가
SELECT name, age FROM users WHERE email = 'kim@example.com';
-- age가 인덱스에 없어서 클러스터드 인덱스 조회 필요

왜 빠른가?

일반 세컨더리 인덱스 조회:
  세컨더리 인덱스 B+Tree 탐색 (I/O 3~4회)
      → PK 획득
      → 클러스터드 인덱스 B+Tree 탐색 (I/O 3~4회 추가)
      → 실제 행 반환
  총 I/O: 6~8회

커버링 인덱스:
  세컨더리 인덱스 B+Tree 탐색 (I/O 3~4회)
      → 리프 노드에서 바로 데이터 반환 (추가 I/O 없음)
  총 I/O: 3~4회

특히 대용량 데이터에서 2차 룩업을 제거하는 것만으로도 수십 배의 성능 향상이 가능합니다.

EXPLAIN에서 “Using index” 확인

EXPLAIN SELECT name FROM users WHERE email = 'kim@example.com';

+----+-------------+-------+------+----------------+----------------+
| id | select_type | table | type | possible_keys  | Extra          |
+----+-------------+-------+------+----------------+----------------+
|  1 | SIMPLE      | users | ref  | idx_email_name | Using index    |
+----+-------------+-------+------+----------------+----------------+

Extra 컬럼에 Using index가 표시되면 커버링 인덱스가 동작하는 것입니다.

반면 Using index condition은 Index Condition Pushdown(ICP)으로, 스토리지 엔진 레벨에서 조건을 필터링하는 최적화입니다(커버링 인덱스와 다름).

커버링 인덱스 설계 예시

-- 자주 실행되는 쿼리
SELECT user_id, name, status FROM orders WHERE user_id = 100 AND status = 'PAID';

-- 커버링 인덱스 설계: 조건 컬럼 + SELECT 컬럼 모두 포함
CREATE INDEX idx_user_status_name ON orders(user_id, status, name);
-- user_id, status: WHERE 조건
-- name: SELECT 컬럼 (커버링을 위해 추가)
-- user_id: PK이므로 InnoDB 세컨더리 인덱스에 자동 포함됨

---

6. 복합 인덱스 (Composite Index)

컬럼 순서의 중요성

복합 인덱스는 지정한 컬럼 순서대로 B+Tree 키가 구성됩니다.

-- (last_name, first_name, age) 순서의 복합 인덱스
CREATE INDEX idx_name_age ON employees(last_name, first_name, age);

이 인덱스의 리프 노드는 다음 순서로 정렬됩니다:

(Kim, Cheol, 25)
(Kim, Cheol, 30)
(Kim, Ji,    22)
(Lee, Minho, 28)
(Lee, Soo,   35)
(Park, Jun,  27)

최좌선 접두사 규칙 (Leftmost Prefix Rule)

복합 인덱스 (A, B, C)는 다음 조합에서만 유효하게 사용됩니다:

조건	인덱스 사용 여부
WHERE A = ?	사용 가능
WHERE A = ? AND B = ?	사용 가능
WHERE A = ? AND B = ? AND C = ?	사용 가능
WHERE A = ? AND C = ?	A만 사용, C는 인덱스 미사용
WHERE B = ?	사용 불가
WHERE B = ? AND C = ?	사용 불가
WHERE C = ?	사용 불가

-- idx_name_age: (last_name, first_name, age)

-- 사용 가능: Leftmost Prefix 충족
SELECT * FROM employees WHERE last_name = 'Kim';
SELECT * FROM employees WHERE last_name = 'Kim' AND first_name = 'Cheol';
SELECT * FROM employees WHERE last_name = 'Kim' AND first_name = 'Cheol' AND age = 25;

-- 부분 사용: last_name 범위 조건 이후 first_name 인덱스 탐색 불가
SELECT * FROM employees WHERE last_name LIKE 'K%' AND first_name = 'Cheol';
-- last_name LIKE 'K%'는 인덱스 사용, 그 다음 first_name은 범위 스캔

-- 사용 불가: 첫 번째 컬럼 누락
SELECT * FROM employees WHERE first_name = 'Cheol';
SELECT * FROM employees WHERE age = 25;

범위 조건의 영향

복합 인덱스에서 범위 조건(>, <, BETWEEN, LIKE 'prefix%') 이후의 컬럼은 인덱스 탐색에 활용되지 않습니다.

-- 인덱스: (age, name, status)
SELECT * FROM users WHERE age > 20 AND name = 'Kim' AND status = 'active';
-- age > 20: 인덱스 Range Scan (O)
-- name = 'Kim': age 범위 내에서 필터링 (인덱스 탐색 X, 행 레벨 필터)
-- status = 'active': 마찬가지로 행 레벨 필터

따라서 등호(=) 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 배치하는 것이 기본 원칙입니다.

-- 좋은 설계: 등호 조건 먼저
CREATE INDEX idx_status_name_age ON users(status, name, age);
SELECT * FROM users WHERE status = 'active' AND name = 'Kim' AND age > 20;
-- status: 인덱스 탐색 (O)
-- name: 인덱스 탐색 (O)
-- age > 20: 범위 스캔 (O) — 여기서 인덱스 탐색 종료

카디널리티와 컬럼 순서 설계

카디널리티(Cardinality)는 해당 컬럼의 고유값 수입니다.

-- 카디널리티 확인
SELECT
    COUNT(DISTINCT status) AS status_card,    -- 예: 3 (active, inactive, banned)
    COUNT(DISTINCT country) AS country_card,  -- 예: 50
    COUNT(DISTINCT user_id) AS user_id_card   -- 예: 1,000,000
FROM users;

일반적으로 카디널리티가 높은 컬럼(고유값 많음)을 앞에 배치합니다. 단, 쿼리 패턴이 최우선입니다.

-- 나쁜 설계: gender(카디널리티=2)가 앞에, user_id(고유)가 뒤에
CREATE INDEX idx_bad ON orders(gender, user_id);

-- 좋은 설계: 쿼리 패턴 고려
-- 쿼리: WHERE user_id = ? AND status = ?
CREATE INDEX idx_good ON orders(user_id, status);
-- user_id로 먼저 좁히고, status로 추가 필터링

실무 원칙 요약:

1. 등호(=) 조건 컬럼을 먼저
2. 범위 조건 컬럼을 나중에
3. 자주 사용되는 쿼리 패턴에 맞게 순서 결정
4. 카디널리티는 보조 기준 (쿼리 패턴 > 카디널리티)

---

7. 인덱스가 동작하지 않는 경우

인덱스가 있어도 옵티마이저가 사용하지 않거나 사용할 수 없는 경우가 있습니다. 이런 패턴을 인덱스 무력화(Index Suppression)라고 합니다.

함수/연산 적용 시

인덱스 컬럼에 함수나 연산을 적용하면 인덱스를 사용할 수 없습니다. 함수 결과로는 B+Tree를 탐색할 수 없기 때문입니다.

-- 인덱스: idx_created_at ON orders(created_at)

-- 잘못된 방법: 함수 적용 → 인덱스 무력화
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';
SELECT * FROM orders WHERE created_at + INTERVAL 1 DAY > NOW();

-- 올바른 방법: 컬럼에 직접 범위 조건
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
SELECT * FROM orders WHERE created_at >= '2026-01-01 00:00:00' AND created_at < '2026-01-02 00:00:00';

-- 인덱스: idx_price ON products(price)

-- 잘못된 방법: 산술 연산 적용
SELECT * FROM products WHERE price * 1.1 > 10000;

-- 올바른 방법: 상수 쪽을 계산
SELECT * FROM products WHERE price > 10000 / 1.1;

타입 불일치 (암시적 형변환)

-- 테이블 정의: phone VARCHAR(20), 인덱스 존재
-- 잘못된 방법: 정수로 비교 → 암시적 형변환 → 인덱스 무력화
SELECT * FROM users WHERE phone = 01012345678;

-- 올바른 방법: 문자열로 비교
SELECT * FROM users WHERE phone = '010-1234-5678';

MySQL은 VARCHAR 컬럼을 숫자로 비교하려고 할 때 컬럼 값 전체를 숫자로 변환합니다. 이는 인덱스 컬럼에 암시적 함수가 적용되는 것과 같습니다.

-- 반대 방향: 숫자 컬럼을 문자열로 비교
-- 인덱스: idx_user_id ON orders(user_id) — user_id는 INT
SELECT * FROM orders WHERE user_id = '100';  -- '100' → 100으로 변환 (이 경우는 OK)
-- INT 컬럼에 문자열을 비교할 때는 문자열이 숫자로 변환되므로 인덱스 사용 가능
-- 하지만 VARCHAR 컬럼에 정수를 비교할 때는 컬럼 전체가 형변환되어 인덱스 무력화!

LIKE ‘%keyword’

-- 인덱스: idx_name ON users(name)

-- 인덱스 사용 가능: 접두사 매칭 (Prefix Matching)
SELECT * FROM users WHERE name LIKE 'Kim%';

-- 인덱스 사용 불가: 전방 와일드카드
SELECT * FROM users WHERE name LIKE '%Kim';
SELECT * FROM users WHERE name LIKE '%Kim%';

B+Tree는 왼쪽부터 정렬되므로, 앞부분이 %이면 어디서부터 탐색해야 할지 알 수 없어 풀스캔이 됩니다. 전방 와일드카드 검색이 필요하면 FULLTEXT 인덱스나 Elasticsearch 같은 검색 엔진을 고려해야 합니다.

OR 조건

-- 인덱스: idx_status, idx_type 각각 존재

-- OR 조건: 경우에 따라 인덱스 활용 방식이 다름
SELECT * FROM orders WHERE status = 'PAID' OR type = 'ONLINE';
-- MySQL 옵티마이저가 Index Merge를 시도할 수 있으나
-- 두 인덱스를 각각 스캔 후 Union 하는 방식 → 효율 낮을 수 있음
-- EXPLAIN Extra에서 "Using union(idx_status,idx_type); Using where" 확인

OR 조건은 UNION으로 분리하는 것이 더 효율적인 경우가 많습니다:

-- OR를 UNION으로 분리
SELECT * FROM orders WHERE status = 'PAID'
UNION ALL
SELECT * FROM orders WHERE type = 'ONLINE' AND status != 'PAID';

NOT, != 연산

-- 인덱스: idx_status ON orders(status)

-- 인덱스 활용 어려움: 부정 조건
SELECT * FROM orders WHERE status != 'CANCELLED';
SELECT * FROM orders WHERE status NOT IN ('CANCELLED', 'REFUNDED');

부정 조건은 대부분의 행을 반환할 가능성이 높아 옵티마이저가 풀스캔을 선택합니다. 실제 반환 행이 적다면 인덱스를 사용할 수도 있지만, 일반적으로 부정 조건보다는 긍정 조건으로 바꿔 설계하는 것이 좋습니다.

NULL 처리

-- InnoDB는 NULL도 인덱스에 저장함 (IS NULL 조건에서 인덱스 사용 가능)
SELECT * FROM users WHERE age IS NULL;  -- 인덱스 사용 가능

-- 하지만 NOT NULL 조건은 대부분의 행을 포함하므로 풀스캔 가능성 높음
SELECT * FROM users WHERE age IS NOT NULL;

-- NULL을 포함한 복합 조건
SELECT * FROM users WHERE age IS NULL OR age > 30;
-- 옵티마이저에 따라 다름, 일반적으로 풀스캔 가능성

---

8. 인덱스 종류

UNIQUE 인덱스

-- 컬럼 값의 유일성 보장 + 인덱스 역할 동시 수행
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 또는 테이블 정의 시
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    email   VARCHAR(200) UNIQUE  -- 내부적으로 UNIQUE INDEX 생성
);

• UNIQUE 인덱스는 중복 값 삽입 시 즉시 오류 발생
• NULL은 UNIQUE 제약에서 예외: NULL은 중복 허용 (NULL != NULL)
• eq_ref 또는 const 조인 타입으로 매우 효율적인 조회

FULLTEXT 인덱스

-- 자연어 전문 검색을 위한 인덱스
CREATE TABLE articles (
    id      INT PRIMARY KEY,
    title   VARCHAR(500),
    content TEXT,
    FULLTEXT INDEX ft_idx (title, content)
);

-- FULLTEXT 검색 사용
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('데이터베이스 인덱스' IN NATURAL LANGUAGE MODE);

-- Boolean Mode
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+인덱스 -해시' IN BOOLEAN MODE);

• InnoDB 5.6+, MyISAM에서 지원
• 단어 단위로 역색인(Inverted Index) 구조 사용
• 한국어는 별도 파서(ngram) 필요

-- 한국어 지원을 위한 ngram 파서
CREATE FULLTEXT INDEX ft_idx ON articles(content) WITH PARSER ngram;

SPATIAL 인덱스

-- 지리 공간 데이터를 위한 R-Tree 기반 인덱스
CREATE TABLE locations (
    id    INT PRIMARY KEY,
    name  VARCHAR(100),
    coord POINT NOT NULL,
    SPATIAL INDEX sp_idx (coord)
);

INSERT INTO locations VALUES (1, '서울시청', ST_GeomFromText('POINT(37.5665 126.9780)'));

-- 공간 쿼리
SELECT name FROM locations
WHERE ST_Within(coord, ST_GeomFromText('POLYGON((...))'));

• B+Tree 대신 R-Tree 구조 사용
• 위도/경도, 다각형 등 2D 공간 데이터 검색에 특화
• 컬럼이 NOT NULL이어야 함

Hash 인덱스 (Memory 엔진)

-- Memory(HEAP) 스토리지 엔진에서 Hash 인덱스 사용
CREATE TABLE session_cache (
    session_id VARCHAR(64) PRIMARY KEY,
    data       TEXT,
    expires_at DATETIME
) ENGINE=MEMORY;
-- Memory 엔진 기본 인덱스는 Hash

-- InnoDB는 Adaptive Hash Index(AHI)를 내부적으로 자동 관리
-- 사용자가 명시적으로 생성할 수 없음
SHOW ENGINE INNODB STATUS;  -- AHI 사용 현황 확인 가능

Hash 인덱스 특성:

특성	Hash Index	B+Tree Index
등호(=) 검색	O(1) — 매우 빠름	O(log n)
범위 검색	불가	가능
정렬 활용	불가	가능
메모리 사용	낮음	높음

InnoDB의 Adaptive Hash Index(AHI)는 자주 접근되는 B+Tree 페이지에 대해 내부적으로 자동으로 해시 인덱스를 구축하여 성능을 향상시킵니다.

---

9. 인덱스와 정렬/그룹핑

ORDER BY + 인덱스 활용

인덱스는 이미 정렬된 순서로 데이터를 제공하므로, 적절한 인덱스가 있으면 별도 정렬(filesort) 없이 결과를 반환할 수 있습니다.

-- 인덱스: idx_age ON users(age)

-- 인덱스 순서 활용 (filesort 없음)
SELECT * FROM users ORDER BY age ASC;
SELECT * FROM users ORDER BY age DESC;  -- 역방향 스캔

-- 조건 + 정렬
SELECT * FROM users WHERE age > 20 ORDER BY age;  -- age 인덱스로 처리 가능

복합 인덱스와 ORDER BY:

-- 인덱스: (status, created_at)

-- 인덱스 활용 (filesort 없음): 순서와 방향이 일치
SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at ASC;
SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at DESC;

-- 인덱스 활용 불가 (filesort 발생): 순서 불일치
SELECT * FROM orders ORDER BY created_at;  -- status 조건 없이 created_at만
SELECT * FROM orders WHERE status = 'PAID' ORDER BY status, created_at;  -- 중복 불필요

-- 주의: ASC/DESC 혼합
SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at DESC;  -- 가능
-- MySQL 8.0부터 (status ASC, created_at DESC) 같은 혼합 방향 인덱스도 지원
CREATE INDEX idx_mixed ON orders(status ASC, created_at DESC);

GROUP BY + 인덱스 활용

-- 인덱스: (department, salary)

-- 인덱스 활용: 그룹 정렬이 인덱스 순서와 일치
SELECT department, AVG(salary) FROM employees GROUP BY department;
-- EXPLAIN Extra: Using index (커버링 + 그룹 활용)

-- Loose Index Scan 활용
SELECT department, MAX(salary) FROM employees GROUP BY department;
-- EXPLAIN Extra: Using index for group-by

filesort 회피 전략

-- EXPLAIN Extra에 "Using filesort"가 있으면 정렬 비용 발생

-- 전략 1: 정렬 컬럼을 인덱스에 포함
CREATE INDEX idx_dept_sal ON employees(department, salary);
SELECT * FROM employees WHERE department = 'Engineering' ORDER BY salary;

-- 전략 2: ORDER BY + LIMIT 조합에서 인덱스 효과 극대화
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- created_at 인덱스가 있으면: 마지막 10개만 읽고 종료 (매우 빠름)
-- 인덱스 없으면: 전체 정렬 후 10개 반환 (느림)

-- 전략 3: 페이지네이션에서 커버링 인덱스 + 지연 조인
-- 느린 방법
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 10;
-- created_at 인덱스가 있어도 100000개 행을 읽어 skip

-- 빠른 방법: 지연 조인 (Deferred Join)
SELECT o.* FROM orders o
INNER JOIN (
    SELECT order_id FROM orders ORDER BY created_at DESC LIMIT 100000, 10
) sub ON o.order_id = sub.order_id;
-- 서브쿼리는 커버링 인덱스로 order_id만 추출, 이후 실제 행 조회

---

10. 인덱스 설계 실무 가이드

선택도(Selectivity) 계산

선택도는 인덱스의 효율성을 나타내는 지표입니다.

선택도 = 고유값 수(Distinct Values) / 전체 행 수(Total Rows)

값이 1에 가까울수록 선택도가 높고, 인덱스 효율이 좋습니다.

-- 선택도 계산
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity
FROM orders;

-- 결과 예시:
-- status_selectivity: 0.0000 (3가지 값, 100만 행) → 인덱스 비효율
-- user_id_selectivity: 0.9500 (95만 명, 100만 행) → 인덱스 매우 효율적
-- email_selectivity: 0.9999 (거의 유일) → 인덱스 최적

일반적인 기준:

• 선택도 > 0.1 (10%): 인덱스 효율적
• 선택도 < 0.01 (1%): 인덱스 효과 의심스러움
• gender, boolean 같은 컬럼 단독 인덱스는 대부분 비효율

단, 쿼리 패턴과 조건 조합에 따라 선택도가 낮아도 복합 인덱스의 일부로 활용될 수 있습니다.

인덱스 개수 트레이드오프 (읽기 성능 vs 쓰기 비용)

-- 인덱스가 많을수록 INSERT/UPDATE/DELETE 비용 증가
-- 예: 인덱스 10개 있는 테이블에 INSERT → 10개 B+Tree 모두 갱신

-- 쓰기가 많은 테이블 (로그, 이벤트 등)
CREATE TABLE event_log (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT,
    event_type VARCHAR(50),
    created_at DATETIME DEFAULT NOW()
    -- 인덱스 최소화: 쓰기 성능 우선
);
-- 주로 user_id로만 조회한다면 단일 인덱스만
CREATE INDEX idx_user_id ON event_log(user_id);

-- 읽기가 많은 테이블 (상품, 사용자 등)
CREATE TABLE products (
    product_id  BIGINT PRIMARY KEY,
    category_id INT,
    price       DECIMAL(10,2),
    name        VARCHAR(200),
    status      VARCHAR(20),
    created_at  DATETIME
    -- 다양한 검색 패턴 지원
);
CREATE INDEX idx_category_price ON products(category_id, price);
CREATE INDEX idx_status_created ON products(status, created_at);
CREATE INDEX idx_name ON products(name);

실무 권장 사항:

• 테이블당 인덱스 5~6개 이하 유지 (상황에 따라 다름)
• 사용되지 않는 인덱스는 제거
• 자주 변경되는 컬럼에는 인덱스 최소화

인덱스 힌트 (USE INDEX, FORCE INDEX, IGNORE INDEX)

MySQL 옵티마이저의 판단을 오버라이드할 수 있습니다.

-- USE INDEX: 이 인덱스만 고려하도록 권고 (다른 인덱스 무시)
SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id = 100;

-- FORCE INDEX: 무조건 이 인덱스 사용 (풀 스캔 포함 다른 방법 고려 안 함)
SELECT * FROM orders FORCE INDEX (idx_created_at)
WHERE user_id = 100 ORDER BY created_at DESC;

-- IGNORE INDEX: 이 인덱스는 사용하지 말 것
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE status = 'PAID';

-- 힌트 범위 지정
SELECT * FROM orders USE INDEX FOR JOIN (idx_user_id)
JOIN users ON orders.user_id = users.user_id;

SELECT * FROM orders USE INDEX FOR ORDER BY (idx_created_at) ORDER BY created_at;

힌트 사용 시 주의사항:

• 통계가 부정확하거나 옵티마이저가 잘못된 계획을 선택할 때 임시방편으로 사용
• 데이터 분포가 바뀌면 힌트가 오히려 역효과
• 가능하면 통계 업데이트(ANALYZE TABLE)나 쿼리/인덱스 재설계로 해결

---

11. 인덱스 모니터링

EXPLAIN 핵심 컬럼 분석

EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.status = 'PAID' AND u.country = 'KR'
ORDER BY o.created_at DESC
LIMIT 100;

+----+-------------+-------+--------+------------------+----------------+---------+--------------------+------+-----------------------------+
| id | select_type | table | type   | possible_keys    | key            | key_len | ref                | rows | Extra                       |
+----+-------------+-------+--------+------------------+----------------+---------+--------------------+------+-----------------------------+
|  1 | SIMPLE      | u     | ref    | idx_country      | idx_country    | 203     | const              | 5000 | Using where                 |
|  1 | SIMPLE      | o     | ref    | idx_user_status  | idx_user_status | 12     | u.user_id, const  |    3 | Using index; Using filesort |
+----+-------------+-------+--------+------------------+----------------+---------+--------------------+------+-----------------------------+

핵심 컬럼 의미:

컬럼	의미
type	접근 방식 (아래 상세 설명)
key	실제 사용된 인덱스
key_len	사용된 인덱스 바이트 수 (복합 인덱스에서 몇 개 컬럼 사용했는지 파악)
rows	예상 스캔 행 수 (작을수록 좋음)
Extra	추가 정보

type 컬럼 값 (좋은 순서):

system > const > eq_ref > ref > range > index > ALL

type	의미	예시
system	테이블에 1행만 존재	시스템 테이블
const	PK 또는 UNIQUE로 1행 확정	WHERE id = 1
eq_ref	조인에서 PK/UNIQUE로 1행	JOIN ON a.id = b.id
ref	인덱스로 동등 조건	WHERE status = 'PAID'
range	인덱스 범위 스캔	WHERE age BETWEEN 20 AND 30
index	인덱스 풀 스캔	ORDER BY indexed_col
ALL	풀 테이블 스캔	인덱스 없거나 미사용

Extra 컬럼 주요 값:

Extra	의미
Using index	커버링 인덱스 — 매우 좋음
Using where	WHERE 조건으로 행 필터링
Using filesort	추가 정렬 필요 — 최적화 필요
Using temporary	임시 테이블 사용 — 최적화 필요
Using index condition	Index Condition Pushdown
Using join buffer	조인 버퍼 사용 — 조인 인덱스 검토

EXPLAIN ANALYZE (MySQL 8.0+):

-- 실제 실행하여 정확한 실행 계획과 시간 측정
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 10;

-> Limit: 10 row(s)  (cost=1250 rows=10) (actual time=0.523..0.531 rows=10 loops=1)
    -> Sort: orders.created_at DESC, limit input to 10 row(s) per chunk  (cost=1250 rows=5000) (actual time=0.521..0.521 rows=10 loops=1)
        -> Index lookup on orders using idx_status (status='PAID')  (cost=1250 rows=5000) (actual time=0.102..0.412 rows=5000 loops=1)

미사용 인덱스 탐지

-- performance_schema를 이용한 미사용 인덱스 확인
-- performance_schema가 활성화되어 있어야 함
SELECT
    t.object_schema,
    t.object_name,
    t.index_name,
    t.count_star AS total_access
FROM performance_schema.table_io_waits_summary_by_index_usage t
WHERE t.object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
  AND t.index_name IS NOT NULL
  AND t.index_name != 'PRIMARY'
  AND t.count_star = 0
ORDER BY t.object_schema, t.object_name, t.index_name;

-- sys 스키마를 이용한 미사용 인덱스 (더 편리)
SELECT * FROM sys.schema_unused_indexes;

-- 중복 인덱스 탐지
SELECT * FROM sys.schema_redundant_indexes;

인덱스 통계 (ANALYZE TABLE)

MySQL 옵티마이저는 인덱스 통계를 기반으로 실행 계획을 수립합니다. 통계가 부정확하면 잘못된 계획이 선택됩니다.

-- 통계 업데이트
ANALYZE TABLE orders;
ANALYZE TABLE users, products;  -- 여러 테이블 동시

-- InnoDB 통계 상태 확인
SELECT
    table_name,
    last_analyzed
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY last_analyzed;

-- 통계 정밀도 설정 (innodb_stats_persistent_sample_pages)
-- 기본값: 20 (20페이지 샘플링)
-- 대용량 테이블에서는 높일수록 정확하지만 ANALYZE 시간 증가
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';

-- 테이블별 통계 정밀도 설정
ALTER TABLE orders STATS_SAMPLE_PAGES = 50;
ANALYZE TABLE orders;

-- 인덱스 카디널리티 확인
SHOW INDEX FROM orders;

+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+
| orders |          0 | PRIMARY          |            1 | order_id    | A         |     1000000 | NULL     | NULL   |      |
| orders |          1 | idx_user_id      |            1 | user_id     | A         |       95000 | NULL     | NULL   |      |
| orders |          1 | idx_status       |            1 | status      | A         |           3 | NULL     | NULL   |      |
| orders |          1 | idx_user_status  |            1 | user_id     | A         |       95000 | NULL     | NULL   |      |
| orders |          1 | idx_user_status  |            2 | status      | A         |      100000 | NULL     | NULL   |      |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+

Cardinality 값이 실제 데이터와 크게 다르면 ANALYZE TABLE로 통계를 갱신해야 합니다.

---

정리: 인덱스 설계 체크리스트

[ ] 자주 실행되는 쿼리의 WHERE, JOIN, ORDER BY 컬럼 파악
[ ] 선택도(Selectivity) 계산 — 낮은 컬럼 단독 인덱스 지양
[ ] 복합 인덱스: 등호(=) 조건 컬럼 앞, 범위 조건 컬럼 뒤
[ ] 커버링 인덱스 가능 여부 검토 — SELECT 컬럼까지 인덱스에 포함
[ ] EXPLAIN으로 type=ALL, Extra=Using filesort/temporary 확인
[ ] 인덱스 무력화 패턴 점검 (함수적용, 형변환, LIKE '%', OR)
[ ] 미사용 인덱스 주기적 삭제
[ ] ANALYZE TABLE로 통계 갱신
[ ] 쓰기가 많은 테이블은 인덱스 수 최소화
[ ] EXPLAIN ANALYZE로 실제 실행 계획과 예상 계획 비교