デフォルトで有効になっている innodb_stats_auto_recalc 変数は、テーブルが 10% を超える行に変更された場合に統計を自動的に計算するかどうかを制御します。 テーブルの作成または変更時に STATS_AUTO_RECALC 句を指定して、個々のテーブルの自動統計再計算を構成することもできます。

自動統計再計算はバックグラウンドで行われるため、innodb_stats_auto_recalc が有効な場合でも、10% を超えるテーブルに影響を与える DML 操作を実行した直後に統計が再計算されないことがあります。 場合によっては、統計の再計算が数秒遅れることがあります。 最新の統計がすぐに必要な場合は、ANALYZE TABLE を実行して統計の同期 (フォアグラウンド) 再計算を開始します。

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 句を定義します。

次の CREATE TABLE の例では、3 つの句がすべて指定されています:

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;

オプティマイザは、キー配分に関する推定 statistics を使用して、インデックスの相対 selectivity に基づいて実行計画のインデックスを選択します。 ANALYZE TABLE などの操作を行うと、InnoDB は、インデックスのカーディナリティーを推定するためにテーブル上の各インデックスからランダムなページをサンプリングします。 このサンプリング手法は、random dive と呼ばれます。

innodb_stats_persistent_sample_pages は、サンプリングされるページの数を制御します。 実行時に設定を調整して、オプティマイザで使用される統計の見積りの品質を管理できます。 デフォルト値は 20 です。 次の問題が発生した場合は、設定の変更を検討してください:

デフォルトでは、InnoDB は統計の計算時にコミットされていないデータを読み取ります。 テーブルから行を削除するコミットされていないトランザクションの場合、行の見積りおよびインデックス統計の計算時に削除マークが付けられたレコードが除外されるため、READ UNCOMMITTED 以外のトランザクション分離レベルを使用してテーブルで同時に操作している他のトランザクションの実行計画が最適でなくなる可能性があります。 このシナリオを回避するために、innodb_stats_include_delete_marked を有効にして、永続オプティマイザ統計の計算時に削除マーク付きレコードが含まれるようにできます。

innodb_stats_include_delete_marked が有効な場合、ANALYZE TABLE では、統計の再計算時に削除マークが付けられたレコードが考慮されます。

innodb_stats_include_delete_marked は、すべての InnoDB テーブルに影響するグローバル設定で、永続オプティマイザ統計にのみ適用されます。

永続的統計機能は、innodb_table_stats および innodb_index_stats という名前の、mysql データベース内の内部的に管理されているテーブルに依存します。 これらのテーブルは、すべてのインストール、アップグレード、およびソースからのビルド手順で自動的に設定されます。

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_table_stats テーブルには、テーブルごとに 1 つの行が含まれます。 次の例は、収集されるデータのタイプを示しています。

テーブル 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 カラムには、次のタイプの統計が示されます。

カーディナリティデータを提供する 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 インデックスの場合は、2 つの n_diff% 行があります。 行数は、インデックス内のカラム数に等しくなります。

セカンダリインデックス (i1) の場合は、4 つの n_diff% 行があります。 セカンダリインデックス (c,d) には 2 つのカラムのみが定義されていますが、InnoDB では一意でないすべてのインデックスに主キーが接尾辞として付加されるため、セカンダリインデックスには 4 つの n_diff% 行があります。 その結果、セカンダリインデックスカラム (c,d) と主キーカラム (a,b) の両方を反映して、2 つではなく 4 つの n_diff% 行があります。

一意のインデックス (i2uniq) の場合は、2 つの n_diff% 行があります。

innodb_index_stats テーブルを使用して、テーブル、パーティションまたはサブパーティションのインデックスサイズを取得できます。 次の例では、テーブル t1 のインデックスサイズが取得されています。 テーブル t1 の定義および対応するインデックス統計については、セクション15.8.10.1.6「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 |
+-------+------------+-------+

パーティションまたはサブパーティションの場合は、変更された 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;