一.基礎架構-一條SQL查詢語句是怎么執(zhí)行的？

1.Mysql的邏輯架構圖

大體分為Server層和存儲引擎層

1）Server層

包括連接器，查詢緩存，分析器，優(yōu)化器，執(zhí)行器等，涵蓋Mysql的大多數(shù)核心服務功能，所有跨存儲引擎的功能都在這一層實現(xiàn)，比如存儲過程，觸發(fā)器，視圖等

2）存儲引擎層

負責數(shù)據(jù)的存儲和提取。架構模式是插件式的，支持InnoDB,MyISAM,Memory等多個存儲引擎(執(zhí)行create table建表的時候，默認使用的就是InnoDB)

• 不同的存儲引擎共用一個Server層

有時候MYSQL的內存漲的特別快，解決方法有：

• 定期斷開長連接。使用一段時間之后斷開連接，之后要查詢的時候重連
• 如果是MySQL 5.7以上的版本，可以在每次執(zhí)行一個比較大的操作的時候通過執(zhí)行mysql_reset_connection來重新初始化連接資源

2.查詢緩存

查詢緩存每次只要是有一個表要更新，這個表上所有的查詢緩存都會被清空。除非是一張靜態(tài)表

3.分析器--》優(yōu)化器--》執(zhí)行器

4.MySQL是如何執(zhí)行一條SQL的

二.日志系統(tǒng)-一條SQL更新語句是如何執(zhí)行的？

1.更新

InnoDB引擎先把記錄寫到redo log里面，并更新內存。

2.redo log和binlog都可以用于表示事務的提交狀態(tài)，而兩階段提交就是讓兩個狀態(tài)保持邏輯上的一致

3.redo log用于保證crash-safe的能力

4.redolog可以去掉嗎？

• redolog只有InnoDB有，別的引擎沒有
• redolog是循環(huán)寫的，不持久保存，binlog的歸檔的功能，redolog是沒有的

5.redo log與binlog的區(qū)別

• redo log是在InnoDB存儲索引層產生，而binlog是mysql數(shù)據(jù)庫的上層產生，并且二進制日志不僅僅針對InnoDB存儲索引，MySQL數(shù)據(jù)庫中的任何存儲索引對于數(shù)據(jù)庫的更改都會產生二進制日志
• 兩種日志記錄的內容形式不同，MySQL的binlog是邏輯日志，對應的為SQL語句。InnoDB存儲引擎層面的重做日志是物理日志
• 兩種日志與記錄寫入磁盤的時間點不同，二進制日志只是在事務提交完成之后進行一次寫入。而innoDB的重做日志不斷被寫入
• redo log是循環(huán)使用的。binlog在寫滿或者重啟之后，會生成新的binlog
• binlog可以作為恢復數(shù)據(jù)使用，主從復制搭建

三.事務隔離

1.隔離性與隔離級別

• ACID特性(原子性，一致性，隔離性，持久性)
• 同一條記錄在系統(tǒng)中可能存在多個版本，就是數(shù)據(jù)庫的多版本并發(fā)控制

2.事務隔離級別

• 讀未提交：別人改數(shù)據(jù)的事務尚未提交，我在我的事務中也能讀到

• 讀已提交：別人改數(shù)據(jù)的事務已經提交，我在我的事務中才能讀到

• 可重復讀：別人改數(shù)據(jù)的事務已經提交，我在我的事務中也不去讀(同一行在同一個事務下無論怎么讀取都是同一個結果[除非自己把他改了])

• 串行：我的事務尚未提交，別人就別想改數(shù)據(jù)

  這4種隔離級別，并行性能依次降低，安全性依次提高。

3.幻讀&&可重復讀

關于幻讀，可重復讀的真實用例是什么

四.索引

1.索引的出現(xiàn)時為了提高數(shù)據(jù)查詢的效率，就像書的目錄一樣

存儲引擎 描述
ARCHIVE -----用于數(shù)據(jù)存檔（行被插入后不能再修改）
BLACKHOLE-----丟棄寫操作，讀操作會返回空內容
CSV-----在存儲數(shù)據(jù)時，以逗號分隔各個數(shù)據(jù)項
FEDERATED-----用來訪問遠程表
InnoDB-----具備外鍵支持功能的事務存儲引擎
MEMORY-----置于內存的表
MERGE-----用來管理多個MyISAM表構成的表集合
MyISAM-----主要的非事務處理存儲引擎
NDB-----MySQL集群專用存儲引擎

