[Real MySQL] 6장 실행 계획

6장 실행 계획

6.1 개요

• 실행 계획전 알고 있어야 할 부분들 살펴보자

 

6.1.1 쿼리 실행 절차 (p.263)

• 쿼리가 실행되는 과정

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리 - SQL Parsing
2. SQL 의 파싱정보를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택 - 최적화 및 실행 계획 수립 단계(옵티마이저에서 처리)
3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴

 

6.1.2 옵티마이저의 종류 (p.265)

• 데이터 베이스에서 두뇌와 같은 역할을 담당
• 비용 기반 최적화(Cost-based optimizer, CBO) - 대부분의RDBMS 가 채택

• 쿼리를 처리하기 위한 여러가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획 별 비용을 산출
• 규칙 기반 최적화(Rule-based optimizer, RBO)

• 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립

 

6.1.3 통계 정보 (p.265)

• 비용 기반 최적화에서 “통계 정보” 는 매우 중요

• 통계정보가 부정확하면 엉뚱한 방향으로 쿼리를 실행해버리기 때문
• MySQL 에서 통계 정보는 사용자가 알아차리지 못하는 순간 순간 자동으로 변경되기 때문에 상당히 동적인 편 - but 레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많으므로 ANALYZE 명령을 이용해 통계 정보를 갱신해야 할 때도 있음
• 통계정보를 갱신하려면…
• ANALYZE TABLE tb_test;

• 파티션을 사용하지 않는 일반 테이블
• ALTER TABLE tb_test ANALYZE PARTITION p3;
• 파티션 사용하는 테이블에서 특정 파티션의 통계 정보 수집

 

6.2 실행 계획 분석

• EXPLAIN : 쿼리의 실행 계획 확인

 

6.2.1 id 칼럼

• 단위 SELECT 쿼리별로 부여되는 식별자 값
• SELECT 문장 하나에 여러 개의 테이블이 조인되는 경우 id 값이 증가하지 않고 같은 id 부여

 

EXPLAIN

SELECT e.emp_no, e.first_name, s.from_date, s.salary

FROM employees e, salaries s

WHERE e.emp_no = s.emp_no

LIMIT 10;

 

EXPLAIN

SELECT

((SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM departments)) AS total_count;

 

// id check

6.2.2 select_type 칼럼

• SIMPLE
• UNION이나 서브 쿼리를 사용하지 않은 단순한 SELECT 쿼리
• PRIMARY
• UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 가장 바깥쪽에 있는 단위 쿼리
• UNION
• UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리

EXPLAIN

SELECT * FROM (

(SELECT emp_no FROM employees e1 LIMIT 10)

UNION ALL

(SELECT emp_no FROM employees e2 LIMIT 10)

UNION ALL

(SELECT emp_no FROM employees e3 LIMIT 10)

)tb;

 

• DEPENDENT UNION
• 쿼리에 UNION이나 UNION ALL로 집합을 결합하는 쿼리
• DEPENDENT는 단위 쿼리가 외부의 영향에 의해 영향을 받는 것을 의미
• DEPENDENT 키워드가 포함된 서브 쿼리는 외부 쿼리에 의존적이므로 비효율적인 경우가 다수

EXPLAIN

SELECT

e.first_name,

(SELECT CONCAT('Salary change count : ', COUNT(*)) AS message

FROM salaries s WHERE s.emp_no-e.emp_no

UNION

SELECT CONCAT('Department change count : ', COUNT(*)) AS message

FROM dept_emp de WHERE de.emp_no = .e.emp_no

)AS message

FROM employees e

WHERE e.emp_no=10001;

 

• UNION RESULT
• UNION 결과를 담아두는 임시 테이블을 의미
• 단위 쿼리가 아니기 때문에 별도로 id 값은 부여X

 

EXPLAIN

SELECT emp_no FROM salaries WHERE salary > 100000

UNION ALL

SELECT emp_no FROM dept_emp WHERE from_date > '2001-01-01';

 

• SUBQUERY
• FROM절 이외에서 사용되는 서브 쿼리를 의미

 

EXPLAIN

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;

 

• DEPENDENT SUBQUERY
• 서브 쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우
• 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브 쿼리보다 처리 속도가 느릴 때가 다수

 

EXPLAIN

SELECT e.first_name,

