MySQL（三）

本文為側重MySQL優化優化部分。關於函式、儲存過程、檢視、觸發器、索引結構等知識點並未涉及，希望對你有幫助！！！

---

一、MySQL的架構介紹

1.1、MySQL簡介

1、概述

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB公司開發，目前屬於Oracle公司

MySQL是一種關聯資料庫管理系統，將資料儲存在不同的表中，而不是將所有的資料放在一個大倉庫中，這樣就增加了速度並且提高了靈活性

MySQL是開源的，所以你不需要支付額外的費用

MySQL支援大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。

MySQL使用表準的SQL資料語言形式

MySQL可以允許於多個系統上，並且支援多種語言。這些變成語言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等

MySQL對PHP有很好的支援，

MySQL支援大型資料庫，支援5000萬條記錄的資料倉儲，32位系統表檔案最大可支援4GB，64位系統支援最大的表檔案為8TB

MySQL是可以定製的，採用了GBL協議，你可以修改原始碼來開發自己的MySQL系統

2、高階MySQL

• MySQL核心
• MySQL優化工程師
• MySQL伺服器的優化
• 各種引數常量設定
• 查詢語句優化
• 主從複製
• 軟硬體升級
• 容災備份
• SQL程式設計

完整的MySQL優化需要很深的功底，大公司有專門的DBA完成上述任務

1.2、MySQL配置檔案

1、日誌檔案

MySQL中有六種日誌檔案，分別是

• 事務日誌：事務日誌主要用來保障RDBMS的ACID，磁碟隨機IO轉換為順序IO，儘可能降低當機造成的記憶體中的資料丟失
  • 重做日誌（redo log）：對於事務日誌中已正常提交但未同步到持久化儲存上時，則會記錄到redo log中。遇到當機後重啟MySQL服務時，會重新根據該日誌執行一遍事務，然後讓資料儲存到磁碟上，從而保證資料一致性。
  • 回滾日誌（undo log）：對於事務日誌中未正常提交的事務，則會記錄到undo log中，因為事務未正確執行完，因此必須回滾，從而保證資料一致性（類比理解回滾操作）。
• 二進位制日誌（bin log）： 記錄所有更改資料的語句。在主從複製中，從庫利用主庫上的bin log進行重播，實現主從同步，用於資料庫的基於時間點的還原。
• 錯誤日誌（error log）：記錄著 MySQL的啟動和停止，以及伺服器在執行過程中發生的錯誤及警告相關資訊。當資料庫意外當機或發生其他錯誤時，我們應該去排查錯誤日誌。
• 一般查詢日誌（general log）：開啟查詢日誌功能，會記錄所有的查詢操作，這在繁忙的資料庫應用中會增加額外的IO開銷，磁碟空間增長也會很快，不建議開啟。通常用於DBA除錯優化時臨時開啟。
• 慢查詢日誌（slow query log）：在所有的查詢中，有一部分查詢執行時所需要的時候如果超過了我們設定的long_query_time的值，那麼就會記錄到該日誌中。慢查詢一般都會開啟，用於DBA進行效能優化。
• 中繼日誌（relay log）：用於主從複製架構中的從伺服器上，從伺服器的 slave 程式從主伺服器處獲取二進位制日誌的內容並寫入中繼日誌，然後由 IO 程式讀取並執行中繼日誌中的語句。

2、資料檔案

資料庫檔案

• Window下，一般在安裝資料庫時，會在my.ini檔案中進行配置（Linux下叫my.cnf檔案）。在指定的data目錄下，能夠檢視到我們的資料庫

frm檔案

用於存放資料表的結構

MYD檔案、MYI檔案

• MYD存放表中的資料

• MYI存放表中的索引

ibd檔案

• 存放表的資料和索引

補充：

這裡的檔案，分別來自MySQL的兩個不同的儲存引擎InnoDB和MyISAM，這兩個儲存引擎都使用.frm檔案來儲存表結構。如果表中有資料，MyISAM儲存引擎則使用MYD檔案來儲存表中的資料，使用MYI檔案來儲存表中的索引；而InnoDB儲存引擎則只使用一個ibd檔案來儲存表的資料和表的索引。

