TIL

10.3(4) 실행 계획 분석 (possible_keys, key, key_len, ref, rows, filtered)

10.3.6 possible_keys 칼럼

• possible_keys 칼럼에 있는 내용은 옵티마이저가 후보로 선정했던 접근 방법에서 사용되는 인덱스 목록이다.
  • 사용될 법했던 인덱스의 목록
  • 테입르의 모든 인덱스가 표시되는 경우가 허다하다.
• 특별한 경우를 제외하고는 그냥 무시해도 된다.

10.3.7 key 칼럼

• key 칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.
• index_merge인 경우에만 2개 이상의 인덱스가 사용되는데 이 때는 ‘,’로 구분되어 표시된다.
• 풀 테이블 스캔으로 동작할 땐 NULL로 표시된다.

10.3.8 key_len 칼럼

• key_len 칼럼은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려준다.
  • 정확하게는 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값
  • 단일 칼럼 인덱스에서도 같은 지표 제공

10.3.9 ref 칼럼

• ref 칼럼은 접근 방법이 ref인 경우 참조 조건(Equal 배교 조건)으로 제공된 값을 표시한다.
  • 상숫값을 지저했다면 const가 표시된다.
  • 다른 테이블의 칼럼값이면 그 테이블명과 칼럼명이 표시된다.
  • 참조로 사용되는 값을 변환하거나 연산을 거치게 하면 func이 표시된다.
    • 사용자가 명시적으로 변환할 때가 아닌 MySQL 서버 내부적으로 값을 변환할 때도 func가 출력된다.
    • MySQL 서버가 변환을 하지 않아도 되게 조인 칼럼 타입은 일치시키는 편이 좋다.

10.3.10 rows 칼럼

• rows 칼럼은 실행 계획 효율성 판단을 위해 예측했던 레코드 건수를 보여준다.
  • 인덱스를 사용하는 조건에만 일치하는 레코드 건수
  • 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측
  • 테이블의 레코드 수와 인덱스 값의 분포도가 어떤지로 판단
• 아래 쿼리를 보면 옵티마이저가 쿼리를 처리하기 위해 약 331,143건의 레코드를 읽을 것이라 예측하여 풀 테이블 스캔을 선택한 것을 확인할 수 있다.

EXPLAIN SELECT * FROM dept_emp WHERE from_date>='1985-01-01';

id	select_type	table	type	key	key_len	rows
1	SIMPLE	dept_emp	ALL	NULL	NULL	331143

• 아래처럼 범위를 좁히자 약 292건의 레코드만 읽고 체크하면 원하는 결과를 가져올 수 있을거라 예측하여 인덱스 레인지 스캔을 선택했다.

EXPLAIN SELECT * FROM dept_emp WHERE from_date>='2002-07-01';

id	select_type	table	type	key	key_len	rows
1	SIMPLE	dept_emp	range	ix_fromdate	NULL	292

10.3.11 filtered 칼럼

• filtered 칼럼은 인덱스 조건만 일치하는 레코드들에서 인덱스를 사용하지 못하는 WHERE 조건으로 한 번 더 필터링해서 남은 비율이다.
  • rows 칼럼이 바로 인덱스 조건만 일치하는 레코드 건수를 나타낸다.
  • 필터링되어 버려지는 레코드 비율이 아닌 필터링되고 남은 레코드 비율

• 아래 예제 쿼리에서 WHERE 조건의 첫 번째 조건과 마지막 조건만 각 테이블에서 인덱스를 사용할 수 있는 조건들이다.

    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
  

id	select_type	table	type	key	rows	filtered
1	SIMPLE	e	ref	ix_firstname	233	16.03
1	SIMPLE	s	ref	PRIMARY	10	0.48

• 두 테이블 중 모든 조건들로 필터링해서 최종적으로 일치하는 레코드 건수가 적은 employees 테이블이 드라이빙 테이블로 선정된다.
• employees 테이블에서 인덱스 조건에만 일치하는 레코드는 약 233건

• 233건 중 16.03%만 나머지 WHERE 조건에 일치한다는 것을 알 수 있다.

• filted 값이 얼맘나 정확히 예측될 수 있느냐에 따라 조인 성능이 달라진다.
  • 조인이 될 때 조건에 만족하는 레코드 건수가 적은 테이블을 드라이빙 테이블로 선저한다.
  • 레코드 건수 예측에 filtered 칼럼이 사용된다.
  • 레코드 건수가 적어야 조인 횟수가 줄어들고 그 과정에서 읽어온 데이터를 저장해둘 메모리 사용량이 낮아진다.