[DB] Index 적용 및 성능분석, 실행계획 분석

개요

- 게시글을 의미하는 post와 작성자를 의미하는 member 테이블을 만든다

- post 테이블에서 FK로 member의 id를 갖도록 구성한다.

- post 테이블에서 member_id에 인덱스를 지정할 경우와 그렇지 않은 경우에 대해 분석한다.

- post 테이블에는 데이터를 10,000개 넣어두고 분석한다.

 

인덱스를 설정하지 않은 경우

실행쿼리) select * from post where post_owner_id = 1

소요시간) 466 ms (execution: 147 ms, fetching: 319 ms)

 

실행계획 분석

쿼리) explain select * from post where post_owner_id = 1;

 

분석 결과

- type 항목이 ALL 이므로 최악인 '테이블 풀 스캔'이 발생했음.

- possible_key 항목이 post_owner_id 인 것으로 보아 옵티마이저가 최적화를 위해 해당 컬럼의 인덱스를 사용할 수 있음을 의미함

- 가장 중요한 key 항목이 null이다. 즉, 인덱스(PK포함)를 전혀 사용하지 않았음을 의미한다.

- filtered 항목이 100퍼센트이다. 즉, 스토리지 엔진으로 부터 가져온 데이터 중 실질적으로 몇 퍼센트가 최종 결과로 출력되었는지를 보여준다. 100퍼센트이므로 best라고 할 수 있다.

- extra 항목이 Using where이므로 쿼리의 where절에 사용된 조건을 필터 조건으로 사용했음을 의미한다. 여기서 필터조건이란 스토리지 엔진으로부터 가져온 데이터에 적용시킨 조건을 의미한다. 참고로 Using temporary가 사용된 것은 아니므로 다행히도 추가적인 메모리는 사용하지 않았다.

 

 

인덱스를 설정한 경우

인덱스 설정쿼리) ALTER TABLE post ADD INDEX POST_IX01(post_owner_id)

 

실행계획 분석

1) 특정 값 탐색의 경우

실행쿼리) select * from post where post_owner_id = 1

소요시간) 174 ms (execution: 50 ms, fetching: 89 ms)

 

쿼리) explain select * from post where post_owner_id = 1;

 

 

분석 결과

- type 항목이 ALL이고 possible_keys가 만든 인덱스인 것으로 보아 인덱스를 통해 데이터에 접근하는것이 오히려 더 비효율적이라고 옵티마이저가 판단하고 인덱스를 사용하지 않았으며 테이블 풀 스캔을 진행했다. 

- 영속성 컨텍스트에서 결과가 캐싱되기 때문에 애플리케이션을 종료하고 다시 실행한 후 테스트해야 한다.

 

2) 범위 탐색의 경우

실행쿼리) select * from post where post_owner_id >= 1

소요시간) 139 ms (execution: 50 ms, fetching: 89 ms)

 

쿼리) explain select * from post where post_owner_id >= 1;

 

- type이 range 이므로 범위 탐색을 진행했음을 알 수 있다.

- key 항목을 보면 실제로 만든 인덱스가 적용되었음을 알 수 있다.

- key_len은 인덱스를 사용할 경우, 인덱스 풀 스캔 또는 범위 탐색 중 얼마나 탐색을 했는지를 보여준다. 현재 쿼리에서는 인덱스에서 8바이트를 사용했음을 의미한다. 

- extra 항목이 Using index condition 인 것으로 보아, 옵티마이저가 where 절에 사용된 조건을 MySQL이 스토리지 엔진으로 보내서 응답되는 데이터의 수를 줄이는 최적화 작업을 수행했음을 알 수 있다.

 

 

 

 

Reference

- 실행계획 분석 : https://jh-labs.tistory.com/56

- 인덱스 구조 B-Tree : https://jh-labs.tistory.com/147

- 인덱스 종류와 카디널리티 : https://jh-labs.tistory.com/172