不過，在MySQL8開始刪除了原來的frm檔案，並採用 Serialized Dictionary Information (SDI)，它是MySQL8.0重新設計資料詞典後引入的新產物，並開始已經統一使用InnoDB儲存引擎來儲存表的後設資料資訊。SDI資訊源記錄儲存在ibd檔案中。

如何可以檢視錶結構資訊，官方提供了一個工具叫做ibd2sdi，在安裝目錄下可以找到，可以離線的將ibd檔案中的冗餘儲存的sdi資訊提取出來，並以json的格式輸出到終端。

1.3、MySQL邏輯架構介紹

和其它資料庫相比，MySQL 有點與眾不同，它的架構可以在多種不同場景中應用併發揮良好作用。主要體現在儲存引擎的架構上，外掛式的儲存引擎架構將查詢處理和其它的系統任務以及資料的儲存提取相分離。這種架構可 以根據業務的需求和實際需要選擇合適的儲存引擎。

這裡可以類比於MVC三層架構，每一層各司其職，職能單一，便於管理。

1、連線層

最上層是一些客戶端和連線服務，包含本地 sock 通訊和大多數基於客戶端/服務端工具實現的類似於 TCP/IP 的通訊。主要完成一些類似於連線處理、授權認證、及相關的安全方案。在該層上引入了執行緒池的概念，為通過認證 安全接入的客戶端提供執行緒。同樣在該層上可以實現基於 SSL 的安全連結。伺服器也會為安全接入的每個客戶端驗 證它所具有的操作許可權。

2、服務層

服務層模組	模組作用
Management Serveices & Utilities	系統管理和控制工具
SQL Interface	SQL 介面。接受使用者的SQL命令，並且返回使用者需要查詢的結果。比如 select from 就是呼叫 SQL Interface
Parser	解析器。 SQL 命令傳遞到解析器的時候會被解析器驗證和解析
Optimizer	查詢優化器。 SQL 語句在查詢之前會使用查詢優化器對查詢進行優化，比如有 where 條件時，優化器來決定先投影還是先過濾。
Cache 和 Buffer	查詢快取。如果查詢快取有命中的查詢結果，查詢語句就可以直接去查詢快取中取 資料。這個快取機制是由一系列小快取組成的。比如表快取，記錄快取，key 快取， 許可權快取等

3、引擎層

儲存引擎層，儲存引擎真正的負責了 MySQL 中資料的儲存和提取，伺服器通過 API 與儲存引擎進行通訊。不同 的儲存引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。

4、儲存層

資料儲存層，主要是將資料儲存在執行於裸裝置的檔案系統之上，並完成與儲存引擎的互動。

1.4、MySQL儲存引擎

1、檢視我們的資料庫提供了哪些儲存引擎：

show engines;

2、檢視我們的資料庫預設使用的儲存引擎：

show variables like ‘%storage_engine’;

注：

本人使用的MySQL版本為5.7，其提供了InnoDB、MRG_MYISAM、MEMORY、BLACKHOLE、MyISAM、CSV、ARCHIVE、PERFORMANCE_SCHEMA、FEDERATED這幾個儲存引擎。其中InnoDB為預設的儲存引擎。

3、常見的InnoDB和MyISAM儲存引擎的對比

對比項	MyISAM	InnoDB
主外來鍵	不支援	支援
事務	不支援	支援
行表鎖	表鎖，即使操作一條記錄也會鎖主整個表，不適合高併發的操作	行鎖，操作時只鎖住某一行，不對其它行有影響，適合高併發的操作
快取	只快取索引，不快取真實資料	不僅快取索引，還要快取真實資料，對記憶體要求較高，而且記憶體大小對效能有決定性的影響
表空間	小	大
關注點	效能	事務
預設安裝	安裝	安裝

---

二、索引優化分析

2.1、查詢效能下降原因

原因

1. 查詢語句寫的太差勁
2. 索引失效
3. 關聯了太多的表（設計缺陷或不得已的需求）
4. 伺服器調優及各個引數設定（緩衝、執行緒數等）

新增單值索引和複合索引

