📔설명

통계 정보, 실행 계획 확인, 실행 계획 분석을 알아보자

---

🍆통계 정보

인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램 도입

1. 테이블 및 인덱스 통계 정보

쿼리의 실행 계획을 수립할 때 실제 테이블의 데이터를 일부 분석해서 통계 정보 보완해서 사용했었음

MySQL 서버의 통계 정보

각 테이블에 대한 통계 정보를 영구적으로 관리할 수 있도록 mysql 데이터베이스의 innodb_index_stats와 innodb_table_stats로 관리

show tables like '%_stats';

테이블 생성시 STATS_PERSISTENT 옵션을 설정 가능

create table ...()
engine=InnoDB
stats_persistent={default|0|1}

• 0 : 통계 정보를 메모리에만 관리하고, 테이블에 저장 안함
• 1 : 통계 정보를 mysql 데이터베이스 테이블에 저장
• default : 시스템 변수 값으로 결정

테이블 통계 정보 수집시 몇 개의 InnoDB 테이블 블록을 샘플링할지 결정

• innodb_stats_transient_sample_pages : 기본값은 8로, 자동 통계 정보 수집시 8개 페이지만 임의로 샘플링
• innodb_stats_persistent_sample_pages : 기본값은 20으로, analyze table 명령이 실행되면 임의로 20개 페이지만 샘플링

2. 히스토그램

히스토그램(Histogram) : 칼럼의 데이터 분포를 참조할 수 있는 것

히스토그램 정보 수집 및 삭제

analyze table ... update histogram

위 명령어를 통해 수동으로 히스토그램 정보 수집 및 관리

시스템 딕셔너리에 함께 저장
=> information_schema 데이터베이스의 column_statistics 테이블에 로드

analyze table employees.employees update histogram on gender, hire_date;

select *
from column_statistics
where schema_name='employees'
and table_name='employees'\G

• Singleton(싱글톤 히스토그램) : 칼럼값 개별로 레코드 건수 관리, 도수 분포
  => 칼럼이 가지는 값별로 버킷 할당
  => 각 버킷의 칼럼 값과 발생 빈도 비율 가짐

• Equi-Height(높이 균형 히스토그램) : 칼럼값의 범위를 균등한 개수로 구분해서 관리
  => 개수가 균등한 칼럼값의 범위별로 버킷 할당
  => 각 버킷의 범위 시작 값과 마지막 값, 발생 빈도율과 버킷에 포함된 유니크한 값 개수 가짐

히스토그램의 모든 레코드 건수 비율은 누적으로 표시되므로 F는 (1-0.5998)의 비율을 가진다.

• smapling-rate : 히스토그램 정보 수집을 위해 스캔한 페이지 비율
  => ex. 0.35라면 전체 페이지 중 35% 스캔
• histogram-type : 히스토그램 종류 저장
• number-of-buckets-specified : 히스토그램 생성시 설정한 버킷 개수

-- 히스토그램 삭제
-- 딕셔너리에 저장된 내용만 삭제하므로 성능 영향X
analyze table employees.employees drop histogram on gender, hire_date;

-- 히스토그램 사용하지 않게 하려면
-- 다른 최적화 기능이 사용되지 않을 수 있으니 주의
set global optimizer_switch='condition_fanout_filter=off';

히스토그램의 용도

히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지진 않지만, 각 범위(버킷)별로 레코드 건수와 유니크한 값의 개수 정보를 가지므로 훨씬 더 정확

히스토그램 정보가 없다면, 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측

히스토그램 정보가 있다면, 어느 테이블을 먼저 읽어야 조인 횟수를 줄일 수 있을지 옵티마이저가 판단 가능

히스토그램과 인덱스

MySQL 서버에서는 쿼리 실행 계획 수립시 사용 가능한 인덱스로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획 선택
=> 조건절에 일치하는 레코드 건수를 예측하기 위해 실제 인덱스의 B-Tree를 샘플링 : 인덱스 다이브

select *
from employees
where first_name='Tonny'
and birth_date between '1954-01-01' and '1955-01-01';

위 경우 first_name에 인덱스가 있을 때, 인덱스 다이브를 통해 직접 샘플링을 실행하는 것이므로 히스토그램보다 정확한 결과를 기대할 수 있어 히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용

