第15章：儲存引擎和資料表類型

·         --myisam-recover=mode

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

·         --delay-key-write=ALL

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

註釋：如果您要這麼做。當資料表在使用中之時，您應該不使用來自另一個程式的MyISAM資料表（比如從另一個MySQL伺服器或用myisamchk）。這麼做會導致索引被破壞。

對使用--delay-key-write的資料表，使用--external-locking沒有幫助。

·         bulk_insert_buffer_size

用在塊插入最佳化中的樹緩衝區的大小。註釋：這是一個per thread的限制。

·         (OBSOLETE) myisam_max_extra_sort_file_size

這個參數已經不在MySQL中使用。

·         myisam_max_sort_file_size

如果臨時檔案會變得超過索引，不要使用快速排序索引方法來建立一個索引。註釋：這個參數以字節的形式給出。

·         myisam_sort_buffer_size

設置恢復資料表之時使用的緩衝區的尺寸。

·         資料表被查錯。

·         如果伺服器發現一個錯誤，它試著做快速資料表修復（排序且不重新建立數據檔案）。

·         如果修復因為數據檔案中的一個錯誤而失敗（例如，一個重複鍵錯誤），伺服器會再次嘗試修復，這一次重建數據檔案。

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

15.1.3.1. 靜態（固定長度）資料表特徵

15.1.3.2. 動態資料表特徵

15.1.3.3. 已壓縮資料表特徵

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

MyISAM的三種儲存格式中，靜態格式就最簡單也是最安全的（至少對於崩潰而言）。靜態格式也是最快的on-disk格式。快速來自於數據檔案中的行在磁盤上被找到的容易方式：當按照索引中的行號搜尋一個行時，用行長度乘以行號。同樣，當掃瞄一個資料表的 時候，很容易用每個磁盤讀操作讀一定數量的記錄。

當MySQL伺服器正往一個固定格式MyISAM檔案寫的時候，如果計算機崩潰了，安全是顯然的。在這種情況下，myisamchk可以容易地決定每行從哪裡開始到哪裡結束，所以它通常可以收回所有記錄，除了寫了一部分的記錄。注意，基於數據行，MyISAM資料表索引可以一直被重新構建。

靜態格式資料表的一般特徵：

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

這個格式更為複雜一點，因為每行有一個表明行有多長的頭。當一個記錄因為更新的結果被變得更長，該記錄也可以在超過一個位置處結束。

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

動態格式資料表的一般特徵：

·                3

·                + (number of columns + 7) / 8

·                + (number of char columns)

·                + (packed size of numeric columns)

·                + (length of strings)

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

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

所有MySQL分發版裡都預設包括myisampack。已壓縮資料表可以用myisamchk來解壓縮。

已壓縮資料表有下列特徵：

15.1.4.1. 損壞的MyISAM資料表

15.1.4.2. 未被適當關閉的資料表的問題

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

一個損壞的資料表的典型症狀如下：

·                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.6節，「REPAIR TABLE語法”，和5.9.5節，「myisamchk — MyISAM資料表維護工具”。

如果您的資料表變得頻繁損壞，您應該試著確定為什麼會這樣的原因。要明白的最重要的事是資料表變得損壞是不是因為伺服器崩潰的結果。您可以在錯誤日誌中搜尋最近的restarted mysqld消息來早期驗證這個。如果存在這樣一個消息，則資料表損壞是伺服器死掉的一個結果是很有可能的。否則，損壞可能在正常操作中發生。這是一個問題。您應該試著建立一個展示這個問題的可重複生成的測試案例。請參閱A.4.2節，「如果MySQL保持崩潰，該怎麼做” 及E.1.6節，「如果出現資料表崩潰，請生成測試案例”。

每個MyISAM索引檔案(.MYI)在頭有一個計數器，它可以被用來檢查一個資料表是否被恰當地關閉。如果您從CHECK TABLE或myisamchk得到下列警告，意味著這個計數器已經不同步了：

