MySQL EXPLAIN 실행계획 마스터하기(feat. RealMySQL 8.0)

💯  MySQL EXPLAIN 실행계획 마스터하기(feat. RealMySQL 8.0)

실행 계획(EXPLAIN) 이란?

대부분의 DBMS는 많은 데이터를 안전하고, 빠르게 저장 및 관리하는 것이 주목적이다.

이러한 목적을 달성하기 위해 사용자의 쿼리를 옵티마이저가 최적으로 처리될 수 있도록 쿼리의 실행 계획을 수립할 수 있어야 한다.

하지만 옵티마이저가 항상 최적의 실행 계획을 만들어낼 수 있는 것은 아니므로 DBMS 서버는 이러한 문제점을 관리자나 사용자가 보완할 수 있도록 EXPLAIN 명령으로 옵티마이저가 수립한 실행 계획을 확인할 수 있다.

 

테스트 데이터베이스: https://github.com/datacharmer/test_db

실행 계획	의미
id	SELECT 쿼리 별 부여되는 식별자 값
select_type	각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼
table	접근하고 있는 테이블에 대한 표시
partitions	테이블에 파티셔닝이 되어 있는 경우 사용되는 필드
type	각 테이블의 레코드를 어떻게 읽었는지에 대한 접근 방식
possible_keys	“사용될 법했던 인덱스의 목록”이며 반드시 사용된 것은 아님
key	최종 선택된 실행 계획에서 사용되는 인덱스
key_len	선택된 인덱스의 길이를 의미
ref	접근 방법이 ref면 참조 조건(equal 비교 조건)으로 어떤 값이 제공됐는지 표시
rows	실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. (정확하지는 않음)
filtered	필터링되고 남은 레코드의 비율 (정확하지는 않음)
Extra	옵티마이저가 어떻게 동작하는지에 대해 알려주는 힌트 값

 

실행 계획 분석

EXPLAIN 명령으로 MySQL에서 실행 계획을 확인할 수 있는데 이는 쿼리 문장에서 사용된 테이블의 갯수만큼 출력한다.

 

🔗  1. id

• SELECT 쿼리 별 부여되는 식별자 값

 

 

🔗  2. select_type

• 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼

2.1 SIMPLE

• 단순 SELECT 쿼리
• 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 최대 하나만 존재

2.2 PRIMARY

• UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리
• SIMPLE와 마찬가지 최대 하나만 존재

2.3 UNION

• UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 쿼리들
• UNION의 첫 번째 단위 SELECT는 UNION되는 쿼리 결과들을 모아서 저장하는 임시 테이블(DERIVED)

💡 Quiz 1. 위 쿼리에서 DERIVED 타입과 UNION 타입은 각각 어떤 쿼리들일까?

-- 1. DERIVED

-- 2. UNION
-- 3. UNION

DERIVED & UNION

• <>로 둘러싸인 이름이 명시되는 경우 임시 테이블을 의미함
  • <derived2> → id 값이 2인 실행 계획으로부터 만들어진 파생(DERIVED) 테이블

2.4 DEPENDENT UNION

• DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것
• 아래 쿼리에서 옵티마이저는 IN 내부의 서브쿼리를 먼저 처리하지 않고, 외부의 employees 테이블을 먼저 읽은 후 서브쿼리를 실행
• 이때 employees 테이블의 칼럼값이 서브쿼리에 영향을 줌
• (내부 쿼리가 외부의 값을 참조해서 처리될 때)

2.5 UNION RESULT

• UNION RESULT는 UNION 결과를 담아두는 테이블
• <union2,3> : id 값이 2, 3 쿼리의 조회 결과를 UNION

2.4 쿼리와 동일

2.6 SUBQUERY

• select_type의 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리만을 의미함

💡 Quiz 2. 서브쿼리의 종류는 어떠한 것들이 있을까? (Hint: SELECT, FROM, WHERE 절)

