Home >Database >Mysql Tutorial >Detailed overview of MySQL statistics
This article provides a comprehensive overview of the relevant knowledge points of MySQL statistical information through the introduction of the concept of statistical information and the advantages of MYSQL statistical information. I hope it can help friends in need.
MySQL will go through the process of SQL parsing and query optimization when executing SQL. The parser decomposes SQL into data structures and passes them to subsequent steps. The query optimizer finds the best solution for executing SQL queries and generates execution plan. The query optimizer determines how SQL is executed, relying on the statistics of the database. Below we introduce the relevant content of innodb statistics in MySQL 5.7.
The storage of MySQL statistical information is divided into two types, non-persistent and persistent statistical information.
Non-persistent statistical information is stored in memory. If the database is restarted, the statistical information will be lost. There are two ways to set non-persistent statistics:
1 Global variable, INNODB_STATS_PERSISTENT=OFF |
2 CREATE/ALTER table parameters, STATS_PERSISTENT=0 |
non-persistent Statistics will be automatically updated in the following situations:
1 Execute ANALYZE TABLE |
2 When innodb_stats_on_metadata=ON, execute SHOW TABLE STATUS, SHOW INDEX, query TABLES under INFORMATION_SCHEMA, STATISTICS |
##3 When the --auto-rehash function is enabled , use mysql client to log in |
4 The table is opened for the first time |
5 distance The last time statistics were updated, the data in table 1/16 was modified |
In this case, 10% of the data in the table is modified |
Database name | |
Table name |
##last_update |
##Last update time of statistical information | n_rows |
The number of rows in the table | clustered_index_size |
The number of pages in the clustered index | sum_of_other_index_sizes |
The pages of other indexes Quantity |
database_name | |
##table_name | |
index_name | |
##last_update | Statistics last updated time |
stat_name | Statistical information name |
##stat_value | ##Value of statistical information |
sample_size |
Sample size |
##stat_description | Type Description |
The above is the detailed content of Detailed overview of MySQL statistics. For more information, please follow other related articles on the PHP Chinese website!