DB Index 팁 간단 정리 (ft. Maria DB, MySQL)

정리하게 된 계기

오늘 DB튜닝을 하다가 실행계획을 보았는데, 제가 알고 있던것과 다른점이 있어

믿보 블로그(기억보단 기록을)의 내용과 이번에 쓰면서 알게 된 점을 정리해봤습니다.

 

그 내용은...

여러개의 컬럼으로 이루어진 복합 인덱스의 컬럼 순서를 지키지 않았는데 인덱스를 타버리네?! 

Index1 : A,B,D
Index2 : C,D
쿼리1 : where A = '조건' and D = '조건' and  B = '조건'
쿼리2 : where C = '조건' and D = 조건''

쿼리 2는 Index2를 타고, 쿼리 1은 Index1과 컬럼 순서가 달라서 당연히 오래걸리겠거니 했는데, 제가 잘못 알고 있었어요.

위처럼 and 조건의 순서가 실제 인덱스와 꼭 같지 않더라도, 실행계획이나, 실제 수행시간이 똑같이 나왔습니다.

컬럼 순서를 지켜야, 인덱스를 탄다고 알고 있었는데, 민망하니까 후딱 찾아봤습니다.

 

인덱스 생성시

○ 카디널리티(중복을 제외하는 수치) 높은 컬럼 사용
○ 인덱스의 생성시 컬럼 순서는 카디널리티가 높음 -> 낮음 순서가 성능이 좋음

인덱스 사용 조회시

○ 인덱스가 여러 컬럼인 경우, 몇몇 조건 생략 가능
(인덱스 컬럼 앞에서 부터 포함하는게 좋음, index-컬럼1,2,3,4 중 3,4 생략가능)
○ 범위 조건 (Between, like, <,>)은 해당 컬럼까지만 인덱스를 타고, 그 뒤로는 인덱스를 타지 않음
○ In, =는 다음 컬럼도 인덱스 사용 (in은 =의 멀티)
○ Or 보다는 and가 성능상 좋음 (or는 row가 늘어나서 풀스캔 가능)
○ 인덱스의 컬럼을 그대로 사용해야 함 (연산 처리를 하지 않고)
○ 예전처럼 인덱스와 컬럼 순서를 반드시 지키지 않아도 인덱스 사용 가능 (컬럼 재배치 과정이 추가 되지만 거의 같음)

 

출처 - 기억보단 기록을

 

실제 사용하면서, 느낀점

• 시간/날짜를 범위로 많이 검색하는 경우
  인덱스 생성시 시간 컬럼을 뒤쪽에 배치하는게 유리 (인덱스의 최대한 많은 컬럼에 대해 인덱스 사용)
• 정확한 시간/날짜로 많이 검색하는 경우
  인덱스 생성시 시간 컬럼을 앞쪽에 배치하는게 유리 (시간으로 필터링 = 카디널리티 높음)
• or , !=, not in 은 비효율적
  or는 row수가 늘어나고, not 이 붙는 연산자는 결국 full-scan을 타게 되는 경우가 대부분이라, 되도록 지양하는게 성능상 유리
• 인덱스의 컬럼 순서를 지키지 않아도 인덱스 사용 가능 (그래도 이왕이면 지키자)