MySQL 存储过程运行的内存管理

set global log_bin_trust_function_creators=1;
CREATE FUNCTION f1 (a VARCHAR(32)) RETURN VARCHAR(32) no sql
is result VARCHAR(32);
BEGIN
  result := CONCAT(a,'y');
  RETURN(result);
END;
1.
2.
3.
4.
5.
6.
7.

mysql> show function code f1;
+-----+------------------------------+
| Pos | Instruction                  |
+-----+------------------------------+
|   0 | set result@1 NULL            |
|   1 | set result@1 concat(a@0,'y') |
|   2 | freturn 15 result@1          |
+-----+------------------------------+
3 rows in set (0.01 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.

mysql> select f1('mysql');
1.

#0  sp_head::create_result_field (this=0x7fff400fd258, thd=0x7fffe83a9ca0, field_max_length=93825047739703, 
    field_name_or_null=0x7fffe83a9c90 "\300\234:\350\377\177", table=0x7fff400fd258)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/sp_head.cc:1972
#1  0x0000555558b52165 in Item_func_sp::init_result_field (this=0x7fff400fbe50, thd=0x7fff40001060)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/item_func.cc:8768
#2  0x0000555558b53209 in Item_func_sp::fix_fields (this=0x7fff400fbe50, thd=0x7fff40001060, ref=0x7fff400fc2d0)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/item_func.cc:9021
#3  0x0000555558e37345 in setup_fields (thd=0x7fff40001060, want_privilege=1, allow_sum_func=true, split_sum_funcs=true, 
    column_update=false, typed_items=0x0, fields=0x7fff400fadd0, ref_item_array=..., is_ora_update_set=false)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/sql_base.cc:9244
#4  0x0000555558f9d023 in Query_block::prepare (this=0x7fff400fadc8, thd=0x7fff40001060, insert_field_list=0x0)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/sql_resolver.cc:275
#5  0x0000555558fcba8b in Sql_cmd_select::prepare_inner (this=0x7fff400fc4d0, thd=0x7fff40001060)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/sql_select.cc:470
#6  0x0000555558fcb52c in Sql_cmd_dml::prepare (this=0x7fff400fc4d0, thd=0x7fff40001060)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/sql_select.cc:392
#7  0x0000555558fcbd43 in Sql_cmd_dml::execute (this=0x7fff400fc4d0, thd=0x7fff40001060)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/sql_select.cc:525
#8  0x0000555558f42e77 in mysql_execute_command (thd=0x7fff40001060, first_level=true)
    at /home/wuyy/greatdb/gitmerge/percona-server/sql/sql_parse.cc:4784

在sp_head::create_result_field看到该result field是建立在thd->mem_root的，也就是一开始thd的内存里面。
table->record[0] = thd->mem_root->ArrayAlloc<uchar>(m_return_field_def.pack_length() + 1);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

1、在sp_head::execute_function有如下代码用来创建运行内存：
  thd->swap_query_arena(call_arena, &backup_arena); 建立新的内存块call_arena用来存放funciton运行产生的数据。
  sp_rcontext *func_runtime_ctx =
      sp_rcontext::create(thd, m_root_parsing_ctx, return_value_fld); sp_rcontext::create运行的内存在call_arena
  thd->swap_query_arena(backup_arena, &call_arena); 内存切换回一开始thd的内存。
2、接着是每个instr步骤的内存管理：
thd->swap_query_arena(call_arena, &backup_arena); 内存切换到call_arena
err_status = execute(thd, true);
thd->swap_query_arena(backup_arena, &call_arena); 内存切换回一开始thd的内存。
3、以上第2步的execute(thd, true)内存管理
thd->swap_query_arena(execute_arena, &backup_arena);建立新的内存块execute_arena用来存放funciton运行每一个步骤产生的数据
do{
    i = get_instr(ip);
    err_status = i->execute(thd, &ip);
    free_root(&execute_mem_root, MYF(0));
}while        每个步骤的内存块都在execute_arena上，每个sp_instr都是单独管理内存，该sp_instr执行完毕立即释放内存。因此这个内存块是临时的，所有希望    永久存放的数据都不应该存放在这个内存上。
thd->swap_query_arena(backup_arena, &execute_arena); 内存切换回call_arena
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

sp_head::execute_function代码结束时候释放call_arena：
err_with_cleanup:
  ::destroy(func_runtime_ctx);
  call_arena.free_items();
  free_root(&call_mem_root, MYF(0)); 这里释放call_arena内存块
1.
2.
3.
4.
5.

+------------------------------------------+
| thd(return result)                       |
+           -------------------------------+
|           |call_arena(create)            |
+           |    --------------------------+
|           |    |execute_arena(sp_instr)  |
|           |    |                         |
+------------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.