mysql의 인덱스(r-tree,b-tree, 전문검색, 함수기반, 멀티밸류, 클러스터링, 유니크)

🔍 MySQL 인덱스 종류

MySQL 인덱스의 디스크 읽기 방식은 랜덤 I/O와 순차 I/O로 나뉩니다.

모든 인덱스는 디스크에서 자료를 더 효율적으로 읽기 위해 자료구조를 이용해 인덱스를 만들었습니다.

이를 이해하려면 디스크의 물리적 구조(HDD vs SSD), 인덱스 자료구조(B+Tree, Hash, Inverted Index)를 알아야 합니다.

 

🛠️ 디스크 읽기 기본 개념

1️⃣ 랜덤 I/O (Random I/O)

• 특징: 디스크 헤드가 다양한 위치로 이동하며 데이터 검색.
• 비용: 디스크 **시크 타임(Seek Time)**과 로테이션 딜레이 발생.
• 발생 예:
  • B-Tree 탐색: 인덱스 노드를 탐색하며 비순차적으로 디스크 접근.
  • 해시 인덱스: 해시 버킷이 분산되어 위치가 불규칙할 때.

2️⃣ 순차 I/O (Sequential I/O)

• 특징: 디스크 헤드가 연속된 블록을 읽으며 데이터 탐색.
• 비용: 한 번의 시크 후 연속 읽기 가능 → 성능 향상.
• 발생 예:
  • 클러스터드 인덱스(Clustered Index): PK를 기준으로 데이터가 정렬되어 순차적으로 읽을 때.
  • 범위 검색: WHERE age BETWEEN 20 AND 30과 같은 범위 질의.

[CPU I/O 요청]                 ↓         +-----------------+         | OS I/O Scheduler |         +-----------------+                 ↓         [HDD 컨트롤러]                 ↓      ┌────────┐      │       HDD 내부 구조     │      │─────────│      │  플래터(Platter)        │      │    ┌────┐       │      │    │ 데이터 블록 │        │      │    │(1234번 블록)│      │      │    └────┘       │      │                                 │      │  헤드(Head)             │      │  └─> 특정 트랙(Track)    │      └─────────┘

🛠️ 동작 단계 (랜덤 I/O 예시)

CPU 요청: 사용자가 SELECT * FROM users WHERE id=100을 요청. OS 처리: I/O 스케줄러는 LBA(Logical Block Address) 1234번 블록을 찾음. HDD 컨트롤러: LBA를 물리적 위치로 변환(트랙 5, 섹터 3). 헤드 이동(Seek Time): **액추에이터(Actuator)**가 헤드를 트랙 5로 이동(랜덤 I/O). 데이터 읽기: 헤드가 플래터의 자기장 변화를 감지하여 데이터 읽기.

🔍 HDD 디스크 I/O  핵심

📊 구분	🔄 HDD
I/O 발생	플래터 회전 및 헤드 이동 필요
랜덤 I/O 비용	높음(Seek Time 발생)
순차 I/O 성능	우수하지만 랜덤 I/O는 매우 느림
내부 구조	플래터+헤드+모터
데이터 저장 원리	자기장 변화로 데이터 기록

⚙️ 디스크 접근 과정: B+Tree 인덱스를 중심으로

B+Tree 인덱스는 MySQL InnoDB에서 가장 많이 사용되는 구조로, 데이터의 빠른 검색과 순차 접근을 모두 지원합니다.

🧱 구조 개요

루트 노드 → 브랜치 노드 → 리프 노드(데이터 위치 정보). 리프 노드: 클러스터드 인덱스의 경우 실제 데이터 저장, 비클러스터드 인덱스는 포인터만 저장.

📖 디스크 읽기 흐름 (예: SELECT * FROM users WHERE age = 25)

1️⃣ 루트 노드 탐색 (랜덤 I/O) DB는 **페이지 단위(기본 16KB)**로 디스크에서 데이터를 읽음. 루트 노드는 디스크의 특정 위치에 존재 → 랜덤 I/O 발생. 메모리에 없으면 디스크 시크 → 페이지 로드(OS Page Cache 또는 InnoDB Buffer Pool). 2️⃣ 브랜치 노드 탐색 (랜덤 I/O) 루트에서 적절한 브랜치 노드를 찾아 하위로 이동 → 랜덤 I/O 반복. 3️⃣ 리프 노드 탐색 (순차 I/O) 리프 노드에서 연속된 페이지를 읽으며 순차 I/O 발생. 클러스터드 인덱스의 경우 실제 데이터가 포함되어 있어 디스크 추가 접근 최소화.

---

🌳 B-Tree 인덱스 (Balanced Tree)

• 구조: B-Tree를 기반으로 하는 균형 트리 구조.
• 사용 대상: 일반적인 WHERE 조건, ORDER BY, GROUP BY, BETWEEN, =, <, <=, >, >= 등.
• 작동 원리: 루트 노드 → 중간 노드 → 리프 노드(데이터)로 탐색.
• 제약: 문자열의 왼쪽부터 일치해야 인덱스를 활용.
• 활용 예:
   

CREATE INDEX idx_name ON users(name); SELECT * FROM users WHERE name = 'Alice';

 

🌲 R-Tree 인덱스 (Spatial Index)

