MySQL學習筆記(二)：MyISAM 存儲引擎

MyISAM 存儲引擎

MyISAM 基于舊的 (不再可用) ISAM 存儲引擎, 但有許多有用的擴展。

每個 MyISAM 表都存儲在三個文件中的磁盤上。這些文件具有以表名開頭的名稱, 并具有用于指示文件類型的擴展名。

.frm文件存儲表定義。

數據文件的擴展名為.MYD (MYData)。

索引文件的擴展名是.MYI (MYIndex)

要明確表示要用一個MyISAM表格，請用ENGINE表選項指出來：

CREATE TABLE t (i INT) ENGINE = MYISAM;

在 MySQL 5.7 中, 通常需要使用 ENGINE 來指定 MyISAM 存儲引擎, 因為 InnoDB 是默認引擎。

可以使用 mysqlcheck 客戶端或 myisamchk 實用程序檢查或修復 MyISAM 表。還可以使用 myisampack 壓縮 MyISAM 表, 以減少更少的空間。

如下是MyISAM存儲引擎的一些特征:

1. 所有數據值首先以低字節存儲。這使得數據機器和操作系統獨立。二進制可移植性的唯一要求是機器使用兩個補間符號整數和 IEEE 浮點格式。這些要求廣泛應用于在主流機器中。二進制兼容性可能不適用于有特殊的處理器的嵌入式系統。

2. 先存儲數據低字節并不嚴重地影響速度；數據行中的字節一般是未聯合的，從一個方向讀未聯合的字節并不比從反向讀更占用更多的資源。服務器上的獲取列值的代碼與其它代碼相比并不顯得時間緊。

3. 所有數字鍵值以高字節為先被存儲以允許一個更高地索引壓縮

4. 大文件（達63位文件長度）在支持大文件的文件系統和操作系統上被支持

5. MyISAM表最大行數為 18440000000000000000

6. 每個MyISAM表最大索引數是64

7. 每個索引最大的列數是16個

8. 最大的鍵長度是1000字節。這也可以通過編譯來改變。對于鍵長度超過250字節的情況，一個超過1024字節的的鍵塊被用上。

9. 當記錄以排好序的順序插入（就像你使用一個AUTO_INCREMENT列之時），索引樹被劈開以便高節點僅包含一個鍵。這改善了索引樹的空間利用率。

10. 支持每表一個AUTO_INCREMEN列的內部處理。MyISAM為INSERT和UPDATE操作自動更新這一列。這使得AUTO_INCREMENT列更快（至少10%）。在序列頂的值被刪除之后就不能再利用。（當AUTO_INCREMENT列被定義為多列索引的最后一列，可以出現重使用從序列頂部刪除的值的情況 ）。AUTO_INCREMENT值可用ALTER TABLE或myisamch來重置。

11. 當把刪除和更新及插入混合的時候，動態尺寸的行更少碎片。這要通過合并相鄰被刪除的塊，以及若下一個塊被刪除，就擴展到下一塊來自動完成。

12. MyISAM 支持并發插入:如果數據文件中間的表沒有自由塊了，在其它線程從表讀的同時，你可以INSERT新行到表中。（這被認識為并發操作 ）。自由塊的出現是作為刪除行的結果，或者是用比當前內容多的數據對動態長度行更新的結果。當所有自由塊被用完（填滿），未來的插入又變成并發。請參閱8.11.3 節 "并發插入"。

13. 你可以把數據文件和索引文件放在不同目錄，用DATA DIRECTORY和INDEX DIRECTORY選項CREATE TABLE以獲得更高的速度，請參閱13.3.18節，“CREATE TABLE語法”。

14. BLOB和TEXT列可以被索引。

15. NULL值被允許在索引的列中。這個占每個鍵的0-1個字節

16. 每個字符列可以又不同的字符集，請參閱第10.1章 ：“字符集支持”。

