Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


MySQL 5.6 リファレンスマニュアル  /  ...  /  永続的オプティマイザ統計のパラメータの構成

14.13.16.1 永続的オプティマイザ統計のパラメータの構成

計画安定性は、もっとも大きく、かつもっとも重要なクエリーの望ましい目標です。InnoDB は、オプティマイザがもっとも効率的なクエリー実行計画を容易に見つけることができるように、常に InnoDB テーブルごとの統計を計算してきました。これらの統計を永続的なものにすることが可能になったため、特定のクエリーのインデックス使用状況や結合順序が変更される可能性は低くなります。

この機能はデフォルトでオンになっており、構成オプション innodb_stats_persistent で有効になります。

innodb_stats_persistent_sample_pages 構成オプションを設定することによって、統計を収集するために実行されるサンプリングの量を制御します。

構成オプション innodb_stats_auto_recalc は、テーブルに (行の 10% を超える) 大幅な変更が加えられた場合は常に統計を自動的に計算するかどうかを決定します。

注記

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

innodb_stats_auto_recalc が無効になっている場合は、インデックス付きカラムへの大幅な変更を行なったあと、該当する各テーブルに対して ANALYZE TABLE ステートメントを発行することによってオプティマイザ統計の精度を確保してください。このステートメントは、代表的なデータがテーブルにロードされたあとにセットアップスクリプトで実行したり、インデックス付きカラムの内容が DML 操作によって大幅に変更されたあとに定期的に、または低アクティビティーの時間帯にスケジュールに従って実行したりすることができます。既存のテーブルに新しいインデックスが追加された場合は、innodb_stats_auto_recalc の値には関係なく、インデックス統計が計算されて innodb_index_stats テーブルに追加されます。

注意

新しいインデックスが作成されたときに統計が確実に収集されるように、innodb_stats_auto_recalc オプションを有効にするか、または永続的統計モードが有効になっているときに新しいインデックスを作成するたびに ANALYZE TABLE を実行してください。

テーブルを作成する前にグローバルレベルで innodb_stats_persistentinnodb_stats_auto_recalc オプションを設定するか、または CREATE TABLE および ALTER TABLE ステートメントで STATS_PERSISTENTSTATS_AUTO_RECALC、および STATS_SAMPLE_PAGES 句を使用して、システム全体の設定をオーバーライドし、個々のテーブルの永続的統計を構成することができます。

以前は、これらの統計は各サーバーの再起動やその他の一部の操作のあとにクリアされ、テーブルが次回アクセスされたときに再計算されていました。これらの統計は、次回には異なる推定値が生成される可能性のあるランダムなサンプリング手法を使用して計算されるため、実行計画において異なる選択が行われ、そのためにクエリーパフォーマンスが変動します。

定期的に消去される以前の統計収集方法に戻すには、コマンド ALTER TABLE tbl_name STATS_PERSISTENT=0 を実行します。関連情報については、セクション14.13.16.2「非永続的オプティマイザ統計のパラメータの構成」を参照してください。

14.13.16.1.1 InnoDB オプティマイザ統計でサンプリングされるページの数の構成

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

統計の推定値の品質を制御する (それにより、クエリーオプティマイザへの情報を改善する) ために、実行時に設定できるパラメータ innodb_stats_persistent_sample_pages を使用して、サンプリングされるページの数を変更できます。

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 の複雑さを減らすためにテーブル内のインデックス付きカラムの数を減らすか、またはパーティションの数を制限することを考慮してください。また、主キーカラムは一意でない各インデックスに付加されるため、テーブルの主キー内のカラム数について考慮することも重要です。

    関連情報については、セクション14.13.17「InnoDB テーブルに対する ANALYZE TABLE の複雑さの推定」を参照してください。

14.13.16.1.2 InnoDB 永続的統計テーブル

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

表 14.8 innodb_table_stats のカラム

カラム名 説明
database_name データベース名
table_name テーブル名、パーティション名、またはサブパーティション名
last_update InnoDB が最後にこの行を更新した時間を示すタイムスタンプ
n_rows テーブル内の行数
clustered_index_size プライマリインデックスのサイズ (ページ数)
sum_of_other_index_sizes その他の (プライマリ以外の) インデックスの合計サイズ (ページ数)

表 14.9 innodb_index_stats のカラム

カラム名 説明
database_name データベース名
table_name テーブル名、パーティション名、またはサブパーティション名
index_name インデックス名
last_update InnoDB が最後にこの行を更新した時間を示すタイムスタンプ
stat_name stat_value カラムに値がレポートされている統計の名前
stat_value stat_name カラムで名前が指定されている統計の値
sample_size stat_value カラムに示されている推定値のサンプリングされるページの数
stat_description stat_name カラムで名前が指定されている統計の説明

次の例に示すように、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 コマンドを発行します。

14.13.16.1.3 InnoDB 永続的統計テーブルの例

