[MYSQL] 실행 계획 수행 - rows, filtered, extra

기본 실행 계획 항목 분석

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

 

rows

SQL문을 수행하고자 접근하는 데이터의 모든 행(row)수를 나타내는 예측 항목

수시로 변동되는 MYSQL의 통계정보를 참고하여 산출하는 값이므로 수치가 정확하지 않음

최종 출력될 행 수 가 아니라는 점에 유의해야 함

SQL문의 최종 결과 건수와 비교해 rows 수가 크게 차이 날 때는 SQL 튜닝 대상

---

filtered

SQL문을 통해 DB 엔진으로 가져온 데이터 대상으로 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는지를 의미하는 항목

DB 엔진으로 100건의 데이터를 가져왔다고 가정

WHERE 절의 사원번호 BETWEEN 1 AND 10 조건으로 100건의 데이터가 10건으로 필터링 됨 → filtered = 10 (%)

---

extra

SQL문을 어떻게 수행할 것인지에 관한 추가 정보를 보여주는 항목

부가적인 정보들은 세미콜론(;)으로 구분하여 여러 가지 정보를 나열 가능 (30여 가지 항목)

 

| Distinct |

중복이 제거되어 유일한 값을 찾을 때 출력되는 정보

중복 제거가 포함되는 distinct 키워드나 union 구문이 포함된 경우 출력

 

| Using where |

실행 계획에서 자주 볼 수 있는 extra 정보, WHERE 절의 필터 조건을 사용해 MYSQL 엔진으로 가져온 데이터를 추출할 것이라는 의미

 

| Using temporary |

데이터 중간 결과를 저장하고자 임시 테이블을 생성하겠다는 의미

보통 DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우 Using temporary 정보가 출력

임시 테이블을 메모리에 생성하거나 메모리 영역을 초과하여 디스크에 임시 테이블을 생성하면 Using temporary는 성능 저하의 원인이 될 수 있음 → SQL 튜닝 대상

 

| Using index |

물리적인 데이터 파일을 읽지 않고 인덱스만 읽어서 SQL문의 요청사항을 처리할 수 있는 경우

커버링 인덱스(covering index) 방식, 인덱스로 구성된 열만 SQL문에서 사용할 경우 이방식을 활용

물리적으로도 테이블보다 인덱스가 작고 정렬되어 있으므로 적은 양의 데이터에 접근할 때 성능 측면에서 효율적

 

직급 테이블의 기본 키는 사원번호, 직급명, 시작일자 순서로 구성

WHERE 절에서는 사원번호를, SELECT절에서는 직급명을 조회하므로 다른 열은 필요하지 않음

기본 키만 활용해서 원하는 정보를 모두 가져올 수 있는 커버링 인덱스 방식으로 데이터에 접근

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

 

결과

 

| Using filesort |

정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미

이미 정렬된 인덱스를 사용하면 추가적인 정렬 작업이 필요 없지만, 인덱스를 사용하지 못할 때는 정렬을 위해 메모리 영역에 데이터를 올림 

Using filesort → 추가적인 정렬 작업, 인덱스를 활용하도록 SQL 튜닝 검토 대상

 

| Using join buffer |

조인을 수행하기 위해 중간 데이터 결과를 저장하는 조인 버퍼를 사용한다는 의미

드라이빙 테이블의 데이터에 먼저 접근한 결과를 조인 버퍼에 담고 난 뒤, 조인 버퍼와 드리븐 테이블 간에 서로 일치하는 조인 키값을 찾는 과정을 수행 (조인 버퍼 활용)

 

| Using union / Using intersect / Using sort_union |

실행 계획의 type 항목에서 두 개 이상의 인덱스가 병합되어 데이터에 접근하는 경우 → index_merge 유형

인덱스가 병합되어 실행되는 SQL문의 extra 항목에는 인덱스를 어떻게 병합했는지 관한 상세 정보가 출력

 

Using union : 인덱스들을 합집합처럼 모두 결합하여 데이터에 접근한다는 뜻, 보통 SQL문이 OR 구문으로 작성된 경우

Using intersect : 인덱스들을 교집합처럼 추출하는 방식, SQL문이 AND 구문으로 작성된 경우

Using sort_union : Using union과 유사하지만 WHERE 절의 OR 구문이 동등 조건이 아닐 때

 

| Using index confition |

Mysql 엔진에서 인덱스로 생성된 열의 필터 조건에 따라 요청된 데이터만 필터링하는 Using where 방식과 달리, 필터 조건으로 스토리지 엔진으로 전달하여 필터링 작업에 대한 MYSQL 엔진의 부하를 줄이는 방식

스토리지 엔진의 데이터 결과를 MYSQL엔진으로 전송하는데 데이터양을 줄여 성능 효율을 높일 수 있는 옵티마이저의 최적화 방식

 

| Using index confition(BKA) |

type 정보의 Using index confition 유형과 비슷하나, 데이터를 검색하기 위해 배치 키 액세스를 사용하는 방식

 

| Using index for group-by |

SQL문에 group by 구문이나 Distinct 구문이 포함될 때는 인덱스로 정렬 작업을 수행하여 최적화

Using index for group-by : 인덱스로 정렬 작업을 수행하는 인덱스 루스 스캔일 때 출력되는 부가 정보

 

| Not exists |

하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력되는 유형

왼쪽 외부 조인 또는 오른쪽 외부 조인에서 테이블에 존재하지 않는 데이터를 명시적으로 검색할 때 발생

SELECT * FROM t1
	LEFT JOIN t2 ON (...)
WHERE t2.not_null_column IS NULL;

t1과 t2 테이블의 조건에 일치하는 데이터가 없는 경우에는 그 값이 NULL이 될 수 있음

일치하는 행을 하나 찾아서 검색 중지 → Not exists

 

* 참고

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

- extra 정보에 관한 추가 항목