MySQL表空间碎片整理

发布时间:2017-6-29 10:20:35编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"MySQL表空间碎片整理",主要涉及到MySQL表空间碎片整理方面的内容,对于MySQL表空间碎片整理感兴趣的同学可以参考一下。

MySQL可以使用alter table tn engine=innodb语句进行表空间碎片整理。而页内记录并不是物理有序的,并且删除记录后,被删除的记录放到页内free链表,可能会产生很多空洞。alter table命令会重建表,这样的话,猜测可能扫描老表页内说有数据,一个一个的插入到新表中。是不是这个过程呢?跟踪了下堆栈:

(gdb) bt
#0  btr_cur_optimistic_insert (flags=23, cursor=0xa34a4074, offsets=0xa34a40b4, heap=0xa34a40dc, entry=0xa6b72c8, 
    rec=0xa34a40b8, big_rec=0xa34a40bc, n_ext=0, thr=0x0, mtr=0xa34a3c04)
    at /home/mysql26/mysql-5.6.26/storage/innobase/btr/btr0cur.cc:1241
#1  0x087c006c in row_merge_insert_index_tuples (trx_id=9503, index=0xa6b7ca8, old_table=0xa4df530, fd=35, 
    block=0x9d232000 "\002") at /home/mysql26/mysql-5.6.26/storage/innobase/row/row0merge.cc:2519
#2  0x087c3c5e in row_merge_build_indexes (trx=0xa63a358, old_table=0xa4df530, new_table=0xa625f20, online=true, 
    indexes=0xa640d20, key_numbers=0xa640d28, n_indexes=1, table=0xa63fad8, add_cols=0x0, col_map=0xa640d70, 
    add_autoinc=4294967295, sequence=...) at /home/mysql26/mysql-5.6.26/storage/innobase/row/row0merge.cc:3743
#3  0x086f6304 in ha_innobase::inplace_alter_table (this=0xa6253b0, altered_table=0xa63fad8, 
    ha_alter_info=0xa34a4e7c) at /home/mysql26/mysql-5.6.26/storage/innobase/handler/handler0alter.cc:3950
#4  0x08447be2 in handler::ha_inplace_alter_table (this=0xa6253b0, altered_table=0xa63fad8, 
    ha_alter_info=0xa34a4e7c) at /home/mysql26/mysql-5.6.26/sql/handler.h:2870
#5  0x0844121e in mysql_inplace_alter_table (thd=0xa4e0fc0, table_list=0xa60ae48, table=0xa63e6d0, 
    altered_table=0xa63fad8, ha_alter_info=0xa34a4e7c, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, 
    target_mdl_request=0xa34a564c, alter_ctx=0xa34a43f4) at /home/mysql26/mysql-5.6.26/sql/sql_table.cc:6597
#6  0x08442d26 in mysql_alter_table (thd=0xa4e0fc0, new_db=0xa60b218 "yzs", new_name=0x0, create_info=0xa34a5ebc, 
    table_list=0xa60ae48, alter_info=0xa34a5f44, order_num=0, order=0x0, ignore=false)
    at /home/mysql26/mysql-5.6.26/sql/sql_table.cc:8448
#7  0x08594a97 in Sql_cmd_alter_table::execute (this=0xa60b228, thd=0xa4e0fc0)
    at /home/mysql26/mysql-5.6.26/sql/sql_alter.cc:313
#8  0x083da486 in mysql_execute_command (thd=0xa4e0fc0) at /home/mysql26/mysql-5.6.26/sql/sql_parse.cc:4976
#9  0x083dae5d in mysql_parse (thd=0xa4e0fc0, rawbuf=0xa60ad98 "alter table t1 engine=innodb", length=28, 
    parser_state=0xa34a70e0) at /home/mysql26/mysql-5.6.26/sql/sql_parse.cc:6386
#10 0x083dc834 in dispatch_command (command=COM_QUERY, thd=0xa4e0fc0, 
    packet=0xa602d69 "alter table t1 engine=innodb", packet_length=28)
    at /home/mysql26/mysql-5.6.26/sql/sql_parse.cc:1340
#11 0x083ddf9a in do_command (thd=0xa4e0fc0) at /home/mysql26/mysql-5.6.26/sql/sql_parse.cc:1037
#12 0x08391989 in do_handle_one_connection (thd_arg=0xa4e0fc0) at /home/mysql26/mysql-5.6.26/sql/sql_connect.cc:982
#13 0x08391a53 in handle_one_connection (arg=0xa4e0fc0) at /home/mysql26/mysql-5.6.26/sql/sql_connect.cc:898
#14 0x089b3141 in pfs_spawn_thread (arg=0xa462868) at /home/mysql26/mysql-5.6.26/storage/perfschema/pfs.cc:1860
---Type <return> to continue, or q <return> to quit---
#15 0x00cdc832 in start_thread () from /lib/libpthread.so.0
#16 0x00c31e0e in clone () from /lib/libc.so.6

mysql_execute_command->

Sql_cmd_alter_table::execute ->mysql_alter_table->mysql_inplace_alter_table ->

handler::ha_inplace_alter_table ->ha_innobase::inplace_alter_table->

row_merge_build_indexes ->row_merge_insert_index_tuples ->btr_cur_optimistic_insert

关键函数在红色函数。下节详细分析。




上一篇:基于video texture(camera流,视频流) 创建一个纹理
下一篇:当我们谈论计算机科学

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款