MySql- Show Index , Explain필드 분석

 

이전에 Index의 종류와 설계, 주의할 점 등등 많이 알아봤다.

이제는 Index가 잘 만들었는지 MySQL 에서 확인하는 커맨드인 Show Index 

그리고 실행 계획을 알아 볼 수 있는 Explain 커맨드에 대해서 알아보자!

---

Show Index 

Show Index 는 특정 테이블에 이미 만들어진 인덱스를 확인할 수 있는 커맨드입니다 

 

SHOW INDEX FROM announce;

 

이런 식으로 확인하고 싶은 Table에 대해서 명령어를 수행하면 됩니다 

 

명령어를 실행 결과를 보면 무수히 많은 칼럼들이 결과 화면에 보이게 됩니다.

이 각각의 컬럼과 올 수 있는 값들 그리고 의미를 알아보겠습니다.

 

럼	의미	올 수 있는 값
Table	인덱스가 속한 테이블명	테이블명 문자열
Non_unique	인덱스로 걸린 컬럼의 값이 중복 가능 여부	0 = 유니크(중복 불가) , 1 = 일반 (중복 허용)
Key_name	인덱스 이름	PRIMARY , 직접 지정한 인덱스명
Seq_in_index	복합 인덱스에서 컬럼 순서	1,2,3.... (단일 인덱스는 항상 1)
Column_name	인덱스가 걸린 컬럼명	컬럼명 문자열
Collation	인덱스 정렬 방향	A = 오름차순 , D = 내림차순 , NULL = 정렬 없음
Cardinality	유니크한 값의 추정 개수	숫자 (높을수록 인덱스 효율 높음)
Sub_part	컬럼 일부만 인덱싱한 경우 앞에서 몇 글자	숫자 (예: 10) , 전체 컬럼이면 NULL
Packed	인덱스 키 압축 여부	NULL = 압축 안 함 , 압축 방식 문자열
Null	해당 컬럼에 NULL 허용 여부	YES = NULL 허용 , ""(빈값) = NULL 불가
Index_type	인덱스 자료구조 종류	BTREE, HASH, FULLTEXT, SPATIAL
Comment	인덱스 상태 관련 코멘트	보통 빈값, disabled 등
Index_comment	인덱스 생성 시 직접 단 코멘트	직접 작성한 문자열, 없으면 빈값
Visible	옵티마이저에게 인덱스 보임 여부	YES = 사용 가능, NO = 옵티마이저가 무시
Expression	함수 기반 인덱스인 경우 표현식	일반 인덱스는 NULL, 함수 인덱스는 표현식 문자열

 

중요한 것들

Cardinality:
높을수록 인덱스 효율이 좋습니다. 성별처럼 M/F 두 가지 값밖에 없는 칼럼은 Cardinality가 낮아서 MySQL이 인덱스를 무시하고 풀스캔을 선택할 수 있습니다.

 

Seq_in_index:
복합 인덱스에서 순서가 중요합니다. (announce_id, member_id) 인덱스라면 announce_id가 1, member_id가 2로 표시됩니다.

 

Visible:
NO로 설정하면 인덱스를 삭제하지 않고 옵티마이저가 무시하게 만들 수 있습니다. 인덱스 삭제 전 테스트할 때 유용합니다.

 

Sub_part:
 VARCHAR(255) 같은 긴 문자열 컬럼에 앞 10글자만 인덱싱 하면 10이 표시됩니다.

 

---

Explain 실행 계획

Explain 실행 계획을 코드에 달면 실제 이 쿼리가 옵티마이저에 의해 어떻게 실행될 계획을 가지고 있는지 볼 수 있다

이 결과 많은 컬럼이 있는데 여기서도 한번 정리해 보자!!

 

컬럼	의미	올 수 있는 값
id	쿼리 실행 순서	숫자 (서브쿼리,UNION 이 있으면 2, 3 .. 증가)
select_type	쿼리 종류	SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION
table	접근하는 테이블 명	테이블명
partitions	사용된 파티션	파티션명 , 없으면 NULL
type	접근 방식	const , eq_ref , ref , range , index, all
possible_keys	사용 가능한 후보 인덱스	인덱스명들 , 없으면 NULL
key	실제로 선택된 인덱스	인덱스명, 안 쓰면 NULL
key_len	인덱스에서 사용한 바이트 수	숫자 (복합 인덱스 에서 몇개 컬럼 썻는지 판단 가능)
ref	인덱스와 비교한 값의 종류	const (상수) , 컬럼명 (JOIN) , func
rows	탐색할 것으로 예상 되는 행 수	숫자
filtered	rows중 조건을 만족하는 비율(%)	0 ~ 100 (높을 수록 좋음)
extra	추가 처리 정보	아래 별도로 정리

 

