2.인덱스 실전,멀티컬럼 인덱스 개념,실전

1. MySQL 인덱스 적용 실험: 100만 개 데이터에서 검색 속도 비교

MySQL에서 100만 개의 더미 데이터를 생성한 후, 특정 값을 검색하는 경우 인덱스 적용 전후의 성능 차이를 실험해본다.

 

(1) 테이블 생성

DROP TABLE IF EXISTS users; # 기존 테이블 삭제

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

 

(2) 100만개의 랜덤 데이터 삽입

이제 WITH RECURSIVE를 활용하여 1부터 100만까지 숫자를 생성한 후, 이를 기반으로 name과 age 데이터를 만들어 삽입한다.

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, age)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')),   -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    FLOOR(1 + RAND() * 1000) AS age    -- 1부터 1000 사이의 랜덤 값으로 나이 생성
FROM cte;

-- 잘 생성됐는 지 확인
SELECT COUNT(*) FROM users;

 

(3) 데이터가 정상적으로 삽입되었는지 확인

SELECT COUNT(*) FROM users;

SELECT * FROM users WHERE age = 23;

이 상태에서는 age 컬럼에 인덱스가 없기 때문에, MySQL은 모든 행을 순차적으로 검사(Full Table Scan)하여 데이터를 찾음. ->   실행 시간: 약 180~230ms

 

(4) 인덱스 설정후 조회

# 인덱스 생성
# CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);
CREATE INDEX idx_age ON users(age);

# 인덱스 조회
# SHOW INDEX FROM 테이블명;
SHOW INDEX FROM users;

인덱스가 적용되면서 기존 180~230ms에서 약 40ms로 성능이 크게 향상됨.
이는 Full Table Scan 대신, 인덱스를 활용하여 데이터를 빠르게 찾을 수 있기 때문이다.

인덱스를 직접 생성하게 되면 우리 눈에는 안 보이지만 왼쪽과 같은 표가 시스템 내부적으로 생성된다. 나이를 기준으로 정렬해놓은 표를 가지고 있기 때문에, 나이를 기준으로 데이터를 조회할 때 훨씬 빠르게 찾을 수 있었던 것이다. 

 

2. 클러스터링 인덱스(Clustering Index)란?

MySQL에서는 기본 키(PK, Primary Key)가 자동으로 인덱스 역할을 하며, 데이터가 정렬된 상태로 저장된다.
이렇게 원본 데이터 자체가 정렬되는 인덱스를 "클러스터링 인덱스(Clustering Index)"라고 한다.

 

클러스터링 인덱스의 특징

✔ PK(Primary Key)는 자동으로 클러스터링 인덱스가 된다.
✔ 데이터는 PK 기준으로 항상 정렬된 상태로 저장된다.
✔ 클러스터링 인덱스는 테이블당 하나만 존재할 수 있다.

즉, PK는 단순히 중복을 허용하지 않는 제약 조건이 아니라, 데이터가 실제로 정렬되는 기준이 된다

 

이것만 기억하자!

⭐ PK에는 인덱스가 자동으로 적용된다.
⭐ PK를 기준으로 데이터가 항상 정렬된 상태로 저장된다.
⭐ 이러한 정렬된 인덱스를 "클러스터링 인덱스"라고 부른다.

 

3. 제약 조건을 추가하면 자동으로 생성되는 인덱스 (UNIQUE)

UNIQUE 제약 조건을 추가하면 자동으로 인덱스가 생성된다.

DROP TABLE IF EXISTS users; # 기존 테이블 삭제

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE
);

#생성후 인덱스 조회
SHOW INDEX FROM users;

 

생성후 조회해보면 저렇게 UNIQUE 특징으로 인해 인덱스가 같이 생성된다.(PK는 클러스터링 설명대로 당연 생성된거)

 

UNIQUE 옵션을 사용하면 인덱스가 같이 생성되기 때문에 조회 성능이 향상된다!

 

4. 인덱스를 무식하게 많이 걸면 어떻게 될까?

인덱스가 이렇게 조회 성능은 좋은데 그럼 무조건 다 적용하는게 좋을까 고민될수 있는데, 결과는 데이터 추가하거나 수정등 쓰기작업일때는 성능이 저하된다

 

