MySQL 8.0 Reference Manual(读书笔记76节--Optimizer Statistics for InnoDB (2))

这篇具有很好参考价值的文章主要介绍了MySQL 8.0 Reference Manual(读书笔记76节--Optimizer Statistics for InnoDB (2))。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1. Configuring Non-Persistent Optimizer Statistics Parameters

开始讲解 非固化的统计数据

This section describes how to configure non-persistent optimizer statistics. Optimizer statistics are not persisted to disk when innodb_stats_persistent=OFF or when individual tables are created or altered with STATS_PERSISTENT=0. Instead, statistics are stored in memory, and are lost when the server is shut down. Statistics are also updated periodically by certain operations and under certain conditions.

Optimizer Statistics Updates


Non-persistent optimizer statistics are updated when:


• Running SHOW TABLE STATUS, SHOW INDEX, or querying the Information Schema TABLES or STATISTICS tables with the innodb_stats_on_metadata option enabled.

The default setting for innodb_stats_on_metadata is OFF. Enabling innodb_stats_on_metadata may reduce access speed for schemas that have a large number of tables or indexes, and reduce stability of execution plans for queries that involve InnoDB tables. innodb_stats_on_metadata is configured globally using a SET statement.

SET GLOBAL innodb_stats_on_metadata=ON

留意: innodb_stats_on_metadata only applies when optimizer statistics are configured to be non-persistent (when innodb_stats_persistent is disabled).

• Starting a mysql client with the --auto-rehash option enabled, which is the default. The autorehash option causes all InnoDB tables to be opened, and the open table operations cause statistics to be recalculated.

To improve the start up time of the mysql client and to updating statistics, you can turn off autorehash using the --disable-auto-rehash option. The auto-rehash feature enables automatic name completion of database, table, and column names for interactive users.

• A table is first opened.

• InnoDB detects that 1 / 16 of table has been modified since the last time statistics were updated.

Configuring the Number of Sampled Pages

The MySQL query optimizer uses estimated statistics about key distributions to choose the indexes for an execution plan, based on the relative selectivity of the index. When InnoDB updates optimizer statistics, it samples random pages from each index on a table to estimate the cardinality of the index. (This technique is known as random dives.)

To give you control over the quality of the statistics estimate (and thus better information for the query optimizer), you can change the number of sampled pages using the parameter innodb_stats_transient_sample_pages. The default number of sampled pages is 8, which could be insufficient to produce an accurate estimate, leading to poor index choices by the query optimizer. This technique is especially important for large tables and tables used in joins. Unnecessary full table scans for such tables can be a substantial performance issue.innodb_stats_transient_sample_pages is a global parameter that can be set at runtime.

The value of innodb_stats_transient_sample_pages affects the index sampling for all InnoDB tables and indexes when innodb_stats_persistent=0. Be aware of the following potentially significant impacts when you change the index sample size:

• Small values like 1 or 2 can result in inaccurate estimates of cardinality.

• Increasing the innodb_stats_transient_sample_pages value might require more disk reads. Values much larger than 8 (say, 100), can cause a significant slowdown in the time it takes to open a table or execute SHOW TABLE STATUS.

• The optimizer might choose very different query plans based on different estimates of index selectivity.

Whatever value of innodb_stats_transient_sample_pages works best for a system, set the option and leave it at that value. Choose a value that results in reasonably accurate estimates for all tables in your database without requiring excessive I/O. Because the statistics are automatically recalculated at various times other than on execution of ANALYZE TABLE, it does not make sense to increase the index sample size, run ANALYZE TABLE, then decrease sample size again. Smaller tables generally require fewer index samples than larger tables. If your database has many large tables, consider using a higher value for innodb_stats_transient_sample_pages than if you have mostly smaller tables.

2. Estimating ANALYZE TABLE Complexity for InnoDB Tables

ANALYZE TABLE complexity【kəmˈpleksəti 复杂性;难题;难懂;难以理解的局势】 for InnoDB tables is dependent on:

• The number of pages sampled, as defined by innodb_stats_persistent_sample_pages.

• The number of indexed columns in a table

• The number of partitions【pɑːrˈtɪʃnz 分割;隔断;分治;瓜分;隔扇;隔板墙】. If a table has no partitions, the number of partitions is considered to be 1.

Using these parameters, an approximate formula for estimating ANALYZE TABLE complexity would be:

The value of innodb_stats_persistent_sample_pages * number of indexed columns in a table * the number of partitions

Typically, the greater the resulting value, the greater the execution time for ANALYZE TABLE.


innodb_stats_persistent_sample_pages defines the number of pages sampled at a global level. To set the number of pages sampled for an individual table, use the STATS_SAMPLE_PAGES option with CREATE TABLE or ALTER TABLE.

If innodb_stats_persistent=OFF, the number of pages sampled is defined by innodb_stats_transient_sample_pages.

 In Big O notation, ANALYZE TABLE complexity is described as: ---公式描述

 * (n_cols_in_uniq_i
 + n_cols_in_non_uniq_i
 + n_cols_in_pk * (1 + n_non_uniq_i))
 * n_part)