17. 在MyISAM索引文件里又一個標志，它表明表是否被正確關閉。如果用--myisam-recover選項啟動mysqld，MyISAM表在打開得時候被自動檢查，如果被表被不恰當地關閉，就修復表。

18. 如果你用--update-state選項運行myisamchk，它標注表為已檢查。myisamchk --fast只檢查那些沒有這個標志的表。

19. myisamchk --analyze為部分鍵存儲統計信息，也為整個鍵存儲統計信息。

20. myisampack可以打包BLOB和VARCHAR列

MyISAM也支持下列特征：

1. 支持true VARCHAR類型；VARCHAR列以存儲在2個字節中的長度來開始。

2. 有VARCHAR的表可以有固定或動態記錄長度。

3. VARCHAR和CHAR列可以多達64KB。

4. 一個被搞亂的已計算索引對可對UNIQUE來使用。這允許你在表內任何列的合并上有UNIQUE。（盡管如此，你不能在一個UNIQUE已計算索引上搜索）。

MyISAM 啟動選項:

mysqld 的以下選項可用于更改 MyISAM 表的行為。有關其他信息, 請參見5.1.4 節 "服務器命令選項"。

1. --myisam-recover-options=mode

設置為崩潰MyISAM表自動恢復的模式

2. --delay-key-write=ALL

對任何MyISAM表的寫操作之間不要刷新鍵緩沖區。

注釋：如果要這么做。當MyISAM表在使用中之時，不應該使用另一個程序來訪問它（比如從另一個MySQL服務器或用myisamchk）。這么做會導致索引被破壞。對使用--delay-key-write的表，--external-locking不會生效

以下系統變量會影響 MyISAM 表的行為。有關其他信息, 請參見5.1.5 節 "服務器系統變量"。

1. bulk_insert_buffer_size

用在塊插入優化中的樹緩沖區的大小。注釋：這是一個per thread的限制。

2. myisam_max_sort_file_size

在重新創建 MyISAM 索引 (在修復表、更改表或加載數據 INFILE 期間) 允許 MySQL 使用的臨時文件的最大大小。如果文件大小大于此值, 則使用密鑰緩存來創建索引, 這會比較慢。該值以字節為單位給出。

3. myisam_sort_buffer_size

設置恢復表時使用的緩沖區大小。

如果用--myisam-recover選項啟動mysqld，自動恢復被激活。在這種情況下，當服務器打開一個MyISAM表之時，服務器會檢查是否表被標注為崩潰，或者表的打開計數變量是否不為0且你正用--skip-external-locking運行服務器。如果這些條件的任何一個為真，下列情況發生：

1. 表被查錯。

2. 如果服務器發現一個錯誤，它試著做快速表修復（排序且不重新創建數據文件）。

3. 如果修復因為數據文件中的一個錯誤而失敗（例如，一個重復鍵錯誤），服務器會再次嘗試修復，這一次重建數據文件。

4. 如果修復仍然失敗，服務器用舊修復選項方法再重試一次修復（一行接一行地寫，不排序）。這個方法應該能修復任何類型的錯誤，并且需要很低的磁盤空間。

如果恢復不能夠從先前完成的語句里恢復所有行，而且你不能在--myisam-recover選項值指定FORCE，自動修復會終止，并在錯誤日志里寫一條錯誤信息：

Error: Couldn't repair table: test.g00pages

如果你指定FORCE，取而代之地，類似這樣的一個警告被給出：

Warning: Found 344 of 354 rows when repairing ./test/g00pages

注釋：如果自動恢復值包括BACKUP，恢復進程創建文件并用tbl_name-datetime.BAK形式取名。你應該有一個cron腳本，它自動把這些文件從數據庫目錄移到備份媒質上。

鍵所需的空間

1. MyISAM表使用B型樹索引。你可以粗略地計算索引文件的大小為(key_length+4)/0.67, 加上所有的鍵之和。當所有鍵以排序的順序插入并且表沒有任何壓縮的鍵之時，以上估計是對最壞的情況的。

