MySQL的InnoDB引擎入門學習教程_Mysql

MySQL發展到今天，InnoDB引擎已經作為絕對的主力，除了像大資料量分析等比較特殊領域需求外，它適用於眾多情境。然而，仍有不少開發人員還在“執迷不悟”的使用MyISAM引擎，覺得對InnoDB無法把握好，還是MyISAM簡單省事，還能支援快速COUNT(*)。本文是由於最近幾天幫忙處理discuz論壇有感而發，希望能對廣大開發人員有協助。

1. 快速認識InnoDB
InnoDB是MySQL下使用最廣泛的引擎，它是基於MySQL的高可擴充性和高效能儲存引擎，從5.5版本開始，它已經成為了預設引擎。
InnODB引擎支援眾多特性：

a) 支援ACID，簡單地說就是支援事務完整性、一致性；
b) 支援行鎖，以及類似ORACLE的一致性讀，多使用者並發；
c) 專屬的叢集索引主鍵設計方式，可大幅提升並發讀寫效能；
d) 支援外鍵；
e) 支援崩潰資料自修複；
InnoDB有這麼多特性，比MyISAM來的優秀多了，還猶豫什麼，果斷的切換到InnoDB引擎吧 :)

2. 修改InnoDB配置選項
可以選擇官方版本，或者Percona的分支，如果不知道在哪下載，就google吧。
安裝完MySQL後，需要適當修改下my.cnf設定檔，針對InnoDB相關的選項做一些調整，才能較好的運行InnoDB。
相關的選項有：

#InnoDB儲存資料字典、內部資料結構的緩衝池，16MB 已經足夠大了。innodb_additional_mem_pool_size = 16M#InnoDB用於快取資料、索引、鎖、插入緩衝、資料字典等#如果是專用的DB伺服器，且以InnoDB引擎為主的情境，通常可設定實體記憶體的50%#如果是非專用DB伺服器，可以先嘗試設定成記憶體的1/4，如果有問題再調整#預設值是8M，非常坑X，這也是導致很多人覺得InnoDB不如MyISAM好用的緣故innodb_buffer_pool_size = 4G#InnoDB共用資料表空間初始化大小，預設是 10MB，也非常坑X，改成 1GB，並且自動擴充innodb_data_file_path = ibdata1:1G:autoextend#如果不瞭解本選項，建議設定為1，能較好保護資料可靠性，對效能有一定影響，但可控innodb_flush_log_at_trx_commit = 1#InnoDB的log buffer，通常設定為 64MB 就足夠了innodb_log_buffer_size = 64M#InnoDB redo log大小，通常設定256MB 就足夠了innodb_log_file_size = 256M#InnoDB redo log檔案組，通常設定為 2 就足夠了innodb_log_files_in_group = 2#啟用InnoDB的獨立資料表空間模式，便於管理innodb_file_per_table = 1#啟用InnoDB的status file，便於管理員查看以及監控等innodb_status_file = 1#設定交易隔離等級為 READ-COMMITED，提高事務效率，通常都滿足事務一致性要求transaction_isolation = READ-COMMITTED 在這裡，其他配置選項也需要注意：#設定最大並發串連數，如果前端程式是PHP，可適當加大，但不可過大#如果前端程式採用串連池，可適當調小，避免串連數過大max_connections = 60#最大串連錯誤次數，可適當加大，防止頻繁串連錯誤後，前端host被mysql拒絕掉max_connect_errors = 100000#設定慢查詢閥值，建議設定最小的 1 秒long_query_time = 1#設定暫存資料表最大值，這是每次串連都會分配，不宜設定過大 max_heap_table_size 和 tmp_table_size 要設定一樣大max_heap_table_size = 96Mtmp_table_size = 96M#每個串連都會分配的一些排序、串連等緩衝，一般設定為 2MB 就足夠了sort_buffer_size = 2Mjoin_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 2M#建議關閉query cache，有些時候對效能反而是一種損害query_cache_size = 0#如果是以InnoDB引擎為主的DB，專用於MyISAM引擎的 key_buffer_size 可以設定較小，8MB 已足夠#如果是以MyISAM引擎為主，可設定較大，但不能超過4G#在這裡，強烈建議不使用MyISAM引擎，預設都是用InnoDB引擎key_buffer_size = 8M#設定連線逾時閥值，如果前端程式採用短串連，建議縮短這2個值#如果前端程式採用長串連，可直接注釋掉這兩個選項，是用預設配置(8小時)interactive_timeout = 120wait_timeout = 120

3. 開始使用InnoDB引擎
修改完設定檔，即可啟動MySQL。啟動完畢後，在MySQL的datadir目錄下，若產生以下幾個檔案，則表示應該可以使用InnoDB引擎了。

-rw-rw---- 1 mysql mysql 1.0G Sep 21 17:25 ibdata1-rw-rw---- 1 mysql mysql 256M Sep 21 17:25 ib_logfile0-rw-rw---- 1 mysql mysql 256M Sep 21 10:50 ib_logfile1

登入MySQL後，執行命令，確認已啟用InnoDB引擎：

(root:imysql.cn:Thu Oct 15 09:16:22 2009)[mysql]> show engines;+------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine   | Support | Comment                            | Transactions | XA  | Savepoints |+------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB   | YES   | Supports transactions, row-level locking, and foreign keys   | YES     | YES | YES    |

接下來建立一個InnoDB表：

