[MySQL][InnoDB] 옵티마이저 통계 설정

■ InnoDB에 대한 옵티마이저 통계 구성

이 섹션에서는 InnoDB 테이블에 대한 지속적 및 비 영구 옵티 마이저 통계를 구성하는 방법에 대해 설명합니다.

+ 지속적 옵티마이저 통계 매개 변수 구성

+ 비 영구 옵티 마이저 통계 매개 변수 구성

+ InnoDB 테이블에 대한 ANALYZE TABLE 복잡성 추정

 

지속적 옵티마이저 통계는 서버 재시작시에도 유지되므로 계획 안정성이 향상되고 보다 일관된 쿼리 성능이 보장됩니다. 영구 옵티마이저 통계는 다음과 같은 추가 이점으로 제어 및 유연성을 제공합니다.

+ innodb_stats_auto_recalc 구성 옵션을 사용하여 테이블이 크게 변경된 후 통계가 자동으로 업데이트되는지 여부를 제어 할 수 있습니다.

+ STATS_PERSISTENT, STATS_AUTO_RECALC 및 STATS_SAMPLE_PAGES 절을 CREATE TABLE 및 ALTER TABLE 문과 함께 사용하여 개별 테이블에 대한 옵티 마이저 통계를 구성 할 수 있습니다.

+ mysql.innodb_table_stats 및 mysql.innodb_index_stats 테이블에서 옵티 마이저 통계 데이터를 쿼리 할 수 ​​있습니다.

+ mysql.innodb_table_stats 및 mysql.innodb_index_stats 테이블의 last_update 열을보고 통계가 마지막으로 업데이트된 시기를 확인할 수 있습니다.

+ mysql.innodb_table_stats 및 mysql.innodb_index_stats 테이블을 수동으로 수정하여 특정 쿼리 최적화 계획을 강제 실행하거나 데이터베이스를 수정하지 않고 대체 계획을 테스트 할 수 있습니다.

 

지속적 최적화 알고리즘 통계 기능은 기본적으로 사용 가능합니다 (innodb_stats_persistent = ON).

 

비 지속적 옵티마이저 통계는 각 서버를 다시 시작할 때마다 그리고 일부 다른 조작 후에 지워지고 다음 테이블 액세스에서 다시 계산됩니다. 결과적으로 통계를 재 계산할때 다른 추정값이 생성 될 수 있으며, 실행 계획의 선택과 쿼리 성능의 변화로 이어집니다.

 

이 섹션에서는 ANALYZE TABLE 복잡성 추정에 대한 정보도 제공하며, 이는 정확한 통계와 ANALYZE TABLE 실행시간간의 균형을 유지하려고 할 때 유용합니다.

 

■ 지속적 통계 매개 변수 구성

지속적 옵티마이저 통계 기능은 통계를 디스크에 저장하고 서버 재시작시 이를 지속적으로 유지함으로써 계획 안정성을 향상시켜 옵티마이 저가 주어진 쿼리에 대해 매번 일관성있는 선택을 할 가능성이 높습니다.

 

innodb_stats_persistent = ON 또는 STATS_PERSISTENT = 1로 개별 테이블이 정의 된 경우 옵티 마이저 통계는 디스크에 유지됩니다. innodb_stats_persistent는 기본적으로 활성화되어 있습니다.

 

이전에는 서버를 다시 시작할 때와 다른 유형의 작업 후 옵티 마이저 통계가 지워졌으며 다음 테이블 액세스에서 다시 계산되었습니다. 결과적으로 통계를 다시 계산할 때 쿼리 실행 계획에서 다른 선택과 쿼리 성능의 변화로 이어지는 다른 추정치가 생성 될 수 있습니다.

 

지속적 통계는 mysql.innodb_table_stats 및 mysql.innodb_index_stats 테이블에 저장됩니다.

 

▶ 지속적 옵티마이저 통계에 대한 자동 통계 계산 구성

기본적으로 사용되는 innodb_stats_auto_recalc 변수는 테이블의 행의 10% 이상으로 변경 될 때 통계가 자동으로 계산되는지 여부를 제어합니다. 테이블을 작성하거나 변경할 때 STATS_AUTO_RECALC 절을 지정하여 개별 테이블에 대한 자동 통계 재 계산을 구성할 수도 있습니다.

 

백그라운드에서 발생하는 자동 통계 재계산의 비동기 특성으로 인해 innodb_stats_auto_recalc가 활성화 된 경우에도 테이블의 10% 이상에 영향을 주는 DML 작업을 실행한 후 통계가 즉시 재 계산되지 않을 수 있습니다. 경우에 따라 통계 재 계산이 몇 초 지연 될 수 있습니다. 최신 통계가 즉시 필요한 경우 ANALYZE TABLE을 실행하여 통계의 동기(Foreground:앞단의 작업) 재계산을 시작합니다.

 

