[인덱스] MySQL 인덱스의 분류

인덱스의 종류

실제 행 데이터와의 관계 

리프페이지에 실제 행 데이터가 저장되는지를 기준

종류	리프 페이지에 저장되는 정보	특징
클러스터드 인덱스	인덱스 키 + 실제 행 데이터	- 리프 페이지에서 실제 행을 바로 확인 - 하나의 테이블에는 실제 행 데이터를 저장하는 기준이 하나만 존재하므로, 클러스터드 인덱스도 테이블에 하나만 존재 함. - 클러스터드 인덱스 결정 순서   1. PRIMARY KEY   2. 모든 컬럼이 NOT NULL인 첫 번째 UNIQUE 인덱스   3. 내부적으로 생성한 GEN_CLUST_INDEX
보조 인덱스	보조 인덱스 키 + 기본 키	- 클러스터드 인덱스가 아닌 나머지 인덱스 - 기본 키로 클러스터드 인덱스를 다시 탐색   1. 보조 인덱스 탐색   2. 기본 키 확인   3. 클러스터드 인덱스 재탐색   4. 실제 행 확인

 

탐색 구조와 검색 목적

인덱스가 값을 어떤 방식으로 찾는지, 어떤 검색에 사용되는지에 따른 분류

 

종류	주요 용도	특징
B+Tree 인덱스	동등 검색, 범위 검색, 정렬	값을 정렬된 트리 구조로 관리
Hash 인덱스	정확히 일치하는 값 검색	해시값으로 검색 위치 탐색 ※지원여부는 스토리지 엔진에 따라 다름.  InnoDB는 사용자가 생성하는 Hash Index를 사용할 수 있음.  MEMORY 엔진에서는 Hash 인덱스를 생성할 수 있음
Full-Text 인덱스	긴 문자열 안에 포함된 단어나 문서를 검색 EX) 제목이나 본문 내부의 단어 검색	일반 문자열 인덱스와 다른 전문 검색 구조 문법] -- 인덱스 생성 CREATE FULLTEXT INDEX <인덱스명> ON <테이블명> (<문자열_컬럼1>, <문자열_컬럼2>); -- 검색 문법  MATCH (<검색_컬럼1>, <검색_컬럼2>) AGAINST (<검색어> [<검색 모드>]) - 검색모드 : 자연어 검색 | 불리언 검색 | 검색어 확장
Spatial 인덱스	좌표, 영역, 공간 관계 검색	공간 데이터 검색에 특화 문법] CREATE SPATIAL INDEX idx_places_location ON places (location);

 

---

인덱스 구성 방식

컬럼 개수에 따른 구성

 

단일 인덱스

하나의 컬럼 또는 키 부분으로 구성

 

복합 인덱스

두 개 이상의 컬럼을 정해진 순서로 묶어 구성한 인덱스 (첫 번째 컬럼부터 순서대로 묶어 정렬)

왼쪽부터 이어지는 컬럼 조합을 활용하기 쉬움

 

왼쪽 우선 원칙 (왼쪽 접두부 원칙)

(<컬럼 A>, <컬럼 B>, <컬럼 C>)

1. 컬럼 A로 먼저 정렬
2. 컬럼 A가 같은 범위 안에서 컬럼 B로 정렬
3. 컬럼 A와 B가 모두 같은 범위 안에서 컬럼 C로 정렬
=> 첫 번째 컬럼 부터 순서대로 정렬되므로, 일반적으로 가장 왼쪽 컬럼부터 연속된 조건을 사용할 때 검색 범위를 효과적으로 줄일 수 있음.

 

EX]

CREATE INDEX idx_articles_status_created
ON articles (status, created_at);

-- 인덱스는 다음 순서대로 정렬됨 : status -> 같은 status 안에서 created_at
/**
status       created_at
----------------------------
draft        2026-07-01
draft        2026-07-10
published    2026-07-02
published    2026-07-08
published    2026-07-15
*/

-- 아래와 같은 쿼리를 실행하면
SELECT *
FROM articles
WHERE status = 'published'
  AND created_at >= '2026-07-05';

/**
조회과정)
1. status가 published인 범위 탐색
2. 그 범위 안에서 created_at이 조건에 맞는 부분 탐색
3. 실제 행 조회
4. 결과 반환
*/

 

인덱싱 하는 값에 따른 구성

 

일반 컬럼 인덱스

컬럼에 저장된 값을 그대로 인덱스 키로 사용

email 컬럼값 -> email 값을 인덱싱

 

접두부 인덱스

긴 문자열 전체가 아니라 앞부분의 일정 길이만 인덱싱

ex) 전체 문자열 mysql-index-structure => 접두부 5글자 mysql

 

- 인덱스 크기를 줄일 수 있지만, 앞부분이 같은 값이 많으면 검색 범위는 충분히 줄이지 못할 수 있음.

