、MySQL、SQLserver、Linux也在积极的扩展IT方向的其他知识面✌✌️

❣️❣️❣️大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注❣️❣️❣️

哈喽各位小伙伴好久不见甚是想念。

今天给大家上强度讲解一下和SQL优化有关的内容——统计信息和执行计划。

众所周知优化器在SQL执行过程中扮演着至关重要的角色它依赖于统计信息来为每个SQL语句制定最优的执行计划。

而这些统计信息对于优化器的决策具有决定性的影响。

因此了解和掌握统计信息以及执行计划对于数据库的性能调优至关重要。

接下来我们将探讨统计信息和执行计划的相关知识帮助大家更好地理解并优化自己的数据库性能。

因为统计信息和执行计划涉及到的内容过多为了使大家更好消化我将分成三篇文章来进行介绍以便大家因为篇幅过长而感到阅读疲惫。

三篇的内容分别如下让大家先做了解

第一篇持久化和非持久化统计信息介绍当前篇

持久化统计信息既innodb_stats_persistentON默认on生产必须持久化

1相关参数

analyze方式收集oracle也支持analyze是单表收集统计信息

案例一计算ANALYZE

mysqlcheck命令方式收集mysqlcheck命令是全表全库收集统计信息

6解决统计信息差别较大的问题执行计划受统计信息影响统计信息不准会导致执行计划不准

案例一通过设置STATS_SAMPLE_PAGES或者设置innodb_stats_persistent_sample_pages解决统计信息不准问题

1.2

非持久化统计信息既innodb_stats_persistentOFF默认on不推荐使用仅了解

1相关参数

MySQL执行SQL会经过SQL解析和查询优化的过程解析器将SQL分解成数据结构并传递到后续步骤查询优化器发现执行SQL查询的最佳方案、生成执行计划。

查询优化器决定SQL如何执行依赖于数据库的统计信息。

optimizer优化器根据统计信息对每个sql语句执行最优的执行计划执行计划受统计信息影响。

MySQL

Oracle统计信息是在特定的时间收集的不是自动收集。

当对象还没有统计信息时那么先通过动态采样技术来选择执行计划默认2级别的动态采样采取对象的64个数据块进行分析。

1.1

持久化统计信息既innodb_stats_persistentON默认on生产必须持久化

持久化统计信息在数据库重启统计信息不丢失统计信息会被持久化到物理表中会给出最优的执行计划稳定和精确对于大表也节省了收集统计信息的所需资源。

5.6.6开始默认使用了持久化统计信息。

1)innodb_stats_auto_recalc为on自动更新统计信息。

阈值是表中行数的10%发生更改。

2)create

5)而如果变更的行数超过16n_rows/166.25%或者表修改的行超过1/6或者20亿条时

1相关参数

作用变量控制统计信息是否持久化统计信息在早期的MySQL中是不持久化在新版本的MySQL中持久化统计信息是默认的选项。

当变量打开时统计信息就会被持久化到物理表中统计信息会更加的稳定和精确对于大表也节省了收集统计信息的所需资源。

如果为off可能会频繁地重新计算统计信息这可能会导致查询执行计划的变化。

默认值ON

作用InnoDB会长期追踪每一张表的行数判断更新的记录是否超过表记录总数的1/10超过那么就把这张表加入到后台的recalc

pool中。

由于自动统计信息重新计算发生在后台是异步在运行影响超过10的表的DML操作时即innodb_stats_auto_recalc启用后可能不会立即重新计算统计信息。

在某些情况下统计重新计算可能会延迟几秒钟(10s)。

如果在更改表的重要部分之后立即需要最新统计信息请运行ANALYZE

如果禁用了innodb_stats_auto_recalc请在对索引列进行实质性更改后通过为每个适用的表发出ANALYZE

此设置适用于启用innodb_stats_persistent选项时创建的表。

也可以在CREATE

TABLE或者ALTER

TABLE时通过STATS_AUTO_RECALC语法来指定比率。

三、innodb_stats_persistent_sample_pages

参数含义持久化统计信息采样的索引页数。

分析配置的页数优化器根据统计信息给出执行计划

默认值20

