為達到最高鎖定速度,除InnoDB和BDB之外,對所有存儲引擎,MySQL使用表鎖定(而不是頁、行或者列鎖定)。
對于InnoDB和BDB表,如果你用LOCK TABLES顯式鎖定表,MySQL只使用表鎖定。對于這些表類型,我們建議你根本不要使用LOCK TABLES,因為InnoDB使用自動行級鎖定而BDB使用頁級鎖定來保證事務隔離。
對于大表,對于大多數應用程序,表鎖定比行鎖定更好,但存在部分缺陷。
表鎖定使許多線程同時從一個表中進行讀取操作,但如果一個線程想要對表進行寫操作,它必須首先獲得獨占訪問。更新期間,所有其它想要訪問該表的線程必須等待直到更新完成。
表更新通常情況認為比表檢索更重要,因此給予它們更高的優先級。這應確保更新一個表的活動不能“餓死”,即使該表上有很繁重的SELECT活動。
表鎖定在這種情況下會造成問題,例如當線程正等待,因為硬盤已滿并且在線程可以處理之前必須有空閑空間。在這種情況下,所有想要訪問出現問題的表的線程也被設置成等待狀態,直到有更多的硬盤空間可用。
表鎖定在下面的情況下也存在問題:
· 一個客戶發出長時間運行的查詢。
· 然后,另一個客戶對同一個表進行更新。該客戶必須等待直到SELECT完成。
· 另一個客戶對同一個表上發出了另一個SELECT語句。因為UPDATE比SELECT優先級高,該SELECT語句等待UPDATE完成,并且等待第1個SELECT完成。
下面描述了一些方法來避免或減少表鎖定造成的競爭:
· 試圖使SELECT語句運行得更快。你可能必須創建一些摘要(summary)表做到這點。
· 用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優先級。在這種情況下,在先前情形的第2個SELECT語句將在UPDATE語句前執行,而不需要等候第1個SELECT完成。
· 可以使用SET LOW_PRIORITY_UPDATES=1語句指定具體連接中的所有更新應使用低優先級。參見13.5.3節,“SET語法”
· 可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先級。
· 可以用HIGH_PRIORITY屬性給與一個特定的SELECT語句較高優先級。參見13.2.7節,“SELECT語法”。
· 為max_write_lock_count系統變量指定一個低值來啟動mysqld來強制MySQL在具體數量的插入完成后臨時提高所有等待一個表的SELECT語句的優先級。這樣允許在一定數量的WRITE鎖定后給出READ鎖定。
· 如果你有關于INSERT結合SELECT的問題,切換到使用新的MyISAM表,因為它們支持并發的SELECT和INSERT。
· 如果你對同一個表混合插入和刪除,INSERT DELAYED將會有很大的幫助。參見13.2.4.2節,“INSERT DELAYED語法”。
· 如果你對同一個表混合使用SELECT和DELETE語句出現問題,DELETE的LIMIT選項可以有所幫助。參見13.2.1節,“DELETE語法”。
· 對SELECT語句使用SQL_BUFFER_RESULT可以幫助使表鎖定時間變短。參見13.2.7節,“SELECT語法”。
· 可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊列。在這種情況下,寫鎖定和讀鎖定將具有相同的優先級,對一些應用程序會有幫助。
這里是一些MySQL中表鎖定相關的技巧:
· 如果不混合更新與需要在同一個表中檢查許多行的選擇,可以進行并行操作。
· 可以使用LOCK TABLES來提高速度,因為在一個鎖定中進行許多更新比沒有鎖定的更新要快得多。將表中的內容切分為幾個表也可以有所幫助。
· 如果在MySQL中表鎖定時遇到速度問題,可以將你的表轉換為InnoDB或BDB表來提高性能。參見15.2節,“InnoDB存儲引擎”和15.5節,“BDB (BerkeleyDB)存儲引擎”。