인덱스의 종류

MySQL에서는 사실 인덱스의 종류가 정말 많습니다

이번에는 인덱스의 종류를 알아보겠습니다!!

 

---

클러스터 인덱스

비슷한것 끼리 묶는다 라는 뜻입니다 

인덱스(PK)와 실제 데이터(레코드)가 같은 물리적 저장 공간에 묶여(Cluster) 있습니다. 그렇기 때문에 인덱스를 통해 찾으면 곧바로 데이터에 접근 가능하다 이를 통해 조회 시 매우 효율적입니다.

클러스터 인덱스는 일반적으로 한 테이블에 하나만 두는 경우가 대부분입니다. (보통 Primary Key와 연결)

 

저장 공간을 많이 차지하며, 데이터와 인덱스가 묶여 있기 때문에  삽입/수정/삭제 시 유지 비용이 상대적으로 높지만 , 데이터가 논리적으로도 정렬된 상태이므로 범위검색 시 효과가 큽니다. 

---

넌 클러스터 인덱스 

 

인덱스 구조만 별도로 저장하고 , 실제 데이터는 다른 공간에 존재합니다

인덱스에서는 데이터가 저장된 위치 정보만 가지고 있고 해당 정보를 가지고 데이터를 다시 조회해야 합니다.

따라서 조회 시 한번 더 접근 (인덱스 → 실제 데이터 ) 이 필요함으로 Clusetered Index 보단 느릴 수 있습니다.

그러나 클러스터 인덱스와는 다르게 여러 칼럼에 걸쳐 다수의 Non-clustered Index를 생성할 수 있어  Select 최적화에 유연합니다. 또한 저장 공간 부담이 비교적 작고, Clustered Index 가 하나뿐인 제약 조건을 보안한다 

 

---

복합 인덱스 (Composite Index)

두 개 이상의 칼럼을 묶어서 만드는 인덱스입니다. 이때 칼럼의 순서가 매우 중요합니다.

CREATE INDEX idx_notice ON notice (status, created_date);

 

이 인덱스는 status가 선두 칼럼이기 때문에 아래 쿼리에서 최대 효과를 발휘합니다.

WHERE status = 'ACTIVE' ORDER BY created_date DESC

 

MySQL은 인덱스를 왼쪽에서 오른쪽으로 읽습니다. status로 범위를 좁힌 뒤 created_date로 정렬까지 인덱스 하나로 처리할 수 있어 filesort가 발생하지 않습니다.

반대로 선두 칼럼인 status를 빠뜨리고 created_date만 조건으로 쓰면 이 인덱스는 사용되지 않습니다. 복합 인덱스는 선두 칼럼부터 순서대로 사용해야 한다는 것이 핵심입니다.

 

 

---

커버링 인덱스

 

쿼리에서 필요한 모든 칼럼이 인덱스 안에 포함되어 있는 경우, MySQL은 실제 테이블에 접근하지 않고 인덱스만으로 결과를 반환합니다. 이것을 커버링 인덱스라고 합니다.

만약 테이블에 Where 절에 조건이 2개인 쿼리를 날린다고 해봅시다. 이때 각각의 조건으로 들어가는 칼럼을 복합 인덱스로 설계를 하면 그것이 커버링 인덱스가 됩니다.

WHERE announce_id = ? AND member_id = ?

조건으로 데이터를 확인할 때, 두 칼럼이 모두 인덱스에 포함되어 있어 테이블 접근 없이 인덱스만으로 처리됩니다.

EXPLAIN 결과에서 Using index라고 표시되면 커버링 인덱스로 동작하고 있는 것입니다.

 

---

Hash 인덱스

B-Tree와 달리 해시 함수를 이용해 키를 고정 길이의 해시값으로 변환하고, 그 해시값으로 데이터 위치를 찾습니다. 이론적으로 O(1) 탐색 성능을 보여주기 때문에 정확히 일치하는 값을 찾을 때는 매우 빠릅니다.

 

다만 치명적인 단점이 있습니다.

해시값에는 순서가 없기 때문에 범위 조회나 정렬에 전혀 사용할 수 없습니다.

MySQL InnoDB에서는 사용자가 직접 Hash 인덱스를 생성할 수 없고, 내부적으로 자주 조회되는 데이터에 대해 자동으로 생성되는 Adaptive Hash Index 형태로만 존재합니다.

 

---

FULLTEXT 인덱스

일반 B-Tree 인덱스로는

 LIKE '%검색어%'

 

같은 텍스트 검색을 효율적으로 처리할 수 없습니다.

 

