[MySQL] 실행 계획(Query Plan)이 뭔데

최근에 본 면접에서 질문으로 MySQL 실행계획을 물어보는 분이 계셨는데

감기 이슈로 멍해진 머리로는 제대로 된 답을 하지 못했었다.

 

이 참에 제대로 정리해 보자.

 

빨리 취업하고 싶다

정의

DB + Query Plan을 들을 경우 딱 느낌이 오는 건

"아 DB에서 쿼리를 분석해서 사용하는 과정에 대한 계획인가"로 추론해 볼 순 있는 데 대충 그게 맞습니다.

 

이게 왜 필요하나면

우리가 TABLE에 쿼리를 아래처럼 날렸을 때

SELECT LoanedBook.*
FROM LoanedBook
JOIN Person ON LoanedBook.person_id = Person.person_id
WHERE Person.person_id = 1;

주어진 쿼리가 같더라도 

LoanedBook에 어디에 Index가 있는지 또는 Person에 Unique 값이 있는지 Primary가 있는지 등과 같은 여러 정보가 존재하고 쿼리 캐시등도 관여가 되기에 수많은 방법이 존재하고

 

이 방법을 정하는 게 Query Plan이다.

 

• Query Plan 순서
  
  • SQL 문법 해석 및 분석 : 우리가 적은 쿼리를 해석하고 분석
  • 최적화 : 옵티마이저가 여러 계획 중에서 테이블 정보, 인덱스, 조인 등을 여러 정보를 통해 가장 최적의 계획을 선택함
  • 실행 계획 수립 : 선택된 계획으로 실제 데이터에 접근
  • 실행 : 최종적으로 쿼리를 실행

 

위에 순서를 보면 알 수 있듯이 최적화 단계에서의 옵티마이저(Optimizer)가 가장 중요한 걸 알 수 있다
아래 종류가 있는데 요즘은 비용 기반이야 

• 옵티마이저 종류
  • 비용 기반 최적화 (Cost-Based Optimizer, CBO)
    • 최근 대부분 DBMS가 이용
    • 쿼리를 처리하기 위해 여러 방법을 만들고 각 작업에 대한 비용과 테이블의 통계 정보를 이용해서 비용을 산출 후 적은 걸 선택 
    • MySQL 5.7부터 InnoDB 스토리지 엔진에서 비용 기반을 선택
  • 규칙 기반 최적화 (Rule-Based Optimizer, RBO)
    • 예전 버전 DBMS에서 사용되었지만 지금은 사용 안 함
    • 대상 테이블의 레코드 건수나 선택 등을 고려하지 않은 채 Optimizer에 내장된 우선순위로 실행 계획을 정함

 

사용법

대충 뭔지는 알겠으니 사용해 보자, 아래 코드로 테이블을 만들고 

-- 사람 테이블 생성
CREATE TABLE Person (
    person_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birthdate DATE
);

