使用調(diào)度和鎖定進(jìn)行MySQL查詢優(yōu)化
DBA在日常工作中經(jīng)常會(huì)遇到如何讓單獨(dú)的查詢執(zhí)行的速度更快。MySQL還允許你改變語(yǔ)句調(diào)度的優(yōu)先級(jí)，它可以使來(lái)自多個(gè)客戶端的查詢更好地協(xié)作，這樣單個(gè)客戶端就不會(huì)由于鎖定而等待很長(zhǎng)時(shí)間。改變優(yōu)先級(jí)還可以確保特定類型的查詢被處理得更快。這一部分講解MySQL的默認(rèn)的調(diào)度策略和可以用來(lái)影響這些策略的選項(xiàng)。它還談到了并發(fā)性插入操作的使用和存儲(chǔ)引擎鎖定層次對(duì)客戶端的并發(fā)性的影響。為了討論的方便，我們把執(zhí)行檢索(SELECT)的客戶端稱為"讀取者"，把執(zhí)行修改操作(DELETE、INSERT、REPLACE或UPDATE)的客戶端稱為"寫入者"。
MySQL的默認(rèn)的調(diào)度策略可用總結(jié)如下：
◆寫入操作優(yōu)先于讀取操作。
◆對(duì)某張數(shù)據(jù)表的寫入操作某一時(shí)刻只能發(fā)生一次，寫入請(qǐng)求按照它們到達(dá)的次序來(lái)處理。
◆對(duì)某張數(shù)據(jù)表的多個(gè)讀取操作可以同時(shí)地進(jìn)行。
MyISAM和MEMORY存儲(chǔ)引擎借助于數(shù)據(jù)表鎖來(lái)實(shí)現(xiàn)這樣的調(diào)度策略。當(dāng)客戶端訪問(wèn)某張表的時(shí)候，首先必須獲取它的鎖。當(dāng)客戶端完成對(duì)表的操作的時(shí)候，鎖就會(huì)被解除。通過(guò)LOCK TABLES和UNLOCK TABLES語(yǔ)句來(lái)顯式地獲取或釋放鎖是可行的，但是在通常情況下，服務(wù)器的鎖管理器會(huì)自動(dòng)地在需要的時(shí)候獲取鎖，在不再需要的時(shí)候釋放鎖。獲取的鎖的類型依賴于客戶端是寫入還是讀取操作。
對(duì)某張表進(jìn)行寫入操作的客戶端必須擁有獨(dú)占的(排他的)訪問(wèn)權(quán)的鎖。操作在進(jìn)行的過(guò)程中，該數(shù)據(jù)表處于不一致的(inconsistent)狀態(tài)，因?yàn)閿?shù)據(jù)記錄在刪除、添加或修改的時(shí)候，數(shù)據(jù)表上的索引也可能需要更新以相互匹配。這個(gè)數(shù)據(jù)表在變化的過(guò)程中，如果允許其它的客戶端訪問(wèn)，會(huì)出現(xiàn)問(wèn)題。非常明顯，允許兩個(gè)客戶端同時(shí)寫入一張數(shù)據(jù)表是不利的，因?yàn)檫@樣的操作會(huì)很快使數(shù)據(jù)表中的信息成為一堆無(wú)用的垃圾。但是允許客戶端讀取變化之中的數(shù)據(jù)表也不好，因?yàn)檎谧x取的位置中的數(shù)據(jù)可能正在變化(修改)，讀取的結(jié)果可能不是真實(shí)的。
對(duì)某張表執(zhí)行讀取操作的客戶端必須獲取一個(gè)鎖，防止在讀取的過(guò)程中，其它的客戶端寫入或改變表。但是這個(gè)鎖不需要獨(dú)占的訪問(wèn)權(quán)。讀取操作不會(huì)改變數(shù)據(jù)，因此沒有理由讓某個(gè)讀取者阻止其它的讀取者訪問(wèn)這張表。因此讀取鎖允許其它的客戶端在同一時(shí)刻讀取這張表。
MySQL提供了幾個(gè)語(yǔ)句調(diào)節(jié)符，允許你修改它的調(diào)度策略：
◆LOW_PRIORITY關(guān)鍵字應(yīng)用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。
◆HIGH_PRIORITY關(guān)鍵字應(yīng)用于SELECT和INSERT語(yǔ)句。
◆DELAYED關(guān)鍵字應(yīng)用于INSERT和REPLACE語(yǔ)句。
LOW_PRIORITY和HIGH_PRIORITY調(diào)節(jié)符影響那些使用數(shù)據(jù)表鎖的存儲(chǔ)引擎(例如MyISAM和MEMORY)。DELAYED調(diào)節(jié)符作用于MyISAM和MEMORY數(shù)據(jù)表。
改變語(yǔ)句調(diào)度的優(yōu)先級(jí)
LOW_PRIORITY關(guān)鍵字影響DELETE、INSERT、LOAD DATA、REPLACE和UPDATE語(yǔ)句的執(zhí)行調(diào)度。通常情況下，某張數(shù)據(jù)表正在被讀取的時(shí)候，如果有寫入操作到達(dá)，那么寫入者一直等待讀取者完成操作(查詢開始之后就不能中斷，因此允許讀取者完成操作)。如果寫入者正在等待的時(shí)候，另一個(gè)讀取操作到達(dá)了，該讀取操作也會(huì)被阻塞(block)，因?yàn)槟J(rèn)的調(diào)度策略是寫入者優(yōu)先于讀取者。當(dāng)***個(gè)讀取者完成操作的時(shí)候，寫入者開始操作，并且直到該寫入者完成操作，第二個(gè)讀取者才開始操作。
如果寫入操作是一個(gè)LOW_PRIORITY(低優(yōu)先級(jí))請(qǐng)求，那么系統(tǒng)就不會(huì)認(rèn)為它的優(yōu)先級(jí)高于讀取操作。在這種情況下，如果寫入者在等待的時(shí)候，第二個(gè)讀取者到達(dá)了，那么就允許第二個(gè)讀取者插到寫入者之前。只有在沒有其它的讀取者的時(shí)候，才允許寫入者開始操作。理論上，這種調(diào)度修改暗示著，可能存在LOW_PRIORITY寫入操作永遠(yuǎn)被阻塞的情況。如果前面的讀取操作在進(jìn)行的過(guò)程中一直有其它的讀取操作到達(dá)，那么新的請(qǐng)求都會(huì)插入到LOW_PRIORITY寫入操作之前。
SELECT查詢的HIGH_PRIORITY(高優(yōu)先級(jí))關(guān)鍵字也類似。它允許SELECT插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優(yōu)先級(jí)更高。另外一種影響是，高優(yōu)先級(jí)的SELECT在正常的SELECT語(yǔ)句之前執(zhí)行，因?yàn)檫@些語(yǔ)句會(huì)被寫入操作阻塞。
如果你希望所有支持LOW_PRIORITY選項(xiàng)的語(yǔ)句都默認(rèn)地按照低優(yōu)先級(jí)來(lái)處理，那么請(qǐng)使用--low-priority-updates選項(xiàng)來(lái)啟動(dòng)服務(wù)器。通過(guò)使用INSERT HIGH_PRIORITY來(lái)把INSERT語(yǔ)句提高到正常的寫入優(yōu)先級(jí)，可以消除該選項(xiàng)對(duì)單個(gè)INSERT語(yǔ)句的影響。
#p#
使用延遲插入操作
DELAYED調(diào)節(jié)符應(yīng)用于INSERT和REPLACE語(yǔ)句。當(dāng)DELAYED插入操作到達(dá)的時(shí)候，服務(wù)器把數(shù)據(jù)行放入一個(gè)隊(duì)列中，并立即給客戶端返回一個(gè)狀態(tài)信息，這樣客戶端就可以在數(shù)據(jù)表被真正地插入記錄之前繼續(xù)進(jìn)行操作了。如果讀取者從該數(shù)據(jù)表中讀取數(shù)據(jù)，隊(duì)列中的數(shù)據(jù)就會(huì)被保持著，直到?jīng)]有讀取者為止。接著服務(wù)器開始插入延遲數(shù)據(jù)行(delayed-row)隊(duì)列中的數(shù)據(jù)行。在插入操作的同時(shí)，服務(wù)器還要檢查是否有新的讀取請(qǐng)求到達(dá)和等待。如果有，延遲數(shù)據(jù)行隊(duì)列就被掛起，允許讀取者繼續(xù)操作。當(dāng)沒有讀取者的時(shí)候，服務(wù)器再次開始插入延遲的數(shù)據(jù)行。這個(gè)過(guò)程一直進(jìn)行，直到隊(duì)列空了為止。
感覺上LOW_PRIORITY和DELAYED是相似的，兩者都允許數(shù)據(jù)行插入操作被延遲，但是它們對(duì)客戶端操作的影響卻有很大的差異。LOW_ PRIORITY強(qiáng)迫客戶端等待，直到那些數(shù)據(jù)行可以被插入數(shù)據(jù)表。DELAYED允許客戶端繼續(xù)操作，服務(wù)器在內(nèi)存中緩沖那些數(shù)據(jù)行，直到自己有時(shí)間處理它們。
如果其它的客戶端可能運(yùn)行很長(zhǎng)的SELECT語(yǔ)句并且你不希望阻塞，等待插入操作完成的時(shí)候，INSERT DELAYED就非常有用處了?？蛻舳颂峤籌NSERT DELAYED的時(shí)候可能處理得很快，因?yàn)榉?wù)器只是簡(jiǎn)單地把要插入的數(shù)據(jù)行排隊(duì)。
但是，你也必須知道正常的INSERT與INSERT DELAYED行為之間的一些其它的差異。如果INSERT DELAYED語(yǔ)句包含語(yǔ)法錯(cuò)誤，客戶端會(huì)得到一個(gè)錯(cuò)誤，但是卻無(wú)法得到其它一些在正常情況下可以使用的信息。例如，當(dāng)語(yǔ)句返回的時(shí)候，你無(wú)法依賴(得到)AUTO_INCREMENT(自動(dòng)增長(zhǎng))值。同樣，你無(wú)法得到***索引的副本數(shù)量。發(fā)生這種情況的原因在于插入操作在真正地被執(zhí)行之前已經(jīng)返回了狀態(tài)信息。另一種可能出現(xiàn)的情況是，由于INSERT DELAYED語(yǔ)句的數(shù)據(jù)行都在內(nèi)存中排隊(duì)，當(dāng)服務(wù)器崩潰或者使用kill -9退出的時(shí)候，數(shù)據(jù)行可能丟失(正常情況下，kill -TERM終止命令不會(huì)導(dǎo)致這種情況，因?yàn)榉?wù)器在退出之前會(huì)把數(shù)據(jù)行插入表中)。
使用并發(fā)的插入操作
MyISAM存儲(chǔ)引擎有一條例外的規(guī)則，它允許讀取者阻塞寫入者。這種現(xiàn)象發(fā)生在MyISAM數(shù)據(jù)表中間沒有"空洞"(可能是刪除或更新數(shù)據(jù)行的結(jié)果)的情況下。當(dāng)數(shù)據(jù)表沒有"空洞"的時(shí)候，任何INSERT語(yǔ)句必然在末尾而不是中部添加數(shù)據(jù)行。在這種情況下，MySQL允許其它客戶端在讀取數(shù)據(jù)的同時(shí)向數(shù)據(jù)表添加數(shù)據(jù)行。這就是"并發(fā)性插入操作"，因?yàn)樗鼈兺瑫r(shí)發(fā)生，檢索并沒有被阻塞。
如果你希望使用并發(fā)性插入操作，請(qǐng)注意下面一些事項(xiàng)：
◆在INSERT語(yǔ)句中不要使用LOW_PRIORITY調(diào)節(jié)符。它會(huì)引起INSERT經(jīng)常被讀取者阻塞，因此阻礙了并發(fā)性插入操作的執(zhí)行。
◆如果讀取者需要顯式地鎖定數(shù)據(jù)表以執(zhí)行并發(fā)性插入操作，就應(yīng)該使用LOCK TABLES ... READ LOCAL，而不是LOCK TABLES ... READ。LOCAL關(guān)鍵字會(huì)獲取一個(gè)鎖，允許并發(fā)性操作繼續(xù)進(jìn)行，因?yàn)樗荒軕?yīng)用于數(shù)據(jù)表中已有的數(shù)據(jù)行，不會(huì)阻塞那些添加到末尾的新數(shù)據(jù)行。
◆LOAD DATA操作應(yīng)該使用CONCURRENT調(diào)節(jié)符，允許該數(shù)據(jù)表上的SELECT語(yǔ)句同時(shí)執(zhí)行。
◆中間包含了"空洞"的MyISAM數(shù)據(jù)表不能使用并發(fā)性插入操作。但是，你可以使用OPTIMIZE TABLE語(yǔ)句來(lái)整理該數(shù)據(jù)表的碎片。
鎖的層次和并發(fā)性
前面討論的調(diào)度調(diào)節(jié)符允許你改變默認(rèn)的調(diào)度策略。其中的大部分內(nèi)容都是介紹使用這些調(diào)節(jié)符來(lái)解決數(shù)據(jù)表層次(table-level)的鎖引起的問(wèn)題，這都是MyISAM和MEMORY存儲(chǔ)引擎用來(lái)管理數(shù)據(jù)表爭(zhēng)用的問(wèn)題的。
BDB和InnoDB存儲(chǔ)引擎實(shí)現(xiàn)了不同層次的鎖，所以其性能特征和對(duì)爭(zhēng)用的管理是不同的。BDB引擎使用頁(yè)面層次(page-level)的鎖。InnoDB引擎使用數(shù)據(jù)行層次(row-level)的鎖，但是只在必要的時(shí)候使用(在很多情況下，例如當(dāng)讀取操作都完成的時(shí)候，InnoDB可能根本就不使用鎖)。
存儲(chǔ)引擎使用的鎖的層次對(duì)客戶端的并發(fā)操作有很大的影響。假設(shè)兩個(gè)客戶端都希望更新某個(gè)數(shù)據(jù)表中的一行。由于要執(zhí)行更新，每個(gè)客戶端都需要一個(gè)寫入鎖。對(duì)于MyISAM數(shù)據(jù)表，引擎會(huì)為***個(gè)客戶端分配一個(gè)鎖，這會(huì)引起第二個(gè)客戶端阻塞，直到***個(gè)客戶端完成操作。對(duì)于BDB數(shù)據(jù)表，它可以實(shí)現(xiàn)更大的并發(fā)性：兩個(gè)更新操作會(huì)同步進(jìn)行，除非兩個(gè)數(shù)據(jù)行都位于同一個(gè)頁(yè)面中。在InnoDB數(shù)據(jù)表中，并發(fā)性更高;只要兩個(gè)客戶端沒有更新同一行，兩個(gè)更新操作就能同時(shí)發(fā)生。
一般的規(guī)則是，鎖的層次越細(xì)微，并發(fā)性越好，因?yàn)橹灰蛻舳耸褂脭?shù)據(jù)表的部分不同，那么使用表的客戶端就可以更多。它實(shí)際暗示著不同的存儲(chǔ)引擎適合于不同的語(yǔ)句混合(mixes)：
◆MyISAM檢索的速度非?？?。但是使用表層次的鎖可能成為混合的檢索和更新環(huán)境中的問(wèn)題，特別是檢索傾向于長(zhǎng)時(shí)間運(yùn)行的時(shí)候。在這些條件下，更新可能需要等待很久才能進(jìn)行。
◆當(dāng)更新操作很多的時(shí)候，BDB和InnoDB數(shù)據(jù)表可以提供更好的性能。由于鎖在頁(yè)面或數(shù)據(jù)行層次進(jìn)行，表被鎖定的范圍較小。這會(huì)減少鎖的爭(zhēng)用，提高并發(fā)性。
在防止死鎖(deadlock)方面，表層次的鎖比細(xì)微層次的鎖更有優(yōu)勢(shì)。使用表層次的鎖的時(shí)候，死鎖不會(huì)發(fā)生。服務(wù)器可以通過(guò)查看語(yǔ)句來(lái)檢測(cè)需要的數(shù)據(jù)表，并提前鎖定它們。而InnoDB和BDB數(shù)據(jù)表會(huì)發(fā)生死鎖，因?yàn)檫@些存儲(chǔ)引擎沒有在事務(wù)開始的時(shí)候分配所有必要的鎖。作為代替，在事務(wù)處理的過(guò)程中，當(dāng)檢測(cè)到需要鎖的時(shí)候才分配。這就可能出現(xiàn)兩個(gè)語(yǔ)句獲取了鎖，接著試圖進(jìn)一步獲取鎖(需要多個(gè)鎖)，但是這些鎖卻被對(duì)方保持著，等待對(duì)方釋放。其結(jié)果是每個(gè)客戶端都擁有一個(gè)鎖，同時(shí)還需要利用其它的客戶端擁有的鎖才能繼續(xù)執(zhí)行。這會(huì)導(dǎo)致死鎖，服務(wù)器必須終止其中一個(gè)事務(wù)。
【編輯推薦】