• SELECT: 스칼라 서브쿼리
• FROM: 인라인 뷰
• WHERE: 중첩 서브쿼리

2.8 DERIVED

• DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것
• select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 함
• MySQL 5.6 버전부터는 옵티마이저 옵션에 따라 이전 버전과는 달리 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화됨

• MySQL 서버는 버전이 업그레이드되면서 조인 쿼리에 대한 최적화는 많이 성숙된 상태
• 파생 테이블(DERIVED)에 대한 최적화가 부족한 버전의 MySQL 서버를 사용 중일 경우(MySQL 5.x 버전), 가능하다면 DERIVED 형태의 실행 계획을 조인으로 해결할 수 있게 쿼리를 바꿔주는 것이 좋다.
  • 가장 좋은 방법은 실행 계획을 확인해보고 결정하는 것

💡 한줄 요약: 서브쿼리 대신 조인을 사용하자

쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 컬럼의 값이 DERIVED인 것이 있는지 확인하고, 서브쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리보다는 조인을 사용할 것을 강력히 권장한다. -Real MySQL 8.0 -

2.12 MATERIALIZED

• MySQL 5.6 버전부터 도입된 select_type으로 주로 FROM절, In(Subquery) 형태의 쿼리에서 포함되어 있는 서브쿼리의 최적화를 위해 사용
• MySQL 5.6 버전까지는 employees 테이블을 읽어서 employees 테이블의 레코드마다 salaries 테이블을 읽는 서브쿼리가 실행되는 형태로 처리됐다. (Table Full Scan)
• MySQL 5.7 버전부터 서브쿼리의 내용을 임시 테이블로 구체화(Materialization) 한 후 임시 테이블 사용
• MySQL 5.6 Switchable Optimizations(semijoin=on)

(위 최적화가 어느정도의 차이가 발생하는 지는 아래 *5.7의 예시 참고)

https://engineering.linecorp.com/ko/blog/mysql-workbench-visual-explain-index/

 

🔗  3. table

• 접근하고 있는 테이블에 대한 표시
• MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시된다.
• 테이블의 이름에 별칭이 부여된 경우에는 별칭 표시

💡 Quiz 3. 실행 계획을 통해 쿼리를 유추해보자.

1. 첫 번째 라인의 <derived2>를 통해 id = 2인 라인이 먼저 실행되고, 그 결과가 파생 테이블로 준비
2. 세 번째 라인(id = 2)을 보면 select_type = DERIVED, table = s 이므로 salaries(별칭: s) 테이블을 읽어서 파생 테이블 생성
   • SELECT ~ FROM salaries s
3. 첫 번째 라인과 두 번째 라인은 같은 id 값을 가지고 있는것으로 보아 2개 테이블이 조인된 쿼리(e, s 테이블)인 걸 알 수 있음
   • FROM employees e JOIN SELECT ~ FROM salaries s ON ~
4. <derived 2> 테이블이 e 테이블보다 먼저(위에) 표시됐기 때문에 <derived 2>가 드라이빙 테이블이 되고 e 테이블이 드리븐 테이블이 된다는 것을 알 수 있다.
   • 보통 옵티마이저는 테이블 조인 시 레코드가 적은 테이블을 먼저 읽음(드라이빙 테이블)

 

*위 실행 계획을 통해 아래 쿼리가 실행되는 것을 유추할 수 있다.

 

🔗  4. partitions

• 테이블에 파티셔닝이 되어 있는 경우 사용되는 필드
  • 쉽게 말하면 동일한 테이블에 데이터를 분할하여 저장하는 방식
• ~ MySQL 5.7 버전 : EXPLAIN PARTITION
• MySQL 8.0 버전부터는 EXPLAIN 명령으로 파티션 관련 실행 계획까지 확인 가능

