mysql中sending data状态包含了使用内部临时表的示例分析

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

语句如下：

mysql> desc select id,count(*) from t110 group by  id;+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+| 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 | Using temporary; Using filesort |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+1 row in set, 1 warning (0.00 sec)

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

使用内部临时表   1249 T@3: | | | | | | | | >handler::ha_rnd_next   1250 T@3: | | | | | | | | | >rnd_next   1251 T@3: | | | | | | | | | | >general_fetch   1252 T@3: | | | | | | | | | | | >row_search_mvcc   1253 T@3: | | | | | | | | | | | | >row_sel_store_mysql_rec   1254 T@3: | | | | | | | | | | | | | >row_sel_store_mysql_field_func   1255 T@3: | | | | | | | | | | | | | evaluate_join_record   1262 T@3: | | | | | | | | | enter: join: 0x7ffe7c007778 join_tab index: 0 table: t112 cond: 0x0   1263 T@3: | | | | | | | | | counts: evaluate_join_record join->examined_rows++: 4   1264 T@3: | | | | | | | | | >sub_select_op   1265 T@3: | | | | | | | | | | >end_update   1266 T@3: | | | | | | | | | | | >handler::ha_index_read_map   1267 T@3: | | | | | | | | | | | | >heap_rkey   1268 T@3: | | | | | | | | | | | | | enter: info: 0x7ffe7caa74d0  inx: 0   1269 T@3: | | | | | | | | | | | | | >hp_search   1270 T@3: | | | | | | | | | | | | | | exit: hash: 0x105050504   1271 T@3: | | | | | | | | | | | | | | exit: found key at 0x7ffe7ceb4880   1272 T@3: | | | | | | | | | | | | | hp_extract_record   1274 T@3: | | | | | | | | | | | | | hanlder::ha_update_row   1278 T@3: | | | | | | | | | | | | >heap_update   1279 T@3: | | | | | | | | | | | | | >hp_copy_record_data_to_chunks   1280 T@3: | | | | | | | | | | | | | 
下面是count(*)累加关于heap_update old值和new值的查看
Breakpoint 2, heap_update (info=0x7ffe7c9a9620, old_record=0x7ffe7c00f3b8  , new_record=0x7ffe7c00f3a0  )    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/heap/hp_update.c:2424        my_bool auto_key_changed= 0;(gdb) x/16bx 0x7ffe7c00f3b80x7ffe7c00f3b8: 0xfd    0x00    0x01    0x00    0x00    0x00    0x01    0x000x7ffe7c00f3c0: 0x00    0x00    0x00    0x00    0x00    0x00    0x00    0x00(gdb) x/16bx 0x7ffe7c00f3a00x7ffe7c00f3a0: 0xfd    0x00    0x01    0x00    0x00    0x00    0x02    0x000x7ffe7c00f3a8: 0x00    0x00    0x00    0x00    0x00    0x00    0x00    0x00(gdb) cContinuing.Breakpoint 2, heap_update (info=0x7ffe7c9a9620, old_record=0x7ffe7c00f3b8  , new_record=0x7ffe7c00f3a0  )    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/heap/hp_update.c:2424        my_bool auto_key_changed= 0;(gdb) x/16bx 0x7ffe7c00f3b80x7ffe7c00f3b8: 0xfd    0x00    0x01    0x00    0x00    0x00    0x02    0x000x7ffe7c00f3c0: 0x00    0x00    0x00    0x00    0x00    0x00    0x00    0x00(gdb) x/16bx 0x7ffe7c00f3a00x7ffe7c00f3a0: 0xfd    0x00    0x01    0x00    0x00    0x00    0x03    0x000x7ffe7c00f3a8: 0x00    0x00    0x00    0x00    0x00    0x00    0x00    0x00
注意到key = 1 的这个值，第一次断点old值为0x01 new值为0x02，第二次断点old值为0x02 new值为0x03
然后遍历完所有的行过后，进入排序状态为Creating sort index
   1526 T@3: | | | | | | | | | THD::enter_stage: 'Creating sort index' /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2604   1527 T@3: | | | | | | | | | >PROFILING::status_change   1528 T@3: | | | | | | | | | create_sort_index   1530 T@3: | | | | | | | | | | >my_raw_malloc   1531 T@3: | | | | | | | | | | | my: size: 376  my_flags: 48   1532 T@3: | | | | | | | | | | | exit: ptr: 0x7ffe7c9d1a90   1533 T@3: | | | | | | | | | | heap_info   1535 T@3: | | | | | | | | | | filesort   1537 T@3: | | | | | | | | | | | >make_sortorder   1538 T@3: | | | | | | | | | | | | >alloc_root   1539 T@3: | | | | | | | | | | | | | enter: root: 0x7ffe7c003c08   1540 T@3: | | | | | | | | | | | | | exit: ptr: 0x7ffe7caa4fc8   1541 T@3: | | | | | | | | | | | | 
看完了这篇文章，相信你对"mysql中sending data状态包含了使用内部临时表的示例分析"有了一定的了解，如果想了解更多相关知识，欢迎关注行业资讯频道，感谢各位的阅读！