最近搬遷服務(wù)器也遇到一個(gè)問題，一個(gè)ecshop的站點(diǎn)，訪問速度很慢，以前也沒有這種情況發(fā)生，現(xiàn)在出現(xiàn)這么慢的情況確實(shí)讓人疑惑，換了另外一個(gè)服務(wù)器也還是這樣，可是訪問的人也不多呢，帶寬也不差，所以納悶…
多搜索一下資料看看，似乎找到了出路，可以不用再嘗試換服務(wù)器了。
我把表類型換為innodb類型后，情況就好很多了，請(qǐng)參考后面的資料。
sql語(yǔ)句類似 ALTER p aslibra TYPE = innodb ; 很簡(jiǎn)單
其實(shí)碰到這個(gè)問題我就看了一下數(shù)據(jù)庫(kù)的進(jìn)程，發(fā)現(xiàn)有很多l(xiāng)ocked狀態(tài)的查詢語(yǔ)句，比較奇怪為何會(huì)這樣，為什么會(huì)出現(xiàn)這樣的情況的，這個(gè)情況很可能是讀寫限制所導(dǎo)致的，也可能是讀寫速度有關(guān)，可是不理解為何一直鎖定而不放開，導(dǎo)致頁(yè)面等待時(shí)間很長(zhǎng)。況且讀寫也要求不高，帶寬訪問人數(shù)等都并不高呢？
換了一個(gè)數(shù)據(jù)庫(kù)還是不行，看來得郁悶一陣了，搬服務(wù)器真能夠讓人變得暴躁，起碼我就不耐煩了，搬來搬去的折磨的要死，就好像給你兩個(gè)瓶子，從A瓶子倒東西到B瓶子，然后復(fù)制一份到A,把A搬走，A穩(wěn)定后，又開始把A的東西挪到B,反復(fù)折磨人的意志。
查了些資料，記錄一下，希望以后借鑒：
引用最近有個(gè)項(xiàng)目網(wǎng)站訪問很慢。上去查了查，發(fā)現(xiàn)cpu經(jīng)常100%,運(yùn)行vmstat看了看，發(fā)現(xiàn)瓶頸不在cpu,不在磁盤上。系統(tǒng)是Java+MySQL,發(fā)現(xiàn)這兩個(gè)東西也幾乎不占用cpu,都是排在很后面的。
難道是網(wǎng)絡(luò)問題，查了一下也不是，那么就是mysql內(nèi)部問題。運(yùn)行 SHOW PROCESSLIST,發(fā)現(xiàn)很多Locked,難道就是這個(gè)問題。因?yàn)橐婚_始就把mysql設(shè)置成MyIasm的格式，因?yàn)檫@個(gè)速度比較快，不過這種格式是表鎖定的，看來就是這里。于是改了程序中某些頻繁更新的操作，做成cache,然后更新上去，現(xiàn)在速度好了。不過下來還是需要把數(shù)據(jù)庫(kù)換成innodb格式。
由此得出結(jié)論，MyIasm適合讀遠(yuǎn)大于寫的情況，比如CMS之類的，而讀寫頻繁的可能要innodb方式的。javaeye的robbin把數(shù)據(jù)庫(kù)從myiasm換成innodb據(jù)說性能好了太多，看來也是這個(gè)問題。
2 《輕松掌握MySQL數(shù)據(jù)庫(kù)鎖機(jī)制的相關(guān)原理》
MySQL 5.1支持對(duì)MyISAM和MEMORY表進(jìn)行表級(jí)鎖定，對(duì)BDB表進(jìn)行頁(yè)級(jí)鎖定，對(duì)InnoDB 表進(jìn)行行級(jí)鎖定。在許多情況下，可以根據(jù)培訓(xùn)猜測(cè)應(yīng)用程序使用哪類鎖定類型最好，但一般很難說出某個(gè)給出的鎖類型就比另一個(gè)好。一切取決于應(yīng)用程序，應(yīng)用程序的不同部分可能需要不同的鎖類型。為了確定是否想要使用行級(jí)鎖定的存儲(chǔ)引擎，應(yīng)看看應(yīng)用程序做什么并且混合使用什么樣的選擇和更新語(yǔ)句。例如，大多數(shù)Web應(yīng)用程序執(zhí)行許多選擇，而很少進(jìn)行刪除，只對(duì)關(guān)鍵字的值進(jìn)行更新，并且只插入少量具體的表。基本MySQL MyISAM設(shè)置已經(jīng)調(diào)節(jié)得很好。
在MySQL中對(duì)于使用表級(jí)鎖定的存儲(chǔ)引擎，表鎖定時(shí)不會(huì)死鎖的。這通?？偸?span style="color:#FF0000">在一個(gè)查詢開始時(shí)立即請(qǐng)求所有必要的鎖定并且總是以同樣的順序鎖定表來管理。（myisam 以有序的順序去所表，inodb 沒有順序的去所表，所以會(huì)產(chǎn)生死鎖）
對(duì)WRITE,MySQL使用的表鎖定方法原理如下：
◆ 如果在表上沒有鎖，在它上面放一個(gè)寫鎖。
◆否則，把鎖定請(qǐng)求放在寫鎖定隊(duì)列中。
對(duì)READ,MySQL使用的鎖定方法原理如下：
◆如果在表上沒有寫鎖定，把一個(gè)讀鎖定放在它上面。
◆否則，把鎖請(qǐng)求放在讀鎖定隊(duì)列中。
當(dāng)一個(gè)鎖定被釋放時(shí)，鎖定可被寫鎖定隊(duì)列中的線程得到，然后是讀鎖定隊(duì)列中的線程。
這意味著，如果你在一個(gè)表上有許多更新，SELECT語(yǔ)句將等待直到?jīng)]有更多的更新。
如果INSERT 語(yǔ)句不沖突，可以自由為MyISAM 表混合并行的INSERT 和SELECT 語(yǔ)句而不需要鎖定。
InnoDB 使用行鎖定，BDB 使用頁(yè)鎖定。對(duì)于這兩種存儲(chǔ)引擎，都可能存在死鎖。這是因?yàn)椋赟QL語(yǔ)句處理期間，InnoDB 自動(dòng)獲得行鎖定，BDB 獲得頁(yè)鎖定，而不是在事務(wù)啟動(dòng)時(shí)獲得。
也就是說對(duì)MyISAM表的讀操作，不會(huì)阻塞其他用戶對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求；對(duì) MyISAM表的寫操作，則會(huì)阻塞其他用戶對(duì)同一表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的！
優(yōu)先級(jí)：update ---> (刪除與select、insert 優(yōu)先級(jí)相同)
當(dāng)表為 myisam 時(shí) 頻繁插入的數(shù)據(jù)會(huì)出現(xiàn) table lock 這就是鎖表
行級(jí)鎖定的優(yōu)點(diǎn)：
· 當(dāng)在許多線程中訪問不同的行時(shí)只存在少量鎖定沖突。
· 回滾時(shí)只有少量的更改。
· 可以長(zhǎng)時(shí)間鎖定單一的行。
行級(jí)鎖定的缺點(diǎn)：
· 比頁(yè)級(jí)或表級(jí)鎖定占用更多的內(nèi)存。
· 當(dāng)在表的大部分中使用時(shí)，比頁(yè)級(jí)或表級(jí)鎖定速度慢，因?yàn)槟惚仨毇@取更多的鎖。
· 如果你在大部分?jǐn)?shù)據(jù)上經(jīng)常進(jìn)行 GROUP BY 操作或者必須經(jīng)常掃描整個(gè)表，比其它鎖定明顯慢很多。
· 用高級(jí)別鎖定，通過支持不同的類型鎖定，你也可以很容易地調(diào)節(jié)應(yīng)用程序，因?yàn)槠滏i成本小于行級(jí)鎖定。
在以下情況下，表鎖定優(yōu)先于頁(yè)級(jí)或行級(jí)鎖定：
· 表的大部分語(yǔ)句用于讀取。
· 對(duì)嚴(yán)格的關(guān)鍵字進(jìn)行讀取和更新，你可以更新或刪除可以用單一的讀取的關(guān)鍵字來提取的一行：
UPDATE tbl_name SET column = value WHERE unique_key_col = key_value ;
DELETE FROM tbl_name WHERE unique_key_col = key_value ;
· SELECT 結(jié)合并行的INSERT 語(yǔ)句，并且只有很少的UPDATE或 DELETE 語(yǔ)句。
· 在整個(gè)表上有許多掃描或 GROUP BY 操作，沒有任何寫操作。
不同于行級(jí)或頁(yè)級(jí)鎖定的選項(xiàng)：
· 版本（例如，為并行的插入在MySQL中使用的技術(shù)），其中可以一個(gè)寫操作，同時(shí)有許多讀取操作。這明數(shù)據(jù)庫(kù)或表支持?jǐn)?shù)據(jù)依賴的不同視圖，取決于訪問何時(shí)開始。其它共同的術(shù)語(yǔ)是"時(shí)間跟蹤"、"寫復(fù)制"或者"按需復(fù)制".
· 按需復(fù)制在許多情況下優(yōu)先于頁(yè)級(jí)或行級(jí)鎖定。然而，在最壞的情況下，它可能比使用常規(guī)鎖定使用多的內(nèi)存。
· 除了行級(jí)鎖定外，你可以使用應(yīng)用程序級(jí)鎖定，例如在MySQL中使用GET_LOCK（）和RELEASE_LOCK（）。這些是建議性鎖定，它們只能在運(yùn)行良好的應(yīng)用程序中工作。
為達(dá)到最高鎖定速度，除InnoDB 和BDB 之外，對(duì)所有存儲(chǔ)引擎，MySQL使用表鎖定（而不是頁(yè)、行或者列鎖定）。對(duì)于InnoDB 和BDB 表，如果你用LOCK TABLES顯式鎖定表，MySQL只使用表鎖定；如果你不使用LOCK TABLES,因?yàn)?InnoDB 使用自動(dòng)行級(jí)鎖定而BDB 使用頁(yè)級(jí)鎖定來保證事務(wù)隔離。
但是對(duì)于大表，對(duì)于大多數(shù)應(yīng)用程序，表鎖定比行鎖定更好，但存在部分缺陷。表鎖定使許多線程同時(shí)從一個(gè)表中進(jìn)行讀取操作，但如果一個(gè)線程想要對(duì)表進(jìn)行寫操作，它必須首先獲得獨(dú)占訪問。更新期間，所有其它想要訪問該表的線程必須等待直到更新完成。
表更新通常情況認(rèn)為比表檢索更重要，因此給予它們更高的優(yōu)先級(jí)。這應(yīng)確保更新一個(gè)表的活動(dòng)不能"餓死",即使該表上有很繁重的SELECT 活動(dòng)。
表鎖定在這種情況下會(huì)造成問題，例如當(dāng)線程正等待，因?yàn)橛脖P已滿并且在線程可以處理之前必須有空閑空間。在這種情況下，所有想要訪問出現(xiàn)問題的表的線程也被設(shè)置成等待狀態(tài)，直到有更多的硬盤空間可用。
表鎖定在下面的情況下也存在問題：
· 一個(gè)客戶發(fā)出長(zhǎng)時(shí)間運(yùn)行的查詢。
· 然后，另一個(gè)客戶對(duì)同一個(gè)表進(jìn)行更新。該客戶必須等待直到SELECT完成。
· 另一個(gè)客戶對(duì)同一個(gè)表上發(fā)出了另一個(gè) SELECT 語(yǔ)句。因?yàn)閁PDATE比 SELECT 優(yōu)先級(jí)高，該SELECT 語(yǔ)句等待UPDATE完成，并且等待第1個(gè) SELECT 完成。
下面描述了一些方法來避免或減少表鎖定造成的競(jìng)爭(zhēng)：
· 試圖使 SELECT 語(yǔ)句運(yùn)行得更快?？赡鼙仨殑?chuàng)建一些摘要（summary）表做到這點(diǎn)。
· 用--low-priority-updates啟動(dòng)mysqld.這將給所有更新（修改）一個(gè)表的語(yǔ)句以比SELECT語(yǔ)句低的優(yōu)先級(jí)。在這種情況下，在先前情形的第2個(gè)SELECT語(yǔ)句將在UPDATE語(yǔ)句前執(zhí)行，而不需要等候第1個(gè) SELECT 完成。
· 可以使用SET_UPDATES=1語(yǔ)句指定具體連接中的所有更新應(yīng)使用低優(yōu)先級(jí)。
· 可以用LOW_PRIORITY屬性給與一個(gè)特定的INSERT、UPDATE或DELETE語(yǔ)句較低優(yōu)先級(jí)。
· 可以用HIGH_PRIORITY屬性給與一個(gè)特定的SELECT語(yǔ)句較高優(yōu)先級(jí)。
· 為max_write_lock_count系統(tǒng)變量指定一個(gè)低值來啟動(dòng)mysqld來強(qiáng)制MySQL在具體數(shù)量的插入完成后臨時(shí)提高所有等待一個(gè)表的SELECT 語(yǔ)句的優(yōu)先級(jí)。這樣允許在一定數(shù)量的WRITE鎖定后給出READ鎖定。
· 如果你有關(guān)于INSERT結(jié)合SELECT的問題，切換到使用新的MyISAM表，因?yàn)樗鼈冎С植l(fā)的SELECT和INSERT.
· 如果你對(duì)同一個(gè)表混合插入和刪除，INSERT DELAYED將會(huì)有很大的幫助。
· 如果你對(duì)同一個(gè)表混合使用 SELECT 和DELETE 語(yǔ)句出現(xiàn)問題，DELETE 的LIMIT 選項(xiàng)可以有所幫助。
· 對(duì) SELECT 語(yǔ)句使用SQL_BUFFER_RESULT可以幫助使表鎖定時(shí)間變短。
· 可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊(duì)列。在這種情況下，寫鎖定和讀鎖定將具有相同的優(yōu)先級(jí)，對(duì)一些應(yīng)用程序會(huì)有幫助。
這里是一些MySQL中表鎖定相關(guān)的技巧：
· 如果不混合更新與需要在同一個(gè)表中檢查許多行的選擇，可以進(jìn)行并行操作。
· 可以使用 LOCK TABLES 來提高速度，因?yàn)樵谝粋€(gè)鎖定中進(jìn)行許多更新比沒有鎖定的更新要快得多。將表中的內(nèi)容切分為幾個(gè)表也可以有所幫助。
· 如果在MySQL中表鎖定時(shí)遇到速度問題，可以將表轉(zhuǎn)換為 InnoDB 或BDB 表來提高性能
轉(zhuǎn)自：http://hi.baidu.com/lqk12/item/0271953f53135323b3c0c5ee
聯(lián)系客服
