인덱스(Index)는 친숙한 도구이지만, 설계를 실수하기 쉬운 것이기도 합니다. "만약을 위해" 열을 추가하거나, SELECT *로 커버링(Covering)을 깨뜨리거나, 사용되지 않는 인덱스가 쓰기 작업만 느리게 만드는 등의 실수는 흔히 발생합니다. AI 에이전트 시대에는 SQL 인덱스에 관한 명확한 규칙과 가이드를 준비함으로써 프로젝트를 더욱 빠르고 정확하게 만들 수 있습니다.

실무 경험과 신뢰할 수 있는 자료를 바탕으로, SQL 인덱스 작업을 위한 스킬을 정리한 리포지토리(Repository)를 공개했습니다. MySQL/InnoDB의 4가지 인덱스 스킬을 복합 인덱스 설계, 읽기 경로 최적화, 전문 검색, 운영 감사 및 유지보수까지 하나의 실전 로드맵으로 정리했습니다.

본 기사는 HieuNT44/indexing의 4가지 주요 스킬 파일(각 장당 1개 파일)을 기반으로 한 리포지토리 해설입니다. 아래에서는 orders 테이블의 케이스 스터디와 EXPLAIN 결과를 사용하여, 이론뿐만 아니라 즉시 사용할 수 있는 형태로 설명합니다.

리포지토리: https://github.com/HieuNT44/indexing

장	소스 파일	배우는 내용
제 1장	composite-indexes.md	복합 인덱스 (Composite Index) 설계, leftmost prefix, 열의 순서
제 2장	covering-indexes.md	인덱스 온리 스캔 (Index Only Scan), ICP와 커버링, EXPLAIN으로 검증
제 3장	fulltext-indexes.md	MATCH() AGAINST()를 이용한 전문 검색 (Full-text Search)
제 4장	index-maintenance.md	미사용·중복 인덱스 감사, 유지보수, 안전한 삭제

읽기 로드맵:

제 1장 (설계) → 제 2장 (읽기 최적화) → 제 3장 (특수 검색) → 제 4장 (운영)

• 서론: 각 장에서 사용하는 케이스 스터디
• 제 1장: 복합 인덱스 설계— equality를 먼저, range를 나중에. leftmost prefix를 준수할 것
• 제 2장: 커버링 인덱스 (Covering Index)—Using index = 쿼리가 인덱스만 읽음. SELECT *는 피할 것
• 제 3장: 전문 검색 인덱스 (Full-text Index)—키워드 검색. 복잡하다면 전용 검색 엔진으로
• 제 4장: 인덱스 유지보수 및 정리—performance_schema 감사, 불가시 인덱스 (Invisible Index), ANALYZE TABLE
• 요약

제 1·2장 설명에는 orders 테이블과 3가지 쿼리를 사용합니다:

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT UNSIGNED NOT NULL,
  ...

-- Q1: 테넌트 + 상태별 주문 목록, 기간으로 필터링
SELECT id, status, total, created_at
FROM orders
...

제 3장은 별도의 테이블인 articles (전문 검색용)를 사용합니다.

제 4장은 감사를 위해 다시 orders로 돌아갑니다.

복합 인덱스는 기초입니다. OLTP 테이블 인덱스의 대부분은 다중 열로 구성됩니다.

인덱스 (a, b, c)는 다음과 같이 사용할 수 있습니다:

쿼리	사용되는 인덱스 열
WHERE a	a
WHERE a AND b	a, b
WHERE a AND b AND c	a, b, c
WHERE a AND c	a만 사용 — b가 없으면 c는 필터로 사용할 수 없음

WHERE b만 사용하거나, WHERE b AND c에는 사용할 수 없습니다.

흔한 실수: INDEX (status, created_at)를 만들었는데도 WHERE created_at > ?가 풀 스캔 (Full Scan) 상태로 남는 경우에 대한 의문.

Q1의 경우:

-- Query: WHERE tenant_id = ? AND status = ? AND created_at > ?
CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at);

중요: 범위 술어 (range predicate: >, <, BETWEEN, LIKE 'prefix%', 큰 IN (...) 등)는 그 이후의 컬럼에 대한 필터링 활용을 중단시킵니다. 단, 범위(range) 조건 뒤의 컬럼은 다음과 같은 경우에 유용합니다:

• 커버링 인덱스 (Covering Index, 제 2장)
• 인덱스 접두사 (Index Prefix)와 일치하는 ORDER BY / GROUP BY

인덱스 (status, created_at)의 경우:

ORDER BY status ASC, created_at ASC -- 일치 (최적)
ORDER BY status DESC, created_at DESC -- 리버스 스캔 (Reverse Scan) OK
ORDER BY status ASC, created_at DESC -- 방향 혼재 → filesort 발생 가능성
...

