Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How does MySQL's optimizer handle count (*)?

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

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.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report