怎么理解并掌握MySQL

本篇內容主要講解“怎么理解并掌握MySQL”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“怎么理解并掌握MySQL”吧!

MySQL分為 server 層和存儲引擎

server層

• 連接器：管理連接權限驗證

• 查詢緩存：命中緩存直接換回查詢結果

• 分析器：分析語法

• 優(yōu)化器：生成執(zhí)行計劃，選擇索引

• 執(zhí)行器：操作索引返回結果

存儲引擎

存儲引擎負責數(shù)據(jù)的存儲和提?。黄浼軜嬍遣寮降?。innodb 在 mysql5.5.5 版本開始成為 mysql 默認存儲引擎。

各存儲引擎比對：

• InnoDB：支持事務，支持外鍵，InnoDB 是聚集索引，數(shù)據(jù)文件是和索引綁在一起的，必須要有主鍵，通過主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢到主鍵，然后再通過主鍵查詢到數(shù)據(jù)，不支持全文索引。

• MyISAM：不支持事物，不支持外鍵，MyISAM 是非聚集索引，數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨立的，查詢效率上 MyISAM 要高于 InnnDB ，因此做讀寫分離的時候一般選擇用 InnoDB 做主機，MyISAM 做從機

• Memory：有比較大的缺陷使用場景很少；文件數(shù)據(jù)都存儲在內存中，如果 mysqld 進程發(fā)生異常，重啟或關閉機器這些數(shù)據(jù)都會消失。

sql 的執(zhí)行過程

第一步客戶端連接上 mysql 數(shù)據(jù)庫的連接器，連接器獲取權限，維持管理連接；連接完成后如果你沒有后續(xù)的指令這個連接就會處于空閑狀態(tài)，如果太長時間不使用這個連接這個連接就會斷開，這個空閑時長默認是 8 小時，由 wait_timeout 參數(shù)控制。

第二步你往 mysql 數(shù)據(jù)庫發(fā)送了一條 sql ，這個時候查詢緩存開始工作，看看之前有沒有執(zhí)行過這個 sql ，如果有則直接返回緩存數(shù)據(jù)到客戶端，只要對表執(zhí)行過更新操作緩存都會失效，因此一些很少更新的數(shù)據(jù)表可考慮使用數(shù)據(jù)庫緩存，對頻繁更新的表使用緩存反而弊大于利。使用緩存的方法如以下 sql ，通過 SQL_CACHE 來指定：

select  SQL_CACHE * from table where xxx=xxx

第三步當未命中緩存的時候，分析器開始工作；分析器判斷你是 select 還是 update 還是 insert ，分析你的語法是否正確。

第四步優(yōu)化器根據(jù)你的表的索引和 sql 語句決定用哪個索引，決定 join 的順序。

第五步執(zhí)行器執(zhí)行 sql ，調用存儲引擎的接口，掃描遍歷表或者插入更新數(shù)據(jù)。

MySQL日志

MySQL日志介紹

mysql 有兩個重要日志—— redolog 和 binlog ，redolog 是獨屬于 innodb 的日志，binlog 則是屬于 server 層的日志。下面介紹這兩個日志有什么用：當我們更新數(shù)據(jù)庫數(shù)據(jù)的時候，這兩個日志文件也會被更新，記錄數(shù)據(jù)庫更新操作。

redolog 又稱作重做日志，用于記錄事務操作的變化，記錄的是數(shù)據(jù)修改之后的值，不管事務是否提交都會記錄下來。它在數(shù)據(jù)庫重啟恢復的時候被使用，innodb 利用這個日志恢復到數(shù)據(jù)庫宕機前的狀態(tài)，以此來保證數(shù)據(jù)的完整性。redolog 是物理日志，記錄的是某個表的數(shù)據(jù)做了哪些修改，redolog 是固定大小的，也就是說后面的日志會覆蓋前面的日志。

binlog 又稱作歸檔日志，它記錄了對 MySQL 數(shù)據(jù)庫執(zhí)行更改的所有操作，但是不包括 SELECT 和 SHOW 這類操作。binlog 是邏輯日志，記錄的是某個表執(zhí)行了哪些操作。binlog 是追加形式的寫入日志，后面的日志不會被前面的覆蓋。

