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개의 단위 쿼리가 있다
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;
SQL
복사
id 칼럼은
•
단위 쿼리별로 부여되는 식별자 값이다
•
여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여
•
다음 쿼리의 실행 계획에서는 쿼리 문장이 3개의 단위 구성돼 있으므로 총 3개의 다른 id 값이 표시
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);
SQL
복사
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)
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;
SQL
복사
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 테이블의 칼럼값이 서브쿼리에 영향을 준다
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'
);
SQL
복사
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 값은 부여되지 않음
EXPLAIN
SELECT emp_no FROM salaries WHERE salary > 100000
UNION DISTINCT
SELECT emp_no FROM dept_emp WHERE from_date > '2001-01-01';
SQL
복사
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 라인이 없음
EXPLAIN
SELECT emp_no FROM salaries WHERE salary > 100000
UNION ALL
SELECT emp_no FROM dept_emp WHERE from_date > '2001-01-01';
SQL
복사
id | select_type | table |
1 | PRIMARY | salaries |
2 | UNION | dept_emp |