FULLTEXT 인덱스는 텍스트를 토큰 단위로 분리하고 역인덱스(Inverted Index) 구조로 저장해 자연어 검색, 불리언 검색 등을 지원합니다.

SELECT * FROM article WHERE MATCH(content) AGAINST('검색어' IN BOOLEAN MODE);

 

일반 WHERE 조건이 아니라 MATCH... AGAINST 문법을 사용해야 합니다. 게시판이나 상품 검색처럼 텍스트 내용으로 검색해야 하는 경우에 활용합니다.

 

---

인덱스 사용 시 주의 

데이터가 삽입/삭제/갱신될 때마다 해당 인덱스도 함께 조정해야 합니다. (오버헤드)

따라서 자주 사용하는 검색 조건(Where)이나 Join 칼럼 등에만 인덱스를 두는 것이 일반적입니다.

인덱스가 많으면 SELECT는 빨라질 수 있지만 , INSERT/UPDATE/DELETE 비용은 상승하게 됩니다. 또 테이블이 작거나 , 특정 칼럼 검색 빈도가 낮다면 인덱스 효과는 미미 할 수 있으니 주의해야 합니다.

 

---

쿼리 플래너 

인덱스를 설계하면서 한 가지 중요한 사실을 알게 됐습니다. 인덱스를 아무리 열심히 만들어도 DB가 항상 그 인덱스를 사용하는 건 아니라는 것입니다.

MySQL은 쿼리를 실행하기 전에 쿼리 옵티마이저(Query Optimizer)라는 내부 모듈이 먼저 실행 계획을 세웁니다.

 

옵티마이저는 인덱스를 사용할지, 아니면 테이블 전체를 스캔할지를 스스로 판단합니다. 그리고 경우에 따라서는 인덱스를 쓰는 것보다 Full Scan이 더 빠르다고 판단하면 인덱스를 아예 무시하기도 합니다.

 

예를 들어 테이블 전체 데이터의 50% 이상을 가져와야 하는 쿼리라면, 인덱스를 타면서 개별 row를 하나씩 찾아가는 것보다 그냥 테이블을 처음부터 끝까지 읽는 게 더 빠를 수 있습니다. 인덱스를 통한 조회는 결국 포인터를 따라가는 추가 작업이 생기기 때문입니다. 옵티마이저가 실행 계획을 세울 때 고려하는 요소들은 다음과 같습니다.

 

 

• 테이블 통계: 전체 row 수, 페이지 수 등
• 데이터 분포: 특정 값이 얼마나 고르게 퍼져 있는지
• 카디널리티(Cardinality): 칼럼 내 고유한 값의 수. 카디널리티가 높을수록 인덱스 효과가 크고, 낮을수록 효과가 줄어듭니다. 예를 들어 성별 칼럼처럼 값이 2가지뿐인 칼럼에 인덱스를 달면 옵티마이저가 인덱스를 타지 않을 가능성이 높습니다.

 

---

그래서 EXPLAIN이 필요합니다

개발자가 SQL을 작성하면 DB가 알아서 최적의 경로를 찾아 실행합니다. 하지만 내가 의도한 대로 인덱스가 실제로 사용되고 있는지는 직접 확인해 봐야 압니다. 그때 사용하는 것이 EXPLAIN입니다.

 

EXPLAIN SELECT * FROM announce 
WHERE status = 'ACTIVE' 
ORDER BY pub_date DESC;

 

 

EXPLAIN을 실행하면 DB가 해당 쿼리를 어떤 경로로 처리하는지 실행 계획을 보여줍니다. 여기서 확인해야 할 핵심 칼럼들은 다음과 같습니다.

컬럼	의미
type	접근 방식. ALL이면 풀스캔, ref, range, const 등이면 인덱스 사용
key	실제로 사용된 인덱스 이름. NULL이면 인덱스 미사용
rows	예상 스캔 row 수. 낮을수록 좋음
Extra	Using index면 커버링 인덱스, Using filesort면 인덱스로 정렬 못 함

 

인덱스를 설계한 뒤에는 반드시 EXPLAIN으로 실제로 인덱스가 사용되는지 확인하는 습관이 필요합니다. 저도 이번 프로젝트에서 인덱스를 달고 나서 EXPLAIN을 돌려봤을 때 예상과 다르게 동작하는 경우를 직접 경험했습니다.

인덱스를 설계하는 것과, 그 인덱스가 실제로 쓰이도록 유도하는 것은 다른 문제입니다. 인덱스를 달았으면 EXPLAIN으로 확인하는 것까지가 인덱스 설계입니다.