[mysql] 왕초보를 위한 index 설정

인덱스란?

인덱스는 테이블에서 원하는 데이터를 빠르게 조회하기 위한 자료구조입니다.

SELECT 성능을 향상시키는 데 유용하지만, INSERT/UPDATE/DELETE 시에는 인덱스 갱신 비용이 발생해 성능에 부담이 될 수 있습니다.

인덱스는 단일 컬럼 기준으로도 만들 수 있고, 여러 컬럼을 조합한 복합 인덱스로도 설정할 수 있습니다.

---

옵티마이저란?

옵티마이저는 SQL 실행 계획을 수립하는 MySQL 내부 엔진입니다. 하나의 쿼리를 다양한 방식으로 실행할 수 있을 때, 가장 효율적인 경로를 선택하는 역할을 합니다.

주요 역할

• 어떤 인덱스를 사용할지 선택
• 테이블 읽기 순서 결정
• JOIN 방식 선택 (예: Nested Loop)
• Index Merge 적용 여부 판단

예시

SELECT * FROM users WHERE age = 30 AND name = 'Tom';

→ age 또는 name 인덱스를 사용할 수도 있고, 복합 인덱스를 선택할 수도 있습니다.

Index Merge 예외

SELECT * FROM users WHERE age = 30 OR name = 'Tom';

→ 둘 다 인덱스가 있을 경우 옵티마이저가 각 인덱스 결과를 병합할 수 있습니다. EXPLAIN 결과의 Extra 항목에 Using union(...)으로 표시됩니다.

옵티마이저 힌트 사용

1. 전통적인 힌트

SELECT * FROM users USE INDEX (idx_age);
SELECT * FROM users FORCE INDEX (idx_name);
SELECT * FROM users IGNORE INDEX (idx_name);

• USE INDEX: 특정 인덱스를 우선 고려
• FORCE INDEX: 특정 인덱스를 반드시 사용
• IGNORE INDEX: 특정 인덱스를 무시

2. MySQL 8.0.20 이상 힌트 문법

SELECT /*+ BKA(t1) NO_ICP(t2) QB_NAME(main_qb) */ *
FROM t1 JOIN t2 ON t1.id = t2.id;

• BKA: Batched Key Access Join 사용
• NO_ICP: Index Condition Pushdown 비활성화
• QB_NAME: 쿼리 블록 이름 지정

참고 문서:

• https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
• https://dev.mysql.com/doc/refman/8.0/en/query-optimization.html
• https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html

실행 계획 확인

EXPLAIN SELECT ...

→ 어떤 인덱스가 사용되는지, 접근 방식은 무엇인지 확인할 수 있습니다.

---

인덱스 관련 팁

1. 인덱스는 여러 개 설정해도 되나요?

가능합니다. 하지만 옵티마이저는 한 번의 쿼리에서 정된 인덱스 중 가장 효율적인 하나를 선택 하나의 인덱스만 사용하는 것이 일반적이며, 반복적으로 조건이 함께 사용된다면 복합 인덱스를 고려하는 것이 더 효율적입니다.

2. 인덱스를 많이 만들면 성능이 더 좋아지나요?

아닙니다. 인덱스는 SELECT 성능에는 도움이 되지만, 데이터 수정(CUD) 작업 시마다 인덱스도 함께 갱신 되어야 하므로 오히려 성능이 저하될 수 있습니다.

★ 3. 문자열 검색 시 인덱스 사용 여부 (feat. like)

1. LIKE 검색

• LIKE 'abc%' → 인덱스 사용 가능 (왼쪽 고정 패턴이므로 B-Tree 인덱스 활용 가능)
• LIKE '%abc', LIKE '%abc%' → 인덱스 사용 불가 (패턴의 앞쪽이 불확정이므로 풀스캔 발생)

 

LIKE 'abc%'	✅	B-Tree 인덱스 사용 가능
LIKE '%abc'	❌	풀스캔 발생
MATCH ... AGAINST	✅	FULLTEXT 인덱스 사용 (InnoDB 5.6+)
RLIKE, REGEXP	❌	정규표현식 검색, 항상 전체 스캔 발생

2. FULLTEXT 검색

• MySQL에서 제공하는 자연어 전문 검색용 인덱스입니다.
• MATCH(column) AGAINST('keyword') 구문 사용 시 FULLTEXT 인덱스 활용 가능
• LIKE '%abc%'와 유사한 검색을 훨씬 빠르게 처리 가능
• InnoDB 스토리지 엔진은 MySQL 5.6 이상부터 지원

