厂家做网站,搭建企业网站具体过程,网站报备流程,wordpress 下载#x1f4ab;《博主介绍》#xff1a;✨又是一天没白过#xff0c;我是奈斯#xff0c;DBA一名✨ #x1f4ab;《擅长领域》#xff1a;✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux#xff0c;也在扩展大数据方向的知识面✌️… 《博主介绍》✨又是一天没白过我是奈斯DBA一名✨ 《擅长领域》✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux也在扩展大数据方向的知识面✌️ 大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注 哈喽各位小伙伴好久不见甚是想念。今天给大家上强度讲解一下和SQL优化有关的内容——统计信息、优化器、执行计划、hint干预等。众所周知优化器在SQL执行过程中扮演着至关重要的角色它依赖于统计信息来为每个SQL语句制定最优的执行计划。而这些统计信息对于优化器的决策具有决定性的影响。因此了解和掌握统计信息、优化器、执行计划、hint干预对于数据库的性能调优至关重要。接下来我们将探讨统计信息、优化器、执行计划、hint干预的相关知识帮助大家更好地理解并优化自己的数据库性能。 因为统计信息、优化器、执行计划、hint干预涉及到的内容过多为了使大家更好消化我将分成六篇文章来进行介绍以便大家因为篇幅过长而感到阅读疲惫。六篇的内容分别如下让大家先做了解
第一篇持久化和非持久化统计信息的深度剖析当前篇第二篇全面理解优化器和SQL语句的解析步骤第三篇SQL执行计划之访问路径第四篇SQL执行计划之多表连接第五篇精细化查询优化如何有效使用Hint对优化器的执行计划进行干预第六篇SQL性能优化实战案例 目录
1.1 持久化统计信息既innodb_stats_persistentON默认on生产必须持久化
1相关参数
2配置每张表的统计信息参数
案例一create表时配置表的持久化统计信息
3查看统计信息
4手动收集统计信息
4.1 analyze方式收集oracle也支持analyze是单表收集统计信息
案例一计算ANALYZE TABLE复杂性消耗的读取
4.2 mysqlcheck命令方式收集mysqlcheck命令是全表全库收集统计信息
58.0版本直方图的最新变化
6解决统计信息差别较大的问题执行计划受统计信息影响统计信息不准会导致执行计划不准
案例一通过设置STATS_SAMPLE_PAGES或者设置innodb_stats_persistent_sample_pages解决统计信息不准问题
1.2 非持久化统计信息既innodb_stats_persistentOFF默认on不推荐使用仅了解
1相关参数
2设置非持久化统计信息的两种方式 那让我们开始今天统计信息的介绍。 首先MySQL统计信息是指数据库通过采样、统计出来的表、索引的相关信息例如表的记录数、聚集索引page个数、字段Cardinality....。MySQL在生成执行计划时需要根据索引的统计信息进行估算计算出 最低代价 或者说是最小开销的执行计划。需要注意哦MySQL支持有限的索引统计信息因存储引擎不同而统计信息收集的方式也不同。而且MySQL官方关于统计信息的概念介绍几乎等同于无官方文档的链接在下面有兴趣的小伙伴可以看看不过对于已经接触过其它类型数据库的小伙伴而言比如oracle理解这个概念应该不在话下。在oracle中统计信息是非常丰富的在以后的篇幅中我也会介绍今天还是主要介绍MySQL的。相对于其它数据库MySQL统计信息无法手工删除并且MySQL 8.0之前的版本是没有直方图的。optimizer优化器根据统计信息对每个sql语句执行最优的执行计划执行计划受统计信息影响。 MySQL统计信息的存储分为两种非持久化和持久化统计信息。 官方文档对统计信息的介绍 MySQL :: MySQL 8.0 Reference Manual :: 17.8.10 Configuring Optimizer Statistics for InnoDB Oracle和MySQL统计信息的区别 Oracle统计信息是在特定的时间收集的全实例的不是自动收集。当对象还没有统计信息时那么先通过动态采样技术来选择执行计划默认2级别的动态采样采取对象的64个数据块进行分析。 MySQL默认的持久化统计信息自动进行收集统计信息但是需要注意MySQL InnoDB默认是以表为单位来收集和存储统计数据的。 1.1 持久化统计信息既innodb_stats_persistentON默认on生产必须持久化 持久化统计信息在数据库重启统计信息不丢失统计信息会被持久化到物理表中会给出最优的执行计划稳定和精确对于大表也节省了收集统计信息的所需资源。5.6.6开始默认使用了持久化统计信息。 持久化统计信息在以下情况会被自动更新 1)innodb_stats_auto_recalc为on自动更新统计信息。阈值是表中行数的10%发生更改。 2)create table、create index、alter table、truncate table等涉及数据修改的DDL语句 3)手动更新统计信息注意执行过程中会加读锁analyze table tablename。 4)dict_stats_thread线程专门处理统计信息。 5)而如果变更的行数超过16n_rows/166.25%或者表修改的行超过1/6或者20亿条时 1相关参数 一、innodb_stats_persistent 参数含义是否启用持久化统计信息功能 默认值ON 作用变量控制统计信息是否持久化统计信息在早期的MySQL中是不持久化在新版本的MySQL中持久化统计信息是默认的选项。当变量打开时统计信息就会被持久化到物理表中统计信息会更加的稳定和精确对于大表也节省了收集统计信息的所需资源。如果为off可能会频繁地重新计算统计信息这可能会导致查询执行计划的变化。 二、innodb_stats_auto_recalc 参数含义是否自动触发更新统计信息 默认值ON 触发阈值表变化的数据是否超过10%超过自动收集统计信息。 作用InnoDB会长期追踪每一张表的行数判断更新的记录是否超过表记录总数的1/10超过那么就把这张表加入到后台的recalc pool中。由于自动统计信息重新计算发生在后台是异步在运行影响超过10的表的DML操作时即innodb_stats_auto_recalc启用后可能不会立即重新计算统计信息。在某些情况下统计重新计算可能会延迟几秒钟(10s)。如果在更改表的重要部分之后立即需要最新统计信息请运行ANALYZE TABLE以启动统计信息的同步前台重新计算。 如果禁用了innodb_stats_auto_recalc请在对索引列进行实质性更改后通过为每个适用的表发出ANALYZE TABLE语句来确保统计信息的准确性。 此设置适用于启用innodb_stats_persistent选项时创建的表。也可以在CREATE TABLE或者ALTER TABLE时通过STATS_AUTO_RECALC语法来指定比率。 三、innodb_stats_persistent_sample_pages 参数含义持久化统计信息采样的索引页数。分析配置的页数优化器根据统计信息给出执行计划 默认值20 作用在估计索引列的基数和其他统计信息(例如由ANALYZE TABLE计算的统计信息)时要采样的索引页数。增加该值可以提高索引统计的准确性从而改进查询执行计划但代价是在InnoDB表执行ANALYZE TABLE时增加I/O。 该值设置的越大统计出的n_rows值越精确但是统计耗时也就最久 该值设置的越小统计出的n_rows值越不精确但是统计耗时特别少。 1)统计信息不够准确优化器选择次优计划如果确定统计信息不够准确则应增加innodb_stats_persistent_sample_pages的值直到统计估计值足够准确。但是过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢。 2)ANALYZE TABLE太慢在这种情况下应减少innodb_stats_persistent_sample_pages直到ANALYZE TABLE执行时间可以接受。但是过多地降低该值可能会导致生成不准确的统计信息和执行计划的问题。 四、innodb_stats_include_delete_marked 默认值OFF 作用在5.7.16中引入的此参数默认为不启用表示在未提交的事务有从表中删除行则InnoDB在收集统计信息时将会排除这些delete_marked行。这可能会导致除READ UNCOMMITTED之外的事务隔离级别的事务运行的不是最佳的执行计划。 为了避免这种情况可以启用innodb_stats_include_delete_marked以确保在计算持久化统计信息时InnoDB包含Delete-marked记录。 2配置每张表的统计信息参数 innodb_stats_persistent、innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全局配置选项。若要覆盖这些系统范围的设置并为各个表配置统计信息参数可以在CREATE TABLE或ALTER TABLE语句中定义STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句。 一、STATS_PERSISTENT 含义指定是否为InnoDB表启用持久统计信息。 设置值 DEFAULT:表示表的持久统计信息设置由innodb_stats_persistent配置选项确定 1:表示启用表的持久统计信息 0:关闭此功能 二、STATS_AUTO_RECALC 含义指定是否自动触发InnoDB表的持久统计信息。 设置值 DEFAULT:表示表的持久统计信息设置由innodb_stats_auto_recalc配置选项确定 1:表示表中10的数据发生更改时将重新计算统计信息 0:禁用自动重新计算此表 三、STATS_SAMPLE_PAGES 含义指定在估计索引列的基数和其他统计信息(例如由ANALYZE TABLE计算的统计信息)时要采样的索引页数。 设置值 DEFAULT:表示持久化统计信息采样的页数由innodb_stats_persistent_sample_pages配置选项确定 案例一create表时配置表的持久化统计信息
CREATE TABLEt1(
id int(8) NOT NULL auto increment,
data varchar(255),
date datetime,
PRIMARY KEY (id),
INDEXDATE IX (date)
) ENGINEInnoDB,
STATS PERSISTENT1,
STATS AUTO RECALC1,
STATS SAMPLE PAGES25; 3查看统计信息
table statistics相关视图 mysql select * from mysql.innodb_table_stats where table_name表名; database_name数据库名 table_name表名 last_update统计信息最后一次更新时间sql执行计划受统计信息影响。 n_rows表的行数 clustered_index_size聚集索引的页的数量 sum_of_other_index_sizes其他索引的页的数量 mysql select * from information_schema.tables where table_name表名; mysql select * from information_schema.statistics where table_name表名; 注意mysql.innodb_table_stats会在持久化统计信息下自动更新而information_schema.tables和information_schema.statistics不会自动更新需要手动执行analyze table或者mysqlcheck命令方式收集所以统计信息以按照mysql.innodb_table_stats表的信息为准。 index statistics相关视图 mysql select * from mysql.innodb_index_stats where table_name表名; ---会在持久化统计信息下自动更新 database_name数据库名 table_name表名 index_name索引名 last_update统计信息最后一次更新时间sql执行计划受统计信息影响。 stat_name统计信息名 stat_value统计信息的值 sample_size采样大小 stat_description类型说明 4手动收集统计信息
4.1 analyze方式收集oracle也支持analyze是单表收集统计信息 innodb和myisam存储引擎都可以通过执行analyze table tablename来收集表和索引的统计信息。除非执行计划不准确否则不要轻易执行该操作如果是很大的表该操作会影响表的性能单表9亿行的收集秒级完成即使整个实例有2T并且上千张表通过mysqlcheck工具进行所有库的收集也是几分钟就完成亲测 由于Analyze table会更新数据字典里的统计信息表8.0因此在innodb_read_only 开关被打开时有可能会导致执行失败。在analyze table的过程中会持有InnoDB表的read only锁因此会存在短暂的阻塞用户写入更新删除的操作。除此之外analyze table要把table从table definition cache刷出来因此还会需要一个flush lock此时如果有长事务使用了这张表那么必须等待长事务结束。
注意ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定因此一定注意要在数据库不繁忙的时候执行相关的操作。 5.7语法 ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ... 8.0语法8.0中支持了直方图统计信息因此analyze table还扩充了Histogram语法 ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]TABLE tbl_nameUPDATE HISTOGRAM ON col_name [, col_name] ...[WITH N BUCKETS]ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]TABLE tbl_nameDROP HISTOGRAM ON col_name [, col_name] ... InnoDB表的ANALYZE TABLE复杂性消耗的读取 1)采样的页数由innodb_stats_persistent_sample_pages定义。 2)表中索引列的数量由多个数相加而成参考下面案例。 3)分区数量。如果表没有分区则分区数被视为1。 总结ANALYZE TABLE复杂性度innodb_stats_persistent_sample_pages * 表中索引列的数量多个数相加而成 * 分区数 * innodb_page_size 通常结果值越大ANALYZE InnoDB TABLE的执行时间越长。 innodb_stats_persistent_sample_pages定义在全局级别采样的页数。要设置单个表的采样页数请使用带有CREATE TABLE或ALTER TABLE的STATS_SAMPLE_PAGES选项。 如果innodb_stats_persistent OFF则采样的页数由innodb_stats_transient_sample_pages定义。 案例一计算ANALYZE TABLE复杂性消耗的读取 ANALYZE TABLE复杂性度innodb_stats_persistent_sample_pages * 表中索引列的数量多个数相加而成 * 分区数 * innodb_page_size O(n_sample * (n_cols_in_uniq_i n_cols_in_non_uniq_i n_cols_in_pk * (1 n_non_uniq_i)) * n_part * innodb_page_size) n_sample是取样的页数(定义为innodb_stats_persistent_sample_pages) n_cols_in_uniq_i所有唯一索引中所有列的总数(不包括主键列) n_cols_in_non_uniq_i所有非唯一索引中所有列的总数 n_cols_in_pk主键中的列数(如果没有定义主键InnoDB在内部创建单列主键) n_non_uniq_i表中非唯一索引的数目 n_part是分区的数量。如果没有定义分区则该表被视为单个分区。 innodb_page_sizeinnodb每个页的大小是16K且不可更改 SQL CREATE TABLE t (a INT,b INT,c INT,d INT,e INT,f INT,g INT,h INT,PRIMARY KEY (a, b),UNIQUE KEY i1uniq (c, d),KEY i2nonuniq (e, f),KEY i3nonuniq (g, h)
);SQL SELECT index_name, stat_name, stat_descriptionFROM mysql.innodb_index_stats WHEREdatabase_nametest ANDtable_namet ANDstat_name like n_diff_pfx%;n_cols_in_uniq_i所有唯一索引中不包括主键列的所有列的总数为2c和d
n_cols_in_non_uniq_i所有非唯一索引中所有列的总数为4efg和h
n_cols_in_pk主键中的列数为2a和b
n_non_uniq_i表中非唯一索引的数量是2i2nonuniq和i3nonuniq
n_part分区数是1。 那么读取t表 innodb_stats_persistent_sample_pages20 n_cols_in_uniq_i 2 n_cols_in_non_uniq_i4 n_cols_in_pk2 n_non_uniq_i2 n_part1 innodb_page_size16kb 估计表t读取20*(242*(12))*1*16kb3840kb为3.75M 4.2 mysqlcheck命令方式收集mysqlcheck命令是全表全库收集统计信息 mysqlcheck是用来检查、修复、优化、分析表。只有在数据库运行的状态下才可运行意味着不用停止服务操作。 mysqlcheck其实就是CHECK TABLE(检查表), REPAIR TABLE(修复表), ANALYZE TABLE(分析表)以及OPTIMIZE TABLE(优化表)的便捷操作集合利用指定参数将对于的SQL语句发送到数据库中进行执行。同样对于那些存储引擎的的支持也受对于表维护SQL语句的限制(如check 则不支持MEMORY表, repair 则不支持 InnoDB表)
注意ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定因此一定注意要在数据库不繁忙的时候执行相关的操作。 mysqlcheck参数
参数选项描述-A, --all-databases选择所有的库-B, --databases选择多个库-a, --analyze分析表ANALYZE TABLE-c, --check 检查表CHECK TABLE-C, --check-only-changed最后一次检查之后变动的表CHECK TABLE-m, --medium-check近似完全检查速度比--extended稍快CHECK TABLE-o, --optimize优化表OPTIMIZE TABLE--auto-repair自动修复表-g, --check-upgrade检查表是否有版本变更可用 auto-repair修复-F, --fast只检查没有正常关闭的表-f, --force忽悠错误强制执行-e, --extended表的百分百完全检查速度缓慢-q, --quick最快的检查方式在repair 时使用该选项则只会修复 index tree-r, --repair修复表REPAIR TABLE-s, --silent 只打印错误信息-V, --version显示版本 收集库所有表的统计信息 收集test库 [rootmgr1 ~]# mysqlcheck -uroot -p123456 -S /mysql/data/3306/mysql.sock --analyze --databases test 收集所有库 [rootmgr1 ~]# mysqlcheck -uroot -p123456 -S /mysql/data/3306/mysql.sock --analyze --all-databases SQL select * from mysql.innodb_table_stats where database_nametest; ---test库所有表的统计信息更新为最新 58.0版本直方图的最新变化 MySQL 5.7并没有提供直方图的功能某些情况下如数据分布不均仅仅更新统计信息不一定能得到准确的执行计划只能通过index hint的方式指定索引。 在MySQL 8.0中支持了直方图统计信息因此analyze table还扩充了Histogram语法参考官方文档即可。 直方图是MySQL 8.0中新增的统计信息方式Analyze table加上直方图语句就可以操作直方图的信息, 直方图并不是存储引擎层实现的而是在Server层利用InnoDB存储引擎实现的系统表mysql.column_statsMySQL利用JSON类型的字段来保存直方图的信息其实现的核心代码在sql/histogram目录下。 直方图的具体的操作包括更新直方图以及drop直方图其中更新直方图还可以重新指定bucket的数目需要注意的是直方图不支持加密表不支持GIS列以及JSON列以及不支持单列唯一索引的列。 通过histogram_generation_max_mem_size参数可以调整用于生成直方图的采样记录内存大小通过查看information_schema的column_statistic表可以查看sampling-rate。 最新的MySQL8.0.19中InnoDB实现了自己的采样算法来避免全表扫描。在MySQL计算直方图填充时会调用Handler层的ha_sample_init, ha_sample_next以及ha_sample_end接口。在8.0.19前InnoDB并没有实现sample的接口而是用的Handler层的默认实现rnd_next也就是全表扫描直到独到采样比率的数据为止。这里有一个问题如果采样率设置为10%那采样只是读前10%的记录。更科学的做法是在整棵索引树上均匀的采样。在新版本中终于有了InnoDB引擎层的sample实现。目前的代码只支持单线程的采样但是从代码架构看已经实现了parallel_reader的接口不久后一定会实现多线程并行的采样。InnoDB的采样是交给了单独的worker线程来实现的一般是对主键进行。整体思路就是根据采样比率相对平均的选择叶子节点页面假设采样率是10%那么会选择一个叶子页面后跳过9个叶子页面被选中的页面中会对所有的记录进行采样。 6解决统计信息差别较大的问题执行计划受统计信息影响统计信息不准会导致执行计划不准 如果自动更新持久化统计信息后发现与实际count(*)数据量差距较大可考虑增加表采样的数据页两种方式修改 修改一全局变量影响所有表 innodb_stats_persistent_sample_pages默认20个页面。持久化统计信息采样的页数。分析配置的页数优化器根据统计信息给出执行计划 缺点过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢。 该值设置的越大统计出的n_rows值越精确但是统计耗时也就最久 该值设置的越小统计出的n_rows值越不精确但是统计耗时特别少。 修改二CREATE/ALTER表的参数只影响设置的表 ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES40; ---经测试此处STATS_SAMPLE_PAGES的最大值是65535超出会报错。 STATS_SAMPLE_PAGES指定在估计索引列的基数和其他统计信息(例如由ANALYZE TABLE计算的统计信息)时要采样的索引页数。表示持久化统计信息采样的页数由innodb_stats_persistent_sample_pages配置选项确定。 案例一通过设置STATS_SAMPLE_PAGES或者设置innodb_stats_persistent_sample_pages解决统计信息不准问题
1创建表
SQL create table tb_700w like tb;
SQL insert into tb_700w select * from tb limit 7000000;SQL select * from mysql.innodb_table_stats where table_nametb_700w;
---tb_700w真实有700万行数据由于innodb_stats_persistent_sample_pages进行自动持久化统计信息采样只采集20页那么就会有误差 2设置STATS_SAMPLE_PAGES
SQL ALTER TABLE tb_700w STATS_SAMPLE_PAGES65535; ---此处STATS_SAMPLE_PAGES的最大值是65535超出会报错。
SQL analyze table tb_700w;SQL select * from mysql.innodb_table_stats where table_nametb_700w;
---收集单表的STATS_SAMPLE_PAGES的最大值是65535个页超出会报错。65535页还是不能给出准确的行数 3设置innodb_stats_persistent_sample_pages
注意ANALYZE TABLE复杂性度innodb_stats_persistent_sample_pages * 表中索引列的数量多个数相加而成 * 分区数 * innodb_page_size那么过多地增加innodb_stats_persistent_sample_pagesANALYZE InnoDB TABLE的执行时间越长。
SQL ALTER TABLE tb_700w STATS_SAMPLE_PAGESdefault; ---恢复默认STATS_SAMPLE_PAGES由innodb_stats_persistent_sample_pages配置选项确定
SQL show variables like innodb_stats_persistent_sample_pages; ---默认采集20页
算出innodb_stats_persistent_sample_pages最合适的值。公式innodb_stats_persistent_sample_pagesANALYZE TABLE复杂性度(大小)/表中索引列的数量(多个数相加而成)/分区数/innodb_page_size
SQL show create table tb_700w\G;SQL SELECT index_name, stat_name, stat_descriptionFROM mysql.innodb_index_stats WHEREdatabase_nametest ANDtable_nametb_700w ANDstat_name like n_diff_pfx%; 详细算法参考上面4手动收集统计信息的案例一计算ANALYZE TABLE复杂性消耗的读取 n_cols_in_uniq_i所有唯一索引中不包括主键列的所有列的总数为0 n_cols_in_non_uniq_i所有非唯一索引中所有列的总数为0 n_cols_in_pk主键中的列数为1id n_non_uniq_i表中非唯一索引的数量是0 n_part分区数是1。 那么读取tb_700w表 n_cols_in_uniq_i 0 n_cols_in_non_uniq_i0 n_cols_in_pk1 n_non_uniq_i0 n_part1 innodb_page_size16kb innodb_stats_persistent_sample_pages(1611Mx1024)/(001*(10))/1/16kb103104 SQL set global innodb_stats_persistent_sample_pages103104;
SQL analyze table tb_700w;SQL select * from mysql.innodb_table_stats where table_nametb_700w; 给出了最准确的统计信息 1.2 非持久化统计信息既innodb_stats_persistentOFF默认on不推荐使用仅了解 非持久化统计信息存储在内存里如果数据库重启统计信息将丢失在下一次访问表时重新计算。会导致频繁地重新计算统计信息这可能会导致查询执行计划的变化。不推荐使用也不是默认值。 当innodb_stats_persistent OFF或使用STATS_PERSISTENT 0创建或更改单张表时统计信息不会保留到磁盘。相反统计信息存储在内存中并在服务器关闭时丢失。某些业务和某些条件下也会定期更新统计数据。 非持久化统计信息在以下情况会被自动更新前提innodb_stats_on_metadata设置为on默认off 1)手动更新统计信息注意执行过程中会加读锁analyze table tablename 2)设置innodb_stats_on_metadataON默认off执行SHOW TABLE STATUS , SHOW INDEX查询INFORMATION_SCHEMA下的TABLES, STATISTICS 3)启用--auto-rehash选项这是默认设置。--auto-rehash选项会打开所有InnoDB表打开表的操作会导致统计数据重新计算。使用mysql client登录。 4)表第一次被打开 5)距上一次更新统计信息表1/16的数据被修改 总结非持久化统计信息的缺点显而易见数据库重启后如果大量表开始更新统计信息会对实例造成很大影响所以目前都会使用持久化统计信息。 1相关参数 一、innodb_stats_on_metadata 参数含义表示是否InnoDB在如SHOW TABLE STATUS或访问INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS操作期间更新统计信息。 默认值OFF 作用保留禁用的设置可以提高具有大量表或索引的模式的访问速度。它还可以提高涉及InnoDB表的查询的执行计划的稳定性。 此选项仅在优化器统计信息配置为非持久性时适用。当innodb_stats_persistent为off默认on启用持久化统计信息时生效。或者使用STATS_PERSISTENT0创建或修改单个表时优化器统计信息不会被持久化到磁盘。在关闭持久化统计信息时是否在show table status/查看information_schema的TABLESSTATISTICS表时更新统计信息亲测关闭innodb_stats_persistentoff在设置innodb_stats_on_metadata为on或者off下都使用show table status/查看information_schema的TABLES、STATISTICS表也不会更新统计信息了解即可生产环境必须开启持久化统计信息也是默认选项 二、innodb_stats_transient_sample_pages 参数含义表示每次随机采样页的数量 默认值8 作用当innodb_stats_persistent 0时innodb_stats_transient_sample_pages的值会影响所有InnoDB表和索引的索引采样。更改索引样本大小时请注意以下潜在的重大影响 像1或2这样的小值可能导致基数估计不准确。 增加innodb_stats_transient_sample_pages值可能需要更多磁盘读取。远大于8例如100的值可能导致打开表或执行SHOW TABLE STATUS所花费的时间显着减慢。优化器可能会根据索引选择性的不同估计选择非常不同的查询计划。 2设置非持久化统计信息的两种方式 1)全局变量影响所有表 innodb_stats_persistentOFF ---默认on启用持久化统计信息。变量控制统计信息是否持久化统计信息在早期的MySQL中是不持久化在新版本的MySQL中持久化统计信息是默认的选项。当变量打开时统计信息就会被持久化到物理表中统计信息会更加的稳定和精确对于大表也节省了收集统计信息的所需资源。如果为off可能会频繁地重新计算统计信息这可能会导致查询执行计划的变化。 2)CREATE/ALTER表的参数只影响设置的表 STATS_PERSISTENT0 ---是否启用InnoDB表的持久统计功能。 默认值由innodb_stats_persistent配置选项决定。 值1启用表的持久统计而值0关闭此特性。 在通过CREATE TABLE或ALTER TABLE语句启用持久统计信息后在将代表性数据加载到表中之后发出ANALYZE TABLE语句来计算统计信息。 STATS_PERSISTENT指定是否为InnoDB表启用持久统计信息。默认值由innodb_stats_persistent配置选项确定。1:表示启用表的持久统计信息; 0:关闭此功能