TABLE计算的统计信息)时要采样的索引页数。

增加该值可以提高索引统计的准确性从而改进查询执行计划但代价是在InnoDB表执行ANALYZE

1)统计信息不够准确优化器选择次优计划如果确定统计信息不够准确则应增加innodb_stats_persistent_sample_pages的值直到统计估计值足够准确。

但是过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE

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记录。

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

DEFAULT:表示表的持久统计信息设置由innodb_stats_persistent配置选项确定

0:关闭此功能

DEFAULT:表示表的持久统计信息设置由innodb_stats_auto_recalc配置选项确定

0:禁用自动重新计算此表

含义指定在估计索引列的基数和其他统计信息(例如由ANALYZE

设置值

DEFAULT:表示持久化统计信息采样的页数由innodb_stats_persistent_sample_pages配置选项确定

CREATE

last_update统计信息最后一次更新时间sql执行计划受统计信息影响。

sum_of_other_index_sizes其他索引的页的数量

mysql

注意mysql.innodb_table_stats会在持久化统计信息下自动更新而information_schema.tables和information_schema.statistics不会自动更新需要手动执行analyze

table或者mysqlcheck命令方式收集所以统计信息以按照mysql.innodb_table_stats表的信息为准。

index

last_update统计信息最后一次更新时间sql执行计划受统计信息影响。

stat_name统计信息名

analyze方式收集oracle也支持analyze是单表收集统计信息

innodb和myisam存储引擎都可以通过执行analyze

table

tablename来收集表和索引的统计信息。

除非执行计划不准确否则不要轻易执行该操作如果是很大的表该操作会影响表的性能9亿行的数据很快亲测

由于Analyze

table会更新数据字典里的统计信息表8.0因此在innodb_read_only

only锁因此会存在短暂的阻塞用户写入更新删除的操作。

除此之外analyze

table要把table从table

TABLE执行期间将对表进行锁定因此一定注意要在数据库不繁忙的时候执行相关的操作。

5.7语法

1)采样的页数由innodb_stats_persistent_sample_pages定义。

总结ANALYZE

TABLE复杂性度innodb_stats_persistent_sample_pages

分区数

innodb_stats_persistent_sample_pages定义在全局级别采样的页数。

要设置单个表的采样页数请使用带有CREATE

TABLE或ALTER

OFF则采样的页数由innodb_stats_transient_sample_pages定义。

案例一计算ANALYZE

TABLE复杂性度innodb_stats_persistent_sample_pages

分区数

n_sample是取样的页数(定义为innodb_stats_persistent_sample_pages)

n_cols_in_uniq_i所有唯一索引中所有列的总数(不包括主键列)

n_cols_in_non_uniq_i所有非唯一索引中所有列的总数

n_cols_in_pk主键中的列数(如果没有定义主键InnoDB在内部创建单列主键)

n_part是分区的数量。

如果没有定义分区则该表被视为单个分区。

innodb_page_sizeinnodb每个页的大小是16K且不可更改

SQL

n_diff_pfx%;n_cols_in_uniq_i所有唯一索引中不包括主键列的所有列的总数为2c和d

n_cols_in_non_uniq_i所有非唯一索引中所有列的总数为4efg和h

n_non_uniq_i表中非唯一索引的数量是2i2nonuniq和i3nonuniq

那么读取t表

innodb_stats_persistent_sample_pages20

n_cols_in_uniq_i

估计表t读取20*(242*(12))*1*16kb3840kb为3.75M

4.2

mysqlcheck命令方式收集mysqlcheck命令是全表全库收集统计信息

mysqlcheck是用来检查、修复、优化、分析表。

只有在数据库运行的状态下才可运行意味着不用停止服务操作。

TABLE(检查表),

TABLE(优化表)的便捷操作集合利用指定参数将对于的SQL语句发送到数据库中进行执行。

