這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於資料庫廠商面試題的一些總結，下面一起來看一下，希望對大家有幫助。
推薦學習：mysql影片教學
- 第一範式（1NF）：指表的列不可再分，資料庫中表格的每一列都是不可分割的基本資料項，同一列中不能有多個值；
- 第二範式（2NF）：在1NF 的基礎上，還包含兩部分的內容：一是表必須有一個主鍵；二是表中非主鍵列必須完全依賴主鍵，不能只依賴主鍵的一部分；
- 第三範式（3NF）：在 2NF 的基礎上，消除非主鍵列對主鍵的傳遞依賴，非主鍵列必須直接依賴主鍵。
- BC範式（BCNF）：在3NF 的基礎上，消除主屬性對於碼部分的傳遞依賴
2.1、客戶端的資料庫驅動與資料庫連接池：
（1）客戶端與資料庫通訊前，透過資料庫驅動與MySQL建立連接，建立完成之後，就發送SQL語句
（2）為了減少頻繁創建和銷毀連接造成系統性能的下降，透過資料庫連接池維護一定數量的連接線程，當需要進行連接時，就直接從連線池中獲取，使用完畢之後，再歸還給連線池。常見的資料庫連線池有Druid、C3P0、DBCP
2.2、MySQL架構的Server層的執行過程：
（1）連接器：主要負責跟隨客戶端建立連線、取得權限、維持和管理連線
（2）查詢快取：優先在快取中進行查詢，如果查到了則直接傳回，如果快取中查詢不到，在去資料庫中查詢。
MySQL快取是預設關閉的，也就是說不建議使用緩存，並且在MySQL8.0 版本已經將查詢快取的整個功能刪掉了。這主要是它的使用場景限製造成的：
- 先說下快取中資料儲存格式：key（sql語句）- value（資料值），所以如果SQL語句（key）只要存在一點不同之處就會直接進行資料庫查詢了；
- 由於表中的資料不是一成不變的，大多數是經常變化的，而當資料庫中的資料變化了，那麼相應的與此表相關的快取資料就需要移除掉；
（3）解析器/分析器：分析器的工作主要是對要執行的SQL語句進行詞法解析、語法解析，最終得到抽象語法樹，然後再使用預處理器對抽象語法樹進行語義校驗，判斷抽象語法樹中的表是否存在，如果存在的話，在接著判斷select投影列字段是否在表中存在等。
（4）優化器：主要將SQL經過詞法解析、語法解析後得到的語法樹，透過資料字典和統計資訊的內容，再經過一系列運算 ，最終得出一個執行計劃，包括選擇使用哪個索引
在分析是否走索引查詢時，是透過進行動態資料取樣統計分析出來；只要是統計分析出來的，那就可能會存在分析錯誤的情況，所以在SQL執行不走索引時，也要考慮到這方面的因素
（5）執行器：根據一系列的執行計劃去調用儲存引擎提供的API介面去呼叫操作數據，完成SQL的執行。
2.3、Innodb儲存引擎的執行程序：
- （1）首先MySQL執行器根據執行計劃呼叫儲存引擎的API查詢資料
- （2）儲存引擎先從快取池buffer pool中查詢數據，如果沒有就會去磁碟中查詢，如果查詢到了就將其放到快取池中
- （3）在資料載入到Buffer Pool 的同時，會將這條資料的原始記錄保存到undo 日誌檔案中
- （4）innodb 會在Buffer Pool 中執行更新操作
- （5）更新後的資料會記錄在redo log buffer 中
- （6）提交交易在提交的同時會做以下三件事
- （7）（第一件事）將redo log buffer中的資料刷入到redo log檔案中
- （8）（第二件事）將本次操作記錄寫入bin log檔
- （9）（第三件事）將bin log檔名字和更新內容在bin log 中的位置記錄到redo log中，同時在redo log 最後加入commit 標記
- （10）使用一個後台線程，它會在某個時機將我們Buffer Pool中的更新後的資料刷到MySQL 資料庫中，這樣就將記憶體和資料庫的資料保持統一了
儲存引擎是對底層實體資料執行實際操作的元件，為Server服務層提供各種操作資料的API。常用的儲存引擎有InnoDB、MyISAM、Memory。這裡我們主要介紹InnoDB 與MyISAM 的區別：
（1）事務：MyISAM不支援事務，InnoDB支援交易
（2）鎖定層級：MyISAM只支援表級鎖，InnoDB支持行級鎖和表級鎖，預設使用行級鎖，但是行鎖只有透過索引查詢資料才會使用，否則會使用表鎖。行級鎖在每次取得鎖和釋放鎖的操作需要消耗比表鎖更多的資源。使用行鎖可能會存在死鎖的情況，但是表級鎖定不存在死鎖
（3）主鍵和外鍵：MyISAM 允許沒有任何索引和主鍵的表存在，不支援外鍵。 InnoDB的主鍵不能為空且支援主鍵自增長，如果沒有設定主鍵或非空唯一索引，就會自動產生一個6位元組的主鍵，支援外鍵完整性約束
#（4）索引結構：MyISAM 和InnoDB 都是使用B 樹索引，MyISAM的主鍵索引和輔助索引的Data域都是保存行資料記錄的位址。但是InnoDB的主鍵索引的Data域保存的不是行資料記錄的位址，而是保存該行的所有資料內容，而輔助索引的Data域保存的則是主索引的值。
由於InnoDB的輔助索引保存的是主鍵索引的值，所以使用輔助索引需要檢索兩遍索引：先檢索輔助索引取得主鍵，然後用主鍵到主索引中檢索取得記錄。這也是為什麼不建議使用過長的字段作為主鍵的原因：由於輔助索引包含主鍵列，所以，如果主鍵使用過長的字段，將會導致其他輔助索變得更大，所以爭取盡量把主鍵定義得小一些。
（5）全文索引：MyISAM支援全文索引，InnoDB在5.6版本之前不支援全文索引，5.6版本及之後的版本開始支援全文索引
（6）表的具體行數：
（7）儲存結構：
（8）儲存空間：
（9）適用場景：
備註：在mysql8.0版本中已經廢棄了MyISAM儲存引擎
資料庫的事務是並發控制的基本單位，是指邏輯上的一組操作，要麼全部執行，要麼全部不執行。
4.1、事務的ACID：
#
- （1）原子性：事務是一個不可分割的工作單元，事務裡的操作要麼都成功，要麼都失敗，如果事務執行失敗，則需要進行回滾。
- （2）隔離性：交易的所操作的資料在提交之前，對其他交易的可見程度。
- （3）持久性：一旦交易提交，它對資料庫中資料的改變就是永久的。
- （4）一致性：交易不能破壞資料的完整性和業務的一致性。例如在轉帳時，不管事務成功或失敗，雙方錢的總額不變。
4.2、ACID的實作原理：
#4.2.1、原子性：原子性是透過MySQL的回溯日誌undo log來實現的：當交易對資料庫進行修改時，InnoDB會產生對應的undo log；如果事務執行失敗或呼叫了rollback，導致事務需要回滾，便可以利用undo log中的資訊將數據回滾到修改之前的樣子。
4.2.2、隔離性：
（1）交易的隔離等級：
##為保證在並發環境下讀取資料的完整性和一致性，資料庫提供了四種事務隔離級別，隔離級別越高，越能保證資料的完整性和一致性，但對高並發效能影響也越大，執行效率越低。 （四種隔離等級從上往下依序升高）讀取未提交：允許交易在執行過程中，讀取其他交易尚未提交的資料；
- #讀取已提交：允許交易在執行過程中讀取其他交易已經提交的資料；
- 可重複讀取（預設層級）：在同一個交易內，任意時刻的查詢結果都是一致的；
- 讀取序列化：所有交易逐一依序執行，每次讀取都需要取得表級共享鎖，讀寫會相互阻塞。
（2）事務的並發問題：
如果不考慮交易的隔離性，在交易並發的環境下，可能存在問題有：不同的交易隔離級別，在並發環境會存在不同的並發問題：更新遺失：兩個或多個事務操作相同的數據，然後基於選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生遺失更新問題：最後的更新覆蓋了其他事務所所做的更新。
- 髒讀：指事務A正在存取數據，並且對數據進行了修改（事務未提交），這時，事務B也使用這個數據，後來事務A撤銷回滾，並把修改後的資料恢復原值，B所讀到的資料就與資料庫中的資料不一致，即B所讀到的資料是髒資料。
- 不可重複讀取：在一個事務內，多次讀取同一個數據，但是由於另一個事務在此期間對這個數據做了修改並提交，導致前後讀取到的數據不一致；
- 幻讀：在一個事務中，先後兩次進行讀取相同的數據（一般是範圍查詢），但由於另一個事務新增或刪除了數據，導致前後兩次結果不一致。
Innodb事務的隔離等級是由MVVC和鎖定機制實現的：
① MVCC（Multi-Version Concurrency Control，多版本並發控制）是MySQL 的InnoDB 儲存引擎實作事務隔離等級的一種具體方式，用於實作讀取已提交和可重複讀取這兩種隔離等級。而讀未提交隔離等級總是讀取最新的資料行，無需使用 MVCC。讀序列化隔離等級需要對所有讀取的行都加鎖，單純使用 MVCC 無法實現。
MVCC是透過在每行記錄後面保存兩個隱藏的列來實現的，一個保存了行的事務ID，一個保存了行的回滾段指標。每開始一個新的事務，就會自動遞增產生一個新的事務ID。交易開始時會把該事務ID放到當前事務影響的行事務ID字段中，而回滾段的指針有該行記錄上的所有版本數據，在undo log回滾日誌中通過鍊錶形式組織，也就是說該值實際指向undo log中該行的歷史記錄鍊錶。
在並發存取資料庫時，對正在事務中的資料做MVCC多版本的管理，以避免寫入操作阻塞讀取操作，並且可以透過比較版本解決快照讀取方式的幻讀問題，但對於當前讀的幻讀，MVCC並不能解決，需要用臨鍵鎖來解決。
② 鎖定機制：
MySQL鎖定機制的基本運作原理是：事務在修改資料庫之前，需要先取得對應的鎖，取得鎖定的交易才可以修改資料；在該事務操作期間，這部分的數據是鎖定，其他事務如果需要修改數據，需要等待當前交易提交或回滾後釋放鎖。
- 排它鎖定解決髒讀
- 共享鎖定解決不可重複讀取
- #臨鍵鎖定解決幻讀
4.2.3、持久性：
持久性的依靠redo log日誌實現，在執行SQL時會保存已執行的SQL語句到一個redo log文件，但是為了提高效率，將資料寫入到redo log之前，會先寫入到記憶體中的redo log buffer快取區。寫入過程如下：當寫入資料到資料庫時，執行程序會先寫入redo log buffer，redo log buffer中修改的資料會定期刷新到磁碟的redo log檔案中，這個過程稱為刷盤（即redo log buffer寫日誌到磁碟的redo log file中）。
redo log buffer的使用可以大大提高了讀寫資料的效率，但也帶了新的問題：如果MySQL宕機，而此時redo log buffer中修改的資料在記憶體還沒有刷新到磁碟，就會導致資料的遺失，事務的持久性無法保證。為了確保交易的持久性，在交易提交時，會呼叫fsync介面對redo log進行刷盤 ，刷新頻率由innodb_flush_log_at_trx_commit變數來控制的：
- 0：表示不刷入磁碟；
- 1：交易每次提交的時候，就把緩衝池中的資料刷新到磁碟中；
- 2：提交交易的時候，把緩衝池中的資料寫入磁碟文件對應的os cache 快取裡去，而不是直接進入磁碟檔案。可能 1 秒後才會把 os cache 裡的資料寫入到磁碟檔案裡去。
4.2.4、一致性：
一致性指的是交易不能破壞資料的完整性和業務的一致性：
#資料的完整性： 實體完整性、列完整性（如欄位的型別、大小、長度要符合要求）、外鍵限制等
業務的一致性：例如在銀行轉帳時，不管事務成功或失敗，雙方錢的總額不變。
當資料庫中多個交易並發訪問相同數據的時候，若對並發操作不加控制就可能會讀取和儲存不正確的數據，破壞資料庫的一致性。 MySQL鎖機制的基本運作原理就是，事務在修改資料庫之前，需要先取得對應的鎖，取得鎖的事務才可以修改資料；在該事務操作期間，這部分的資料是鎖定，其他事務如果需要修改數據，需要等待目前事務提交或回滾後釋放鎖定。
依照不同的分類方式，鎖的種類可以分為以下幾種：
- #依鎖的粒度分割：表級鎖定、行級鎖、頁級鎖；
- 依鎖的類型劃分：共享（鎖S鎖）、排他鎖（X鎖）；
- 按鎖的使用策略劃分：樂觀鎖、悲觀鎖；
5.1、表格層級鎖定、行層級鎖定、頁面層級鎖定：
#不同的儲存引擎支援不同的鎖定機制：##表格層級鎖定：最大粒度的鎖級別，發生鎖衝突的機率最高，並發度最低，但開銷小，加鎖快，不會出現死鎖；
- 行級鎖：最小粒度的所級別，發生鎖衝突的機率最小，併發度最高，但開銷大，加鎖慢，會發生死鎖；
- 頁級鎖：鎖粒度界於表級鎖和行級鎖之間，對錶級鎖和行級鎖的折中，並發度一般。開銷與加鎖時間也界於表鎖和行鎖之間，會出現死鎖；
#InnoDB儲存引擎支援行級鎖和表級鎖，預設使用行級鎖，但只有透過索引進行查詢數據，才使用行級鎖，否就使用表級鎖。
- MyISAM和MEMORY儲存引擎採用的是表格級鎖定；
- BDB儲存引擎使用的是頁面鎖定，但也支援表級鎖定；
5.2、InnoDB的行鎖定：
InnoDB的行鎖定有兩種類型：對於update，delete，insert 操作，InnoDB會自動為涉及的資料行加排他鎖定；對於普通SELECT語句，InnoDB不會加任何鎖定。
5.3、InnoDB的表鎖定與意圖鎖定：
因為InnoDB引擎允許行鎖定和表鎖定共存，實現多粒度的鎖定機制，但是表鎖和行鎖雖然鎖定範圍不同，但會互相衝突。當你要加表鎖時，勢必要先遍歷該表的所有記錄，判斷是否有排他鎖。這種遍歷檢查的方式顯然是一種低效率的方式，MySQL引入了意向鎖，來偵測表鎖和行鎖的衝突。意向鎖也是表格級鎖，分為讀取意向鎖（IS鎖）和寫意向鎖（IX鎖）。 當交易要在記錄上加上行鎖時，則先在表上加上對應的意向鎖。之後事務如果想進行鎖表，只要先判斷是否有意向鎖存在，存在時則可快速返回該表不能啟用表鎖，否則就需要等待，提高效率。
5.4、InnoDB行鎖定的實作與臨鍵鎖定：
InnoDB的行鎖定是透過為索引上的索引項目加鎖來實現的。 只有透過索引檢索數據，才能使用行鎖，否則將使用表鎖。
在InnoDB中，為了解決幻讀的現象，引入了臨鍵鎖定(next-key)。根據索引，劃分為一個個左開右閉的區間。當進行範圍查詢的時候，若命中索引且能夠檢索到數據，則鎖住記錄所在的區間和它的下一個區間。其實，臨鍵鎖定(Next-Key) = 記錄鎖定(Record Locks) 間隙鎖定(Gap Locks)
- 間隙鎖定：當使用範圍查詢而不是精確查詢進行檢索數據，並且請求共用或排它鎖時，InnoDB會為符合範圍條件的已有資料記錄的索引項目加鎖；對於鍵值在條件範圍內但不存在的記錄，稱為間隙(GAP)。
- 記錄鎖定：當使用唯一索引，且記錄存在的精確查詢時，使用記錄鎖定
#5.5、利用鎖定機制解決並發問題：
- X鎖定解決髒讀
- S鎖定解決無法重複讀取
- 臨鍵鎖定解決幻讀
#InnoDB儲存引擎鎖定機制的詳細內容和MyISAM儲存引擎的鎖定機制的詳細內容可以閱讀這篇文章：MySQL資料庫：鎖定機制_張維鵬的部落格-CSDN部落格_資料庫中的鎖定機制
索引本質上就是一種透過減少查詢需要遍歷行數，加快查詢效能的資料結構，避免資料庫進行全表掃描，好比書的目錄，讓你更快找到的內容。 （一個表最多16個索引）
6.1、索引的優缺點：
（1）索引的優點：
- 減少查詢需要檢索的行數，加快查詢速度，避免進行全表掃描，這也是建立索引的最主要的原因。
- 如果索引的資料結構是B 樹，在使用分組和排序時，可以顯著減少查詢中分組和排序的時間。
- 透過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
（2）索引的缺點：
- 當表格中的資料增加、刪除、修改時，索引也要更新，維護的耗時隨著數據量的增加而增加。
- 索引需要佔用實體空間，如果要建立叢集索引，那麼需要的空間就會更大。
6.2、索引的使用場景：
（1）在哪些欄位上面建立索引：
- WHERE子句中常出現的欄位上面建立索引，加快條件的判斷速度。
- 按範圍訪問的列或在group by或order by中使用的列，因為索引已經排序，這樣可以利用索引來加快排序查詢時間。
- 經常用於連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
- 作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
（2）不在哪些列建索引？
- 區分度不高的欄位。由於這些列的取值很少，例如性別，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。
- 在查詢中很少的欄位不應該建立索引。由於這些列很少使用到，但增加了索引，反而降低了系統的維護速度和增加了空間需求。
- 當新增索引造成修改成本的提高 遠大於 檢索效能的提高時，不應該建立索引。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。
- 定義為text, image和bit資料類型的欄位不應該增加索引。這些列的資料量要麼相當大，要麼取值很少。
6.3、 索引的分類：
（1）普通索引、唯一索引、主鍵索引、全文索引、組合索引。
- 普通索引：最基本的索引，沒有任何限制
- 唯一索引：但索引列的值必須唯一，允許有空值，可以有多個NULL值。如果是組合索引，則列值的組合必須唯一。
- 主鍵索引：一個特殊的唯一索引，不允許有空值。
- 全文索引：全文索引僅可用於MyISAM 表，並且只支援從CHAR、VARCHAR或TEXT類型，用於取代效率較低的like 模糊匹配操作，而且可以透過多字段組合的全文索引一次性全模糊匹配多個字段。
- 組合索引：主要是為了提高mysql效率，在建立組合索引時應該將最常用作限制條件的欄位放在最左邊，依序遞減。
（2）叢集索引與非叢集索引：
若依資料儲存的物理順序與索引值的順序分類，可以將索引分為聚合簇索引與非叢集索引兩類：
- 叢集索引：表中資料儲存的物理順序與索引值的順序一致，一個基本表最多只能有一個叢集索引，更新叢集索引列上的資料時，往往導致表中記錄的物理順序的變更，代價較大，因此對於經常更新的資料列不宜建立叢集索引
- 非叢集索引：表中資料的物理順序與索引值的順序不一致的索引組織，一個基本表可以有多個聚集索引。
6.4、索引的資料結構：
#常見的索引的資料結構有：B Tree、Hash索引。
（1）Hash索引：MySQL中只有Memory儲存引擎支援hash索引，是Memory表的預設索引類型。 hash索引把資料以hash值形式組織起來，因此查詢效率非常高，可以一次定位。
hash索引的缺點：
- Hash索引只能滿足等值的查詢，無法滿足範圍查詢、排序。因為數據在經過Hash演算法後，其大小關係就可能改變。
- 當建立組合索引時，不能只使用組合索引的部分欄位進行查詢。因為hash索引是把多個列資料合併後再計算Hash值，所以對單獨列資料計算Hash值是沒有意義的。
- 當發生Hash碰撞時，Hash索引無法避免表格資料的掃描。因為僅僅比較Hash值是不夠的，需要比較實際的值以判定是否符合要求。
（2）B Tree索引：B Tree是mysql使用最頻繁的一個索引資料結構，是Innodb和Myisam儲存引擎模式的索引類型。 B Tree索引在尋找時需要從根節點到葉節點進行多次IO操作，在查詢速度比不上Hash索引，但更適合排序等操作。
B Tree索引的優點：
- 頁內節點不儲存內容，每次IO可以讀取更多的行，大幅減少磁碟I/O讀取次數
- 帶順序存取指針的B Tree：B Tree所有索引資料都儲存在葉子結點上，並且增加了順序存取指針，每個葉子節點都有指向相鄰葉子節點的指針，這樣做是為了提高區間查詢效率。
6.5、為什麼使用B Tree作為索引：
索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存在磁碟上。這樣的話，索引查找過程中就要產生磁碟I/O消耗，相對於記憶體存取，磁碟I/O訪問的消耗要高幾個數量級，所以評價一個資料結構作為索引的優劣最重要的指標就是在查找過程中磁碟I/O操作次數的漸進複雜度。換句話說，索引的資料結構要盡量減少查找過程中磁碟I/O的存取次數。
（1）局部性原理與程式預讀：
由於磁碟本身存取就比主存慢很多，再加上機械運動耗費，因此為了提高效率，要盡量減少磁碟I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這樣做的理論基礎是計算機科學中著名的局部性原理：當一個數據被用到時，其附近的數據也通常會馬上被使用。程式運行期間所需的資料通常比較集中。
由於磁碟順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對於具有局部性的程式來說，預讀可以提高I/O效率。 預讀的長度一般為頁的整數倍。當程式要讀取的資料不在主記憶體時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續運行。
（2）B Tree索引的效能分析：
上文說過一般使用磁碟I/O次數評價索引結構的優劣。我們先從B樹分析，B樹檢索一次最多需要存取h個節點，同時，資料庫巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，即每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點在物理上也儲存在一個頁裡，加之電腦儲存分配都是按頁對齊的，這樣就實現了每個節點只需要一次I/O就可以完全載入。 B樹中一次檢索最多需要h-1次I/O（根節點常駐記憶體），時間複雜度為O(h)=O(logdN)。一般實際應用中，出度d是非常大的數字，通常超過100，因此h非常小。綜上所述，以B樹作為索引結構效率是非常高的。
而紅黑樹這種結構，雖然時間複雜度也為O(h)，但是h明顯要深的多，並且由於邏輯上很近的節點，在物理上可能很遠，無法利用局部性，所以IO效率明顯比B樹差很多。
另外，B Tree更適合作為索引的資料結構，原因和內節點出度d有關。從上面分析可以看到，d越大索引的效能越好，而出度d的上限取決於節點內key和data的大小，由於B Tree內節點去掉了data域，因此可以擁有更大的出度，磁碟IO的次數也就更少了。
（3）B 樹索引 和 B樹索引 的比較？
根據B-Tree 和B Tree的結構，我們可以發現B 樹比起B樹，在檔案系統或資料庫系統當中，更有優勢，原因如下：
- （1）B 樹有利於對資料庫的掃描：B樹在提高了磁碟IO效能的同時並沒有解決元素遍歷的效率低下的問題，而B 樹只需要遍歷葉子節點就可以解決對全部關鍵字資訊的掃描，所以範圍查詢、排序等操作，B 樹有著更高的效能。
- （2）B 樹的磁碟IO代價更低：B 樹的內部結點的data域並沒有儲存數據，因此其內部結點相對於B樹更小。如果把所有相同內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量就越多。一次讀入記憶體中的需要尋找的關鍵字也就越多，相對來說I/O讀寫次數也就降低了。
- （3）B 樹的查詢效率更加穩定：由於B 樹的內部結點只是葉子結點中關鍵字的索引，因此不會儲存資料。所以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每個資料的查詢效率相當。
（4）MySQL的 InnoDB 和 MyISAM 儲存引擎中B Tree索引的實作？
MyISAM和InnoDB都是使用B 樹索引，MyISAM的主鍵索引和輔助索引的Data域都是保存行的位址，但是InnoDB的主鍵索引保存的不是行的位址，而是保存該行的所有所有數據，而輔助索引的Data域保存的則是主索引的值。
索引的長度限制：
- 對於 Innodb 的組合索引，如果各個欄位中的長度超過767位元組的，則會對超過767位元組的列取前綴索引；對於Innodb 的單列索引，如果列的長度超過767的，則取前綴索引（取前255字元）
- 對於MyISAM 的組合索引，所建立的索引長度和不能超過1000 bytes ，否則會報錯，創建失敗；對於MyISAM 的單列索引，最大長度也不能超過1000，否則會報警，但是創建成功，最終創建的是前綴索引（取前333個字符）
（1）MySQL的SQL最佳化與索引最佳化：https://blog.csdn.net/a745233700 /article/details/84455241
（2）MySQL的表格結構最佳化：https://blog.csdn.net/a745233700/article/details/84405087
MySQL屬於IO 密集型的應用程序，主要職責是資料的管理及儲存工作。而我們知道，從記憶體中讀取一個資料庫的時間是微秒級別，而從一塊普通硬碟上讀取一個IO是在毫秒級別，二者相差3個數量級。所以，要優化資料庫，首先第一步要最佳化的就是 IO，盡可能將磁碟IO轉換為記憶體IO。所以對於MySQL資料庫的參數最佳化上，主要針對減少磁碟IO的參數做最佳化：例如使用 query_cache_size 調整查詢快取的大小，使用 innodb_buffer_pool_size 調整緩衝區的大小;
執行計劃是SQL語句經過查詢分析器後得到的抽象語法樹和相關表的統計資訊所作出的查詢方案，這個方案是由查詢最佳化器自動分析所產生的。由於是動態資料採樣統計分析出來的結果，所以可能會存在分析錯誤的情況，也就是存在執行計畫並不是最優的情況。 透過explain關鍵字知道MySQL是如何執行SQL查詢語句的，分析select 語句的效能瓶頸，從而改進我們的查詢，explain的結果如下：
重要的有id、type、key、key_len、rows、extra：
（1）id：id列可以理解為SQL執行順序的標識，有幾個select 就有幾個id。
- id值不同：id值越大優先權越高，越先被執行；
- id值相同：從上往下依序執行；
- id列為null：表示這是一個結果集，不需要使用它來進行查詢。
（2）select_type：查詢的類型，主要用於區分普通查詢、聯合查詢、子查詢等複雜的查詢；
（3）table：表示explain 的一行正在存取哪個表
（4）type：存取類型，即MySQL決定如何查找表中的行。依序從好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了 type都可以使用到索引，除了index_merge 之外，其他的type只可以用到一個索引。一般要求type為 ref 級別，範圍查找需要達到 range 級別。
- system：表中只有一條資料符合（等於系統表），可以看成 const 類型的特例
- const：透過索引一次就找到了，表示使用主鍵索引或唯一索引
- eq_ref：主鍵或唯一索引中的欄位被用於連接使用，只會傳回一行符合的資料
- ref：普通索引掃描，可能傳回多個符合查詢條件的行。
- fulltext：全文索引檢索，全文索引的優先權很高，若全文索引和普通索引同時存在時，mysql不管代價，優先選擇使用全文索引。
- ref_or_null：與ref方法類似，只是增加了null值的比較。
- index_merge：表示查詢使用了兩個以上的索引，索引合併的最佳化方法，最後取交集或併集，常見and ，or的條件使用了不同的索引。
- unique_subquery：用於where中的in形式子查詢，子查詢傳回不重複值唯一值；
- index_subquery：用於in形式子查詢使用到了輔助索引或in常數列表，子查詢可能會傳回重複值，可以使用索引將子查詢去重。
- range：索引範圍掃描，常見於使用>,<,between ,in ,like等運算子的查詢。
- index：索引全表掃描，把索引樹從頭到尾掃描一遍；
- all：遍歷全表以找到匹配的行（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬碟讀取）
- NULL：MySQL在最佳化過程中分解語句，執行時甚至不用存取表或索引
（5）possible_keys：查詢時可能使用到的索引
（6）key：實際使用哪個索引來優化對該表的存取
（7）key_len：實際上使用於最佳化查詢的索引長度，即索引中使用的位元組數。透過這個值，可以計算出一個多列索引裡實際使用了索引的哪寫欄位。
（8）ref：顯示哪個欄位或常數與key一起被使用
（9）rows：根據表格統計資訊及索引選用情況，大致估算此處查詢需要讀取的行數，不是精確值。
（10）extra：其他的一些額外資訊
- using index：使用覆蓋索引
- using index condition：查詢的列未被索引覆蓋，where篩選條件使用了索引
- using temporary：用臨時表保存中間結果，常用於group by 和order by 操作中，通常是因為group by 的列上沒有索引，也有可能是因為同時有group by和order by，但group by和order by的列又不一樣，一般看到它說明查詢需要優化了
- using filesort：MySQL有兩種方式對查詢結果進行排序，一種是使用索引，另一種是filesort（基於快排實現的外部排序，效能比較差），當資料量很大時，這將是一個CPU密集型的過程，所以可以透過建立合適的索引來優化排序的效能
對explain執行計畫詳請有興趣的讀者可以閱讀這篇文章：https://blog.csdn.net/a745233700/article/details/84335453
#10.1、MySQL主從複製的原理：
（2）Slave I/O thread執行緒：讀取master 伺服器Binlog Dump執行緒傳送的內容並儲存到slave伺服器的relay log中繼日誌中，位於slave端：
- ##Slave從Master取得binlog二進位日誌文件，然後再將日誌文件解析成對應的SQL語句在從伺服器上重新執行一遍主伺服器的操作，透過這種方式來確保資料的一致性。由於主從複製的過程是非同步複製的，因此Slave和Master之間的資料有可能存在延遲的現象，只能保證資料最終的一致性。在master和slave之間實作整個複製過程主要由三個執行緒來完成：
- #（1）Slave SQL thread執行緒：建立用於讀取relay log中繼日誌並執行日誌中包含的更新，位於slave端
（3）Binlog dump thread執行緒(也稱為IO執行緒)：將bin-log二進位日誌中的內容傳送到slave伺服器，位於master端
#注意：如果一台主伺服器配兩台從伺服器那主伺服器上就會有兩個Binlog dump 線程，而每個從伺服器上各自有兩個線程；
- #10.2、主從複製流程：
#（1）master伺服器在執行SQL語句之後，記錄在binlog二進位檔案中； （2）slave端的IO線程連接上master端，並請求從指定bin log日誌檔案的指定pos節點位置（或從最開始的日誌）開始複製之後的日誌內容。
（3）master端在接收到來自slave端的IO線程請求後，通知負責複製進程的IO線程，根據slave端IO線程的請求信息，讀取指定binlog日誌指定pos節點位置之後的日誌訊息，然後傳回給slave端的IO線程。此傳回資訊中除了binlog日誌所包含的資訊之外，還包括本次傳回的資訊在master端的binlog檔名以及該binlog日誌中的pos節點位置。
（4）slave端的IO線程在接收到master端IO回傳的訊息後，將接收到的binlog日誌內容依序寫入到slave端的relay log檔案的最末端，並將讀取到的master端的binlog檔案名稱和pos節點位置記錄到master-info檔案（該檔案儲存slave端），以便在下次同步的候能夠告訴master從哪個位置開始進行資料同步；（5）slave端的SQL執行緒在偵測到relay log檔案中新增內容後，就馬上解析該relay log檔案中的內容，然後還原成在master端真實執行的那些SQL語句，再按順序依序執行這些SQL語句，從而到達master端與slave端的資料一致性；
10.3、主從複製的好處：
（2）提高資料安全，因為資料已複製到從伺服器，從伺服器可以終止複製進程，所以，可以在從伺服器上備份而不破壞主伺服器對應資料。 （3）在主伺服器上產生即時數據，而在從伺服器上分析這些數據，從而提高主伺服器的效能。 ###############10.4、MySQL支援的複製類型及其優缺點：#########binlog日誌檔案有兩種格式，一種是Statement-Based （基於語句的複製），另一種是Row-Based（基於行的複製）。預設格式為Statement-Based，如果想改變其格式在開啟服務的時候使用-binlog-format 選項，其具體命令如下：#########mysqld_safe –user=msyql –binlog-format=格式& #########（1）基於語句的複製（Statement-Based）：在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。效率比較高。一旦發現沒法精確複製時，會自動選著基於行的複製。 #########優點：#########① 因為記錄的SQL語句，所以佔用較少的儲存空間。 binlog日誌包含了描述資料庫操作的事件，但這些事件包含的情況只是對資料庫進行改變的操作，例如 insert、update、create、delete等操作。相反select、desc等類似的操作並不會去記錄。 ######② binlog日誌檔案記錄了所有的變更資料庫的語句，所以此檔案可以作為資料庫的審核依據。 #########缺點：###
- （1）讀寫分離，透過動態增加從伺服器來提高資料庫的效能，在主伺服器上執行寫入和更新，在從伺服器上執行讀取功能。
- ① 不安全，不是所有的改變資料的語句都會被記錄下來。對於非確定性的行為不會被記錄下來。例如：對於 delete 或 update 語句，如果使用了 limit 但是並沒有 order by ，這就屬於非確定性的語句，就不會被記錄。
- ② 對於沒有索引條件的update，insert……select 語句，必須鎖定更多的數據，降低了資料庫的效能。
（2）基於行的複製（Row-Based）：把改變的內容複製過去，而不是把指令在從伺服器上執行一遍，從mysql5.0開始支援；
優點：
- ① 所有的改變都會被複製，這是最安全的複製方式；
- ② 對於update、insert…select等語句鎖定更少的行；
缺點：
- ① 不能透過binlog日誌檔案查看什麼語句執行了，也無從知道在從伺服器上接收到什麼語句，我們只能看到什麼資料改變。
- ② 因為記錄的是數據，所以說binlog日誌檔案所佔用的儲存空間要比Statement-based大。
- ③ 對於資料量大的操作其花費的時間有較長。
（3）混合類型的複製：預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。
有關主從複製更詳細的內容，請閱讀這篇文章：https://blog.csdn.net/a745233700/article/details/85256818
11.1、實作原理：
#讀寫分離解決的是，資料庫的寫入操作，影響了查詢的效率，適用於讀遠大於寫的場景。讀寫分離的實作基礎是主從複製，主資料庫利用主從複製將自身資料的改變同步到從資料庫叢集中，然後主資料庫負責處理寫入作業（當然也可以執行讀取操作），從資料庫負責處理讀取操作，不能執行寫入操作。並且可以根據壓力情況，部署多個從資料庫提高讀取操作的速度，減少主資料庫的壓力，提高系統整體的效能。
11.2、讀寫分離提高效能的原因：
- （1）增加實體伺服器，負荷分攤；
- （2）主從只負責各自的寫和讀，極大程度的緩解X鎖和S鎖爭用；
- （3）從庫可配置MyISAM引擎，提升查詢性能以及節約系統開銷；
- （4）主從複製另外一大功能是增加冗餘，提高可用性，當一台資料庫伺服器宕機後能透過調整另外一台從庫來以最快的速度恢復服務。
11.3、Mysql讀取和寫入分寫的實作方式：
- （1）基於程式碼內部實作：在程式碼中依據select 、insert進行路由分類。優點是效能較好，因為程式在程式碼中實現，不需要增加額外的硬體開支，缺點是需要開發人員來實現，維運人員無從下手。
- （2）基於中間代理層實作：代理一般介於應用伺服器和資料庫伺服器之間，代理資料庫伺服器接收到應用伺服器的請求後根據判斷後轉送到後端資料庫，有以下代表性的代理層。
讀寫分離解決的是資料庫讀寫作業的壓力，但是沒有分散資料庫的儲存壓力，利用分庫分錶可以解決資料庫的儲存瓶頸，並提升資料庫的查詢效率。
12.1、垂直拆分：
（1）垂直分錶：將一個表格依照欄位分成多個表，每個表格儲存其中一部分字段。一般會將常用的欄位放到一個表格中，將不常用的欄位放到另一個表格中。
優點：
（1）避免IO競爭減少鎖定表的機率。因為大的字段效率更低，第一，大字段佔用的空間更大，單頁內存儲的行數變少，會使得IO操作增多；第二數據量大，需要的讀取時間長。
（2）可以更好地提升熱門資料的查詢效率。
（2）垂直分庫：依照業務模組的不同，將表格拆分到不同的資料庫中，適合業務之間的耦合度非常低、業務邏輯清晰的系統。
優點：
- 降低業務中的耦合，方便對不同的業務進行分級管理
- 可以提升IO、資料庫連線數、解決單機硬體儲存資源的瓶頸問題
（3）垂直分割（分庫、分錶）的缺點：
- 主鍵出現冗餘，需要管理冗餘列
- 交易的處理變得複雜
- 仍然存在單表資料量過大的問題
12.2 、水平拆分：
（1）水平分錶：在同一個資料庫內，把同一個表格的資料依照一定規則拆分到多個表格中。
優點：
- 解決了單表資料量過大的問題
- 避免IO競爭並減少鎖定表的機率
#（2）水平分庫：把同一個表格的資料依照一定規則拆分到不同的資料庫中，不同的資料庫可以放到不同的伺服器上。
優點：
- 解決了單庫大資料量的瓶頸問題
- IO衝突減少，鎖定的競爭減少，某個資料庫出現問題不影響其他資料庫，提高了系統的穩定性和可用性
（3）水平拆分（分錶、分庫）的缺點：
- 分片事務一致性難以解決
- 跨節點JOIN效能差，邏輯會變得複雜
- #資料擴展難度大，不易維護
12.3、分庫分錶存在的問題的解決：
（1）事務的問題：
① 方案一：使用分散式事務：
- 優點：由資料庫管理，簡單有效。
- 缺點：效能代價高，特別是shard越來越多。
② 方案二：程式與資料庫共同控制實現，原理就是將一個跨多個資料庫的分散式事務分解成多個僅存在於單一資料庫上面的小事務，並交由應用程序來總體控制各個小事務。
- 優點：效能上有優勢；
- 缺點：需要在應用程式在交易上做靈活控制。如果使用了spring的事務管理，改變起來會面臨一定的困難。
（2）跨節點Join 的問題：
解決該問題的普遍做法是分兩次查詢實現：在第一次查詢的結果集中找出關聯資料的id，根據這些id發起第二次請求得到關聯資料。
（3）跨節點count，order by，group by，分頁和聚合函數問題：
由於這類問題都需要基於全部資料集合進行計算。多數的代理程式都不會自動處理合併工作，解決方案：與解決跨節點join問題的類似，分別在各個節點上得到結果後在應用程式端進行合併。和 join 不同的是每個結點的查詢可以並行執行，因此速度要比單一大表快很多。但如果結果集很大，對應用程式記憶體的消耗是一個問題。
12.4、分庫分錶後，ID鍵如何處理？
分庫分錶後不能每個表的ID都是從1開始，所以需要一個全域ID，設定全域ID主要有以下幾種方法：
（1）UUID：
- 優點：本地產生ID，不需要遠端調用，全域唯一不重複。
- 缺點：佔用空間大，不適合當索引。
（2）資料庫自增ID：在分庫分錶後使用資料庫自增ID，需要一個專門用來產生主鍵的函式庫，每次服務接收到請求，先向這個庫中插入一條沒有意義的數據，取得一個資料庫自增的ID，利用這個ID去分庫分錶中寫入資料。
- 優點：簡單易實作。
- 缺點：在高並發下有瓶頸。
（3）Redis產生ID：
- 優點：不依賴資料庫，效能比較好。
- 缺點：引入新的元件會讓系統複雜度增加
（4）Twitter的snowflake演算法：是一個64位元的long型的ID，其中有1bit是不用的，41bit作為毫秒數，10bit作為工作機器ID，12bit作為序號。
- 1bit：第一個bit預設為0，因為二進位中第一個bit為1的話為負數，但是ID不能為負數.
- 41bit：表示的是時間戳，單位是毫秒。
- 10bit：記錄工作機器ID，其中5個bit表示機房ID，5個bit表示機器ID。
- 12bit：用來記錄同一毫秒內產生的不同ID。
（5）美團的Leaf分散式ID產生系統，美團點評分佈式ID產生系統：
分區就是將表格的資料依照特定規則存放在不同的區域，也就是將表格的資料檔案分割成多個小塊，查詢資料的時候，只要知道資料資料儲存在哪些區域，然後直接在對應的區域進行查詢，不需要對錶資料進行全部的查詢，提高查詢的效能。同時，如果表資料特別大，一個磁碟磁碟放不下時，我們也可以將資料分配到不同的磁碟去，解決儲存瓶頸的問題，利用多個磁碟，也能夠提高磁碟的IO效率，提高資料庫的效能。 使用分區表時，需要注意分區欄位必須放在主鍵或唯一索引中、每個表最大分區數為1024；常見的分區類型有：Range分區、List分區、Hash分區、Key分區，
- （1）Range分割區：依照連續的區間範圍進行分割區
- （2）List分割區：依照給定的集合中的值進行選擇分割區。
- （3）Hash分區：基於使用者定義的表達式的傳回值進行分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL中有效的、產生非負整數值的任何表達式。
- （4）Key分區：類似依照HASH分割區，差別在於Key分割區只支援計算一列或多列，而key分割區的雜湊函數是由 MySQL 伺服器提供。
（1）表格分割區的優點：
#① 可擴展性：
- 將分區分在不同磁碟，可以解決單磁碟容量瓶頸問題，儲存更多的數據，也能解決單磁碟的IO瓶頸問題。
② 提升資料庫的效能：
- 減少資料庫擷取時需要遍歷的資料量，查詢時只需要在資料對應的分割區進行查詢。
- 避免Innodb的單一索引的互斥存取限制
- 對於聚合函數，例如sum()和count()，可以在每個分區進行並行處理，最終只需要統計所有分區得到的結果
③ 方便對數據進行維運管理：
- 方便管理，對於失去保存意義的數據，透過刪除對應的分區，達到快速刪除的作用。例如刪除某一時間的歷史數據，直接執行truncate，或直接drop整個分割區，這比detele刪除效率更高；
- 在某些場景下，單一分割表的備份很恢復會更有效率。
（1）自增ID：
使用自增ID的好處：
- 欄位長度較 UUID 會小很多。
- 資料庫自動編號，依序存放，利於檢索
- 無須擔心主鍵重複問題
使用自增ID的缺點：
- 因為是自增，在某些業務場景下，容易被其他人查到業務量。
- 發生資料遷移時，或當表合併時會非常麻煩
- 在高並發的場景下，競爭自增鎖會降低資料庫的吞吐能力
（2）UUID：通用唯一識別碼，UUID是基於當前時間、計數器和硬體識別等資料計算產生的。
使用UUID的優點：
- 唯一標識，不用考慮重複問題，在資料拆分、合併時也能達到全域的唯一性。
- 可以在應用程式層生成，提高資料庫的吞吐能力。
- 無需擔心業務量外洩的問題。
使用UUID的缺點：
- 因為UUID是隨機產生的，所以會發生隨機IO，影響插入速度，並且會造成硬碟的使用率較低。
- UUID佔用空間較大，建立的索引越多，造成的影響越大。
- UUID之間比較大小較自增ID慢不少，影響查詢速度。
一般情況下，MySQL建議使用自增ID，因為在MySQL的InnoDB 儲存引擎中，主鍵索引是叢集索引，主鍵索引的B 樹的葉子節點依序儲存了主鍵值及數據，如果主鍵索引是自增ID，只需要依序往後排列即可，如果是UUID，ID是隨機產生的，在資料插入時會造成大量的資料移動，產生大量的記憶體碎片，造成插入效能的下降。
#檢視是從一個或多個資料表（或檢視）匯出的資料表，其內容由查詢定義。視圖是一個虛擬表，資料庫中只儲存視圖的定義，不儲存視圖對應的數據，在對視圖的數據進行操作時，系統會根據視圖的定義去操作對應的基本表。 可以說，視圖是在基本表之上建立的表，它的結構和內容都來自基本表，依據基本表存在而存在。一個視圖可以對應一個基本表，也可以對應多個基本表。視圖是基本表的抽象化和在邏輯意義上建立的新關係。
（1）視圖的優點：
- 簡化了操作，把經常使用的資料定義為視圖
- 安全性，使用者只能查詢和修改能看到的資料
- 邏輯上的獨立性，屏蔽了真實表的結構所帶來的影響
（2）視圖的缺點：
- 效能差，資料庫必須把對視圖的查詢轉換成對基本表的查詢，如果這個視圖是由一個複雜的多表查詢所定義，那麼，即使是視圖的一個簡單查詢，資料庫也要把它變成一個複雜的結合體，需要花費一定的時間。
SQL語句需要先編譯然後執行，而預存程序就是一組為了完成特定功能的SQL語句集，經過編譯後儲存在資料庫中，使用者透過制定預存程序的名字並給定參數來呼叫它。
使用程式也可以實現操作資料庫的複雜邏輯，那為什麼需要預存程序呢？主要是因為使用程式呼叫API執行，其效率相對較慢，應用程式需透過引擎把SQL語句交給MYSQL引擎來執行，那還不如直接讓MySQL負責它最精通最能完成的工作。
預存程序的優點：
- （1）標準元件式程式設計：在儲存過程建立後，可以在程式中被多次調用，而不必重新編寫該預存程序的SQL語句。且DBA可以隨時對預存程序進行修改，對應用程式原始碼毫無影響。
- （2）更快的執行速度：如果某一操作包含大量的Transaction-SQL程式碼或分別被多次執行，那麼預存程序要比批次的執行速度快很多。因為預存程序是預先編譯的，在首次運行預存程序時查詢，優化器對其進行分析最佳化，並且給出最終被儲存在系統表中的執行計劃。而批次的Transaction-SQL語句在每次執行時都要進行編譯和最佳化，速度相對要慢一些。
- （3）增強SQL語言的功能與彈性：預存程序可以用控制語句寫，有很強的彈性，可以完成複雜的判雜的斷和較複運算。
- （4）減少網路流量：針對同一個資料庫物件的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織進預存過程，那麼當在客戶電腦上當呼叫該預存程序時，網路中傳送的只是該呼叫語句，從而大大減少網路流量並降低了網路負載。
- （5）作為一種安全機制來充分利用：透過對執行某一預存程序的權限進行限制，能夠實現對對應的資料的存取權限的限制，避免了非授權使用者對資料的訪問，保證了資料的安全。
#觸發器是與表格有關的資料庫對象，當觸發器所在表上出現指定事件並滿足定義條件的時候，將執行觸發器中定義的語句集合。 觸發器的特性可以應用在資料庫端確保資料的完整性。觸發器是一個特殊的預存過程，不同的是預存程序要用call來調用，而觸發器不需要使用call，也不需要手動調用，它在插入，刪除或修改特定表中的資料時觸發執行，它比資料庫本身標準的功能有更精細和更複雜的資料控制能力。
#遊標，就是遊動的標識，可以充當指標的作用，使用遊標可以遍歷查詢資料庫返回的結果集中的所有記錄，但是每次只能提取一筆記錄，即每次只能指向並取出一行的數據，以便進行相應的操作。 當你沒有使用遊標的時候，相當於別人一下給你所有的東西讓你拿走；用了遊標之後，相當於別人一件一件的給你，這時你可以先看看這個東西好不好，再自己進行選擇。
推薦學習：mysql影片教學
#以上是歸納整理MySQL資料庫常見面試題的詳細內容。更多資訊請關注PHP中文網其他相關文章！
