Skip to content

Latest commit

 

History

History
336 lines (276 loc) · 17.7 KB

6-2.md

File metadata and controls

336 lines (276 loc) · 17.7 KB
Raw

6-2장. 실행계획분석(Extra칼럼)

Extra

  • Extra라는 이름과 달리 실행 계획에서 성능과 중요한 내용이 표기된다.
  • 여러개의 문장이 표시될 수 있고, 일반적으로 2~3개가 표시된다.

const row not found (MySQL 5.1이상)

  • 실행 계획에서 const 접근 방식으로 테이블을 읽었지만 레코드가 1건도 존재하지 않을때 노출된다.

Distinct

EXPLAIN
SELECT DISTINCT d.dept_no
FROM departments d, dept_emp de WHERE de.dept_no=d.dept_no;

P.299 표, 그림 6-5 참고

  • [그림 6-5]는 Extra에 Distinct가 표시되는경우의 Flow를 보여준다.
  • 쿼리에 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 무시하고, 필요한 레코드만 읽었음을 표현한다.

Full scan on NULL key

  • col1 IN (SELECT col2 FROM ...) 조건에서 col1이 NULL이 될 수 있는경우 발생할 수 있다.
  • SQL표준에서 NULL을 알 수 없는 값으로 정의하고 있으며 연산의 규칙까지 정의한다.
  • 규칙 (NULL IN (SELECT col2 FROM ...)구문에 대한 규칙)
  • 서브 쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
  • 서브 쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE
  • col1이 NOT NULL이면 해당 문장이 뜨지 않는다.
  • 만약, NULL이라도 위에 정의된 비교규칙을 무시해도 된다면, 옵티마이저에 알릴 수 있다.
  • WHERE col1 IS NOT NULL AND col1 IN (SELECT col2 FROM ...)
  • Full scan on NULL key은 값이 NULL이면 테이블 풀 스캔을 사용할 것이라는 것을 알려주는 키워드다.

Impossible HAVING (MySQL 5.1이상)

  • 쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을때 표시된다.
EXPLAIN
SELECT e.emp_no, COUNT(1) AS cnt
FROM employees e
WHERE e.emp_no=10001
GROUP BY e.emp_no
HAVING e.emp_no IS NULL;
  • e.emp_no는 Primary키면서 NOT NULL이기 때문에 해당 속성이 실행 계획에 노출된다.
  • 예제에선 구조상 불가능한 조건이었지만 실제로는 데이터로인해 발생하는 경우도 있다.
  • 이 키워드가 표시된 경우 쿼리의 내용자체를 다시 점검하는 것이 좋다.

Impossible WHERE (MySQL 5.1이상)

  • Impossible HAVING과 비슷하며 Where 절이 항상 False일때 표시된다.
// emp_no = Primary
EXPLAIN
SELECT * FROM employees WHERE emp_no IS NULL;

Impossible WHERE noticed after reading const tables

  • 실제로 동작시켜야 Where 절이 항상 False임을 확인할 수 있을때 표시된다.
  • 실행 계획을 확인하기위해 실제로 쿼리를 동작시킴도 확인할 수 있다.
EXPLAIN
SELECT * FROM employees WHERE emp_no=0;

No matching min/max row (MySQL 5.1이상)

  • MIN, MAX같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 0건일때 표시된다.
  • MIN, MAX의 값은 NULL이 된다.
## 참고 ##
Extra 칼럼의 내용중 "No matching ..."이나 "Impossible Where..." 등의 메세지는 쿼리 자체가 오류인것처럼 오해하기 쉽다.
Extra 칼럼에 출력되는 내용은 쿼리의 실행 계획을 산출하기 위한 기초자료가 없음을 표현하는 것뿐이다.

-> 실제 쿼리에 오류가 발생하지는 않음

no matching row in const table (MySQL 5.1이상)

  • 조인을 사용하는 쿼리에서 const방식으로 접근하고, 일치하는 레코드가 없을때 표시된다.

No tables used

  • FROM 절이 없거나 FROM DUAL로 실행됐을떄 표시된다.
