[MYSQL] 실행 계획 수행 - possibile_keys, key, key_len, ref

기본 실행 계획 항목 분석

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, extra

 

possibile_keys

옵티마이저가 SQL문을 최적화하고자 사용할 수 있는 인덱스 목록을 출력

실제 사용한 인덱스가 아닌, 사용할 수 있는 후보군의 기본 키와 인덱스 목록만 보여줌 → SQL튜닝 효용성 없음

---

key

옵티마이저가 SQL문을 최적화하고자 사용한 기본 키(PK) 또는 인덱스명을 의미

어떤 인덱스로 데이터 검색했는지 확인 가능 → 비효율적인 인덱스를 사용, 인덱스 자체를 사용하지 않았다면 SQL 튜닝 대상

 

1. 기본 키(PK)로 데이터 접근

EXPLAIN
SELECT 사원번호
	FROM 직급
WHERE 직급명 = 'Manager';

 

결과

 

2. 기본 키(PK)와 인덱스를 사용하지 않음 : NULL,

type : all → 인덱스 활용하지 않음

 

EXPLAIN
SELECT * FROM 사원;

 

결과

---

key_len

사용한 인덱스의 바이트(bytes) 수를 의미 (인덱스를 사용할 때 전체 사용 또는 일부 사용)

UTF-8 캐릭터셋 기준

- INT 데이터 유형 : 단위당 4바이트

- VARCHAR 데이터 유형 : 단위당 3바이트

EXPLAIN
SELECT 사원번호
	FROM 직급
WHERE 직급명 = 'Manager';

'사원번호 + 직급명 + 시작일자'

사원번호 : INT(11) → 4바이트

직급명 : VARCHAR(50) → (50+1) x 3바이트 = 155바이트

key_len = 159 = 4 + 155

 

결과

---

ref

reference의 약자, 테이블 조인을 수행할 때 어떤 조건으로 해당 테이블에 액세스 되었는지를 알려주는 정보

시행한 결과 2개의 행 id = 1 → 사원테이블과 직급 테이블의 조인 수행

드리븐 테이블인 직급 테이블의 데이터에 접근하면 사원번호로 데이터를 검색

WHERE 절의 사원.사원번호 = 직급.사원번호 로도 짐작 가능

EXPLAIN
SELECT 사원.사원번호, 직급.직급명
 	FROM 사원, 직급
WHERE 사원.사원번호 = 직급.사원번호
AND 사원.사원번호 BETWEEN 10001 AND 10100;

 

결과

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 3장 SQL 튜닝의 실행 계획 파헤치기