본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.

---

10.1 통계 정보

10.1.1 테이블 및 인덱스 통계 정보

MySQL 서버의 통계 정보

MySQL 5.5 버전까지는 각 테이블의 통계 정보가 메모리에 관리되어, MySQL 서버가 재시작되면 수집되었던 통계 정보가 모두 사라졌다.

MySQL 5.6 버전부터는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선되었다.

mysql> ALTER TABLE employees.employees STATS_PERSISTENT=1;

mysql> SELECT *
		FROM innodb_index_stasts
        WHERE database_name='employees'
        	AND TABLE_NAME='employees';
            
mysql> SELECT *
		FROM innodb_table_stasts
        WHERE database_name='employees'
        	AND TABLE_NAME='employees';

또한 MySQL 서버에 특정 이벤트가 발생하면 자동으로 통계 정보가 갱신되는데, innodb_stats_auto_recalc 시스템 설정 변수의 값을 "OFF"로 설정하면 통계 정보가 자동으로 갱신되는 것을 막을 수 있다. 따라서 영구적인 통계 정보를 이용하고자 하면 이 설정을 "OFF"로 변경하면 된다.

10.1.2 히스토그램

MySQL 8.0 버전으로 업그레이드되면서 MySQL 서버도 칼럼의 데이터 분포도를 참조할 수 있는 히스토그램(Histogram) 정보를 활용할 수 있게 됐다.

히스토그램 정보 수집 및 삭제

MySQL 8.0 버전에서 히스토그램 정보는 칼럼 단위로 관리하는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리된다.

수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드하므로 이를 SELECT해서 정보를 조회할 수 있다.

MySQL 8.0 버전에서는 다음과 같은 2종류의 히스토그램 타입이 지원된다.

• Singleton (싱글톤 히스토그램)
  칼럼값 개별로 레코드 건수를 관리하는 히스토그램, Value-Based 히스토그램 또는 도수 분포라고도 부른다.

• Equi-Height (높이 균형 히스토그램)
  칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램, Height-Balanced 히스토그램이라고도 부른다.

생성된 히스토그램은 다음과 같이 삭제할 수 있으며, 참조 데이터가 아닌 딕셔너리의 내용만 삭제하기 때문에 다른 쿼리 처리 성능에는 영향을 주지 않는다. 하지만 히스토그램이 삭제되면 실행 계획이 달라질 수 있으므로 주의하자.

mysql> ANALYZE TABLE employees.employees
		DROP HISTOGRAM gender, hire_date;

히스토그램을 삭제하지 않고 MySQL 옵티마이저가 히스토그램을 사용하지 않게 하려면 다음과 같이 optimizer_switch 시스템 변수 값을 변경하면 되지만 conidition_fanout_filter 옵션에 의해 영향받는 다른 최적화 기능들이 사용되지 않을 수 있다.

mysql> SET GLOBAL optimizer_switch='condition_fanout_filter=off'

특정 커넥션 또는 쿼리에서만 히스토그램을 사용하지 않고자 한다면 다음과 같은 방법을 사용하면 된다.

-- // 현재 커넥션에서 실행되는 쿼리만 히스토그램을 사용하지 않게 설정
mysql > SET SESSION optimizer_switch='condition_fanout_filter=off';

-- // 현재 쿼리만 히스토그램을 사용하지 않게 설정
mysql> SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ *
		FROM ...

히스토그램의 용도

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

히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측한다. 하지만 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별하므로 쿼리 성능에 상당한 영향을 미친다.

간단한 예제로 employees 테이블의 bitrh_date 칼럼에 대해 히스토그램이 있을 때와 없을 때의 예측치의 차이를 살펴보자.

mysql> EXPLAIN
		SELECT *
        FROM employees
        WHERE first_name='Zita'
        	AND birth_date BETWEEN '1950-01-01' AND '1960-01-01';

히스토그램 정보를 수집하기 전, 옵티마이저는 first_name='Zita' 조건에 일치하는 레코드가 224건 있고, 대략 11.11%가 birth_date 조건을 만족한다고 예측했다.

mysql> ANALYZE TABLE employees
		UPDATE histogram ON first_name, birth_date;

mysql> EXPLAIN
		SELECT *
        FROM employees
        WHERE first_name='Zita'
        	AND birth_date BETWEEN '1950-01-01' AND '1960-01-01';

반면 히스토그램 정보를 수집한 뒤의 실행 계획에서는 61.14%가 조건을 만족할 것으로 예측했으며, 실제 데이터를 조회해보면 63.84%의 레코드가 birth_date 조건을 만족하고 있었다. 히스토그램 정보의 유무가 데이터 분포 정보 예측에 굉장히 큰 차이를 보임을 확인할 수 있다.

다음으로 이러한 데이터 분포 정보가 쿼리 성능에 어떤 영향을 주는지 확인해보자. 다음 예제는 2개의 테이블을 조인하는데, 옵티마이저 힌트를 사용해 강제로 조인의 순서를 바꿔 성능을 살펴본 것이다.