2. 字符串索引是被空間壓縮的。如果第一個字符串索引部分是字符串，它也被加前綴壓縮。如果字符串列有許多拖曳空間，或字符串 列是一個總是不用完全長度的VARCHAR列，空間壓縮使得索引文件比最壞情況時的數值要小。前綴壓縮被用在以字符串開始的鍵上。如果有許多具有同一前綴的字符串，前綴壓縮是有幫助的。

3. 在MyISAM表，你也可以在創建表的時候通過指定PACK_KEYS=1來前綴壓縮數字。當數字被以高字節優先存儲之時，若你有許多具有同一前綴的整數鍵，上述方法是有幫助的。

MyISAM表的存儲格式

MyISAM 支持三種不同的存儲格式。根據所使用的列類型, 將自動選擇其中的兩個, 即固定和動態格式。第三個壓縮格式只能使用 myisampack 實用程序創建 (請參見4.6.5 節 "myisampack 生成壓縮的只讀 MyISAM 表")。

當你CREATE或ALTER一個沒有BLOB或TEXT列的表，你可以用ROW_FORMAT表選項強制表的格式為FIXED或DYNAMIC。這 會導致CHAR和VARCHAR列因FIXED格式變成CHAR，或因DYNAMIC格式變成VARCHAR。

有關 ROW_FORMAT 的信息, 請參見13.1.18 節 "創建表語法"。

您可以使用 myisamchk --unpack 解壓 (unpack) 壓縮的 MyISAM 表，“myisamchk — MyISAM Table-Maintenance Utility”的詳細信息見4.6.3章節

靜態（固定長度）表特征

1. 靜態格式是MyISAM表的默認存儲格式。當表不包含變量長度列（VARCHAR, BLOB, 或TEXT）時，使用這個格式。每一行用固定字節數存儲。

2. 在三 MyISAM 存儲格式中, 靜態格式是最簡單、最安全的 (最不受損壞的)。由于可以輕松地在磁盤上找到數據文件中的行, 因此它也是最快的on-disk格式: 當按照索引中的行號查找一個行時，用行長度乘以行號。同樣，當掃描一個表的時候，很容易用每個磁盤讀操作讀一定數量的記錄。

3. 如果您的計算機在 MySQL 服務器寫入固定格式的 MyISAM 文件時崩潰, 安全是顯然的。在這種情況下, myisamchk 可以很容易地確定每行的開始和結束位置, 因此它通常可以回收除了部分寫入之外的所有行(緩沖區中未寫入的所有行)。MyISAM 表索引始終可以基于數據行進行重建。

注意：固定長度行格式僅適用于沒有 BLOB 或TEXT列的表。使用顯式 ROW_FORMAT 子句創建具有BLOB 或TEXT這些列的表不會引發錯誤或警告; 格式規范將被忽略。

靜態格式表的一般特征：

1. CHAR 和 VARCHAR 列的空間填充到指定的列寬，盡管列類型未被更改。BINARY和 VARBINARY 列用0x00 字節填充到列寬。

2. NULL 列需要行中的額外空間來記錄它們的值是否為 NULL。每個 NULL 列需要一個額外的位, 四舍五入到最近的字節。

3. 非常快。

4. 容易緩存。

5. 在崩潰后易于重建, 因為數據行位于固定位置。

6. 除非刪除大量的行并希望將可用磁盤空間返回給操作系統, 否則無需進行重組。為此, 請使用 OPTIMIZE TABLE 或 myisamchk -r。

7. 通常比動態格式表需要更多的磁盤空間。

8. 使用以下表達式計算靜態大小行的預期行長度 (以字節為單位):

row length = 1

+ (sum of column lengths)

+ (number of NULL columns + delete_flag + 7)/8

+ (number of variable-length columns)

對于具有靜態行格式的表, delete_flag 為1。靜態表在行記錄中使用位, 用于指示是否已刪除行的標志。delete_flag 是0用于動態表, 因為該標志存儲在動態行標題中。

