(轉)mysql鎖相關知識

標籤：

原文地址：http://www.cnblogs.com/RicCC/archive/2009/09/25/mysql.html

儲存引擎

Attribute	MyISAM	Heap	BDB	InnoDB
Transactions	No	No	Yes	Yes
Lock granularity	Table	Table	Page (8 KB)	Row
Storage	Split files	In-memory	Single file per table	Tablespace(s)
Isolation levels	None	None	Read committed	All
Portable format	Yes	N/A	No	Yes
Referential integrity	No	No	No	Yes
Primary key with data	No	No	Yes	Yes
MySQL caches data records	No	Yes	Yes	Yes
Availability	All versions	All versions	MySQL-Max	All Versions

To make it easier to follow the unique characteristics of each storage engine, I created this magic quadrant diagram:

Below are some examples of using the best storage engine for different tasks:
Search Engine - NDBCluster
Web stats logging - Flat file for the logging with an offline processing demon processing and writing all stats into InnoDB tables.
Financial Transactions - InnoDB
Session data - MyISAM or NDBCluster
Localized calculations - HEAP
Dictionary - MyISAM

MyISAM
設計為處理讀頻率遠大於寫頻率的情況，查詢效能非常好；不支援事務，沒有REDO、UNDO日誌；支援表鎖；
每個表使用獨立的檔案，索引和資料都存放在不同檔案中，檔案的儲存以file block（頁）的形式儲存。只緩衝索引，並不緩衝實際資料，每次讀取資料時要使用磁碟IO，索引在記憶體中以cache block形式組織，與file block對應。索引的緩衝使用LRU演算法管理，為了提高緩衝的利用率，支援將緩衝分成多個地區，例如分成Hot Area、Warm Area
key_buffer_size：設定緩衝總大小
key_buffer_block_size：設定cache block大小
key_cache_division_limit：以百分比的形式將整個緩衝區劃分為多個地區。系統預設為100，即只有Warm Area
key_cache_age_threshold：控制各地區中的何時被降級，值越小，越容易降級到下一級area中
表的掃描分為Sequential Scan和Radom Scan 2種方式，read_buffer_size設定sequential scan時使用的緩衝，read_rnd_buffer_size設定radom scan時使用的緩衝
InnoDB
設計用於高並發讀寫情況，支援行鎖（必須有索引支援）；支援事務安全性，具有REDO、UNDO日誌，具備故障恢複能力，其事務實現了SQL92的4個層級；支援外鍵，實現了資料庫的參考完整性特性；
Adaptive Hash Index：InnoDB自動檢測索引狀況，如果發現可以通過hash index提高效率，會在內部建立一個基於B-Tree的hash index，並根據B-Tree索引的變化自動調整。hash index並不基於整個B-Tree建立，只針對其中的某部分；並不會儲存到磁碟，僅建立在緩衝區中
InnoDB的資料檔案支援共用資料表空間和獨享資料表空間2種模式，資料和索引儲存在一起，支援資料和索引的緩衝。儲存結構從大到小依次為tablespace->segment->extent->Page，page預設為16KB，每個extent包含64個page，每個segment存放同一種資料，一般每個表存放於一個單獨的segment中

鎖機制
有表鎖（MyISAM）、頁鎖（BDB）、行鎖（InnoDB）三種

表鎖：
有4個隊列記錄鎖的使用方式：Current read-lock（當前讀鎖隊列）, Pending read-lock（等待讀鎖的隊列）, Current write-lock（當前寫鎖隊列）, Pending write-lock（等待寫鎖的隊列）
讀鎖、寫鎖：
a). Current write-lock中當前資源的寫鎖會阻塞讀鎖和寫鎖請求.
b). Pending write-lock中WRITE類型的寫鎖會阻塞除了READ_HIGH_PRIORITY類型外的所有讀鎖請 求；READ_HIGH_PRIORITY類型的讀鎖比WRITE類型的寫鎖優先順序高，因此它會阻塞Pending write-lock中所有的寫鎖請求；除了WRITE類型的寫鎖，Pending write-lock中其他類型的寫鎖優先順序比讀鎖低（提高查詢的回應時間）
c). Current write-lock中對資源的寫鎖類型為WRITE_ALLOW_WRITE時，允許除了WRITE_ONLY之外的所有讀鎖和寫鎖請求
MyISAM是MySQL官方開發的儲存引擎，完全使用MySQL自己的表鎖機制，其他幾種支援事務的儲存引擎都是讓MySQL將鎖處理交由儲存引擎自行 實現，他們在MySQL中僅持有WRITE_ALLOW_WRITE類型的鎖，至於鎖的定義、並發衝突控制等都由各儲存引擎處理
MyISAM表鎖最佳化提示：MyISAM表鎖讀寫互相阻塞，寫鎖優先順序高於讀鎖
a). 參數選項low_priority_updates設定寫鎖優先順序比讀鎖低，用於保證查詢響應速度
b). 參數選項concurrent_insert配置是否使用並發插入特性，可以實現並發的讀取和插入操作，配置值: 0:不允許並發插入; 1:資料檔案中不存在空閑空間的時候可以在檔案尾部進行並發插入; 2:不管資料檔案是否存在空閑空間，均允許在檔案尾部進行並發插入（插入操作將一直在檔案尾部進行，中間的空閑空間無法利用，適用於刪除操作很少的表）

