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

How to manually update the statistical analysis records of the MYSQL table, I believe that many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Generally speaking, most databases have statistical analysis, and the main function is to choose a relatively correct way to carry out the execution plan as far as possible in the case of sentence execution, and the more accurate statistical analysis is, it can bring better execution plan and database statement execution performance, but relatively accurate statistical analysis will also bring system performance consumption in statistics, the larger the database system. The higher the demand and requirements for statistical analysis.

Starting with MYSQL 5.6.The information of statistical analysis will be solidified in the storage of the system. You can see whether our separate switches are turned on or not through the following statement.

In statistical analysis, there is a concept of sampling rate, that is, the number of index pages to be sampled according to the cardinality of the estimated index column and other statistical information. In fact, this may seem reasonable, but in fact, if the selected index page is not representative, such an algorithm may also misguide the basic statistical analysis information of the database. This is why sometimes the index is established but does not go, and the result of the execution changes after the statistical analysis is triggered manually. The number of pages sampled by default is 8 pages. We can change the number of pages sampled by default to improve the accuracy. But the same price is the consumption of Icano and related resources at the time of sampling.

We can analyze table some large tables on a regular basis, and we can write a script that runs regularly to accomplish this, so as to achieve the accuracy of statistical analysis as far as possible. But this kind of work is usually done by automatic trigger.

We can do a test to see if there is a gap between the index of the MYSQL and the actual table information.

1 We distinct the fields we need to verify and compare the data returned by count with the data we indexed to see if there is any problem. Comparing the data in the two graphs, we can clearly find that the Cardinality in the index is different from the actual field data.

In fact, we have operated the analyze table but we still do not get the exact number, in normal times this may not be a problem, but if it is a larger table, such as tens of millions of tables, if this aspect is wrong, it will cause problems to the implementation plan, at this time we may need to update the records of some tables manually.

Update mysql.innodb_table_stats set n_rows = 300024 where database_name = 'employees' and table_name =' employees'

In the same way, you can update the data in the innodb_index_stats table.

The inaccuracy of the above methods only used in statistical analysis seriously affects the execution plan. In general, we should not touch the statistical analysis table in the system. Another problem with this is that your table will not be updated frequently. And you need to find a good point in time to update your values.

After reading the above, do you know how to manually update the statistical analysis records of the MYSQL table? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.