• 옵티마이저는 쿼리의 hire_date 칼럼 조건을 보고, 쿼리에서 필요한 데이터는 p2015, p2020 파티션에만 있다는 것을 파악 → 해당 파티션만 스캔
• 파티션 프루닝 : 쿼리 수행을 위해 접근해야 할 테이블만 골라내는 과정
• type을 보면 ALL이라 풀 테이블 스캔으로 처리가 되는데, MySQL 포함 대부분 RDBMS에서 지원하는 파티션은 물리적으로 개별 테이블처럼 별도의 저장 공간을 가지기 때문
• 위 쿼리는 employees_partitions 테이블의 특정 파티션(p2015, p2020)만 풀 스캔을 실행

• 파티셔닝 → 수평 / 수직
  • https://gmlwjd9405.github.io/2018/09/24/db-partitioning.html

 

🔗  5. type

• 각 테이블의 레코드를 어떻게 읽었는지에 대한 접근 방식
  • 방식 → 인덱스, 풀 테이블 스캔, 인덱스 풀 스캔 등등
• 쿼리 튜닝 시 반드시 체크해야 할 중요한 정보
• type 칼럼에 표시될 수 있는 값들 .. (12개)
  • system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL
• ALL(테이블 풀 스캔)을 제외한 나머지는 모두 인덱스를 사용하는 접근 방식
• system → ALL 갈수록 성능이 느려짐
• MySQL 옵티마이저는 이러한 접근 방법과 비용을 함께 계산해서 최소의 비용이 필요한 접근 방법을 선택해 쿼리를 처리함

5.1 system

• 테이블의 레코드가 최대 1건 존재하는 테이블을 참조하는 형태의 접근 방법
• InnoDB 스토리지 엔진에서는 나타나지 않고, MyISAM/MEMORY 테이블에서만 사용되는 접근 방식
• 테이블에 레코드가 1건 이하인 경우에만 사용할 수 있기에 거의 보이지 않음

ENGINE = MyISAM

ENGINE = InnoDB

5.2 const

• 쿼리 결과가 반드시 1건을 반환하는 쿼리의 처리 방식
  • PK 및 Unique Key 컬럼을 이용하는 조건절

 

• 아래 처럼 다중 칼럼으로 구성된 PK에서 일부 칼럼만 조건으로 사용할 때는 const 접근 방식 불가능
• 실제 레코드가 1건만 저장되어 있더라도 MySQL 엔진이 테이블을 읽기 전에는 레코드가 1건이라는 것을 확신할 수 없기 때문
• PK의 일부만 조건으로 사용할 때는 type 칼럼에 ref로 표시

 

PK의 모든 칼럼을 동등 조건으로 WHERE 절에 명시하면 const 접근 방식 사용

5.3 eq_ref

• 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시
• 조인에서 처음 읽은 테이블의 칼럼값을, 다음 읽을 테이블의 PK나 유니크 키 칼럼의 검색 조건에 사용할 때 eq_ref 접근방식 사용
• 두 번째 이후에 읽는 테이블의 type 칼럼에 eq_ref가 표시
• 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용

5.4 ref

• 조인의 순서와 관계없이 동등(Equal) 조건으로 검색할 때 사용하는 접근 방식
• ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const, eq_ref 보다는 느리지만 동등한 조건으로만 비교되기에 매우 빠른 레코드 조회 방법 중 하나

 

💡 const, ref 정리

• const: 모든 컬럼에 대해 PK, 유니크 키 등 동등 조건으로 검색(반드시 1건의 레코드만 반환)
• ref: 동등 조건으로 검색(1건의 레코드만 반환된다는 보장이 없어도 됨)
• const, eq_ref, ref은 좋은 접근 방법으로 크게 신경쓰지 않고 넘어가도 무방

5.5 fulltext

• fulltext 접근 방법은 MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드 접근
• 전문 검색 인덱스는 전혀 다른 SQL 문법을 사용해야 한다
  • MATCH (…) AGAINST (…) 구문??

5.6 ref_or_null

• ref에서 NULL 비교만 추가된 형태
• 많이 활용되지는 않으나 사용된다면 나쁘지 않은 정도의 접근 방법 정도로 기억

