코스트 모델 (Cost Model)

• MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 한다.
  • 디스크로부터 데이터 페이지 읽기
  • 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
  • 인덱스 키 비교
  • 레코드 평가
  • 메모리 임시 테이블 작업
  • 디스크 임시 테이블 작업
• MySQL 서버는 사용자의 쿼리에 대해 이러한 다양한 작업이 얼마나 필요한지 예측하고, 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다.
• 이렇게 전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model)이라고 한다.

비용 관리

• MySQL 5.7 이전 버전까지는 이런 작업들의 비용을 MySQL 서버 소스 코드에 상수화해서 사용했다.
  • MySQL 서버가 사용하는 하드웨어에 따라 비용이 달라질 수 있기 때문에, 고정된 비용을 일률적으로 적용하는 것은 최적의 실행 계획 수립에 있어 방해 요소라는 단점이 있다.
• MySQL 5.7 버전부터 이러한 단점 보완을 위해 MySQL 서버의 소스 코드에 상수화돼 있던 각 단위 작업의 비용을 DBMS 관리자가 조정할 수 있게 개선됐다.
• MySQL 8.0 버전으로 업데이트되면서 칼럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의 실행 계획 수립에 사용되기 시작했다.
  • MySQL 5.7 버전에서는 인덱스되지 않은 칼럼의 데이터 분포(히스토그램)나 메모리에 상주 중인 페이지의 비율 등 비율 계산과 연관된 부분의 정보가 부족한 상태였다.

코스트 모델의 설정값

• MySQL 8.0 서버의 코스트 모델은 다음 mysql DB의 테이블에 저장돼 있는 설정값을 사용한다.
  • server_cost : 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
  • engine_cost : 레코드를 가진 데이터 페이지를 가져오는데 필요한 비용 관리

공통 칼럼

server_cost 테이블과 engine_cost 테이블은 공통으로 다음 5개의 칼럼을 가지고 있다.

• cost_name
  • 코스트 모델의 각 단위 작업
• default_value
  • 각 단위 작업의 비용
  • 기본값이며, MySQL 서버 소스 코드에 설정된 값이다.
• cost_value
  • DBMS 관리자가 설정한 값
  • 이 값이 Null이면 MySQL 서버는 default_vale 칼럼의 비용을 사용한다.
• last_updated
  • 단위 작업의 비용이 변경된 시점
  • 단순 정보성 (옵티마이저에 영향을 미치는 정보 X)
• comment
  • 비용에 대한 추가 설명
  • 단순 정보성 (옵티마이저에 영향을 미치는 정보 X)

engine_cost 테이블은 추가로 다음 2개 칼럼을 더 갖는다.

• egine_name
  • 비용이 적용된 스토리지 엔진
  • 스토리지 엔진별로 각 단위 작업의 비용을 설정할 수 있다.
  • 기본값은 “default”로, 특정 스토리지 엔진의 비용이 설정되지 않았다면 해당 스토리지 엔진의 비용으로 이 값을 적용한다.
  • 각 스토리지 엔진별로 단위 작업의 비용을 달리 설정하고자 한다면 이 칼럼을 이용한다.
• device_type
  • 디스크 타입
  • MySQL 8.0에서는 아직 이 칼럼의 값을 활용하지 않는다. (그래서 “0”만 설정할 수 있다)

코스트 모델에서 지원하는 작업 단위

MySQL 8.0 버전의 코스트 모델에서 지원하는 단위 작업은 8개다.

engine_cost

cost_name	default_value	설명
io_block_read_cost	1.00	디스크 데이터 페이지 읽기
memory_block_read_cost	0.25	메모리 데이터 페이지 읽기

server_cost

cost_name	default_value	설명
disk_temptable_create_cost	20.00	디스크 임시 테이블 생성
disk_temptable_row_cost	0.50	디스크 임시 테이블의 레코드 읽기
key_compare_cost	0.05	인덱스 키 비교
memory_temptable_create_cost	1.00	메모리 임시 테이블 생성
memory_temptable_row_cost	0.10	메모리 임시 테이블의 레코드 읽기
row_evaluate_cost	0.10	레코드 비교

row_evaluate_cost

• 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업이다.
• row_evaluate_cost 값이 증가할수록
  • 많은 레코드를 처리하는 쿼리의 비용이 높아지고 (풀 테이블 스캔)
  • 상대적으로 적은 레코드를 처리하는 쿼리의 비용이 낮아진다. (레인지 스캔)

key_compare_cost

• 키 값의 비교 작업에 필요한 비용을 의미한다.
• key_compare_cost 값이 증가할수록
  • 키 값 비교 처리가 많은 경우 쿼리의 비용이 높아진다. (레코드 정렬)

비용 확인

• MySQL 서버에서 각 실행 계획의 계산된 비용(Cost)은 다음과 같이 확인할 수 있다.

  EXPLAIN FORMAT=TREE
  SELECT *
  FROM employees
  WHERE first_name='Matt'
  
  EXPLAIN FORMAT=JSON
  SELECT *
  FROM employees
  WHERE first_name='Matt'
  

• 각 단위 작업의 비용을 이용해 MySQL 서버의 실행 계획에 표시되는 비용을 직접 계산하는 것은 상당히 어렵다.
  • MySQL 옵티마이저가 쿼리의 비용을 계산할 때 이용하는 정보들이 사용자에게 표시되지 않게 때문이다.
    • 인덱스의 B-Tree 깊이
    • 인덱스 키 검색을 위해 읽어야 하는 페이지 개수
    • 디스크와 메모리(InnoDB 버퍼 풀)에서 데이터 페이지를 각각 몇 개씩 읽어야 하는지
    • 레코드 정렬 작업에서 사용되는 알고리즘별로 키 값 비교 작업이 몇 번 필요한지 등

비용 예상

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

예시

대표적으로 각 단위 작업의 비용이 변경되면 예상할 수 있는 결과들
다음 예시가 MySQL 서버 옵티마이저의 실행 계획에 미치는 영향의 전부는 아니다.

• key_compare_cost 비용을 높이면
  • MySQL 서버 옵티마이저가 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• row_evaluate_cost 비용을 높이면
  • 풀 스캔을 실행하는 쿼리들의 비용이 높아진다.
  • MySQL 서버 옵티마이저는 가능하면 인덱스 레인지 스캔을 사용하는 실행 계획을 선택할 가능성이 높아진다.
• disk_temptable_create_cost 와 disk_temptable_row_cost 비용을 높이면
  • MySQL 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• io_block_read_cost 비용이 높아지면
  • MySQL 서버 옵티마이저는 가능하면 InnoDB 버퍼 풀에 데이터 페이지가 많이 적재돼 있는 인덱스를 사용하는 실행 계획을 선택할 가능성이 높아진다.
• memory_block_read_cost 비용이 높아지면
  • MySQL 서버 옵티마이저는 InnoDB 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스를 사용할 가능성이 높아진다.

주의

• 코스트 모델은 MySQL 서버가 사용하는 하드웨어와 MySQL 서버 내부적인 처리 방식에 대한 깊이 있는 지식을 필요로 한다.
• 따라서 이런 부분에 대해 전문적인 지식이 없다면 서비스에 사용되는 코스트 모델의 설정값(engine_cost, server_cost)의 기본값을 함부로 변경하지 않는게 좋다.

Reference

참고 서적

📔 Real MySQL 8.0