Q1은 ORDER BY created_at DESC를 사용하므로, 인덱스에 created_at DESC를 고려하거나 정렬 방향에 맞춰 설계해야 합니다.

MySQL에는 인덱스 병합 (index merge) (union/intersection) 기능이 있지만, 일반적으로 복합 인덱스 (Composite Index)가 더 빠르고 안정적입니다. 인덱스 병합은 접두사 (prefix)를 공유하지 않는 컬럼 조합에 유용하지만, 오버헤드가 크고 확장성(scale)이 떨어집니다.

동등 조건 (equality) 컬럼 중에서는 가능하다면 카디널리티 (cardinality)가 높은 컬럼을 앞에 둡니다. 하지만 쿼리 패턴과 빈도가 순수한 선택도 (selectivity)보다 중요한 경우가 많습니다.

GROUP BY는 그룹화할 컬럼이 인덱스 접두사 (index prefix)와 일치할 때 이점을 얻습니다. MySQL은 정렬을 피하기 위해 인덱스를 사용할 수 있습니다 (Using index for group-by). 조건은 까다로운데, GROUP BY 컬럼은 인덱스의 연속된 접두사여야 하며, 중간에 범위 (range) 컬럼이 끼어들어 흐름을 끊어서는 안 됩니다.

Q3의 경우:

WHERE tenant_id = 1 AND created_at >= ... AND created_at < ...
GROUP BY status

권장 인덱스:

CREATE INDEX idx_orders_tenant_created_status
ON orders (tenant_id, created_at, status);

• tenant_id 동등 조건을 앞에, created_at 범위를 뒤에 두어 WHERE 절을 최적화합니다.
• 이 인덱스는 Q3 (SELECT status, COUNT(*))도 **커버 (cover)**하므로, Using index가 나타날 가능성이 있습니다 (제 2장 참조).
• 마지막의 status는 GROUP BY status의 정렬 회피를 보장하지 않습니다. 스캔 순서는 (created_at, status)이므로, status 단위로 정렬되어 있지 않습니다. 집계는 보통 해시 (hash) 또는 임시 테이블 (temp table)을 사용합니다.

인덱스 추가 후에는 EXPLAIN / EXPLAIN ANALYZE로 검증하십시오.

보충: (tenant_id, status, created_at)는 status의 값 종류가 적을 때 Loose Index Scan에 도움이 될 수 있지만, tenant_id + created_at 범위 조건만 있는 WHERE 절에는 최좌측 접두사 (leftmost prefix) 관점에서 불리합니다. Q3에서는 (tenant_id, created_at, status)를 우선하십시오.

-- 대응: WHERE user_id=?, WHERE user_id=? AND status=?, 
-- WHERE user_id=? AND status=? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_status_created
...

InnoDB의 세컨더리 인덱스(Secondary Index)는 항상 기본 키(PK)를 보유합니다. 명시하지 않아도 PK 룩업(Lookup)을 커버할 수 있는 경우가 있습니다 — 자세한 내용은 제2장에서 다룹니다.

제1장 요약: "인덱스로 만들 수 있을 것 같은" 열이 아니라, 실제 쿼리 형태(Query Shape)에 맞춰 복합 인덱스(Composite Index)를 설계해야 합니다.

올바른 복합 인덱스(제1장)를 설계했다면, 다음은 읽기 경로(Read Path) 최적화입니다 — 가능하다면 테이블 룩업(Table Lookup)을 피해야 합니다.

커버링 인덱스(Covering Index)는 쿼리에 필요한 모든 열을 포함합니다. InnoDB는 인덱스만으로 결과를 반환할 수 있으며, 이 경우 EXPLAIN의 Extra 필드에 Using index라고 표시됩니다.

-- Query: SELECT user_id, status, total FROM orders WHERE user_id = 42
-- Covering index (filter columns first, then included columns):
CREATE INDEX idx_orders_cover ON orders (user_id, status, total);

세컨더리 인덱스는 PK를 보유하므로, INDEX(status)만으로도 다음은 커버됩니다:

SELECT id FROM orders WHERE status = 2; -- id는 PK

인덱스 정의에 id를 추가할 필요는 없습니다.

Extra 플래그	의미
Using index condition	ICP (Index Condition Pushdown) — 인덱스로 필터링하지만 테이블 룩업이 발생함
Using index	커버링 (Covering) — 쿼리 전체를 인덱스만으로 처리하며, 룩업이 없음

