一 MySQL總體架構

上圖是《高性能MySQL》中對MySQL總體架構的描述，客戶端對服務端的連接有很多條，有一個專門的處理組件，類似tomcat使用線程池處理請求。解析器負責解析sql語句，在這同時會訪問緩存如果緩存有目標數據就直接返回。如果需要執行sql語句，還會先經過優化器重新編排執行過程(重寫查詢，重排查詢表的順序，選擇合適的索引、優化min()max() in()、重排where的順序以適應左前綴原則等)，優化的原則根本上只有一個：從磁盤讀取的數據頁(一頁16K,IO的基本單位)越少越好。例如：

使用where語句想走索引查詢，但是如果優化器認為查到的數據基本是全表就會直接走全表掃描，不走索引減少數據頁的讀取，也無需回表，雖然這也是性能上的優化，但是會讓MySQL執行的操作在我們的意料之外，例如不走索引的查詢不會對受影響的行加鎖，這有時會導致一些問題。因此，有explain這個指令讓我們可以知道MySQL的具體執行過程。

以上說的都是服務層面的，一些通用的功能，還包括了用戶權限驗證啊等等。最下面的則是存儲引擎，負責對磁盤數據的存取。看似對磁盤的數據存取只需調用API就行，實際上MySQL在存儲引擎這做了很多工作，例如事務控制啊、并發控制啊。

二 引擎

目前最常用的是InnoDB引擎，據說95 %的情況下使用它就行了。主要有點有：

1. 采用聚簇索引，數據本體存在主鍵索引的葉子結點下，查詢速度非常快。

2.還會視情況建立自適應的hash索引(根據數據的值散列運算得到地址,O(1)復雜度)，hash索引雖然查詢速度非常快但地址隨機不適合范圍查找，而且沖突多的時候表現不佳。

3. 相比MyISAM額外支持事務、支持行鎖。雖然行鎖不是任何情況優于表鎖，表鎖雖然并發量很低但是加鎖開銷小適合死鎖率很高的情況。InnoDB二者都有，可以靈活選擇。

4. InnoDB有完善的事務日志及熱備份機制，可用性很強，崩潰后恢復很方便

MyISAM實現非常簡單，功能非常有限：非聚簇索引(索引文件與數據文件單獨保存，通過索引查詢時總是需要回行)、不支持事務、鎖只支持表鎖。只適合小型項目、讀操作占大都數寫操作非常少的場景。

三 鎖與并發控制

鎖：MySQL的鎖從模式上來說有 共享鎖S(讀鎖)和 排他鎖X(寫鎖)，從粒度上或加鎖策略上分又分為行鎖與表鎖。一個引擎支持行鎖說明它可以一次給若干行加鎖，只支持表鎖的話就說明一次加鎖過程要么不鎖要么把全表鎖住。兩個事務獲取同一目標(若干行或整個表)的不同模式的鎖時，沖突情況如下：

上圖的√表示兩個事務獲取這兩種鎖時不會沖突，×表示這兩種鎖不能被兩個事務同時獲取，會沖突。上圖內容其實總結就是：兩個鎖同時有‘S’，或同時有‘I’是可以同時被兩個事務獲取的，不會沖突。

MVCC多版本控制并發：由于加鎖的開銷比較大，導致并發量的下降，因此很多數據庫都會有MVCC這個機制。這個機制簡單的說是通過給每個事務分配一個版本號，這個事務修改刪除等操作影響的行將被打上事務版本號存起來作為一個快照版本，通過這種做法可以在并發環境下避免很多的加鎖操作同時也能保證數據庫的正確性，從而大幅提高并發量。可以說MVCC是一種變種的行級鎖(不是簡單無腦給行加鎖)，當然只有支持行級鎖的引擎支持。

具體是MySQL有一個系統版本號，每創建一個事務后就遞增，并把這個版本號給新建事務，可以作為這個事務的標識。每個行有隱藏的兩列，分別為創建時間、刪除時間，實際中我們把這兩列存放創建這一行的事務的版本號、刪除這一行的版本號。進行各個操作的具體實現是：

insert： 把插入行的創建標識置為當前版本號(即當前事務版本號)。

delete： 把刪除行的刪除標識置為當前版本號。

update: 先新建一行，把當前版本號作為新建行的創建標識，把當前版本號作為原先行的刪除標識。

select：讀取數據時有兩個條件? a：行的創建標識要早于或等于本事務版本號(保證不會讀到后到的事務修改的數據，即解決不可重復讀)；? b:行的刪除標識要晚于或未定義本事務版本號(保證不會讀到被之前事務執行刪除操作的數據，即數據不失效)。

