事務處理對持久統計信息自動收集的影響
作者：王慶勛
系統變量innodb_stats_auto_recalc（默認ON）控制表行更改超過10%時，是否自動計算統計信息。也可以通過創建或更改表時指定stats_auto_recalc子句為單個表配置自動統計重新計算。
1. 持久化統計信息
持久統計信息將統計信息存儲到磁盤，使它們在服務器重啟后保持不變，優化器更有可能在查詢時做出一致的選擇，從而提高執行計劃的穩定性。
在innodb_stats_persistent=ON（默認值）或表定義使用 stats_persistent=1時，優化器統計信息會持久化保存。
持久統計信息存儲在 mysql.innodb_table_stats 和 mysql.innodb_index_stats 表中，last_update 列可以看到上次更新統計信息的時間。
系統變量innodb_stats_auto_recalc（默認ON）控制表行更改超過10%時，是否自動計算統計信息。也可以通過創建或更改表時指定stats_auto_recalc子句為單個表配置自動統計重新計算。
由于自動統計信息收集是一個后臺線程，其處理過程與DML操作是異步的，在DML操作超過 10% 的表后，可能不會立即重新計算統計信息。在某些情況下，統計數據重新計算可能會延遲幾秒鐘。如果立即需要最新的統計信息，執行 ANALYZE TABLE 以啟動統計信息的同步計算。
事務的 commit 和 rollback 會影響統計信息的自動收集么？通過下面測試，可以回答這問題。
2. 測試commit和rollback對持久統計信息收集的影響
測試環境的系統變量值：
greatsql> SHOW GLOBALVARIABLESLIKE'innodb_stats%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+2.1 INSERT 操作
greatsql> TRUNCATE TABLE test1;
Query OK, 0 rows affected (0.05 sec)
-- 開啟事務，在空表test1中插入10萬行數據
greatsql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
greatsql> SELECTnow();INSERTINTO test1 SELECT * FROMLIMIT100000;SELECTnow();
+---------------------+
| now() |
+---------------------+
| 2025-01-07 09:59:19 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 100000rows affected (2.73 sec)
Records: 100000 Duplicates: 0Warnings: 0
+---------------------+
| now() |
+---------------------+
| 2025-01-0709:59:21 |
+---------------------+
1rowinset (0.00 sec)
-- 事務沒有提交，但統計信息已收集
greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1'AND table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:19 | n_diff_pfx01 | 11 | 1 | id |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:19 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:19 | size | 1 | NULL | Number of pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:19 | n_diff_pfx01 | 11 | 1 | k |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:19 | n_diff_pfx02 | 11 | 1 | k,id |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:19 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:19 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)
greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1'AND table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:29 | n_diff_pfx01 | 98712 | 20 | id |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:29 | n_leaf_pages | 1371 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:29 | size | 1379 | NULL | Number of pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:29 | n_diff_pfx01 | 30169 | 20 | k |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:29 | n_diff_pfx02 | 100268 | 20 | k,id |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:29 | n_leaf_pages | 128 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:29 | size | 161 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)
-- 回滾事務
greatsql> ROLLBACK;
Query OK, 0 rows affected (2.64 sec)
-- 沒有重新收集統計信息，統計信息與表數據不匹配
greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1'AND table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:29 | n_diff_pfx01 | 98712 | 20 | id |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:29 | n_leaf_pages | 1371 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:59:29 | size | 1379 | NULL | Number of pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:29 | n_diff_pfx01 | 30169 | 20 | k |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:29 | n_diff_pfx02 | 100268 | 20 | k,id |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:29 | n_leaf_pages | 128 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:59:29 | size | 161 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)
greatsql> SELECTCOUNT(*) FROM test1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
-- analyze重新收集統計信息，統計信息才和表數據一致
greatsql> ANALYZETABLE test1;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| testdb1.test1 | analyze | status | OK |
+---------------+---------+----------+----------+
1rowinset (0.06 sec)
greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1'AND table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb1 | test1 | PRIMARY | 2025-01-07 10:01:58 | n_diff_pfx01 | 0 | 1 | id |
| testdb1 | test1 | PRIMARY | 2025-01-07 10:01:58 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | PRIMARY | 2025-01-07 10:01:58 | size | 1 | NULL | Number of pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 10:01:58 | n_diff_pfx01 | 0 | 1 | k |
| testdb1 | test1 | k_1 | 2025-01-07 10:01:58 | n_diff_pfx02 | 0 | 1 | k,id |
| testdb1 | test1 | k_1 | 2025-01-07 10:01:58 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 10:01:58 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)2.2 DELETE 操作
greatsql> SELECT COUNT(*) FROM test1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.15 sec)
-- 開啟事務，執行delete操作
greatsql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
greatsql> SELECTnow();DELETEFROM test1;SELECTnow();
+---------------------+
| now() |
+---------------------+
| 2025-01-07 09:41:36 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 100000rows affected (1.87 sec)
+---------------------+
| now() |
+---------------------+
| 2025-01-0709:41:38 |
+---------------------+
1rowinset (0.00 sec)
-- 在delete開始時，進行了一次統計信息收集
greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1'AND table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:36 | n_diff_pfx01 | 98712 | 20 | id |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:36 | n_leaf_pages | 1371 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:36 | size | 1379 | NULL | Number of pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:36 | n_diff_pfx01 | 32313 | 20 | k |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:36 | n_diff_pfx02 | 99244 | 20 | k,id |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:36 | n_leaf_pages | 128 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:36 | size | 161 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)
-- delete完成一段時間后（約10秒）進行了第二次統計信息收集
greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1'AND table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:46 | n_diff_pfx01 | 0 | 20 | id |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:46 | n_leaf_pages | 1371 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:46 | size | 1379 | NULL | Number of pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:46 | n_diff_pfx01 | 0 | 20 | k |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:46 | n_diff_pfx02 | 0 | 20 | k,id |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:46 | n_leaf_pages | 128 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:46 | size | 161 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)
-- 回滾事務
greatsql> ROLLBACK;
Query OK, 0 rows affected (1.95 sec)
-- 事務回滾后，統計信息與表數據不匹配
greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1'AND table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:46 | n_diff_pfx01 | 0 | 20 | id |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:46 | n_leaf_pages | 1371 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:41:46 | size | 1379 | NULL | Number of pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:46 | n_diff_pfx01 | 0 | 20 | k |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:46 | n_diff_pfx02 | 0 | 20 | k,id |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:46 | n_leaf_pages | 128 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:41:46 | size | 161 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)
greatsql> SELECTCOUNT(*) FROM test1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.15 sec)
-- analyze重新收集統計信息，統計信息才和表數據一致
greatsql> ANALYZETABLE test1;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| testdb1.test1 | analyze | status | OK |
+---------------+---------+----------+----------+
1rowinset (0.08 sec)
greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1'AND table_name='test1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb1 | test1 | PRIMARY | 2025-01-07 09:47:29 | n_diff_pfx01 | 98712 | 20 | id |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:47:29 | n_leaf_pages | 1371 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | PRIMARY | 2025-01-07 09:47:29 | size | 1379 | NULL | Number of pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:47:29 | n_diff_pfx01 | 32332 | 20 | k |
| testdb1 | test1 | k_1 | 2025-01-07 09:47:29 | n_diff_pfx02 | 100051 | 20 | k,id |
| testdb1 | test1 | k_1 | 2025-01-07 09:47:29 | n_leaf_pages | 128 | NULL | Number of leaf pages in the index |
| testdb1 | test1 | k_1 | 2025-01-07 09:47:29 | size | 161 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.01 sec)3. 總結
- 數據量變化大（超過10%）的DML操作會導致2次統計信息收集，一次是DML開始時，一次是DML完成約10秒后。
- DML操作是否COMMIT提交，不影響統計信息收集。
- DML操作的rollback回滾，可能造成統計信息與表數據不一致。當大數據DML操作回滾后，可以執行ANALYZE TABLE重新收集表的統計信息。
責任編輯：武曉燕
來源：
GreatSQL
