史萊姆論壇

返回   史萊姆論壇 > 專業主討論區 > 論壇程式討論區
忘記密碼?
註冊帳號 論壇說明 標記討論區已讀

歡迎您來到『史萊姆論壇』 ^___^

您目前正以訪客的身份瀏覽本論壇,訪客所擁有的權限將受到限制,您可以瀏覽本論壇大部份的版區與文章,但您將無法參與任何討論或是使用私人訊息與其他會員交流。若您希望擁有完整的使用權限,請註冊成為我們的一份子,註冊的程序十分簡單、快速,而且最重要的是--註冊是完全免費的!

請點擊這裡:『註冊成為我們的一份子!』

Google 提供的廣告


發文 回覆
 
主題工具 顯示模式
舊 2007-12-16, 02:19 PM   #1
Admin1
管理員
 
Admin1 的頭像
榮譽勳章
UID - 112827
在線等級: 級別:29 | 在線時長:972小時 | 升級還需:48小時級別:29 | 在線時長:972小時 | 升級還需:48小時級別:29 | 在線時長:972小時 | 升級還需:48小時級別:29 | 在線時長:972小時 | 升級還需:48小時級別:29 | 在線時長:972小時 | 升級還需:48小時級別:29 | 在線時長:972小時 | 升級還需:48小時級別:29 | 在線時長:972小時 | 升級還需:48小時級別:29 | 在線時長:972小時 | 升級還需:48小時級別:29 | 在線時長:972小時 | 升級還需:48小時
註冊日期: 2007-02-18
VIP期限: 0000-00
文章: 3507
精華: 0
現金: 1702 金幣
資產: 10196 金幣
預設 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的順序如下:
  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資料表中不可以包含BLOBTEXT 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檔案中記載著所有被MERGEMyISAM table名稱。
  • MERGE table在邏輯上將多個MyISAM table合併在一起。可以避免檔案系統的最大檔案大小限制,因為你可以將資料分散到多個MyISAM table中,然後再將它們合併起來即可。www]slime_com+tw
  • 使用table level lock
  • 具有binary portable特性。因為.MRG本身只是一般的文字檔案,而被MERGEMyISAM 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 Engine
www]slime]com]tw
  • 每個資料表都會對應到檔案系統上的 .frm,而dataindex則是儲存在shared tablespace中。(tablespace裡面還放有data dictionaryrollback segment)
  • 支援transaction,並提供完整的ACID(atomicity, consistency, isolation, durability)相容性。
  • 採用multi-versioningrow level lockwww]slime_com+tw
  • 支援foreign key integrity(包含cascade update, delete)
  • tablespace具有binary portable的特性(databasetable的名稱必須為小寫)
InnodbACIDwww]slime]com]tw
  • Atomictransaction中的所有statement皆視為單一的unit,必須全部執行成功,若是有任一statement執行失敗則必需rollback
  • Consistent:保持在consistent state的資料庫在執行transaction後依然保持在consistent state
  • Isolationtransaction之間無法相互影響。
  • Durabetransaction所有對資料庫的修改皆完整的記錄在資料庫之中,不會喪失。
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中被SELECTrow會被read lock住,其他client可以讀取但無法寫入。可以用多個不同的client同時對同一筆row進行SELECT......LOCK IN SHARE MODE,也因此較容易造成deadlockwww]slime_com+tw
    FOR UPDATE:在transaction中被SELECTrow會被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 ReadNon-Repeatable ReadPhantom
  • READ COMMITTED:不會發生Dirty Read,但會發生Non-Repeatable ReadPhantomwww]slime_com+tw
  • REPEATABLE READ:不會發生Dirty ReadNon-Repeatable Read,但會發生Phantom
  • SERIALIZABLE:不會發生Dirty ReadNon-Repeatable ReadPhantom
REPEATABLE READSERIALIZABLE這兩個層級最大的差異在於:在REPEATABLE READ層級中,被transaction T1所修改且尚未COMMITrow,其他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 tablereferenced table皆使用InnoDB Storage Engine,並在regerring tableCREATE 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 columnreferenced column必須要具有相同的data type,而且兩者都必需被index,若是referring column沒有被indexInnoDB會自動加上index

若是ON UPDATEON DELETE子句,則效果等同於使用RESTRICT,也就是parent table中有被child table參考到的row將無法被刪除或修改。
用來設定InnoDBoptionwww]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中的資料flushlog 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 Engine
www]slime]com]tw
  • table的資料結構存在.frm檔案中,dataindex則是存在memory。因此當系統重新啟動的時候MEMORY table仍然會存在,只是所包含的資料將會遺失。
  • 由於資料與索引皆存在memory,具有極高的存取效率。
  • 採用table level lockwww]slime_com+tw
  • 不可以包含有BLOBTEXT column
支援兩種不同的index演算法:
  1. HASH:適用於=<=>運算
  2. BTREE:適同於Range的比對,例如price < 500等。
The FEDERATED Enginewww]slime]com]tw
  • 可透過FEDERATED tableremote 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.”
Admin1 目前離線  
送花文章: 8870, 收花文章: 2195 篇, 收花: 5820 次
回覆時引用此帖
發文 回覆


主題工具
顯示模式

發表規則
不可以發文
不可以回覆主題
不可以上傳附加檔案
不可以編輯您的文章

論壇啟用 BB 語法
論壇啟用 表情符號
論壇啟用 [IMG] 語法
論壇禁用 HTML 語法
Trackbacks are 禁用
Pingbacks are 禁用
Refbacks are 禁用


所有時間均為台北時間。現在的時間是 09:26 PM


Powered by vBulletin® 版本 3.6.8
版權所有 ©2000 - 2019, Jelsoft Enterprises Ltd.


SEO by vBSEO 3.6.1