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

This article will explain in detail how many query fields in MySQL will affect the query efficiency. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

I. the origin of the problem

We know that different execution plans will certainly lead to differences in efficiency, but in this case the execution plans are exactly the same, all are full table scans, and the only difference is the number of fields. Secondly, the tests use where conditions for filtering (Using where), and there is no data return after filtering. We often say that where filtering is actually in the MySQL layer. Of course, in some cases, using ICP will filter data in the Innodb layer in advance. Let's not consider ICP here. I will describe the ICP process in detail in a later article. This article will also give the where filtering interface for your reference.

The screenshot below is from two friends who thank them for their tests and questions. In addition, for a large amount of data access may involve physical IO, the first access and subsequent access because of Innodb buffer, the efficiency is different is normal, need to be tested several times.

Test 1:

Test 2:

Through these two tests, we can find that as the number of fields continues to decrease, the efficiency becomes more and more efficient, and the main differences are under sending data, a state I have roughly described in the reference article:

Https://www.jianshu.com/p/46ad0aaf7ed7

Https://www.jianshu.com/p/4cdec711adef

To put it simply, the acquisition of Innodb data and the transmission of Innodb data to MySQL layer data are included.

Second, a simple process introduction

Below I mainly combine the number of fields and full table scan two aspects to do a simple process introduction. In fact, one of the core interfaces is row_search_mvcc, which probably contains the following functions:

Get data through prefetch cache

Open transaction

Locate the index location (including quick positioning using AHI)

Whether to enable readview

Continuously access the next piece of data by persisting cursors

Add Innodb table lock and Innodb row lock

Visibility judgment

Return to the table according to the primary key (row lock may be required to return the table)

ICP optimization

SEMI update optimization

And as a necessary interface to access data, this function is also worth studying carefully.

1. Build read_set (MySQL layer) through the select field

First, you need to build a bitmap called read_set to represent the location and number of fields accessed. Together with write set, it also plays an important role in recording the Event of binlog. You can refer to my "in-depth understanding of MySQL Master-Slave principle" section on binlog_row_image parameters. The main interface built here is the TABLE::mark_column_used function, which is called for each field that needs to be accessed to set its own bitmap. The following is one of the following paragraphs:

Case MARK_COLUMNS_READ: bitmap_set_bit (read_set, field- > field_index)

From the stack frame, the process of building the read_set is under the state 'init'. For the stack frame, see the end stack frame 1.

two。 A template (mysql_row_templ_t) (Innodb layer) is also built when the first access location is located.

This template is mainly used when converting Innodb layer data to MySQL layer data, which records the number of fields used, the character set of the field, the type of the field, and so on. The interface build_template_field is used to build this template. For the stack frame, see the end stack frame 2.

However, it is important to note that the construction template here will determine how many fields need to be built into the template through the read_set mentioned above, and then the build_template_field function will be called. The following is the most important code, which is in the build_template_needs_field interface.

Bitmap_is_set (table- > read_set, static_cast (I)

You can see that this field is being tested here to see if it appears in read_set, and if not, skip it. Here are the comments for the function build_template_needs_field:

Determines if a field is needed in a m_prebuilt struct 'template'.@return field to use, or NULL if the field is not needed * /

So far, the fields we need to access have been established.

3. Locate the data for the first time, locate the first row of the cursor to the primary key index, and prepare for a full table scan (Innodb layer)

For this full table scan mode, the positioning data becomes simple. We only need to find the first piece of data in the primary key index, which is different from the usual (ref/range) positioning method and does not need the support of dichotomy. Therefore, the first location call function for a full table scan is btr_cur_open_at_index_side_func, rather than what we usually call btr_pcur_open_with_no_init_func.

If you take a rough look at the function btr_cur_open_at_index_side_func, we can easily see that it locates the first block of the leaf node through the B + tree structure, and then calls the function page_cur_set_before_first to place the cursor at the beginning of all records, with only one purpose ready for full table scanning. For the stack frame, see the end stack frame 3.

Notice that this is called through our row_search_mvcc.

4. Get the first piece of data of the Innodb layer (Innodb layer)

After you get the cursor, you can get the data. Here, the code is as follows:

Rec = btr_pcur_get_rec (pcur); / / get the whole row of data from the persistent cursor

But it should be noted that the data obtained here is only a pointer, the implication can be understood as the entire row of data, its format is also the original Innodb data, which also contains some pseudo columns such as (rollback ptr and trx id). This actually has nothing to do with the number of fields accessed.

5. Convert the first row of records to MySQL format (Innodb layer)

After this step, we can assume that the record has been returned to the MySQL layer, here is the actual data copy, not a pointer, the whole process is put into the function row_sel_store_mysql_rec.

Our previous template (mysql_row_templ_t) will also play its role here, which is a process of field filtering. Let's first look at a loop.

For (I = 0; I)

Nasty templates; iplates +)

