MySQL 인덱스 기초를 100만 건으로 확인하기: 단일·멀티 컬럼·커버링 인덱스와 쓰기 비용

API 서버 체크리스트에서 슬로우 쿼리 로그를 켜 두라고 적었다. 로그에 느린 쿼리가 잡혔을 때 가장 먼저 꺼내는 도구가 인덱스다. 그런데 "인덱스를 걸면 빨라진다"는 말만 알고 있으면, 아무 컬럼에나 인덱스를 걸고 쓰기 성능이 떨어지는 걸 뒤늦게 발견하게 된다.

이 글은 MySQL에 더미 데이터를 넣고 직접 측정하며 정리한 인덱스 기초다. 측정 환경은 다음과 같다.

MySQL 8.0.46(Docker 공식 mysql:8.0 이미지, OrbStack), InnoDB 버퍼 풀은 기본값 128MB
macOS 26.6.2 arm64(Apple M5 Pro), 2026-10-23 측정
시간은 서버 세션에서 쿼리를 보내고 결과를 다 받기까지 걸린 시간(NOW(6)의 차이)이고, 같은 쿼리를 7번(INSERT는 5번) 반복해 중앙값을 적었다

컴퓨터마다 절대값이 다르니 전후 비율을 보면 된다.

인덱스는 "미리 정렬해 둔 표"다

인덱스는 특정 컬럼을 기준으로 미리 정렬해 두고, 원본 행의 위치를 가리키는 별도의 자료 구조다. 책 뒤의 색인과 같다. InnoDB는 B+Tree로 이 정렬을 유지한다.

알아 둘 전제가 하나 있다. InnoDB 테이블은 기본 키(PK) 순서로 데이터 자체가 저장된다. 이걸 클러스터형 인덱스라고 하고, 테이블당 하나뿐이다. 그 밖에 직접 만드는 인덱스는 모두 보조 인덱스로, "컬럼 값 → PK"를 담고 있다. 그래서 보조 인덱스로 찾은 뒤에는 PK로 원본 행을 한 번 더 찾아가야 한다. 이 사실이 뒤에 나올 커버링 인덱스의 핵심이다.

실험 데이터 만들기

재귀 CTE로 100만 건을 한 번에 넣는다.

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

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')), FLOOR(1 + RAND() * 1000)
FROM cte;

cte_max_recursion_depth 기본값은 1000이라, 만들 행 수만큼 올려 줘야 한다.

인덱스 전후 비교

SELECT * FROM users WHERE age = 23;   -- 중앙값 64ms

CREATE INDEX idx_age ON users (age);

SELECT * FROM users WHERE age = 23;   -- 중앙값 0.8ms

나이 값이 1~1000에 고르게 퍼져 있으니 23세는 약 1,000명이다(이 실행에서는 1,033명). 인덱스가 없으면 100만 행을 전부 읽고(Full Table Scan), 인덱스가 있으면 age = 23인 구간만 찾아간다.

구분	7회 측정 범위	중앙값
인덱스 없음	63.8~66.2ms	64.1ms
`idx_age` 있음	0.8~1.5ms(첫 실행이 1.5ms)	0.8ms

같은 결과에 약 80배 차이가 났다. 참고로 100만 건을 넣는 데 1,461ms, idx_age를 만드는 데 1,271ms가 걸렸다.

반면 SELECT * FROM users처럼 전체를 읽는 쿼리는 인덱스와 상관없이 그대로다. 인덱스가 만들어진 뒤에도 전체 조회는 중앙값 399ms(382.6~449.9ms)였다. 인덱스는 읽을 행 수를 줄여 줄 때만 의미가 있다.

인덱스의 대가: 쓰기가 느려진다

인덱스는 공짜가 아니다. 행을 넣거나 고치거나 지울 때마다 모든 인덱스의 정렬도 함께 갱신해야 한다. INT 컬럼 10개(값은 0~999의 난수)짜리 테이블 두 개를 만들어 비교했다. 한쪽은 PK만, 다른 쪽은 PK에 컬럼마다 인덱스를 하나씩 걸었다.

테이블	인덱스 수	10만 건 INSERT
`test_table_no_index`	PK 1개	중앙값 208ms (179~218ms)
`test_table_many_indexes`	PK + 컬럼별 10개	중앙값 747ms (721~787ms)

인덱스를 10개 더 걸자 삽입이 약 3.6배 느려졌다(5번씩 측정, 10만 건 INSERT). 그런데 정작 column3 <= 100 조회는 중앙값 15.1ms에서 7.2ms로 약 2배만 빨라졌다. 값이 0~999에 퍼져 있어 조건에 맞는 행이 10%(10만 건 중 10,021건)나 되기 때문이다. EXPLAIN을 보면 인덱스가 없을 때는 type: ALL로 10만 행 가까이(99,875행으로 추정)를 읽고, 인덱스가 있을 때는 type: range에 idx_c3를 써서 18,850행으로 추정한다. 거르는 힘이 약한 컬럼의 인덱스는 이득이 크지 않다.

