MySQL(15) - 실행 계획

통계 정보

테이블 및 인덱스 통계 정보

비용 기반 최적화에서 가장 중요한 것은 통계 정보다.

테이블의 통계 정보가 자동으로 갱신되도록 할 수도 있고 원할때마다 수동적으로 갱신을 해줄 수도 있다. 통계 정보가 자동으로 갱신되면 인덱스 레인지 스캔으로 처리하던게 어느 날 갑자기 풀 테이블 스캔으로 실행되는 상황이 발생할 수도 있다.

통계 정보를 명령을 통해 갱신하려고 한다면 MySQL 사용량이 많지 않은 시간을 이용하는 것이 좋다.

 

히스토그램

히스토그램 도입 이전에는 통계 정보로 테이블 레코드 건수와 인덱스된 칼럼이 가지는 유니크한 값의 개수 정도만 가지고 있었다. 하지만 데이터는 균등한 분포도를 가지지 않는다.

히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 더 정확한 예측을 할 수 있다.

히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포되어 있을 것으로 예측한다. 하지만 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다. 이로 인해 쿼리의 성능이 크게 달라질 수 있다.

 

코스트 모델

쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델이라고 한다. row_evaluate_cost(레코드 비교), key_compare_cost(인덱스 키 비교) 등이 있다. 이러한 각 단위 작업의 비용들은 기본값이 있고 설정할 수도 있다.

코스트 모델에서 중요한 것은 각 단위 작업에 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 바뀌고 저비용으로 바뀌는지를 파악하는 것이다.

 

실행 계획 확인

MySQL 서버의 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있다. 또 테이블, 트리, JSON으로 실행 계획 출력 포맷을 지정할 수 있다.

EXPLAIN ANALYZE 명령을 통해 실행 계획의 단계별로 소요된 시간 정보를 확인할 수 있다. EXPLAIN ANALYZE 명령은 EXPLAIN 명령과 다르게 실행 계획만 추출하는 것이 아니라 실제 쿼리를 실행하고 사용된 실행 계획과 소요된 시간을 보여준다.

 

실행 계획 분석

• id 칼럼
  실행 계획에서 가장 왼쪽에 표시되는 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다. SELECT 문장은 하나인데, 여러 개의 테이블이 조인되는 경우에는 같은 id 값이 부여된다.
• select_type 칼럼
  각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.
• table 칼럼
  사용하는 table이 표시된다. <>로 둘러싸인 이름이 명시되는 경우가 있는데 <>안에 표시되는 숫자와 같은 id 값의 실행 계획으로 인해 만들어진 파생 테이블이라는 것이다.
• partitions 칼럼
  쿼리 처리를 위해 필요한 파티션들의 목록만 모아서 표시해준다.
• type 칼럼
  각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다. 인덱스를 사용했는지, 풀 테이블 스캔으로 읽었는지 등을 의미한다.
• possible_keys 칼럼
  후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록(사용될 법했던 인덱스의 목록)이다. 특별한 경우를 제외하곤 크게 도움이 되지 않는다.
• key 칼럼
  최종 선택된 실행 계획에서 사용하는 인덱스를 말한다. type 칼럼이 index_merge가 아닌 경우에는 테이블 하나당 하나의 인덱스만 이용할 수 있다. 만약 인덱스를 사용하지 못한다면 NULL로 표시된다.
• key_len 칼럼
  쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 표시된다. 더 정확하게는 바이트 단위로 보여진다.
• ref 칼럼
  Equal 비교 조건으로 어떤 값이 제공됐는지 보여준다. 상숫값을 지정했다면 const로 표시되고, 다른 테이블의 칼럼값이면 그 테이블명과 칼럼명이 표시된다.
• rows 칼럼
  실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. 즉 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미한다.
• filtered 칼럼
  rows 칼럼 값에 대비해서 조건에 맞게 필터링되고 남은 레코드의 비율을 의미한다. filtered 칼럼이 얼마나 정확히 예측될 수 있는냐에 따라 조인의 성능이 달라진다.
• Extra 칼럼
  쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시된다. Extra 칼럼에는 고정된 몇 개의 문장이 표시되는데, 일반적으로 2~3개씩 표시된다. 주로 내부적인 처리 알고리즘에 대해 조금 더 깊이 있는 내용을 보여준다.

 

 

 

참조: 책 Real MySQL 8.0