Analyze Tableがレプリケートされない様にNO_WRITE_TO_BINLOGオプションを付けた場合の
バイナリーログの確認。殆どのケースで参照側でAnalyzeが実行されても問題は無いと思うが、
サンプリングのオーバーヘッドが無い訳ではないので、念のためのオプションとして。By default, the server writes ANALYZE TABLE statements to the binary log so that they replicate to replicas. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.
13.7.3.1 ANALYZE TABLE Statement
マスターログの確認
mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.000049 | 156 | | | |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin-changelog.000049' limit 10;
+----------------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin-changelog.000049 | 4 | Format_desc | 487927918 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| mysql-bin-changelog.000049 | 125 | Previous_gtids | 487927918 | 156 | |
+----------------------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
Binary Logの確認
mysql> update t_note set name = 'Analayze Confirm' where id = 1000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show binlog events in 'mysql-bin-changelog.000049' limit 10;
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql-bin-changelog.000049 | 4 | Format_desc | 487927918 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| mysql-bin-changelog.000049 | 125 | Previous_gtids | 487927918 | 156 | |
| mysql-bin-changelog.000049 | 156 | Anonymous_Gtid | 487927918 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-changelog.000049 | 235 | Query | 487927918 | 326 | BEGIN |
| mysql-bin-changelog.000049 | 326 | Table_map | 487927918 | 388 | table_id: 127 (MER.t_note) |
| mysql-bin-changelog.000049 | 388 | Update_rows | 487927918 | 468 | table_id: 127 flags: STMT_END_F |
| mysql-bin-changelog.000049 | 468 | Xid | 487927918 | 499 | COMMIT /* xid=853553 */ |
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.00 sec)Analyze Tableの実行
mysql> analyze table t_note;
+------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+---------+----------+----------+
| MER.t_note | analyze | status | OK |
+------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> show binlog events in 'mysql-bin-changelog.000049' limit 15;
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql-bin-changelog.000049 | 4 | Format_desc | 487927918 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| mysql-bin-changelog.000049 | 125 | Previous_gtids | 487927918 | 156 | |
| mysql-bin-changelog.000049 | 156 | Anonymous_Gtid | 487927918 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-changelog.000049 | 235 | Query | 487927918 | 326 | BEGIN |
| mysql-bin-changelog.000049 | 326 | Table_map | 487927918 | 388 | table_id: 127 (MER.t_note) |
| mysql-bin-changelog.000049 | 388 | Update_rows | 487927918 | 468 | table_id: 127 flags: STMT_END_F |
| mysql-bin-changelog.000049 | 468 | Xid | 487927918 | 499 | COMMIT /* xid=853553 */ |
| mysql-bin-changelog.000049 | 499 | Anonymous_Gtid | 487927918 | 576 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-changelog.000049 | 576 | Query | 487927918 | 665 | use `MER`; analyze table t_note |
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
9 rows in set (0.00 sec)
Analyze NO_WRITE_TO_BINLOG Table
mysql> analyze NO_WRITE_TO_BINLOG table t_note;
+------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+---------+----------+----------+
| MER.t_note | analyze | status | OK |
+------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> show binlog events in 'mysql-bin-changelog.000049' limit 15;
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql-bin-changelog.000049 | 4 | Format_desc | 487927918 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| mysql-bin-changelog.000049 | 125 | Previous_gtids | 487927918 | 156 | |
| mysql-bin-changelog.000049 | 156 | Anonymous_Gtid | 487927918 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-changelog.000049 | 235 | Query | 487927918 | 326 | BEGIN |
| mysql-bin-changelog.000049 | 326 | Table_map | 487927918 | 388 | table_id: 127 (MER.t_note) |
| mysql-bin-changelog.000049 | 388 | Update_rows | 487927918 | 468 | table_id: 127 flags: STMT_END_F |
| mysql-bin-changelog.000049 | 468 | Xid | 487927918 | 499 | COMMIT /* xid=853553 */ |
| mysql-bin-changelog.000049 | 499 | Anonymous_Gtid | 487927918 | 576 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin-changelog.000049 | 576 | Query | 487927918 | 665 | use `MER`; analyze table t_note |
+----------------------------+-----+----------------+-----------+-------------+--------------------------------------+
9 rows in set (0.00 sec)
mysql>
備考：統計情報
Defaultではおおよそ10%のデータが更新された時に、自動的に20ページのサンプリングで統計情報が更新されます。
これが十分ではない大きなテーブルの場合、テーブル単位で設定を変更する事も可能です。勿論トレードオフもあります。
CREATE TABLE `t_stat_test` ( `id` int(8) NOT NULL auto_increment, `data` varchar(255), `date` datetime default now(), PRIMARY KEY (`id`), INDEX `DATE_IX` (`date`) ) ENGINE=InnoDB, STATS_PERSISTENT=1, STATS_AUTO_RECALC=1, STATS_SAMPLE_PAGES=25;
STATS_PERSISTENT 1の値を指定するとテーブルの永続統計が有効 0の値を指定するとテーブルの永続統計が無効 STATS_AUTO_RECALC 1の値を指定するとテーブルデータの10% が変更されたときに統計が再計算されます。 0の値を指定するとテーブルの自動再計算は行われません。
mysql> desc mysql.innodb_table_stats;
+--------------------------+
| Field |
+--------------------------+
| database_name |データベース名
| table_name |テーブル名
| last_update |行を更新した時間
| n_rows |テーブル内の行数
| clustered_index_size |PKのサイズ (ページ数)
| sum_of_other_index_sizes |PK以外のインデックス合計サイズ (ページ数)
+--------------------------+
mysql> desc mysql.innodb_index_stats;
+------------------+
| Field |
+------------------+
| database_name |データベース名
| table_name |テーブル名
| index_name |インデックス名
| last_update |行を更新した時間
| stat_name |stat_value カラムに値がレポートされている統計名
| stat_value |stat_name カラムで名前が指定されている統計の値
| sample_size |stat_value カラムに示されている推定値のサンプリングページ数
| stat_description |stat_name カラムで名前が指定されている統計の説明
+------------------+統計情報の確認
mysql> SELECT SUM(stat_value) pages, index_name,
-> SUM(stat_value)*@@innodb_page_size size
-> FROM mysql.innodb_index_stats WHERE table_name like 'city%'
-> AND stat_name = 'size' GROUP BY index_name;
+--------+--------------------+------------+
| pages | index_name | size |
+--------+--------------------+------------+
| 63784 | CountryCode | 1045037056 |
| 363742 | PRIMARY | 5959548928 |
| 2597 | idx_Population | 42549248 |
| 110656 | idx_Name_PT_ONLINE | 1812987904 |
| 6 | idex_multi | 98304 |
+--------+--------------------+------------+
5 rows in set (0.00 sec)
Histogram
mysql> SELECT TABLE_NAME, COLUMN_NAME,
-> HISTOGRAM->>'$."data-type"' AS 'data-type',
-> JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
+------------+-------------+-----------+--------------+
| TABLE_NAME | COLUMN_NAME | data-type | bucket-count |
+------------+-------------+-----------+--------------+
| city | CountryCode | string | 32 |
| city | District | string | 32 |
| city_poc | District | string | 10 |
| city_poc | Population | int | 10 |
+------------+-------------+-----------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT HISTOGRAM->>'$."sampling-rate"'
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE TABLE_NAME = "city" AND COLUMN_NAME = "CountryCode";
+---------------------------------+
| HISTOGRAM->>'$."sampling-rate"' |
+---------------------------------+
| 1.0 |
+---------------------------------+
1 row in set (0.00 sec)
ヒストグラム統計は、主にインデックス付けされていないカラムに役立ちます。 ヒストグラム統計が適用可能なカラムにインデックスを追加すると、オプティマイザが行の見積りを行うのにも役立ちます。 トレードオフは次のとおりです ①テーブルデータが変更された場合は、インデックスを更新する必要があります。 ②ヒストグラムはオンデマンドでのみ作成または更新されるため、テーブルデータの変更時にオーバーヘッドは発生しません。 一方、統計は、次回更新されるまで、テーブルの変更が発生すると徐々に期限切れになります。 オプティマイザは、ヒストグラム統計から取得したものよりも範囲オプティマイザ行の見積りを優先します。 オプティマイザが範囲オプティマイザが適用されると判断した場合、ヒストグラム統計は使用されません。