## 참고 ##
MySQL은 FROM 절 없이 쿼리가 동작하지만, 오라클에서는 FROM절이 항상 있어야한다.
MySQL에서는 오라클과의 호환을 위해 FROM DUAL구문을 제공한다.
옵티마이져에서는 FROM DUAL 구문에 대해 FROM 절이 없는것과 동일하게 처리한다.

Not exists

  • 아우터 조인을 사용해서 안티 조인을 했을때 발생한다.
  • Not exists키워드는 쿼리의 NOT EXISTS명령어와는 무관하다.
  • NOT EXISTS명령어로 변환해서 처리했음을 뜻하는게 아니라 옵티마이저에서 최적화를 했는데 그 최적화의 이름이 Not exists 라는 의미다.
## 참고 ##
개발시에 A테이블에는 있지만 B테이블에는 없는 경우를 조회해야 할때 아래 3가지의 방법중 한가지를 사용한다.
그리고 이런 형태의 조인을 안티 조인(Anti-Join)이라고 한다.

1. `NOT IN (subquery)`
2. `NOT EXISTS`
3. Outer Join Where col IS NULL

Range checked for each record(index map: N)

Range checked for each record

EXPLAIN
SELECT *
FROM employess e1, employees e2
WHERE e2.emp_no >= e1.emp_no;
  • 예제와 같이 e1을 읽고 e2를 읽을때 e1.emp_no의 값에 따라 효율적인 접근 방식이 달라지는 경우에 표시된다.
  • 이름 그대로 “매 레코드마다 접근 방식을 체크한다”는 의미다.
  • 인덱스 레인지 스캔 or 테이블 풀 스캔중 선택
  • Extra에 이 키워드가 표시될떄 접근 방식은 ALL로 표기된다.

P.307 그림 6-6 참고

index map: N

  • 키워드에서 index map: N은 인덱스를 사용할지 말지 판단하는 후보 인덱스의 순번을 나타낸다.
  • 인덱스의 순번이란 SHOE CREATE TABLE employees 명령으로 나타나는 인덱스의 순서이다.
  • N은 16진수로 이루어져 있고 이진수로 변환을 한후에 해석할 수 있다.

P.308 참고

Scanned N Databases (MySQL 5.1이상)

  • INFORMATION_SCHEMA를 조회할떄 표시된다.
  • INFORMATION_SCHEMA는 실제 테이블이 아니라 메모리의 메타데이터를 가져오는 것이다.
  • 키워드에 N은 몇개의 DB정보를 읽었는지 보여주는 것인데 0, 1 또는 all의 값을 가질 수 있다.
  • 0 - 특정 테이블의 정보만 요청되어 데이터베이스 전체의 메타 정보를 읽지 않음
  • 1 - 특정 데이터베이스내의 모든 스키마 정보가 요청되어 해당 데이터베이스의 모든 스키마 정보를 읽음
  • All - MySQL 서버 내의 모든 스키마 정보를 다 읽음

Select tables optimized away

  • MIN, MAX가 Select 절에 사용되거나 또는 Group By 절에 MIN, MAX를 조회하는 쿼리가, 적절한 인덱스를 선택할 수 없어 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용되면 표시된다.
EXPLAIN 
SELECT MAX(emp_no), MIN(emp_no) FROM employees; -> 최적화 실패

EXPLAIN
SELECT MAX(from_date), MIN(from_date) FROM salaries WHERE emp_no=10001; -> 최적화 성공

Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table (MySQL 5.1이상)

  • Scanned N Databaes와 마찬가지로 INFORMATION_SCHEMA 메타정보를 조회할때만 표시된다.
  • 4가지중 한가지가 표시되며 의미는 아래와 같다.
  • Skip_open_table - 테이블의 메타 정보가 저장된 파일을 별도로 읽을 필요가 없음
  • Open_frm_only - 테이블의 메타 정보가 저장된 파일(*.FRM)만 열어서 읽음
  • Open_trigger_only - 테이블의 트리거가 저장된 파일(*.TRG)만 열어서 읽음
  • Open_full_table - 최적화되지 못해서 테이블 메타 정보 파일(.FRM)과 데이터(.MYD) 및 인덱스 파일(*.MYI)까지 모두 읽음
