mysql的邏輯架構

首先我們來看一下mysql的邏輯架構圖

server層包括聯結器，查詢快取，分析器，優化器，執行器等，內建函式（例如時間函式，數學函式等）和儲存過程，觸發器，事務等都在這一層實現。

引擎層負責資料的儲存和提取，包括很多我們常用的引擎如Innodb，MyISAM，Memory等。

聯結器

我們使用下面命令來連線資料庫：

 mysql -uxxxx -pxxxx
複製程式碼

聯結器接收到命令後，完成如下操作：

• 判斷使用者名稱和密碼是否正確，如果錯誤，會報 "Access denied for user"的錯誤，然後結束本次會話。
• 使用者名稱密碼正確，會去許可權表中檢視許可權，然後把許可權寫入此次連線的程式中。這意味在這連線成功後，即使你用管理員賬號對這個使用者做了修改，也不會影響此次連線的許可權。

一個連線預設儲存時間是8個小時，由引數wait_timeout決定。也就是說如果不操作，過了8小時聯結器會自動斷開連線，再次操作，則會報 "LOST CONNECTION"的錯誤。

快取

mysql拿到一個請求後會先看快取中是否有，如果有則直接返回，如果沒有，再往下執行，執行完成後，把結果放入快取，如果是複雜的查詢，效率會非常的高。 但mysql的快取有一個非常大的問題： 只要對一個表更新，這個表上的所有快取都會失效。 所以快取的命中率非常低。在大多數情況下，不建議使用快取。

mysql8.0以上版本直接將查詢快取的整快功能都去掉了。

分析器

分析器主要是對sql語句進行解析，分析出關鍵字，讓mysql知道你要做什麼，如果sql語句有語法錯誤，會拋錯。

優化器

優化器主要目的是生成執行計劃。比如語句：

 SELECT a,b FROM t WHERE a=1 AND b=1
複製程式碼

• 可以根據a索引，找到所有a=1的資料，然後再判斷b是否等於1
• 也可以根據b索引，找到所有b=1的資料，然後再判斷a是否等於1
• 甚至可以全表掃描，找出所有a=1 並且 b=1的資料

至於具體使用哪種執行計劃，就是優化器根據效率最高來做判斷的了。

執行器

執行期在拿到執行計劃後，會先做一個許可權的判斷，看使用者是否對錶有操作許可權，如果沒有許可權，會拋錯。如果有許可權，就開啟表呼叫引擎介面獲取資料。

小練習

mysql當中已經有了快取，為什麼我們還要用redis，memcache等第三方的快取呢？

定位分析sql語句

在工作中，我們是不是有時會遇到查詢返回非常慢的情況，那麼這種情況如何定位慢sql，並且優化呢？

定位慢sql

定位慢sql有以下兩種方案：

• 通過慢查詢日誌確定慢查詢
• 通過show processlist檢視正在執行的查詢

慢查詢日誌

mysql慢查詢日誌是記錄執行時間超過設定的閥值的SQL語句，可以使用如下命令來檢視是否開啟

show variables like '%slow_query_log%';
複製程式碼

預設慢日誌是未開啟狀態。

慢查詢日誌有四個比較關鍵的引數：

• slow_query_log：是否開啟慢查詢日誌。
• long_query_time：慢查詢日誌設定的時間閥值，超過這個閥值會被記錄到日誌中。
• show_query_log_file: 慢查詢日誌記錄的檔案
• log_queries_not_using_indexes: 是否把沒有走索引的sql語句也記錄到日誌中。

在開啟慢查詢日誌之前，我們先在表裡插入一下資料