數(shù)據(jù)更新過程

我們執(zhí)行一個更新操作是這樣的：讀取對應的數(shù)據(jù)到內存—>更新數(shù)據(jù)—>寫 redolog 日志—> redolog 狀態(tài)為 prepare —>寫 binlog 日志—>提交事務—> redolog 狀態(tài)為 commit ，數(shù)據(jù)正式寫入日志文件。我們發(fā)現(xiàn) redolog 的提交方式為“兩段式提交”，這樣做的目的是為了數(shù)據(jù)恢復的時候確保數(shù)據(jù)恢復的準確性，因為數(shù)據(jù)恢復是通過備份的 binlog 來完成的，所以要確保 redolog 要和 binlog 一致。

MySQL的MVCC

事務隔離級別在此略過，相信大部分小伙伴都知道相關的知識了，在這里單單只介紹 mysql 實現(xiàn)事務隔離的原理—— mvcc(多版本并發(fā)控制)。在學習 mvcc 之前我需要先介紹快照讀和當前讀。

快照讀和當前讀

快照讀就是一個 select 語句，形如：

select * from table

在 Repeatableread 事務隔離級別下，快照讀的特點是獲取當前數(shù)據(jù)庫的快照數(shù)據(jù)，對于所有未 commit 的數(shù)據(jù)都不可見，快照讀不會對數(shù)據(jù)上鎖。

當前讀是對所讀數(shù)據(jù)上悲觀鎖使其他當前讀無法操作數(shù)據(jù)。當前讀 sql 包括:

select ... lock in share modeselect ... for updateinsertupdatedelete

其中后面三個 sql 都是給數(shù)據(jù)庫上排他鎖（X鎖），而第一個 sql 是給數(shù)據(jù)庫上共享鎖（S鎖）。X 鎖是一旦某個當前讀到這個鎖，其他當前讀則沒有對這個事務讀寫的權利，其他當前讀會被阻塞住。而 S 鎖是當一個當前讀對某條數(shù)據(jù)上 S 鎖，其他當前讀可以對該數(shù)據(jù)也上 S 鎖但不能上 X 鎖，拿到 S 鎖的當前讀可以讀數(shù)據(jù)不能改數(shù)據(jù)。（關于數(shù)據(jù)庫悲觀鎖樂觀鎖并發(fā)章節(jié)會介紹）。

MVCC 原理

innodb 實現(xiàn)快照讀和當前讀悲觀鎖的技術就是 mvcc 。innodb 在插入一條數(shù)據(jù)的時候會在后面跟上兩個隱藏的列，這兩個列，一個保存了這個行的創(chuàng)建時系統(tǒng)版本號，一個保存的是行的刪除的系統(tǒng)版本號。每開始一個新的事務，系統(tǒng)版本號就會自動遞增，事務開始時刻的系統(tǒng)版本號會作為事務的 ID。innodb 更新一條數(shù)據(jù)是設置舊數(shù)據(jù)刪除版本號，然后插入一條新的數(shù)據(jù)并設置創(chuàng)建版本號，然后刪除舊的數(shù)據(jù)。那么怎么保證快照讀是讀取到未 commit 的數(shù)據(jù)呢，兩個條件：

• InnoDB 只查找創(chuàng)建版本早于當前事務版本的數(shù)據(jù)行，即，行的系統(tǒng)版本號小于或等于事務的系統(tǒng)版本號，這樣可以確保事務讀取的行，要么是在事務開始前已經存在的，要么是事務自身插入或者修改過的。

• 行的刪除版本，要么未定義，要么大于當前事務版本號。這樣可以確保事務讀取到的行，在事務開始之前未被刪除。只有符合上述兩個條件的紀錄，才能作為查詢結果返回。

而數(shù)據(jù)庫鎖也是通過比對版本號來決定是否阻塞某個事物。

MySQL索引

索引介紹

索引按數(shù)據(jù)結構分可分為哈希表，有序數(shù)組，搜索樹，跳表：

• 哈希表適用于只有等值查詢的場景

• 有序數(shù)組適用于有等值查詢和范圍查詢的場景，但有序數(shù)組索引的更新代價很大，所以最好用于靜態(tài)數(shù)據(jù)表