SELECT * FROM t_user WHERE avatar = '' AND type = ''


-- 對於t_user表的查詢，我們經常使用avatar欄位和type欄位進行查詢，就可以對該欄位新增相應的索引

-- 單值索引
CREATE INDEX idx_user_avatar ON t_user(avatar);
CREATE INDEX idx_user_type ON t_user(type);

-- 複合索引
CREATE INDEX idx_user_avatarType ON t_user(avatar,type);

2.2、常見通用的join查詢

1、7種查詢

建表語句

-- 表1
CREATE TABLE `tbl_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) ,
KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

-- 表2
CREATE TABLE `tbl_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`locAdd` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;



INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD', 11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR', 12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK', 13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS', 14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD', 15);
 
INSERT INTO tbl_emp(NAME, deptId) VALUES('z3', 1);
INSERT INTO tbl_emp(NAME, deptId) VALUES('z4', 1);
INSERT INTO tbl_emp(NAME, deptId) VALUES('z5', 1);
 
INSERT INTO tbl_emp(NAME, deptId) VALUES('w5', 2);
INSERT INTO tbl_emp(NAME, deptId) VALUES('w6', 2);
 
INSERT INTO tbl_emp(NAME, deptId) VALUES('s7', 3);
INSERT INTO tbl_emp(NAME, deptId) VALUES('s8', 4);
INSERT INTO tbl_emp(NAME, deptId) VALUES('s9', 51);

查詢SQL

-- 左連線
select * from tbl_emp e left join tbl_dept d on e.deptid = d.id


-- 右連線
select * from tbl_emp e right join tbl_dept d on e.deptid = d.id



-- 內連線
select * from tbl_emp e inner join tbl_dept d on e.deptid = d.id


-- 左邊獨有
select * from tbl_emp e left join tbl_dept d on e.deptid = d.id
where d.id is  null


-- 右邊獨有
select * from tbl_emp e right join tbl_dept d on e.deptid = d.id
where e.deptid is null



-- 全連線
select * from tbl_emp e left join tbl_dept d on e.deptid = d.id
union
select * from tbl_emp e right join tbl_dept d on e.deptid = d.id



-- 兩邊獨有
select * from tbl_emp e  left join tbl_dept d on e.deptid = d.id
where d.id is null
union
select * from tbl_emp e right join tbl_dept d on e.deptid = d.id
where e.deptid is null

2、SQL執行時的順序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT 
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

2.3、索引簡介

MySQL官方對多因的定義為：索引（Index）是幫助MySQL高效獲取資料的資料結構。

1、是什麼

可以得到索引的本質：索引是資料結構

可以理解為一種排好序的快速查詢資料機構

在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料。這樣就可以在這些資料結構上實現高階的查詢演算法。這種資料結構就是索引

一般來說，索引本身也很大，不可能全在記憶體中。因此索引往往以索引檔案的形式儲存在磁碟上（MyISAM的.myi檔案和InnoDB的.idb檔案）

2、優缺點

優點

• 提高資料檢索的效率，降低資料庫的IO成本
• 通過索引列對資料進行排序，降低了資料排序的成本，降低了CPU的消耗

缺點

• 索引也是一張表，即索引列需要佔用額外的儲存空間
• 雖然提高了表的查詢速度，但是降低了更新速度。因為在insert、update、delete時，除了要更新表的資訊變更，還需要儲存索引檔案中索引資訊的變更

3、MySQL索引分類

單值索引：即一個索引只包含一列，一個表可以有多個單列索引

唯一索引：索引列的值必須唯一，但允許有NULL

複合索引：即一個索引列包含多個列

建立語法：

-- 建立
CREATER [UNIQUE] INDEX indexName ON tableName(columnname(length));
ALTER tableName ADD [UNIQUE] INDEX tableName ON columnname(length);

ALTER tableName ADD PRIMARY KEY (column_list); -- 主鍵索引
ALTER tableName ADD UNIQUE index_name(column_list); -- 唯一索引
ALTER tableName ADD INDEX index_name(column_list); -- 普通索引
ALTER tableName ADD FULLTEXT index)name(column_list);-- 全文索引

