MySQLでクエリを実行する場合、クエリオプティマイザがテーブルやインデックスの統計情報を使用して、最適な実行計画を選択します。InnoDBストレージエンジンでは、この統計情報を自動的に再計算する機能があり、この自動更新の頻度やサンプル数を制御するための設定が存在します。
MySQLの統計情報とは
MySQLの統計情報は、データベースのテーブルやインデックスに関するメタデータであり、クエリオプティマイザが最適な実行計画を選択するために使用されます。これらの統計情報には、行数、データ分布、インデックスの選択性などが含まれます。
この情報が更新されていないと、クエリの実行計画が最適でなくなり、パフォーマンスが低下する場合があります。逆に、統計情報の更新が高頻度で発生してしまい、更新によるオーバーヘッドや、一時的に統計情報の偏りが発生することもあります。
このため、統計情報の更新は適切なタイミングで行われることが重要です。
統計情報更新まわりの設定
統計情報更新に関する設定がいくつか存在します。
innodb_stats_auto_recalc
InnoDBが統計情報を自動で再計算するかどうかを制御します。再計算のタイミングや挙動は後述します。この設定は、デフォルトで有効になっています。
mysql> show variables like 'innodb_stats_auto_recalc'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_auto_recalc | ON | +--------------------------+-------+ 1 row in set (0.00 sec)
innodb_stats_persistent
InnoDBが統計情報をディスクに永続化するかどうかを制御します。この設定は、デフォルトで有効になっています。
mysql> show variables like 'innodb_stats_persistent'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_stats_persistent | ON | +-------------------------+-------+ 1 row in set (0.00 sec)
innodb_stats_persistent_sample_pagesとinnodb_stats_transient_sample_pages
InnoDBが統計情報を収集するためにサンプリングするページ数を制御します。innodb_が有効の場合はinnodb_が使用され、無効の場合はinnodb_が使用されます。
値が大きいほど計算に利用されるサンプル数が増えるため、統計情報の精度が向上しますが、計算にかかる時間も増加します。逆に値が小さいと計算は速くなりますが、偏った統計情報が作成されてしまうことがあり、精度が低下する可能性があります。
これらの設定は、デフォルトで20, 8に設定されています。
mysql> show variables like 'innodb_stats_%_sample_pages'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_transient_sample_pages | 8 | +--------------------------------------+-------+ 2 rows in set (0.00 sec)
innodb_stats_on_metadata
メタデータにアクセスしたとき
mysql> show variables like 'innodb_stats_on_metadata'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_on_metadata | OFF | +--------------------------+-------+ 1 row in set (0.01 sec)
統計情報更新の挙動
innodb_stats_auto_recalcが有効の場合
innodb_が有効な場合、InnoDBはテーブル全体の10％が更新されたときに統計情報を自動的に再計算します。この10％の値についてはMySQLのコード上で定義されており、変更することはできません。
全体の10％の更新についてはインデックスに関わるカラムの値が更新された場合に含まれます。この値は前回の統計情報上の累計の更新レコード数を統計情報上のテーブルレコード数で割った値になります。時間によるリセットは行われません。
具体的には更新には以下が含まれます。
- 挿入・
削除が行われた場合 - テーブルのインデックスに関わるカラムの値が更新された場合
- テーブルのインデックスが変更された場合
インデックスに関わらないからのカラムの値が更新された場合や、カラムの値が更新されない場合は、統計情報の再計算は行われません。
mysql> select * from mysql.innodb_index_stats where database_name = 'stats_calc_sample'; +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | n_diff_pfx01 | 1000 | 6 | id | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | n_leaf_pages | 6 | NULL | Number of leaf pages in the index | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | size | 7 | NULL | Number of pages in the index | +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec) mysql> UPDATE users SET age = age + 1; Query OK, 1000 rows affected (0.03 sec) Rows matched: 1000 Changed: 1000 Warnings: 0 mysql> select * from mysql.innodb_index_stats where database_name = 'stats_calc_sample'; +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | n_diff_pfx01 | 1000 | 6 | id | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | n_leaf_pages | 6 | NULL | Number of leaf pages in the index | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | size | 7 | NULL | Number of pages in the index | +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec) mysql> UPDATE users SET id = id; Query OK, 0 rows affected (0.01 sec) Rows matched: 1000 Changed: 0 Warnings: 0 mysql> select * from mysql.innodb_index_stats where database_name = 'stats_calc_sample'; +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | n_diff_pfx01 | 1000 | 6 | id | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | n_leaf_pages | 6 | NULL | Number of leaf pages in the index | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:05:56 | size | 7 | NULL | Number of pages in the index | +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec)
innodb_stats_auto_recalcが無効の場合
innodb_が無効な場合、InnoDBは統計情報を自動的に再計算しません。この場合、統計情報は手動で更新する必要があります。手動で更新するには、ANALYZE TABLEを使用します。
mysql> ANALYZE TABLE stats_calc_sample.users; +-------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------+---------+----------+----------+ | stats_calc_sample.users | analyze | status | OK | +-------------------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats where database_name = 'stats_calc_sample'; +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | stats_calc_sample | users | PRIMARY | 2025-07-12 12:14:39 | n_diff_pfx01 | 1000 | 6 | id | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:14:39 | n_leaf_pages | 6 | NULL | Number of leaf pages in the index | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:14:39 | size | 7 | NULL | Number of pages in the index | +-------------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec)
テーブルごとの統計情報の設定
InnoDBでは、テーブルごとに統計情報の更新方法を制御するためのシステム変数も用意されています。これらの変数は、テーブルごとの統計情報の自動更新や永続化に影響を与えます。
以下のシステム変数を使用して、テーブルごとの統計情報の更新を制御できます。
- STATS_
AUTO_ RECALC - STATS_
PERSISTENT - STATS_
SAMPLE_ PAGES
CREATE TABLE users_non_stast_auto_calc (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)ENGINE=InnoDB,
STATS_PERSISTENT=1,
STATS_AUTO_RECALC=0,
STATS_SAMPLE_PAGES=20;
STATS_をOFFに設定している場合、innodb_がONであっても、統計情報は自動的に再計算されません。generate_とgenerate_は、テスト用のデータを生成するストアドプロシージャを事前に作成しています。
mysql> select * from mysql.innodb_index_stats where database_name = 'stats_calc_sample'; +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | stats_calc_sample | users | PRIMARY | 2025-07-12 12:22:56 | n_diff_pfx01 | 1000 | 6 | id | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:22:56 | n_leaf_pages | 6 | NULL | Number of leaf pages in the index | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:22:56 | size | 7 | NULL | Number of pages in the index | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:22:51 | n_diff_pfx01 | 1000 | 6 | id | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:22:51 | n_leaf_pages | 6 | NULL | Number of leaf pages in the index | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:22:51 | size | 7 | NULL | Number of pages in the index | +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 6 rows in set (0.00 sec) mysql> CALL generate_test_users(); Query OK, 1 row affected (0.48 sec) mysql> CALL generate_test_users_non(); Query OK, 1 row affected (0.45 sec) mysql> select * from mysql.innodb_index_stats where database_name = 'stats_calc_sample'; +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | stats_calc_sample | users | PRIMARY | 2025-07-12 12:23:16 | n_diff_pfx01 | 2000 | 11 | id | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:23:16 | n_leaf_pages | 11 | NULL | Number of leaf pages in the index | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:23:16 | size | 12 | NULL | Number of pages in the index | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:22:51 | n_diff_pfx01 | 1000 | 6 | id | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:22:51 | n_leaf_pages | 6 | NULL | Number of leaf pages in the index | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:22:51 | size | 7 | NULL | Number of pages in the index | +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 6 rows in set (0.00 sec) mysql> ANALYZE TABLE users_non_stast_auto_calc; +---------------------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+----------+ | stats_calc_sample.users_non_stast_auto_calc | analyze | status | OK | +---------------------------------------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats where database_name = 'stats_calc_sample'; +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | stats_calc_sample | users | PRIMARY | 2025-07-12 12:23:16 | n_diff_pfx01 | 2000 | 11 | id | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:23:16 | n_leaf_pages | 11 | NULL | Number of leaf pages in the index | | stats_calc_sample | users | PRIMARY | 2025-07-12 12:23:16 | size | 12 | NULL | Number of pages in the index | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:23:29 | n_diff_pfx01 | 2000 | 11 | id | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:23:29 | n_leaf_pages | 11 | NULL | Number of leaf pages in the index | | stats_calc_sample | users_non_stast_auto_calc | PRIMARY | 2025-07-12 12:23:29 | size | 12 | NULL | Number of pages in the index | +-------------------+---------------------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 6 rows in set (0.00 sec)
統計情報の更新によってパフォーマンスが下がる場合
高頻度で統計情報が更新されると、パフォーマンスに影響を与える可能性があります。特に、大量のデータが挿入、更新、削除されるテーブルでは、統計情報の再計算が頻繁に発生し、クエリの実行計画に悪影響を及ぼすことがあります。このような場合、以下の対策を検討できます。
innodb_を無効にして、手動で統計情報を更新するstats_ auto_ recalc - テーブルごとに
STATS_を無効にして、統計情報の自動更新を行わないAUTO_ RECALC innodb_を無効にして、統計情報の永続化を行わないstats_ persistent
まとめ
MySQLでの統計情報の更新について紹介しました。同じクエリを実行しても、統計情報によって実行計画が変わることがあります。ワークロードに応じて設定の調整、適切に更新を行い、クエリのパフォーマンスを最適化してください。