[ MySQL 최적화 ] Index

Index

MySQL은 UNIQUE 제약 조건을 추가하면 자동으로 Index가 생성된다.

- UNIQUE 옵션을 사용하면 Index가 같이 생성되기 때문에 조회 성능이 향상된다.

▶ Index 를 사용하면 데이터를 조회할 때의 성능이 향상된다.

 

그러면 Index 를 무조건적으로 많이 추가하는 게 좋을까?

  ⊕ Index 를 추가하면 조회 성능은 올라간다.

  ⊖ 쓰기 작업(삽입, 수정, 삭제)의 성능은 저하된다. 

 

<중요>

- 최소한의 인덱스만 사용하려고 하자.

- 인덱스를 추가하면 조회 속도는 빨라지나, 쓰기(삽입, 수정, 삭제) 속도는 느려짐을 항상 기억하자.

 

Multiple-Column Index

2개 이상의 컬럼을 묶어서 인덱스를 생성하는 방법

 

user 라는 테이블에 (이름, 부서, 나이) 컬럼이 있다고 가정해보자.

 

멀티 컬럼 인덱스 생성 예시

CREATE INDEX idx_부서_이름 ON users (부서, 이름);

• 앞에 있는 부서를 기준으로 데이터를 정렬한 후 그 다음 같은 부서 내에서 이름을 기준으로 정렬

데이터 조회할 때 인덱스를 활용

SELECT * FROM users
WHERE 부서 = '인사' 
ORDER BY 이름;

• 이미 (부서, 이름)으로 인덱스를 만들어놔서 WHERE 부서 = ‘인사’를 만족하는 데이터들은 금방 찾을 수 있다.
• ORDER BY 이름; 을 사용했지만 불러오면 이미 정렬이 되어 있다.

주의 할 점

1. 이미 정렬된 것을 또 정렬할 필요 없다.

•  멀티 컬럼 인덱스(부서, 이름)를 부서 컬럼의 인덱스처럼 활용할 수 있다.

SELECT * FROM users
WHERE 부서 = '인사' 
ORDER BY 이름;

 에서 "ORDER BY 이름" 을 사용할 필요가 없다.

• 멀티 컬럼 인덱스에서 이미 부서를 기준으로 정렬이 되어 있으므로 부서 인덱스를 따로 생성할 필요가 없다. 이 멀티 컬럼 인덱스를 바로 사용하면 된다.

2. 멀티 컬럼 인덱스(부서, 이름)를 이름 컬럼의 인덱스 처럼 활용할 수 없다.

• 부서를 기준으로만 정렬이 되어 있지 이름을 기준으로 정렬이 되어 있지는 않다.
• 멀티 컬럼 인덱스에서 일반 인덱스처럼 활용할 수 있는 건 처음에 배치된 컬럼들뿐

3. 순서에 주의해야 한다.

• 소분류 → 중분류 → 대분류 컬럼 순으로 정렬하기
  • 소분류를 탐색한 다음 대분류를 탐색하는 방식이 더 빠르기 때문
    • 예)  대기업에서 "회계"부서에서 "홍길동"을 찾는다고 해보자. 회계 부서에 있는 인원보다 홍길동 이름을 가진 인원이 적기 때문에 홍길동을 찾은뒤 회계 부서 사람인지 확인하는 것이 더 빠르다.
    • 데이터 중복도가 낮은 컬럼이 앞쪽으로 오는 것이 좋다.

요약

- 멀티 컬럼 인덱스 컬럼의 순서는 매우 중요하다.

- 멀티 컬럼 인덱스에서 처음에 배치된 컬럼들은 일반 인덱스처럼 활용할 수 있다.

- 멀티 컬럼 인덱스를 구성할 때 데이터 중복도가 낮은 컬럼이 앞쪽으로 오는 게 좋다. 

 

커버링 인덱스(Covering Index)

SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스

 

예시 유저 테이블과 인덱스

위의 데이터를 조회하기 위해 실행하는 SQL 문 예시

SELECT id, created_at FROM users;
SELECT id, name FROM users;

첫번째 SQL = 두 컬럼(id, created_at)만 조회하는데 실제 데이터 테이블에 접근을 해야한다.

두 번째 SQL = id, name 컬럼은 실제 테이블에 접근하지 않고 인덱스에만 접근해서 알아낼 수 있는 정보들

▶ SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스를 커버링 인덱스 라고 한다.

 

 

참고 강의

(인프런) 비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전