【轉】InnoDB和MyISAM的技術資料

 

InnoDB和MyISAM是在使用MySQL最常用的兩個表型別，各有優缺點，視具體應用而定。下面是已知的兩者之間的差別，僅供參考。 

 

innodb

InnoDB 給 MySQL 提供了具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行鎖(locking on row level)，提供與 Oracle 型別一致的不加鎖讀取(non-locking read in SELECTs)。這些特性均提高了多使用者併發操作的效能表現，在InnoDB表中不需要擴大鎖定(lock escalation)，因為 InnoDB 的列鎖定(row level locks)適宜非常小的空間。InnoDB 是 MySQL 上第一個提供外來鍵約束(FOREIGN KEY constraints)的表引擎。

InnoDB 的設計目標是處理大容量資料庫系統，它的 CPU 利用率是其它基於磁碟的關聯式資料庫引擎所不能比的。在技術上，InnoDB 是一套放在 MySQL 後臺的完整資料庫系統，InnoDB 在主記憶體中建立其專用的緩衝池用於高速緩衝資料和索引。InnoDB 把資料和索引存放在表空間裡，可能包含多個檔案，這與其它的不一樣，舉例來說，在 MyISAM 中，表被存放在單獨的檔案中。InnoDB 表的大小隻受限於作業系統的檔案大小，一般為 2 GB。 

在 http://www.innodb.com/ 上可以找到 InnoDB 最新的資訊。InnoDB 手冊的最新版本總是被放置在那裡，並且在那裡可以得到 InnoDB 的商業許可(order commercial licenses)以及支援。 

InnoDB 現在(2001年十月)在一些大的需高效能的資料庫站點上被使用。著名的 Internet 新聞站點 Slashdot.org 就是使用的 InnoDB。 Mytrix, Inc. 在 InnoDB 表上儲存了超過 1 TB 的資料，而且另外的一個站點在 InnoDB 表上處理著平均每秒 800 次的插入/更新的負載。 

 

MyISAM

MyISAM 是MySQL預設存貯引擎。

每張MyISAM 桌被存放在盤在三個檔案：frm 檔案存放表格定義， 資料檔案是MYD (MYData) ，索引檔案是MYI (MYIndex) 引伸。 

 

以下是一些細節和具體實現的差別： 

1.InnoDB不支援FULLTEXT型別的索引。 

2.InnoDB 中不儲存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。 

3.對於AUTO_INCREMENT型別的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。 

4.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。 

5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用的額外的InnoDB特性(例如外來鍵)的表不適用。 

另外，InnoDB表的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like “%aaa%”任何一種表都不是萬能的，只用恰當的針對業務型別來選擇合適的表型別，才能最大的發揮MySQL的效能優勢。

 

MYSQL innoDB介紹

InnoDB 是 MySQL 上第一個提供外來鍵約束的引擎，除了提供事務處理外，InnoDB 還支援行鎖，提供和 Oracle 一樣的一致性的不加鎖讀取，能增加併發讀的使用者數量並提高效能，不會增加鎖的數量。

InnoDB 的設計目標是處理大容量資料時最大化效能，它的 CPU 利用率是其他所有基於磁碟的關聯式資料庫引擎中最有效率的。

InnoDB是一套放在 MySQL 後臺的完整資料庫系統，InnoDB 有它自己的緩衝池，能緩衝資料和索引。InnoDB還把資料和索引存放在表空間裡面，可能包含好幾個檔案，這和 MyISAM 表完全不同，在 MyISAM 中，表被存放在單獨的檔案中，InnoDB表的大小隻受限於作業系統檔案的大小，一般為 2GB。

MySQL 的原始碼中，從 3.23.34a 開始包含 InnoDB 表，並在 MySQL -Max 的二進位制版本中啟用。如果你使用原始碼版本下載，自己編譯的話，需要採用 --with-innodb 選項：

./configure --with-innodb

在 MySQL-Max-3.23 上使用 InnoDB 時，必須在 my.cnf 或者 my.ini 檔案中的 [mysqld] 小節指定啟動引數：

innodb_data_file_path=ibdata:30M

 

InnoDB 以 GNU GPL 版本 2 的許可釋出。

MySQL-Max-3.23: 這個產品可以用於生產環境使用。

MySQL-4.0: 這個是開發版本，和 3.23 相比增加了多表刪除，查詢結果緩衝，SSL 通訊，4.0.1 為 Beta 版本。在 3.23 中，必須至少指定 innodb_data_file_path 的大小，但是在 MySQL-4.0 中這個值預設為 64MB，檔名為 ibdata1。

為了得到好的效能，必須指定 InnoDB 引數，例如：

對於一臺 128MB 記憶體， 10GB 硬碟的 Windows NT 機器的配置如下：

[mysqld]

innodb_data_home_dir = c:/ibdata

# 資料檔案必須能容下資料和索引

innodb_data_file_path = ibdata1:2000M;ibdata2:2000M

# 設定緩衝池大小為記憶體的 50 - 80 %

set-variable = innodb_buffer_pool_size=70M

set-variable = innodb_additional_mem_pool_size=10M

innodb_log_group_home_dir = c:/iblogs

# log_arch_dir 必須和 log_group_home_dir 一樣

innodb_log_arch_dir = c:/iblogs

innodb_log_archive=0

set-variable = innodb_log_files_in_group=3

# 設定日誌檔案大小為緩衝池的 15%。

set-variable = innodb_log_file_size=10M

set-variable = innodb_log_buffer_size=8M

# 如果丟失最近的幾個事務影響不大的話，設定 flush_log_at_trx_commit 為 0。

innodb_flush_log_at_trx_commit=1

set-variable = innodb_file_io_threads=4

set-variable = innodb_lock_wait_timeout=50

