mysql 실행계획 2

1번 포스팅에 이어서 작성한다.

5. possible_keys

옵티마이저는 어떤 인덱스를 사용할지 후보들을 정해놓고 그중에 하나를 사용하는데 possible_keys는 후보에서 떨어진 인덱스 키들을 보여준다. 강제로 인덱스를 태우고자할때는 유용할 수도 있을것 같으나 일단 수립된 계획을 확인할때는 상대적으로 비중이 떨어지는 항목이다.

6. key

possible_keys가 탈락한 키들의 집합이라면 key는 뽑힌 인덱스키를 보여준다. index_merge 처럼 다중 인덱스를 활용한 경우가 아니라면 값은 항상 1개를 표현한다.

7. key_len

사용된 키의 컬럼크기를 나타낸다. character set에 따라 같은 컬럼이라도 다르게 표현될 수 있으며, null 유무에 따라서도 크기가 달라질 수 있다.

8. ref

해당 테이블을 어떤 것으로 참조했는지를 표현한다. const라면 상수를 이용해 참조했음을 나타내고 join 같은 경우엔 컬럼 명을 표현한다. 크게 의미있는 항목은 아닌데 해당 항목이 func로 표현될때는 튜닝대상으로 의심해보는것이 좋다. function의 줄임말로 로우마다 무언가 연산을하는 다른값이 참조된다는 의미이기 때문이다.

9. rows

조회 쿼리를 실행하기위해 몇건의 데이터를 확인해야하는지를 표현한다. 해당 값이 전체 데이터 값과 같다면 풀 테이블 스캔을 의미한다. 값이 작을수록 좋다.

실제 데이터 수가 아니라 엔진 내에 저장되어있는 통계정보를 기반으로하기때문에 오차가 발생할 수 있다.

10. Extra

영어 뜻만 보면 추가적인 정보라는 뜻인데 추가적인것 치고는 꽤나 치명적인것들을 많이 보여준다. 때문에 해당 컬럼값들의 의미를 아는것이 좋을듯 하다.

-const row not found

테이블에 데이터가 존재하지않을때 표현된다.

-Distinct

distinct 를 사용해서 중복을 제거할때 나타낸다. 인덱스가 존재하는 컬럼을 이용해서 실행계획을 봤을땐 다른 내용이 표현되는걸로 봐서, 중복을 제거하고자하는 컬럼에  인덱스가 존재하지 않아야 나타나는것 같다.

-Full scan on NULL key

column IN (subquery) 형태의 쿼리는 상당히 자주 쓰이는 문법인데 이때 column에 not null 제약이 걸려있다면 상관없지만 그렇지 않다면 null이 들어갈수도있다. mysql, mariadb 같은경우 null이 포함된 연산은 항상 null을 반환하므로 null IN (subquery) 는 null이 되게된다. 이때 null로 인해 인덱스를 사용하지못하고 테이블 풀스캔을 수행할 수 있는데 Full scan on NULL key는 '풀스캔을 수행했다' 가 아니라 '수행할 수 도 있다' 라는 표현이다. 실제 column에 null이 존재하지않으면 실행계획에는 표현되고 풀스캔을 수행하지는 않는다.

-Impossible HAVING

HAVING 절에 사용된 조건을 사용할 일이 없다는 표현이다. 예를 들어 not null 제약조건이 걸린 컬럼에 is null 같은 비교를 하게되면 표현한다.

-Impossible WHERE

WHERE 절에 사용된 조건을 사용할 일이 없다는 표현이다. 예를 들어 not null 제약조건이 걸린 컬럼에 is null 같은 비교를 하게되면 표현한다.

-Impossible WHERE noticed after reading const tables

위 Impossible WHERE 같은 경우 테이블의 제약조건을 확인하면 null 비교를 할 필요없다는 것을 알 수 있다. 하지만 데이터가 없는 경우, 즉 where column1 = 0; 이라는 비교에서 column1에 0이라는 값이 없는 경우는 데이터를 확인하지 않으면 알 수 없다. 실행계획을 수립할때 쿼리의 일부를 실행해보는경우도 있다는것을 알 수 있다.

-No tables used

테이블 없는 쿼리 실행시 표현

SELECT 1;

-No matching min/max row

where 절에 만족하는 데이터가 없을 경우 위에서 살펴본 Impossible 이 주로 출력된다. 하지만 select 절에 min(), max() 함수가 존재하는 경우 해당 내용이 출력된다.

-Not exists

A와 B 테이블을 outer join을 이용해 A에는 존재하지만 B테이블에는 존재하지않는 값을 조회하는 경우가 있다.

SELECT * FROM A a LEFT OUTER JOIN B b ON a.column = b.column WHERE b.column is null;

