MySQL 인덱스 활용 되는지 보는법

개요

MySQL에서 쿼리가 인덱스를 제대로 활용하고 있는지 확인하는 방법을 정리한다. Buffer Pool 히트율, Handler 지표, EXPLAIN 결과를 통해 현재 상태를 진단할 수 있다.

Buffer Pool 히트율 확인

InnoDB는 자주 사용하는 데이터를 메모리(Buffer Pool)에 캐싱한다. 히트율이 높을수록 디스크 I/O 없이 메모리에서 데이터를 읽는다는 의미다.

상태 확인

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

히트율 계산

SELECT 
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS buffer_pool_hit_ratio
FROM (
  SELECT 
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests
) t;

해석 기준

히트율	상태
99% 이상	정상
95% ~ 99%	모니터링 필요
95% 미만	buffer pool 증설 고려

 

95%가 낮은 이유는 DB가 초당 수천~수만 번 읽기 요청을 하기 때문이다. 초당 10,000번 요청 시 95% 히트율은 초당 500번 디스크 I/O가 발생한다는 의미다.

Handler 지표로 인덱스 사용 여부 확인

내 데이터베이스가 인덱스를 얼마나 잘 태우고있는지  Handler 지표에서 확인할 수 있다. value에 횟수가 나온다

상태 확인

SHOW GLOBAL STATUS LIKE 'Handler%';

주요 지표

지표	의미
Handler_read_rnd_next	테이블 순차 스캔 횟수
Handler_read_key	인덱스로 직접 조회한 횟수
Handler_read_next	인덱스 순서대로 다음 행 읽은 횟수

 

Handler_read_rnd_next 값이 높으면 full scan이 많다는 의미다.

Select 지표

SHOW GLOBAL STATUS LIKE 'Select%';

지표	의미
Select_scan	첫 번째 테이블 full scan 횟수
Select_full_join	조인 시 인덱스 없이 full scan한 횟수

 

Select_full_join이 높으면 조인에 인덱스가 없는 것이다.

EXPLAIN으로 쿼리 분석

개별 쿼리가 인덱스를 타는지 확인하려면 EXPLAIN을 사용한다.

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

주요 컬럼

컬럼	의미
type	접근 방식
key	실제 사용한 인덱스
rows	예상 조회 행 수
Extra	추가 정보

type 값 (성능 순)

system > const > eq_ref > ref > range > index > ALL

type	의미
const	PK/유니크로 1건 조회
eq_ref	조인에서 PK/유니크 매칭
ref	인덱스로 조건에 맞는 것만 조회
range	인덱스 범위 스캔
index	인덱스 전체 스캔
ALL	테이블 전체 스캔

 

PK와 유니크 인덱스는 값이 하나만 존재하는 것이 보장되므로, 찾으면 추가 탐색 없이 종료한다. 이것이 const 타입이 가장 빠른 이유다.

 

ref와 index의 차이는 다음과 같다. ref는 인덱스에서 조건에 맞는 것만 찾는다. index는 인덱스 전체를 스캔한다.

Extra 값

값	의미
Using index	커버링 인덱스 사용
Using where	WHERE 조건 필터링
Using filesort	추가 정렬 작업 발생
Using temporary	임시 테이블 사용

인덱스 설계 원칙

복합 인덱스 순서

등호(=) 조건 컬럼을 먼저, 범위 조건 컬럼을 나중에 배치한다.

-- 쿼리
WHERE status = 'active' AND created_at > '2024-01-01'

-- 인덱스
CREATE INDEX idx ON orders(status, created_at);

커버링 인덱스

SELECT 컬럼까지 인덱스에 포함하면 테이블 접근 없이 인덱스만으로 결과를 반환한다.

-- 쿼리
SELECT user_id, created_at FROM orders WHERE status = 'active';

-- 인덱스
CREATE INDEX idx ON orders(status, user_id, created_at);

인덱스가 안 타는 케이스

-- 컬럼 가공
WHERE YEAR(created_at) = 2024      -- 인덱스 안 탐
WHERE created_at >= '2024-01-01'   -- 인덱스 탐

-- LIKE 앞에 %
WHERE name LIKE '%kim'   -- 인덱스 안 탐
WHERE name LIKE 'kim%'   -- 인덱스 탐

-- 형변환
WHERE user_id = '123'    -- user_id가 int면 형변환 발생

체크리스트

EXPLAIN 결과에서 다음을 확인한다.

• type이 ALL 또는 index가 아닌가
• key가 NULL이 아닌가
• rows가 테이블 전체 행 수가 아닌가
• Extra에 Using filesort, Using temporary가 없는가

네 가지를 모두 통과하면 인덱스를 제대로 활용하고 있는 것이다.

참조

• InnoDB Buffer Pool - Buffer Pool의 구조와 LRU 알고리즘에 대한 설명
• Configuring InnoDB Buffer Pool Size - Buffer Pool 크기 설정 방법
• EXPLAIN Output Format - EXPLAIN 결과의 각 컬럼에 대한 설명
• Server Status Variables - Handler, Select 등 서버 상태 변수 목록