innodb_stats_auto_recalc가 비활성화 된 경우 인덱스된 열을 실질적으로 변경한 후 ANALYZE TABLE 문을 실행하여 옵티 마이저 통계의 정확성을 보장 할 수 있습니다. 데이터를 로드 한 후 실행하는 설정 스크립트에 ANALYZE TABLE을 추가하고 활동이 적은 시간에 스케줄에 따라 ANALYZE TABLE을 실행할 수도 있습니다.

 

인덱스가 기존 테이블에 추가되거나 열이 추가 또는 삭제되면 인덱스 통계가 계산되어 innodb_stats_auto_recalc의 값에 관계없이 innodb_index_stats 테이블에 추가됩니다.

 

▶ 개별 테이블에 대한 옵티 마이저 통계 매개 변수 구성

innodb_stats_persistent, innodb_stats_auto_recalc 및 innodb_stats_persistent_sample_pages는 전역 변수입니다. 이러한 시스템 전체 설정을 대체하고 개별 테이블에 대한 옵티마이저 통계 매개변수를 구성하기 위해 CREATE TABLE 또는 ALTER TABLE 문에서 STATS_PERSISTENT, STATS_AUTO_RECALC 및 STATS_SAMPLE_PAGES절을 정의할 수 있습니다.

 

+ STATS_PERSISTENT는 InnoDB 테이블에 대한 지속적 통계 사용 여부를 지정합니다. DEFAULT 값은 테이블의 지속적 통계 설정이 innodb_stats_persistent 설정에 의해 결정되도록 합니다. 값이 1이면 테이블에 대한 지속적인 통계가 활성화되고 값이 0이면 기능이 비활성화됩니다. 개별 테이블에 대해 지속적 통계를 사용으로 설정 한 후 ANALYZE TABLE을 사용하여 테이블 데이터가 로드 된 후 통계를 계산하도록 합니다.

 

+ STATS_AUTO_RECALC는 지속적 통계를 자동으로 다시 계산할지 여부를 지정합니다. DEFAULT 값은 테이블의 지속적 통계 설정이 innodb_stats_auto_recalc 설정에 의해 결정되도록 합니다. 값이 1이면 테이블 데이터의 10%가 변경 될 때 통계가 다시 계산됩니다. 값이 0이면 테이블에 대한 자동 재계산이 방지됩니다. 값 0을 사용하는 경우 테이블을 실질적으로 변경한 후 ANALYZE TABLE을 사용하여 통계를 다시 계산합니다.

 

+ STATS_SAMPLE_PAGES는 예를 들어 ANALYZE TABLE 조작으로 색인화 된 열에 대한 카디널리티 및 기타 통계가 계산될 때 샘플링 할 색인 페이지 수를 지정합니다.

 

맨 아래에서 세 개의 절(STATS로 시작)은 모두 다음 CREATE TABLE 예에 지정되어 있습니다.

