[mySql 인덱스 설계] 기본원칙, 설계방법 , 주의사항!!!

인덱스는 조회 속도를 높이는 강력한 도구지만, 잘못 쓰면 오히려 성능을 망친다.
쿼리 패턴과 데이터 특성에 맞는 인덱스 설계가 핵심이다.

---

✅ 1. 인덱스 설계 시 기본 원칙

항목	설명
조건절에 자주 등장하는 컬럼에만 인덱스	WHERE, JOIN, ORDER BY, GROUP BY 등에 자주 쓰이는 컬럼만 인덱싱함
정렬/조회 목적 아니면 인덱스 남발 금지	자주 갱신되는 컬럼에 인덱스를 남발하면 쓰기 성능 저하됨
중복이 적은 컬럼(카디널리티 높음) 우선	예: 성별(M/F)처럼 중복률 높은 컬럼은 인덱스 효율 낮음
범위 조건 이후 컬럼 인덱스 무시됨	WHERE age > 30 AND name = '철수' → name은 인덱스 안 탐
연산/함수는 인덱스 무시	WHERE DATE(created_at) = '2024-01-01' → 인덱스 사용 안 됨
OR 조건은 인덱스 효율 낮음	가능하면 UNION 등으로 쪼개는 방식 권장
LIKE '%abc'는 인덱스 무시	접두어 검색(LIKE 'abc%') 형태만 인덱스 사용됨

 

예시설명

1. 중간 컬럼 건너뛰면 이후 인덱스 무시됨

복합 인덱스는 앞에서부터 순서대로 조건이 걸려야 인덱스를 탐색할 수 있음.

예시

-- 인덱스 정의
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 이건 OK (순서대로 조건 걸림)
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01';

-- 이건 NO (user_id 건너뜀 → created_at 인덱스 못 탐색)
SELECT * FROM orders WHERE created_at > '2024-01-01';

💡 왜?

• 인덱스는 user_id → created_at 순으로 정렬되어 있음.
• 그런데 user_id 조건이 없으면 created_at 기준으로 정렬된 상태를 활용할 수가 없음.

---

2. 범위 조건 이후 컬럼 인덱스 무시됨

범위 조건(>, <, BETWEEN 등)이 나오면, 그 뒤 컬럼부터는 인덱스 못 탐색함.

예시

-- 인덱스 정의
CREATE INDEX idx_a_b_c ON example(a, b, c);

-- 이건 OK (정확히 순서대로)
WHERE a = 1 AND b = 2 AND c = 3;

-- 이건 문제 있음
WHERE a = 1 AND b > 2 AND c = 3;

💡 왜?

• b > 2처럼 범위 조건이 나오면, 그 순간부터 정렬 구조가 깨짐.
• 그래서 c = 3 조건은 인덱스로 못 가고 테이블 스캔 함.

👉 즉, 범위 조건이 등장하면 그 이후 인덱스 컬럼은 포기해야 한다.

---

3. 연산/함수는 인덱스 무시

컬럼에 수식이나 함수가 들어가면, 인덱스를 탈 수 없음
이유는 간단함. 컬럼 원래 값 기준 정렬인데, 연산하면 그 정렬이 깨짐

예시

-- price에 인덱스가 있음
CREATE INDEX idx_price ON product(price);

-- 이건 OK
SELECT * FROM product WHERE price > 100;

-- 이건 NO (인덱스 안 탐)
SELECT * FROM product WHERE price * 2 > 200;

-- 이건 OK (리터럴을 바꿈)
SELECT * FROM product WHERE price > 200 / 2;

💡 핵심은 연산 대상이 컬럼이면 안 되고, 상수 쪽이어야 함.

---

4. OR 조건은 인덱스 효율 ↓

OR 조건은 인덱스를 여러 번 타야 해서 결국 Full Scan 되기 쉬움.
옵티마이저가 "야 그냥 다 훑자…"라고 결정할 수도 있음.

예시

-- name, email 에 인덱스 있음
SELECT * FROM users WHERE name = '승민' OR email = 'abc@naver.com';

👉 이 쿼리는 name, email 둘 다 인덱스가 있어도

• 옵티마이저 입장에서: “두 조건 다 봐야 하네… 그냥 테이블 전체 한번 스캔할까?”
• 실제로 EXPLAIN 돌려보면 Full Table Scan일 수 있음

✅ 해결 방법:

• UNION 으로 분리하면 각각 인덱스를 타게 유도 가능

SELECT * FROM users WHERE name = '승민'
UNION
SELECT * FROM users WHERE email = 'abc@naver.com';

---

🔚 요약

개념	왜 인덱스를 못타는가?	해결방안
중간 컬럼 건너뜀	인덱스는 앞에서부터 탐색하기 때문	인덱스 정의 순서 맞춰서 WHERE 작성
범위 조건 이후 컬럼 무시	정렬이 깨져서 그 뒤는 탐색 불가	범위 조건은 마지막에 사용
연산/함수 사용	컬럼 값이 변경되어 정렬 기준 무의미	리터럴만 계산해서 비교
OR 조건	옵티마이저가 그냥 전체 스캔할 수도 있음	UNION 으로 분리 권장

 

---

✅ 2. 복합 인덱스 설계 시 주의사항

