《高性能Mysql》重點總結（五）——慢查詢日志、show profile、mysql鎖以及主從複制

本篇将介紹慢查詢日志、show profile、mysql鎖以及主從複制。

一、慢查詢日志

1. 是什麼

MySQL的慢查詢日志是MySQL提供的一種日志記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運作時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。

具體指運作時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。long_query_time的預設值為10，意思是運作10秒以上的語句。

由他來檢視哪些SQL超出了我們的最大忍耐時間值，比如一條sql執行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，結合之前explain進行全面分析。

2. 怎麼用

預設情況下，MySQL資料庫沒有開啟慢查詢日志，需要我們手動來設定這個參數。（當然，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日志會或多或少帶來一定的性能影響。慢查詢日志支援将日志記錄寫入檔案。）

-- 檢視開啟情況
SHOW VARIABLES LIKE '%slow_query_log%';

-- 開啟（隻對目前資料庫生效，如果要永久生效，就必須修改配置檔案my.cnf）
set global slow_query_log=1;
           

二、show profile

1. 是什麼

mysql提供可以用來分析目前會話中語句執行的資源消耗情況。可以用于SQL的調優的測量，相比explain，show profile展示的資料更加詳盡。

2. 怎麼用

-- 檢視是否開啟
show variables like 'profiling';

-- 開啟功能，預設是關閉，使用前需要開啟
set profiling=1;

-- 檢視結果
show profiles;

-- 診斷SQL
show profile cpu,block io for query n;
           

三、Mysql鎖機制

1. 按照對資料操作的類型（讀\寫）分

讀鎖(共享鎖)：針對同一份資料，多個讀操作可以同時進行而不會互相影響。

寫鎖（排它鎖）：目前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

2. 按照對資料操作的粒度分

表鎖：

表鎖以典型的MyISAM引擎為例，加S鎖會阻塞整個表的寫，但不會阻塞讀。加X鎖會阻塞整個表的讀寫。

行鎖：

行鎖以InnoDB引擎為例，加S鎖會阻塞操作行的寫，但不會阻塞讀。加X鎖會阻塞操作行的讀寫。适合并發量大的場景。

頁鎖：

開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

3. 死鎖

死鎖：

事務T1封鎖了資料R1，事務T2封鎖了資料R2，同時事務T1請求封鎖資料R2，由于資料R2已被事務T2封鎖，因為事務T1隻能等待，事務T2請求封鎖資料R1，此時雙方陷入互相等待狀态，造成了

死鎖

。

解決：

一次封鎖法：要求每個事務必須一次性将所有要用到的資料加鎖，否則就不能執行。這種方式雖然可以有效防止死鎖的發生，但是增加了鎖的粒度，降低了系統的并發性。并且資料庫是不斷變化的，很難精确地确定每個事務所需的資料對象，為此隻能擴大封鎖範圍，将事務在執行過程中需要封鎖的資料對象全部加鎖，進一步降低了并發度。

順序封鎖法：順序封鎖法是預先對一個資料對象規定一個封鎖順序，所有事務都按這個順序實施封鎖。但實施難度大。

資料庫中不适合預防死鎖，更适合進行死鎖的診斷和解除。比如逾時法和事務等待圖法。

4. 活鎖

活鎖：

事務T1封鎖了資料R，事務T2請求封鎖資料R，事務T3也請求封鎖資料R，事務T4…Tn都請求封鎖資料R，當事務T1釋放鎖後，系統首先準許事務T3的請求，其它繼續等待；事務T3釋放鎖後，系統準許了事務T4…Tn的請求，有可能事務T2永遠在等待，這就是

活鎖

。

解決：

采用先來先服務的方式。當多個事務請求封鎖同一資料時，系統按請求的先後次序對事務進行排隊，優先準許請求隊列中的第一個請求。

5. GAP鎖

間隙鎖：行鎖可能造成間隙鎖。當我們用【範圍條件】而不是相等條件檢索資料，并請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對于鍵值在條件範圍内但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（GAP Lock）。因為Query執行過程中通過過範圍查找的話，他會鎖定整個範圍内所有的索引鍵值，即使這個鍵值并不存在。GAP鎖可以解決幻讀問題。

間隙鎖有一個比較緻命的弱點，就是當鎖定一個範圍鍵值之後，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值範圍内的任何資料。在某些場景下這可能會對性能造成很大的危害。

四、主從複制

1. MySQL主從複制過程分成三步

1 master将改變記錄到二進制日志（binary log）。這些記錄過程叫做二進制日志事件，binary log events；

2 slave将master的binary log events拷貝到它的中繼日志（relay log）；

3 slave重做中繼日志中的事件，将改變應用到自己的資料庫中。 MySQL複制是異步的且串行化的。

2. 主從複制的基本原則

1 每個slave隻有一個master。

2 每個slave隻能有一個唯一的伺服器ID。

3 每個master可以有多個salve。