[MySQL or MariaDB] 인덱스 관련 정리

이곳저곳에서 검색하고 체크해왔었던 인덱스 관련해서 내용을 정리합니다.

지금까지 계속해서 필요할때마다 검새하고 했더니.. 이거 뭐 검색에 너무 시간이 많이 걸려서 말이죠.

DB 는 제목에 명시해놓은 것 처럼 MySQL, MariaDB 에 관련된 내용이지만 인덱스 전반적인 내용은 Oracle, MS-SQL 과도 별반 다르지 않을 것 같네요.

다시 한번 말씀드리지만, 아래 내용은 어떤 특정 형식화된 문서에서 갈무리 한 것이 아니라 검색을 통해 블로그, 커뮤니티 등을 보고 나름 정리한 내용이므로 원문과 거의 흡사할 수도 있습니다.

굵고 크게 표시된 내용만 훑어 보셔도 도움이 되시리라 생각하며 내용 시작합니다.

 

- 인덱스 선정은 카디널리티가 높은 것으로 잡는다. (유니크한 값을 가진 것)

-> 성별, 학년, 주민등록번호, 계좌번호가 있다면 주민등록번호, 계좌번호로 잡는다.

-> 성별로 잡으면 남/녀 중 1개 이므로 50% 밖에 걸러내지 못함.

-> 주민등록번호로 걸러내면 대부분을 걸러내므로 빠르게 검색 가능.

 

- 복합인덱스

- 구성 시 카디널리티가 높은순 > 낮은순으로 잡는 것이 좋다.

- 가장 먼저 선언한 컬럼은 WHERE절에 포함되어야 한다.

-> NAME (이름), GRADE (학년), CLASS (반) 3개로 잡았을 경우 예제

#1

SELECT        *

FROM        SCHOOL

WHERE        NAME = '이름'

AND                CLASS = 8;

-> GRADE 가 빠졌으나 인덱스를 타긴 탐. 하지만 효율이 떨어짐

 

#2        

SELECT        *

FROM        SCHOOL

WHERE        GRADE = 1

AND                CLASS = 8;

-> 복합인덱스 중 첫번째 컬럼인 NAME 이 빠져 인덱스를 타지 않음.

 

- BETWEEN, LIKE, <, > 등 범위조건은 해당 컬럼은 인덱스를 타지만 그 뒤 인덱스 컬럼들은 인덱스를 사용하지 않음

-> NAME, GRADE, CLASS 3개로 잡았을 경우 예제

SELECT         *

FROM        SCHOOL

WHERE        NAME = '이름'

AND                CLASS = 5

AND          GRADE > 3;

-> CLASS 는 인덱스를 타지 않음

 

- =, IN 은 인덱스를 탄다.

-> IN 은 결국 반복적인 = 이므로

-> IN 은 인자값으로 상수 (일반 값) 를 넣으면 상관없지만 서브쿼리를 넣으면 성능상 이슈 발생

-> 서브쿼리가 먼저 수행되고 IN 은 체크조건으로 성질이 변하기 때문

 

- AND 연산자는 ROW 수를 줄이는 역할을 하지만 OR 연산자는 ROW 가 더 늘어나므로 테이블 풀 스캔 발생 확률 높으므로 WHERE 에서 OR 사용시에는 주의

 

- 인덱스로 사용된 컬럼 값 그대로 사용해야 인덱스가 수행

 

- 인덱스는 가공한 데이터를 저장하고 있지 않으므로 인덱스 컬럼의 데이터를 가공하면 안됨

-> WHERE CLASS * 2 > 4

-> 인덱스 타지 않음

 

-> WHERE CLASS > (10/2)

-> 인덱스를 탄다

 

- 인덱스 컬럼 타입으로 조회해야한다.

-> 문자열 컬럼인데 숫자로 조회 시 타입이 다르므로 인덱스를 타지 않는다.

 

- NULL 을 비교값으로 사용한 경우 인덱스를 타지 않는다.

 

- 이전에는 인덱스 순서와 조회 순서를 지켜야 했으나 지금은 그렇지 않다.        

-> 옵티마이저가 조회 조건의 컬럼을 인덱스 컬럼 순서에 맞춰 재배열하는 과정이 있긴 하지만 거의 차이가 없다고 함.

-> 기왕이면 맞추는게 좋다고.

 

- ORDER BY 의 경우 컬럼 순서 및 쿼리에 명시된 ASC, DESC 또한 인덱스에 적용되어야 한다

-> MySQL 은 인덱스 생성 시 ASC 만을 지원.

-> 인덱스 생성 사양에는 정렬순서 (ASC, DESC) 를 명시 할 수 있게 되어있지만 실제로 DESC 는 무시되어 ASC 로만 생성된다.

-> 즉, 복합인덱스에서 ASC, DESC 가 혼재되어 있다면 인덱스는 무시된다.

 

- 아래 내용은 인덱스 및 성능 관련 추가적으로 메모코자...

- 인덱스가 설계되었어도 쿼리가 느리다면 INNODB_BUFFER_POOL_SIZE 변경 검토

-> 서버에서 MYSQL 만 동작한다는 가정하에 시스템의 80% 정도로 셋팅하면 적정.

 

- PERFORMANCE_SCHEMA 활용

-> PERFORMANCE_SCHEMA 기능을 활성화하면 모니터링으로 인한 오버헤드로 전체 성능이 1~3% 정도 하락

-> 하지만 매우 유용한 서버 모니터링 기능을 제공함.