登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›事务处理对持久统计信息自动收集的影响 ...

事务处理对持久统计信息自动收集的影响

溥价 7 天前
事务处理对持久统计信息自动收集的影响

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对持久统计信息收集的影响

测试环境的系统变量值：
  1. greatsql> SHOW GLOBAL VARIABLES LIKE 'innodb_stats%';
  2. +--------------------------------------+-------------+
  3. | Variable_name                        | Value       |
  4. +--------------------------------------+-------------+
  5. | innodb_stats_auto_recalc             | ON          |
  6. | innodb_stats_include_delete_marked   | OFF         |
  7. | innodb_stats_method                  | nulls_equal |
  8. | innodb_stats_on_metadata             | OFF         |
  9. | innodb_stats_persistent              | ON          |
  10. | innodb_stats_persistent_sample_pages | 20          |
  11. | innodb_stats_transient_sample_pages  | 8           |
  12. +--------------------------------------+-------------+
复制代码
2.1 INSERT 操作
  1. greatsql> TRUNCATE TABLE test1;
  2. Query OK, 0 rows affected (0.05 sec)
  4. -- 开启事务，在空表test1中插入10万行数据
  5. greatsql> BEGIN;
  6. Query OK, 0 rows affected (0.00 sec)
  8. greatsql> SELECT now();INSERT INTO test1 SELECT * FROM LIMIT 100000;SELECT now();
  9. +---------------------+
  10. | now()               |
  11. +---------------------+
  12. | 2025-01-07 09:59:19 |
  13. +---------------------+
  14. 1 row in set (0.00 sec)
  16. Query OK, 100000 rows affected (2.73 sec)
  17. Records: 100000  Duplicates: 0  Warnings: 0
  19. +---------------------+
  20. | now()               |
  21. +---------------------+
  22. | 2025-01-07 09:59:21 |
  23. +---------------------+
  24. 1 row in set (0.00 sec)
  27. -- 事务没有提交，但统计信息已收集
  28. greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1' AND table_name='test1';
  29. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  30. | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
  31. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  32. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:19 | n_diff_pfx01 |         11 |           1 | id                                |
  33. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:19 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
  34. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:19 | size         |          1 |        NULL | Number of pages in the index      |
  35. | testdb1       | test1      | k_1        | 2025-01-07 09:59:19 | n_diff_pfx01 |         11 |           1 | k                                 |
  36. | testdb1       | test1      | k_1        | 2025-01-07 09:59:19 | n_diff_pfx02 |         11 |           1 | k,id                              |
  37. | testdb1       | test1      | k_1        | 2025-01-07 09:59:19 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
  38. | testdb1       | test1      | k_1        | 2025-01-07 09:59:19 | size         |          1 |        NULL | Number of pages in the index      |
  39. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  40. 7 rows in set (0.00 sec)
  42. greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1' AND table_name='test1';
  43. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  44. | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
  45. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  46. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:29 | n_diff_pfx01 |      98712 |          20 | id                                |
  47. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:29 | n_leaf_pages |       1371 |        NULL | Number of leaf pages in the index |
  48. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:29 | size         |       1379 |        NULL | Number of pages in the index      |
  49. | testdb1       | test1      | k_1        | 2025-01-07 09:59:29 | n_diff_pfx01 |      30169 |          20 | k                                 |
  50. | testdb1       | test1      | k_1        | 2025-01-07 09:59:29 | n_diff_pfx02 |     100268 |          20 | k,id                              |
  51. | testdb1       | test1      | k_1        | 2025-01-07 09:59:29 | n_leaf_pages |        128 |        NULL | Number of leaf pages in the index |
  52. | testdb1       | test1      | k_1        | 2025-01-07 09:59:29 | size         |        161 |        NULL | Number of pages in the index      |
  53. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  54. 7 rows in set (0.00 sec)
  57. -- 回滚事务
  58. greatsql> ROLLBACK;
  59. Query OK, 0 rows affected (2.64 sec)
  61. -- 没有重新收集统计信息，统计信息与表数据不匹配
  62. greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1' AND table_name='test1';
  63. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  64. | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
  65. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  66. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:29 | n_diff_pfx01 |      98712 |          20 | id                                |
  67. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:29 | n_leaf_pages |       1371 |        NULL | Number of leaf pages in the index |
  68. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:59:29 | size         |       1379 |        NULL | Number of pages in the index      |
  69. | testdb1       | test1      | k_1        | 2025-01-07 09:59:29 | n_diff_pfx01 |      30169 |          20 | k                                 |
  70. | testdb1       | test1      | k_1        | 2025-01-07 09:59:29 | n_diff_pfx02 |     100268 |          20 | k,id                              |
  71. | testdb1       | test1      | k_1        | 2025-01-07 09:59:29 | n_leaf_pages |        128 |        NULL | Number of leaf pages in the index |
  72. | testdb1       | test1      | k_1        | 2025-01-07 09:59:29 | size         |        161 |        NULL | Number of pages in the index      |
  73. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  74. 7 rows in set (0.00 sec)
  76. greatsql> SELECT COUNT(*) FROM test1;
  77. +----------+
  78. | count(*) |
  79. +----------+
  80. |        0 |
  81. +----------+
  82. 1 row in set (0.00 sec)
  85. -- analyze重新收集统计信息，统计信息才和表数据一致
  86. greatsql> ANALYZE TABLE test1;
  87. +---------------+---------+----------+----------+
  88. | Table         | Op      | Msg_type | Msg_text |
  89. +---------------+---------+----------+----------+
  90. | testdb1.test1 | analyze | status   | OK       |
  91. +---------------+---------+----------+----------+
  92. 1 row in set (0.06 sec)
  94. greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1' AND table_name='test1';
  95. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  96. | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
  97. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  98. | testdb1       | test1      | PRIMARY    | 2025-01-07 10:01:58 | n_diff_pfx01 |          0 |           1 | id                                |
  99. | testdb1       | test1      | PRIMARY    | 2025-01-07 10:01:58 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
  100. | testdb1       | test1      | PRIMARY    | 2025-01-07 10:01:58 | size         |          1 |        NULL | Number of pages in the index      |
  101. | testdb1       | test1      | k_1        | 2025-01-07 10:01:58 | n_diff_pfx01 |          0 |           1 | k                                 |
  102. | testdb1       | test1      | k_1        | 2025-01-07 10:01:58 | n_diff_pfx02 |          0 |           1 | k,id                              |
  103. | testdb1       | test1      | k_1        | 2025-01-07 10:01:58 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
  104. | testdb1       | test1      | k_1        | 2025-01-07 10:01:58 | size         |          1 |        NULL | Number of pages in the index      |
  105. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  106. 7 rows in set (0.00 sec)
