PDF (US Ltr)
- 36.1Mb
PDF (A4)
- 36.2Mb
このページは機械翻訳したものです。
永続オプティマイザ統計機能は、統計をディスクに格納し、サーバーの再起動後も永続させることで 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「非永続的オプティマイザ統計のパラメータの構成」 を参照してください
デフォルトで有効になっている 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 句を定義します。
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;
オプティマイザは、キー配分に関する推定 statistics を使用して、インデックスの相対 selectivity に基づいて実行計画のインデックスを選択します。 ANALYZE TABLE などの操作を行うと、InnoDB は、インデックスのカーディナリティーを推定するためにテーブル上の各インデックスからランダムなページをサンプリングします。 このサンプリング手法は、random dive と呼ばれます。
innodb_stats_persistent_sample_pages は、サンプリングされるページの数を制御します。 実行時に設定を調整して、オプティマイザで使用される統計の見積りの品質を管理できます。 デフォルト値は 20 です。 次の問題が発生した場合は、設定の変更を検討してください:
-
統計が十分ではなく、オプティマイザが最適でない計画を選択しています (
EXPLAIN出力を参照)。 インデックスの実際のカーディナリティ (インデックスカラムでSELECT DISTINCTを実行して決定) をmysql.innodb_index_statsテーブルの見積りと比較することで、統計の正確性をチェックできます。統計の精度が十分でないことが確認された場合は、統計の推定値が十分な精度になるまで
innodb_stats_persistent_sample_pagesの値を増やすようにしてください。 ただし、innodb_stats_persistent_sample_pagesを大きくしすぎると、ANALYZE TABLEの実行が遅くなる可能性があります。 -
ANALYZE TABLEが遅すぎる。 この場合は、ANALYZE TABLEの実行時間が許容可能になるまでinnodb_stats_persistent_sample_pagesを減らすようにしてください。 ただし、この値を小さくしすぎると、精度の低い統計および次善のクエリー実行計画という最初の問題につながる可能性があります。統計の精度と
ANALYZE TABLEの実行時間のバランスをとることができない場合は、ANALYZE TABLEの複雑さを減らすためにテーブル内のインデックス付きカラムの数を減らすか、またはパーティションの数を制限することを考慮してください。 主キーカラムは一意でない各インデックスに追加されるため、テーブルの主キーのカラム数も考慮することが重要です。関連情報については、セクション15.8.10.3「InnoDB テーブルに対する ANALYZE TABLE の複雑さの推定」を参照してください。
デフォルトでは、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 データベース内の内部的に管理されているテーブルに依存します。 これらのテーブルは、すべてのインストール、アップグレード、およびソースからのビルド手順で自動的に設定されます。
表 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 を実行して統計の同期再計算を開始すると、ステートメントはレプリケートされ (ロギングを抑制していないかぎり)、レプリカで再計算が行われます。
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 カラムには、次のタイプの統計が示されます。
size:stat_name=sizeである場合、stat_valueカラムには、インデックス内のページの総数が表示されます。n_leaf_pages:stat_name=n_leaf_pagesである場合、stat_valueカラムには、インデックス内のリーフページの数が表示されます。n_diff_pfx:NNstat_name=n_diff_pfx01である場合、stat_valueカラムには、インデックスの最初のカラム内の固有の値の数が表示されます。stat_name=n_diff_pfx02である場合、stat_valueカラムには、インデックスの最初の 2 つのカラム内の固有の値の数が表示されます。以下も同様です。stat_name=n_diff_pfxの場合、NNstat_descriptionカラムには、カウントされるインデックスカラムのカンマ区切りリストが表示されます。
カーディナリティデータを提供する n_diff_pfx 統計をさらに詳しく説明するために、前に紹介した NNt1 テーブルの例をもう一度検討してください。 次に示すように、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% 行があります。 行数は、インデックス内のカラム数に等しくなります。
注記
一意でないインデックスの場合、InnoDB は主キーのカラムを追加します。
index_name=PRIMARYおよびstat_name=n_diff_pfx01である場合、stat_valueは1です。これは、インデックスの最初のカラム (カラムa) 内に固有の値が 1 つ存在することを示します。 カラムa内の固有の値の数は、テーブルt1内のカラムaのデータを表示することによって確認されます。ここには、固有の値が 1 つ存在します (1)。 カウントされるカラム (a) は、結果セットのstat_descriptionカラムに示されています。index_name=PRIMARYおよびstat_name=n_diff_pfx02である場合、stat_valueは5です。これは、インデックスの 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_valueは1です。これは、インデックスの最初のカラム (カラムc) 内に固有の値が 1 つ存在することを示します。 カラムc内の固有の値の数は、テーブルt1内のカラムcのデータを表示することによって確認されます。ここには、固有の値が 1 つ存在します: (10)。 カウントされるカラム (c) は、結果セットのstat_descriptionカラムに示されています。index_name=i1およびstat_name=n_diff_pfx02である場合、stat_valueは2です。これは、インデックスの最初の 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_valueは2です。これは、インデックスの最初の 3 つのカラム (c,d,a) 内に固有の値が 2 つ存在することを示します。 カラムc、d、およびa内の固有の値の数は、テーブルt1内のカラムc、d、およびaのデータを表示することによって確認されます。ここには、固有の値が 2 つ存在します: (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の個別値の数を確認するには、カラムc,d,aのデータおよびテーブルt1の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_valueは2です。これは、インデックスの最初のカラム (カラムe) 内に固有の値が 2 つ存在することを示します。 カラムe内の固有の値の数は、テーブルt1内のカラムeのデータを表示することによって確認されます。ここには、固有の値が 2 つ存在します: (100) および (200)。 カウントされるカラム (e) は、結果セットのstat_descriptionカラムに示されています。index_name=i2uniqおよびstat_name=n_diff_pfx02である場合、stat_valueは5です。これは、インデックスの 2 つのカラム (e,f) 内に固有の値が 5 つ存在することを示します。 カラムeおよびfの個別値の数を確認するには、テーブルt1のカラムeおよびfのデータを表示します。これらには 5 つの個別値があります: (100,101)、(200,102)、(100,103)、(200,104) および (100,105)。 カウントされるカラム (e,f) は、結果セットのstat_descriptionカラムに示されています。
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;