Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL InnoDB storage engine update Cardinality statistics strategy analysis", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL InnoDB storage engine update Cardinality statistics strategy analysis"!

In the InnoDB storage engine, updates to Cardinality statistics occur in two operations: insert and update.

The internal policies of the InnoDB storage engine for updating Cardinality information are: persistence (PERSISTENT) and non-persistent statistics (TRANSIENT).

PERSISTENT statistics, stored in mysql.innodb_index_stats and mysql.innodb_table_stats.

Nonpersistent (TRANSIENT) statistics, stored in information_schema.statistics and information_schema.tables.

The former is the innodb table and the latter is the memory table, and they are controlled by the parameter innodb_stats_persistent. Starting with MySQL 5.6.6, by default, innodb_stats_persistent defaults to ON, and optimizer statistics are retained on disk.

For persistence (persistent) statistics policy:

When the data in table 1 to 10 has changed, and innodb_stats_auto_recalc and innodb_stats_persistent are set, the statistics are updated by persistent.

The statistical data collection of two applications is more than 10s.

In 5. 6, a new parameter, innodb_stats_auto_recalc, is introduced to control whether automatic statistical calculations are performed. When the records on the table are modified by more than 10%, the statistics will be recalculated. This only takes effect if innodb_stats_persistent is turned on when the table is created or the table creation option STATS_PERSISTEND=1 is specified. The number of sampling page is controlled by the parameter innodb_stats_persistent_sample_pages (the actual number of page read will be greater than this value).

The parameter innodb_stats_auto_recalc controls whether statistics are re-collected when the number of rows in the table changes by more than 10%. This collected action is asynchronous, and it may take some time to re-collect statistics after executing a large dml. If you want timely statistics, execute the analyze command to collect them.

For non-persistent (transient) statistics policies:

InnoDB detects that the table's 1max 16 has been modified since the statistics were last updated, and updates the statistics by transient.

Stat_modified_counter > 2000000000.

For non-persistence, the first strategy is to update the Cardinality information because the data in table 1 Cardinality 16 has changed since the last time the Cardinality information was counted. The second consideration is that if a row of data in the table is updated frequently, the data in the table does not actually increase, and it is the row of data that actually changes. the first update strategy does not apply to this situation. Therefore, there is a counter stat_modified_counter inside the InnoDB storage engine to indicate the number of changes. When the stat_modified_counter is greater than 20000000, the Cardinality information also needs to be updated.

Thank you for your reading, the above is the content of "Strategy Analysis of updating Cardinality Statistics by MySQL InnoDB Storage engine". After the study of this article, I believe you have a deeper understanding of the strategy analysis of updating Cardinality statistics by MySQL InnoDB storage engine, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.