mysql> SELECT /*+ JOIN_ORDER(e, s) */ *
		FROM salaries s
       	INNER JOIN employees e ON e.emp_no=s.emp_no
           	AND e.birth_date BETWEEN '1950-01-01' AND '1950-02-01'
       	WHERE s.salary BETWEEN 40000 AND 70000;
           
mysql> SELECT /*+ JOIN_ORDER(s, e) */ *
		FROM salaries s
       	INNER JOIN employees e ON e.emp_no=s.emp_no
           	AND e.birth_date BETWEEN '1950-01-01' AND '1950-02-01'
       	WHERE s.salary BETWEEN 40000 AND 70000;

두 쿼리 모두 동일한 결과를 만들어 내지만 쿼리의 성능은 굉장히 큰 차이를 보이고 있다. bitrh_date 칼럼과 salary 칼럼은 인덱스되지 않은 칼럼으로 해당 칼럼들에 히스토그램이 없다면 옵티마이저는 이 칼럼들의 데이터 분포를 전혀 알지 못하고 실행 계획을 수립하여, 옵티마이저 힌트를 제거했을 때 테이블의 전체 레코드 건수나 크기 등의 단순한 정보만으로 조인의 드라이빙 테이블을 결정하게 된다.

각 칼럼에 대해 히스토그램 정보가 있다면 어느 테이블을 먼저 읽어야 조인 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단하여 실행 계획 수립에 크게 영향을 미칠 수 있다.

히스토그램과 인덱스

MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다. 이때 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴보는데, 이 작업을 인덱스 다이브(Index Dive)라고 표현한다.

MySQL 8.0 서버에서는 인덱스된 칼럼을 검색 조건으로 사용하는 경우 그 칼럼의 히스토그램은 사용하지 않고 인덱스 다이브를 통해 직접 수집한 정보를 활용하고, 히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용한다.

10.1.3 코스트 모델 (Cost Model)

MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 한다.

• 디스크로부터 데이터 페이지 읽기
• 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
• 인덱스 키 비교
• 레코드 평가
• 메모리 임시 테이블 작업
• 디스크 임시 테이블 작업

MySQL 서버는 사용자의 쿼리 처리에 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다. 이렇게 전체 쿼리 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model)이라고 한다.

MySQL 8.0 서버의 코스트 모델은 다음 2개 테이블에 저장돼 있는 설정값을 사용하며, 모두 mysql DB에 존재한다.

• server_cost
  인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리

• engine_cost
  레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리

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	레코드 비교

코스트 모델에서 중요한 것은 각 단위 작업에 설정되는 비용 값 설정에 따른 실행 계획의 비용 변화를 파악하는 것이다. 대표적인 예시는 다음과 같다.

• key_compare_cost 비용을 높이면 MySQL 서버 옵티마이저가 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.

• row_evaluate_cost 비용을 높이면 풀 스캔을 실행하는 쿼리들의 비용이 높아지고, MySQL 서버 옵티마이저는 가능하면 인덱스 레인지 스캔을 사용하는 실행 계획을 선택할 가능성이 높아진다.

• disk_temptable_create_cost와 dist_temptable_row_cost 비용을 높이면 MySQL 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.

• memory_temptable_create_cost와 memory_temptable_row_cost 비용을 높이면 MySQL 서버 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.

• io_block_read_cost 비용이 높아지면 MySQL 서버 옵티마이저는 가능하면 InnoDB 버퍼 풀에 데이터 페이지가 많이 적재돼 있는 인덱스를 사용하는 실행 계획을 선택할 가능성이 높아진다.

• memory_block_read_cost 비용이 높아지면 MySQL 서버는 InnoDB 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스를 사용할 가능성이 높아진다.

10.2 실행 계획 확인

10.2.1 실행 계획 출력 포맷

MySQL 8.0 버전부터는 FORMAT 옵션을 사용해 실행 계획의 표시 방법을 JSON, TREE, 단순 테이블 형태로 선택할 수 있다.

-- // 테이블 포맷 표시
mysql> EXPLAIN
		SELECT * FROM ...
        
-- // 트리 포맷 표시
mysql> EXPLAIN FORMAT=TREE
		SELECT * FROM ...
 
-- // JSON 포맷 표시
mysql> EXPLAIN FORMAT=JSON
		SELECT * FROM ...

10.2.2 쿼리의 실행 시간 확인

MySQL 8.0.18 버전부터는 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가됐다.

EXPLAIN ANALYZE 명령은 항상 결과를 TREE 포맷으로 보여주기 때문에 EXPLAIN 명령에 FORMAT 옵션을 사용할 수 없다. TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미하며, 실제 실행 순서는 다음 기준으로 읽으면 된다.

• 들여쓰기가 같은 레벨에서는 상단에 위치하는 라인이 먼저 실행
• 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행

EXPLAIN ANALYZE 명령은 EXPLAIN 명령과 달리 실행 계획만 추출하는 것이 아닌 실제 쿼리를 사용하고 사용된 실행 계획과 소요된 시간을 보여주므로 쿼리가 완료돼야 실행 계획의 결과를 확인할 수 있다.