Memory表,刪除數(shù)據(jù)時的處理

xiao7ng

一天同事問我,一處英文手冊上的一段話的意思

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表刪除數(shù)據(jù)的時候并不回收內(nèi)存空間,而是在一個linked list記錄那些行被刪除,然后新插入的數(shù)據(jù),不是添加到末尾,而是插入到那些"空洞"中.為了說服同事,于是我做了如下實驗:

同時建立2張表,一張myisam的,一張memory的,結(jié)構(gòu)一致,只是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條數(shù)據(jù),同時查看表狀態(tài)

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浪費空間,呵呵,這不是這里重點,現(xiàn)在各自刪除1000條記錄在查看狀態(tài)

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)

除了行數(shù),數(shù)據(jù)文件大小沒有變化,再插入1000條數(shù)據(jù),觀察狀態(tài)

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表的數(shù)據(jù)文件變大了,而memory沒有,則說明新的數(shù)據(jù)真的插到那些"空洞"中了.其實想想也不奇怪,因為,如果memory沒有這樣的機制,在添加,刪除頻繁的應(yīng)用場景下是非常浪費內(nèi)存的.寫這個并不是要說明我發(fā)現(xiàn)了這么個東西,只是想告訴大家一個方法,這些東西其實非常簡單的

cenalulu

頂技術(shù)貼~ MyISAM我比較疑惑，記得也會自動填充的~難道是只有fixed -row -format 才會？
我去查查文檔。。。

xiao7ng

回復 2# cenalulu


    確實是這樣子的,Row_format=Fixed的時候,是插到"洞"里的,呵呵

ruochen

不錯

用示例比較有說服力

phphp

內(nèi)存表數(shù)據(jù)存儲是一棵自增長的樹，當沒有空間插入記錄時，會一次性分配從根節(jié)到葉子節(jié)點路徑上的所有內(nèi)存，所以不能按記錄回收，圖中顏色相同的代表是同時分配的內(nèi)存，其中葉子節(jié)點存儲記錄。
于被刪除的記錄，會記錄在del_link中，在插入新記錄時復用，當del_link為空同時樹中沒有可用的空間時，樹就會膨脹。

對于峰值記錄數(shù)遠大于平均記錄數(shù)的應(yīng)用，可定期重整回收內(nèi)存。

yueliangdao0608

實踐出真理。