讀書筆記–高效能MySQL

 

 

MySQL伺服器架構邏輯圖

 

 

共用鎖定 排它鎖

解決並發要使用鎖，分為共用鎖定（shared  lock）和排它鎖（exclusive  lock），也就是讀鎖（read  lock）和寫鎖（write  lock）。

讀鎖是共用的，多個使用者可以同時讀取同一資源而互不干擾；寫鎖是獨佔鎖定，阻塞其他的寫鎖和讀鎖，

 

 

鎖粒度

 

為了更好的實現並發，應該只鎖定需要修改的資料而不用鎖定所有的資源，鎖的粒度越小，同時間能進行的並發就越大。但是如果粒度太小，對鎖的管理（檢查鎖、獲得鎖、釋放鎖）就要消耗很多更多的資源，系統效能也會受到影響；而資料庫主要的任務是讀寫，不是去管理鎖，所以應該考慮折中的方式。鎖策略就是考慮鎖開銷和資料安全的平衡。

 

表鎖（table lock）

在整個表上加鎖，讀寫申請隊列中的寫鎖優先順序高於讀鎖，有寫操作時禁止其他讀和寫。

行級鎖（row locks）

可以支援最大的並發，也帶來最大的開銷。

 

 

事務

事務，一組原子性的sql操作，也叫一個工作單元。     

ACID  

Atomicity         原子性          不可分的工作單元，要麼全執行，要麼全失敗

Consistency     一致性          從一種一致性到另一種一致性，提交之前做的操作不會影響資料庫內容。

Isolation      隔離性          事務完成之後結果才可見，中間過程不可見

Durability    持久性          事務提交後的資料改變是永久性的

 

隔離等級

隔離等級分4級，定義了事務過程中哪些結果是可見的，哪些是不可見的。

Readuncommitted              讀取未提交內容

       所有事務都可以看到其他未提交的事務的執行結果，又稱為髒讀（dirty read）

Readcommitted            讀取提交內容

       事務開始時只能看到已提交的事務改變，一個事務從開始到提交前，資料改變對外不可見。又叫不可重複讀取（nonrepeated）

Repeatable              可重複讀

該事務執行過程中，有另外的事務提交，該事務中看不到變化。除非該事務結束後，重新查看，才能看到變化。

有一個例外是幻讀（phantom read），就是在該事務執行過程中，有其他事務新增記錄，那麼該事務會查到多一條記錄。InnoDB除外，根據搜尋引擎的實現不同，不一定會出現幻讀。

Serializable        可序列化，最進階的隔離等級，代價最高，效能最低。通過強制事務排序，解決幻讀問題。在每個讀的資料行上都加鎖，會導致大量的逾時和鎖競爭。

 

死結

InnoDB處理死結的機制是：復原擁有最少排他行級鎖的事務（一種最易復原事務的大致估算）。

 

交易記錄：

事務採取預寫式日誌（write ahead log）的方式。

先更新記憶體中的資料拷貝，然後將日誌寫入記錄檔，涉及順序IO的磁碟操作。等到某個時間再通過日誌同步資料，  從而替代直接將資料寫入磁碟需要的大量隨機IO操作。用兩次磁碟寫入操作把資料寫入磁碟。

 

 

二相鎖協議（two phase lock protocal）

Mysql的一個事務在任何時刻都可以獲得鎖，但是只有在提交或復原的時候，才會釋放鎖。

多版本並發控制（Multiversion concurrency control）

是通過儲存資料快照實現的。這意味著一個事務的執行個體不論運行多久，期間看到的資料都是一樣的。不同事務看到的一張表的資料可能是不同的。

Mvcc只工作在repeatable read 和 read committed交易隔離等級

 

交易隔離等級為repeatable read的實現方式：

 

為每一行添加兩個隱藏欄位，create_version版本號碼和delete _version版本號碼；每一個事務開始時都要產生一個遞增的系統版本號碼，通過對比版本實現。

Insert             記錄create_version為當前事務版本。

Update 建立一條新的行拷貝記錄，記錄create_version為當前事務版本號碼；同時標記舊記錄的delete_version為當前事務版本號碼。

Delete         標記舊記錄的delete_version為當前事務版本號碼。

 

Select         

查詢要求查看在事務開始之前建立的或者當前事務建立的

同時滿足  在該事務之後刪除的或者沒有被刪除過的

（create<=cur）&& ( delete==null ||delete >cur)

 

好處是大量的讀操作都不必加鎖，提高讀速度；

壞處是為每個行都記錄了多餘的資料，有更多的檢查操作和資料整理操作。

這個講得可以：http://blog.csdn.net/chen77716/article/details/6742128

 

 

查看錶狀態

showtable status like 't_sales_opportunity'

 

myisam的表修複  

checktable mytable

repairtable mytable