-- 刪除
DROP INDEX indexName ON tableName


-- 檢視
SHOW INDEX FROM tableName;

4、MySQL索引結構

B+樹

5、哪些情況需要建立索引

1. 主鍵自動建立唯一索引
2. 頻繁作為查詢條件的欄位應該建立索引
3. 查詢中與其他表關聯的欄位，外來鍵關係建立索引
4. 查詢中排序的欄位，需要建立索引（排序欄位若通過索引去訪問將大大提高排序速度）
5. 查詢中統計或者分組欄位，需要建立索引

6、哪些情況不需要建立索引

1. where條件裡面用不到的欄位不建立索引
2. 表記錄太少不建立索引
3. 經常增刪改的表欄位不建立索引
4. 儲局重複且分佈平均的表欄位，因此應該只為最經常查詢和最經常排序的資料列建立索引（如果某個資料列包含許多重複的內容，為它建立索引就沒有太大的效果）

2.4、效能分析

1、MySQL Query Optimizer

• MySQL中有專門負責優化SELECT語句的優化器模組，主要功能：通過計算分析系統中收集到的統計資訊，為客戶端請求的Query提供它認為最優的執行計劃（它認為最優的資料檢索方式，但不見得是DBA認為是最優的，這部分最耗費時間）
• 當客戶端向MySQL請求一條Query，命令解析器模組完成請求分類，區別出是SELECT並轉發給MySQL Query Optimize時，MySQL Query Optimize首先會對整條Query進行優化，處理掉一些常見表示式的預算，直接換算成常量值。並對Query中的查詢條件進行簡化和轉換，如去掉一些無用或顯而易見的條件、結構調整等。然後分析Query中的Hint資訊（如果有），看顯示Hint資訊是否可以完全確定該Query的執行計劃。如果沒有Hint或Hint資訊還不足以完全確定執行計劃，則會讀取所涉及物件的統計資訊，根據Query進行寫相應的計算分析，然後再得出最後的執行計劃。

2、MySQL常見瓶頸

• CPU：CPU在飽和的時候一般發生資料裝入記憶體或從磁碟上讀取資料時候
• IO：磁碟I/O瓶頸發生在裝入資料遠大於記憶體容量的時候
• 伺服器硬體的效能瓶頸：top、free、iostat和vmstat來檢視系統的效能狀態

3、Explain

使用Explain關鍵字對我們的SQL進行分析

具體細節參考：

Explain的簡單使用

---

三、查詢擷取分析

3.1、查詢優化

1、永遠小表驅動大表

IN和EXISTS的區別

• in：查詢出來的資料中存在的，先進行in後面的條件查詢，再執行select後面的資料查詢
• exists：查詢資料重存在的。先執行select後面的資料查詢，再選擇出符合要求的資料。將主查詢的資料，放到子查詢中做條件驗證，根據驗證結果顯示最後查詢出來的資料。

select * from A where id in (select id from B)

-- 等價於
1、select id from B
2、select * from A where A.id = B.id

我們可以把該SQL想象成一個雙層巢狀的for迴圈，外層迴圈是B，每次外層迴圈代表一次磁碟IO。我們要做的就是儘可能地減少外層迴圈，即此時我們就希望B表越小越好，那麼就是小表驅動達標。

select * from A where exists (select 1 from B where B.id = A.id)

-- 等價於
1、select * from A
2、select * from B where B.id = A.id

同理，我們依然把他們想象成一個雙層巢狀地for迴圈。此時由於in和exists的機制的不同，我們此時應該把A想象成磁碟IO，即A越小越好。

即：我們在具體選擇in還是exists時是十分靈活的，但是唯一不變的是明確誰是大表，誰是小表，一定要大表驅動小表。

2、Order By關鍵字優化

方法

Order By子句，儘量使用Index索引的方式排序，避免使用FileSort方式排序

• MySQL支援兩種排序索引排序（Index）和檔案內排序（FileSort），Index效率高
• 滿足條件會使用Index排序：Order By語句使用了索引最左前列；使用where子句與Order By子句條件列組合滿足索引最左前列

補充：