2.三種常見的索引模型

• 哈希表：鍵值對存儲數(shù)據(jù)的結構，適用于只有等值查詢的場景(鏈表法處理沖突)
• 有序數(shù)組：在等值查詢和范圍查詢場景中的性能非常優(yōu)秀，但是只是適合于靜態(tài)存儲引擎
• 搜索樹：讀寫性能高，適配磁盤的訪問模式

3.InnoDB的索引模型

• 每一個索引在InnoDB中對應一顆B+樹

• 主鍵索引與非主鍵索引

  • 主鍵索引的葉子節(jié)點的內容是整行數(shù)據(jù)。在InnoDB中，主鍵索引也被稱為聚簇索引
  • 非主鍵索引的葉子節(jié)點內容是主鍵的值。在InnoDB中，非主鍵索引也被稱為二級索引

• 基于主鍵索引和普通索引的查詢有什么區(qū)別？

  • 主鍵查詢方式，只需要搜索主鍵這一個B+樹
  • 普通索引查詢方式，需要回表[回到主鍵索引樹搜索的過程，叫做回表]

  基于非主鍵索引的查詢需要多掃描一顆索引樹

• 主鍵長度越小，普通索引的葉子節(jié)點就越小，普通索引占用的空間也就越小

• 適合用業(yè)務字段直接做主鍵的場景：

  • 只有一個索引
  • 該索引必須是唯一索引

• N叉樹的N值在5.6之后是可以通過page大小來間接控制的(默認情況下，表空間中的頁大小都為 16KB，當然也可以通過改變 innodb_page_size 選項對默認大小進行修改，需要注意的是不同的頁大小最終也會導致區(qū)大小的不同)

• B+樹的葉子節(jié)點是page （頁），一個頁里面可以存多個行

• drop主鍵索引會導致其他索引失效，但drop普通索引不會

• B+樹這種索引結構，可以利用索引的“最左前綴”

• MySQL 5.6引入的索引下推優(yōu)化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)

• 重建索引的過程會創(chuàng)建一個新的索引，把數(shù)據(jù)按照順序插入，這樣頁面的利用率最高，也就是索引最緊湊

4.in和between

k in (1,2,3,4,5)相當于從根遍歷索引樹5次，而between 1 and 5 是范圍查詢，每一個葉子節(jié)點都有一個額外指針，指向下一個葉子節(jié)點，而且它們的值都是有序的，直接遍歷葉子節(jié)點的鏈表就可以

5.ACID里面，原子性和持久性做不到；隔離性只能實現(xiàn)基本用不到的串行化；一致性在正常運行的時候依賴于串行化，在異常崩潰的時候也不能保證

6.聯(lián)合索引

聯(lián)合索引是依次按照聯(lián)合字段的先后順序，依次進行排序。如a,b,c三個字段是聯(lián)合索引，則葉子節(jié)點存儲的是三個字段的數(shù)據(jù)，且按照先后順序進行排序；而非葉子節(jié)點存儲的是第一個關鍵字的索引。故當執(zhí)行查詢的時候，因為聯(lián)合索引中是先根據(jù)a進行排序的，如果a沒有確定，直接對b或c進行查詢的話，就相當于是亂序查詢，因此聯(lián)合索引無法生效，此時就相當于是全表查詢

7.索引優(yōu)化為全覆蓋索引

• 增加索引中的字段，讓索引字段覆蓋SQL語句中使用的所有字段
• 減少SQL語句中使用的字段，使SQL需要的字段都包含在現(xiàn)有索引中

8.節(jié)點的讀取成本[為什么數(shù)據(jù)庫索引使用B+樹而不是二叉樹呢？]

現(xiàn)代的操作系統(tǒng)中，把數(shù)據(jù)從外存讀到內存使用的單位一般來說被稱為"頁"，每次讀取數(shù)據(jù)都需要讀入整數(shù)個的“頁”。數(shù)據(jù)庫索引使用節(jié)點大小恰好等于操作系統(tǒng)一頁大小的B+樹是實現(xiàn)效率最高的選擇

9.面試相關的問題

