2022/11/09 ~ 2022/11/15

danbi5228 commented 1 year ago

다음 스터디

2022-11-15 pm 10:20

학습 범위

10.3.12.11 ~ 10.3.12.16

정리 범위

1: 10.3.12.11 ~ 10.3.12.13
2: 10.3.12.14 ~ 10.3.12.16

danbi5228 commented 1 year ago

10.3.12.11 No matching rows after partition pruning

파티션된 테이블에 대한 UPDATE 또는 DELETE 명령의 실행계획에서 표시됨
- 해당 파티션에서 UPDATE 하거나 DELETE할 대상 레코드가 없을 때 표시됨
단순히 삭제할 레코드가 없음을 의미하는 것이 아니라 대상 파티션이 없다는 것을 의미
- p.463 ~ 464: 조건을 만족하는 파티션 자체가 없는 경우에는 이 표시가 되지만, 조건을 만족하는 파티션이 있지만 실제 삭제할 레코드가 없는 경우에는 이 표시가 되지 않음

10.3.12.12 No tables used

FROM 절이 없는 쿼리 문장이나 "FROM DUAL" 형태의 쿼리 실행 계획에서 표시됨
ex) EXPLAIN SELECT 1 , EXPLAIN SELECT 1 FROM DUAL

10.3.12.13 Not exists

MySQL 내부적으로 아우터 조인을 이용해서 안티-조인을 수행하는 경우에 최적화가 진행된 방식의 이름이 Not exists 이고, 이 경우에 쿼리 실행 계획에서 표시됨
- 조인 조건에 일치하는 레코드가 여러 건이 있다고 하더라도 딱 1건 만 조회해보고 처리를 완료하는 최적화를 의미함
안티-조인 (Anti-JOIN): A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 하는 경우에 사용되는 NOT IN(subquery)의 형태나 NOT EXIST 등의 연산자를 사용하는 형태의 조인
- 똑같은 처리를 아우터 조인 (LEFT OUTER JOIN)을 이용해서도 구현할 수 있다
- 일반적으로는 안티-조인으로 처리해야 하지만 레코드 건수가 많을 경우에는 아우터 조인이 더 빠른 결과를 낼 수 있다
참고링크; https://velog.io/@jsj3282/26.-MySQL-실행-계획-실행-계획-분석4

mojh7 commented 1 year ago

10.3.12.14 Plan isn't ready yet

다른 커넥션에서 실행 중인 쿼리의 실행 계획을 살펴볼 수 있다

SHOW PROCESSLIST; // 이걸로 쿼리 실행 중인 프로세스 id 확인

// 실제 커넥션 id(프로세스 번호)가 8인 커넥션에서 실행하고 있는 쿼리의 실행 계획 살펴보기
EXPLAIN FOR CONNECTION 8;

해당 명령은 옵티마이저가 의도된 인덱스를 사용하지 못해서 풀 스캔을 한다거나 잘못된 실행 계획을 선택한 것이 아닌지 확인할 때 유용하게 사용할 수 있는 명령이다

이 명령을 사용했을 때 Plan is not ready yet 메시지가 표시될 때는

해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못한 상태에서 EXPLAIN FOR CONNECTION 명령이 실행된 것을 의미한다

실행 계획을 수립할 여유 시간을 좀 더 주고, 다시 명령을 실행하면 된다

10.3.12.15 Range checked for each record(index map:N)

두 개의 테이블을 조인하는 쿼리에서 조인 조건에 상수가 없고 둘다 변수(e1.emp_no, e2.emp_no)인 경우

옵티마이저는 e1 테이블을 먼저 읽고 조인을 위해 e2을 읽을 때 인덱스 레인지 스캔과 풀 테이블 스캔 중에서 어느 것이 효율적일지 판단할 수 없다

e1 테이블의 레코드를 하나씩 읽을 때마다 e1.emp_no 값이 계속 바뀌므로 쿼리의 비용 계산을 위한 기준 값이 계속 변하는 것이고 어떤 접근 방법으로 e2 테이블을 읽는 것이 좋을지 판단할 수 없다

EXPLAIN
SELECT *
FROM employees e1, employees e2
WHERE e2.emp_no >= e1.emp_no;

만약 사번이 1~1억 까지 있다고 가정하면 e1.emp_no=1 인 경우에는 e2 테이블을 1억 건 전부를 읽어야 하고, e1이 1억이면 e2 테이블을 한 건만 읽으면 된다

즉, e1 테이블의 emp_no가 작을 때는 e2 테이블을 풀 테이블 스캔, e1테이블의 emp_no가 큰 값이면 e2 테이블을 인덱스 레인지 스캔으로 접근하는 형태를 수행하는 것이 최적의 조인 방법이다

이를 줄여서 표현하면 레코드마다 인덱스 레인지 스캔을 체크한다 라고 할 수 있다

이 것이 Extra 칼럼에 표시되는 Range checked for each record의 의미이다

Extra 칼럼을 자세히보면 (index map: 0x1)은 사용할지 말지를 판단하는 후보 인덱스의 순번을 나타내고 16진수로 표시된다.

0x1은 이진수로 1이고 의미는 e2 테이블의 첫 번째 인덱스를 사용할지 아니면 테이블을 풀 스캔할지를 매 레코드 단위로 결정하면서 처리된다

첫번 째 인덱스란 SHOW CREATE TABLE employees명령으로 테이블의 구조를 조회했을 때 제일 먼저 출력되는 인덱스를 의미

type이 ALL로 표시되어 있는데 index map에 표시된 후보 인덱스를 사용할지 여부를 검토하고 도움이 되지 않아 최종적으로 풀 테이블 스캔을 사용하기 때문에 ALL로 표시된 것

469p에서 index map: 0x19 -> 이진수로 11001 이고 표에서 1인 값에 해당하는 인덱스 pk, gender, phone를 사용 가능한 인덱스 후보로 선정했음을 의미한다

각 레코드 단위로 이 후보 인덱스 가운데 어떤 인덱스를 사용할지 결정하게 되나, 실제로 어떤 인덱스가 사용됐는지 알 수 없다.

10.3.12.16 Recursive

8.0 버전부터 CTE(Common Table Expression)을 이용해 재귀 쿼리를 작성할 수 있게 됐다

WITH RECURSIVE cte (n) AS
(
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE < 5
)
SELECT * FROM cte;

n 이라는 칼럼 하나를 가진 cte라는 이름의 내부 임시 테이블 생성
n 칼럼의 값이 1부터 5까지 1씩 증가하게 해서 레코드 5건을 만들어서 cte 내부 임시 테이블에 저장

WITH 구문을 이용한 CTE가 사용 됐다고 해서 꼭 recursive로 표시되지는 않는다

WITH 구문이 재귀 CTE로 사용될 경우만 표시

mojh7 / real-mysql-study