Extra에 올 수 있는 값

값	의미	좋음/나쁨
Using index	커버링 인덱스, 테이블 안 읽음	매우 좋음
Using index condition	인덱스로 조건 필터링	좋음
Using where	인덱스 후 추가 필터링 발생	보통
Using filesort	정렬을 인덱스 없이 처리	나쁨
Using temporary	임시 테이블 생성 (GROUP BY 등)	나쁨
NULL	특이사항 없음	정상

 

 

Type 성능 순서 (중요)

const > eq_ref > ref > range > index > ALL

 

const 

SELECT * FROM announce WHERE announce_id = 1

 

PK나 유니크 인덱스로 정확히 1건을 찾을 때 나옵니다. 결과가 무조건 1건이라 MySQL이 아예 상수로 처리합니다. 가장 빠릅니다.

 

 

eq_ref

SELECT * FROM announce a
JOIN member m ON a.member_id = m.id  -- m.id가 PK

 

JOIN할 때 PK나 유니크 인덱스로 1:1 매칭되는 경우입니다. const와 비슷하지만 JOIN 상황에서 나옵니다.

 

 

ref

SELECT * FROM business WHERE business_code = 'ABC'

 

일반 인덱스(유니크 아닌)로 등호 조건 탐색할 때 나옵니다. 결과가 1건 이상일 수 있습니다. 좋은 상태입니다.

 

 

range

SELECT * FROM announce WHERE reqst_start_date >= '2024-01-01'
SELECT * FROM announce WHERE view_num BETWEEN 100 AND 500
SELECT * FROM member WHERE id IN (1, 2, 3)

 

인덱스에서 특정 범위만 탐색할 때 나옵니다. >=, <=, BETWEEN, IN, LIKE 'abc%' 조건에서 발생합니다. 실무에서 자주 보이는 정상적인 상태입니다

 

 

index

SELECT created_date FROM announce ORDER BY created_date DESC

인덱스 전체를 처음부터 끝까지 순회합니다. 테이블을 안 읽는다는 점에서 ALL보다는 낫지만 인덱스 전체를 다 뒤지기 때문에 데이터가 많으면 느립니다. ALL이랑 같이 경고 신호로 봐야 합니다.

 

 

ALL

SELECT * FROM announce WHERE title LIKE '%공고%'

 

테이블 풀스캔입니다. 인덱스를 전혀 사용하지 않고 모든 행을 다 읽습니다. 데이터가 적을 때는 빠를 수 있지만 데이터가 쌓이면 치명적입니다. 즉시 인덱스 추가를 검토해야 합니다.

 

확인 순서

1. type — 가장 먼저 봐야 함

const > eq_ref > ref > range > index > ALL

 

ALL 또는 index가 나오면 즉시 인덱스 추가를 검토해야 합니다. 데이터가 적을 때는 ALL이어도 빠르지만 데이터가 쌓이면 바로 느려집니다.

 

2. rows — 실제 성능 체감과 직결

예상 탐색 행 수라 숫자가 클수록 느립니다. 인덱스 최적화 전후를 비교할 때 rows가 얼마나 줄었는지 보면 개선 효과를 바로 확인할 수 있습니다.

 

3. key — NULL이면 인덱스 안 탄 것

possible_keys에는 있는데 key가 NULL이면 후보는 있었지만 옵티마이저가 인덱스를 포기하고 풀스캔을 선택한 것입니다. Cardinality가 너무 낮거나 데이터가 적을 때 발생합니다.

 

4. key_len — 복합 인덱스에서 몇 개 칼럼을 탔는지 확인

복합 인덱스 (announce_id, member_id)가 있을 때

key_len: 4  → announce_id 하나만 사용
key_len: 8  → announce_id + member_id 둘 다 사용

 

5. Extra — Using filesort / Using temporary는 위험 신호

값	언제 나오냐	해결법
Using filesort	ORDER BY 컬럼에 인덱스 없을 때	정렬 컬럼에 인덱스 추가
Using temporary	GROUP BY, DISTINCT 처리 시 임시 테이블 생성	인덱스 재설계
Using index	커버링 인덱스로 테이블 안 읽을 때	이게 나오면 최적 상태

 

 

6. filtered — rows랑 같이 봐야 함

rows: 1000, filtered: 10  → 실제로는 1000건 읽고 100건만 씀
rows: 100,  filtered: 100 → 100건 읽고 100건 전부 씀

 

filtered가 낮으면 인덱스가 조건을 충분히 걸러내지 못하고 있다는 뜻입니다.

 

---