小编给大家分享一下mysql中sending data状态包含了使用内部临时表的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

语句如下：

mysql>descselectid,count(*)fromt110groupbyid;+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+|1|SIMPLE|t110|NULL|ALL|NULL|NULL|NULL|NULL|99395|100.00|Usingtemporary;Usingfilesort|+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+1rowinset,1warning(0.00sec)

存入内部临时表的操作也在’sending data’ 下面，下面的debug trace可以看出
操作是获取innodb层一条数据handler::ha_rnd_next，然后在内部临时表中查询
这行记录hp_search，如果存在则做相应的更改heap_update（如count累加，sum加值等）

使用内部临时表1249T@3:||||||||>handler::ha_rnd_next1250T@3:|||||||||>rnd_next1251T@3:||||||||||>general_fetch1252T@3:|||||||||||>row_search_mvcc1253T@3:||||||||||||>row_sel_store_mysql_rec1254T@3:|||||||||||||>row_sel_store_mysql_field_func1255T@3:|||||||||||||<row_sel_store_mysql_field_func32671256T@3:||||||||||||<row_sel_store_mysql_rec34571257T@3:|||||||||||<row_search_mvcc64531258T@3:||||||||||<general_fetch99131259T@3:|||||||||<rnd_next100861260T@3:||||||||<handler::ha_rnd_next31591261T@3:||||||||>evaluate_join_record1262T@3:|||||||||enter:join:0x7ffe7c007778join_tabindex:0table:t112cond:0x01263T@3:|||||||||counts:evaluate_join_recordjoin->examined_rows++:41264T@3:|||||||||>sub_select_op1265T@3:||||||||||>end_update1266T@3:|||||||||||>handler::ha_index_read_map1267T@3:||||||||||||>heap_rkey1268T@3:|||||||||||||enter:info:0x7ffe7caa74d0inx:01269T@3:|||||||||||||>hp_search1270T@3:||||||||||||||exit:hash:0x1050505041271T@3:||||||||||||||exit:foundkeyat0x7ffe7ceb48801272T@3:|||||||||||||<hp_search1241273T@3:|||||||||||||>hp_extract_record1274T@3:|||||||||||||<hp_extract_record3691275T@3:||||||||||||<heap_rkey811276T@3:|||||||||||<handler::ha_index_read_map32611277T@3:|||||||||||>hanlder::ha_update_row1278T@3:||||||||||||>heap_update1279T@3:|||||||||||||>hp_copy_record_data_to_chunks1280T@3:|||||||||||||<hp_copy_record_data_to_chunks3081281T@3:||||||||||||<heap_update791282T@3:|||||||||||<hanlder::ha_update_row85181283T@3:||||||||||<end_update34821284T@3:|||||||||<sub_select_op10851285T@3:||||||||<evaluate_join_record1707

下面是count(*)累加关于heap_update old值和new值的查看

Breakpoint2,heap_update(info=0x7ffe7c9a9620,old_record=0x7ffe7c00f3b8<incompletesequence\375>,new_record=0x7ffe7c00f3a0<incompletesequence\375>)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/heap/hp_update.c:2424my_boolauto_key_changed=0;(gdb)x/16bx0x7ffe7c00f3b80x7ffe7c00f3b8:0xfd0x000x010x000x000x000x010x000x7ffe7c00f3c0:0x000x000x000x000x000x000x000x00(gdb)x/16bx0x7ffe7c00f3a00x7ffe7c00f3a0:0xfd0x000x010x000x000x000x020x000x7ffe7c00f3a8:0x000x000x000x000x000x000x000x00(gdb)cContinuing.Breakpoint2,heap_update(info=0x7ffe7c9a9620,old_record=0x7ffe7c00f3b8<incompletesequence\375>,new_record=0x7ffe7c00f3a0<incompletesequence\375>)at/root/mysqlall/percona-server-locks-detail-5.7.22/storage/heap/hp_update.c:2424my_boolauto_key_changed=0;(gdb)x/16bx0x7ffe7c00f3b80x7ffe7c00f3b8:0xfd0x000x010x000x000x000x020x000x7ffe7c00f3c0:0x000x000x000x000x000x000x000x00(gdb)x/16bx0x7ffe7c00f3a00x7ffe7c00f3a0:0xfd0x000x010x000x000x000x030x000x7ffe7c00f3a8:0x000x000x000x000x000x000x000x00

注意到key = 1 的这个值，第一次断点old值为0x01 new值为0x02，第二次断点old值为0x02 new值为0x03

然后遍历完所有的行过后，进入排序状态为Creating sort index

1526T@3:|||||||||THD::enter_stage:'Creatingsortindex'/root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:26041527T@3:|||||||||>PROFILING::status_change1528T@3:|||||||||<PROFILING::status_change3841529T@3:|||||||||>create_sort_index1530T@3:||||||||||>my_raw_malloc1531T@3:|||||||||||my:size:376my_flags:481532T@3:|||||||||||exit:ptr:0x7ffe7c9d1a901533T@3:||||||||||<my_raw_malloc2191534T@3:||||||||||>heap_info1535T@3:||||||||||<heap_info571536T@3:||||||||||>filesort1537T@3:|||||||||||>make_sortorder1538T@3:||||||||||||>alloc_root1539T@3:|||||||||||||enter:root:0x7ffe7c003c081540T@3:|||||||||||||exit:ptr:0x7ffe7caa4fc81541T@3:||||||||||||<alloc_root3041542T@3:|||||||||||<make_sortorder6631543T@3:|||||||||||opt:(null):startingstruct1544T@3:|||||||||||opt:filesort_information:startingstruct1545T@3:|||||||||||opt:(null):startingstruct1546T@3:|||||||||||opt:direction:"asc"1547T@3:|||||||||||opt:table:"intermediate_tmp_table"1548T@3:|||||||||||opt:field:"id"1549T@3:|||||||||||opt:(null):endingstruct1550T@3:|||||||||||opt:filesort_information:endingstruct1551T@3:|||||||||||info:sort_length:5

看完了这篇文章，相信你对“mysql中sending data状态包含了使用内部临时表的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！