MVCC總結就是：變種的行級鎖，增刪改時維護行的創建標識和刪除標識，讀取時對這兩個標識加點限制條件。實現了不加鎖的情況下讀取到正確的數據，少加了這么多鎖，大幅提高了并發量。

四 事務與實際加鎖策略

事務：事務的概念及ACID特性都是老生常談了，這里總結一下‘I’隔離性的每個隔離級別下的加鎖策略以及解決的問題。

RU級別臟讀原因：此級別事務可以讀到其他事務修改的且未提交的數據，如事務A將x = 1，事務B此時讀到了x = 1，但是A回滾了，數據庫中x肯定也不為1了，所以B的數據是臟數據。

RC級別： 此隔離級別規定只有事務提交了，數據才能被其他事務讀到，自然解決了臟讀。但是卻沒解決不可重復讀的問題：事務A先讀到x = 1,然后事務B修改x = 2并提交,這時事務A再讀就會發現x = 2，與之前不一樣了。

RR級別: 不可重復讀使一個事務可能會讀到后來的事務修改的數據，為了避免這種情況，有了MVCC機制，讀數據時對每一行的版本做一些限制(MVCC在上一部分已總結原理)。

S級別： 這個級別下，操作表時會鎖住整個表，所以事務A操作此表時，其他事務不能對這張表做任何操作，包括了插入操作，自然也沒了幻讀的事情。

間隙鎖：間隙鎖會鎖住額外的行(即不止受影響的行)，讓其他事務沒法刪和改后面的行。間隙鎖的作用具體例子：當事務A在修改刪除 id>10的數據時，還沒執行完事務B插了進來添加了10條數據id都大于10，這時事務A再恢復執行就會把事務B插進來的數據也給改/刪咯。總之就是間隙鎖只有在刪/改操作才會觸發，鎖住其他行防止中途插數據，這樣被無辜污染。

上述都是理論上各個隔離級別解決的問題，在實際的MySQL中，可以加一些額外操作在RR隔離級別就避免幻讀問題了，一般使用當前讀和GAP鎖，快照讀不存在幻讀，但是update等操作是當前讀，舉個例子：

事務1：

select * from A where p_id = 10; //1

insert into A (id,p_id) values(1,10); //2

事務2：

insert into A (id,p_id) values(1,10); //1

如果事務2在事務1的第1行和第2行之間插入執行完畢，那么事務1的第二行就出了duplicate_key錯誤，可以總結幻讀的后果就是目前where條件讀到的數據不足以支持后續的操作的正確性。基于此有兩個辦法，一：如果where條件篩選的列是唯一索引說明只有一條符合條件的行，則讀數據時改為當前讀(select for update)將該行鎖住防止其他事務操作；二：如果當前where條件篩選的列不是唯一索引說明后續事務插入的行也可能符合條件，這時需要間隙鎖鎖住其他暫時不存在的行防止后續事務插入符合條件的行。

MySQL的實際加鎖策略：前面也說了，由于有了MVCC，RR及以下級別讀操作無需加鎖，增刪改操作影響的行加X鎖，刪/改還會有有間隙鎖，除非sql中顯示指明select .... lock in share mode則為讀操作影響的行加S鎖。S隔離級別有點特殊，為了防止幻讀，讀操作時會對整個表加S鎖，寫操作時會給整個表加X鎖。

五 優化總結

合理建表：

變長字段與定長字段盡量分離，每一行的大小固定方便跳躍計算

常用字段與非常用字段盡量分離，合理分配訪問量

列的選擇：

優先選用： int -> date time -> enum -> char -> vchar -> text

避免可為null的列，不適合索引的建立以及比較等計算，還會浪費額外的空間記錄

建立合適的索引：

查詢時應該用獨立的列，像where id+1 = 5是用不上索引的

選擇區分度大的列建立索引，像性別這種列就沒必要

使用頻率高的聯合查詢 where 列1..and 列2...則需要考慮為這幾個列建一個聯合索引

由于最左原則，聯合索引建立時應該把區分度高的放到左邊建立，查詢語句也要注意左前綴原則

用到索引覆蓋最好，像select * 則幾乎用不到索引覆蓋

SQL語句的優化：

少用In查詢

where ... or ...這類的查詢可以拆分為幾個select 再用union合并，性能提升明顯

表在連接之前先用where篩選，也要避免過多表的連接

select ..時要幾個列就寫幾個，不要多寫，會增加數據傳送量

總結

以上是生活随笔為你收集整理的mysql 运行模式_MySQL的运行模式及一些特性，引擎、事务、并发控制、优化总结...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。