鎖機制優化MySQL

markzy5201190發表於2012-09-20
優化MySql
MyISAM表鎖優化"建議"
其使用表鎖在鎖定過程比行鎖或頁鎖級別附加成本小,鎖定本身資源也最少,但是鎖定顆粒度較大,所以,造成鎖定資源的爭用也會比其他級別要多,導致併發處理能力很低;
為了改善併發能力,如下建議:
1.縮短鎖定時間,讓query執行時間儘量的短

*減少複雜Query,將複雜Query分拆幾個小的Query分佈進行
*建立高效index,提高檢索資料速度
*讓myisam表只存放必要資訊,控制欄位型別
*合適機會優化myisam表資料檔案

2.分離並行的操作,myisam是讀寫互相阻塞的表鎖,併發插入(Concurrent Insert)特性

* value=2,表資料檔案中間部分是否存在因為刪除資料而留下空閒空間,容許資料檔案尾進行併發插入
* value=1,表資料檔案中間不存在空閒空間,可以從檔案尾進行併發插入
* value=0,表資料檔案中間部分是否存在因為刪除資料留下空閒空間,不容許併發插入;

3.合理利用讀寫優先順序

myisam表鎖中預設寫優先順序大於讀優先順序,如果一個系統以讀為主,且要優先保證查詢效能的話,設定引數:
low_priority_updates=1,告訴mysql先處理讀請求;
如果資料被刪除可能性很小,對暫時性浪費空間不在乎,可以concurrent_insert=2(嘗試)
資料檔案空間有空域空間,在浪費空間時候,還會造成查詢時間需要讀取更多資料,所以刪除資料量不是很小的,還是建議concurrent_insert=1;

InnoDB行鎖優化"建議"
InnoDB使用行級鎖,鎖定機制帶來的效能損耗可能比表級鎖更高一些,併發能力要比Myisam要好,但是也需要我們合理利用innodb鎖機制，否則也會帶來其他想不到的問題：
如下建議:
*資料檢索通過index完成,避免InnoDB無法通過index加鎖而升級為表鎖；
*合理利用index,讓Innodb在index上加鎖儘可能準確，縮小鎖定範圍，避免造成因鎖定而影響其他Query執行;
*減少基於範圍資料檢索過濾條件,避免因間隙鎖帶來負面影響而鎖定不該鎖定記錄;
*控制事務大小,減少鎖定時間和資源；
*在業務環境許可下，儘量使用低階別事務隔離,減少MySQL因實現事務隔離級別帶來附加成本;

因InnoDB行級鎖和事務性,肯定會產生死鎖,減少死鎖小建議：
*類似業務模組中，儘可能按照相同訪問順序來訪問，防止產生死鎖；
*在同一個事務中，儘可能做到一次鎖定所有資源，減少死鎖產生概率；
*對非常容易產生死鎖業務部分，可以嘗試升級鎖定顆粒度,同過表鎖減少死鎖概率；

系統資源鎖定爭用情況查詢：
1.表鎖：
mysql>show status like '%table%';
Table_locks_immediate:表級鎖次數
Table_locks_waited:出現表級鎖定爭用而發生等待次數
--2個值是從系統啟動後開始記錄,每次出現對應事件數量+1，如果table_locks_waited值高,系統表級鎖定爭用現象嚴重,需要分析原因；
2.InnoDB 行級鎖
mysql> show status like '%innodb_row_lock%';
Innodb行級鎖,記錄鎖定等待次數，還記錄鎖定總時長，每次平均時長和最大時長,此外非累積當前正在等待鎖定數量,對各個狀態說明如下：
Innodb_row_lock_current_waits:當前正在等待鎖定數量
Innodb_row_lock_time:從系統啟動到現在鎖定總時間長度
Innodb_row_lock_time_avg:每次等待所花費平均時間
Innodb_row_lock_max:從系統啟動到現在等待最長一次所花費時間
Innodb_row_lock_waits:從系統啟動到現在總共等待次數
較重要Innodb_row_lock_time_avg\Innodb_row_lock_waits\Innodb_row_lock_time 排序依次：等待次數很高,每次等待時長也不小時候,分析原因;

Innodb提供5個系統狀態變數,豐富即使狀態資訊;
1) 建立Innodb Monitor 表來開啟InnoDB的monitor功能
mysql>create table innodb_monitor(a int) engine=Innodb;
2) mysql > show innodb status 檢視細節資訊

摘自網路

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26855487/viewspace-744444/，如需轉載，請註明出處，否則將追究法律責任。

相關文章