3. 코스트 모델(Cost Model)

코스트 모델 : 전체 쿼리의 비용을 계싼하는 데 필요한 단위 작업들의 비용

코스트 모델은 아래 2개 테이블에 저장돼 있는 설정값 사용 (mysql DB)

• server_cost : 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
• engine_cost : 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리

공통 칼럼

• cost_name : 코스트 모델 각 단위 작업
• default_value : 각 단위 작업의 비용
• cost_value : DBMS 관리자가 설정한 값 (null이면, default_value 값 사용)
• last_updated : 단위 작업 비용이 변경된 시점
• comment : 비용에 대한 추가 설명

engine_cost 테이블의 추가 칼럼

• engine_name : 비용이 적용된 스토리지 엔진
• device_type : 디스크 타입

• row_evaluate_cost : 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업
  => 증가할수록 풀 테이블 스캔처럼 많은 레코드 처리하는 쿼리 비용 높아짐

• key_compare_cost : 키 값의 비교 작업에 필요한 비용
  => ex. 레코드 정렬

-- 실행 계획에 사용된 비용 확인
explain format=tree
select *
from employees
where first_name='Matt' \G

explain format=json
select *
from employees
where first_name='Matt' \G

• key_compare_cost 비용을 높이면, 옵티마이저가 가능한 정렬 수행X 방향으로 계획 선택
• row_evaluate_cost 비용을 높이면, 풀 스캔을 실행하는 쿼리들의 비용이 높아져, 옵티아미저는 가능한 인덱스 레인지 스캔을 사용함
• disk_temptable_create_cost와 disk_temptable_row_cost 비용을 높이면, 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향으로 선택
• memory_temptable_create_cost와 memory_temptable_row_cost 비용을 높이면, 옵티마이저는 메모리에 임시 테이블을 만들지 않는 방향으로 선택
• io_block_read_cost 비용을 높이면, 옵티아미저는 가능한 버퍼 풀에 데이터 페이지가 많이 적재돼 있는 인덱스 사용
• memory_block_read_cost 비용을 높이면, 옵티마이저는 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스 사용

---

🌽실행 계획 확인

DESC 또는 EXPLAIN 명령으로 확인

1. 실행 계획 출력 포맷

format 옵션을 사용해 json, tree, 단순 테이블 형태로 선택 가능

-- 테이블
explain
select *
from employees e inner join salaries s on s.emp_no=e.emp_no
where first_name='ABC';

-- 트리
explain format=tree
select *
from employees e inner join salaries s on s.emp_no=e.emp_no
where first_name='ABC';

-- json
explain format=json
select *
from employees e inner join salaries s on s.emp_no=e.emp_no
where first_name='ABC';

2. 쿼리의 실행 시간 확인

EXPLAIN ANALYZE : 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인

explain analyze
select e.emp_no, avg(s.salary)
from employees e inner join salaries s
on s.emp_no=e.emp_no
and s.salary>50000
and s.from_date<='1990-01-01'
and s.to_date>'1990-01-01'
where e.first_name='Matt'
group by e.emp_no \G

rows 값이 약 10 이므로, salaries 테이블에서 emp_no를 검색해서 10개의 레코드를 모두 가져오는데 평균 0.424초가 걸린 것

---

🍄실행 계획 분석

explain
select *
from employees e inner join salaries s on s.emp_no=e.emp_no
where first_name='ABC';

위쪽에 출력된 결과일수록 쿼리 바깥 부분이거나 먼저 접근한 테이블이고, 아래쪽일 수록 쿼리 안쪽 또는 나중에 접근한 테이블

1. id 칼럼

id 칼럼 : 단위 select별로 부여되는 식별자 값
=> 단위 select : select 키워드 단위로 구분한 것

-- 여러 테이블이 조인되는 경우 id값 증가 X
explain
select e.emp_no, e.first_name, s.from_date, s.salary
from employees e, salaries s
where e.emp_no=s.emp_no limit 10;

explain
select ((select count(*) from employees)+(select count(*) from departments)) as total_count;

실행계획의 id 칼럼이 테이블 접근 순서를 의미하진 않음

2. select_type 칼럼

select_type 칼럼 : 각 단위 select 쿼리가 어떤 타입 쿼리인지 표시

SIMPLE

