[최적화] 인덱스 explain

서론

※ 과거에 기록한 내용에서 중요한 부분만 발췌하여 모두가 이해하기 쉽게 다시 서술한다.

 

본론

- 실행 계획

DBMS가 실행을 할 때 어떻게 쿼리가 실행되는지 분석한 것을 보여주는 것으로 실행 계획을 함으로써 적절한 인덱스를 선택할 수 있다. type은 위에서 부터 조회 속도가 빠른 순이다. 개발자는 ALL을 없애는 방향으로 개발하면 된다.

 

- pk로 검색

first name은 중복 가능한 컬럼이라 데이터를 찾고 또 다음 행을 찾는 full scan을 하는데 중복을 불허한 컬럼은 검색할 데이터 1개를 찾고 바로 끝내서 매우 빠르다. 이 처럼 중복을 불허한 컬럼으로 인덱스를 생성하고 단 1건 검색하면 실행 계획 const를 사용한다. pk 검색이 가장 빠르고 일반적으로 pk 검색이 가장 좋다.

 

- UQ로 검색

dept_name은 UQ 키가 걸려있다. 이는 앞서 배운 클러스터링과 논 클러스터링이 같이 있는 상황이다.

 

UQ 키로 정렬이 되어있고 조회를 할 때 UQ키로 검색하면 pk와 동일하게 중복이 없어 1개 찾고 끝내서 빠른 조회가 가능하다. 역시 실행 계획이 const로 나온다.

 

- eq-ref

ref : 중복을 허용한 컬럼의 인덱스로 여러건 검색 시 사용
eq-ref : 중복을 불허한 컬럼의 인덱스로 여러건 검색 시 사용

 

ref : SELECT * FROM users WHERE email = 'example@example.com';
eq-ref : SELECT * FROM users WHERE id = 1; 단 한 건이 아님. 따라서 조인 시 사용 됨
const : SELECT * FROM users WHERE id = 1; 단 한 건 가져오는 것

eq-ref는 const처럼 중복을 불허한 인덱스로 검색을 했을 때 사용되지만 차이점은 단 한 건 검색이 아니다. 따라서 조인 시 중복을 불허한 컬럼으로 여러 건 가져올 때 사용된다.

 

예를 들어보자. dept를 거쳐서 사원 목록을 찾아볼 것이다. 여기서 중요한 것은 "어떤 테이블을 먼저 읽느냐"로 이너 조인의 경우 DBMS의 최적화에 의해 먼저 where 절로 검색하고 검색한 일부 데이터를 사용해, 조인 컬럼으로 대상 테이블의 같은 조인 컬럼을 검색한다. 아래 쿼리는 전체 조인을 하고 where 절을 하는 것이 아니라, 먼저 dept_no가 있는 테이블을 where 절로 읽고 읽은 데이터를 가지고만 emp 테이블과 조인한다.

 

이때 where에 equal 쿼리에 사용된 컬럼이 dept_no인데, 매핑 테이블에서 dept_no는 중복을 허용하므로 여러 건이 검색되므로 req가 사용된다. 그 후 찾은 부서명 = 005 데이터들의 emp_no로 emp 테이블의 emp_no을 검색하는데 이때 두번째 읽히는 테이블의  검색 컬럼이 중복을 불허한 컬럼이고 이 컬럼의 인덱스가 생성되어 있다면 이미 찾은 005 데이터들의 개수가 여러개라 여러 건이 조회되지만 중복을 불허한 컬럼에 대한 검색이므로 eq-ref가 나온다. dept 테이블에서 dept no가 005인 것을 equal 검색으로(ref) 먼저 찾고 emp 테이블과 조인하는 것이 매우 중요한 포인트이다.

 

무조건 먼저 필터링 후 조인하는 것이 빠르며, 따라서 경우에 따라 최적화에 의해 조건 필터링의 대상이 되는 다른 테이블을 먼저 찾을 수도 있다는 것이다. emp 테이블을 검색 주체로 하면 emp를 먼저 검색 필터링한다.

 

이때 first name은 uq가 아니고 여러 건 조회이므로 ref이다. emp 테이블에서 georigi를 찾고 dept_emp 매핑 테이블에 emp_no로 검색 시 pk로 검색하는 것이지만 dept_emp는 매핑 테이블이라서 emp_no가 중복이 허용되고 따라서 여러 건 조회되어 ref이다. 검색할 때 pk, uq처럼 Non Unique가 0인 인덱스로 하면 eq-ref고 유일성을 보장하면 ref이다.

 

- range 검색

생일 인덱스가 없는 상태에서 생일로 범위 검색을 하면 ALL로 풀스캔하여 범위에 해당하는 행을 찾고 또 찾아 너무 느리다.

 

