5. TYPE 칼럼
쿼리의 실행계획에서 TYPE 이후의 칼럼은 MYSQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다(ACCESS TYPE). 여기서 방식이라하면, 인덱스를 사용해 읽었는지, 테이블을 처음부터 끝까지 읽는 풀테이블 스캔으로 읽었는지 등을 의미한다.
TYPE에 올 수있는 값은 아래 12개가 있는데 성능이 빠른 순서대로 나열한다.
1. system
레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 한다. 이 접근 방법은 innoDB 엔진이 아닌 MyISAM이나 MEMORY 엔진을 사용하는 테이블에서만 사용된다.
2. const (튜닝 필요X)
테이블의 레코드 건수에 관계없이 쿼리가
프라이머리키나유니크키칼럼을 이용하는 WHERE 조건절을 가지며,반드시 1건을 반환하는 쿼리의 처리방식을 const라고한다. 프라이머리키나 유니크키의 일부만 사용하는 경우 const 타입의 접근 방법을 사용할 수 없다.3. eq_ref (튜닝 필요X)
여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 칼럼값을 두번째 테이블의
프라이머리키나 유니크키칼럼의 검색 조건에 사용할 때 eq_ref라고 한다.SELECT * FROM dept_emp de, employees e WHERE e.id = de.id AND de.iid = 'd005';위 조건에서 e테이블을 읽을 때 첫번째 테이블(de)의 칼럼값을 e의 프라이머리키 검색 조건으로 사용했기 때문에 eq_ref로 노출된다.
4. ref (튜닝 필요X)
eq_ref와 달리 조인의 순서와 관계없으며 프라이머리키나 유니크키 제약도 없다. 인덱스의 종류와 관계 없이
동등(Equal) 조건으로 검색할때 ref 접근 방법이 사용된다.5. fulltext (확인 필요)
fulltext 접근 방법은 MySQL 서버의 전문 검색 인덱스를 사용해 인덱스를 읽는 접근 방법을 의미한다.
전문 검색은MATCH (...) AGAINST (...)구문을 사용해서 실행해야 하는데, 이때 반드시 테이블에 전문 검색용 인덱스가 준비돼 있어야만 한다.6. ref_or_null (나쁘지 않음)
이 접근 방법은 ref 접근 방법과 같은데, NULL 비교가 추가된 형태다.
7. unique_subquery (나쁘지 않음)
WHERE 조건절에서 사용될 수 있는
ÌN(subquery)형태의 쿼리를 위한 방법이다. 의미 그대로 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용한다.8. index subquery (나쁘지 않음)
IN 연산자 특성상 괄호내 값 중 중복값이 먼저 제거돼야 한다. 이러한 중복을 인덱스를 이용하여 제거할 수 있을 때 index subquery 방법이 사용된다.
9. range (나쁘지 않음)
우리가 알고 있는
인덱스 레인지 스캔형태의 접근 방법이다. 얼마나 많은 레코드를 필요로 하느냐에 따라 차이가 있겠지만 range 접근 방법도 상당히 빠르다.10. index_merge (그다지 효율적이지 않음..)
2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다. index_merge 접근 방법에는 다음과 같은 특징이 있다.
- 여러 인덱스를 읽어야 하므로 일반적으로 range접근 보다 효율이 떨어진다.ㅣ - 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다. - index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에, 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.11. index (효율적이지 않음..)
index 접근 방법은 이름과 다르게 인덱스를 처음부터 끝까지 읽는
인덱스 풀 스캔(인덱스를 사용하는 의도가 아님)을 의미한다. index 접근 방법은 다음 조건 가운데첫번째 + 두번째나
첫번째 + 세번째조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.- range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우 - 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우 (데이터 파일을 읽지 않아도 되는 경우) - 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우 (별도의 정렬 작업을 피할 수 있는 경우)12. ALL (제일 느림)
우리가 알고 있는 풀 테이블 스캔을 의미하는 접근 방법이다. 가장 비효율적인 방법.
6. Key 칼럼
Key 칼럼에 표시되는 인덱스는 쿼리 수행에 사용된 인덱스를 의미한다.
7. Key_len 칼럼
Key_len 칼럼은 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 우리에게 알려준다. 다음 쿼리의 경우 두 개의 칼럼(dept_no, emp_no)으로 구성된 프라이머리 키를 가지는 테이블에서 dept_no만 사용되어 key_len은 16으로 표시된다 (4Bytes x 4글자)
추가로, 문자열의 경우 글자 당 4Bytes 정수형의 경우 고정 4Bytes를 사용한다.
또, NULL값이 저장될 수 있는(NULLABLE) 칼럼은 1Bytes를 더 사용한다.
SELECT * FROM dept_emp WHERE dept_no='d005';8. ref 칼럼
ref 칼럼의 경우 접근 방법이 ref인 경우 참조 조건(동등 비교 조건)으로 어떤 값이 제공됐는지; 보여준다. 상수값이면 const, 다른 테이블 칼럼값이면 테이블명과 칼럼명이 표시된다. 또한 값이 func으로 표시되는 경우는 값을 그대로 사용한 것이 아니라, 콜레이션 변환이나 연산을 거쳐서 참조된 것을 의미한다.
9. rows 칼럼
MySQL 옵티마이저는 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정한다. 이 때 rows 칼럼값은 실행 계획의 효율성 판단을 위해 얼마나 많은 레코드를 체크해야하는지 예측했던 레코드 건수를 보여준다.(정확하지는 않음)
10. filtered 칼럼
filtered 칼럼의 값은 필터링되어 버려지는 레코드의 비율이 아니라 필터링되고 남은 레코드의 비율을 의미한다. filtered 칼럼의 값이 중요한 점은 옵티마이저는 보통 최종적으로 일치하는 레코드 건수가 적은 테이블이 드라이빙 테이블로 선정되는데 이때, filtered 칼럼의 예상값을 기준으로 선정하기 때문에 이 값이 얼마나 중요하냐에 따라 조인의 성능이 달라지게 된다.
11. extra 칼럼
칼럼의 이름과 달리, 쿼리의 실행 계획에서 성능에 관련된 내용이 Extra 칼럼에 자주 표시된다. Extra 칼럼에 올수 있는 값들은 다음과 같다.
- const row not found
const 접근으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않는 경우
- Deleting all rows
테이블의 모든 레코드를 삭제하는 기능을 제공하는 스토리지 엔진 테이블인 경우 제공
- Distinct
테이블 조인시에 특정 칼럼값을 중복없이 유니크하게 가져오는 경우 (조인 필요없는 항목은 무시)
- FirstMatch
세미조인 최적화 중 FirstMatch 전략이 사용되면 해당 메시지를 출력한다.
** 세미조인이란? - 다른 테이블과 실제 조인을 수행하지는 않고, 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리. ex) IN(subquery)형태** FirstMatch란? - IN 내부의 서브쿼리를 조인으로 풀어서 실행하면서 일치하는 첫번째 레코드만 검색하는 최적화 방법.- Full scan on NULL key
해당 처리는
col1 IN (SELECT col2 FROM ...)같은 조건을 가진 쿼리에서 발생하는데 col1이 NULL일 경우 서브쿼리 테이블을 풀스캔해서 결과 레코드가 있는지 없는지를 확인해야한다.
(있으면 결과:NULL, 없으면 결과:False)
이러한 풀테이블 스캔 상황을 피하기 위해서는col1 IS NOT NULL과 같은 조건절을 포함하여 col1이 NULL이 아니다라는 조건을 추가해줄 수 있다.- Impossible HAVING
쿼리에 사용된 HAVING절을 만족하는 레코드가 없을 때 해당 키워드가 표시된다.
- Impossible WHERE
위와 같이 WHERE절을 만족하는 레코드가 없을 때 표시된다.
- LooseScan
세미조인 최적화 중에서 LooseScan 최적화 전략이 사용될 때 표시된다.
** LooseScan이란? - 세미조인시에 모든 레코드를 읽지 않고 조건에 사용된 인덱스를 유니크한 값만 읽어오는 방법- No matching min/max row
min(), max() 함수가 포함된 쿼리에서 조건절을 만족하는 레코드가 없을 경우 표시된다.
- no matching row in const table
조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없으면 표시된다.
- No matching rows after partition pruning
파티션된 테이블에 대한 UPDATE 또는 DELETE를 수행할 때 대상 파티션이 없을 경우 표시된다.
- No table used
FROM절이 없거나 FROM DUAL 형태의 쿼리가 사용될 때 출력된다.
- Not exists
아우터 조인을 이용해 안티조인(NOT IN, NOT EXISTS)을 수행하는 쿼리에서 표시된다.
- Plan isn't ready yet
특정 쿼리의 실행 계획을 수립하지 못한 상태에서
EXPLAIN FOR CONNECTION명령을 사용할때.- Range checked for each record
아래와 같은 쿼리에서는 e1 테이블이 레코드를 하나씩 읽을 때마다 쿼리의 비용 계산을 위한 기준값이 계속 바뀌어 어떤 방법으로 e2 테이블을 읽는 것이 좋을지 판단하기 힘들다.
SELECT * FROM employees e1, employees e1 WHERE e2.emp_no >= e1.emp_no위 쿼리의 경우 e1.emp_no 값이 작을때는 풀 테이블 스캔, e1.emp_no 값이 클 때는 인덱스 레인지 스캔 형태로 접근 수행하는 것이 최적이다. 이러한 내용에서 "레코드마다 인덱스 레인지 스캔을 체크한다"의 의미를 가지는 것이 "Range checked for each record" 값이다.
- Select tables optimized away
Min() 또는 Max()만 SELECT절에 사용되거나, GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 최적화가 적용된다면 표시된다.
(특정 인덱스를 1건만 읽으면 되는 경우)- Start / End Temporary
중복제거 (Duplicated Weed-out) 세미조인 최적화를 사용할 때 표시된다.
** Duplicated Weed-out - 세미조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꾸고 GROUP BY를 통해 중복을 제거함. - 이때 임시 테이블에 조인된 결과를 넣는 작업을 수행한다.위의 설명에서 조인을 수행할 때 Start Temporary, 결과를 임시 테이블로 넣을 때 End Temporary가 표시된다.
- Unique row not found
두 개의 테이블이 각각 유니크 칼럼으로 아우터 조인을 수행하는 쿼리에서 일치하는 레코드가 존재하지 않을 때 표시된다.
- Using filesort
ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못할 때는 MySQL 서버가 재정렬해야 한다. 이 때 표시된다.
Using filesort가 출력되는 쿼리는 많은 부하를 일으키므로 가능하면 쿼리를 튜닝하는 것이 좋다.- Using Index(커버링 인덱스)
데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 표시된다.
만약 인덱스에 포함되지 않는 칼럼이 SELECT문에 포함되어 있으면 데이터 파일을 읽어야해서 풀테이블 스캔을 고려할 수 있다.- Using index condition
인덱스 푸시 다운(Index condition pushdown) 최적화를 사용할 때 표시된다.
** 인덱스 푸시 다운 - 인덱스를 비교하는 작업은 InnoDB 스토리지 엔진이 수행하지만 조건을 비교하는 작업은 MySQL 엔진에서 수행하는데 인덱스를 범위 제한 조건으로 사용하지 못하는 조건은 스토리지 엔진으로 전달해주지 않는데 이러한 비효율을 없애기 위해 인덱스를 범위 제한 조건으로 사용하지 못하더라도 인덱스에 포함된 칼럼의 조건이 있다면 스토리지 엔진으로 전달해 주는 최적화이다.- Using index for group-by
GROUP BY 처리를 위해 MySQL 서버는 그루핑 기준 칼럼을 이용해 정렬하고 결과를 그루핑한다.
이러한 작업은 인덱스 없이 진행되면 매우 고부하 작업이 된다.
이 때 GROUP BY 처리를 위해 인덱스가 사용되는 방법을 "루스 인덱스 스캔"이라고 하는데 이런 경우에 해당 문구가 노출된다.- Using index for skip scan
옵티마이저가 인덱스 스킵 스캔 최적화를 사용하면 해당 문구가 표시된다.
** 인덱스 스킵 스캔 - (A, B)로 구성된 인덱스에서 MySQL8.0부터 B로만 조건절을 구성하더라도 인덱스를 사용할 수 있도록 최적화- Using join buffer
일반적으로 조인되는 칼럼은 인덱스를 생성한다. 실제로 인덱스는 양쪽 테이블 칼럼에 모두 필요한 것이 아니라 조인에서 뒤에 읽는 테이블(드리븐 테이블)의 칼럼에만 필요하다. (검색 위주만 사용되므로)
이때 드리븐 테이블에 적절한 인덱스가 없으면 블록 네스티드 루프 조인, 해시 조인을 사용하는데 이때 노출되는 문구이다.- Using MRR
일반적으로 아무리 많은 레코드를 읽는 과정이라 하더라도 스토리지 엔진은 MySQL엔진이 넘겨주는 키 값을 기준으로 레코드를 한건 한건 읽어서 반환하는 한계가 있다. 이를 보완하기 위해 MRR(Multi Range Read) 최적화를 도입했는데 MySQL 엔진은 여러개의 키 값을 스토리지 엔진으로 전달하고 스토리지 엔진은 해당 키 값들을 정렬해서 최소한의 페이지 접근으로 필요한 레코드를 읽는데 이때 노출되는 문구이다.
- Using where
MySQL 엔진에서 스토리지 엔진으로부터 받은 레코드에 별도의 가공을 해서 필터링 작업을 처리하는 경우 표시된다. 보통 인덱스에 포함되지 않은 조건문은 체크 조건으로 간주하여 필터링 작업을 통해 처리한다.
