服务器数据库数据统计查询系统,mysql 系统库二） —— 数据库对象信息、统计信息、优化器成本记录表...

最新推荐文章于 2021-12-23 10:18:52 发布

AppWizard

最新推荐文章于 2021-12-23 10:18:52 发布

阅读量149

点赞数

文章标签：服务器数据库数据统计查询系统

在线QQ客服：1922638

专业的SQL Server、MySQL数据库同步软件

这些表的功能基本上已由information_schema下具有相同名称的表替换。本节仅作简要介绍。

该表提供有关自定义安装的插件(非系统启用的插件)的查询信息，此表已被替换information_schema.plugins表。

表字段的含义

名称:自定义在安装插件时指定的插件名称。

dl:定制安装的插件so库的名称。

？

记录有关存储过程和函数的信息，类似于information_schema.routines表，但后者更为详细。

？

提供与查询计划任务有关的事件信息，类似于information_schema.events表，但后者更为详细。

？

记录与审计和防火墙相关的信息，类似于information_schema.routines表，但后者更为详细。此外，该表还用于记录从so插件库安装的功能信息。

？

统计持久性

统计持久性功能将统计信息存储在内存中的磁盘上，可以在数据库中快速重新启动重新启动后，无需重新执行统计信息即可进行读取，查询优化器可以使用这些持久性统计信息来准确选择执行计划。持久性统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中，前者存储表结构，与数据行相关的统计信息，而后者存储与索引相关的统计信息。

设置innodb_stats_persistent = ON(默认)或创建表时，将使用STATS_PERSISTENT = 1选项，这意味着统计信息的持久性功能已打开。前者是全局打开的，后者是单表打开的。要分别关闭表的持久统计功能，请执行ALTER TABLE tbl_name STATS_PERSISTENT = 0;。

统计信息会自动重新计算

innodb_stats_auto_recalc系统变量控制是否启用统计信息的自动计算功能，默认情况下启用该功能表中数据的变化当变化超过10％时，将触发统计信息的自动计算。您还可以在CREATE TABLE或ALTER TABLE语句中使用STATS_AUTO_RECALC子句为单个表配置统计信息的自动重新计算。

自动重新计算在后台运行。即使启用了innodb_stats_auto_recalc系统变量，当表DML操作中的数据超过10％时，也可能不会立即重新计算统计信息。在某些情况下，它可能会延迟几秒钟。如果需要准确的统计信息，则可以手动执行ANALYZE TABLE语句。

当向表中添加新索引时，无论innodb_stats_auto_recalc的值如何，都会触发重新计算索引统计信息并将其添加到innodb_index_stats表。如果要在添加索引时将相关统计信息更新到mysql.innodb_table_stats表，则仍需要启用innodb_stats_auto_recalc或修改表的innodb_stats_auto_recalc表创建选项，或在表上执行ANALYZE TABLE语句。

InnoDB优化器统计信息示例页面

优化器使用索引键的相关统计信息来计算索引选择性，然后选择适当的索引。如何获得这些统计信息？例如，当执行ANALYZE TABLE时，InnoDB将从表中的每个索引中提取随机页面以估计索引的基数(随机采样)，确定采样页面的数量由系统参数Innodb_stats_persistent_sample_pages决定，默认值为20，该变量是动态变量。通常，不需要修改。增加变量设置可能会导致每个采样时间变长，但是如果确定默认采样数会导致索引统计信息不正确，则可以尝试逐渐增加系统变量的值，直到足够准确的统计数据。可以通过将SELECT DISTINCT(索引名称)的返回值与mysql.innodb_index_stats表中提供的估计值进行比较来检查统计信息的准确性。

使用与统计信息有关的选项来创建示例:

？

如何在持久性统计信息的计算中配置删除记录的包含方式

默认情况下，InnoDB在计算统计信息时将读取未提交的数据。对于从表执行删除行操作的未提交事务，InnoDB在评估行和索引统计信息时会忽略这些标记为删除的记录，这可能会导致在表上执行并行查询的其他事务的执行计划。准确。为避免这种情况，您可以启用系统参数innodb_stats_include_delete_marked以确保InnoDB在计算持久性统计信息时包括标记为删除的记录。此参数是全局变量，不能单独设置表，该参数在5.7.16中引入。

？

注释

统计信息的持久性取决于mysql库下的innodb_table_stats和innodb_index_stats表。这些表在安装，升级和源代码构建期间自动设置。

innodb_table_stats和innodb_index_stats表是普通表，可以手动更新。通过手动更新统计信息的功能，您可以执行特定的查询优化计划或测试替代计划，而无需修改数据库。如果要手动更新统计信息，则需要执行FLUSH TABLE tbl_name命令以使MySQL重新加载更新的统计信息。