(SELECT COUNT(*)

FROM dept_emp de, dept_manager dm

WHERE dm.dept_no=de.dept_no AND de.emp_no=e.emp_no)AS cnt

FROM employees e

WHERE e.emp_no=10001;

 

• DERIVED
• 서브 쿼리가 FROM 절에 사용된 경우
• 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미
• DERIVED인 경우 생성되는 임시 테이블을 파생 테이블이라고 하는데 이 테이블에는 인덱스가 없어 다른 테이블과 조인할 때 성능상 불리
• 가능하다면 조인으로 해결할 수 있게 변환

 

EXPLAIN

SELECT *

FROM

(SELECT de.emp_no FROM dept_emp de) tb,

employees e

WHERE e.emp_no=tb.emp_no;

 

• UNCACHEABLE SUBQUERY (p.276 그림 참고)
• SUBQUERY 중 캐시를 사용하지 못하는 SUBQUERY를 의미
• 캐시를 사용하지 못하는 경우
• 사용자 변수가 서브 쿼리에 사용
• NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브 쿼리 내에서 사용
• UUID()나 RAND()와 같이 결과값이 호출할 때마다 달라지는 함수 사용

 

EXPLAIN

SELECT *

FROM emplo0yees e

WHERE e.emp_no = (

SELECT @status FROM dept_emp de WHERE de.dept_no="d005");

 

사용자 변수 (@status) : 다른 커넥션에서는 공유 못하고 해당 커넥션에서만 사용 가능

 

• UNCACHEABLE UNION
• UNION 중 캐시를 사용하지 못하는 UNION을 의미   

6.2.3 table 칼럼 (p.278 그림 참고)

• 실행계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시
• 테이블의 별칭이 부여된 경우 별칭 표시
• 별도의 테이블을 사용하지 않은 경우엔 NULL 표시
• <>로 둘러싸인 이름이 명시되는 경우는 임시 테이블을 의미

6.2.4 type 칼럼

• 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미
• 성능 순으로 정리
• system
• 레코드가 1건 이하만 존재하는 테이블을 참조하는 쿼리
• const
• 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지며, 반드시 1건을 반환하는 쿼리
• UNIQUE INDEX SCAN 이라고도 함

 

EXPLAIN SELECT * FROM employees WHERE emp_no=10005;

 

EXPLAIN SELECT * FROM dept_emp WHERE dept_no='d005';

 

//type check

 

• eq_ref
• 여러 테이블이 조인되는 쿼리의 실행 계획에만 표시
• 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장(프라이머리 키나 NOT NULL인 유니크 키 사용)

 

EXPLAIN

SELECT * FROM dept_emp de, employees e

WHERE e.emp_no=de.emp_no AND de.dept_no='d005';

 

• ref
• 조인의 순서와 인덱스의 종류에 관계없이 동등 조건으로 검색

 

EXPLAIN

SELECT * FROM dept_emp WHERE dept_no='d005';

 

• fulltext
• 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미
• “MATCH … AGAINST ... “ 구문을 사용해서 실행
• 반드시 해당 테이블에 전문 검색용 인덱스가 준비돼 있어야만 가능

 

EXPLAIN

SELECT *

FROM employee_name

WHERE emp_no=10001

AND emp_no BETWEEN 10001 AND 10005

AND MATCH(first_name, list_name) AGAINST ('Facello' IN BOOLEAN MODE);

 

• ref_or_null (존재감 없는 접근방식)
• ref 접근 방식에 NULL 비교가 추가된 형태

 

EXPLAIN SELECT * FROM titles WHERE to_date='1985-03-01' OR to_date IS NULL;

 

• unique_subquery
• WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리
• 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때

 

EXPLAIN SELECT * FROM departments WHERE dept_no IN(SELECT dept_no FROM dept_emp WHERE emp_no=10001);

 

• index_subquery
• IN(subquery)에서 subquery가 중복된 값을 반환할 수 있지만 중복된 값을 인덱스를 이용해 제거할 수 있을 때

 

EXPLAIN