• 搜索樹的搜索效率穩(wěn)定，不會出現(xiàn)大幅波動，而且基于索引的順序掃描時，也可以利用雙向指針快速左右移動，效率非常高

• 跳表可以理解為優(yōu)化的哈希索引

innodb 使用了 B+ 樹索引模型，而且是多叉樹。雖然二叉樹是索引效率最高的，但是索引需要寫入磁盤，如果使用二叉樹磁盤 io 會變得很頻繁。在 innodb 索引中分為主鍵索引（聚簇索引）和非主鍵索引（二級索引）。主鍵索引保存了該行數(shù)據(jù)的全部信息，二級索引保存了該行數(shù)據(jù)的主鍵；所以使用二級索引的時候會先查出主鍵值，然后回表查詢出數(shù)據(jù)，而使用主鍵索引則不需要回表。

對二級索引而言可使用覆蓋索引來優(yōu)化 sql，看下面兩條 sql

select * from table where key=1;select id from table where key=1;

key 是一個二級索引，第一條 sql 是先查詢出 id ，然后根據(jù) id 回表查詢出真正的數(shù)據(jù)。而第二條查詢索引后直接返回數(shù)據(jù)不需要回表。第二條 sql 索引 key 覆蓋了我們的查詢需求，稱作覆蓋索引

普通索引和唯一索引

innoDB 是按數(shù)據(jù)頁來讀寫數(shù)據(jù)的，當要讀取一條數(shù)據(jù)的時候是先將本頁數(shù)據(jù)全部讀入內存，然后找到對應數(shù)據(jù)，而不是直接讀取，每頁數(shù)據(jù)的默認大小為 16KB。

當一個數(shù)據(jù)頁需要更新的時候，如果內存中有該數(shù)據(jù)頁就直接更新，如果沒有該數(shù)據(jù)頁則在不影響數(shù)據(jù)一致性的前提下將；更新操作先緩存到 change buffer 中，在下次查詢需要訪問這個數(shù)據(jù)頁的時候再寫入更新操作除了查詢會將 change buffer 寫入磁盤，后臺線程線程也會定期將 change buffer 寫入到磁盤中。對于唯一索引來說所有的更新操作都要先判斷這個操作是否會違反唯一性約束，因此唯一索引的更新無法使用 change buffer 而普通索引可以，唯一索引更新比普通索引更新多一個唯一性校驗的過程。

聯(lián)合索引

兩個或更多個列上的索引被稱作聯(lián)合索引（復合索引）。聯(lián)合索引可減少索引開銷，以聯(lián)合索引 (a,b,c) 為例，建立這樣的索引相當于建立了索引 a、ab、abc 三個索引—— Mysql 從左到右的使用索引中的字段，一個查詢可以只使用索引中的一部份，但只能是最左側部分，而且當最左側字段是常量引用時，索引就十分有效，這就是最左前綴原則。由最左前綴原則可知，組合索引是有順序的，那么哪個索引放在前面就比較有講究了。對于組合索引還有一個知識點——索引下推，假設有組合索引（a，b，c）有如下 sql:

selet * from table where a=xxx and b=xxx

這個 sql 會進行兩次篩選第一次查出 a=xxx 數(shù)據(jù) 再從 a=xxx 中查出 b=xxx 的數(shù)據(jù)。使用索引下推和不使用索引下推的區(qū)別在于不使用索引下推會先查出 a=xxx 數(shù)據(jù)的主鍵然后根據(jù)查詢出的主鍵回表查詢出全行數(shù)據(jù)，再在全行數(shù)據(jù)上查出 b=xxx 的數(shù)據(jù)；而索引下推的執(zhí)行過程是先查出 a=xxx 數(shù)據(jù)的主鍵，然后在這些主鍵上二次查詢 b=xxx 的主鍵，然后回表。

索引下推的特點：

• innodb 引擎的表，索引下推只能用于二級索引

• 索引下推一般可用于所查詢字段不全是聯(lián)合索引的字段，查詢條件為多條件查詢且查詢條件子句字段全是聯(lián)合索引。

優(yōu)化器與索引

