개요
과거 프로젝트를 진행하면서 spring과 서버에 대해서는 공부를 집중적으로 했지만 DB에 대해서는 적게 공부하였기 때문에 DB에 대한 공부를 하던 중 index 실습중에 마주친 문제와 index가 왜 필요한지에대해서 기술하기 위해 작성한 글이다.
Index란?
DB에서 데이터를 빠르게 검색하고 접근하기 위해 테이블의 하나 또는 여러 column을 이용하여 생성하는 자료 구조로 B-Tree 또는 B+Tree구조로 이뤄어 졌고 책의 목차 또는 색인처럼 동작하며 full Table Sca하는 대신, 인덱스를 통해 데이터가 저장된 위치를 즉시 찾아갈 수 있게 합니다
index의 목적
조회(SELECT) 작업의 성능을 크게 향상시키기 위해(그렇기에 사입, 삭재, 갱신시 오버해드가 발생할 수 있다. 그렇기에 전략적으로 사용해야 한다.)
백과사전 비유로 index 설명
DB 테이블 (Table): 백과사전의 '본문 전체'입니다. (수많은 데이터가 순서 없이, 혹은 특정 순서로 저장되어 있는 곳)
DB 인덱스 (Index): 백과사전의 '맨 뒤에 있는 찾아보기 (색인)'입니다.
데이터 검색 (Query): 인덱스가 없는 경우: 백과사전에서 특정 단어(예: '양자역학')를 찾으려면 첫 페이지부터 마지막 페이지까지 한 장씩 넘겨가며 눈으로 직접 찾아야 합니다. (이것이 Full Table Scan)
인덱스가 있는 경우: 먼저 색인 페이지를 펼칩니다. 색인에서 '양자역학'을 찾으면 옆에 '357페이지'라고 적혀 있습니다. 바로 357페이지로 넘어가 원하는 정보를 즉시 찾습니다. (이것이 Index Scan)
실습 진행
- repo clone
- repo의 init.sql 문 실행 (DB에 테이블과 row 생성)
- full scan과 range 조회 SQL 실행 후 시간과 결과 비교
마주친 문제
1. CTE 동작 안 됨
실습을 진행하기 위해서는 DB에 대량에 데이터가 필요했고 CTE를 사용해서 sql을 만들어 init.sql을 구정했다. 하지만 CTE 사용에서 에러가 발생했다.
MySQL 8.0 신기술 CTE
<문제 내용>
WITH RECURSIVE CTE를 이용하여 30만 개 숫자를 생성하는 과정에서 발생
ERROR 1137 (HY000): Can't reopen table: 'd4'
<문제 원인>
1. MySQL이 CTE를 “재사용 가능 데이터셋"으로 보지 않는 구조이지만 임시 테이블이라고 생각하고 사용하였다. (MySQL에서 CTE를 "쿼리 구조를 단순화하는 도구"로만 사용할 것을 권장한다)
2. CTE가 INSERT 대상 테이블과 충돌,INSERT … SELECT 구문 안에서 CTE가 동시에 참조되면 MySQL은 동일한 CTE를 2번 오픈하려다 실패함.
[에러 sql]
-- 실행 중 MySQL이 내부적으로 CTE를 두 번 읽으려 할 때,
-- "Can't reopen table" 오류가 발생함
WITH RECURSIVE customer_seq AS (
SELECT 0 AS id
UNION ALL
SELECT id + 1
FROM customer_seq
WHERE id + 1 < @customer_target
)
INSERT INTO customer_seq
SELECT ... FROM customer_seq;<문제 해결>
재귀 CTE를 완전히 제거하고 숫자 생성 TEMP TABLE 방식으로 모두 교체
CREATE TEMPORARY TABLE seq_100k (id INT PRIMARY KEY);
INSERT INTO seq_100k (id)
SELECT a.N + b.N * 1000 AS id
FROM
(SELECT @row1:=@row1+1 AS N FROM information_schema.columns, (SELECT @row1:= -1) r LIMIT 1000) a,
(SELECT @row2:=@row2+1 AS N FROM information_schema.columns, (SELECT @row2:= -1) r LIMIT 100) b
LIMIT 100000;2. 조회시 범위 지정
<문제 내용>
city와 created_at에 복합 인덱스가 있음에도 불구하고, IGNORE INDEX를 통한 full scan에 총 응답 시간이 range보다 빠른 것을 확인
-- 문제의 쿼리 1: 인덱스 사용 (하지만 더 느림)
-- Total Time: 380 ms
SELECT *
FROM customers
WHERE city = 'Seoul'
AND created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- 문제의 쿼리 2: 인덱스 무시 (하지만 더 빠름)
-- Total Time: 155 ms
SELECT *
FROM customers IGNORE INDEX (idx_customers_city_created_at)
WHERE city = 'Seoul'
AND created_at BETWEEN '2024-01-01' AND '2024-12-31';<문제 원인>
1. 조회한 데이터가 많음
EXPLAIN을 통해 확인한 결과, 옵티마이저는 인덱스를 사용했지만(type: range) 조건에 해당하는 데이터가 약 2,000건 이상으로 많았다.
2. Bookmark Lookup 사용
SELECT * 때문에, 1번에서 찾은 2,000여 개의 PRIMARY KEY를 가지고 실제 테이블에 2,000번 접근하여 나머지 컬럼(id, name, email 등)을 가져왔다.
이 2개 과정에서 발생하는 대량의 Random I/O 비용이 테이블 전체를 순차적으로 읽는 Sequential I/O 비용보다 더 컸기 때문에 성능 역전 현상이 발생한 것이었다.
<문제 해결>
"커버링 인덱스"로 성능 극대화로 해결 Covering Index로 쿼리 성능 개선하기
-- AS-IS: 모든 컬럼 조회
SELECT * FROM customers ...
-- TO-BE: 필요한 컬럼만 명시
SELECT id, name, email, city, created_at FROM customers
--------------------------------------------------------
-- AS-IS: 기존 인덱스
INDEX idx_customers_city_created_at (city, created_at)
-- TO-BE: 커버링 인덱스
CREATE INDEX idx_customers_cover ON customers (city, created_at, id, name, email);적용 후, EXPLAIN 결과의 Extra 필드에 Using index가 표시되었고 fetching 시간이 줄며 index를 사용한 조회가 더 효율적인 것을 실험하였다.
SELECT SQL_NO_CACHE id, name, email, city, created_at
FROM customers
WHERE city = 'Seoul'
AND created_at BETWEEN '2025-10-01' AND '2025-10-02 23:59:59'
LIMIT 10
10 rows retrieved starting from 1 in 156 ms (execution: 35 ms, fetching: 121 ms)
SELECT SQL_NO_CACHE id, name, email, city, created_at
FROM customers IGNORE INDEX (idx_customers_cover)
WHERE city = 'Seoul'
AND created_at BETWEEN '2025-10-01' AND '2025-10-02 23:59:59'
LIMIT 10
10 rows retrieved starting from 1 in 227 ms (execution: 18 ms, fetching: 209 ms)
느낀점
index가 당연히 빠를거라고 간다하게 실습한 내용인데 잘못된 index 적용으로 full scan이 더 빠른 결과를 보게 되었다. 개발자의 실수가 이렇게 치명적일 수 있구나 다시 한 번 느끼게 되었고 테스트 하지 않았다면 잘못을 몰랐을 거라는 것에 테스트에 중요성을 더욱 더 깨닮게 되었다.
참고한 자료
