Overview of MySQL Statistics

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 statistics

Non-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 Statistics

Starting 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 information

We 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!