-- 대출도서 테이블 생성
CREATE TABLE LoanedBook (
    loan_id INT PRIMARY KEY,
    book_title VARCHAR(100) NOT NULL,
    loan_date DATE,
    person_id INT,
    FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

 

더미 데이터도 넣어주고

-- 사람 3명 생성
INSERT INTO Person (person_id, name, birthdate) VALUES
(1, 'Person1', '1990-01-01'),
(2, 'Person2', '1995-05-15'),
(3, 'Person3', '1988-08-20');

-- 도서 14권 생성
INSERT INTO LoanedBook (loan_id, book_title, loan_date, person_id) VALUES
(1, 'Book1', '2023-01-05', 1),
(2, 'Book2', '2023-02-10', 2),
(3, 'Book3', '2023-03-15', 3),
(4, 'Book4', '2023-04-20', 1),
(5, 'Book5', '2023-05-25', 2),
(6, 'Book6', '2023-06-30', 3),
(7, 'Book7', '2023-07-05', 1),
(8, 'Book8', '2023-08-10', 2),
(9, 'Book9', '2023-09-15', 3),
(10, 'Book10', '2023-10-20', 1),
(11, 'Book11', '2023-11-25', 2),
(12, 'Book12', '2023-12-30', 3),
(13, 'Book13', '2024-01-05', 1),
(14, 'Book14', '2024-02-10', 2);

MYSQL에서는 키워드를 통해 간단하게 실행계획을 볼 수 있다

EXPLAIN SELECT LoanedBook.*
FROM LoanedBook
JOIN Person ON LoanedBook.person_id = Person.person_id
WHERE Person.person_id = 1;

결과물

 

보면은 이게 뭔지 알 수 없는데 간단하게 키워드 별로 기능을 알아보자, 후에 필요한 건 별도로 표시함

 

키워드

키워드	설명
id	select문에서 인식되는 순서 값 (id가 동일하면 같은 순서로 실행)
select_type	select 유형, 복잡한 조인을 해도 SIMPLE이며 서브 쿼리나 UNION이 있을 경우 id와 같이 바뀜
table	접근하는 테이블
partitions	파티션 된 테이블을 사용했을 경우 그 파티션의 정보
type	어떻게 조인되는 지 나타내서 접근 방식이 효율적인지를 나타내는 지 이 값을 분석해서 어떤 인덱스가 사용되고 이를 통해 어떻게 쿼리를 튜닝해야하는 지에 대한 Insight 제공
possible_key	테이블에서 row를 매핑하기 위해 사용 가능한 Key
key	실제로 쿼리 실행에 사용된 Key로 possible_keys에 포함되지 않은 인덱스도 가능
key_len	선택된 Index의 길이로 이 값이 너무 길다면 비효율적인 것 (Byte 단위이며 다중 인덱스도 가능)
ref	key에 저장된 인덱스와 비교되는 column 혹은 constants인지를 보여줌
rows	최초로 가져온 행의 개수로 통계 값이므로 실제 행과 반드시 일치하지 않음
filtered	WHERE 구의 검색 조건이 적용되면서 몇행이 남는지를 표시, 이 값도 통계 값이므로 반드시 일치하지 않음
extra	옵티마이저가 동작하는 걸 알려주는 힌트

 

select_type 종류

구분	설명
SIMPLE	단순 SELECT (Union 이나 Sub Query 가 없는 SELECT 문)
PRIMARY	Sub Query를 사용할 경우 Sub Query의 외부에 있는 쿼리(첫번째 쿼리) UNION 을 사용할 경우 UNION의 첫 번째 SELECT 쿼리.복수행의 mysql explain 실행계획에서 1행만 존재
UNION	UNION 쿼리에서 Primary를 제외한 나머지 SELECT, UNION과 UNION ALL 절로 생성된 임시 테이블을 의미
DEPENDENT_UNION	UNION 과 동일하나, 외부 결과에 의존할 때 표현된다. UNION 쿼리가 내부에서 사용되었을때 표현
UNION_RESULT	UNION 쿼리의 결과물
SUBQUERY	Sub Query 또는 Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문(FROM절 외에서 사용되는 서브 쿼리를 의미)
DEPENDENT_SUBQUERY	Sub Query 와 동일하나, 외곽쿼리에 의존적임 (값을 공급 받음)
DERIVED	SELECT로 추출된 테이블 (FROM 절 에서의 서브쿼리 또는 Inline View)
MATERIALIZED	MySQL 5.6 버전에 추가된 셀렉트 타입이로 그 이전의 버전에서는 IN 절 내에 서브쿼리가 존재할 경우 매 레코드마다 서브쿼리를 실행시키는 형태로 수행되었다. 생각만해봐도 비효율적임을 알 수 있다. 5.6 에서부터 추가된 MATERIALIZED는 IN 절 내의 서브쿼리를 임시테이블로 만들어 조인을 하는 형태로 최적화를 해줌. DERIVED와 비슷
UNCACHEABLE SUBQUERY	Sub Query와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리. 외부쿼리에서 공급되는 값이 동이라더라도 Cache된 결과를 사용할 수 없음 서브쿼리는 종류에 따라 바깥쿼리 행만큼 수행되어야 하는 경우도있다. 실제로 그렇게 작동한다면 성능에 큰 영향을 끼치게되므로 경우에 따라 쿼리를 캐싱해놓고 캐싱된 데이터를 갖다쓰게끔 최적화가 되어있는데 그런 캐싱이 작동할 수 없는 경우에 표현된다. 즉 캐싱되지못하는 이유가 수정 가능하다면 캐싱되게끔 하는것이 성능에 좋음
UNCACHEABLE UNION	UNION 과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리

 

type 종류

구분	설명
system	레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태
const	테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식
eq_ref	조인을 할 때 Primary Key
ref	조인을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭하는 경우
ref_or_null	ref 와 같지만 null 이 추가되어 검색되는 경우
index_merge	두 개의 인덱스가 병합되어 검색이 이루어지는 경우
unique_subquery	다음과 같이 IN 절 안의 서브쿼리에서 Primary Key가 오는 특수한 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT Primary Key FROM tab01);
index_subquery	unique_subquery와 비슷하나 Primary Key가 아닌 인덱스인 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT key01 FROM tab02);
range	특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우로, 데이터가 방대하지 않다면 단순 SELECT 에서는 나쁘지 않음
index	인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 일반적으로 인덱스 풀스캔이라고 함
all	테이블을 처음부터 끝까지 검색하는 경우로, 일반적으로 테이블 풀스캔이라고 함

 

돌아와서

아까 쿼리 돌려본 결과물을 보면 

둘 다 id가 1이므로  순위가 같고, 서브쿼리나 UNION을 안 쓰기 때문에 SIMPLE이고 table 명칭은 그대로고 

Person은 단일 로우이므로 const, LoanedBook은 person_id가 multi key 이기에 ref이다. 

 

key_len이 4와 5인 이유는 Person의 Primary는 Null이 불가능한 Int 이므로 4바이트이고 

LoanedBook의 person_id는 Null이 가능하기에 1바이트가 추가돼서 5바이트가 된 것이다.

 

ref는 비교 값을 의미하는데 Query에서는 상수를 비교했으므로 const가 온 것

extra는 Primary Key의 index를 사용했기에 Using index라고 뜬 것

 

참고

- https://velog.io/@ddangle/MySQL-%EC%98%B5%ED%8B%B0%EB%A7%88%EC%9D%B4%EC%A0%80

- https://spidyweb.tistory.com/460

- https://jaehoney.tistory.com/193

- https://coding-factory.tistory.com/744

- https://tgyun615.com/161

- https://nomadlee.com/mysql-explain-sql/