mysql中sending data状态包括了使用内部临时表的示例分析
发布时间：2021-12-27 06:17:23 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下mysql中sending data状态包含了使用内部临时表的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ 语句如下： mysql desc select id,count(*) from t110 group by id; +----+-------------+-------+------------
|
小编给大家分享一下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加值等） Breakpoint 2, heap_update (info=0x7ffe7c9a9620, old_record=0x7ffe7c00f3b8 <incomplete sequence 375>, new_record=0x7ffe7c00f3a0 <incomplete sequence 375>) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/heap/hp_update.c:24 24 my_bool auto_key_changed= 0; (gdb) x/16bx 0x7ffe7c00f3b8 0x7ffe7c00f3b8: 0xfd 0x00 0x01 0x00 0x00 0x00 0x01 0x00 0x7ffe7c00f3c0: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 (gdb) x/16bx 0x7ffe7c00f3a0 0x7ffe7c00f3a0: 0xfd 0x00 0x01 0x00 0x00 0x00 0x02 0x00 0x7ffe7c00f3a8: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 (gdb) c Continuing. Breakpoint 2, heap_update (info=0x7ffe7c9a9620, old_record=0x7ffe7c00f3b8 <incomplete sequence 375>, new_record=0x7ffe7c00f3a0 <incomplete sequence 375>) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/heap/hp_update.c:24 24 my_bool auto_key_changed= 0; (gdb) x/16bx 0x7ffe7c00f3b8 0x7ffe7c00f3b8: 0xfd 0x00 0x01 0x00 0x00 0x00 0x02 0x00 0x7ffe7c00f3c0: 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x00 (gdb) x/16bx 0x7ffe7c00f3a0 0x7ffe7c00f3a0: 0xfd 0x00 0x01 0x00 0x00 0x00 0x03 0x00 0x7ffe7c00f3a8: 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: | | | | | | | | | <PROFILING::status_change 384 1529 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: | | | | | | | | | | <my_raw_malloc 219 1534 T@3: | | | | | | | | | | >heap_info 1535 T@3: | | | | | | | | | | <heap_info 57 1536 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: | | | | | | | | | | | | <alloc_root 304 1542 T@3: | | | | | | | | | | | <make_sortorder 663 1543 T@3: | | | | | | | | | | | opt: (null): starting struct 1544 T@3: | | | | | | | | | | | opt: filesort_information: starting struct 1545 T@3: | | | | | | | | | | | opt: (null): starting struct 1546 T@3: | | | | | | | | | | | opt: direction: "asc" 1547 T@3: | | | | | | | | | | | opt: table: "intermediate_tmp_table" 1548 T@3: | | | | | | | | | | | opt: field: "id" 1549 T@3: | | | | | | | | | | | opt: (null): ending struct 1550 T@3: | | | | | | | | | | | opt: filesort_information: ending struct 1551 T@3: | | | | | | | | | | | info: sort_length: 5 看完了这篇文章，相信你对“mysql中sending data状态包含了使用内部临时表的示例分析”有了一定的了解。 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
