4.2-中間件之MySQL

4.2.1MySQL的基本知識

SQL語句

用于存取數據以及查詢、更新和管理關系數據庫系統。包括：DQL（select）、DML（insert,update,delete）、DDL（create,alter,drop）、DCL（grant,revoke）權限驗證。

MySQL體系結構

MySQL內部分層。

在最外層是server層，有幾個組件組成。其中SQL Interface用于對用戶發送來的sql進行詞法句法分析，生成語法樹。再由Parser對語法樹進行過濾，比如要查詢的字段在表中沒有，進行報錯。過濾后，由Optimizer制定執行計劃，選擇一個最佳的執行方案執行sql

在server層執行后，是存儲引擎層，用于具體執行server層傳入的執行方案。常用的存儲引擎是InnoDB。

在存儲引擎層下面，是文件系統層，存儲著物理數據。

MySQL內部連接池

mysql會為每一個連接分配一個線程，每一個線程以阻塞io的方式進行read、do_command，各線程的處理是并發的。

MySQL執行一條select語句的過程

在網絡層面，連接器接收連接，為連接分配線程。再先查詢緩存（mysql8.0后已經刪除這一步），若緩存沒有啊，通過SQL Interface分析器組件進行詞法句法分析生成語法樹，再通過優化器組件制定執行方案，執行器根據執行計劃，從存儲引擎獲取數據返回。

如果是預處理語句，比如有很多select，就可以寫成select? * from user where id?= ?;?用來接收參數，避免重復解析。會跳過連接器、分析器、優化器，直接進入到執行器執行計劃這一步。

數據庫設計的三范式

目的：避免數據冗余、允許空間占用

三范式內容：每一個列的字段，都是最簡的，不可再分的。如果有多個主鍵，那么表中其他鍵都需要完全依賴主鍵，且與主鍵直接相關，而不是間接相關。如果某表不能滿足上述要求，那么說明表中有冗余數據存在，需要對表進行拆分，以編號替代冗余部分。

反范式：范式可以簡單理解為 “時間換空間”，犧牲多張表跳轉查詢的時間來避免數據冗余空間浪費。但是如果冗余字段較少且追求查詢效率，可以用反范式。

MySQL刪除數據的幾種方式

1.drop(DDL):完全刪除表，包括數據和結構，表將不存在，不可回滾

2.truncate(DDL)：只刪除表中數據，變成空表，其中自增約束置為初始值。不可回滾，以頁為單位刪除

3.delete(DML)：刪除部分或全部數據，可以回滾，以行為單位刪除。

MySQL的高級查詢

1.分組+聚合查詢：通過group by對表分組后，計算sum()、avg()、max()、min()、count()，都是對分組后的各組內進行操作。

比如：select? `gender`,count(*) as num from `student` group by `gender`;

2.聯表查詢??

舉例：查詢 ‘數學’課程比‘語文’課程成績高的所有學生的學號；

分析：涉及兩張表，一個是分數表，一個是課程表。需要先從課程表中，通過課程名稱cname = '數學' 查詢出課程對應的cid，然后再到分數表中查詢對應課程的學生id以及分數num，這樣就構成了兩個表A與B，分別意味著報了數學的集合，報了語文的集合，表中只有兩個字段：sid與num。

再通過inner join取兩張表的交集，即數學語文都報了的人，構成一張新表，最后select 數學分數高于語文分數的學生學號即可。

