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

The level is limited. If there is any mistake, please point out.

Source code version: 5.7.22

Welcome to my "in-depth understanding of MySQL Master and Slave principle 32", as follows:

If the picture cannot be displayed, please see the following link:

Https://www.jianshu.com/p/d636215d767f

Following the previous article:

Https://www.jianshu.com/p/ce063e2024ad MySQL: the influence of the number of query fields on query efficiency

Let's move on to the differences in the implementation of count (*) count (field). Note that we all use Innodb as the storage engine here, and we don't discuss other engines. Because of the previous discussion, it is easier to see the difference between them, here are the following points to note:

We need to do the same as the implementation plan, here a full table scan as an example. In fact, count is likely to use overlay indexes (Using index), and this article focuses on the similarities and differences between their implementations. Count (*) and count (field) may not agree on the results. For example, count (field), but if some row fields are NULL, then the number of rows will not be counted, and the location of this NULL judgment will be explained below.

This article still uses a simple full table scan to compare the differences in implementation. First of all, we need to make it clear that count uses a COUNT counter.

I. use examples

In the example, we can also see that the results of the two statements are actually not consistent.

Mysql > show create table baguai_f\ gateway * 1. Row * * Table: baguai_fCreate Table: CREATE TABLE `baguai_ f` (`id` int (11) DEFAULT NULL, `a` varchar (20) DEFAULT NULL, `b` varchar (20) DEFAULT NULL `c` varchar (20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql > select * from baguai_f +-+ | id | a | b | c | +-+ | 1 | g | g | NULL | | 1 | G1 | 3 | G2 | 4 | g | g | NULL | | 5 | g | | | g | NULL | | 6 | G3 | +-+ 6 rows in set (0.00 sec) mysql > desc select count (*) from baguai_f where baggage +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-+ -+ | 1 | SIMPLE | baguai_f | NULL | ALL | NULL | 6 | 16.67 | Using where | +- -- + 1 row in set 1 warning (0.00 sec) mysql > desc select count (c) from baguai_f where +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-+ -+ | 1 | SIMPLE | baguai_f | NULL | ALL | NULL | 6 | 16.67 | Using where | +- -- + 1 row in set 1 warning (0.00 sec) mysql > select count (*) from baguai_f where breadwinner +-+ | count (*) | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql > select count (c) from baguai_f where count (c) | +-+ | 0 | +-+ 1 row in set (0.00 sec)

This inconsistency arises from the fact that there are null values in the c column of bounded roomg', so count (c) returns 0.

Second, a brief analysis of the data acquisition process of count (*) in the example

Note that in the article "MySQL: the impact of the number of query fields on query efficiency", we have described part of the process in detail. I will not discuss it here. If you need more detailed understanding, please refer to it yourself.

1. Build read_set in MySQL layer

The read_set you build here will actually contain only column b, which is a field.

2. Innodb layer construction template

Similarly, only column b will be included in the field template built from read_set.

LOOP: here starts to return each piece of data in a loop

3. The Innodb layer returns data according to the template

Here we can look at the number of templates and the specific column names corresponding to the templates.

Number of templates breakpoint: row_sel_store_mysql_rec View template number: (gdb) p prebuilt- > n_template$1 = 1 View template corresponding field breakpoint: row_sel_field_store_in_mysql_format_func View template corresponding field: (gdb) p field- > name$3 = {m_name = 0x7ffe7c99cf85 "b"}

Obviously, the value of column b is just returned to the MySQL layer, which is also easy to understand, because column b needs to continue to filter in the MySQL layer.

4. Filter conditions in MySQL layer

All right, the data currently returned to the MySQL layer contains only the data in column b, and then apply the condition of bounded roomg'to filter.

5. Do a COUNT count operation after filtering in the MySQL layer

For ordinary select statements, the filtered data can be returned, but for the operation of count, what is done here is a counting operation, in which the row judges the null value of the count field. Of course, if count (*) is used here, there is no null value judgment. Here is the code:

Bool Item_sum_count::add () {if (aggr- > arg_is_null (false)) return 0; count++; return 0;} END LOOP

In the end, we just need to return this count. The following is the data sent, and the breakpoint can be set in Query_result_send::send_data.

22 = Item::SUM_FUNC_ITEM (gdb) p ((Item*) (items)-> first- > info)-> field_type () $23 = MYSQL_TYPE_LONGLONG (gdb) p ((Item*) (items)-> first- > info)-> val_int () $24 = 3 (gdb) p (items)-> first- > info$26 = (void *) 0x7ffe7c006580 (gdb) p ((Item_sum_count*) $26)-> count$28 = 3

The data we can send is actually this counter, with a final value of 3.

Third, the difference in the process of count (c) obtaining data in the example

In fact, the whole process is basically the same, but the difference is:

The number of templates varies with different read_set, because two fields, b and c, are needed, in which column b is used for where conditional filtering, and column b is used to count whether there is a null value, so the number of templates is 2, as follows: (gdb) p prebuilt- > n_template$29 = 2 will actually filter according to the null value of column c when doing the COUNT counter. As long as the operation is NULL, the count count will not increase by 1. Refer to this code again: bool Item_sum_count::add () {if (aggr- > arg_is_null (false)) / / filter null value return 0 Count++; return 0;}

Finally, the function Field::is_null will be called to determine the null value, and the breakpoint can be set here.

IV. Summary of different points

The statement count (c) in the example returns 0. Now it is clear when the data was filtered out, which can be summarized as follows:

The Innodb layer returns all the row data. The MySQL layer is filtered through the where condition, leaving the rows of bounded variables g'. The MySQL layer determines through NULL and excludes the remaining rows in count (c) that are NULL from the count.

Count (*) does not have step 3, which is a difference.

Then the difference is in the returned field:

Count (c) obviously needs to return the c column to the MySQL layer count (*) in addition to the where condition, then there is no need to return additional fields to the MySQL layer, just the MySQL layer to filter the required b column.

Through the above analysis, in fact, there is not much difference in efficiency. I think that under the premise that the same execution plan and the same data results are returned, the efficiency of count (*) may be slightly higher.

Fifth, spare stack frame null value count filter stack frame # 0 Field::is_null (this=0x7ffe789949d8, row_offset=0) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.h:1129#1 0x0000000000fbc678 in Item_field::is_null (this=0x7ffe78006a78) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/item.h:2929#2 0x000000000146ebf5 in Aggregator_simple::arg_is_null (this=0x7ffe78b451d0 Use_null_value=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_sum.cc:1633#3 0x000000000146ef18 in Item_sum_count::add (this=0x7ffe780066c0) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_sum.cc:1683#4 0x0000000001478475 in Aggregator_simple::add (this=0x7ffe78b451d0) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_sum. 0x0000000001478301 in Item_sum::aggregator_add (this=0x7ffe780066c0) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_sum.h:526#6 0x000000000157fd0b in update_sum_func (func_ptr=0x7ffe78007740) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:535#7 0x0000000001585d3e in end_send_group (join=0x7ffe78007370) Qep_tab=0x7ffe78007bd0, end_of_records=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:3117#8 0x0000000001582059 in evaluate_join_record (join=0x7ffe78007370, qep_tab=0x7ffe78007a58) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1645#9 0x000000000158145a in sub_select (join=0x7ffe78007370, qep_tab=0x7ffe78007a58 End_of_records=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297#10 0x0000000001580cce in do_select (join=0x7ffe78007370) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950

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.