Mysql之MyISAM儲存引擎

標籤：mysql myisam

    MyISAM是Mysql的預設儲存引擎，它在磁碟上儲存為三個檔案， .MYD是資料檔案  .MYI是索引檔案  .frm是儲存表定義。我們可以考慮把資料檔案和索引檔案分別存到不同的磁碟，實現IO平均分布。 

    如果對事務完整性沒有要求，或者以select 、insert 為主的操作，可以選擇該儲存引擎。
     1、MyISAM只會對索引進行緩衝，而資料檔案則是使用作業系統緩衝，當索引資料大於key buffer的時候，也會使用作業系統緩衝。

     2、使用count(*)的時候很快，因為行數是單獨儲存的。

     3、不支援事務，不支援外鍵，訪問快。

     4、使用表級鎖定。

     5、每張表一個資料檔案，備份的時候可以直接複製，恢複的時候也可以直接覆蓋，操作方便。

     6、可以使用myisamchk進行故障恢複。

    MyISAM表的儲存格式：靜態表（固定長度）【預設格式】、動態表、壓縮表

    1、固定格式的特點：快速、容易緩衝、崩潰後容易恢複、比動態表佔用更大空間。但是靜態表裡面的欄位是固定長度的，如果儲存的資料長度不夠，那麼其他位就是用空格填充，在使用的時候，會由系統去掉空格。所以這個地方引入一個問題，如果我們的資料裡面尾部本來就含有空格，那麼在使用資料的時候，空格會被去掉， 關於這一點我們需要非常小心。

    2、動態表格式的特點：除了長度小於4的列，其他字元型的列長度是可變的；比靜態固定格式使用較少空間；如果一行變得很大的時候，會進行分區，所以會造成片段化，可以使用optimize table或者myisamchk -r來改善效能，使用myisamchk -ei擷取表的統計資訊；如果崩潰了，比靜態格式表的恢複困難。

    效果--------

root->/tmp# myisamchk -r /var/lib/mysql/test/test_table.MYI
- recovering (with keycache) MyISAM-table ‘/var/lib/mysql/test/test_table.MYI‘
Data records: 23068672

root->/tmp# myisamchk -ei /var/lib/mysql/test/test_table.MYI
Checking MyISAM file: /var/lib/mysql/test/test_table.MYI
Data records: 23068672   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check records and index references
Records:          23068672    M.recordlength:        7   Packed:           -40%
Recordspace used:      100%   Empty space:           0%  Blocks/Record:   1.00
Record blocks:    23068672    Delete blocks:         0
Record data:     161480704    Deleted data:          0
Lost space:              0    Linkdata:              0

User time 1.25, System time 0.12
Maximum resident set size 1684, Integral resident set size 0
Non-physical pagefaults 516, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 25, Involuntary context switches 2746
root->/tmp#

    3、壓縮表的特點：佔用非常少的磁碟空間；

    root->/var/lib/mysql/test# ls -lh
total 155M
-rw-rw---- 1 mysql mysql 8.5K Sep 29 14:05 err_table.frm
-rw-rw---- 1 mysql mysql  612 Sep 29 14:05 err_table.MYD
-rw-rw---- 1 mysql mysql 1.0K Nov 26 16:16 err_table.MYI
-rw-rw---- 1 mysql mysql 8.4K Jan 19 23:34 test_table.frm
-rw-rw---- 1 mysql mysql 154M Jan 22 11:19 test_table.MYD
-rw-rw---- 1 mysql mysql 1.0K Jan 22 11:19 test_table.MYI
root->/var/lib/mysql/test# myisampack test_table.MYI
Compressing test_table.MYD: (23068672 records)
- Calculating statistics
- Compressing file
71.43%    
root->/var/lib/mysql/test# ls -lh
total 45M
-rw-rw---- 1 mysql mysql 8.5K Sep 29 14:05 err_table.frm
-rw-rw---- 1 mysql mysql  612 Sep 29 14:05 err_table.MYD
-rw-rw---- 1 mysql mysql 1.0K Nov 26 16:16 err_table.MYI
-rw-rw---- 1 mysql mysql 8.4K Jan 19 23:34 test_table.frm
-rw-rw---- 1 mysql mysql  45M Jan 22 11:19 test_table.MYD
-rw-rw---- 1 mysql mysql 1.0K Jan 22 11:24 test_table.MYI    

      

測試MyISAM的事務性  

----------------------

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into err_table(errsql) values(‘ppyy‘);
Query OK, 1 row affected (0.03 sec)

mysql> select * from err_table;
+---------+--------+--------+
| errcode | errsql | retime |
+---------+--------+--------+
| NULL    | ppyy   | NULL   |
+---------+--------+--------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from err_table;
+---------+--------+--------+
| errcode | errsql | retime |
+---------+--------+--------+
| NULL    | ppyy   | NULL   |
+---------+--------+--------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from err_table;
+---------+--------+--------+
| errcode | errsql | retime |
+---------+--------+--------+
| NULL    | ppyy   | NULL   |
+---------+--------+--------+
1 row in set (0.00 sec)

mysql> alter table err_table engine=innodb;
Query OK, 1 row affected (0.19 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> start transaction;                          
Query OK, 0 rows affected (0.00 sec)

mysql> insert into err_table(errsql) values(‘zzqqq‘);
Query OK, 1 row affected (0.00 sec)

mysql> select * from err_table;                     
+---------+--------+--------+
| errcode | errsql | retime |
+---------+--------+--------+
| NULL    | ppyy   | NULL   |
| NULL    | zzqqq  | NULL   |
+---------+--------+--------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from err_table;
+---------+--------+--------+
| errcode | errsql | retime |
+---------+--------+--------+
| NULL    | ppyy   | NULL   |
+---------+--------+--------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from err_table;
+---------+--------+--------+
| errcode | errsql | retime |
+---------+--------+--------+
| NULL    | ppyy   | NULL   |
+---------+--------+--------+
1 row in set (0.00 sec)

----------------------

查看錶狀體

----------------------

mysql> show table status like ‘test_table‘\G;
*************************** 1. row ***************************
           Name: test_table
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 46137344
 Avg_row_length: 7
    Data_length: 322961408
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-01-19 23:34:54
    Update_time: 2015-01-22 11:19:17
     Check_time: 2015-01-22 11:19:17
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

----------------------

        MyISAM表常見損壞情況：

        1、在寫過程中Mysql的進程被殺掉；

        2、主機宕機（例如硬體故障）

              

本文出自 “影魔登場” 部落格，請務必保留此出處http://woodywoodpecker.blog.51cto.com/4820467/1606984

Mysql之MyISAM儲存引擎