- MySQL은 문자열 컬럼의 앞부분만 인덱싱하는 접두부 길이를 지원함.

 

함수 기반 인덱스

컬럼 원본값이 아니라 함수나 표현식을 적용한 결과를 인덱싱

 

EX]

-- 별도의 생성 컬럼을 직접 선언하지 않고, 인덱스에 계산식을 바로 넣는다. 
CREATE INDEX idx_total_price
ON order_items ((price * quantity));

-- 조회할 때는 인덱스에 정의한 표현식을 사용함
SELECT * 
FROM order_items
WHERE price*quantity >= 100000;

 

생성 컬럼 인덱스 

계산 결과를 Generated Column으로 정의하고 해당 컬럼에 인덱스를 적용

: 계산 결과를 이름 있는 컬럼으로 생성 → 그 컬럼에 인덱스 생성 

- 실제 컬럼이 있으므로 쿼리에서 직접 사용할 수 있음.
cf) 함수 기반 인덱스 : 별도 컬럼을 직접 만들지 않음 → 인덱스 정의에 계산식을 바로 작성 

 

EX]

계산 결과를 담는 생성 컬럼을 직접 정의

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY,
    price INT NOT NULL,
    quantity INT NOT NULL,

    total_price INT
        GENERATED ALWAYS AS (price * quantity)
        VIRTUAL,

    INDEX idx_total_price (total_price)
);

-- 직접 사용 가능
SELECT id, total_price
FROM order_items
WHERE total_price >= 100000
ORDER BY total_price;

 

 

 

다중 값 인덱스

JSON 배열에 포함된 여러 요소를 각각 인덱스 항목으로 구성함

 

일반 인덱스는 하나의 행에서 하나의 인덱스 항목을 만드는데, 

다중 값 인덱스는 하나의 행에서 여러 인덱스 항목을 만들 수 있음

ex) 한 행의 JSON 배열

[10001, 10002, 10003]

 

사용자 행 1개
├─ 10001 인덱스 항목
├─ 10002 인덱스 항목
└─ 10003 인덱스 항목

 

문법

JSON 배열을 가리키는 표현식을 SQL 배열 타입으로 변환한 뒤, 그 배열의 각 요소를 인덱스 키로 지정하여 생성

CREATE INDEX 인덱스명
ON 테이블명 (
    (
    	-- JSON 배열에서 배열 추출 → 배열 요소를 지정한 SQL 자료형으로 변환 → 요소마다 별도의 인덱스 항목 생성
        CAST(
            JSON_배열_표현식 ( json_data->'$.values')
            AS 변환할_자료형 ARRAY
        )
    )
);

 

구성 요소	의미
인덱스명	생성할 인덱스 이름
테이블명	인덱스를 생성할 테이블
JSON_배열 표현식 (json_data →$values)	- json_data = JSON 데이터가 저장된 컬럼 - $.values = JSON 문서에서 배열이 위치한 경로
변환할_자료형	배열 요소를 변환할 SQL 자료형
ARRAY	배열 요소를 각각 인덱싱하도록 지정

 

 

생성과정

1. JSON 컬럼에서 인덱싱할 배열을 선택한다.
2. CAST를 사용해 배열 요소의 SQL 자료형을 지정한다
3. ARRAY를 지정해 각 요소가 독립적인 인덱스 키임을 나타낸다
4. 배열 요소마다 해당 행을 가리키는 인덱스 항목이 만들어진다.

 

예시

-- 다음과 같은 JSON 컬럼을 가진 테이블
CREATE TABLE customers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    profile JSON
);
/**
| 컬럼        | 자료형      | 설명                           |
| --------- | -------- | ---------------------------- |
| `id`      | `BIGINT` | 고객을 구분하는 기본 키                |
| `profile` | `JSON`   | 고객 이름과 우편번호 배열을 저장하는 JSON 컬럼 |

*/

-- 데이터는 다음과 같이 저장함
INSERT INTO customers (profile)
VALUES (
    JSON_OBJECT(
        'name', '철수',
        'zipcodes', JSON_ARRAY(10001, 10002, 10003)
    )
);
/**
데이터를 저장한 뒤 테이블 내용

| id | profile                                             |
| -: | --------------------------------------------------- |
|  1 | `{"name": "철수", "zipcodes": [10001, 10002, 10003]}` |

*/

-- 다중 값 인덱스 생성
CREATE INDEX idx_customer_zipcodes
ON customers(
	(CAST(profile->'$.zipcodes' AS UNSIGNED ARRAY))
);

-- 다음 JSON 배열은 [1001,1002,1003] UNSIGNED ARRAY로 변환되어 다음 세 개의 정수 인덱스 항목을 만든다
-- 1001, 1002, 1003
/**
| 인덱스 키 | 연결된 기본 키 |
| ----: | -------: |
| 10001 |        1 |
| 10002 |        1 |
| 10003 |        1 |

*/

 

 