在索引建立之后，一條語句可能會命中多個索引，這時，就會交由優(yōu)化器來選擇合適的索引。優(yōu)化器選擇索引的目的，是找到一個最優(yōu)的執(zhí)行方案，并用最小的代價去執(zhí)行語句。那么優(yōu)化器是怎么去確定索引的呢？優(yōu)化器會優(yōu)先選擇掃描行數(shù)最少的索引，同時還會結合是否使用臨時表、是否排序等因素進行綜合判斷。MySQL 在開始執(zhí)行 sql 之前，并不知道滿足這個條件的記錄有多少條，而只能根據(jù) mysql 的統(tǒng)計信息來估計，而統(tǒng)計信息是通過數(shù)據(jù)采樣得出來的。

其他索引知識點

有時候需要索引很長的字符列，這會讓索引變得很大很慢還占內存。通?？梢砸蚤_始的部分字符作為索引，這就是前綴索引。這樣可以大大節(jié)約索引空間，從而提高索引效率，但這樣也會降低索引的選擇性。

臟頁對數(shù)據(jù)庫的影響：

當內存數(shù)據(jù)頁和磁盤的數(shù)據(jù)不一致的時候我們稱這個內存頁為臟頁，內存數(shù)據(jù)寫入磁盤后數(shù)據(jù)一致，稱為干凈頁。當要讀入數(shù)據(jù)而數(shù)據(jù)庫沒有內存的時候，這個時候需要淘汰內存中的數(shù)據(jù)頁——干凈頁可以直接淘汰掉，而臟頁需要先刷入磁盤再淘汰。如果一個查詢要淘汰的臟頁太多會導致查詢的時間變長。為了減少臟頁對數(shù)據(jù)庫性能影響，innodb 會控制臟頁的比例和臟頁刷新時機。

MySQL語法分析及優(yōu)化

count(*)

count(*) 對 innodb 而言，它需要把數(shù)據(jù)從磁盤中讀取出來然后累計計數(shù)；而 MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，所以執(zhí)行 count(*) 會直接返回這個數(shù)，如果有 where 條件則和 innodb一樣。那么如何優(yōu)化 count(*) ？一個思路是使用緩存，但是需要注意雙寫一致的問題（雙寫一致性后文緩存章節(jié)會做介紹）。還可以專門設計一張表用以存儲 count(*)。

對于 count(主鍵 id )來說，InnoDB 引擎會遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。對于 count(1) 來說，InnoDB 引擎遍歷整張表，但不取值。server 層對于返回的每一行，放一個數(shù)字“1” 進去，判斷是不可能為空的，按行累加。單看這兩個用法的差別的話，你能對比出來，count(1) 執(zhí)行得要比 count(主鍵 id)快。因為從引擎 返回 id 會涉及到解析數(shù)據(jù)行，以及拷貝字段值的操作。對于 count(字段)來說：如果這個“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個字段，判斷不能為 null，按行累加；如果這個“字段”定義允許為 null，那么執(zhí)行的時候，判斷到有可能是 null，還要把值取出來再 判斷一下，不是 null 才累加。而對于 count(*) 來說，并不會把全部字段取出來，而是專門做了優(yōu)化，不取值，按行累加。所以排序效率：

count(*)=count(1)>count(id)>count(字段)

order by

Mysql 會給每個線程分配一塊內存用于做排序處理，稱為 sort_buffer ,一個包含排序的 sql 執(zhí)行過程為：申請排序內存 sort_buffer ,然后一條條查詢出整行數(shù)據(jù)，然后將需要的字段數(shù)據(jù)放入到排序內存中，染回對排序內存中的數(shù)據(jù)做一個快速排序，然后返回到客戶端。當數(shù)據(jù)量過大，排序內存盛不下的時候就會利用磁盤臨時文件來輔助排序。當我們排序內存盛不下數(shù)據(jù)的時候，mysql 會使用 rowid 排序來優(yōu)化。rowid 排序相對于全字段排序，不會把所有字段都放入 sort_buffer，所以在 sort buffer 中進行排序之后還得回表查詢。在少數(shù)情況下，可以使用聯(lián)合索引+索引覆蓋的方式來優(yōu)化 order by。

