MySQL 인덱스(Index)

인덱스가 필요한 이유

• 데이터 양이 많아 질수록 인덱스 없는 검색은 풀 테이블 스캔으로 인해 매우 느려짐
• 풀 테이블 스캔은 테이블의 모든 데이터를 처음부터 끝까지 순차적으로 비교하는 방식으로, 데이터 양에 비례하여 성능이 저하 됨
• WHERE 절에 자주 사용되는 컬럼에 인덱스를 생성하는 것이 기본적인 해결책

인덱스 소개

• 특정 컬럼의 값과 해당 데이터의 물리적 위치 주소를 쌍으로 저징
• 인덱스의 핵심은 데이터가 항상 정렬된 상태로 유지 된다는 점으로, 이를 통해 데이터를 매우 빠르게 찾을 수 있음
• 검색 시 테이블 전체를 스캔하는 대신, 정렬된 인덱스를 먼저 탐색하여 원하는 데이터의 위치로 즉시 이동

트리 자료 구조

• 인덱스는 주로 트리(Tree) 자료 구조이며, B-Tree를 사용하여 구현 됨
• 이진 탐색 트리와 같이 데이터를 정렬된 상태로 저장하여, 검색 시 O(log n)의 매우 빠른 시간 복잡도를 가짐
• 데이터가 1억건이여도 27번의 비교만으로 데아터를 찾을 수 있음
• 대부분 DB는 트리의 한쪽으로 치우치는 문제를 햐결한 밸런스 트리를 사용하여 최악의 경우에도 O(log n) 성능을 보장

인덱스 생성, 조회, 삭제

• PRIMARY KEY, FOREIGN KEY, UNIQUE 제약 조건을 설정하면 해당 컬럼에 인덱스가 자동으로 생성 됨
• 실행 계획의 type이 ALL이면 풀 스캔, ref나 range이면 인덱스 사용을 의미

생성

CREATE INDEX index_name ON table_name (column_name);

삭제

DROP INDEX index_name ON table_name;

조회

SHOW
INDEX FROM table_name;

인덱스와 동등 비교

• 동등 비교(=) 조건에 인덱스가 사용 될 때, 실행 계획의 type은 ref로 표시
• ref는 인덱스를 통해 조건에 맞는 데이터를 매우 효율적으로 참조했다는 의미
• 인덱스를 사용하면 탐색해야 할 행(rows)의 수가 극적으로 줄어들어 검색 성능이 크게 향상

인덱스와 범위 검색

• BETWEEN, >, <와 같은 범위 검색에 인덱스가 사용될 때, 실행 계획의 type은 range로 표시
• DB는 정렬된 인덱스에서 범위의 시작점을 빠르게 찾고, 범위가 끝날 때까지만 순차적으로 스캔하므로 효율적
• 인덱스를 사용하면 결과가 인덱스 키 순서로 정룔되어 나올 수 있지만, 정확한 정렬을 위해서는 ORDER BY를 명시해야 함

인덱스와 LIKE 범위 검색

• LIKE 검색에서 인덱스를 활용하려면 와일드카드(%)가 반드시 뒤쪽에 위치해야 함
• 와일드카드가 앞에 오면 시작점을 특정할 수 없어 인덱스를 사용하지 못하고 풀 테이블 스캔이 발생
• 텍스트 중간에 포함된 단어를 검색하기 위해서는 LIKE 대신 전문 검색(Full-Text Search) 기능을 사용해야 함

인덱스와 정렬

• 정렬(ORDER BY)은 비용이 많이 드는 작업이지만, 인덱스를 활용하면 별도의 정렬과정을 생략하여 성능을 개선할 수 있음
• WHERE 조건과 ORDER BY 조건이 인덱스와 일치하면, DB는 이미 정렬된 인덱스를 순서대로 읽기만 하면 되므로 filesort가 발생하지 않음
• 내림차순 정렬(DESC) 시에는 인덱스를 거꾸로 읽는 역방향 스캔을 통해 filesort를 피할 수 있음

옵티마이저와 인덱스 선택