select A.student_id from(select student_id,num from score where course_id =(select cid from course where cname = `數學`)） as Ainner join(select student_id,num from score where course_id =(select cid from course where cname = `語文`)）as Bon A.student_id = B.student_idwhere A.num > B.num;如果沒報語文課程的人也算在內，那就改成left join最后一行改成where A.num > IFNULL(B.num,0);

視圖是什么？

是一種虛擬存在的表，是一個邏輯表，其內容由查詢定義，本身并不包含數據。

優點：向上面例子中的表構建比較麻煩，如果每次都要寫這么多sql很冗余，可以直接構建一個視圖，且視圖的結構不會受原表的影響。

4.2.2MySQL索引原理以及SQL優化

索引是什么？

索引是一種有序的數據結構，按照單個或者多個列的值進行排序，用于提升搜索的效率。比如主鍵索引（非空+唯一）、唯一索引（可空）、普通索引（可空+可不唯一），組合索引（對表中多個列進行索引）。

索引常用的數據結構是B+樹：

在course表中的主鍵索引cid，作用就相當于一個map<int , course>，根據cid找到course，其中索引cid就是聚集索引B+樹中的非葉子節點，用于查找，葉子節點中存儲的就是course（數據）+cid（索引）

在course表中的普通索引tid,作用就相當于一個 multimap<int ,pairs<int,int>> ，根據tid找到鍵值對 <索引信息,主鍵信息>。其中索引tid就是輔助索引B+樹中的非葉子節點，用于查找，葉子節點中存儲的就是索引信息 + 主鍵信息，要拿到完整的數據要拿到主鍵信息，再到聚集索引B+樹中取出。但是如果要查詢的信息只有主鍵信息，或者其他輔助索引B+樹中本身就有的信息，則為覆蓋查詢，可以直接返回，速度很快，這也是不建議用select *，而具體寫出字段名的原因，可以避免回表查詢。

主鍵的選擇

有設置primary key則當然其為主鍵；如果沒設置則選一個非空唯一索引作為主鍵；如果非空唯一索引也沒有，則創建一個6字節的_rowid作為主鍵。

MySQL是磁盤IO，索引如何保證業務高效？

1.葉子節點的連續

在通過非葉子索引節點查找到目標數據所在的頁后，由于葉子節點間是有序且直接連接的，可以減少磁盤的尋道時間，直接把頁從磁盤讀入內存。

2.一次從磁盤中讀取多個頁到內存中緩存

對B+樹的操作，也不會馬上寫回磁盤，對于change buffer，會定期合并到buffer pool中，而buffer pool中的被修改后的臟數據，會集中direct io到磁盤中

MySQL的內存安全保證

在MySQL對磁盤的操作，如果是操作的聚集索引B+樹，則是先對buffer pool中的節點進行操作，如果操作的是輔助索引B+樹，則是對Change buffer中的節點操作。但是如果出現斷電，也不用擔心內存數據丟失，沒有及時刷盤導致數據錯誤，因為有Redo Log，aof型日志，記錄buffer中的變化信息，可以恢復buffer。

組合索引在查詢時候的規則:最左匹配原則

組合索引算作輔助索引。

如果將兩個索引（A，B）結合成一個索引，那么在select查詢的時候，會按照最左匹配原則進行查詢，即select中有沒有where帶上A的相關限定信息，如果有，則會按照其進行排序，如果沒有或者只有B的限定信息，則會進行全表查詢。

索引下推是什么？

對象是輔助索引，普通索引和聯合索引場景居多。

原本是在server層獲得存儲引擎層的數據后，根據索引條件過濾數據

索引下推機制，即將部分索引條件判斷下推到存儲引擎中過濾數據，最終返回給server層。

索引的作用是加速sql執行，有沒有索引失效的情況？

1.select ... where A and B 若A和B中，有一個字段不是索引，則會索引失效

2.如果讓索引字段參與運算，則會索引失效，如? where cid -1 = 2；

3.like 模糊查詢，以 %開頭，會進行全表查詢，索引失效，因為B+樹讀取到%不知道怎么查。

4.沒有遵循最左匹配原則

出現了sql比較慢的排查思路

先開啟慢查詢日志，找到SQL語句，然后通過explain字段，通過分析器看where、group by、order by后面的字段有沒有踩索引，如果沒有，看看是沒有創建索引還是索引失效。如果有踩索引但是速度慢，看看能否用in 或者 not in優化成聯表查詢，或者如果SQL語句太大了，考慮拆分，減少聯合查詢。

4.2.3MySQL事務原理

事務的特性和Redis中的事務有共同性，也是在有多條連接的情況下，利用事務性保證單個連接上用戶操作的不可分割。只需要在多條sql前加上 begin; 末尾加上 commit;事務的特征有：

1.原子性：undolog輔助實現原子性

2.一致性：數據庫的一致性：用戶處理完后比如有唯一約束的字段也不能出現重復的值。

? ? ? ? ? ? ? ? 邏輯的一致性：沒有臟讀、幻讀、不可重復讀的問題。

3.隔離性：指并發連接間的隔離性，適當的破壞一致性。有四個隔離級別，對應著四種并發程度。

4.持久性：redolog實現持久性

四個隔離級別（由低到高）

1.讀未提交：對讀操作不做任何處理，對寫操作自動加鎖

分析：存在臟讀問題。A事務寫還未結束，B事務因為讀無限制，看到A事務的中間結果，為臟讀。

2.讀已提交（RC）：讀操作采用MVCC策略。讀到的是最新的數據，寫操作自動加鎖

分析：避免了臟讀，A沒有結束，B讀不到他修改的結果。但是存在“不可重復讀”問題，A事務要是在B事務結束前結束了，那么B事務會看到最新的結果，即兩次B事務中的查詢結果不一致。

3.可重復讀（RR）：讀操作采用MVCC策略。讀到的是事務開始前版本的數據，寫操作自動加鎖

分析：避免了不可重復讀問題，兩次查詢的結果都是一樣的。但是存在“幻讀”問題，A事務和B事務都在執行過程中，B事務查詢到沒有ID為３的行數據，但緊接著A事務插入了一個ID為３的行數據，那么B事務在插入ID為３的行數據的時候，就會因為違反唯一約束而返回錯誤。

4.可串行化：讀寫都加鎖

分析：解決了幻讀的問題，即在B事務查詢是否有ID為3的行數據的時候，加上for update字段（x鎖）或者lock in share mode（S鎖/共享鎖）加鎖，這時候A事務想要添加ID為３的行數據會阻塞，知道B事務添加ID為３的行數據并且commit后，A事務會報錯。

MVCC策略

MVCC的核心思想：不加鎖，為每行數據保存多個版本（快照）。當一個事務需要讀取數據時，MVCC會提供一個符合該事務開始時間點的“數據快照”，而不是直接讀取當前最新的、可能正在被其他事務修改的數據。?? 這樣就實現了讀不加鎖，讀寫操作通常不會互相阻塞，極大地提升了并發性能。

Read View是事務在進行快照讀（普通SELECT語句）時產生的數據結構，它定義了當前事務能看到哪個版本的數據。
“讀已提交RC”是在每次讀取數據的時候，生成新的read_view

“可重復讀RR”是在啟動事務的時候，生成新的read view，一直使用到事務提交或者回滾

read view中有trx_id 、min_trx_id 、max_trx_id 、m_ids，事務間可見的情況只有：

trx_id < min_trx_id? ?和? min_trx_id < trx_id < max_trx_id && trx_id 不屬于 m_ids

事務隔離級別的實現

利用鎖機制實現，鎖的類型有：行級鎖（共享鎖、排他鎖），表級鎖（意向共享鎖、意向排他鎖）。

鎖算法有：Record記錄鎖（單個行記錄上的鎖），Gap間隙鎖（鎖定一個范圍，全開區間），Next-Key Lock（記錄所 + 間隙鎖，左開右閉）

鎖的對象：在RC和RR兩種隔離級別下

1.行級鎖是針對表的索引加鎖，包括聚合索引和輔助索引

2.表級鎖是針對頁或者表進行加鎖

3.Gap鎖通常是加在RR隔離級的情況下

舉例：

聚集索引，查詢命中，RC和RR兩種隔離級別都會對命中行加鎖。

聚集索引，查詢未命中，RC不加鎖，RR在未命中字段的左右區間加一個gap鎖

輔助非唯一索引，查詢命中，RC把輔助表命中的行鎖定，并且把聚合數據表中相關的行也鎖定。

RR在RC的基礎上，還在輔助表中，命中的兩個字段，產生的三個間隙，都加gap鎖。

死鎖的產生

情況1：相同表不同行加鎖順序相反，比如A事務update id1 id2? ，B事務update id2 id1；update和delete操作是會自動加上鎖的。解決：調整執行順序。

情況2：鎖沖突，在RR隔離級別下面，如果先持有了gap鎖，再想加意向鎖，就會產生鎖沖突。解決：降為RC隔離級別，這樣就沒有gap鎖了。

4.2.4MySQL緩存策略

在MySQL中，也會采用緩存，不同于buffer pool，而是一種類似Redis的內存緩存，他不僅有更快的速度，而且不像buffer pool受限于MySQL，雖然緩存中的數據必須在MySQL中存在，但是可以根據業務自定義熱點數據存入緩存。可以大大降低數據庫的讀壓力，對寫壓力沒有什么緩解，因為如果頻繁寫的話，我們用一個緩存還需要多考慮一致性的問題。

弊端是redis不支持事務的回滾，不能rollback，還有就是存在緩存和MySQL不一致的可能。

在引入Redis緩存后的寫操作如何實現？

1.追求安全

在每次DML寫操作，都先在Redis緩存中，刪除對應的行（比如要修改 id = 1的年齡，先在緩存中刪除id =1的行數據）。然后再到MySQL中進行操作，操作后，利用一個偽裝從數據庫的節點，從MySQL中拉取數據到Redis緩存中。這樣可以保證在修改期間，其余用戶不會讀到臟數據，但是在修改前還要額外操作緩存影響效率。

2.追求效率

DML操作也對Redis執行，但是加上一個pexpire，設置過期時間如200ms，這樣緩存中id=1的年齡就先被修改，隨后再修改MySQL中id=1的年齡。如果MySQL中修改失敗，那么Redis在200ms后也會刪除這個錯誤的數據，如果MySQL成功，那么就會通過偽造從數據庫進行數據同步。

此處的過期時間實際上 = MySQL傳輸時間 + MySQL處理時間 + MySQL同步時間

在這個期間內，別的用戶可能會讀到Redis中的臟數據。

MySQL到Redis的同步是如何實現的？

1.修改配置文件使transfer工具與MySQL和Redis建立連接。

2.配置熱點數據，只拉取熱點數據表的內容

3.用lua腳本設定同步的規則：怎么把MySQL的結構改成Redis的結構？transfer工具啟動連接到MySQL的bin.log文件，按行讀取里面的內容，改成Hash結構。獲取各列的字段值，設置key后再通過Redis中的命令將各值直接HSET寫入key對應的value。

還有哪些方式能提升MySQL的訪問性能？

1.讀寫分離

MySQL也有很多salve從數據庫用作備份，他們會不斷的主動讀取主數據庫的二進制log，保持數據一致性。這樣我們可以將業務的DML操作交給主數據庫，而讀操作提交給從數據庫，分擔主數據庫的讀壓力。但是從數據庫讀取的數據沒有強一致性，會有延遲存在，如果有強一致性需求，需要從主數據庫讀取。

2.數據庫連接池