我的云文档怎么没有了(我的云文档怎么找)
1037
2022-05-28
配置非持久优化器统计信息参数
本节介绍如何配置非持久优化器统计信息。当innodb_stats_persistent=OFF或使用STATS_PERSISTENT=0创建或修改单个表时,优化器统计信息不会被持久化到磁盘。相反,统计信息存储在内存中,并且在服务器关闭时丢失。统计数据还由某些操作在某些条件下定期更新。
从MySQL 5.6.6开始,默认情况下,优化器统计数据被持久化到磁盘上,由innodb_stats_persistent配置选项启用。
优化器数据更新
在以下情况出现时非持久化的优化器统计信息会被更新:
.执行analyze table
.运行show table status,show index,或者在innodb_stats_on_metadata选项被启用时查询information_schema.tables或information_schema.statistics表。
MySQL 5.6.6中,当持久化优化器统计信息被启用时,innodb_stats_on_metadata默认设置为OFF。启用innodb_stats_on_metadata可能会降低具有大量表或索引的模式的访问速度,并降低涉及InnoDB表的查询的执行计划的稳定性。innodb_stats_on_metadata使用SET语句全局配置。set global innodb_stats_on_metadata=ON
innodb_stats_on_metadata只适用于优化器统计信息配置为非持久化(当innodb_stats_persistent被禁用时)。
.启动mysql客户端时启用–auto-rehash选项,这是默认设置。auto-rehash选项会打开所有InnoDB表,打开表的操作会导致统计数据重新计算。为了提高mysql客户端的启动和更新统计信息时间,你可以使用–disable-auto-rehash选项关闭auto-rehash。自动auto-rehash特性允许交互用户自动完成数据库、表和列名的命名。
.表第一次打开。
.InnoDB检测到有1 / 16的表在上次统计数据更新后被修改。
配置采样页面数
MySQL查询优化器使用关于键分布的估计统计信息,根据索引的相对选择性为执行计划选择索引。当InnoDB更新优化器统计数据时,它会从表上的每个索引中随机取样,以估计索引的基数。(这种技术被称为随机潜水。)
为了控制统计信息评估的质量(从而为查询优化器提供更好的信息),可以使用参数innodb_stats_transient_sample_pages更改抽样页面的数量。默认的抽样页面数是8,这可能不足以产生准确的评估,导致查询优化器的索引选择很差。这种技术对于大型表和连接中使用的表尤其重要。对这样的表进行不必要的全表扫描可能会造成严重的性能问题。
当innodb_stats_transient_sample_pages =0时,innodb_stats_persistent的值会影响所有InnoDB表和索引的索引采样。当您更改索引样本大小时,请注意以下潜在的重大影响。
.小值像1或2可以导致不精确的基数评估
.增加innodb_stats_transient_sample_pages的值可能需要更多的磁盘读取。大于8(比如100)的值会导致打开表或执行SHOW table STATUS所需的时间显著放缓。
.优化器可能会根据索引选择性的不同估计选择非常不同的查询计划
无论innodb_stats_transient_sample_pages的值是什么,设置该选项并保持该值。选择一个值,它可以为数据库中的所有表提供合理准确的估计,而不需要过多的I/O。因为除了在执行ANALYZE TABLE时,统计数据会在其他时间自动重新计算,所以增加索引样本大小,运行ANALYZE TABLE,然后再次减少样本大小是没有意义的。
较小的表通常比较大的表需要更少的索引样本。如果你的数据库有很多大的表,考虑使用一个更大的innodb_stats_transient_sample_pages值。
评估InnoDB表analyze table的复杂度
InnoDB表的ANALYZE TABLE复杂度依赖于:
.采样的页面数,由innodb_stats_persistent_sample_pages定义
.表中索引列的数目
.分区数。如果表没有分区,则认为分区数为1。
使用这些参数,估计ANALYZE TABLE复杂度的近似公式是
innodb_stats_persistent_sample_pages的值表中索引的列数分区数
通常,结果值越大,ANALYZE TABLE的执行时间就越长
innodb_stats_persistent_sample_pages定义了在全局级别上采样的页面数量。要设置单个表的采样页数,请使用有STATS_SAMPLE_PAGES选项的CREATE TABLE或ALTER TABLE的语句。
如果innodb_stats_persistent=OFF,则由innodb_stats_transient_sample_pages定义采样的页面数
要了解估算ANALYZE TABLE复杂度的更深入的方法,请考虑以下示例:
在大O符号中,ANALYZE TABLE的复杂度被描述为:
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)
.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是分区的数量。如果没有定义分区,则将表视为单个分区。
现在,考虑下面的表(表t),它有一个主键(2列)、一个唯一索引(2列)和两个非唯一索引(各有两列):
mysql> 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)
-> );
Query OK, 0 rows affected (0.13 sec)
对于上述算法所需的列和索引数据,查询mysql.innodb_index_stats来查看表t的持久索引统计信息。n_diff_pfx%显示了每个索引列的统计信息。
例如,列a和列b用于计算主键索引。对于非唯一索引,除了用户定义的列外,还要统计主键列(a,b)。
mysql> select index_name, stat_name, stat_description
-> from mysql.innodb_index_stats
-> where
-> database_name=‘mysql’ and
-> table_name=‘t’ and
-> stat_name like ‘n_diff_pfx%’;
±-----------±-------------±-----------------+
| index_name | stat_name | stat_description |
±-----------±-------------±-----------------+
| PRIMARY | n_diff_pfx01 | a |
| PRIMARY | n_diff_pfx02 | a,b |
| i1uniq | n_diff_pfx01 | c |
| i1uniq | n_diff_pfx02 | c,d |
| i2nonuniq | n_diff_pfx01 | e |
| i2nonuniq | n_diff_pfx02 | e,f |
| i2nonuniq | n_diff_pfx03 | e,f,a |
| i2nonuniq | n_diff_pfx04 | e,f,a,b |
| i3nonuniq | n_diff_pfx01 | g |
| i3nonuniq | n_diff_pfx02 | g,h |
| i3nonuniq | n_diff_pfx03 | g,h,a |
| i3nonuniq | n_diff_pfx04 | g,h,a,b |
±-----------±-------------±-----------------+
12 rows in set (0.01 sec)
根据上面显示的索引统计数据和表定义,可以确定以下值:
.n_cols_in_uniq_i,不计算主键列的所有唯一索引中所有列的总数为2 (c和d)
.n_cols_in_non_uniq_i,所有非唯一索引中所有列的总数,为4 (e、f、g和h)
.n_cols_in_pk,主键中的列数是2(a和b)
.n_non_uniq_i,表中非唯一索引的数量为2 (i2nonuniq和i3nonuniq))
.n_part, 分区数,为1
现在可以计算innodb_stats_persistent_sample_pages *(2 + 4 + 2 *(1 + 2)) * 1来确定扫描的叶页数。如果将innodb_stats_persistent_sample_pages设置为默认值20,并将默认的页面大小设置为16 KiB (innodb_page_size=16384),那么你可以估计为表t读取20 * 12 * 16384字节,或者大约4 MiB。
所有4MiB可能不是从磁盘读取的,因为一些叶页可能已经缓存在缓冲池中。
MySQL
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。