注意 InnoDB 不會自己建立目錄，你必須使用作業系統命令建立目錄，並注意 MySQL 的許可權。第一次執行 MySQL 時，建議使用命令列方式。在 Windows 下，用命令列開啟：

C:/MYSQL>mysqld-max --standalone --console

在 Windows 下設定配置檔案的原則是：

my.cnf 和 my.ini 只能是一個有效。

my.cnf 放在 C: 根目錄下， my.ini 檔案放在 WINDIR 目錄下，C:/WINDOWS 或者 C:/WINNT。

在 Linux 下面，初始配置檔案按下面的順序搜尋：

/etc/my.cnf 全域性選項

COMPILATION_DATADIR/my.cnf 伺服器範圍的選項

defaults-extra-file 採用 --defaults-extra-file=.... 的檔案

~/.my.cnf 使用者指定的選項

假設你有一臺 512MB 記憶體，三臺 20GB 硬碟(路徑分別為："/"，"/dr2"，"/dr3“)的伺服器，設定的例子為：

[mysqld]

innodb_data_home_dir = /

innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M

set-variable = innodb_buffer_pool_size=350M

set-variable = innodb_additional_mem_pool_size=20M

innodb_log_group_home_dir = /dr3/iblogs

innodb_log_arch_dir = /dr3/iblogs

innodb_log_archive=0

set-variable = innodb_log_files_in_group=3

set-variable = innodb_log_file_size=50M

set-variable = innodb_log_buffer_size=8M

innodb_flush_log_at_trx_commit=1

set-variable = innodb_file_io_threads=4

set-variable = innodb_lock_wait_timeout=50

#innodb_flush_method=fdatasync

#innodb_fast_shutdown=1

#set-variable = innodb_thread_concurrency=5

為了提升效能，我們把日誌檔案和資料檔案放在不同的磁碟，另外還可以採用裸的磁碟分割槽來儲存資料以提升效能。需要提醒的是，在 Linux x86 上不要把記憶體設定太高，glibc 會把程式堆增長到執行緒堆疊之上，能把伺服器給搞垮，下面的值接近於 2GB 時就很危險：

innodb_buffer_pool_size + key_buffer +

max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB

每個執行緒將使用 2MB(二進位制版本為 256 KB) 的堆疊，在最壞的情況下，還會使用 sort_buffer + record_buffer 的附加記憶體。

後續的文章，我們還將介紹 InnoDB 表的建立，MyISAM 到 InnoDB 的轉換，外來鍵約束的使用，自動增長列的操作，InnoDB 資料庫的備份和恢復，事務的使用，效能調優等內容。

 

MySQL InnoDB的效能問題討論

MySQL最為人垢病的缺點就是缺乏事務的支援，MyISAM 效能雖然出眾，不是沒有代價的，InnoDB 又如何呢？InnoDB 的磁碟效能很令人擔心，MySQL 缺乏良好的 tablespace 真是天大的缺陷！ 

InnoDB的表空間分成三種，一種是裸裝置，一種是若干個 ibdata 檔案(預設方式)，再一種是 Per-Table 檔案，第一種用得少，第二種顯然比第三種效率更差，本文的討論基於 Per-Table，也即innodb_file_per_table 配置引數。 

現象重現：匯出一個幾百萬行資料、帶若干索引、有過頻繁更新的表出來再匯入，如果能以真實環境下的表來做測試就更理想，到 data 目錄下觀察對應的資料檔案的 size 增長情況，會發現前 1G 速度相當令人滿意，可是越往後效率越低，到後面基本就是蝸牛般的速度了。 

不是隻有匯入才會讓你慢得受不了，alter column/index 都會這樣。

InnoDB 跟磁碟相關的檔案儲存，可以分成兩個部分，一個是日誌檔案，另一個是資料檔案。當有頻繁的 INSERT/UPDATE 操作的時候，InnoDB 需要分別寫入這兩個檔案，日誌檔案是順序操作，資料檔案包括了表資料和索引資料兩個部分(和 MyISAM 直接拆開成表檔案和索引檔案不同，InnoDB 的表和索引是在同一個檔案當中的)。 

InnoDB 的索引用的是 BTREE 格式，如果當前更新的記錄影響到索引的變化，邏輯上就存在三個操作，從原來的 BTREE 找到並摘除原來這行的記錄並做調整、插入行資料、根據新資料查詢 BTREE 相應的位置並重新插入新索引資訊，假設索引數為 N，相應的邏輯運算元就為 1 + 2*N，顯然這些資訊不能保證在同一個磁碟連續空間上，因此需要 1 + 2*N 次的磁頭移動，行數越大、檔案尺寸越大，磁頭的移動幅度也就可能越大，帶來的後果顯然是極差的磁碟 IO 效率。 

MySQL 對於 MyISAM 的的磁碟 IO 優化是如何建議的呢？使用符號連結將表檔案和索引檔案分別指向不同的不同的目錄，分散到不同的磁碟上以增加系統的訪問速度。這種優化方式，在 InnoDB 上完全沒有可能性！ 

如果有 tablespace 支援，磁碟效率問題就好解決了，一如商業資料庫的做法，將日誌、表檔案、索引檔案分別分佈到不同的表空間也就是物理磁碟上，可是 MySQL 一直到 5.1 都沒有提供 tablespace 功能，僅在 NDB/NDBCLUSTER 中才提供，但是 - "CREATE TABLESPACE was added in MySQL 5.1.6. In MySQL 5.1, it is useful only with Disk Data storage for MySQL Cluster."。 不知道 Yahoo 等大網站是怎麼解決這個難題的。考慮切換到 PostgreSQL 中。

 

 

轉自：http://www.unixaid.info/index.php/unixtecspt/37-db/528-innodbmyisam