5.7 unique_subquery

• WHERE 조건절에서 사용될 수 있는 IN(Subquery) 형태의 쿼리를 위한 접근 방법
• 이름 그대로 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 사용

💡 type이 unique_subquery이 아닌데요??

MySQL 8.0 버전에서는 위 쿼리처럼 WHERE 조건절에 사용된 IN(Subquery) 형태의 세미 조인을 최적화하기 위해 많은 기능이 도입되었고, 이로 인해 최적화된 실행 계획이 보이는 것 semijoin 최적화 옵션을 on/off 하면 실행 계획이 다르게 보임

***
MySQL 5.6 버전까지는 employees 테이블을 읽어서 employees 테이블의 레코드마다 salaries 테이블을 읽는 서브쿼리가 실행되는 형태로 처리됐다.

위 2.12 MATERIALIZED 에서 언급했었던 서브쿼리 최적화의 차이점을 semijoin=on/off를 통해 아래 사진을 통해 파악해보자.

semijoin=on/off 시 실행 계획 차이

• MySQL 5.6 버전까지는 employees 테이블을 읽어서 employees 테이블의 레코드마다 salaries 테이블을 읽는 서브쿼리가 실행되는 형태로 처리됐다. (Table Full Scan)
• MySQL 5.7 버전부터 서브쿼리의 내용을 임시 테이블로 구체화(Materialization) 한 후 임시 테이블 사용

5.8 index_subquery

• IN(subquery)에서 서브쿼리가 중복된 값을 반환할 수 있는데, 이때 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 index_subquery 접근 방법 사용
  • unqiue_subquery: 서브쿼리 반환 값에 중복 X
  • index_subquery: 서브쿼리 반환 값에 중복이 있을 수 있으나 index를 이용해 중복 제거

5.9 range

• 인덱스 레인지 스캔 형태의 접근 방법
• range는 인덱스를 하나의 값이 아닌 범위로 검색하는 경우
• 주로 “<, >, IS NULL, BETWEEN, IN, LIKE” 등의 연산자를 이용해 인덱스 검색할 때 사용
• 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법
• 위에서 소개한 type 칼럼에서 우선순위(성능)가 낮으나 range 접근 방법도 상당히 빠름
• 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼 수 있음

5.11 index

• index 접근 방법은 많은 사람이 자주 오해하는 접근 방법으로 반드시 효율적이지는 않음
• 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미함
• 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔시 풀 테이블 스캔보다 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 사용할 수 있기에 효율적이라 할 수 있다.
• 다음 조건 중 (1,2) 충족 혹은 (1,3) 조건을 충족하는 쿼리에서 사용되는 읽기 방식
  1. range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
  2. 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우(테이블 풀 스캔을 안해도 되는 경우)
  3. 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(별도 정렬 작업을 피할 수 있는 경우)

 

5.12 ALL

• 풀 테이블 스캔을 의미하는 접근 방법
• 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 반환
• 앞선 방식으로 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법
• 테이블이 매우 작지 않다면 실제 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해보고 적용하는 것이 좋다.

 

🔗  6. possible_keys

• 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록
• “사용될 법했던 인덱스의 목록”이며 반드시 사용된 것은 아님
• 실행 계획에서 특별한 경우를 제외하고는 그냥 무시하자.

 

🔗  7. key

• key 칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용되는 인덱스
• 쿼리 튜닝 시 key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요함
• 실행 계획의 type이 ALL일 때와 같이 인덱스를 전혀 사용하지 못하면 NULL로 표시됨

 

 

🔗 8. key_len 칼럼

• 선택된 인덱스의 길이를 표시
• 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 표기
• 더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값

INTEGER 타입은 4바이트 차지

 

2 * INTEGER(4 Byte)

 

🔗  9. ref

• 접근 방법이 ref면 참조 조건(equal 비교 조건)으로 어떤 값이 제공됐는지 표시
  • 상숫값 → const, 다른 테이블의 칼럼값이면 그 테이별명과 칼럼명이 표시
  • 조인 칼럼의 타입(NUMBER, VARCHAR 등)은 동일하게 일치시키는 편이 좋다.