持久统计信息被视为本地信息，因为它们与实例本身相关。因此，innodb_table_stats和innodb_index_stats表的自动统计数据更改将不会在主数据库和备份数据库之间复制。但是，如果手动执行ANALYZE TABLE语句以触发统计信息重新计算，则ANALYZE TABLE语句本身将在活动和备用体系结构之间复制，并且统计信息将在备用数据库中重新计算(除非设置sql_log_bin = 0或就像在活动数据库的操作过程中设置的那样(” S语句关闭日志记录)。

？

此表提供与查找表数据有关的统计信息。

表字段的含义:

database_name:数据库名称。

table_name:表名称，分区名称或子分区名称。

last_update:指示InnoDB最近更新此统计信息行的时间戳。

n_rows:表中数据记录行的估计数量。

clustered_index_size:主键索引的大小，以页为单位。

sum_of_other_index_sizes:其他(非主键)索引的总大小，以页为单位。

？

该表提供与查询索引有关的统计信息。

表字段的含义:

database_name:数据库名称。

table_name:表名，分区表名，子分区表名。

index_name:索引名称。

last_update:指示InnoDB最近更新此统计信息行的时间戳。

stat_name:统计信息的名称，相应的统计信息值存储在stat_value列中。

stat_value:保存与统计信息名称的stat_name列对应的统计信息值。

sample_size:stat_value列中提供的统计信息的估计值的样本页面数。

stat_description:在统计信息名称的stat_name列中指定的统计信息的描述信息。

？

stat_name 字段的详细说明:

stat_name列可以具有以下值:

size:stat_value列值表示索引中的总页数。

n_leaf_pages:stat_value列值显示索引叶子页的数量。

n_diff_pfxNN(NN代表数字，例如01,02等):stat_value列值指示该值前导列中唯一值的数量指数。例如，当NN为01时，stat_value列值表示索引第一列中唯一值的数量;当NN为02时，stat_value列值表示索引的第一列和第二列的组合中唯一值的数量。比喻。

？

计算索引大小:

您可以将此表中的索引信息页与系统变量innodb_page_size的值结合使用来计算索引大小:

优化器成本模型基于以下两个成本模型表，并且该表中的值可以修改调整执行计划的决定。

server_cost表:MySQL的正常运行所需的优化程序成本估算的恒定值。

engine_cost表:针对特定存储引擎操作估算的优化程序成本的常数。

在MySQL启动时将成本模型表读入内存，并在生成执行计划时使用内存中的值。将优先使用表中指定的任何非NULL成本估算常量，并使用未指定的默认常量(在编译时生成)。

server_cost和engine_cost表中的成本常量数据仅适用于当前实例，并且它们的修改将不会被复制和同步。修改后，它仅对新连接生效，而对修改前建立的连接无效。

？

下面将详细介绍这两个表。

该表为查询MySQL的常规操作所需的优化程序的成本估算提供了恒定值。

表字段的含义:

cost_name:成本估算变量名称，不区分大小写。如果MySQL在读取此表时无法识别成本名称，它将在错误日志中写入警告。

cost_value:成本估算变量值。如果该值不为NULL，则MySQL将直接将其用于成本计算。否则，将使用默认估计(代码中的默认编译)。如果MySQL在读取该表时发现成本值无效(不正确)，它将在错误日志中写入警告。您需要恢复默认值，将此字段设置为NULL，然后执行FLUSH OPTIMIZER_COSTS语句。

last_update:上次更新行记录的时间。

注释:与成本估算变量有关的描述性信息。

？

表中记录的内容是服务器识别的成本估算常量，如下所示:

disk_temptable_create_cost(默认值为40.0)，disk_temptable_row_cost(默认值为1.0):？成本估算常数基于磁盘的内部临时表。增大这些值将使查询优化器的内部临时表在进行成本估算时会占用更少的磁盘。

key_compare_cost(默认值为0.1):比较记录的成本常量。增加此值会使查询优化器认为需要更多比较操作的执行计划很昂贵。例如，与使用文件排序相比，更倾向于使用索引来避免排序。

memory_temptable_create_cost(默认值为2.0)，memory_temptable_row_cost(默认值为0.2):成本估算常数基于MEMORY存储引擎的内部临时表。增加这些值将增加使用内部存储器临时表的成本估算，从而使优化器更倾向于减少使用它。

row_evaluate_cost(默认值为0.2):用于评估记录行的成本常量。增大该值将使查询优化器认为需要读取更多行的执行计划非常昂贵，并且倾向于使用较少的表扫描

？

该表提供了一个查询，用于估计特定存储引擎操作所需的优化程序成本的常数值。

表字段的含义:

ENGINE_NAME:此成本估算常量所应用的存储引擎名称，不区分大小写。默认值适用于所有存储引擎。如果MySQL在读取该表时无法识别引擎名称，它将在错误日志中写入警告。