SQL - 04. Storage Engines

[Admin1]

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的順序如下：

1. Write Query具有比Read Query更高的優先權。
2. 若是系統收到多個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 Formatswww]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-formatwww]slime_com+tw

• 資料經過壓縮以減少所佔用的磁碟空間
• 儲存格式經過最佳化，因此具有很高的查詢效率
• 資料表是唯讀的

The MERGE Enginewww]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 lockwww]slime_com+tw
• 支援foreign key integrity(包含cascade update, delete)
• tablespace具有binary portable的特性(但database與table的名稱必須為小寫)

Innodb與ACIDwww]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 Keyswww]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_sizewww]slime_com+tw
innodb_log_buffer_size
innodb_log_files_in_group
innodb_log_file_size
inoodb_flush_log_at_trx_commit

為了確保transaction的資料能夠完整的保存在檔案系統中，InnoDB必須要完成兩件事：

1. 把memory中的資料flush到log file(此時資料有可能被快取到file system cache)
2. 讓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 lockwww]slime_com+tw
• 不可以包含有BLOB或TEXT column

支援兩種不同的index演算法：

1. HASH：適用於=與<=>運算
2. BTREE：適同於Range的比對，例如price < 500等。

The FEDERATED Enginewww]slime]com]tw

• 可透過FEDERATED table將remote server上的table當成是local table來使用
• table的資料結構存在.frm檔案中
• 不支援transactionwww]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 Enginewww]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.”