## 참고 ##
데이터 파일(*.FRM)이나 인덱스 파일(*.MYI)관련 파일은 MyISAM에만 해당된다.

unique row not found (MySQL 5.1이상)

  • 두 개의 테이블이 각각 유니크 속성의 칼럼으로 아우터 조인을 수행했을때, 드리븐 테이블에 일치하는 레코드가 존재하지 않을 때 표시된다.
CREATE TABLE tb_test1 (fdpk INT, PRIMARY KEY(fdpk));
CREATE TABLE tb_test2 (fdpk INT, PRIMARY KEY(fdpk));

INSERT INTO tb_test1 VALUES (1, 2);
INSERT INTO tb_test2 VALUES (1);

EXPLAIN
SELECT t1.fdpk
FROM tb_test1 t1
LEFT JOIN tb_test2 t2 ON t2.fdpk=t1.fdpk
WHERE t1.fdpk=2;

Using filesort

  • ORDER BY를 처리하기 위해 적절한 인덱스를 사용하지 못할 때는 조회된 레코드들을 다시 한번 정렬한다.
  • 이때 Using filesort키워드가 표시되며, 조회된 레코드들을 대상으로 퀵소트정렬을 실행한다.
  • ORDER BY를 사용한 쿼리에서만 나타나는 키워드다.
  • Using filesort가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 설정해주는것이 좋다.

Using index(커버링 인덱스)

  • 테이블 데이터을 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을떄 표시된다.
  • 인덱스에서 가장 부하가 큰부분이 테이블 데이터를 읽는 것이다.
  • InnoDB의 경우 모든 테이블이 클러스터링 인덱스로 구성돼 있다.
  • 이때문에 보조인덱스에서는 실제 주소값이 아니라 Primary키를 가진다.
  • 인덱스, Primary키만 조회하는 경우까지 커버링 인덱스가 적용된다.
## 참고 ##
`Using index`와 `index`접근 방식은 성능상 전혀 반대되는 것이다.
`index`는 인덱스 풀 스캔을 의미하며, 인덱스 레인지 스캔보다 느린 방식이다.
`Using index`는 커버링 인덱스를 의미하며 커버링 인덱스가 사용되지 않는 쿼리보다 더 빠르게 처리한다는 것을 의미한다.

커버링 인데스는 실행 계획의 접근 방식(type 칼럼)과 관계없이 사용될 수 있다.

Using index for group-by

  • GROUP BY에서 인덱스를 이용할 때 표시된다.
  • GROUP BY는 기준칼럼을 정렬하고 그룹핑하는 고부하 작업이다.
  • GROUP BY에 인덱스를 이용하면 이미 정렬된 인덱스에 그룹핑 작업만 수행하면 된다.
  • GROUP BY의 처리를 위해 인덱스를 읽는 방법을 루즈 인덱스 스캔이라고 한다.

타이트 인덱스 스캔과 루즈 인덱스 스캔의 차이

  • 타이트 인덱스 스캔을 통한 GROUP BY처리
  • GROUP BY에서 인덱스를 사용할 수 있더라도 AVG, SUM 또는 COUNT와 같이 모든 인덱스를 다 읽어야 할떄는 루즈 인덱스 스캔을 사용할 수 없다.
  • 타이트 인덱스 스캔을 통해 GROUP BY를 처리하게 되면 Using index for group-by가 출력되지 않는다.
  • 루즈 인덱스 스캔을 통한 GROUP BY처리
  • 단일 칼럼 인덱스의 경우 그룹핑 칼럼 말고는 아무것도 조회하지 않는 쿼리에서 루즈 인덱스 스캔을 사용할 수 있다.
  • 다중 칼럼 인덱스의 경우 GROUP BY 절이 인덱스를 사용할 수 있어야 한다.

