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

This article is about how to query the storage engine in Innodb. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

Source code analysis

Query is closely related to the implementation of storage engine, so the following contents mainly focus on the query processing of Innodb storage engine. For the entry point of query output, this article starts with the do_select () (sql\ sql_select.cc) function. This function is mainly used to query the matching results and transmit the query results through socket or write them to the data table.

First, take a look at the invocation logic, as follows:

Do_select (): query entry function.

| sub_select (): query some join records. Call ha_innobase::rnd_next () and evaluate_join_record () in a loop to get and process each record in that section. (sql\ sql_select.cc:11705)

| | evaluate_join_record (): processes a query record. (sql\ sql_select.cc:11758)

| | rr_sequential (): call ha_innobase::rnd_next () to read the next record. (sql\ records.cc:452)

| ha_innobase::rnd_next (): reads the next record. (storage\ innobase\ handler\ ha_innodb.cc:6141)

| ha_innobase::general_fetch (): gets the next or previous record from a given index location. (storage\ innobase\ handler\ ha_innodb.cc:5948)

| row_search_for_mysql (): query a record from the database. The following is divided into six stages to deal with each part. (storage\ innobase\ row\ row0sel.c:3369)

| | Phase 1: release the lock of the adaptive hash index.

| the rw_lock_get_writer () function is used to acquire read-write locks. If the acquisition fails, release the current read-write lock. (storage\ innobase\ include\ sync0rw.ic:122)

| | Phase 2: obtain records from pre-read cache.

| | row_sel_pop_cached_row_for_mysql (): function is used to read a row of records from cache, (storage\ innobase\ row\ row0sel.c:3167) |

| | row_sel_copy_cached_field_for_mysql (): function reads each field. (storage\ innobase\ row\ row0sel.c:3134)

| | Phase 3: quick search using adaptive hash index.

| the row_sel_try_search_shortcut_for_mysql () function uses the hash index to get the records of the clustered index. (storage\ innobase\ row\ row0sel.c:3293)

| | row_sel_store_mysql_rec () function converts the acquired row records in innobase format to mysql format. (storage\ innobase\ row\ row0sel.c:2692)

| the row_sel_field_store_in_mysql_format () function converts each field in a row record in innobase format to mysql format. (storage\ innobase\ row\ row0sel.c:2535)

| | Phase 4: open and restore the cursor position of the index.

| sel_restore_position_for_mysql (): restores the cursor position of the index. (storage\ innobase\ row\ row0sel.c:3070)

| | btr_pcur_restore_position_func (): restores the location of a persistent cursor. (storage\ innobase\ btr\ btr0pcur.c:208)

| btr_cur_get_index (): get the index. (storage\ innobase\ include\ btr0pcur.ic:51)

| buf_page_optimistic_get ():

| btr_pcur_get_rec (): gets the record of persistent cursors. (storage\ innobase\ include\ btr0pcur.ic:104)

| btr_cur_get_rec (): gets the record of the current cursor position. (storage\ innobase\ include\ btr0pcur.ic:104)

| rec_get_offsets_func (): gets the offset of each field in the record. (storage\ innobase\ rem\ rem0rec.c:524)

| | btr_pcur_move_to_next (): move the persistent cursor to the next record. (storage\ innobase\ include\ btr0pcur.ic:342)

| | Phase 5: find matching records. |

| page_rec_is_infimum (): check whether the current record is the infinum record of this page. Infinum records represent records that are smaller than any key value. (storage\ innobase\ include\ page0page.ic:415)

| page_rec_is_supermum (): check whether the current record is the supermum record of this page. Supermum records represent records that are larger than any key value. (storage\ innobase\ include\ page0page.ic:403)

| rec_get_next_offs (): gets the offset of the next record in the same page. (storage\ innobase\ include\ rem0rec.ic:325)

| rec_get_offsets_func (): gets the offset of each field in the record. (storage\ innobase\ rem\ rem0rec.c:524)

| rec_offs_validate (): verify the offset of the record. (storage\ innobase\ rem\ rem0rec.c:954)

| the row_sel_store_mysql_rec () function converts the acquired row records in innobase format to mysql format. (storage\ innobase\ row\ row0sel.c:2692)

| | row_sel_field_store_in_mysql_format () function converts each field in a row record in innobase format to mysql format. (storage\ innobase\ row\ row0sel.c:2535)

| | btr_pcur_store_position (): the location where the cursor is stored. (storage\ innobase\ btr\ btr0pcur.c:89)

| btr_pcur_get_block (): gets the buffer block of the persistent cursor. (storage\ innobase\ include\ btr0pcur.ic:90)

| btr_pcur_get_page_cur (): gets the cursor of the page that persists the cursor. (storage\ innobase\ include\ btr0pcur.ic:64)

| page_cur_get_rec (): gets the record of the cursor position. (storage\ innobase\ include\ page0cur.ic:76)

| dict_index_copy_rec_order_prefix (): copy the record. (storage\ innobase\ dict\ dict0dict.c:4185)

| rec_copy_prefix_to_buf (): copy the record field to the cache buffer. (storage\ innobase\ rem\ rem0rec.c:1383)

| dict_index_get_nth_field (): get the starting address of the nth field. (storage\ innobase\ include\ dict0dict.ic:620)

| dict_field_get_col (): gets the value of the nth field. (storage\ innobase\ include\ dict0dict.ic:663)

| | Phase 6: move the cursor to the next index record.

| btr_pcur_move_to_next (): move the persistent cursor to the next record. (storage\ innobase\ include\ btr0pcur.ic:342)

