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 条评论
登录 后参与评论

相关文章

来自专栏应用案例

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

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

2288
来自专栏杂烩

mycat安装使用 原

    github地址:https://github.com/MyCATApache/Mycat-Server/wiki

1382
来自专栏梅海峰的专栏

可重复读事务隔离级别之 django 解读

本文尝试结合 django 解释应用开发中并发访问数据库可能会遇到的可重复读引起的问题,希望能帮助大家在开发过程中有效避免类似问题。

3700
来自专栏idba

漫谈死锁

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

1574
来自专栏蓝天

高性能高可用的分布式唯一ID服务——mooon-uniq-id

源码位置:https://github.com/eyjian/mooon/tree/master/application/uniq_id。

792
来自专栏james大数据架构

微软官方提供的用于监控MS SQL Server运行状况的工具及SQL语句

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返...

2957
来自专栏Python

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

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

2589
来自专栏编程心路

语言小知识-MySQL数据库引擎

MySQL 作为全世界广受欢迎的数据库,被用于很多中小型的项目中,但是你对 MySQL 数据库的存储引擎了解多少呢?

1394
来自专栏野路子程序员

记录一次乌云漏洞裤搭建过程

5186
来自专栏工科狗和生物喵

【计算机本科补全计划】Mysql 学习小计(3)

正文之前 想到自己每天中午还要玩一小时手机,就自责不已,今天看成甲的好好学习一书,颇有收获,晚上写给大伙看,现在还是谢谢 Mysql,到了后面感觉越来越难了呢!...

2924

扫码关注云+社区

领取腾讯云代金券