動態表特性

如果一個MyISAM表包含任何可變長度 列（VARCHAR, BLOB或TEXTDynamic），或者如果一個表被用ROW_FORMAT=DYNAMIC選項來創建，動態存儲格式被使用。

動態格式比靜態格式稍微復雜一些，因為每行有一個表明行有多長的頭。當一個記錄因為更新的結果被變得更長，該記錄也可以在超過一個位置處結束。

可以使用OPTIMIZE TABLE或myisamchk -r來對一個表整理碎片。如果在一個表中有頻繁訪問或改變的固定長度列，并且表中也有一些可變長度列，為避免碎片而把這些可變長度列移到其它表可能是一個好主意。

動態格式表的一般特征：

1. 除了長度少于4的列外，所有的字符串列是動態的。

2. 每行前面都有一個位圖, 指示哪些列包含空字符串 (對于字符串列) 或0 (對于數值列)。這不包括包含 NULL 值的列。如果字符串列在尾隨空格后的長度為0, 或者數值列的值為0, 則在位圖中標記, 而不保存到磁盤。非空字符串保存為一個長度字節加上字符串內容。

3. NULL 列需要行中的額外空間來記錄它們的值是否為 NULL。每個 NULL 列需要一個額外的位, 四舍五入到最近的字節。

4. 通常比固定長度表需要更少的磁盤空間

5. 每行只使用所需的空間。但是, 如果行變得更大, 則會將其拆分為所需的多個片斷, 從而導致行碎片。例如, 如果使用擴展行長度的信息更新行, 則該行將變得有碎片。在這種情況下, 您可能需要時不時運行OPTIMIZE TABLE或myisamchk -r以提高性能。使用 myisamchk -ei 獲取表統計信息。

6. 比靜態格式表在崩潰后重建更困難, 因為行可能會被分割成許多片段和鏈接 (碎片), 可能會在重建時丟失。

7. 使用以下表達式計算動態大小行的預期行長度:

row length = 3

+ (number of columns + 7) / 8

+ (number of char columns)

+ (packed size of numeric columns)

+ (length of strings)

+ (number of NULL columns + 7) / 8

8. 對每個鏈接需要額外的6字節。在一個更新導致一個記錄的擴大之時，一個動態記錄被鏈接了。每個新鏈接至少是20字節，所以下一個擴大可能在同樣的鏈接里進行。如果不是，則另一個鏈接將被建立。你可以使用myisamchk -ed來找出鏈接的數目。所有的鏈接可以用 OPTIMIZE TABLE 或 myisamchk -r來移除。

壓縮表特性

已壓縮存儲格式是由myisampack工具創建的只讀格式。

已壓縮表可以用myisamchk來解壓縮。

壓縮表有下列特征：

1. 已壓縮表占據非常小的磁盤空間。這最小化了磁盤用量，當使用緩慢的磁盤（如CD-ROM)之時，這是很有用的。

2. 每個記錄是被單獨壓縮的，所以只有非常小的訪問開支。依據表中最大的記錄，一個記錄的頭在每個表中占據1到3個字節。每個 列被不同地壓縮。通常每個列有一個不同的Huffman樹。一些壓縮類型如下：

 2.1 后綴空間壓縮。

 2.2 前綴空間壓縮。

 2.3 零值的數用一個位來存儲。

 2.4 如果在一個整型列中的值有一個小的范圍，列被用最小可能的類型來存儲。比如，一個BIGINT列（8字節），如果所有它的值在-128到127范圍內，它可以被存儲為TINYINT列（1字節）

 2.5 如果一個列僅有一小組可能的值，列的類型被轉化成ENUM。

 2.6 一個列可以使用先前壓縮類型的任意合并。

3. 可以處理固定長度或動態長度記錄。

注意