ALTER TABLE users ADD FULLTEXT(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('abc');

3. RLIKE, REGEXP 검색

• 정규표현식을 사용하여 유연한 문자열 검색 가능
• 예: RLIKE '^abc', REGEXP 'abc$' 등
• 하지만 일반 인덱스를 전혀 사용하지 않으며, 항상 전체 테이블 스캔이 발생함

SELECT * FROM users WHERE name RLIKE '^abc';
SELECT * FROM users WHERE name REGEXP 'abc$';

★ 4. WHERE 조건에 함수가 들어가면 인덱스를 사용할 수 있나요?

일반적으로는 사용할 수 없습니다.

조건절에 함수가 사용되면, MySQL은 인덱스를 무시하고 전체 테이블을 스캔하게 됩니다.

이유는 인덱스는 컬럼의 원본 값을 기준으로 정렬되는데, 함수가 그 값을 변경해버리기 때문입니다.

사용 방식	인덱스 사용 여부	비고
WHERE LOWER(name) = 'john'	❌	함수로 인해 인덱스 무력화
WHERE name_lower = 'john'	✅	전처리 컬럼 사용 필요
WHERE REPLACE(name, ...)	❌	문자열 변형 함수는 인덱스 무력화
WHERE name LIKE 'abc%'	✅	B-Tree 인덱스 활용 가능
WHERE DATE(date_col) = '2023-01-01'	❌	함수 대신 BETWEEN 사용 권장

 

예시:

-- 인덱스 사용 불가
SELECT * FROM users WHERE REPLACE(name, 'a', 'b') = 'bbc';

이처럼 REPLACE, LOWER, UPPER, CONCAT, TRIM, SUBSTRING 등 대부분의 문자열 함수는 인덱스를 무력화합니다.

함수가 필요한 경우 대안

함수를 사용하지 않고 인덱스를 유지할 수 있는 방식으로 쿼리를 바꾸는 것이 좋습니다.

예 1: 소문자 검색

-- 잘못된 방식 (인덱스 사용 불가)
SELECT * FROM users WHERE LOWER(name) = 'john';

-- 추천 방식 (컬럼에 소문자 값을 따로 저장)
SELECT * FROM users WHERE name_lower = 'john';

예 2: 문자열 일부 비교

-- 잘못된 방식 (인덱스 사용 불가)
SELECT * FROM users WHERE LEFT(name, 3) = 'kim';

-- 추천 방식
SELECT * FROM users WHERE name LIKE 'kim%';

예 3: 불필요한 연산 제거

-- 잘못된 방식
SELECT * FROM orders WHERE DATE(order_time) = '2023-05-01';

-- 추천 방식
SELECT * FROM orders WHERE order_time BETWEEN '2023-05-01 00:00:00' AND '2023-05-01 23:59:59';

 

★ 5. 복합 인덱스의 순서가 중요한가요? (최좌측 원칙)

중요합니다. 복합 인덱스는 정의된 컬럼 순서의 왼쪽부터 조건이 충족되어야 인덱스가 사용됩니다.

예:

INDEX (a, b, c)

• WHERE a = 1 → 사용됨 ✅
• WHERE a = 1 AND b = 2 → 사용됨 ✅
• WHERE b = 2 AND c = 3 → 사용 안 됨 ❌ (a가 빠졌기 때문)

6. OR 조건에도 인덱스가 사용되나요?

경우에 따라 옵티마이저가 Index Merge를 통해 두 인덱스를 병합해서 사용할 수 있습니다. 그러나 상황에 따라 UNION으로 쿼리를 나누는 것이 더 효율적일 수 있습니다.

7. SELECT 문에만 인덱스가 필요한가요?

아니요. UPDATE나 DELETE 문에서도 WHERE 조건이 있다면 인덱스가 성능에 중요한 역할을 합니다.

8. 사용하지 않는 인덱스는 제거해야 하나요?

네. 사용하지 않는 인덱스는 관리 비용만 늘리고 성능을 저하시킬 수 있습니다.

9. 인덱스는 테이블 단위인가요, 컬럼 단위인가요?

인덱스는 컬럼 단위로 생성되며, 두 개 이상의 컬럼을 포함하는 복합 인덱스도 가능합니다.

10. NULL 값이 있는 컬럼에도 인덱스를 설정할 수 있나요?

가능합니다. 하지만 IS NULL 조건은 인덱스를 효율적으로 사용하지 못할 수 있습니다.

11. AUTO_INCREMENT 컬럼이 PRIMARY KEY로 많이 사용되는 이유는?

값이 순차적으로 증가하므로 B-Tree 인덱스에 적합하며 삽입 시 성능이 좋습니다.

13. 정렬 시 인덱스를 사용할 수 있나요?

ORDER BY 절의 컬럼이 인덱스 컬럼과 순서, 방향이 같으면 정렬도 인덱스를 통해 빠르게 처리됩니다.

14. COUNT(*)에서도 인덱스가 사용되나요?

조건이 있는 경우 사용됩니다. 전체 COUNT는 일반적으로 풀스캔합니다.

15. IN 조건은 인덱스를 사용할 수 있나요?

• IN (1, 2, 3)처럼 상수 값이면 인덱스를 사용할 수 있습니다.
• IN (SELECT ...)이나 값이 많은 경우는 옵티마이저가 풀스캔을 선택할 수 있습니다.

16. BETWEEN은 인덱스를 잘 사용하나요?

범위 조건을 명확하게 지정하면 인덱스를 잘 사용할 수 있습니다.

17. 어떤 제약 조건이 인덱스를 자동 생성하나요?

PRIMARY KEY, UNIQUE, FOREIGN KEY 제약 조건은 자동으로 인덱스를 생성합니다.

18. 긴 문자열도 인덱스에 사용할 수 있나요?

가능하지만 성능상 부담이 크므로, 앞부분만 인덱싱하는 prefix 인덱스를 사용하는 것이 일반적입니다.

20. BOOLEAN 타입 컬럼에 인덱스를 사용해야 하나요?

TRUE/FALSE 비율이 고르게 분포되어 있다면 도움이 되지만, 한쪽으로 편향된 경우 인덱스 효과가 낮습니다.

21. ENUM 타입에도 인덱스를 사용할 수 있나요?

ENUM은 내부적으로 정수로 처리되므로 인덱스를 사용하는 데 적합합니다.

23. SELECT * 는 인덱스를 사용할 수 없나요?

WHERE 조건이 인덱스를 타면 SELECT * 도 인덱스를 사용할 수 있습니다.

24. 인덱스 사용 여부는 어떻게 확인하나요?

EXPLAIN SELECT 문을 통해 실행 계획을 분석하면 어떤 인덱스가 사용되었는지 확인할 수 있습니다.

25. 인덱스 통계는 어떻게 확인하나요?

SHOW INDEX FROM 테이블명, INFORMATION_SCHEMA.STATISTICS를 통해 인덱스 관련 정보를 확인할 수 있습니다.

26. 복합 인덱스와 개별 인덱스의 차이는?

복합 인덱스는 여러 조건을 하나의 인덱스로 커버하지만, 개별 인덱스는 Index Merge가 필요할 수 있어 효율이 낮을 수 있습니다.

28. JOIN 시 인덱스는 왜 필요하나요?

조인 조건에 인덱스가 없으면 매번 테이블 전체를 스캔해야 하므로 성능이 급격히 저하됩니다.

29. FULLTEXT 인덱스는 언제 사용하나요?

자연어 검색이 필요한 경우 MATCH ... AGAINST 구문과 함께 사용됩니다.

30. Spatial 인덱스는 언제 사용하나요?

공간(GIS) 기반 데이터를 다룰 때 사용합니다. 예: 위치 기반 검색

31. SELECT 쿼리가 느려졌다면 인덱스를 점검해야 하나요?

그렇습니다. WHERE 조건에 알맞은 인덱스가 적용되고 있는지 확인해야 합니다.

32. 인덱스도 재구성이 필요한가요?

데이터 변경이 많으면 인덱스가 단편화될 수 있습니다. OPTIMIZE TABLE 명령으로 재구성할 수 있습니다.

33. 복합 인덱스의 컬럼 순서를 바꾸면 어떤 영향이 있나요?

WHERE 조건 순서와 인덱스 정의 순서가 맞지 않으면 인덱스가 전혀 사용되지 않을 수 있습니다.

34. 외래키 컬럼에는 인덱스를 설정해야 하나요?

참조 무결성을 유지하기 위해 외래키 컬럼에는 반드시 인덱스가 필요합니다.

35. 정규화된 테이블에도 인덱스가 필요한가요?

정규화 여부와 관계없이 쿼리 패턴에 따라 인덱스는 반드시 고려되어야 합니다.

36. 조회가 느릴 때 인덱스가 원인일 수 있나요?

인덱스가 없거나, 잘못된 인덱스 사용, 혹은 인덱스 충돌 등이 주요 원인이 될 수 있습니다.

37. INSERT 속도가 느려질 수 있나요?

인덱스가 많을수록 INSERT 시 모든 인덱스를 갱신해야 하므로 성능이 떨어질 수 있습니다.

38. 데이터가 적은 테이블에도 인덱스를 설정해야 하나요?

데이터가 매우 적은 경우엔 풀스캔이 오히려 더 빠를 수 있습니다. 상황에 따라 판단이 필요합니다.

45. LIMIT + OFFSET 쿼리에 인덱스를 사용할 수 있나요?

OFFSET이 클수록 성능이 저하됩니다. 커버링 인덱스를 사용하거나 Seek 방식으로 최적화하는 것이 좋습니다.

47. 인덱스에 적합하지 않은 데이터 타입은?

TEXT, BLOB과 같은 대용량 문자열은 일반 인덱스로 적합하지 않으며, FULLTEXT 인덱스를 사용하거나 별도 처리가 필요합니다.

49. 서브쿼리는 인덱스를 사용할 수 없나요?

상관 서브쿼리(Correlated Subquery)는 외부 쿼리의 값을 참조하면서 각 행마다 실행되기 때문에 인덱스를 활용하지 못하는 경우가 많습니다. 가능하면 JOIN으로 대체하는 것이 좋습니다.

SELECT name
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);