[DB] MySQL InnoDB의 인덱스 생성 전략

📌 1. 기본키 인덱스 (Primary Key Index)

• 기본키(PK)가 설정된 컬럼에 자동으로 생성되는 인덱스이다.
• InnoDB에서는 기본키가 클러스터형 인덱스(Clustered Index)로 동작한다.
• 데이터가 기본키 기준으로 물리적으로 정렬되며 저장된다.

✅ 특징

• 테이블당 하나의 기본키만 설정 가능하다.
• NULL을 허용하지 않으며, 중복된 값을 가질 수 없음.
• 기본키 검색이 빠르며, 다른 인덱스에서 기본키를 사용해 데이터 검색을 수행함.

🚀 예제

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

• 위와 같이 id를 기본키로 설정하면, id를 기준으로 클러스터형 인덱스(Clustered Index)가 생성된다.

---

📌  2. 클러스터형 인덱스 (Clustered Index)

• InnoDB에서 기본키가 클러스터형 인덱스로 동작한다.
• 테이블의 데이터 자체가 기본키 순서대로 저장된다.
• 기본키를 기준으로 검색 시 가장 빠른 조회 속도를 제공한다.

✅ 특징

• 기본키(Primary Key)가 곧 클러스터형 인덱스.
• 데이터를 삽입할 때 기본키 순서대로 정렬됨.
• 클러스터형 인덱스를 기반으로 기본키를 통해 직접 데이터에 접근할 수 있음.
• 테이블당 하나의 클러스터형 인덱스만 존재 가능.
• 클러스터형 인덱스 = 기본키(PK)

🚀 예제

CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

 

• 위 테이블에서 id는 클러스터형 인덱스로 작동하며, 데이터가 id 순서대로 정렬되어 저장된다.

---

📌  3. 비클러스터형 인덱스 (Non-Clustered Index)

• 기본키 외의 다른 컬럼에 대해 생성하는 인덱스.
• 데이터의 물리적 저장 순서와 무관하며, 인덱스 테이블을 따로 관리한다.
• 인덱스를 통해 기본키를 찾고, 이후 해당 기본키를 이용해 데이터를 조회하는 방식으로 동작.
• 비클러스터형 인덱스는 (인덱싱된 컬럼 값 + 기본 키 값)을 저장

✅ 특징

• 테이블당 여러 개의 비클러스터형 인덱스를 생성 가능.
• 검색 속도를 높일 수 있지만, 기본키를 추가적으로 조회하는 과정이 필요하여 클러스터형 인덱스보다 느릴 수 있음.

🚀 예제

CREATE INDEX idx_name ON employees(name);

• name 컬럼에 대한 비클러스터형 인덱스(idx_name)를 생성.
• name을 기준으로 정렬된 별도의 인덱스 테이블이 생성됨.

🛠️ 비클러스터형 인덱스의 동작 방식

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 기본 키(PK)
    name VARCHAR(50),  
    salary INT,  
    department VARCHAR(50)
);

 

1. 비클러스터형 인덱스 생성

CREATE INDEX idx_salary ON employees(salary);

• idx_salary 비클러스터형 인덱스는 salary 값을 기준으로 정렬된 구조를 가짐.
• 하지만 실제 데이터(행 전체)는 포함하지 않고, 기본 키(id) 값만 저장함.

2. 인덱스 저장 방식 (예시)

id	name	salary	department
1	Alice	60000	IT
2	Bob	50000	HR
3	Charlie	70000	Finance

• employees 테이블 (실제 데이터)

salary	id (기본 키)
50000	2
60000	1
70000	3

• 비클러스터형 인덱스 (idx_salary)
• 정렬 기준: salary 기준으로 정렬됨.
• 값의 구성: salary 값과 id(기본 키) 값만 저장.
• 데이터 조회 방식:
  • salary = 60000을 찾으면, id = 1을 얻음.
  • 그다음, 기본 키(PK) 값을 사용해 클러스터형 인덱스에서 실제 데이터(행 전체)를 조회함.
  • 이 과정이 "인덱스 루킹(Index Lookup) → 테이블 액세스(Table Access)" 과정임.

---

📌 4. 보조 인덱스 (Secondary Index)

• 기본키가 아닌 컬럼에 대해 생성된 추가적인 인덱스.
• 비클러스터형 인덱스와 개념적으로 동일.
• 검색 시 보조 인덱스를 먼저 조회한 후, 기본키를 이용해 실제 데이터를 찾음.
• 비클러스터형 인덱스 = 보조 인덱스(Secondary Index)

🚀 예제

CREATE INDEX idx_salary ON employees(salary);

CREATE INDEX idx_name_salary ON employees(name, salary);

• salary에 대한 보조 인덱스를 생성.
• 보조 인덱스를 사용하여 salary 값이 특정 범위 내에 있는 데이터를 빠르게 조회 가능.
• 여러 개의 컬럼(복합 인덱스)에도 생성 가능

---

⚠️ 비클러스터형 인덱스 사용 시 주의점

1. 복합 인덱스는 선언된 순서대로 활용됨.

