WeChat ID woqutech Intro 杭州沃趣科技创建于2012年，是一家专注为企业用户提供基于高性能、高可用、可扩展的开放数据库云平台解决方案的国产厂商。创始团队为原阿里巴巴数据库及运维团队核心骨干，凭借丰富的运维经验，为行业客户提供数据库云产品及软硬件一体化解决方案。 统计信息的作用 上周同事在客户现场遇到了由于统计信息的原因，导致应用数据迁移时间过慢，整个迁移差点失败。关键时刻同事发现测试环境与生产环境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时候会让你的语句走到索引，但是效率并不一定是好的。 相关链接 mysqldump与innobackupex备份过程你知多少（三） mysqldump与innobackupex备份过程你知多少（二） mysqldump与innobackupex备份过程你知多少（一） Performance Schema使用简介(一) 关于热点数据导出与加载的影响，多的是你不知道的事 “mysqlbinlog”工具做binlog server靠谱吗？ 沃趣科技与Mellanox联合打造证券行业高性能及同城双活数据库云平台 杭州沃趣科技股份有限公司创建于2012年（股票代码：839849），是一家专注为企业用户提供基于高性能、高可用、可扩展的开放数据库云平台解决方案的国产厂商。公司创始团队为原阿里巴巴数据库及运维团队核心骨干，凭借丰富的运维经验，为行业客户提供数据库云产品及软硬件一体化解决方案。 公司产品已广泛应用于证券、保险、医疗、广电传媒、银行、电信、能源电力、快递物流、公共事业、大型企业等，为这些行业用户持续提供行业解决方案及服务支持。 公司先后获得国家级高新技术企业、杭州市高新技术企业、杭州高新区瞪羚企业等称号，并设有杭州市安全可控数据库技术研发中心。公司总部位于杭州，同时在北京、上海、广州、南京、兰州建立了分支机构，拥有辐射全国的销售和服务体系。 我们始终坚信，数据是驱动企业创新的源动力！坚持围绕企业数据库做好一件事 ——让高性能触手可及！ Reward 长按二维码向我转账 受苹果公司新规定影响，微信 iOS 版的赞赏功能被关闭，可通过二维码转账支持公众号。 Scan QR Code via WeChat to follow Official Account