MySQL统计信息更新小结
2023-06-13 09:16:12 时间
参考材料:
- mysql官网文档:
MySQL 索引选择机制
MySQL优化器的工作之一是选择索引。通过选择索引,找到一个最优的执行方案,以最小的代价去执行语句。而评估代价大小的因素之一,就是扫描行数。因为扫描的行数越少,访问磁盘数据的次数越少,消耗的CPU资源就相应越少。另外,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
针对扫描行数,实际上MySQL在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条。它是通过统计信息来估算记录数的。这个统计信息就是索引的“区分度”。一个索引上不同的值越多,这个索引的区分度就越好。
MySQL使用“采样统计”的方式来维护统计信息。采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过1/M的时候,会自动触发重新做一次索引统计。
在MySQL中,有两种存储索引统计的方式,可以通过设置参数innodb_stats_persistent的值来选择:
- 设置为on的时候,表示统计信息会持久化存储。这时,默认的N是20,M是10。
- 设置为off的时候,表示统计信息只存储在内存中。这时,默认的N是8,M是16。
由于是采样统计,所以不管N是20还是8,这个基数都是很容易不准的。
MySQL 统计信息更新
- 默认情况下innodb_stats_persistent=ON,优化器的统计信息会持久化保存在mysql.innodb_table_stats和mysql.innodb_index_stats表里。STATS_SAMPLE_PAGES specifies the number of index pages to sample when cardinality and other statistics are calculated for an indexed column, by an ANALYZE TABLE operation, for example.
- innodb_stats_persistent_sample_pages控制每次更新统计信息时的采样数据,默认是20,多了会导致analyze命令执行变慢,少了可能导致统计数据不够准确。
- innodb_stats_auto_recalc参数默认也是开启的,当一个表数据更新超过10%的时候,会触发统计信息的更新计算。
mysql> show variables like '%innodb_stats_persistent%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
+--------------------------------------+-------+
2 rows in set (0.14 sec)
mysql> show variables like '%innodb_stats_auto_recalc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON |
+--------------------------+-------+
1 row in set (0.03 sec)
- 另外也可以在建表时指定这些参数,例如:CREATE TABLE `t1` ( `id` int(8) NOT NULL auto_increment, `data` varchar(255), `date` datetime, PRIMARY KEY (`id`), INDEX `DATE_IX` (`date`) ) ENGINE=InnoDB, STATS_PERSISTENT=1, STATS_AUTO_RECALC=1, STATS_SAMPLE_PAGES=25;
- 统计信息的更新是在数据库服务端异步进行的,因此并不一定会在每次执行DML后触发,哪怕表数据更新超过10%也不一定是实时就会触发统计信息的更新(有些场景会延迟几秒后再触发)。mysql> select * from mysql.innodb_table_stats where database_name = 'test' limit 5 ; +---------------+--------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+--------------+---------------------+--------+----------------------+--------------------------+ | test | name | 2022-08-09 21:30:42 | 4 | 1 | 0 | +---------------+--------------+---------------------+--------+----------------------+--------------------------+ mysql> analyze table name; +-----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+----------+ | test.name | analyze | status | OK | +-----------+---------+----------+----------+ 1 row in set (0.05 sec) mysql> select * from mysql.innodb_table_stats where database_name = 'test' limit 5 ; +---------------+--------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+--------------+---------------------+--------+----------------------+--------------------------+ | test | name | 2022-11-14 23:58:18 | 4 | 1 | 0 | +---------------+--------------+---------------------+--------+----------------------+--------------------------+ 5 rows in set (0.00 sec)
- 可以通过执行Analyze 命令主动触发统计信息的更新。
- analyze table命令从库也会执行。
- 如果将innodb_stats_auto_recalc设置为OFF,不开启自动更新统计信息,可以考虑通过定时任务的试,在业务低峰期触发Analyze命令来更新。
- 以下几种情况,不管innodb_stats_auto_recalc是ON还是OFF,都会更新innodb_index_stats表的索引统计信息:
- 表增加索引
- 表增加或者删除字段
- innodb_table_stats和innodb_index_stats这2个表的统计数据,其实也可以通过手动方式去修改它(直接update表数据),修改后,需要执行一下FLUSH TABLE命令更新对应的表统计信息让它加载生效.
相关文章
- MySQL字段长度定义——有利于数据质量(mysql字段长度定义)
- MySQL安装及默认设置指南(mysql默认安装)
- 必须遵循的MySQL执行计划(执行计划mysql)
- 每日MySQL统计:有效数据洞察(mysql统计每天的数据)
- MySQL分布式查询:让数据检索变得更快、更准确!(mysql分布式查询)
- 优化MySQL性能优化之过程优化实战(mysql过程)
- 性MySQL数据库中约束唯一性的实现(mysql约束唯一)
- MySQL统计函数实战研究(mysql的统计函数)
- MySQL统计数量的技巧(mysql取数量)
- 活跃用户MySQL实现每天活跃用户统计(mysql统计每天)
- MySQL在大数据分析中的应用(mysql大数据解决方案)
- MySQL 如何使用表分区和索引优化查询性能(mysql表分区索引)
- 如何达到MySQL最优配置?(mysql最优配置)
- MySQL中的地区表:精确查找位置信息(mysql地区表)
- 脚本批处理:自动执行MySQL脚本(.bat执行mysql)
- MySQL 快速查询编码技巧(mysql查询编码)
- MySQL技术深度解析:统计条件的使用(mysql统计条件)
- 优化你的MySQL查询时间:掌握统计查询技巧(mysql统计查询时间)
- 深入MySQL:如何显示表信息(mysql 显示表)
- 授权MySQL 正版授权带来的无限可能(mysql 正版)
- MySQL统计语句:分析大数据的强大工具(mysql 统计 语句)
- MySQL中妙用count函数快速统计(count函数mysql)
- MySQL中convert函数的作用和使用方法(mysql中covert)
- MySQL中文乱码问题解决方法小结(mysql个别中文问号)
- 情况使用CMD查看MySQL启动状态(cmd查看mysql启动)
- 1499错误MySQL的尴尬之处(1499错误mysql)
- MySQL高级分组统计,轻松掌握数据分析技巧(mysql中分组统计高级)
- MySQL中的内连接和外连接详解(mysql中内连接外连接)
- MySQL的主键不一定要用自增ID,正确使用UUID可以提升性能并防止信息泄露(mysql不要自增id)
- MySQL数据统计报告上周销售额数据分析(mysql 上周数数据)