溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下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: | | | | | | | | | | | | | <row_sel_store_mysql_field_func 3267 1256 T@3: | | | | | | | | | | | | <row_sel_store_mysql_rec 3457 1257 T@3: | | | | | | | | | | | <row_search_mvcc 6453 1258 T@3: | | | | | | | | | | <general_fetch 9913 1259 T@3: | | | | | | | | | <rnd_next 10086 1260 T@3: | | | | | | | | <handler::ha_rnd_next 3159 1261 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_search 124 1273 T@3: | | | | | | | | | | | | | >hp_extract_record 1274 T@3: | | | | | | | | | | | | | <hp_extract_record 369 1275 T@3: | | | | | | | | | | | | <heap_rkey 81 1276 T@3: | | | | | | | | | | | <handler::ha_index_read_map 3261 1277 T@3: | | | | | | | | | | | >hanlder::ha_update_row 1278 T@3: | | | | | | | | | | | | >heap_update 1279 T@3: | | | | | | | | | | | | | >hp_copy_record_data_to_chunks 1280 T@3: | | | | | | | | | | | | | <hp_copy_record_data_to_chunks 308 1281 T@3: | | | | | | | | | | | | <heap_update 79 1282 T@3: | | | | | | | | | | | <hanlder::ha_update_row 8518 1283 T@3: | | | | | | | | | | <end_update 3482 1284 T@3: | | | | | | | | | <sub_select_op 1085 1285 T@3: | | | | | | | | <evaluate_join_record 1707
下面是count(*)累加關于heap_update old值和new值的查看
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狀態包含了使用內部臨時表的示例分析”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