• 크게 신경 쓰지 않아도 무방함

 

🔗  10. rows

• 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다.
• 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미함
• 통계 값으로 계산한 값이므로 실제 쿼리 결과 반환된 레코드 건수는 일치하지 않는 경우가 많음

 

🔗  11. filtered

• 필터링되고 남은 레코드의 비율
• 통계 값 바탕으로 계산한 값으로 실제 결과 값과 반드시 일치하지 않는다.

row가 5건, filtered가 100.00이면 필터링되고 남은 레코드는 5건

 

row가 4건, filtered가 33.33이면 필터링 되고 남은 레코드는 1.333건 (정확하지는 않음)

 

🔗  12. Extra

• 쿼리 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시됨
• 내부적인 처리 알고리즘에 대해 조금 더 깊이있는 내용을 보여주는 경우가 많음
• 옵티마이저가 어떻게 동작하는지에 대해 알려주는 힌트 값

12.1 Using filesort

• ORDER BY 처리가 인덱스를 사용하지 못할 때 실행 계획의 Extra 칼럼에 “Using filesort” 표시
• 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬 수행
• ORDER BY가 사용된 쿼리의 실행 계획에서만 나타날 수 있음
• Using filesort가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋음

12.2 Using index(커버링 인덱스)

데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있는 경우 표시

최악의 경우 인덱스를 통해 검색된 결과 레코드 한 건씩 디스크를 읽어야 할 수 있다.

위 쿼리가 만약 인덱스(ix_firstname)를 사용한다면 employees 테이블의 first_name 칼럼에 생성된 인덱스를 이용해 일치하는 레코드 건수를 검색하고, 각 레코드의 birth_date 칼럼의 값을 읽기 위해 그 만큼 추가적으로 데이터 페이지를 읽어야 한다.

그래서 MySQL 옵티마이저는 인덱스를 사용하는 것보다 풀 테이블 스캔으로 처리하는 편이 더 효율적이라고 판단한 것이다.

 

하지만 위 쿼리처럼 SELECT 조회 칼럼에 birth_date 칼럼을 제외하면 풀 테이블 스캔이 아닌 인덱스 레인지 스캔으로 처리가 된다.

위 쿼리는 인덱스를 통해 필요한 레코드를 검색하고 필요한 칼럼(first_name)까지 인덱스에서 가져올 수 있다. 필요한 칼럼이 모두 인덱스에 있으므로 매우 빠른 속도로 처리된다.

 

위와 같이 인덱스만으로 쿼리 수행이 가능할 때 실행 계획의 Extra에는 "Using index"라는 메시지가 출력되고, 이렇게 인덱스만으로 처리되는 방법을 "커버링 인덱스(Covering Index)"라고 한다.

 

인덱스 레인지 스캔을 사용하지만 쿼리의 성능이 만족스럽지 못한 경우 위 두 번째 예제처럼 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경하여 큰 성능 향상을 볼 수 있다.

(쿼리를 커버링 인덱스로 처리하느냐 못하느냐의 성능 차이는 수십 배에서 수백 배까지 날 수 있다고 한다.)

 

12.3 Using temporary

쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블(Temporary Table)을 생성하여 표시된 것을 의미한다.

12.4 Using index for skip scan

MySQL 옵티마이저가 인덱스 스킵 스캔 최적화를 사용할 경우 "Using index for skip scan"

💡 index skip scan 

예를 들어, employees 테이블에서 (gender, birth_date) 칼럼으로 이루어진 ix_gb 인덱스가 존재한다고 가정해보자.
MySQL 8.0 버전 부터 WHERE 조건절에 인덱스의 선행 칼럼인 gender 조건이 없이 birth_date 칼럼으로만 이루어진 쿼리가 존재하더라도 인덱스를 사용할 수 있게 해준다.

