[MySQL] 데이터베이스 기초 - 인덱스 (Index)

 인덱스 개념 

 

인덱스란 SELECT 구문을 사용할 때 조회되는 속도를 높이기 위해서 특정 컬럼의 데이터를 말 그대로 인덱싱 하는 기술을 의미합니다. 책을 읽을 때 맨 뒤쪽에 색인(Index) 부분을 떠올리시면 좋을 것 같습니다. 만약 어떤 책에서 특정 단어를 포함한 내용을 눈으로만 찾으려면 정말 오래 걸릴 겁니다. 하지만 색인 부분에서 특정 페이지를 찾고 해당 부분을 찾는 다면 훨씬 빨리 찾을 수 있습니다. 물론 컴퓨터는 색인이 되어 있지 않아도 빨리 찾을 수 있지만 특정 자원을 활용하기 위해서는 반드시 인덱스를 사용하고 효율적으로 데이터를 찾아서 반환하는 게 중요합니다.

 

 

 

 인덱스 키의 종류 

 

• 프라이머리 키 : 보통 테이블 생성 시 기본 적으로 만들게 되는 기본키입니다. 키 이름이 없는 것이 특징이고 가장 빠르게 찾을 수 있는 필수 키입니다.
• 유니크 키 : 프라이머리 키는 아니지만 기본 키처럼 테이블에 유일성을 보장하는 키입니다.
• 인덱스 키 : 일반적으로 만들어서 사용하는 키입니다.
• 풀 텍스트 키 : 키가 없으므로 전문 검색

 

▼ 인덱스 키를 확인하기 위해서는 다음과 같이 인덱스를 조회합니다. 특히 이중에 Carinality를 잘 살펴봐야 합니다.

show index from 테이블

---

 

▼ 다음과 같이 테이블 정의를 확인하면 인덱스 키의 이름과 해당 컬럼들을 확인할 수 있습니다.

show create table 테이블

---

 

 

 

 인덱스 추가 삭제 

 

보통 테이블을 만들고 인덱스를 설정해줍니다. 검색 시 해당 컬럼이 빈번하게 조회 대상이 된다면 인덱스키를 추가하는 것을 고려해야 합니다 한 테이블에는 3~4개의 인덱스가 적당하며 만약 해당 인덱스가 적합하지 않다면 제거해야 합니다. 이는 조회 속도의 문제 보다도 UPDATE, INSERT 구문 실행 시 인덱싱을 다시 한다는 문제 때문입니다. 아무래도 인덱스가 많다 보면 데이터를 추가하거나 수정할 때 속도가 느려지겠죠.

 

 

▼ 인덱스키 추가 / 삭제

 

 

▼ 키를 추가할 때 키 이름을 포함해서 만들 수 있습니다.

 

 

▼ 다음과 같이 복합 인덱스를 만들 수도 있습니다.

 

 

일반적으로 다음과 같은 컬럼에 인덱스를 부여합니다.

• where 절에서 비교하는 컬럼
• order by 로 정렬하는 컬럼
• group by 로 그룹화하는 컬럼

 

다음과 같은 컬럼은 인덱스를 부여하지 않습니다.

• 검색이나 정렬에 포함되지 않는 컬럼
• 성별과 같은 낮은 Cardinality를 갖는 컬럼
• 성별과 같은 높은 분포도의 컬럼 (10 ~ 30% 분포도)

 

 

 

 실행계획 (Explain) 

 

인덱스에 따라서 SELECT 구문을 사용할 때 MySQL 옵티마이저는 자동으로 실행계획을 수립하고 해당 구문을 실행합니다. 따라서 인덱스를 잘 구성해야 실행계획이 잘 만들어지고 검색 속도에도 영향을 미칩니다. 그렇기 때문에 실행 계획의 종류를 파악하고 이에 따른 실행계획이 정상인지 다음과 같이 explain 키워드로 해당 실행계획을 확인해 보는 게 좋습니다. 만약 실행계획이 all이라면 전체 검색(full scan)을 뜻하니 성능이 좋지 않습니다.

const	primary key 또는 unique key를 사용해서 1건을 가져오는 쿼리
eq-ref	조인에서 두 번째 이후에 읽는 테이블의 프라이머리 키로 조인
ref	인덱스에 equal로 검색
fulltext	전문검색 인덱스(fulltext key)를 사용
range	인덱스를 범위로 검색
all	테이블 full scan

 

▼ 다음과 같이 between 조건을 통해서 검색을 한다면 인덱스 키가 있다는 가정하에 range 실행계획으로 동작합니다. 또한 테이블 JOIN 시 해당 테이블이 프라이머리 키로 SELECT 한다면 eq-ref로 설정됩니다.