索引就是一種排好序的快速查詢資料機構，既然是已經排好序那我們直接拿來使用就好了。如果出現了File Sort代表著，檔案還需要自己額外再來一次排序，耗費效能。

如果排序欄位沒有在索引列上，就會有兩種演算法

• 雙路排序：在MySQL4.1之前，使用的是雙路排序，即對磁碟進行兩次掃描才能得到最終的資料。第一次掃描得到表的指標和Order By列，對它們進行排序；然後掃描已經排好序的列表，按照列表中的值重新從列表中讀取對應的資料進行輸出
• 單路排序：從磁碟讀取查詢需要的所有列，按照Order By列在buffer中對它們進行排序，然後掃描排序後的列表進行輸出。該方式的效率會更快一些，避免了二次讀取資料。並且把隨機IO變成了順序IO，但是會佔用更多的空間（ buffer空間）

sort_buffer

在sort_buffer中，方法B比方法A要多佔用很多空間，因為方法B是把所有欄位都取出來，所有有可能取出的資料的總大小超過了sort_buffer的容量，導致每次只能取sort_buffer大小的資料，進行排序（建立tmp檔案，多路合併），排序完成過後再取出sort_buffer大小的資料進行排序，反覆。

由此可見原本的一次IO變成了隨資料量的變化而變化。為了防止這種現象的出現，我們可以適當的調節sort_buffer的大小，使我們取出的資料只需要一次IO就能完成

總結

Order By時切記不要使用select *，只需要取出我們想要的欄位就可以了

• 當查詢的欄位大小總和小於max_length_for_sort_data而且排序欄位不是text或者blob型別時，會用改進後的演算法——單路排序，否則就會使用雙路排序（兩次磁碟IO耗費效能）
• 兩種演算法的資料都有可能會超過sort_buffer的容量，超出之後，會建立tmp檔案進行合併排序，導致多次IO，但是單路排序演算法的風險會更大一下，所有要提高sort_buffer_size的 大小

3、Group By關鍵字優化

• Group By實質是先排序後分組，優化方式參考Order By的最佳左字首
• where高於having，能寫在where限定條件的就不要寫在having的限定條件中

3.2、慢查詢日誌

1、是什麼

• MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閾值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中

• long_query_time的預設值為10，即標識記錄執行時間超過10秒以上的SQL語句

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

2、怎麼用

預設情況下，MySQL資料庫沒有開啟慢查詢日誌，需要我們手動開啟並設定這個引數。

**當然，如果不是調優需要的話，一般不建議啟動該引數。**因為開啟慢查詢日誌或多或少會帶來一定的效能影響。慢查詢日誌支援將日誌記錄寫入檔案。

-- 查詢我們當前慢日誌的配置。OFF表禁用
show variables like 'slow_query%';

-- 查詢我們慢日誌的閾值時間
show variables like 'long_query_time';

-- 開啟我們的慢日誌查詢。
-- 該方式開啟僅僅是臨時生效，想要永久開啟，需要在my.ini下進行相關的配置
set global slow_query_log='ON'; 

注意：此處的閾值10秒，表示大於10秒的才會被記錄，剛好等於10秒的不會被記錄。

模擬的一條慢SQL的查詢：

3、日誌分析工具MySQL Dump

相關的引數說明：

• s：表示按照何種方式排序
• c：訪問次數
• l：鎖定時間
• r：返回記錄
• t：查詢時間
• al：平均鎖定時間
• ar：平均返回記錄數
• at：平均查詢時間
• t：返回前面多少條的資料
• g：後面搭配一個正則匹配模式，不區分大小寫

-- 注意：如下命令為Linux的命令。win下貌似需要安裝其他外掛，才能使用mysqldumpslow分析功能

-- 得到返回記錄集最多的10個SQL
mysqldumpslow -s r -t 10 /var/lib.mysql/mobian-slow.log

-- 得到訪問次數最多的10個SQL
mysqldumpslow -s c -t 10 /var/lib.mysql/mobian-slow.log

-- 得到按時間排序的前10條SQL裡面含有左連線的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib.mysql/mobian-slow.log