• 聚集索引：

  • 如果我們定義了主鍵，那么會選擇主鍵作為聚集索引
  • 如果沒有顯式定義主鍵，則會選擇第一個不包含NULL值得唯一索引作為主鍵索引
  • 如果也沒有這樣的唯一索引，則會選擇內置6字節(jié)長的ROWID作為隱含的聚集索引

• 數(shù)據(jù)記錄本身存于主索引的葉子節(jié)點上，這就要求同一個葉子節(jié)點內各條數(shù)據(jù)記錄按照主鍵順序存放，每次有一個新的記錄插入的時候，會根據(jù)主鍵將其插入適當?shù)慕Y點和位置，如果頁面達到裝載因子，則開辟一個新的頁

• • 如果使用自增主鍵，則每次插入新的記錄，記錄就會順序添加到當前索引結點的后序位置，當一頁寫滿的時候，就會自動開辟新的頁
  • 如果使用非自增主鍵，由于每次插入的主鍵類似于隨機，因此每次新的記錄都要被插入到索引頁的中間某個位置

• 為什么使用數(shù)據(jù)索引能夠提高效率？

  • 數(shù)據(jù)索引的存儲是有序的，在有序的情況下，通過索引查詢一個數(shù)據(jù)是無需遍歷索引記錄的，極端情況下，查詢效率近似于二分查找

• B+樹中所有的葉節(jié)點中包含了全部的關鍵字的信息，以及指向含有這些關鍵字記錄的指針，。所有的非終端節(jié)點可以看成是索引部分，結點中僅僅含有其子樹根節(jié)點中最大或最小的關鍵字

• 為什么B+樹比B樹更適合用在數(shù)據(jù)庫索引中？

  • 磁盤讀寫代價更低
  • 查詢效率穩(wěn)定，任何一個都是從根節(jié)點到葉子節(jié)點

• 聯(lián)合索引:

  • 索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3種組合進行查找（類似于電話簿，知道姓比名字更重要，確切來說只知道名毫無用處）

• 什么時候應該少建索引？

  • 表記錄太少
  • 經常插入，刪除，修改的表
  • 數(shù)據(jù)重復且分布平均的表字段
  • 經常和主字段一塊查詢但是主字段索引值較多的表字段

• 表分區(qū)：從邏輯上看只是一張表，但是從底層卻是多個物理分區(qū)組成

• 分區(qū)和分表：分區(qū)從邏輯上只有一張表，而分表則是將一張表分解為多張表

• 表分區(qū)的好處:

  • 存儲更多數(shù)據(jù)：分區(qū)表的數(shù)據(jù)可以分布在不同的物理設備上
  • 優(yōu)化查詢：可以在多個分區(qū)上并行處理，最后匯總結果
  • 分區(qū)表更容易維護
  • 避免某些特殊的瓶頸

• key和index的區(qū)別：key是數(shù)據(jù)庫的物理結構，有約束與索引；index是數(shù)據(jù)庫的物理結構，只是輔助查詢的

• MySQL中的MyISAM和InnoDB的區(qū)別有哪些？

  • InnoDB支持事務，而MyISAM不支持
  • InnoDB支持外鍵，而MyISAM不支持。對于一個包含外鍵的InnoDB轉化為MyISAM會失敗
  • InnoDB是聚集索引，數(shù)據(jù)文件是和索引綁在一起的，必須要有主鍵，通過主鍵索引效率很高，但是會回表；而MyISAM是非聚集索引，數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)文件的指針
  • InnoDB不支持全文索引，而MyISAM支持全文索引，查詢效率上MyISAM要高
  • 如果表中絕大多數(shù)都是讀查詢，可以考慮MyISAM，如果既有讀寫也挺頻繁，則使用InnoDB
  • InnoDB支持行鎖，而MyISAM支持表鎖

五.鎖相關

1.鎖的分類

• 全局鎖

  • 全局鎖為對整個數(shù)據(jù)庫實例加鎖，使用的場景為做全庫邏輯備份，也就是把整庫每個表都select出來存成文本[備份過程中整個庫完全只是處于只讀狀態(tài)]

  如果整個庫都是只讀狀態(tài)，則：

  • 如果只是在主庫上做備份，那么在備份期間都不能執(zhí)行更新，業(yè)務基本就得停擺
  • 如果在從庫上做備份，那么備份期間從庫不能執(zhí)行從庫同步過來的binlog，會導致主從延遲