join

在了解 join 之前我們應該先了解驅動表這個概念——當兩表發(fā)生關聯(lián)的時候就會有驅動表和被驅動表之分，驅動表也叫外表（R 表），被驅動表也叫做內表（S 表）。一般我們將小表當做驅動表（指定了聯(lián)接條件時，滿足查詢條件的記錄行數(shù)少的表為「驅動表」,未指定聯(lián)接條件時，行數(shù)少的表為「驅動表」；MySQL 內部優(yōu)化器也是這么做的）。

假設有這樣一句 sql（xxx 為索引）:

select * from table1 left join tablet2 on table1.xxx=table2.xxx

這條語句執(zhí)行過程是先遍歷表 table1 ，然后根據(jù)從表 table1 中取出的每行數(shù)據(jù)中的 xxx 值，去表 table2 中查找滿足條件的 記錄。這個過程就跟我們寫程序時的嵌套查詢類似，并且能夠用上被驅動表的索引，這種查詢方式叫 NLJ 。當 xxx 不是索引的時候，再使用 NLJ 的話就會對 table2 做多次的全表掃描（每從 table1 取一條數(shù)據(jù)就全表掃描一次 table2），掃描數(shù)暴漲。這個時候 mysql 會采用另外一個查詢策略。Mysql 會先把 table1 的數(shù)據(jù)讀入到一個 join_buffer 的內存空間里面去，然后依次取出 table2 的每一行數(shù)據(jù)，跟 join_buffer 中的數(shù)據(jù)做對比，滿足 join 條件的作為結果集的一部分返回。

我們在使用 join 的時候,要遵循以下幾點：

• 小表驅動大表。

• 被驅動表走索引的情況下（走 NLJ 查詢方式）的時候才考慮用join

sql 的優(yōu)化

1） 在 mysql 中，如果對字段做了函數(shù)計算，就用不上索引了。

如以下 sql(data 為索引):

select *  from tradelog where month(data)=1;

優(yōu)化器對這樣的 sql 會放棄走搜索樹，因為它無法知道 data 的區(qū)間。

2）隱式的類型轉換會導致索引失效。

如以下 sql:

select * from table where xxx=110717;

其中 xxx 為 varchar 型，在 mysql 中，字符串和數(shù)字做比較的話，將字符串轉換成數(shù)字再進行比較，這里相當于使用了 CAST(xxx ASsigned) 導致無法走索引。

3）索引列參與了計算不會走索引

4）like %xxx 不會走索引，like xxx% 會走索引

5）在 where 子句中使用 or ，在 innodb 中不會走索引，而 MyISAM 會。

執(zhí)行計劃和慢查詢日志

執(zhí)行計劃

在查詢 sql 之前加上 explain 可查看該條 sql 的執(zhí)行計劃,如：

EXPLAIN SELECT * FROM table

這條 sql 會返回這樣一個表：

這個表便是 sql 的執(zhí)行計劃，我們可以通過分析這個執(zhí)行計劃來知道我們 sql 的運行情況?，F(xiàn)對各列進行解釋：

1）id：查詢中執(zhí)行 select 子句或操作表的順序。

2）select_type：查詢中每個 select 子句的類型（簡單 到復雜）包括：

• SIMPLE：查詢中不包含子查詢或者UNION；

• PRIMARY：查詢中包含復雜的子部分；

• SUBQUERY：在SELECT或WHERE列表中包含了子查詢，該子查詢被標記為SUBQUERY；

• DERIVED：衍生，在FROM列表中包含的子查詢被標記為DERIVED；

• UNION：若第二個SELECT出現(xiàn)在UNION之后，則被標記為UNION；

• UNION RESULT：從UNION表獲取結果的SELECT被標記為UNION RESULT；

3） type：表示 MySQL 在表中找到所需行的方式，又稱“訪問類型”，包括：

• ALL：Full Table Scan， MySQL 將遍歷全表以找到匹配的行；

• index：Full Index Scan，index 與 ALL 區(qū)別為 index 類型只遍歷索引樹；

• range：索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于 between < > 等查詢；

• ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進行的查找；

• eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描；

