2007-12-16, 02:19 PM
#1
管理員
榮譽勳章
SQL - 04. Storage Engines
Storage Engines
The MyISAM Engine www]slime]com]tw
每個資料表都會對應到檔案系統上的三個檔案:.frm, .MYI, .MYD 。.frm 儲存資料表的資料結構,.MYI 儲存資料表的索引,.MYD 儲存資料表的內容。
擁有最高彈性的AUTO_INCREMENT column
可以用來設定MERGE table
支援compress row format
支援FULLTEXT index
使用table level lock 因此不會有deadlock( 但有例外)
可使用modifier 改變query 的優先權值
具有binary portable 特性,但必需滿足以下兩個要件:兩台機器必需皆採用二的補數整數運算,以及兩者必需皆採用IEEE 浮點數格式。( 現代的電腦系統幾乎都符合上述兩個要件) www]slime_com+tw
當載入資料到table 時,可以先關閉non-unique index 的更新再載入資料,等資料載入完成後再開啟index 更新,如此具有較高的處理效率。
當磁碟空間不足時系統不會出現任何錯誤訊息而是會把資料存在memory 中,等到有可用的磁碟空間時系統會再把資料寫入磁碟中。
MyISAM 是內建且為系統預設使用的Storage Engine ,因此它是不可以被disable 的。
MyISAM 資料表鎖定的特徵(Locking Characteristics) www]slime]com]tw 在一般的情況下,Server 執行Query 的順序如下:Write Query 具有比Read Query 更高的優先權。
若是系統收到多個Write Queries ,則以收到的先後順序執行之
在MyISAM 資料表中,我們可以使用下列modifier 來改變Query 的優先權值:LOW_PRIORITYwww]slime_com+tw
我們可以使用LOW_PRIORITY來降低Write Query的優先權。當系統收到Write Query並且正在等待寫入資料至table時,若是此時系統收到新的Read Query則Read Query會搶先在Write Query之前執行。由此可知在非常忙錄的Server中,LOW_PRIORITY Write有可能永遠不會被執行。
HIGH_PRIORITY
我們可以使用HIGH_PRIORITY來提高Read Query的優先權。當系統收到Write Query並且正在等待寫入資料至table時,若是此時系統收到HIGH_PRIORITY的Read Query則該Read Query會搶先在Write Query之前執行。
DELAYED
只可以使用在INSERT(或REPLACE),會將寫入的資料暫存在memory中而不是立刻寫入資料表。
MyISAM Row-Storage Formats www]slime]com]tw Fix-length format 所有的row 都具有相同的大小
row 實際儲存的位置是以row size 的倍數算出,具有較高的查詢效率
佔用較多的磁碟空間
註:在Fix-length format 資料表中不可以包含BLOB 或TEXT column 。
Dynamic-length format 每個Row 的大小不儘相同
無法像Fix-length format 具有那麼高的查詢效率
通常佔用較少的磁碟空間
較容易出現fragmentation
Compressed-format www]slime_com+tw 資料經過壓縮以減少所佔用的磁碟空間
儲存格式經過最佳化,因此具有很高的查詢效率
資料表是唯讀的
The MERGE Engine www]slime]com]tw 必須要是具有完全相同結構的MyISAM table 才可以進行MERGE
每個資料表都會對應到檔案系統上的二個檔案:.frm, .MRG 。.MRG 檔案中記載著所有被MERGE 的MyISAM table 名稱。
MERGE table 在邏輯上將多個MyISAM table 合併在一起。可以避免檔案系統的最大檔案大小限制,因為你可以將資料分散到多個MyISAM table 中,然後再將它們合併起來即可。www]slime_com+tw
使用table level lock
具有binary portable 特性。因為.MRG 本身只是一般的文字檔案,而被MERGE 的MyISAM table 本身又是binary portable 。
支援SELECT, DELETE, INSERT, UPDATE statement ,還可以在CREATE TABLE 中指定INSERT 進來的row 要插入到第一個table 、最後一個table 或是禁止插入。
MERGE 資料表鎖定的特徵www]slime]com]tw 當要對MERGE table 進行lock 的時候,系統會lock 其所有的成員(MyISAM table)
The InnoDB Enginewww]slime]com]tw 每個資料表都會對應到檔案系統上的 .frm ,而data 與index 則是儲存在shared tablespace 中。(tablespace 裡面還放有data dictionary 與rollback segment)
支援transaction ,並提供完整的ACID(atomicity, consistency, isolation, durability) 相容性。
採用multi-versioning 與row level lock www]slime_com+tw
支援foreign key integrity( 包含cascade update, delete)
tablespace 具有binary portable 的特性( 但database 與table 的名稱必須為小寫)
Innodb 與ACID www]slime]com]tw Atomic :transaction 中的所有statement 皆視為單一的unit ,必須全部執行成功,若是有任一statement 執行失敗則必需rollback 。
Consistent :保持在consistent state 的資料庫在執行transaction 後依然保持在consistent state 。
Isolation :transaction 之間無法相互影響。
Durabe :transaction 所有對資料庫的修改皆完整的記錄在資料庫之中,不會喪失。
InnoDB 資料表鎖定的特徵www]slime]com]tw 使用multi-version ,在transaction 尚未commit 前只有該transaction 自己才能看到在transaction 中對資料庫所做的修改,其他的transaction 無法看到。
採用row level lock ,有可能會發生deadlock 。
支援locking modifier ,可將non-locking read 轉變成locking read :
LOCK IN SHARE MODE :在transaction 中被SELECT 的row 會被read lock 住,其他client 可以讀取但無法寫入。可以用多個不同的client 同時對同一筆row 進行SELECT......LOCK IN SHARE MODE ,也因此較容易造成deadlock 。www]slime_com+tw
FOR UPDATE :在transaction 中被SELECT 的row 會被read lock 住,其他client 可以讀取但無法寫入。只可以由單一client 同時對同一筆row 進行SELECT......FOR UPDATE ,較不容易造成deadlock 。
InnoDB Isolation Levels, Multi-Versioning 與Concurrency當多個client 同時執行transaction 時,有可能會出現下列問題:Dirty Read :當transaction T1 在還沒COMMIT 前,其他transaction 卻可以看到T1 對資料表所做的修改。
Non-Repeatable Read :在transaction T1 並未對資料表進行修改的前提下,在同一個transaction 中的不同時間點,執行兩次SELECT 查詢卻得到不同的查詢結果(Row 資料遭到修改) 。
Phantom :在transaction T1 並未對資料表進行修改的前提下,在同一個transaction 中的不同時間點,執行兩次SELECT 查詢卻得到不同的查詢結果( 出現由其他transaction 新增的row) 。
InnoDB 的四種isolation 層級:www]slime]com]tw READ UNCOMMITTED :會發生Dirty Read 、Non-Repeatable Read 、Phantom 。
READ COMMITTED :不會發生Dirty Read ,但會發生Non-Repeatable Read 、Phantom 。www]slime_com+tw
REPEATABLE READ :不會發生Dirty Read 、Non-Repeatable Read ,但會發生Phantom 。
SERIALIZABLE :不會發生Dirty Read 、Non-Repeatable Read 、Phantom 。
REPEATABLE READ 和SERIALIZABLE 這兩個層級最大的差異在於:在REPEATABLE READ 層級中,被transaction T1 所修改且尚未COMMIT 的row ,其他transaction 無法修改;在SERIALIZABLE 層級中,即使只是被transaction T1 讀取的row 在尚未COMMIT 前,其他transaction 也無法修改。
設定isolation 層級的方式:
[mysqld]
transaction-isolation = isolation_level
或是www]slime_com+tw
SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level
SET SESSION TRANSACTION ISOLATION LEVEL isolation_level
SET TRANSACTION ISOLATION LEVEL isolation_level
Foreign Keys www]slime]com]tw InnoDB 支援Foreign Key Constraint ,只要referring table 與referenced table 皆使用InnoDB Storage Engine ,並在regerring table 的CREATE TABLE statement 中使用下列語法指定foreign key 即可:
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}]
referring column 與referenced column 必須要具有相同的data type ,而且兩者都必需被index ,若是referring column 沒有被index 則InnoDB 會自動加上index 。
若是ON UPDATE 或ON DELETE 子句,則效果等同於使用RESTRICT ,也就是parent table 中有被child table 參考到的row 將無法被刪除或修改。
用來設定InnoDB 的option : www]slime]com]tw innodb_data_home_dir
innodb_data_file_path
innodb_file_per_table
innodb_buffer_pool_size www]slime_com+tw
innodb_log_buffer_size
innodb_log_files_in_group
innodb_log_file_size
inoodb_flush_log_at_trx_commit
為了確保transaction 的資料能夠完整的保存在檔案系統中,InnoDB 必須要完成兩件事:把memory 中的資料flush 到log file( 此時資料有可能被快取到file system cache)
讓file system cache 中的資料實際寫入到log file
inoodb_flush_log_at_trx_commit 這個選項的設定值可控制InnDB 如何完成上述兩件事情。當inoodb_flush_log_at_trx_commit 的值為1 時,在每次transaction commit 時皆會執行前述兩點。www]slime_com+tw
當inoodb_flush_log_at_trx_commit 的值為0 時,系統會每隔一秒鐘自動執行前述兩點,但在每次transaction commit 時不會有特別的動作。
當inoodb_flush_log_at_trx_commit 的值為2 時,在每次transaction commit 時只會執行第一點,但要每隔一秒鐘才會自動執行第二點。
The MEMORY Enginewww]slime]com]tw table 的資料結構存在.frm 檔案中,data 與index 則是存在memory 。因此當系統重新啟動的時候MEMORY table 仍然會存在,只是所包含的資料將會遺失。
由於資料與索引皆存在memory ,具有極高的存取效率。
採用table level lock www]slime_com+tw
不可以包含有BLOB 或TEXT column
支援兩種不同的index 演算法:HASH :適用於= 與<=> 運算
BTREE :適同於Range 的比對,例如price < 500 等。
The FEDERATED Engine www]slime]com]tw 可透過FEDERATED table 將remote server 上的table 當成是local table 來使用
table 的資料結構存在.frm 檔案中
不支援transaction www]slime_com+tw
不支援lock 機制
支援SELECT, DELETE, UPDATE, INSERT Statement
Local table 的資料結構必須與remote table 完全一致,唯一的差別在於local table 要設定為:
ENGINE=FEDERATED
CONNECTION='mysql://user_name[: password]@hostname[: port]/db_name/table_name'
The Cluster Storage Engine www]slime_com+tw In-memory database in a shared-nothing system
High availibility
Scalability
High performance
雖然database 是存在memory 中,但是所有對於database 的修改皆會記錄在檔案系統上,因此即使重新啟動系統資料也不會遺失。
參考資料:
www^slime^com^tw
Paul Dubois, Stefan Hinz, Carsten Pedersen(2005), “MySQL 5.0 Certification Study Guide.”
送花文章: 8870,
收花文章: 2195 篇, 收花: 5820 次