Shulou(Shulou.com)06/02 Report--

How to achieve the persistence of statistical information in MYSQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Statistics are the basis for helping database queries to take a more appropriate query path in any database. What to do with persistent statistics in MYSQL 8 and how to improve the stability of the execution plan after persistence.

By default, this parameter is on

Show variables like 'innodb_stats_persistent'

In fact, the statistics exist in the two tables of mysql.innodb_table_stats and mysql.innodb_index_stats.

Statistics will not be carried out until the amount of data in each table changes. It depends on the parameter innodb_stats_auto_recalc, which is turned on by default, and 10% of the rows in a table are changed before the statistics are recalculated.

In fact, some of the following things may be similar to some open source databases. The parameters that can be adjusted can be adjusted slightly at the table level or the database level, because we cannot make the increments of the data of each table consistent. Imagine that the daily increment of a table is 1 million rows, and one is 50 rows. If the statistical analysis has been carried out in the same way as all tables. This is obviously a bit inappropriate.

So the screenshot above is a parameter similar to fine-tuning.

Stats_persistent = 1 is to persist the performance counter

Stats_auto_recale controls whether the table should be analyzed automatically. For example, if someone is comfortable with ORACLE, he will probably run for statistical analysis at night. What stats_auto_recalc means here is whether you want to do it automatically or manually. The final stats_sample_pages is for the accuracy of the statistics of your index. The default is 20. Increasing this value can improve the accuracy of statistics. Of course, you also have to pay some disk space, and analysis of cpu and other resources.

So whether a large table is often queried by HOT TABLE or COLD TABLE is analyzed here. Although this table inserts a lot of data, but in fact there are few queries, it can reduce the number of random samples of stats_sample_pages. On the contrary, it can be increased appropriately.

Let's do a test about inserting data into the database, but it should be noted before that the connection between PYTHON and MYSQL 8.019 requires a new connection method mysql_connector_python instead of the previous one. The picture above continues to use the old way to transfer your account.

CREATE USER link@'%' IDENTIFIED WITH mysql_native_password BY 'link'; otherwise an error will be reported in the connection

Another point is that in statistical analysis, the default is for READ UNCOMMITED, in which deleted records and marked deleted records are still recorded in statistical analysis, so the statistical analysis information obtained by RC RR in the case of a large number of delete operations will be relatively low.

So you can open innodb_stats_include_delete. But it will also increase the time of statistical analysis, and will increase the burden of the system in statistical analysis.

Idx_name n_diff_pfx01 | 1743985 | 100 | name idx_name n_diff_pfx02 | 1761487 | 100 | name,id

Idx_name n_leaf_pages | 2722 | NULL | Number of leaf pages

Idx_name | size | 3175 | NULL | Number of pages in the index

From the text shown above, size shows the overall number of page, n_leaf_pages shows the number of page of the current page node, n_diff_pfx01 refers to the different values of a single column, and n_diff_pfx02 shows the different values between the two columns.

According to the way the primary key of our MYSQL is set, the values of the primary key and the index column are generally different, so here you can think that n_diff_pfx02 is roughly the number of rows of your current table (not accurate, because starting re-counting requires data change 10%rows)

Finally, you need to take a look at how NULL values are used in statistical analysis. Innodb_stats_method.

Mysql provides three ways

All NULL index values of nulls_equal are considered equal

Nulls_unequal values are considered to be unequal, and each NULL forms a different group of values of size 1.

Nulls_ignored ignores null values

In addition, it is inconvenient to expand here, null = null, no, null! = null, no, what role is null in the database, and whether it should be counted into the statistics, should be considered, and MYSQL will let users choose this problem, in fact, it also shows that MYSQL itself does not have its own solution to this problem, so.

When designing tables, we try not to have NULL columns, and even if there are, we don't want INDEX it.

Finally, leave a picture, in the normal sentence, if there is a null, you have to add an and field is null or a field not is null to the query, which is meaningful, otherwise.

This is the answer to the question about how to achieve the persistence of statistical information in MYSQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.