MySQL 8.0 의 새로운 기능: 히스토그램

질의 최적기는 가능한 한 효율적으로 SQL 질의를 실행 계획으로 변환할 책임이 있습니다. 그러나 데이터 환경이 끊임없이 변화함에 따라 질의 최적기가 최적의 실행 계획을 찾지 못할 수 있으며 이로 인해 SQL 효율성이 떨어질 수 있습니다. 이는 최적기가 각 테이블에 있는 데이터 행 수, 열당 서로 다른 값 수, 열당 데이터 분포 등 질의된 데이터에 대해 충분히 알지 못하기 때문입니다.

따라서 MySQL8.0.3 은 데이터 분포의 근사치인 히스토그램 함수를 도입하여 최적기에 더 많은 통계를 제공합니다. 필드 수, 각 다른 값의 백분율, 최대/최소 등을 예로 들 수 있습니다. MySQL 히스토그램은 동일 폭 히스토그램과 윤곽 히스토그램으로 나뉘며, MySQL 은 사용할 히스토그램 유형을 자동으로 할당하므로 간섭할 수 없습니다.

히스토그램에도 몇 가지 제한이 있습니다.

히스토그램 생성 및 삭제

구문을 생성합니다

히스토그램을 만들 때 여러 열에 대한 히스토그램을 동시에 만들 수 있지만 1- 1024 범위, 기본값은 100 으로 배럴 수를 지정해야 합니다. 배럴 수의 경우 몇 개의 다른 값, 데이터의 기울기 및 정밀도 등을 종합적으로 고려해야 합니다. 낮은 값부터 시작하여 요구 사항을 충족하지 않을 경우 순차적으로 늘리는 것이 좋습니다.

구문을 삭제합니다

히스토그램 정보

MySQL 은 사전 테이블 column_statistics 를 통해 히스토그램의 정의를 저장합니다. 각 행마다 한 필드의 히스토그램을 기록하고 JSON 형식으로 저장되었습니다.

MySQL 은 employees 의 first_name 필드에 윤곽 히스토그램을 할당합니다. 기본값은 100 bucket 입니다.

히스토그램이 생성되면 MySQL 은 모든 데이터를 메모리로 로드하고 메모리에서 모든 작업을 수행합니다. 막대 그래프가 큰 테이블에서 생성된 경우 수백 미터의 데이터를 메모리로 읽을 위험이 있을 수 있습니다. 따라서 hitogram _ generation _ max _ mem _ size 매개변수를 통해 히스토그램 생성에 허용되는 최대 메모리를 제어할 수 있습니다. 지정된 메모리가 모든 데이터 세트를 충족하지 못할 경우 샘플링이 사용됩니다.

MySQL8.0. 19 부터 스토리지 엔진 자체는 테이블에 저장된 데이터의 샘플링 구현을 제공합니다. 스토리지 엔진이 지원하지 않는 경우 MySQL 은 기본 샘플링을 사용하기 위해 전체 테이블 스캔이 필요합니다. 이는 큰 테이블에 너무 많은 비용이 듭니다. 샘플링 구현은 전체 테이블 스캔을 방지하고 샘플링 성능을 향상시킵니다.

INNODB_METRICS 카운터를 사용하여 데이터 페이지의 샘플링을 모니터링할 수 있으므로 카운터를 미리 열어야 합니다.

샘플 비율은 sampled _ page _ read/(sampled _ pages _ read+sampled _ pages _ skipped) 로 계산됩니다.

사례 최적화

소스 테이블에는 히스토그램이 없고 새 테이블에는 히스토그램이 있는 테이블을 복사합니다.

두 테이블에서 각각 SQL 에 대한 실행 계획을 확인합니다.

비용 값이 302 14.45 에서 18744.56 으로 떨어졌고, 스캔 행 수가 299822 에서 4 1654 로 줄어 성능이 향상되었음을 알 수 있습니다.