MySQL中的统计信息相关参数介绍

统计信息的作用

上周同事在客户现场遇到了由于统计信息的原因,导致应用数据迁移时间过慢,整个迁移差点失败。关键时刻同事发现测试环境与生产环境SQL语句执行计划不一致,立刻收集统计信息才保证迁移得以正常完成。 统计信息对于SQL的执行时间有重要的影响,统计信息的不准确会导致SQL的执行计划不准确,从而致使SQL执行时间变慢,Oracle DBA非常了解统计信息的收集规则,同样在MySQL中也有相关的参数去控制统计信息。

相关参数

innodb_stats_auto_recalc

控制innodb是否自动收集统计信息,默认是打开的。当表中数据变化超过%10时候,就会重新计算统计信息。参数的生效依赖于建表时指定innodb_stats_persistent是打开的或CREATE TABLE , ALTER TABLE 时指定STATS_PERSISTENT=1采样page的个数通过参数innodb_stats_persistent_sample_pages来控制。

  • 测试验证

创建一张测试表,并在表上创建一个索引:

create table dhytest (id int) STATS_PERSISTENT=1;
create index idx_id on dhytest(id);

通过mysql.innodb_index_stats可以查看索引最后收集统计信息的时间,这里的聚集索引我们删除先不用去看,只看自己创建的二级索引

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:37:43]>select * from mysql.innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_diff_pfx01 |          0 |           1 | id                                |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_diff_pfx02 |          0 |           1 | id,DB_ROW_ID                      |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)

我们手工往表中插入数据,让数据的变化超过%10

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:37:56]>insert into dhytest values (10);
Query OK, 1 row affected (0.00 sec)

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:17]>insert into dhytest select * from dhytest;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:28]>insert into dhytest select * from dhytest;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:31]>insert into dhytest select * from dhytest;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:34]>insert into dhytest select * from dhytest;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:35]>insert into dhytest select * from dhytest;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

这时我们在查看下mysql.innodb_index_stats表,last_update时间发生了变化

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:36]>select * from mysql.innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_diff_pfx01 |          1 |           1 | id                                |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_diff_pfx02 |          2 |           1 | id,DB_ROW_ID                      |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)

innodb_stats_persistent

控制是否将统计信息持久到磁盘当中,设置此参数之后我们就不需要实时去收集统计信息了,因为实时收集统计信息在高并发下可能会造成一定的性能上影响,并且会导致执行计划有所不同。建议是将此参数打开,将innodb_stats_auto_recalc参数进行关闭。

innodb_stats_persistent_sample_pages

控制收集统计信息时采样的page数量,默认是20。收集的page数量越多,每次收集统计信息的实际则越长,但是统计信息也相对比较准确。

我们可以在创建表的时候对不同的表指定不同的page数量、是否将统计信息持久化到磁盘上、是否自动收集统计信息

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;

innodb_stats_on_metadata

此参数在5.6.5版本之前是默认开启的,设置此参数后当我们执行show index 或者 show table status 或者访问INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS表时就会收集统计信息,但是这样可能会导致执行计划改变。

在以前当表中记录变化超过1/16就会收集统计信息,但是现在如果设置了innodb_stats_persistent就不会有这样的说法了。

innodb_stats_include_delete_marked

5.6.35版本中新增的参数,就是在未提交的事务中如果我们删除了记录,收集统计信息的时候是排查这些删除了的记录的。这样就可能导致统计信息并不是很准确,设置此参数之后就是收集统计信息的时候包含未提交事务中被标记为已删除的数据。

innodb_stats_method

控制统计信息针对索引中NULL值的算法

当设置为nulls_equal 所有的NULL值都视为一个value group 当设置为nulls_unequal每一个NULL值被视为一个value group 设置为nulls_ignored时 NULL值被忽略

这个参数同事彭许生做过一些测试发现nulls_equal和nulls_unequal没有发现show index中的cardinality有不同的地方,但是如果设置为nulls_ignored的时候会有所不同。

  • 测 试

表结构数据

设置为nulls_ignored

设置为nulls_unequal

设置为nulls_equal

推荐配置

  • innodb_stats_method 统计信息的自动收集在高并发情况下可能会带来性能的抖动,建议将此参数关闭。
  • innodb_stats_persistent 建议打开此参数将统计信息持久化到磁盘上 。
  • innodb_stats_include_delete_marked建议设置开启,这样可以针对未提交事务中删除的数据也收集统计信息 。
  • innodb_stats_method经过测试和mos查看到的按默认配置就可以,当然如果设置nulls_ignored时候会让你的语句走到索引,但是效率并不一定是好的。

原文发布于微信公众号 - 沃趣科技(woqutech)

原文发表时间:2017-07-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JMCui

SQL优化二(SQL性能调优)

一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知...

3896
来自专栏应用案例

索引,视图,存储过程和触发器文档

实验案例一:验证索引的作用 1、首先创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为自动编号,班级为默认值“一班”。 ?...

2228
来自专栏Python

MySQL常见的库操作,表操作,数据操作集锦及一些注意事项

一 库操作(文件夹) 1 数据库命名规则 可以由字母、数字、下划线、@、#、$ 区分大小写 唯一性 不能使用关键字如 create select 不能单独使用数...

2329
来自专栏Netkiller

数据库进程间通信解决方案

数据库进程间通信解决方案 数据库与其他第三方应用程序进程间通信解决方案 摘要 你是否想过当数据库中的数据发生变化的时候出发某种操作?但因数据无法与其他进程通信(...

3816
来自专栏数据和云

深入内核:Oracle数据库里SELECT操作Hang解析

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

35510
来自专栏idba

漫谈死锁

一 前言 死锁是每个MySQL DBA 都会遇到的技术问题,本文是自己针对死锁学习的一个总结,了解死锁是什么,MySQL如何检测死锁,处理死锁,死锁的案例,...

1254
来自专栏架构师之路

数据库中间件cobar调研笔记

13年底负责数据库中间件设计时的调研笔记,拿出来和大家分享,轻拍。文章很长,可提前收藏,转发。 一,cobar是什么 开源的mysql的中间件服务 使用mysq...

3946
来自专栏Netkiller

数据库进程间通信解决方案IPC

数据库进程间通信解决方案 数据库与其他第三方应用程序进程间通信解决方案 摘要 你是否想过当数据库中的数据发生变化的时候出发某种操作?但因数据无法与其他进程通信...

3083
来自专栏Netkiller

数据库进程间通信解决方案

数据库进程间通信解决方案 数据库与其他第三方应用程序进程间通信解决方案 摘要 你是否想过当数据库中的数据发生变化的时候出发某种操作?但因数据无法与其他进程通信(...

3435
来自专栏乐沙弥的世界

MyCAT全局表描述及示例

1291

扫码关注云+社区