simple : union이나 서브쿼리를 사용하지 않는 단순한 select 쿼리
=> 아무리 복잡해도 실행 계획에서 simple 쿼리는 하나만 존재
=> 일반적으로 제일 바깥 select 쿼리

PRIMARY

primary : union이나 서브쿼리를 가지는 select 쿼리의 가장 바깥쪽 단위 쿼리
=> 하나만 존재

UNION

union : union으로 결합하는 단위 select 쿼리 중 두 번째 이후 단위 select 쿼리
=> 첫 번째는 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;

DEPENDENT UNION

dependent union : union이나 union all로 집합을 결합하는 쿼리에 표시
=> 내부 쿼리가 외부의 값을 참조해서 처리될 경우

-- 옵티아미저는 in 내부 서브쿼리를 먼저 처리하지 않고 외부 테이블을 먼저 읽은 다음 서브쿼리 실행
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'
);

UNION RESULT

union result : union 결과를 담아두는 테이블
=> union의 결과를 임시 테이블로 생성해 결과를 버퍼링
=> 단위 쿼리가 아니므로 id값 부여 X
=> union all 사용시 안나타남

explain
select emp_no
from salaries where salary>100000
union distinct
select emp_no
from dept_emp where from_date>'2001-01-01';

table의 union1,2는 id값이 1과 2인 결과를 union 했다는 것을 의미

SUBQUERY

subquery : from절 이외에서 사용되는 서브쿼리

EXPLAIN
SELECT
	e.first_name,
	(SELECT COUNT(*)
	FROM dept_emp de, dept_manager dm
	WHERE dm.dept_no=de.dept_no
	) AS cnt
FROM employees e WHERE e.emp_no=10001;

서브쿼리 사용 위치

• 중첩된 쿼리 : select절에 사용
• 서브쿼리 : where절에 사용
• 파생 테이블 : from절에 사용된 서브쿼리

서브쿼리 반환 값

• 스칼라 서브쿼리 : 하나의 값(칼럼 단 하나, 레코드 1건) 반환
• 로우 서브쿼리 : 칼럼 개수 관계X 하나의 레코드만 반환

DEPENDENT SUBQUERY

dependent subquery : 서브쿼리가 바깥쪽 쿼리에서 정의된 칼럼 사용시

-- 외부 쿼리 먼저 실행 후 서브 쿼리 실행돼야 함
EXPLAIN
SELECT
		e.first_name,
		(
		SELECT COUNT(*)
		FROM dept_emp de, dept_manager dm
		WHERE dm.dept_no=de.dept_no AND de.emp_no=e.emp_no --참조
		) AS cnt
FROM employees e
WHERE e.first_name='Matt';

DERIVED

derived : 서브쿼리가 from절에 사용됐을 때
=> 쿼리 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것

-- 임시 테이블에도 인덱스 추가 가능해짐
explain
select *
from (select de.emp_no from dept_emp de group by de.emp_no)tb, employees e
where e.emp_no=tb.emp_no;

DEPENDENT DERIVED

dependent derived : 해당 테이블이 레터럴 조인으로 사용된 것

래터럴 조인(LATERAL JOIN) : from절의 서브쿼리는 외부 칼럼을 사용할 수 없었던 것을 해소

explain
select *
from employees e
left join lateral ( 
	select * from salaries s
	where s.emp_no= e.emp_no
    order by s.from_date desc limit 2) as s2
on s2.emp_no=e.emp_no;

UNCACHEABLE SUBQUERY

uncacheable subquery : 서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능한 경우

조건이 똑같은 서브쿼리가 실행될 때 내부적인 캐시 공간에 이전 실행 결과를 사용할 수 있도록 담아둠

• subquery : 바깥쪽 영향이 없으므로 처음 한 번만 실행해서 그 결과를 캐시
• dependent subquery : 의존하는 바깥쪽 쿼리의 칼럼 값 단위로 캐시

캐시 사용 불가 요소

• 사용자 변수가 서브쿼리에 사용된 경우
• not-deterministic 속성의 스토어드 루틴이 서브쿼리 내에 사용
• uuid()나 rand()와 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용

explain
select *
from employees e 
where e.emp_no=(select @status from dept_emp de where de.dept_no='d005');

UNCACHEABLE UNION

uncacheable union : 두 개 키워드 속성이 혼합된 것

