MariaDB SQL INDEX - EXPLAIN (실행 계획), INDEX 처리 여부

MariaDB EXPLAIN (실행 계획)

Explain은 MariaDB에서 Query(쿼리)에 대한 예상되는 실행 결과를 보여줍니다.

Explain은 결과를 통해 INDEX가 사용되어 처리되었는지 확인할 수 있습니다.

 

DBeaver에서 EXPLAIN (실행 계획) 실행 결과

 

id : 실행 아이디

 

select_type : SELECT 타입

• SIMPLE : UNION이나 SUBQUERY가 없으면
• PRIMARY : UNION이나 SUBQUERY가 있고 중심이 되는 Table이면
• SUBQUERY : SELECT에 SUBQUERY가 있으면
• DERIVED : FROM에 SUBQUERY가 있으면
• UNION : UNION이 있으면
• UNION_RESULT : UNION의 결과가 임시 테이블에 저장되면

 

table : 처리되는 Table 명

 

type : 처리 타입 (조인 타입 - 1개의 Table도 조인처럼 처리됩니다.)

• all : Table를 처음부터 끝까지 검색 (Full Scan)
• const : 처리된 Row가 1개 일 때 (PRIMARY나 UNIQUE로 WHERE 절에 처리 – UNIQUE INDEX SCAN)
• ref : JOIN 할 때 PRIMARY KEY 또는 UNIQUE KEY가 아닌 KEY로 검색 (JOIN 순서와 관계가 없습니다.)
• eq_ref : Table들이 JOIN으로 처리될 때 (JOIN 순서와 관계가 있습니다.)
• range : 범위 내에서 INDEX를 사용하여 검색

 

possible_keys : 실행계획을 만들기 위해 선정된 INDEX 목록 (후보 INDEX)

 

key : Table에 접근할 때 사용한 INDEX 목록 (최종 선택된 INDEX)

 

rows : 처리하기 위해 읽을 Row 수 (예측 값)

 

ref : 참조 조건

• const : 상수 값으로 처리
• Column 명 : 참조되어 처리된 Column

 

Extra : Optimizer 동작 유형

• using where : WHERE 절로 처리 (INDEX로 검색되지 않고 WHERE 절 조건으로 처리) 
• using index : INDEX를 이용해 처리 (INDEX로 모두 처리 - 모든 데이터를 갖고 있는 INDEX를 Covering Index라고 합니다.)
• using index condition : INDEX를 이용해 처리 (INDEX 조건을 스토리지 엔진으로 전달하여 최대한 스토리지 엔진에서 처리되게 하고 엔진에 전달된다. ICP(Index Condition Pushdown)라고 합니다.)

 

 

MBR_ACCOUNT_TB에서 INDEX 생성 후 EXPLAIN으로 확인하기

 

WHERE 절에 있는 Column이 항상 Join Column이면 단일 INDEX으로 생성합니다.

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1';
--> type : ALL, key : NULL, rows : 2, extra : Using where
--> INDEX로 검색되지 않고 WHERE 절 조건으로 처리

-- 단일 INDEX 생성
CREATE INDEX IDX_MBR_ID ON MBR_ACCOUNT_TB (MBR_ID);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1';
--> type : ref, key : IDX_MBR_ID, rows : 1, extra : Using index condition
--> INDEX로 검색

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1' OR MBR_ID = 'testid2';
--> type : range, key : IDX_MBR_ID, rows : 2, extra : Using index condition
--> INDEX로 검색

 

WHERE 절에 있는 2개 이상의 Column이 항상 Join Column이면 복합 INDEX으로 생성합니다.

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1' AND MBR_NAME = '홍길동';
--> type : ref, key : IDX_MBR_ID, rows : 1, extra : Using index condition; Using where
--> MBR_ID는 INDEX로 검색되고 MBR_NAME은 WHERE 절 조건으로 처리

-- 단일 INDEX 생성
CREATE INDEX IDX_MBR_NAME ON MBR_ACCOUNT_TB (MBR_NAME);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1' AND MBR_NAME = '홍길동';
--> type : ref|filter, key : IDX_MBR_ID|IDX_MBR_NAME, rows : 1 (50%), extra : Using index condition; Using where; Using rowid filter
--> 두개의 INDEX로 검색되었지만 WHERE 절 조건으로 일부 처리 

-- INDEX 제거
ALTER TABLE MBR_ACCOUNT_TB DROP INDEX IDX_MBR_ID;
ALTER TABLE MBR_ACCOUNT_TB DROP INDEX IDX_MBR_NAME;


-- 복합 INDEX 생성
CREATE INDEX IDX_MBR_MULTI ON MBR_ACCOUNT_TB (MBR_ID, MBR_NAME);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1' AND MBR_NAME = '홍길동';
--> type : ref, key : IDX_MBR_MULTI, rows : 1, extra : Using index condition
--> INDEX로 검색

 

복합 INDEX의 모든 Column이 Join Column이면 Column 순서가 틀려도 INDEX가 적용됩니다.

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_NAME = '홍길동' AND MBR_ID = 'testid1';
--> type : ref, key : IDX_MBR_MULTI, rows : 1, extar : Using index condition
--> INDEX로 검색

 

복합 INDEX의 Column 순서와 Join Column의 순서가 틀리면 INDEX가 적용되지 않는다.
그러나 앞에서부터 Column 순서가 맞는 부분은 INDEX가 적용된다.

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1';
--> type : ref, key : IDX_MBR_MULTI, rows : 1, extra : Using index condition
--> INDEX로 검색

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_NAME = '홍길동';
--> type : ALL, key : NULL, rows : 2, extra : Using where
--> INDEX로 검색되지 않고 WHERE 절 조건으로 처리

-- INDEX 제거
ALTER TABLE MBR_ACCOUNT_TB DROP INDEX IDX_MBR_MULTI;

-- 복합 INDEX 생성
CREATE INDEX IDX_MBR_MULTI ON MBR_ACCOUNT_TB (MBR_ID, MBR_NICKNAME);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1' AND MBR_NAME = '홍길동';
--> type : ref, key : IDX_MBR_MULTI, rows : 1, extra : Using index condition; Using where
--> MBR_ID는 INDEX로 검색되고 MBR_NAME은 WHERE 절 조건으로 처리 

-- INDEX 제거
ALTER TABLE MBR_ACCOUNT_TB DROP INDEX IDX_MBR_MULTI;

-- 복합 INDEX 생성
CREATE INDEX IDX_MBR_MULTI ON MBR_ACCOUNT_TB (MBR_NICKNAME, MBR_ID);

EXPLAIN
SELECT * FROM MBR_ACCOUNT_TB WHERE MBR_ID = 'testid1' AND MBR_NAME = '홍길동';
--> type : ALL, key : NULL, rows : 2, extra : Using where
--> INDEX로 검색되지 않고 WHERE 절 조건으로 처리

-- INDEX 제거
ALTER TABLE MBR_ACCOUNT_TB DROP INDEX IDX_MBR_MULTI;