15. 存儲引擎和表類型 · mysql5.1參考手冊

### 第15章：存儲引擎和表類型 MySQL支持數個存儲引擎作為對不同表的類型的處理器。MySQL存儲引擎包括處理事務安全表的引擎和處理非事務安全表的引擎： ·????????MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默認的存儲引擎，除非你配置MySQL默認使用另外一個引擎。 ·????????MEMORY存儲引擎提供“內存中”表。MERGE存儲引擎允許集合將被處理同樣的MyISAM表作為一個單獨的表。就像MyISAM一樣，MEMORY和MERGE存儲引擎處理非事務表，這兩個引擎也都被默認包含在MySQL中。 **注釋：**MEMORY存儲引擎正式地被確定為HEAP引擎。 ·????????InnoDB和BDB存儲引擎提供事務安全表。BDB被包含在為支持它的操作系統發布的MySQL-Max二進制分發版里。InnoDB也默認被包括在所有MySQL 5.1二進制分發版里，你可以按照喜好通過配置MySQL來允許或禁止任一引擎。 ·????????EXAMPLE存儲引擎是一個“存根”引擎，它不做什么。你可以用這個引擎創建表，但沒有數據被存儲于其中或從其中檢索。這個引擎的目的是服務，在MySQL源代碼中的一個例子，它演示說明如何開始編寫新存儲引擎。同樣，它的主要興趣是對開發者。 ·????????NDB Cluster是被MySQL Cluster用來實現分割到多臺計算機上的表的存儲引擎。它在MySQL-Max 5.1二進制分發版里提供。這個存儲引擎當前只被Linux, Solaris, 和Mac OS X 支持。在未來的MySQL分發版中，我們想要添加其它平臺對這個引擎的支持，包括Windows。 ·????????ARCHIVE存儲引擎被用來無索引地，非常小地覆蓋存儲的大量數據。 ·????????CSV存儲引擎把數據以逗號分隔的格式存儲在文本文件中。 ·???????? BLACKHOLE存儲引擎接受但不存儲數據，并且檢索總是返回一個空集。 ·????????FEDERATED存儲引擎把數據存在遠程數據庫中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。在未來的分發版中，我們想要讓它使用其它驅動器或客戶端連接方法連接到另外的數據源。尋求選擇一個存儲引擎的幫助，請參閱[14.4節，“選擇一個存儲引擎”](# "14.4.?Choosing a Storage Engine")。這一章講述除NDB Cluster外的每一個MySQL存儲引擎，NDB Cluster在[第17章：_MySQL Cluster_](# "Chapter?17.?MySQL Cluster")中介紹。當年創建一個新表的時候，你可以通過添加一個ENGINE 或TYPE 選項到CREATE TABLE語句來告訴MySQL你要創建什么類型的表： CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY; 雖然TYPE仍然在MySQL 5.1中被支持，現在ENGINE是首選的術語。如果你省略掉ENGINE或TYPE選項，默認的存儲引擎被使用。一般的默認是MyISAM，但你可以用--default-storage-engine或--default-table-type服務器啟動選項來改變它，或者通過設置storage_engine或table_type系統變量來改變。當MySQL被用MySQL配置向導安裝在Windows平臺上，InnoDB存儲引擎替代MyISAM存儲引擎作為替代，請參閱[2.3.5.1節，“介紹”](# "2.3.5.1.?Introduction")。要把一個表從一個類型轉到另一個類型，可使用ALTER TABLE語句，這個語句指明新的類型： ALTER TABLE t ENGINE = MYISAM; ALTER TABLE t TYPE = BDB; 請參閱[13.1.5節，“CREATE TABLE語法”](# "13.1.5.?CREATE TABLE Syntax")和[13.1.2節，“ALTER TABLE語法”](# "13.1.2.?ALTER TABLE Syntax")。如果你試著使用一個未被編譯進MySQL的存儲引擎，或者試著用一個被編譯進MySQL但沒有被激活的存儲引擎，MySQL取而代之地創建一個MyISAM類型的表。當你在支持不同存儲引擎的MySQL服務器之間拷貝表的時候，上述的行為是很方便的。（例如，在一個復制建立中，可能你的主服務器為增加安全而支持事務存儲引擎，但從服務器為更快的速度而僅使用非事務存儲引擎。) 在不可用的類型被指定時，自動用MyISAM表來替代，這會對MySQL的新用戶造成混淆。無論何時一個表被自動改變之時，產生一個警告。 MySQL總是創建一個.frm文件來保持表和列的定義。表的索引和數據可能被存儲在一個或多個文件里，這取決于表的類型。服務器在存儲引擎級別之上創建.frm文件。單獨的存儲引擎創建任何需要用來管理表的額外文件。一個數據庫可以包含不同類型的表。事務安全表(TST) 比起非事務安全表 (NTST)有幾大優勢： ·????????更安全。即使MySQL崩潰或遇到硬件問題，要么自動恢復，要么從備份加事務日志恢復，你可以取回數據。 ·????????你可以合并許多語句，并用COMMIT語句同時接受它們全部（如果autocommit被禁止掉）。 ·???????? 你可以執行ROLLBACK來忽略你的改變（如果autocommit被禁止掉）。 ·????????如果更新失敗，你的所有改變都變回原來。（用非事務安全表，所有發生的改變都是永久的）。 ·????????事務安全存儲引擎可以給那些當前用讀得到許多更新的表提供更好的部署。雖然MySQL支持數個事務安全存儲引擎，為獲得最好結果，你不應該在一個事務那混合不同表類型。如果你混合表類型會發生問題，更多信息請參閱[13.4.1節，“START TRANSACTION, COMMIT和ROLLBACK Syntax”](# "13.4.1.?START TRANSACTION, COMMIT, and ROLLBACK Syntax")。如果你沒有指定配置值的話，InnoDB使用默認的配置值。請參閱[15.2.3節，“InnoDB配置”](# "15.2.3.?InnoDB Configuration")。非事務安全表自身有幾個優點，因為沒有事務開支，所有優點都能出現： ·????????更快 ·????????需要更少的磁盤空間 ·????????執行更新需要更少的內存你可以在同一個語句中合并事務安全和非事務安全表來獲得兩者最好的情況。盡管如此，在autocommit被禁止掉的事務里，變換到非事務安全表依舊即時提交，并且不會被回滾。 ### 15.1.?MyISAM存儲引擎 [15.1.1. MyISAM啟動選項](#) [15.1.2.鍵需要的空間](#) [15.1.3. MyISAM表存儲格式](#) [15.1.4. MyISAM表的問題](#) MyISAM是默認存儲引擎。它基于更老的ISAM代碼，但有很多有用的擴展。（注意MySQL 5.1不支持ISAM）。每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。.frm文件存儲表定義。數據文件的擴展名為.MYD (MYData)。索引文件的擴展名是.MYI (MYIndex)。要明確表示你想要用一個MyISAM表格，請用ENGINE表選項指出來： CREATE TABLE t (i INT) ENGINE = MYISAM; **注釋**：老版本的MySQL使用TYPE而不是ENGINE（例如，TYPE = MYISAM）。MySQL 5.1為向下兼容而支持這個語法，但TYPE現在被輕視，而ENGINE是首先的用法。一般地，ENGINE選項是不必要的；除非默認已經被改變了，MyISAM是默認存儲引擎。你可以用**myisamchk**工具來檢查或修復MyISAM表。請參閱[5.9.5.6節，“使用**myisamchk**做崩潰恢復”](# "5.9.5.6.?Using myisamchk for Crash Recovery")。你也可以用**myisampack**來壓縮MyISAM表，讓它們占更少的空間。請參閱[8.2節，“myisampack，產生壓縮、只讀的MyISAM表”](# "8.2.?myisampack — Generate Compressed, Read-Only MyISAM Tables")。如下是MyISAM存儲引擎的一些特征： ·????????所有數據值先存儲低字節。這使得數據機和操作系統分離。二進制輕便性的唯一要求是機器使用補碼（如最近20年的機器有的一樣）和IEEE浮點格式（在主流機器中也完全是主導的）。唯一不支持二進制兼容性的機器是嵌入式系統。這些系統有時使用特殊的處理器。先存儲數據低字節并不嚴重地影響速度；數據行中的字節一般是未聯合的，從一個方向讀未聯合的字節并不比從反向讀更占用更多的資源。服務器上的獲取列值的代碼與其它代碼相比并不顯得時間緊。 ·????????大文件（達63位文件長度）在支持大文件的文件系統和操作系統上被支持。 ·????????當把刪除和更新及插入混合的時候，動態尺寸的行更少碎片。這要通過合并相鄰被刪除的塊，以及若下一個塊被刪除，就擴展到下一塊來自動完成。 ·????????每個MyISAM表最大索引數是64。這可以通過重新編譯來改變。每個索引最大的列數是16個。 ·????????最大的鍵長度是1000字節。這也可以通過編譯來改變。對于鍵長度超過250字節的情況，一個超過1024字節的的鍵塊被用上。 ·????????BLOB和TEXT列可以被索引。 ·????????NULL值被允許在索引的列中。這個占每個鍵的0-1個字節。 ·????????所有數字鍵值以高字節為先被存儲以允許一個更高地索引壓縮。 ·????????當記錄以排好序的順序插入（就像你使用一個AUTO_INCREMENT列之時），索引樹被劈開以便高節點僅包含一個鍵。這改善了索引樹的空間利用率。 ·????????每表一個AUTO_INCREMEN列的內部處理。MyISAM為INSERT和UPDATE操作自動更新這一列。這使得AUTO_INCREMENT列更快（至少10%）。在序列頂的值被刪除之后就不能再利用。（當AUTO_INCREMENT列被定義為多列索引的最后一列，可以出現重使用從序列頂部刪除的值的情況）。AUTO_INCREMENT值可用ALTER TABLE或**myisamch**來重置。 ·????????如果數據文件中間的表沒有自由塊了，在其它線程從表讀的同時，你可以INSERT新行到表中。（這被認識為并發操作）。自由塊的出現是作為刪除行的結果，或者是用比當前內容多的數據對動態長度行更新的結果。當所有自由塊被用完（填滿），未來的插入又變成并發。 ·????????你可以把數據文件和索引文件放在不同目錄，用DATA DIRECTORY和INDEX DIRECTORY選項CREATE TABLE以獲得更高的速度，請參閱[13.1.5節，“CREATE TABLE語法”](# "13.1.5.?CREATE TABLE Syntax")。 ·????????每個字符列可以又不同的字符集，請參閱[第10章：“字符集支持”](# "Chapter?10.?Character Set Support")。 ·????????在MyISAM索引文件里又一個標志，它表明表是否被正確關閉。如果用--myisam-recover選項啟動**mysqld**，MyISAM表在打開得時候被自動檢查，如果被表被不恰當地關閉，就修復表。 ·????????如果你用--update-state選項運行**myisamchk**，它標注表為已檢查。**myisamchk --fast**只檢查那些沒有這個標志的表。 ·????????**myisamchk --analyze**為部分鍵存儲統計信息，也為整個鍵存儲統計信息。 ·????????**myisampack**可以打包BLOB和VARCHAR列。 MyISAM也支持下列特征： ·????????支持true VARCHAR類型；VARCHAR列以存儲在2個字節中的長度來開始。 ·????????有VARCHAR的表可以有固定或動態記錄長度。 ·????????VARCHAR和CHAR列可以多達64KB。 ·???????? 一個被搞亂的已計算索引對可對UNIQUE來使用。這允許你在表內任何列的合并上有UNIQUE。（盡管如此，你不能在一個UNIQUE已計算索引上搜索）。對MyISAM存儲引擎，有一個更詳細的論壇在[http://forums.mysql.com/list.php?21](http://forums.mysql.com/list.php?21)。 ### 15.1.1.?MyISAM啟動選項下列對**mysqld** 的選項可用來改變MyISAM表的行為： ·????????--myisam-recover=_mode_ 設置為崩潰MyISAM表自動恢復的模式。 ·????????--delay-key-write=ALL 對任何MyISAM表的寫操作之間不要刷新鍵緩沖區。 **注釋：**如果你要這么做。當表在使用中之時，你應該不使用來自另一個程序的MyISAM表（比如從另一個MySQL服務器或用**myisamchk**）。這么做會導致索引被破壞。對使用--delay-key-write的表，使用--external-locking沒有幫助。請參閱[5.3.1節，“**mysqld**命令行選項”](# "5.3.1.?mysqld Command-Line Options")。下列系統變量影響MyISAM表的行為： ·????????bulk_insert_buffer_size 用在塊插入優化中的樹緩沖區的大小。**注釋**：這是一個_per thread_的限制。 ·????????(_OBSOLETE_) myisam_max_extra_sort_file_size 這個參數已經不在MySQL中使用。 ·????????myisam_max_sort_file_size 如果臨時文件會變得超過索引，不要使用快速排序索引方法來創建一個索引。**注釋**：這個參數以字節的形式給出。 ·????????myisam_sort_buffer_size 設置恢復表之時使用的緩沖區的尺寸。請參閱[5.3.3節，“服務器系統變量”](# "5.3.3.?Server System Variables")。如果用--myisam-recover選項啟動**mysqld**，自動恢復被激活。在這種情況下，當服務器打開一個MyISAM表之時，服務器會檢查是否表被標注為崩潰，或者表的打開計數變量是否不為0且你正用--skip-external-locking運行服務器。如果這些條件的任何一個為真，下列情況發生： ·????????表被查錯。 ·????????如果服務器發現一個錯誤，它試著做快速表修復（排序且不重新創建數據文件）。 ·????????如果修復因為數據文件中的一個錯誤而失敗（例如，一個重復鍵錯誤），服務器會再次嘗試修復，這一次重建數據文件。 ·???????? 如果修復仍然失敗，服務器用舊修復選項方法再重試一次修復（一行接一行地寫，不排序）。這個方法應該能修復任何類型的錯誤，并且需要很低的磁盤空間。如果恢復不能夠從先前完成的語句里恢復所有行，而且你不能在--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**腳本，它自動把這些文件從數據庫目錄移到備份媒質上。 ### 15.1.2.鍵所需的空間 MyISAM表使用B型樹索引。你可以粗略地計算索引文件的大小為(key_length+4)/0.67, 加上所有的鍵之和。當所有鍵以排序的順序插入并且表沒有任何壓縮的鍵之時，以上估計是對最壞的情況的。字符串索引是被空間壓縮的。如果第一個字符串索引部分是字符串，它也被加前綴壓縮。如果字符串列有許多拖曳空間，或字符串列是一個總是不用完全長度的VARCHAR列，空間壓縮使得索引文件比最壞情況時的數值要小。前綴壓縮被用在以字符串開始的鍵上。如果有許多具有同一前綴的字符串，前綴壓縮是有幫助的。在MyISAM表，你也可以在創建表的時候通過指定PACK_KEYS=1來前綴壓縮數字。當數字被以高字節優先存儲之時，若你有許多具有同一前綴的整數鍵，上述方法是有幫助的。 ### 15.1.3.?MyISAM表的存儲格式 [15.1.3.1. 靜態（固定長度）表特征](#) [15.1.3.2. 動態表特征](#) [15.1.3.3. 已壓縮表特征](#) MyISAM支持三種不同存儲格式。其中兩個（固定格式和動態格式)根據正使用的列的類型來自動選擇。第三個，即已壓縮格式，只能使用**myisampack**工具來創建。當你CREATE或ALTER一個沒有BLOB或TEXT列的表，你可以用ROW_FORMAT表選項強制表的格式為FIXED或DYNAMIC。這會導致CHAR和VARCHAR列因FIXED格式變成CHAR，或因DYNAMIC格式變成VARCHAR。通過用ALTER TABLE指定ROW_FORMAT={COMPRESSED | DEFAULT}，你可以壓縮或解壓縮表，請參閱[13.1.5節，“CREATE TABLE語法”](# "13.1.5.?CREATE TABLE Syntax")。 #### 15.1.3.1.?靜態（固定長度）表特征靜態格式是MyISAM表的默認存儲格式。當表不包含變量長度列（VARCHAR, BLOB, 或TEXT）時，使用這個格式。每一行用固定字節數存儲。 MyISAM的三種存儲格式中，靜態格式就最簡單也是最安全的（至少對于崩潰而言）。靜態格式也是最快的on-disk格式。快速來自于數據文件中的行在磁盤上被找到的容易方式：當按照索引中的行號查找一個行時，用行長度乘以行號。同樣，當掃描一個表的時候，很容易用每個磁盤讀操作讀一定數量的記錄。當MySQL服務器正往一個固定格式MyISAM文件寫的時候，如果計算機崩潰了，安全是顯然的。在這種情況下，**myisamchk**可以容易地決定每行從哪里開始到哪里結束，所以它通常可以收回所有記錄，除了寫了一部分的記錄。注意，基于數據行，MyISAM表索引可以一直被重新構建。靜態格式表的一般特征： ·????????CHAR列對列寬度是空間填補的。 ·????????非常快。 ·????????容易緩存。 ·???????? 崩潰后容易重建，因為記錄位于固定位置。 ·????????重新組織是不必要的，除非你刪除巨量的記錄并且希望為操作系統騰出磁盤空間。為此，可使用OPTIMIZE TABLE或者**myisamchk -r**。 ·????????通常比動態格式表需要更多的磁盤空間。 #### 15.1.3.2.?動態表特征如果一個MyISAM表包含任何可變長度列（VARCHAR, BLOB或TEXTDynamic），或者如果一個表被用ROW_FORMAT=DYNAMIC選項來創建，動態存儲格式被使用。這個格式更為復雜一點，因為每行有一個表明行有多長的頭。當一個記錄因為更新的結果被變得更長，該記錄也可以在超過一個位置處結束。你可以使用OPTIMIZE TABLE或**myisamchk**來對一個表整理碎片。如果在一個表中有你頻繁訪問或改變的固定長度列，表中也有一些可變長度列，僅為避免碎片而把這些可變長度列移到其它表可能是一個好主意。動態格式表的一般特征： ·????????除了長度少于4的列外，所有的字符串列是動態的。 ·????????在每個記錄前面是一個位圖，該位圖表明哪一列包含空字符串（對于字符串列）或者0（對于數字列）。注意，這并不包括包含NULL值的列。如果一個字符列在拖曳空間移除后長度為零，或者一個數字列為零值，這都在位圖中標注了且列不被保存到磁盤。非空字符串被存為一個長度字節加字符串的內容。 ·????????通常比固定長度表需要更少的磁盤空間。 ·????????每個記錄僅使用必需大小的空間。盡管如此，如果一個記錄變大，它就按需要被分開成多片，造成記錄碎片的后果。比如，你用擴展行長度的信息更新一行，該行就變得有碎片。在這種情況下，你可以時不時運行OPTIMIZE TABLE或**myisamchk -r**來改善性能。可使用**myisamchk -ei**來獲取表的統計數據。 ·???????? 動態格式表在崩潰后要比靜態格式表更難重建，因為一個記錄可能被分為多個碎片且鏈接（碎片）可能被丟失。 ·????????動態尺寸記錄期望的行長度用下列表達式來計算： ·??????????????? 3 ·??????????????? + (number of columns + 7) / 8 ·??????????????? + (number of char columns) ·??????????????? + (packed size of numeric columns) ·??????????????? + (length of strings) ·??????????????? + (number of NULL columns + 7) / 8 對每個鏈接需要額外的6字節。在一個更新導致一個記錄的擴大之時，一個動態記錄被鏈接了。每個新鏈接至少是20字節，所以下一個擴大可能在同樣的鏈接里進行。如果不是，則另一個鏈接將被建立。你可以使用**myisamchk -ed**來找出鏈接的數目。所有的鏈接可以用**myisamchk -r**來移除。 #### 15.1.3.3.?已壓縮表特征已壓縮存儲格式是由**myisampack**工具創建的只讀格式。所有MySQL分發版里都默認包括**myisampack**。已壓縮表可以用**myisamchk**來解壓縮。已壓縮表有下列特征： ·????????已壓縮表占據非常小的磁盤空間。這最小化了磁盤用量，當使用緩慢的磁盤（如CD-ROM)之時，這是很有用的。 ·???????? 每個記錄是被單獨壓縮的，所以只有非常小的訪問開支。依據表中最大的記錄，一個記錄的頭在每個表中占據1到3個字節。每個列被不同地壓縮。通常每個列有一個不同的Huffman樹。一些壓縮類型如下： o??????? 后綴空間壓縮。 -??????? 前綴空間壓縮。 -??????? 零值的數用一個位來存儲。 -??????? 如果在一個整型列中的值有一個小的范圍，列被用最小可能的類型來存儲。比如，一個BIGINT列（8字節），如果所有它的值在-128到127范圍內，它可以被存儲為TINYINT列（1字節） -??????? 如果一個列僅有一小組可能的值，列的類型被轉化成ENUM。 -??????? 一個列可以使用先前壓縮類型的任意合并。 ·????????可以處理固定長度或動態長度記錄。 ### 15.1.4.?MyISAM表方面的問題 [15.1.4.1. 損壞的MyISAM表](#) [15.1.4.2. 未被適當關閉的表的問題](#) MySQL用來存儲數據的文件格式已經被廣泛測試過，但總是有導致數據表變得損壞的環境。 #### 15.1.4.1.?損壞的MyISAM表即使MyISAM表格式非常可靠（SQL語句對表做的所有改變在語句返回之前被寫下），如果下列任何事件發生，你依然可以獲得損壞的表： ·????????**mysqld**進程在寫中間被殺掉。 ·????????發生未預期的計算機關閉（例如，計算機被關閉）。 ·????????硬件故障。 ·????????你可以同時在正被服務器修改的表上使用外部程序（如**myisamchk**）。 ·????????MySQL或MyISAM代碼的軟件缺陷。一個損壞的表的典型癥狀如下： ·????????當在從表中選擇數據之時，你得到如下錯誤： ·??????????????? Incorrect key file for table: '...'. Try to repair it ·????????查詢不能在表中找到行或返回不完全的數據。你可以用CHECK TABLE statement語句來檢查MyISAM表的健康，并用REPAIR TABLE修復一個損壞的MyISAM表。當**mysqld**不運行之時，你也可以用**myisamchk**命令檢查或修理一個表。請參閱[13.5.2.3節，“CHECK TABLE語法”](# "13.5.2.3.?CHECK TABLE Syntax")， [13.5.2.6節，“REPAIR TABLE語法”](# "13.5.2.6.?REPAIR TABLE Syntax")，和[5.9.5節，“myisamchk — MyISAM表維護工具”](# "5.9.5.?myisamchk — MyISAM Table-Maintenance Utility")。如果你的表變得頻繁損壞，你應該試著確定為什么會這樣的原因。要明白的最重要的事是表變得損壞是不是因為服務器崩潰的結果。你可以在錯誤日志中查找最近的restarted mysqld消息來早期驗證這個。如果存在這樣一個消息，則表損壞是服務器死掉的一個結果是很有可能的。否則，損壞可能在正常操作中發生。這是一個缺陷。你應該試著創建一個展示這個問題的可重復生成的測試案例。請參閱[A.4.2節，“如果MySQL保持崩潰，該怎么做”](# "A.4.2.?What to Do If MySQL Keeps Crashing") 及[E.1.6節，“如果出現表崩潰，請生成測試案例”](# "E.1.6.?Making a Test Case If You Experience Table Corruption")。 #### 15.1.4.2.?未被適當關閉的表的問題每個MyISAM索引文件(.MYI)在頭有一個計數器，它可以被用來檢查一個表是否被恰當地關閉。如果你從CHECK TABLE或**myisamchk**得到下列警告，意味著這個計數器已經不同步了： clients are using or haven't closed the table properly 這個警告并不是完全意味著表已被破壞，但你至少應該檢查表。計數器的工作方式如下： ·????????表在MySQL中第一次被更新，索引文件頭的計數器加一。 ·????????在未來的更新中，計數器不被改變。 ·????????當表的最后實例被關閉（因為一個操作FLUSH TABLE或因為在表緩沖區中沒有空間）之時，若表已經在任何點被更新，則計數器減一。 ·????????當你修理或檢查表并且發現表完好之時，計數器被重置為零。 ·????????要避免與其它可能檢查表的進程進行事務的問題，若計數器為零，在關閉時計數器不減一。換句話來說，計數器只有在下列情況會不同步： ·????????MyISAM表不隨第一次發出的LOCK TABLES和FLUSH TABLES被復制。 ·????????MySQL在一次更新和最后關閉之間崩潰（注意，表可能依然完好，因為MySQL總是在每個語句之間為每件事發出寫操作）。 ·????????一個表被**myisamchk --recover**或**myisamchk --update-state**修改，同時被**mysqld**使用。 ·????????多個**mysqld**服務器正使用表，并且一個服務器在一個表上執行REPAIR TABLE或CHECK TABLE，同時該表也被另一個服務器使用。在這個結構中，使用CHECK TABLE是安全的，雖然你可能從其它服務器上得到警告。盡管如此，REPAIR TABLE應該被避免，因為當一個服務器用一個新的數據文件替代舊的之時，這并沒有發送信號到其它服務器上。總的來說，在多服務器之間分享一個數據目錄是一個壞主意。請參閱[5.12節，“在同一個機器上運行多個MySQL服務器”](# "5.12.?Running Multiple MySQL Servers on the Same Machine") 獲得更多地討論 ### 15.2.?InnoDB存儲引擎 [15.2.1. InnoDB概述](#) [15.2.2. InnoDB聯系信息](#) [15.2.3. InnoDB配置](#) [15.2.4. InnoDB啟動選項](#) [15.2.5. 創建InnoDB表空間](#) [15.2.6. 創建InnoDB表](#) [15.2.7. 添加和刪除InnoDB數據和日志文件](#) [15.2.8. InnoDB數據庫的備份和恢復atabase](#) [15.2.9. 把InnoDB數據庫移到另一臺機器上](#) [15.2.10. InnoDB事務模型和鎖定](#) [15.2.11. InnoDB性能調節提示](#) [15.2.12. 多版本的實施](#) [15.2.13. 表和索引結構](#) [15.2.14.文件空間管理和磁盤I/O](#) [15.2.15. InnoDB錯誤處理](#) [15.2.16. 對InnoDB表的限制](#) [15.2.17. InnoDB故障診斷和排除](#) ### 15.2.1.?InnoDB概述 InnoDB給MySQL提供了具有提交，回滾和崩潰恢復能力的事務安全（ACID兼容）存儲引擎。InnoDB鎖定在行級并且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多用戶部署和性能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支持FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。 InnoDB是為處理巨大數據量時的最大性能設計。它的CPU效率可能是任何其它基于磁盤的關系數據庫引擎所不能匹敵的。 InnoDB存儲引擎被完全與MySQL服務器整合，InnoDB存儲引擎為在主內存中緩存數據和索引而維持它自己的緩沖池。InnoDB存儲它的表＆索引在一個表空間中，表空間可以包含數個文件（或原始磁盤分區）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統上。 InnoDB默認地被包含在MySQL二進制分發中。Windows Essentials installer使InnoDB成為Windows上MySQL的默認表。 InnoDB被用來在眾多需要高性能的大型數據庫站點上產生。著名的Internet新聞站點Slashdot.org運行在InnoDB上。Mytrix, Inc.在InnoDB上存儲超過1TB的數據，還有一些其它站點在InnoDB上處理平均每秒800次插入/更新的負荷。 InnoDB在和MySQL一樣在同一個GNU GPL證書，第2版（1991年6月版）下發行。更多有關MySQL證書的信息，請參閱[http://www.mysql.com/company/legal/licensing/](http://www.mysql.com/company/legal/licensing/)。關于InnoDB存儲引擎，在[http://forums.mysql.com/list.php?22](http://forums.mysql.com/list.php?22)有一個詳細的論壇。 ### 15.2.2.?InnoDB聯系信息 InnoDB引擎的廠家的聯系信息，Innobase Oy的聯系方式如下： Web site: http://www.innodb.com/ Email: <sales@innodb.com> Phone: +358-9-6969 3250 (office) ?? ????+358-40-5617367 (mobile) ? Innobase Oy Inc. World Trade Center Helsinki Aleksanterinkatu 17 P.O.Box 800 00101 Helsinki Finland ### 15.2.3.?InnoDB配置 InnoDB存儲引擎是默認地被允許的。如果你不想用InnoDB表，你可以添加skip-innodb選項到MySQL選項文件。被InnoDB存儲引擎管理的兩個重要的基于磁盤的資源是InnoDB表空間數據文件和它的日志文件。如果你指定無InnoDB配置選項，MySQL將在MySQL數據目錄下創建一個名為ibdata1的10MB大小的自動擴展數據文件，以及兩個名為ib_logfile0和ib_logfile1的5MB大小的日志文件。 **注釋**：InnoDB給MySQL提供具有提交，回滾和崩潰恢復能力的事務安全（ACID兼容）存儲引擎。如果擬運行的操作系統和硬件不能如廣告說的那樣運行，InnoDB**就不能實現如上能力**。許多操作系統或磁盤子系統可能為改善性能而延遲或記錄寫操作。在一些操作系統上，就是系統調用（fsync()）也要等著，直到所有未寫入已被刷新文件的數據在被刷新到穩定內存之前可以確實返回了。因為這個，操作系統崩潰或掉電可能損壞當前提交的數據，或者在最壞的情況，因為寫操作已被記錄了，甚至破壞了數據庫。如果數據完整性對你很重要，你應該在用任何程序于生產中之前做一些“pull-the-plug”測試。Mac OS X 10.3 及以后版本，InnoDB使用一個特別的fcntl()文件刷新方法。在Linux下，建議**禁止回寫緩存。** 在ATAPI硬盤上，一個類似hdparm -W0 /dev/_hda_命令可能起作用。**小心某些驅動器或者磁盤控制器可能不能禁止回寫緩存。** **注釋**：要獲得好的性能，你應該如下面例子所討論那樣，明確提供InnoDB參數。自然地，你應該編輯設置來適合你的硬件和要求。要建立InnoDB表空間文件，在my.cnf選項文件里的[mysqld]節里使用innodb_data_file_path選項。在Windows上，你可以替代地使用my.ini文件。innodb_data_file_path的值應該為一個或多個數據文件規格的列表。如果你命名一個以上的數據文件，用分號(‘;’)分隔它們： innodb_data_file_path=datafile_spec1[;datafile_spec2]... 例如：把明確創建的具有相同特征的表空間作為默認設置的設置操作如下： [mysqld] innodb_data_file_path=ibdata1:10M:autoextend 這個設置配置一個可擴展大小的尺寸為10MB的單獨文件，名為ibdata1。沒有給出文件的位置，所以默認的是在MySQL的數據目錄內。尺寸大小用M或者G后綴來指定說明單位是MB或者GB。一個表空間，它在數據目錄里包含一個名為ibdata1的固定尺寸50MB的數據文件和一個名為ibdata2大小為50MB的自動擴展文件，其可以像這樣被配置： [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 一個指定數據文件的完全后綴包括文件名，它的尺寸和數個可選屬性： file_name:file_size[:autoextend[:max:max_file_size]] autoextend屬性和后面跟著的屬性只可被用來對innodb_data_file_path行里最后一個數據文件。如果你對最后的數據文件指定autoextend選項。如果數據文件耗盡了表空間中的自由空間，InnoDB就擴展數據文件。擴展的幅度是每次8MB。如果磁盤已滿，你可能想要把其它數據添加到另一個硬盤上。重新配置一個已存在表空間的指令見[15.2.7節，“添加和刪除InnoDB數據和日志文件”](# "15.2.7.?Adding and Removing InnoDB Data and Log Files")。 InnoDB并不感知最大文件尺寸，所以要小心文件系統，在那上面最大的文件尺寸是2GB。要為一個自動擴展數據文件指定最大尺寸，請使用max屬性。下列配置允許ibdata1漲到極限的500MB： [mysqld] innodb_data_file_path=ibdata1:10M:autoextend:max:500M InnoDB默認地在MySQL數據目錄創建表空間文件。要明確指定一個位置，請使用innodb_data_home_dir選項。比如，要使用兩個名為ibdata1和ibdata2的文件，但是要把他們創建到/ibdata，像如下一樣配置InnoDB： [mysqld] innodb_data_home_dir = /ibdata innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend **注釋**：InnoDB不創建目錄，所以在啟動服務器之前請確認/ibdata目錄的確存在。這對你配置的任何日志文件目錄來說也是真實的。使用Unix或DOS的mkdir命令來創建任何必需的目錄。通過把innodb_data_home_dir的值原原本本地部署到數據文件名，并在需要的地方添加斜杠或反斜杠，InnoDB為每個數據文件形成目錄路徑。如果innodb_data_home_dir選項根本沒有在my.cnf中提到，默認值是“dot”目錄 ./，這意思是MySQL數據目錄。如果你指定innodb_data_home_dir為一個空字符串，你可以為列在innodb_data_file_path值里的數據文件指定絕對路徑。下面的例子等價于前面那個例子： [mysqld] innodb_data_home_dir = innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend **一個簡單的****my.cnf****例子。**假設你有一臺配備128MB內存和一個硬盤的計算機。下面的例子顯示在my.cnf或my.ini里對InnoDB可能的配置參數，包括autoextend屬性。這個例子適合大多數在Unix和Windows上，不想分配InnoDB數據文件和日志文件到數個磁盤上的用戶。它在MySQL數據目錄創建一個自動擴展數據文件ibdata1和兩個日志文件ib_logfile0及ib_logfile1。同樣，InnoD在數據目錄里自動創建的小型檔案InnoDB日志文件ib_arch_log_0000000000也結束。 [mysqld] # You can write your other MySQL server options here # ... # Data files must be able to hold your data and indexes. # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 請確認MySQL服務器有適當的權限在數據目錄里創建文件。更一般地，服務器必須對任何它需要創建數據文件或日志文件的目錄有訪問權限。注意，在一些文件系統上，數據文件必需小于2GB。數據文件的合并尺寸必須至少10MB。當你第一次創建一個InnoDB表空間，最好從命令行來啟動MySQL服務器。InnoDB然后把數據庫創建的信息打印到屏幕，所以你可以看見正在發生什么。比如，在Windows上，如果**mysqld-max**位于C:\mysql\bin，你可以如下來啟動它： C:\> C:\mysql\bin\mysqld-max --console 如果你不發送服務器輸出到屏幕上，請檢查服務器的錯誤日志來看在啟動過程中InnoDB打印了什么。請參閱[15.2.5節，“創建InnoDB表空間”](# "15.2.5.?Creating the InnoDB Tablespace")，以獲得InnoDB顯示的信息看起來應該像什么的例子。 **Windows上如何指定選項?** 在Windows上選項文件的規則如下： ·????????只應該創建一個my.cnf或文件。 ·????????my.cnf文件應該被放在C盤根目錄。 ·????????my.ini文件應該被放置在WINDIR目錄；例如C:\WINDOWS或C:\WINNT。你可以在Windows控制臺的命令提示符使用SET命令來打印WINDIR的值： ·??????????????? C:\> SET WINDIR ·??????????????? windir=C:\WINNT ·????????如果你的PC在C盤不是啟動盤的地方使用啟動裝載機，你唯一的選擇是使用my.ini文件。 ·???????? 如果你使用安裝和配置向導安裝的MySQL，my.ini文件被放在MySQL的安裝目錄。請參閱[2.3.5.14節，“my.ini文件的位置”](# "2.3.5.14.?The Location of the my.ini File")。 **Unix上在哪里指定選項？** 在Unix上，**mysqld**從下列文件，如果它們存在的話。以下列的順序讀取選項： ·????????/etc/my.cnf 全局選項。 ·????????$MYSQL_HOME/my.cnf 服務器專用選項。 ·????????defaults-extra-file --defaults-extra-file選項指定的文件。 ·????????~/.my.cnf 用戶專用選項。 MYSQL_HOME代表環境變量，它內含著到包含服務器專用my.cnf文件的目錄的路徑。如果你確信**mysqld**只從指定文件讀取選項，你可以在啟動服務器之時在命令行使用--defaults-option作為第一個選項： mysqld --defaults-file=your_path_to_my_cnf **一個高級的****my.cnf****例子。**假設你有一臺Linux計算機，有2GB內存和三個60GB硬盤（在目錄路徑/, /dr2和/dr3)。下列例子顯示了在my.cnf里對InnoDB可能的配置參數。 [mysqld] # You can write your other MySQL server options here # ... innodb_data_home_dir = # # Data files must be able to hold your data and indexes innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend # # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is < 2GB innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /dr3/iblogs # innodb_log_files_in_group = 2 # # Set the log file size to about 25% of the buffer pool size innodb_log_file_size=250M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 # # Uncomment the next lines if you want to use them #innodb_thread_concurrency=5 注意，該例子把兩個數據文件放在不同磁盤上。InnoDB開始用第一個數據文件填充表空間。在一些情況下，如果所有數據不被放置在同一物理磁盤上，這樣將改善數據庫的性能。把日志文件放在與數據文件不同的磁盤上對性能是經常很有好處的。你也可以使用原始磁盤分區（原始設備）作為InnoDB數據文件，這樣可以加速I/O。請參閱[15.2.14.2節，“為表空間使用原始設備”](# "15.2.14.2.?Using Raw Devices for the Tablespace")。 **警告**：在32位GNU/Linux x86上,你必須要小心不要設置過高的內存用量。glibc可能允許進程堆積在線程堆棧上發展，它會造成你的服務器崩潰。如果下列表達式的值接近或者超過2GB，系統會面臨危機： innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB 每個線程使用一個堆棧（通常是2MB，但在MySQL AB二進制分發版里只有256KB）并且在最壞的情況下也使用sort_buffer_size + read_buffer_size附加內存。你可以自己編譯MySQL，在32位Windows上使用高達64GB物理內存。請參閱[15.2.4節，“InnoDB啟動選項”](# "15.2.4.?InnoDB Startup Options")里對innodb_buffer_pool_awe_mem_mb的描述。 **如何調整其它mysqld服務器參數？**下列值是典型的，且適用于多數用戶： [mysqld] skip-external-locking max_connections=200 read_buffer_size=1M sort_buffer_size=1M # # Set key_buffer to 5 - 50% of your RAM depending on how much # you use MyISAM tables, but keep key_buffer_size + InnoDB # buffer pool size < 80% of your RAM key_buffer_size=value ### 15.2.4.?InnoDB啟動選項這一節敘述InnoDB相關的服務器選項，所有這些選項可以以--_opt_name_=_value_的形式在命令行或在選項文件里被指定。 ·????????innodb_additional_mem_pool_size InnoDB用來存儲數據目錄信息＆其它內部數據結構的內存池的大小。你應用程序里的表越多，你需要在這里分配越多的內存。如果InnoDB用光了這個池內的內存，InnoDB開始從操作系統分配內存，并且往MySQL錯誤日志寫警告信息。默認值是1MB。 ·????????innodb_autoextend_increment 當自動擴展表空間被填滿之時，為擴展而增加的尺寸（MB為單位）。默認值是8。這個選項可以在運行時作為全局系統變量而改變。 ·????????innodb_buffer_pool_awe_mem_mb 如果緩沖池被放在32位Windows的AWE內存里，這個參數就是緩沖池的大小（MB為單位）。(僅在32位Windows上相關）如果你的32位Windows操作系統使用所謂的“地址窗口擴展（AWE)”支持超過4GB內存，你可以用這個參數把InnoDB緩沖池分配進AWE物理內存。這個參數最大的可能值是64000。如果這個參數被指定了，innodb_buffer_pool_size是在32位地址空間的**mysqld**內的窗口，InnoDB把那個AWE內存映射上去。對innodb_buffer_pool_size參數，一個比較好的值是500MB。 ·????????innodb_buffer_pool_size InnoDB用來緩存它的數據和索引的內存緩沖區的大小。你把這個值設得越高，訪問表中數據需要得磁盤I/O越少。在一個專用的數據庫服務器上，你可以設置這個參數達機器物理內存大小的80%。盡管如此，還是不要把它設置得太大，因為對物理內存的競爭可能在操作系統上導致內存調度。 ·????????innodb_checksums InnoDB在所有對磁盤的頁面讀取上使用校驗和驗證以確保額外容錯防止硬件損壞或數據文件。盡管如此，在一些少見的情況下（比如運行標準檢查之時）這個額外的安全特征是不必要的。在這些情況下，這個選項（默認是允許的）可以用--skip-innodb-checksums來關閉。 ·????????innodb_data_file_path 到單獨數據文件和它們尺寸的路徑。通過把innodb_data_home_dir連接到這里指定的每個路徑，到每個數據文件的完整目錄路徑可被獲得。文件大小通過給尺寸值尾加M或G以MB或者GB（1024MB）為單位被指定。文件尺寸的和至少是10MB。在一些操作系統上，文件必須小于2GB。如果你沒有指定innodb_data_file_path，開始的默認行為是創建一個單獨的大小10MB名為ibdata1的自擴展數據文件。在那些支持大文件的操作系統上，你可以設置文件大小超過4GB。你也可以使用原始磁盤分區作為數據文件，請參閱[15.2.14.2節，“為表空間使用原始設備”](# "15.2.14.2.?Using Raw Devices for the Tablespace")。 ·????????innodb_data_home_dir 目錄路徑對所有InnoDB數據文件的共同部分。如果你不設置這個值，默認是MySQL數據目錄。你也可以指定這個值為一個空字符串，在這種情況下，你可以在innodb_data_file_path中使用絕對文件路徑。 ·????????innodb_doublewrite 默認地，InnoDB存儲所有數據兩次，第一次存儲到doublewrite緩沖，然后存儲到確實的數據文件。這個選項可以被用來禁止這個功能。類似于innodb_checksums，這個選項默認是允許的；因為標準檢查或在對頂級性能的需要超過對數據完整性或可能故障的關注之時，這個選項用--skip-innodb-doublewrite來關閉。 ·????????innodb_fast_shutdown 如果你把這個參數設置為0，InnoDB在關閉之前做一個完全凈化和一個插入緩沖合并。這些操作要花幾分鐘時間，設置在極端情況下要幾個小時。如果你設置這個參數為1，InnoDB在關閉之時跳過這些操作。默認值為1。如果你設置這個值為2 (在Netware無此值)， InnoDB將刷新它的日志然后冷關機，仿佛MySQL崩潰一樣。已提交的事務不會被丟失，但在下一次啟動之時會做一個崩潰恢復。 ·????????innodb_file_io_threads InnoDB中文件I/O線程的數量。正常地，這個參數是用默認的，默認值是4，但是大數值對Windows磁盤I/O有益。在Unix上，增加這個數沒有效果，InnoDB總是使用默認值。 ·????????innodb_file_per_table 這個選項致使InnoDB用自己的.ibd文件為存儲數據和索引創建每一個新表，而不是在共享表空間中創建。請參閱[15.2.6.6節，“使用Per-Table表空間”](# "15.2.6.6.?Using Per-Table Tablespaces")。 ·????????innodb_flush_log_at_trx_commit 當innodb_flush_log_at_trx_commit被設置為0，日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務提交不做任何操作。當這個值為1（默認值）之時，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。當設置為2之時，在每個提交，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。盡管如此，在對日志文件的刷新在值為2的情況也每秒發生一次。我們必須注意到，因為進程安排問題，每秒一次的刷新不是100%保證每秒都發生。你可以通過設置這個值不為1來獲得較好的性能，但隨之你會在一次崩潰中損失二分之一價值的事務。如果你設置這個值為0，那么任何**mysqld**進程的崩潰會刪除崩潰前最后一秒的事務，如果你設置這個值為2，那么只有操作系統崩潰或掉電才會刪除最后一秒的事務。盡管如此，InnoDB的崩潰恢復不受影響，而且因為這樣崩潰恢復開始作用而不考慮這個值。注意，許多操作系統和一些磁盤硬件會欺騙刷新到磁盤操作。盡管刷新沒有進行，你可以告訴**mysqld**刷新已經進行。即使設置這個值為1，事務的持久程度不被保證，且在最壞情況下掉電甚至會破壞InnoDB數據庫。在SCSI磁盤控制器中，或在磁盤自身中，使用有后備電池的磁盤緩存會加速文件刷新并且使得操作更安全。你也可以試著使用Unix命令**hdparm**來在硬件緩存中禁止磁盤寫緩存，或使用其它一些對硬件提供商專用的命令。這個選項的默認值是1。 ·????????innodb_flush_method 這個選項只在Unix系統上有效。如果這個選項被設置為fdatasync （默認值），InnoDB使用fsync()來刷新數據和日志文件。如果被設置為O_DSYNC，InnoDB使用O_SYNC來打開并刷新日志文件，但使用fsync()來刷新數據文件。如果O_DIRECT被指定了（在一些GNU/Linux版本商可用），InnoDB使用O_DIRECT來打開數據文件，并使用fsync()來刷新數據和日志文件。注意，InnoDB使用fsync()來替代fdatasync()，并且它默認不使用O_DSYNC，因為這個值在許多Unix變種上已經發生問題。 ·????????innodb_force_recovery 警告：這個選項僅在一個緊急情況下被定義，當時你想要從損壞的數據庫轉儲表。可能的值為從1到6。這些值的意思在[15.2.8.1節，“強制恢復”](# "15.2.8.1.?Forcing Recovery")中敘述。作為一個安全措施，當這個選項值大于零之時，InnoDB阻止用戶修改數據。 ·????????innodb_lock_wait_timeout InnoDB事務在被回滾之前可以等待一個鎖定的超時秒數。InnoDB在它自己的鎖定表中自動檢測事務死鎖并且回滾事務。InnoDB用LOCK TABLES語句注意到鎖定設置。默認值是50秒。為在一個復制建立中最大可能的持久程度和連貫性，你應該在主服務器上的my.cnf文件里使用innodb_flush_log_at_trx_commit=1和sync-binlog=1。 ·????????innodb_locks_unsafe_for_binlog 這個選項在InnoDB搜索和索引掃描中關閉下一鍵鎖定。這個選項的默認值是假（false）。正常地，InnoDB使用一個被稱為_next-key locking_的算法。當搜索或掃描一個表索引之時，InnoDB以這樣一種方式實行行級鎖定，它對任何遇到的索引記錄設置共享的或獨占的鎖定。因此，行級鎖定實際是索引記錄鎖定。InnoDB對索引記錄設置的鎖定也影響被鎖定索引記錄之前的“gap”。如果一個用戶對某一索引內的記錄_R_又共享的或獨占的鎖定，另一個用戶不能立即在_R_之前以索引的順序插入一個新的索引記錄。這個選項導致InnoDB不在搜索或索引掃描中使用下一鍵鎖定。下一鍵鎖定仍然被用來確保外鍵強制及重復鍵核查。注意，使用這個選項可能會導致一些詭異的問題：假設你想要用值大于100的標識符從子表里讀取并鎖定所有的子記錄，同時向隨后在選定的行更新一些列： SELECT * FROM child WHERE id > 100 FOR UPDATE; 假設在id列有一個索引。查詢從id大于100的第一個記錄開始掃描索引。如果在索引記錄上的鎖定不把在間隙處生成的插入排除鎖定，同時一個新行被插進表中。如果你在同一個事務之內執行同樣的SELECT，你會在查詢返回的結果包里看到一個新行。這也意味著，如果新條目被加進數據庫，InnoDB不保證連續性；盡管如此，對應連續性仍被保證。因此，如果這個選項被使用，InnoDB在大多數孤立級別保證READ COMMITTED。這個選項甚至更不安全。InnoDB在一個UPDATE或DELETE中只鎖定它更新或刪除的行。這大大減少了死鎖的可能性，但是可以發生死鎖。注意，即使在當類似的操作影響不同行時的情況下，這個選項仍然不允許諸如UPDATE這樣的操作壓倒相似選項（比如另一個UPDATE）。考慮下列例子： CREATE TABLE A(A INT NOT NULL, B INT); INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT; 如果一個連接執行一個查詢： SET AUTOCOMMIT = 0; UPDATE A SET B = 5 WHERE B = 3; 并且其它連接跟著第一個連接執行其它查詢： SET AUTOCOMMIT = 0; UPDATE A SET B = 4 WHERE B = 2; 接著查詢2要等查詢1的提交或回滾，因為查詢1對行（2，3）有一個獨占的鎖定，并且查詢2在掃描行的同時也試著對它不能鎖定的同一個行（2，3）采取一個獨占的鎖定。這是因為當innodb_locks_unsafe_for_binlog選項被使用之時，查詢2首先對一個行采取一個獨占的鎖定，然后確定是否這個行屬于結果包，并且如果不屬于，就釋放不必要的鎖定。因此，查詢1按如下執行： x-lock(1,2) unlock(1,2) x-lock(2,3) update(2,3) to (2,5) x-lock(3,2) unlock(3,2) x-lock(4,3) update(4,3) to (4,5) x-lock(5,2) unlock(5,2) 并且查詢2按如下執行： x-lock(1,2) update(1,2) to (1,4) x-lock(2,3) - 等待查詢1提交或回滾 ·????????innodb_log_arch_dir 如果我們使用日志檔案，被完整寫入的日志文件所在的目錄也被歸檔。這個參數值如果被使用了，應該被設置得與innodb_log_group_home_dir一樣。盡管如此，它不是必需的。 ·????????innodb_log_archive 這個值當前被設為0。因為MySQL使用它自己的日志文件從備份來恢復，所以當前沒有必要來歸檔InnoDB日志文件。這個選項的默認值是0。 ·????????innodb_log_buffer_size InnoDB用來往磁盤上的日志文件寫操作的緩沖區的大小。明智的值是從1MB到8MB。默認的是1MB。一個大的日志緩沖允許大型事務運行而不需要在事務提交之前往磁盤寫日志。因此，如果你有大型事務，使日志緩沖區更大以節約磁盤I/O。 ·????????innodb_log_file_size 在日志組里每個日志文件的大小。在32位計算機上日志文件的合并大小必須少于4GB。默認是5MB。明智的值從1MB到_N_分之一緩沖池大小，其中_N_是組里日志文件的數目。值越大，在緩沖池越少需要檢查點刷新行為，以節約磁盤I/O。但更大的日志文件也意味這在崩潰時恢復得更慢。 ·????????innodb_log_files_in_group 在日志組里日志文件的數目。InnoDB以循環方式寫進文件。默認是2（推薦）。 ·????????innodb_log_group_home_dir 到InnoDB日志文件的目錄路徑。它必須有和innodb_log_arch_dir一樣的值。如果你不指定任何InnoDB日志參數，默認的是在MySQL數據目錄里創建兩個5MB大小名為ib_logfile0和ib_logfile1的文件。 ·????????innodb_max_dirty_pages_pct 這是一個范圍從0到100的整數。默認是90。InnoDB中的主線程試著從緩沖池寫頁面，使得臟頁（沒有被寫的頁面）的百分比不超過這個值。如果你有SUPER權限，這個百分比可以在服務器運行時按下面來改變： SET GLOBAL innodb_max_dirty_pages_pct = value; ·????????innodb_max_purge_lag 這個選項控制在凈化操作被滯后之時，如何延遲INSERT, UPDATE和DELETE操作。（請參閱[15.2.12節，“多版本的實施”](# "15.2.12.?Implementation of Multi-Versioning")）。這個參數的默認值是零，意為無延遲。這個選項可以在運行時作為全局系統變量而被改變。 InnoDB事務系統維持一個事務列表，該列表有被UPDATE或DELETE操作標志為刪除的索引記錄。讓這個列表的長度為_purge_lag_。當_purge_lag_超過innodb_max_purge_lag之時，每個INSERT, UPDATE和DELETE操作延遲 ((_purge_lag_/innodb_max_purge_lag)*10)-5毫秒。在凈化批處理的開始，延遲每隔10秒計算。如果因為一個舊的可以看到行被凈化的一致的讀查看，刪除操作不被延遲。對有問題的工作量，典型設置可能是1百萬，假設我們的事務很小，只有100字節大小，我們就可以允許在我們的表之中有100MB未凈化的行。 ·????????innodb_mirrored_log_groups 我們為數據庫保持的日志組內同樣拷貝的數量。當前這個值應該被設為1。 ·????????innodb_open_files 在InnoDB中，這個選項僅與你使用多表空間時有關。它指定InnoDB一次可以保持打開的.ibd文件的最大數目。最小值是10。默認值300。對.ibd文件的文件描述符是僅對InnoDB的。它們獨立于那些由--open-files-limit服務器選項指定的描述符，且不影響表緩存的操作。 ·????????innodb_status_file 這個選項讓InnoDB為周期的SHOW INNODB STATUS輸出創建一個文件_<datadir>_/innodb_status._<pid>_。 ·????????innodb_support_xa 當被設置為ON或者1（默認地），這個變量允許InnoDB支持在XA事務中的雙向提交。允許innodb_support_xa導致一個額外的對事務準備的磁盤刷新。如果你對使用XA并不關心，你可以通過設置這個選項為OFF或0來禁止這個變量，以減少磁盤刷新的次數并獲得更好的InnoDB性能。 ·????????innodb_table_locks InnoDB重視LOCK TABLES，直到所有其它線程已經釋放他們所有對表的鎖定，MySQL才從LOCK TABLE .. WRITE返回。默認值是1，這意為LOCK TABLES讓InnoDB內部鎖定一個表。在使用AUTOCOMMIT=1的應用里，InnoDB的內部表鎖定會導致死鎖。你可以在my.cnf文件（Windows上是my.ini文件）里設置innodb_table_locks=0 來消除這個問題。 ·????????innodb_thread_concurrency InnoDB試著在InnoDB內保持操作系統線程的數量少于或等于這個參數給出的限制。如果有性能問題，并且SHOW INNODB STATUS顯示許多線程在等待信號，可以讓線程“thrashing” ，并且設置這個參數更小或更大。如果你的計算機有多個處理器和磁盤，你可以試著這個值更大以更好地利用計算機的資源。一個推薦的值是系統上處理器和磁盤的個數之和。值為500或比500大會禁止調用并發檢查。默認值是20，并且如果設置大于或等于20，并發檢查將被禁止。 ·????????innodb_status_file 這個選項讓InnoDB為周期的SHOW INNODB STATUS輸出創建一個文件_<datadir>_/innodb_status._<pid>_。 ### 15.2.5.?創建InnoDB表空間 [15.2.5.1. 處理InnoDB初始化問題](#) 假設你已經安裝了MySQL，并且已經編輯了選項文件，使得它包含必要的InnoDB配置參數。在啟動MySQL之前，你應該驗證你為InnoDB數據文件和日志文件指定的目錄是否存在，并且MySQL有訪問這些目錄的權限。InnoDB不能創建目錄，只能創建文件。也檢查你有足夠的空間來放數據和日志文件。當創建InnoDB數據庫時，最好從命令提示符運行MySQL服務器**mysqld**，而不要從**mysqld_safe**包裝或作為Windows的服務來運行。當你從命令提示符運行，你可看見**mysqld**打印什么以及發生了什么。在Unix上，只需要調用**mysqld**。在Windows上，使用--console選項。當在選項文件里初始地配置InnoDB后，開始啟動MySQL服務器之時，InnoDB創建一個數據文件和日志文件。InnoDB打印如下一些東西： InnoDB: The first specified datafile /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: datafile /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created InnoDB: Started mysqld: ready for connections 一個新的InnoDB數據庫被創建了。你可以用**mysql**這樣通常的MySQL客戶端程序連接到MySQL服務器。當你用**mysqladmin shutdown**關閉MySQL服務器之時，輸出類似如下： 010321 18:33:34? mysqld: Normal shutdown 010321 18:33:34? mysqld: Shutdown Complete InnoDB: Starting shutdown... InnoDB: Shutdown completed 你可以看數據文件和日志文件，并且你可以看見文件被創建。日志目錄也包含一個名為ib_arch_log_0000000000的小文件。這個文件是數據庫被創建的結果，數據庫被創建之后InnoDB切斷日志歸檔。當MySQL再次啟動之時，數據文件＆日志文件已經被創建，所以輸出更簡潔： InnoDB: Started mysqld: ready for connections 你可以添加innodb_file_per_table選項到my.cnf文件，并且讓InnoDB存儲每一個表到MySQL數據庫目錄里自己的.ibd文件。請參閱[15.2.6.6節，“使用Per-Table表空間”](# "15.2.6.6.?Using Per-Table Tablespaces")。 #### 15.2.5.1.?處理InnoDB初始化問題如果InnoDB在一個文件操作中打印一個操作系統錯誤，通常問題是如下中的一個： ·????????你沒有創建一個InnoDB數據文件目錄或InnoDB日志目錄。 ·????????**mysqld**沒有訪問這些目錄的權限以創建文件。 ·????????**mysqld**不能恰當地讀取my.cnf或my.ini選項文件，因此不能看到你指定的選項。 ·????????磁盤已滿，或者超出磁盤配額。 ·????????你已經創建一個子目錄，它的名字與你指定的數據文件相同。 ·????????在innodb_data_home_dir或innodb_data_file_path有一個語法錯誤。當InnoDB試著初始化它的表空間或日志文件之時，如果出錯了，你應該刪除InnoDB創建的所有文件。這意味著是所有ibdata文件和所有ib_logfiles文件。萬一你創建了一些InnoDB表，為這些表也從MySQL數據庫目錄刪除相應的.frm文件(如果你使用多重表空間的話，也刪除任何.ibd文件）。然后你可以試著再次創建InnoDB數據庫。最好是從命令提示符啟動MySQL服務器，以便你可以查看發生了什么。 ### 15.2.6.?創建InnoDB表 [15.2.6.1. 如何在InnoDB用不同API來使用事務](#) [15.2.6.2. 轉換MyISAM表到InnoDB](#) [15.2.6.3. AUTO_INCREMENT列如何在InnoDB中工作](#) [15.2.6.4.外鍵約束](#) [15.2.6.5. InnoDB和MySQL復制](#) [15.2.6.6. 使用Per-Table表空間](#) 假如你用mysql test命令啟動MySQL客戶端。要創建一個InnoDB表，你必須在表創建SQL語句中指定ENGINE = InnoDB或者TYPE = InnoDB選項： CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB; CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB; SQL語句在表空間的列上創建一個表和索引，表空間包含你在my.cnf指定的數據文件。此外，MySQL在MySQL數據庫目錄下的test目錄里創建一個名為customers.frm的文件。內部地，InnoDB為'test/customers'表往自己的數據目錄添加一個條目。這意味這你可以在其它數據庫創建一個具有相同名字customers的表，表的名字不會與InnoDB內的沖突。你可以對任何InnoDB表，通過使用SHOW TABLE STATUS語句，查詢在InnoDB表空間內空閑空間的數量。表空間內空閑空間的數量出現在SHOW TABLE STATUS的輸出結果內的Comment節里。例如： SHOW TABLE STATUS FROM test LIKE 'customers' 注意，統計的SHOW只給出關于InnoDB表的大概情況。它們被用于SQL優化。可是，表和索引保留的大小，以字節為單位是準確的。 #### 15.2.6.1.?如何在InnoDB中用不同的API來使用事務默認地，每個連接到MySQL服務器的客戶端開始之時是允許自動提交模式的，這個模式自動提交你運行的每個SQL語句。要使用多語句事務，你可以用SQL語句SET AUTOCOMMIT = 0禁止自動提交，并且用COMMIT和ROLLBACK來提交或回滾你的事務。如果你想要autocommit保持打開狀態，可以在START TRANSACTION與COMMIT或ROLLBACK之間封裝你的事務。下列的例子演示兩個事務。第一個是被提交的，第二個是被回滾的： shell> mysql test Welcome to the MySQL monitor.? Commands end with ; or \g. Your MySQL connection id is 5 to server version: 3.23.50-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) ??? -> ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki'); Query OK, 1 row affected (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO CUSTOMER VALUES (15, 'John'); Query OK, 1 row affected (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM CUSTOMER; +------+--------+ | A??? | B????? | +------+--------+ |?? 10 | Heikki | +------+--------+ 1 row in set (0.00 sec) mysql> 在類似PHP, Perl DBI/DBD, JDBC, ODBC, 或者MySQL的標準C調用接口這樣的API上，你能夠以字符串形式發送事務控制語句，如COMMIT，到MySQL服務器，就像其它任何的SQL語句那樣，諸如SELECT或INSERT。一些API也提供單獨的專門的事務提交和回滾函數或者方法。 #### 15.2.6.2.?轉換MyISAM表到InnoDB 要點：你不應該在mysql數據庫（比如，user或者host）里把MySQL系統表轉換為InnoDB類型。系統表總是MyISAM型。如果你想要所有（非系統）表都被創建成InnoDB表，你可以簡單地把default-table-type=innodb行添加到my.cnf或my.ini文件的[mysqld]節里。 InnoDB對MyISAM存儲引擎采用的單獨索引創建方法沒有做專門的優化。因此，它不值得導出或導入表以及隨后創建索引。改變一個表為InnoDB型最快的辦法就是直接插入進一個InnoDB表。即，使用ALTER TABLE ... ENGINE=INNODB，或用相同的定義創建一個空InnoDB表，并且用INSERT INTO ... SELECT * FROM ...插入行。如果你對第二個鍵有UNIQUE約