[MySQL] Index 정리

개요

정리 하기에 앞서 기준을 잡아야 할거 같아 MySQL문서와 ChatGPT 검색 기반으로 테스트를 할 예정입니다.
아래에 Mysql문서 Link와 ChatGPT 정리 내용이 있으며 아래 내용을 토대로 Index에 관하여 테스트 할 예정이다. 

 

MySql 문서

https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html

MySQL :: MySQL 8.4 Reference Manual :: 10.3 Optimization and Indexes

ChatGPT 내용

MySQL에서 복합 인덱스를 설정할 때는 쿼리의 실행 계획과 컬럼의 사용 방식에 따라 인덱스 컬럼 순서를 신중히 결정해야 합니다. 특히 SELECT, JOIN, WHERE, GROUP BY, HAVING에 컬럼이 모두 있는 경우, 가장 중요한 기준은 쿼리에서의 필터링 및 데이터 접근 순서입니다.

아래는 각 조건에 따른 인덱스 설정 우선순위입니다:

1. WHERE 조건: 가장 우선적으로 고려

• WHERE 절은 쿼리 실행 시 가장 먼저 사용되어 데이터 범위를 필터링합니다.
• 선택도가 높은(중복이 적은) 컬럼이 앞에 올수록 인덱스 효율이 높아집니다.

EX)

SELECT ...
FROM table
WHERE column1 = 'value1' AND column2 > 10

• 인덱스 순서: (column1, column2) (column1이 더 좁은 범위를 걸러내는 경우).

2. JOIN 조건: 조인의 기준이 되는 컬럼

• JOIN 절에서 사용되는 컬럼은 두 테이블을 연결할 때 효율적인 데이터 탐색을 위해 인덱스가 필요합니다.
• 일반적으로 ON 조건에 사용된 컬럼을 WHERE 조건 다음으로 고려합니다.

EX)

SELECT ...
FROM table1 t1
JOIN table2 t2
  ON t1.column1 = t2.column2
WHERE t1.column3 = 'value'

 

• table1: (column3, column1)
• table2: (column2) (다른 테이블의 JOIN 키).

3. GROUP BY: 데이터 집계

• GROUP BY에 사용된 컬럼도 인덱스에 포함하면 효율적입니다.
• 단, WHERE 및 JOIN 조건 뒤에 추가하는 것이 일반적입니다.

EX)

SELECT column1, column2, SUM(column3)
FROM table
WHERE column4 = 'value'
GROUP BY column1, column2

• 인덱스 순서: (column4, column1, column2).

4. SELECT: 사용된 컬럼이 WHERE, JOIN, GROUP BY에 없는 경우

• SELECT 절에 있는 컬럼이 이미 인덱스의 다른 컬럼에 포함되어 있다면 효율적으로 처리됩니다.
• 단, WHERE나 GROUP BY와 관계없는 컬럼이 많다면 Covering Index를 생성해 성능을 높일 수 있습니다.
  • Covering Index: SELECT 절의 모든 컬럼을 포함한 인덱스.

EX)

SELECT column1, column2
FROM table
WHERE column3 = 'value'
GROUP BY column4

• 인덱스 순서: (column3, column4) (column1, column2는 SELECT에서 자동으로 커버됨).

5. HAVING 조건: 가장 마지막 우선순위

• HAVING 절은 GROUP BY 이후에 적용되므로, 인덱스에서 우선순위가 낮습니다.
• WHERE 절로 필터링할 수 없는 조건이 HAVING에 있는 경우 인덱스에 추가할 수 있습니다.

EX)

SELECT column1, SUM(column2)
FROM table
GROUP BY column1
HAVING SUM(column2) > 100

• 인덱스 순서: (column1) (SUM(column2)는 인덱스에 포함될 필요 없음).

종합적인 인덱스 순서 결정

우선순위:

1. WHERE: 가장 중요한 필터링 조건.
2. JOIN: 테이블 간 관계를 탐색하는 컬럼.
3. GROUP BY: 그룹핑 및 집계 컬럼.
4. SELECT: Covering Index로 활용 가능.
5. HAVING: 우선순위는 낮지만, 필요한 경우 추가.

---

추가 팁

1. EXPLAIN 활용: 생성한 인덱스가 실제로 사용되는지 확인합니다.
2. Query Rewrite: WHERE 조건을 사용해 HAVING을 줄이고, 쿼리 최적화를 시도합니다.
3. 인덱스 크기 관리: 너무 많은 컬럼을 인덱스에 포함하면 성능 저하와 저장 공간 낭비가 발생할 수 있습니다. 가장 중요한 컬럼만 포함하세요.