GROUP BY 인덱스 사용에서 WHERE 절에게 받는 영향

  • WHERE 조건절이 없는 경우
  • WHERE 조건절이 전혀 없는 쿼리는 GROUP BY를 사용하는 쿼리가 루즈 인덱스 스캔을 사용할 수 있는 조건만 갖추면 된다.
  • 조건을 갖추지 못하면 타이트 인덱스 스캔이나 별도의 정렬로 처리된다.
  • WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못하는 경우
  • 이 경우 GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건의 비교를 위해 데이터 레코드를 읽어야한다. 따라서 루즈 인덱스 스캔을 사용하지 못한다.
  • WHERE 절의 조건이 있으며, 검색을 위해 인덱스를 사용하는 경우
  • WHERE 절의 인덱스와 GROUP BY의 인덱스가 같은 경우에만 루즈 인덱스 스캔을 사용할 수 있다.
  • WHERE 절의 인덱스와 GROUP BY의 인덱스가 다른경우 옵티마이저는 주로 WHERE 절의 인덱스를 선택한다.
  • 범위 제한 조건이 아니더라도
  • 만일, WHERE 절을 통해 선택된 레코드의 수가 적을 경우 루즈 인덱스 스캔을 사용하지 않는다.
  • 옵티마이저가 손익분기점을 확인해서 판단해준다.

Using join buffer (MySQL 5.1이상)

Using sort_union, Using union, Using intersect

  • 실행 계획의 접근 방식(type 칼럼)이 index_merge인 경우 인덱스가 2개이상 사용될 수 있다. 이때 어떻게 병합했는지를 설명하기 위해 표시된다.
  • 병합방식
  • Using intersect
  • 각 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출하는 작업을 했다는 의미다.
  • Using union
  • 각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출하는 작업을 했다는 의미다.
  • Using sort_union
  • Using union과 같은 작업을 수행하지만 Using union으로 처리할 수 없는경우 사용한다.
  • OR로 연결된 조건이 상대적으로 대량의 레인지 조건일때
  • Using sort_union의 경우 Primary키만 먼저 읽어서 정렬하고 병합한후 레코드를 읽어서 반환할 수 있다.

Using temporary

  • 쿼리를 처리하는 중에 임시 테이블을 사용한경우 표시된다.
  • 임시 테이블이 생성된 곳이 메모리인지 디스크인지는 구분할 수 없다.

Using where

P.323 그림 6-11 참고

  • 스토리지 엔진에서 가져온 레코드를 MySQL엔진에서 다시 필터링을 수행한 경우에 표시된다.
  • 실행계획에서 가장 흔하게 표시되는 키워드
  • 스토리지 엔진에서 가져온 레코드를 그대로 반환한경우에는 표시하지 않는다.
EXPLAIN
SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 10100 AND gender='F';

// emp_no BETWEEN 10001 AND 10100 조건을 만족하는 레코드는 100건
// 두 조건을 모두 만족하는 레코드는 37건이라고 가정
  • 예제의 쿼리를 분석해보면 스토리지 엔지에서 100건의 레코드를 가져온후 MySQL엔진에서 필터링을 통해 63건의 레코드를 버림을 알 수 있다.
  • MySQL엔진과 스토리지엔진의 이원화로 인해 생기는 비효율
  • MySQL 5.1부터는 범위 제한 조건이 아니여도 스토리지 엔진으로 넘긴다. (Condition Push Down)
  • Using where은 가장 흔하게 보이는 만큼 가장 놓치기 쉬운데 MySQL 5.1부터는 EXPLAIN EXTENDED명령을 통해 쉽게 성능의 이슈를 확인할 수 있다.

Using where with pushed condition

  • Condition Push Down이 적용됐을떄 표시된다.
  • MyISAM과 InnoDB에서는 표시되지 않고 NDB에서만 표시되는 정보이다.

EXPLAIN EXTENDED (Filter 컬럼)

