MySQL 튜닝 : 적합한 인덱스 생성하기

Case 1 : 적합한 인덱스가 없을 때

 

다음은 이름이 Badri 인 사원을 검색하는 쿼리입니다.  먼저 적합한 인덱스가 없어 테이블 전체 299335건을 탐색했습니다(type : ALL). 그리고 나서  where 조건을 이용해10%만 걸러내었습니다. ( filtered : 10.00,  Extra Using where)

SELECT E.EMP_NO 
FROM employees E WHERE E.FIRST_NAME = 'Badri';

 

 

실제 저 쿼리 결과로 검색되는 데이터의 건수는 227건입니다. 전체 중 227건을 추출하기 위해 테이블 전체를 스캔하는 것은 비효율적입니다. 그래서 first_name을 구성 칼럼으로 가지는 인덱스를 추가해주었습니다. 

alter table employees add index idx_firstName(first_name);

다음은 인덱스를 추가한 뒤의 실행계획입니다. index를 이용해 데이터 바로 접근했음을 알 수 있습니다. (ref:const, Extra:Using Index)

* idx_firstName이 unique 인덱스가 아니기 때문에 type은 ref로 출력됩니다.

 

Case 2 : 인덱스가 있으나 사용하지 못한 경우

현재 사원 테이블의 인덱스 구성은 다음과 같습니다.

 

다음은 first_name이 Badri거나 입사일자가 '1997-12-01'인 사원을 찾는 쿼리문입니다.

select emp_no, first_name, last_name, hire_date 
from employees 
where first_name = 'Badri'
or hire_date = '1997-12-01'

 

실행계획을 살펴보면 TABLE FULL SCAN(type : All)으로 데이터를 탐색합니다. where 조건이 or로 구성되어 있어 적합한 인덱스를 찾지 못해 table full scan으로 처리했습니다.

 

* key:null -> 인덱스를 사용하지 못함

 

인덱스를 사용하도록 힌트를 사용하더라도 idx_firstName 만으로는 hire_date 조건을 검색하지 못해 비효율적이므로 힌트를 무시합니다.

select emp_no, first_name, last_name, hire_date 
from employees /*! use index (idx_firstName) */
where first_name = 'Badri'
or hire_date = '1997-12-01'

 

따라서 hire_date를 구성칼럼으로 갖는 index를 추가해줍니다.

alter table employees add index idx_hireDate(hire_date);

인덱스 추가 후 실행계획은 다음과 같습니다. idx_firstName과 idx_hireDate를 합쳐서 사용했음을 알 수 있습니다. (type index_merge)