• 구조: 공간 데이터(GIS)용으로 설계된 다차원 인덱스.
• 사용 대상: GEOMETRY, POINT, POLYGON 등 공간 데이터.
• 작동 원리: 공간 객체를 최소 경계 사각형(MBR)으로 감싸서 인덱싱.
• 제약: MyISAM 엔진에서만 지원(기본 InnoDB는 지원 X).
• 활용 예:
   

CREATE SPATIAL INDEX idx_location ON locations (geo_point); SELECT * FROM locations WHERE ST_Contains(geo_area, POINT(10, 20));

 

📖 FULLTEXT 인덱스 (전문 검색 인덱스)

• 구조: 역색인(Inverted Index) 기반.
• 사용 대상: 긴 텍스트 필드(CHAR, VARCHAR, TEXT).
• 작동 원리: 단어를 분리하여 개별 토큰으로 저장 후 검색.
• 제약: MATCH() AGAINST() 구문 사용 필요.
• 활용 예

CREATE FULLTEXT INDEX idx_description ON articles (content); SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL Index');

 

🛠️ 자료구조: 역색인(Inverted Index)

역색인은 전통적인 B-Tree와 달리 단어 → 문서로의 매핑을 관리합니다.

 

⚙️ 구조 및 동작 원리

1. 토큰화(Tokenization): 문장을 단어 단위로 분리.
2. 역색인 생성: 각 단어를 키로, 해당 단어가 나타나는 행(Row) 리스트를 값으로 저장.
3. 검색: MATCH() AGAINST() 구문을 사용하여 토큰을 기반으로 검색.

🧠 자료구조 내부 동작

• 해시 맵(Hash Map) 또는 B-Tree 구조 사용(엔진에 따라 다름).
• InnoDB는 내부적으로 B+Tree를 기반으로 하며, MyISAM은 효율적인 압축 구조를 사용.

🧠 함수 기반 인덱스 (Functional Index) (MySQL 8.0 이상)

• 구조: 특정 함수의 결과를 인덱스로 생성.
• 사용 대상: 컬럼이 함수 연산에 자주 사용되는 경우.
• 작동 원리: 함수 결과를 인덱싱하여 함수 호출 시 성능 개선.
• 활용 예

CREATE INDEX idx_lower_name ON users((LOWER(name))); SELECT * FROM users WHERE LOWER(name) = 'alice';

 

🛠️ 자료구조: B+Tree

• MySQL은 함수 결과를 인덱스에 저장할 때도 B+Tree를 사용합니다.
• 인덱스 생성 시 계산된 결과를 저장하여 함수 연산 비용을 줄입니다.

⚙️ 동작 원리

1. 인덱스 생성 시 함수 결과를 리프 노드에 저장.
2. 검색 시 함수 연산 없이 인덱스 조회 수행.

🔢 멀티밸류 인덱스 (Multi-Valued Index) (MySQL 8.0 이상)

• 구조: 배열 같은 JSON 데이터 안의 다중 값에 대한 인덱스.
• 사용 대상: JSON 컬럼 안의 다중 값을 개별 인덱스로 관리.
• 작동 원리: JSON 배열의 각 원소를 독립적인 인덱스 항목으로 저장.
• 활용 예:
   

CREATE INDEX idx_tags ON posts((JSON_EXTRACT(tags, '$[*]'))); SELECT * FROM posts WHERE JSON_CONTAINS(tags, '["mysql"]');

 

🛠️ 자료구조: B+Tree

• JSON 배열 같은 비정형 데이터의 인덱싱을 위해 도입.
• 하나의 행이 여러 개의 인덱스 키를 가질 수 있음.

⚙️ 동작 원리

1. JSON 배열 내 원소를 개별 인덱스 키로 저장.
2. 검색 시 B+Tree를 통해 각 값 탐색.

 

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

• 구조: 데이터 자체가 인덱스의 리프 노드에 포함.
• 사용 대상: InnoDB의 Primary Key.
• 작동 원리: B+Tree의 리프 노드가 실제 데이터를 포함.
• 특징:
  • 기본 키가 클러스터드 인덱스로 자동 생성(InnoDB).
  • 기본 키를 기준으로 테이블 정렬.

CREATE TABLE users (     id INT PRIMARY KEY, -- 클러스터드 인덱스 자동 생성     name VARCHAR(100) );

 

⚔️ 클러스터드 vs 논클러스터드 인덱스

구분	클러스터드 인덱스	논클러스터드 인덱스
데이터 저장 방식	리프 노드에 실제 데이터 저장	리프 노드에 포인터만 저장
정렬 기준	기본 키(Primary Key)에 따라 정렬	별도 컬럼 기준으로 정렬
개수 제한	테이블당 1개	여러 개 생성 가능
성능 특성	범위 검색, PK 조회 시 빠름	특정 컬럼 조건 검색 시 효율적
사용 예	InnoDB 테이블의 PRIMARY KEY	보조 인덱스(Secondary Index)

🔹 활용 포인트:

• 클러스터드 인덱스는 범위 조회에 유리.
• 논클러스터드 인덱스는 특정 컬럼 검색 시 효율적.
• 복합 인덱스(Composite Index)를 통해 다중 컬럼을 활용할 수 있음.