MySQL executes SQL through the process of SQL parsing and query optimization. The parser breaks down the SQL into data structures and passes them to subsequent steps. The query optimizer finds the best solution for executing the SQL query and generates an execution plan. The query optimizer determines how SQL is executed, which depends on the database statistics. Below we introduce the relevant content of innodb statistics in MySQL 5.7. There are two types of storage for MySQL statistics: non-persistent and persistent statistics. 1. Non-persistent statisticsNon-persistent statistics are stored in memory and will be lost if the database is restarted. There are two ways to set non-persistent statistics:
Non-persistent statistics are automatically updated in the following situations:
The disadvantages of non-persistent statistical information are obvious. If a large number of tables start to update statistical information after the database is restarted, it will have a great impact on the instance, so persistent statistical information is currently used. 2. Persistent StatisticsStarting from 5.6.6, MySQL uses persistent statistics by default, that is, INNODB_STATS_PERSISTENT=ON, and persistent statistics are stored in the tables mysql.innodb_table_stats and mysql.innodb_index_stats. Persistent statistics are automatically updated in the following situations:
innodb_table_stats is the statistical information of the table, and innodb_index_stats is the statistical information of the index. The meaning of each field is as follows:
To better understand innodb_index_stats, create a test table for illustration: CREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f) )ENGINE=INNODB; The written data is as follows: To view the statistics of the t1 table, focus on the stat_name and stat_value fields. When tat_name=size: stat_value indicates the number of indexed pages When stat_name=n_leaf_pages: stat_value indicates the number of leaf nodes When stat_name=n_diff_pfxNN: stat_value indicates the number of unique values in the index field. Here is a detailed description: 1. n_diff_pfx01 indicates the number after distinct in the first column of the index. For example, column a of PRIMARY has only one value 1, so when index_name='PRIMARY' and stat_name='n_diff_pfx01', stat_value=1. 2. n_diff_pfx02 indicates the number of distinct values in the first two columns of the index. For example, the e and f columns of i2uniq have 4 values, so when index_name='i2uniq' and stat_name='n_diff_pfx02', stat_value=4. 3. For non-unique indexes, the primary key index will be added after the original column. For example, if index_name='i1' and stat_name='n_diff_pfx03', the primary key column a is added after the original index columns c and d. The distinct result of (c, d, a) is 2. Understanding the specific meanings of stat_name and stat_value can help us troubleshoot why the appropriate index is not used during SQL execution. For example, if the stat_value of an index n_diff_pfxNN is much smaller than the actual value, the query optimizer may believe that the index has poor selectivity, which may lead to the use of the wrong index. 3. Dealing with inaccurate statistical informationWe checked the execution plan and found that the correct index was not used. If it is caused by a large difference in the statistical information in innodb_index_stats, you can handle it in the following ways: 1. Manually update statistics. Note that a read lock will be added during the execution: ANALYZETABLE TABLE_NAME; 2. If the statistics are still inaccurate after the update, consider increasing the data pages sampled from the table. There are two ways to modify it: a) Global variable INNODB_STATS_PERSISTENT_SAMPLE_PAGES, default value is 20; b) A single table can specify sampling for that table: ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40; After testing, the maximum value of STATS_SAMPLE_PAGES here is 65535. If it exceeds this value, an error will be reported. Currently, MySQL does not provide a histogram function. In some cases (such as uneven data distribution), simply updating statistical information may not necessarily result in an accurate execution plan. The only way is to specify the index through index hint. The new version 8.0 will add the histogram function. Let us look forward to MySQL becoming more and more powerful! You may also be interested in:
|
<<: How to configure SSL certificate in nginx to implement https service
>>: Stop using absolute equality operators everywhere in JS
routing vue-router4 keeps most of the API unchang...
Table of contents 1. Overview 1. Principle 2. Imp...
Table of contents definition grammar Examples 1. ...
When installing Tomcat in Docker, Tomcat may over...
In the MySQL database, when we need fuzzy query, ...
ffmpeg is a very powerful audio and video process...
1. Configure Docker remote connection port Locate...
Due to the needs of the work project, song playba...
Table of contents 1. Characteristics of JS 1.1 Mu...
How is the MySQL Select statement executed? I rec...
This article takes Centos7.6 system and Oracle11g...
Preface Since the most important data structure i...
How to implement the "Set as homepage" ...
Locks in MySQL Locks are a means to resolve resou...
Use the find command to find files larger than a ...