쿼리 플랜

과녁스·2021년 12월 16일

db mysql 쿼리플랜

DB

목록 보기

2/5

쿼리 플랜(Query Plan)이란?

쿼리 플랜(query plan) 또는 쿼리 실행 계획(query execution plan)은 SQL 관계형 데이터베이스 관리 시스템의 데이터 접근에 사용되는 순서가 있는 단계별 집합
SQL이 선언형이기 때문에 주어진 쿼리를 실행하기 위해 수많은 방법이 존재하는 것이 일반적이며 이에 따라 다양한 성능 차이를 보임
쿼리가 데이터베이스에 제출되면 쿼리 옵티마이저는 쿼리 실행을 위한 각기 다른 가능성 있는 올바른 플랜 중 일부를 평가한 다음 최적의 옵션을 고려할 때 반환
쿼리 옵티마이저가 불완전하기 때문에 데이터베이스 사용자와 관리자들은 더 나은 성능을 얻기 위해 옵티마이저가 만들어내는 플랜을 수동으로 검사하고 튜닝해야 하는 경우가 있음
- 😮 옵티마이징 : 데이터를 찾는 가장 빠른 방법을 찾아내는 단계

쿼리 플랜 생성

제공된 데이터베이스 관리 시스템은 주어진 쿼리의 플랜을 반환하기 위한 하나 이상의 매커니즘을 제공할 수 있음
일부 패키지에는 그래픽으로 표현되는 쿼리 플랜을 생성하는 도구가 포함
기타 도구들은 특수한 모드를 통해 DBMS가 텍스트 방식으로 쿼리 플랜을 기술할 수 있도록 설정
쿼리 플랜을 조회하는 다른 매커니즘에는 검사할 쿼리를 실행한 이후 가상 데이터베이스 테이블을 조회하는 것이 수반된다. 이를테면 오라클에서는 EXPLAIN PLAN 문을 사용하여 이를 수행할 수 있다.

그래픽 플랜

이를테면 마이크로소프트 SQL 서버에 포함되어 있는 SQL 서버 매니지먼트 스튜디오 도구는 샘플 데이터베이스에 대해 이 2개의 테이블 조인(join)을 실행할 때 그래픽 플랜을 표시

SELECT *
FROM HumanResources.Employee AS e
    INNER JOIN Person.Contact AS c
    ON e.ContactID = c.ContactID
ORDER BY c.LastName

UI는 쿼리 플랜에 수반되는 다양한 연산자 속성의 탐색을 허용하는데, 여기에는 연산자 타입, 각 연산자가 소비하거나 만들어내는 로우(row)의 수, 각 연산자의 작업의 예측되는 비용이 포함된다.

텍스트 플랜

위와 동일한 쿼리의 텍스트 플랜은 아래와 같이 표시

StmtText
----
  |--Sort(ORDER BY:([c].[LastName] ASC))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[ContactID], [Expr1004]) WITH UNORDERED PREFETCH)
            |--Clustered Index Scan(OBJECT:([AdventureWorks].[HumanResources].[Employee].[PK_Employee_EmployeeID] AS [e]))
            |--Clustered Index Seek(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS [c]),
               SEEK:([c].[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as [e].[ContactID]) ORDERED FORWARD)

쿼리 플랜 보는법

MySQL

쿼리문에 EXPLAIN을 붙이면 실행계획 확인 가능

예시

EXPLAIN SELECT * FROM short_url su LEFT OUTER JOIN short_url_stat sus ON su.hash = sus.hash WHERE deleted_date IS NULL;

이미지 출처 : https://img1.daumcdn.net/thumb/R1280x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2Fdbbqa9%2FbtqD8PMH5VI%2FzlEXJRccyWVzs9eFeLK9A1%2Fimg.png

EXPLAIN 정보

id
- SELECT를 구분하는 번호
select_type
- SELECT 의 타입
  - SIMPLE: 단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)
  - PRIMARY: 가장 외곽의 SELECT
  - UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT
  - DEPENDENT UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT, 외곽쿼리에 의존적
  - UNION RESULT: UNION 의 결과물
  - SUBQUERY: 서브쿼리의 첫번째 SELECT
  - DEPENDENT SUBQUERY: 서브쿼리의 첫번째 SELECT, 외곽쿼리에 의존적
  - DERIVED: SELECT 로 추출된 테이블 (FROM 절 내부의 서브쿼리)