| mtr_commit (): commit transaction. (storage\ innobase\ mtr\ mtr0mtr.c:247)

From the above query logic, we can clearly see how the query of MySQL is carried out. In a nutshell, if the result can be obtained from an adaptive hash index (in memory), the result is converted from innobase format to mysql format and output; otherwise, according to the cursor position of the index, the record in the current page is obtained, and the current record is copied to memory, and the result is also converted to mysql format output.

From the above, it is difficult to see the regularity of the output. In fact, the output results are regular, which is closely related to the design and storage of the innodb storage engine.

The storage of the innodb storage engine aggregates the primary key as the key value according to the B+ index. If the primary key is not specified, the system will hide and establish a primary key. All the leaf nodes in the innodb storage engine are data records, which can be logically accessed sequentially. And the data storage and acquisition of the innodb storage engine are carried out according to the page. Therefore, when querying, the data of the entire page is loaded into memory, and the default page size of the innodb storage engine is 16K.

Experimental test

Through the above analysis, it is not difficult to understand that when querying, the output of a simple query is generally arranged according to the storage order of the B+ index. For further verification, let's do two experiments

Lab 1:

With a simple data table student with a primary key, the table definition is shown in the following table. The test statement takes a simple select * from student; as an example to test.

CREATE TABLE `student` (

`std_ id` int (11) NOT NULL

`std_ name`varchar (20) NOT NULL DEFAULT'""'

`std_ spec` varchar (20) NOT NULL DEFAULT'""'

`std_*** `tinyint (4) NOT NULL DEFAULT'0'

`std_ age`tinyint (3) unsigned NOT NULL DEFAULT'0'

PRIMARY KEY (`std_ id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

The test results are as follows:

Mysql > select * from student

+-+

| | std_id | std_name | std_spec | std_*** | std_age | |

+-+

| | 2012072301 | aaa | computer | 0 | 20 | |

| | 2012072303 | ccc | computer | 1 | 21 | |

| | 2012072304 | ddd | computer | 0 | 20 | |

| | 2012072305 | eee | information | 0 | 22 | |

| | 2012072306 | fff | computer | 1 | 20 | |

| | 2012072307 | ggg | computer | 0 | 20 | |

| | 2012072308 | hhh | computer | 0 | 21 | |

| | 2012072309 | iii | automatic | 0 | 20 | |

| | 2012072310 | abc | computer | 1 | 20 | |

| | 2012072311 | kkk | computer | 0 | 18 |

| | 2012072312 | lll | computer | 0 | 20 | |

| | 2012072313 | mmm | computer | 0 | 20 | |

| | 2012072314 | nnn | computer | 1 | 20 | |

| | 2012072315 | ooo | information | 0 | 20 | |

| | 2012072316 | ppp | computer | 0 | 19 | |

| | 2012072317 | qqq | computer | 1 | 20 | |

| | 2012072318 | rrr | information | 0 | 20 | |

| | 2012072319 | sss | computer | 1 | 20 | |

| | 2012072320 | ttt | computer | 0 | 20 | |

| | 2012072321 | uuu | automatic | 0 | 23 | |

| | 2012072322 | vvv | computer | 0 | 20 | |

| | 2012072323 | www | computer | 1 | 20 | |

| | 2012072324 | xxx | computer | 0 | 25 | |

| | 2012072325 | yyy | automatic | 0 | 20 | |

| | 2012072326 | zzz | computer | 1 | 20 | |

| | 2012080811 | bbb | information | 0 | 20 | |

+-+

Experiment 2

Also take the student table as an example, update the record with the primary key of 2012080811 as follows:

Update student set std_id=2012072302 where std_id=2012080811

Then, during the test, the test results are as follows:

+-+

| | std_id | std_name | std_spec | std_*** | std_age | |

+-+

| | 2012072301 | aaa | computer | 0 | 20 | |

| | 2012072302 | bbb | information | 0 | 20 | |

| | 2012072303 | ccc | computer | 1 | 21 | |

| | 2012072304 | ddd | computer | 0 | 20 | |

| | 2012072305 | eee | information | 0 | 22 | |

| | 2012072306 | fff | computer | 1 | 20 | |

| | 2012072307 | ggg | computer | 0 | 20 | |

| | 2012072308 | hhh | computer | 0 | 21 | |

| | 2012072309 | iii | automatic | 0 | 20 | |

| | 2012072310 | abc | computer | 1 | 20 | |

| | 2012072311 | kkk | computer | 0 | 18 |

| | 2012072312 | lll | computer | 0 | 20 | |

| | 2012072313 | mmm | computer | 0 | 20 | |

| | 2012072314 | nnn | computer | 1 | 20 | |

| | 2012072315 | ooo | information | 0 | 20 | |

| | 2012072316 | ppp | computer | 0 | 19 | |

| | 2012072317 | qqq | computer | 1 | 20 | |

| | 2012072318 | rrr | information | 0 | 20 | |

| | 2012072319 | sss | computer | 1 | 20 | |

| | 2012072320 | ttt | computer | 0 | 20 | |

| | 2012072321 | uuu | automatic | 0 | 23 | |

| | 2012072322 | vvv | computer | 0 | 20 | |

| | 2012072323 | www | computer | 1 | 20 | |

| | 2012072324 | xxx | computer | 0 | 25 | |

| | 2012072325 | yyy | automatic | 0 | 20 | |

| | 2012072326 | zzz | computer | 1 | 20 | |

+-+

The above is how to query the storage engine in Innodb. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.