인덱스를 추가한다는 건 인덱스용 테이블이 추가적으로 생성된다는 뜻이다. 

그렇다면 인덱스를 추가하지 않은 상태에서 원래 테이블에만 데이터를 넣는 것보다,
인덱스를 추가한 상태에서 원래 테이블과 인덱스용 테이블 둘 다에 데이터를 넣어야 하는 게 더 느릴 수 밖에 없다. 

인덱스가 쓰기 성능에 미치는 영향

예시로 컬럼1~10까지 있는 A,B 테이블이 있을때 A에는 인덱스 하나도 안걸고 B에는 1~10 모든 컬럼에 인덱스 추가하고 각각의 테이블에 더미 데이터 10만개씩 추가할때 걸리는 속도 비교해보면 

A 테이블 (인덱스 없음)	0개	약 300ms
B 테이블 (1~10 모든 컬럼에 인덱스 추가)	10개	2,000ms → 최대 25,000ms

📌 결과:

• 인덱스가 많을수록 쓰기 작업이 느려진다.
• 조회 성능만 고려하지 말고, 쓰기 성능도 함께 고려해야 한다.

그러므로

최소한의 인덱스만 사용하려고 하자.

인덱스를 추가하면 조회 속도는 빨라지나, 쓰기(삽입, 수정, 삭제) 속도는 느려짐을 항상 기억하자.

 

5. 멀티컬럼 인덱스란?

멀티 컬럼 인덱스란, 2개 이상의 컬럼을 묶어서 설정하는 인덱스를 뜻한다. 즉, 데이터를 빨리 찾기 위해 2개 이상의 컬럼을 기준으로 미리 정렬해놓은 표이다. 

왼쪽 사진보면  이름,부서,나이 테이블이 있을때 부서와 이름 컬럼을 멀티 인덱스 정용하면 부서를 기준으로 먼저 오름차순으로 정렬한 뒤, 같은 부서의 값을 가진 데이터들 사이에서 이름을 기준으로 오름차순 정렬을 한다.

 

위에 사진 예시는 2개의 컬럼 가지고 인덱스 생성했으나 2개 이상 컬럼으로 인덱스 생성 가능하다.

 

6. 멀티컬럼 인덱스 실전

(1) 테이블 생성후 더미 데이터 넣기

# 1. 테이블 생성
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    이름 VARCHAR(100),
    부서 VARCHAR(100),
    나이 INT
);

# 2. 더미 데이터 생성
INSERT INTO users (이름, 부서, 나이) VALUES
('박미나', '회계', 26),
('김미현', '회계', 23),
('김민재', '회계', 21),
('이재현', '운영', 24),
('조민규', '운영', 23),
('하재원', '인사', 22),
('최지우', '인사', 22);

 

(2) 멀티 컬럼 인덱스 생성후 확인

CREATE INDEX idx_부서_이름 ON users (부서, 이름);

SHOW INDEX FROM users;

이렇게 부서,이름 순으로 멀티 컬럼 인덱스 설정해서 부서가 순서1 이름이 순서2이다.

 

(3) 조회할때 멀티 컬럼 인덱스 어떻게 활용하는지 과정 이해

SELECT * FROM users
WHERE 부서 = '인사' 
ORDER BY 이름;

이렇게 부서가 '인사'팀이고 이름 순으로 정렬한거 조회할때 

(부서, 이름)으로 인덱스를 만들어놔서 WHERE 부서 = ‘인사’를 만족하는 데이터들은 금방 찾을 수 있다. 그런 뒤에 ORDER BY 이름을 기준으로 데이터를 정렬해야 하는데 이미 정렬이 되어 있다. 그래서 위 SQL문을 통해 데이터를 가져오는 속도가 굉장히 빠를 수 밖에 없다. 

 

7. 멀티컬럼 인덱스 생성시 주의점

이전 예시 보면 부서-이름으로 멀티 컬럼 인덱스 설정해서 부서를 기준으로 먼저 정렬,다음 이름 기준으로 정렬인데