table
- 참조하는 테이블명
type :
- 조인타입(우수한 순서부터 차례대로...?)
  - system
    - 테이블에 단 하나의 행만 존재(시스템 테이블)
    - const join 의 특수한 경우
  - const
    - 많아야 하나의 매치되는 행만 존재할 때
    - PRIMARY KEY 나 UNIQUE index 를 상수와 비교할 때
    - 각 컬럼값은 나머지 연산에서 상수로 간주, 처음 한번만 읽어들이면 되므로 매우 빠름
  - eq_ref
    - 조인수행을 위해 각 테이블에서 하나씩의 행만이 읽히는 경우
    - 조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우
    - 인덱스된 컬럼이 = 연산에 사용되는 경우
  - ref
    - 이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀질 때
    - leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때 (즉 키값으로 단일행을 추출할수 없을때)
    - 사용된 키가 적은수의 행과 매치되면 이것은 적절한 조인 타입
    - ref 는 인덱스된 컬럼과 = 연산에서 사용됨
  - ref_or_null
    - ref 와 같지만 NULL 값을 포함하는 행에대한 검색이 수반될 때
    - 서브쿼리 처리에서 대개 사용됨
  - index_merge
    - 인덱스 병합 최적화가 적용되는 조인 타입
    - 이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스중 가장 긴 key 명을 나타냄
  - unique_subquery
    - 몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용됨
    - unique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체함
  - index_subquery
    - unique_subquery 와 마찬가지로 IN 서브쿼리를 대체
    - 서브쿼리에서 non-unique 인덱스가 사용될때 동작 함
  - range
    - 인덱스를 사용하여 주어진 범위 내의 행들만 추출
      - key 컬럼: 사용된 인덱스
      - key_len: 사용된 가장 긴 key 부분
      - ref 컬럼: 이 타입의 조인에서 NULL
      - 키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용됨
  - index
    - 인덱스가 스캔된다는걸 제외하면 ALL 과 같음
    - 일반적으로 인덱스 파일이 데이타파일보다 작기 때문에 ALL 보다는 빠름
    - MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할때 이 조인타입을 적용함
  - ALL
    - 이전 테이블과의 조인을 위해 풀스캔 (조인에 쓰인) 첫번째 테이블이 고정이 아니라면 비효율적
    - 대부분의 경우에 아주 느린 성능
possible_keys
- MySQL 이 해당 테이블의 검색에 사용할수 있는 인덱스들
- possible_keys 에 나타난 인덱스들이 결과에 나타난 테이블 순서에서 실제 사용할 수 없을 수도 있음
key
- MySQL 이 실제 사용 key(index)
key_len
- MySQL 이 사용한 인덱스의 길이, key 컬럼값이 NULL 이면 이값도 NULL
- key_len 값으로 MySQL 이 실제 복수컬럼 키중 얼마나 많은 부분을 사용할 것인지 알 수 있음
ref
- 행을 추출하는데 키와 함께 사용된 컬럼이나 상수 값
rows
- 쿼리 수행에서 MySQL 이 예상하는 검색해야할 행수
extra
- MySQL 이 쿼리를 해석한 추가적인 정보를 나타냄
  - Distinct: MySQL 이 매치되는 첫행을 찾는 즉시 검색을 중단한다는 의미
  - Not exists: MySQL 이 LEFT JOIN 을 수행함에 매치되는 한 행을 찾으면 더이상 매치되는 행을 검색하지 않음
  - range checked for each record (index map: #): MySQL 이 사용할 좋은 인덱스가 없음 의미
  - Using filesort: MySQL 이 정렬을 위해 추가적인 과정을 필요로 함
  - Using index: 컬럼정보가 실제 테이블이 아닌 인덱스 트리에서 추출, 쿼리에서 단일 인덱스된 컬럼들만을 사용하는 경우
  - Using temporary: MySQL 이 결과의 재사용을 위해 임시테이블을 사용, 쿼리 내에 GROUP BY 와 ORDER BY 절이 각기 다른 컬럼을 사용할때 발생
  - Using where: WHERE 절이 다음 조인에 사용될 행이나 클라이언트에게 돌려질 행을 제한하는 경우 테이블의 모든 행을 검사할 의도가 아니면 ALL 이나 index 라면 쿼리사용이 잘못된 것임
  - Using sort_union(…) , Using union(…) , Using intersect(…)
  - Using index for group-by: Using index 와 접근방식이 같으며, 추가적인 디스크 접근 없이 GROUP BY 나 DICTINCT 쿼리에 사용된 모든 컬럼에 대한 인덱스를 찾았음을 의미