Where prebuilt- > n_template is the number of field templates, as we mentioned earlier, through read_set filtering, we will not create templates for fields we do not need. So the number of templates here is the same as the number of fields we access.

Then row_sel_store_mysql_field_func is called under the loop and then row_sel_field_store_in_mysql_format_func is called to convert the fields one by one to MySQL format. Let's take a look at one of the types of conversions as follows:

Case DATA_INT: / * Convert integer data from Innobase to a little-endian format, sign bit restored to normal * / ptr = dest + len; for (;;) {ptr--; * ptr = * data;// value copy memory copy if (ptr = = dest) {break;} data++;}

We can find that this is an actual transformation, that is, it costs memory space. For the stack frame, see the end stack frame 4.

So we probably know that the more fields you query, the longer the conversion process here, and here are the actual memory copies.

Eventually, this line of data will be stored in the parameter buffer of row_search_mvcc and returned to the MySQL layer. The comments for this parameter are as follows:

@ paramout] buf buffer for the fetched row in MySQL format6. Where filtering the first piece of data (MySQL layer)

Of course, after getting the data, it cannot be returned to the user as the final result. We need to do a filter operation in the MySQL layer. This condition comparison is at the beginning of the function evaluate_join_record. The comparison is the following sentence.

Found= MY_TEST (condition- > val_int ()); / / compare calls to conditions and returns comparisons that will be recorded

False will be returned if it does not match the condition. The comparison here will eventually call the various methods of Item_func, if equal to Item_func_eq, and see the stack frame at the end of stack frame 5.

7. Access the next piece of data

I have shown the general process of accessing the first piece of data above, and the next thing I need to do is to continue the access, as follows:

Move the cursor to the next line

Access data

Return the conversion data to the MySQL layer according to the template

Filter based on where condition

The whole process continues until all primary key index data access is complete. However, it should be noted that there are some changes in the upper layer interface, from ha_innobase::index_first to ha_innobase::rnd_next, and statistics from Handler_read_first to Handler_read_rnd_next. Please refer to my article:

Https://www.jianshu.com/p/25fed8f1f05e

And the process of row_search_mvcc is bound to change. I won't talk about it here. However, the actual acquisition data conversion process and filtering process have not changed.

Note that except for step 1, these steps are basically under sending data.

Third, go back to the question itself

Now that we know the process of accessing data from a full table scan, let's take a look at the similarities and differences between the number of fields in the full table scan process:

Differences:

The read_set you build is different. The more fields you have, the more bits in the read_set that are'1'.

Different templates are established. The more fields, the more templates.

Each row of data is converted to MySQL format differently, and the more fields there are, the more templates there are, the more cycles each field has to loop, and that's what each row has to deal with.

The greater the memory consumption of rows returned to the MySQL tier

Similarities:

The number of rows accessed is the same

The process of access is the same

Where filters in the same way

In the whole difference, I think the most time-consuming part is that it is the most expensive to convert each row of data to MySQL format, because each row and every field needs to do such a transformation, which is just below the sending data state. Our online tables with more than 10 fields abound, and if we only need to access a small number of them, we'd better write the actual fields instead of'*'to avoid this problem.

Fourth, write at the end

Although the full table scan is explained as a column in this article, in fact, we should reduce the number of access fields in any case and access only the fields we need.

