How does MySQL's optimizer handle count (*)?
Today, I will talk to you about how MySQL's optimizer handles count (*). Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.
Recently, I have read a lot of Ali students' MySQL articles. Ali Kernel students' articles put on the code when they don't agree with each other, which not only let us see the results, but also have the code to read. If we encounter similar problems, this kind of interpretation is really very rare.
I did a little test today and found that the handling of count (*) in MySQL 5.7seems a little bossy and not as good as I thought.
For comparison, I found a set of 5.6 environment.
Overall, the handling of count (*) in the 5.6 environment is very flexible and easy-going, and I can check as much as you want me to. The initial data is 1 million.
+-+
| | count (*) |
+-+
| | 1000000 |
+-+
The statement to create the table is as follows:
> show create table test\ G
* * 1. Row *
Table: test
Create Table: CREATE TABLE `test` (
`id`int (11) NOT NULL AUTO_INCREMENT
`a`int (11) DEFAULT NULL
`b` int (11) DEFAULT NULL
`c`int (11) DEFAULT NULL
PRIMARY KEY (`id`)
KEY `mrrx` (`a`, `b`)
KEY `xx` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec) the use of count (*) in MySQL has always been not advocated, or notorious, which makes many Oracle students do not understand, in fact, they are not blessed.
For such a count (*) query, the effect is as follows in 5.6. when estimating, the index xx is taken by default.
> explain select count (*) from test\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: test
Type: index
Possible_keys: NULL
Key: xx
Key_len: 5
Ref: NULL
Rows: 998396
Extra: Using index
1 row in set (0.01 sec)
If we force the mrrx index, the optimizer says it's fine, so we take the mrrx index, and the estimated data is slightly different from the above.
> explain select count (*) from test force index (mrrx)\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: test
Type: index
Possible_keys: NULL
Key: mrrx
Key_len: 10
Ref: NULL
Rows: 947698
Extra: Using index
1 row in set (0. 00 sec) or we explicitly specify the xx index, and the optimizer says yes, and then estimates the number of rows with little difference from the first.
> explain select count (*) from test force index (xx)\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: test
Type: index
Possible_keys: NULL
Key: xx
Key_len: 5
Ref: NULL
Rows: 947698
Extra: Using index
1 row in set (0.00 sec) if you change the position, if you specify index column c, specify a condition, and then take a look, you will see a big difference in the results before and after.
> explain select count (*) from test where c > 0\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: test
Type: range
Possible_keys: xx
Key: xx
Key_len: 5
Ref: NULL
Rows: 473849
Extra: Using where; Using index
1 row in set (0.00 sec) from this point of view, there is still a big difference in the evaluation of statistics, and there are still a lot of limitations without statistics, but the optimizer is very easygoing.
Let's take a look at 5.7.
The same amount of statements and data are obviously filtered in 5.7.
> explain select count (*) from test\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: NULL
Partitions: NULL
Type: NULL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: NULL
Filtered: NULL
Extra: Select tables optimized away
1 row in set, 1 warning (0.02 sec)
This means that it has been optimized during the optimizer phase.
And then the same statements are handled in the same way.
> explain select count (*) from test force index (mrrx)\ G
> explain select count (*) from test force index (xx)\ G
Extra: Select tables optimized away
If we still give index column c a filter condition as before, the optimizer suddenly becomes mild. It is obvious that the effect of this implementation is much better.
> explain select count (*) from test where c > 0\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: test
Partitions: NULL
Type: range
Possible_keys: xx
Key: xx
Key_len: 5
Ref: NULL
Rows: 498949
Filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.02 sec)
To some extent, 5.7 such a treatment is also a disguised retrogression.
After reading the above, do you have any further understanding of how MySQL's optimizer handles count (*)? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