• onst 和 system：當 MySQL 對查詢某部分進行優(yōu)化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于 where 列表中，MySQL 就能將該查詢轉換為一個常量，system 是 const 類型的特例，當查詢的表只有一行的情況下， 使用 system；

• NULL：MySQL 在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引。

4）possible_keys：指出 MySQL 能使用哪個索引在表中找到行，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用。

5）key：顯示 MySQL 在查詢中實際使用的索引，若沒有使用索引，顯示為 NULL。

6）key_len：表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度。

7）ref：表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。

8）rows：表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。

9）Extra：其他重要信息 包括：

• Using index：該值表示相應的 select 操作中使用了覆蓋索引；

• Using where：MySQL 將用 where 子句來過濾結果集；

• Using temporary：表示 MySQL 需要使用臨時表來存儲結果集，常見于排序和分組查詢；

• Using filesort：MySQL 中無法利用索引完成的排序操作稱為“文件排序”。

慢查詢日志

mysql 支持慢查詢日志功能—— mysql 會將查詢時間過長的 sql 相關信息寫入日志。這個查詢時間閥值由參數(shù) long_query_time 指定， long_query_time 的默認值為 10，運行 10S 以上的查詢 sql 會被記錄到慢查詢日志中。默認情況下，Mysql 數(shù)據(jù)庫并不啟動慢查詢日志，需要我們手動來設置這個參數(shù)。慢查詢日志支持將日志記錄寫入文件，也支持將日志記錄寫入數(shù)據(jù)庫表。

可通過以下 sql 查看慢查詢日志是否開啟：

show variables  like '%slow_query_log%';

通過以下 sql 開啟慢查詢：

set global slow_query_log=1;

使用 sql 修改慢查詢日志設置只對當前數(shù)據(jù)庫生效，如果 MySQL 重啟后則會失效。如果要永久生效，就必須修改配置文件 my.cnf。

通過以下 sql 查看修改慢查詢的閾值：

show variables like 'long_query_time%';set global long_query_time=4;

主從備份

主從備份原理

主從復制是指一臺服務器充當主數(shù)據(jù)庫服務器，另一臺或多臺服務器充當從數(shù)據(jù)庫服務器，主服務器中的數(shù)據(jù)自動復制到從服務器之中。通過這種手段我們可以做到讀寫分離，主庫寫數(shù)據(jù)，從庫讀數(shù)據(jù)，從而提高數(shù)據(jù)庫的可用。MySQL 主從復制涉及到三個線程，一個運行在主節(jié)點（log dump thread），其余兩個(I/O thread, SQL thread)運行在從節(jié)點。

主節(jié)點 binary log dump 線程：

當從節(jié)點連接主節(jié)點時，主節(jié)點會創(chuàng)建一個 logdump 線程，用于發(fā)送 binlog 的內容。在讀取 binlog 中的操作時，此線程會對主節(jié)點上的 binlog 加鎖，當讀取完成，甚至在發(fā)動給從節(jié)點之前，鎖會被釋放。

從節(jié)點I/O線程:用于從庫將主庫的 binlog復制到本地的 relay log中，首先，從庫庫會先啟動一個工作線程，稱為IO工作線程，負責和主庫建立一個普通的客戶端連接。如果該進程追趕上了主庫，它將進入睡眠狀態(tài)，直到主庫有新的事件產生通知它，他才會被喚醒，將接收到的事件記錄到 relay log(中繼日志)中。

從節(jié)點 SQL 線程:

SQL 線程負責讀取 relay log 中的內容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性。

主從備份延遲

主備延遲最直接的表現(xiàn)是，備庫消費中繼日志（ relay log）的速度，比主庫生產 binlog 的速度要慢?？赡軐е碌脑蛴校?/p>

• 大事務，主庫上必須等事務執(zhí)行完成才會寫入 binlog，再傳給備庫，當一個事物用時很久的時候，在從庫上會因為這個事物的執(zhí)行產生延遲。

• 從庫壓力大。

主備延遲當然是不好的，那么有哪些辦法盡量減小主備延遲呢？有下面幾個辦法：

• 一主多從——多接幾個從庫，讓這些從庫來分擔讀的壓力。這樣方法適用于從庫讀壓力大的時候。