innodb_table_stats テーブルには、テーブルごとに 1 行が含まれています。収集されるデータを次の例に示します。

テーブル t1 には、プライマリインデックス (カラム ab)、セカンダリインデックス (カラム cd)、および一意のインデックス (カラム ef) が含まれています。

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

  • 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 カラムには、インデックスの最初の 2 つのカラム内の固有の値の数が表示されます。以下も同様です。さらに、stat_name=n_diff_pfxNN である場合、stat_description カラムには、カウントされるインデックスカラムのカンマ区切りリストが示されます。

カーディナリティーデータを提供する n_diff_pfxNN の統計をさらに詳細に示すために、t1 テーブルの例を考えてみます。次に示すように、t1 テーブルは、プライマリインデックス (カラム ab)、セカンダリインデックス (カラム cd)、および一意のインデックス (カラム ef) で作成されます。

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_namestat_namestat_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 インデックスの場合は、2 つの n_diff% 行があります。行数は、インデックス内のカラム数に等しくなります。

注記

一意でないインデックスの場合は、InnoDB によって主キーのカラムが付加されます。

  • index_name=PRIMARY および stat_name=n_diff_pfx01 である場合、stat_value1 です。これは、インデックスの最初のカラム (カラム a) 内に固有の値が 1 つ存在することを示します。カラム a 内の固有の値の数は、テーブル t1 内のカラム a のデータを表示することによって確認されます。ここには、固有の値が 1 つ存在します (1)。カウントされるカラム (a) は、結果セットの stat_description カラムに示されています。

  • index_name=PRIMARY および stat_name=n_diff_pfx02 である場合、stat_value5 です。これは、インデックスの 2 つのカラム (a,b) 内に固有の値が 5 つ存在することを示します。カラム a および b 内の固有の値の数は、テーブル t1 内のカラム a および b のデータを表示することによって確認されます。ここには、固有の値が 5 つ存在します: (1,1)、(1,2)、(1,3)、(1,4)、および (1,5)。カウントされるカラム (a,b) は、結果セットの stat_description カラムに示されています。

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

  • index_name=i1 および stat_name=n_diff_pfx01 である場合、stat_value1 です。これは、インデックスの最初のカラム (カラム c) 内に固有の値が 1 つ存在することを示します。カラム c 内の固有の値の数は、テーブル t1 内のカラム c のデータを表示することによって確認されます。ここには、固有の値が 1 つ存在します: (10)。カウントされるカラム (c) は、結果セットの stat_description カラムに示されています。

  • index_name=i1 および stat_name=n_diff_pfx02 である場合、stat_value2 です。これは、インデックスの最初の 2 つのカラム (c,d) 内に固有の値が 2 つ存在することを示します。カラム c および d 内の固有の値の数は、テーブル t1 内のカラム c および d のデータを表示することによって確認されます。ここには、固有の値が 2 つ存在します: (10,11) および (10,12)。カウントされるカラム (c,d) は、結果セットの stat_description カラムに示されています。

  • index_name=i1 および stat_name=n_diff_pfx03 である場合、stat_value2 です。これは、インデックスの最初の 3 つのカラム (c,d,a) 内に固有の値が 2 つ存在することを示します。カラム cd、および a 内の固有の値の数は、テーブル t1 内のカラム cd、および a のデータを表示することによって確認されます。ここには、固有の値が 2 つ存在します: (10,11,1) および (10,12,1)。カウントされるカラム (c,d,a) は、結果セットの stat_description カラムに示されています。

  • index_name=i1 および stat_name=n_diff_pfx04 である場合、stat_value5 です。これは、インデックスの 4 つのカラム (c,d,a,b) 内に固有の値が 5 つ存在することを示します。カラム cda、および b 内の固有の値の数は、テーブル t1 内のカラム cda、および 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) の場合は、2 つの n_diff% 行があります。

  • index_name=i2uniq および stat_name=n_diff_pfx01 である場合、stat_value2 です。これは、インデックスの最初のカラム (カラム e) 内に固有の値が 2 つ存在することを示します。カラム e 内の固有の値の数は、テーブル t1 内のカラム e のデータを表示することによって確認されます。ここには、固有の値が 2 つ存在します: (100) および (200)。カウントされるカラム (e) は、結果セットの stat_description カラムに示されています。

  • index_name=i2uniq および stat_name=n_diff_pfx02 である場合、stat_value5 です。これは、インデックスの 2 つのカラム (e,f) 内に固有の値が 5 つ存在することを示します。カラム e および f 内の固有の値の数は、テーブル t1 内のカラム e および f のデータを表示することによって確認されます。ここには、固有の値が 5 つ存在します: (100,101)、(200,102)、(100,103)、(200,104)、および (100,105)。カウントされるカラム (e,f) は、結果セットの stat_description カラムに示されています。

14.13.16.1.4 innodb_index_stats テーブルを使用したインデックスサイズの取得

テーブル、パーティション、またはサブパーティションのインデックスのサイズは、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;