阿里云支持与服务 + 关注
手机版

innodb的统计信息对optimizer成本预估影响实例

  1. 云栖社区>
  2. 阿里云支持与服务>
  3. 博客>
  4. 正文

innodb的统计信息对optimizer成本预估影响实例

手辨 2017-08-18 16:41:03 浏览7300 评论3

摘要: mysql会依据innodb表的数据变化阈值来自动收集和计算表的统计信息(innodb_stats_auto_recalc)以供优化器使用,统计信息的收集是先通过获取一部分符合条件的索引页中的leaf page(是leaf page,不是non-leaf page)的数据,然后通过对这些采集的lea

实为吾之愚见,望诸君酌之!闻过则喜,与君共勉

第一节 innodb引擎统计信息

mysql会依据innodb表的数据变化阈值来自动收集和计算表的统计信息(innodb_stats_auto_recalc)以供优化器使用,统计信息的收集是先通过获取一部分符合条件的索引页中的leaf page(是leaf page,不是non-leaf page)的数据,然后通过对这些采集的leaf page计算估计出不同值的数量,进而估算出的信息,信息采集的准确度除了和数据本身的构成有关,还与采集page数量有关,数量越多,采集精度越准确,在mysql5.6中引入了Persistent Optimizer Statistics来解决之前的Non-Persistent Optimizer Statistics带来的一些问题,可以使用innodb_stats_persistent_sample_pages/innodb_stats_sample_pages控制采集精度,innodb_stats_sample_pages已经不推荐使用。innodb_stats_persistent_sample_pages参数是全局的,如果想单独指定某个表的采集page数量,可以使用STATS_SAMPLE_PAGES选项,采集信息结果不准确甚至过度不准确会影响执行计划的生成,造成语句的执行出现问题以至于影响数据库的正常运行,这时可能就需要手动指定采集page数量来收集准确的统计信息,矫正执行计划。查看mysql的统计信息(5.6)可以从mysql.innodb_table_stats and mysql.innodb_index_stats以及information_schema.INNODB_SYS_TABLESTATS获取,以下测试多是基于自建mysql进行

第二节 准备数据和对比测试

2.1 建测试表

CREATE TABLE `MOCK_DATA` (

  `autoid` int(11) NOT NULL AUTO_INCREMENT,

  `id` int(11) DEFAULT NULL,

  `first_name` varchar(50) DEFAULT NULL,

  `last_name` varchar(50) DEFAULT NULL,

  `email` varchar(50) DEFAULT NULL,

  `gender` varchar(50) DEFAULT NULL,

  `ip_address` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`autoid`),

  KEY `first_name` (`first_name`),

  KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=16580327 DEFAULT CHARSET=latin1

其中autoid是clustered index,first_name和id是secondary index,且id是1至1000连续的数字循环插入,

即id列只有1至1000这些数字


2.2 生成测试数据

使用mockaroo临时生成16384000行测试数据:

a7912d9f8b22c01e383e7bbf0004a61683938df0

 

2.3 查询统计信息表

00d8a9bfff5338b1d33ed8a2c68451cb88818e5d

Index_name为id的stat_value为15185,即以index列为索引,在16384000的数据里有15185个不同值(实际是1至1000个不同值),index_name索引有18368个索引页(nonleaf page+leaf page),有15985个叶子页(leaf page)

3d7b86312d6d92a864d75b98e0cff7c00eed49d7

b9b91a0cb123028aa3f0fb65f2a8085cc3998c58

该表经过统计,预估有16312847行数据,primary index有92736个索引页,除primary index外其他索引一共有40128个索引页(正好是innodb_index_stats中first_name和id索引页的和)

16d09123492d82fb3683156f353bea2ec1696c6f

Index name为id的索引其Cardinality是30377(与innodb_index_stats中的stat_value的distinct value的数值不同)

 

如上获取的一些统计信息是在innodb_stats_persistent_sample_pages20的情况下,手动analyze table MOCK_DATA生成的

 

2.4 执行查询

执行如下语句:

SET optimizer_trace="enabled=on";

EXPLAIN EXTENDED select count(1) from MOCK_DATA where id=1

865764e6f8c9ffdb91a937ea44bb7ce02560ad9a

查看INFORMATION_SCHEMA.OPTIMIZER_TRACE表的优化器追踪信息:

9b67678e40debb0d3f4b1d3e7501880efc73a986

fc030c447a079c21fc0583db70ad1e77c9a66399

以上是正常情况下的执行计划,下面进行修改统计信息数据,模拟统计信息对执行计划的影响在哪里

2.5 修改统计信息

