책너두 (Real MySQL 8.0 1권) 42일차 (~457p)

요약

• key_len 칼럼에서 표시하는 값의 의미를 알게 됨.
  • 인덱스를 사용하는 칼럼의 데이터 타입이 가질 수 있는 바이트임
• ref 칼럼에 표시되는 값의 의미를 알게 됨.
  • 상수 값인지, 외부 테이블의 어떤 칼럼인지, 산술 표현이 사용된 func 타입인지 알 수 있음.
• rows 칼럼의 값의 의미를 알게 됨.
  • 실행 계획을 위해 예측한 레코드 건수를 알려줌
• filtered 칼럼의 값의 의미를 알게 됨.
  • 인덱스 뿐만아니라 모든 조건에서의 일치하는 레코드 건수를 알 수 있는 값임.
  • 필터링 되고 남은 값의 %를 보여줌.
• Extra 칼럼에 표시되는 값들을 알게 됨.
  • const row not found
  • Deleting all rows
  • Distinct

메모

key_len 칼럼

• 실제 업무에서 사용하는 테이블은 단일 칼럼으로만 만들어진 인덱스보다 다중 칼럼으로 만들어진 인덱스가 더 많음.
• 실행 계획의 key_len 칼럼의 값은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용헀는지 알려줌.
  • 더 정확하게는, 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값임.
  • 그래서, 다중 칼럼 인덱스뿐 아니라 단일 칼럼으로 만들어진 인덱스에서도 같은 지표를 제공함.
  • ex) (dept_no, emp_no) 로 구성된 프라이머리 키를 가진 dep_emp 테이블
    • dept_no 의 칼럼 타입은 CHAR(4) 임. dept_no 로만 조회를 하면 key_len 은 16으로 표시됨
      • dept_no 칼럼은 utf8mb4 문자 집합을 사용함
      • 문자 하나가 차지하는 공간은 1~4 바이트로 가변적이지만, MySQL 서버가 utf8mb4 문자를 위해 메모리 공간을 할당해야 할 때는 문자와 관계없이 고정적으로 4바이트로 계산함. (그래서 4*4 = 16 바이트 로 표시됨)
    • emp_no, dept_no로 조회할 경우 key_len 이 20으로 표시됨.
      • emp_no 칼럼 타입은 INTEGER 타입임 (4바이트)
      • 따라서 16 + 4 = 20 바이트임
    • key_len 필드 값이 데이터 타입의 길이보다 조금 길게 표시되는 경우도 있음.
      • NULL이 저장될 수 있는 칼럼으로 정의되면 NOT NULL 인이 아닌지 를 저장하기 위해 1바이트를 추가로 더 사용함.

ref 칼럼

• 접근 방법이 ref면, 참조 조건 (Equal 비교 조건)으로 어떤 값이 제공됐는 지 보여줌.
• 상숫값을 지정했다면 ref 칼럼 값은 const 로 표시됨
• 다른 테이블 칼럼 값이면 그 테이블 명과 칼럼명이 표시됨.
• 쿼리 조인 조건에 산술 표현식을 사용했다면 ref 칼럼에 func 로 표시됨.
  • 사용자가 명시적으로 값을 변환할 때뿐만 아니라 MySQL 서버가 내부적으로 값을 변환해야 할 때도 ref 칼럼에 func 가 출력됨
  • 아래가 대표적인 예임.
    • 문자 집합이 일치하지 않는 두 문자열 칼럼을 조인할 경우
    • 숫자 타입의 칼럼과 문자열 타입의 칼럼으로 조인할 때
  • 가능하면 MySQL 서버가 이런 변환을 하지 않아도 되게 조인 칼럼의 타입은 일치시키는 편이 좋음.

rows 칼럼

• MySQL 옵티마이저는 각 조건에 대해 가능한 처리 방식을 나열하고, 각 처리 방식의 비용을 비교해, 최종적으로 하나의 실행 계획을 수립함.
  • 이때, 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정함.
  • 대상 테이블에 얼마나 많은 레코드가 포함되어 있는지, 또는 각 인덱스 값의 분포도가 어떤지를 통계 정보를 기준으로 조사해서 예측함.
