15. 存儲引擎和表類型 · MySQL 5.1參考手冊

### 第15章：存儲引擎和表類型 **目錄** [15.1. MyISAM存儲引擎](#) [15.1.1. MyISAM啟動選項](#) [15.1.2.鍵所需的空間](#) [15.1.3. MyISAM表的存儲格式](#) [15.1.4. MyISAM表的問題](#) [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數據庫的備份和恢復](#) [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.3. MERGE存儲引擎](#) [15.3.1. MERGE表方面的問題](#) [15.4. MEMORY(HEAP)存儲引擎](#) [15.5. BDB(BerkeleyDB)存儲引擎](#) [15.5.1. BDB支持的操作系統](#) [15.5.2. 安裝BDB](#) [15.5.3. BDB啟動選項](#) [15.5.4. BDB表的特性](#) [15.5.5. 修改BDB所需的事宜](#) [15.5.6. 對BDB表的限制](#) [15.5.7. 使用BDB表時可能出現的錯誤](#) [15.6. EXAMPLE存儲引擎](#) [15.7. FEDERATED存儲引擎](#) [15.7.1. 安裝FEDERATED存儲引擎](#) [15.7.2. FEDERATED存儲引擎的介紹](#) [15.7.3. 如何使用FEDERATED表](#) [15.7.4. FEDERATED存儲引擎的局限](#) [15.8. ARCHIVE存儲引擎](#) [15.9. CSV存儲引擎](#) [15.10.BLACKHOLE存儲引擎](#) 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約束，你可以在導入階段設置：SET UNIQUE_CHECKS=0，以臨時關掉唯一性檢查好加速表的導入。對于大表，這節省了大量的磁盤I/O，因為InnoDB隨后可以使用它的插入緩沖區來第二個索引記錄作為一批來寫入。為獲得對插入進程的更好控制，分段插入大表可能比較好： ~~~ INSERT INTO newtable SELECT * FROM oldtable ~~~ ~~~ ?? WHERE yourkey > something AND yourkey <= somethingelse; ~~~ 所有記錄已經本插入之后，你可以重命名表。在大表的轉換中，你應該增加InnoDB緩沖池的大小來減少磁盤I/O。盡管如此，不要使用超過80%的內部內存。你也可以增加InnoDB日志文件和日志文件的大小。確信你沒有填滿表空間：InnoDB表比MyISAM表需要大得多的磁盤空間。如果一個ALTER TABLE耗盡了空間，它就開始一個回滾，并且如果它是磁盤綁定的，回滾可能要幾個小時。對于插入，InnoDB使用插入緩沖區來以成批地合并第二個索引記錄到索引中。那樣節省了大量磁盤I/O。在回滾中，沒有使用這樣的機制，而回滾要花比插入長30倍的時間來完成。在失控的回滾情況下，如果你在數據庫中沒有有價值的數據，比較明智的是殺掉數據庫進程而不是等幾百萬個磁盤I/O被完成。完整的過程，請參閱[15.2.8.1節，“強制恢復”](# "15.2.8.1.?Forcing Recovery")。 #### 15.2.6.3.?AUTO_INCREMENT列在InnoDB里如何工作如果你為一個表指定AUTO_INCREMENT列，在數據詞典里的InnoDB表句柄包含一個名為自動增長計數器的計數器，它被用在為該列賦新值。自動增長計數器僅被存儲在主內存中，而不是存在磁盤上。 InnoDB使用下列算法來為包含一個名為ai_col的AUTO_INCREMENT列的表T初始化自動增長計數器：服務器啟動之后，當一個用戶對表T做插入之時，InnoDB執行等價如下語句的動作： ~~~ SELECT MAX(ai_col) FROM T FOR UPDATE; ~~~ 語句取回的值逐次加一，并被賦給列和自動增長計數器。如果表是空的，值1被賦予該列。如果自動增長計數器沒有被初始化，而且用戶調用為表T顯示輸出的SHOW TABLE STATUS語句，則計數器被初始化（但不是增加計數）并被存儲以供隨后的插入使用。注意，在這個初始化中，我們對表做一個正常的獨占讀鎖定，這個鎖持續到事務的結束。 InnoDB對為新創建表的初始化自動增長計數器允許同樣的過程。注意，如果用戶在INSERT中為AUTO_INCREMENT列指定NULL或者0，InnoDB處理行，就仿佛值還沒有被指定，且為它生成一個新值。自動增長計數器被初始化之后，如果用戶插入一個明確指定該列值的行，而且該值大于當前計數器值，則計數器被設置為指定列值。如果沒有明確指定一個值，InnoDB給計數器增加一，并且賦新值給該列。當訪問自動增長計數器之時，InnoDB使用專用的表級的AUTO-INC鎖定，該鎖持續到當前SQL語句的結束而不是到業務的結束。引入了專用鎖釋放策略，來為對一個含AUTO_INCREMENT列的表的插入改善部署。兩個事務不能同時對同一表有AUTO-INC鎖定。注意，如果你回滾從計數器獲得數的事務，你可能會在賦給AUTO_INCREMENT列的值的序列中發現間隙。如果用戶給列賦一個賦值，或者，如果值大過可被以指定整數格式存儲的最大整數，自動增長機制的行為不被定義。在CREATE TABLE和ALTER TABLE語句中，InnoDB支持AUTO_INCREMENT = *n* 表選項來設置計數器初始值或變更當前計數器值。因在本節早先討論的原因，這個選項的影響在服務器重啟后就無效了。 #### 15.2.6.4.外鍵約束 InnoDB也支持外鍵約束。InnoDB中對外鍵約束定義的語法看起來如下： ~~~ [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) ~~~ ~~~ ??? REFERENCES tbl_name (index_col_name, ...) ~~~ ~~~ ??? [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] ~~~ ~~~ ??? [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] ~~~ 外鍵定義服從下列情況： ·???????? 所有tables必須是InnoDB型，它們不能是臨時表。 ·????????在引用表中，必須有一個索引，外鍵列以同樣的順序被列在其中作為第一列。這樣一個索引如果不存在，它必須在引用表里被自動創建。 ·????????在引用表中，必須有一個索引，被引用的列以同樣的順序被列在其中作為第一列。 ·????????不支持對外鍵列的索引前綴。這樣的后果之一是BLOB和TEXT列不被包括在一個外鍵中，這是因為對這些列的索引必須總是包含一個前綴長度。 ·????????如果CONSTRAINT*symbol*被給出，它在數據庫里必須是唯一的。如果它沒有被給出，InnoDB自動創建這個名字。 InnoDB拒絕任何試著在子表創建一個外鍵值而不匹配在父表中的候選鍵值的INSERT或UPDATE操作。一個父表有一些匹配的行的子表，InnoDB對任何試圖更新或刪除該父表中候選鍵值的UPDATE或DELETE操作有所動作，這個動作取決于用FOREIGN KEY子句的ON UPDATE和ON DETETE子句指定的*referential action*。當用戶試圖從一個父表刪除或更新一行之時，且在子表中有一個或多個匹配的行，InnoDB根據要采取的動作有五個選擇： ·????????CASCADE: 從父表刪除或更新且自動刪除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在兩個表之間，你不應定義若干在父表或子表中的同一列采取動作的ON UPDATE CASCADE子句。 ·????????SET NULL: 從父表刪除或更新行，并設置子表中的外鍵列為NULL。如果外鍵列沒有指定NOT NULL限定詞，這就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。 ·????????NO ACTION: 在ANSI SQL-92標準中，NO ACTION意味這不采取動作，就是如果有一個相關的外鍵值在被參考的表里，刪除或更新主要鍵值的企圖不被允許進行（Gruber, *掌握SQL*, 2000:181）。 InnoDB拒絕對父表的刪除或更新操作。 ·????????RESTRICT: 拒絕對父表的刪除或更新操作。NO ACTION和RESTRICT都一樣，刪除ON DELETE或ON UPDATE子句。（一些數據庫系統有延期檢查，并且NO ACTION是一個延期檢查。在MySQL中，外鍵約束是被立即檢查的，所以NO ACTION和RESTRICT是同樣的）。 ·????????SET DEFAULT: 這個動作被解析程序識別，但InnoDB拒絕包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定義。當父表中的候選鍵被更新的時候，InnoDB支持同樣選擇。選擇CASCADE，在子表中的外鍵列被設置為父表中候選鍵的新值。以同樣的方式，如果在子表更新的列參考在另一個表中的外鍵，更新級聯。注意，InnoDB支持外鍵在一個表內引用，在這些情況下，子表實際上意味這在表內附屬的記錄。 InnoDB需要對外鍵和被引用鍵的索引以便外鍵檢查可以快速進行且不需要一個表掃描。對外鍵的索引被自動創建。這是相對于一些老版本，在老版本中索引必須明確創建，否則外鍵約束的創建會失敗。在InnoDB內，外鍵里和被引用列里相應的列必須有類似的內部數據類型，以便它們不需類型轉換就可被比較。整數類型的大小和符號必須相同。字符串類型的長度不需要相同。如果你指定一個SET NULL動作，請確認你沒有在子表中宣告該列為為*NOT NULL*。如果MySQL從CREATE TABLE語句報告一個錯誤號1005，并且錯誤信息字符串指向errno 150，這意思是因為一個外鍵約束被不正確形成，表創建失敗。類似地，如果ALTER TABLE失敗，且它指向errno 150，那意味著對已變更的表，外鍵定義會被不正確的形成。你可以使用SHOW INNODB STATUS來顯示一個對服務器上最近的InnoDB外鍵錯誤的詳細解釋。 **注釋：**InnoDB不對那些外鍵或包含NULL列的被引用鍵值檢查外鍵約束。 **對SQL標準的背離：**如果在父表內有數個行，其中有相同的被引用鍵值，然后InnoDB在外鍵檢查中采取動作，就仿佛其它有相同鍵值的父行不存在一樣。例如，如果你已定義一個RESTRICT類型的約束，并且有一個帶數個父行的子行，InnoDB不允許任何對這些父行的刪除。居于對應外鍵約束的索引內的記錄，InnoDB通過深度優先選法施行級聯操作。 **對SQL標準的背離：** 如果ON UPDATE CASCADE或ON UPDATE SET NULL遞歸更新相同的表，之前在級聯過程中該表一被更新過，它就象RESTRICT一樣動作。這意味著你不能使用自引用ON UPDATE CASCADE或者ON UPDATE SET NULL操作。這將阻止級聯更新導致的無限循環。另一方面，一個自引用的ON DELETE SET NULL是有可能的，就像一個自引用ON DELETE CASCADE一樣。級聯操作不可以被嵌套超過15層深。 **對SQL標準的背離：** 類似一般的MySQL，在一個插入，刪除或更新許多行的SQL語句內，InnoDB逐行檢查UNIQUE和FOREIGN KEY約束。按照SQL的標準，默認的行為應被延遲檢查，即約束僅在整個SQL語句被處理之后才被檢查。直到InnoDB實現延遲的約束檢查之前，一些事情是不可能的，比如刪除一個通過外鍵參考到自身的記錄。 **注釋：**當前，觸發器不被級聯外鍵的動作激活。一個通過單列外鍵聯系起父表和子表的簡單例子如下： ~~~ CREATE TABLE parent(id INT NOT NULL, ~~~ ~~~ ??????????????????? PRIMARY KEY (id) ~~~ ~~~ ) TYPE=INNODB; ~~~ ~~~ CREATE TABLE child(id INT, parent_id INT, ~~~ ~~~ ?????????????????? INDEX par_ind (parent_id), ~~~ ~~~ ?????????????????? FOREIGN KEY (parent_id) REFERENCES parent(id) ~~~ ~~~ ???????????????????? ON DELETE CASCADE ~~~ ~~~ ) TYPE=INNODB; ~~~ 如下是一個更復雜的例子，其中一個product_order表對其它兩個表有外鍵。一個外鍵引用一個product表中的雙列索引。另一個引用在customer表中的單行索引： ~~~ CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, ~~~ ~~~ ????????????????????? price DECIMAL, ~~~ ~~~ ????????????????????? PRIMARY KEY(category, id)) TYPE=INNODB; ~~~ ~~~ CREATE TABLE customer (id INT NOT NULL, ~~~ ~~~ ????????????????????? PRIMARY KEY (id)) TYPE=INNODB; ~~~ ~~~ CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, ~~~ ~~~ ????????????????????? product_category INT NOT NULL, ~~~ ~~~ ????????????????????? product_id INT NOT NULL, ~~~ ~~~ ????????????????????? customer_id INT NOT NULL, ~~~ ~~~ ????????????????????? PRIMARY KEY(no), ~~~ ~~~ ????????????????????? INDEX (product_category, product_id), ~~~ ~~~ ????????????????????? FOREIGN KEY (product_category, product_id) ~~~ ~~~ ??????????????????????? REFERENCES product(category, id) ~~~ ~~~ ??????????????????????? ON UPDATE CASCADE ON DELETE RESTRICT, ~~~ ~~~ ????????????????????? INDEX (customer_id), ~~~ ~~~ ????????????????????? FOREIGN KEY (customer_id) ~~~ ~~~ ?? ?????????????????????REFERENCES customer(id)) TYPE=INNODB; ~~~ InnoDB允許你用ALTER TABLE往一個表中添加一個新的外鍵約束： ~~~ ALTER TABLE yourtablename ~~~ ~~~ ??? ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) ~~~ ~~~ ??? REFERENCES tbl_name (index_col_name, ...) ~~~ ~~~ ??? [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] ~~~ ~~~ ??? [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] ~~~ **記住先創建需要的索引**。你也可以用ALTER TABLE往一個表添加一個自引用外鍵約束。 InnoDB也支持使用ALTER TABLE來移除外鍵： ~~~ ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol; ~~~ 當年創建一個外鍵之時，如果FOREIGN KEY子句包括一個CONSTRAINT名字，你可以引用那個名字來移除外鍵。另外，當外鍵被創建之時，fk_symbol值被InnoDB內部保證。當你想要移除一個外鍵之時，要找出標記，請使用SHOW CREATE TABLE語句。例子如下： ~~~ mysql> SHOW CREATE TABLE ibtest11c\G ~~~ ~~~ *************************** 1. row *************************** ~~~ ~~~ ?????? Table: ibtest11c ~~~ ~~~ Create Table: CREATE TABLE `ibtest11c` ( ~~~ ~~~ ? `A` int(11) NOT NULL auto_increment, ~~~ ~~~ ? `D` int(11) NOT NULL default '0', ~~~ ~~~ ? `B` varchar(200) NOT NULL default '', ~~~ ~~~ ? `C` varchar(175) default NULL, ~~~ ~~~ ? PRIMARY KEY? (`A`,`D`,`B`), ~~~ ~~~ ? KEY `B` (`B`,`C`), ~~~ ~~~ ? KEY `C` (`C`), ~~~ ~~~ ? CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`) ~~~ ~~~ REFERENCES `ibtest11a` (`A`, `D`) ~~~ ~~~ ON DELETE CASCADE ON UPDATE CASCADE, ~~~ ~~~ ? CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`) ~~~ ~~~ REFERENCES `ibtest11a` (`B`, `C`) ~~~ ~~~ ON DELETE CASCADE ON UPDATE CASCADE ~~~ ~~~ ) ENGINE=INNODB CHARSET=latin1 ~~~ ~~~ 1 row in set (0.01 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775; ~~~ InnoDB解析程序允許你在FOREIGN KEY ... REFERENCES ...子句中用`（backticks)把表和列名名字圍起來。InnoDB解析程序也考慮到lower_case_table_names系統變量的設置。 InnoDB返回一個表的外鍵定義作為SHOW CREATE TABLE語句輸出的一部分： ~~~ SHOW CREATE TABLE tbl_name; ~~~ 從這個版本起，**mysqldump**也將表的正確定義生成到轉儲文件中，且并不忘記外鍵。你可以如下對一個表顯示外鍵約束： ~~~ SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'; ~~~ 外鍵約束被列在輸出的Comment列。當執行外鍵檢查之時，InnoDB對它照看著的子或父記錄設置共享的行級鎖。InnoDB立即檢查外鍵約束，檢查不對事務提交延遲。要使得對有外鍵關系的表重新載入轉儲文件變得更容易，**mysqldump**自動在轉儲輸出中包括一個語句設置FOREIGN_KEY_CHECKS為0。這避免在轉儲被重新裝載之時，與不得不被以特別順序重新裝載的表相關的問題。也可以手動設置這個變量： ~~~ mysql> SET FOREIGN_KEY_CHECKS = 0; ~~~ ~~~ mysql> SOURCE dump_file_name; ~~~ ~~~ mysql> SET FOREIGN_KEY_CHECKS = 1; ~~~ 如果轉儲文件包含對外鍵是不正確順序的表，這就以任何順序導入該表。這樣也加快導入操作。設置FOREIGN_KEY_CHECKS為0，對于在LOAD DATA和ALTER TABLE操作中忽略外鍵限制也是非常有用的。 InnoDB不允許你刪除一個被FOREIGN KEY表約束引用的表，除非你做設置SET FOREIGN_KEY_CHECKS=0。當你移除一個表的時候，在它的創建語句里定義的約束也被移除。如果你重新創建一個被移除的表，它必須有一個遵從于也引用它的外鍵約束的定義。它必須有正確的列名和類型，并且如前所述，它必須對被引用的鍵有索引。如果這些不被滿足，MySQL返回錯誤號1005 并在錯誤信息字符串中指向errno 150。 #### 15.2.6.5.?InnoDB和MySQL復制 MySQL復制就像對MyISAM表一樣，也對InnoDB表起作用。以某種方式使用復制也是可能的，在這種方式中從服務器上表的類型不同于主服務器上原始表的類型。例如，你可以復制修改到主服務器上一個InnoDB表，到從服務器上一個MyISAM表里。要為一個主服務器建立一個新服務器，你不得不復制InnoDB表空間和日志文件，以及InnoDB表的.frm文件，并且移動復件到從服務器。關于其恰當步驟請參閱[15.2.9節，“移動InnoDB數據庫到另一臺機器”](# "15.2.9.?Moving an InnoDB Database to Another Machine")。如果你可以關閉主服務器或者一個存在的從服務器。你可以采取InnoDB表空間和日志文件的冷備份，并用它來建立一個從服務器。要不關閉任何服務器來建立一個新的從服務器，你也可以使用非免費（商業的）[InnoDB熱備份工具](http://www.innodb.com/order.html)。 InnoDB復制里一個小的限制是LOAD TABLE FROM MASTER不對InnoDB類型表起作用。有兩種可能的工作區： ·????????轉儲主服務器上的表并導入轉儲文件到從服務器。 ·????????在用LOAD TABLE *tbl_name* FROM MASTER建立復制之前，在主服務器上使用ALTER TABLE *tbl_name* TYPE=MyISAM，并且隨后使用ALTER TABLE把主表轉換回InnoDB。在主服務器失敗的事務根本不影響復制。MySQL復制基于二進制日志，MySQ在那里寫修改數據的SQL語句。從服務器讀主服務器的二進制日志，并執行同樣的SQL語句。盡管如此，在事務里發生的語句不被寫進二進制日志直到事務提交，在那一刻，事務里所有語句被一次性寫進日志。如果語句失敗了，比如，因為外鍵違例，或者，如果事務被回滾，沒有SQL語句被寫進二進制日志，并且事務根本不在從服務器上執行。 #### 15.2.6.6.?使用Per-Table表空間你可以存儲每個InnoDB表和它的索引在它自己的文件在中，這個特征被稱為“多表空間” ，因為實際上每個表有它自己的表空間。對那些想把特定表格移到分離物理磁盤的用戶，或者那些希望快速恢復單個表的備份而無須打斷其余InnoDB表的使用的用戶，使用多表空間會是有益的。你可以往my.cnf的[mysqld]節添加下面行來允許多表空間： ~~~ [mysqld] ~~~ ~~~ innodb_file_per_table ~~~ 重啟服務器之后，InnoDB存儲每個新創建的表到表格所屬于的數據庫目錄下它自己的文件*tbl_name*.ibd里。這類似于MyISAM存儲引擎所做的，但MyISAM 把表分成數據文件*tbl_name*.MYD和索引文件*tbl_name*.MYI。對于InnoDB，數據和所以被一起存到.ibd文件。*tbl_name*.frm文件照舊依然被創建。如果你從my.cnf文件刪除innodb_file_per_table行，并重啟服務器，InnoDB在共享的表空間文件里再次創建表。 innodb_file_per_table只影響表的創建。如果你用這個選項啟動服務器，新表被用.ibd文件來創建，但是你仍舊能訪問在共享表空間里的表。如果你刪掉這個選項，新表在共享表空間內創建，但你仍舊可以訪問任何用多表空間創建的表。 InnoDB總是需要共享標空間。.ibd文件對InnoDB不足以去運行，共享表空間包含熟悉的ibdata文件，InnoDB把內部數據詞典和未作日志放在這個文件中。 **注釋：**你不能像對MyISAM一樣，在數據目錄之間隨意地移動.ibd文件。這是因為表定義被存在InnoDB共享表空間內，而且InnoDB必須保持事務ID和日志順序號的一致性。在一個給定的MySQL安裝里，你可以用RENAME TABLE語句把.ibd文件和關聯的表從一個數據庫移到另一個數據庫： ~~~ RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name; ~~~ [如果你有]().ibd文件的一個干凈的備份，你可以按如下操作從被起源的地方恢復它到MySQL安裝中： 1.??? 發出這個ALTER TABLE語句： ~~~ 2.??????ALTER TABLE tbl_name DISCARD TABLESPACE; ~~~ **警告****：**這個語句刪除當前.ibd文件。 3.??? 把備份的.ibd文件放回到恰當的數據庫目錄。 4.???發出這個ALTER TABLE語句: ~~~ 5.????? ALTER TABLE tbl_name IMPORT TABLESPACE; ~~~ 在上下文中，一個.ibd文件干凈的備份意為： ·????????.ibd文件里沒有尚未提交的事務做的修改。 ·???????? .ibd文件里無未合并的插入混充條目。 ·????????凈化已經從.ibd文件移除所有已標注刪除的索引記錄。 ·????????**mysqld**已經把.ibd文件的所有已修改頁面從緩沖池刷新到文件。你可以用下列方法生成一個.ibd文件的干凈備份： 1.??? 停止所有來自**mysqld**服務器的活動，并提交所有事務。 2.??? 等待直至SHOW INNODB STATUS顯示在數據庫被已經沒有激活的事務，并且InnoDB主線程的狀態是Waiting for server activity。然后你就可以復制.ibd文件了。生成一個.ibd文件的干凈復制的另一個方法是使用商業的**InnoDB熱備份工具**： 1.??? 使用**InnoDB熱備份工具**備份InnoDB安裝。 2.??? 在備份上啟動第二個**mysqld服務器**，讓它清潔備份里的.ibd文件。 ### 15.2.7.?添加和刪除InnoDB數據和日志文件這一節描述在InnoDB表空間耗盡空間之時，或者你想要改變日志文件大小之時，你可以做的一些事情。最簡單的，增加InnoDB表空間大小的方法是從開始配置它為自動擴展的。為表空間定義里的最后一個數據文件指定autoextend屬性。然后在文件耗盡空間之時，InnoDB以8MB為增量自動增加該文件的大小。增加的大小可以通過設置innodb_autoextend_increment值來配置,這個值以MB為單位，默認的是8。作為替代，你可以通過添加另一個數據文件來增加表空間的尺寸。要這么做的話，你必須停止MySQL服務器，編輯my.cnf文件，添加一個新數據文件到innodb_data_file_path的末尾，然后再次啟動服務器。如果最后一個數據文件是用關鍵字autoextend定義的，編輯my.cnf文件的步驟必須考慮最后一個數據文件已經增長到多大。獲取數據文件的尺寸，把它四舍五入到最接近乘積1024 × 1024bytes (= 1MB)，然后在innodb_data_file_path中明確指定大致的尺寸。然后你可以添加另一個數據文件。記得只有innodb_data_file_path里最后一個數據可以被指定為自動擴展。作為一個例子。假設表空間正好有一個自動擴展文件ibdata1： ~~~ innodb_data_home_dir = ~~~ ~~~ innodb_data_file_path = /ibdata/ibdata1:10M:autoextend ~~~ 假設這個數據文件過一段時間已經長到988MB。下面是添加另一個總擴展數據文件之后的配置行： ~~~ innodb_data_home_dir = ~~~ ~~~ innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend ~~~ 當你添加一個新文件到表空間的之后，請確信它并不存在。當你重啟服務器之時，InnoDB創建并初始化這個文件。當前，你不能從表空間刪除一個數據文件。要增加表空間的大小，使用如下步驟： 1.??? 使用**mysqldump**轉儲所有InnoDB表。 2.??? 停止服務器。 3.??? 刪除所有已存在的表空間文件。 4.??? 配置新表空間。 5.??? 重啟服務器。 6.??? 導入轉儲文件。如果你想要改變你的InnoDB日志文件的數量和大小，你必須要停止MySQL服務器，并確信它被無錯誤地關閉。隨后復制舊日志文件到一個安全的地方以防萬一某樣東西在關閉時出錯而你需要用它們來恢復表空間。從日志文件目錄刪除所有舊日志文件，編輯my.cnf來改變日志文件配置，并再次啟動MySQL服務器。**mysqld**在啟動之時發現沒有日志文件，然后告訴你它正在創建一個新的日志文件。 ### 15.2.8.?備份和恢復InnoDB數據庫 [15.2.8.1. 強制恢復](#) [15.2.8.2. 檢查點](#) 安全數據庫管理的關鍵是定期做備份。 **InnoDB熱備份工具**是一個在線備份工具，你可以用它來在InnoDB數據庫運行之時備份你的InnoDB數據庫。**InnoDB熱備份工具**不要求你關閉數據庫，并且它不設置任何鎖定或干擾你正常的數據庫處理。**InnoDB熱備份工具**是非免費（商業的）附加軟件，它每年的證書費用是每臺MySQL服務器運行的計算機€390。請參閱[**InnoDB**熱備份](http://www.innodb.com/order.html)[主頁](http://www.innodb.com/order.html)以獲得更詳細的信息以及屏幕截圖。如果你可以關閉你的MySQL服務器，你可以生成一個包含InnoDB用來管理它的表的所有文件的二進制備份。使用如下步驟： 1.??? 關閉MySQL服務器，確信它是無錯誤關閉。 2.??復制你所有數據文件（ibdata文件和.ibd文件）到一個安全的地方。? 3.???復制你所有ib_logfile文件到一個安全的地方。 4.??? 復制my.cnf配置文件或文件到一個安全的地方。 5.??? 為你InnoDB表復制.frm文件到一個安全的地方。復制對InnoDB表起作用，所以你可以使用MySQL復制能力來在需要高可用性的數據庫站點保有一份數據庫的復制。除了剛才描述的二進制備份，你也應該周期性地用**mysqldump**轉儲你的數據庫。這么做的原因是，二進制文件可能被破壞而你沒有注意到。轉儲的文件被存儲成為人可讀的文本文件，所以定點表的損壞修復變得更容易。再者，因為形式更簡單，嚴重數據損壞的機會就更小。**mysqldump** 也有一個--single-transaction選項，你可以用它來做一個一致的快照而不用把其它客戶端排除在外面。要能夠從上述的二進制備份恢復InnoDB數據庫到現在，你必須讓二進制日志功能打開正在運行的MySQL服務器。然后你可以應用二進制日志到備份數據庫以實現point-in-time恢復： ~~~ mysqlbinlog yourhostname-bin.123 | mysql ~~~ 要從MySQL服務器的崩潰恢復，唯一需要的是重新啟動它。InnoDB自動檢查日志并執行到現在的數據庫前滾。InnoDB自動回滾在崩潰時呈現的未提交的事務。在恢復過程中，**mysqld**顯示如下一些輸出： ~~~ InnoDB: Database was not shut down normally. ~~~ ~~~ InnoDB: Starting recovery from log files... ~~~ ~~~ InnoDB: Starting log scan based on checkpoint at ~~~ ~~~ InnoDB: log sequence number 0 13674004 ~~~ ~~~ InnoDB: Doing recovery: scanned up to log sequence number 0 13739520 ~~~ ~~~ InnoDB: Doing recovery: scanned up to log sequence number 0 13805056 ~~~ ~~~ InnoDB: Doing recovery: scanned up to log sequence number 0 13870592 ~~~ ~~~ InnoDB: Doing recovery: scanned up to log sequence number 0 13936128 ~~~ ~~~ ... ~~~ ~~~ InnoDB: Doing recovery: scanned up to log sequence number 0 20555264 ~~~ ~~~ InnoDB: Doing recovery: scanned up to log sequence number 0 20620800 ~~~ ~~~ InnoDB: Doing recovery: scanned up to log sequence number 0 20664692 ~~~ ~~~ InnoDB: 1 uncommitted transaction(s) which must be rolled back ~~~ ~~~ InnoDB: Starting rollback of uncommitted transactions ~~~ ~~~ InnoDB: Rolling back trx no 16745 ~~~ ~~~ InnoDB: Rolling back of trx no 16745 completed ~~~ ~~~ InnoDB: Rollback of uncommitted transactions completed ~~~ ~~~ InnoDB: Starting an apply batch of log records to the database... ~~~ ~~~ InnoDB: Apply batch completed ~~~ ~~~ InnoDB: Started ~~~ ~~~ mysqld: ready for connections ~~~ 如果數據庫被損壞或磁盤出錯，你必須從備份做恢復。在損壞的情況下，你首先應該找出一個沒有被損壞的備份。恢復數據庫備份之后，從二進制日志文件恢復。在一些數據庫損壞的情況下，僅僅轉儲，移除并重建一個或數個被破壞的表是足夠的。你可以用CHECK TABLE SQL語句來檢查表是否損壞，雖然CHECK TABLE正常地不檢查每種可能的損壞，你可以使用innodb_tablespace_monitor來檢查表空間文件內文件空間管理的完整性。在一些情況下，明顯地數據庫損壞是因為操作系統損壞它自己的文件緩存，磁盤上的數據可能完好，最好是首先重啟計算機。它可以消除那些顯得是數據庫頁損壞的錯誤。 #### 15.2.8.1.?強制恢復如果數據庫頁被破壞，你可能想要用SELECT INTO OUTFILE從從數據庫轉儲你的表，通常以這種方法獲取的大多數數據是完好的。即使這樣，損壞可能導致SELECT * FROM *tbl_name*或者InnoDB后臺操作崩潰或斷言，或者甚至使得InnoDB前滾恢復崩潰。盡管如此，你可以用它來強制InnoDB存儲引擎啟動同時阻止后臺操作運行，以便你能轉儲你的表。例如：你可以在重啟服務器之前，在選項文件的[mysqld]節添加如下的行： ~~~ [mysqld] ~~~ ~~~ innodb_force_recovery = 4 ~~~ innodb_force_recovery被允許的非零值如下。一個更大的數字包含所有更小數字的預防措施。如果你能夠用一個多數是4的選項值來轉儲你的表，那么你是比較安全的，只有一些在損壞的單獨頁面上的數據會丟失。一個為6的值更夸張，因為數據庫頁被留在一個陳舊的狀態，這個狀態反過來可以引發對B樹和其它數據庫結構的更多破壞。 ·????????1 (SRV_FORCE_IGNORE_CORRUPT) 即使服務器檢測到一個損壞的頁，也讓服務器運行著；試著讓SELECT * FROM *tbl_name* 跳過損壞的索引記錄和頁，這樣有助于轉儲表。 ·????????2 (SRV_FORCE_NO_BACKGROUND) 阻止主線程運行，如果崩潰可能在凈化操作過程中發生，這將阻止它。 ·????????3 (SRV_FORCE_NO_TRX_UNDO) 恢復后不運行事務回滾。 ·????????4 (SRV_FORCE_NO_IBUF_MERGE) 也阻止插入緩沖合并操作。如果你可能會導致一個崩潰。最好不要做這些操作，不要計算表統計表。 ·????????5 (SRV_FORCE_NO_UNDO_LOG_SCAN) 啟動數據庫之時不查看未完成日志：InnoDB把未完成的事務視為已提交的。 ·????????6 (SRV_FORCE_NO_LOG_REDO) 不要在恢復連接中做日志前滾。 *數據庫不能另外地帶著這些選項中被允許的選項來使用。*作為一個安全措施，當innodb_force_recovery被設置為大于0的值時，InnoDB阻止用戶執行INSERT, UPDATE或DELETE操作. 即使強制恢復被使用，你也可以DROP或CREATE表。如果你知道一個給定的表正在導致回滾崩潰，你可以移除它。你也可以用這個來停止由失敗的大宗導入或失敗的ALTER TABLE導致的失控回滾。你可以殺掉**mysqld**進程，然后設置innodb_force_recovery為3，使得數據庫被掛起而不需要回滾，然后舍棄導致失控回滾的表。 #### 15.2.8.2.?檢查點 InnoDB實現一種被認識為“模糊”檢查點設置的檢查點機制。InnoDB以小批量從緩沖池刷新已修改的數據庫頁。沒必要以單個批次刷新緩沖池，單批次刷新實際操作中可能會在檢查點設置進程中停止用戶SQL語句的處理。在崩潰恢復中，InnoDB找尋被寫進日志的檢查點標簽。它知道所有在該標簽之前對數據庫的修改被呈現在數據庫的磁盤映像中。然后InnoDB從檢查點往前掃描日志文件，對數據庫應用已寫入日志的修改。 InnoDB以循環方式寫日志文件。所有使得緩沖池里的數據庫頁與磁盤上的映像不同的已提交修改必須出現在日志文件中，以備萬一InnoDB需要做一個恢復。這意味著，當InnoDB開始重新使用一個日志文件，它需要確認在磁盤上的數據庫頁映像包含已寫進InnoDB準備重新使用的日志文件里的修改。換句話說，InnoDB必須創建一個檢查點，這經常涉及已修改數據庫頁到磁盤的刷新。前面的敘述解釋了為什么使你的日志文件非常大會在設置檢查點中節約磁盤I/O。設置日志文件總的大小和緩沖池一樣大或者甚至比緩沖池大通常是有意義的。大日志文件的缺點是崩潰恢復要花更長的時間，因為有更多寫入日志的信息要應用到數據庫上。 ### 15.2.9.?把一個InnoDB數據庫移到另一臺機器在Windows上， InnoDB 總是在內部以小寫名字的方式存儲數據庫和表。要從Unix把二進制格式的數據庫移到Windows，或者從Windows移到Unix，你應該讓所有表和數據庫的名字小寫。要實現這個，一個方便的方式是在創建任何數據庫和表之前，在你的my.cnf或my.ini文件的[mysqld]節內添加如下行： ~~~ [mysqld] ~~~ ~~~ lower_case_table_names=1 ~~~ 類似于MyISAM數據文件，InnoDB數據和日志文件在所有有相同浮點數格式的平臺上是二進制兼容的。你可以拷貝所有列在[15.2.8節，“InnoDB數據庫的備份和恢復”](# "15.2.8.?Backing Up and Recovering an InnoDB Database")里的相關文件來簡單地移動一個InnoDB數據庫。如果浮點格式不同，但你沒有在表中使用FLOAT或DOUBLE數據類型，則過程是一樣：簡單地拷貝相關文件。如果格式不容，且你的表包含浮點數據，你必須使用**mysqldump**在一臺機器轉儲你的表，然后在另一臺機器導入轉儲文件。假設表空間有足夠的空間供導入事務產生的大型回滾片斷使用，則提高性能的一個方法是在導入數據時關掉autocommit模式。僅在導入整個表或表的一個片斷之后提交。 ### 15.2.10.?InnoDB事務模型和鎖定 [15.2.10.1. InnoDB鎖定模式](#) [15.2.10.2. InnoDB和AUTOCOMMIT](#) [15.2.10.3. InnoDB和TRANSACTION ISOLATION LEVEL](#) [15.2.10.4. 持續非鎖定讀](#) [15.2.10.5. 鎖定讀SELECT ... FOR UPDATE和SELECT ... LOCK IN SHAREMODE](#) [15.2.10.6. Next-Key鎖定：避免匪夷所思的問題](#) [15.2.10.7. 持續讀如何在](#)[InnoDB中作用的例子](#) [15.2.10.8. 在](#)[InnoDB中用不同的SQL語句設定鎖](#) [15.2.10.9.MySQL什么時候提交或回滾一個事務？](#) [15.2.10.10. 死鎖檢測＆回滾](#) [15.2.10.11. 如何應對死鎖](#) 在InnoDB事務模式中，目的是把多版本數據庫的最好特性與傳統的二相鎖定合并起來。InnoDB以Oracle的風格，對行級進行鎖定，并且默認運行查詢作為非鎖定持續讀。在InnoDB中鎖定的表被存儲得如此節省空間，以至于不需要鎖定增大：典型地，數個用戶被允許在數據庫中鎖定每一行，或者行的任何隨機子集，而InnoDB不會耗盡內存。 #### 15.2.10.1.?InnoDB鎖定模式 InnoDB實現標準行級鎖定，在這里有兩種類型的鎖： locks: ·????????共享的(*S*)鎖允許一個事務去讀一行（tuple）。 ·????????獨占的鎖(*X*)允許一個事務更新或刪除一行。如果事務*A* 在tuple *t*上持有獨占鎖定，來自不同事務*B*的對*t*上任一類型的鎖的請求不被馬上許可，取而代之地，事務*B* 不得不等待事務t釋放在tuple *t*上的鎖。如果事務 *A* 在tuple *t*上持有一個共享的鎖(*S*)，那么 ·????????來自不同的事務*B*對在*t*?上*X*的鎖定請求不能被馬上許可。 ·????????來自不同的事務*B*對在*t* 上*S*的鎖定請求可以被馬上獲準。因此*A*和*B*持有*t*上的*S*鎖定。不僅如此，InnoDB支持*多間隔尺寸鎖定*，它允許記錄鎖和對整個表的鎖共存。要使得多間隔尺寸級別的鎖定實際化，額外類型的鎖，被稱為*intention locks*被使用。在InnoDB中，意圖鎖定是表鎖定。對于一個事務，意圖鎖定之后理想的是指明在該表中對一個行隨后需要哪一類型的鎖定（共享還是獨占）。有兩種意圖鎖被用在InnoDB中（假設事務*T* 在表*R*中要求一個已指出的類型的鎖）： ·????????意圖共享(*IS*)：事務*T* 意圖給表*T*上單獨的tuple設置*S* 鎖定。 ·????????意圖獨占(*IX*)：事務*T* 意圖給這些tuple設置*X*?鎖定。意圖鎖協議如下： ·????????在假設的事務可以獲得對某假定行的*S* 鎖定之前，它必須首先獲得對包含該行的表的一個*IS* 或者更強的鎖定。 ·????????在假設的事務可以獲得對某假定行的*X* 鎖定之前，它必須首先獲得對包含該行的表的一個*IX* 鎖定。這些結果可以方便地用一個鎖類型兼容矩陣來總結： <table border="1" cellpadding="0"><colgroup><col/><col/><col/><col/><col/></colgroup><tbody><tr><td> ? </td> <td> X </td> <td> IX </td> <td> S </td> <td> IS </td> </tr><tr><td> X </td> <td> 沖突 </td> <td> 沖突 </td> <td> 沖突 </td> <td>沖突</td> </tr><tr><td> IX </td> <td> 沖突 </td> <td> 兼容 </td> <td> 沖突 </td> <td> 兼容 </td> </tr><tr><td> S </td> <td> 沖突 </td> <td> 沖突 </td> <td> 兼容 </td> <td> 兼容 </td> </tr><tr><td> IS </td> <td> 沖突 </td> <td> 兼容 </td> <td> 兼容 </td> <td> 兼容 </td> </tr></tbody></table> 如果一個鎖定與現在鎖定兼容的話，它被授給一個委托事務。如果一個鎖定與現存鎖定沖突，它就不被授予一個委托事務。事務等待著直到沖突的現存鎖定被釋放掉。如果一個鎖定請求與現存鎖定相沖突，且不能被授予，因為它可能會導致死鎖，一個錯誤產生。因此，意圖鎖定不阻礙任何東西，除了完全表請求（比如LOCK TABLES ... WRITE）。*IX* 和*IS*鎖定的主要目的是顯示某人正鎖定一行，或將要在表中鎖定一行。下列的例子演示當鎖定請求可能會導致死鎖之時一個錯誤會如何發生。例子中包括兩個客戶端A和B。首先客戶端A創建一個包含一個行的表，然后開始一個事務。在這個事務內，A通過在共享模式選擇行獲得對行的*S* 鎖定： ~~~ mysql> CREATE TABLE t (i INT) ENGINE = InnoDB; ~~~ ~~~ Query OK, 0 rows affected (1.07 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> INSERT INTO t (i) VALUES(1); ~~~ ~~~ Query OK, 1 row affected (0.09 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> START TRANSACTION; ~~~ ~~~ Query OK, 0 rows affected (0.00 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; ~~~ ~~~ +------+ ~~~ ~~~ | i??? | ~~~ ~~~ +------+ ~~~ ~~~ |??? 1 | ~~~ ~~~ +------+ ~~~ ~~~ 1 row in set (0.10 sec) ~~~ 接著，客戶端B開始一個事務并嘗試從該表刪除行： ~~~ mysql> START TRANSACTION; ~~~ ~~~ Query OK, 0 rows affected (0.00 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> DELETE FROM t WHERE i = 1; ~~~ 刪除操作要求一個*X* 鎖定。因為這個鎖定不兼容客戶端A持有的*S*鎖定，所以*X* 鎖定不被允許，所以請求進入對行及客戶端阻擋的鎖定請求隊列。最后，客戶端A也試圖從表中刪除該行： ~~~ mysql> DELETE FROM t WHERE i = 1; ~~~ ~~~ ERROR 1213 (40001): Deadlock found when trying to get lock; ~~~ ~~~ try restarting transaction ~~~ 因為客戶端A需要一個*X* 鎖定來刪除該行，所以在這里發生死鎖。盡管如此，鎖定請求不被允許，因為客戶端B已經有一個對*X*鎖定的請求并且它正等待客戶端A釋放*S*鎖定。因為客戶端B之前對*X* 鎖定的請求，被客戶端A持有的*S*鎖定也不能升級到*X*鎖定。因此，InnoDB對客戶端A產生一個錯誤，并且釋放它的鎖定。在那一點上，客戶端B的鎖定請求可以被許可，并且客戶端B從表中刪除行。 #### 15.2.10.2.?InnoDB和 AUTOCOMMIT 在InnoDB中，所有用戶行為都在事務內發生。如果自動提交模式被允許，每個SQL語句在它自己上形成一個單獨的事務。MySQL總是帶著允許自動提交來開始一個新連接。如果自動提交模式被用SET AUTOCOMMIT = 0關閉，那么我們可以認為一個用戶總是有一個事務打開著。一個SQL COMMIT或ROLLBACK語句結束當前事務并且一個新事務開始。兩個語句都釋放所有在當前事務中被設置的InnoDB鎖定。一個COMMIT語句意味著在當前事務中做的改變被生成為永久的，并且變成其它用戶可見的。一個ROLLBACK語句，在另一方面，撤銷所有當前事務做的修改。如果連接有被允許的自動提交，通過用明確的START TRANSACTION或BEGIN語句來開始一個事務，并用COMMIT或者ROLLBACK語句來結束它，這樣用戶仍舊可以執行一個多重語句事務。 #### 15.2.10.3.?InnoDB和TRANSACTION ISOLATION LEVEL 按照SQL:1992 事務隔離級別，InnoDB默認是可重復讀的（REPEATABLE READ）。MySQL/InnoDB 提供SQL標準所描述的所有四個事務隔離級別。你可以在命令行用--transaction-isolation選項，或在選項文件里，為所有連接設置默認隔離級別。例如，你可以在my.inf文件的[mysqld]節里類似如下設置該選項：globally ~~~ [mysqld] ~~~ ~~~ transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED ~~~ ~~~ ???????????????????????? | REPEATABLE-READ | SERIALIZABLE} ~~~ 用戶可以用SET TRANSACTION語句改變單個會話或者所有新進連接的隔離級別。它的語法如下： ~~~ SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL ~~~ ~~~ ?????????????????????? {READ UNCOMMITTED | READ COMMITTED ~~~ ~~~ ??????????????????????? | REPEATABLE READ | SERIALIZABLE} ~~~ 注意，對--transaction-isolation選項的級別名中有連字符，但在對SET TRANSACTION語句的級別名中沒有。默認的行為是為下一個（未開始）事務設置隔離級別。如果你使用GLOBAL關鍵字，語句在全局對從那點開始創建的所有新連接（除了不存在的連接）設置默認事務級別。你需要SUPER全縣來做這個。使用SESSION 關鍵字集為將來在當前連接上執行的事務設置默認事務級別。任何客戶端都能自由改變會話隔離級別（甚至在事務的中間），或者為下一個事務設置隔離級別。你可以用下列語句查詢全局和會話事務隔離級別： ~~~ SELECT @@global.tx_isolation; ~~~ ~~~ SELECT @@tx_isolation; ~~~ 在行級鎖定中，InnoDB使用next-key鎖定。這意味著除了索引記錄，InnoDB也可以把索引記錄前的間隙鎖定到其它用戶所做的緊接該索引記錄之前的塊插入上。一個next-key鎖定指向一個鎖定住一個索引記錄和它之前的間隙的鎖定。一個間隙鎖定指僅鎖住一些索引記錄之前的間隙的鎖定。 InnoDB中每個隔離級別的詳細描述如下： ·????????READ UNCOMMITTED SELECT語句以非鎖定方式被執行，但是一個可能更早期版本的記錄會被用到。因此，使用這個隔離級別，比如，讀是不連貫的。著也被稱為“臟讀”（dirty read）。另外，這個隔離級別象READ COMMITTED一樣作用。 ·????????READ COMMITTED 一個有些象Oracle的隔離級別。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MOD語句僅鎖定索引記錄，而不鎖定記錄前的間隙，因而允許隨意緊挨著已鎖定的記錄插入新記錄。UPDATE和DELETE語句使用一個帶唯一搜索條件的唯一的索引僅鎖定找到的索引記錄，而不包括記錄前的間隙。在范圍類型UPDATE和DELETE語句，InnoDB必須對范圍覆蓋的間隙設置next-key鎖定或間隙鎖定以及其它用戶做的塊插入。這是很必要的，因為要讓MySQL復制和恢復起作用，“幽靈行”必須被阻止掉。持續讀行為如同在Oracle中：即使在同一事務內，每個持續讀設置并讀取它自己的新快照。請參閱[15.2.10.4節，“持續非鎖定讀”](# "15.2.10.4.?Consistent Non-Locking Read")。 ·????????REPEATABLE READ 這是InnoDB的默認隔離級別。帶唯一搜索條件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE語句只鎖定找到的索引記錄，而不鎖定記錄前的間隙。用其它搜索條件，這些操作采用next-key鎖定，用next-key鎖定或者間隙鎖定鎖住搜索的索引范圍，并且阻止其它用戶的新插入。在持續讀中，有一個與之前隔離級別重要的差別：在這個級別，在同一事務內所有持續讀讀取由第一次讀所確定的同一快照。這個慣例意味著如果你在同一事務內發出數個無格式SELECT語句，這些SELECT語句對相互之間也是持續的，請參閱[15.2.10.4節，“持續非鎖定讀”](# "15.2.10.4.?Consistent Non-Locking Read")。 ·????????SERIALIZABLE 這個級別類似REPEATABLE READ，但是所有無格式SELECT語句被隱式轉換成SELECT ... LOCK IN SHARE MODE。 #### 15.2.10.4. 持續非鎖定讀持續讀意味著InnoDB使用它的多版本化來給一個查詢展示某個時間點處數據庫的快照。查詢看到在那個時間點之前被提交的那些確切事務做的更改，并且沒有其后的事務或未提交事務做的改變。這個規則的例外是，查詢看到發布該查詢的事務本身所做的改變。如果你運行在默認的REPEATABLE READ隔離級別，則在同一事務內的所有持續讀讀取由該事務中第一個這樣的讀所確立的快照。你可以通過提交當前事務并在發布新查詢的事務之后，為你的查詢獲得一個更新鮮的快照。持續讀是默認模式，在其中InnoDBzai在READ COMMITTED和REPEATABLE READ隔離級別處理SELECT語句。持續讀不在任何它訪問的表上設置鎖定，因此，其它用戶可自由地在持續讀在一個表上執行的同一時間修改這些表。注意，持續讀不在DROP TABLE和ALTER TABLE上作用。持續讀不在DROP TABLE上作用，因為MySQL不能使用已經被移除的表，并且InnoDB 破壞了該表。持續讀不在ALTER TABLE上作用，因為它在某事務內執行，該事務創建一個新表，并且從舊表往新表中插入行。現在，當你重新發出持續讀之時，它不能在新表中看見任何行，因為它們被插入到一個在持續讀讀取的快照中不可見的事務里。 #### 15.2.10.5. 鎖定讀SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE 在一些環境中，一個持續讀是不方便的。比如，你可能想要往表的子表里添加一個新行，并確信該子表在父表中有一個根。下列例子顯示如何在你應用程序代碼中實現參考的完整性。假設你使用一個持續讀去讀取父表并且看到表中子表的根。不能安全地往子表添加子行嗎？不，因為可能同時發生一些其它用戶從父表刪除父行，而你沒有注意到它的情況。解決辦法是在使用LOCK IN SHARE MODE的鎖定模式執行SELECT： ~~~ SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE; ~~~ 在共享模式執行一個讀意味著我們讀最新的可用數據，并在我們讀的行設置一個共享鎖定。共享模式鎖防止其它人更新或刪除我們已讀的行。同時，如果最新的數據屬于其它客戶端尚未提交的事務，我們等著知道那個事務被提交。我們看到前述的查詢返回父'Jones'，我們可以安全地往子表添加子記錄并提交我們的事務。讓我們看另外一個例子：我們在表child_codes 中有一個整數計數器域，我們用該表給每個添加到子表里的子項指派一個唯一的識別符。顯然，使用持續讀或者共享模式讀去讀取當前計數器的值并是一個好主意，因為數據庫的兩個用戶可能看到計數器的同一個值，如果兩個用戶試著用同一識別符往該表添加子項，就會發生一個重復鍵（duplicate-key）錯誤。在這里，如果兩個用戶同時讀計數器，當試圖更新計數器之時，至少它們中有一個會發生死鎖錯誤并終止，因此LOCK IN SHARE MODE并不是一個好的解決方法。在這種情況下，有兩個好方法去實現讀計數器和增長計數器值：(1)?先更新計數器，讓計數器值增1，之后讀計數器，或者(2)用鎖定模式FOR UPDATE先讀計數器，之后計數器值增加。后一個途徑可被如下實現： ~~~ SELECT counter_field FROM child_codes FOR UPDATE; ~~~ ~~~ UPDATE child_codes SET counter_field = counter_field + 1; ~~~ SELECT ... FOR UPDATE讀最新的可見數據，在每個它讀取的行設置獨占鎖定。因此，它設置與搜索的SQL UPDATE可能會在行上設置的鎖定同樣的鎖定。請注意，以上僅是一個SELECT ... FOR UPDATE如何起作用的例子。在MySQL中，事實上生成一個唯一識別符的特殊任務可被用對該表的單獨訪問來完成： ~~~ UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1); ~~~ ~~~ SELECT LAST_INSERT_ID(); ~~~ SELECT語句僅僅取回識別符信息（專門對當前連接）。它不訪問任何表。 #### 15.2.10.6.?Next-Key鎖定：避免幽靈問題在行級鎖定中，InnoDB 使用一個名為*next-key locking*的算法。InnoDB以這樣一種方式執行行級鎖定：當它搜索或掃描表的索引之時，它對遇到的索引記錄設置共享或獨占鎖定。因此，行級鎖定事實上是索引記錄鎖定。 InnoDB對索引記錄設置的鎖定也映像索引記錄之前的“間隙”。如果一個用戶對一個索引上的記錄R有共享或獨占的鎖定，另一個用戶不能緊接在R之前以索引的順序插入一個新索引記錄。這個間隙的鎖定被執行來防止所謂的“幽靈問題”。假設你想要從有一個標識符值大于100的子表讀并鎖定所有子記錄，并想著隨后在選定行中更新一些列： ~~~ SELECT * FROM child WHERE id > 100 FOR UPDATE; ~~~ 假設在id列有一個索引。查詢從id大于100的第一個記錄開始掃描。如果設置在索引記錄上的鎖定不把在間隙生成的插入排除在外，一個新行可能與此同時被插進表中。如果你在同一事務內執行同樣的SELECT，你可能會在該查詢返回的結果包里看到一個新行。這與事務的隔離原則是相反的：一個事務應該能夠運行，以便它已經讀的數據在事務過程中不改變。如果我們把一套行視為數據項，新的“幽靈”子記錄可能會違反這一隔離原則。當InnoDB掃描一個索引之時，它也鎖定所以記錄中最后一個記錄之后的間隙。剛在前一個例子中發生：InnoDB設置的鎖定防止任何插入到id可能大過100的表。你可以用next-key鎖定在你的應用程序上實現一個唯一性檢查：如果你以共享模式讀數據，并且沒有看到你將要插入的行的重復，則你可以安全地插入你的行，并且知道在讀過程中對你的行的繼承者設置的next-key鎖定與此同時阻止任何人對你的行插入一個重復。因此，the next-key鎖定允許你鎖住在你的表中并不存在的一些東西。 #### 15.2.10.7.?持續讀如何在InnoDB中工作的例子假設你正運行在默認的REPEATABLE READ隔離級別。當你發出一個持續讀之時，即一個普通的SELECT語句，InnoDB按照你的查詢看到的數據庫，給你的事務一個時間點。如果另一個事務在你的時間點被指定之后刪除一行并提交，你不會看見已被刪除的行。插入和更新被相似地處理。你可以通過提交你的事務來提前你的時間點，然后做另一個SELECT。這被稱為*多版本并發控制（multi-versioned concurrencycontrol）*。 ~~~ ?????????????? User A???????????????? User B ~~~ ~~~ ? ~~~ ~~~ ?????????? SET AUTOCOMMIT=0;????? SET AUTOCOMMIT=0; ~~~ ~~~ time ~~~ ~~~ |????????? SELECT * FROM t; ~~~ ~~~ |????????? empty set ~~~ ~~~ |???????????????????????????????? INSERT INTO t VALUES (1, 2); ~~~ ~~~ | ~~~ ~~~ v????????? SELECT * FROM t; ~~~ ~~~ ?????????? empty set ~~~ ~~~ ????????????????????????????????? COMMIT; ~~~ ~~~ ? ~~~ ~~~ ?????????? SELECT * FROM t; ~~~ ~~~ ?????????? empty set ~~~ ~~~ ? ~~~ ~~~ ?????????? COMMIT; ~~~ ~~~ ? ~~~ ~~~ ?????????? SELECT * FROM t; ~~~ ~~~ ?????????? --------------------- ~~~ ~~~ ?????????? |??? 1??? |??? 2??? | ~~~ ~~~ ?????????? --------------------- ~~~ ~~~ ?????????? 1 row in set ~~~ 在這個例子中，只有當用戶B已經提交插入，并且用戶A也已經提交之時，用戶A可看見被用戶B插入的行，因此時間點是在用戶B提交之前。如果你想看數據庫的最新狀態，你應該用READ COMMITTED隔離級別或用一個鎖定讀： ~~~ SELECT * FROM t LOCK IN SHARE MODE; ~~~ #### 15.2.10.8.?在InnoDB中不同SQL語句設置的鎖定在SQL查詢處理中，一個鎖定讀，一個UPDATE或一個DELETE一般地對被掃描的每個索引記錄設置記錄鎖定。如果在某查詢中有一個WHERE條件是沒什么關系的，而這個查詢可能從查詢的結果包中排除行。InnoDB不記得確切的WHERE條件，但是僅知道哪個索引范圍被掃描。記錄鎖定是正常的next-key鎖定，它也阻止對緊接著記錄之前的間隙的插入。如果鎖定被設置為獨占，則InnoDB總是取回集束的索引目錄并對其設置鎖定。如果你沒有適合查詢的索引，MySQL不得不掃描整個表來處理查詢，表的每行變成被鎖定的，這樣反過來阻止其它用戶的所有對表的插入。創建一個好的索引讓你的查詢不必要掃描很多行是很重要的。 ·????????SELECT ... FROM是一個持續讀，讀取數據庫的快照并且設置不鎖定，除非事務隔離級別被設為SERIALIZABLE。對于 SERIALIZABLE級別，這個設置對它遇到的索引記錄設置共享的next-key鎖定。 ·????????SELECT ... FROM ... LOCK IN SHARE MODE對讀遇到的所有索引記錄設置共享的next-key鎖定。 ·????????SELECT ... FROM ... FOR UPDATE對讀遇到的所有索引記錄設置獨占的next-key鎖定。 ·????????INSERT INTO ... VALUES (...)對被插入的行設置獨占鎖定。注意，這不是一個next-key鎖定，并且不阻止其它用戶在已插入行之前的間隙插入。如果發生重復鍵錯誤，對重復的索引記錄設置共享鎖定。 ·????????在一個表上初始化之前指定的AUTO_INCREMENT列之時，InnoDB在與AUTO_INCREMENT列相關聯的索引的末尾設置獨占鎖定。在訪問自動增長計數器中，InnoDB使用專用的表鎖定模式AUTO-INC，其中鎖定僅持續到當前SQL語句的結束，而不是到整個事務的結束。請參閱[15.2.10.2節，“InnoDB和AUTOCOMMIT”](# "15.2.10.2.?InnoDB and AUTOCOMMIT")。 InnoDB取回先前初始化的AUTO_INCREMENT列的值而不設定任何鎖定。 ·????????INSERT INTO T SELECT ... FROM S WHERE ...?對每個插入到T的行設置獨占（非next-key）鎖定。它在S上把搜索當作一個持續讀，但是如果MySQL二進制日志功能被打開，它就對S設置一個共享的next-key鎖定。InnoDB在后一種情況不得不設置鎖定：在從一個備份的前滾恢復中，每個SQL語句不得不以與它最初被執行的方式完全同樣的方式執行。 ·????????CREATE TABLE ... SELECT ...?把SELECT當作一個持續讀來執行，或者帶著共享鎖定來執行，如前面的條目所述。 ·????????如果唯一鍵沒有沖突，REPLACE象一個插入一樣被做。另外，對必須更新的行設置一個獨占的next-key鎖定。 ·????????UPDATE ... WHERE ... 對搜索遇到的每個記錄設置一個獨占的next-key鎖定。 ·????????DELETE FROM ... WHERE ... 對搜索遇到的每個記錄設置一個獨占的next-key鎖定。 ·????????如果對一個表定義FOREIGN KEY約束，任何需要檢查約束條件的插入，更新或刪除對它看著檢查約束的記錄設置共享行級鎖定。InnoDB在約束失敗的情況下也設置這些鎖定。 ·????????LOCK TABLES設置表鎖定，但是是InnoDB層之上更高的MySQL層設置這些鎖定。如果innodb_table_locks=1并且 and AUTOCOMMIT=0，InnoDB意識到表鎖定，并且InnoDB之上的MySQL層知道行級鎖定。另外，InooDB的自動死鎖檢測不能檢測在這個表鎖定被涉及之處的死鎖。同樣，既然更高的MySQL層不知道行級鎖定，很可能對另一個用戶當前對其有行鎖定的表獲得一個表鎖定。盡管如此，這并不破壞事務的完整性，如[15.2.10.10節，“死鎖檢測和回滾”](# "15.2.10.10.?Deadlock Detection and Rollback")中討論的一樣。請參閱[15.2.16節，“對InnoDB表的限制”](# "15.2.16.?Restrictions on InnoDB Tables")。 #### 15.2.10.9.? MySQL何時隱式提交或回滾一個事務？ MySQL以默認允許autocommit模式來開始每一個客戶端連接。當autocommit被允許之時，如果SQL語句不返回錯誤的話，MySQL在每個SQL語句之后，做一個提交。如果你關閉autocommit模式并且關閉一個連接而不調用你的事務的明確提交，則MySQL回滾你的事務。如果SQL語句返回一個錯誤，提交/回滾行為取決于這個錯誤。請參閱[15.2.15節，“InnoDB錯誤處理”](# "15.2.15.?InnoDB Error Handling")。下列每一個語句（以及它們的任何同義詞）隱式結束一個事務，就好像在執行這個語句之前你做了一個COMMIT： ·????????ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE, UNLOCK TABLES. ·????????UNLOCK TABLES 僅在如果任何表當前被鎖定之時，提交一個事務。 ·????????在InnoDB中的CREATE TABLE語句被作為一個單獨的事務來處理。這意味著來自用戶的ROLLBACK不撤銷用戶在事務過程中生成的CREATE TABLE語句。事務不能被嵌套。當你發出START TRANSACTION語句或與之同義的語句之時，這是對任何當前事務隱式提交的一個結果。 #### 15.2.10.10. 死鎖檢測和回滾 InnoDB自動檢測事務的死鎖，并回滾一個或幾個事務來防止死鎖。InnoDB試著挑選小事務來回滾，事務的大小通過被插入、更新或刪除的行的數量來確定。如果innodb_table_locks=1?（1是默認值），InnoDB意識到表鎖定，其上的MySQL層知道row-level鎖定。另外InnoDB不能在MySQL LOCK TABLES設定表鎖定的地方或者涉及InnoDB之外的存儲引擎設置鎖定的地方檢測死鎖。你必須通過設定innodb_lock_wait_timeout系統變量的值來解決這些情況。當InnoD執行完全的事務回滾之時，該事務的所有鎖定被釋放。盡管如此，如果單個SQL語句被因為錯誤的原因被回滾，該SQL語句設定的部分鎖定可能被保留。這是因為InnoDB以一種方式存儲行鎖定，在這種方式中它不能知道隨后的哪個鎖定是被哪個SQL語句設定的。 #### 15.2.10.11. 如何處理死鎖死鎖是事務型數據庫典型的問題，但是除非它們頻繁出現以至于你更本不能運行某個事務，它們一般是不危險的。正常地，你必須編寫你的應用程序使得它們總是準備如果因為死鎖而回滾一個事務就重新發出一個事務。 InnoDB使用自動行級鎖定。即使在只插入或刪除單個行的事務的情況下，你可以遇到死鎖。這是因為這些操作不是真正的“極小的”，它們自動對插入或刪除的行的（可能是數個）索引記錄設置鎖定。你可以用下列技術對付死鎖減少它們發生的可能性： ·????????用Use SHOW INNODB STATUS來確定最后一個死鎖的原因。這樣可以幫助你調節應用程序來避免死鎖。 ·????????總是準備著重新發出事務，如果它因為死鎖而失敗了。死鎖不危險，再試一次。 ·????????經常提交你的事務。小事務更少地傾向于沖突。 ·????????如果你正使用鎖定讀，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），試著用更低的隔離級別，比如READ COMMITTED。 ·????????以固定的順序訪問你的表和行。則事務形成良好定義的查詢并且沒有死鎖。 ·????????添加精心選定的索引到你的表。則你的查詢需要掃描更少的索引記錄并且因此設置更少的鎖定。使用EXPLAIN SELECT來確定對于你的查詢,MySQL認為哪個索引是最適當的。 ·????????使用更少的鎖定。如果你可以接受允許一個SELECT從一個舊的快照返回數據，不要給它添加FOR UPDATE或LOCK IN SHARE MODE子句。這里使用READ COMMITTED隔離級別是比較好的，因為每個在同一事務里的持續讀從它自己新鮮的快照里讀取。 ·????????如果沒有別的有幫助的了，用表級鎖定系列化你的事務。用LOCK TABLES對事務型表（如InnoDB)的正確方法是設置AUTOCOMMIT = 0 并且不調用UNLOCK TABLES直到你明確地提交了事務。例如，如果你需要寫表t1并從表t讀，你可以按如下做： ~~~ ·??????????????? SET AUTOCOMMIT=0; ~~~ ~~~ ·??????????????? LOCK TABLES t1 WRITE, t2 READ, ...; ~~~ ~~~ ·??????????????? [do something with tables t1 and t2 here]; ~~~ ~~~ ·??????????????? COMMIT; ~~~ ~~~ ·??????????????? UNLOCK TABLES; ~~~ 表級鎖定使得你的事務很好地排隊，并且死鎖被避免了。 ·????????領一個系列化事務的方法是創建一個輔助的“semaphore” 表，它只包含一個單行。讓每個事務在訪問其它表之前更新那個行。以這種方式，所有事務以序列的方式發生。注意，InnoDB即時死鎖檢測算法也能在這種情況下起租用，因為系列化鎖定是行級鎖定。超時方法，用MySQL表級鎖定，必須被用來解決死鎖。 ·????????在應用程序中使用LOCK TABLES命令，如果AUTOCOMMIT=1,MySQL不設定InnoDB表鎖定。 ### 15.2.11.?InnoDB性能調節提示 [15.2.11.1. SHOW INNODBSTATUS和InnoDB Monitors](#) ·????????如果Unix頂層工具或者Windows任務管理器顯示，你的數據庫的工作負荷的CPU使用率小于70%，則你的工作負荷可能是磁盤綁定的，可能你正生成太多的事務和提交，或者緩沖池太小。使得緩沖池更大一些會有幫助的，但不要設置緩沖池等于或超過物理內存的80%. ·????????把數個修改裹進一個事務里。如果事務對數據庫修改，InnoDB在該事務提交時必須刷新日志到磁盤。因為磁盤旋轉的速度至多167轉/秒，如果磁盤沒有騙操作系統的話，這就限制提交的數目為同樣的每秒167次。 ·????????如果你可以接受損失一些最近的已提交事務，你可以設置my.cnf文件里的參數innodb_flush_log_at_trx_commit為0。無論如何InnoDB試著每秒刷新一次日志，盡管刷新不被許可。 ·????????使用大的日志文件，讓它甚至與緩沖池一樣大。當InnoDB寫滿日志文件時，它不得不在一個檢查點把緩沖池已修改的內容寫進磁盤。小日志文件導致許多不必要的吸盤寫操作。大日志文件的缺點時恢復時間更長。 ·????????也讓日志緩沖相當大（與8MB相似的數量)。 ·????????如果你存儲變長度字符串，或者列可能包含很多NULL值，則使用VARCHAR列類型而不是CHAR類型。一個CHAR(*N*)列總是占據*N* 字節來存儲，即使字符串更短或字符串的值是NULL。越小的表越好地適合緩沖池并且減少磁盤I/O。當使用row_format=compact （MySQL 5.1中默認的InnoDB記錄格式）和可變長度字符集，比如UTF-8或sjis，CHAR(*N*)將占據可變數量的空間，至少為*N* 字節。 ·????????在一些版本的GNU/Linux和Unix上，用Unix的fsync()（InnoDB默認使用的）把文件刷新到磁盤，并且其他相似的方法是驚人的慢。如果你不滿意數據庫的寫性能，你可以試著設置my.cnf里的innodb_flush_method為O_DSYNC，雖然O_DSYNC在多數系統上看起來更慢。 ·????????當在Solaris 10上，為x86_64架構（AMD Opteron)使用InnoDB存儲引擎，重要的是使用forcedirectio選項來安裝任何為存儲與InnoDB相關的文件而使用的數據系統。（默認在Solaris 10/x86_64上不使用這個文件系統安裝選項）。使用forcedirectio 失敗會導致InnoDB在這個平臺上的速度和性能嚴重退化。 ·????????當導入數據到InnoDB中之時，請確信MySQL沒有允許autocommit模式，因為允許autocommit模式會需要每次插入都要刷新日志到磁盤。要在導入操作規程中禁止autocommit模式，用SET AUTOCOMMIT和COMMIT語句來包住導入語句： ~~~ ·??????????????? SET AUTOCOMMIT=0; ~~~ ~~~ ·??????????????? /* SQL import statements ... */ ~~~ ~~~ ·??????????????? COMMIT; ~~~ 如果你使用**mysqldump** 選項--opt，即使不用SET AUTOCOMMIT和COMMIT語句來包裹，你也使得快速的轉儲文件被導入到InnoDB表中。 ·????????小心大宗插入的大回滾：InnoDB在插入中使用插入緩沖來節約磁盤I/O，但是在相應的回滾中沒有使用這樣的機制。一個磁盤綁定的回滾可以用相應插入花費時間的30倍來執行。殺掉數據庫進程沒有是幫助的，因為回滾在服務器啟動時會再次啟動。除掉一個失控的回滾的唯一方法是增大緩沖池使得回滾變成CPU綁定且跑得快，或者使用專用步驟，請參閱[15.2.8.1節，“強制恢復”](# "15.2.8.1.?Forcing Recovery")。 ·????????也要小心其它大的磁盤綁定操作。用DROP TABLE或CREATE TABLE來清空一個表，而不是用DELETE FROM *tbl_name*。 ·????????如果你需要插入許多行，則使用多行插入語法來減少客戶端和服務器之間的通訊開支： ~~~ ·??????????????? INSERT INTO yourtable VALUES (1,2), (5,5), ...; ~~~ 這個提示對到任何表類型的插入都是合法的，不僅僅是對InnoDB類型。 ·????????如果你在第二個鍵上有UNIQUE約束，你可以在導入會話中暫時關閉唯一性檢查以加速表的導入： ~~~ ·??????????????? SET UNIQUE_CHECKS=0; ~~~ 對于大表，這節約了大量磁盤I/O，因為InnoDB可以使用它的插入緩沖來在一批內寫第二個索引記錄。 ·????????如果你對你的表有FOREIGN KEY約束，你可以在導入會話過程中通過關閉外鍵檢查來提速表的導入： ~~~ ·??????????????? SET FOREIGN_KEY_CHECKS=0; ~~~ 對于大表，這可以節約大量的磁盤I/O。 ·????????如果你經常有對不經常更新的表的重發查詢，請使用查詢緩存： ~~~ ·??????????????? [mysqld] ~~~ ~~~ ·??????????????? query_cache_type = ON ~~~ ~~~ ·??????????????? query_cache_size = 10M ~~~ #### 15.2.11.1.?SHOW INNODB STATUS和 InnoDB Monitors InnoDB包含InnoDB Monitors，它打印有關InnoDB內部狀態的信息。你可以使用SQL語句SHOW INNODB STATUS來取標準InnoDB Monitor的輸出送到你的SQL客戶端。這個信息在性能調節時有用。（如果你正使用**mysql**事務SQL客戶端，如果你用\G替代通常的語句終止符分號，輸出結果會更可讀）。關于InnoDB鎖定模式的討論，請參閱[15.2.10.1節，“InnoDB鎖定模式”](# "15.2.10.1.?InnoDB Lock Modes")。 ~~~ mysql> SHOW INNODB STATUS\G ~~~ 另一個使用InnoDB Monitors的方法時讓它們不斷寫數據到**mysqld**服務器的標準輸出。在這種情況下，沒有輸出被送到客戶端。當被打開的時候，InnoDB Monitors每15秒打印一次數據。服務器輸出通常被定向到MySQL數據目錄里的.err日志。這個數據在性能調節時有用。在Windows上，如果你想定向輸出到窗口而不是到日志文件，你必須從控制臺窗口的命令行使用--console選項來啟動服務器。監視器輸出包括下列類型的信息： ·????????被每個激活事務持有的表和記錄鎖定 ·????????事務的鎖定等待 ·????????線程的信號等待 ·????????未解決的文件I/O請求 ·????????緩沖池統計數據 ·????????主InnoDB線程的凈化和插入緩沖合并活動要讓標準InnoDB Monitor寫到**mysqld**的標準輸出，請使用下列SQL語句： ~~~ CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB; ~~~ 監視器可以通過發出下列語句來被停止： ~~~ DROP TABLE innodb_monitor; ~~~ CREATE TABLE語法正是通過MySQL的SQL解析程序往InnoDB引擎傳遞命令的方法：唯一有問題的事情是表名字innodb_monitor及它是一個InnoDB表。對于**InnoDB**** Monitor**, 表的結構根本不相關。如果你在監視器正運行時關閉服務器，并且你想要再次啟動監視器，你必須在可以發出新CREATE TABLE語句啟動監視器之前移除表。這個語法在將來的發行版本中可能會改變。你可以以相似的方式使用innodb_lock_monitor。除了它也提供大量鎖定信息之外，它與innodb_monitor相同。一個分離的 innodb_tablespace_monitor打印存在于表空間中已創建文件片斷的一個列表，并且確認表空間分配數據結構。此外，有innodb_table_monitor，用它你可以打印InnoDB內部數據詞典的內容。 **InnoDB**** Monitor**輸出的一個例子： ~~~ mysql> SHOW INNODB STATUS\G ~~~ ~~~ *************************** 1. row *************************** ~~~ ~~~ Status: ~~~ ~~~ ===================================== ~~~ ~~~ 030709 13:00:59 INNODB MONITOR OUTPUT ~~~ ~~~ ===================================== ~~~ ~~~ Per second averages calculated from the last 18 seconds ~~~ ~~~ ---------- ~~~ ~~~ SEMAPHORES ~~~ ~~~ ---------- ~~~ ~~~ OS WAIT ARRAY INFO: reservation count 413452, signal count 378357 ~~~ ~~~ --Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore: ~~~ ~~~ X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135 ~~~ ~~~ a writer (thread id 32782) has reserved it in mode wait exclusive ~~~ ~~~ number of readers 1, waiters flag 1 ~~~ ~~~ Last time read locked in file btr0sea.c line 731 ~~~ ~~~ Last time write locked in file btr0sea.c line 1347 ~~~ ~~~ Mutex spin waits 0, rounds 0, OS waits 0 ~~~ ~~~ RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485 ~~~ ~~~ ------------------------ ~~~ ~~~ LATEST FOREIGN KEY ERROR ~~~ ~~~ ------------------------ ~~~ ~~~ 030709 13:00:59 Transaction: ~~~ ~~~ TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser ~~~ ~~~ ting ~~~ ~~~ 15 lock struct(s), heap size 2496, undo log entries 9 ~~~ ~~~ MySQL thread id 25, query id 4668733 localhost heikki update ~~~ ~~~ insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk') ~~~ ~~~ Foreign key constraint fails for table test/ibtest11a: ~~~ ~~~ , ~~~ ~~~ ? CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`) ~~~ ~~~ ON DELETE CASCADE ON UPDATE CASCADE ~~~ ~~~ Trying to add in child table, in index PRIMARY tuple: ~~~ ~~~ 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4; ~~~ ~~~ hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex ~~~ ~~~ 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;; ~~~ ~~~ But in parent table test/ibtest11b, in index PRIMARY, ~~~ ~~~ the closest match we can find is record: ~~~ ~~~ RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a ~~~ ~~~ sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ...... ~~~ ~~~ ;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;; ~~~ ~~~ ------------------------ ~~~ ~~~ LATEST DETECTED DEADLOCK ~~~ ~~~ ------------------------ ~~~ ~~~ 030709 12:59:58 ~~~ ~~~ *** (1) TRANSACTION: ~~~ ~~~ TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser ~~~ ~~~ ting ~~~ ~~~ LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146 ~~~ ~~~ MySQL thread id 21, query id 4553379 localhost heikki update ~~~ ~~~ INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f' ~~~ ~~~ ,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7 ~~~ ~~~ *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ~~~ ~~~ RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole ~~~ ~~~ trx id 0 290252780 lock mode S waiting ~~~ ~~~ Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a ~~~ ~~~ a35818;; 1: ~~~ ~~~ *** (2) TRANSACTION: ~~~ ~~~ TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser ~~~ ~~~ ting ~~~ ~~~ 130 lock struct(s), heap size 11584, undo log entries 437 ~~~ ~~~ MySQL thread id 23, query id 4554396 localhost heikki update ~~~ ~~~ REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,' ~~~ ~~~ h396', NULL, NULL, 7.31,7.31,7.31,200) ~~~ ~~~ *** (2) HOLDS THE LOCK(S): ~~~ ~~~ RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole ~~~ ~~~ trx id 0 290251546 lock_mode X locks rec but not gap ~~~ ~~~ Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a ~~~ ~~~ a35818;; 1: ~~~ ~~~ *** (2) WAITING FOR THIS LOCK TO BE GRANTED: ~~~ ~~~ RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole ~~~ ~~~ trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting ~~~ ~~~ Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa ~~~ ~~~ 35720;; 1: ~~~ ~~~ *** WE ROLL BACK TRANSACTION (1) ~~~ ~~~ ------------ ~~~ ~~~ TRANSACTIONS ~~~ ~~~ ------------ ~~~ ~~~ Trx id counter 0 290328385 ~~~ ~~~ Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 ~~~ ~~~ Total number of lock structs in row lock hash table 70 ~~~ ~~~ LIST OF TRANSACTIONS FOR EACH SESSION: ~~~ ~~~ ---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002 ~~~ ~~~ MySQL thread id 32, query id 4668737 localhost heikki ~~~ ~~~ show innodb status ~~~ ~~~ ---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in ~~~ ~~~ serting ~~~ ~~~ 1 lock struct(s), heap size 320 ~~~ ~~~ MySQL thread id 29, query id 4668736 localhost heikki update ~~~ ~~~ insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh ~~~ ~~~ gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh ~~~ ~~~ ---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co ~~~ ~~~ mmitting ~~~ ~~~ 1 lock struct(s), heap size 320, undo log entries 1 ~~~ ~~~ MySQL thread id 19, query id 4668734 localhost heikki update ~~~ ~~~ insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh ~~~ ~~~ hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf ~~~ ~~~ ---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st ~~~ ~~~ arting index read ~~~ ~~~ LOCK WAIT 2 lock struct(s), heap size 320 ~~~ ~~~ MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update ~~~ ~~~ update ibtest11a set B = 'kHdkkkk' where A = 89572 ~~~ ~~~ ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED: ~~~ ~~~ RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM ~~~ ~~~ ARY trx id 0 290328327 lock_mode X waiting ~~~ ~~~ Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc ~~~ ~~~ supremum.;; ~~~ ~~~ ------------------ ~~~ ~~~ ---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro ~~~ ~~~ llback of SQL statement ~~~ ~~~ ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9 ~~~ ~~~ MySQL thread id 25, query id 4668733 localhost heikki update ~~~ ~~~ insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk') ~~~ ~~~ ---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782 ~~~ ~~~ 58 lock struct(s), heap size 5504, undo log entries 159 ~~~ ~~~ MySQL thread id 23, query id 4668732 localhost heikki update ~~~ ~~~ REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f ~~~ ~~~ ','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'), ~~~ ~~~ ---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in ~~~ ~~~ serting ~~~ ~~~ 4 lock struct(s), heap size 1024, undo log entries 165 ~~~ ~~~ MySQL thread id 21, query id 4668735 localhost heikki update ~~~ ~~~ INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL, ~~~ ~~~ 'h321', NULL, NULL, 7.31,7.31,7.31,200) ~~~ ~~~ -------- ~~~ ~~~ FILE I/O ~~~ ~~~ -------- ~~~ ~~~ I/O thread 0 state: waiting for i/o request (insert buffer thread) ~~~ ~~~ I/O thread 1 state: waiting for i/o request (log thread) ~~~ ~~~ I/O thread 2 state: waiting for i/o request (read thread) ~~~ ~~~ I/O thread 3 state: waiting for i/o request (write thread) ~~~ ~~~ Pending normal aio reads: 0, aio writes: 0, ~~~ ~~~ ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 ~~~ ~~~ Pending flushes (fsync) log: 0; buffer pool: 0 ~~~ ~~~ 151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs ~~~ ~~~ 25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s ~~~ ~~~ ------------------------------------- ~~~ ~~~ INSERT BUFFER AND ADAPTIVE HASH INDEX ~~~ ~~~ ------------------------------------- ~~~ ~~~ Ibuf for space 0: size 1, free list len 19, seg size 21, ~~~ ~~~ 85004 inserts, 85004 merged recs, 26669 merges ~~~ ~~~ Hash table size 207619, used cells 14461, node heap has 16 buffer(s) ~~~ ~~~ 1877.67 hash searches/s, 5121.10 non-hash searches/s ~~~ ~~~ --- ~~~ ~~~ LOG ~~~ ~~~ --- ~~~ ~~~ Log sequence number 18 1212842764 ~~~ ~~~ Log flushed up to?? 18 1212665295 ~~~ ~~~ Last checkpoint at? 18 1135877290 ~~~ ~~~ 0 pending log writes, 0 pending chkp writes ~~~ ~~~ 4341 log i/o's done, 1.22 log i/o's/second ~~~ ~~~ ---------------------- ~~~ ~~~ BUFFER POOL AND MEMORY ~~~ ~~~ ---------------------- ~~~ ~~~ Total memory allocated 84966343; in additional pool allocated 1402624 ~~~ ~~~ Buffer pool size?? 3200 ~~~ ~~~ Free buffers?????? 110 ~~~ ~~~ Database pages???? 3074 ~~~ ~~~ Modified db pages? 2674 ~~~ ~~~ Pending reads 0 ~~~ ~~~ Pending writes: LRU 0, flush list 0, single page 0 ~~~ ~~~ Pages read 171380, created 51968, written 194688 ~~~ ~~~ 28.72 reads/s, 20.72 creates/s, 47.55 writes/s ~~~ ~~~ Buffer pool hit rate 999 / 1000 ~~~ ~~~ -------------- ~~~ ~~~ ROW OPERATIONS ~~~ ~~~ -------------- ~~~ ~~~ 0 queries inside InnoDB, 0 queries in queue ~~~ ~~~ Main thread process no. 3004, id 7176, state: purging ~~~ ~~~ Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779 ~~~ ~~~ 1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s ~~~ ~~~ ---------------------------- ~~~ ~~~ END OF INNODB MONITOR OUTPUT ~~~ ~~~ ============================ ~~~ ~~~ 1 row in set (0.05 sec) ~~~ 關于這個輸出一些要注意的： ·????????如果TRANSACTIONS節報告鎖定等待，你的應用程序可能有鎖定競爭。輸出也幫助跟蹤事務死鎖的原因。 ·????????SEMAPHORES節報告等待一個信號的線程和關于線程對一個互斥體或rw-lock信號需要多少次回滾或等待的統計數據。大量等待信號的線程可能是磁盤I/O的結果或者InnoDB內競爭問題的結果。競爭可能是因為查詢的嚴重并行，或者操作系統線程計劃的問題。設置innodb_thread_concurrency小于默認值在這種情況下是有幫助的。 ·????????BUFFER POOL AND MEMORY節給你關于頁面讀寫的統計數據。你可以從這些數計算你當前的查詢正做多少文件數據I/O操作。 ·????????ROW OPERATIONS節顯示主線程正在做什么。 InnoDB發送診斷輸出到stderr或文件，而不是到stdout或者固定尺寸內存緩沖，以避免底層緩沖溢出。作為一個副效果，SHOW INNODB STATUS的輸出每15秒鐘寫到一個狀態文件。這個文件的名字是innodb_status.*pid*，其中*pid* 是服務器進程ID。這個文件在MySQL數據目錄里創建。正常關機之時，InnoDB刪除這個文件。如果發生不正常的關機，這些狀態文件的實例可能被展示，而且必須被手動刪除。在移除它們之前，你可能想要檢查它們來看它們是否包含有關不正常關機的原因的有用信息。僅在配置選項innodb_status_file=1被設置之時，innodb_status.*pid*文件被創建。 ### 15.2.12. 多版本的實施因為InnoDB是多版本化的數據庫, 它必須保持關于表空間中舊版本行的信息。這個信息被存在名為*rollback segment*（在Oracle中模擬數據結構之后）的數據結構中。內部地，InnoDB往存在數據庫中的每一行中添加兩個域。一個6字節的域說明插入或更新該行的最后一個事務的事務識別符。同時，一個刪除也被內部處理為一個更新，其中行中一個特殊的位被設置來標注該行為已刪除。每一行也包含一個稱為滾動指針的7字節域。滾動指針指向一個被寫到回滾片斷的撤銷日志記錄。如果該行被更新，撤銷日志記錄包含在該行被更新之前重建該行的內容必需的的信息。 InnoDB使用在回滾片斷中的信息來執行在事務回滾中需要的撤銷操作。它也使用這個信息來為一個持續讀構建更早版本的行。在回滾片斷中的撤銷日志被分為插入和更新撤銷日志。插入撤銷日志僅在事務回滾中需要，且只要事務一提交就可以被丟棄。更新撤銷日志也被用在持續讀中，而且它們僅在當前沒有被InnoDB分配給一個快照的事務之后被丟棄，這個快照在持續讀中可能會需要更新撤銷日志的信息來建立一個數據庫行的早期版本。你必須記得規律地提交你的事務，包括那些只發布持續讀的事務。否則, InnoDB不能從更新撤銷日志丟棄數據，并且回滾片斷可能變得太大，填滿你的表空間。在一個回滾片斷里，一個撤銷日志記錄的物理尺寸典型地小于相應的已插入行或已更新行。你可以用這個信息來計算回滾片斷需要的空間。在InnoDB多版本化方案中，當你用SQL語句刪除一行之時，該行沒有被從數據庫立即物理刪除掉。只有當InnoDB可以丟棄為刪除而被寫的更新撤銷日志記錄時，InnoDB也物理地從數據庫刪除相應行和它的索引記錄。這個刪除操作被成為精華，它運行得很快，通常與做刪除的SQL語句花的時間在一個數量級。在某一情景下，在那里，用戶以幾乎相同的比率，小批次地在表中插入和刪除行，凈化線程開始滯后是可能的，并且表變得越來越大，使得每樣事都是磁盤綁定的而且非常慢。即使表僅載有10MB有用的數據，它可能變得用所有的死行占據10GB空間。在這種情況下，節流新操作，并分配更多的資源來凈化線程可能是比較好的。啟動選項和可設置全球變量innodb_max_purge_lag就是為這個目的而存在的。請參閱[15.2.4節，“InnoDB 啟動選項”](# "15.2.4.?InnoDB Startup Options") 以獲得更多信息。 ### 15.2.13. 表和索引結構 [15.2.13.1.索引的物理結構](#) [15.2.13.2. 緩沖插入](#) [15.2.13.3. 適應的哈希索引](#) [15.2.13.4. 物理記錄結構](#) MySQL為表把它的數據詞典信息以.frm文件的形式存在數據庫目錄里，這對所有MySQL存儲引擎都是真的。但是每個InnoDB表在表空間內的InnoDB內部數據詞典里有它自己的條目。當MySQL移除表或數據庫，它不得不刪除.frm文件和InnoDB數據詞典內的相應條目。這就是為什么你不能在數據庫之間簡單地移動.frm文件來移動InnoDB表。每個InnoDB表有專門索引，被稱為*clustered index*，對行的數據被存于其中。如果你對你的表定義一個PRIMARY KEY，主鍵的索引是集束索引。如果你沒有為表定義PRIMARY KEY，MySQL拾取第一個僅有NOT NULL列的UNIQUE索引作為主鍵，并且InnoDB把它當作集束索引來用。如果表中沒有這樣一個索引，InnoDB內部產生一個集束索引，其中用InnoDB在這樣一個表內指定給行的行ID來排序行。行ID是一個6字節的域，它在新行被插入的時候單一地增加。因此被行ID排序的行是物理地按照插入順序排的。通過集束索引訪問一個行是較快的，因為行數據是在索引搜索引導的同一頁面。如果表是巨大的，當對比于傳統解決方案，集束索引構架經常節約磁盤I/O。（在許多數據庫，數據傳統地被存在與索引記錄不同的頁）。在InnoDB中，非集束索引里的記錄（也稱為第二索引）包含對行的主鍵值。InnoDB用這個主鍵值來從集束索引中搜索行。注意，如果主鍵是長的，第二索引使用更多空間。 InnoDB比較CHAR和VARCHAR字符串不同長度，以便在較短字符串中剩下的長度被處理視為用空格補上的。 #### 15.2.13.1. 索引的物理結構所有InnoDB的索引是B數，其中索引記錄被存儲在樹的樹葉頁。一個索引頁的默認大小是16KB。當新記錄被插入，InnoDB試著為將來索引記錄的插入和更新留下十六分之一的空白頁。如果索引記錄以連續的順序被插入（升序或者降序），結果索引頁大約是15/16滿。如果記錄被以隨機的順序被插入，頁面是從1/2到 15/16滿。如果索引頁的填充因子降到低于1/2，InnoDB試著搜索索引樹來釋放頁。 #### 15.2.13.2. 緩沖插入在數據庫應用中，主鍵是一個唯一的識別符，并且新行被以主鍵的升序來插入，這是個常見的情況。因此，到集束索引的插入不需要從一個磁盤隨機讀。另一方面，第二索引通常是非唯一的，到第二索引的插入以相對隨機次序發生。這可能會導致大量的隨機磁盤I/O操作，而沒有一個被用在InnoDB中的專用機制。如果一個索引記錄應該被插入到一個非唯一第二索引，InnoDB檢查第二索引頁是否在緩沖池中。如果是，InnoDB直接插入到索引頁。如果索引頁沒有在緩沖池中被發現，InnoDB插入記錄到一個專門的插入緩沖結構。插入緩沖被保持得如此小以至于它完全適合在緩沖池，并且可以非常快地做插入。插入緩沖周期地被合并到數據庫中第二索引樹里。把數個插入合并到索引樹的同一頁，節省磁盤I/O操作，經常地這是有可能的。據測量，插入緩沖可以提高到表的插入速度達15倍。在插入事務被提交之后，插入緩沖合并可能連續發生。實際上，服務器關閉和重啟之后，這會連續發生。（請參閱[15.2.8.1節，“強制恢復”](# "15.2.8.1.?Forcing Recovery")）。當許多第二索引必須被更新之時，并且許多行已被插入之時，插入緩沖合并可能需要數個小時。在這個時間內，磁盤I/O將會增加，這樣會導致磁盤綁定查詢明顯緩慢。另一個明顯的后臺I/O操作是凈化線程（請參閱[15.2.12節，“實現多版本化”](# "15.2.12.?Implementation of Multi-Versioning")）。 #### 15.2.13.3. 適應的哈希索引如果一個表幾乎完全配合主內存，在其上執行查詢最快的方法就是使用哈希索引。InnoDB有一個自動機制，它監視對為一個表定義的索引的索引搜索。如果InnoDB注意到查詢會從建立一個哈希索引中獲益，它會自動地這么做。注意，哈希索引總是基于表上已存在的B樹索引來建立。根據InnoDB對B樹索引觀察的搜索方式，InnoDB會在為該B樹定義的任何長度的鍵的一個前綴上建立哈希索引。哈希索引可以是部分的：它不要求整個B樹索引被緩存在緩沖池。InnoDB根據需要對被經常訪問的索引的那些頁面建立哈希索引。在某種意義上，InnoDB通過針對豐富主內存的適應的哈希索引機制來剪裁自己，更加靠近主內存數據庫的架構。 #### 15.2.13.4.?物理記錄結構 InnoDB表中的記錄有如下特征： ·????????InnoDB中每個索引記錄包含一個6字節的頭。這個頭被用來將連續的記錄連接在一起，并且也用在row-level鎖定中。 ·????????集束索引中的記錄包含對所有自定義列的域。此外，有一個6字節的域給事務ID以及一個7字節的域給滾動指針。 ·????????如果沒有為一個表定義初級鍵，每個集束索引記錄也包含一個6字節的行ID域。 ·????????每個第二索引記錄也包含為集束索引鍵定義的所有域。 ·????????一個記錄也包含一個指向該記錄每一個域的指針，如果在一個記錄中域的總長度小于128字節，該指針時一個字節；否則就是2字節。這些指針的陣列被稱為記錄目錄。這些指針指向的區域被稱為記錄的數據部分。 ·????????內部地，InnoDB以固定長度格式存儲固定長度字符列，比如CHAR(10)。InnoDB從VARCHAR列截短跟蹤空間。注意，MySQL可以內部地把CHAR列轉換為VARCHAR列。請參閱[13.1.5.1節，“安靜的列規格改變”](# "13.1.5.1.?Silent Column Specification Changes")。 ·????????一個SQL的NULL值在記錄目錄里保留1或2字節。此外，SQL的NULL值如果被保存在可變長度列，則在記錄數據部分保留零字節。在一個固定長度列，它在記錄的數據部分保留該列的固定長度。為NULL值保留固定空間背后的動機是之后該列從NULL值到非NULL值的更新可以就地完成，且不會導致索引頁的碎片。 ### 15.2.14.文件空間管理和磁盤I/O [15.2.14.1. 磁盤I/O](#) [15.2.14.2. 為表空間使用原始設備](#) [15.2.14.3.文件空間管理](#) [15.2.14.4.整理表碎片](#) #### 15.2.14.1. 磁盤 I/O InnoDB使用模擬異步磁盤I/O：InnoDB創建許多線程來處理I/O操作，比如read-ahead. 在InnoDB中有兩個read-ahead試探： ·????????在連續read-ahead中，如果InnoDB注意到在表空間中對一個片斷的訪問方式是連續的，它就預先布置一批數據庫頁的讀給I/O系統。 ·????????在隨機read-ahead中，如果InnoDB注意到表空間中的一些區域看起來進入完全讀進緩沖池中的處理中，它就布置剩余的讀到I/O系統。 InnoDB使用一個被稱為*doublewrite*的新穎的文件刷新技術。它給操作體統崩潰或掉電后的恢復添加了安全，并且通過減少對fsync()操作的需要，它在多數Unix變種上改善了性能。. Doublewrite意為在向一個數據文件寫頁之前，InnoDB首先把它們寫到一個毗鄰的表空間區域，稱為doublewrite緩沖。僅在寫然后刷新到doublewrite已經完成之前，InnoDB寫頁面到它們在表空間中恰當的位置。如果操作系統在寫頁面的中間崩潰，在恢復過程中，InnoDB可以在隨后從doublewrite緩沖中找到頁面的一個良好復制。 #### 15.2.14.2. 為表空間使用原始設備你也可以使用原始磁盤分區作為表空間數據文件。通過使用原始磁盤，你可以在Windows和一些Unix系統上執行non-buffered I/O 而無須文件系統開支，這樣可以改善性能當你創建一個新數據文件之時，你必須在innodb_data_file_path里緊接著數據文件尺寸之后放置關鍵字newraw。分區必須至少和你指定的尺寸一樣大，注意，在InnoDB中，1MB是1024x1024字節，但是在磁盤規格中，1MB通常意為1,000,000字節。 ~~~ [mysqld] ~~~ ~~~ innodb_data_home_dir= ~~~ ~~~ innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw ~~~ 下次你啟動服務器之時，InnoDB注意到關鍵字newraw并初始化新分區。但是仍然并不創建或改變任何InnoDB表。另外，當你重啟服務器之時，InnoDB重新初始化分區，你的改變會丟失。（從3.23.44啟動，作為一個安全措施，當用newraw指定任何分區之時，InnoDB阻止用戶修改數據）。 InnoDB初始化新分區之后，停止服務器，在對行的數據文件規格中改變newraw： ~~~ [mysqld] ~~~ ~~~ innodb_data_home_dir= ~~~ ~~~ innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw ~~~ 然后重啟動服務器，并且InnoDB允許做改變。在Windows上，你可以象這樣分配磁盤分區為一個數據文件： ~~~ [mysqld] ~~~ ~~~ innodb_data_home_dir= ~~~ ~~~ innodb_data_file_path=//./D::10Gnewraw ~~~ 對于訪問物理驅動器，//./ 相當于Windows語法的\\.\ 。當你使用原始磁盤分區之時，確信它們有允許被用來運行MySQL服務器的帳號讀和寫訪問的許可。 #### 15.2.14.3.文件空間管理你在配置文件中定義的數據文件形成InnoDB的表空間。文件被簡單地連起來以形成表空間。沒有條紋在使用。當前你不能定義你的表被分配到表空間中的位置。但是，在一個新創建的表中間中，InnoDB 從第一個數據文件開始分配空間。表空間包含數據庫頁，默認大小是16KB。這些頁被分組成64個連續頁的范圍。表空間內的文件在InnoDB中被稱為片斷。術語“rollback segment”有一些混淆，因為它確切地包含許多表空間片斷。在InnoDB中，每個索引分配兩個片斷。一個是給B樹的非樹葉節點的，另一個是給樹葉節點的。在這里，理想的是為包含數據的樹葉節點達到更好的有序性。當一個片斷在表空間內長大，InnoDB單獨地分配最先的32頁給它。此后InnoDB開始分配整個范圍給該片斷。InnoDB可以一次給一個大片斷添加多達4個范圍以確保數據良好的連續性。在表空間中的一些頁包含其它頁的位圖，因此在一個InnoDB表空間中的一些范圍不能被整個地分配給片斷，只能作為單個頁被分配。當你發出SHOW TABLE STATUS詢問表空間里可用的自由空間之時，InnoDB報告在表空間中完全自由的范圍。InnoDB總是為掃除和其它內部目的保留一些范圍，這些保留的范圍不包括在自由空間里。當你從一個表中刪除數據之時，InnoDB聯系相應的B樹索引。是否釋放單獨頁或是范圍到表空間取決刪除的方式，因此被釋放的空間變成對其它用戶可用，但是記住，已刪除的行僅在該行不再被事務回滾或持續讀所需要之后的一個（自動）凈化操作中被物理刪除。 #### 15.2.14.4. 整理表碎片如果有隨機插入到表的索引或從表的索引隨機刪除，索引可能變成碎片的。碎片意思是索引頁在磁盤上的物理排序并不接近頁上記錄的索引排序，或者在分配給索引的64頁塊上有許多沒有被使用的頁。碎片的一個“同義詞”是一個表占據的空間超過它應該占據的空間的大小。確切是多少，這是很難去確定的。所有InnoDB數據和索引被存在B樹中，并且它們的填充因子可能從50%到100%。碎片的另一個“同義詞”是一個表掃描例如： ~~~ SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345; ~~~ 花了超過它應該花的時間。（在上面的查詢中我們“欺騙”SQL優化器來掃描集束索引，而不是一個第二索引）。多數磁盤可以讀10MB/s到50MB/s，這可以被用來評估一個表掃描可以多快地運行。如果你周期地執行“null” ALTER TABLE操作，它就可以加速索引掃描：? ~~~ ALTER TABLE tbl_name ENGINE=INNODB ~~~ 這導致MySQL重建表。另一個執行碎片整理操作的辦法是使用**mysqldump**來轉儲一個表到一個文本文件，移除表，并重新從轉儲文件重裝載它。如果到一個索引的插入總是升序的，并且記錄僅從末尾被刪除，InnoDB文件空間管理保證在索引中的碎片不會發生。 ### 15.2.15.?InnoDB錯誤處理 [15.2.15.1. InnoDB錯誤代碼](#) [15.2.15.2.操作系統錯誤代碼](#) 在InnoDB中錯誤處理不像SQL標準中指定的一樣。按照標準，在SQL語句過程中的任何錯誤應該導致該語句的回滾。InnoDB有時僅回滾部分語句，或者整個事務。下列條目敘述InnoDB如何執行錯誤處理： ·????????如果你耗盡表空間中的文件空間，你使得MySQL表完全錯誤，并且InnoDB返回SQL語句。 ·????????一個事務死鎖導致InnoDB回滾整個事務，在鎖定等待超時的情況，InnoDB僅回滾最近的SQL語句。當一個事務回滾因為死鎖或鎖定等待超時而發生，它在事務中撤銷語句的作用。但是如果事務是用START TRANSACTION或BEGIN語句開始的，它就不撤銷該語句。進一步，SQL語句變成事務的一部分直到COMMIT, ROLLBACK或者導致暗地提交的SQL語句發生。 ·????????如果你沒有在語句中指定IGNORE選項，重復鍵錯誤回滾SQL語句。 ·????????行太長錯誤回滾SQL語句。 ·????????其它錯誤主要被代碼的MySQL層（在InnoDB存儲引擎級別以上）探測，它們回滾相應的SQL語句。在單個SQL語句中的回滾中鎖定不被釋放。在暗地回滾過程中，就像在一個明確的ROLLBACK SQL命令的執行過程中一樣，SHOW PROCESSLIST在State列為有關的連接顯示Rolling back。 #### 15.2.15.1.?InnoDB錯誤代碼下面的這個不完全列表是你可能遇見的常見的InnoDB專有錯誤，帶著為什么發生的原因以及如何該解決問題的相關信息 ·????????1005 (ER_CANT_CREATE_TABLE) 不能創建表。如果錯誤信息字符串指向errno 150，因為外鍵約束被不正確地形成，所以表創建失敗。 ·????????1016 (ER_CANT_OPEN_FILE) 雖然對某表的.frm文件存在，但不能從InnoDB數據文件找到該InnoDB表。請參閱[15.2.17.1節，“InnoDB數據詞典操作故障診斷和排除”](# "15.2.17.1.?Troubleshooting InnoDB Data Dictionary Operations")。 ·????????1114 (ER_RECORD_FILE_FULL) InnoDB耗盡表空間中的可用空間，你應該重新配置表空間來添加一個新數據文件。 ·????????1205 (ER_LOCK_WAIT_TIMEOUT) 鎖定等待超時過期。事務被回滾。 ·????????1213 (ER_LOCK_DEADLOCK) 事務死鎖。你應該重運行事務。 ·????????1216 (ER_NO_REFERENCED_ROW) 你正試著添加一行，但沒有父行，并且一個外鍵約束失敗。你應該先添加父行。 ·????????1217 (ER_ROW_IS_REFERENCED) 你正試圖刪除一個有子行的父行，并且一個外鍵約束失敗。你應該先刪除子行。 #### 15.2.15.2.?操作系統錯誤代碼要打印一個操作系統錯誤號的意思，請使用MySQL分發版里的**perror**程序。下面表提供一些常用Linux系統錯誤代碼。更完整的列表請參閱[Linuxsource code](http://www.iglu.org.il/lxr/source/include/asm-i386/errno.h)。 ·????????1 (EPERM) 操作不被允許 ·????????2 (ENOENT) 無此文件或目錄 ·????????3 (ESRCH) 無此進程 ·????????4 (EINTR) 中斷的系統調用 ·????????5 (EIO) I/O 錯誤 ·????????6 (ENXIO) 無此設備或地址 ·????????7 (E2BIG) Arg列表太長 ·????????8 (ENOEXEC) Exec合適錯誤 ·????????9 (EBADF) 壞文件號 ·????????10 (ECHILD)? 無子進程 ·????????11 (EAGAIN) 再試一次 ·????????12 (ENOMEM) 內存耗盡 ·????????13 (EACCES) 許可被否定 ·????????14 (EFAULT) 壞地址 ·????????15 (ENOTBLK) 阻止需求的設備 ·????????16 (EBUSY) 設備或資源忙 ·????????17 (EEXIST) 文件存在 ·????????18 (EXDEV) 交叉設備連接 ·????????19 (ENODEV) 無此設備 ·????????20 (ENOTDIR) 不是一個目錄 ·????????21 (EISDIR) 是一個目錄？ ·????????22 (EINVAL) 非法參量 ·????????23 (ENFILE) 文件表溢出 ·????????24 (EMFILE) 打開的文件過多 ·????????25 (ENOTTY) 設備不適合的ioctl ·????????26 (ETXTBSY) 文本文件忙 ·????????27 (EFBIG) 文件太大 ·????????28 (ENOSPC) 設備上沒空間了 ·????????29 (ESPIPE) 非法查找 ·????????30 (EROFS) 只讀文件系統 ·????????31 (EMLINK) 太多連接下列表提供一列常用Windows系統錯誤代碼。完整列表請訪問[Microsoft website](http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes.asp)。 ·????????1 (ERROR_INVALID_FUNCTION) 不正確函數。 ·????????2 (ERROR_FILE_NOT_FOUND) 系統不能找到指定的文件。 ·????????3 (ERROR_PATH_NOT_FOUND) 系統不能找到指定的路徑。 ·????????4 (ERROR_TOO_MANY_OPEN_FILES) 系統不能打開文件。 ·????????5 (ERROR_ACCESS_DENIED) 訪問被拒絕。 ·????????6 (ERROR_INVALID_HANDLE) 句柄非法。 ·????????7 (ERROR_ARENA_TRASHED) 存儲控制塊被破壞。 ·????????8 (ERROR_NOT_ENOUGH_MEMORY) 無足夠存儲來處理這個命令。 ·????????9 (ERROR_INVALID_BLOCK) 存儲控制塊地址非法。 ·????????10 (ERROR_BAD_ENVIRONMENT) 環境不正確。 ·????????11 (ERROR_BAD_FORMAT) 試圖用不正確的格式裝載一個程序。 ·????????12 (ERROR_INVALID_ACCESS) 訪問代碼不合法。 ·????????13 (ERROR_INVALID_DATA) 數據不合法。 ·????????14 (ERROR_OUTOFMEMORY) 無足夠的存儲來完成這個操作。 ·????????15 (ERROR_INVALID_DRIVE) 系統不能找到指定的驅動器。 ·????????16 (ERROR_CURRENT_DIRECTORY) 目錄不能被刪除。 ·????????17 (ERROR_NOT_SAME_DEVICE) 系統不能移動此文件到一個不同的磁盤驅動器。 ·????????18 (ERROR_NO_MORE_FILES) 沒有更多文件。 ·????????19 (ERROR_WRITE_PROTECT) 媒質寫保護。 ·????????20 (ERROR_BAD_UNIT) 系統不能找到指定的設備。 ·????????21 (ERROR_NOT_READY) 設備未準備好。 ·????????22 (ERROR_BAD_COMMAND) 設備不能識別此命令。 ·????????23 (ERROR_CRC) 數據錯誤(循環冗余碼校驗). ·????????24 (ERROR_BAD_LENGTH) 程序發出一個命令，但是命令長度不正確。 ·????????25 (ERROR_SEEK) 驅動器不能在磁盤上定位指定區域或磁道。 ·????????26 (ERROR_NOT_DOS_DISK) 指定的磁盤或軟盤不能被訪問。 ·????????27 (ERROR_SECTOR_NOT_FOUND) 驅動器不能找到請求的扇區。 ·????????28 (ERROR_OUT_OF_PAPER) 打印機缺紙。 ·????????29 (ERROR_WRITE_FAULT) 系統不能寫指定設備。 ·????????30 (ERROR_READ_FAULT) 系統不能從指定設備讀。 ·????????31 (ERROR_GEN_FAILURE) 附加到系統的設備不起作用。 ·????????32 (ERROR_SHARING_VIOLATION) 進程不能訪問文件，因為它正被另一個進程使用。 ·????????33 (ERROR_LOCK_VIOLATION) 進程不能訪問文件，因為另一個進程已經鎖定文件的一部分。 ·????????34 (ERROR_WRONG_DISK) 驅動器的的磁盤不正確，請插入 %2 (卷系列號: %3) 到驅動器 %1. ·????????36 (ERROR_SHARING_BUFFER_EXCEEDED) 太多文件被打開以共享。 ·????????38 (ERROR_HANDLE_EOF) 到達文件的末尾。 ·????????39 (ERROR_HANDLE_DISK_FULL) 磁盤已滿。 ·????????87 (ERROR_INVALID_PARAMETER) 參數不正確。（如果你在Windows中得到這個錯誤，并且已經在my.cnf或my.ini文件中設置innodb_file_per_table，則添加innodb_flush_method=unbuffered到你的my.cnf或my.ini文件）。 ·????????112 (ERROR_DISK_FULL) 磁盤已滿。 ·????????123 (ERROR_INVALID_NAME) 文件名，目錄名或者卷標語法不正確。 ·????????1450 (ERROR_NO_SYSTEM_RESOURCES) 存在系統資源不夠完成請求的服務。 ### 15.2.16.?對InnoDB表的限制 ·????????一個表不能包含超過1000列。 ·????????內部最大鍵長度是3500字節，但MySQL自己限制這個到1024字節。 ·????????除了VARCHAR, BLOB和TEXT列，最大行長度稍微小于數據庫頁的一半。即，最大行長度大約8000字節。LONGBLOB和LONGTEXT列必須小于4GB, 總的行長度，頁包括BLOB和TEXT列，必須小于4GB。InnoDB在行中存儲VARCHAR，BLOB或TEXT列的前768字節，余下的存儲的分散的頁面中。 ·????????雖然InnoDB內部地支持行尺寸大于65535，你不能定義一個包含VARCHAR列的，合并尺寸大于65535的行。 ~~~ ·??????????????? mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000), ~~~ ~~~ ·??????????????? ????-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000), ~~~ ~~~ ·??????????????? ????-> f VARCHAR(10000), g VARCHAR(10000)); ~~~ ~~~ ·??????????????? ERROR 1118 (42000): Row size too large. The maximum row size for the ~~~ ~~~ ·??????????????? used table type, not counting BLOBs, is 65535. You have to change some ~~~ ~~~ ·??????????????? columns to TEXT or BLOBs ~~~ ·????????在一些更老的操作系統上，數據文件必須小于2GB。 ·????????InnoDB日志文件的合并尺寸必須小于4GB。 ·????????最小的表空間尺寸是10MB。最大的表空間尺寸是4,000,000,000個數據庫頁（64TB）。這也是一個表的最大尺寸。 ·????????InnoDB表不支持FULLTEXT索引。 ·????????ANALYZE TABLE 通過對每個索引樹做八次隨機深入并相應地更新索引集估值，這樣來計數集。注意，因為這是僅有的估值，反復運行ANALYZE TABLE會產生不同數。這使得 ANALYZE TABLE 在 InnoDB 表上很快，不是百分百準確，因為它沒有考慮所有的行。 MySQL 不僅在匯合優化中使用索引集估值。如果一些匯合沒有以正確的方式優化，你可以試一下 ANALYZE TABLE 。很少有情況，ANALYZE TABLE 沒有產生對你特定的表足夠好的值，你可以使用 FORCE INDEX 在你查詢中來強制使用特定索引，或者設置 max_seeks_for_key 來確保MySQL在表掃描之上運行索引查找。請參閱[5.3.3節，“服務器系統變量”](# "5.3.3.?Server System Variables")。請參閱[A.6節，“優化器相關的問題”](# "A.6.?Optimizer-Related Issues")。 ·????????在Windows上，InnoDB總是內部地用小寫字母存儲數據庫和表名字。要把數據庫以二進制形式從Unix 移到Windows，或者從Windows移到Unix，你應該讓所有數據庫和表的名字都是小寫。 ·????????**警告:**?不要在MySQL數據庫內的把MySQL系統表從MyISAM轉為InnoDB表！這是一個不被支持的操作。如果你這么做了，MySQL直到你從備份恢復舊系統表，或用**mysql_install_db**腳本重建系統表才重啟動。 ·????????InnoDB在表內不保留行的內部計數。（因為多版本化，這可能確實有些復雜）。要處理一個SELECT COUNT(*) FROM t語句，InnoDB必須掃描表的一個索引，如果這個索引不在緩沖池中，掃描需要花一些時間。要獲得快速計數，你不得不使用一個自己創建的計數器表，并讓你的應用按照它做的插入和刪除來更新它。如果你的表格不經常改變，使用MySQL查詢緩存時一個好的解決方案。如果大致的行數就足夠了，則SHOW TABLE STATUS也可被使用。請參閱[15.2.11節，“InnoDB性能調節提示”](# "15.2.11.?InnoDB Performance Tuning Tips")。 ·????????對于AUTO_INCREMENT列，你必須總是為表定義一個索引，并且索引必須包含AUTO_INCREMENT列。在MyISAM表中，AUTO_INCREMENT列可能時多列索引的一部分。 ·????????當你重啟MySQL服務器之時，InnoDB可能為一個AUTO_INCREMENT列重使用一個舊值（即，一個被賦給一個老的已回滾的事務的值）。 ·????????當一個AUTO_INCREMENT列用完值，InnoDB限制一個BIGINT到－9223372036854775808以及BIGINT UNSIGNED到1。盡管如此，BIGINT值有由64位，所以注意到，如果你要一秒輸入100萬個行，在BIGINT到達它上限之前，可能還需要將近30萬年。用所有其它整數類型列，產生一個重復鍵錯誤。這類似于MyISAM如何工作的，因為它主要是一般MySQL行為，并不特別關于任何存儲引擎。 ·????????DELETE FROM *tbl_name*不重新生成表，但取而代之地刪除所有行，一個接一個地刪除。 ·????????TRUNCATE *tbl_name*為InnoDB而被映射到DELETE FROM *tbl_name* 并且不重置AUTO_INCREMENT計數器。 ·????????SHOW TABLE STATUS不能給出關于InnoDB表準確的統計數據，除了被表保留的物理尺寸。行計數僅是在SQL優化中粗略的估計。 ·????????在MySQL 5.1中，如果innodb_table_locks=1(1是默認值） MySQL LOCK TABLES操作在每一個表上獲取兩個鎖定。除了在MySQL層的表鎖定，它也獲得一個InnoDB表鎖定。舊版的MySQL不獲取InnoDB表鎖定，舊行為可以通過設置innodb_table_locks=0 來選擇。如果沒有InnoDB表鎖定被獲得，即使表的一些記錄被其它事務鎖定，LOCK TABLES完成。? ·????????所有被一個事務持有的InnoDB鎖定在該事務被提交或中止之時被釋放。因此在AUTOCOMMIT=1模式，在InnoDB表上調用是沒有太多意義的，因為被需求的InnoDB表鎖定可能會被立即釋放。 ·????????有時，在事務的過程中鎖定更多的表可能是有用的。不幸地，MySQL中的LOCK TABLES執行一個暗地的COMMIT和UNLOCK TABLES。LOCK TABLES的一個InnoDB變量已經被計劃，該計劃在事務的中間被執行。 ·????????為建立復制從服務器的LOAD TABLE FROM MASTER語句對InnoDB表不起作用。一個工作區在主服務器上更換表為MyISAM的，然后做負載，之后更換主服務器表回到InnoDB中。 ·????????在InnoDB中默認數據庫頁的大小是16KB。通過編譯代碼，你可以在8KB到64KB之間來設置這個值。你不得不更新在univ.i源文件中的UNIV_PAGE_SIZE和UNIV_PAGE_SIZE_SHIFT的值。 ·????????在MySQL 5.1中，觸發器不被級聯的外鍵行為激活。 ### 15.2.17.?InnoDB故障診斷和排除 [15.2.17.1.InnoDB數據詞典操作的錯誤診斷和排除](#) ·????????一個總的規則是，當一個操作失敗或這你懷疑有一個缺陷。你應該查看MySQL服務器的錯誤日志，該日志典型地有一個有些象*hostname*.err這樣的名字，或者在Windows上是mysql.err這樣的。 ·????????故障診斷與排除之時，通常最好從命令提示符運行MySQL服務器，而不是從**mysqld_safe**包運行，或不作為一個Windows服務來運行。你可以看**mysqld**打印到控制臺上的內容，因此更好掌握發生了什么。在Windows上，你必須用--console選項啟動服務器將輸出定向到控制臺窗口 ·????????使用InnoDB Monitors獲取關于某問題的信息。如果問題是性能相關的，或者你的服務器看起來被掛起，你應該使用innodb_monitor來打印InnoDB內部狀態的信息，如果問題是關于鎖定，則使用innodb_lock_monitor。如果問題是在表的創建或其它數據詞典操作，使用innodb_table_monitor來打印InnoDB內部數據詞典的內容。 ·????????如果你猜測一個表被破壞，則在該表上運行CHECK TABLE。 #### 15.2.17.1.?InnoDB數據詞典操作錯誤診斷和排除表的一個特殊問題是MySQL服務器以.frm文件來保存數據詞典信息，它被放在數據庫目錄，然而InnoDB也存儲信息到表空間文件里它自己的數據詞典里。如果你把.frm文件移來移去；或者，如果服務器在數據詞典操作的中間崩潰，.frm文件可能結束與InnoDB內部數據詞典的同步。一個不同步的數據詞典的癥狀是CREATE TABLE語句失敗。如果發生這種情況，你應該查看服務器的錯誤日志。如果日志說表已經存在于InnoDB內部數據詞典當中，你在InnoDB表空間文件內有一個孤表，它沒有對應的.frm文件。錯誤信息看起來象如下的： ~~~ InnoDB: Error: table test/parent already exists in InnoDB internal ~~~ ~~~ InnoDB: data dictionary. Have you deleted the .frm file ~~~ ~~~ InnoDB: and not used DROP TABLE? Have you used DROP DATABASE ~~~ ~~~ InnoDB: for InnoDB tables in MySQL version <= 3.23.43? ~~~ ~~~ InnoDB: See the Restrictions section of the InnoDB manual. ~~~ ~~~ InnoDB: You can drop the orphaned table inside InnoDB by ~~~ ~~~ InnoDB: creating an InnoDB table with the same name in another ~~~ ~~~ InnoDB: database and moving the .frm file to the current database. ~~~ ~~~ InnoDB: Then MySQL thinks the table exists, and DROP TABLE will ~~~ ~~~ InnoDB: succeed. ~~~ 你可以按照錯誤日志里給的指示移除一個孤表。如果還是不能成功地使用DROP TABLE，問題可能是因為在**mysql**客戶端里的名字完成。要解決這個問題，用--disable-auto-rehash選項來啟動**mysql**客戶端并再次嘗試DROP TABLE 。（有名字完成打開著，**mysql**試著構建個表名字的列表，當一個正如描述的問題存在之時，這個列表就不起作用）。不同步數據詞典的另一個“同義詞”是MySQL打印一個不能打開.InnoDB文件的錯誤： ~~~ ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1) ~~~ 在錯誤日志你可以發現一個類似于此的信息： ~~~ InnoDB: Cannot find table test/child2 from the internal data dictionary ~~~ ~~~ InnoDB: of InnoDB though the .frm file for the table exists. Maybe you ~~~ ~~~ InnoDB: have deleted and recreated InnoDB data files but have forgotten ~~~ ~~~ InnoDB: to delete the corresponding .frm files of InnoDB tables? ~~~ 這意味這有一個孤單的.frm文件，在InnoDB內沒有相對應的表。你可以通過手動刪除來移除這個孤單的.frm文件。如果MySQL在一個 ALTER TABLE操作的中間崩潰，你可以用InnoDB表空間內臨時孤表來結束。你可以用innodb_table_monitor看一個列出的表，名為#sql-...。如果你把表的名字包在`(backticks)里，你可以在名字包含“#”字符的表上執行SQL語句。因此，你可以用前述的的方法象移除其它孤表一樣移除這樣一個孤表。注意，要在Unix外殼里復制或重命名一個文件，如果文件名包含"#"字符，你需要把文件名放在雙引號里。 ### 15.3.?MERGE存儲引擎 [15.3.1. MERGE表的問題](#) MERGE存儲引擎，也被認識為MRG_MyISAM引擎，是一個相同的可以被當作一個來用的MyISAM表的集合。“相同”意味著所有表同樣的列和索引信息。你不能合并列被以不同順序列于其中的表，沒有恰好同樣列的表，或有不同順序索引的表。而且，任何或者所有的表可以用**myisampack**來壓縮。請參閱[8.2節，“myisampack — 生成壓縮的只讀MyISAM表”](# "8.2.?myisampack — Generate Compressed, Read-Only MyISAM Tables")。表選項的差異，比如AVG_ROW_LENGTH, MAX_ROWS或PACK_KEYS都不重要。當你創建一個MERGE表之時，MySQL在磁盤上創建兩個文件。文件名以表的名字開始，并且有一個擴展名來指明文件類型。一個.frm文件存儲表定義，一個.MRG文件包含被當作一個來用的表的名字。這些表作為MERGE表自身，不必要在同一個數據庫中。你可以對表的集合用SELECT, DELETE, UPDATE和INSERT。你必須對你映射到一個MERGE表的這些表有SELECT, UPDATE和DELETE 的權限。如果你DROP MERGE表，你僅在移除MERGE規格。底層表沒有受影響。當你創建一個MERGE表之時，你必須指定一個UNION=(*list-of-tables*)子句，它說明你要把哪些表當作一個來用。如果你想要對MERGE表的插入發生在UNION列表中的第一個或最后一個表上，你可以選擇地指定一個INSERT_METHOD選項。使用FIRST或LAST值使得插入被相應地做在第一或最后一個表上。如果你沒有指定INSERT_METHOD選項，或你用一個NO值指定該選項。往MERGE表插入記錄的試圖導致錯誤。下面例子說明如何創建一個MERGE表： ~~~ mysql> CREATE TABLE t1 ( ~~~ ~~~ ??? ->??? a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ~~~ ~~~ ??? ->??? message CHAR(20)); ~~~ ~~~ mysql> CREATE TABLE t2 ( ~~~ ~~~ ??? ->??? a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ~~~ ~~~ ??? ->??? message CHAR(20)); ~~~ ~~~ mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1'); ~~~ ~~~ mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2'); ~~~ ~~~ mysql> CREATE TABLE total ( ~~~ ~~~ ??? ->??? a INT NOT NULL AUTO_INCREMENT, ~~~ ~~~ ??? ->??? message CHAR(20), INDEX(a)) ~~~ ~~~ ??? ->??? TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; ~~~ 注意，一個列在MERGEN表中被索引，但沒有被宣告為一個PRIMARY KEY，因為它是在更重要的MyISAM表中。這是必要的，因為MERGE表在更重要的表中的設置上強制非唯一性。創建MERGE表之后，你可以發出把一組表當作一體來操作的查詢： ~~~ mysql> SELECT * FROM total; ~~~ ~~~ +---+---------+ ~~~ ~~~ | a | message | ~~~ ~~~ +---+---------+ ~~~ ~~~ | 1 | Testing | ~~~ ~~~ | 2 | table?? | ~~~ ~~~ | 3 | t1????? | ~~~ ~~~ | 1 | Testing | ~~~ ~~~ | 2 | table?? | ~~~ ~~~ | 3 | t2????? | ~~~ ~~~ +---+---------+ ~~~ 注意，你也可以直接從MySQL之外直接操作.MRG文件： ~~~ shell> cd /mysql-data-directory/current-database ~~~ ~~~ shell> ls -1 t1 t2 > total.MRG ~~~ ~~~ shell> mysqladmin flush-tables ~~~ 要重映射一個MERGE表到一個不同的MyISAM表集，你可以執行下列之一： ·????????DROP MERGE表并且重建它。 ·????????使用ALTER TABLE *tbl_name* UNION=(...)來改變底層表的列表。 ·????????改變.MRG文件，并對MERGE表或者所有底層表發出一個FLUSH TABLE語句來強制存儲引擎去讀新的定義文件。 MERGE表可以幫你解決以下問題： ·????????容易地管理一套日志表。比如，你可以把不同月的數據放進分離的表中，用**myisampack**壓縮其中的一些，并隨后創建一個MERGE表來把它們當作一個來使用。 ·????????獲得更快的速度。你可以以一些標準來分割大的只讀表，然后放進不同磁盤上的單個表中。基于此的一個MERGE表可比使用大表要快得多。 ·????????執行更有效的搜索。如果你確切知道要搜索什么，對一些查詢你可以只在被分割的表的其中之一來搜索，并且對其它使用MERGE。你甚至有許多不同的MERGE表，它們使用有重疊的表套。 ·????????執行更有效的修補。修補被映射到一個MERGE表中的單個表比修補單個大型表要更輕松。 ·????????即刻映射許多表成一個。MERGE表不需要維護它自己的索引，因為它使用大哥表的所用。因此，MERGE表集合是非常塊地創建或重映射。（注意，當你創建一個MERGE表之時，即使沒有索引被創建，你必須仍然指定索引定義）。 ·????????如果根據需要或按照批次，你有一組要合起來作為一個大表的表，你應該根據需要對它們創建一個MERGE表來替代大表。這樣要快得多而且節約大量的磁盤空間。 ·????????超過操作系統的文件尺寸限制。每個MyISAM表都受制于這個限制，但是一個MyISAM表的集合則不然。 ·????????你可以通過定義一個映射到單個表的MERGE表來為一個MyISAM表創建一個別名或“同物異名”。這樣做應該沒有真實的可察覺的性能影響（對每個讀只有一些間接調用和memcpy()調用）。 MERGE表的缺點： ·????????你可以對MERGE表使用僅相同的MyISAM表。 ·????????你不能在MERGE表中使用很多MyISAM功能。比如，你不能在MERGE表上創建FULLTEXT索引。（當然，你可以在底層MERGE 表上創建FULLTEXT索引，但是你不能用全文搜索來搜索MERGE表）。 ·????????如果MERGE表是非臨時的，所有底層MyISAM表也必須是永久的。如果MERGE表是臨時的，MyISAM表可以是任何臨時＆非臨時的混合。 ·????????MERGE表使用更多的文件描述符。如果是個客戶端正使用一個映射到10個表的MERGE表，服務器使用(10*10)+10個文件描述符。（10個數據文件描述符給10個客戶端每人一個，并且在客戶端之間共享10個索引文件描述符）。 · 鍵讀會更慢。當你讀一個鍵的時候，MERGE存儲引擎需要在所有底層表上發出一個讀以檢查哪一個接近匹配給定的鍵。如果你隨后做了一個read-next，MERGE存儲引擎需要搜索讀緩沖來找出下一個鍵。只有當一個鍵緩沖被耗盡，存儲引擎才需要讀下一個鍵塊。這使得MERGE鍵在eq_ref搜索中非常慢，但在ref搜索中不是太慢。請參閱[7.2.1節，“EXPLAIN 語法(獲取SELECT相關信息)”](# "7.2.1.?EXPLAIN Syntax (Get Information About a SELECT)") 以獲得更多關于eq_ref和ref的信息。 ### 15.3.1.?MERGE表方面的問題下列是已知關于MERGE表的問題： ·????????如果你使用ALTER TABLE 來把MERGE表變為其它表類型，到底層表的映射就被丟失了。取而代之的，來自底層MyISAM表的行被復制到已更換的表中，該表隨后被指定新類型。 ·????????REPLACE不起作用。 ·????????沒有WHERE子句，或者在任何被映射到一個打開的MERGE表上的任何一個表上的REPAIR TABLE，TRUNCATE TABLE, OPTIMIZE TABLE或ANALYZE TABLE，你不能使用DROP TABLE, ALTER TABLE, DELETE FROM。如果你這么做了，MERGE表將仍舊指向原始表，這樣產生意外結果。解決這個不足最簡單的辦法是在執行任何一個這些操作之前發出一個FLUSH TABLES語句來確保沒有MERGE表仍舊保持打開。 ·????????一個MERGE表不能在整個表上維持UNIQUE約束。當你執行一個INSERT, 數據進入第一個或者最后一個MyISAM表（取決于INSERT_METHOD選項的值）。MySQL確保唯一鍵值在那個MyISAM表里保持唯一，但不是跨集合里所有的表。 ·????????當你創建一個MERGE表之時，沒有檢查去確保底層表的存在以及有相同的機構。當MERGE表被使用之時，MySQL檢查每個被映射的表的記錄長度是否相等，但這并不十分可靠。如果你從不相似的MyISAM表創建一個MERGE表，你非常有可能撞見奇怪的問題。 ·????????在MERGE表中的索引的順序和它的底層表中的索引應該一樣。如果你使用ALTER TABLE給一個被用在MERGE表中的表添加一個UNIQUE索引，然后使用ALTER TABLE在MERGE表上添加一個非唯一索引，如果在底層表上已經有一個非唯一索引，對表的索引排序是不同的。（這是因為ALTER TABLE把UNIQUE索引放在非唯一索引之前以利于重復鍵的快速檢測）。因此對使用這樣索引的表的查詢可能返回不期望的結果。 ·????????在Windows中，在一個被MERGE表使用的表上DROP TABLE不起作用，因為MERGE引擎的表映射對MySQL的更上層隱藏。因為Windows不允許已打開文件的刪除，你首先必須刷新所有MERGE表（使用FLUSH TABLES）或在移除該表之前移除MERGE表。對于MERGE存儲引擎，在[http://forums.mysql.com/list.php?93](http://forums.mysql.com/list.php?93)上有一個專門的論壇。 ### 15.4.?MEMORY (HEAP)存儲引擎 MEMORY存儲引擎用存在內存中的內容來創建表。這些在以前被認識為HEAP表。MEMORY是一個首選的術語，雖然為向下兼容，HEAP依舊被支持。每個MEMORY表和一個磁盤文件關聯起來。文件名由表的名字開始，并且由一個.frm的擴展名來指明它存儲的表定義。要明確指出你想要一個MEMORY表，可使用ENGINE選項來指定： ~~~ CREATE TABLE t (i INT) ENGINE = MEMORY; ~~~ 如它們名字所指明的，MEMORY表被存儲在內存中，且默認使用哈希索引。這使得它們非常快，并且對創建臨時表非常有用。可是，當服務器關閉之時，所有存儲在MEMORY表里的數據被丟失。因為表的定義被存在磁盤上的.frm文件中，所以表自身繼續存在，在服務器重啟動時它們是空的。這個例子顯示你如何可以創建，使用并刪除一個MEMORY表： ~~~ mysql> CREATE TABLE test ENGINE=MEMORY ~~~ ~~~ ??? ->???? SELECT ip,SUM(downloads) AS down ~~~ ~~~ ??? ->???? FROM log_table GROUP BY ip; ~~~ ~~~ mysql> SELECT COUNT(ip),AVG(down) FROM test; ~~~ ~~~ mysql> DROP TABLE test; ~~~ MEMORY表有下列特征： ·????????給MEMORY表的空間被以小塊來分配。表對插入使用100%動態哈希來。不需要溢出區或額外鍵空間。自由列表無額外的空間需求。已刪除的行被放在一個以鏈接的列表里，并且在你往表里插入新數據之時被重新使用。MEMORY表也沒有通常與在哈希表中刪除加插入相關的問題。 ·????????MEMORY表可以有多達每個表32個索引，每個索引16列，以及500字節的最大鍵長度。 ·????????MEMORY存儲引擎執行HASH和BTREE索引。你可以通過添加一個如下所示的USING子句為給定的索引指定一個或另一個： ~~~ ·??????????????? CREATE TABLE lookup ~~~ ~~~ ·??????????????? ????(id INT, INDEX USING HASH (id)) ~~~ ~~~ ·??????????????? ????ENGINE = MEMORY; ~~~ ~~~ ·??????????????? CREATE TABLE lookup ~~~ ~~~ ·??????????????? ????(id INT, INDEX USING BTREE (id)) ~~~ ~~~ ·??????????????? ????ENGINE = MEMORY; ~~~ B樹的一般特征和哈希索引在[7.4.5節，“MySQL如何使用索引”](# "7.4.5.?How MySQL Uses Indexes")里描述。 ·????????你可以在一個MEMORY表中有非唯一鍵。（對哈希索引的實現，這是一個不常用的功能）。 ·????????你頁可以對MEMORY表使用INSERT DELAYED。請參閱[13.2.4.2節，“INSERT DELAYED語法”](# "13.2.4.2.?INSERT DELAYED Syntax") ·????????如果你在一個有高度鍵重復的（許多索引條目包含同一個值）MEMORY表上有一個哈希索引，對影響鍵值的表的更新及所有刪除都是明顯地慢的。這個變慢的程度比例于重復的程度（或者反比于索引cardinality）。你可以使用一個B樹索引來避免這個問題。 ·????????MEMORY表使用一個固定的記錄長度格式。 ·????????MEMORY不支持BLOB或TEXT列。 ·????????MEMORY支持AUTO_INCREMENT列和對可包含NULL值的列的索引。 ·????????MEMORY表在所有客戶端之間共享（就像其它任何非TEMPORARY表）。 ·????????MEMORY表內容被存在內存中，內存是MEMORY表和服務器在查詢處理之時的空閑中創建的內部表共享。可是，兩個類型的表不同在于MEMORY表不會遇到存儲轉換，而內部表是： -??????? 如果一個內部表變得太大，服務器自動把它轉換為一個磁盤表。尺寸限制由tmp_table_size系統變量的值來確定。 -??????? MEMORY表決不會轉換成磁盤表。要確保你不會偶爾做點傻事，你可以設置max_heap_table_size系統變量給MEMORY表加以最大尺寸。對于單個的表，你也可以在CREATE TABLE語句中指定一個MAX_ROWS表選項。 ·????????服務器需要足夠內存來維持所有在同一時間使用的MEMORY表。 ·????????當你不再需要MEMORY表的內容之時，要釋放被MEMORY表使用的內存，你應該執行DELETE FROM或TRUNCATE TABLE，或者整個地刪除表（使用DROP TABLE）。 ·????????當MySQL服務器啟動時，如果你想開拓MEMORY表，你可以使用--init-file選項。例如，你可以把INSERT INTO ... SELECT 或LOAD DATA INFILE這樣的語句放入這個文件中以便從持久穩固的的數據源裝載表。請參閱[5.3.1節，“**mysqld** 命令行選項”](# "5.3.1.?mysqld Command-Line Options") and?[13.2.5節，“LOAD DATA INFILE 語法”](# "13.2.5.?LOAD DATA INFILE Syntax")。 ·????????如果你正使用復制，當主服務器被關閉且重啟動之時，主服務器的MEMORY表變空。可是從服務器意識不到這些表已經變空，所以如果你從它們選擇數據，它就返回過時的內容。自從服務器啟動后，當一個MEMORY表在主服務器上第一次被使用之時，一個DELETE FROM語句被自動寫進主服務器的二進制日志，因此再次讓從服務器與主服務器同步。注意，即使使用這個策略，在主服務器的重啟和它第一次使用該表之間的間隔中，從服務器仍舊在表中有過時數據。可是，如果你使用--init-file選項于主服務器啟動之時在其上推行MEMORY表。它確保這個時間間隔為零。 ·????????在MEMORY表中，一行需要的內存使用下列表達式來計算： ~~~ ·??????????????? SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4) ~~~ ~~~ ·??????????????? + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2) ~~~ ~~~ ·??????????????? + ALIGN(length_of_row+1, sizeof(char*)) ~~~ ALIGN()代表round-up因子，它使得行的長度為char指針大小的確切倍數。sizeof(char*)在32位機器上是4，在64位機器上是8。對于MEMORY存儲引擎，在[http://forums.mysql.com/list.php?92](http://forums.mysql.com/list.php?92)上有一個專門的論壇。 ### 15.5.?BDB (BerkeleyDB)存儲引擎 [15.5.1. BDB支持的操作系統](#) [15.5.2. 安裝BDB](#) [15.5.3. BDB啟動選項](#) [15.5.4. BDB表的特征](#) [15.5.5. 修改BDB所需的事宜](#) [15.5.6.?BDB表的限制](#) [15.5.7. 使用BDB表時可能發生的錯誤](#) Sleepycat Software給MySQL提供Berkeley DB事務性存儲引擎。這個存儲引擎典型被簡稱為BDB。對BDB存儲引擎的支持包括在MySQL源碼分發版里，在MySQL-Max二進制分發版里被激活。 BDB表可能有一個更大的崩潰幸存機會，并且也具有對事務COMMIT和ROLLBACK操作的能力。MySQL源碼分發版和被補丁過可以與MySQL一起工作的BDB分發版一起提供。你不能使用一個未補丁過的BDB版本與MySQL一起工作。。我們在MySQL AB上與Sleepycat緊密合作工作以保持MySQL/BDB接口的質量在高水平。（即使Berkeley DB其本身是非常能經受考驗和非常可靠的。MySQL接口仍然被認為是第三等質量的。我們將繼續改善和優化它）。當它達到對所有涉及BDB表的問題的支持之時，我們答應負責幫助我們的用戶定位問題并創建可重復產生的測試案例。任何這樣的測試案例被轉交給Sleepycat，它反過來幫助我們找到并解決問題。因為這是一個二階段的操作，任何關于BDB表的問題我們可能要花比對其它存儲引擎稍微更長一點的時間來解決它。可是，我們期望這個過程沒有顯著的困難，因為Berkeley DB 代碼本身被用在MySQL之外許多的應用中。要獲得關于Berkeley DB的一般信息，請訪問Sleepycat網站，[http://www.sleepycat.com/](http://www.sleepycat.com/)。 ### 15.5.1.?BDB支持的操作系統當前，我們知道BDB存儲引擎支持下列操作系統： ·????????Linux 2.x Intel ·????????Sun Solaris (SPARC and x86) ·????????FreeBSD 4.x/5.x (x86, sparc64) ·????????IBM AIX 4.3.x ·????????SCO OpenServer ·????????SCO UnixWare 7.1.x ·????????Windows NT/2000/XP BDB不支持下列操作系統： ·????????Linux 2.x Alpha ·????????Linux 2.x AMD64 ·????????Linux 2.x IA-64 ·????????Linux 2.x s390 ·????????Mac OS X **注釋：**前一個列表還不完全，我們收到更多信息時我們會更新它。如果你從支持BDB表的源碼建立的MySQL，但是，當你啟動**mysqld**之時，發生下列錯誤，這意味著對你的架構BDB不被支持： ~~~ bdb: architecture lacks fast mutexes: applications cannot be threaded ~~~ ~~~ Can't init databases ~~~ 在這種情況下，你必須重建MySQL不帶BDB表支持或用--skip-bdb選項啟動服務器。 ### 15.5.2. 安裝BDB 如果你已經下載一個包括對Berkeley DB支持的的二進制版的MySQL，只要簡單地按照通常的二進制分發版安裝指令。（MySQL-Max 分發版包括BDB支持）。如果你從源碼建立MySQL，你可以在其它任何常用選項之外用--with-berkeley-db選項運行**configure**來允許支持BDB。下載一個MySQL 5.1分發版，改變位置到它的最頂層目錄，然后運行下面命令： ~~~ shell> ./configure --with-berkeley-db [other-options] ~~~ 更多信息，請參閱[2.7節，“在其它類似Unix系統上安裝MySQL”](# "2.7.?Installing MySQL on Other Unix-Like Systems"), [5.1.2節，“**mysqld-max**擴展MySQL服務器”](# "5.1.2.?The mysqld-max Extended MySQL Server"), 和[2.8節，“使用源碼分發版版安裝MySQL”](# "2.8.?MySQL Installation Using a Source Distribution")。 ### 15.5.3.?BDB啟動選項下列對**mysqld**的選項可被用來改變BDB存儲引擎的行為： ·????????--bdb-home=*path* BDB表的基礎目錄。這應該和你為--datadir使用的目錄相同。 ·????????--bdb-lock-detect=*method* BDB 鎖定檢測方式。選項值應該為DEFAULT, OLDEST, RANDOM或YOUNGEST。 ·????????--bdb-logdir=*path* BDB日志文件目錄。 ·????????--bdb-no-recover 不在恢復模式啟動Berkeley DB。 ·????????--bdb-no-sync 不同步刷新BDB日志。這個選項不被贊成，取而代之地使用--skip-sync-bdb-logs（請參閱對--sync-bdb-logs的描述)。 ·????????--bdb-shared-data 以多處理模式啟動Berkeley DB。（初始化Berkeley DB之時，不要使用DB_PRIVATE）。 ·????????--bdb-tmpdir=*path* BDB臨時文件目錄。 ·????????--skip-bdb 禁止BDB存儲引擎。 ·????????--sync-bdb-logs 同步刷新BDB日志。這個選項默認被允許，請使用--skip-sync-bdb-logs來禁止它。請參閱[5.3.1節，“**mysqld**命令行選項”](# "5.3.1.?mysqld Command-Line Options")。如果你使用--skip-bdb選項，MySQL不初始化Berkeley DB庫，而且這樣節省大量的內存。盡管如此，如果你使用這個選項，你不能使用BDB表。如果你試著創建一個BDB表，MySQL取而代之地創建一個MyISAM。通常，如果你象使用BDB表，你應該不用--bdb-no-recover選項啟動**mysqld**。可是，如果BDB日志被破壞則當你試著啟動**mysqld**時，上述辦法啟動服務器可能導致問題。請參閱[2.9.2.3節，“MySQL服務器的啟動和故障診斷排除”](# "2.9.2.3.?Starting and Troubleshooting the MySQL Server")。使用bdb_max_lock 變量，你可以指定在BDB表上被激活的鎖定的最大數目。默認值是10,000。當你執行長事務或當**mysqld**不得不檢查許多行來執行一個查詢之時，如果發生如下錯誤，你應該增加這個數目： ~~~ bdb: Lock table is out of available locks ~~~ ~~~ Got error 12 from ... ~~~ 如果你正使用大型多語句事務，你可能也想改變binlog_cache_size和max_binlog_cache_size變量。請參閱[5.11.3節，“二進制日志”](# "5.11.3.?The Binary Log")。也請參閱[5.3.3節，“服務器系統變量”](# "5.3.3.?Server System Variables")。 ### 15.5.4.?BDB表的特征每個BDB表用兩個文件被存在磁盤上。文件的名字用表的名字做開頭，并且有一個擴展名來指明文件類型。一個.frm文件存儲表定義，一個.db文件包含表數據和索引。要明確指出你想要一個BDB表，用ENGINE或TYPE表選項來指明： ~~~ CREATE TABLE t (i INT) ENGINE = BDB; ~~~ ~~~ CREATE TABLE t (i INT) TYPE = BDB; ~~~ BerkeleyDB是用ENGINE或者TYPE選項的BDB存儲引擎的“同義詞”。 BDB存儲引擎提供事務性表，你使用這些表的方法取決于autocommit模式： ·????????如果你正運行著，同時隨著autocommit的被允許（這是默認的），對BDB表的改變被立即提交并且不能被回滾。 ·????????如果你正運行著，同時隨著autocommit的被禁止，改變不變成永久的直到你執行一個COMMIT語句。作為提交的替代，你可以執行ROLLBACK來忘記改變。你可以用BEGIN WORK語句開始一個事務來掛起autocommit，或者用SET AUTOCOMMIT=0來明確禁止autocommit。請參閱[13.4.1節，“START TRANSACTION, COMMIT和ROLLBACK語法”](# "13.4.1.?START TRANSACTION, COMMIT, and ROLLBACK Syntax")。 BDB存儲引擎有下列特征： ·????????BDB表可以有多達每表31個索引，每個索引16列，并且1024字節的最大鍵尺寸。 ·????????MySQL在每個BDB表中需要一個PRIMARY KEY以便每一行可以被唯一地識別。如果你不明確創建一個，MySQL為你創建并維持一個隱藏的PRIMARY KEY。隱藏的鍵有一個5字節的長度，并且為每個插入的企圖而被增加。這個鍵不出現在SHOW CREATE TABLE或DESCRIBE的輸出之中。 ·????????PRIMARY KEY比任何其它索引都要快，因為PRIMARY KEY被與行的數據一起存儲。其它索引被存儲為鍵數據＋PRIMARY KEY，所以保持PRIMARY KEY盡可能地短以節約磁盤空間并獲得更好速度是重要的。這個行為類似于InnoDB的，在其中較短的primary keys不僅在主索引也在第二索引節約空間。 ·????????如果在BDB表中，你訪問的所有列是同一索引的一部分或主鍵的一部分，MySQL可以執行查詢而不訪問確實的行。在一個MyISAM表中，只有列是同一索引的一部分之時，才可以這么做。 ·????????連續掃描比對MyISAM表的掃描更慢，因為在BDB表中的數據被存儲在B樹而不是在分離的數據文件中。 ·????????鍵值象MyISAM表中的鍵值一樣不是前綴或后綴壓縮的。換句話說，在BDB表中鍵信息只比在MyISAM表中稍微多占據一點空間。 ·????????在BDB表中經常有洞允許你在索引樹的中間插入新行。這個使得BDB表比MyISAM表稍微大一些 ·????????SELECT COUNT(*) FROM *tbl_name*對BDB表很慢，因為在該表中沒有行計數被維持。 ·????????優化器需要知道表中的大概行數。MySQL通過計數插入以及在每個BDB表中的單獨片斷內維持它來解決了問題。如果你不發出大量的DELETE或ROLLBACK語句，這個數對MySQL優化器來說是足夠精確了。可是，MySQL僅在關閉的時候才存儲這個數，所以，如果服務器被意外地終止，這個數可能是不正確的。即使數不是100%正確，它不是明確的。你可以使用ANALYZE TABLE或者 OPTIMIZE TABLE語句來更新行計數。請參閱[13.5.2.1節，“ANALYZE TABLE語法”](# "13.5.2.1.?ANALYZE TABLE Syntax") 和[13.5.2.5節，“OPTIMIZE TABLE語法”](# "13.5.2.5.?OPTIMIZE TABLE Syntax")。 ·????????BDB表上的內部鎖定在頁面級別上做。 ·????????LOCK TABLES在BDB表上就想在其它表上一樣工作。如果你不使用LOCK TABLES，MySQL對該表發出一個內部多重寫鎖定（一個不阻止其它作者的鎖定）來確保即使另一個線程發出一個表鎖定，該表也被恰當地鎖定了。 ·????????要能夠回滾一個事務，BDB存儲引擎維護日志文件。多實現最高性能，你可以使用--bdb-logdir選項來把BDB日志放在不同的磁盤上，而不是放在數據庫被放置的那個磁盤。 ·????????每次新BDB日志文件被啟動之時，MySQL執行一個檢查點，并且刪掉當前事務不需要的任何BDB日志文件。你也可以在任何時候使用FLUSH LOGS來給Berkeley DB表設置檢查點。對災難恢復，你應該使用表備份加MySQL的二進制日志，請參閱[5.9.1節，“數據庫備份”](# "5.9.1.?Database Backups")。 **警告：**如果你刪除仍在使用中的舊日志文件，BDB根本不能做恢復，并且如果有些事不對，你可能會丟失數據。 ·????????應用程序必須總是被準備來處理情況，即BDB表任何的改變可能導致一個自動回滾并且任何讀可能會帶著一個死鎖錯誤而失敗。 ·????????如果你在BDB表內遇到磁盤滿，你得到一個錯誤（可能是錯誤28），并且事務應該回滾。這與MyISAM表相反，對于MyISAM 表，**mysqld**在繼續之前等待足夠的自由磁盤空間。 ### 15.5.5. 修改BDB所需的事宜 ·????????同時打開許多BDB表可能是非常慢的。如果你要使用BDB表，你不應使用非常大的表緩存（例如，大于256）并且當你使用**mysql**客戶端之時，你應該使用--no-auto-rehash選項。 ·????????SHOW TABLE STATUS 不為BDB表提供一些信息： ~~~ ·??????????????? mysql> SHOW TABLE STATUS LIKE 'bdbtest'\G ~~~ ~~~ ·??????????????? *************************** 1. row *************************** ~~~ ~~~ ·??????????????? ???????????Name: bdbtest ~~~ ~~~ ·??????????????? ?????????Engine: BerkeleyDB ~~~ ~~~ ·??????????????? ????????Version: 10 ~~~ ~~~ ·??????????????? ?????Row_format: Dynamic ~~~ ~~~ ·??????????????? ???????????Rows: 154 ~~~ ~~~ ·??????????????? ?Avg_row_length: 0 ~~~ ~~~ ·??????????????? ????Data_length: 0 ~~~ ~~~ ·??????????????? Max_data_length: 0 ~~~ ~~~ ·??????????????? ???Index_length: 0 ~~~ ~~~ ·??????????????? ??????Data_free: 0 ~~~ ~~~ ·??????????????? ?Auto_increment: NULL ~~~ ~~~ ·??????????????? ????Create_time: NULL ~~~ ~~~ ·??????????????? ????Update_time: NULL ~~~ ~~~ ·??????????????? ?????Check_time: NULL ~~~ ~~~ ·??????????????? ??????Collation: latin1_swedish_ci ~~~ ~~~ ·??????????????? ???????Checksum: NULL ~~~ ~~~ ·??????????????? ?Create_options: ~~~ ~~~ ·??????????????? ????????Comment: ~~~ ·????????優化性能。 ·????????改變為對表掃描操作使用無頁面鎖定。 ### 15.5.6.?對BDB表的限制下列表說明使用BDB表之時你必須要遵從的限制： ·????????每個BDB表在.db文件里存儲文件被創建之時到該文件的路徑。這個被做來允許在支持symlinks的多用戶環境里檢測鎖定。因此，從一個數據庫目錄往另一個目錄移動BDB表是不能的。 ·????????當制作BDB表的備份之時，你必須要么使用**mysqldump**要么做一個包含對每個BDB表的文件（.frm和.db文件）及BDB日志文件的備份。BDB存儲引擎在它的日志文件存儲未完成的事務以及要求它們在**mysqld**啟動的時候被呈出來。BDB日志在數據目錄里，具有log.XXXXXXXXXX（10位數字）形式名字的文件。 ·????????如果允許NULL值的列有唯一的索引，只有單個NULL值是被允許的。這不同于其它存儲引擎。 ### 15.5.7.?使用BDB表時可能發生的錯誤 ·????????如果你升級之后啟動**mysqld**時發生下列錯誤，它意味著新BDB版本不支持舊日志文件格式： ~~~ ·??????????????? bdb:? Ignoring log file: .../log.XXXXXXXXXX: ~~~ ~~~ ·??????????????? unsupported log version # ~~~ 在這種情況下，你必須刪除從數據目錄所有BDB日志（名字為log.XXXXXXXXXX這樣格式的文件）并重新啟動**mysqld**。我們也推薦你隨后用**mysqldump --opt**來轉儲你的BDB表，移除表，并且從轉儲文件恢復它們。 ·????????如果autocommit模式被禁止，你移除在另一個事務中被參考的BDB表，你會從你的MySQL錯誤日志得到如下的錯誤信息： ~~~ ·??????????????? 001119 23:43:56? bdb:? Missing log fileid entry ~~~ ~~~ ·??????????????? 001119 23:43:56? bdb:? txn_abort: Log undo failed for LSN: ~~~ ~~~ ·??????????????? ???????????????????????1 3644744: Invalid ~~~ 這不是一個致命錯誤，但是知道問題被解決，我們推薦你不要移除BDB表，除了autocommit模式被允許之時。（修復不是微不足道的）。 ### 15.6.?EXAMPLE存儲引擎 EXAMPLE存儲引擎是一個不做任何事情的存根引擎。它的目的是作為MySQL源代碼中的一個例子，用來演示如何開始編寫一個新存儲引擎。同樣，它的主要興趣是對開發者。要對EXAMPLE引擎檢查源碼，請查看MySQL源碼分發版的sql/examples目錄。要允許這個存儲引擎，使用在建立MySQL之時使用--with-example-storage-engine選項來**configure**。當你創建一個EXAMPLE之時，服務器創建在數據庫目錄創建一個表定義文件。文件由表名字開始，并由一個.frm的擴展名。沒有其它文件被創建。沒有數據被存儲進表或者從中取回。 ~~~ mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE; ~~~ ~~~ Query OK, 0 rows affected (0.78 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> INSERT INTO test VALUES(1),(2),(3); ~~~ ~~~ ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option ~~~ ~~~ ? ~~~ ~~~ mysql> SELECT * FROM test; ~~~ ~~~ Empty set (0.31 sec) ~~~ EXAMPLE存儲引擎不支持編索引。 ### 15.7.?FEDERATED存儲引擎 [15.7.1. 安裝FEDERATED存儲引擎](#) [15.7.2. FEDERATED存儲引擎的介紹](#) [15.7.3. 如何使用FEDERATED表](#) [15.7.4.?FEDERATED存儲引擎的局限性](#) FEDERATED存儲引擎訪問在遠程數據庫的表中的數據，而不是本地的表。 FEDERATED存儲引擎僅在－MAX版的MySQL上可用。要為FEDERATED引擎檢查源碼，請查看MySQL源碼分發版的sql目錄。對于FEDERATED存儲引擎，在[http://forums.mysql.com/list.php?105](http://forums.mysql.com/list.php?105)上有一個專門的論壇。 ### 15.7.1. 安裝FEDERATED存儲引擎要允許這個存儲引擎，當你構建MySQL時請使用--with-federated-storage-engine來**configure**。 ### 15.7.2.?FEDERATED存儲引擎的描述當你創建一個FEDERATED表的時候，服務器在數據庫目錄創建一個表定義文件。文件由表的名字開始，并有一個.frm擴展名。無其它表被創建，因為實際的數據在一個遠程數據庫上。這不同于為本地表工作的存儲引擎的方式。對于本地的數據錄表，數據文件是本地的。例如，如果你創建一個名為user的MyISAM表，MyISAM處理器創建一個名為users.MYD的數據文件。對本地表讀，插入，刪除和更新在本地數據文件里的數據的處理器和記錄被以對處理器的特定格式存儲。為了讀記錄，處理器必須把數據分解進列。為了寫記錄，列值必須被轉換到被處理器使用的行格式并且被寫進本地的數據文件。使用MySQL FEDERATED存儲引擎，沒有對表的本地數據文件（比如，沒有.MYD文件）。取而代之地，一個遠程數據庫存儲那些正常地應該在表中的數據。這使得MySQL客戶端API來讀，刪除，更新和插入數據的使用成為必要。數據取回被通過SELECT * FROM *tbl_name* SQL語句來初始化。要讀這個結果，通過使用mysql_fetch_row() C API函數，行被一次取一個，然后從SELECT結果包中的列轉換成FEDERATED處理器期望的格式。基本流程如下： 1.??? SQL調用被本地發布 2.??? MySQL處理器API （數據以處理器格式） 3.??? MySQL客戶端API （數據被轉換成SQL調用） 4.??? 遠程數據庫-> MySQL客戶端API 5.??? 轉換結果包（如果有的話）到處理器格式 6.??? 處理器 API -> 結果行或受行影響的對本地的計數 ### 15.7.3. 如何使用FEDERATED表使用FEDERATED表的步驟是非常簡單的。通常，你運行兩個服務器，要么在同一個主機上，要么在不同主機上。（一個FEDERATED表使用其它被同一服務器管理的表也是可能的。雖然只有極少的點要這么做）。首先，你必須在你想要用FEDERATED表訪問的遠程服務器上有一個表。假設，遠程的表在FEDERATED數據庫中并且被如下定義： ~~~ CREATE TABLE test_table ( ~~~ ~~~ ??? id???? int(20) NOT NULL auto_increment, ~~~ ~~~ ??? name?? varchar(32) NOT NULL default '', ~~~ ~~~ ??? other? int(20) NOT NULL default '0', ~~~ ~~~ ??? PRIMARY KEY? (id), ~~~ ~~~ ??? KEY name (name), ~~~ ~~~ ??? KEY other_key (other) ~~~ ~~~ ) ~~~ ~~~ ENGINE=MyISAM ~~~ ~~~ DEFAULT CHARSET=latin1; ~~~ ENGINE表選項可能命名任何存儲引擎，該表需要不是一個MyISAM表。接著，在本地服務器上為訪問遠程表創建一個FEDERATED表： ~~~ CREATE TABLE federated_table ( ~~~ ~~~ ??? id???? int(20) NOT NULL auto_increment, ~~~ ~~~ ??? name?? varchar(32) NOT NULL default '', ~~~ ~~~ ??? other? int(20) NOT NULL default '0', ~~~ ~~~ ??? PRIMARY KEY? (id), ~~~ ~~~ ??? KEY name (name), ~~~ ~~~ ??? KEY other_key (other) ~~~ ~~~ ) ~~~ ~~~ ENGINE=FEDERATED ~~~ ~~~ DEFAULT CHARSET=latin1 ~~~ ~~~ CONNECTION='mysql://root@remote_host:9306/federated/test_table'; ~~~ （**注意**: CONNECTION 替代用在先前版本的MySQL里的COMMENT）。除了ENGINE表選項應該是FEDERATED，并且CONNECTION表選項是給FEDERATED指明如何連接到遠程服務器上的連接字符串之外，這個表的結構必須完全與遠程表的結構相同。 FEDERATED引擎僅創建在已聯盟數據庫中的test_table.frm文件。遠程主機信息指明本地服務器要連接到的遠程服務器，數據庫和表信息指明哪一個遠程表要被作為數據文件來用。在這個例子中。遠程服務器被指定來作為遠程主機在9306端口上運行，所以你要啟動服務器，讓它監聽9306端口。在CONNECTION選項中的連接字符串的一般形式如下： ~~~ scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name ~~~ 只有mysql在這一點被支持為*scheme*，密碼和端口號時可選的。這里有一些連接字符串的例子： ~~~ CONNECTION='mysql://username:password@hostname:port/database/tablename' ~~~ ~~~ CONNECTION='mysql://username@hostname/database/tablename' ~~~ ~~~ CONNECTION='mysql://username:password@hostname/database/tablename' ~~~ 為指定連接字符串使用CONNECTION是非可選，并且在將來可能會改變。當你使用FEDERATED表的時候，要記得這個，因為這意味著當將來發生那種改變之時，可能被要求。因為任何被用的密碼作為純文本被存在連接字符串中，它可以被任何使對FEDERATED表使用SHOW CREATE TABLE或SHOW TABLE STATUS的用戶，或者在INFORMATION_SCHEMA數據庫中查詢TABLES表的用戶看見。對于FEDERATED存儲引擎，在[http://forums.mysql.com/list.php?105](http://forums.mysql.com/list.php?105)上有一個專門的論壇。 ### 15.7.4.?FEDERATED存儲引擎的局限性 FEDERATED支持及不支持的如下： ·????????在第一個版本中，遠程服務器必須是一個MySQL服務器。FEDERATED對其它數據庫引擎的支持可能會在將來被添加。 ·????????FEDERATED表指向的遠程表在你通過FEDERATED表訪問它之前必須存在。 ·????????一個FEDERATED表指向另一個FEDERATED表是可能的，但是你必須小心不要創建一個循環。 ·????????沒有對事務的支持。 ·????????如果遠程表已經改變，對FEDERATED引擎而言是沒有辦法知道的。這個的原因是因為這個表必須象數據文件一樣工作，除了數據庫其它任何都不會被寫入。如果有任何對遠程數據庫的改變，本地表中數據的完整性可能會被破壞。 ·????????FEDERATED存儲引擎支持SELECT, INSERT, UPDATE, DELETE和索引。它不支持ALTER TABLE, DROP TABLE或任何其它的數據定義語言語句。當前的實現不使用預先準備好的語句。 ·????????執行使用SELECT, INSERT, UPDATE和DELETE，但不用HANDLER。 ·????????FEDERATED表不能對查詢緩存不起作用。這些限制中的一些在FEDERATED處理機的將來版本可能被消除。 ### 15.8.?ARCHIVE存儲引擎 ARCHIVE存儲引擎被用來以非常小的覆蓋區存儲大量無索引數據。? 要允許這個存儲引擎，在建立MySQL之時使用--with-archive-storage-engine選項來**configure**。如果這個存儲引擎可帶這個語句使用，你可以看見： ~~~ mysql> SHOW VARIABLES LIKE 'have_archive'; ~~~ 當年創建一個ARCHIVE表，服務器在數據庫目錄創建一個表定義文件。文件由表的名字開始，并由一個.frm的擴展名。存儲引擎創建其它文件，所有都有由表名字開頭的名字。數據和元數據文件有擴展名.ARZ和.ARM。一個.ARN文件可能在優化操作中出現。 ARCHIVE引擎僅支持INSERT和SELEC（無刪除，替換或更新）。它支持ORDER BY操作，BLOB 域，以及基本地所有數據類型，除了幾何數據類型（請參閱[19.4.1節，“MySQL 空間數據類型”](# "19.4.1.?MySQL Spatial Data Types")）。ARCHIVE引擎使用行級鎖定。 **存儲:** 當記錄被插入時，它們被壓縮。ARCHIVE引擎使用[zlib](http://www.zlib.net/)無損數據壓縮。OPTIMIZE TABLE的使用可以分析表，并把它打包為更小的格式（使用OPTIMIZE TABLE的原因，往下看）。引擎頁支持CHECK TABLE。有數種被使用的插入類型： ·????????直接插入INSERT之時把一行推僅壓縮緩沖，并且緩沖在它需要的時候刷新。到緩沖的插入被鎖定保護。一個SELECT強制一個刷新發生，除非進來的唯一插入是INSERT DELAYED（那些刷新如同需要一樣）。請參閱[13.2.4.2節，“INSERT DELAYED 語法”](# "13.2.4.2.?INSERT DELAYED Syntax")。 ·????????塊插入旨在它完成后可見，除非其它插入在同一時間發生，在這種情況下，塊插入可以被部分看見。一個SELECT不會導致一個塊插入的刷新，除非SELECT在被裝載時發生一個正常插入。 **取回：** 在取回時，記錄根據需要被解壓縮，沒有行緩存。一個SELECT操作執行完全表格掃描：當一個SELECT發生之時，它找出當前有多少行可用，并讀行的數量。SELECT被當作持續讀來執行。注意，許多SELECT語句在插入過程中會損壞壓縮，除非塊插入或者延遲的插入被使用。要修復任何已發生壓縮問題，你可以總是做OPTIMIZE TABLE（也支持REPAIR TABLE）。被SHOW TABLE STATUS報告的行數總是正確的。請參閱[13.5.2.6節，“REPAIR TABLE語法”](# "13.5.2.6.?REPAIR TABLE Syntax")，[13.5.2.5節，“OPTIMIZE TABLE語法”](# "13.5.2.5.?OPTIMIZE TABLE Syntax")，[13.5.4.18節，“SHOW TABLE STATUS 語法”](# "13.5.4.18.?SHOW TABLE STATUS Syntax")。對于ARCHIVE存儲引擎，在[http://forums.mysql.com/list.php?112](http://forums.mysql.com/list.php?112) 上有專門論壇。 ### 15.9.?CSV存儲引擎 CSV存儲引擎使用逗號分隔值格式的文本文件存儲數據。要允許使用這個存儲引擎，當你建立MySQL之時，使用--with-csv-storage-engine選項來**configure**。當你創建一個CSV表之時，服務器在數據庫目錄創建一個表定義文件。文件由表的名字開始，并且由一個.frm的擴展名。存儲引擎也創建一個數據文件。它的名字由表的名字開始，并且有一個.CSV的擴展名。數據文件是無格式文本文件。當你把數據存儲進表時，存儲引擎用CSV格式把它存進數據文件。 ~~~ mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV; ~~~ ~~~ Query OK, 0 rows affected (0.12 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two'); ~~~ ~~~ Query OK, 2 rows affected (0.00 sec) ~~~ ~~~ Records: 2? Duplicates: 0? Warnings: 0 ~~~ ~~~ ? ~~~ ~~~ mysql> SELECT * FROM test; ~~~ ~~~ +------+------------+ ~~~ ~~~ | i??? | c????????? | ~~~ ~~~ +------+------------+ ~~~ ~~~ |??? 1 | record one | ~~~ ~~~ |??? 2 | record two | ~~~ ~~~ +------+------------+ ~~~ ~~~ 2 rows in set (0.00 sec) ~~~ 如果你檢查在執行前述語句所創建的數據庫目錄里的test.CSV文件，它的內容應該看起來象如下所示： ~~~ "1","record one" ~~~ ~~~ "2","record two" ~~~ CSV存儲引擎不支持索引。 ### 15.10.?BLACKHOLE存儲引擎 BLACKHOLE存儲引擎就像“黑洞”一樣，它接收數據但丟棄它而不是存儲它。取回總是返回空集： ~~~ mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE; ~~~ ~~~ Query OK, 0 rows affected (0.03 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two'); ~~~ ~~~ Query OK, 2 rows affected (0.00 sec) ~~~ ~~~ Records: 2? Duplicates: 0? Warnings: 0 ~~~ ~~~ ? ~~~ ~~~ mysql> SELECT * FROM test; ~~~ ~~~ Empty set (0.00 sec) ~~~ 當你創建一個BLACKHOLE表的時候，服務器在數據庫目錄創建一個表定義文件。文件用表的名字開頭，并且有一個.frm擴展名。沒有其它文件關聯到這個表格。 BLACKHOLE存儲引擎支持所有種類的索引。要允許這個存儲引擎，在你建立MySQL之時使用--with-blackhole-storage-engine選項來**configure**。BLACKHOLE存儲引擎在MySQ供應的服務器二進制版里可以找到；通過查看SHOW ENGINES或SHOW VARIABLES LIKE 'have%'的輸出，你可以確定你的版本是否支持這個引擎。到BLACKHOLE表的插入不存儲任何數據，但如果二進制日志被允許，SQL語句被寫入日志（并被復制到從服務器）。這可以有用幫助地作為重復器或過濾器機制。例如，假設你的應用需要從服務器側的過濾規則，但傳輸所有二進制日志數據到從服務器首先導致過多交通的結果。在這種情況下，在主服務器主機上建立一個偽從服務器進程，它的存儲引擎是BLACKHOLE，描述如下： ![Replication using BLACKHOLE for filtering](https://box.kancloud.cn/2015-11-02_5636c7c44abf1.png) 主服務器寫它的二進制日志。偽mysqld進程作為從服務器，應用期望的replicate-do和replicate-ignore規則的合并，并且寫一個新的，被過濾的屬于它自己的二進制日志。（請參閱[6.8節，“復制啟動選項”](# "6.8.?Replication Startup Options")）。這個已過濾日志被提供給從服務器。既然偽進程不確實地存儲任何數據，只有很小的由在復制主服務器主機上額外的mysqld進程招致的處理開支。這個類型的建立可以用額外復制從服務器來重復。其它可能對BLACKHOLE存儲引擎的使用包括： ·????????轉儲文件語法的驗證。 ·????????來自二進制日志記錄的開銷測量，通過比較允許二進制日志功能的BLACKHOLE的性能與禁止二進制日志功能的BLACKHOLE的性能。 ·????????因為BLACKHOLE本質上是一個“no-op” 存儲引擎，它可能被用來查找與存儲引擎自身不相關的性能瓶頸。這是MySQL參考手冊的翻譯版本，關于MySQL參考手冊，請訪問[dev.mysql.com](http://dev.mysql.com/doc/mysql/en)。原始參考手冊為英文版，與英文版參考手冊相比，本翻譯版可能不是最新的。 ?