DB 설계 PART.인덱스 explain

서론

※ 이 포스트는 다음 강의의 학습이 목표임을 밝힙니다.

https://www.youtube.com/playlist?list=PL9hiYwOHVUQduJN7Pf_kOR8htpJU7K1H8 

2020 데이터베이스

 

본론

- 실행계획

실행 계획은 DBMS가 실행을 할 때 어떻게 최적의 쿼리를 실행할 지 분석한 것을 보여주는 것으로 개발자가 쿼리를 작성할 때 어떻게 하는 것이 좋을지 제안합니다. 실행 계획을 함으로써 적절한 인덱스를 선택할 수 있으며 type은 위에서부터 조회 속도가 빠른 순입니다. 계획이지 실제 실행을 하는 것은 아니고 최적의 쿼리 분석 결과라고 보면 됩니다. 따라서 개발자는 ALL을 없애는 방향으로 개발을 하면 됩니다.

 

- pk로 검색

다른 인덱스와 pk로 검색한 것의 차이는 first_name으로 검색하면 중복이 가능한 컬럼이라 first_name = '호프만'은 한 개 찾고 또 다음 행을 찾아 full scan을 하는데 pk는 중복이 안 되어서 검색할 데이터가 1개 밖에 없습니다. 그래서 데이터를 한 개 찾고 검색을 바로 끝내 매우 빠릅니다. pk는 한 개 찾고 끝내 버린다는 것입니다. pk 검색이 가장 빠르고 일반적으로 pk 검색이 가장 좋습니다.

 

- UQ로 검색

dept_name은 UQ 키가 걸려있습니다. 이는 앞서 배운 클러스터링과 논 클러스터링이 같이 있는 상황으로 pk가 아닌 유니크 키로 정렬이 됩니다. UQ와 pk가 같이 있는 경우 pk로 인덱스가 생성이 되고 클러스터링 인덱스는 한 개 테이블에 한 개만 가능하다고 했는데 그건 UQ + NotNull일 때 얘기고 이 경우는 클러스터링과 논 클러스터링이 같이 있는 경우입니다.

 

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

 

- eq-ref

조인할 때 두번째 이후에 읽은 테이블의 pk로 조인했다는 것을 의미합니다. 여기서 중요한 것은 "어떤 테이블을 먼저 읽느냐"입니다.

 

dept를 거처간 사원 목록을 찾아봅니다. dept_emp에서 사원 목록을 검색할 건데 employees 테이블의 first_name을 보기 위해 employees와 조인합니다.

 

이를 실행계획을 보면 dept_emp에서 dept_no가 'd005'인 것을 먼저 찾고 그 상태에서 employees로 조인이 들어갑니다. 먼저 조건으로 거른 다음에 조인합니다. 이는 join 문법에 앞에 어떤 테이블을 두냐에 따라 무엇을 먼저 거를 지 결정되는 것이 아닙니다. 그건 아우터 조인의 얘기이고 이너 조인의 경우 DBMS에 옵티마이저에 의해 먼저 거를 테이블을 선택하여 거르고 조인이 됩니다.

 

무조건 먼저 거른 후 조인이 되는 것이 빠르며 때문에 DBMS가 where에 dept_no 컬럼이 있는 dept_emp 테이블을 먼저 실행하고 조인하는 것입니다. 따라서 경우에 따라서 옵티마이저에 의해 조건이 있는 다른 테이블을 먼저 찾을 수 있다는 것입니다. emp 테이블에 있는 first_name을 조건으로 검색하면 emp를 먼저 검색합니다.

 

- range 검색

between으로 생일을 범위 검색을 하면 all로 풀 스캔을 하여 너무 느립니다.

 

생일로 일반 인덱스(UQ가 아닌)를 만들면 생일로 정렬될 것입니다. 정렬을 하게 되면 모든 테이블을 다 일일히 보지 않고 처음과 끝인 범위만 찾으면 되게 됩니다. 즉 정렬을 하고 between를 하는 것을 range 검색이라고 하고 실행 계획을 보면 range로 나옵니다. 이게 정말 중요한 게 실행 계획을 주고 검색을 해봤더니 all 풀 스캔을 하면 "시간이 오래걸리겠구나 인덱스를 줘야겠다."라고 생각을 할 수 있다는 것입니다. 되도록이면 all을 다 없애줘야합니다.

 

- all 검색

그런데 경우에 따라서는 풀 스캔을 반드시 해야하는 경우가 있습니다. 성은 데이터 종류가 2개밖에 없어서 인덱스를 통해서 검색하는 것이 더 오래걸립니다. 카디널리티 수치가 큰 컬럼이 인덱스가 적절하며 작은 컬럼은 인덱스에 적합하지 않습니다.

 

gender에 키를 줬음에도 불구하고 풀 스캔하는 것을 알 수 있습니다. 성별에 인덱스를 줘봤자 DBMS의 옵티마이저가 분석하기에 풀 스캔하는게 났다고 판단하여 풀 스캔을 하기로 한 것입니다.

 

show index를 했을 때 카디널리티가 1자리수로 나오는 것은 인덱스를 만들지 않고 DBMS가 사용하지 않습니다. docs를 보면 중복 분포도가 20%가 넘는 컬럼은 인덱스를 사용하지 않습니다.

 

- like 검색

name에 인덱스를 주고 where name like 이병%을 검색을 하면 이병 <= 이름 <=이볒의 범위 검색입니다.

 

%병헌은 %가 앞에 있으면 인덱스로 정렬을 할 수 없어서 all로 풀 스캔이 됩니다.

 

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

 

- fulltext 검색

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

 

innoDB는 DBMS에서 테이블을 만드는 default 엔진으로 ft_min_token_size 속성이 있습니다. fulltext로 전문 검색을 할 때 검색이 가능한 최소 글자 수로 min_token이 3면 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) +, -

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

 

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