雖然壓縮表是只讀的, 因此不能在表中更新或添加行, 但 DDL (數據定義語言) 操作仍然有效。例如, 您仍然可以使用 drop 除去表, 并截斷表以清空表。

MyISAM 表問題

MySQL用來存儲數據的文件格式已經被廣泛測試過，但總是有導致數據表變得損壞的環境。

損壞的MyISAM表

即使MyISAM表格式非常可靠（SQL語句對表做的所有改變在語句返回之前被寫下），如果下列任何事件發生，你依然可以獲得損壞的表：

1. mysqld 進程在寫入過程中被殺死。

2. 發生意外的計算機關機 (例如, 計算機被關閉)。

3. 硬件故障。

4. 使用外部程序 (如 myisamchk) 修改正在被服務器修改的表。

5. MySQL 或 MyISAM 代碼中的bug。

表損壞的典型癥狀為:

1. 從表中查詢數據時會出現以下錯誤:

Incorrect key file for table: '...'. Try to repair it

2. 查詢在表中找不到行或返回不完整的結果。

可以使用 CHECK TABLE語句檢查 MyISAM 表的健康狀況, 并用REPAIR TABLE修復損壞的 MyISAM 表。當 mysqld 未運行時, 還可以使用 myisamchk 命令檢查或修復表。

參閱 13.7.2.2 章節 “CHECK TABLE Syntax”, 13.7.2.5 章節 “REPAIR TABLE Syntax”, 4.6.3 章節, “myisamchk — MyISAM Table-Maintenance Utility”.

如果表頻繁損壞, 則應嘗試確定發生此事件的原因。最重要的是要知道該表是否因服務器崩潰而損壞。通過在錯誤日志中查找最近 restarted mysqld 消息, 進行驗證。如果有這樣的消息, 表損壞很可能是服務器崩潰導致的。否則, 損壞可能發生在正常操作期間。這是個 bug。您應該嘗試創建一個可重現的測試用例來演示問題。

參閱 B.5.3.3 章節 “What to Do If MySQL Keeps Crashing”, 28.5 章節 “Debugging and Porting MySQL”.

未正確關閉表的問題

每個 MyISAM 索引文件 (.MYI文件) 在頭部有一個計數器, 可用于檢查表是否已正確關閉。如果從CHECK TABLE或 myisamchk 得到以下警告, 則表示此計數器已經不同步了:

clients are using or haven't closed the table properly

此警告不一定意味著該表已損壞, 但至少應檢查表。

該計數器的工作原理如下:

1. 第一次在 MySQL 中更新表時, 索引文件頭部中的計數器將遞增。

2. 在未來的更新中, 計數器不會更改。

3. 當表的最后實例被關閉（因為一個操作FLUSH TABLE或因為在表緩沖區中沒有空間）之時，若表已經在任何點被更新，則計數器減一。

4. 當修復表, 或檢查表時它被發現是好的, 計數器被重置為零。

5. 為了避免與其它可能檢查表的進程進行交互的問題，若計數器為零，在關閉時計數器不減一。

換言之, 只有在下列情況下, 計數器才會變得不正確:

1. 復制 MyISAM 表, 不先 LOCK TABLES 和 FLUSH TABLES

2.  MySQL在一次更新和最后關閉之間崩潰（注意，表可能依然完好，因為MySQL總是在每個語句之間為每件事發出寫操作）

3. 一個表被mysqld使用的同時，又被myisamchk --recover或myisamchk --update-state修改。

4. 多個 mysqld 服務器正在使用該表, 一臺服務器在其被另一臺服務器使用時在表上執行了修復表或檢查表。在這個架構中, 使用CHECK TABLE是安全的, 盡管可能從其他服務器得到警告。但是, 應避免REPAIR TABLE, 因為當一個服務器用一個新的數據文件替代舊的之時，其他服務器不知道這一點。

總的來說，在多服務器之間共用一個數據目錄是一個壞主意。

參閱 5.6 章節 “Running Multiple MySQL Instances on One Machine” 獲得更多研討。