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

立聪堂德州十三局店  论坛元老 | 2025-4-2 12:40:34 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 2301|帖子 2301|积分 6903

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

x
事务处理对长期统计信息自动收集的影响

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
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

立聪堂德州十三局店

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表