clients are using or haven't closed the table properly

這個警告並不是完全意味著資料表已被破壞，但您至少應該檢查資料表。

計數器的工作方式如下：

換句話來說，計數器只有在下列情況會不同步：

·         只應該建立一個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檔案的位置”。

·         /etc/my.cnf

全局選項。

·         $MYSQL_HOME/my.cnf

伺服器專用選項。

·         defaults-extra-file

--defaults-extra-file選項指定的檔案。

·         ~/.my.cnf

用戶專用選項。

·         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節，「為資料表空間使用原始設備”。

·         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資料表空間”。

·         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節，「強制恢復”中敘述。作為一個安全措施，當這個選項值大於零之時，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節，「多版本的實施”）。這個參數的 預設值是零，意為無延遲。這個選項可以在運行時作為全局系統變數而被改變。

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.1. 處理InnoDB初始化問題

如果InnoDB在一個檔案操作中打印一個作業系統錯誤，通常問題是如下中的一個：

當InnoDB試著初始化它的資料表空間或日誌檔案之時，如果出錯了，您應該刪除InnoDB建立的所有檔案。這意味著是所有ibdata檔案和所有ib_logfiles檔案。萬一您建立了一些InnoDB資料表，為這些資料表也從MySQL資料庫目錄刪除相應的.frm檔案(如果您使用多重資料表空間的話，也刪除任何.ibd檔案）。然後您可以試著再次建立InnoDB資料庫。最好是從命令提示符啟動MySQL伺服器 ，以便您可以查看發生了什麼。

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伺服器的客戶端開始之時是允許自動提交模式的，這個模式自動提交您運行的每個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也提供單獨的專門的事務提交和回滾函數或者方法。

要點：您不應該在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節，「強制恢復”。

如果您為一個資料表指定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 資料表選項來設置計數器初始值或變更當前計數器值。因在本節早先討論的原因，這個選項的影響在伺服器重啟後就無效了。

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}]

外部鍵定義服從下列情況：

InnoDB拒絕任何試著在子資料表建立一個外部鍵值而不匹配在父資料表中的候選鍵值的INSERT或UPDATE操作。一個父資料表有一些匹配的行 的子資料表，InnoDB對任何試圖更新或刪除該父資料表中候選鍵值的UPDATE或DELETE操作有所動作，這個動作取決於用FOREIGN KEY子句的ON UPDATE和ON DETETE子句指定的referential action。當用戶試圖從一個父資料表刪除或更新一行之時，且在子資料表中有一個或多個匹配的行，InnoDB根據要採取的動作有五個選擇：

當父資料表中的候選鍵被更新的時候，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。

MySQL複製就像對MyISAM資料表一樣，也對InnoDB資料表起作用。以某種方式使用複製也是可能的，在這種方式中從伺服器上資料表的類型不同於主伺服器上 原始資料表的類型。例如，您可以複製修改到主伺服器上一個InnoDB資料表，到從伺服器上一個MyISAM資料表裡。

要為一個主伺服器建立一個新伺服器，您不得不複製InnoDB資料表空間和日誌檔案，以及InnoDB資料表的.frm檔案，並且移動復件到從伺服器。 關於其恰當步驟請參閱15.2.9節，「移動InnoDB資料庫到另一台機器”。

如果您可以關閉主伺服器或者一個存在的從伺服器。您可以採取InnoDB資料表空間和日誌檔案的冷備份，並用它來建立一個從伺服器。要不關閉任何伺服器來建立一個新的從伺服器，您也可以使用非免費（商業的）InnoDB熱備份工具。

InnoDB複製裡一個小的限制是LOAD TABLE FROM MASTER不對InnoDB類型資料表起作用。有兩種可能的工作區：

