[RealMySQL 8.0] 10장. 실행 계획 (2)
🌀

[RealMySQL 8.0] 10장. 실행 계획 (2)

Created
2023/02/09
Tags
RealMySQL 8.0
 
2023-02-09 @이영훈
 
 

히스토그램과 인덱스

인덱스 다이브: 옵티마이저가 실제 인덱스의 B-Tree를 샘플링해서 살펴보는 것
 
MySQL 8.0 서버에서는 인덱스된 칼럼을 검색 조건으로 사용할 때,
  • 히스토그램을 사용하지 않는다
  • 인덱스 다이브를 통해 직접 수집한 정보를 활용한다
 
MySQL 8.0 버전에서 히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용된다
 
 

코스트 모델 (Cost Model)

전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model)이라고 한다.
 
MySQL 8.0 서버의 코스트 모델은 다음 2개의 테이블에 저장돼 있는 설정값을 사용 (mysql DB에 존재)
  • server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
  • engine_cost: 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
💡
MySQL 서버의 engine_cost 테이블과 server_cost 테이블의 기본값을 함부로 변경하지 않는 게 좋다. MySQL 서버에 적용된 기본값으로도 MySQL 서버는 20년이 넘는 시간 동안 수많은 응용 프로그램에서 잘 사용돼 왔다.
 
 

실행 계획 확인

  • EXPLAIN 명령어로 실행
  • 실제로 쿼리를 실행하지 않고 실행 계획 추출 (EXPLAIN ANALYZE와 다름)
  • FORMAT 옵션으로 JSON, TREE, 테이블 형태로 출력
 
 

쿼리의 실행 시간 확인

  • EXPLAIN ANALYZE : 쿼리의 실행 계획과 단계별 소요된 시간 정보 확인
  • 결과를 항상 TREE 포맷으로 출력
  • 실제 쿼리를 실행하고 사용된 실행 계획과 소요된 시간을 표시
 
실제 실행 순서
  • 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
  • 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행
 
 

실행 계획 분석

  • 실행 계획이 어떤 접근 방법을 사용해서 어떤 최적화를 수행하는지,
  • 어떤 인덱스를 사용하는지를 이해하는 것이 중요
 
  • 표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블(서브쿼리로 임시 테이블을 생성한 경우 그 임시 테이블까지 포함)의 개수만큼 출력된다
  • 실행 순서는 위에서 아래로 순서대로 표시된다
    • UNION이나 상관 서브쿼리와 같은 경우 순서대로 표시되지 않을 수 있다
    • 실행 계획의 id 칼럼이 테이블의 접근 순서를 표현하지 않는다
    • 실행 순서는 select_type과 다른 필드를 종합적으로 보고 판단해야 한다
    • EXPLAIN FORMAT=TREE 명령으로 확인해보면 순서를 더 정확히 알 수 있다
  • 위쪽에 출력된 결과일수록(id 칼럼 값이 작을수록) 쿼리의 바깥(Outer) 부분이거나 먼저 접근한 테이블이고 아래쪽에 출력된 결과일수록(id 칼럼 값이 클수록) 쿼리의 안쪽(Inner) 부분 또는 나중에 접근한 테이블에 해당
 
 

id 칼럼

단위 쿼리: SELECT 키워드 단위로 구분한 것
  • 다음의 예에서는 아래와 같이 2개의 단위 쿼리가 있다
SQL
SELECT ...
FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2
WHERE tb1.id = tb2.id;
 
SQL
1. SELECT ... FROM tb_test1;
2. SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id = tb2.id;
 
id 칼럼은
  • 단위 쿼리별로 부여되는 식별자 값이다
  • 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여
  • 다음 쿼리의 실행 계획에서는 쿼리 문장이 3개의 단위 구성돼 있으므로 총 3개의 다른 id 값이 표시
    • SQL
      EXPLAIN
      SELECT
      ( (SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM departments) ) AS total_count;
 
 
SQL
EXPLAIN FORMAT=TREE
SELECT *
FROM dept_emp de
WHERE de.emp_no = (SELECT e.emp_no
                   FROM employees e
                   WHERE e.first_name = 'Georgi'
                     AND e.last_name = 'Facello' LIMIT 1);
