Extra 칼럼

• 쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시된다.
  • 주로 내부적인 처리 알고리즘에 대해 조금 더 깊이 있는 내용을 보여주는 경우가 많다.
  • MySQL 서버의 버전이 업그레이드되고 최적화 기능이 도입될 수록 새로운 내용이 더 추가될 것으로 보인다.
• Extra 칼럼에는 고정된 몇 개의 문장이 표시된다.
  • 일반적으로 2~3개씩 함께 표시된다.

Extra 칼럼에 표시될 수 있는 문장

여기서 설명하는 순서는 성능과는 무관하다.

const row not found

• 쿼리의 실행 계획에서 const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않을 경우 표시된다.
• 이 메시지가 표시되는 경우 테이블에 적절히 테스트용 데이터를 저장하고 다시 한 번 쿼리의 실행 계획을 확인해 보는 것이 좋다.

Deleting all rows

• MyISAM 스토리지 엔진과 같이 스토리지 엔진의 핸들러 차원에서 테이블의 모든 레코드를 삭제하는 기능을 제공하는 스토리지 엔진 테이블인 경우 이 문구가 표시된다.
• 테이블의 모든 레코드를 삭제하는 핸들러 기능(AP)을 한 번 호출함으로써 처리됐다는 것을 의미한다.
  • 기존에는 테이블의 레코드를 삭제하기 위해 각 스토리지 엔진의 핸들러 함수를 레코드 건수만큼 호출해서 삭제해야 했다.
  • “Deleting all rows” 처리 방식은 한 번의 핸들러 함수 호출로 아주 간단하고 빠르게 처리할 수 있다.
• WHERE 조건절이 없는 DELETE 문장의 실행 계획에서 자주 표시된다.
• MySQL 8.0 버전에서는 InnoDB 스토리지 엔진과 MyISAM 스토리지 엔진 모두 더 이상 실행 계획에 “Deleting all rows” 최적화는 표시되지 않는다.
  • 테이블의 모든 레코드를 삭제하고자 한다면 TRUNCATE TABLE 명령을 사용할 것을 권장한다.

Distinct

• SELECT 문에 DISTINCT 명령어를 사용했을 경우, 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인한다.

FirstMatch

• 세미 조인의 여러 최적화 중 FirstMatch 전략이 사용되면 이 문구가 출력된다.
• FirstMatch 메시지에 기준 테이블 명이 함께 표시된다.
  • FirstMatch(테이블명)

Full scan on NULL key

• col1 IN (SELECT col2 FROM ...) 과 같은 조건을 가진 쿼리에서 자주 발생한다.
• SQL 표준에서는 NULL을 “알 수 없는 값”으로 정의하며, NULL에 대한 연산의 규칙도 정의하고 있다.
  • 서브쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
  • 서브쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE
• Full scan on NULL key 는 MySQL 서버가 쿼리를 실행하는 중 col1 이 NULL을 만나면 차선책으로 서브쿼리 테이블에 대해서 풀 테이블 스캔을 사용할 것이라는 사실을 알려주는 키워드이다.
  • col1 이 NOT NULL로 정의되었다면 이러한 차선책은 사용되지 않는다.
• 이 문구가 표시되었더라도 IN 이나 NOT IN 연산자의 왼쪽에 있는 값이 실제로 NULL이 없다면 풀 테이블 스캔은 발생하지 않으므로 걱정하지 않아도 된다.

Impossible HAVING

• 쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때 이 문구가 표시된다.
• 쿼리가 제대로 작성되지 못한 경우가 대부분이므로 쿼리의 내용을 다시 점검하는 것이 좋다.

Impossible WHERE

• WHERE 조건이 항상 FALSE가 될 수밖에 없는 경우 이 문구가 표시된다.
• 불가능한 WHERE 조건을 의미한다.

LooseScan

• 세미 조인 최적화 중에서 LooseScan 최적화 전략이 사용되면 이 문구가 표시된다.

No matching min/max row

• MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 경우 이 문구가 표시된다.
• 실제 쿼리가 문법적으로 오류가 있는 것은 아니고, 쿼리 처리를 위한 데이터가 없다는 의미이다.

no matching row in const table

• 조인이 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없을 경우 이 문구가 표시된다.
• “Impossible WHERE …”와 같은 종류로, 실행 계획을 만들기 위한 기초 자료가 없음을 의미한다.

No matching rows after partition pruning

• 파티션된 테이블에 대한 UPDATE 또는 DELETE 명령에서, 해당 파티션에서 UPDATE 하거나 DELETE 할 대상 레코드가 없을 경우 표시되는 문구이다.
• 단순히 삭제한 레코드가 없음을 의미하는 것이 아니라, 대상 파티션이 없다는 것을 의미한다.

No tables used

• FROM 절이 없는 쿼리 문장이나 “FROM DUAL” 형태의 쿼리 실행 계획에서 이 문구가 출력된다.
  • “FROM DUAL”은 FROM 절에 상수 테이블을 읨하는 DUAL(칼럼과 레코드를 각각 1개씩만 가지는 가상의 상수 테이블)이 사용되는 것을 말한다.
• MySQL 서버는 다른 DBMS와는 달리 FROM 절이 없는 쿼리도 허용된다.

Not exists

• 아우터 조인을 이용해 안티-조인을 수행하는 쿼리에서 이 문구가 표시된다.
  • 안티-조인(Anti-JOIN)은 A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 하는 쿼리 형태를 말한다.
  • 주로 NOT IN(subquery) 형태나 NOT EXISTS 연산자를 사용하는데, 레코드의 건수가 많을 때는 아우터 조인을 이용하면 빠른 성능을 낼 수 있다.
