自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

事務(wù)處理對持久統(tǒng)計信息自動收集的影響

作者：王慶勛
存儲 數(shù)據(jù)管理
系統(tǒng)變量innodb_stats_auto_recalc（默認ON）控制表行更改超過10%時，是否自動計算統(tǒng)計信息。也可以通過創(chuàng)建或更改表時指定stats_auto_recalc子句為單個表配置自動統(tǒng)計重新計算。

1. 持久化統(tǒng)計信息

持久統(tǒng)計信息將統(tǒng)計信息存儲到磁盤，使它們在服務(wù)器重啟后保持不變，優(yōu)化器更有可能在查詢時做出一致的選擇，從而提高執(zhí)行計劃的穩(wěn)定性。

在innodb_stats_persistent=ON（默認值）或表定義使用 stats_persistent=1時，優(yōu)化器統(tǒng)計信息會持久化保存。

持久統(tǒng)計信息存儲在 mysql.innodb_table_stats 和 mysql.innodb_index_stats 表中，last_update 列可以看到上次更新統(tǒng)計信息的時間。

系統(tǒng)變量innodb_stats_auto_recalc（默認ON）控制表行更改超過10%時，是否自動計算統(tǒng)計信息。也可以通過創(chuàng)建或更改表時指定stats_auto_recalc子句為單個表配置自動統(tǒng)計重新計算。

由于自動統(tǒng)計信息收集是一個后臺線程，其處理過程與DML操作是異步的，在DML操作超過 10% 的表后，可能不會立即重新計算統(tǒng)計信息。在某些情況下，統(tǒng)計數(shù)據(jù)重新計算可能會延遲幾秒鐘。如果立即需要最新的統(tǒng)計信息，執(zhí)行 ANALYZE TABLE 以啟動統(tǒng)計信息的同步計算。

事務(wù)的 commit 和 rollback 會影響統(tǒng)計信息的自動收集么？通過下面測試，可以回答這問題。

2. 測試commit和rollback對持久統(tǒng)計信息收集的影響

測試環(huán)境的系統(tǒng)變量值：

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)

-- 開啟事務(wù)，在空表test1中插入10萬行數(shù)據(jù)
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)


-- 事務(wù)沒有提交，但統(tǒng)計信息已收集
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)


-- 回滾事務(wù)
greatsql> ROLLBACK;
Query OK, 0 rows affected (2.64 sec)

-- 沒有重新收集統(tǒng)計信息，統(tǒng)計信息與表數(shù)據(jù)不匹配
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重新收集統(tǒng)計信息，統(tǒng)計信息才和表數(shù)據(jù)一致
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)

-- 開啟事務(wù)，執(zhí)行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開始時，進行了一次統(tǒng)計信息收集
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秒）進行了第二次統(tǒng)計信息收集
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)

-- 回滾事務(wù)
greatsql> ROLLBACK;
Query OK, 0 rows affected (1.95 sec)

-- 事務(wù)回滾后，統(tǒng)計信息與表數(shù)據(jù)不匹配
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重新收集統(tǒng)計信息，統(tǒng)計信息才和表數(shù)據(jù)一致
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. 總結(jié)

  • 數(shù)據(jù)量變化大（超過10%）的DML操作會導(dǎo)致2次統(tǒng)計信息收集，一次是DML開始時，一次是DML完成約10秒后。
  • DML操作是否COMMIT提交，不影響統(tǒng)計信息收集。
  • DML操作的rollback回滾，可能造成統(tǒng)計信息與表數(shù)據(jù)不一致。當(dāng)大數(shù)據(jù)DML操作回滾后，可以執(zhí)行ANALYZE TABLE重新收集表的統(tǒng)計信息。
責(zé)任編輯：武曉燕 來源： GreatSQL
事務(wù)處理信息自動收集
相關(guān)推薦

2009-07-15 17:41:55

iBATIS事務(wù)處理

2011-04-27 15:55:16

2009-09-14 19:55:03

LINQ事務(wù)處理

2009-07-09 18:15:42

JDBC事務(wù)處理

2014-01-22 13:37:53

2010-04-13 15:44:00

Oracle與SqlS

2011-04-27 16:09:48

SQL ServerSSIS

2010-05-17 14:59:05

MySQL事務(wù)處理

2009-04-15 17:03:15

OracleSQL Server事務(wù)處理

2010-01-04 13:06:50

ADO.NET事務(wù)

2009-11-13 17:01:07

ADO.NET事務(wù)處理

2022-06-22 05:42:32

數(shù)據(jù)庫事務(wù)處理分析查詢

2011-03-15 10:22:42

SQL Server 聯(lián)機事務(wù)處理

2014-02-11 09:07:31

2017-04-10 13:30:47

Redis數(shù)據(jù)庫命令

2009-11-04 15:05:45

ADO.NET事務(wù)

2022-06-13 10:42:21

分布式事務(wù)數(shù)據(jù)庫

2009-07-15 15:57:28

AWT或Swing

2015-03-18 09:33:41

大數(shù)據(jù)分布式系統(tǒng)事務(wù)處理

2009-06-16 11:42:00

JDBC事務(wù)處理JavaBean
點贊
收藏

51CTO技術(shù)棧公眾號