ex) 여러 행이 있을 때

id	zipcodes
1	[10001, 10002, 10003]
2	[10003, 10004]
3	[10002, 10005]

 

다음과 같이 구성됨

10001 → id 1

10002 → id 1
10002 → id 3

10003 → id 1
10003 → id 2

10004 → id 2

10005 → id 3

 

-- 10003을 찾기 --

1003 인덱스 항목 탐색

→ id 1 확인

→ id 2확인

→ 두 행 조회

 

검색방법

기능	확인하는 내용
검색값 MEMBER OF(JSON_배열_표현식)	특정 값 하나가 배열에 포함되어 있는가
JSON_CONTAINS(검색_대상_JSON, 포함되어야_할_JSON) JSON_CONTAINS(JSON_문서, 포함되어야_할_JSON, JSON_경로)	지정한 값 또는 값들이 모두 포함되어 있는가
JSON_OVERLAPS(JSON_배열_표현식, JSON_ARRAY(검색값1, 검색값2))	두 JSON 값 사이에 하나라도 겹치는 값이 있는가

 

-- MEMBER OF 
/**
[customer table]

| id | name | zipcodes                |
| -: | ---- | ----------------------- |
|  1 | 철수   | `[10001, 10002, 10003]` |
|  2 | 영희   | `[10003, 10004]`        |
|  3 | 민수   | `[10002, 10005]`        |
*/

-- zipcodes 배열에 10003이 포함된 고객을 조회
SELECT * 
FROM customers
-- 각 행의 profile에서 zipcodes 배열을 가져온다 (배열의 요소 중 10003이 있는지 확인)
WHERE 10003 MEMBER OF (
	profile->'$.zipcodes'
);

/** 
[결과]
| id | zipcodes                | 결과               |
| -: | ----------------------- | ---------------- |
|  1 | `[10001, 10002, 10003]` | `10003`이 있으므로 조회 |
|  2 | `[10003, 10004]`        | `10003`이 있으므로 조회 |
|  3 | `[10002, 10005]`        | `10003`이 없으므로 제외 |

*/



-- JSON_CONTAINS()
/**
| id | name | zipcodes                |
| -: | ---- | ----------------------- |
|  1 | 철수   | `[10001, 10002, 10003]` |
|  2 | 영희   | `[10003, 10004]`        |
|  3 | 민수   | `[10002, 10005]`        |
*/

-- zipcodes 배열에 10002와 10003이 모두 포함된 행을 조회
SELECT *
FROM customers
WHERE JSON_CONTAINS(
    profile->'$.zipcodes',
    CAST('[10002, 10003]' AS JSON) -- JSON_ARRAY(10002, 10003) 이렇게 써도 됨
);

/**
결과
| id | zipcodes                | `10002` 포함 | `10003` 포함 | 결과 |
| -: | ----------------------- | ---------- | ---------- | -- |
|  1 | `[10001, 10002, 10003]` | O          | O          | 조회 |
|  2 | `[10003, 10004]`        | X          | O          | 제외 |
|  3 | `[10002, 10005]`        | O          | X          | 제외 |
*/



-- JSON_OVERLAPS()
/**
| id | name | zipcodes                |
| -: | ---- | ----------------------- |
|  1 | 철수   | `[10001, 10002, 10003]` |
|  2 | 영희   | `[10003, 10004]`        |
|  3 | 민수   | `[10002, 10005]`        |
*/

-- zipcodes 배열에 10003 또는 10005가 하나라도 포함된 고객을 조회
SELECT * 
FROM customers
WHERE JSON_OVERLAPS(
	profile->'$.zipcodes',
    JSON_ARRAY(10003,10005)
);

/**
결과
| id | zipcodes                | 공통 요소   | 결과 |
| -: | ----------------------- | ------- | -- |
|  1 | `[10001, 10002, 10003]` | `10003` | 조회 |
|  2 | `[10003, 10004]`        | `10003` | 조회 |
|  3 | `[10002, 10005]`        | `10005` | 조회 |

*/

 

---

 

제약조건과 인덱스

데이터의 고유성을 보장하는 제약조건과 이를 검사하기 위해 생성되는 인덱스와의 관계 

 

고유성 제약과 인덱스 

구분	중복	NULL	의미
PRIMARY KEY	허용하지 않음	허용하지 않음	각 행을 고유하게 식별 - 실제 행과의 관계 → 일반적으로 클러스터드 인덱스 - 탐색 구조 → 일반적으로 B+Tree
UNIQUE	허용하지 않음	nullable 컬럼은 여러 NULL 가능	컬럼 또는 컬럼 조합의 고유성 보장
비유니크 INDEX	허용	컬럼 정의에 따라 가능	중복을 허용하는 일반 조회용 인덱스

 

 