CREATE INDEX idx_name_salary ON employees(name, salary);

• 예를 들어, (name, salary)로 인덱스를 만들었다면 name을 먼저 검색하는 경우 최적화가 되지만, salary 단독 검색은 최적화되지 않음.

# 📍 잘 최적화되는 경우 (name이 먼저 사용됨)
SELECT * FROM employees WHERE name = 'John';

# 📍 비효율적인 경우 (salary만 사용하는 경우, 인덱스 사용 불가)
SELECT * FROM employees WHERE salary = 50000;

• 이 경우, MySQL은 idx_name_salary 인덱스를 사용하지 못하고 풀 테이블 스캔(Full Table Scan)이 발생할 수 있음.
• salary 단독 검색이 빈번하다면 별도의 인덱스 idx_salary를 생성하는 것이 낫다.

❌ 1. 인덱스 적용되지 않음 (Full Table Scan 가능성 높음)

# MySQL의 B-Tree 인덱스는 OR 연산자에 대해 복합 인덱스를 최적화하여 활용하지 못함.
SELECT * FROM employees WHERE name = 'John' OR salary = 5000;

• 즉, name = 'John' 조건만 idx_name_salary 인덱스를 활용할 수 있지만, salary = 5000이 단독으로 사용될 경우 인덱스를 활용할 수 없으므로 풀 테이블 스캔(Full Table Scan)이 발생할 가능성이 큼.

CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_salary ON employees(name);

• 대안으로 위와 같이 별도의 다른 인덱스를 추가하는 것이 좋음.

✅ 2. 인덱스 적용됨 (Index Range Scan 가능성 높음)

 

# 📌 name = "John" AND salary = 5000; 인덱스 적용 여부
SELECT * FROM employees WHERE name = 'John' AND salary = 5000;

• name = 'John' 조건으로 먼저 B-Tree를 탐색한 후, 해당 name 값을 가진 데이터 중에서 salary = 5000인 데이터를 추가 필터링함.

✅ 3. 인덱스 적용됨 (MySQL이 자동으로 name 먼저 검색하도록 최적화)

# 📌 salary = 5000 AND name = "John"; 인덱스 적용 여부
SELECT * FROM employees WHERE salary = 5000 AND name = 'John';

• MySQL의 실행 계획(Execution Plan)은 WHERE 절의 조건 순서와 관계없이 최적의 실행 계획을 선택함.
• 즉, 실제 실행 시 MySQL은 salary가 아닌 name을 먼저 검색하도록 변환하여 실행할 가능성이 높음.
• 따라서 name = 'John'을 기준으로 먼저 검색하고, 그 중에서 salary = 5000인 데이터만 필터링하여 반환

2. 인덱스를 너무 많이 만들면 오히려 성능 저하 가능

CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_age ON employees(age);

• 데이터 삽입·수정·삭제 시마다 인덱스도 같이 갱신해야 하므로 불필요한 인덱스는 피하는 것이 좋음.
• 너무 많은 인덱스가 존재하면, INSERT, UPDATE, DELETE 시 모든 인덱스를 갱신해야 하므로 오버헤드 발생
• 쿼리 실행 계획(Execution Plan)이 인덱스 선택을 잘못하면 오히려 성능 저하 가능

📍 비효율적인 경우 (인덱스가 너무 많아 갱신 부담 발생)

INSERT INTO employees (name, salary, department, age) 
VALUES ('Alice', 60000, 'IT', 30);

• 이 한 번의 INSERT 실행 시, MySQL은 idx_name, idx_salary, idx_department, idx_age 인덱스 모두를 갱신해야 함.
• 인덱스가 많아질수록 INSERT, UPDATE, DELETE 성능이 저하될 수 있음.

🚀 즉, 인덱스는 "필요한 곳에 최소한으로" 생성해야 최적의 성능을 낼 수 있음!

---

📊  클러스터형 vs 비클러스터형 인덱스 

1. 클러스터형 인덱스 

• 기본키 기반으로 데이터가 저장되는 방식.
• 데이터가 기본키 순서대로 물리적으로 정렬되어 있음.
• 검색 속도가 가장 빠름.

2. 비클러스터형 인덱스

• 기본키 외의 컬럼에 대해 생성되는 인덱스.
• 인덱스를 통해 기본키를 찾고, 기본키를 이용해 데이터 검색.
• 여러 개의 컬럼(복합 인덱스)에도 생성 가능
• 테이블당 여러 개 생성 가능.

비교 항목	클러스터형 인덱스	비클러스터형 인덱스
인덱스 구조	기본키를 기반으로 데이터가 정렬됨	데이터와 별도로 저장되는 인덱스
데이터 저장 방식	데이터 자체가 인덱스 순서대로 저장	인덱스 테이블에 기본키 참조 저장
검색 속도	기본키 검색이 가장 빠름	기본키를 추가로 조회해야 하므로 상대적으로 느림
개수 제한	테이블당 하나만 존재 가능	여러 개 생성 가능
인덱스 크기	데이터 크기가 커질 수 있음	인덱스 자체는 작음

---