5. Alternate stack frame

Stack frame 1 read_set construction

# 0 TABLE::mark_column_used (this=0x7ffe7c996c50, thd=0x7ffe7c000b70, field=0x7ffe7c997c88, mark=MARK_COLUMNS_READ) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/table.cc:6344#1 0x00000000015449b4 in find_field_in_table_ref (thd=0x7ffe7c000b70, table_list=0x7ffe7c0071f0, name=0x7ffe7c006a38 "id", length=2, item_name=0x7ffe7c006a38 "id", db_name=0x0, table_name=0x0, ref=0x7ffe7c006bc0, want_privilege=1, allow_rowid=true, cached_field_index_ptr=0x7ffe7c0071a0, register_tree_change=true Actual_table=0x7fffec0f46d8) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_base.cc:7730#2 0x0000000001544efc in find_field_in_tables (thd=0x7ffe7c000b70, item=0x7ffe7c0070c8, first_table=0x7ffe7c0071f0, last_table=0x0, ref=0x7ffe7c006bc0, report_error=IGNORE_EXCEPT_NON_UNIQUE, want_privilege=1, register_tree_change=true) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_base.cc:7914#3 0x0000000000faadd8 in Item_field::fix_fields (this=0x7ffe7c0070c8, thd=0x7ffe7c000b70 Reference=0x7ffe7c006bc0) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/item.cc:5857#4 0x00000000015478ee in setup_fields (thd=0x7ffe7c000b70, ref_pointer_array=..., fields=..., want_privilege=1, sum_func_list=0x7ffe7c005d90, allow_sum_func=true, column_update=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_base.cc:9047#5 0x000000000161419d in st_select_lex::prepare (this=0x7ffe7c005c30 Thd=0x7ffe7c000b70) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_resolver.cc:190

Stack frame 2 build template

# 0 build_template_field (prebuilt=0x7ffe7c99b880, clust_index=0x7ffe7c999c20, index=0x7ffe7c999c20, table=0x7ffe7c996c50, field=0x7ffe7c997c88, iTun0, v_no=0) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:7571#1 0x00000000019d1dc1 in ha_innobase::build_template (this=0x7ffe7c997610, whole_row=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:8034#2 0x00000000019d60f5 in ha_innobase::change_active_index (this=0x7ffe7c997610 Keynr=0) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9805#3 0x00000000019d682b in ha_innobase::rnd_init (this=0x7ffe7c997610, scan=true) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10031#4 0x0000000000f833b9 in handler::ha_rnd_init (this=0x7ffe7c997610 Scan=true) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:3096#5 0x00000000014e24d1 in init_read_record (info=0x7ffe7cf47d60, thd=0x7ffe7c000b70, table=0x7ffe7c996c50, qep_tab=0x7ffe7cf47d10, use_record_cache=1, print_error=true, disable_rr_cache=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/records.cc:315

Stack frame 3 full table scan initial positioning stack frame

# 0 page_cur_set_before_first (block=0x7fff4d02f4a0, cur=0x7ffe7c99bab0) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/include/page0cur.ic:99#1 0x0000000001c5187f in btr_cur_open_at_index_side_func (from_left=true, index=0x7ffe7c999c20, latch_mode=1, cursor=0x7ffe7c99baa8, level=0, file=0x239d388 "/ root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/include/btr0pcur.ic", line=562 Mtr=0x7fffec0f3570) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/btr/btr0cur.cc:2422#2 0x0000000001b6e9c9 in btr_pcur_open_at_index_side (from_left=true, index=0x7ffe7c999c20, latch_mode=1, pcur=0x7ffe7c99baa8, init_pcur=false, level=0, mtr=0x7fffec0f3570) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/include/btr0pcur.ic:562#3 0x0000000001b79a35 in row_search_mvcc (buf=0x7ffe7c997b50 "\ 377", mode=PAGE_CUR_G, prebuilt=0x7ffe7c99b880, match_mode=0 Direction=0) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:5213#4 0x00000000019d5493 in ha_innobase::index_read (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\ 377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536#5 0x00000000019d66ea in ha_innobase::index_first (this=0x7ffe7c997610 Buf=0x7ffe7c997b50 "\ 377") at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9977#6 0x00000000019d6934 in ha_innobase::rnd_next (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\ 377") at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10075#7 0x0000000000f83725 in handler::ha_rnd_next (this=0x7ffe7c997610 Buf=0x7ffe7c997b50 "\ 377") at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:3146#8 0x00000000014e2b3d in rr_sequential (info=0x7ffe7cf47d60) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/records.cc:521