이런 경우 옵티마이저가 최적화를 진행하는데 그 최적화 이름이 Not exists이다. 실제 쿼리에서 사용되는 NOT EXISTS와는 동명이인(?) 같은 존재니 헷갈리지 말자.

-unique row not found

A와 B 테이블을 각각 유니크 컬럼을 이용해 outer join을 수행할때 한쪽 테이블에 값이 없는 경우에 표현된다.

즉 Not exists 예제 쿼리에서 각 테이블의 column 컬럼이 모두 유니크럴럼일경우 발생한다.

-Using filesort

데이터들을 정렬하는것에 있어 인덱스를 사용하지 못할때 표현된다. 인덱스를 사용하지 못한다는 것은 실제 데이터들을 핸들링해서 정렬을 한다는 것이기때문에 성능에 악영향을 끼치게된다. 튜닝시 제거대상이 되어야 한다.

-Using Index

Covering Index 라고도 표현하며 조회쿼리를 실행하는 것에 있어 인덱스만으로 모든 조회가 가능할때 표현된다.

SELECT col1, col2, col3 FROM table WHERE col1 = 1000;

col1이 primary key 라고 가정할때 인덱스가 자동적으로 걸려있으니 로우를 찾아가는것에는 인덱스만으로 가능하다. 하지만 이후에 col2, col3 컬럼 값을 가져오기위해서는 실제 데이터에 대한 조회가 이루어져야 한다. 이때 만약 col2, col3에도 인덱스가 걸려있어 모든 컬럼값을 인덱스만으로 처리가능할때 Using Index가 표현된다. 즉 매우 좋은 표현이다.

-Using index for group-by

group by를 사용한 쿼리의 경우 인덱스를 스캔하는 방법은 2가지가 있다. 인덱스 스캔과 루스 인덱스 스캔이 존재하는데 인덱스 스캔의 경우 count(), sum() 등 인덱스 유무에 상관없이 모든 데이터 값을 읽어야하는 경우에 사용하게 된다. 이외에 모든 데이터를 확인할 필요 없이 인덱스만으로 group by를 수행할 수 있을때 인덱스 루스 스캔을 사용하게되는데 이때 표현되는 값이 Using index for group-by 이다.

다만 보통 1개의 조회쿼리에서 index-merge 가 아닌이상 1개의 인덱스만을 사용하게되는데, where 절 등 복잡한 조건으로인해 group by 컬럼과는 다른 인덱스가 사용되거나 인덱스가 사용되지 못하는 경우엔 표현되지 않는다. 생각보다 까다로운 조건을 만족시켜야 표현되며, 일단 보이면 좋다고 생각하면 될 듯 하다.

-Using join buffer

조인시 적절한 인덱스가 존재하지않을땐 테이블 풀스캔이 발생하게되는데 이때 나름 옵티마이저가 최적화하는 경우 표현된다.

-Using intersect

index_merge로 조회하게될경우 2개 이상의 인덱스를 사용하게되는데 이때 AND 조건으로 연결된 경우 결과를 교집합으로 처리했음을 표현한다.

-Using union

index_merge로 조회하게될경우 2개 이상의 인덱스를 사용하게되는데 이때 OR 조건으로 연결된 경우 결과를 합집합으로 처리했음을 표현한다.

-Using temporary

쿼리를 처리함에 있어서 임시테이블을 사용했을때 표현된다. select_type에 derived도 임시테이블을 의미하는것이었는데 Using temporary가 없다고해서 임시테이블을 사용하지 않았다는 뜻은 아니다.

-Using where

MySql은 스토리지엔진과 DB엔진 두개의 레이어로 구성되어있다. 스토리지 엔진이 1차 조회를 해오면 DB엔진이 2차 필터링을 하게되는데 이때 2차 필터링이 실행되게되면 Using where가 표현된다. 당연하게도 스토리지엔진에서 가져온걸 필터링할필요없는게 가장 이상적이긴하다.

-FirtMatch()

IN (subquery) 같은경우 EXISTS (subquery) 로 최적화가 가능하다. 옵티마이저가 내부적으로 쿼리를 최적화했을때 표현된다.

-LooseScan

IN (subquery) 에서 subquery에 중복된 값이 나타날때 중복값을 제거하는 최적화를 진행했을때 표현된다.

-filtered

EXPLAIN EXTENDED 로 실행계획을 조회했을때 나타나는 컬럼이다. 스토리지 엔진에서 가져온 결과를 DB 엔진에서 한번 더 필터링을 거친다고 했었는데 그때 최종적으로 얼마나 필터링됐는지를 %로 보여준다. 필터링으로 제외된 값들이 아니라 살아남은 로우의 %이다.

즉 100이 표현됐다면 DB 엔진에서 아무것도 필터링되지 않은것이다.