InnoDB的行鎖：
不是MySQL實現的鎖機制，行鎖都由其他儲存引擎實現，這裡以InnoDB為例（不同儲存引擎實現機制也不一樣）
Oracle的行鎖是在物理塊的事務槽中記錄鎖資訊，而InnoDB是在索引索引值的起始、結束位置上記錄鎖資訊（間隙鎖），所以InnoDB的行鎖只是利用索引實現的一個範圍鎖，而利用索引可以定位到資料行
鎖類型以及排他性：

	共用鎖定（S）	獨佔鎖定（X）	意圖共用鎖（IS）	意向獨佔鎖定（IX）
共用鎖定（S）	相容	衝突	相容	衝突
獨佔鎖定（X）	衝突	衝突	衝突	衝突
意圖共用鎖（IS）	相容	衝突	相容	相容
意向獨佔鎖定（IX）	衝突	衝突	相容	相容

InnoDB行鎖潛在的問題：
a). 如果無法使用索引資訊，InnoDB將使用表鎖
b). 當索引不是確定到某一行資料時，InnoDB鎖定的是索引匹配到的整個範圍內的資料。例如使用索引定位到了10條記錄，而加上非索引的條件可以準確確定到一條記錄，這種情況下InnoDB仍然鎖定這10條記錄
交易隔離等級：
InnoDB實現了SQL92的4個隔離等級：Read UnCommited, Read Commited, Repeatable Read, Serializable
InnoDB有死結檢測機制，將發生死結的2個事務中較小（修改資料量比較少）的那個作為死結犧牲品。當然只限於InnoDB儲存引擎範圍之內，跨儲存引擎的死結只能通過死結逾時設定進行處理

索引
MySQL主要有4類索引：B-Tree索引、Hash索引、Fulltext索引、R-Tree索引

B-Tree索引: 通用索引類型
InnoDB中的B-Tree索引分為Cluster形式的Primary Key和Secondary Index，與SQL Server類似，Primary Key索引的分葉節點是實際資料檔案，按索引順序排列，Secondary Index的分葉節點只儲存Primary Key值。MyISAM的主鍵索引和非主鍵索引沒什麼區別，與InnoDB的Secondary Index類似，只是其分葉節點存放的不是PK值，而是直接定位到資料行的資訊

Hash索引:
將資料的索引索引值進行hash運算建立索引，查詢匹配時將查詢條件也做hash運算，比較hash值進行匹配。主要是Memory和NDB Cluster儲存引擎使用
Hash索引的查詢效率非常高，因為不需要像B-Tree一樣從根匹配到頁節點（《MySQL效能調優與架構設計》中說hash索引可以一次定位要尋找的記錄，這種說法可能存在問題，Hash索引的組織、hash值的匹配同樣需要資料結構和演算法的實現，不可能一次定位，設想hash索引以B-Tree方式組織，比B-Tree索引優秀的地方可能是其B-Tree資料量會小，即使這樣也可能意味著hash衝突的存在，其效率比B-Tree索引高的說法是有待驗證的，可能需要有不少前提條件）
缺點：只能進行等值匹配；hash索引是無序的，可能需要額外的排序操作；無法進行部分匹配，只能全索引匹配；遇到hash衝突後效率可能會比較低

Fulltext索引: 只有MyISAM的CHAR, VARCHAR, TEXT三種資料類型支援fulltext索引，主要用於最佳化效率比較低的like ‘%***%‘操作
MySQL的fulltext中文支援有待考察，fulltext索引的建立成本比較高

R-Tree索引: 用於空間資料檢索
MySQL有空間資料類型GEOMETRY（5.0.16之前只有MyISAM支援，之後BDB、InnoDB、NDB Cluster、Archieve等支援），只有MyISAM儲存引擎支援R-Tree索引

(轉)mysql鎖相關知識