생일 인덱스를 만들면 생일로 정렬된 인덱스가 생기고 모든 테이블을 일일히 보지 않고 처음과 끝 범위만 찾으면 된다. 실행 계획을 보면 range로 나오는데 이것이 바로 실행 계획으로 ALL을 없애는 방법으로, 실행 계획을 주고 검색을 해봤더니 ALL이면 시간이" 오래 걸리는 구나 인덱스를 줘야겠다."라고 생각하고 인덱싱을 하면 된다.

 

- all 검색

경우에 따라 반드시 풀 스캔을 해야하는 경우가 있다. 성별은 데이터 종류가 2개밖에 없어서 다수 출력값이 나오므로 인덱스 리스트 탐색과 데이터 로드 과정에서 오버헤드가 발생해 더 오래걸린다. 카디널리티 수치가 큰 컬럼이 인덱스가 적절하며 작은 컬럼은 인덱스에 적합하지 않다.

 

gender에 인덱스를 줬음에도 풀 스캔을 한다. 성별에 인덱스를 줘봤자 DBMS의 옵티마이저가 분석하기에 풀 스캔이 났다고 판단하여 풀 스캔을 한다.

 

카디널리티가 1자리수로 나오는 컬럼은 DBMS가 인덱스를 만들지 않는다. docs에 중복 분포도가 20%가 넘는 컬럼은 인덱스를 사용하지 않는다고 나와있다.

 

- like 검색

이름에 인덱스를 주고 like 이병%을 검색하면 이병 <= <= 이볒로 범위 검색이라 실행계획 타입이 range이다. 이름으로 정렬하고 이병을 시작으로 이볒을 끝만 찾았다는 얘기다.

 

%병헌은 %가 앞에 있으면 인덱스로 사전 순 정렬을 할 수 없어서 ALL 풀스캔이 된다. 따라서 %가 앞에 붙은 검색은 지양해야 한다. 따라서 fulltext를 사용한다.

 

%를 앞에 붙이는 경우 강력한 검색 효과를 가져오지만 인덱스를 사용하지 않으므로 속도가 느리다. 데이터가 억 단위의 경우 %로 시작하는 검색은 엄청나게 오래 걸리므로 강력한 검색 효과를 할 수 있을 지라도 지양해야 한다.

 

- fulltext 검색

전체 문서 검색으로 게시판 검색에서 반드시 사용해야 한다.

 

innoDB는 DBMS에서 테이블을 만드는 기본 엔진으로 ft_min_token_size 속성은 fulltext 검색을 할 때 검색이 가능한 최소 글자 수로 3이면 3글자 이상만 전문 검색이 가능한 상태이다.

 

=> 검색 문법 where + match

ft는 '로마의 휴일'을 '로마'나 '휴일'로 검색하고자 할 때 사용한다. like로도 가능한데 그러면 로마%는 가능하지만 %휴일이 인덱스를 사용하지 않아 엄청 느려져서 ft를 사용하는 것이다.

 

ft를 하면 '의리적 구토'로 떨어져있는 문장들을 다 sep로 쪼개 인덱스를 만들고 '의리적', '구토' 이런 식으로 인덱스를 잡아 쪼갠 단어들을 검색에 사용할 수 있다.

 

단어들을 쪼개서 인덱스를 잡는 것을 ft 인덱스라고 하고 쪼개진 단어를 통해서 검색하는 것이 ft 검색이다. ft 검색을 하기 위해 match에 들어가는 컬럼은 반드시 ft 인덱스가 잡혀있어야 한다.

 

-> ft 검색 기능

ft 검색의 다양한 기능을 알아보자.

 

1. 단어 검색

where match(ft 인덱스 컬럼) against('검색 단어')라고 하면 휴일이라는 단어가 있는 모든 영화가 검색된다. ft 인덱스에서 '검색 단어'를 검색한다.

 

2. 다중 단어 검색

,를 넣으면 따로 따로 검색이 가능하다.

 

3. boolean 모드

1) %가 앞에 있는 검색

하지만 로마의 휴일은 '로마의'와 '휴일'로 인덱스가 쪼개져서 '로마'로는 검색이 안 된다. 이럴 때 ['*' in boolean mode] 라고 하면 '로마'로 시작하는 '로마의 휴일'이 검색 가능하다.

 

2) 필수로 포함, 제거

+는 검색에 필수로 들어가야 하는 단어이고, -는 있으면 안 되는 값이다. 열정과 냉정이 무조건 들어가야하는 것과

 

열정은 들어가고 냉정은 들어가면 안 되는 것을 지정할 수 있다.