在主伺服器失敗的事務根本不影響複製。MySQL複製基於二進制日誌，MySQ在那裡寫修改數據的SQL語句。從伺服器讀主伺服器的二進制日誌，並執行同樣的SQL語句。儘管如此，在事務裡發生的語句不被寫進二進制日誌直到事務提交，在那一刻，事務裡所有語句被一次性寫進日誌。如果語句失敗了，比如，因為外部鍵違例，或者，如果事務被回滾，沒有SQL語句被寫進二進制日誌，並且事務根本不在從伺服器上執行。

您可以儲存每個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安裝中：

2.      ALTER TABLE tbl_name DISCARD TABLESPACE;

5.      ALTER TABLE tbl_name IMPORT TABLESPACE;

在上下文中，一個.ibd檔案乾淨的備份意為：

您可以用下列方法生成一個.ibd檔案的乾淨備份：

生成一個.ibd檔案的乾淨複製的另一個方法是使用商業的InnoDB熱備份工具：

1.    使用mysqldump轉儲所有InnoDB資料表。

2.    停止伺服器。

3.    刪除所有已存在的資料表空間檔案。

4.    配置新資料表空間。

5.    重啟伺服器。

6.    導入轉儲檔案。

15.2.8.1. 強制恢復

15.2.8.2. 檢查點

1.    關閉MySQL伺服器，確信它是無錯誤關閉。

2.  複製您所有數據檔案（ibdata檔案和.ibd檔案）到一個安全的地方。 

3.   複製您所有ib_logfile檔案到一個安全的地方。

4.    複製my.cnf配置檔案或檔案到一個安全的地方。

5.    為您InnoDB資料表複製.frm檔案到一個安全的地方。

如果資料庫頁被破壞，您可能想要用SELECT INTO OUTFILE從從資料庫轉儲您的資料表，通常以這種方法獲取的大多數數據是完好的。即使這樣，損壞可能導致SELECT * FROM tbl_name或者InnoDB後台操作崩潰或斷言，或者甚至使得InnoDB前滾恢復崩潰。 儘管如此，您可以用它來強制InnoDB儲存引擎啟動同時阻止後台操作運行，以便您能轉儲您的資料表。例如：您可以在重啟伺服器之前，在選項檔案的[mysqld]節新增如下的行：

[mysqld]

innodb_force_recovery = 4

innodb_force_recovery被允許的非零值如下。一個更大的數字包含所有更小數字的預防措施。如果您能夠用一個多數是4的選項值來轉儲您的資料表，那麼您是比較安全的，只有一些在損壞的單獨頁面上的數據會丟失。一個為6的值更誇張，因為資料庫頁被留在一個陳舊的狀態，這個狀態反過來可以引發對B樹和其它資料庫結構的更多破壞。

資料庫不能另外地帶著這些選項中被允許的選項來使用。作為一個安全措施，當innodb_force_recovery被設置 為大於0的值時，InnoDB阻止用戶執行INSERT, UPDATE或DELETE操作.

即使強制恢復被使用，您也可以DROP或CREATE資料表。如果您知道一個給定的資料表正在導致回滾崩潰，您可以移除它。您也可以用這個來停止由失敗的大宗導入或失敗的ALTER TABLE導致的失控 回滾。您可以殺掉mysqld程序，然後設置innodb_force_recovery為3，使得資料庫被 掛起而不需要回滾，然後捨棄導致失控回滾的資料表。

InnoDB實現一種被認識為“模糊”檢查點設置的檢查點機制。InnoDB以小批量從緩衝池 刷新已修改的資料庫頁。沒必要以單個批次刷新緩衝池，單批次刷新實際操作中可能會在檢查點設置程序中停止用戶SQL語句的處理。

在崩潰恢復中，InnoDB找尋被寫進日誌的檢查點標籤。它知道所有在該標籤之前對資料庫的修改被呈現在資料庫的磁盤映像中。然後InnoDB從檢查點往前掃瞄日誌檔案，對資料庫應用已寫入日誌的修改。