항목	설명
카디널리티 높은 컬럼을 앞에 둔다	(created_at, user_id) 순이 더 효율적일 수 있음
WHERE 절 컬럼 순서와 인덱스 순서 일치	인덱스가 (a, b, c)면 WHERE a=? AND b=? 순으로 써야 탐색 가능
중간 컬럼 건너뛰면 이후 인덱스 무시됨	WHERE a=? AND c=? → c는 인덱스 사용 못함
정렬 조건도 인덱스 순서 고려	ORDER BY created_at DESC → 인덱스도 동일하게 생성 필요
Covering Index 활용	인덱스에 조회 대상 컬럼이 모두 포함돼 있으면 테이블 접근 생략 가능

 

Covering Index 활용

-- Covering Index 예시
CREATE INDEX idx_email_name ON users(email, name);

SELECT name FROM users WHERE email = 'abc@example.com';
-- → 테이블 접근 없이 인덱스만으로 처리됨

 

중간컬럼 건너뛰면 인덱스 못탐 예시

• 인덱스는 user_id → created_at 순으로 정렬되어 있음.
• 그런데 user_id 조건이 없으면 created_at 기준으로 정렬된 상태를 활용할 수가 없음.

-- 인덱스 정의
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 이건 OK (순서대로 조건 걸림)
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01';

-- 이건 NO (user_id 건너뜀 → created_at 인덱스 못 탐색)
SELECT * FROM orders WHERE created_at > '2024-01-01';

 

---

🧨 3. 인덱스 잘못 쓰면 생기는 문제

문제	설명
인덱스 너무 많음 → 쓰기 성능 저하	INSERT, UPDATE, DELETE 시 인덱스도 함께 갱신됨
잘못된 순서 → 인덱스 무용지물	복합 인덱스 순서 안 맞으면 Full Scan 발생
EXPLAIN 없이 설계 → 인덱스 무시 가능성	항상 EXPLAIN으로 실행 계획 확인해야 함
정렬/그룹핑 비용 과다	인덱스 없으면 ORDER BY, GROUP BY 시 병목 발생

---

🔍 4. 인덱스 사용 여부 체크리스트

• EXPLAIN으로 인덱스 사용 확인했는가?
  (테스트할떄 데이터가 별로없으면 인덱스를 안탈수있으니 테스트 시에도 더미데이터를 넣어두는것이 좋다 ! 실무에서 테스트 할떄 데이터가 없어서 인덱스를 안타기도함 이게 슬로우쿼린지아닌지 판단어려웡..)
• 복합 인덱스 컬럼 순서가 쿼리와 일치하는가?
• WHERE절에서 함수/연산 사용하지 않았는가?
• SELECT * 대신 필요한 컬럼만 조회했는가?
• 쿼리 반복 사용 시 인덱스 구조 재검토했는가?

---

📚 5. 인덱스 동작 원리 요약

개념	설명
인덱스 구조	대부분 B+ Tree 사용. 리프 노드에 인덱스 키 + PK 저장
클러스터드 vs 보조 인덱스	InnoDB 기준 PK는 클러스터드, 나머지는 보조 인덱스(PK 참조)
Bookmark Lookup	보조 인덱스를 타고 PK 주소 찾아 테이블에서 다시 조회함

---

❌ 6. 인덱스가 무시되는 조건들

조건	이유
LIKE '%abc'	접두어 없으면 인덱스 무시됨
WHERE col + 1 = 5, DATE(col)	연산/함수 쓰면 인덱스 사용 안 됨
OR, !=, NOT IN 등	옵티마이저가 인덱스 무시하고 Full Scan 가능성 있음
SELECT *	Covering Index 불가 → 테이블 접근 발생
데이터 너무 적음	옵티마이저가 인덱스보다 Full Scan 선택할 수 있음

---

❗ 7. 인덱스 많을 때 쓰기 성능 저하 이유

작업	내부 동작
INSERT	새 row + 모든 인덱스에 값 추가
UPDATE	인덱스 컬럼 변경 시 기존 인덱스 제거 + 새 값 추가
DELETE	관련된 인덱스에서도 값 삭제

👉 즉, 인덱스는 읽기 성능 향상을 위한 도구이지만, 쓰기 작업엔 부하를 준다.

---

⚠️ 8. 실무에서 인덱스 설계 시 주의사항

체크포인트	설명
인덱스 남발 금지	모든 컬럼에 인덱스 다는 건 오히려 독
자주 갱신되는 컬럼은 가급적 제외	예: status, last_login_dt
통계/정렬용 인덱스는 읽기 전용 테이블에만	실시간 테이블보다 배치용 테이블에 적합
변경 작업 많은 테이블은 인덱스 최소화	장바구니, 주문 테이블 등

---

🔚 결론 요약

핵심 포인트	설명
인덱스는 잘 쓰면 성능 향상, 잘못 쓰면 병목 유발	트레이드오프 존재
항상 EXPLAIN으로 실행 계획 확인	눈으로 확인하지 않으면 놓치기 쉬움
Covering Index를 적극 활용	테이블 접근 자체를 줄이면 속도 향상
설계는 쿼리 패턴 중심으로	무조건적인 인덱싱은 금물

---