MySQL 쿼리 최적화 프로그램은 인덱스의 상대적인 선택성 에 따라 키 분포에 대한 추정 된 통계 를 사용하여 실행 계획을위한 인덱스를 선택합니다. ANALYZE TABLE 등의 작업을 수행하면 InnoDB 는 인덱스의 중요도 를 추정하기 위해 테이블의 각 인덱스에서 임의의 페이지를 샘플링합니다. (이 기술은 랜덤 다이브 라고합니다.)

통계 추정치의 품질을 제어하는 (그러면 쿼리 최적화에 대한 정보를 개선하기) 위해 런타임에 설정할 수있는 매개 변수 innodb_stats_persistent_sample_pages 를 사용하여 추출 된 페이지 수를 변경할 수 있습니다.

innodb_stats_persistent_sample_pages 의 기본값은 20입니다. 일반적인 지침으로 다음과 같은 문제가 발생했을 경우,이 매개 변수를 변경하는 것을 고려하십시오.

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

다음의 예와 같이, innodb_table_stats 테이블과 innodb_index_stats 테이블에는 모두 InnoDB 가 마지막으로 인덱스 통계를 업데이트 한 시간을 나타내는 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 테이블은 일반 테이블이기 때문에 수동으로 업데이트 할 수 있습니다. 통계를 수동으로 업데이트하는 기능은 데이터베이스를 변경하지 않고도 특정 쿼리 최적화 계획과 테스트 대체 계획을 강제로 수행 할 수 있습니다. 통계를 수동으로 업데이트하면 업데이트 된 통계가 MySQL에서 다시로드되도록, FLUSH TABLE tbl_name 명령을 실행합니다.

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 행의 샘플 데이터를 삽입 한 후 테이블은 다음과 같이됩니다.

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 |
+---+---+------+------+------+------+
5 rows in set (0.00 sec)

통계를 즉시 업데이트하려면 ANALYZE TABLE 을 실행합니다 ( innodb_stats_auto_recalc 이 활성화되어있는 경우 변경 될 테이블 행의 10 % 임계 값에 도달했다고 가정 할 때, 통계는 몇 초 내에 자동 에 업데이트됩니다.)

mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.02 sec)   

테이블 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
 1 row in set (0.00 sec) 

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      |
+------------+--------------+------------+-----------------------------------+
14 rows in set (0.00 sec)  

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

중요도 데이터를 제공하는 n_diff_pfx NN 의 통계를 자세히 설명하기 위해 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 행의 샘플 데이터를 삽입 한 후 테이블은 다음과 같이됩니다.

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 |
+---+---+------+------+------+------+
5 rows in set (0.00 sec)

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              |
+------------+--------------+------------+------------------+
8 rows in set (0.00 sec)

PRIMARY 인덱스의 경우 두 n_diff% 줄 수 있습니다. 행은 인덱스의 컬럼 수와 동일합니다.

보조 인덱스 ( i1 )의 경우 4 개의 n_diff% 줄 수 있습니다. 보조 인덱스로 정의되는 열은 2 가지 ( c,d ) 밖에 없지만 보조 인덱스 n_diff% 행은 4 가지가 있습니다. 이것은 고유하지 않은 인덱스는 모든 InnoDB 접미사에 의해 기본 키가 추가되기 때문입니다. 그 결과, 보조 인덱스 컬럼 ( c,d )와 프라이 머리 키 컬럼 ( a,b )를 모두 반영하여 2 개가 아닌 4 개의 n_diff% 줄 수 있습니다.

고유 인덱스 ( i2uniq )의 경우는 2 개의 n_diff% 줄 수 있습니다.

테이블, 파티션 또는 서브 파티션 인덱스의 크기는 innodb_index_stats 테이블을 사용하여 얻을 수 있습니다. 다음 예에서는 테이블 t1 의 인덱스 크기를 검색합니다. 테이블 t1 의 정의 및 대응하는 인덱스 통계 내용은 섹션 14.13.16.1.3 "InnoDB 영구 통계 테이블의 예" 를 참조하십시오.

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 |
+-------+------------+-------+
3 rows in set (0.00 sec)   

파티션 또는 서브 파티션의 경우 변경된 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;