Using index condition이 뜨고 Using index가 뜨지 않는다면 — 인덱스가 도움은 주고 있지만 커버하지는 못하고 있다는 뜻입니다. SELECT 열을 인덱스에 추가하는 것을 검토하십시오.

EXPLAIN SELECT user_id, status, total FROM orders WHERE user_id = 42;
-- Extra: Using index

관련된 기타 Extra 플래그:

플래그	의미	대응
Using filesort	인덱스를 통하지 않은 정렬	ORDER BY 열을 인덱스화 (제1장)
Using temporary	GROUP BY를 위한 임시 테이블	그룹화 열을 인덱스화 (제1장)
Using join buffer	조인(Join) 시 인덱스 부족	조인 열을 인덱스화

인덱스 추가 후에는 EXPLAIN ANALYZE (MySQL 8.0.18+)로 실제 성능을 측정하십시오.

사용해야 할 경우:

• 폭이 넓은 테이블에서 소수의 열을 빈번하게 읽을 때

피해야 할 경우:

• 쓰기(Write) 작업이 많은 테이블

• 결과 집합이 매우 넓은 경우 (TEXT/BLOB)

• 드물게 발생하는 쿼리라 트레이드오프(Trade-off)를 감수할 가치가 없는 경우

• 쓰기 증폭 (Write Amplification) — INSERT/UPDATE/DELETE 시마다 관련 인덱스를 업데이트해야 함

• 인덱스 크기 — 폭이 넓은 인덱스는 디스크와 버퍼 풀(Buffer Pool)을 소비함

• 유지보수 — 큰 인덱스는 ALTER TABLE 재구축 시 시간이 오래 걸림

• 신규 인덱스를 만들기보다 기존 인덱스에 열을 추가하십시오 — 순서: 필터링 열을 앞쪽에, 커버링 열을 뒤쪽에 배치

• 추가 후 EXPLAIN을 통해 Using index를 확인하십시오

• 함정: SELECT *는 커버링을 깨뜨립니다 — 필요한 열만 SELECT 하십시오

-- BAD
SELECT * FROM orders WHERE user_id = 42;
-- GOOD
...

제2장 요약: 복합(제1장) + 커버링(제2장) + EXPLAIN = 읽기 경로 설계의 삼위일체입니다.

전체 텍스트 검색(Full-text Search)은 별개의 종류의 인덱스입니다 — 일반적인 OLTP의 B-tree를 대체할 수 없습니다. 텍스트 키워드 검색에 사용됩니다.

ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);
-- 자연어 모드 (기본값, 관련도 순 정렬)
SELECT title, body,
...

MySQL 전문 검색 (Full-text Search)은 단순한 키워드 검색에 적합합니다. 복잡한 랭킹 (Ranking), 퍼지 매칭 (Fuzzy Match), 대규모 문서 검색에는 Elasticsearch, Meilisearch 등 전용 엔진을 검토하십시오.

함정 (Pitfall)	상세 내용
최소 단어 길이	기본 3자 (innodb_ft_min_token_size). 변경 후에는 FULLTEXT 인덱스를 drop/recreate 해야 함
불용어 (Stopword)	일반적인 단어는 제외됨. innodb_ft_enable_stopword, innodb_ft_user_stopword_table로 커스텀 가능 — 인덱스 생성 전에 설정하고 rebuild 필요
부분 일치 불가	LIKE '%term%'과 달리 토큰 전체가 필요함 (boolean의 * 제외)
MATCH()는 인덱스와 일치해야 함	MATCH(title, body)를 사용하려면 (title, body) 구성의 FULLTEXT 인덱스가 필요함
Boolean 모드에서 + 미사용	필수 단어가 없으면 → 대부분의 인덱스에 매칭되어 속도가 느려짐
쓰기 오버헤드 (Write Overhead)	전문 검색은 쓰기 비용을 증가시킴 — 쓰기가 빈번한 테이블에서는 주의

제 3 장 요약: 텍스트 검색은 전문 검색 (Full-text Search)을 사용합니다. OLTP의 filter/join은 B-tree (제 1–2 장)를 그대로 유지합니다.

설계 후 (제 1–3 장), 운영 환경에서는 **정기적인 감사 (Audit)**가 필요합니다 — 불필요한 인덱스는 읽기에 도움이 되지 않으면서 쓰기만 느리게 만듭니다.

-- performance_schema가 필요함 (MySQL 5.7+ 에서 기본 활성화)
-- 「미사용」 = 최근 재시작 이후 read/write 없음
SELECT object_schema, object_name, index_name, COUNT_READ, COUNT_WRITE
...

쓰기는 발생하지만 읽기는 없는 인덱스 — 쿼리에 이득이 없는 오버헤드:

SELECT object_schema, object_name, index_name, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
...