复制代码
2.2 DELETE 操作
  1. greatsql> SELECT COUNT(*) FROM test1;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |   100000 |
  6. +----------+
  7. 1 row in set (0.15 sec)
  9. -- 开启事务，执行delete操作
  10. greatsql> BEGIN;
  11. Query OK, 0 rows affected (0.00 sec)
  13. greatsql> SELECT now();DELETE FROM test1;SELECT now();
  14. +---------------------+
  15. | now()               |
  16. +---------------------+
  17. | 2025-01-07 09:41:36 |
  18. +---------------------+
  19. 1 row in set (0.00 sec)
  21. Query OK, 100000 rows affected (1.87 sec)
  23. +---------------------+
  24. | now()               |
  25. +---------------------+
  26. | 2025-01-07 09:41:38 |
  27. +---------------------+
  28. 1 row in set (0.00 sec)
  30. -- 在delete开始时，进行了一次统计信息收集
  31. greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1' AND table_name='test1';
  32. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  33. | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
  34. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  35. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:36 | n_diff_pfx01 |      98712 |          20 | id                                |
  36. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:36 | n_leaf_pages |       1371 |        NULL | Number of leaf pages in the index |
  37. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:36 | size         |       1379 |        NULL | Number of pages in the index      |
  38. | testdb1       | test1      | k_1        | 2025-01-07 09:41:36 | n_diff_pfx01 |      32313 |          20 | k                                 |
  39. | testdb1       | test1      | k_1        | 2025-01-07 09:41:36 | n_diff_pfx02 |      99244 |          20 | k,id                              |
  40. | testdb1       | test1      | k_1        | 2025-01-07 09:41:36 | n_leaf_pages |        128 |        NULL | Number of leaf pages in the index |
  41. | testdb1       | test1      | k_1        | 2025-01-07 09:41:36 | size         |        161 |        NULL | Number of pages in the index      |
  42. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  43. 7 rows in set (0.00 sec)
  45. -- delete完成一段时间后（约10秒）进行了第二次统计信息收集
  46. greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1' AND table_name='test1';
  47. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  48. | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
  49. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  50. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:46 | n_diff_pfx01 |          0 |          20 | id                                |
  51. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:46 | n_leaf_pages |       1371 |        NULL | Number of leaf pages in the index |
  52. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:46 | size         |       1379 |        NULL | Number of pages in the index      |
  53. | testdb1       | test1      | k_1        | 2025-01-07 09:41:46 | n_diff_pfx01 |          0 |          20 | k                                 |
  54. | testdb1       | test1      | k_1        | 2025-01-07 09:41:46 | n_diff_pfx02 |          0 |          20 | k,id                              |
  55. | testdb1       | test1      | k_1        | 2025-01-07 09:41:46 | n_leaf_pages |        128 |        NULL | Number of leaf pages in the index |
  56. | testdb1       | test1      | k_1        | 2025-01-07 09:41:46 | size         |        161 |        NULL | Number of pages in the index      |
  57. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  58. 7 rows in set (0.00 sec)
  60. -- 回滚事务
  61. greatsql> ROLLBACK;
  62. Query OK, 0 rows affected (1.95 sec)
  64. -- 事务回滚后，统计信息与表数据不匹配
  65. greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1' AND table_name='test1';
  66. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  67. | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
  68. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  69. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:46 | n_diff_pfx01 |          0 |          20 | id                                |
  70. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:46 | n_leaf_pages |       1371 |        NULL | Number of leaf pages in the index |
  71. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:41:46 | size         |       1379 |        NULL | Number of pages in the index      |
  72. | testdb1       | test1      | k_1        | 2025-01-07 09:41:46 | n_diff_pfx01 |          0 |          20 | k                                 |
  73. | testdb1       | test1      | k_1        | 2025-01-07 09:41:46 | n_diff_pfx02 |          0 |          20 | k,id                              |
  74. | testdb1       | test1      | k_1        | 2025-01-07 09:41:46 | n_leaf_pages |        128 |        NULL | Number of leaf pages in the index |
  75. | testdb1       | test1      | k_1        | 2025-01-07 09:41:46 | size         |        161 |        NULL | Number of pages in the index      |
  76. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  77. 7 rows in set (0.00 sec)
  79. greatsql> SELECT COUNT(*) FROM test1;
  80. +----------+
  81. | count(*) |
  82. +----------+
  83. |   100000 |
  84. +----------+
  85. 1 row in set (0.15 sec)
  87. -- analyze重新收集统计信息，统计信息才和表数据一致
  88. greatsql> ANALYZE TABLE test1;
  89. +---------------+---------+----------+----------+
  90. | Table         | Op      | Msg_type | Msg_text |
  91. +---------------+---------+----------+----------+
  92. | testdb1.test1 | analyze | status   | OK       |
  93. +---------------+---------+----------+----------+
  94. 1 row in set (0.08 sec)
  96. greatsql> SELECT * FROM mysql.innodb_index_stats WHERE database_name='testdb1' AND table_name='test1';
  97. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  98. | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
  99. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  100. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:47:29 | n_diff_pfx01 |      98712 |          20 | id                                |
  101. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:47:29 | n_leaf_pages |       1371 |        NULL | Number of leaf pages in the index |
  102. | testdb1       | test1      | PRIMARY    | 2025-01-07 09:47:29 | size         |       1379 |        NULL | Number of pages in the index      |
  103. | testdb1       | test1      | k_1        | 2025-01-07 09:47:29 | n_diff_pfx01 |      32332 |          20 | k                                 |
  104. | testdb1       | test1      | k_1        | 2025-01-07 09:47:29 | n_diff_pfx02 |     100051 |          20 | k,id                              |
  105. | testdb1       | test1      | k_1        | 2025-01-07 09:47:29 | n_leaf_pages |        128 |        NULL | Number of leaf pages in the index |
  106. | testdb1       | test1      | k_1        | 2025-01-07 09:47:29 | size         |        161 |        NULL | Number of pages in the index      |
  107. +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
  108. 7 rows in set (0.01 sec)
复制代码
3. 总结


  • 数据量变化大（超过10%）的DML操作会导致2次统计信息收集，一次是DML开始时，一次是DML完成约10秒后。
  • DML操作是否COMMIT提交，不影响统计信息收集。
  • DML操作的rollback回滚，可能造成统计信息与表数据不一致。当大数据DML操作回滚后，可以执行ANALYZE TABLE重新收集表的统计信息。

Enjoy GreatSQL
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
相关推荐
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
溥价
7 天前

0

粉丝关注

8

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