-- 另外建議在使用這些命令的時候結合|和more使用，否則容易出現因資訊太多的爆屏現象
mysqldumpslow -s r -t 10 /var/lib.mysql/mobian-slow.log|more

我的理解：

如果我們已經進行到了在用日誌分析工具分析這一步，說明這個專案很可能是已經上線並且發現了問題，此時我們希望通過日誌分析工具進行分析，解決對應的問題。我們也應該明白，專案都是部署在Linux上，那麼我們此處的程式碼練習也應該傾向於在Linux上練習。

3.3、Show Profile

是MySQL提供可以用來分析當前會話中語句執行的資源消耗情況。可以用於MySQL的調優測量

預設情況下，引數處於關閉狀態，並儲存最近15次的執行結果

1、開啟對應的功能

-- 如果為ON表示開啟了對應的分析功能。否則使用命令開啟
SHOW VARIABLES LIKE 'profiling'

2、執行SQL語句

執行我們希望分析的SQL語句

3、查詢分析結果

4、診斷分析具體id的SQL

-- 進查詢cpu和block io的資訊，查詢的id為上表中的1，即query_id = 3
-- show type... for query Query_id
show profile cpu,block io for query 3

type引數

• ALL：顯示所有的開銷資訊
• BLOCK IO：顯示塊IO相關的開銷
• CONTEXT SWITCHES：顯示上下文切換相關的開銷
• CPU：顯示CPU相關的開銷資訊
• IPC：顯示傳送和接收相關的開銷資訊
• MEMORY：顯示記憶體相關的開銷資訊
• PAGE FAULTS：顯示頁面錯誤相關的開銷資訊
• SOURCE：顯示和Source_function，Souce_file，Source_line相關的開銷資訊
• SWAPS：顯示交換次數相關的開銷資訊

診斷SQL資訊中，出現的常見的問題

出現如下的一些情況，說明該條SQL很危險，需要優化

• converting HEAP to MyISAM：查詢結果太大，記憶體不夠用，需要往磁碟空間上擠
• Create tmp table：建立臨時表（資料需要先拷貝到臨時表，然後再轉移，最後刪除臨時表，耗時）
• Copying to tmp table on disk：把記憶體中的臨時表複製到磁碟上
• locked：表被鎖定

建立臨時表問題示例：

---

四、MySQL鎖機制

4.1、概述

鎖是計算機協調多個程式或執行緒併發訪問某一資源的機制。

在資料庫中，除傳統的計算機資源（如CPU、RAM、I/O等）的爭用以外，資料也是一種供許多使用者共享的資源。如果保證資料併發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫併發訪問效能的一個重要因素。從這個角度來說，鎖對資料庫而言就顯得十分重要，也更加複雜。

分類

按照粒度來分：

• 行鎖
• 表鎖
• 頁鎖

按照操作來分：

• 讀鎖（共享鎖）
• 寫鎖（排他鎖）

4.2、三鎖

現在MySQL為了更好的順應時代的潮流，使用最多的儲存引擎是InnoDB，關於其鎖的更多細節，可以參考我之前的文章

淺談InnoDB儲存引擎下鎖的分類

1、表鎖（偏讀）

MyISAM儲存引擎的操作級別，開銷小，加鎖快無死鎖。鎖的粒度大，發生鎖衝突的概率最高，併發度最低。

查詢鎖情況

-- 這兩個狀態變數記錄了MySQL內部表級鎖定的情況
show status like 'table_lock%'

各引數說明：

• Table_locks_immediate：產生表級別鎖定的次數，表示可以立即獲取鎖的查詢次數，每立即獲取鎖時值+1
• Table_locks_waited：出現表級鎖定爭用而發生等待的次數（不能立即獲取鎖的次數，每等待依次鎖的值+1）。即值越大，說明表的鎖競爭越激烈。

2、行鎖（偏寫）

InnoDB儲存引擎的特點，開銷大，加鎖慢，會出現死鎖。鎖粒度最小，發生鎖衝突概率最低，併發度最高。

行鎖帶來的事務相關的知識點

• 事務的ACID性：原子性、一致性、隔離性、持久化
• 併發事務帶來的問題：髒讀、不可重複讀、幻讀
• 事務的隔離級別：讀未提交、讀已提交、可重複度、序列化