InnoDB以循環方式寫日誌檔案。所有使得緩衝池裡的資料庫頁與磁盤上的映像不同的已提交修改必須出現在日誌檔案中 ，以備萬一InnoDB需要做一個恢復。這意味著，當InnoDB開始重新使用一個日誌檔案，它需要確認在磁盤上的資料庫頁映像包含已寫進InnoDB準備重新使用的日誌檔案裡的修改。換句話 說，InnoDB必須建立一個檢查點，這經常涉及已修改 資料庫頁到磁盤的刷新。

前面的敘述解釋了為什麼使您的日誌檔案非常大會在設置檢查點中節約磁盤I/O。設置日誌檔案總的大小和緩衝池一樣大或者甚至比緩衝池大通常是有意義的。大日誌檔案的缺點是崩潰恢復要花更長的時間，因為有更多寫入日誌的訊息要應用到資料庫上。

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 SHARE MODE

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實現標準行級鎖定，在這裡有兩種類型的鎖： locks:

如果事務A 在tuple t上持有獨佔鎖定，來自不同事務B的對t上任一類型的鎖的請求不被馬上授權，取而代之地，事務B 不得不等待事務t釋放在tuple t上的鎖。

如果事務 A 在tuple t上持有一個共享的鎖(S)，那麼

不僅如此，InnoDB支援多間隔尺寸鎖定，它允許記錄鎖和對整個資料表的鎖共存。要使得多間隔尺寸級別的鎖定實際化，額外類型的鎖，被稱為intention locks被使用。在InnoDB中，意圖鎖定是資料表鎖定。 對於一個事務，意圖鎖定之後理想的是指明在該資料表中對一個行隨後需要哪一類型的鎖定（共享還是獨佔）。有兩種意圖鎖被用在InnoDB中（假設事務T 在資料表R中要求一個已指出的類型的鎖）：

意圖鎖協議如下：

這些結果可以方便地用一個鎖類型兼容矩陣來總結：

如果一個鎖定與現在鎖定兼容的話，它被授給一個委託事務。如果一個鎖定與現存鎖定衝突，它就不被授予一個委託事務。事務等待著直到衝突的現存鎖定被釋放掉。如果一個鎖定請求與現存鎖定相衝突，且不能被授予，因為它可能會導致死鎖，一個錯誤產生。

因此，意圖鎖定不阻礙任何東西，除了完全資料表請求（比如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從資料表中刪除行。

在InnoDB中，所有用戶行為都在事務內發生。如果自動提交模式被允許，每個SQL語句在它自己上形成一個單獨的事務。MySQL總是帶著允許自動提交來開始一個新連接。

如果自動提交模式被用SET AUTOCOMMIT = 0關閉，那麼我們可以認為一個用戶總是有一個事務打開著。一個SQL COMMIT或ROLLBACK語句結束當前事務並且一個新事務開始。兩個語句都釋放所有在當前事務中被設置的InnoDB鎖定。一個COMMIT語句意味著在當前事務中做的改變被生成為永久的，並且變成其它用戶可見的。一個ROLLBACK語句，在另一方面，撤銷所有當前事務做的修改。

如果連接有被允許的自動提交，通過用明確的START TRANSACTION或BEGIN語句來開始一個事務，並用COMMIT或者ROLLBACK語句來結束它，這樣用戶仍舊可以執行一個多重語句事務。

按照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中每個隔離級別的詳細描述如下：

持續讀意味著InnoDB使用它的多版本化來給一個查詢展示某個時間點處資料庫的快照。查詢看到在那個時間點之前被提交的那些確切事務做的更改，並且沒有其後的事務或未提交事務做的改變。這個規則的例外是，查詢看到發佈該查詢的事務本身所做的改變。

如果您運行在預設的REPEATABLE READ隔離級別，則在同一事務內的所有持續讀讀取由該事務中第一個這樣的讀所確立的快照。您可以通過提交當前事務並在發佈新查詢的事務之後，為您的查詢獲得一個更新鮮的快照。

持續讀是預設模式，在其中InnoDBzai在READ COMMITTED和REPEATABLE READ隔離級別處理SELECT語句。持續讀不在任何它訪問的資料表上設置鎖定，因此，其它用戶可自由地在持續讀在一個資料表上執行的同一時間修改這些資料表。

