데이터 사이언스/MySQL
B-Tree 인덱스 Full Table Scan EXPLAIN 실행계획 함수에 의한 인덱스 무효화 PRIMARY UNIQUE FOREIGN KEY
윤슬새벽
2025. 6. 23. 13:10
반응형
1. 📌 인덱스란?
| 항목 | 설명 |
| 정의 | 테이블에서 원하는 데이터를 빠르게 찾기 위한 색인 구조 |
| 구조 | MySQL 기준 대부분 B-tree 기반 (Balanced Tree) |
| 비유 | 책의 색인(index), 도서관 카드 분류표 |
| 본질 | 검색 속도 향상 ⬆️, 쓰기 성능 저하 ⬇️, 저장 공간 증가 ⬆️ |
2. ✅ 인덱스 사용 여부 확인 방법
| 방법 | 목적 | 예시 |
| SHOW INDEX FROM 테이블명 | 인덱스가 존재하는지 확인 | SHOW INDEX FROM employees; |
| EXPLAIN SELECT ... 또는 Ctrl + Alt + X | 쿼리 실행 시 인덱스를 사용하는지 확인 | EXPLAIN SELECT * FROM employees WHERE last_name = 'KIM'; |
| INFORMATION_SCHEMA.STATISTICS | 전체 메타정보 조회 | SELECT * FROM information_schema.statistics WHERE table_name = 'employees'; |
✅ SELECT 결과에서 인덱스 정보가 직접 보이는 건 아니고,
B-tree 인덱스가 존재하면 실행계획 상에서 사용 여부(key)에 나타나는 것입니다.
3. ✅ 인덱스 생성 (추가) 방법
| 구문 | 설명 |
| ALTER TABLE employees ADD INDEX idx_colname (colname); | 일반 인덱스 추가 |
| ALTER TABLE employees ADD UNIQUE (email); | 유일 인덱스 추가 |
| ALTER TABLE employees ADD INDEX idx_multi (col1, col2); | 복합(다중) 인덱스 추가 |
| ALTER TABLE employees ADD PRIMARY KEY (id); | 기본키 인덱스 추가 |
📌 CREATE INDEX 구문도 있지만, ALTER TABLE 방식이 실무에서 더 흔하게 사용됨
4. ✅ 왜 ALTER로 인덱스를 설정하나?
| ✅ 기존 테이블 구조를 그대로 유지하면서 인덱스 추가 가능 |
| ✅ 실무에서는 초기 테이블 설계 후, 분석을 통해 인덱스를 점진적으로 최적화 |
| ✅ ALTER로 인덱스를 추가하면 제약조건 없이 성능 최적화 목적만으로도 가능 |
5. ✅ 인덱스를 “탄다”는 표현 = 실제 사용됨
| 항목 | 설명 |
| 의미 | 실행계획(Execution Plan)에서 쿼리가 실제로 인덱스를 사용하고 있음 |
| 확인 | EXPLAIN의 key 컬럼에 인덱스명이 나타나면 ✅ |
| 반대 | key = NULL, type = ALL이면 ❌ 인덱스를 안 탐 (Full Table Scan) |
6. ✅ 인덱스를 못 타는 대표적 상황 (함수/연산)
| 예시 | 설명 |
| WHERE CHAR_LENGTH(name) > 5 | ❌ 함수 사용 → 인덱스 무효 |
| WHERE salary + 100 > 2000 | ❌ 연산 사용 → 인덱스 무효 |
| LIKE '%abc' | ❌ 접미어 와일드카드 → 인덱스 무효 |
| OR 조건 | 일부 인덱스 미사용 시 전체 무효 |
✅ 해결책:
- 계산 결과를 별도 컬럼(len_name)에 저장하고 인덱스를 따로 건다
- MySQL 8.0 이상에서는 Functional Index도 가능
7. ✅ 실무 우회 전략 예시 (함수 피해서 인덱스 타게 만들기)
ALTER TABLE employees ADD COLUMN len_first_name INT;
UPDATE employees SET len_first_name = CHAR_LENGTH(first_name);
ALTER TABLE employees ADD INDEX idx_len_first_name (len_first_name);
SELECT * FROM employees WHERE len_first_name >= 8;8. ✅ 인덱스는 반드시 KEY에만 거는가?
| 질문 | 답변 |
| 인덱스는 KEY에만 걸 수 있나요? | ❌ 아무 컬럼에도 걸 수 있음 |
| PRIMARY/UNIQUE KEY는 자동 인덱스 생기나요? | ✅ 항상 생성됨 |
| FOREIGN KEY는 자동 인덱스 생기나요? | ❌ 보통 별도로 인덱스 생성 필요 |
| 일반 컬럼에도 인덱스 추가 가능? | ✅ ALTER TABLE ... ADD INDEX (...) 로 추가 가능 |
9. ✍️ 오늘의 실습 예시 복습
| 내용 | 요약 |
| EXPLAIN SELECT * FROM employees WHERE last_name = 'KIM'; → type = ALL, key = NULL | ❌ 인덱스 안 탐 |
| ALTER TABLE employees ADD INDEX (last_name); → 이후 EXPLAIN 재실행 | ✅ 인덱스 탐 (key = idx_last_name) |
| UPDATE employees SET len_first_name = CHAR_LENGTH(first_name); | 함수 우회용 별도 컬럼 준비 |
| SELECT * FROM employees WHERE len_first_name >= 8; | ✅ 인덱스 사용 가능 쿼리로 변경됨 |
🔚 핵심 요약 Takeaways
- ✅ 인덱스는 "빠른 읽기, 느린 쓰기, 늘어나는 공간"을 교환하는 구조
- ✅ 단순 SELECT로는 인덱스 사용 여부 판단 불가 → EXPLAIN 필수
- ✅ 함수, 연산은 인덱스를 무효화 → 우회하거나 MySQL 8.0 이상에서는 Functional Index 활용
- ✅ 실무에서는 인덱스를 타게 만드는 튜닝 능력이 핵심 경쟁력
1. 🔧 EXPLAIN 또는 Ctrl + Alt + X 를 사용하는 이유
| 항목 | 설명 |
| 목적 | 쿼리를 실제로 실행하지 않고 DB가 어떤 경로로 데이터를 조회할지 미리 분석 |
| 실무 효과 | 인덱스 사용 여부, 풀스캔 여부, 조인 방식 등을 판단하여 성능 개선 포인트 식별 가능 |
| 시험 포인트 | 실행 계획에 대한 해석 (특히 type, key, rows 등 항목 분석 문제 자주 출제) |
2. EXPLAIN 주요 항목 (시험 빈출)
| 항목 | 의미 | 인덱스 사용 여부 |
| type | 접근 방식 (ALL, ref, range, const) | ALL이면 인덱스 미사용 |
| possible_keys | 사용할 수 있는 인덱스 목록 | 인덱스가 설정되어 있어야 나옴 |
| key | 실제 사용된 인덱스 이름 | 여기에 인덱스명이 나오면 '인덱스를 탄다' |
| rows | 예측되는 탐색 row 수 | 값이 작을수록 성능 좋음 |
| Extra | "Using where", "Using index" 등 최적화 힌트 | Using index = 커버링 인덱스 사용 중 |
✅ 시험문제 예: "EXPLAIN 결과 type = ALL 이 의미하는 것은?" → 정답: Full Table Scan
3. ✅ 성능 향상 vs 저하 요소 요약
| 요소 | 성능 영향 | 설명 |
| 인덱스 사용 (type = ref 이상) | 향상 | 조회 건수 줄고 탐색 속도 빨라짐 |
| Full Table Scan (type = ALL) | 저하 | 전체 테이블 훑기 |
| 함수 또는 연산 사용 | 저하 | WHERE 조건에서 인덱스 무효화 |
| 복합 인덱스 순서 위반 | 저하 | 인덱스 일부만 사용되거나 무시됨 |
| 정렬 없는 커버링 인덱스 (Using index) | 향상 | 인덱스만으로 데이터 해결 → 테이블 접근 자체 생략됨 |
4. ✅ 시험에 자주 나오는 키워드
- EXPLAIN 실행계획 해석
- Full Table Scan vs Index Scan
- B-Tree 인덱스 구조 특징
- 함수 사용 시 인덱스 무효화
- 복합 인덱스 설계 및 순서 중요성
- 커버링 인덱스(Using index)의 의미
✍️ 기출 예제 패턴 (실무형 + 이론형)
Q. 다음 SQL 실행계획에서 성능 병목 가능성이 있는 항목은?
→ 보기 중 type = ALL, key = NULL 정답
Q. EXPLAIN 결과에서 key에 인덱스명이 나타나지 않는 이유는?
→ 인덱스 없음, 혹은 함수/연산 사용으로 인덱스 무효화
✅ 인덱스가 출제되는 주요 시험 목록
| 시험 | 인덱스 출제 여부 | 설명 |
| 📘 정보처리기사 / 산업기사 | ✅ 매우 자주 출제 | 데이터베이스 파트 핵심 개념. 정의 + B-Tree 구조 + 장단점 |
| 🧠 정보처리기술사 | ✅ 심층 출제 | 인덱스 구조, 옵티마이저, 힌트 등 실무 적용과 연결 |
| 🏛️ 9급/7급 전산직 공무원 | ✅ 빈출 | SQL/DBMS 단원에서 EXPLAIN, 인덱스 종류, 속도비교 등 |
| 🏢 공기업 NCS (코레일, 한전 등) | ✅ 자주 나옴 | 실무형 SQL 기출, 데이터 조회 성능 비교 문제 |
| 🧪 SQLD (SQL 개발자 자격시험) | ✅ 자주 나옴 | 쿼리 최적화, 인덱스 활용 전략, EXPLAIN 분석 |
| 🏢 DB 관련 기업 코딩테스트 (네이버, 카카오, 쿠팡 등) | ✅ 실무형 출제 | 주어진 쿼리의 성능 문제 진단 및 인덱스 추천 등 |
✅ 시험에 나오는 출제 유형별 정리
① 정의형 문제 (개념)
❓ "인덱스의 정의를 설명하시오."
❓ "B-tree 인덱스의 특징으로 옳지 않은 것은?"
- 자주 나오는 개념:
- 인덱스란?
- B-tree vs Hash 인덱스
- 클러스터형 인덱스 vs 비클러스터형 인덱스
② 구조/비교형 문제
❓ "인덱스를 사용하지 못하는 경우는?"
❓ "다음 중 인덱스를 가장 효율적으로 사용할 수 있는 쿼리는?"
- 보기에서 LIKE '%a%', col + 1, CHAR_LENGTH(col) 등을 주고,
- 어떤 조건이 인덱스를 무효화시키는지 묻는 문제 자주 출제
③ EXPLAIN/실행계획 해석 문제
❓ "EXPLAIN 결과에서 type = ALL, key = NULL이 의미하는 것은?"
✅ 정답: Full Table Scan / 인덱스 미사용
- EXPLAIN 표를 주고 인덱스 사용 여부 판단 → SQLD, 전산직에서 자주 출제
④ 실무형 튜닝 문제 (SQLD, 기술사, 코딩면접)
❓ 주어진 SQL 쿼리의 성능을 개선하려면 어떤 인덱스를 추가해야 하는가?
❓ 인덱스를 타기 위해 쿼리를 어떻게 바꿔야 하는가?
✅ 빈출 키워드 요약
| 키워드 | 출제 포인트 |
| B-Tree 구조 | 노드 수, 깊이, 성능 |
| 인덱스 무효화 | 함수, 연산, 와일드카드 |
| EXPLAIN | key, type, rows 분석 |
| 인덱스 종류 | 일반 / 유니크 / 클러스터 / 복합 |
| 커버링 인덱스 | Using index 힌트, 테이블 접근 없이 인덱스로 해결 |
✍️ 출제 예시 문제
[정보처리기사]
Q. 인덱스가 효율적으로 작동하지 않는 조건은?
① LIKE 'abc%'
② salary + 100 > 2000
③ WHERE name = 'Kim'
④ WHERE age BETWEEN 20 AND 30
✅ 정답: ②
[전산직 9급 기출]
Q. 다음 SQL 실행계획 결과에서 테이블 전체를 스캔하는 경우는?
→ 보기 중 type = ALL, key = NULL 포함된 것
[SQLD 실기형]
Q. 다음 쿼리에서 인덱스가 사용되도록 변경하시오.
SELECT * FROM emp WHERE LEFT(name, 1) = 'K';
✅ 마무리 팁
- 인덱스 = 성능 = 튜닝의 시작점 이라는 감각을 가져가세요.
- 특히 시험에서는 정의+구조+EXPLAIN 해석의 3세트를 반드시 대비해야 합니다.
- 실무 대비에는 **우회 전략(함수 피하기, 가상컬럼)**까지 알아두면 확실히 차별화됩니다.
반응형