(root:imysql.cn:Thu Oct 15 09:16:22 2009)[mysql]> CREATE TABLE my_innodb_talbe(id INT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(20) NOT NULL DEFAULT '',passwd VARCHAR(32) NOT NULL DEFAULT '',PRIMARY KEY(id),UNIQUE KEY `idx_name`(name)) ENGINE = InnoDB;

有幾個和MySQL(尤其是InnoDB引擎)資料表設計相關的建議，希望開發人員朋友能遵循：

a) 所有InnoDB資料表都建立一個和業務無關的自增數字型作為主鍵，對保證效能很有協助；
b) 杜絕使用text/blob，確實需要使用的，儘可能拆分出去成一個獨立的表；
c) 時間戳記建議使用 TIMESTAMP 類型儲存；
d) IPV4 地址建議用 INT UNSIGNED 類型儲存；
e) 性別等非是即非的邏輯，建議採用 TINYINT 儲存，而不是 CHAR(1)；
f) 儲存較長常值內容時，建議採用JSON/BSON格式儲存；

4.瞭解InnoDB的儲存結構
從物理意義上來講，InnoDB表由共用資料表空間、記錄檔組（redo檔案組）、表結構定義檔案組成。若將innodb_file_per_table設定為on，則系統將為每一個表單獨的產生一個table_name.ibd的檔案，在此檔案中，儲存與該表相關的資料、索引、表的內部資料字典資訊。表結構檔案則以.frm結尾，這與儲存引擎無關。

　　以下為InnoDB的資料表空間結構圖：

在InnoDB儲存引擎中，預設資料表空間檔案是ibdata1，初始化為10M，且可以擴充，如下圖所示：

實際上，InnoDB的資料表空間檔案是可以修改的，使用以下語句就可以修改：

Innodb_data_file_path=ibdata1:370M；ibdata2:50M:autoextend

　　使用共用資料表空間儲存方式時，Innodb的所有資料儲存在一個單獨的資料表空間裡面，而這個資料表空間可以由很多個檔案組成，一個表可以跨多個檔案存在，所以其大小限制不再是檔案大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其資料表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關資料。

　　而在使用單獨資料表空間儲存方式時，每個表的資料以一個單獨的檔案來存放，這個時候的單表限制，又變成檔案系統的大小限制了。

　　以下即為不同平台下，單獨資料表空間檔案最大限度。

Operating System  File-size Limit
Win32 w/ FAT/FAT32  2GB/4GB
Win32 w/ NTFS          2TB (possibly larger)
Linux 2.4+          (using ext3 file system) 4TB
Solaris 9/10          16TB
MacOS X w/ HFS+         2TB
NetWare w/NSS file system  8TB

以下是MySQL文檔中的內容：
Windows使用者請注意： FAT和VFAT (FAT32)不適合MySQL的生產使用。應使用NTFS。

共用資料表空間與獨佔資料表空間可以通過參數innodb_file_per_table來轉換，若為1，則開啟獨佔資料表空間，否則，開啟共用表格儲存體。

在伺服器資源有限,單表資料不是特別多的情況下, 獨立資料表空間明顯比共用方式效率更高 . 但是MySQL 預設是共用資料表空間 。

具體的共用資料表空間和獨立資料表空間優缺點如下：

共用資料表空間：
優點：
可以放資料表空間分成多個檔案存放到各個磁碟上（資料表空間檔案大小不受表大小的限制，如一個表可以分布在不同步的檔案上）。資料和檔案放在一起方便管理。
缺點：
所有的資料和索引存放到一個檔案中以為著將有一個很常大的檔案，雖然可以把一個大檔案分成多個小檔案，但是多個表及索引在資料表空間中混合儲存，這樣對於一個表做了大量刪除操作後資料表空間中將會有大量的空隙，特別是對於統計分析，日值系統這類應用最不適合用共用資料表空間。

獨立資料表空間：在設定檔（my.cnf）中設定： innodb_file_per_table

優點：
1．  每個表都有自已獨立的資料表空間。
2．  每個表的資料和索引都會存在自已的資料表空間中。
3．  可以實現單表在不同的資料庫中移動。
4．  空間可以回收（除drop table操作處，表空不能自已回收）
a)         Drop table操作自動回收資料表空間，如果對於統計分析或是日值表，刪除大量資料後可以通過:alter table TableName engine=innodb;回縮不用的空間。
b)         對於使innodb-plugin的Innodb使用truncate table也會使空間收縮。
c)         對於使用獨立資料表空間的表，不管怎麼刪除，資料表空間的片段不會太嚴重的影響效能，而且還有機會處理。
缺點：
單表增加過大，如超過100個G。

對於啟用了innodb_file_per_table 的參數選項之後，在每個表對應的.idb檔案內只是存放了資料、索引和插入緩衝，而撤銷（undo）資訊，系統事務資訊，二次寫緩衝等還是存放在了原來的共用資料表空間內。

資料區段即B+樹的分葉節點，索引段即為B+樹的非索引節點。

InnoDB儲存引擎的管理是由引擎本身完成的，資料表空間是由分散的頁和段組成。

區由64個連續的頁組成，每個頁大小為16K，即每個區大小為1MB，建立新表時，先有32頁大小的片段頁存放資料，使用完後才是區的申請，（InnoDB最多每次申請4個區，保證資料的順序效能）

頁類型有：資料頁、Undo頁、系統頁、交易資料頁、插入緩衝位元影像頁、以及插入緩衝空閑列表頁。