-- 查詢MySQL資料庫的預設隔離級別
show variables like 'tx_isolation%'

優化建議

• 儘可能讓所有資料檢索都通過索引完成，避免索引失效（資料庫欄位型別不匹配的條件）行鎖升級為表鎖
• 合理設計索引，儘量縮小鎖的範圍
• 儘可能減少檢索條件，避免間隙鎖
• 儘量控制事務大小，減少鎖定的資源量和時間長度
• 儘可能低階別事務隔離

補充：

間隙鎖：

當我們用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB會給出符合條件的已有資料記錄的索引項加鎖；對於值在條件範圍內但不存在的記錄，叫做”間隙（GAP）“

InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）

產生問題：

因為DQL語句執行過程種通過範圍查詢，他會鎖定整個範圍內所有的索引鍵值，即時這個鍵值並不存在。

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

查詢鎖情況

-- 記錄了InnoDB行鎖的鎖定情況
show status like 'innodb_row_lock%'

各引數說明：

• Innodb_row_lock_current_waits：當前正在等待鎖定的數量
• Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度（等待總時間）
• Innodb_row_lock_time_avg：每次等待所花費平均時間（等待平均時間）
• Innodb_row_lock_time_max：從系統啟動到現在等待最長的依次所花費的時間
• Innodb_row_lock_waits：系統啟動後到現在總共等待的次數（等待總次數）

3、頁鎖

開銷和加鎖時間介於表鎖和行鎖之間，會出現死鎖。鎖粒度介於表鎖和行做之間，併發度也是如此。

---

五、主從複製

5.1、複製的基本原理

1、原理

slave會從master讀取binlog日誌檔案來進行資料同步

複製步驟：

1. master將改變記錄到二進位制日誌檔案中（binary log）。這些記錄過程叫做二進位制日誌事件，binary log events
2. slave將master的binary log events拷貝到它的中繼日誌（relay log）中
3. slave重做中繼日誌中的事件，將改變應用到自己的資料庫中。MySQL複製是非同步的且序列化的

2、規則

• 每一個slave只有一個master伺服器
• 每一個slave只能有一個唯一的伺服器ID
• 每一個master可以用多個salve

3、問題

• 時延

5.2、一主一從常見配置

1、主從機器MySQL版本必須一致

2、修改配置檔案

主機

-- 1. 主伺服器唯一ID（必須）
server-id = 1

-- 2. 啟用二進位制日誌（必須）
log-bin = 本地路徑

-- 3.啟用錯誤日誌（可選）
log-err = 本地路徑

-- 4.根目錄（可選）
basedir = 本地路徑

-- 5.臨時目錄（可選）
tmpdir = 本地路徑

-- 6.資料目錄（可選）
datadir = 本地路徑

-- 7.主機讀寫都可以（可選）
read-only = 0 

-- 8.設定不要複製的資料庫（可選）
binlog-ignore-db=mysql 

-- 9.設定需要複製的資料庫（可選）
binlog-do-db = 

從機

-- 1、從伺服器id（必須）
servr-id = 2

-- 2、啟用日誌檔案（可選）
log-bin = 本地目錄

3、重啟服務

連線過程需要關閉防火牆，否則會出現其他問題。

4、在Win上新增賬戶並授權slave

-- 授權給192.168.67.67ip下的mobian使用者，其登入資料庫時的密碼為123456
grant replication slave on *.* TO 'mobian'@'192.168.67.67' identified by '123456';

-- 重新整理資源
flush privileges;

查詢master狀態，並完成記錄

-- 記錄下File和Position的值（每新建立一次連線，都需要重新記錄這兩個值）
show master status;

5、在從機上配置主機的資訊

-- 建立連線.File和Position分別是來自主機上的master狀態
change master to master_host='192.168.67.68',master_user='mobian',master_password='123456',master_log_file='File',master_log_pos='Position'

-- 啟動主從複製功能
start slave

檢視是否配置成功

-- 如果引數Slave_IO_Running和Slave_SQL_Running都是Yes，則說明配置成功
show slave status

6、建立資料庫，檢視效果