// 기존에는 인덱스를 사용할 수 없음
SELECT * FROM employees WHERE birth_date >= '2001-02-04';

// skip scan은 대략적으로 아래와 같이 쿼리가 처리됨
SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '2001-02-04';
SELECT * FROM employees WHERE gender = 'F' AND birth_date >= '2001-02-04';

* 하지만 WHERE 조건절에 없는 gender 칼럼의 유니크한 값의 개수가 적어야 한다는 단점이 있다.
(위에서는 'M', 'F' 2개만 존재)

12.5 Using join buffer(Block Nested Loop, hash join)

보통 빠른 쿼리 실행을 위해 조인되는 칼럼에는 인덱스를 생성한다.

실제 조인에 필요한 인덱스는 조인에서 뒤에 읽는 테이블(드리븐 테이블)의 칼럼에만 필요하다. 

(뒤에 읽는 테이블은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 크기 때문)

 

이처럼 조인이 수행될 때 드리븐 테이블에 적절한 인덱스가 없다면, MySQL 옵티마이저는 블록 네스티드 루프나 해시 조인을 사용한다.

이는 MySQL 서버의 join buffer를 사용한다. (join_buffer_size라는 시스템 변수를 통해 조인 버퍼의 크기 설정이 가능하다.)

12.6 Using where

쿼리에 대한 접근 방식을 설명한 것으로 스토리지 엔진으로 부터 받은 데이터를 MySQL 엔진에서 별도의 가공을 해서 필터링 작업을 처리한 경우 Extra 칼럼에 "Using where" 표시

 

 

예제로 사용된 쿼리

-- EXPLAIN
-- SELECT * 
-- FROM employees e 
-- INNER JOIN salaries s ON e.emp_no = s.emp_no
-- WHERE first_name = 'ABC';

-- id, SIMPLE
EXPLAIN 
SELECT e.*
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no;

EXPLAIN 
SELECT e.*
FROM employees e
INNER JOIN salaries s ON s.emp_no = e.emp_no;


-- id, PRIMARY
EXPLAIN
SELECT 
( (SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM salaries) ) AS total_count
FROM employees;
    
-- Subquery
EXPLAIN
SELECT 
e.first_name, 
    (SELECT COUNT(*) FROM salaries LIMIT 3) AS cnt
FROM employees e
WHERE e.emp_no = 3;    

-- Union
EXPLAIN
SELECT *
FROM (
(SELECT emp_no FROM employees e1 LIMIT 2) UNION ALL
    (SELECT emp_no FROM employees e2 LIMIT 2) UNION ALL
    (SELECT emp_no FROM employees e3 LIMIT 3)
) tb;

-- Dependent Union
EXPLAIN
SELECT *
FROM employees e1 
WHERE e1.emp_no IN (
SELECT e2.emp_no FROM employees e2 WHERE e2.first_name = 'Matt'
    UNION
    SELECT e3.emp_no FROM employees e3 WHERE e3.last_name = 'Matt'
);

-- Subquery
EXPLAIN
SELECT 
e.first_name, 
    (SELECT COUNT(*) FROM salaries LIMIT 3) AS cnt
FROM employees e
WHERE e.emp_no = 3;

-- Derived
EXPLAIN
SELECT *
FROM ( SELECT s.emp_no FROM salaries s GROUP BY s.emp_no) tb,
employees e
WHERE e.emp_no = tb.emp_no;

-- Derived join
EXPLAIN 
SELECT *
FROM employees e
JOIN (SELECT s.emp_no FROM salaries s GROUP BY s.emp_no) tb
ON e.emp_no = tb.emp_no;

-- Materialized
EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no IN (
SELECT  emp_no FROM salaries WHERE amount BETWEEN 100 AND 1000
);

EXPLAIN
SELECT NOW();



SELECT *
FROM ( SELECT s.emp_no FROM salaries s GROUP BY s.emp_no) tb,
employees e
WHERE tb.emp_no = e.emp_no;