注意，持續讀不在DROP TABLE和ALTER TABLE上作用。持續讀不在DROP TABLE上作用，因為MySQL不能使用已經被移除的資料表，並且InnoDB 破壞了該資料表。持續讀不在ALTER TABLE上作用，因為它在某事務內執行，該事務建立一個新資料表，並且從舊資料表往新資料表中插入行。現在，當您重新發出持續讀之時，它不能在新資料表中看見任何行，因為它們被插入到一個在持續讀讀取的快照中不可見的事務 裡。

在一些環境中，一個持續讀是不方便的。比如，您可能想要往資料表的子資料表裡新增一個新行，並確信該子資料表在父資料表中有一個根。下列例子顯示如何在您應用程式代碼中實現參考的完整性。

假設您使用一個持續讀去讀取父資料表並且看到資料表中子資料表的根。不能安全地往子資料表新增子行嗎？不，因為可能同時發生一些其它用戶從父資料表刪除父行，而您沒有注意到它的情況。

解決辦法是在使用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語句僅僅取回識別符訊息（專門對當前連接）。它不訪問任何資料表。

在行級鎖定中，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鎖定允許您鎖住在您的資料表中並不存在的一些東西。

假設您正運行在預設的REPEATABLE READ隔離級別。當您發出一個持續讀之時，即一個普通的SELECT語句，InnoDB按照您的查詢看到的資料庫，給您的事務一個時間點。如果另一個事務在您的時間點被指定之後刪除一行並提交，您不會看見已被刪除的行。插入和更新被相似地處理。

您可以通過提交您的事務來提前您的時間點，然後做另一個SELECT。

這被稱為多版本並發控制（multi-versioned concurrency control）。

               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;

在SQL查詢處理中，一個鎖定讀，一個UPDATE或一個DELETE一般地對被掃瞄的每個索引記錄設置記錄鎖定。如果在某查詢中有一個WHERE條件是沒什麼關係的，而這個查詢可能從查詢的結果包中排除行。InnoDB不記得確切的WHERE條件，但是僅知道哪個索引範圍被掃瞄。記錄鎖定是正常的next-key鎖定，它也阻止對緊接著記錄之前的間隙的插入。

如果鎖定被設置為獨佔，則InnoDB總是取回集束的索引目錄並對其設置鎖定。

如果您沒有適合查詢的索引，MySQL不得不掃瞄整個資料表來處理查詢，資料表的每行變成被鎖定的，這樣反過來阻止其它用戶的所有對資料表的插入。建立一個好的索引讓您的查詢不必要掃瞄很多行是很重要的。

MySQL以預設允許autocommit模式來開始每一個客戶端連接。當autocommit被允許之時，如果SQL語句不返回錯誤的話，MySQL在每個SQL語句之後，做一個提交。

如果您關閉autocommit模式並且關閉一個連接而不使用您的事務的明確提交，則MySQL回滾您的事務。

如果SQL語句返回一個錯誤，提交/回滾行為取決於這個錯誤。請參閱15.2.15節，「InnoDB錯誤處理”。

下列每一個語句（以及它們的任何同義詞）隱式結束一個事務，就好像在執行這個語句之前您做了一個COMMIT：

事務不能被嵌套。當您發出START TRANSACTION語句或與之同義的語句之時，這是對任何當前事務 隱式提交的一個結果。

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語句設定的。

死鎖是事務型資料庫典型的問題，但是除非它們頻繁出現以至於您更本不能運行某個事務，它們一般是不危險的。正常地，您必須編寫您的應用程式使得它們總是準備如果因為死鎖而 回滾一個事務就重新發出一個事務。

InnoDB使用自動行級鎖定。即使在只插入或刪除單個行的事務的情況下，您可以遇到死鎖。這是因為這些操作不是真正的“極小的”，它們自動對插入或刪除的行的（可能是數個）索引記錄設置鎖定。

