GreatSQL / GreatSQL社区 /
正文

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

原创
GreatSQL
GreatSQL社区
04/02 09:55
阅读数 57
本文被收录于专区
数据库
进入专区参与更多专题讨论

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

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 重新收集表的统计信息。

Enjoy GreatSQL :)
<往 期 推 荐>
dbops 助力 GreatSQL 单机架构安装部署
【GreatSQL优化器系列】完整优化器流程图
GreatSQL 为何选择全表扫描而不选索引

《用三分钟学会一个MySQL知识》

<关于 Great SQL >

GreatSQL数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为MySQL或Percona Server for MySQL的理想可选替换。

💻社区官网:  https://greatsql.cn/ 
⏩Gitee  :  https://gitee.com/GreatSQL/GreatSQL
 ⏩GitHub  : https://github.com/GreatSQL/

🆙BiliBili  : https://space.bilibili.com/1363850082

（对文章有疑问或见解可去社区官网提出哦~）

加入 微信交流群
加入 QQ交流群
想看更多技术好文，点个" 在看" 吧！

本文分享自微信公众号 - GreatSQL社区（GreatSQL）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

展开阅读全文
持久统计信息优化器统计信息innodb_stats_persistentstats_persistentinnodb_table_stats

© 著作权归作者所有

举报
加载中
点击引领话题📣 发布并加入讨论🔥

关于作者

GreatSQL
Lv8
GreatSQL
私信
提问
文章
740
经验值
1.5W
粉丝
46
关注
1

作者的专辑

全部
GreatSQL社区(740)

作者的其它热门文章

▪ {{formatHtml(o.title)}}

热门资讯

1
TIOBE 5 月榜单：Python 统治世界
2
Linus 发布 Linux 6.15-rc6，顺便秀了自己用的樱桃机械键盘
3
Cursor 0.50 发布，简化定价与增强 AI 编程体验
4
DeepSeek 使用率下降 50%
5
Kimi 界面重构：从对话气泡，到多模态容器
6
不要让谷歌出售 Chrome
7
在微软任职 18 年的资深 TypeScript 工程师被裁员
8
Firefox 代码仓库已迁移至 GitHub
9
淘宝推出基于 Electron 开发的「桌面版」客户端
10
Spring AI 1.0.0 RC1 发布，5 月 20 日 GA

推荐关注

换一批
whoru
whoru
文章 194
访问 26.5W
ksfzhaohui
ksfzhaohui
文章 196
访问 44.7W
kiwisoft
kiwisoft
文章 42
访问 4.7W
OpenSCA
OpenSCA
文章 54
访问 1.5W
青苗
青苗
文章 29
访问 4.8W

热门软件

VoltDB - 基于内存的数据库系统
jQuery Mobile - HTML5 开发框架
ThinkPHP - 轻量级 PHP 开发框架
ClickHeat - 网站热图生成工具
reCAPTCHA - 专业校验码
Sphinx - 创建智能且美观的文档
LoadMask - jQuery插件
jQuery.spritely - jQuery动态背景插件
Subversion Edge
EasyImage - 图片效果制作的Jar包
Shindig - OpenSocial 容器
JSwiff - Java 的 Flash 类库
Sencha Touch - 手机上的HTML5框架
infoScoop - Java门户平台
OpenJDK - Java 开发环境
jQuery Raty - jQuery 星级评分插件
Live USB - USB随身碟
iBATIS - 数据持久层框架
PaintWeb - 在浏览器上绘图
EclipseME - Eclipse 手机应用开发插件
0 评论
0 收藏
0 赞
分享
ad close
返回顶部
顶部