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

This article mainly explains the "MySQL index statistics update related parameters", the article explains the content 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 index statistics update related parameters" bar!

Parameters related to MySQL statistics:

1. Innodb_stats_on_metadata (whether to update statistics automatically). It is disabled by default in MySQL 5.7s.

Takes effect only if the statistics are configured to be non-persistent.

In other words, when innodb_stats_persistent is configured as OFF, the setting of innodb_stats_on_metadata takes effect only if the hand that stores statistics is not persisted.

When innodb_stats_on_metadata is set to ON

Update fee persistence statistics when InnoDB holds show table status or accesses INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS system tables (similar to ANALYZE TABLE)

For the statistics update time of an index, refer to mysql.innodb_index_stats, the system table.

Select * from mysql.innodb_index_stats where table_name = 'teststatistics'

2. Innodb_stats_auto_recalc

Whether to automatically trigger the update of statistics only affects the tables of persistently stored statistics. The threshold is that the changed data exceeds 10% of the number of table rows.

That is, a table index statistics are persisted, and the data in the table has changed by more than 10%

If innodb_stats_auto_recalc is ON, the statistics are updated automatically, otherwise they are not updated.

3. Innodb_stats_persistent (non-persistent statistics switch), which is enabled by default in MySQL 5.7.The statistics are persisted.

When this option is set to ON, statistics are persisted to disk rather than in memory

On the contrary, if it is non-persistent storage (stored in memory), the corresponding statistics will be lost as the server shuts down.

4. Innodb_stats_persistent_sample_pages (the number of sampled pages of the index page when updating statistics persistently)

The default is 20 page. If the setting is too high, the execution time of ANALYZE TABLE will be increased when the statistics are updated.

5. Innodb_stats_transient_sample_pages (the number of sampled pages of the index page when updating statistics temporarily)

The default value is 8, and innodb_stats_transient_sample_pages takes effect only if it is set to disable.

That is, when index statistics are not stored persistently, innodb_stats_transient_sample_pages is the number of sample pages to update the statistics.

6. Innodb_stats_sample_pages

Has been discarded. Replaced with innodb_stats_transient_sample_pages.

= re-collect statistics =

1. Analyze and store the keyword distribution of tables

Analyze table table_name

Analyze is used to collect optimizer statistics, related to tuning, and works on myisam, BOB, and innodb.

Note: (executing sql:analyze table table_name under the MySQL5.5 version will return an ok status immediately after practice, but the backend

There will still be situations such as locking the table.

two。 Check tables (views), check one or more tables for errors

Check table table_name

It works for both myisam (keyword statistics are updated) and innodb, and for myisam, check and repair are required (due to the myisam table

May be damaged).

3. Optimize the table periodically (collate and merge space debris to eliminate space waste caused by deletions or updates)

Optimize table table_name

Works on myisam,bob and innodb tables, but this operation causes the table to be locked.

Optimize can recycle space, defragment and improve IO innodb, myisam, archive;. If it is a replication environment, you can add no_wri.

Te_to_binlog .

Optimize local table table_name

Thank you for your reading, the above is the content of "what are the parameters related to the update of MySQL index statistics?" after the study of this article, I believe you have a deeper understanding of what the parameters related to the update of MySQL index statistics have, 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.