CREATE TABLE `t1` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY  (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
  STATS_PERSISTENT=1,
  STATS_AUTO_RECALC=1,
STATS_SAMPLE_PAGES=25;

 

▶ InnoDB Optimizer 통계를 위해 샘플링 된 페이지 수 구성

옵티마이저는 키 분포에 대한 추정 통계를 사용하여 인덱스의 상대적 선택도에 따라 실행 계획에 대한 인덱스를 선택합니다. ANALYZE TABLE과 같은 작업으로 인해 InnoDB는 테이블의 각 인덱스에서 임의의 페이지를 샘플링하여 인덱스의 카디널리티를 추정합니다. 이 샘플링 기법을 랜덤 다이빙이라고합니다.

 

innodb_stats_persistent_sample_pages는 샘플링된 페이지 수를 제어합니다. 런타임시 설정을 조정하여 옵티마이저가 사용하는 통계 추정 품질을 관리할 수 ​​있습니다. 기본값은 20입니다. 다음 문제가 발생하면 설정을 수정합니다.

 

1. 통계가 충분히 정확하지 않으며 옵티마이저는 EXPLAIN 출력에 표시된 것처럼 차선책을 선택합니다.

인덱스의 실제 카디널리티 (인덱스 열에서 SELECT DISTINCT를 실행하여 결정)를 mysql.innodb_index_stats 테이블의 추정값과 비교하여 통계의 정확성을 확인할 수 있습니다.

 

통계가 충분히 정확하지 않다고 판단되면 통계 추정이 충분히 정확해질 때까지 innodb_stats_persistent_sample_pages의 값을 늘려야합니다. 그러나 innodb_stats_persistent_sample_pages를 너무 많이 늘리면 ANALYZE TABLE이 느리게 실행될 수 있습니다.

 

2. ANALYZE TABLE이 너무 느립니다.

이 경우 ANALYZE TABLE 실행 시간이 허용 될 때까지 innodb_stats_persistent_sample_pages를 줄여야합니다. 그러나 값을 너무 줄이면 부정확 한 통계 및 최적이 아닌 쿼리 실행계획의 첫 번째 문제가 발생할 수 있습니다.

 

정확한 통계와 ANALYZE TABLE명령 수행 시간간에 균형을 이룰 수 없는 경우 테이블에서 인덱스화 된 열 수를 줄이거나 파티션 수를 제한하여 ANALYZE TABLE 복잡성을 줄여야 합니다. 기본키 열이 각 고유하지 않은 인덱스에 추가되므로 테이블의 기본키의 열 수도 고려해야합니다.

 

관련 정보는 "InnoDB 테이블에 대한 ANALYZE 테이블 복잡성 추정”을 참조하세요.

 

 

 

▶ 영구 통계 계산에 삭제 표시 레코드 포함

기본적으로 InnoDB는 통계를 계산할때 커밋되지 않은 데이터를 읽습니다. 테이블에서 행을 삭제하는 커밋되지 않은 트랜잭션의 경우 행 추정값 및 인덱스 통계를 계산할때 삭제 표시 레코드가 제외되기 때문에, READ UNCOMMITED(커밋되지 않은 읽기) 이외의 격리 수준 트랜잭션을 사용하여 테이블에서 동시에 작동하는 다른 트랜잭션에 대한 최적의 실행계획이 발생할 수 없습니다. 이 시나리오를 피하기 위해 영구 최적화 프로그램 통계를 계산할 때 삭제 표시 레코드가 포함되도록 innodb_stats_include_delete_marked를 사용할 수 있습니다.

 

+ innodb_stats_include_delete_marked가 활성화되면 ANALYZE TABLE은 통계를 다시 계산할 때 삭제 표시 레코드를 고려합니다.

+ innodb_stats_include_delete_marked는 모든 InnoDB 테이블에 영향을 미치는 전역 설정이며 영구 최적화 통계에만 적용됩니다.

+ innodb_stats_include_delete_marked는 MySQL 5.7.16에서 도입되었습니다.

 

▶ InnoDB 영구 통계 테이블

지속적 통계 기능은 mysql 데이터베이스에서 내부적으로 관리되는 테이블 (innodb_table_stats 및 innodb_index_stats)에 의존합니다. 이 테이블은 모든 설치, 업그레이드 및 소스에서 빌드 절차에서 자동으로 설정됩니다.

 

Table 14.4 innodb_table_stats의 컬럼들

컬럼이름	의미
database_name	데이터베이스 이름
table_name	테이블 이름, 파티션 이름, 서브파티션 이름
last_update	Row가 마지막으로 업데이트 된 시간을 포함하는 타임스탬프.
n_rows	테이블안의 row갯수
clustered_index_size	페이지안의 프리머리 인덱스의 크기
sum_of_other_index_sizes	페이지의 다른인덱스들(프라이머리 키가 아닌)의 총 크기

 

 

Table 14.5 innodb_index_stats의 컬럼들

컬럼이름	의미
database_name	데이터베이스 이름
table_name	테이블이름, 파티션이름, 서브파티션 이름
index_name	인덱스 이름
last_update	InnoDB에서 Row가 마지막으로 업데이트 된 시간을 포함하는 타임스탬프.
stat_name	stat_value 열에 값이보고되는 통계 이름
stat_value	stat_name 열에 이름이 지정된 통계 값
sample_size	stat_value 열에 제공된 견적에 대해 샘플링 된 페이지 수
stat_description	stat_name 열에 명명 된 통계에 대한 설명

 

innodb_table_stats 및 innodb_index_stats 테이블에는 인덱스 통계가 마지막으로 업데이트 된 시간을 표시하는 last_update 열이 포함되어 있습니다.

mysql> SELECT * FROM innodb_table_stats \G
*************************** 1. row ***************************
           database_name: sakila
              table_name: actor
             last_update: 2014-05-28 16:16:44
                  n_rows: 200
    clustered_index_size: 1
sum_of_other_index_sizes: 1
...
mysql> SELECT * FROM innodb_index_stats \G
*************************** 1. row ***************************
   database_name: sakila
      table_name: actor
      index_name: PRIMARY
     last_update: 2014-05-28 16:16:44
       stat_name: n_diff_pfx01
      stat_value: 200
     sample_size: 1
...

 

innodb_table_stats 및 innodb_index_stats 테이블을 수동으로 업데이트 할 수 있으므로 데이터베이스를 수정하지 않고도 특정 쿼리 옵티마이저 플랜을을 강제 실행하거나 대체 계획을 테스트 할 수 있습니다. 통계를 수동으로 업데이트하는 경우 FLUSH TABLE tbl_name 문을 사용하여 업데이트된 통계를 로드합니다.

 

영구 통계는 서버 인스턴스와 관련되므로 로컬 정보로 간주됩니다. 따라서 자동 통계 재계산이 수행 될 때 innodb_table_stats 및 innodb_index_stats 테이블이 복제되지 않습니다. 통계의 동기 재계산을 시작하기 위해 ANALYZE TABLE을 실행하는 경우, 이 명령문은 로깅을 억제하지 않는한 복제되고 복제 슬레이브에서 재계산이 수행됩니다.

 

 

 

▶ InnoDB 영구 통계 테이블 예제

innodb_table_stats 테이블은 각 테이블에 대해 하나의 행을 포함합니다. 다음예는 수집된 데이터 유형을 보여줍니다.

표 t1에는 기본 색인 (열 a, b) 보조 색인 (열 c, d) 및 고유 색인 (열 e, f)이 있습니다.

CREATE TABLE t1 (
a INT, b INT, c INT, d INT, e INT, f INT,
PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
)ENGINE=INNODB;

 

샘플데이터 5줄을 입력 후, 테이블 t1의 데이터는 다음과 같습니다.

mysql> SELECT * FROM t1;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+

 

통계를 즉시 업데이트하려면 ANALYZE TABLE을 실행합니다 (innodb_stats_auto_recalc가 사용 가능한 경우 변경된 테이블 행의 10 % 임계 값에 도달 한 것으로 가정하면 몇 초 내에 통계가 자동으로 업데이트 됩니다).

mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+

 

테이블 t1의 테이블 통계는 InnoDB가 테이블 통계 (2014-03-14 14:36:34), 테이블의 행 수(5), 클러스터 된 인덱스 크기(1 페이지) 및 결합 된 마지막 시간과 다른 인덱스의 크기 (2 페이지) 을 보여줍니다.

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 't1'\G
*************************** 1. row ***************************
           database_name: test
              table_name: t1
             last_update: 2014-03-14 14:36:34
                  n_rows: 5
    clustered_index_size: 1
sum_of_other_index_sizes: 2

 

innodb_index_stats 테이블에는 각 인덱스에 대한 여러 행이 있습니다. innodb_index_stats 테이블의 각 행은 stat_name 컬럼에 이름이 지정되고 stat_description 컬럼에 설명된 특정 인덱스 통계와 관련된 데이터를 제공합니다. 예를 들면 다음과 같습니다.

mysql> SELECT index_name, stat_name, stat_value, stat_description
       FROM mysql.innodb_index_stats WHERE table_name like 't1';
+------------+--------------+------------+-----------------------------------+
| index_name | stat_name    | stat_value | stat_description                  |
+------------+--------------+------------+-----------------------------------+
| PRIMARY    | n_diff_pfx01 |          1 | a                                 |
| PRIMARY    | n_diff_pfx02 |          5 | a,b                               |
| PRIMARY    | n_leaf_pages |          1 | Number of leaf pages in the index |
| PRIMARY    | size         |          1 | Number of pages in the index      |
| i1         | n_diff_pfx01 |          1 | c                                 |
| i1         | n_diff_pfx02 |          2 | c,d                               |
| i1         | n_diff_pfx03 |          2 | c,d,a                             |
| i1         | n_diff_pfx04 |          5 | c,d,a,b                           |
| i1         | n_leaf_pages |          1 | Number of leaf pages in the index |
| i1         | size         |          1 | Number of pages in the index      |
| i2uniq     | n_diff_pfx01 |          2 | e                                 |
| i2uniq     | n_diff_pfx02 |          5 | e,f                               |
| i2uniq     | n_leaf_pages |          1 | Number of leaf pages in the index |
| i2uniq     | size         |          1 | Number of pages in the index      |
+------------+--------------+------------+-----------------------------------+

 

stat_name 열에는 다음과 같은 유형의 통계가 표시됩니다.

+ size : stat_name = size 인 경우 stat_value 열은 인덱스의 총 페이지 수를 표시합니다.

+ n_leaf_pages : stat_name = n_leaf_pages에서 stat_value 열은 색인의 리프 페이지 수를 표시합니다.

+ n_diff_pfxNN : 여기서 stat_name = n_diff_pfx01 인 경우 stat_value 열은 인덱스의 첫 번째 열에 고유한 값의 수를 표시합니다. 여기서 stat_name = n_diff_pfx02 인 경우 stat_value 열은 인덱스의 처음 두 열에있는 고유 값의 수 등을 표시합니다. stat_name = n_diff_pfxNN 인 경우 stat_description 열은 계산 된 색인 열의 쉼표로 구분 된 목록을 표시합니다.

 

카디널리티 데이터를 제공하는 n_diff_pfxNN 통계를 추가로 설명하려면 이전에 소개된 t1 테이블 예제를 다시 한 번 고려해 봅니다. 아래에 표시된 것처럼 t1 테이블은 기본 인덱스 (열 a, b), 보조 인덱스 (열 c, d) 및 고유 인덱스 (열 e, f)로 작성됩니다.

CREATE TABLE t1 (
  a INT, b INT, c INT, d INT, e INT, f INT,
  PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
) ENGINE=INNODB;

 

5 행의 샘플 데이터를 삽입 한 후 테이블 t1은 다음과 같이 나타납니다.

mysql> SELECT * FROM t1;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+

 

stat_name LIKE 'n_diff %'인 index_name, stat_name, stat_value 및 stat_description을 조회하면 다음 결과가 리턴됩니다.

mysql> SELECT index_name, stat_name, stat_value, stat_description
       FROM mysql.innodb_index_stats
       WHERE table_name like 't1' AND stat_name LIKE 'n_diff%';
+------------+--------------+------------+------------------+
| index_name | stat_name    | stat_value | stat_description |
+------------+--------------+------------+------------------+
| PRIMARY    | n_diff_pfx01 |          1 | a                |
| PRIMARY    | n_diff_pfx02 |          5 | a,b              |
| i1         | n_diff_pfx01 |          1 | c                |
| i1         | n_diff_pfx02 |          2 | c,d              |
| i1         | n_diff_pfx03 |          2 | c,d,a            |
| i1         | n_diff_pfx04 |          5 | c,d,a,b          |
| i2uniq     | n_diff_pfx01 |          2 | e                |
| i2uniq     | n_diff_pfx02 |          5 | e,f              |
+------------+--------------+------------+------------------+

PRIMARY 인덱스의 경우 두 개의 n_diff % 행이 있습니다. 행 수는 인덱스의 열 수와 같습니다.

 

노트

고유하지 않은 인덱스의 경우 InnoDB는 기본 키의 열을 추가합니다.

 

+ index_name = PRIMARY 및 stat_name = n_diff_pfx01에서 stat_value는 1이며, 이는 인덱스의 첫 번째 열 (열 a)에 하나의 고유 한 값이 있음을 나타냅니다. 테이블 t1의 열 a에 있는 데이터를 보고 열 a에 있는 고유 값의 수를 확인합니다. 여기에는 단일 구별 값이 있습니다 (1). 계산 된 열 (a)이 결과 집합의 stat_description 열에 표시됩니다.

+ index_name = PRIMARY 및 stat_name = n_diff_pfx02에서 stat_value는 5이며, 이는 인덱스의 두 열 (a, b)에 5 개의 고유 한 값이 있음을 나타냅니다. 테이블 t1의 열 a와 b에있는 데이터를 보고 열 a와 b의 고유값 수를 확인합니다. 여기에서 (1,1), (1,2), (1,3)의 5 개의 고유 값이 있습니다. , (1,4) 및 (1,5). 계산된 열 (a, b)은 결과 집합의 stat_description 열에 표시됩니다.

 

보조 인덱스 (i1)의 경우 네 개의 n_diff % 행이 있습니다. 보조 인덱스 (c, d)에 대해 두 개의 열만 정의되지만 InnoDB는 기본 키로 모든 비 고유 인덱스에 접미사를 지정하므로 보조 인덱스에 대해 네개의 n_diff % 행이 있습니다. 결과적으로 2차 인덱스 컬럼 (c, d)과 1차 키컬럼 (a, b)을 모두 설명하기 위해 2개 대신 4개의 n_diff % 행이 있습니다.

+ index_name = i1 및 stat_name = n_diff_pfx01에서 stat_value는 1이며, 인덱스의 첫 번째 열 (열 c)에 하나의 고유한 값이 있음을 나타냅니다. c 열에 있는 고유 값의 수는 표 t1의 c 열에있는 데이터를 확인하여 확인합니다. 여기서 단일 고유 값은 (10)입니다. 계산 된 열 (c)은 결과 집합의 stat_description 열에 표시됩니다.

+ index_name = i1 및 stat_name = n_diff_pfx02에서 stat_value는 2이며, 인덱스의 처음 두 열 (c, d)에 두 개의 고유 한 값이 있음을 나타냅니다. 테이블 t1에서 열 c와 d의 데이터를보고 열 c와 d의 고유 값의 수를 확인합니다. 여기에서 (10,11)과 (10,12)의 두 가지 고유 값이 있습니다. 계산된 열 (c, d)은 결과집합의 stat_description 열에 표시됩니다.

+ index_name = i1 및 stat_name = n_diff_pfx03에서 stat_value는 2이며, 인덱스의 처음 세 열에 두 개의 고유 한 값이 있음을 나타냅니다 (c, d, a). 열 c, d 및 a의 고유 값 수는 열 c, d 및 a 테이블 t1의 데이터를 확인하여 확인합니다. 여기에서 (10,11,1) 및 (10, 12,1). 계산 된 열 (c, d, a)은 결과 집합의 stat_description 열에 표시됩니다.

+ index_name = i1 및 stat_name = n_diff_pfx04에서 stat_value는 5이며, 인덱스의 4개 열 (c, d, a, b)에 5개의 고유 한 값이 있음을 나타냅니다. c, d, a 및 b 열의 고유 값 수는 표 t1에서 c, d, a 및 b 열의 데이터를 확인하여 확인합니다. 여기에서 5 개의 고유 값이 있습니다. (10,11,1,1 ), (10,11,1,2), (10,11,1,3), (10,12,1,4) 및 (10,12,1,5). 계산 된 열 (c, d, a, b)은 결과 집합의 stat_description 열에 표시됩니다.

 

유니크 인덱스 (i2uniq)의 경우 두 개의 n_diff % 행이 있습니다.

+ index_name = i2uniq 및 stat_name = n_diff_pfx01에서 stat_value는 2입니다. 이는 인덱스의 첫 번째 열 (열 e)에 두 개의 고유 한 값이 있음을 나타냅니다. 테이블 t1에서 열 e의 데이터를 확인하여 열 e의 고유 값 수를 확인합니다. 여기에서 (100)과 (200)의 두 가지 고유 값이 있습니다. 계산 된 열 (e)이 결과 집합의 stat_description 열에 표시됩니다.

+ index_name = i2uniq 및 stat_name = n_diff_pfx02에서 stat_value는 5이며, 이는 인덱스의 두 열 (e, f)에 5 개의 고유 한 값이 있음을 나타냅니다. e 및 f 열의 고유 값 수는 표 t1에서 e 및 f 열의 데이터를 확인하여 확인합니다. 여기에서 (100,101), (200,102), (100,103), (200,104) 및 (100,105). 계산 된 열 (e, f)은 결과 집합의 stat_description 열에 표시됩니다.

 

▶ innodb_index_stats 테이블을 사용하여 인덱스 크기 검색

innodb_index_stats 테이블을 사용하여 테이블, 파티션 또는 서브 파티션의 인덱스 크기를 검색 할 수 있습니다. 다음 예에서는 테이블 t1에 대한 색인 크기를 검색합니다.

mysql> SELECT SUM(stat_value) pages, index_name,
       SUM(stat_value)*@@innodb_page_size size
       FROM mysql.innodb_index_stats WHERE table_name='t1'
       AND stat_name = 'size' GROUP BY index_name;
+-------+------------+-------+
| pages | index_name | size  |
+-------+------------+-------+
|     1 | PRIMARY    | 16384 |
|     1 | i1         | 16384 |
|     1 | i2uniq     | 16384 |
+-------+------------+-------+

 

파티션 또는 하위 파티션의 경우 수정 된 WHERE 절과 동일한 쿼리를 사용하여 인덱스 크기를 검색 할 수 있습니다. 예를 들어 다음 쿼리는 테이블 t1의 파티션에 대한 인덱스 크기를 검색합니다.

mysql> SELECT SUM(stat_value) pages, index_name,
       SUM(stat_value)*@@innodb_page_size size
       FROM mysql.innodb_index_stats WHERE table_name like 't1#P%'
       AND stat_name = 'size' GROUP BY index_name;

 

■ 비 영구 옵티 마이저 통계 매개 변수 구성

이 섹션에서는 비 영구적 옵티 마이저 통계를 구성하는 방법에 대해 설명합니다. innodb_stats_persistent = OFF 일 때 또는 STATS_PERSISTENT = 0으로 개별 테이블을 작성하거나 변경할 때 옵티 마이저 통계는 디스크에 유지되지 않습니다. 대신 통계는 메모리에 저장되며 서버가 종료되면 유실됩니다. 통계는 특정 작업 및 특정 조건에 따라 정기적으로 업데이트됩니다.

 

MySQL 5.6.6부터 옵티 마이저 통계는 기본적으로 디스크에 유지되며 innodb_stats_persistent 구성 옵션으로 활성화됩니다.

 

▶ 최적화 통계 업데이트

비 지속적 최적화 알고리즘 통계는 다음과 같은 경우에 업데이트됩니다.

 

+ ANALYZE TABLE 실행.

+ SHOW TABLE STATUS, SHOW INDEX를 실행하거나 innodb_stats_on_metadata 옵션을 사용하여 INFORMATION_SCHEMA.TABLES 또는 INFORMATION_SCHEMA.STATISTICS 테이블을 조회합니다.

 

MySQL 5.6.6부터 지속적 옵티 마이저 통계가 기본적으로 활성화되면 innodb_stats_on_metadata의 기본 설정이 OFF로 변경되었습니다. innodb_stats_on_metadata를 활성화하면 테이블 또는 인덱스가 많은 스키마의 액세스 속도가 줄어들고 InnoDB 테이블이 포함 된 쿼리에 대한 실행 계획의 안정성이 떨어질 수 있습니다. innodb_stats_on_metadata는 SET 문을 사용하여 전역 적으로 구성됩니다.

SET GLOBAL innodb_stats_on_metadata = ON

 

노트

innodb_stats_on_metadata는 옵티 마이저 통계가 비 지속성으로 구성된 경우에만 적용됩니다 (innodb_stats_persistent가 비활성화 된 경우).

 

+ --auto-rehash 옵션을 사용하여 mysql 클라이언트를 시작합니다. 이것이 기본값입니다. 자동 재해시(rehash) 옵션을 사용하면 모든 InnoDB 테이블이 열리고 테이블 열기 작업으로 인해 통계가 다시 계산됩니다.

mysql 클라이언트의 시작 시간을 개선하고 통계를 업데이트하기 위해 --disable-auto-rehash 옵션을 사용하여 자동 재해시(rehash)를 해제 할 수 있습니다. 자동 재해시 기능은 대화식 사용자를위한 데이터베이스, 테이블 및 열 이름의 자동 이름 완성을 가능하게 합니다.

+ 테이블이 먼저 열립니다.

+ InnoDB는 통계가 마지막으로 업데이트된 이후 테이블의 1/16이 수정되었음을 감지합니다.

 

▶ 샘플 페이지 수 구성

MySQL 쿼리 최적화 프로그램은 주요 분포에 대한 추정된 통계를 사용하여 인덱스의 상대적 선택도에 따라 실행 계획에 대한 인덱스를 선택합니다. InnoDB는 옵티 마이저 통계를 업데이트 할 때 테이블의 각 인덱스에서 임의의 페이지를 샘플링하여 인덱스의 카디널리티를 추정합니다. (이 기술은 랜덤 다이빙이라고합니다.)

 

통계 추정의 품질(따라서 조회 최적화 프로그램에 대한 더 나은 정보)을 제어하기 위해 innodb_stats_transient_sample_pages 매개 변수를 사용하여 샘플링된 페이지 수를 변경할 수 있습니다. 샘플링된 페이지의 기본수는 8이며, 정확한 추정값을 작성하기에 충분하지 않아서 쿼리 최적화 프로그램이 색인 선택을 제대로 수행하지 못할 수 있습니다. 이 기술은 조인에 사용되는 큰 테이블 및 테이블에 특히 중요합니다. 이러한 테이블에 대한 불필요한 전체 테이블 스캔은 상당한 성능 문제가 될 수 있습니다.

innodb_stats_transient_sample_pages는 런타임에 설정할 수있는 전역 매개 변수입니다.

 

innodb_stats_transient_sample_pages값은 innodb_stats_persistent=0일때 모든 InnoDB 테이블 및 인덱스의 인덱스 샘플링에 영향을줍니다. 인덱스 샘플 크기를 변경할 때 다음과 같은 잠재적 영향에 유의하십시오.

 

+ 1 또는 2와 같은 작은 값은 카디널리티 추정이 부정확 할 수 있습니다.

+ innodb_stats_transient_sample_pages 값을 늘리려면 더 많은 디스크 읽기가 필요할 수 있습니다. 8보다 큰 값 (예 : 100)은 테이블을 열거나 SHOW TABLE STATUS를 실행하는데 걸리는 시간이 상당히 느려질 수 있습니다.

+ 옵티마이 저는 다른 인덱스 선택성 추정값에 따라 매우 다른 쿼리 계획을 선택할 수 있습니다.

 

innodb_stats_transient_sample_pages의 값이 시스템에 가장 적합하면 옵션을 설정한 것이라면 변경하지 않습니다. 과도한 I / O를 요구하지 않고 데이터베이스의 모든 테이블에 대해 합리적으로 정확한 추정값을 얻는 값을 설정해야 합니다. 통계는 ANALYZE TABLE 실행 이외의 다양한 시간에 자동으로 재 계산되므로 인덱스 샘플 크기를 늘리고 ANALYZE TABLE을 실행한 다음 샘플 크기를 다시 줄이는 것은 의미가 없습니다.

 

작은 테이블은 일반적으로 큰 테이블보다 적은 인덱스 샘플이 필요합니다. 데이터베이스안에 있는 테이블들이  주로 작은 테이블보다 큰 테이블이 많은 경우 innodb_stats_transient_sample_pages에 더 큰 값을 설정합니다.

 

 

 

■ InnoDB 테이블에 대한 ANALYZE TABLE 복잡성 추정

InnoDB 테이블의 ANALYZE TABLE 복잡도는 다음에 따라 다릅니다.

+ innodb_stats_persistent_sample_pages에서 정의한대로 샘플링 된 페이지 수.

+ 테이블의 인덱싱 된 열 수

+ 파티션 수. 테이블에 파티션이없는 경우 파티션 수는 1로 간주됩니다.

 

이 매개 변수를 사용하면 ANALYZE TABLE 복잡성을 추정하기위한 대략적인 공식은 다음과 같습니다.

innodb_stats_persistent_sample_pages의 값 * 테이블의 색인화 된 열 수 * 파티션 수

 

일반적으로 결과 값이 클수록 ANALYZE TABLE의 실행 시간이 길어집니다.

 

innodb_stats_persistent_sample_pages는 글로벌 레벨에서 샘플링 된 페이지 수를 정의합니다. 개별 테이블에 대해 샘플링 된 페이지 수를 설정하려면 STATS_SAMPLE_PAGES 옵션을 CREATE TABLE 또는 ALTER TABLE과 함께 사용합니다.

 

innodb_stats_persistent = OFF 인 경우 샘플링 된 페이지 수는 innodb_stats_transient_sample_pages에 의해 정의됩니다.

 

ANALYZE TABLE 복잡성을 추정하기위한보다 심층적인 접근 방법을 보려면 다음 예를 참고하세요.

Big O 표기법에서 ANALYZE TABLE의 복잡성은 다음과 같이 설명됩니다.

 

O(n_sample

  * (n_cols_in_uniq_i

     + n_cols_in_non_uniq_i

     + n_cols_in_pk * (1 + n_non_uniq_i))

  * n_part)

 

어디에서(where) :

+ n_sample은 샘플링 된 페이지 수입니다 (innodb_stats_persistent_sample_pages에 의해 정의 됨)

+ n_cols_in_uniq_i는 모든 유니크 인덱스에 있는 모든 열의 총 수입니다 (기본 키 열은 제외).

+ n_cols_in_non_uniq_i는 모든 고유하지 않은 인덱스에 있는 모든 열의 총 수입니다.

+ n_cols_in_pk는 기본 키의 열 수입니다 (기본 키가 정의되지 않은 경우 InnoDB는 내부적으로 단일 열 기본 키를 만듭니다)

+ n_non_uniq_i는 테이블에서 고유하지 않은 인덱스의 수입니다.

+ n_part는 파티션 수입니다. 파티션이 정의되지 않은 경우 테이블은 단일 파티션으로 간주됩니다.

 

이제 기본 키(2 개의 열), 고유 인덱스(2 개의 열) 및 2개의 고유하지 않은 인덱스 (각각 2 개의 열)가있는 다음 테이블 (표 t)을 참고합니다.

CREATE TABLE t (
  a INT,
  b INT,
  c INT,
  d INT,
  e INT,
  f INT,
  g INT,
  h INT,
  PRIMARY KEY (a, b),
  UNIQUE KEY i1uniq (c, d),
  KEY i2nonuniq (e, f),
  KEY i3nonuniq (g, h)
);

 

위에서 설명한 알고리즘에 필요한 열 및 인덱스 데이터의 경우, 테이블 t에 대한 mysql.innodb_index_stats 지속적 인덱스 통계 테이블을 쿼리합니다. n_diff_pfx % 통계는 각 인덱스에 대해 계산 된 열을 보여줍니다. 예를 들어, 기본 키 인덱스에 대해 열 a와 b가 계산됩니다. 고유하지 않은 인덱스의 경우 사용자 정의 열 외에 기본 키 열 (a, b)이 계산됩니다.

mysql> SELECT index_name, stat_name, stat_description
       FROM mysql.innodb_index_stats WHERE
       database_name='test' AND
       table_name='t' AND
       stat_name like 'n_diff_pfx%';
  +------------+--------------+------------------+
  | index_name | stat_name    | stat_description |
  +------------+--------------+------------------+
  | PRIMARY    | n_diff_pfx01 | a                |
  | PRIMARY    | n_diff_pfx02 | a,b              |
  | i1uniq     | n_diff_pfx01 | c                |
  | i1uniq     | n_diff_pfx02 | c,d              |
  | i2nonuniq  | n_diff_pfx01 | e                |
  | i2nonuniq  | n_diff_pfx02 | e,f              |
  | i2nonuniq  | n_diff_pfx03 | e,f,a            |
  | i2nonuniq  | n_diff_pfx04 | e,f,a,b          |
  | i3nonuniq  | n_diff_pfx01 | g                |
  | i3nonuniq  | n_diff_pfx02 | g,h              |
  | i3nonuniq  | n_diff_pfx03 | g,h,a            |
  | i3nonuniq  | n_diff_pfx04 | g,h,a,b          |
  +------------+--------------+------------------+

 

위에 표시된 인덱스 통계 데이터 및 테이블 정의를 기반으로 다음 값을 판별 할 수 있습니다.

+ 기본 키 열을 계산하지 않는 모든 고유 인덱스의 모든 열의 총 수인 n_cols_in_uniq_i는 2 (c 및 d)입니다.

+ 고유하지 않은 모든 인덱스에있는 모든 열의 총 개수 인 n_cols_in_non_uniq_i는 4 (e, f, g 및 h)입니다.

+ 기본 키의 열 수인 n_cols_in_pk는 2 (a 및 b)입니다.

+ 테이블의 고유하지 않은 인덱스 수인 n_non_uniq_i는 2 (i2nonuniq 및 i3nonuniq)입니다.

+ 파티션 수인 n_part는 1입니다.

 

이제 innodb_stats_persistent_sample_pages * (2 + 4 + 2 * (1 + 2)) * 1을 계산하여 스캔 할 리프 페이지 수를 결정할 수 있습니다. innodb_stats_persistent_sample_pages를 기본값 20으로 설정하고 기본 페이지 크기가 16 KiB (innodb_page_size = 16384) 인 경우 테이블 t에 대해 20 * 12 * 16384 바이트를 읽거나 약 4MiB를 추정해볼 수 있습니다. 또한 일부 리프 페이지가 이미 버퍼 풀에 캐시되어 있기 때문에 디스크에서 4MiB를 모두 읽을 수 없습니다.

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※