• 通過 binlog 輸出到外部系統(tǒng)，比如 Hadoop 這類系統(tǒng)，讓外部系統(tǒng)提供統(tǒng)計類查詢的能力

分布式事務

由于篇幅問題，這里不再對分布式事物的概念做普及，直接介紹兩種分布式事務: XA 分布式事務和 TCC 分布式事務。

XA 分布式事務

XA 是兩階段提交的強一致性事物。在 MySQL 5.7.7 版本中，Oracle 官方將 MySQL XA 一直存在的一個 “bug” 進行了修復，使得MySQL XA 的實現(xiàn)符合了分布式事務的標準。

XA 事務中的角色：

• 資源管理器（resource manager）：用來管理系統(tǒng)資源，是通向事務資源的途徑。數(shù)據(jù)庫就是一種資源管理器。資源管理還應該具有管理事務提交或回滾的能力。

• 事務管理器（transaction manager）：事務管理器是分布式事務的核心管理者。事務管理器與每個資源管理器（resource manager）進行通信，協(xié)調并完成事務的處理。事務的各個分支由唯一命名進行標識。

XA 規(guī)范的基礎是兩階段提交協(xié)議：

在第一階段，交易中間件請求所有相關數(shù)據(jù)庫準備提交（預提交）各自的事務分支，以確認是否所有相關數(shù)據(jù)庫都可以提交各自的事務分支。當某一數(shù)據(jù)庫收到預提交后，如果可以提交屬于自己的事務分支，則將自己在該事務分支中所做的操作固定記錄下來，并給交易中間件一個同意提交的應答，此時數(shù)據(jù)庫將不能再在該事務分支中加入任何操作，但此時數(shù)據(jù)庫并沒有真正提交該事務，數(shù)據(jù)庫對共享資源的操作還未釋放（處于鎖定狀態(tài)）。如果由于某種原因數(shù)據(jù)庫無法提交屬于自己的事務分支，它將回滾自己的所有操作，釋放對共享資源上的鎖，并返回給交易中間件失敗應答。

在第二階段，交易中間件審查所有數(shù)據(jù)庫返回的預提交結果，如所有數(shù)據(jù)庫都可以提交，交易中間件將要求所有數(shù)據(jù)庫做正式提交，這樣該全局事務被提交。而如果有任一數(shù)據(jù)庫預提交返回失敗，交易中間件將要求所有其它數(shù)據(jù)庫回滾其操作，這樣該全局事務被回滾。

mysql 允許多個數(shù)據(jù)庫實例參與一個全局的事務。MySQL XA 的命令集合如下：

-- 開啟一個事務，并將事務置于 ACTIVE 狀態(tài)，此后執(zhí)行的 SQL 語句都將置于該是事務中。XA START xid-- 將事務置于 IDLE 狀態(tài)，表示事務內的 SQL 操作完成。XA END xid-- 事務提交的準備動作，事務狀態(tài)置于 PREPARED 狀態(tài)。事務如果無法完成提交前的準備操作，該語句會執(zhí)行失敗。XA PREPARE xid-- 事務最終提交，完成持久化。XA COMMIT xid-- 事務回滾終止XA ROLLBACK xid-- 查看 MySQL 中存在的 PREPARED 狀態(tài)的 xa 事務。XA RECOVER

MySQL 在 XA 事務中扮演的是參與者的角色，被事務協(xié)調器所支配。XA 事務比普通本地事務多了一個 PREPARE 狀態(tài)，普通事務是 begin-> commit 而分布式事務是 begin->PREPARE 等其他數(shù)據(jù)庫事務都到 PREPARE 狀態(tài)的時候再 PREPARE->commit。分布式事務 sql 示例：

 xa start 'aaa'; insert into table(xxx) values(xxx); xa end 'aaa'; xa prepare 'aaa'; xa commit 'aaa';

XA 事務存在的問題：

• 單點問題:事務管理器在整個流程中扮演的角色很關鍵，如果其宕機，比如在第一階段已經完成，在第二階段正準備提交的時候事務管理器宕機，資源管理器就會一直阻塞，導致數(shù)據(jù)庫無法使用。

