MySQL 8.0 リファレンスマニュアル


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

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

永続オプティマイザ統計機能は、統計をディスクに格納し、サーバーの再起動後も永続させることで plan stability を改善し、optimizer が特定のクエリーに対して一貫性のある選択を行う可能性を高めます。

オプティマイザ統計は、innodb_stats_persistent=ON または個々のテーブルが STATS_PERSISTENT=1 で定義されている場合、ディスクに永続化されます。innodb_stats_persistent はデフォルトで有効になっています。

以前は、サーバーの再起動時および他のタイプの操作後にオプティマイザ統計がクリアされ、次のテーブルアクセスで再計算されていました。 したがって、統計を再計算すると、クエリー実行計画の選択肢やクエリーパフォーマンスの変動につながる様々な見積りが生成される可能性があります。

永続統計は、mysql.innodb_table_stats テーブルおよび mysql.innodb_index_stats テーブルに格納されます。 セクション15.8.10.1.5「InnoDB 永続的統計テーブル」を参照してください。

オプティマイザ統計をディスクに永続化しない場合は、セクション15.8.10.2「非永続的オプティマイザ統計のパラメータの構成」 を参照してください

15.8.10.1.1 永続オプティマイザ統計の自動統計計算の構成

デフォルトで有効になっている 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 テーブルに追加されます。

15.8.10.1.2 個々のテーブルのオプティマイザ統計パラメータの構成

innodb_stats_persistentinnodb_stats_auto_recalc および innodb_stats_persistent_sample_pages はグローバル変数です。 これらのシステム全体の設定をオーバーライドし、個々のテーブルのオプティマイザ統計パラメータを構成するには、CREATE TABLE ステートメントまたは ALTER TABLE ステートメントで STATS_PERSISTENTSTATS_AUTO_RECALC および STATS_SAMPLE_PAGES 句を定義します。

  • STATS_PERSISTENT では、InnoDB テーブルに対して persistent statistics を有効にするかどうかを指定します。 値が DEFAULT の場合、テーブルの永続統計設定は innodb_stats_persistent 設定によって決定されます。 1 の値を指定するとテーブルの永続統計が有効になり、0 の値を指定するとこの機能は無効になります。 個々のテーブルの永続統計を有効にした後、ANALYZE TABLE を使用して、テーブルデータのロード後に統計を計算します。

  • STATS_AUTO_RECALC では、persistent statistics を自動的に再計算するかどうかを指定します。 値が DEFAULT の場合、テーブルの永続統計設定は innodb_stats_auto_recalc 設定によって決定されます。 1 の値を指定すると、テーブルデータの 10% が変更されたときに統計が再計算されます。 値が 0 の場合、テーブルの自動再計算は行われません。 値 0 を使用する場合は、テーブルに大幅な変更を加えた後、ANALYZE TABLE を使用して統計を再計算します。

  • STATS_SAMPLE_PAGES では、ANALYZE TABLE 操作などによって、インデックス付けされたカラムのカーディナリティおよびその他の統計が計算される場合にサンプリングするインデックスページの数を指定します。

次の 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;
15.8.10.1.3 InnoDB オプティマイザ統計でサンプリングされるページの数の構成

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

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

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

15.8.10.1.4 永続統計計算への削除マーク付きレコードの組込み

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

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

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

15.8.10.1.5 InnoDB 永続的統計テーブル

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

表 15.6 innodb_table_stats のカラム

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

表 15.7 innodb_index_stats のカラム

カラム名 説明
database_name データベース名
table_name テーブル名、パーティション名、またはサブパーティション名
index_name インデックス名
last_update 行が最後に更新された時刻を示すタイムスタンプ
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 を実行して統計の同期再計算を開始すると、ステートメントはレプリケートされ (ロギングを抑制していないかぎり)、レプリカで再計算が行われます。

15.8.10.1.6 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 行のサンプルデータを挿入すると、テーブル 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 カラムには、インデックスの最初の 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 行のサンプルデータを挿入すると、テーブル 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_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              |
+------------+--------------+------------+------------------+

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% 行があります。 セカンダリインデックス (c,d) には 2 つのカラムのみが定義されていますが、InnoDB では一意でないすべてのインデックスに主キーが接尾辞として付加されるため、セカンダリインデックスには 4 つの n_diff% 行があります。 その結果、セカンダリインデックスカラム (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 つ存在することを示します。 カラム c, d, a および b の個別値の数を確認するには、カラム c, d, a のデータおよびテーブル t1b を表示します。これらには 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 カラムに示されています。

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

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;

関連キーワード:  InnoDB, テーブル, 統計, カラム, stat, インデックス, stats, 構成, 永続, TABLE