1、MySQL執行計劃
執行計劃——一條sql語句的執行過程
explain
官網
2、索引
資料結構
設計原則
優化
失效
回表
索引覆寫
最左比對
索引下推
聚簇索引
用途：提高sql語句執行效率
索引和實際的資料都是存儲在磁盤的，隻不過在進行資料讀取的時候會優先把索引加載到記憶體中
存儲引擎：不同的資料檔案在磁盤的不同組織形式
3、索引儲存資料的格式
格式——>K-V——>資料結構——>hash表——>樹——>為什麼是B+樹？
當索引非常大的時候，分塊讀取
分而治之
IO問題：減少IO量/減少IO次數
作業系統
局部性原理：
- 時間局部性：之前被通路過的資料很有可能再次被通路
-
空間局部性：資料和程式都有聚內建群的傾向，具備某些特征的資料可以放在一起
磁盤預讀：記憶體跟磁盤在進行互動的時候有一個最小的邏輯單元，這個機關稱之為頁，或者datapage，大小一般是4k或者8k，由作業系統決定，我們在進行資料讀取的時候，一般會讀取頁的整數倍，也就是4k、8k、16k，innodb存儲引擎在進行資料加載的時候讀取的是16kb的資料
hash表
需要比較好的hash算法，如果算法不好的話，會導緻hash碰撞，hash沖突，導緻資料散列不均勻
當需要進行範圍查找的時候需要挨個周遊，效率比較低
memory的存儲引擎支援的就是hash索引，同時注意innodb存儲引擎支援自适應hash
二叉樹
BST
AVL
紅黑樹
劣勢：當需要向這些樹中插入更多資料的時候，會導緻目前樹變得非常高，加大讀取的次數，影響查詢效率
MySQL索引資料結構——B+Tree
B+Tree是在BTree的基礎之上做的一種優化，變化如下：
1、B+Tree每個節點可以包含更多的節點，這個做的原因有兩個，第一個原因是為了降低樹的高度，第二個原因是将資料範圍變為多個區間，區間越多，資料檢索越快
2、非葉子節點存儲key，葉子節點存儲key和資料
3、葉子節點兩兩指針互相連接配接（符合磁盤的預讀特性），順序查詢性能更高
注意：在B+Tree上有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即資料節點）之間是一種鍊式環結構，是以可以對B+Tree進行兩種查找運算：一種是對于主鍵的範圍查找和分頁查找，另一種是從根節點開始，進行随機查找
一般情況下，三到四層的B+Tree足以支援千萬級别的資料存儲
key要盡可能少的占用存儲空間
4、聚簇索引和非聚簇索引
索引的分類：
主鍵索引——>主鍵字段
唯一索引——>唯一字段
普通索引——>非主鍵非唯一
全文索引——>全文檢索——>lucene、solr、es
組合索引——>表中的多個字段值可以共同構成索引
索引并不是越多越好：
1、索引的維護會非常麻煩
2、占用的存儲空間變大，會導緻io增多
1、一個表中隻能有一個索引嗎？
可以按照需求來建立多個索引，但不要太多
2、每一個索引是一棵B+樹，還是所有的索引共用一棵B+樹？
一個索引一棵B+樹
3、如果有多棵B+樹的話，那麼資料存儲幾份？
1份
4、那麼其他索引的葉子節點放什麼呢？
主鍵（不太精準）跟資料綁定存儲的索引列的值
聚簇索引：資料跟索引綁定在一起的叫做聚簇索引
非聚簇索引：資料跟索引分開存儲的叫做非聚簇索引
資料跟索引存儲在一起的叫做聚簇索引，沒有存儲在一起的叫做非聚簇索引
innodb存儲引擎在進行資料插入的時候，資料必須要跟某一個索引列存儲在一起，這個索引列可以是主鍵，如果沒有主鍵，選擇唯一鍵，如果沒有唯一鍵，選擇6位元組的rowid來進行存儲
資料必定是跟某一個索引綁定在一起的，綁定資料的索引叫做聚簇索引
其他索引的葉子節點中存儲的資料不再是整行的記錄，而是聚簇索引的id值
innodb中既有聚簇索引也有非聚簇索引
myisam中隻有非聚簇索引
存儲引擎：不同的資料檔案在磁盤的不同組織形式
索引的選擇性——>基數Distinct Value——>dv個數/count——>>80%适合建立索引
id,name,age,gender
id是主鍵，name為普通索引
id是聚簇索引，name對應的索引的B+樹上的葉子節點存儲的就是id值
回表
id,name,age,gender
id主鍵，name普通索引
select * from table where name=‘zhangsan’;
先根據nameB+樹比對到對應的葉子節點，查詢到對應行記錄的id值，再根據id去id的B+樹中檢索整行記錄，這個記錄就稱為回表，要盡量避免回表操作
索引覆寫
id,name,age,gender
id主鍵，name普通索引
select id,name from table where name=‘zhangsan’;
根據name的值去nameB+樹檢索對應的記錄，能擷取到id的屬性值，索引的葉子節點中包含了查詢的所有列，此時不需要回表，這個過程叫做索引覆寫，using index的提示資訊，推薦使用，在某些場景中，可以考慮将要查詢的所有列都變成組合索引，此時會使用索引覆寫，加快查詢效率
最左比對
建立索引的時候可以選擇多個列來共同組成索引，此時叫做組合索引或者聯合索引，要遵守最左比對原則
id,name,age,gender
id主鍵，name,age組合索引
select * from table where name='zhangsan' and age=12;
select * from table where name='zhangsan';
select * from table where age=12;
select * from table where age=12 and name='zhangsan';
索引下推
沒有索引下推之前：
先根據name從存儲引擎中拉取資料到server層，然後在server層中對age進行資料過濾
有了索引下推之後：
根據name和age兩個條件來做資料篩選，将篩選之後的結果傳回給server層
client（最上面）
server（中間）
存儲引擎（最下面）
索引下推預設開啟
架構：
用戶端：jdbc、navicat、sqlyog
Server端：連接配接器——>管理連接配接、驗證權限
分析器——>詞法分析、文法分析——>AST——>antlr、calcite
優化器——>RBO規則、CBO成本
執行器——>執行具體的sql語句
存儲引擎
針對特定場景，進行索引優化
表：cityName(Varchar) 5 20——>字首——>前3,4,5,6,7
alter table citydemo add key(city(7)
hive
mysql
OLAP——>聯機分析處理——>資料倉庫——>hive——>對曆史資料做分析，産生決策性的影響
OLTP——>聯機事務處理——>資料庫——>mysql、oracle、db2——>支撐業務系統，在很短時間内傳回結果
kylin——>cube，将所有可能預先存在的結果都計算出來，直接去拿結果，而不是臨時計算。measure、daimond
5、如何回答面試中問到的優化問題？
技術+表達方式
工作中做過很多sql的優化，一般的優化我們并不是出現了問題才進行優化的，在進行資料庫模組化和資料庫設計的時候會預先考慮到一些優化問題，比如表字段的類型、長度等等，包括建立合适的索引等方式，但是這種方式隻是提前的預防，并不一定能解決所有的問題，是以當我們生産環境中已經出現sql問題之後我會從資料庫的性能監控，索引的建立和維護、sql語句的調整，參數的設定、架構的調整等多個方面去進行綜合考慮，性能監控會選擇show profiles,performance_schema來進行監控，索引。。。參數。。。在我最近做的一個XX項目中，出現了XXX問題，我通過分析執行計劃以及XXX的方式順利解決了這個問題，并且在公司做了技術分享，詳細了解對應資料的知識，在另外的項目中，。。。。
6、事務、鎖、MVCC基本概念
ACID——四個特性實作的原理
鎖——樂觀鎖、悲觀鎖、間隙鎖、行鎖、表鎖、記錄鎖、自增鎖、意向鎖
MVCC——多版本并發控制——提高讀寫效率
鎖
按粒度來分：行鎖、表鎖
按讀寫來分：共享鎖、排他鎖
意向鎖（表鎖的一類）
樂觀鎖、悲觀鎖
記錄鎖、間隙鎖、臨鍵鎖
目前讀：讀取的是資料的最新版本，總是讀取到最新的資料——select … lock in share mode，select … for update，update，delete，insert
快照讀：讀取的是曆史版本的記錄——select
四個隔離級别
讀未送出
讀已送出（RC）
可重複讀（RR）——預設隔離級别
串行化
讀讀：不會存在任何問題的，也不需要并發控制
讀寫：會有線程安全問題，會造成髒讀、幻讀、不可重複讀，需要進行并發控制（鎖）MVCC
寫寫：會有線程安全問題，存在更新丢失問題
MVCC：多版本并發控制，解決資料并發讀寫問題
能否讀取到剛剛修改的最新的結果值？
RC：可以讀取到最新的結果記錄
RR：不可以讀取到最新的結果記錄
⬇
MVCC
⬇
可見性算法
7、MVCC是如何實作的？
MVCC多版本并發控制指的是維持一個資料的多個版本，使得讀寫操作沒有沖突，快照讀是mysql為了實作MVCC的一個非阻塞讀功能
第一部分：隐藏字段
每一行記錄上都會包含幾個使用者不可見的字段
DB_TRX_ID：建立或者最後一次修改該記錄的事務id
DB_ROW_ID：隐藏主鍵
DB_ROLL_PTR：復原指針，指向這條記錄的上一個版本——undolog
第二部分：undolog——復原日志（儲存資料的曆史版本狀态）
當不同的事務對同一條記錄做修改的時候，會導緻該記錄的undolog形成一個線性表，也就是連結清單，連結清單的鍊首是最新的曆史記錄，而鍊尾是最早的曆史記錄
現在有了事務4，那麼事務4讀取到的資料是哪一個版本的資料？一定有對應的規則，需要按照規則來進行判斷讀取
第三部分：readview——>事務在進行快照讀的時候産生的讀視圖，來進行可見性判斷，可見性判斷是由可見性算法來确定的
trx_list——目前系統活躍的事務id
up_limit_id——活躍清單中事務最小的id
low_limit_id——目前系統尚未配置設定的下一個事務id
能否讀取到剛剛修改的記錄值？
可見性算法
1、首先比較DB_TRX_ID<up_limit_id，如果小于，則目前事務能看到DB_TRX_ID所在的記錄，如果大于等于進入下一個判斷
2、接下來判斷DB_TRX_ID>=low_limit_id，如果大于等于則代表DB_TRX_ID所在的記錄在Read View生成後才出現的，那麼對于目前事務肯定不可見，如果小于，則進入下一步判斷
3、判斷DB_TRX_ID是否在活躍事務中，如果在，則代表在Read View生成時刻，這個事務還是活躍狀态，還沒有commit，修改的資料，目前事務也是看不到，如果不在，則說明這個事務在Read View生成之前已經開始commit，那麼修改的結果是能夠看見的
經過目前可見性算法的判斷之後，可以得到結論，能看到修改的記錄
readview生成的時機是不同的
RC：每次在進行快照讀的時候都會生成新的readview
RR：隻有在第一次進行快照讀的時候才會生成readview，之後的讀操作都會用第一次生成的readview
如果目前的所有操作都是目前讀，那麼是不會産生幻讀問題，隻有目前讀和快照讀一起使用的時候才會産生幻讀問題
加鎖
select * from user where age = 20 for update
8、四個特點
原子性——>要麼全部成功，要麼全部失敗——>它是通過undolog來實作的
原子性實作原理：undolog
- undolog是為了實作事務的原子性，在MySQL資料庫InnoDB存儲引擎中，還用undo log來實作多版本并發控制（簡稱MVCC）
- 在操作任何資料之前，首先将資料備份到一個地方（這個存儲資料備份的地方稱為undo log）。然後進行資料的修改。如果出現了錯誤或者使用者執行了ROLLBACK語句，系統可以利用undolog中的備份将資料恢複到事務開始之前的狀态
- 注意：undolog是邏輯日志，可以了解為：
- 當delete一條記錄時，undolog中會記錄一條對應的insert記錄
- 當insert一條記錄時，undolog中會記錄一條對應的delete記錄
-
當update一條記錄時，它記錄一條對應相反的update記錄
undolog會形成一個連結清單，鍊首存儲的是最新的舊紀錄，鍊尾存放的是最舊的舊記錄
undolog不會無限膨脹下去，會存在一個背景線程，purge線程，當發現目前記錄不需要復原且不需要參與MVCC的時候，就會把資料給清理掉
隔離性——>MVCC
持久性——>redolog——>兩階段送出——>WAL(write ahead log)——>先寫日志，再寫資料
一緻性
随機讀寫——>慢
順序讀寫——>快
因為随機讀寫的效率要低于順序讀寫，為了保證資料的一緻性，可以先将資料通過順序讀寫的方式寫到日志檔案中，然後再将資料寫入到對應的磁盤檔案中，這個過程順序的效率要遠遠高于随機的效率，換句話說，如果實際的資料沒有寫入到磁盤，隻要日志檔案儲存成功了，那麼資料就不會丢失，可以根據日志來進行資料的恢複
binlog（mysql server）
innodb（插件引擎）redolog、undolog
因為兩種日志屬于不同的元件，是以為了保證資料的一緻性，要保證binlog和redolog一緻，是以有了二階段送出的概念
資料更新的流程
執行流程：
1、執行器先從引擎中找到資料，如果在記憶體中直接傳回，如果不在記憶體中，查詢後傳回
2、執行器拿到資料之後會先修改資料，然後調用引擎接口重新寫入資料
3、引擎将資料更新到記憶體，同時寫資料到redo中，此時處于prepare階段，并通知執行器執行完成，随時可以操作
4、執行器生成這個操作的binlog
5、執行器調用引擎的事務送出接口，引擎把剛剛寫完的redo改成commit狀态，更新完成
Redo log的兩階段送出
- 先寫redolog後寫binlog：假設在redolog寫完，binlog還沒有寫完的時候，MySQL程序異常重新開機。由于我們前面說過的，redolog寫完之後，系統即使崩潰，仍然能夠把資料恢複回來，是以恢複後這一行c的值是1.但是由于binlog沒寫完就crash了，這時候binlog裡面就沒有記錄這個語句。是以，之後備份日志的時候，存起來的binlog裡面就沒有這條語句。然後你會發現，如果需要用這個binlog來恢複臨時庫的話，由于這個語句的binlog丢失，這個臨時庫就會少了這一次更新，恢複出來的這一行的c的值就是0，與原庫的值不同
- 先寫biinlog後寫redolog：如果在binlog寫完之後crash，由于redolog還沒寫，崩潰恢複以後這個事務無效，是以這一行c的值是0。但是binlog裡面已經記錄了”把c從0改成1“這個日志。是以，在之後用binlog來恢複的時候就多了一個事務出來，恢複出來的這一行c的值就是1，與原庫的值不同。
9、MySQL分布式叢集
主從複制
Master當發生data change之後，會寫入binlog中去，I/O thread線程會讀取我們的日志，放入Relaylog中去，進行SQL thread重放之後，同步到slave中
1、master将操作語句記錄到binlog日志中，然後授予slave遠端連接配接的權限（master一定要開啟binlog二進制日志功能；通常為了資料安全考慮，slave也開啟binlog功能）
2、slave開啟兩個線程：IO線程和SQL線程。其中：IO線程負責讀取master的binlog内容到中繼relay log裡；SQL線程負責從relay log日志裡讀出binlog内容，并更新到slave的資料庫裡，這樣就能保證slave資料和master資料保持一緻了
3、mysql複制至少需要兩個mysql的服務，當然mysql服務可以分布在不同的伺服器上，也可以在一台伺服器上啟動多個服務
4、mysql複制最好確定master和slave伺服器上的mysql版本相同（如果不能滿足版本一緻，那麼要保證master主節點的版本低于slave從節點的版本）
5、master和slave兩節點間的時間需同步
MTS——>組送出
讀寫分離
用戶端隻能感覺到隻有一個資料庫
這時候需要一個代理
mycat、shardingsphere
分庫分表
垂直分表、水準分表
分片鍵
雪花算法