MySQL查詢優化系列講座之調度和鎖定

  前面的部分主要是聚焦於如何讓單獨的查詢執行的速度更快。MySQL還允許你改變語句調度的優先級,它可以使來自多個客戶端的查詢更好地協作,這樣單個客戶端就不會由於鎖定而等待很長時間。改變優先級還可以確保特定類型的查詢被處理得更快。這一部分講解MySQL的默認的調度策略和可以用來影響這些策略的選項。它還談到瞭並發性插入操作的使用和存儲引擎鎖定層次對客戶端的並發性的影響。為瞭討論的方便,我們把執行檢索(SELECT)的客戶端稱為”讀取者”,把執行修改操作(DELETE、INSERT、REPLACE或UPDATE)的客戶端稱為”寫入者”。


  MySQL的默認的調度策略可用總結如下:


  · 寫入操作優先於讀取操作。


  · 對某張數據表的寫入操作某一時刻隻能發生一次,寫入請求按照它們到達的次序來處理。


  · 對某張數據表的多個讀取操作可以同時地進行。


  MyISAM和MEMORY存儲引擎借助於數據表鎖來實現這樣的調度策略。當客戶端訪問某張表的時候,首先必須獲取它的鎖。當客戶端完成對表的操作的時候,鎖就會被解除。通過LOCK TABLES和UNLOCK TABLES語句來顯式地獲取或釋放鎖是可行的,但是在通常情況下,服務器的鎖管理器會自動地在需要的時候獲取鎖,在不再需要的時候釋放鎖。獲取的鎖的類型依賴於客戶端是寫入還是讀取操作。


  對某張表進行寫入操作的客戶端必須擁有獨占的(排他的)訪問權的鎖。操作在進行的過程中,該數據表處於不一致的(inconsistent)狀態,因為數據記錄在刪除、添加或修改的時候,數據表上的索引也可能需要更新以相互匹配。這個數據表在變化的過程中,如果允許其它的客戶端訪問,會出現問題。非常明顯,允許兩個客戶端同時寫入一張數據表是不利的,因為這樣的操作會很快使數據表中的信息成為一堆無用的垃圾。但是允許客戶端讀取變化之中的數據表也不好,因為正在讀取的位置中的數據可能正在變化(修改),讀取的結果可能不是真實的。


  對某張表執行讀取操作的客戶端必須獲取一個鎖,防止在讀取的過程中,其它的客戶端寫入或改變表。但是這個鎖不需要獨占的訪問權。讀取操作不會改變數據,因此沒有理由讓某個讀取者阻止其它的讀取者訪問這張表。因此讀取鎖允許其它的客戶端在同一時刻讀取這張表。


  MySQL提供瞭幾個語句調節符,允許你修改它的調度策略:


  · LOW_PRIORITY關鍵字應用於DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。


  · HIGH_PRIORITY關鍵字應用於SELECT和INSERT語句。


  · DELAYED關鍵字應用於INSERT和REPLACE語句。


  LOW_PRIORITY和HIGH_PRIORITY調節符影響那些使用數據表鎖的存儲引擎(例如MyISAM和MEMORY)。DELAYED調節符作用於MyISAM和MEMORY數據表。


  改變語句調度的優先級


  LOW_PRIORITY關鍵字影響DELETE、INSERT、LOAD DATA、REPLACE和UPDATE語句的執行調度。通常情況下,某張數據表正在被讀取的時候,如果有寫入操作到達,那麼寫入者一直等待讀取者完成操作(查詢開始之後就不能中斷,因此允許讀取者完成操作)。如果寫入者正在等待的時候,另一個讀取操作到達瞭,該讀取操作也會被阻塞(block),因為默認的調度策略是寫入者優先於讀取者。當第一個讀取者完成操作的時候,寫入者開始操作,並且直到該寫入者完成操作,第二個讀取者才開始操作。


  如果寫入操作是一個LOW_PRIORITY(低優先級)請求,那麼系統就不會認為它的優先級高於讀取操作。在這種情況下,如果寫入者在等待的時候,第二個讀取者到達瞭,那麼就允許第二個讀取者插到寫入者之前。隻有在沒有其它的讀取者的時候,才允許寫入者開始操作。理論上,這種調度修改暗示著,可能存在LOW_PRIORITY寫入操作永遠被阻塞的情況。如果前面的讀取操作在進行的過程中一直有其它的讀取操作到達,那麼新的請求都會插入到LOW_PRIORITY寫入操作之前。


  SELECT查詢的HIGH_PRIORITY(高優先級)關鍵字也類似。它允許SELECT插入正在等待的寫入操作之前,即使在正常情況下寫入操作的優先級更高。另外一種影響是,高優先級的SELECT在正常的SELECT語句之前執行,因為這些語句會被寫入操作阻塞。


  如果你希望所有支持LOW_PRIORITY選項的語句都默認地按照低優先級來處理,那麼請使用–low-priority-updates選項來啟動服務器。通過使用INSERT HIGH_PRIORITY來把INSERT語句提高到正常的寫入優先級,可以消除該選項對單個INSERT語句的影響。


  使用延遲插入操作


  DELAYED調節符應用於INSERT和REPLACE語句。當DELAYED插入操作到達的時候,服務器把數據行放入一個隊列中,並立即給客戶端返回一個狀態信息,這樣客戶端就可以在數據表被真正地插入記錄之前繼續進行操作瞭。如果讀取者從該數據表中讀取數據,隊列中的數據就會被保持著,直到沒有讀取者為止。接著服務器開始插入延遲數據行(delayed-row)隊列中的數據行。在插入操作的同時,服務器還要檢查是否有新的讀取請求到達和等待。如果有,延遲數據行隊列就被掛起,允許讀取者繼續操作。當沒有讀取者的時候,服務器再次開始插入延遲的數據行。這個過程一直進行,直到隊列空瞭為止。


  感覺上LOW_PRIORITY和DELAYED是相似的,兩者都允許數據行插入操作被延遲,但是它們對客戶端操作的影響卻有很大的差異。LOW_ PRIORITY強迫客戶端等待,直到那些數據行可以被插入數據表。DELAYED允許客戶端繼續操作,服務器在內存中緩沖那些數據行,直到自己有時間處理它們。


  如果其它的客戶端可能運行很長的SELECT語句並且你不希望阻塞,等待插入操作完成的時候,INSERT DELAYED就非常有用處瞭。客戶端提交INSERT DELAYED的時候可能處理得很快,因為服務器隻是簡單地把要插入的數據行排隊。


  但是,你也必須知道正常的INSERT與INSERT DELAYED行為之間的一些其它的差異。如果INSERT DELAYED語句包含語法錯誤,客戶端會得到一個錯誤,但是卻無法得到其它一些在正常情況下可以使用的信息。例如,當語句返回的時候,你無法依賴(得到)AUTO_INCREMENT(自動增長)值。同樣,你無法得到唯一索引的副本數量。發生這種情況的原因在於插入操作在真正地被執行之前已經返回瞭狀態信息。另一種可能出現的情況是,由於INSERT DELAYED語句的數據行都在內存中排隊,當服務器崩潰或者使用kill -9退出的時候,數據行可能丟失(正常情況下,kill -TERM終止命令不會導致這種情況,因為服務器在退出之前會把數據行插入表中)。


  使用並發的插入操作


  MyISAM存儲引擎有一條例外的規則,它允許讀取者阻塞寫入者。這種現象發生在MyISAM數據表中間沒有”空洞”(可能是刪除或更新數據行的結果)的情況下。當數據表沒有”空洞”的時候,任何INSERT語句必然在末尾而不是中部添加數據行。在這種情況下,MySQL允許其它客戶端在讀取數據的同時向數據表添加數據行。這就是”並發性插入操作”,因為它們同時發生,檢索並沒有被阻塞。


  如果你希望使用並發性插入操作,請註意下面一些事項:


  · 在INSERT語句中不要使用LOW_PRIORITY調節符。它會引起INSERT經常被讀取者阻塞,因此阻礙瞭並發性插入操作的執行。


  · 如果讀取者需要顯式地鎖定數據表以執行並發性插入操作,就應該使用LOCK TABLES … READ LOCAL,而不是LOCK TABLES … READ。LOCAL關鍵字會獲取一個鎖,允許並發性操作繼續進行,因為它隻能應用於數據表中已有的數據行,不會阻塞那些添加到末尾的新數據行。


  · LOAD DATA操作應該使用CONCURRENT調節符,允許該數據表上的SELECT語句同時執行。


  · 中間包含瞭”空洞”的MyISAM數據表不能使用並發性插入操作。但是,你可以使用OPTIMIZE TABLE語句來整理該數據表的碎片。


  鎖的層次和並發性


  前面討論的調度調節符允許你改變默認的調度策略。其中的大部分內容都是介紹使用這些調節符來解決數據表層次(table-level)的鎖引起的問題,這都是MyISAM和MEMORY存儲引擎用來管理數據表爭用的問題的。


  BDB和InnoDB存儲引擎實現瞭不同層次的鎖,所以其性能特征和對爭用的管理是不同的。BDB引擎使用頁面層次(page-level)的鎖。InnoDB引擎使用數據行層次(row-level)的鎖,但是隻在必要的時候使用(在很多情況下,例如當讀取操作都完成的時候,InnoDB可能根本就不使用鎖)。


  存儲引擎使用的鎖的層次對客戶端的並發操作有很大的影響。假設兩個客戶端都希望更新某個數據表中的一行。由於要執行更新,每個客戶端都需要一個寫入鎖。對於MyISAM數據表,引擎會為第一個客戶端分配一個鎖,這會引起第二個客戶端阻塞,直到第一個客戶端完成操作。對於BDB數據表,它可以實現更大的並發性:兩個更新操作會同步進行,除非兩個數據行都位於同一個頁面中。在InnoDB數據表中,並發性更高;隻要兩個客戶端沒有更新同一行,兩個更新操作就能同時發生。


  一般的規則是,鎖的層次越細微,並發性越好,因為隻要客戶端使用數據表的部分不同,那麼使用表的客戶端就可以更多。它實際暗示著不同的存儲引擎適合於不同的語句混合(mixes):


  · MyISAM檢索的速度非常快。但是使用表層次的鎖可能成為混合的檢索和更新環境中的問題,特別是檢索傾向於長時間運行的時候。在這些條件下,更新可能需要等待很久才能進行。


  · 當更新操作很多的時候,BDB和InnoDB數據表可以提供更好的性能。由於鎖在頁面或數據行層次進行,表被鎖定的范圍較小。這會減少鎖的爭用,提高並發性。


  在防止死鎖(deadlock)方面,表層次的鎖比細微層次的鎖更有優勢。使用表層次的鎖的時候,死鎖不會發生。服務器可以通過查看語句來檢測需要的數據表,並提前鎖定它們。而InnoDB和BDB數據表會發生死鎖,因為這些存儲引擎沒有

發佈留言