COUNT_READ = 0

하지만 UNIQUE/PK 용으로 필요한 인덱스가 있습니다. 카운터는 재시작 시 리셋되므로 — 1 영업 주기 (Business Cycle) 이상 관찰한 후에 drop 하십시오.

(a)

는 (a, b)

가 있다면 중복입니다 — leftmost prefix로 커버됩니다. (a, b)

와 (a, c)

는 중복이 아닙니다 — 수동 검토가 필요합니다.

SELECT table_schema, table_name,
redundant_index_name, redundant_index_columns,
dominant_index_name, dominant_index_columns
...

SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
...

INSERT, UPDATE, DELETE가 발생할 때마다 모든 인덱스를 업데이트합니다:

INSERT— 세컨더리 인덱스(Secondary Index)당 1회 쓰기 -
UPDATE— 인덱스 컬럼 변경 시 관련 인덱스를 모두 업데이트 -
DELETE— 모든 인덱스에서 엔트리(Entry) 삭제

InnoDB는 change buffer를 통해 일부를 지연시키지만, 과도한 인덱스는 쓰기 처리량 (Write Throughput)을 저하시킵니다.

테이블당 인덱스 수	권장 사항
1–5	일반적
6+	중복성 및 사용 현황 감사

옵티마이저 (Optimizer)는 카디널리티 (Cardinality)에 의존합니다. 대량의 데이터 변경 후에는:

ANALYZE TABLE orders;

통계를 업데이트하며, 테이블을 재구축하지 않습니다.

OPTIMIZE TABLE orders;

InnoDB에서는 실질적으로 테이블과 인덱스를 재구축합니다. 큰 테이블에서는 느리므로 주의가 필요합니다.

DROP 하지 않고 인덱스 삭제를 시도하기:

ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- 1~2주간 관찰 후, 문제가 없다면 DROP INDEX idx_status
ALTER TABLE orders ALTER INDEX idx_status VISIBLE; -- 롤백 (Rollback)

불가시 인덱스 (Invisible Index)는 쓰기 작업 시 여전히 유지 관리됩니다. 단지 옵티마이저로부터 숨길 뿐입니다.

온라인 DDL (Online DDL, 내장형):

ALTER TABLE orders
ADD INDEX idx_status (status),
ALGORITHM=INPLACE, LOCK=NONE;

pt-online-schema-change / gh-ost — 초거대 규모 테이블이나 높은 쓰기 부하가 발생하는 환경에 적합합니다 (트레이드오프: 운영의 복잡성, 권한, 트리거/binlog).

• 테이블당 1~5개의 인덱스는 통상적입니다. 6개 이상은 감사가 필요합니다.
• 매월: performance_schema와 빈번한 쿼리의 EXPLAIN을 조합하여 확인하십시오.

운영 환경에서 인덱스를 DROP 하기 전에는 반드시 팀과 확인하십시오.

제4장 요약: 유지 관리를 통해 루프를 완성합니다 — 측정 → 감사 → 불가시 인덱스로 테스트 → 제어된 DROP.

장	답하는 질문
1 — 복합	여러 열의 인덱스를 어떻게 배치할 것인가?
...

실무 워크플로우:

제1장 설계 → 제2장 EXPLAIN 검증 → 제3장 (검색이 필요한 경우) → 제4장 정기 감사

최상의 인덱스는 플래너 (Planner)가 선택하고, EXPLAIN이 증명하며, 운영 환경에서 측정 가능한 것입니다. 단순히 "빨라질 것 같다"고 생각되는 것이 아닙니다.

여기까지 읽어주셔서 진심으로 감사합니다.

만약 **"시스템 개발을 의뢰하고 싶다", "기술적인 상담을 하고 싶다", "AI를 활용한 과제 해결 아이디어가 있다"**와 같은 고민이 있으시다면, 언제든 저희 TOMOSIA VIETNAM에 문의해 주세요.

• 💻 소프트웨어 수탁 개발 (Web, 앱, 시스템)

• 🤖 AI 솔루션 (챗봇, 이미지 처리, LLM 응용)

• 📱 모바일 앱 개발 (iOS / Android)

• 🔌 Fintech / IoT 개발

• 🔧 브릿지 SE (BrSE) 지원 (일본어로 원활한 커뮤니케이션 지원)

• ISO/IEC 27001 (정보 보안) 인증 취득 – 안심하고 맡기실 수 있습니다.

• 일본어 대응 팀 – 일본의 문화와 비즈니스 관습을 이해하는 멤버가 직접 대응합니다.

• "Win-Win, Happy Together" 문화 – 장기적인 파트너십을 소중히 여깁니다.

🔗 공식 사이트: tomosia.com