您可以用下列技術對付死鎖減少它們發生的可能性：

·                SET AUTOCOMMIT=0;

·                LOCK TABLES t1 WRITE, t2 READ, ...;

·                [do something with tables t1 and t2 here];

·                COMMIT;

·                UNLOCK TABLES;

15.2.11.1. SHOW INNODB STATUS和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節，「強制恢復”。

·         也要小心其它大的磁盤綁定操作。用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

InnoDB包含InnoDB Monitors，它打印有關InnoDB內部狀態的訊息。您可以使用SQL語句SHOW INNODB STATUS來取標準InnoDB Monitor的輸出送到您的SQL客戶端。這個訊息在性能調節時有用。（如果您正使用mysql事務SQL客戶端，如果您用\G替代通常的語句終止符分號，輸出結果會更可讀 ）。關於InnoDB鎖定模式的討論，請參閱15.2.10.1節，「InnoDB鎖定模式”。

mysql> SHOW INNODB STATUS\G

另一個使用InnoDB Monitors的方法時讓它們不斷寫數據到mysqld伺服器的標準輸出。在這種情況下，沒有輸出被送到客戶端。當被打開的時候，InnoDB Monitors每15秒打印一次數據。伺服器輸出通常被定向到MySQL數據目錄裡的.err日誌。這個數據在性能調節時有用。在Windows上，如果您想定向輸出到窗口 而不是到日誌檔案，您必須從控制台窗口的命令行使用--console選項來啟動伺服器。

監視器輸出包括下列類型的訊息：

要讓標準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)

關於這個輸出一些要注意的：

InnoDB發送診斷輸出到stderr或檔案，而不是到stdout或者固定尺寸內存緩衝，以避免 底層緩衝溢出。作為一個副效果，SHOW INNODB STATUS的輸出每15秒鐘寫到一個狀態檔案。這個檔案的名字是innodb_status.pid，其中pid 是伺服器程序ID。這個檔案在MySQL數據目錄裡建立。正常關機之時，InnoDB刪除這個檔案。如果發生不正常的關機， 這些狀態檔案的實例可能被展示，而且必須被手動刪除。在移除它們之前，您可能想要檢查它們來看它們是否包含有關不正常關機的原因的有用訊息。僅在配置選項innodb_status_file=1被設置之時，innodb_status.pid檔案被建立。

15.2.13.1. 索引的物理結構

15.2.13.2. 緩衝插入

15.2.13.3. 適應的哈希索引

15.2.13.4. 物理記錄結構

所有InnoDB的索引是B數，其中索引記錄被儲存在樹的樹葉頁。一個索引頁的預設大小是16KB。當新記錄被插入，InnoDB試著為將來索引記錄的插入和更新留下十六分之一的空白頁。

如果索引記錄以連續的順序被插入（升序或者降序），結果索引頁大約是15/16滿。如果記錄被以隨機的順序被插入，頁面是從1/2到 15/16滿。如果索引頁的填充因子降到低於1/2，InnoDB試著搜索索引樹來釋放頁。

在資料庫應用中，主鍵是一個唯一的識別符，並且新行被以主鍵的升序來插入，這是個常見的情況。因此，到集束索引的插入不需要從一個磁盤隨機讀。

另一方面，第二索引通常是非唯一的，到第二索引的插入以相對隨機次序發生。這可能會導致大量的隨機磁盤I/O操作，而沒有一個被用在InnoDB中的專用機制。

如果一個索引記錄應該被插入到一個非唯一第二索引，InnoDB檢查第二索引頁是否在緩衝池中。如果是，InnoDB直接插入到索引頁。如果索引頁沒有在緩衝池中被發現，InnoDB插入記錄到一個專門的插入緩衝結構。插入緩衝被保持得如此小以至於它完全適合在緩衝池，並且可以非常快地做插入。