SELECT * FROM employees e
WHERE e.emp_no IN ( 
SELECT emp_no FROM salaries WHERE amount BETWEEN 12000 AND 16000
);

SELECT * FROM salaries;

CREATE TABLE employees_partitions(
emp_no int NOT NULL AUTO_INCREMENT,
    birth_date DATE NOT NULL,
    first_name VARCHAR(16) NOT NULL,
    last_name VARCHAR(16) NOT NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (emp_no, hire_date)
) PARTITION BY RANGE COLUMNS(hire_date)
(
    PARTITION p2000 VALUES LESS THAN ('2000-01-01'),
    PARTITION p2005 VALUES LESS THAN ('2005-01-01'),
    PARTITION p2010 VALUES LESS THAN ('2010-01-01'),
    PARTITION p2015 VALUES LESS THAN ('2015-01-01'),
    PARTITION p2020 VALUES LESS THAN ('2020-01-01')
);

INSERT INTO employees_partitions VALUES (null, "2022-12-30", 'JH', 'Lee', '2019-12-30');
INSERT INTO employees_partitions VALUES (null, "2022-11-30", 'JH', 'Lee', '2019-12-30');
INSERT INTO employees_partitions VALUES (null, "2022-10-30", 'JH', 'Lee', '2019-12-30');
INSERT INTO employees_partitions VALUES (null, "2022-09-30", 'JH', 'Lee', '2019-12-30');
INSERT INTO employees_partitions VALUES (null, "2022-08-30", 'JH', 'Lee', '2019-12-30');

SELECT * FROM employees_partitions;

-- partitions
EXPLAIN
SELECT *
FROM employees_partitions
WHERE hire_date BETWEEN '2014-01-06' AND '2022-12-30';



-- MyISAM -> system
CREATE TABLE employees_2(
emp_no int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    birth_date DATE NOT NULL,
    first_name VARCHAR(16) NOT NULL,
    last_name VARCHAR(16) NOT NULL
) ENGINE = MyISAM;

INSERT INTO employees_2 VALUES (null, '2022-12-30', 'a', 'b');
commit;
EXPLAIN SELECT * FROM employees_2;

-- InnoDB -> ALL
CREATE TABLE employees_3(
emp_no int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    birth_date DATE NOT NULL,
    first_name VARCHAR(16) NOT NULL,
    last_name VARCHAR(16) NOT NULL
) ENGINE = InnoDB;

INSERT INTO employees_3 VALUES (null, '2022-12-30', 'a', 'b');
commit;
EXPLAIN SELECT * FROM employees_3;

SELECT * FROM employees;

-- const
EXPLAIN SELECT * FROM employees WHERE emp_no = 4 AND hire_date = '2000-01-01';
EXPLAIN SELECT * FROM salaries WHERE sal_id = 2;

EXPLAIN
SELECT *
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND s.sal_id = 2;
    
-- ref
EXPLAIN
SELECT * FROM employees e WHERE e.emp_no = 3;

-- ALL
EXPLAIN
SELECT * FROM salaries s WHERE s.emp_no = 3;

-- const
EXPLAIN
SELECT * FROM salaries s WHERE s.sal_id = 2;

SELECT * FROM salaries;
INSERT INTO salaries VALUES (6, null, 8000);

-- unique_subquery
EXPLAIN
SELECT * FROM employees
WHERE emp_no IN (
SELECT emp_no FROM salaries WHERE sal_id = 3
);

-- const, ref (MySQL 버전 5.x -> unqiue_subquery)
EXPLAIN
SELECT * FROM employees
WHERE emp_no IN (
SELECT emp_no FROM salaries WHERE sal_id = 3
);

-- range
EXPLAIN
SELECT * FROM employees
WHERE emp_no BETWEEN 2 AND 6;

-- range
EXPLAIN
SELECT * FROM salaries
WHERE amount BETWEEN 9000 AND 14000;

SELECT * FROM employees;
SELECT * FROM salaries;

