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

This article mainly introduces the storage of MySQL statistical information, which is very detailed and has a certain reference value. Friends who are interested must finish reading it!

There are two types of storage of MySQL statistics, non-persistent and persistent statistics.

I. 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:

1 global variable

INNODB_STATS_PERSISTENT=OFF

2 parameters of the CREATE/ALTER table

STATS_PERSISTENT=0

Non-persistent statistics are automatically updated in the following situations:

1 execute ANALYZE TABLE

In the case of 2 innodb_stats_on_metadata=ON, execute SHOW TABLE STATUS and SHOW INDEX, and query TABLES and STATISTICS under INFORMATION_SCHEMA.

3 use mysql client to log in when the auto-rehash feature is enabled

4 the table is opened for the first time

5 since the statistical information was last updated, the data of Table 1 hand 16 has been modified.

The disadvantage of non-persistent statistics is obvious. If a large number of tables start to update statistics after a database restart, it will have a great impact on the instance, so persistent statistics are currently used.

2. Persistence statistics

Since 5.6.6, MySQL uses persistence statistics, namely INNODB_STATS_PERSISTENT=ON, by default. Persistence statistics are stored in tables mysql.innodb_table_stats and mysql.innodb_index_stats.

Persistence statistics are automatically updated in the following situations:

1 INNODB_STATS_AUTO_RECALC=ON

In this case, 10% of the data in the table is modified

2 add a new index

Innodb_table_stats is the statistics of the table, and innodb_index_stats is the statistics of the index. The meanings of each field are as follows:

Innodb_table_stats

Database_name

Database name

Table_name

Table name

Last_update

When the statistics were last updated

N_rows

Number of rows in the table

Clustered_index_size

Number of pages clustered indexed

Sum_of_other_index_sizes

Number of pages in other indexes

Innodb_index_stats

Database_name

Database name

Table_name

Table name

Index_name

Index name

Last_update

When the statistics were last updated

Stat_name

Statistics name

Stat_value

The value of statistics

Sample_size

Sample size

Stat_description

Type description

To better understand innodb_index_stats, create a test table to illustrate:

CREATE TABLE T1 (an 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 data is written as follows:

To view the statistics of the T1 table, you need to focus on the stat_name and stat_value fields.

On tat_name=size: stat_value represents the number of pages indexed

When stat_name=n_leaf_pages: stat_value indicates the number of leaf nodes

When stat_name=n_diff_pfxNN: stat_value represents the number of unique values on the index field, which is explained here:

1. N_diff_pfx01 represents the number after the first column distinct of the index, such as column an of PRIMARY, which has only one value of 1, so when index_name='PRIMARY' and stat_name='n_diff_pfx01', stat_value=1.

2. N_diff_pfx02 represents the number of the first two columns of the index after distinct, such as i2uniq's epenary f column, which has 4 values, so when index_name='i2uniq' and stat_name='n_diff_pfx02', stat_value=4.

3. For the non-unique index, the primary key index will be added after the original column. For example, index_name='i1' and stat_name='n_diff_pfx03', adds the primary key column an after the original index column cMagazine d, and the distinct result of (cmemery a) is 2.

Understanding the specific meaning of stat_name and stat_value can help us to find out why SQL does not use the appropriate index. For example, the stat_value of an index n_diff_pfxNN is much smaller than the actual value, and the query optimizer thinks that the poor selection of the index may lead to the use of the wrong index.

III. Inaccurate handling of statistical information

We look at the execution plan and find that the correct index is not used. If it is caused by a large difference in statistics in innodb_index_stats, we can deal with it in the following ways:

1. Update the statistics manually and note that a read lock will be added during execution:

ANALYZETABLE TABLE_NAME

2. If the updated statistical information is still inaccurate, you can consider adding the data page of table sampling. You can modify it in two ways:

A) Global variable INNODB_STATS_PERSISTENT_SAMPLE_PAGES, default is 20

B) A single table can specify samples for that table:

ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40

It has been tested that the maximum value of STATS_SAMPLE_PAGES here is 65535, and an error will be reported if it is exceeded.

At present, MySQL does not provide the function of histogram, in some cases (such as uneven data distribution), just updating statistics may not necessarily get an accurate execution plan, and the index can only be specified by index hint. The new version 8.0 will add histogram features, so let's look forward to the more and more powerful functions of MySQL.

These are all the contents of this article entitled "what are the storage of MySQL statistics?" Thank you for your reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.