辛星淺談mysql中的數據碎片以及引擎為MyISAM下的操作

辛星淺談mysql中的數據碎片以及引擎為MyISAM下的操作

對於mysql中的數據碎片，其實和我們刪除數據是息息相關的，刪除數據的時候必然會在數據文件中造成不連續的空白空間，對於少量的數據的刪除，並不會產生多少的空白空間。如果在一段時間內的大量的刪除操作，會使得這種留空的空間變得比存儲列表內容所使用的空間更大。可能有人會說，我們向數據庫中插入數據的時候，會不會在這些空白空間中插入數據呢？答案是會的。但是，它會造成一個後果，那就是數據的存儲位置不連續，以及物理存儲順序與理論上的存儲順序不相同，這就比較麻煩了。

按照數據碎片的大小來分，可以分為單行數據碎片和多行數據碎片。其實不僅僅會產生數據碎片，如果加了索引，還會產生索引碎片，這樣會造成順序的紊亂。由於MySQL的引擎的內部實現機制不同，在數據碎片的處理上也會不同。

對於MyISAM來說，因為它的索引和數據以及表結構分為三個文件來存儲，因此optimize可以整理數據文件，並且重新排序，這樣因為數據碎片產生的性能問題會減少很多，直接使用【optimize table 表名】即可，但是此時也應該注意一些問題，由於該操作會鎖住表，所以我們盡量定期整理一下碎片，在訪問量小的時候來做這件事。我們可以查看information_schema數據庫下的tables中的free_data字段即可，如果該字段不為0，則產生了數據碎片，看下面操作：

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema;
Database changed
mysql> select data_free from tables;
+-----------+
| data_free |
+-----------+
|         0 |
|         0 |
...中間省略部分數據
|         0 |
|        72 |
|         0 |
|         0 |
+-----------+
162 rows in set (0.06 sec)

mysql>

這裡我們是查看所有的表中的數據碎片，如果我們想單獨看某一個表的數據碎片，看下面操作：

第一步，我們先建立一個庫和一個表，並向其中插入四條數據：

mysql> create database xinxing;
Query OK, 1 row affected (0.16 sec)

mysql> use xinxing;
Database changed

mysql>
mysql> create table xin (c char(40)) engine = myisam;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> insert into xin values('xiaohei'),('xiaoqian'),
    -> ('xiaolin'),('xiaonan');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

第二步，我們查看這個表的信息：

mysql> show table status from xinxing \G
*************************** 1. row ***************************
           Name: xin
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 4
 Avg_row_length: 121
    Data_length: 484
Max_data_length: 34058472181989375
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-08-25 13:19:02
    Update_time: 2014-08-25 13:19:35
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

我們發現這裡面並沒有數據碎片，都是非常嚴密的。

第三步，我們刪除一條數據：

mysql> delete from xin where c = 'xiaolin';
Query OK, 1 row affected (0.00 sec)

第四步，我們再次查看，發現數據碎片產生了:

mysql> show table status from xinxing \G
*************************** 1. row ***************************
           Name: xin
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 3
 Avg_row_length: 121
    Data_length: 484
Max_data_length: 34058472181989375
   Index_length: 1024
      Data_free: 121
 Auto_increment: NULL
    Create_time: 2014-08-25 13:19:02
    Update_time: 2014-08-25 13:20:15
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

我們發現上面的data_free字段為121，也就是產生了121字節的空白空間。

大家注意，我們這裡只是刪除了一條數據，如果刪除n條數據，那這些碎片就很影響性能了，至於它的解決辦法，我們上面也提到了，這裡就不再提了。我是辛星，期待您的關注。