P.327 그림 6-13 참고

  • MySQL 5.1이상에서도 스토리지엔진에서 반환하는 값이 항상 유저가 보는 값은 아니다.
  • Using where참고
  • 조인등의 사유로 MySQL엔진에서 추가적인 필터링을 하는데, 이떄 필연적으로 버려지는 레코드가 발생한다.
  • MySQL 5.1.12 이하에서는 MySQL엔진의 필터링과정에서 얼마나 많은 레코드가 버려졌는지 알 수 없다.
  • 그 이상의 버전에서는 EXPLAIN EXTENDED명령어를 통해서 볼 수 있는 Filtered칼럼으로 확인할 수 있다.
  • Filtered칼럼은 비율로 표시되며 rows에서 필터를 거쳐 최종적으로 사용자에게 리턴된 비율을 뜻한다.
  • Filtered칼럼의 정보 또한 실제 값이 아니라 통계를 통해 유추한 값이다.

EXPLAIN EXTENDED (추가 옵티마이저 정보)

  • EXPLAIN EXTENDED명령에는 Filtered칼럼 노출 기능 외에 쿼리를 파싱하고 나온 파스트리를 재조합해 쿼리문장과 비슷한 순서로 나열하는 기능이 존재한다.
  • EXPLAIN EXTENDED입력 후 SHOW WARNINGS명령어 입력을 통해 확인할 수 있다.
  • SHOW WARNINGS명령어를 통해 노출되는 쿼리는 표준 SQL이 아니다.
  • 예제의 경우 알아보기 좋은 쿼리로 나왔지만, 실제로는 알아보기 힘든 경우도 많다.
  • 옵티마이저에서 쿼리를 변환했고, 어떤 특수한 처리가 수행됐는지 판단할 수 있기 떄문에 알아두면 좋은 기능이다.
EXPLAIN EXTENDED
SELECT e.first_name,
(SELECT COUNT(*) FROM dept_emp de, dept_manager dm WHERE dm.dept_no=de.dept_no) AS cnt
FROM employees e
WHERE e.emp_no=10001;

SHOW WARINIGS;
> SELECT 'Georgi' AS first_name, (
SELECT COUNT(0) 
FROM employees.dept_dmp de
JOIN employees.dept_manager dm
WHERE (employees.de.dept_no = employees.dm.dept_no)) AS cnt
FROM employees.employees e WHERE 1

EXPLAIN PARTITIONS (Partitions 칼럼)

쿼리가 파티션 테이블 중에서 어떤 파티션을 사용했는지 확인하는 명령어

CREATE TABLE tb_partition (
reg_date DATE DEFAULT NULL,
id INT DEFAULT NULL,
name VARCHAR(50) DEFAULT NULL,
) ENGINE=INNODB;
partition BY range(YEAR(reg_date)) (
partition p0 VALUES less then (2008) ENGINE=INNODB,
partition p1 VALUES less then (2009) ENGINE=INNODB,
partition p2 VALUES less then (2010) ENGINE=INNODB,
partition p3 VALUES less then (2011) ENGINE=INNODB,
);

EXPLAIN PARTITIONS 
SELECT * FROM tb_partition
WHERE reg_date BETWEEN '2010-01-01' AND '2010-12-30';

P.329 표 참고

  • 조건에 따라 옵티마이저에서 읽어야할 파티션에만 접근하도록 실행계획을 수립하는데, 이 과정을 파티션 프루닝(Partition Pruning)이라고 한다.
  • EXPLAIN PARTITIONS 명령어를 사용하면 실행계획에 partitions칼럼이 함꼐 노출된다.
  • partitions칼럼에 의도한 파티션만 있는지 확인을 통해 쿼리튜닝을 할 수 있다.
## 참고 ##
MySQL의 파티션 키가 NULL을 반환할 수 있는 함수를 사용할 때는 쿼리의 실행 계획에서 partitions 칼럼에 첫번째 파티션이 포함되기도 한다.
레인지 파티션을 사용하는 테이블에서는 NULL을 항상 첫번째 파티션에 저장되기 떄문이다.

하지만, partitions에 노출된다고 성능상에 문제가 발생하지 않으니 걱정하지 않아도 된다.

코멘트

  • Using index for group-by가 뜰때 Min/Max 혹은 group-by인덱스 필드 외의 필드를 SELECT했을때 결과 확인하기.
  • join buffer size 값의 디폴트 값이 1MB가 적정하다고 하는데 실제 디폴트값은 몇인지 확인해보자