MySQL 실행계획 분석 (3) - key , key_len , possible_keys

intro

 

지난 글에서 배운 type 이 테이블(인덱스)에 접근하는 방법을 나타낸다면 

오늘 배울 key , key_len , possible_keys 는 어떤 인덱스를 , 얼마나 탔는지를 나타냅니다. 

 

마찬가지로 인덱스를 잘 탔는지 체크하는 것은 중요하기에 꼭 체크해야 하는 컬럼 입니다. 

---

예시 테이블 

실습에서 사용할 테이블은 다음과 같습니다. 

 

 

대출 신청서 테이블에서 아래의 2가지 인덱스를 생성했습니다. 

 

idx_amount 인덱스 (key 값은 amount) 

idx_amount_status 인덱스 (key 값은 (amount , status)) 

---

key : 최종 선택된 실행 계획에서 사용하는 인덱스 

 

실행 계획에서 사용하는 인덱스 명이 옵니다. 

의도 했던 인덱스가 표시되는 지 확인하는 것 이므로 매우 중요 합니다. 

 

크게 다음 3가지가 올 수 있습니다.

• NULL
• PRIMARY
• 인덱스를 생성할 때 부여했던 고유 이름 

풀 테이블 스캔 (type 컬럼이 ALL) 같이 인덱스를 전혀 사용하지 못하면 key 컬럼은 NULL 로 표시 됩니다. 

 

PRIMARY 와 인덱스를 생성할 때 부여했던 고유 이름 같은 경우 , MySQL 인덱스에 대한 사전 지식이 필요 합니다. 

 

 

mySQL 의 InnoDB 의 경우 , 인덱스를 2가지로 나눌 수 있습니다. 

• Secondary Index : 인덱스 키 값(amount , status)과 PK(id) 값을 가지고 있습니다. 
• Primary Index : 인덱스 키가 곧 PK 이며 , 리프에 실제 레코드 데이터 전체가 포함되어 있습니다. 

key 값이 인덱스를 생성할 때 부여했던 고유 이름 이라면 ? 

1. Secondary Index 를 먼저 탑니다.
2. Secondary Index 의 리프에서 PK 를 얻습니다. 
3. PK 를 바탕으로 Primary Index 를 탑니다. 
4. Primary Index 의 리프에서 실제 레코드를 얻습니다. 

key 값이 PRIMARY 라면 ? 

1. PK 를 바탕으로 Primary Index 를 탑니다. 
2. Primary Index 의 리프에서 실제 레코드를 얻습니다. 

WHERE 절에서 직접 PK 를 사용하는 경우 , key 값이 PRIMARY 가 될 수 있으며 Secondary Index 를 타지 않기에 성능 상의 이점이 있습니다. 

---

key_len : 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값 

 

key_len 은 MySQL 이 쿼리를 처리하기 위해 인덱스 키에서 몇 바이트를 사용했는지를 나타냅니다. 

실제 인덱스를 만들 때는 , 다중 컬럼으로 만드는 경우가 많은데 그때 , 몇개의 컬럼까지 사용했는지 체크해 볼 수 있습니다. 

 

Case A : 간단한 예시 

 

<요구사항>

대출 신청 데이터 중 금액이 5000 인 건 조회

(WHERE amount = 5000) 

 

 

idx_amount 에서 인덱스의 키는 amount 이며 type 은 decimal(15,2) 입니다. 

decimal(15,2) 는 7 바이트를 차지하기에 key_len 은 7 입니다. 

 

Case B - 1 : 비효율적인 인덱스 사용 

 

<요구사항>

대출 신청 데이터 중 금액이 1000만원을 초과하면서 승인된 건을 조회

(WHERE amount > 10000000 AND status = 'APPROVED') 

이때 , (amount , status) 복합 인덱스를 만들었으니 , 두 컬럼을 모두 사용해 빠르게 필터링

 

 

key_len 이 7 인 것을 통해 amount 만 인덱스에 사용되었음을 알수 있습니다. 

MySQL 구조 상 , 선행 컬럼이 범위 조건(amount > 10000000) 이면 ,

그 뒤의 컬럼은 인덱스 탐색에 쓰이지 못하고 단순 필터링 용도로 쓰이게 됩니다. 

 

Case B - 2 : 비효율적인 인덱스 튜닝 해보기

 

대출 상태(status) 는 보통 동등 조건으로 많이 쓰이고 , 금액(amount) 은 범위 조건으로 많이 쓰입니다. 

이럴 때는 동등 조건으로 쓰이는 컬럼을 인덱스 앞쪽으로 배치 하는 것이 유리 합니다. 

 

-- 튜닝을 위한 새로운 인덱스 생성
ALTER TABLE loan_applications ADD INDEX idx_status_amount (status, amount);

 

 

key_len 의 결과가 90 입니다. 

status(=) 와 amount(>) 가 모두 인덱스 탐색에 사용되었습니다. 

• status(83바이트) : 동등 조건으로 정확한 지점 찾음 
• amount(7바이트) : 그 지점부터 범위 스캔 시작 

---

possible_keys : 사용될 법 했던 후보 인덱스 목록 

 

옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 인덱스의 목록 입니다. 

 

Case A : index scan 대신 full table scan

 

 

possible_keys 를 보면 , idx_amount , idx_amount_status , idx_status_amount 3개의 인덱스가 후보로 있었습니다. 

하지만 key (최종 사용한 인덱스) 를 보면 NULL 입니다. 

이는 옵티마이저가 통계정보를 확인해보니 , 조건에 해당 하는 데이터가 전체의 20% ~ 25% 이상 인것으로 파악한 것 입니다. 

인덱스를 타고 데이터를 조회하는 것 보다 처음부터 full table 을 scan 하는게 성능 상 우위에 있다고 판단한 것 입니다. 

 

사실 사용될 법한 인덱스 는 쿼리를 튜닝하는데 크게 도움이 되지 않습니다. 

그래서 실행 계획을 확인할 때는 possible_keys 컬럼은 특별한 경우를 제외하고는 무시해도 된다고 합니다. 

주의 할 점은 possible_keys 를 보고 실제로 사용된 인덱스라고 판단하지 않는 것 입니다.