SELECT * FROM departments WHERE dept_no IN(

SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003');

 

• range
• 인덱스 레인지 스캔
• 인덱스를 하나의 값이 아니라 범위로 검색하는 경우 (<, >, IS NULL, BETWEEN, IN, LIKE 등)

 

EXPLAIN

SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003';

 

• index_merge
• 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식
• 특징
• 여러 인덱스를 읽어야 하므로 일반적으로 range보다 효율성 낮음
• AND와 OR 연산이 연결된 쿼리에서는 최적화 못할 때 다수
• 전문 검색 인덱스를 사용하는 경우 적용X
• 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업 필요

 

EXPLAIN

SELECT * FROM employees

WHERE emp_no BETWEEN 10001 AND 11000

OR first_name='smith';

 

• index
• 인덱스 풀 스캔

 

EXPLAIN

SELECT * FROM departments ORDER BY dept_name DESC LIMIT 10;

 

• ALL
• 풀 테이블 스캔
• 가장 비효율적 방법

 

6.2.5 possible_keys

• 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 인덱스

 

6.2.6 key

• possible_keys 중 최종 선택된 인덱스(최종 선택된 실행 계획에서 사용하는 인덱스)

 

6.2.7 key_len

• 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값

 

EXPLAIN

SELECT * FROM dept_emp WHERE dept_no='d005';

 

6.2.8 ref

• 접근 방법이 ref 방식이면 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 보여주는 값
• 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐을 때 func라고 표시되는데, 가능하다면 이런 변환을 하지 않도록 하는 편이 좋다

 

EXPLAIN

SELECT *

FROM employees e, dept_emp de

WHERE e.emp_no=de.emp_no;

 

6.2.9 rows

• 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수
• 예상 값

 

6.2.10 Extra

• Const row not found
• const 접근 방식으로 테이블을 읽었지만 레코드가 존재하지 않을 때 Extra 칼럼에 표시
• Distinct : 테이블에 있는 값을 중복없이 유니크하게 가져옴

 

EXPLAIN

SELECT DISTINCT d.dept_no

FROM departments d, dept_emp de WHERE de.dept_no=d.dept_no;

 

• Full Scan on NULL key :  col1  IN(SELECT col2 FROM…) 같은 쿼리에서 자주 발생
• MySQL이 쿼리를 실행하는 중 col1 이 NULL 을 만나면 예비책으로 풀 테이블 스캔을 사용할거라는 사실을 알려주는 키워드

 

EXPLAIN

SELECT d.dept_no, NULL IN(SELECT id.dept_name FROM departments id)

FROM departments d;

 

• Impossible HAVING(since MySQL 5.1) : having 절을 만족하는 레코드가 없을때 Extra 칼럼에 표시
• 이 메세지가 떴다면 쿼리를 제대로 작성하지 못한 경우가 대부분이니 쿼리 다시 점검

 

EXPLAIN

SELECT e.emp_no, COUNT(*) AS cnt

FROM employees e

WHERE e.emp_no=10001

GROUP BY e.`emp_no`HAVING e.emp_no IS NULL;

 

• no matching row in const table(since MySQL 5.1)
• 조인에 사용된 테이블에서 const 방식으로 접근할 때, 일치하는 레코드가 없을 경우 나타나는 메시지

 

EXPLAIN

SELECT *

FROM dept_emp de,

(SELECT emp_no FROM employees WHERE emp_no=1) tb1

WHERE tb1.emp_no=de.emp_no AND de.dept_no='d005';

 

• No tables used
• From 절이 없는 쿼리 문장이나 “FROM DUAL” 형태의 쿼리 실행 계획에서 출력 되는 메시지

 

EXPLAIN SELECT 1;

 

• Not exists
• A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 할때 not in 이나 not exist 를 사용 - anti-join 이라고 함
• NOT IN이나  NOT EXIST를 사용하지 않고, 아우터 조인을 이용해 안티-조인을 수행하는 쿼리에 표시

 

EXPLAIN

SELECT *

FROM dept_emp de

LEFT JOIN departments d ON de.dept_no=d.dept_no

WHERE d.dept_no IS NULL;

 

• Range checked for each record(index map :N)
• 매 레코드마다 인덱스 레인지 스캔을 체크(p.307 그림 6-6 참고)

 

EXPLAIN

SELECT *

FROM employees e1, employees e2

WHERE e2.emp_no >= e1.emp_no;

 

• Scanned N databases(since MySQL 5.1)
• 메타 정보를 검색할 경우 표시
• N 은 0, 1 or all 의 값을 가짐

• 0 : 특정 테이블 정보만 요청되어 DB 전체의 메타 정보를 읽지 않음
• 1: 특정 db 내의 모든 스키마 정보가 요청되어 해당 db 의 모든 스키마 정보를 읽음
• all : MySQL 서버 내의 모든 스키마 정보를 다 읽음

 

EXPLAIN

SELECT table_name

FROM information_schema.`TABLES`WHERE table_schema = 'employees' AND table_name = 'employees';

 

• Select tables optimized away
• MIN() 또는 MAX()만 SELECT 절에 사용되거나 또는 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을 때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 표시

 

EXPLAIN SELECT MAX(emp_no), MIN(emp_no) FROM employees;

 

• Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table
• INFORMATION_SCHEMA DB의 메타 정보를 조회하는 SELECT 쿼리의 실행 계획에서만 표시

• Skip_open_table : 메타 정보가 저장된 파일을 별도로 읽을 필요X
• Open_frm_only : 메타 정보가 저장된 파일(*.FRM)만 열어서 읽음
• Open_trigger_only : 트리거 정보가 저장된 파일(*.TRG)만 열어서 읽음
• Open_full_table : 최적화되지 못해 테이블 메타 정보 파일과 데이터 및 인덱스 파일까지 모두 읽음
• unique row not found
• 두 개의 테이블이 각각 유니크 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 표시

 

• Using filesort
• ORDER BY 처리가 인덱스를 사용하지 못 할 때만 표시
• 조회 된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행
• 많은 부하를 일으키므로 가능하다면 튜닝하거나 인덱스를 생성

 

EXPLAIN SELECT * FROM employees ORDER BY last_name DESC;

 

• Using index
• 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 표시
• 커버링 인덱스
• 인덱스 레인지 스캔을 사용하지만 쿼리의 성능이 만족스럽지 못하다면 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경해 큰 성능 향상 가능
• Using index for group-by
• GROUP BY 처리가 인덱스를 이용할 때 표시
• 루스 인덱스 스캔

• 타이트 인덱스 스캔 : AVG(), SUM(), COUNT(*)와 같이 모든 인덱스를 다 읽어야 할 때
• 루스 인덱스 스캔 : MIN(), MAX()와 같이 조회하는 값이 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리
• Using join buffer
• 읽은 레코드를 임시로 보관해두는 메모리 공간을 “조인 버퍼”라고 하며, 조인 버퍼가 사용되는 실행 계획에 표시

 

EXPLAIN

SELECT *

FROM dept_emp de, employees e

WHERE de.from_date > '2005-01-01' AND e.emp_no < 10904;

 

• Using sort_union(...), Using union(...), Using intersect(...)
• Using intersect(...) : 각각의 인덱스를 사용할 수 있는 조건이 AND로 연결
• Using union(...)  : 각각의 인덱스를 사용할 수 있는 조건이 OR로 연결
• Using sort_uion(...) : OR로 연결된 상대적으로 대량의 range조건들이 있어서 프라이머리 키만 먼저 읽어 정렬하고 병합한 후에야 비로서 레코드를 읽어서 반환할 수 있을 때
• Using temporary
• 임시 테이블을 사용할 때 표시
• 이때 사용된 임시 테이블이 메모리에 생성됐었는지 디스크에 생성됐었는지는 알 수 없다.
• 인덱스를 사용하지 못하는 GROUP BY 쿼리가 대표적인 형태

EXPLAIN

SELECT * FROM employees GROUP BY gender ORDER BY MIN(emp_no);

 

• Using where
• MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리하는 경우에 표시

 

EXPLAIN

SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 10100 AND gender='F';

 

• Using where with pushed condition
• NDB 클러스터 스토리지 엔진을 사용하는 테이블에서만 표시
• “Condition push down”이 적용됐음을 의미

 

6.2.11 EXPLAIN EXTENDED(Filtered 칼럼)

• EXPLAIN EXTENDED 명령
• MySQL 엔진에 의해 필터링되어 제거된 레코드는 제외하고 최종적으로 레코드가 얼마나 남았는 지의 비율

 

EXPLAIN EXTENDED

SELECT * FROM employees

WHERE emp_no BETWEEN 10001 AND 10100 AND gender='F';

 

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

• EXPLAIN EXTENDED 명령 이후, SHOW WARNING 명령
• 옵티마이저가 쿼리를 어떻게 해석했고, 어떻게 쿼리를 변환했으며, 어떤 특수한 처리가 수행됐는 지 등을 판단 가능

 

6.2.13 EXPLAIN PARTITIONS(Partitions 칼럼)

• EXPLAIN PARTITONS 명령
• 파티션 테이블의 생행 계획 정보를 확인 가능

6.3 MySQL의 주요 처리 방식

• 성능에 미치는 영향이 큰 실행 계획과 연관 있는 단위 작업 알아보자

 

6.3.1 풀 테이블 스캔 (p.330)

• 풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업. 아래와 같은 경우 풀 테이블 스캔 사용함
• 테이블의 레코드 건수가 너무 작을 때
• where 절이나 on 절에 인덱스 이용할 적절한 조건이 없는 경우
• 조건 일치 레코드 건수가 너무 많은 경우
• max_seeks_for_key 변수를 특정 값으로 설정하면, 옵티마이저는 그 만큼만 읽으면 된다고 판단. 작게 설정할 수록 인덱스를 더 사용하도록 유도

 

6.3.2 ORDER BY 처리(using filesort) (p.331)

• 정렬 방법
• 인덱스를 이용하는 방법

• 인덱스가 정렬되어 있어 매우 빠름
• INSERT, UPDATE, DELETE 작업 시 인덱스 추가 삭제 작업이 필요
• filesort를 이용하는 방법
• 인덱스를 생성 안해서 인덱스의 단점이 장점으로 바뀜
• 정렬해야할 레코드가 많지 않으면 메모리에서 filesort 가 처리되므로 빨라
• 인덱스를 사용할 수 없는 경우

• 정렬 기준이 너무 많을 때
• GROUP BY의 결과 또는 DISTINCT와 같은 처리의 결과를 정렬
• UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬
• 랜덤하게 결과를 가져와야 하는 경우
• Sort Buffer( 소트 버퍼)
• MySQL이 정렬을 수행하기 위한 별도의 메모리 공간
• 정렬이 필요한 경우에만 메모리가 할당되고 쿼리의 실행이 완료되면 즉시 시스템으로 반납
• 정렬 알고리즘
• 싱글 패스(Single Pass) 알고리즘
• 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 되는 칼럼 전부를 담아 정렬을 수행하는 방법
• 투 패스(Two Pass) 알고리즘
• 정렬대상 칼럼과 프라이머리 키 값만을 소트 버퍼에 담아 정렬을 수행하고, 다시 프라이머리 키로 테이블을 읽어서 SELECT 할 칼럼을 가져오는 알고리즘
• 장단점
• 싱글 패스는 테이블을 한 번만 읽어도 되는 반면 더 많은 소트 버퍼의 공간이 필요
• 정렬 대상 레코드의 크기나 건수가 작은 경우 싱글 패스, 레코드의 크기나 건수가 클 경우엔 투 패스가 효율적(기준 : max_length_for_sort_data 값  혹은 BLOB이나 TEXT 타입의 칼럼이 있을 땐 투 패스)
• 정렬의 처리 방식 ( 밑으로 갈수록 느려져)
• 인덱스를 사용한 정렬
• 반드시 ORDER BY 에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY 의 순서대로 생성된 인덱스가 있어야 함

 

SELECT * FROM employees e, salaries s

WHERE s.emp_no=e.emp_no

AND e.emp_no BETWEEN 100002 AND 100020;

 

• 드라이빙 테이블(첫번째 읽히는 테이블)만 정렬(조인 없는 경우 포함)
• 선 정렬 후 조인. 이 방법은 조인에서 첫번째 읽히는 데이블 칼럼 만으로 ORDER BY 절이 작성되어야 함

 

SELECT * FROM employees e, salaries s

WHERE s.emp_no=e.emp_no AND e.emp_no BETWEEN 100002 AND 100010

ORDER BY e.last_name;

• 임시 테이블을 이용한 정렬
• 2개 이상의 테이블을 조인해서 그 결과를 정렬할 때 사용(할 수도 있고 안 할 수도 있음)
• 드라이빙 테이블만 정렬 외의 패턴 쿼리에서는 항상 조인 결과를 임시 테이블에 저장하고 그 결과를 다시 정렬하는 과정을 거침

• 가장 느린 정렬 방법
• Using Temporary, Using filesort가 같이 표시

 

SELECT * FROM employees e, salaries s

WHERE s.emp_no=e.emp_no AND e.emp_no BETWEEN 100002 AND 100010

ORDER BY s.salary;

 

• 정렬 방식의 성능 비교
• 쿼리가 처리되는 방법
• 스트리밍(Streaming) 방식

• 서버 쪽에서 처리해야 할 데이터의 양에 관계 없이 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송해주는 방식
• LIMIT로 제한을 걸 경우, 빠르게 결과 전송 가능
• 버퍼링(Buffering) 방식
• WHERE 조건에 일치하는 모든 레코드를 가져온 후 정렬하거나 그룹핑 해서 차례로 전송하는 방식
• 인덱스를 사용한 정렬만 스트리밍 방식
• p.344 테이블 참고 (어느 테이블이 먼저 드라이빙 되어 조인 되는지도 중요하지만 어떤 방식으로 처리되는지는 더 큰 차이를 만들어)

 

  

6.3.3 GROUP BY 처리 (p.347)

• 일반적으로 group by 처리 결과는 임시 테이블이나 버퍼에 존재하는 값을 필터링 하는 역할 수행
• 인덱스 스캔을 사용하는 group by(tight index scan)
• 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그룹핑 할때 group by 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례로 읽으며 그룹핑 작업을 수행하고 그 결과로 조인을 처리
• 루스(loose) 인덱스 스캔을 이용하는 group by
• 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것

 

• 임시테이블을 사용하는 group by
• group by 의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할때 사용하는 방식

 

6.3.4 DISTINCT 처리 (p.351)

• 특정 칼럼의 유니크한 값만 조회할때 Select 쿼리에서 사용
• SELECT DISTINCT
• group by 와 같은 방식으로 처리
• 차이 : select distinct 는 정렬이 보장되지 않음
• 주의 : 여러 칼럼 중에서 일부만 유니크하게 조회하는 방법은 없음
• 집합 함수와 함께 사용된 DISTINCT
• COUNT(), MIN, MAX() 같은 집합 함수에서 DISTINCT 사용 가능
• 집합함수 내에 사용된 DISTINCT 는 그 집합 함수의 인자로 전달된 칼럼중 중복을 제거하고 남은 값만 가져옴

 

EXPLAIN

SELECT COUNT(DISTINCT s.salary)

FROM employees e, salaries s

WHERE e.emp_no=s.emp_no

AND e.emp_no BETWEEN 100001 AND 100100;

 

6.3.5 임시 테이블(Using temporary) (p.354)

• 임시 테이블이 필요한 쿼리
• ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
• ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
• DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
• UNION이나 UNION DISTINCT가 사용된 쿼리
• UNION ALL이 사용된 쿼리
• 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
• 임시 테이블이 디스크에 생성되는 경우(MyISAM)
• 내용 중 BLOB이나 TEXT와 같은 대용량 칼럼이 있는 경우
• 전체 크기나 UNION이나 UNION ALL에서 SELECT 되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
• 전체 크기가 tmp_table_size 또는 max_heap_table_size 시스템 설정 값보다 큰 경우
• 임시 테이블 관련 상태 변수
• SHOW SESSION STATUS LIKE ‘Created_tmp%’;
• Created_tmp_disk_tables : 디스크에 내부 임시 테이블이 만들어진 개수
• Created_tmp_tables : 내부 임시 테이블의 개수
• 임시 테이블 관련 주의사항
• 성능 상의 이슈가 될만한 부분은 디스크에 생성되는 경우
• 가능하다면 인덱스를 이용해 처리
• 임시 테이블이 생성되야만 한다면 가공 대상 레코드를 적게 만들 것

 

SELECT * FROM employees GROUP BY last_name ORDER BY first_name;

 

group by 와 order by  칼럼이 다르고 last_name 칼럼에 인덱스가 없어 임시 테이블과 정렬작업까지 수행해야하는 형태

 

6.3.6 테이블 조인 (p.358)

• 조인의 종류
• JOIN (INNER JOIN)
• 네스티드-루프 방식만 지원
• 두 개의 반복 루프 두 개의 테이블을 조건에 맞게 연결해주는 작업

FOR(record1 IN TABLE1){    FOR(record2 IN TABLE2){        IF(record1.join_column == record2.join_column){            join_record_found(record1.*, record2.*);        }ELSE{            join_record_notfound();        }    } }

 

• OUTER JOIN
• INNER 테이블이 조인의 결과에 전혀 영향을 미치지 않고, OUTER 테이블의 내용에 따라 조인의 결과가 결정

FOR(record1 IN TABLE1){                      // OUTER    FOR(record2 IN TABLE2){                  // INNER        IF(record1.join_column == record2.join_column){            join_record_found(record1.*, record2.*);        }ELSE{            join_record_found(record1.*, NULL);        }    } }

 

• CARTESIAN JOIN
• FULL JOIN 또는 CROSS JOIN
• 조인 조건없이 2개 테이블의 모든 레코드 조합을 결과로 가져오는 방식
• 레코드 건수가 많아지면 조인의 결과 건수가 기하급수적으로 늘어남
• NATURAL JOIN
• 두 테이블에 존재하는 같은 이름의 칼럼을 모두 조인 조건으로 사용
• 조인 조건을 명시하지 않아도 된다는 편리함이 있지만 위험한 방식
• Single-sweep multi join
• 네스티드-루프 조인
• 조인에 참여하는 테이블의 개수만큼 반복 루프가 중첩되는 것
• 조인 버퍼를 이용한 조인 (Using join buffer)
• 조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리
• 드리븐 테이블을 검색할 때 인덱스를 사용할 수 없는 쿼리는 상당히 느림
• 옵티마이저가 최대한 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획 수립
• 하지만 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 레코드를 메모리에 캐시
• 이 때 사용되는 메모리 캐시가 조인 버퍼
• 조인 관련 주의사항
• 조인 실행 결과의 정렬 순서
• 일반적으로 드라이빙 테이블로부터 레코드를 읽는 순서가 결과 순서
• 옵티마이저의 선택에 따라 결과는 보장X
• ORDER BY 절로 정렬을 보장할 것을 추천
• INNER JOIN과 OUTER JOIN의 선택
• INNER JOIN은 양쪽에 모두 레코드가 존재하는 경우에 레코드 반환
• OUTER JOIN은 OUTER 테이블에 존재하는 레코드 반환
• 성능상 차이는 거의 없어

 

6.4 실행 계획 분석 시 주의사항

6.4.1 Select_type 칼럼의 주의 대상 (p.373)

• DERIVED
• 서브 쿼리로부터 발생한 임시 테이블 의미
• 데이터의 크기가 커서 임시 테이블을 디스크에 저장하면 성능이 떨어진다.
• UNCACHEABLE SUBQUERY
• 사용자 변수를 제거하거나 다른 함수로 대체해서 사용 가능할 지 검토
• DEPENDENT SUBQUERY
• FROM 절 이외의 부분에서 사용하는 서브 쿼리가 외부 쿼리에서 값을 받아 실행
• 서브 쿼리가 불필요하게 외부 쿼리의 값을 전달받고 있는 지 검토 후, 외부 쿼리와의 의존도를 제거하는 것 추천

 

6.4.2 Type 칼럼의 주의 대상 (p.374)

• ALL, index
• index는 인덱스 풀 스캔, ALL은 풀 테이블 스캔
• 새로운 인덱스를 추가하거나 쿼리의 요건을 변경할 것 추천

 

6.4.3 Key 칼럼의 주의 대상 (p.374)

• 아무 값도 표시되지 않는다면, 쿼리가 인덱스를 사용할 수 있게 인덱스를 추가하거나 WHERE 조건을 변경

 

6.4.4 Rows 칼럼의 주의 대상 (p.374)

• 쿼리가 실제 가져오는 레코드 수보다 훨씬 더 큰 값이 표시되는 경우 검토가 필요

 

6.4.5 Extra 칼럼의 주의 대상 (p.374)

• 쿼리가 요건을 제대로 반영하고 있는지 확인해야 하는 경우 (레코드가 없다는 의미로 버그의 가능성 검토)
• Full scan on NULL key
• Impossible HAVING
• Impossible WHERE
• Impossible WHERE noticed after reading const tables
• No matching min/max row
• No matching row in const table
• Unique row not found
• 쿼리 실행 계획이 좋지 않은 경우 (최적화 검토)
• Range checked for each record
• Using filesort
• Using join buffer
• Using temporary
• Using where
• 쿼리의 실행 계획이 좋은 경우
• Distinct
• Using index
• Using index for group-by