부서 컬럼만 놓고 봤을때는 부서 인덱스와 동일한 정렬 상태를 갖고 있다.

따라서 부서 인덱스 따로 생성 안하고 기존 만들었던 부서-이름 멀티 컬럼 인덱스 구조를 활용하면 부서의 인덱스를 활용하듯이 사용할 수 있다.

SELECT * FROM users
WHERE 부서 = '운영';

 

멀티 컬럼 인덱스를 일반 인덱스처럼 활용하지 못하는 경우도 있다.

위에 사진 자세히 보면 이름 기준으로 정렬이 되어 있지 않다. 이유는 먼저 부서를 가진 데이터끼리만 정렬을 시켜서 그렇다.

따라서 이렇게 이름 기준 조회 할경우 인덱스 활용 안하고 1~100만번 데이터 모두 조회하는 일반 조회한다.

SELECT * FROM users
WHERE 이름 = '이재현';

 

그러므로 멀티 컬럼 인덱스에서 일반 인덱스처럼 활용할 수 있는 건 처음에 배치된 컬럼들뿐이다. 

 

8. 멀티 컬럼 인덱스를 구성할 때 ‘소분류 → 중분류 → 대분류’ 컬럼순으로 구성하기

 

멀티 컬럼 인덱스를 만들 때는 컬럼의 순서를 신중하게 정해야 한다.
어떤 컬럼을 먼저 인덱스로 설정하느냐에 따라 성능 차이가 크게 날 수 있다.

 

직관적인 예시

대기업에서 "회계 부서의 박미나" 직원을 찾는다고 가정하자.

방법 1: 회계 부서에 있는 모든 사람을 확인한 후, 박미나를 찾는다.
방법 2: 박미나라는 이름을 가진 사람들을 먼저 찾고, 그중에서 회계 부서 소속을 확인한다.

대부분의 경우, "박미나"라는 이름을 가진 사람이 전체 직원보다 훨씬 적기 때문에 이름을 먼저 검색하는 것이 더 빠르다.

이처럼 데이터 중복도가 낮은(고유한 값이 많은) 컬럼을 먼저 탐색하는 것이 더 효율적이다.

멀티 컬럼 인덱스의 탐색 순서

멀티 컬럼 인덱스는 지정된 컬럼 순서대로 데이터를 탐색한다.

✔ (이름, 부서) 순서로 인덱스를 만들었다면
→ 먼저 이름을 찾고, 그 안에서 부서를 찾는다.

✔ (부서, 이름) 순서로 인덱스를 만들었다면
→ 먼저 부서를 찾고, 그 안에서 이름을 찾는다.

따라서, 데이터 중복도가 낮고(= 고유한 값이 많고), 더 세분화할 수 있는 컬럼을 앞쪽에 배치하는 것이 일반적으로 좋다.

(항상 그런 건 아니니 실행 계획과 SQL문 실행 속도를 측정해서 판단하도록 하자.)

 

- 멀티 컬럼 인덱스 컬럼의 순서는 매우 중요하다.

- 멀티 컬럼 인덱스에서 처음에 배치된 컬럼들은 일반 인덱스처럼 활용할 수 있다.

- 멀티 컬럼 인덱스를 구성할 때 데이터 중복도가 낮은 컬럼이 앞쪽으로 오는 게 좋다.

 

9. 커버링 인덱스란? 

SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스를 커버링 인덱스(Covering Index)라고 한다. 

위와 같은 users 테이블이 있고, name 인덱스가 있다고 가정하자. 그리고 아래 2개의 SQL문을 실행해야 한다고 가정하자.

SELECT id, created_at FROM users;
SELECT id, name FROM users;

첫번째 SQL문 보면 인덱스에는 created_at이 없으므로 실제 테이블의 데이터에 접근해야하고

두번째 SQL문 보면 id,name컬럼이 인덱스 표에 다 들어 있으므로, 실제 테이블 접근 안하고 조회할 수 있다.

즉, 실제 테이블에 접근하는 것 자체가 인덱스에 접근하는 것보다 속도가 느리다.

 

이 상황에서 SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스를 보고 커버링 인덱스(Covering Index)라고 표현한다.