id
select_type
table
type
key
rows
Extra
1
PRIMARY
de
ref
ix_empno_fromdate
1
Using where
2
SUBQUERY
e
ref
ix_firstname
253
Using where
 
employees 테이블을 먼저 읽고, 그 결과를 이용해 dept_emp 테이블을 읽는 순서로 실행된 것이다
💡
“select_type에서, SUBQUERY를 실행하고 PRIMARY를 실행한다”고 생각됩니다 (실행 계획 모든 내용 공부하면 더 명확해질거라 생각합니다)
 
  • 실행 계획의 id 칼럼이 테이블의 접근 순서를 의미하지는 않는다
  • EXPLAIN FORMAT=TREE 명령어로 확인하면 순서를 더 정확히 알 수 있다
 
 

select_type 칼럼

단위 쿼리(SELECT)가 어떤 타입의 쿼리인지 표시되는 칼럼
 
SIMPLE
  • UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우
  • 쿼리에 조인이 포함된 경우도
  • 쿼리 문장이 아무리 복잡하더라도, SIMPLE인 단위 쿼리는 하나만 존재
  • 일반적으로 제일 바깥 SELECT 쿼리
 
PRIMARY
  • UNION이나 서브쿼리를 가지는 SELECT 쿼리의 가장 바깥족(Outer)에 있는 단위 쿼리
  • PRIMARY인 단위 쿼리는 하나만 존재
  • 제일 바깥쪽에 있는 단위 쿼리
 
UNION
  • UNION으로 결합하는 단위 쿼리 가운데 두 번째 이후 단위 쿼리
  • (UNION의 첫 번째 단위 쿼리는 DERIVED)
 
SQL
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;
 
id
select_type
table
1
PRIMARY
<derived2>
2
DERIVED
e1
3
UNION
e2
4
UNION
e3
 
DEPENDENT UNION
  • UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시
  • DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미
  • 내부 쿼리가 외부의 값을 참조해서 처리될 때 DEPENDENT 키워드가 표시
 
 
  • 옵티마이저는 IN 내부의 서브 쿼리를 먼저 처리하지 않고,
  • 외부의 employees 테이블을 먼저 읽은 다음 서브쿼리를 실행하는데 이때 employees 테이블의 칼럼값이 서브쿼리에 영향을 준다
SQL
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'
);
id
select_type
table
1
PRIMARY
e1
2
DEPENDENT SUBQUERY
e2
3
DEPENDENT UNION
e3
NULL
UNION RESULT
<union2,3>
  • UNION에 사용된 쿼리의 WHERE 조건에 em2.emp_no=em1.emp_no와 e3.emp_no=e1.emp_no 라는 조건이 자동으로 추가되어 실행
  • 외부에 정의된 employees 테이블의 emp_no 칼럼이 서브 쿼리에 사용되기 때문에 DEPENDENT
 
UNION RESULT
  • UNION 결과를 담아두는 테이블을 의미
  • (MySQL 8.0 이상) UNION은 임시 테이블에 저장, UNION ALL은 임시 테이블 없이
  • 임시 테이블을 가리키는 라인이 UNION RESULT
  • UNION RESULT은 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않음
 
 
SQL
EXPLAIN 
SELECT emp_no FROM salaries WHERE salary > 100000
UNION DISTINCT 
SELECT emp_no FROM dept_emp WHERE from_date > '2001-01-01';
id
select_type
table
1
PRIMARY
salaries
2
UNION
dept_emp
NULL
UNION RESULT
<union1,2>
<union1,2>의 1과 2는 실행 계획 결과 id 값
 
 
UNION ALL을 사용하면 MySQL 서버는 임시 테이블에 버퍼링하지 않기 때문에 UNION RESULT 라인이 없음
SQL
EXPLAIN 
SELECT emp_no FROM salaries WHERE salary > 100000
UNION ALL
SELECT emp_no FROM dept_emp WHERE from_date > '2001-01-01';
id
select_type
table
1
PRIMARY
salaries
2
UNION
dept_emp