• DB 옵티마이저는 쿼리 실행 시, 인덱스 사용과 테이블 전체 스캔 중 더 효율적인 방법을 선택
• 인뎃스를 사용하는 것이 오히려 비용이 크다고 판단되면(손익분기점 초과), 인덱스가 있어도 사용하지 않음
• 일반적으로 전체 데이터의 20~25% 이상을 조회하는 경우, 인덱스를 통한 랜덤 I/O보다 테이블 전체를 순차적으로 읽는 순차 I/O가 더 빠르다고 판단
• 데이터 양이 매우 적을 때도 풀 테이블 스캔을 선택할 수 있음

커버링 인덱스

• 쿼리에 필요한 모든 컬럼ㄴ을 포함하는 인덱스를 커버링 인덱스라고 함
• 커버링 인덱스를 사용하면 원본 테이블에 접근하지 않고 인덱스만으로 쿼리를 처이할 수 있어, 랜덤 I/O가 발생하지 않아 성능이 크게 향상
• 실행 계획의 Extra컬럼에 Using index가 표시되면 커버링 인덱스가 사용 된 것
• SELECT 성능을 크게 높이지만, 인덱스 크기가 커지고 쓰기(INSERT, UPDATE, DELETE) 성능이 저하되는 단점이 있음

복합 인덱스

• 두 개 이상의 컬럼을 묶어 하나의 인덱스로 만든 것을 복합 인덱스라 함
• 컬럼의 순서가 매우 중요하며, 인덱스는 첫 번째 컬럼부터 순서대로 조건에 사용되어야 함
• WHERE 절과 ORDER BY절이 인덱스 순서와 일치하며느 불필요한 정렬 작업을 생략할 수 있어 성능에 매우 유리
• 복합 인덱스의 첫 번째 컬럼을 건너뛰고 두 번째 이후의 컬럼만 조건으로 사용하면 인덱스를 활용할 수 없어 풀 테이블 스캔이 발생할 수 있음
• 복합 인덱스의 선행 컬럼에 범위 조건이 서용되면, 그 뒤에 오는 컬럼은 인덱스의 정렬 효과를 누릴 수 없어 효율이 떨어짐
• 범위 조건으로 넓게 가져온 데이터를 필터링 하는 방식으로 동작하여 인덱스의 성능을 절반만 활용하게 됨
• 복합 인덱스 설계 시, 등호(=) 조건을 사용하는 컬럼을 앞에, 범위 조건을 사용하는 컬럼을 뒤에 두는 것이 일반적인 최적화 전략
• 범위 검색(>=) 대신 IN 절을 사용하면, 옵티마이저는 이를 여러 개의 동등 비교(=)로 인식하여 복합 인덱스를 더 효율적으로 활용할 수 있음

💡 복합 인덱스 대원칙

• 인덱스는 순서대로 사용(왼쪽 접두어 규칙)
• 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로
• 정렬(ORDER BY)도 인덱스 순서를 따르자

인덱스 설계 가이드라인

• 인덱스 생성의 가장 중요한 기준은 카디널리티(Cardinality), 즉 값의 고유성 정도임
• 카디널리티가 높은 컬럼에 생성해야 효과적
• 인덱스 생성 가이드라인
  • WHERE 절에서 자주 사용되는 컬럼
  • JOIN의 연결고리가 되는 컬럼(외래 키)
  • ORDER BY 절에서 자주 사용되는 컬럼(정렬 작업 회피)

인덱스의 단점과 주의사항

• 저장 공간 차지: 인덱스는 별도의 파일로 저장되어 추가 디스크 공간을 사용
• 쓰기 성능 저하: INSERT, UPDATE, DELETE 작업 시, 테이블뿐만 아니라 인덱스도 함께 수정해야 하므로 오버헤드가 발생.
  • 특히 인덱스 컬럼의 UPDATE 부하가 큼
• 읽기 중심 서비스와 쓰기 중심 서비스를 구분하여 필요한 최소한의 인덱스만 생성하고, 사용하지 않는 인덱스는 주기적으로 정리해야 함

참고

• https://www.inflearn.com/course/김영한-실전-데이터베이스-기본편