rows
- 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줌(index를 사용하는 조건에만 일치하는 레코드)
- 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해낸 값(정확X)
- 반환하는 레코드 값이 아니라 쿼리 처리를 위해 읽고 체크한 레코드의 값을 의미
옵티마이저가 예측한 값이 틀릴 가능성이 높다.
가끔 인덱스되지 않은 컬럼이나 컬럼의 값이 균등하게 분포되지 않은 경우에도 제대로 된 예측을 못할 수 있음
이런 경우 '히스토그램'을 사용filtered
- 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줌(index를 사용하는 조건에만 일치하는 레코드)
SELECT ..
WHERE e.first_name = 'Matt' (1)
AND s.hire_date BETWEEN `1991-01-01` AND `1991-01-01` (2)
AND s.emp_no = e.emp_no
AND s.from_date BETWEEN '1990-01-01' AND '1991-01-01' (3)
AND s.salary BETWEEN 50000 AND 60000; (4)위의 쿼리에서 index를 사용가능한 조건은 e.first_name = 'Matt', AND s.salary BETWEEN 50000 AND 60000 (1),(2) 2가지 입니다.
이제 index를 사용할 수 없는 조건까지 합쳐 결과적으로 조회한는 레코드가 적은 테이블을 드라이빙 테이블로 선정
만약 e table의 실행 계획 rows 233, filtered 16.03이라면
e table의 조회 레코드에서 index가 사용된 컬럼 233개, 사용되지 않은 컬럼 37개(233 x 0.1603)
정리
1차(Index를 사용한) 값 Matt라는 이름의 컬럼 233건에서
2차() 값 1991-01-01 AND 1991-01-01 컬럼은 37개(233 x 0.16)개가 존재한다.
해당 37개의 레코드는 s table과 join
filterd 수치가 높은 것은 index가 걸리지 않은 조건이 많다는 뜻 = 임시 테이블을 만들어야 함
드라이빙 테이블 선택 조건은 임시 테이블이 생성하지 않는 방향으로 할 가능성이 있음
AND 연산이기 때문에 rows * filtered 공식인 것 같다.
OR 연산이면 결과의 크기는 최소 rows보다 더 큰 것이 아닐까?
Extrea
- 성능과 관련된 주요한 내용이 표시(보통 2~3개씩 함께)
- MySQL버전 업그레이드로 내용이 추가될 수 있음
const row not found
const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않음
Deleting All rows
- MyISAM Engine같이 핸들러 차원에서 테이블 레코드 삭제 기능을 제공하는 스토리지 엔진에서 나타나는 문구
- 테이블의 모든 레코드를 삭제하는 핸들러 기능(API)을 한번 호출함으로써 처리되었다는 것을 의미
- 기존에는 삭제하는 레코드의 수 만큼 스토리지 엔진의 핸들러 함수를 호출하였으나 한번의 호출로 처리되었다는 것을 의미
8.0 부터는 where절 없는 delete보다 truncate table을 권장
Distinct
SELECT DISTINCT d.dept_no가 있다면 테이블 조인 시에 on절에 부합하는 레코드 1개만 발견하고 넘어감
First Match
First Match 전략이 사용되면 노출, 테이블에서 첫 번째로 일치하는 한 건만 검색한다는 것을 의미
FristMatch 전략 : 세미 조인 최적화 전략, Exists형태로 튜닝하는 것
Full scan on NULL Key
차선책으로 Full Scan을 할 것이라는 알림
col1 IN (SELECT col2 FROM ..)과 같은 쿼리에서 col1이 NULL 이라면 NULL IN (SELECT col2 FROM..) 과 같음
비교 과정에서 col1이 Null이면 서브쿼리에 사용된 테이블에 대해서 풀 테이블 스캔 필요
col1이 Not Null이라면 Full scan on Null Key가 출력되지 않음
Full scan on Null Key가 출력되어도 실제 값에 Null이 없다면
풀 테이블 스캔이 일어나지 않음
하지만 Null이 존재하고 서브쿼리에 개별적으로 WHERE 조건이 있다면 상당한 성능 문제가 발생한 가능성 존재
- 인덱스 컬럼은
Null도 정렬이 될텐데.. 왜?Full Scan이 차선책인가?
impossible WHERE
HAVING 절의 조건을 만족하는 레코드가 없을 때 노출, 1가지 예로 NOT NULL 컬럼에 IS NULL 조건을 거는 경우
(쿼리 다시 점검 필요)
LooseScan
세미 조인 최적화 LooseScan 최적화 전략이 사용
loose scan 참고
https://velog.io/@kma95278/Index-Scan
No matching min/max row
min(), max()와 같은 집합 함수 쿼리의 조건절에 일치하는 레코드가 1건도 없는 경우 노출, 결과로 NULL 반환
No matching row in const table
조인에 사용된 테이블에서 const방법으로 접근할 때 일치하는 레코드가 없다면 출력
No matching rows after partition pruning
파티션된 테이블에 대하여 UPDATE or DELETE 명령에서 표시
만약 날짜 기준으로 1991, 1996, 2001, 2006 5년 단위로 partition이 되었을 때 2020년 이상 레코드에 대하여 UPDATE or DELETE 쿼리 실행 시 발생
삭제할 레코드가 없는 것이 아니라 삭제할 대상 파티션이 없다는 것을 의미
삭제 할 레코드는 없지만 대상 파티션이 존재하는 경우에는 출력 X
No tables used
FROM절이 없는 쿼리 문장이나 FROM DUAL형태의 쿼리 실행 시 출력
DUAL은 상수 테이블을 의미한다.
- 컬럼과 레코드를 각각 1개씩만 가지는 가상의 테이블
- dual 은 펑션이나 계산식을 테이블 생성없이 수행해 보기 위한 용도
Not Exists
A 테이블에는 존재하고 B 테이블에는 존재하지 않는 것을 조회하는 쿼리(outer join)에서 사용, 일반적으로 NOT IN, NOT EXISTS를 사용하지만 레코드가 많으면 OUTER JOIN이 성능에 유리
아래와 같은 느낌, B 레코드가 NULL인 A 레코드를 찾는다.
FROM A
LEFT JOIN B
ON A.id = B.a_id
WHERE B.a_id IS NULLNot Exists가 사용되면 옵티마이저가 A에만 존재하는지만 검사
A와 매칭되는 B가 2건 이상 있더라도 1건만 보고 처리를 종료
이게 inner join 아닌가?
Plan isn't ready yet
아직 옵티마이저가 실행 계획을 수립하지 못한 경우 노출
1번에서 ProcessId를 획득한 후 2번 [PROCESSID]에 넣고 실행
1) SHOW PROCESSLIST;
2) EXPLAIN FOR CONNECTION [RPOCESSID];이 때 Extra 값을 확인하며 Plain isn't pready yet을 확인이 가능
Range checked for each record(index map:N)
조인을 할 때 2개의 조인 조건이 상수가 아니라 변수일 때 옵티마이저는
인덱스 레인지 스캔, 풀 테이블 스캔 어떤 것이 효율적인지 판단할 수 없음
FROM employee e1, employee e2
WHERE e2.emp_no >= e1.emp.no;사번이 1억 번까지 있고 e1의 emp_no이 1인 경우는 1억 번을 전부 읽지만
e1의 emp_no이 1억이라면 1번만 테이블을 읽어도 됩니다.
"레코드마다 인덱스 레인지 스캔을 체크한다." 이러한 경우 "Range checked for each record" 노출
?: 레코드마다 다른 인덱스를 사용하는 것인가?
많이 읽어야 한다면 풀 테이블 스캔이 유리, 조금 읽어야 한다면 인덱스 테이블 스캔이 유리
index map: 0x1은 어떤 인덱스를 후보로 두었는지에 대한 값
2진수로 변환했을 때 비트맵의 각 자릿수는 CREATE TABLE table_name에서 나열된 인덱스 그대로 반영함
'1'이라면 후보, '0'이라면 후보 탈락을 의미
실행 계획에 type All로 노출되지만 무조건 풀 테이블 스캔을 사용하는 것이 아니라 인덱스 사용 여부 검토 후 도움이 되지 않는다면 사용한다는 의미로 ALL이 노출
Recursive
8.0부터 CTE(Common Table Expression)을 이용해 재귀 쿼리를 작성할 수 있다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;"( )"안에서 만든 결과로 내부 임시 테이블을 생성
그리고 그다음 SELECT 쿼리에서 WITH RECURSIVE절에서 생성된 내부 임시 테이블을 풀 스캔해서 결과 반환
CTE를 사용한 재귀 쿼리의 실행 계획은 "RECURSIVE"로 노출
Rematerialize
8.0부터 래터럴 조인(LATERAL JOIN)이 추가되었으며, 이 경우 래터럴로 조인되는 경우 선행 테이블의 레코드 별로 서브 쿼리가 실행해서 결과를 임시 테이블에 저장
SELECT
...
employee 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_noemployee 테이블의 레코드 1개 마다
(1) s.emp_no = e.emp_no [emp_no이 일치하는 레코드]
(2) ORDER BY s.from_date DESC [form_date 역순]
(3) LIMIT 2 [2건만]
를 처리하여 임시 테이블을 만든다.
결론 : 테이블의 레코드마다 내부 임시 테이블이 생성
Select tables optimized away
- MIN(), MAX()만 SELECT 절에 사용
- GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태로 최적화
emp_no에 index가 있다고 가정 'optimized way' 최적화가 가능
SELECT MAX(emp_no), MIN(emp_no) FROM emplyoees;
emp_no, from_date 순으로 index가 있다고 가정, 역시 'optimized way'최적화가 가능
SELECT MAX(from_date), MIN(from_date) FROM salaries WHERE emp_no = 10002;
Select temporary, End temporary
Duplicate Weed-out 최적화를 사용하면 'Start temporary','End temporary'가 표시
임시 테이블에 들어가는 테이블의 시작과 끝에 'Start ..' ~ 'End ..'가 표시됩니다.
Duplicate Weed-out
Duplicate Weed-out : Join 중복 제거를 위해 임시 테이블 사용
https://mariadb.com/kb/en/duplicateweedout-strategy/
Unique row not found
두 테이블이 유니크(프라이머리 포함)컬럼으로 조인할 때 일치하는 레코드가 없을 때 표시
Using filesort
Order By를 사용하는 정렬에서 적절한 index를 사용하지 못한 경우 표시,
정렬용 메모리 버퍼에 레코드를 복사해 퀵/힙 소트로 정렬을 수행
Using filesort가 출력된 쿼리가 많이 느리다면 쿼리 튜닝이나 인덱스를 생성하는 것이 좋음
Using index(커버링 인덱스)
데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있다면 표시
! 가장 큰 부하를 차지하는 부분은 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업
최악의 경우 한 건 한 건마다 IO요청이 생길 수 있다 !
이미지에서는 BookID로 정렬된 INDEX가 존재하며 만약 쿼리가 SELECT book_id, name .. 이라면 INDEX Table에 존재하지 않는 name 컬럼 조회를 위해 IO가 생김
INDEX Table의 값만 이용해서 조회시 훨씬 빠르게 조회가 가능함
타겟 index나 clustering table의 PK가 secondary index table에 포함
접근 방법(type column)이 eq_ref, ref, range, index_merge_index등과 같이 인덱스를 사용하는방식 모두 'Using index'가 표시될 수 있음 인덱스 레인지 스캔[eq_ref, ref, range, index_merge]을 사용할 때만 커버링 인덱스로 처리되는 것은 아님
https://blog.quest.com/sql-server-indexes-key-requirements-performance-impacts-and-considerations/s
Using index condition
Index condition pushdown을 사용 시 표시
Using index for group-by
group by에서 Index가 사용되면 표시
기본적으로 MySQL은 group by 처리를 위해 (1)그루핑 기준 컬럼을 이용해 정렬 작업 수행
그리고 (2)다시 그루핑 하는 형태
2가지 케이스
- group by 컬럼이 Index 적용 컬럼이라면 (1)번 작업은 무시하고 바로 (2)를 수행 (이미 정렬)
- Index 미적용 컬럼이라면 (1)도 수행 (임시 테이블 사용)
타이트 인덱스 스캔(인덱스 스캔)을 통한 group by 처리
인덱스를 이용해 GROUP BY를 처리할 수 있더라도 AVG(), COUNT() 같이 모든 값이 필요한 경우 루스 인덱스 스캔을 사용할 수 없다.
SELECT first_name, COUNT(*) AS counter
FROM employees GROUP BY first_name;
루스 인덱스 스캔을 통한 GROUP BY 처리
Grouy By + 루스 인덱스 스캔 조건
단일 컬럼 : 그루핑 컬럼말고는 아무것도 조회하지 않는 경우
다중 컬럼 : group by절이 인덱스를 사용할 수 있어야 하고 MIN(), MAX()같이 조회하는 값이 첫 번째 혹은 마지막 레코드만 읽어도 되는 경우
index(emp_no, from_date) 설정
SELECT emp_no, MIN(from_date), MAX(from_date)
FROM salaries
GROUP BY emp_no;emp_no 그룹별로 처음 값, 마지막 값만 듬성 듬성 읽으면서 진행이 가능하다.
where절이 있다면 조건이 또 추가된다!
- WHERE 조건절이 없는 경우
위에서 나온 조건만 만족하기, 그렇지 못하면 타이트 인덱스 스캔이나 별도의 정렬이 수행 - WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못하는 경우
group by 절은 인덱스가 사용할 수 지만 where절은 사용하지 못하는 경우
타이트 인덱스 스캔 과정을 통해 group by가 처리
group by를 위해 인덱스를 스캔 -> where 조건 처리로 진행 - WHERE 절의 조건이 있고, 검색을 위해 인덱스를 사용하는 경우
where절과 group by절이 공통의 인덱스를 사용해야 루스 인덱스 스캔을 사용할 수 있음
where절과 group by절의 인덱스가 다른 경우 옵티마이저는 일반적으로 where절의 인덱스를 선택하는 경향이 있음
(작업 범위를 좁히지 못하는 인덱스라도 where절이 먼저 인덱스를 사용할 수 있게 실행해주기도 함)where, group by절 동일한 인덱스를 사용해도 루스 인덱스 스캔을 사용하지 않는 경우가 존재
where 조건에 의해 검색되는 레코드가 적으면 루스 인덱스 스캔을 사용하지 않아도 매우 빠름
루스 인덱스 스캔은 대량의 레코드를 group by할 때 효과가 좋다!
같은 인덱스를 사용하지만 where 조건에 의해 검색되는 레코가 매우 작으면
Using index for group-by가 extra에서 사라짐
WHERE emp_no BETWEEN 10001 AND 10009
GROUP BY emp_no;
Using index for skip scan
MySQL 옵티마이저가 인덱스 스킵 스캔 최적화를 사용하면 표시
8.0부터
루스 인덱스 스캔을 확장한 인덱스 스킵 스캔이 도입
Using join buffer(Block Nested Loop), Using join buffer(batched key access), using join buffer(hash join)
- 일반적으로 빠른 쿼리 실행을 위해 조인되는 컬럼은 인덱스 생성
- 조인에 필요한 인덱스는 조인에서 뒤에 읽는 테이블의 컬럼에만 필요
- 옵티마이저는 조인에서 인덱스가 없는 테이블을 먼저 읽는다.
- 뒤에서 읽는 테이블은 검색 위주로 사용(인덱스가 없으면 성능의 영향이 매우 큼)
- 드리븐 테이블에 적절한 인덱스가 없다면 block nested loop join, hash join 사용(join buffer 사용)
인덱스가 적절하게 준비돼 있다면 괜찮다.
카테시안 조인은 항상 join buffer를 사용
Using MRR(Multi Range Read)
MySQL 엔진은 실행 계획을 수립하고 스토리지 엔진 API를 호출
스토리지 엔진 레벨에서는 쿼리 실행의 전체적인 문맥을 모름
문제 : 여러 건의 레코드라도 스토리지 엔진은 한 건 한 건 읽어서 반환
MRR : MySQL엔진이 여러 개의 키 값을 한 번에 스토리지 엔진에 전달하고 넘겨받은 키 값들을 정렬해서 최소한의 접근만으로 최적화
Extra에 Using MRR 표시
정확한 내용은 MRR & Batched key access 참조
Using sort_union, Using union, Using intersect
쿼리가 index_merge 접근 방법으로 실행되는 경우에는 2개 이상의 인덱스가 동시에 사용될 수 있음
intersect : 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 처리 결과에서 교집합으로 추출해내는 작업 수행
union : OR로 연결된 경우 합집합을 추출해내는 작업 수행
sort_union : ??? 모르겠다.
Using temporary
임시 테이블을 사용했다는 의미 하지만 메모리, 디스크 어디에 생성됐는지 알 수 없다.
실행 계획에서는 표시되지 않지만 내부적으로 사용할 때가 많음
대표적으로 메모리, 디스크에 임시테이블을 생성하는 쿼리
- FROM절에 서브쿼리가 포함되는 쿼리의 경우
- COUNT(DISTINCT col)를 포함하는 쿼리가 인덱스를 사용할 수 없는 경우
- UNION, UNION DISTINCT를 사용하는 경우 (UNION ALL 제외)
- 인덱스를 사용하지 못하는 정렬 작업의 경우는 임시 버퍼를 사용하짐나 정렬해야 할 레코드가 많다면 디스크를 사용(Using filesort 출력)
Using where
MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우 표시
예로 들어
WHERE emp_no BETWEEN 10001 AND 10100
AND gender='F'Using where은 BETWEEN 조건 100건의 컬럼을 넘겨서
MySQL 엔진이 gender = F조건을 필터링해서 부합하지 않는 조건에 대하여 버리는 것을 의미
하지만 실제로 왜 'Using where'이 왜 생겼는지 이해할 수 없을 때도 많음.(당근마켓 DBA도 모른다. 알려하지 말자)
emp_no, gender(where절의 index)로 인덱스가 준비돼 있다면 버리는 레코코드(filtered)없이 필요한 레코드만 읽을 수 있음
Zero limit
때로는 데이터가 아닌 메타 데이터가 필요함
쿼리의 결과가 몇개의 데이터를 가지는지, 컬럼의 타입은 무엇인지 정보만 필요한 경우 "LIMIT 0 "을 사용하면 옵티마이저가 의도를 알고 메타 정보만 반환 이러한 경우 표시