• MySQL 실행 계획의 rows 칼럼값은 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줌.
  • 이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해서 MySQL 옵티마이저가 산출해 낸 예상값이라서 정확하지는 않음.
  • 또, rows 칼럼에 표시되는 값은 반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미함.
  • 그래서, 실행 계획의 rows 칼럼에 출력되는 값과 실제 쿼리 결과 반환된 레코드 건수는 일치하지 않는 경우가 많음.

MySQL 옵티마이저가 예측한 값은 틀릴 가능성이 높음. 대략의 값이지, 정확한 값을 산출하기 위한 기능이 아님. 하지만 대략 수치가 어느 정도 근접해야만 옵티마이저는 제대로 된 실행 계획을 수립할 수 있음. 가끔 인덱스 되지 않은 칼럼이나 칼럼 값이 균등하게 분포되지 않은 경우에도 제대로 된 예측을 못할 수 있음. 이 경우를 위해 히스토그램이 도입됐음.

filtered 칼럼

• 옵티마이저는 각 테이블에서 일치하는 레코드 개수를 가능하면 정확히 파악해야 좀 더 효율적인 실행계획을 수립할 수 있음.
• 실행 계획에서 rows 칼럼 값은 인덱스를 사용하는 조건에만 일치하는 레코드 건수를 예측한 것임.
  • 하지만 대부분 쿼리에서 where 절에 사용되는 조건이 모두 인덱스를 사용할 수 있는 것은 아님.
  • 조인이 사용되는 경우, WHERE 절에서 인덱스를 사용할 수 있는 조건도 중요하지만, 인덱스를 사용하지 못하는 조건에서 일치하는 레코드 건수를 파악하는 것도 매우 중요함.
  • ex) rows 가 233, filtered 가 16.03 인 경우
    • 인덱스 조건으로 일치하는 레코드가 대략 233건이고, 이 중, 16.03%만 인덱스를 사용하지 못하는 조건과 일치한다는 것을 알 수 있음.
    • filtered 칼럼 값은 필터링되어 버려지는 레코드 비율이 아니라, 필터링되고 남은 레코드 비율을 의미함.

Extra 칼럼

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

const row not found

• 쿼리 실행 계획에서 const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않을 때 이 내용이 표시됨.

Deleting all rows

• MyISAM 스토리지 엔진과 같이 스토리지 엔진의 핸들러 차원에서 테이블의 모든 레코드를 삭제하는 기능을 제공하는 스토리지 엔진 테이블인 경우, Extra 칼럼에 “Deleting all rows” 문구가 표시됨
  • 이 문구는 WHERE 조건절이 없는 DELETE 문장의 실행 계획에서 자주 표시됨.
  • 이 문구는 테이블의 모든 레코드를 삭제하는 핸들러 기능(API)을 한번 호출함으로써 처리됐다는 것을 의미함.
  • 기존에는 테이블의 레코드 삭제를 위해 각 스토리지 엔진의 핸들러 함수를 레코드 건수만큼 호출해서 삭제해야 했는데, “Deleting all rows” 처리 방식은 한 번의 핸들러 함수 호출로 아주 간단하고 빠르게 처리할 수 있음.

8.0 버전부터 InnoDB 스토리지 엔진과 MyISAM 엔진 모두 더 이상 실행 계획에 “Deleting all rows” 최적화는 표시되지 않음. 테이블의 모든 레코드를 삭제하고자 한다면 WHERE 조건절이 없는 DELETE 보다 TRUNCATE TABLE 명령을 사용할 것을 권장함.

Distinct

• Extra 칼럼에 DISTINCT 키워드가 표시될 수 있음.

• departments 테이블와 dept_emp 테이블이 dept_no 로 조인할 때, 실제로 조회하려는 값은 dept_no 인 상황임.
• 이 상황이라면 조인하면서 DISTINCT 처리로 인해, 중복된 dept_no 를 무시하고 꼭 필욯나 것만 조인함.