同样对于那些存储引擎的的支持也受对于表维护SQL语句的限制(如check

则不支持MEMORY表,

TABLE执行期间将对表进行锁定因此一定注意要在数据库不繁忙的时候执行相关的操作。

mysqlcheck参数

5.7并没有提供直方图的功能某些情况下如数据分布不均仅仅更新统计信息不一定能得到准确的执行计划只能通过index

在MySQL

直方图并不是存储引擎层实现的而是在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

ALTER

---经测试此处STATS_SAMPLE_PAGES的最大值是65535超出会报错。

STATS_SAMPLE_PAGES指定在估计索引列的基数和其他统计信息(例如由ANALYZE

TABLE计算的统计信息)时要采样的索引页数。

表示持久化统计信息采样的页数由innodb_stats_persistent_sample_pages配置选项确定。

案例一通过设置STATS_SAMPLE_PAGES或者设置innodb_stats_persistent_sample_pages解决统计信息不准问题

1创建表

---tb_700w真实有700万行数据由于innodb_stats_persistent_sample_pages进行自动持久化统计信息采样只采集20页那么就会有误差

SQL

---此处STATS_SAMPLE_PAGES的最大值是65535超出会报错。

SQL

---收集单表的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

---恢复默认STATS_SAMPLE_PAGES由innodb_stats_persistent_sample_pages配置选项确定

SQL

innodb_stats_persistent_sample_pages;

---默认采集20页

算出innodb_stats_persistent_sample_pages最合适的值。

公式innodb_stats_persistent_sample_pagesANALYZE

TABLE复杂性度(大小)/表中索引列的数量(多个数相加而成)/分区数/innodb_page_size

SQL

n_cols_in_uniq_i所有唯一索引中不包括主键列的所有列的总数为0

n_cols_in_non_uniq_i所有非唯一索引中所有列的总数为0

那么读取tb_700w表

innodb_stats_persistent_sample_pages(1611Mx1024)/(001*(10))/1/16kb103104

SQL

innodb_stats_persistent_sample_pages103104;

SQL

非持久化统计信息既innodb_stats_persistentOFF默认on不推荐使用仅了解

非持久化统计信息存储在内存里如果数据库重启统计信息将丢失在下一次访问表时重新计算。

会导致频繁地重新计算统计信息这可能会导致查询执行计划的变化。

不推荐使用也不是默认值。

0创建或更改单张表时统计信息不会保留到磁盘。

相反统计信息存储在内存中并在服务器关闭时丢失。

某些业务和某些条件下也会定期更新统计数据。

非持久化统计信息在以下情况会被自动更新前提innodb_stats_on_metadata设置为on默认off

table

2)设置innodb_stats_on_metadataON默认off执行SHOW

TABLE

INDEX查询INFORMATION_SCHEMA下的TABLES,

STATISTICS

3)启用--auto-rehash选项这是默认设置。

--auto-rehash选项会打开所有InnoDB表打开表的操作会导致统计数据重新计算。

使用mysql

4)表第一次被打开

总结非持久化统计信息的缺点显而易见数据库重启后如果大量表开始更新统计信息会对实例造成很大影响所以目前都会使用持久化统计信息。

1相关参数

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

0时innodb_stats_transient_sample_pages的值会影响所有InnoDB表和索引的索引采样。

更改索引样本大小时请注意以下潜在的重大影响

增加innodb_stats_transient_sample_pages值可能需要更多磁盘读取。

远大于8例如100的值可能导致打开表或执行SHOW

TABLE

STATUS所花费的时间显着减慢。

优化器可能会根据索引选择性的不同估计选择非常不同的查询计划。

1)全局变量影响所有表

---默认on启用持久化统计信息。

变量控制统计信息是否持久化统计信息在早期的MySQL中是不持久化在新版本的MySQL中持久化统计信息是默认的选项。

当变量打开时统计信息就会被持久化到物理表中统计信息会更加的稳定和精确对于大表也节省了收集统计信息的所需资源。

如果为off可能会频繁地重新计算统计信息这可能会导致查询执行计划的变化。

STATS_PERSISTENT0

默认值由innodb_stats_persistent配置选项决定。

TABLE或ALTER

TABLE语句启用持久统计信息后在将代表性数据加载到表中之后发出ANALYZE

STATS_PERSISTENT指定是否为InnoDB表启用持久统计信息。

默认值由innodb_stats_persistent配置选项确定。

1:表示启用表的持久统计信息;

0:关闭此功能