第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。當用戶試圖從一個父資料表刪除或更新一行之時，且在子資料表中有一個或