插入緩衝週期地被合併到資料庫中第二索引樹裡。把數個插入合併到索引樹的同一頁，節省磁盤I/O操作，經常地這是有可能的。據測量，插入緩衝可以提高到資料表的插入速度達15倍。

在插入事務被提交之後，插入緩衝合併可能連續發生。實際上，伺服器關閉和重啟之後，這會連續發生。（請參閱15.2.8.1節，「強制恢復”）。

當許多第二索引必須被更新之時，並且許多行已被插入之時，插入緩衝合併可能需要數個小時。在這個時間內，磁盤I/O將會增加，這樣會導致磁盤綁定查詢明顯緩慢。另一個明顯的後台I/O操作是淨化線程（請參閱15.2.12節，「實現多版本化”）。

如果一個資料表幾乎完全配合主內存，在其上執行查詢最快的方法就是使用哈希索引。InnoDB有一個自動機制，它監視對為一個資料表定義的索引的索引搜索。如果InnoDB注意到查詢會從建立一個哈希索引中獲益，它會自動地這麼做。

注意，哈希索引總是基於資料表上已存在的B樹索引來建立。根據InnoDB對B樹索引觀察的搜索方式，InnoDB會在為該B樹定義的任何長度的 鍵的一個前綴上建立哈希索引。 哈希索引可以是部分的：它不要求整個B樹索引被緩存在緩衝池。InnoDB根據需要對被經常訪問的索引的那些頁面建立哈希索引。

在某種意義上，InnoDB通過針對豐富主內存的適應的哈希索引機制來剪裁自己，更加靠近主內存資料庫的架構。

InnoDB資料表中的記錄有如下特徵：

15.2.14.1. 磁盤I/O

15.2.14.2. 為資料表空間使用原始設備

15.2.14.3.檔案空間管理

15.2.14.4. 整理資料表碎片

InnoDB使用模擬異步磁盤I/O：InnoDB建立許多線程來處理I/O操作，比如read-ahead.

在InnoDB中有兩個read-ahead試探：

InnoDB使用一個被稱為doublewrite的新穎的檔案 刷新技術。它給操作體統崩潰或掉電後的恢復新增了安全，並且通過減少對fsync()操作的需要，它在多數Unix變種上改善了性能。.

Doublewrite意為在向一個數據檔案寫頁之前，InnoDB首先把它們寫到一個毗鄰的資料表空間區域，稱為doublewrite緩衝。僅在寫然後 刷新到doublewrite已經完成之前，InnoDB寫頁面到它們在資料表空間中恰當的位置。如果作業系統在寫頁面的中間崩潰，在恢復過程中，InnoDB可以在隨後從doublewrite緩衝中找到頁面的一個良好複製。

您也可以使用原始磁盤分區作為資料表空間數據檔案。通過使用原始磁盤，您可以在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伺服器的帳號讀和寫訪問的授權。

您在配置檔案中定義的數據檔案形成InnoDB的資料表空間。檔案被簡單地連起來以形成資料表空間。沒有條紋在使用。當前您不能定義您的資料表被分配到資料表空間中的位置。但是，在一個新建立的資料表中間中，InnoDB 從第一個數據檔案開始分配空間。

資料表空間包含資料庫頁，預設大小是16KB。這些頁被分組成64個連續頁的範圍。資料表空間內的檔案在InnoDB中被稱為片斷。術語“rollback segment”有一些混淆，因為它確切地包含許多資料表空間片斷。

在InnoDB中，每個索引分配兩個片斷。一個是給B樹的非樹葉節點的，另一個是給樹葉節點的。在這裡，理想的是為包含數據的樹葉節點達到更好的有序性。

當一個片斷在資料表空間內長大，InnoDB單獨地分配最先的32頁給它。此後InnoDB開始分配整個範圍給該片斷。InnoDB可以一次給一個大片斷新增多達4個範圍以確保數據良好的連續性。

在資料表空間中的一些頁包含其它頁的位圖，因此在一個InnoDB資料表空間中的一些範圍不能被整個地分配給片斷，只能作為單個頁被分配。

