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

This article mainly introduces "what does the state of sending data in MySQL contain". In daily operation, I believe that many people have doubts about what the state of sending data in MySQL contains. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the question of "what does the state of sending data in MySQL contain?" Next, please follow the editor to study!

I. the origin of the problem

The original question is as follows:

Does the time when the database sends data to the client count as the execution time of sql?

To solve the problem, we need to know when MySQL transfers the data to the client, since it is to transfer the actual data to the client, then it must be a select statement, and we need to understand what stages a normal select needs to go through.

II. A simple SELECT statement goes through the stage 2392 Test10: | THD::enter_stage: 'checking permissions' / root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authorization.cc:843 2404 Thum10: | | THD::enter_stage:' Opening tables' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc : 5719 2512 titled 10: | THD::enter_stage: 'init' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:121 2681 titled 10: | THD::enter_stage:' System lock' / root/mysql5.7.14/percona-server-5.7.14-7/sql/lock.cc:321 2772 titled 10: | THD :: enter_stage: 'optimizing' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:151 2865 preparing' 10: | THD::enter_stage:' statistics' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:386 3329 Toner 10: | THD::enter_stage: 'preparing' / Root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:494 3466 titled 10: | | THD::enter_stage: 'executing' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:119 3474 titled 10: | | THD::enter_stage:' Sending data' / root/mysql5.7.14/percona-server-5 .7.14-7/sql/sql_executor.cc:195 3668 root/mysql5.7.14/percona-server-5.7.14 10: | THD::enter_stage: 'end' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:199 3685 Toner 10: | THD::enter_stage:' query end' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5174 3754 freeing items' 10: | THD::enter_stage: 'closing tables' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5252 3882 Toner 10: | THD::enter_stage:' freeing items' / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5855

In fact, the whole phase is counted in the actual elapsed time of the statement, but the slow query records:

Actual execution time = (time at the end of freeing items) actual elapsed time-(time at the end of System lock) such as MDL LOCK wait time-(time for Innodb layer locking) Row lock wait time

For more information about slow queries, please refer to one of my articles, which will not be repeated here.

Https://www.jianshu.com/p/1ffadf29d6c0 MySQL slow query recording principle and content parsing 3. What is Sending data status

In fact, this state is only available in the select statement, and if it is DML, it is different, but all have the same stages as follows:

Select:Sending data

Insert statement: Update

Delete/update:Updating

This stage is huge, and it at least contains:

The location of Innodb layer data is returned to MySQL layer.

The query of the Innodb layer data is returned to the MySQL layer

Modification of Innodb layer data (if MDL)

Innodb layer locking and waiting

Wait to enter the Innodb layer (innodb_thread_concurrency parameter)

The MySQL layer sends data to the client

You can see that almost all the processes of dealing with the Innodb layer are wrapped in this state. Of course, I just listed some of what I thought of, but there may be many more. Here I also give the answer that the MySQL layer sends data to the client, which is included in the Sending data. Let's analyze it below.

4. How to prove that the state of Sending data contains the time of network data transmission

You can refer to my article before.

Https://www.jianshu.com/p/25fed8f1f05e MySQL:Innodb Handler_read_* variable interpretation

We create a simple table as follows, and populate the data as follows:

Create Table: CREATE TABLE `ty` (`id` int (11) NOT NULL AUTO_INCREMENT, `a` int (11) DEFAULT NULL, `b` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idxa` (`a`) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4mysql > select * from ty +-+ | id | a | b | +-- + | 8 | 2 | 3 | 9 | 5 | 4 | 10 | 6 | 7 | 11 | 6 | 8 | +-+ 4 rows in set (4.14sec)

We execute the following statement:

Mysql > desc select * from ty where a = 6 and bread8 +- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -- + | 1 | SIMPLE | ty | NULL | ref | idxa | idxa | 5 | const | 2 | 33.33 | Using where | + -+-+ mysql > select * from ty where a = 6 and bread8 +-+ | id | a | b | +-+ | 11 | 6 | 8 | +-+ 1 row in set (7.22 sec)

Note: the statement here takes a long time to execute because I hit the GDB breakpoint, so it looks like a long time.

I did the trace of the statement, which probably requires the following steps:

First of all, Innodb locates to the location of index idxa data 6. This is the first location to call the function ha_innobase::index_read, and the returned data is | 10 | 6 | 7 | to the MySQL layer, but the MySQL layer filters out the condition a = 6 and bounded 8 does not need to be returned to the client.

1547 tweets 12: | | > handler::ha_index_read_map 1548 tweets 12: | > index_read (here for the first visit index positioning) 1552 tweets 12: | > row_search_mvcc 1553 tweets 12: | > btr_cur_search_to_nth_level 1554 tweets 12: | | | | send_result_set_row 1616 titled 12: | |

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.