EXPLAIN
SELECT * FROM employees ORDER BY last_name LIMIT 5;

EXPLAIN
SELECT * FROM employees ORDER BY emp_no LIMIT 5;

EXPLAIN
SELECT * FROM employees ORDER BY hire_date LIMIT 5;

EXPLAIN
SELECT * FROM employees ORDER BY emp_no, hire_date LIMIT 5;

EXPLAIN
SELECT * FROM salaries ORDER BY sal_id;

EXPLAIN
SELECT * FROM salaries ORDER BY emp_no;

EXPLAIN
SELECT * FROM employees;

-- key_len
EXPLAIN
SELECT * FROM dept_emp WHERE dept_no = 1 AND emp_no = '3';
SELECT * FROM dept_emp;

-- ref 
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no = de.emp_no;

-- 총 데이터: 450만건
EXPLAIN
SELECT * FROM salaries WHERE amount > 2000 AND sal_id > 10000;

EXPLAIN
SELECT * FROM salaries WHERE emp_no = 1;

EXPLAIN
SELECT * FROM dept_emp WHERE dept_no > 3;

-- 25건 중 10건 조회 
EXPLAIN
SELECT * FROM employees_partitions WHERE hire_date > '2014-12-01'; 

-- 5건, 33.33
EXPLAIN
SELECT * FROM employees_partitions WHERE emp_no > 20 AND hire_date > '2014-12-01'; 

EXPLAIN
SELECT * FROM employees_partitions WHERE emp_no > 20;

-- 10
SELECT * FROM employees_partitions WHERE hire_date > '2014-12-01';


-- 5건, row:5, filter: 100
EXPLAIN
SELECT * FROM employees_partitions WHERE emp_no > 20;

-- 2건, row:4, filter:33.33
EXPLAIN
SELECT * FROM employees_partitions WHERE emp_no > 20 AND hire_date > '2019-12-01'; 

SELECT COUNT(*) FROM employees_partitions WHERE emp_no > 20;
SELECT COUNT(*) FROM employees_partitions WHERE emp_no > 20 AND hire_date > '2019-12-01';

EXPLAIN
SELECT * FROM salaries ORDER BY amount;


EXPLAIN 
SELECT e.*
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no;


EXPLAIN 
SELECT e.*
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no;


-- Derived
EXPLAIN
SELECT *
FROM (SELECT s.emp_no FROM salaries s GROUP BY s.emp_no) tb,
employees e
WHERE e.emp_no = tb.emp_no;

-- Join
EXPLAIN
SELECT *
FROM employees e
-- JOIN 절에서 서브쿼리 사용
JOIN (SELECT s.emp_no FROM salaries s GROUP BY s.emp_no) tb
ON e.emp_no = tb.emp_no;

EXPLAIN
SELECT *
FROM employees e
-- JOIN 절에서 서브쿼리 사용
JOIN (SELECT s.emp_no FROM salaries s GROUP BY s.emp_no) tb
ON e.emp_no = tb.emp_no;

EXPLAIN FORMAT=TREE
SELECT * 
FROM dept_emp de, employees e
WHERE e.emp_no = de.emp_no
AND de.dept_no = '1';
    
SELECT VERSION();

EXPLAIN
SELECT * FROM employees_partitions WHERE hire_date LIKE '2010%';


SELECT COUNT(*) FROM employees;
INSERT INTO employees VALUES (3, '2002-01-02', 'Hali', 'A', 'M', '2009-04-01', '2');

EXPLAIN 
SELECT first_name
FROM employees WHERE first_name BETWEEN 'Jade' AND 'Neo';

EXPLAIN 
SELECT first_name, birth_date
FROM employees WHERE first_name BETWEEN 'Jade' AND 'Neo';

EXPLAIN 
SELECT emp_no, first_name
FROM employees WHERE first_name BETWEEN 'Jade' AND 'Neo';

 

📄 참고 문서

• Real MySQL 8.0 1권