當您發出SHOW TABLE STATUS詢問資料表空間裡可用的自由空間之時，InnoDB報告在資料表空間中完全自由的範圍。InnoDB總是為掃除和其它內部目的保留一些範圍，這些保留的範圍不包括在自由空間裡。

當您從一個資料表中刪除數據之時，InnoDB聯繫相應的B樹索引。是否釋放單獨頁或是範圍到資料表空間取決刪除的方式，因此被釋放的空間變成對其它用戶可用，但是記住，已刪除的行僅在該行不再被事務 回滾或持續讀所需要之後的一個（自動）淨化操作中被物理刪除。

如果有隨機插入到資料表的索引或從資料表的索引隨機刪除，索引可能變成碎片的。碎片意思是索引頁在磁盤上的物理排序並不接近頁上記錄的索引排序，或者在分配給索引的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.1. InnoDB錯誤代碼

15.2.15.2. 作業系統錯誤代碼

·         如果您耗盡資料表空間中的檔案空間，您使得MySQL資料表完全錯誤，並且InnoDB返回SQL語句。

·         一個事務死鎖導致InnoDB回滾整個事務，在鎖定等待超時的情況，InnoDB僅回滾最近的SQL語句。

當一個事務回滾因為死鎖或鎖定等待超時而發生，它在事務中撤銷語句的作用。但是如果事務是用START TRANSACTION或BEGIN語句開始的，它就不撤銷該語句。進一步，SQL語句變成事務的一部分直到COMMIT, ROLLBACK或者導致暗地提交的SQL語句發生。

·         如果您沒有在語句中指定IGNORE選項，重複鍵錯誤回滾SQL語句。

·         行太長錯誤回滾SQL語句。

·         其它錯誤主要被代碼的MySQL層（在InnoDB儲存引擎級別以上）探測，它們回滾相應的SQL語句。在單個SQL語句中的回滾中鎖定不被釋放。

下面的這個不完全列資料表是您可能遇見的常見的InnoDB專有錯誤，帶著為什麼發生的原因以及如何該解決問題的相關訊息

要打印一個作業系統錯誤號的意思，請使用MySQL分發版裡的perror程式。

下面資料表提供一些常用Linux系統錯誤代碼。更完整的列資料表請參閱Linux source code。

下列資料表提供一列常用Windows系統錯誤代碼。完整列資料表請訪問Microsoft website。

·         一個資料表不能包含超過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節，「伺服器系統變數”。請參閱A.6節，「最佳化器相關的問題”。

·         在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性能調節提示”。

·         對於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.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。

資料表的一個特殊問題是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外殼裡複製或重命名一個檔案，如果檔案名包含"#"字元，您需要把檔案名放在雙引號裡。

·         如果您使用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資料表。

-        如果一個內部資料表變得太大，伺服器自動把它轉換為一個磁盤資料表。尺寸限制由tmp_table_size系統變數的值來確定。

-        MEMORY資料表決不會轉換成磁盤資料表。要確保您不會偶爾做點傻事，您可以設置max_heap_table_size系統變數給MEMORY資料表加以最大尺寸。對於單個的資料表，您也可以在CREATE TABLE語句中指定一個MAX_ROWS資料表選項。

·         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

·         Linux 2.x Alpha

·         Linux 2.x AMD64

·         Linux 2.x IA-64

·         Linux 2.x s390

·         Mac OS X

·         --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來禁止它。

·         如果您正運行著，同時隨著autocommit的被允許（這是預設的），對BDB資料表的改變被立即提交並且不能被 回滾。

·         如果您正運行著，同時隨著autocommit的被禁止，改變不變成永久的直到您執行一個COMMIT語句。作為提交的替代，您可以執行ROLLBACK來忘記改變。

您可以用BEGIN WORK語句開始一個事務來掛起autocommit，或者用SET AUTOCOMMIT=0來明確禁止autocommit。

·         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樹而不是在分離的數據檔案中。