innodb

innodb

InnoDB,是MySQL的資料庫引擎之一,為MySQL AB發布binary的標準之一。InnoDB由Innobase Oy公司所開發,2006年五月時由甲骨文公司併購。與傳統的ISAM與MyISAM相比,InnoDB的最大特色就是支持了ACID兼容的事務(Transaction)功能,類似於PostgreSQL

目前InnoDB採用雙軌制授權,一是GPL授權,另一是專有軟體授權。

簡介


事務型資料庫的首選引擎,支持ACID事務,支持行級鎖定。InnoDB是為處理巨大數據量時的最大性能設計。InnoDB存儲引擎完全與MySQL伺服器整合,InnoDB存儲引擎為在主內存中緩存數據和索引而維持它自己的緩衝池。InnoDB存儲它的表&索引在一個表空間中,表空間可以包含數個文件(或原始磁碟分區)。這與MyISAM表不同,比如在MyISAM表中每個表被存在分離的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制為2GB的操作系統上。InnoDB默認地被包含在MySQL二進位分發中。Windows Essentials installer使InnoDB成為Windows上MySQL的默認表。
InnoDB給MySQL提供了具有事務(transaction)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)、多版本併發控制(multi-versioned concurrency control)的事務安全(transaction-safe(ACID compliant))型表。InnoDB提供了行級鎖(locking on row level),提供與Oracle類似的不加鎖讀取(non-locking read in SELECTs)。InnoDB鎖定在行級並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多用戶部署和性能。沒有在InnoDB中擴大鎖定的需要,因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支持FOREIGN KEY強制。在SQL查詢中,你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來,甚至在同一個查詢中也可以混合。這些特性均提高了多用戶併發操作的性能表現。在InnoDB表中不需要擴大鎖定(lock escalation),因為InnoDB的行級鎖定(row level locks)適宜非常小的空間。InnoDB是MySQL上第一個提供外鍵約束(FOREIGN KEY constraints)的表引擎。
MySQL
MySQL
在技術上,InnoDB是一套放在MySQL後台的完整資料庫系統,InnoDB在主內存中建立其專用的緩衝池用於高速緩衝數據和索引。InnoDB把數據和索引存放在表空間里,可能包含多個文件,這與其它的不一樣,舉例來說,在MyISAM中,表被存放在單獨的文件中。InnoDB表的大小隻受限於操作系統的文件大小,可也可以每個表使用各自獨立的表空間,只需要啟用選項innodb_file_per_table。
在MySQL的源代碼中,從3.23.34a開始包含InnoDB表引擎,並在MySQL-Max的二進位版本中激活。

性能技巧


1.如果Unixtop或Windows任務管理器(Task Manager)顯示服務的CPU佔用率小於70%,(shows that the CPU usage percentage with your workload is less than70%,)你的系統瓶頸可能在磁碟讀寫上。或許你提交了大量的事務,或者是緩衝池(buffer pool)太小了。將緩衝池設大點會有所幫助,但一定要注意不能大於物理內存的80%。
2.在一個事務中包含幾個修改。如果事務對資料庫進行了修改,那麼在這個事務提交時InnoDB必須刷新日誌到磁碟上。因為硬碟的旋轉速度通常至多為167轉/秒,那麼只要磁碟不欺騙操作系統,提交的事務數目限止也同樣為167次/秒·用戶。
3.如果掉失最近的幾個事務無所謂的話,可以在my.cnf文件中將參數innodb_flush_log_at_trx_commit設置為0。InnoDB無論如何總是嘗試一秒刷新(flush)一次日誌,儘管刷新並不能得到保證。
4.將日誌文件(log files)設大一點,使日誌文件的總和正好與緩衝池(buffer pool)一樣大。當InnoDB用光日誌文件的空間時,它不得不在一個時間點上將緩衝池內修改過的內容寫到磁碟上。小的日誌文件可能引起不必要的磁碟寫操作。但是大的日誌文件的缺點就是在數據恢復時將佔用較長的時間。
5.同樣log buffer盡量設大點,比如說8MB。
6.如果要存儲變長的字元串或欄位可能會包含大量的NULLs,請使用VARCHAR型欄位代替CHAR。一個CHAR(n)欄位總是使用n bytes來存儲數據,即使這個字元串很短或是一個NULL值。較小的表更加適合緩衝池同時能夠減少磁碟I/O。
7.(適合從3.23.41以上版本)在某些版本的Linux和Unixes中,使用Unixfsync或其它類似的方法將文件刷新到磁碟是異常地慢的。InnoDB默認的方法就是fsync。如果你對資料庫系統的磁碟寫性能不能感到滿意,你可以嘗試在my.cnf中將innodb_flush_method設置為O_DSYNC,儘管O_DSYNC選項在多數的系統上看起來比較慢。
8.在向InnoDB導入數據時,請確認MySQL沒有打開autocommit=1。否則每個插入語句都要將log刷新到磁碟。在你的SQL導入文件的第一行加入
set autocommit=0;並在最後一行加入commit;
如果使用mysqldump選項--opt,你將會得到一個快速導入InnoDB表的轉儲(dump)文件,甚至可以不再使用上面所提的set autocommit=0;...commit;。
9.小心insert集全的大回滾(roolback):在插入時InnoDB使用插入緩衝來減少磁碟I/O,但在相應的回滾中卻沒有使用這樣的機制。一個disk-bound rollback可能會花費相應插入時間的30倍。如果發生一個失控的回滾,你可以查看第6.1章節的技巧來停止它。
10.同樣也要小心一個大的disk-bound的操作。使用DROP TABLE或TRUNCATE(從MySQL-4.0以上)來清空一個表,而不要使用DELETE FROM yourtable。
11.如果需要插入大量記錄行可以使用多行(multi-line)的INSERT來減少客戶端與伺服器端的通信開銷:
INSERT INTO yourtable VALUES(1,2),(5,5);
這個技巧對插入任何錶均有效,而不僅僅是InnoDB。
12.如果在輔鍵上有UNIQUE約束,從3.23.52和4.0.3開始,可以通過在一個導入會話中將唯一鍵檢查(uniqueness check)關閉來提高數據導入速度:
SET UNIQUE_CHECKS=0;一個大的表導入這將減少大量的磁碟I/O,因為這時InnoDB可能使用自身的插入緩衝來分批地記錄輔助索引。
13.如果在表中有一個子FOREIGN KEY約束,從3.23.52和4.0.3開始,可以通過在一個導入會話中將外鍵檢查(foreign key check)關閉來提高數據導入速度:
SET FOREIGN_KEY_CHECKS=0;
對一個大的表導入這將減少大量的磁碟I/O。

注意事項


輸出信息的某些注意點:
• 如果TRANSACTIONS部分報告鎖定等待(lock waits),那麼你的應用程序可能有鎖爭用(lock contention)。輸出信息可以幫助跟蹤事務死鎖的原因。
• SEMAPHORES部分報告線程等待信號量以及統計出線程需要旋轉(spin)或等待(wait)一個互斥(mutex)或rw-lock號量的次數。一個較大的線程等待信號量的次數可能是由於磁碟I/O引起,或InnoDB內部的爭用問題(contention problems)。爭用(Contention)可能是由於比較繁重的併發性查詢,或操作系統的線程調度的問題。在這種情形下,可將innodb_thread_concurrency設置地小於默認的8。
• FILEI/O部分列出了文件I/O的等待請求。過大的值就意味著磁碟I/O瓶頸。
• BUFFER POOL AND MEMORY部分給出了頁面讀寫的統計。通過這些值可以計算出你的查詢通常所需的數據文件I/O量。