A Preliminary Study on MySQL statistics and estimation methods, and a preliminary study on mysql statistics

A Preliminary Study on MySQL statistics and estimation methods, and a preliminary study on mysql statistics

 

The statistical information in the database describes the data distribution in the table to varying degrees. The execution plan obtains the data size (number of rows) that meets the query conditions through the statistical information ), to guide the generation of execution plans.
Histograms are an important component of statistics in commercial databases represented by Oracle and SQLServer and databases represented by open-source PostgreSQL.
When an execution plan is generated, the number of rows that meet the conditions are estimated through the statistical information and the histogram of the statistical information, thus affecting the generation of the execution plan.
The impact of statistical information on the execution plan is embodied in: index search and scanning, driving sequence between tables when multiple tables are connected, and JOIN mode between tables, and resource allocation for SQL query statements.
However, in MySQL databases, the execution plan method is relatively simple. Only the LOOPJOIN method is used for JOIN between tables, and there is no parallel execution plan, that is to say, the impact of the number of rows in the prediction result set on the execution plan is limited.
However, in some cases, we still need to predict the generation of execution plans,
For example, a common multi-Table connection driver sequence is usually used for small tables to drive large tables (not necessarily, therefore, we need to estimate the generation of execution plans in MySQL.

However, the statistical information in MySQL only has one cardinality information to predict the selectivity of indexes (show index from table). It does not contain the histogram information, that is, it is impossible to predict the size of the query data through the histogram, mysql uses other methods to implement estimation.
For histogram data, the histogram provides an important basis for estimation. For MySQL without a histogram, how is the execution plan estimated? How accurate is the estimation?
When I was studying this problem, I also encountered a lot of questions at the beginning. I was able to explain it only after reading the question of the big God in the blog Park. I will give a link later.

 

First, an interesting phenomenon is observed through a very simple query through an example.

Create a test table. The test table is as follows:

create table test_statistics(    id int auto_increment primary key,    col2 varchar(200),    col3 varchar(200),    create_date datetime,    index idx_create_date(create_date))ENGINE=InnoDB;

The stored procedure inserts data cyclically and calls the stored procedure to generate million rows of data (rows of data, which is already a very small amount of data in actual applications ), the create_date field generates a random time in a range.

CREATE DEFINER=`root`@`%` PROCEDURE `p_insert_test_data`(    IN `loop_count` INT)BEGIN    declare i int;    while (loop_count>0)     do            insert into test_statistics(col2,col3,create_date) values (uuid(),uuid(), DATE_ADD(sysdate(), INTERVAL  -rand()*2400  hour));        set loop_count = loop_count -1;    end while;END

After writing test data, perform the following two queries for testing.

Simply use the select count (1) for testing
First, check the first query. The query time range is: where create_date> '2017-11-01 12:00:00 'and create_date <'2017-11-01 16:00:00'
We can find that the estimated number of rows in the explain statement is exactly the same as the actual number of rows.

Continue the second query and extend the query time range. The query time range is: where create_date> '2017-11-01 12:00:00 'and create_date <'2017-11-03 16:00:00'
We can find that there is a serious deviation between the estimate of the explain Execution Plan and the actual number of rows.

Why is the first query accurate, while the second query has serious deviations?

This should begin with the estimated calculation method.

First, the only difference between the first query and the second query is that the time range of the second query is relaxed. Why is the prediction accuracy of the execution plan greatly reduced after the time is relaxed?
Estimates in other databases are obtained through histograms. accuracy in the histograms contained in statistical information determines the accuracy of estimates.
Since it is an estimation, there must be an error, but it is a problem of large and small errors, which is related to the estimation method.
Any estimation is implemented in a way that is "Partial summary" to varying degrees. For example, SQL Server samples related data pages by a certain percentage, then it is stored in the histogram for estimation.
Of course, this "biased" estimation method is the result of a compromise between performance and accuracy. Considering the impact of collecting statistics on performance and resources, there are various ways or costs of the estimation strategy to minimize the cause of the error.
MySQL directly estimates data pages within the scope of the query conditions during query. However, the data page for sampling has certain restrictions, so there is no unrestricted sampling for statistical estimation.
If the data page that meets the condition is out of the predefined range, the partial page is used for estimation instead of the whole page (not to mention the reason why not all samples are used for statistical estimation ).

 

For example, whether it is clustered index or secondary index (non-clustered index), it is theoretically a Balance Tree and will not explore its details for the moment.
If the data that meets the condition is in a range, it is located between two rectangular boxes. Rectangular boxes are left and right nodes in the range, and multiple leaf nodes can be imagined in the middle.
Refer to zhanlijun's article,
The above reference links show that the estimated principles of MySQL after MySQL 5.5 are as follows:
The data pages of the prediction scan are the first and second data pages and eight consecutive Data Pages starting from the left side. The average number of rows per page is obtained, estimate the number of data rows in this range based on the total number of pages.
Specifically, two left and right leaf nodes and eight consecutive pages of data starting from the left leaf node may have multiple data pages but may be ignored, this is the "partial generalization" method mentioned above.
There is one of the most obvious problems here, that is, the relationship between the Qualified Data Page and the page size collected during the estimation.
If there are less than 10 data pages that meet the conditions, these pages will be fully scanned during estimation. Of course, the estimation is completely accurate, this is also the reason why the actual number of rows estimated by the first query execution plan is completely inconsistent.
If there are more than 10 data pages that meet the conditions, these pages will be partially scanned during the estimation, and the estimated error will be generated, this is also the reason why the actual number of rows in the second query execution plan is significantly different.

Of course, each MySQL version may be improved or different. I have not found a specific algorithm from the source code. The current test is version 5.7.20.

 

But it is still unclear,
1. In the create_date field, the time is generated according to DATE_ADD (sysdate (), INTERVAL-rand () * 2400 hour). The overall distribution shows that the time is evenly distributed.
Theoretically, based on this method, the deviation of the estimated results should not be very large, but it is unclear why the estimation is so different from the actual situation.
2. Try to find the estimated value from accurate to the critical point of the difference, by querying the actual number of rows, according to the value of key_len and the storage principle of the B-tree index (the key value of the secondary index stored by the secondary index leaf node + the key value of the clustered index ).
Theoretically, the number of pages of a rough sample is calculated. It is found that the theoretical 10 pages of this value forecast are significantly different. It may be due to a problem in the push mode, or MySQL estimates have some unknown details.
3. I did not go through the MySQL source code in detail and have not found the specific implementation details.

 

For databases with histograms, the histogram information is neither cost-effective nor omnipotent. histograms also have limitations.
For a MySQL database without a histogram, its estimation principle is to sample and estimate the relevant data pages each time a query is performed, rather than obtaining the estimation information from the histogram, this is a very performance-consuming operation.
Http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/ for details
This may make MySQL not suitable for large data volumes or complex JOIN operations. Of course, this also depends on the specific business design scheme and the degree of dependence on data, or subjective query prompt operation.
This statement is at risk of getting angry with MySQL fans.
I have not found many articles about the estimated knowledge of MySQL, and I am stuck with my personal understanding. I also hope to give more advice to the experts who are interested in this aspect.
It is said that MySQL 8.0 and later versions will add histogram information and other JOIN methods (except loop join), which may be of great help to performance.

 

Reference link:
Https://www.cnblogs.com/LBSer/p/3333881.html
Http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/