• 表級鎖

  MySQL中表級別的鎖有兩種：

  • 表鎖[讀鎖會阻塞寫，不會阻塞讀。而寫鎖則會把讀寫都阻塞]

  • 元數(shù)據(jù)鎖

  表鎖的使用場景：

  • 全表更新
  • 多表查詢

• 行鎖:針對數(shù)據(jù)表中行記錄的鎖

  • 在InnoDB事務中，行鎖是在需要的時候才加上的，但是并不是不需要了就立即釋放，而是等到事務結束之后才釋放。這就是兩階段鎖協(xié)議

  • 最可能影響并發(fā)度的鎖盡可能的往后放

  • 多個事務操作同一行數(shù)據(jù)的時候，后來的事務處于阻塞等待狀態(tài)。這樣可以避免臟讀等數(shù)據(jù)一致性的問題。后來的事務可以操作其他行的數(shù)據(jù)，解決了表鎖的高并發(fā)性能低的問題

  行鎖的優(yōu)化：

  • 盡可能讓所有的數(shù)據(jù)檢索都通過索引來完成
  • 盡可能避免間隙鎖帶來的性能下降
  • 盡可能減少事務的粒度
  • 盡可能低級別事務隔離，隔離級別越高，并發(fā)的處理能力越低

  死鎖：當并發(fā)系統(tǒng)中不同線程出現(xiàn)循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源時，就會導致這幾個線程都進入無限等待的狀態(tài)

  深入理解數(shù)據(jù)庫行鎖與表鎖

  平衡二叉樹，B樹，B+樹

2.事務的隔離級別

• InnoDB里面每一個事務有一個唯一的事務ID，叫做transaction id。它是在事務開始的時候向InnoDB的事務系統(tǒng)申請的，是按照申請順序嚴格遞增的

• InnoDB利用了“所有的數(shù)據(jù)都有多個版本”的特性，實現(xiàn)了“秒級創(chuàng)建快照”的能力

• 更新數(shù)據(jù)都是先讀后寫的，而這個讀，只能讀當前的值，稱為“當前讀”

• InnoDB的行數(shù)據(jù)有多個版本，每一個數(shù)據(jù)版本都有自己的row trx_id，每一個事務或者語句都有自己的一致性視圖。普通查詢語句是一致性讀，一致性讀會根據(jù)row trx_id和一致性視圖確定數(shù)據(jù)版本的可見性

六.實戰(zhàn)部分

1.普通索引和唯一索引

• InnoDB的數(shù)據(jù)是按照數(shù)據(jù)頁為單位進行讀寫的。也就是說，當需要讀一條記錄的時候，并不是將這個記錄從磁盤讀出來，而是以頁為單位，將其整體讀入內存

• 使用前綴索引，定義好長度，就可以做到既節(jié)省空間，又不用額外增加太多的查詢成本

• 身份證的存儲方式：

  • 倒序建立索引存儲：無法使用索引方式
  • 使用hash字段：只是支持等值查找

• count(*)的實現(xiàn)方式

  • MyISAM引擎把一個表的總行數(shù)存放在了磁盤上，執(zhí)行count(*)的時候直接返回這個數(shù)
  • InnoDB引擎在執(zhí)行的時候需要把數(shù)據(jù)一行一行的從引擎里面讀出來，然后累加計數(shù)

  在保證邏輯正確的前提下，盡量減少掃描的數(shù)據(jù)量，是數(shù)據(jù)庫系統(tǒng)設計的通用法則之一

  解決方法：

  • 用緩存系統(tǒng)保存計數(shù)：緩存系統(tǒng)可能會丟失，而且更新會有時延
  • 用數(shù)據(jù)庫保存計數(shù)--》單獨設置一張計數(shù)表：InnoDB支持崩潰恢復不丟數(shù)據(jù)的

2.幻讀

• InnoDB的默認事務隔離級別是可重復讀

3.

• 只要是redo log和binlog保證持久化到磁盤，就能確保MySQL異常重啟之后，數(shù)據(jù)可以恢復
• InnoDB表的數(shù)據(jù)總是有序存放的，而內存表的數(shù)據(jù)是按照寫入順序存放的