drop table if exists `slow_log`;  
CREATE TABLE `slow_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure if exists slow_log_insert;
delimiter ;;
create procedure slow_log_insert()
begin
  declare i int;                    
  set i=1;                          
  while(i<=10000)do                 
    insert into slow_log(a,b) values(i,i);  
    set i=i+1; 
  end while;
end;;
delimiter ;
call slow_log_insert(); 

複製程式碼

我們來開啟一下慢查詢：

set global slow_query_log=1;  -- 在本會話中開啟慢日誌
set global long_query_time=0.005; -- 在本會話中設定閥值時間為5ms
set global slow_query_log_file='/tmp/mysql_slow.log'; -- 設定慢日誌的檔案路徑
複製程式碼

然後執行sql語句

select * from slow_log;
select * from slow_log where a = 5;
複製程式碼

發現只有前一條sql語句記錄到了慢查詢日誌裡面

日誌中比較重要的引數如下：

• Query_time: 執行時間
• Lock_time: 等待表鎖時間
• Rows_sent: 語句返回行數
• Rows_examined：語句執行期間從儲存引擎讀取行數

當然在生成上，慢日誌中的內容會很多，我們可以使用mysqldumpslow 來對慢日誌進行分析和彙總。

檢視正在查詢的慢查詢

有時候，慢查詢還在進行，但資料庫負載已經偏高了，這時候可以用 show processlist 來找出慢查詢。 如果有PROCESS許可權，可以看到在執行的語句。如果沒有則只能看到本次會話中的執行語句。

我們開啟兩個會話，然後執行下面語句

會話1	會話2
SELECT SLEEP(100)
	SHOW PROCESSLIST

會話2的顯示結果如下：

這裡對幾個重要引數解釋一下：

• id: 會話的id
• Command: 現在會話的狀態
• Time： 已執行的時間
• Info：執行的語句

我們可以使用 "kill [query] id" 命令來終止執行

kill 27
或
kill query 27
複製程式碼

"kill 27 和 kill query 27" 的區別在於"kill 27"是結束id為27的會話。"kill query 27"表示結束會話27的本次操作，而保留會話27。

分析SQL語句

通過上面的兩個步驟我們已經找到了慢sql語句，現在我們要進行進行分析了，那麼如何分析SQL語句呢？我們可以使用以下三種工具進行分析：

• explain 獲取mysql的執行計劃
• show profile 獲取每個環節mysql的執行時間
• trace 檢視優化器的執行計劃，獲取每種可能性所需要的代價

explain 獲取mysql的執行計劃

EXPLAIN SELECT * FROM slow_log WHERE a=1
複製程式碼

執行結果如下：

我們重點看以下如下幾個引數：

• select_type 查詢型別
• type 本次查詢表的連線型別
• key 所用到的索引
• rows 掃描的行數
• Extra 其他附加資訊

當key為空的時候表示沒有用到索引，可以考慮優化了。 當Extra出現如下幾個情況，也可以考慮優化。

值	解釋	sql例子
Using filesort	是用外部排序，而非索引排序	EXPLAIN select b from slow_log order by b
Using temporary	建立了臨時表	EXPLAIN SELECT b FROM slow_log group by b order by null
Using join buffer (flat, BNL join)	關聯查詢中，被驅動表欄位沒有索引	EXPLAIN SELECT * FROM slow_log AS s1 INNER JOIN slow_log AS s2 ON s1.b=s2.b

show profile 獲取每個環節mysql的執行時間

有的時候，我們需要確認到底是哪個環節出問題了，此時explain就不是那麼好用了。我們需要使用show profile。

show profile使用步驟如下：

1. 檢視是否支援 profile:

SHOW VARIABLES LIKE '%profiling%';
複製程式碼

• have_profiling 表示支援 profile
• profiling 表示暫未開啟 profile

2. 我們來開啟profile

   SET profiling=1
複製程式碼

上面的命令只是在本次會話中開啟，如果需要全域性開的，可以在命令中加上"GLOBAL"

   SET GLOBAL profiling=1
複製程式碼

3. 執行sql語句

   SELECT a FROM slow_log WHERE a=1
複製程式碼

4. 確定sql的query id

   SHOW PROFILES;
複製程式碼

5. 檢視sql執行詳情

   SHOW PROFILE FOR QUERY 2;
複製程式碼

trace 檢視優化器的執行計劃，獲取每種可能性所需要的代價

我們使用explain可以看到執行計劃，但是explain並不能告訴我們為什麼選擇了A方案而不是B方案。 我們可以使用trace來知道執行方案的細節。

ps:

• trace只支援mysql5.6及以上版本。
• 開啟trace會影響到伺服器的效能，所以我們一般只是在需要除錯時開啟。

trace 使用步驟如下：

• 開啟trace
• 執行sql語句
• 獲取sql語句的執行計劃明細
• 關閉trace

接下來我們用一個例子來說明一下trace是怎麼使用的。 有下面一條sql語句：

SELECT a,b FROM slow_log WHERE a>8000; 
複製程式碼

因為a上面有索引，按照大部分人的常識，應該會走a索引，但是我們用explain工具檢視，發現這條語句，竟然使用了全表掃描。

那麼接下來我們用trace來分析一下這條sql的細節。

1.開啟trace,並且以json格式輸出

SET SESSION optimizer_trace="enabled=on",end_markers_in_json=on;    
複製程式碼

2.執行sql語句：

SELECT a,b FROM slow_log WHERE a>8000; 
複製程式碼

3.獲取結果：

SELECT * FROM information_schema.OPTIMIZER_TRACE
複製程式碼

返回的結果如下：

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `slow_log`.`a` AS `a`,`slow_log`.`b` AS `b` from `slow_log` where (`slow_log`.`a` > 8000)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`slow_log`.`a` > 8000)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`slow_log`.`a` > 8000)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`slow_log`.`a` > 8000)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`slow_log`.`a` > 8000)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`slow_log`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`slow_log`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 10337,
                    "cost": 2092.5
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "a",
                      "usable": true,
                      "key_parts": [
                        "a",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "a",
                        "ranges": [
                          "8000 < a"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 2000,
                        "cost": 2401,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`slow_log`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 10337,
                      "access_type": "scan",
                      "resulting_rows": 10337,
                      "cost": 2090.4,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10337,
                "cost_for_plan": 2090.4,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`slow_log`.`a` > 8000)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`slow_log`",
                  "attached": "(`slow_log`.`a` > 8000)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`slow_log`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
複製程式碼

這個結果主要分為三個部分：

• join_preparation：準備階段，對應邏輯圖中的分析器
• join_optimization：優化階段， 對應邏輯圖中的優化器
• join_execution：執行階段，對應邏輯圖中的執行器

這裡我們重點來看一下join_optimization的內容：

上面的表格中，rows和cost最為重要

• rows 預計掃描行數
• costs 消耗的資料

所以我們可以得出

型別	掃描行數	消耗資源
全表掃描	10337	2092.5
使用"a"索引	2000	2401

我們發現全表掃描比使用"a"索引消耗的資源少，所以mysql使用了全表掃描的方案。

4.關閉trace

   SET SESSION optimizer_trace="enabled=off"
複製程式碼

mysql索引

B+樹索引

大家都知道，在mysql中使用的最多索引就是B+樹索引，那麼今天我們就來了解一下B+樹索引他的資料結構到底是什麼樣子的。在介紹B+樹之前我們先來聊一聊其他我們常用的索引。 比如我們現在有資料[1,2,3,5,6,7,9]。

順序表

我們先把這個資料放在順序表中。得到如下結構圖：

接下來我們要查詢是否存在數字6， 可以使用二分法查詢。

他的時間複雜度為O{logn}。查詢效率非常高。但是插入的效率就不是特別好了，每次插入都需要把後面的元素向後移動一位，而刪除則是把後面的元素向前一位，修改可以看作是刪除和插入的合集操作。 插入數字4的邏輯結構圖如下：

刪除數字5的邏輯結構圖如下：

當然一般我們遇到刪除操作，會做做邏輯刪除，把要刪除的資料設定為null,記憶體先不釋放，等進行n此操作後再進行重建順序表。

所以因為順序表他的插入效率太低，最好是用來儲存那些一次插入不常變的或只做增量遞增的資料。

二叉樹

二叉樹特點:左節點的值小於根節點，右節點的值大於根節點,並且每個節點共有兩課樹 我們根據二叉樹的邏輯結構建立如下結構的二叉樹：

二叉樹的插入不像順序表那麼複雜，他只需要找到插入數字在樹中的位置，修改根節點和自身的索引即可。 舉個例子： 我們要插入數字8,我找到值為7的節點，把7的右節點指向8，8的右節點指向9。

二叉樹的刪除也比較簡單，只需要把刪除節點左子樹最大節點或右子樹最小節點移上來代替自己即可。 舉個例子： 我們要刪除數字數字5，我們可以把3移動上來，或用6移動上來。

聊完了二叉樹的插入與刪除，我們再來聊一下二叉樹的查詢，和層級相關，上圖中二叉樹的層級為3，所以他查詢一個數字，最多隻要三次。但是二叉樹並不只只有這一種建立方法，他只要滿足“左節點的值小於根節點，右節點的值大於根節點,並且每個節點共有兩課樹”就可以，我們來看一個比較極端的二叉樹結構圖。他的層級為7，而且沒辦法使用二分法，只能逐個查詢，效率就非常低了。

平衡二叉樹

上一節我們知道，二叉樹的搜尋和自身的層級有很大的關係，層級越少，檢索效率越高。我們這裡引出了平衡二叉樹： 平衡二叉樹是一種二叉樹，其中每一個節點的左子樹和右子樹的高度差之多等於1。而左子樹深度減去右子樹的深度的值稱為平衡因子BF。 BF只可以是（-1，0，1），如果不在這三個值的範圍內，則需要翻轉。 我們來看一個平衡二叉樹的栗子，現在要構建用平衡二叉樹構建 [3,2,1,4,5]的陣列：

剛開始插入“3”和“2”的時候我們很正常的構建，到插入“1”後，發現3節點的平衡因子變成了2 需要調整，於是向右旋轉。再插入“4”，沒有發生變化，插入“5”時，“3”節點為“-2”右不平衡了，於是向左旋轉。使樹繼續達到平衡。

上面的栗子是完全平衡二叉樹（AVL），但平衡二叉樹維護樹平衡的效率過高，所以很多系統中採用紅黑樹，紅黑樹是AVL樹的改進版本。具體實現方法，這裡就不介紹了。

B樹

我們前面討論的各種資料結構，處理樹都是在記憶體中，因此考慮的都是記憶體中的運算時間複雜度。但對於mysql而言，大部分資料是存放在硬碟上的，所以硬碟的讀取次數是影響效能的關鍵因素。對於通一個檢索，我們讀取硬碟幾百次和讀取硬碟幾次是有本質差別的。 我們之前所說的樹都只存放一個元素。當資料非常多的時候，樹必定會非常大，而且深度非常的深，使得讀硬碟的次數非常多，這是非常影響檢索效率的。所以這使我們不得不打破一個節點只存一個元素的限制，這就是B樹的由來。 我們用B樹來構建[1,2,3,5,6,7,9]：

PS：mysql儲存引擎每一個節點預設大小是16k，是讀取磁碟一次的資料大小。這裡只是為了說明資料結構而做了簡化。

B+樹

雖然我們上面說了B樹的很多優點，但B樹還是有很多優化的空間，這裡我們拿B+樹說明一下。

我們來用B+樹來構建陣列[1,2,3,5,6,7,9]。

我們來分析一下B+樹相對於B樹有什麼不同點，以及有什麼優勢：

• B+樹所有的葉子節點存資料，非葉子節點只存key。使得非葉子節點能儲存更多的資料，使樹的層級更淺了，增加了查詢的效率
• 各葉子節點用指標相連，提高了遍歷和範圍查詢的效率。

Innodb的索引類別

我們先來建立一張表，並且插入一些資料：

drop table if exists t8; 
CREATE TABLE `t8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` char(2) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into t8(a,b) values (1,'a'),(2,'b'),(3,'c'),(5,'e'),(6,'f'),(7,'g'),(9,'i');
複製程式碼

然後使用查詢語句，得到如下結果：

SELECT * FROM t8
複製程式碼

聚簇索引

“聚簇索引”又稱為“主鍵索引”，主要是如下結構：

可以看到，聚簇索引有兩個特點：

• 根據主鍵按照B+樹的結構構建
• 每個葉子節點包含爭行資料

二級索引

“二級索引”也稱為“普通索引”，主要結構如下：

可以看到，二級索引有以下兩個特點：

• 根據a建立B+樹結構
• 葉子節點每個欄位儲存自己和主鍵ID

回表

我們用下面sql語句查詢資料：

SELECT a,b FROM t8 WHERE a=1
複製程式碼

這條語句的執行順序如下：

• 在二級索引中通過a=1查詢出id=1
• 然後再用id=1在聚簇索引中查詢出(a=1,b=a)的資料 我們把 回到主鍵索引樹搜尋的過程，稱之為回表

覆蓋索引

我們把t8表的二級索引a，改成(a,b)索引，二級索引結構如下：

alter table t8 drop index idx_a;
create index idx_ab on t8(a,b);
複製程式碼

我們再用下面的sql語句查詢資料：

SELECT a,b FROM t8 WHERE a=1
複製程式碼

這條語句查詢順序如下

• 再二級索引中通過a=1 找出（a=1,b=a）

我們看，這裡只查詢了二級索引，沒有回表。 我們把沒有回表的查詢稱為“覆蓋索引”。 因為覆蓋索引可以減少查詢硬碟的次數，顯著提升效能，所以覆蓋索引是一個常用的效能優化手段。

小練習

在show_log表中，我們執行sql語句：

SELECT a,b FROM slow_log WHERE a>8000; 
複製程式碼

為什麼全表掃描相比於"a"索引掃描的行數多，但消耗的資源反而少？如何優化？

快照讀在四種隔離級別中的區別

說明

mysql有兩種讀的方式，快照讀和當前讀。

• 快照讀通俗講就是讀某一個時刻的資料，一般為簡單的select操作(不包括 select ... lock in share mode, select ... for update)
• 當前讀通俗將就是讀最新時刻的資料，操作包括select ... lock in share mode，select ... for update，insert，update，delete

mysql有四種隔離級別：

• read uncommitted(讀未提交)
• read committed(讀提交)
• repeatable read(可重複讀)
• serializable(序列化)

在這一章，我們不考慮當前讀，重點分析一下快照讀在四種隔離級別的應用。 快照讀在四個隔離級別中應用，會存在三種讀的問題：

型別	說明
髒讀	事務A讀取了事務B未提交的資料。
不可重複讀	事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的過程中，對資料作了更新並提交，導致事務A多次讀取同一資料時，結果 不一致。
幻讀	事務A首先根據條件索引得到N條資料，然後事務B增添了M條符合A搜尋條件的資料，導致事務A再次搜尋發現有N+M條資料

接下來我們就來分析一下這四種隔離級別和這三個讀問題的關係： 我們先建立一張表：

DROP TABLE if exists `tran`;  
CREATE TABLE `tran` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `tran`(a,b) VALUES(1,1);
複製程式碼

然後我們開啟兩個事務，分別執行如下sql語句：

事務A	事務B
設定隔離級別	設定隔離級別
BEGIN;	BEGIN
SELECT b FROM `tran` WHERE a=1;
	UPDATE `tran` SET b=2 WHERE a=1;
	INSERT INTO `tran`(a,b) VALUES(1,3);
SELECT b FROM `tran` WHERE a=1; // 記為VAL1
	COMMIT;
SELECT b FROM `tran` WHERE a=1; // 記為VAL2
COMMIT;

當VAL1中包含 b=2時，表示讀到了未提交的資料，有“髒讀”的問題。 當VAL2中包含 b=2時，表示兩次讀取的資料不一致，有“不可重複讀”的問題。 當VAL2中包含 b=3時，表示讀到了新插入的行，有“幻讀”的問題。

我們使用下面語句來設定隔離級別

set session transaction isolation level [隔離級別];
// read uncommitted，read committed，repeatable read, serializable
複製程式碼

在執行結束之後我們可以使用下面的sql語句來初始化表的狀態

TRUNCATE TABLE `tran`;
INSERT INTO `tran`(a,b) VALUES(1,1);
複製程式碼

我們分別設定四種不同的隔離級別，執行上面的sql，得到如下的結果：

隔離級別	VAL1	VAL2	髒讀	不可重複讀	幻讀
read uncommitted	2,3	2,3	Y	Y	Y
read committed	1	2,3	N	Y	Y
repeatable read	1	1	N	N	N
serializable	1	1	N	N	N

PS:

• 1.在mysql中 “repeatable read”的隔離級別是沒有幻讀的（網上有很多文章在這個知識點上有錯誤）
• 2.在“read uncommitted(讀未提交)”，讀的始終是最新的資料，所以快照讀和當前讀是一樣的。
• 3.在“serializable(序列)”隔離級別下，事務B執行到 “UPDATE” 語句時會阻塞住，必須要等到事務A “COMMIT” 之後才能繼續執行。說明在“serializable”隔離級別下，事務會 在“SELECT”語句中加上了鎖，所以在此隔離級別中，不存在快照讀，所有的讀都是當前讀。

快照讀的實現原理

我們在上一節瞭解到，mysql四個隔離級別中，只有RC和RR用到了快照讀。這一節我們就來分析一下他們是怎麼實現的。

術語說明

在系統中，每個事務都有唯一的事務id，叫做"transaction id",在事務開始的時候，是向系統申請的，是嚴格遞增的。

每一行資料，也會用多個版本。每次更新一個事務都會產生一個新的版本，並且把transaction id 賦值給當前資料，叫做"row tx_id"。當然舊的版本會保留。

這裡我們使用上一節的"tran"，裡面有一條資料（id,a,b）= (1,1,1)，這裡的"row tx_id" = 10，儲存的邏輯圖如下：

現在我們執行下面的修改語句:

UPDATE `tran` SET b=2 WHERE a=1; // "transaction id"=20
複製程式碼

儲存的邏輯圖如下：

PS：方框裡面的就是undo log（回滾日誌）,當事務失敗時，我們做逆向操作，把undo log中的資料回填就去就可以實現事務的回滾了。

在瞭解了innerdb的儲存邏輯之後，我們來分析“RR”隔離級別。在開啟一個新事務的時候，事務會生成一個一致性檢視（Read-View）。 裡面主要包含三個四個引數：

• transaction id: 本次事務的id
• trx_list: 系統中活躍的事務陣列
• up_limit_id: trx_list中的最小事務陣列id
• low_limit_id: 當前系統已經建立過的最大事務+1

每一行的"row tx_id"在一致性檢視(Read_View)大概可以分為三種情況

• 1. "row tx_id" < up_limit_id: 落在綠色區間，表示已提交事務或當前自己的事務，這個資料是可見的。
• 2. "row tx_id" >= low_limit_id:落在紅色區間，表示這個版本是將來事務生成的，不可見
• 3. up_limit_id<="row tx_id"<low_limit_id: 落在黃色區間，此處分為兩種情況
  • 3.1. "row tx_id" 在 trx_list 中表示事務還未提交，不可見
  • 3.2. "row tx_id" 不在 trx_list 中表示事務已提交，可見

我們還是使用上一節裡的“tran”表，裡面有一條資料（id,a,b）= (1,1,1)，這裡的"row tx_id" = 10，系統目前的事務id為20，我們執行如下語句：

事務A	事務B	事務C
Start transaction with consistent snapshot
	Start transaction with consistent snapshot
UPDATE `tran` SET b=2 WHERE a=1;
	SELECT b FROM `tran` WHERE a=1; // VAL1
COMMIT;
	SELECT b FROM `tran` WHERE a=1; // VAL2
		Start transaction with consistent snapshot
		UPDATE `tran` SET b=3 WHERE a=1;
	SELECT b FROM `tran` WHERE a=1; // VAL3
		COMMIT;
	SELECT b FROM `tran` WHERE a=1; // VAL4

我們把上面的sql語句轉換成邏輯圖如下：

在圖中我們可以知道Read-View是在事務開始的時候生成的。 我們可以得到Read-View如下：

• transaction id: 21
• trx_list: [20, 21]
• up_limit_id: 20
• low_limit_id: 22

邏輯分析如下：

• 最初的資料，"row tx_id" = 10，小於 up_limit_id，表示已提交事務，走了“1”邏輯，可見
• 事務A的事務id為20，“row tx_id”也為20，等於up_limit_id，而小於low_limit_id，但在trx_list中存在，表示事務未提交，所以走的是“3.1”邏輯，不可見
• 事務C的事務id為22，“row tx_id”也為22，等於low_limit_id，表示是未來事務，所以走的是“2”邏輯，不可見

所以在RR隔離級別下面，VAL1，VAL2，VAL3, VAL4的值都為“1”，

我們再來分析一下RC隔離級別，RC隔離級別和RR隔離級別判斷邏輯是一致的，唯一區別是，RR隔離級別在事務開始的時候生成"Read-View", 而RC隔離級別是在每次“SELECT”語句時生成"Read-View"。

RC隔離級別邏輯圖如下：

WX20190926-201003.png

在獲取VAL1時 Read-View如下：

• transaction id: 21
• trx_list: [20, 21]
• up_limit_id: 20
• low_limit_id: 22

分析：

• "row tx_id" = 20，在up_limit_id<="row tx_id" < low_limit_id，並且 在trx_list中，所以為未提交事務走邏輯“3.1”，不可見
• "row tx_id" = 10，"row tx_id"<up_limit_id,為已提交事務，走邏輯“1”，可見。

獲取VAL2時 Read-View如下：

• transaction id: 21
• trx_list: [21]
• up_limit_id: 21
• low_limit_id: 22

分析：

• "row tx_id" = 20，"row tx_id"<up_limit_id,為已提交事務，走邏輯“1”，可見。

獲取VAL3時 Read-View如下：

• transaction id: 21
• trx_list: [21,22]
• up_limit_id: 21
• low_limit_id: 23

分析：

• "row tx_id" = 22，在up_limit_id<="row tx_id" < low_limit_id，並且 在trx_list中，所以為未提交事務走邏輯“3.1”，不可見
• "row tx_id" = 20，"row tx_id"<up_limit_id,為已提交事務，走邏輯“1”，可見。

獲取VAL4時 Read-View如下：

• transaction id: 21
• trx_list: [21]
• up_limit_id: 21
• low_limit_id: 23

分析：

• "row tx_id" = 22，在up_limit_id<="row tx_id" < low_limit_id，並且 不在trx_list中，所以為未提交事務走邏輯“3.2”，可見

所以在RC隔離級別下，我們可以得出結論： VAL1=1，VAL2=2，VAL3=2，VAL4=3。

參考文件

慕課網 《一線資料庫工程師帶你深入理解 MySQL》 s.imooc.com/W2749EM

極客時間 《MYSQL實戰45講》time.geekbang.org/column/intr…

《大話資料結構》

小錯誤修正

在公司做分享後，小夥伴raywang對於RR隔離級別下是存在幻讀，並且也給出了自己的例子，我們還是根據那張tran表，初始值還是（a=1，b=1）

事務A	事務B
BEGIN;
	BEGIN;
SELECT b FROM tran WHERE a=1;
	INSERT INTO tran(a,b) VALUES(1,2);
	COMMIT;
update tran set b=b+10 where a=1;
SELECT b FROM tran WHERE a=1; // VAL
COMMIT;

我們可以看到最終VAL結果有2個值 “11和12”，產生了幻讀

那麼為什麼會幻讀？我們來分析畫張邏輯圖分析一下

這裡的關鍵是update語句，把兩行資料都改了，使他們的tx_id為事務A的id。 所以滿足之前說的條件1（小於up_limit_id或為自己本身時可見），所以可以被查出來。

當然在RR隔離級別下面也同樣會出現不可重複讀的情況。我們依然根據那張tran表，初始值還是（a=1，b=1），來看下面的例子：

事務A	事務B
BEGIN;
	BEGIN;
SELECT a,b FROM tran WHERE id=1;
	update tran set a=2 where id=1;
	COMMIT;
update tran set b=2 where id=1;
SELECT a,b FROM tran WHERE id=1; // VAL
COMMIT;

我們在事務A裡面只修改了b=2的值，並沒有修改a的值，但卻把B事務修改的a讀出來了。至於具體原因和上面的一樣，這裡可交給各位讀者自行分析。 所以我們可以得出結論： 在RR隔離級別下，當一個事務修改了別的事務修改/新增過的資料時，可能會出現不可重複讀和幻讀。

最後，感謝 raywang 給出的非常寶貴的建議，使得這次分享更加的圓滿。