• A 테이블의 레코드를 이용해 B 테이블을 조인할 때 B 테이블의 레코드가 존재하는지 아닌지만 판단한다는 것을 의미한다.
  • B 테이블에 조인 조건에 일치하는 레코드가 여러 건이 있더라도 딱 1건만 조회해보고 처리를 완료하는 최적화

Plain isn’t ready yet

• MySQL 8.0 버전에서는 다른 커넥션에서 실행 중인 쿼리의 실행 계획을 살펴볼 수 있다.

  ## 실행 중인 쿼리 보기 (커넥션ID 확인)
  SHOW PROCESSLIST;
  
  ## 특정 커넥션의 실행 계획 확인하기
  EXPLAIN FOR CONNECTION 커넥션ID;

• 해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못한 상태에서 EXPLAIN FOR CONNECTION 명령이 실행되었을 경우 이 문구가 표시된다.
• 이 문구가 표시된다면 대상 커넥션의 쿼리가 실행 계획을 수립할 여유 시간을 좀 더 주고 다시 EXPLAIN FOR CONNECTION 명령을 실행하면 된다.

Range checked for each record(index map: N)

• “레코드마다 인덱스 레인지 스캔을 체크한다”는 의미이다.
• 두 개의 테이블을 조인하는 쿼리에서, 조인 조건에 상수가 없고 둘 다 변수인 경우 변수의 값을 보고 풀 테이블 스캔으로 접근할지 인덱스 레인지 스캔으로 접근할지 결정하는 최적의 조인 방법이다.

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

  • e1 테이블의 레코드를 하나씩 읽을 때마다 e1.emp_no 값이 계속 바뀌므로 쿼리의 비용 계싼을 위한 기준 값이 계속 변한다.
  • e1 테이블의 emp_no 가 작을 때는 e2 테이블을 풀 테이블 스캔으로 접근하고, emp_no가 큰 값일 때는 e2 테이블을 인덱스 레인지 스캔으로 접근하는 형태를 수행한다.
• 이 문구는 (index map: 0x1) 과 같은 메시지가 함께 출력되는데, 이는 사용할지 말지를 판단하는 후보 인덱스의 순번을 나타낸다.

  • 16진수로 표시되므로 이진수로 표현을 바꿔서 해석해야 한다.

  • 0x1이 표시되었다면 이진수로도 1이므로, 테이블의 첫 번째 인덱스를 사용할지 아니면 테이블을 풀 스캔할지를 매 레코드 단위로 결정하면서 처리된다.

    ## index map의 16진수를 이진수로 변환했을 때 각 자릿수는
    ## 다음 명령으로 테이블의 구조를 조회 시 나열된 인덱스의 순번을 의미한다.
    SHOW CREATE TABLE 테이블명;

  • 각 자릿수의 값이 1인 인덱스를 사용 가능한 인덱스 후보로 선정했음을 의미한다. (여러 후보 인덱스들 중 어떤 것을 사용했는지는 알 수 없다)

• 이 문구가 표시된다면 type 칼럼의 값은 ALL 로 표시된다.
  • “index map”에 표시된 후보 인덱스를 사용할지 여부를 검토해서, 후보 인덱스가 별로 도움되지 않는다면 최종적으로 풀 테이블 스캔을 사용하기 때문이다.

Recursive

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

  • MySQL 서버에서 재귀 쿼리는 WITH 구문을 이용해 CTE를 사용하면 된다.

    ## n이라는 칼럼 하나를 가진 cte라는 이름의 내부 임시 테이블을 생성
    WITH RECURSIVE cte (n) AS
    (
    	## n 칼럼의 값이 1부터 5까지 1씩 증가하게 해서 레코드 5건을 만든다
    	SELECT 1
    	UNION
    	SELECT n + 1 FROM cte WHERE n < 5
    )
    SELECT * FROM cte;

• 이처럼 WITH 구문이 재귀 CTE로 사용되었을 경우 Recursive 메시지가 표시된다.

Rematerialize

• MySQL 8.0 버전부터 래터럴 조인(LATERAL JOIN) 기능이 추가되었다.
• 래터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장하는데, 이 과정을 Rematerializing 이라고 한다.
• 이렇게 매번 임시 테이블이 새로 생성되는 경우 이 문구가 표시된다.

Select tables optimized away

• 다음의 경우 이 문구가 표시된다.
  • MIN() 또는 MAX()만 SELECT 절에 사용되거나
  • GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용될 경우
• MyISAM 테이블에 대해선 GROUP BY 없이 COUNT(*)만 SELECT할 때도 이런 형태의 최적화가 적용된다.
  • MyISAM 테이블은 전체 레코드 건수를 별도로 관리하기 때문에 인덱스나 데이터를 읽지 않고도 전체 건수를 빠르게 조회할 수 있다.
  • 하지만 WHERE 조건절이 있는 쿼리는 이런 최적화를 사용하지 못한다.

Start temporary, End temporary

• 세미 조인 최적화 중에서 Duplicate Weed-out 최적화 전략이 사용될 경우, 이 문구가 표시된다.
• Duplicate Weed-out 최적화 전략은 불필요한 중복 건을 제거하기 위해서 내부 임시 테이블을 사용하는데, 이때 내부 임시 테이블에 저장되는 테이블을 식별할 수 있게 문구를 표시하는 것이다.
  • 조인의 첫 번째 테이블에 Start temporary 표시
  • 조인이 끝나는 부분에 End temporary 표시

unique row not found

• 두 개의 테이블이 각각 유니크(프라미어리 키 포함) 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 이 문구가 표시된다.

Reference

참고 서적

📔 Real MySQL 8.0