• 同步阻塞:在準備就緒之后，資源管理器中的資源一直處于阻塞狀態(tài)，直到提交完成才能釋放資源。

• 數(shù)據(jù)不一致:兩階段提交協(xié)議雖然為分布式數(shù)據(jù)強一致性所設計，但仍然存在數(shù)據(jù)不一致性的可能，比如在第二階段中，假設協(xié)調者發(fā)出了事務 commit 的通知，但是因為網(wǎng)絡問題該通知僅被一部分參與者所收到并執(zhí)行了 commit 操作，其余的參與者則因為沒有收到通知一直處于阻塞狀態(tài)，這時候就產生了數(shù)據(jù)的不一致性。

TCC 分布式事務

TCC 又被稱作柔性事務，通過事務補償機制來達到事務的最終一致性，它不是強一致性的事務。TCC 將事務分為兩個階段，或者說是由兩個事務組成的。相對于 XA 事務來說 TCC 的并發(fā)性更好，XA 是全局性的事務，而 TCC 是由兩個本地事務組成。

假設我們購買一件商品，后臺需要操作兩張表——積分表加積分而庫存表扣庫存，這兩張表存在于兩個數(shù)據(jù)庫中，使用 TCC 事務執(zhí)行這一事務：

1）TCC 實現(xiàn)階段一：Try

在 try 階段并不是直接減庫存加積分，而是將相關數(shù)據(jù)改變?yōu)轭A備的狀態(tài)。庫存表先鎖定一個庫存，鎖定的方式可以預留一個鎖定字段，當這個字段為一的時候表示這個商品被鎖定。積分表加一個數(shù)據(jù)，這個數(shù)據(jù)也是被鎖定狀態(tài)，鎖定方式和庫存表一樣。其 sql 形如：

update stock set lock=1 where id=1;insert into credits (lock,...) values (1,...)

這兩條 sql 如果都執(zhí)行成功則進入 Confirm 階段，如果執(zhí)行不成功則進入 Cancel 階段

2）TCC 實現(xiàn)階段二：Confirm

這一階段正式減庫存加積分訂單狀態(tài)改為已支付。執(zhí)行 sql 將鎖定的庫存扣除，為累加積分累加，以及一些其他的邏輯。

3）TCC 實現(xiàn)階段三：Cancel

當 try 階段執(zhí)行不成功，就會執(zhí)行這一階段，這個階段將鎖定的庫存還原，鎖定的積分刪除掉。退回到事務執(zhí)行前的狀態(tài)。

TCC 事務原理很簡單，使用起來卻不簡單。首先 TCC 事務對系統(tǒng)侵入性很大，其次是讓業(yè)務邏輯變得復雜。在實際使用中我們必須依賴 TCC 事務中間件才能讓 TCC 事務得以實現(xiàn)。通常一個 TCC 事務實現(xiàn)大概是這樣子的：某個服務向外暴露了一個服務，這個服務對外正常調用，其他服務并不能感知到 TCC 事務的存在，而其服務內部，分別實現(xiàn)了 Try,Confirm,Cancel 三個接口，注冊到 TCC 中間件上去。當調用這個服務的時候，其事務操作由該服務和 TCC 中間件共同完成。

而 TCC 事務中間件還要做好其他事情，比如確保 Confirm 或者 Cancel 執(zhí)行成功，如果發(fā)現(xiàn)某個服務的 Cancel 或者 Confirm 一直沒成功，會不停的重試調用他的 Cancel 或者 Confirm 邏輯，務必要他成功！即使在嘗試多次后無法成功也能通知到系統(tǒng)需要人工排查異常。TCC 事務還要考慮一些異常情況的處理，比如說訂單服務突然掛了，然后再次重啟，TCC 分布式事務框架要能夠保證之前沒執(zhí)行完的分布式事務繼續(xù)執(zhí)行。TCC 分布式事務框架還需要做好日志的記錄，保存下來分布式事務運行的各個階段和狀態(tài)，以便系統(tǒng)上線后能夠排查異常，恢復數(shù)據(jù)。目前開源的 TCC 事務框架有：Seata ByteTCC tcc-transaction 等。

到此，相信大家對“怎么理解并掌握MySQL”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續(xù)學習！