前言：大部分業(yè)務研發(fā)同學在工作的90%的場景下會和各種數(shù)據(jù)打交道，在此過程中邁不過去的一個坎就是MySQL了，這篇文章從研發(fā)同學的視角來講述在MySQL使用中需要了解的各種關鍵技術；
下面我會分別從MySQL的體系架構、事務、鎖、索引、性能、部署架構幾個方面來介紹; 非特殊注明的情況下專指innodb引擎，希望各位在看完之后能夠有所收獲！
體系架構
想要了解一個技術體系非常建議的一種方式是先看系統(tǒng)整體的架構設計，這樣能夠快速的預覽整個框架，降低陌生感；然后通過分析一個典型的執(zhí)行過程增強對過程的把控；
整體框架
在MySQL的設計中整體可以分為三層：文件系統(tǒng)、存儲（索引）、服務層；我們所直接面向主要是服務層相關的模塊如：連接池、SQL、優(yōu)化器、緩存等，可以通過下圖對MySQL的組成有個大概的了解
? ??
查詢流程
再看一下一個SQL的執(zhí)行過程，SQL Cache->解析器->優(yōu)化器->執(zhí)行引擎->存儲引擎；是不是類似于我們設計的高性能服務端架構通過緩存->業(yè)務邏輯->業(yè)務數(shù)據(jù);只是這個業(yè)務邏輯是在數(shù)據(jù)庫領域的而已；
程序員的MySQL攻略，總結的非常全面系統(tǒng)
2019-06-22 21:51·少鷹說
前言：大部分業(yè)務研發(fā)同學在工作的90%的場景下會和各種數(shù)據(jù)打交道，在此過程中邁不過去的一個坎就是MySQL了，這篇文章從研發(fā)同學的視角來講述在MySQL使用中需要了解的各種關鍵技術；
下面我會分別從MySQL的體系架構、事務、鎖、索引、性能、部署架構幾個方面來介紹; 非特殊注明的情況下專指innodb引擎，希望各位在看完之后能夠有所收獲！
體系架構
想要了解一個技術體系非常建議的一種方式是先看系統(tǒng)整體的架構設計，這樣能夠快速的預覽整個框架，降低陌生感；然后通過分析一個典型的執(zhí)行過程增強對過程的把控；
整體框架
查詢流程
再看一下一個SQL的執(zhí)行過程，SQL Cache->解析器->優(yōu)化器->執(zhí)行引擎->存儲引擎；是不是類似于我們設計的高性能服務端架構通過緩存->業(yè)務邏輯->業(yè)務數(shù)據(jù);只是這個業(yè)務邏輯是在數(shù)據(jù)庫領域的而已；
事務
數(shù)據(jù)庫事務應該是大部分程序員在面試時的必經題目了，先一句話描述一下我所理解的事務：
在并發(fā)場景保障一次會話對數(shù)據(jù)操作符合預期（原子性、一致性、隔離性、持久性）的方式；
下面會分為名詞解釋和隔離級別兩趴來幫助我們理解事務；
名詞解釋
想象一下我們有多個客戶端（A/B/N）在并行的操作一張表T1中的數(shù)據(jù)，那么就可能會存在如下幾種情況：
1、臟讀：A可能讀取到B會話中未提交事務修改的數(shù)據(jù)（不可預期的數(shù)據(jù)結果）
2、不可重復讀：一個事物內多次查詢同一數(shù)據(jù)行，結果是不一致
3、幻讀：范圍檢索時兩次檢索會看到不同的數(shù)據(jù)行（范圍檢索不僅限于大于小于，部分的in場景或者全表掃描一樣會被覆蓋）
在以上場景下，MySQL定義了如下四種隔離級別：
PS：默認隔離級別是Repeatable_Read
鎖 & MVCC
鎖是并發(fā)場景下解決共享資源操作沖突問題的常見手段，同理MySQL也采用了該種策略，下面會分別介紹一下MySQL的鎖
在正式介紹之前補充一點背景信息，對于MySQL而言，讀分為快照讀和當前讀，
快照讀：select * from t1 where ;
當前讀：select xxx lock in share mode;select xxx for
update;insert/update/delete xxx;
鎖類型
表鎖
表鎖字義是對標進行加鎖，特點是開銷小，加鎖快，并發(fā)度低； MYISAM 引擎僅支持表鎖
在innodb引擎寫操作中兩種意向鎖（意向共享鎖和意向排他鎖）也是一種表鎖；
頁鎖
BDB引擎支持的鎖類型，引擎和相關資料都比較少，鎖粒度介于行和表之間
行鎖
1、行鎖和字面意思有一點區(qū)別，主要是區(qū)分于Oracle數(shù)據(jù)庫的行鎖是針對物理數(shù)據(jù)塊進行加鎖，而MySQL是針對索引進行加鎖的，也就意味著只有走索引才能加行鎖否則在需要加鎖的場景下就是表鎖
2、行鎖開銷大、加鎖慢、并發(fā)度高（沖突概率低）
3、行鎖同樣分為共享鎖和排他鎖
間隙鎖
間隙鎖是在RR級別下生效，在當前讀場景下會鎖定索引間的間隙，保證索引間的記錄不變；以防止插入或更新間隙之間的記錄；
而RR級別下的間隙鎖也就部分解決了幻讀的問題
nextKey lock
等于 行鎖+間隙鎖
鎖沖突表
. IX IS X S IX 兼容 兼容 沖突 沖突 IS 兼容 兼容 沖突 兼容 X 沖突 沖突 沖突 沖突 S 沖突 兼容 沖突 兼容 MVCC
MVCC翻譯過來是多版本并發(fā)控制；在RC和RR級別下生效，就是解決上面所提到不同隔離級別下的不可重復讀和幻讀的問題；
我們假設自己來來實現(xiàn)這個效果，一般的思路是不是在一個事物開啟的時候
1、通過記錄當前記錄的快照，在事物結束之前一直讀該快照來保障單次事物內結果是可預期的；
2、再加上鎖來阻止其他并發(fā)更新
思路是正確的但是MySQL對快照的處理上不像我們想象的一樣真的插入一條記錄而是通過undo_log+readview的機制來實現(xiàn)的
數(shù)據(jù)結構
innodb引擎在數(shù)據(jù)中增加了
事務號DB_TRX_ID（創(chuàng)建、更新、刪除）
回滾段指針DB_ROLL_PTR（用于追溯歷史版本數(shù)據(jù)）
DB_ROW_ID（隨著新行插入而單調遞增的行ID）
undo_log
insert/update/delete時會產生undo log
undo log中包含重建該行記錄被更新之前內容
readview
RC:事務中每條select語句都會創(chuàng)建一個快照(read view);
RR：事務在begin/start transaction之后的第一條select讀操作后, 會創(chuàng)建一個快照(read view), 將當前系統(tǒng)中活躍的其他事務記錄記錄起來 而這個區(qū)分也就決定了RR和RC兩個級別下可見性的區(qū)別；
補充說明一下，有一種說法是幻讀在當前讀場景下是解決了; 但是在快照讀的場景下還是還是存在對方已經提交但是在當前session讀不出來該數(shù)據(jù)，所以是部分解決了（對細節(jié)感興趣的話可以開兩個session 自行驗證一下);
索引
根據(jù)最上面的MySQL的體系架構圖可以得知數(shù)據(jù)最終是存儲在硬盤文件系統(tǒng)中的，計算機體系中硬盤數(shù)據(jù)是訪問最慢的，但是受限于內存數(shù)據(jù)的空間，在這種約束下通過針對具體業(yè)務場景，對高頻數(shù)據(jù)訪問場景通過增加索引的方式提高數(shù)據(jù)訪問效率
索引類型
聚簇索引
以主鍵創(chuàng)建的索引
1、聚集索引在葉子節(jié)點存儲的是表中的數(shù)據(jù)
非聚簇索引（二級索引）
非主鍵創(chuàng)建的索引
1、索引葉子節(jié)點存儲的是主鍵和索引列
2、在檢索列中包含非索引列時，需要用葉子節(jié)點的主鍵，再去表中檢索其他列（回表）
索引結構
數(shù)據(jù)結構應該都不陌生，而索引就是通過不同數(shù)據(jù)結構的特性：查詢復雜度、空間、查詢和新增性能等來做出的選擇；
在一個海量數(shù)據(jù)的場景下我們想要保障查詢的效率很直接的一種方式就是根據(jù)構建hash表；
另外一種相對可控的就是樹形結構可以通過控制樹的深度來保證查詢的效率；
B+ tree
它是一個矮胖子；左右兩個子樹的高度差的絕對值不超過1，并且左右兩個子樹都是一棵平衡二叉樹;
這樣的結構保障了查詢效率的相對高效和穩(wěn)定；
但是我們看一下在更新和新增場景下由于要維持以上特性，需要進行分裂或合并，這樣對插入性能來說是有一定損失的；
Hash表
hash表的好處是查詢復雜度理論值是o(1)，,但是帶來的問題如下
1、空間的消耗
2、不支持范圍和區(qū)間查詢
3、大數(shù)據(jù)量場景下的hash沖突，可能會導致退化成鏈表
在目前的MySQL版本中hash索引是MySQL自適應的無法干預
匹配原則
基于以上索引的設計可以看出，可以分析出在索引使用的過程中有如下特點
一個表可能創(chuàng)建多個索引
一次只能走一個索引
最左匹配
索引選擇上根據(jù) where 條件中的列字段，從左至右的匹配，直到遇到區(qū)間或范圍查詢列；
注意此處和SQL中where 后 in、=條件的列字段順序無關；
查詢列不參與計算
如果對列字段添加了計算函數(shù)，則無法走該字段的索引
where 和 order by limit 場景下的索引
在存在where 和 order by的場景下，本以為會按照where條件走索引，但是在實際情況下可能會發(fā)現(xiàn)最終索引是order by 字段，優(yōu)先選擇order by字段索引；
原因是：limit存在時，查詢的順序就有可能發(fā)生變化，查詢過程不是先通過where過濾再排序再limit而是根據(jù)order by索引反向取進而然后匹配where 條件看看是否滿足，有點類似于全表掃描了；
性能
即使是知道了以上各種原則，但在實際過程中還是可能遇到不符合預期的情況，這時就需要MySQL提供的debug利器執(zhí)行計劃了
執(zhí)行計劃
type index: 掃描全部索引樹
range: 掃描部分索引，索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于between、<、>等的查詢
ref: 非唯一性索引掃描，返回匹配某個單獨值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進行的查找
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描
const, system: 當MySQL對查詢某部分進行優(yōu)化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量。system是const類型的特例，當查詢的表只有一行的情況下， 使用system。
NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引。
possible_keys：可選的索引列
key：當前選擇的索引列
ref：連接匹配條件,如果走主鍵索引的話,該值為: const, 全表掃描的話,為null值
rows：掃描行數(shù)
extra： using index表示在相應的select中使用了覆蓋索引。 usingwhere表示存儲引擎搜到記錄后進行了后過濾(POST-FILTER)，如果查詢未能使用索引，usingwhere的作用只是提醒我們mysql要用where條件過濾z結果集。 using temporay表示用臨時表來存儲結果集，常見于排序和分組查詢。 usingfilesort，mysql中無法用索引完成的排序成為文件排序。
優(yōu)化
索引區(qū)分度
選擇高區(qū)分度列
覆蓋索引：查詢列盡量包含在索引中，否則需要回表
SQL優(yōu)化
大表翻頁：查詢列盡量是索引列，否則建議用join或子查詢的方式通過主鍵完成
樂觀鎖
select for update 和基于version的樂觀鎖,在極限沖突場景下到底哪種性能更高待驗證
數(shù)據(jù)量控制
分庫分表 冷熱數(shù)據(jù)分離
讀寫分離：備庫可以考慮承擔一部分讀流量
部署架構
物理或者邏輯架構的設計對于面向客戶提供的可靠性至關重要，作為數(shù)據(jù)的核心數(shù)據(jù)的不可丟失以及提供高性能的解決方案，從架構層面有一些設計方式；
主從
可靠性的一個重要保障方式就是做副本，一種經典的MySQL部署方式master-slave模式，部署兩個節(jié)點：master和slave；
master節(jié)點提供讀寫服務，通過解析binlog把數(shù)據(jù)變更復制到另外一臺slave節(jié)點；存在ms級的延時，在某種程度上保障了數(shù)據(jù)的物理隔離，降低單點故障帶來的損失；
分庫
分庫其實是一種邏輯上的架構，不過一般來說比較建議不同的邏輯庫可以散列分布，來提高吞吐性能，在各個邏輯庫上又可以做主從架構，每一個節(jié)點做一個備份。
不管是分庫還是分表都要注意維度，按照業(yè)務場景來評估，盡量保證檢索場景覆蓋了拆分維度。
異地多活 & 多副本一致
傳統(tǒng)上而言主備都是在同地域甚至是同一個機房來降低同步延時，但是在一些極限場景下對數(shù)據(jù)的可用性以及吞吐量要求更高，這個時候跨地域的邏輯庫以及多副本的一致性架構就開始出現(xiàn)了，阿里巴巴電商交易在15年開始規(guī)劃跨地域數(shù)據(jù)解決方案，其中主要用到的策略
1、按router路由，保障同一用戶落在同一地域
2、跨地域的數(shù)據(jù)同步
3、同一sequence生產方式（集中式、分布式）
當前基于最新分布式一致協(xié)議（Paxos）構建的 多副本強一致方案也有落地
其他數(shù)據(jù)庫
了解一些其他類型的數(shù)據(jù)庫，在做技術選型和決策的時候可能會有所幫助，目前主流的大概是這些
關系型數(shù)據(jù)庫
MySQL/Oracle/PG/SQLServer 等
非關系型數(shù)據(jù)庫
Hbase/MongoDB/Cassandra/LevelDB/Redis等
圖數(shù)據(jù)庫
Neo4j/GraphDB等
end：如果你覺得本文對你有幫助的話，記得關注點贊轉發(fā)，你的支持就是我更新動力。