MATERIALIZED

materialized : from절이나 in(서브쿼리) 형태의 쿼리에 사용된 서브쿼리 최적화를 위해 사용

explain
select *
from employees e
where e.emp_no in(
	select emp_no from salaries where salary between 100 and 1000);

서브쿼리 내용을 임시 테이블로 구체화한 후, 임시 테이블과 employees 테이블을 조인

3. table 칼럼

table 칼럼 : 테이블 기준으로 표시되며, 별칭 부여된 경우 별칭 표시

explain select now();

테이블을 사용하지 않는 경우 table 칼럼에 null 표시

<derived N> 또는 <union M,N>과 같이 <>로 둘러싸인 이름은 임시 테이블을 의미
=> 안에 표시되는 숫자는 id값 지칭

1. 첫 번째 라인의 테이블이 <derived2> 이므로, id값이 2인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비돼야 함
2. id값이 2인 라인을 보면 derived로 표시돼 있어, dept_emp 테이블을 읽어 파생 테이블을 생성
3. 첫 번째 라인과 두 번째 라인은 같은 id 값을 가지므로 e 테이블보다 <derived2>가 먼저 위에있으니 <derived2>가 드라이빙 테이블이 되고, e가 드리븐 테이블이 됨

서브쿼리를 구체화해서 임시 테이블로 만들었다는 의미

4. partitions 칼럼

partitions 칼럼 : 파티션을 참조하는 쿼리의 경우 필요한 파티션 목록만 모아서 보여줌

-- hire_date 기준으로 5년 단위로 나누어진 파티션
-- 파티션 키로 사용되는 칼럼은 프라이머리 키를 포함한 모든 유니크 인덱스 일부여야 함
-- emp_no + hire_date가 인덱스

CREATE TABLE employees_2 (
	emp_no int NOT NULL,
	birth_date DATE NOT NULL,
	first_name VARCHAR(14) NOT NULL,
	last_name VARCHAR(16) NOT NULL,
	gender ENUM('M','F') NOT NULL,
	hire_date DATE NOT NULL,
	PRIMARY KEY (emp_no, hire_date)
) PARTITION BY RANGE COLUMNS(hire_date) -- 파티션 키
(PARTITION p1986_1990 VALUES LESS THAN ('1990-01-01'),
	PARTITION p1991_1995 VALUES LESS THAN ('1996-01-01'),
	PARTITION p1996_2000 VALUES LESS THAN ('2000-01-01'),
	PARTITION p2001_2005 VALUES LESS THAN ('2006-01-01'));

insert into employees_2 select * from employees;

explain
select *
from employees_2
where hire_date between '1999-11-15' and '2000-01-15';

파티션 프루닝(Partition pruning) : 파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정

5. type 칼럼

type 칼럼 : 각 테이블의 레코드를 어떤 방식으로 읽었는지 나타냄
=> 인덱스 사용했는지, 테이블 풀 스캔 했는지 등..
=> 각 테이블의 접근 방법

system

system : 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법
=> InnoDB 스토리지 엔진에선 나타나지 않음

create table tb_dual(fd1 int not null) engine=MyISAM;
insert into tb_dual values (1);

explain select * from tb_dual;

const

const : 테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 where 조건절을 가지고 있으며, 반드시 1건 반환하는 처리 방식
=> 유니크 인덱스 스캔

explain
select * from employees where emp_no=10001;

eq_ref

eq_ref : 여러 테이블이 조인되는 실행계획에서 표시
=> 처음 읽은 테이블 칼럼 값을 그 다음 읽을 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때
=> 유니크 키로 검색 시 not null이어야 함
=> 다중 칼럼 인덱스라면 모든 칼럼이 비교 조건에 사용돼야 함
=> 즉, 조인에서 두 번째 이후 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용 가능

explain
select * from dept_emp de, employees e
where e.emp_no=de.emp_no
and de.dept_no='d005';

첫 번째 라인과 두 번째 라인 id 값이 같으므로 조인으로 실행