---

인덱스의 속성

 

정렬 방향

인덱스의 각 키 부분은 오름차순 또는 내림차순 방향을 가질 수 있음

 

문법

CREATE INDEX 인덱스명
ON 테이블명 (
	컬럼명 ASC|DESC
);

 

의의

복합 인덱스는 여러 컬럼이 정해진 순서와 방향으로 함께 정렬됨.

=> 단일 컬럼 인덱스에서는 역방향 스캔이 가능하므로 방향 처리가 크지 않을 수 있지만, 
여러 컬럼의 오름차순과 내림차순이 섞인 복합 정렬에서는 인덱스의 방향 조합이 중요함. 

 

 

옵티마이저 가시성

속성	의미
Visible 인덱스	옵티마이저가 일반적인 실행 계획 후보로 사용
Invisible 인덱스	인덱스는 유지하지만 일반 실행 계획 후보에서는 제외 - 인덱스를 실제로 삭제하기 전에, 해당 인덱스가 없는 것처럼 실행 계획을 테스트할 때 사용 할 수 있음.

 

 

---

쿼리와 인덱스의 관계

커버링 인덱스

특정 쿼리에 필요한 컬럼이 모두 인덱스 안에 포함되어 있어, 실제 행 데이터를 다시 조회하지 않고 인덱스만으로 결과를 반환할 수 있는 상태 

ex) 인덱스 (status, name)

SELECT name WHERE status =... => 필요한 값이 모두 인덱스에 있음 (커버링 가능)

SELECT * WHERE status = ... => 인덱스에 없는 값도 필요 (실제 행 조회 필요)

 

커버링 인덱스를 판달할 때 필요한 컬럼

쿼리 실행에 필요한 모든 컬럼을 확인해야 함

• SELECT에 필요한 컬럼
• WHERE 조건에 필요한 컬럼
• JOIN 조건에 필요한 컬럼
• ORDER BY에 필요한 컬럼
• GROUP BY 에 필요한 컬럼

장점

• 실제 행 조회를 줄일 수 있음
  일반 보조 인덱스는 다음 두 단계를 거칠 수 있음
  : 보조 인덱스 탐색 → 클러스터드 인덱스 재탐색
• 읽어야 하는 데이터 크기가 작을 수 있음
  실제 테이블 행에는 여러 컬럼이 들어있을 수 있는데, 인덱스에는 쿼리에 필요한 일부 컬럼만 저장되어 있을 수 있다. 
  테이블 전체 행보다 작은 인덱스 페이지를 읽는 것만으로 쿼리를 처리하면 읽기 비용을 줄일 수 있다.
• 정렬과 조회를 함께 처리할 수 있음
  인덱스 컬럼 순서가 WHERE과 ORDER BY에도 맞고, 조회 컬럼도 인덱스에 포함되어 있다면 
  조건 검색 + 정렬 + 결과 컬럼 반환을 함께 처리할 수 있음.

CREATE INDEX idx_orders_user_created_amount
ON orders (
    user_id,
    created_at DESC,
    amount
);

-- SELECT
SELECT created_at, amount
FROM orders
WHERE user_id = 10
ORDER BY created_at DESC
LIMIT 20;

-- 가능한 처리 흐름
-- 1. user_id = 10 범위 탐색
-- 2. created_at DESC 순서로 읽기
-- 3. 같은 인덱스에서 amount 확인 
-- 4. 20건을 찾으면 종료

 

관련설계

 

Q] 모든 컬럼에 인덱스를 넣으면 더 좋을까? 

A] 커버링 효과를 넣기 위해 너무 많은 컬럼을 인덱스에 포함하면 인덱스 자체가 커짐
=> 자주 실행되고 성능에 중요한 쿼리를 기준으로 설계해야 함. 

인덱스 컬럼 증가

→ 인덱스 레코드 크기 증가

→한 페이지에 저장되는 항목 수 감소

→인덱스 페이지 수 증가

→ 캐시 효율 저하 가능 

 

 

Q] 구조상 쿼리를 커버 할 수 있으면, 옵티마이저는 해당 인덱스를 선택하는가?

A] 아님, 다음 요소를 함께 판단한다. 

• 조건에 맞는 행의 비율
• 인덱스 크기
• 테이블 크기
• 통계 정보
• 다른 인덱스의 존재
• 정렬 및 LIMIT 여부
• 예상 페이지 읽기 비용

 

---

하나의 인덱스에는 여러 분류가 동시에 적용될 수 있다 지금까지 살펴본 분류는 서로 배타적인 인덱스 종류가 아니다. 하나의 인덱스를 서로 다른 기준으로 바라본 결과다.