只修改innodb_table_stats的n_rows变为10,同样执行:

SET optimizer_trace="enabled=on";

EXPLAIN EXTENDED select count(1) from MOCK_DATA where id=1

改完之后要执行flush table重新加载统计信息

如下:

bf3c867c2bf8d3cb02b12e4a356cc2858502097c

执行计划:

eaad84ff0495ea8279ac6b778ff07828c067256a

和之前的对比,执行计划有明显的变化,再次查看INFORMATION_SCHEMA.OPTIMIZER_TRACE表的优化器追踪信息:

a7a6b615e6a8abfee5ab36afd9d2b5a6dbd6d79a

abeef24daf1917c32b21b3c2cef1e4f8afae72dd

通过对比发现,虽然生成的执行计划使用的索引和access type没有错误,但是在生成过程中的cost与之前相比已经变化明显了。不准确的统计信息有很大可能对优化器的cost预估产生影响。所以我们可能有时候需要手工的进行统计信息的收集,除了统计信息还有很多情况会对optimizer的执行计划生成产生影响,比如索引的数量,索引数据的分布等等

第三节 统计信息收集

统计信息收集最常用的是analyze table  和optimize table,一般情况下这两个操作是有效的,但是也有少数情况analyze table和optimize table完全失效(获取不了准确的统计信息),即使我们知道表和索引的数据分布并非如此,我们也无法使用analyze和optimize来获取,此时可能就需要更精确的收集,拿上面的表举例子,其有16384000行数据,使用analyze 和optimize 在innodb_stats_persistent_sample_pages20的情况下,对数据量大的表和索引预估可能并不完全准确(16384000已经比较准确了),如果我们需要其预估完全准确的话(正常情况下不需要完全准确,会加重统计信息采集时间),我们可以对innodb表尝试如下两种方式:

1, 调大innodb_stats_persistent_sample_pages的值,然后再执行analyze table

2,单独设置该表的STATS_SAMPLE_PAGES数量

2.1 调整innodb_stats_persistent_sample_pages

通过如下信息:

00d8a9bfff5338b1d33ed8a2c68451cb88818e5d

因为只有一个Primary key有92634和leaf page,没有其他的unique key,这里

分别设置innodb_stats_persistent_sample_pages6000092634和92635,

然后与默认的20进行对比,如下:

1),set global innodb_stats_persistent_sample_pages=60000:

1292ba56633473e37d5bc27fc7b66eea019d5bfa

2),set global innodb_stats_persistent_sample_pages=92634:

f04dea1cb0fbac04b5748ecded093b1220ab59ae

3),set global innodb_stats_persistent_sample_pages=92635:

8b35f22db1e0810d2f7dd20c6604d02931f907fe

通过对比,当设置为92635(leaf page+1)时,数量才可以完全的准确,此时的mysql.innodb_index_stats表如下,已经很准确了,如下

c9456af4e244f97f9f3fb9bde60227878752e551

2.2 调整STATS_SAMPLE_PAGES

同样分别设置STATS_SAMPLE_PAGES6000092634和92635

1),STATS_SAMPLE_PAGES=60000

9516a00679878160d98ef466ed8ab9af7f35513d

2),STATS_SAMPLE_PAGES=92634以及STATS_SAMPLE_PAGES=92634

06b78c45649a52002303a6c0d11b5cf104a5533d

3),调整STATS_SAMPLE_PAGES设置为 65535,该参数最大为65535(STATS_SAMPLE_PAGES最大值,文档未标明,测试所得,应该是代码限制)

f78576bafd669e59ce46eb47a65013e29961dfb4

未能达到innodb_stats_persistent_sample_pages的效果,当STATS_SAMPLE_PAGES为65535时,此时innodb_index_stats的信息如下:

c5a5708af3e1ca214f856dbc148d962c75aa6502

2.3 问题延伸

如果该表除了primary key,还有一个unique key时?sample page也需要这么多吗?

2.4 测试结果

通过测试,发现默认采集20个leaf page一般情况是比较准确的,故正常情况下,我们是不需要手工干预的,可以交给mysql根据数据量的变化自动统计,太精确的采集page数量过多会造成系统的负担,只有当明确的得知统计信息错误(表中的数据分布并非如此),而且默认采集page数量使用analyze和optimize无法获取更精确的统计信息时可以尝试这样做

 

问题:为何STATS_SAMPLE_PAGES最大值代码里限制为65535暂时不清楚为何,测试所得其最大为65535

【云栖快讯】阿里巴巴小程序繁星计划,20亿补贴第一弹云应用立即开通购买,限量从速!  详情请点击

网友评论