dept_emp가 위쪽이므로 dept_emp가 드라이빙 테이블`

e.emp_no=de.emp_no 조건을 이용해 employees 테이블을 검색함
=> emp_no는 프라이머리 키이므로 eq_ref

ref

ref : 조인 순서와 관계 없이 사용되며, 프라이머리 키나 유니크 키 등의 제약조건도 없음
=> 인덱스 종류와 관계없이 동등 조건으로 검색시 사용

explain
select * from dept_emp where dept_no='d005';

dept_emp 테이블이 dept_no+emp_no의 프라이머리 키로 구성됐기 때문에 ref로 접근

const, eq_ref, ref 모두 동등 조건으로 검색돼야 함

fulltext

fulltext : 전문 검색 인덱스를 이용해 레코드를 읽는 접근 방법

explain
select *
from employee_name
where emp_no between 10001 and 10005
and match(first_name, last_name) against('Facello' in boolean mode);

ref_or_null

ref_or_null : ref방식 또는 null 비교(is null) 접근 방법

explain
select * from titles
where to_date='1985-03-01' or to_date is null;

unique_subquery

unique_subquery : 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 접근 방법 사용

explain
select * from departments
where dept_no in (select dept_no from dept_emp where emp_no=10001);

index_subquery

index_subquery : 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때

range

range : 인덱스 레인지 스캔 형태
=> 인덱스를 하나의 값이 아니라 범위로 검색하는 경우
=> <, >, is null, between, in, like 등의 연산자를 이용해 인덱스 검색시

explain
select * from employees where emp_no between 10002 and 10004;

index_merge

index_merge : 2개 이상의 인덱스를 이용해 각각 검색 결과 만든 후, 병합

index_merge 특징

• 여러 인덱스를 읽어야 해 range 보다 효율성 떨어짐
• 전문 검색 인덱스 사용 쿼리에는 index_merge 적용 X
• 항상 2개 이상의 집합이 되므로 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업 필요

-- 각각 인덱스를 이용해 조회 후 두 결과를 병합
explain
select * from employees
where emp_no between 10001 and 11000
or first_name='Smith';

index

index : 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 의미

• range나 const, ref같은 접근 방법으로 인덱스를 사용하지 못하는 경우
• 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리
• 인덱스 이용해 정렬이나 그룹핑 작업 가능한 경우

=> 1+2 또는 1+3 조건 충족시 index

explain
select * from departments
order by dept_no desc
limit 10;

ALL

all : 풀 테이블 스캔 의미

리드 어해드(Read Ahead) : 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능

6. possible_keys 칼럼

possible_keys 칼럼 : 옵티마이저가 최적의 실행계획을 만들기 위해 후보로 선정했던 인덱스 목록

7. key 칼럼

key 칼럼 : 최종 선택된 실행계획에서 사용하는 인덱스
=> type 칼럼이 ALL 일 땐, key 칼럼은 NULL로 표시

8. key_len 칼럼

key_len 칼럼 : 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려줌
=> 바이트로 알려줌

-- primary : dept_no+emp_no
explain
select * from dept_emp where dept_no='d005';

dept_no의 칼럼이 char(4) 이므로, 4*4바이트로 16바이트 표시

-- primary : dept_no+emp_no
explain
select * from dept_emp where dept_no='d005' and emp_no=10001;

emp_no의 칼럼이 integer 타입으로, 4바이트를 차지함

null 허용 칼럼에서는 칼럼의 값이 null인지 아닌지 저장하기 위해 1바이트를 추가로 더 사용

9. ref 칼럼

ref 칼럼 : equal 비교 조건으로 어떤 값이 제공됐는지 보여줌
=> 상숫값 : const
=> 다른 테이블 : 테이블명.칼럼명

func라고 표시될 때는 참조용으로 사용된 값을 그대로 사용한 것이 아니라 콜레이션 변환이나 값 자체 연산을 거쳐서 참조됐다는 것 의미

explain
select *
from employees e, dept_emp de
where e.emp_no=de.emp_no; --가공 X

explain
select *
from employees e, dept_emp de
where e.emp_no=(de.emp_no-1);

10. rows 칼럼

rows 칼럼 : 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크 해야하는지 의미

explain
select * from dept_emp where from_date>='1985-01-01';

옵티마이저는 위 쿼리를 처리하기 위해 대략 331143 건의 레코드를 읽어야 할 것이라고 예측

explain
select * from dept_emp where from_date>='2002-07-01';

옵티마이저는 위 쿼리를 처리하기 위해 292건만 읽으면 될 것이라 예측

11. filtered 칼럼

filtered 칼럼 : 필터링되고 남은 레코드의 비율

row 칼럼은 인덱스를 사용하는 조건에만 일치하는 레코드 건수

explain
select *
from employees e, salaries s
where e.first_name='Matt'
and e.hire_date between '1990-01-01' and '1991-01-01'
and s.emp_no=e.emp_no
and s.from_date between '1990-01-01' and '1991-01-01'
and s.salary between 50000 and 60000;

employees 테이블에서 인덱스 조건에만 일치하는 레코드는 약 233건이며, 이중 16.67%만 인덱스를 사용하지 못하는 e.hire_date ~~ 조건에 일치

employees 테이블에서 salaries 테이블로 조인 수행한 레코드는 233*0.1667=38건 정도임을 알 수 있음

12. Extra 칼럼

Extra 칼럼 : 내부적인 처리 알고리즘에 조금 더 깊이 있는 내용 보여줌
=> 쿼리 실행 계획에서 성능에 관련된 중요한 내용

const row not found

const 접근 방법으로 테이블을 읽었으나, 실제로 해당 테이블에 레코드가 1건도 존재X 하는 경우

Deleting all rows

where 조건절이 없는 delete 문장에서 테이블의 모든 레코드를 삭제할 때
=> 8.0 이후는 표시X
=> truncate table 권장

Distinct

explain
select distinct d.dept_no
from departments d, dept_emp de
where de.dept_no=d.dept_no;

distinct 처리를 위해서 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인

FirstMatch

FirstMatch 전략 사용시 표시

explain
select *
from employees e
where e.first_name='Matt'
and e.emp_no in(
	select t.emp_no from titles t
    where t.from_date between '1995-01-01' and '1995-01-30'
);

괄호 안의 테이블은 기준 테이블로, employees 테이블을 기준으로 titles에서 첫 번째로 일치하는 한 건만 검색

Full scan on NULL key

col1 in (select col2 from ...) 에서 col1이 null 이라면

• 서브쿼리가 1건이라도 결과 레코드를 가지면, 최종 비교 결과는 NULL
• 서브쿼리가 결과 레코드를 가지지 않으면, 최종 비교 결과는 FALSE

Full scan on NULL key : 서브쿼리 테이블에 대해서 풀 테이블 스캔을 사용할 것을 알려주는 키워드
=> col1이 NULL일 때

explain
select d1.dept_no, 
	null in (select d2.dept_name from departments d2) 
from departments d1;

Impossible HAVING

Impossible HAVING : HAVING절의 조건을 만족하는 레코드가 없을 때

explain
select e.emp_no, count(*) as cnt
from employees e
where e.emp_no=10001
group by e.emp_no
having e.emp_no is null;

emp_no는 프라이머리 키이므로 위의 having 조건을 만족할 가능성이 없다.

Impossible WHERE

Impossible WHERE : where 조건이 항상 FALSE가 될 수 밖에 없는 경우

explain
select * from employees
where emp_no is null;

LooseScan

LooseScan 최적화 전략 사용시

explain
select * from departments d
where d.dept_no in (select de.dept_no from dept_emp de);

No matching min/max row

Min()이나 Max() 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때
=> 결과는 NULL

explain
select min(dept_no), max(dept_no)
from dept_emp
where dept_no='';

no matching row in const table

조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드 X시

explain
select *
from dept_emp de,
	(select emp_no from employees where emp_no=0) tb1
where tb1.emp_no=de.emp_no
and de.dept_no='d005';

No matching rows after partition pruning

파티션된 테이블에 대한 update 또는 delete 대상 레코드가 없을 때

explain
delete from employees_2
where hire_date>='2024-01-01';

파티션 정의시 2006-01-01 이전까지만 정의돼 있으므로, 위 경우는 대상 파티션이 없다는 것을 의미

No tables used

from절이 없는 쿼리 문장이나 from dual 같은 쿼리 실행 계획에서 출력

explain select 1;

Not exists

아우터 조인을 이용해 안티-조인을 수행하는 쿼리에서 나타남

not in 또는 not exists를 아우터 조인(left outer join)을 이용해 구현 가능

-- 일반 조인을 했을 때 나오지 않는 결과만 가져옴
explain
select *
from dept_emp de
left join departments d on de.dept_no=d.dept_no
where d.dept_no is null;

Plan isn't ready yet

다른 커넥션에서 실행 중인 쿼리의 실행 계획을 볼 수 있는데, Plan isn't ready yet은 해당 커넥션에서 아직 쿼리 실행 계획을 수립하지 못한 상태에서 explain for connection 명령이 실행된 것

-- 다른 커넥션 실행계획
explain for connection id(프로세스번호);

--ex
explain for connection 8;

Range checked for each record(index map: N)

explain
select *
from employees e1, employees e2
where e2.emp_no>=e1.emp_no;

e2.emp_no>=e1.emp_no 조건을 만족하는 레코드를 찾을 때, e1.emp_no=1이라면 e2 테이블의 1억건을 모두 읽어야 하지만, e1.emp_no=100000000 인 경우 e2 테이블을 한 건만 읽으면 된다.

위 내용을 토대로, e1 테이블의 emp_no가 작을 땐 e2 테이블을 풀 테이블 스캔하고, e1 테이블의 emp_no가 클 땐 e2 테이블을 인덱스 레인지 스캔으로 접근하면 됨

Range checked for each record(index map: N) : 레코드마다 인덱스 레인지 스캔을 체크한다

index map:0x1에서 16진수로 표시되는데, 해당 문장은 employees의 첫 번째 인덱스를 사용할지 아니면 풀 스캔할지를 결정하는 것이다.

Recursive

CTE(Common Table Expression)을 이용해 재귀 쿼리를 작성할 수 있음

explain
with recursive cte (n) as
(
	select 1
    union all
    select n+1 from cte where n<5
)
select * from cte;

CTE를 이용한 재귀 쿼리의 실행 계획에 recursive 표시

Rematerialize

래터럴 조인(Lateral join) : from절의 서브쿼리에서 외부쿼리 참조 가능
=> 래터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장
=> Rematerializing

explain
select * from employees e
left join lateral ( select *
	from salaries s
    where s.emp_no=e.emp_no
    order by s.from_date desc limit 2) s2
on s2.emp_no=e.emp_no
where e.first_name='Matt';

위 실행계획에선 employees 테이블의 레코드마다 salaries 테이블에서 emp_no가 일치하는 레코드 중 from_date칼럼 역순으로 2건만 가져와 임시 테이블 dervied2로 저장

employees 테이블과 derived2 테이블을 조인

derived2 임시 테이블은 employees 테이블 레코드 마다 새로 내부 임시 테이블이 생성
=> 매번 임시 테이블이 새로 생성되는 경우 Rematerialize 문구 표시

Select tables optimized away

MIN() 또는 MAX()만 select절에 사용되거나, group by로 min(), max()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화 적용시

explain
select max(emp_no), min(emp_no)
from employees;

Start temporary, End temporary

Duplicate Weed-out 최적화 전략 사용시

explain
select * from employees e
where e.emp_no in (select s.emp_no from salaries s where s.salary>150000);

불필요한 중복 제거를 위해 내부 임시 테이블을 사용하는데 조인의 첫 번째 테이블에 start, 끝에 End 표시

unique row not found

두 개의 테이블이 각각 유니크 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드 존재 X시

create table tb_test1(fdpk int, primary key(fdpk));
create table tb_test2(fdpk int, primary key(fdpk));

insert into tb_test1 values(1),(2);
insert into tb_test2 values(1);

explain
select t1.fdpk
from tb_test1 t1 left join tb_test2 t2
on t2.fdpk=t1.fdpk
where t1.fdpk=2;

Using filesort

order by 처리가 인덱스를 사용하지 못할 때

explain
select * from employees
order by last_name desc;

Using index(커버링 인덱스)

데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리 처리 가능할 때

explain
select first_name
from employees
where first_name between 'Babette' and 'Gad';

ix_firstname 인덱스가 있으므로, 인덱스만 읽고 데이터 파일을 읽어 올 필요가 없음

커버링 인덱스 : 인덱스만으로 처리되는 것

Using index condition

인덱스 컨디션 푸시 다운 최적화 사용시

explain
select * from employees
where last_name='Acton'
and first_name like '%sal';

Using index for group-by

group by 처리가 인덱스를 이용할 때, 루스 인덱스 스캔을 이용하는 경우
=> 단순히 인덱스를 순서대로 쭉 읽는 것과, 인덱스의 필요한 부분만 듬성듬성 읽는 스캔

타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리

avg(), sum() 등 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 필요한 레코드만 듬성듬성 읽을 수 없어, 인덱스를 사용하긴 하지만 루스 인덱스 스캔은 아님
=> Using index for group-by 출력 X

루스 인덱스 스캔을 통한 GROUP BY 처리

단일 칼럼으로 구성된 인덱스 : 그루핑 칼럼 말고는 아무것도 조회하지 않는 쿼리
다중 칼럼으로 만들어진 인덱스 : group by 절이 인덱스 사용 가능하며, min()이나 max() 처럼 조회하는 값이 첫 번째 또는 마지막만 읽어도 되는 쿼리

-- index : emp_no+from_date

explain
select emp_no, min(from_date)
from salaries
group by emp_no;

• where 조건절이 없는 경우 : group by절의 칼럼과 select로 가져오는 칼럼이 루스 인덱스 스캔 사용 조건을 갖추면 됨
• where 조건절이 있으나 검색을 위해 인덱스 사용 불가한 경우 : group by를 위해 인덱스를 읽은 후 where 조건 비교를 위해 데이터 레코드를 읽으므로 루스 인덱스 스캔 이용 불가
• where절 조건이 있고 검색을 위해 인덱스 사용시 : 단 하나의 인덱스만 사용할 수 있으므로 where 조건절과 group by가 같은 인덱스를 사용해야만 루스 인덱스 스캔 가능

Using index for skip scan

인덱스 스킵 스캔 최적화 사용시

-- index : gender+birth_date
explain
select gender, birth_date
from employees
where birth_date>='1965-02-01';

Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)

드리븐 테이블에 인덱스가 없으면, 성능에 미치는 영향이 크며 MySQL 서버는 블록 네스티드 루프 조인이나 해시 조인을 사용

해시 조인 사용시 조인 버퍼를 사용하는데, 조인 버퍼가 사용되는 실행 계획에 Using join buffer 표시

-- 카타시안 조인은 항상 조인 버퍼 사용

explain
select *
from dept_emp de, employees e
where de.from_date>'2005-01-01'
and e.emp_no<10904;

Using MRR

MRR(Multi Range Read) : 여러 개의 키 값을 한 번에 스토리지 엔진으로 전달하고, 스토리지 엔진은 넘겨받은 키 값들을 정렬해서 최소한의 페이지 접근만으로 필요한 레코드 읽음

explain
select /*+ join_order(s, e) */ *
from employees e, salaries s
where e.first_name='Matt'
and e.hire_date between '1990-01-01' and '1991-01-01'
and s.emp_no=e.emp_no
and s.from_date between '1990-01-01' and '1991-01-01'
and s.salary between 50000 and 60000;

Using sort_union(...), Using union(...), Using intersect(...)

index_merge로 두 개의 인덱스를 사용했을 때 결과를 어떻게 병합했는지 나타냄

• Using sort_union(...) : Using Union과 비슷하나, or로 연결된 상대적으로 대량의 조건들이 이 방식으로 처리
  => 프라이머리 키만 먼저 읽어서 정렬하고 병합한 후 레코드를 읽어서 반환

Using temporary

임시 테이블이 사용된 경우

explain
select gender
from employees
group by gender
order by min(emp_no);

• from절에 사용된 서브쿼리
• count(distinct col1) 에서 인덱스 사용 불가시
• union이 사용된 쿼리
• 인덱스를 사용하지 못하는 정렬 작업

Using where

스토리지 엔진 : 디스크나 메모리 상에서 필요한 레코드를 읽거나 저장

MySQL 엔진 : 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산

Using where : MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우

explain
select *
from employees
where emp_no between 10001 and 10100
and gender='F';

emp_no가 작업 범위 결정 조건이고, gneder가 체크 조건(필터링 조건)

emp_no 조건 만족하는 레코드는 100건이지만, 두 조건 모두 만족하는 레코드는 37건이다.

즉, Using where은 63건의 레코드를 버리는 처리를 의미

Zero limit

데이터 값이 아닌 메타 데이터만 필요한 경우 limit 0을 사용하면 되는데 이때 Zero limit 출력

explain
select * from employees limit 0;