[MySQL] 인덱스(INDEX) 관련 정리

본 포스팅은 '기억보단 기록'을 이라는 타이틀로 유명한 'jojoldu' 님의 인덱스관련 포스팅을 공부차원에서 단순 요약한 포스팅에 불과하므로,

보다 정확한 정보 습득을 원하시는 분들은 필히 아래 링크로 이동 하시길 바랍니다.

https://jojoldu.tistory.com/243

 

[인덱스 기준]

1. 카디널리티가 높은 --> 낮은 칼럼 순서로 인덱스를 잡아야 좋다.

카디널리티가 높다 : 중복수치가 낮다 ex) 주민등록번호

카디널리티가 낮다 : 중복수치가 높다 ex) 이름

 

2. 인덱스를 잡을 때 키의 길이는 짧을 수록 좋다.

InnoDB(MySQL)에서 디스크에 데이터를 저장하는 가장 기본단위를 페이지라 하며, 인덱스 역시 페이지 단위로 관리된다.

인덱스가 저장되는 페이지는 16KB 크기로 고정되어 있다.

키의 크기가 길 수록 페이지에 저장되는 인덱스의 양은 줄어들고,

페이지에 저장된 인덱스의 양이 줄어들 수록, 조회시 더 많은 페이지를 조회해야 한다.

 

 

[인덱스 사용한 조회]

1. 복수개의 칼럼으로 인덱스를 구성했을 때, 조건절 주의사항

인덱스를 회원그룹(group), 나이(age), 성별(gender)  과 같이 잡았을 때,

아래의 쿼리는 인덱스(age)를 타지 않음

select *
from user
where age = 18

반면, 아래의 쿼리는 인덱스(group)를 탄다.

select *
from user
where group='premium'

: 첫번째 인덱스 칼럼은 조회조건에 포함되어야만 한다.

 

2. 그 외의 주의사항

1) between, like, <, > 등의 범위 조건에 사용된 칼럼은 인덱스를 타지만, 그 뒤의 인덱스 칼럼들은 인덱스로 사용되지 않음.

아래 쿼리에서 gender는 인덱스로 사용되지 않는다.

* LIKE 의 경우 'str%' 좌측 일치 패턴만 인덱스가 사용된다.

select *
from user
where group = 'premium'
and age > 18
and gender = 'M'

2) =, IN 조건으로 사용된 칼럼 뒤의 인덱스 칼럼들은 인덱스로 사용된다.

(IN은 =을 여러번 실행 시킨 것이므로)

단, IN 조건 안에 서브쿼리를 넣게 되면 성능상 이슈가 발생. (서브쿼리 외부 먼저 실행 후 IN 구절은 체크조건으로 실행되므로)

 

3) AND 연산자는 ROW 수를 줄이는 역할을 하지만 OR 연산자는 비교해야할 ROW가 더 늘어나므로 풀 테이블 스캔이 발생할 확률이 높음. 

 

4) 인덱스로 사용된 컬럼은 컬럼값 그대로 사용해야 인덱스가 사용된다.

select *
from user
where group = 'premium'
and age+1 = 18

위 쿼리는 인덱스를 타지 못한다.

위 경우, age = 18-1 과 같이 수정해야 한다.

칼럼이 문자열인데 숫자로 조회시 타입이 달라 인덱스가 사용되지 않는다.

 

5) null의 경우 where ? is null 로 인덱스 사용이 가능

일반적인 DBMS 와 달리 Mysql 에선 null 값도 인덱스로 관리

 

 

[인덱스 사용한 조회시 조회 칼럼 순서]

조회조건에 포함되어 있는지가 중요할 뿐, 반드시 인덱스 순서와 조회 칼럼 순서를 지킬 필요는 없다.

(옵티마이저가 조회 칼럼 순서를 인덱스 칼럼 순서에 맞춰 재배열 하는 과정이 추가되긴한다)