Memory表,删除数据时的处理

一天同事问我,一处英文手册上的一段话的意思

Space for MEMORY tables is allocated in small blocks. Tables use 100% dynamic hashing for inserts. No overflow area or extra key space is needed. No extra space is needed for free lists. Deleted rows are put in a linked list and are reused when you insert new data into the table. MEMORY tables also have none of the problems commonly associated with deletes plus inserts in hashed tables.

说实话英文比较菜,不过知道大概意思,就是memory表删除数据的时候并不回收内存空间,而是在一个linked list记录那些行被删除,然后新插入的数据,不是添加到末尾,而是插入到那些"空洞"中.为了说服同事,于是我做了如下实验:

同时建立2张表,一张myisam的,一张memory的,结构一致,只是engine不同

CREATE TABLE `mem` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY ;

CREATE TABLE `myi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM;

root@localhost[test] 01:34>call insert_table(10000,'mem');
Query OK, 0 rows affected (2.11 sec)

root@localhost[test] 01:35>call insert_table(10000,'myi');
Query OK, 0 rows affected (1.84 sec)

并插入10000条数据,同时查看表状态

root@localhost[test] 01:35>show table status like "mem"/G
*************************** 1. row ***************************
           Name: mem
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 10001
Avg_row_length: 261
   Data_length: 2693200
Max_data_length: 16098480
   Index_length: 99200
      Data_free: 0
Auto_increment: 10002
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

root@localhost[test] 01:38>show table status like "myi"/G
*************************** 1. row ***************************
           Name: myi
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 10001
Avg_row_length: 128
   Data_length: 1280220
Max_data_length: 281474976710655
   Index_length: 105472
      Data_free: 0
Auto_increment: 10002
    Create_time: 2010-06-06 00:46:08
    Update_time: 2010-06-06 01:35:20
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

其实也可以看出,memory比myisam浪费空间,呵呵,这不是这里重点,现在各自删除1000条记录在查看状态

root@localhost[test] 01:38>delete from mem where id between 1000 and 2000;
Query OK, 1001 rows affected (0.00 sec)

root@localhost[test] 01:42>delete from myi where id between 1000 and 2000;
Query OK, 1001 rows affected (0.11 sec)

root@localhost[test] 01:42>show table status like "mem"/G
*************************** 1. row ***************************
           Name: mem
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 9000
Avg_row_length: 261
   Data_length: 2693200
Max_data_length: 16098480
   Index_length: 99200
      Data_free: 261261
Auto_increment: 10002
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

root@localhost[test] 01:42>show table status like "myi"/G
*************************** 1. row ***************************
           Name: myi
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 9000
Avg_row_length: 127
    Data_length: 1280220
Max_data_length: 281474976710655
   Index_length: 105472
      Data_free: 129632
Auto_increment: 10002
    Create_time: 2010-06-06 00:46:08
    Update_time: 2010-06-06 01:42:14
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

除了行数,数据文件大小没有变化,再插入1000条数据,观察状态

root@localhost[test] 01:42>call insert_table(1000,'mem');
Query OK, 0 rows affected (0.30 sec)

root@localhost[test] 01:45>call insert_table(1000,'myi');
Query OK, 0 rows affected (0.45 sec)

root@localhost[test] 01:45>show table status like "mem"/G
*************************** 1. row ***************************
           Name: mem
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 10001
Avg_row_length: 261
    Data_length: 2693200
Max_data_length: 16098480
   Index_length: 99200
      Data_free: 0
Auto_increment: 11003
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

root@localhost[test] 01:45>show table status like "myi"/G
*************************** 1. row ***************************
           Name: myi
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 10001
Avg_row_length: 130
   Data_length: 1302056
Max_data_length: 281474976710655
   Index_length: 105472
      Data_free: 0
Auto_increment: 11003
    Create_time: 2010-06-06 00:46:08
    Update_time: 2010-06-06 01:45:30
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

可以看出myisam表的数据文件变大了,而memory没有,则说明新的数据真的插到那些"空洞"中了.其实想想也不奇怪,因为,如果memory没有这样的机制,在添加,删除频繁的应用场景下是非常浪费内存的.写这个并不是要说明我发现了这么个东西,只是想告诉大家一个方法,这些东西其实非常简单的.