Conversion of stack frame 4 MySQL format

# 0 row_sel_field_store_in_mysql_format_func (dest=0x7ffe7c997b51 ", templ=0x7ffe7c9a27f8, index=0x7ffe7c999c20, field_no=0, data=0x7fff4daec0a1"\ 200 ", len=4, prebuilt=0x7ffe7c99b880, sec_field=18446744073709551615) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:2888#1 0x0000000001b754b9 in row_sel_store_mysql_field_func (mysql_rec=0x7ffe7c997b50"\ 377 ", prebuilt=0x7ffe7c99b880, rec=0x7fff4daec0a1"\ 200 ", index=0x7ffe7c999c20, offsets=0x7fffec0f3a80, field_no=0, templ=0x7ffe7c9a27f8 Sec_field_no=18446744073709551615) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:3255#2 0x0000000001b75c85 in row_sel_store_mysql_rec (mysql_rec=0x7ffe7c997b50 "\ 377", prebuilt=0x7ffe7c99b880, rec=0x7fff4daec0a1 "\ 200", vrow=0x0, rec_clust=0, index=0x7ffe7c999c20, offsets=0x7fffec0f3a80 Clust_templ_for_sec=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:3434#3 0x0000000001b7bd61 in row_search_mvcc (buf=0x7ffe7c997b50 "\ 377", mode=PAGE_CUR_G, prebuilt=0x7ffe7c99b880, match_mode=0, direction=0) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:6123#4 0x00000000019d5493 in ha_innobase::index_read (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\ 377", key_ptr=0x0, key_len=0 Find_flag=HA_READ_AFTER_KEY) at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536#5 0x00000000019d66ea in ha_innobase::index_first (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\ 377") at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9977#6 0x00000000019d6934 in ha_innobase::rnd_next (this=0x7ffe7c997610 Buf=0x7ffe7c997b50 "\ 377") at / root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10075#7 0x0000000000f83725 in handler::ha_rnd_next (this=0x7ffe7c997610 Buf=0x7ffe7c997b50 "\ 377") at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:3146#8 0x00000000014e2b3d in rr_sequential (info=0x7ffe7cf47d60) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/records.cc:521#9 0x0000000001584264 in join_init_read_record (tab=0x7ffe7cf47d10) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2487#10 0x0000000001581349 in sub_select (join=0x7ffe7cf47660, qep_tab=0x7ffe7cf47d10) End_of_records=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1277#11 0x0000000001580cce in do_select (join=0x7ffe7cf47660) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950

Equivalent comparison of stack frame 5 String

# 0 Arg_comparator::compare_string (this=0x7ffe7c0072f0) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.cc:1669#1 0x0000000000fde1e4 in Arg_comparator::compare (this=0x7ffe7c0072f0) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.h:92#2 0x0000000000fcb0a1 in Item_func_eq::val_int (this=0x7ffe7c007218) at / root/mysqlall/percona-server-locks-detail-5.7.22 / sql/item_cmpfunc.cc:2507#3 0x0000000001581af9 in evaluate_join_record (join=0x7ffe7c0077d8 Qep_tab=0x7ffe7cb1dc70) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1492#4 0x000000000158145a in sub_select (join=0x7ffe7c0077d8, qep_tab=0x7ffe7cb1dc70 End_of_records=false) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297#5 0x0000000001580cce in do_select (join=0x7ffe7c0077d8) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950#6 0x000000000157eb8a in JOIN::exec (this=0x7ffe7c0077d8) at / root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199 about "MySQL" The number of query fields in the query will affect the query efficiency. "this is the end of the article." Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

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.