결론은 단순하다. 조회 조건에 실제로 쓰이고, 걸러 내는 힘이 큰 컬럼에만 인덱스를 건다. 쓰기가 많은 로그성 테이블이라면 인덱스 하나하나를 더 신중하게 고른다.

UNIQUE 제약은 인덱스를 함께 만든다

UNIQUE 제약을 걸면 MySQL은 중복을 검사하기 위해 고유 인덱스를 자동으로 만든다. 이메일처럼 유일해야 하고 조회 조건으로도 자주 쓰는 컬럼이라면, 일반 인덱스를 따로 만들 필요 없이 UNIQUE만으로 충분하다. 반대로 같은 컬럼에 UNIQUE와 일반 인덱스를 둘 다 걸면 쓰기 비용만 두 배가 된다.

멀티 컬럼 인덱스는 순서가 전부다

두 개 이상의 컬럼을 묶어 정렬한 인덱스다.

CREATE INDEX idx_dept_name ON users (department, name);

SELECT * FROM users
WHERE department = '인사'
ORDER BY name;

이 인덱스는 먼저 department로 정렬하고, 같은 부서 안에서 name으로 정렬해 둔다. 그래서 위 쿼리는 부서로 범위를 찾은 뒤 이미 이름순으로 정렬된 상태를 그대로 읽어, 별도 정렬 작업 없이 끝난다.

순서에서 나오는 규칙 세 가지를 기억하면 된다.

앞쪽 컬럼은 단독 인덱스처럼 쓸 수 있다. (department, name) 인덱스는 WHERE department = ?에도 쓰인다.
뒤쪽 컬럼만으로는 쓰기 어렵다. WHERE name = ?만 있으면 이 인덱스는 거의 도움이 안 된다. 전화번호부를 이름으로만 찾을 수 없는 것과 같다.
같은 값이 적은(카디널리티가 높은) 컬럼을 앞에 두는 것이 일반적으로 유리하다. 다만 등호 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 두는 원칙이 더 우선이다. 범위 조건 뒤에 오는 컬럼은 인덱스로 걸러지지 않기 때문이다.

그리고 단일 인덱스로도 충분히 빠르다면 멀티 컬럼 인덱스를 만들지 않는다. 인덱스가 커질수록 쓰기와 메모리 비용도 커진다.

커버링 인덱스: 원본을 읽지 않는 쿼리

앞에서 보조 인덱스는 "컬럼 값 → PK"만 들고 있다고 했다. 그런데 쿼리에 필요한 컬럼이 모두 인덱스 안에 있다면, 원본 행을 찾아갈 필요가 없다. 이런 인덱스를 커버링 인덱스라고 한다.

CREATE INDEX idx_age ON users (age);

-- 원본 행까지 읽어야 한다(name이 인덱스에 없음)
SELECT id, name FROM users WHERE age = 23;

-- 인덱스만 읽고 끝난다(age와 PK인 id는 인덱스에 있음)
SELECT id, age FROM users WHERE age = 23;

같은 환경에서 EXPLAIN을 실행하면 SELECT id, name은 Extra가 비어 있고(type: ref, key: idx_age, 1,033행 추정), SELECT id, age는 Extra: Using index로 나온다. 걸린 시간은 각각 중앙값 0.8ms(0.7~~2.7ms)와 0.1ms(0.1~~0.2ms)였다. EXPLAIN의 Extra 칸에 Using index가 보이면 커버링 인덱스로 처리된 것이다. 목록 API에서 SELECT * 대신 필요한 컬럼만 고르는 습관이 성능에도 이득인 이유가 여기에 있다.

정리

상황	판단
WHERE·JOIN·ORDER BY에 자주 쓰이고 값이 다양한 컬럼	인덱스 후보
값의 종류가 적은 컬럼(성별, 상태 플래그)	단독 인덱스 효과가 작다
쓰기가 매우 많은 테이블	인덱스 수를 최소로
여러 조건을 함께 쓰는 쿼리	멀티 컬럼 인덱스, 등호 컬럼을 앞에
목록 조회	필요한 컬럼만 SELECT해서 커버링 인덱스 활용

인덱스를 걸었는데도 느리다면, 옵티마이저가 그 인덱스를 실제로 쓰고 있는지부터 확인해야 한다. 그 방법인 EXPLAIN 읽기와 인덱스가 무시되는 패턴은 별도 글에서 이어서 정리한다.