• n_sample is the number of pages sampled (defined by innodb_stats_persistent_sample_pages)

• n_cols_in_uniq_i is total number of all columns in all unique indexes (not counting the primary key columns)

• n_cols_in_non_uniq_i is the total number of all columns in all nonunique indexes

• n_cols_in_pk is the number of columns in the primary key (if a primary key is not defined, InnoDB creates a single column primary key internally)

• n_non_uniq_i is the number of nonunique indexes in the table

• n_part is the number of partitions. If no partitions are defined, the table is considered to be a single partition.

什么是Big O notation?

大O符号(Big O notation)是用于描述函数渐近行为的数学符号。更确切地说,它是用另一个(通常更简单的)函数来描述一个函数数量级的渐近上界。在数学中,它一般用来刻画被截断的无穷级数尤其是渐近级数的剩余项;在计算机科学中,它在分析算法复杂性的方面非常有用。




到了这里,关于MySQL 8.0 Reference Manual(读书笔记76节--Optimizer Statistics for InnoDB (2))的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用


  • MySQL 8.0 Reference Manual(读书笔记35节-- 字符编码(2))

    Every character string literal has a character set and a collation. For the simple statement SELECT \\\'string\\\', the string has the connection default character set and collation defined by the character_set_connection and collation_connection system variables. A character string literal may have an optional character set introducer and COLLATE clause, to desig

  • MySQL 8.0 Reference Manual(读书笔记19节-- 日期与计算)

    为什么设计的时候,存放的是出生日期而不是年龄呢?这个问题简单,细想很有意思,也包含着智慧,来自生产生活的思考。下面的解释很到位。 How about age? That might be of interest, but it is not a good thing to store in a database. Age changes as time passes, which means you\\\'d have to update your records

  • MySQL 8.0 Reference Manual(读书笔记34节-- 字符编码(1))

    MySQL includes character【ˈkærəktər 字母,符号;】 set support that enables you to store data using a variety【vəˈraɪəti (同一事物的)不同种类,多种式样; 变化; (植物、语言等的)变种,变体; 多样化; 综艺节目; 品种; 多变性; 异体; 】 of character sets and perform comparisons【kəmˈpɛrəsənz 比较; 对比

  • MySQL 8.0 Reference Manual(读书笔记67节--Phantom Rows)

    The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row. Suppose that there is an index on the id column of the child table and that you wa

  • MySQL 8.0 Reference Manual(读书笔记39节-- Data Types(1))

    Data type descriptions use these conventions: • For integer types, M indicates the maximum display width. For floating-point and fixed-point types, M is the total number of digits that can be stored (the precision). For string types, M is the maximum length. The maximum permissible value of M depends on the data type. • D applies to floating-point and fi

  • MySQL 8.0 Reference Manual(读书笔记41节-- Data Types(3))

    Data type specifications can have explicit【ɪkˈsplɪsɪt 明确的; 详述的; 直言的, 坦率的; 一目了然的; 】 or implicit【ɪmˈplɪsɪt 含蓄的; 完全的; 内含的; 无疑问的; 不直接言明的; 成为一部分的; 】 default values. A DEFAULT value clause in a data type specification explicitly indicates a default value for a colum

  • MySQL 8.0 Reference Manual(读书笔记40节-- Data Types(2))

    The string data types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. In some cases, MySQL may change a string column to a type different from that given in a CREATE TABLE or ALTER TABLE statement.  For definitions of character string columns (CHAR, VARCHAR, and the TEXT types), MySQL interprets【ɪnˈtɜːrprəts 诠释; 说明; 把…理解

  • MySQL 8.0 Reference Manual(读书笔记69节--InnoDB Startup Configuration)

    有些InnoDB的配置,是在实例初始化时,就决定了,所以,建议写在configuration file 文件中。 Because MySQL uses data file, log file, and page size settings to initialize InnoDB, it is recommended that you define these settings in an option file that MySQL reads at startup, prior to initializing InnoDB. Normally, InnoDB is initialize

  • MySQL 8.0 Reference Manual(读书笔记80节-- InnoDB Row Formats)

    The row format of a table determines how its rows are physically stored, which in turn can affect the performance of queries and DML operations. As more rows fit into a single disk page, queries and index lookups can work faster, less cache memory is required in the buffer pool, and less I/O is required to write out updated values. The data in each table is

  • MySQL 8.0 Reference Manual(读书笔记37节-- 字符编码(4)-Unicode Support)

    The Unicode Standard includes characters from the Basic Multilingual Plane (BMP) and supplementary characters that lie outside the BMP. This section describes support for Unicode in MySQL. BMP characters have these characteristics: • Their code point values are between 0 and 65535 (or U+0000 and U+FFFF). • They can be encoded in a variable-length encodin