薦藏：MySQL數(shù)據(jù)庫常見面試題總結(jié)

點擊上方藍色字體，選擇“標星公眾號”

優(yōu)質(zhì)文章，第一時間送達

1、數(shù)據(jù)庫的常用范式：

• 第一范式（1NF）：指表的列不可再分，數(shù)據(jù)庫中表的每一列都是不可分割的基本數(shù)據(jù)項，同一列中不能有多個值；

• 第二范式（2NF）：在 1NF 的基礎上，還包含兩部分的內(nèi)容：一是表必須有一個主鍵；二是表中非主鍵列必須完全依賴于主鍵，不能只依賴于主鍵的一部分；

• 第三范式（3NF）：在 2NF 的基礎上，消除非主鍵列對主鍵的傳遞依賴，非主鍵列必須直接依賴于主鍵。

• BC范式（BCNF）：在 3NF 的基礎上，消除主屬性對于碼部分的傳遞依賴 

2、SQL語句的執(zhí)行過程：

2.1、客戶端的數(shù)據(jù)庫驅(qū)動與數(shù)據(jù)庫連接池：

（1）客戶端與數(shù)據(jù)庫進行通信前，通過數(shù)據(jù)庫驅(qū)動與MySQL建立連接，建立完成之后，就發(fā)送SQL語句

（2）為了減少頻繁創(chuàng)建和銷毀連接造成系統(tǒng)性能的下降，通過數(shù)據(jù)庫連接池維護一定數(shù)量的連接線程，當需要進行連接時，就直接從連接池中獲取，使用完畢之后，再歸還給連接池。常見的數(shù)據(jù)庫連接池有 Druid、C3P0、DBCP

2.2、MySQL架構(gòu)的Server層的執(zhí)行過程：

（1）連接器：主要負責跟客戶端建立連接、獲取權(quán)限、維持和管理連接

（2）查詢緩存：優(yōu)先在緩存中進行查詢，如果查到了則直接返回，如果緩存中查詢不到，在去數(shù)據(jù)庫中查詢。

MySQL緩存是默認關閉的，也就是說不推薦使用緩存，并且在MySQL8.0 版本已經(jīng)將查詢緩存的整塊功能刪掉了。這主要是它的使用場景限制造成的：

• 先說下緩存中數(shù)據(jù)存儲格式：key（sql語句）- value（數(shù)據(jù)值），所以如果SQL語句（key）只要存在一點不同之處就會直接進行數(shù)據(jù)庫查詢了；

• 由于表中的數(shù)據(jù)不是一成不變的，大多數(shù)是經(jīng)常變化的，而當數(shù)據(jù)庫中的數(shù)據(jù)變化了，那么相應的與此表相關的緩存數(shù)據(jù)就需要移除掉；

（3）解析器/分析器：分析器的工作主要是對要執(zhí)行的SQL語句進行詞法解析、語法解析，最終得到抽象語法樹，然后再使用預處理器對抽象語法樹進行語義校驗，判斷抽象語法樹中的表是否存在，如果存在的話，在接著判斷select投影列字段是否在表中存在等。

（4）優(yōu)化器：主要將SQL經(jīng)過詞法解析、語法解析后得到的語法樹，通過數(shù)據(jù)字典和統(tǒng)計信息的內(nèi)容，再經(jīng)過一系列運算 ，最終得出一個執(zhí)行計劃，包括選擇使用哪個索引

在分析是否走索引查詢時，是通過進行動態(tài)數(shù)據(jù)采樣統(tǒng)計分析出來；只要是統(tǒng)計分析出來的，那就可能會存在分析錯誤的情況，所以在SQL執(zhí)行不走索引時，也要考慮到這方面的因素

（5）執(zhí)行器：根據(jù)一系列的執(zhí)行計劃去調(diào)用存儲引擎提供的API接口去調(diào)用操作數(shù)據(jù)，完成SQL的執(zhí)行。

2.3、Innodb存儲引擎的執(zhí)行過程：

• （1）首先MySQL執(zhí)行器根據(jù) 執(zhí)行計劃 調(diào)用存儲引擎的API查詢數(shù)據(jù)

• （2）存儲引擎先從緩存池buffer pool中查詢數(shù)據(jù)，如果沒有就會去磁盤中查詢，如果查詢到了就將其放到緩存池中

• （3）在數(shù)據(jù)加載到 Buffer Pool 的同時，會將這條數(shù)據(jù)的原始記錄保存到 undo 日志文件中

• （4）innodb 會在 Buffer Pool 中執(zhí)行更新操作

• （5）更新后的數(shù)據(jù)會記錄在 redo log buffer 中

• （6）提交事務在提交的同時會做以下三件事

• （7）（第一件事）將redo log buffer中的數(shù)據(jù)刷入到redo log文件中

• （8）（第二件事）將本次操作記錄寫入到 bin log文件中

• （9）（第三件事）將bin log文件名字和更新內(nèi)容在 bin log 中的位置記錄到redo log中，同時在 redo log 最后添加 commit 標記

• （10）使用一個后臺線程，它會在某個時機將我們Buffer Pool中的更新后的數(shù)據(jù)刷到 MySQL 數(shù)據(jù)庫中，這樣就將內(nèi)存和數(shù)據(jù)庫的數(shù)據(jù)保持統(tǒng)一了

 詳細內(nèi)容請閱讀這篇文章：https://blog.csdn.net/a745233700/article/details/113927318 

3、常用的存儲引擎？InnoDB與MyISAM的區(qū)別？

存儲引擎是對底層物理數(shù)據(jù)執(zhí)行實際操作的組件，為Server服務層提供各種操作數(shù)據(jù)的API。常用的存儲引擎有InnoDB、MyISAM、Memory。這里我們主要介紹InnoDB 與 MyISAM 的區(qū)別：

（1）事務：MyISAM不支持事務，InnoDB支持事務

（2）鎖級別：MyISAM只支持表級鎖，InnoDB支持行級鎖和表級鎖，默認使用行級鎖，但是行鎖只有通過索引查詢數(shù)據(jù)才會使用，否則將使用表鎖。行級鎖在每次獲取鎖和釋放鎖的操作需要消耗比表鎖更多的資源。使用行鎖可能會存在死鎖的情況，但是表級鎖不存在死鎖

（3）主鍵和外鍵：MyISAM 允許沒有任何索引和主鍵的表存在，不支持外鍵。InnoDB的主鍵不能為空且支持主鍵自增長，如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6字節(jié)的主鍵，支持外鍵完整性約束

（4）索引結(jié)構(gòu)：MyISAM 和 InnoDB 都是使用B+樹索引，MyISAM的主鍵索引和輔助索引的Data域都是保存行數(shù)據(jù)記錄的地址。但是InnoDB的主鍵索引的Data域保存的不是行數(shù)據(jù)記錄的地址，而是保存該行的所有數(shù)據(jù)內(nèi)容，而輔助索引的Data域保存的則是主索引的值。

由于InnoDB的輔助索引保存的是主鍵索引的值，所以使用輔助索引需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。這也是為什么不建議使用過長的字段作為主鍵的原因：由于輔助索引包含主鍵列，所以，如果主鍵使用過長的字段，將會導致其他輔助索變得更大，所以爭取盡量把主鍵定義得小一些。

（5）全文索引：MyISAM支持全文索引，InnoDB在5.6版本之前不支持全文索引，5.6版本及之后的版本開始支持全文索引

（6）表的具體行數(shù)：

• ① MyISAM：保存有表的總行數(shù)，如果使用 select count() from table 會直接取出出該值，不需要進行全表掃描。

• ② InnoDB：沒有保存表的總行數(shù)，如果使用 select count() from table 需要會遍歷整個表，消耗相當大。

（7）存儲結(jié)構(gòu)：

• ① MyISAM會在磁盤上存儲成三個文件：.frm文件存儲表定義，.MYD文件存儲數(shù)據(jù)，.MYI文件存儲索引。

• ② InnoDB：把數(shù)據(jù)和索引存放在表空間里面，所有的表都保存在同一個數(shù)據(jù)文件中，InnoDB表的大小只受限于操作系統(tǒng)文件的大小，一般為2GB。

（8）存儲空間：

• ① MyISAM：可被壓縮，存儲空間較小。支持三種不同的存儲格式：靜態(tài)表(默認，但是注意數(shù)據(jù)末尾不能有空格，會被去掉)、動態(tài)表、壓縮表。

• ② InnoDB：需要更多的內(nèi)存和存儲，它會在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。

（9）適用場景：

• ① 如果需要提供回滾、崩潰恢復能力的ACID事務能力，并要求實現(xiàn)行鎖級別并發(fā)控制，InnoDB是一個好的選擇；

• ② 如果數(shù)據(jù)表主要用來查詢記錄，讀操作遠遠多于寫操作且不需要數(shù)據(jù)庫事務的支持，則MyISAM引擎能提供較高的處理效率；

備注：在mysql8.0版本中已經(jīng)廢棄了MyISAM存儲引擎 

4、事務的ACID與實現(xiàn)原理？

數(shù)據(jù)庫的事務是并發(fā)控制的基本單位，是指邏輯上的一組操作，要么全部執(zhí)行，要么全部不執(zhí)行。

4.1、事務的ACID：

• （1）原子性：事務是一個不可分割的工作單元，事務里的操作要么都成功，要么都失敗，如果事務執(zhí)行失敗，則需要進行回滾。

• （2）隔離性：事務的所操作的數(shù)據(jù)在提交之前，對其他事務的可見程度。

• （3）持久性：一旦事務提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久的。

• （4）一致性：事務不能破壞數(shù)據(jù)的完整性和業(yè)務的一致性。例如在轉(zhuǎn)賬時，不管事務成功還是失敗，雙方錢的總額不變。

4.2、ACID的實現(xiàn)原理：

4.2.1、原子性：原子性是通過MySQL的回滾日志undo log來實現(xiàn)的：當事務對數(shù)據(jù)庫進行修改時，InnoDB會生成對應的undo log；如果事務執(zhí)行失敗或調(diào)用了rollback，導致事務需要回滾，便可以利用undo log中的信息將數(shù)據(jù)回滾到修改之前的樣子。

4.2.2、隔離性：

（1）事務的隔離級別：

為保證在并發(fā)環(huán)境下讀取數(shù)據(jù)的完整性和一致性，數(shù)據(jù)庫提供了四種事務隔離級別，隔離級別越高，越能保證數(shù)據(jù)的完整性和一致性，但對高并發(fā)性能影響也越大，執(zhí)行效率越低。（四種隔離級別從上往下依次升高）

• 讀未提交：允許事務在執(zhí)行過程中，讀取其他事務尚未提交的數(shù)據(jù)；

• 讀已提交：允許事務在執(zhí)行過程中讀取其他事務已經(jīng)提交的數(shù)據(jù)；

• 可重復讀（默認級別）：在同一個事務內(nèi)，任意時刻的查詢結(jié)果都是一致的；

• 讀序列化：所有事務逐個依次執(zhí)行，每次讀都需要獲取表級共享鎖，讀寫會相互阻塞。

（2）事務的并發(fā)問題：

如果不考慮事務的隔離性，在事務并發(fā)的環(huán)境下，可能存在問題有：

• 更新丟失：兩個或多個事務操作相同的數(shù)據(jù)，然后基于選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發(fā)生丟失更新問題：最后的更新覆蓋了其他事務所做的更新。

• 臟讀：指事務A正在訪問數(shù)據(jù)，并且對數(shù)據(jù)進行了修改（事務未提交），這時，事務B也使用這個數(shù)據(jù)，后來事務A撤銷回滾，并把修改后的數(shù)據(jù)恢復原值，B讀到的數(shù)據(jù)就與數(shù)據(jù)庫中的數(shù)據(jù)不一致，即B讀到的數(shù)據(jù)是臟數(shù)據(jù)。

• 不可重復讀：在一個事務內(nèi)，多次讀取同一個數(shù)據(jù)，但是由于另一個事務在此期間對這個數(shù)據(jù)做了修改并提交，導致前后讀取到的數(shù)據(jù)不一致；

• 幻讀：在一個事務中，先后兩次進行讀取相同的數(shù)據(jù)（一般是范圍查詢），但由于另一個事務新增或者刪除了數(shù)據(jù)，導致前后兩次結(jié)果不一致。

不同的事務隔離級別，在并發(fā)環(huán)境會存在不同的并發(fā)問題：

（3）事務隔離性的實現(xiàn)原理：

Innodb事務的隔離級別是由MVVC和鎖機制實現(xiàn)的：

① MVCC（Multi-Version Concurrency Control，多版本并發(fā)控制）是 MySQL 的 InnoDB 存儲引擎實現(xiàn)事務隔離級別的一種具體方式，用于實現(xiàn)讀已提交和可重復讀這兩種隔離級別。而讀未提交隔離級別總是讀取最新的數(shù)據(jù)行，無需使用 MVCC。讀序列化隔離級別需要對所有讀取的行都加鎖，單純使用 MVCC 無法實現(xiàn)。

MVCC是通過在每行記錄后面保存兩個隱藏的列來實現(xiàn)的，一個保存了行的事務ID，一個保存了行的回滾段指針。每開始一個新的事務，都會自動遞增產(chǎn)生一個新的事務ID。事務開始時會把該事務ID放到當前事務影響的行事務ID字段中，而回滾段的指針有該行記錄上的所有版本數(shù)據(jù)，在undo log回滾日志中通過鏈表形式組織，也就是說該值實際指向undo log中該行的歷史記錄鏈表。

在并發(fā)訪問數(shù)據(jù)庫時，對正在事務中的數(shù)據(jù)做MVCC多版本的管理，以避免寫操作阻塞讀操作，并且可以通過比較版本解決幻讀。

② 鎖機制：

MySQL鎖機制的基本工作原理就是：事務在修改數(shù)據(jù)庫之前，需要先獲得相應的鎖，獲得鎖的事務才可以修改數(shù)據(jù)；在該事務操作期間，這部分的數(shù)據(jù)是鎖定，其他事務如果需要修改數(shù)據(jù)，需要等待當前事務提交或回滾后釋放鎖。

• 排它鎖解決臟讀

• 共享鎖解決不可重復讀

• 臨鍵鎖解決幻讀

4.2.3、持久性：

持久性的依靠redo log日志實現(xiàn)，在執(zhí)行SQL時會保存已執(zhí)行的SQL語句到一個redo log文件，但是為了提高效率，將數(shù)據(jù)寫入到redo log之前，會先寫入到內(nèi)存中的redo log buffer緩存區(qū)中。寫入過程如下：當向數(shù)據(jù)庫寫入數(shù)據(jù)時，執(zhí)行過程會首先寫入redo log buffer，redo log buffer中修改的數(shù)據(jù)會定期刷新到磁盤的redo log文件中，這一過程稱為刷盤（即redo log buffer寫日志到磁盤的redo log file中 ）。

redo log buffer的使用可以大大提高了讀寫數(shù)據(jù)的效率，但是也帶了新的問題：如果MySQL宕機，而此時redo log buffer中修改的數(shù)據(jù)在內(nèi)存還沒有刷新到磁盤，就會導致數(shù)據(jù)的丟失，事務的持久性無法保證。為了確保事務的持久性，在當事務提交時，會調(diào)用fsync接口對redo log進行刷盤 ，刷新頻率由 innodb_flush_log_at_trx_commit變量來控制的：

• 0：表示不刷入磁盤；

• 1：事務每次提交的時候，就把緩沖池中的數(shù)據(jù)刷新到磁盤中；

• 2：提交事務的時候，把緩沖池中的數(shù)據(jù)寫入磁盤文件對應的 os cache 緩存里去，而不是直接進入磁盤文件。可能 1 秒后才會把 os cache 里的數(shù)據(jù)寫入到磁盤文件里去。

4.2.4、一致性：

一致性指的是事務不能破壞數(shù)據(jù)的完整性和業(yè)務的一致性 ：

• 數(shù)據(jù)的完整性：實體完整性、列完整性（如字段的類型、大小、長度要符合要求）、外鍵約束等

• 業(yè)務的一致性：例如在銀行轉(zhuǎn)賬時，不管事務成功還是失敗，雙方錢的總額不變。

該部分詳情可參考這篇博客：https://blog.csdn.net/a745233700/article/details/84207186 

5、數(shù)據(jù)庫中的鎖機制？

當數(shù)據(jù)庫中多個事務并發(fā)存取同一數(shù)據(jù)的時候，若對并發(fā)操作不加控制就可能會讀取和存儲不正確的數(shù)據(jù)，破壞數(shù)據(jù)庫的一致性。MySQL鎖機制的基本工作原理就是，事務在修改數(shù)據(jù)庫之前，需要先獲得相應的鎖，獲得鎖的事務才可以修改數(shù)據(jù)；在該事務操作期間，這部分的數(shù)據(jù)是鎖定，其他事務如果需要修改數(shù)據(jù)，需要等待當前事務提交或回滾后釋放鎖。

按照不同的分類方式，鎖的種類可以分為以下幾種：

• 按鎖的粒度劃分：表級鎖、行級鎖、頁級鎖； 

• 按鎖的類型劃分：共享（鎖S鎖）、排他鎖（X鎖）；

• 按鎖的使用策略劃分：樂觀鎖、悲觀鎖；

5.1、表級鎖、行級鎖、頁級鎖：

• 表級鎖：最大粒度的鎖級別，發(fā)生鎖沖突的概率最高，并發(fā)度最低，但開銷小，加鎖快，不會出現(xiàn)死鎖；

• 行級鎖：最小粒度的所級別，發(fā)生鎖沖突的概率最小，并發(fā)度最高，但開銷大，加鎖慢，會發(fā)生死鎖；

• 頁級鎖：鎖粒度界于表級鎖和行級鎖之間，對表級鎖和行級鎖的折中，并發(fā)度一般。開銷和加鎖時間也界于表鎖和行鎖之間，會出現(xiàn)死鎖；

不同的存儲引擎支持不同的鎖機制：

• InnoDB存儲引擎支持行級鎖和表級鎖，默認情況下使用行級鎖，但只有通過索引進行查詢數(shù)據(jù)，才使用行級鎖，否就使用表級鎖。

• MyISAM和MEMORY存儲引擎采用的是表級鎖；

• BDB存儲引擎使用的是頁面鎖，但也支持表級鎖；

5.2、InnoDB的行鎖：

InnoDB的行鎖有兩種類型：

• 共享鎖（S鎖、讀鎖）：多個事務可以對同一數(shù)據(jù)行共享一把S鎖，但只能進行讀不能修改；

• 排它鎖（X鎖、寫鎖）：一個事務獲取排它鎖之后，可以對鎖定范圍內(nèi)的數(shù)據(jù)行執(zhí)行寫操作，在鎖定期間，其他事務不能再獲取這部分數(shù)據(jù)行的鎖（共享鎖、排它鎖），只允許獲取到排它鎖的事務進行更新數(shù)據(jù)。

對于update，delete，insert 操作，InnoDB會自動給涉及的數(shù)據(jù)行加排他鎖；對于普通SELECT語句，InnoDB不會加任何鎖。

5.3、InnoDB的表鎖與意向鎖：

因為InnoDB引擎允許行鎖和表鎖共存，實現(xiàn)多粒度的鎖機制，但是表鎖和行鎖雖然鎖定范圍不同，但是會相互沖突。當你要加表鎖時，勢必要先遍歷該表的所有記錄，判斷是否有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式，MySQL引入了意向鎖，來檢測表鎖和行鎖的沖突。

意向鎖也是表級鎖，分為讀意向鎖（IS鎖）和寫意向鎖（IX鎖）。當事務要在記錄上加上行鎖時，則先在表上加上對應的意向鎖。之后事務如果想進行鎖表，只要先判斷是否有意向鎖存在，存在時則可快速返回該表不能啟用表鎖，否則就需要等待，提高效率。

5.4、InnoDB行鎖的實現(xiàn)與臨鍵鎖：

InnoDB的行鎖是通過給索引上的索引項加鎖來實現(xiàn)的。只有通過索引檢索數(shù)據(jù)，才能使用行鎖，否則將使用表鎖。

在InnoDB中，為了解決幻讀的現(xiàn)象，引入了臨鍵鎖(next-key)。根據(jù)索引，劃分為一個個左開右閉的區(qū)間。當進行范圍查詢的時候，若命中索引且能夠檢索到數(shù)據(jù)，則鎖住記錄所在的區(qū)間和它的下一個區(qū)間。其實，臨鍵鎖(Next-Key) = 記錄鎖(Record Locks) + 間隙鎖(Gap Locks)

• 間隙鎖：當使用范圍查詢而不是精準查詢進行檢索數(shù)據(jù)，并請求共享或排它鎖時，InnoDB會給符合范圍條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做間隙(GAP)。

• 記錄鎖：當使用唯一索引，且記錄存在的精準查詢時，使用記錄鎖

5.5、利用鎖機制解決并發(fā)問題：

• X鎖解決臟讀

• S鎖解決不可重復讀

• 臨鍵鎖解決幻讀

InnoDB存儲引擎鎖機制的詳細內(nèi)容和MyISAM存儲引擎的鎖機制的詳細內(nèi)容可以閱讀這篇文章：https://blog.csdn.net/a745233700/article/details/84504209 

6、MySQL索引的實現(xiàn)原理：

索引本質(zhì)上就是一種通過減少查詢需要遍歷行數(shù)，加快查詢性能的數(shù)據(jù)結(jié)構(gòu)，避免數(shù)據(jù)庫進行全表掃描，好比書的目錄，讓你更快的找到內(nèi)容。（一個表最多16個索引）

6.1、索引的優(yōu)缺點：

（1）索引的優(yōu)點：

• 減少查詢需要檢索的行數(shù)，加快查詢速度，避免進行全表掃描，這也是創(chuàng)建索引的最主要的原因。

• 如果索引的數(shù)據(jù)結(jié)構(gòu)是B+樹，在使用分組和排序時，可以顯著減少查詢中分組和排序的時間。

• 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

（2）索引的缺點：

• 當對表中的數(shù)據(jù)進行增加、刪除和修改時，索引也要進行更新，維護的耗時隨著數(shù)據(jù)量的增加而增加。

• 索引需要占用物理空間，如果要建立聚簇索引，那么需要的空間就會更大。

6.2、索引的使用場景：

（1）在哪些列上面創(chuàng)建索引：

• WHERE子句中經(jīng)常出現(xiàn)的列上面創(chuàng)建索引，加快條件的判斷速度。

• 按范圍存取的列或者在group by或order by中使用的列，因為索引已經(jīng)排序，這樣可以利用索引加快排序查詢時間。

• 經(jīng)常用于連接的列上，這些列主要是一些外鍵，可以加快連接的速度；

• 作為主鍵的列上，強制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；

（2）不在哪些列建索引？

• 區(qū)分度不高的列。由于這些列的取值很少，例如性別，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。

• 在查詢中很少的列不應該創(chuàng)建索引。由于這些列很少使用到，但增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求。

• 當添加索引造成修改成本的提高 遠遠大于 檢索性能的提高時，不應該創(chuàng)建索引。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。

• 定義為text, image和bit數(shù)據(jù)類型的列不應該增加索引。這些列的數(shù)據(jù)量要么相當大，要么取值很少。

6.3、 索引的分類：

（1）普通索引、唯一索引、主鍵索引、全文索引、組合索引。

• 普通索引：最基本的索引，沒有任何限制

• 唯一索引：但索引列的值必須唯一，允許有空值，可以有多個NULL值。如果是組合索引，則列值的組合必須唯一。

• 主鍵索引：一種特殊的唯一索引，不允許有空值。

• 全文索引：全文索引僅可用于 MyISAM 表，并只支持從CHAR、VARCHAR或TEXT類型，用于替代效率較低的like 模糊匹配操作，而且可以通過多字段組合的全文索引一次性全模糊匹配多個字段。

• 組合索引：主要是為了提高mysql效率，創(chuàng)建組合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。

（2）聚簇索引與非聚簇索引：

如果按數(shù)據(jù)存儲的物理順序與索引值的順序分類，可以將索引分為聚簇索引與非聚簇索引兩類：

• 聚簇索引：表中數(shù)據(jù)存儲的物理順序與索引值的順序一致，一個基本表最多只能有一個聚簇索引，更新聚簇索引列上的數(shù)據(jù)時，往往導致表中記錄的物理順序的變更，代價較大，因此對于經(jīng)常更新的列不宜建立聚簇索引

• 非聚簇索引：表中數(shù)據(jù)的物理順序與索引值的順序不一致的索引組織，一個基本表可以有多個聚簇索引。

6.4、索引的數(shù)據(jù)結(jié)構(gòu)：

常見的索引的數(shù)據(jù)結(jié)構(gòu)有：B+Tree、Hash索引。

（1）Hash索引：MySQL中只有Memory存儲引擎支持hash索引，是Memory表的默認索引類型。hash索引把數(shù)據(jù)以hash值形式組織起來，因此查詢效率非常高，可以一次定位。

hash索引的缺點：

• Hash索引僅能滿足等值的查詢，不能滿足范圍查詢、排序。因為數(shù)據(jù)在經(jīng)過Hash算法后，其大小關系就可能發(fā)生變化。

• 當創(chuàng)建組合索引時，不能只使用組合索引的部分列進行查詢。因為hash索引是把多個列數(shù)據(jù)合并后再計算Hash值，所以對單獨列數(shù)據(jù)計算Hash值是沒有意義的。

• 當發(fā)生Hash碰撞時，Hash索引不能避免表數(shù)據(jù)的掃描。因為僅僅比較Hash值是不夠的，需要比較實際的值以判定是否符合要求。

（2）B+Tree索引：B+Tree是mysql使用最頻繁的一個索引數(shù)據(jù)結(jié)構(gòu)，是Innodb和Myisam存儲引擎模式的索引類型。B+Tree索引在查找時需要從根節(jié)點到葉節(jié)點進行多次IO操作，在查詢速度比不上Hash索引，但是更適合排序等操作。

B+Tree索引的優(yōu)點：

• 頁內(nèi)節(jié)點不存儲內(nèi)容，每次IO可以讀取更多的行，大大減少磁盤I/O讀取次數(shù)

• 帶順序訪問指針的B+Tree：B+Tree所有索引數(shù)據(jù)都存儲在葉子結(jié)點上，并且增加了順序訪問指針，每個葉子節(jié)點都有指向相鄰葉子節(jié)點的指針，這樣做是為了提高區(qū)間查詢效率。

 6.5、為什么使用B+Tree作為索引：

索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲在磁盤上。這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗，相對于內(nèi)存存取，磁盤I/O存取的消耗要高幾個數(shù)量級，所以評價一個數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標就是在查找過程中磁盤I/O操作次數(shù)的漸進復雜度。換句話說，索引的數(shù)據(jù)結(jié)構(gòu)要盡量減少查找過程中磁盤I/O的存取次數(shù)。

（1）局部性原理與程序預讀：

由于磁盤本身存取就比主存慢很多，再加上機械運動耗費，因此為了提高效率，要盡量減少磁盤I/O。為了達到這個目的，磁盤往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節(jié)，磁盤也會從這個位置開始，順序向后讀取一定長度的數(shù)據(jù)放入內(nèi)存。這樣做的理論依據(jù)是計算機科學中著名的局部性原理：當一個數(shù)據(jù)被用到時，其附近的數(shù)據(jù)也通常會馬上被使用。程序運行期間所需要的數(shù)據(jù)通常比較集中。

由于磁盤順序讀取的效率很高（不需要尋道時間，只需很少的旋轉(zhuǎn)時間），因此對于具有局部性的程序來說，預讀可以提高I/O效率。預讀的長度一般為頁的整倍數(shù)。當程序要讀取的數(shù)據(jù)不在主存中時，會觸發(fā)一個缺頁異常，此時系統(tǒng)會向磁盤發(fā)出讀盤信號，磁盤會找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁或幾頁載入內(nèi)存中，然后異常返回，程序繼續(xù)運行。

（2）B+Tree索引的性能分析：

上文說過一般使用磁盤I/O次數(shù)評價索引結(jié)構(gòu)的優(yōu)劣。我們先從B樹分析，B樹檢索一次最多需要訪問h個節(jié)點，同時，數(shù)據(jù)庫巧妙利用了磁盤預讀原理，將一個節(jié)點的大小設為等于一個頁，即每次新建節(jié)點時，直接申請一個頁的空間，這樣就保證一個節(jié)點在物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，這樣就實現(xiàn)了每個節(jié)點只需要一次I/O就可以完全載入。B樹中一次檢索最多需要h-1次I/O（根節(jié)點常駐內(nèi)存），時間復雜度為O(h)=O(logdN)。一般實際應用中，出度d是非常大的數(shù)字，通常超過100，因此h非常小。綜上所述，用B樹作為索引結(jié)構(gòu)效率是非常高的。

而紅黑樹這種結(jié)構(gòu)，雖然時間復雜度也為O(h)，但是h明顯要深的多，并且由于邏輯上很近的節(jié)點，在物理上可能很遠，無法利用局部性，所以IO效率明顯比B樹差很多。

另外，B+Tree更適合作為索引的數(shù)據(jù)結(jié)構(gòu)，原因和內(nèi)節(jié)點出度d有關。從上面分析可以看到，d越大索引的性能越好，而出度d的上限取決于節(jié)點內(nèi)key和data的大小，由于B+Tree內(nèi)節(jié)點去掉了data域，因此可以擁有更大的出度，磁盤IO的次數(shù)也就更少了。

（3）B+樹索引 和 B樹索引 的對比？

根據(jù)B-Tree 和 B+Tree的結(jié)構(gòu)，我們可以發(fā)現(xiàn)B+樹相比于B樹，在文件系統(tǒng)或者數(shù)據(jù)庫系統(tǒng)當中，更有優(yōu)勢，原因如下：

• （1）B+樹有利于對數(shù)據(jù)庫的掃描：B樹在提高了磁盤IO性能的同時并沒有解決元素遍歷的效率低下的問題，而B+樹只需要遍歷葉子節(jié)點就可以解決對全部關鍵字信息的掃描，所以范圍查詢、排序等操作，B+樹有著更高的性能。

• （2）B+樹的磁盤IO代價更低：B+樹的內(nèi)部結(jié)點的data域并沒有存儲數(shù)據(jù)，因此其內(nèi)部結(jié)點相對于B樹更小。如果把所有同一內(nèi)部結(jié)點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數(shù)量也越多。一次性讀入內(nèi)存中的需要查找的關鍵字也就越多，相對來說I/O讀寫次數(shù)也就降低了。

• （3）B+樹的查詢效率更加穩(wěn)定：由于B+樹的內(nèi)部結(jié)點只是葉子結(jié)點中關鍵字的索引，并不存儲數(shù)據(jù)。所以任何關鍵字的查找必須走一條從根結(jié)點到葉子結(jié)點的路。所有關鍵字查詢的路徑長度相同，導致每一個數(shù)據(jù)的查詢效率相當。

（4）MySQL的 InnoDB 和 MyISAM 存儲引擎中B+Tree索引的實現(xiàn)？

MyISAM和InnoDB都是使用B+樹索引，MyISAM的主鍵索引和輔助索引的Data域都是保存行的地址，但是InnoDB的主鍵索引保存的不是行的地址，而是保存該行的所有所有數(shù)據(jù)，而輔助索引的Data域保存的則是主索引的值。

索引的長度限制：

• 對于 Innodb 的組合索引，如果各個列中的長度超過767字節(jié)的，則會對超過767字節(jié)的列取前綴索引；對于 Innodb 的單列索引，如果列的長度超過767的，則取前綴索引（取前255字符）

• 對于 MyISAM 的組合索引，所創(chuàng)建的索引長度和不能超過1000 bytes，否則會報錯，創(chuàng)建失敗；對于 MyISAM 的單列索引，最大長度也不能超過1000，否則會報警，但是創(chuàng)建成功，最終創(chuàng)建的是前綴索引（取前333個字符）

索引的實現(xiàn)原理詳細閱讀：https://blog.csdn.net/a745233700/article/details/80798181 

7、SQL優(yōu)化和索引優(yōu)化、表結(jié)構(gòu)優(yōu)化：

（1）MySQL的SQL優(yōu)化和索引優(yōu)化：https://blog.csdn.net/a745233700/article/details/84455241

（2）MySQL的表結(jié)構(gòu)優(yōu)化：https://blog.csdn.net/a745233700/article/details/84405087 

8、數(shù)據(jù)庫參數(shù)優(yōu)化：

MySQL屬于 IO 密集型的應用程序，主要職責就是數(shù)據(jù)的管理及存儲工作。而我們知道，從內(nèi)存中讀取一個數(shù)據(jù)庫的時間是微秒級別，而從一塊普通硬盤上讀取一個IO是在毫秒級別，二者相差3個數(shù)量級。所以，要優(yōu)化數(shù)據(jù)庫，首先第一步需要優(yōu)化的就是 IO，盡可能將磁盤IO轉(zhuǎn)化為內(nèi)存IO。所以對于MySQL數(shù)據(jù)庫的參數(shù)優(yōu)化上，主要針對減少磁盤IO的參數(shù)做優(yōu)化：比如使用 query_cache_size 調(diào)整查詢緩存的大小，使用 innodb_buffer_pool_size 調(diào)整緩沖區(qū)的大?。?/span>

MySQL的參數(shù)優(yōu)化：https://blog.csdn.net/a745233700/article/details/114506553 

9、explain的執(zhí)行計劃：

執(zhí)行計劃是SQL語句經(jīng)過查詢分析器后得到的 抽象語法樹 和 相關表的統(tǒng)計信息 作出的一個查詢方案，這個方案是由查詢優(yōu)化器自動分析產(chǎn)生的。由于是動態(tài)數(shù)據(jù)采樣統(tǒng)計分析出來的結(jié)果，所以可能會存在分析錯誤的情況，也就是存在執(zhí)行計劃并不是最優(yōu)的情況。通過explain關鍵字知道MySQL是如何執(zhí)行SQL查詢語句的，分析select 語句的性能瓶頸，從而改進我們的查詢，explain的結(jié)果如下：

重要的有id、type、key、key_len、rows、extra：

（1）id：id列可以理解為SQL執(zhí)行順序的標識，有幾個select 就有幾個id。

• id值不同：id值越大優(yōu)先級越高，越先被執(zhí)行；

• id值相同：從上往下依次執(zhí)行；

• id列為null：表示這是一個結(jié)果集，不需要使用它來進行查詢。

（2）select_type：查詢的類型，主要用于區(qū)分普通查詢、聯(lián)合查詢、子查詢等復雜的查詢；

（3）table：表示 explain 的一行正在訪問哪個表

（4）type：訪問類型，即MySQL決定如何查找表中的行。依次從好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了all之外，其他的 type 類型都可以使用到索引，除了 index_merge 之外，其他的type只可以用到一個索引。一般要求type為 ref 級別，范圍查找需要達到 range 級別。

• system：表中只有一條數(shù)據(jù)匹配（等于系統(tǒng)表），可以看成 const 類型的特例

• const：通過索引一次就找到了，表示使用主鍵索引或者唯一索引

• eq_ref：主鍵或者唯一索引中的字段被用于連接使用，只會返回一行匹配的數(shù)據(jù)

• ref：普通索引掃描，可能返回多個符合查詢條件的行。

• fulltext：全文索引檢索，全文索引的優(yōu)先級很高，若全文索引和普通索引同時存在時，mysql不管代價，優(yōu)先選擇使用全文索引。

• ref_or_null：與ref方法類似，只是增加了null值的比較。

• index_merge：表示查詢使用了兩個以上的索引，索引合并的優(yōu)化方法，最后取交集或者并集，常見and ，or的條件使用了不同的索引。

• unique_subquery：用于where中的in形式子查詢，子查詢返回不重復值唯一值；

• index_subquery：用于in形式子查詢使用到了輔助索引或者in常數(shù)列表，子查詢可能返回重復值，可以使用索引將子查詢?nèi)ブ亍?/span>

• range：索引范圍掃描，常見于使用>,<,between ,in ,like等運算符的查詢中。

• index：索引全表掃描，把索引樹從頭到尾掃描一遍；

• all：遍歷全表以找到匹配的行（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤讀取）

• NULL：MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引

（5）possible_keys：查詢時可能使用到的索引

（6）key：實際使用哪個索引來優(yōu)化對該表的訪問

（7）key_len：實際上用于優(yōu)化查詢的索引長度，即索引中使用的字節(jié)數(shù)。通過這個值，可以計算出一個多列索引里實際使用了索引的哪寫字段。

（8）ref：顯示哪個字段或者常量與key一起被使用

（9）rows：根據(jù)表統(tǒng)計信息及索引選用情況，大致估算此處查詢需要讀取的行數(shù)，不是精確值。

（10）extra：其他的一些額外信息

• using index：使用覆蓋索引

• using index condition：查詢的列未被索引覆蓋，where篩選條件使用了索引

• using temporary：用臨時表保存中間結(jié)果，常用于 group by 和 order by 操作中，通常是因為 group by 的列上沒有索引，也有可能是因為同時有g(shù)roup by和order by，但group by和order by的列又不一樣，一般看到它說明查詢需要優(yōu)化了

• using filesort：MySQL有兩種方式對查詢結(jié)果進行排序，一種是使用索引，另一種是filesort（基于快排實現(xiàn)的外部排序，性能比較差），當數(shù)據(jù)量很大時，這將是一個CPU密集型的過程，所以可以通過建立合適的索引來優(yōu)化排序的性能

對explain執(zhí)行計劃詳請感興趣的讀者可以閱讀這篇文章：https://blog.csdn.net/a745233700/article/details/84335453 

10、MySQL的主從復制：

10.1、MySQL主從復制的原理：

Slave從Master獲取binlog二進制日志文件，然后再將日志文件解析成相應的SQL語句在從服務器上重新執(zhí)行一遍主服務器的操作，通過這種方式來保證數(shù)據(jù)的一致性。由于主從復制的過程是異步復制的，因此Slave和Master之間的數(shù)據(jù)有可能存在延遲的現(xiàn)象，只能保證數(shù)據(jù)最終的一致性。在master和slave之間實現(xiàn)整個復制過程主要由三個線程來完成：

• （1）Slave SQL thread線程：創(chuàng)建用于讀取relay log中繼日志并執(zhí)行日志中包含的更新，位于slave端

• （2）Slave I/O thread線程：讀取 master 服務器Binlog Dump線程發(fā)送的內(nèi)容并保存到slave服務器的relay log中繼日志中，位于slave端：

• （3）Binlog dump thread線程(也稱為IO線程)：將bin-log二進制日志中的內(nèi)容發(fā)送到slave服務器，位于master端

注意：如果一臺主服務器配兩臺從服務器那主服務器上就會有兩個Binlog dump 線程，而每個從服務器上各自有兩個線程；

10.2、主從復制流程：

• （1）master服務器在執(zhí)行SQL語句之后，記錄在binlog二進制文件中；

• （2）slave端的IO線程連接上master端，并請求從指定bin log日志文件的指定pos節(jié)點位置（或者從最開始的日志）開始復制之后的日志內(nèi)容。

• （3）master端在接收到來自slave端的IO線程請求后，通知負責復制進程的IO線程，根據(jù)slave端IO線程的請求信息，讀取指定binlog日志指定pos節(jié)點位置之后的日志信息，然后返回給slave端的IO線程。該返回信息中除了binlog日志所包含的信息之外，還包括本次返回的信息在master端的binlog文件名以及在該binlog日志中的pos節(jié)點位置。

• （4）slave端的IO線程在接收到master端IO返回的信息后，將接收到的binlog日志內(nèi)容依次寫入到slave端的relay log文件的最末端，并將讀取到的master端的binlog文件名和pos節(jié)點位置記錄到master-info文件中（該文件存slave端），以便在下一次同步的候能夠告訴master從哪個位置開始進行數(shù)據(jù)同步；

• （5）slave端的SQL線程在檢測到relay log文件中新增內(nèi)容后，就馬上解析該relay log文件中的內(nèi)容，然后還原成在master端真實執(zhí)行的那些SQL語句，再按順序依次執(zhí)行這些SQL語句，從而到達master端和slave端的數(shù)據(jù)一致性；

10.3、主從復制的好處：

• （1）讀寫分離，通過動態(tài)增加從服務器來提高數(shù)據(jù)庫的性能，在主服務器上執(zhí)行寫入和更新，在從服務器上執(zhí)行讀功能。

• （2）提高數(shù)據(jù)安全，因為數(shù)據(jù)已復制到從服務器，從服務器可以終止復制進程，所以，可以在從服務器上備份而不破壞主服務器相應數(shù)據(jù)。

• （3）在主服務器上生成實時數(shù)據(jù)，而在從服務器上分析這些數(shù)據(jù)，從而提高主服務器的性能。

10.4、MySQL支持的復制類型及其優(yōu)缺點：

binlog日志文件有兩種格式，一種是Statement-Based（基于語句的復制），另一種是Row-Based（基于行的復制）。默認格式為Statement-Based，如果想改變其格式在開啟服務的時候使用 -binlog-format 選項，其具體命令如下：

mysqld_safe –user=msyql –binlog-format=格式 &

（1）基于語句的復制（Statement-Based）：在主服務器上執(zhí)行的SQL語句，在從服務器上執(zhí)行同樣的語句。效率比較高。一旦發(fā)現(xiàn)沒法精確復制時，會自動選著基于行的復制。 

優(yōu)點：

• ① 因為記錄的SQL語句，所以占用更少的存儲空間。binlog日志包含了描述數(shù)據(jù)庫操作的事件，但這些事件包含的情況只是對數(shù)據(jù)庫進行改變的操作，例如 insert、update、create、delete等操作。相反對于select、desc等類似的操作并不會去記錄。

• ② binlog日志文件記錄了所有的改變數(shù)據(jù)庫的語句，所以此文件可以作為數(shù)據(jù)庫的審核依據(jù)。

缺點：

• ① 不安全，不是所有的改變數(shù)據(jù)的語句都會被記錄。對于非確定性的行為不會被記錄。例如：對于 delete 或者 update 語句，如果使用了 limit 但是并沒有 order by ，這就屬于非確定性的語句，就不會被記錄。

• ② 對于沒有索引條件的update，insert……select 語句，必須鎖定更多的數(shù)據(jù)，降低了數(shù)據(jù)庫的性能。

（2）基于行的復制（Row-Based）：把改變的內(nèi)容復制過去，而不是把命令在從服務器上執(zhí)行一遍，從mysql5.0開始支持；

優(yōu)點：

• ① 所有的改變都會被復制，這是最安全的復制方式；

• ② 對于 update、insert……select等語句鎖定更少的行；

缺點：

• ① 不能通過binlog日志文件查看什么語句執(zhí)行了，也無從知道在從服務器上接收到什么語句，我們只能看到什么數(shù)據(jù)改變。

• ② 因為記錄的是數(shù)據(jù)，所以說binlog日志文件占用的存儲空間要比Statement-based大。

• ③ 對于數(shù)據(jù)量大的操作其花費的時間有更長。

（3）混合類型的復制：默認采用基于語句的復制，一旦發(fā)現(xiàn)基于語句的無法精確的復制時，就會采用基于行的復制。

有關主從復制更詳細的內(nèi)容，請閱讀這篇文章：https://blog.csdn.net/a745233700/article/details/85256818 

11、讀寫分離：

11.1、實現(xiàn)原理：

讀寫分離解決的是，數(shù)據(jù)庫的寫操作，影響了查詢的效率，適用于讀遠大于寫的場景。讀寫分離的實現(xiàn)基礎是主從復制，主數(shù)據(jù)庫利用主從復制將自身數(shù)據(jù)的改變同步到從數(shù)據(jù)庫集群中，然后主數(shù)據(jù)庫負責處理寫操作（當然也可以執(zhí)行讀操作），從數(shù)據(jù)庫負責處理讀操作，不能執(zhí)行寫操作。并可以根據(jù)壓力情況，部署多個從數(shù)據(jù)庫提高讀操作的速度，減少主數(shù)據(jù)庫的壓力，提高系統(tǒng)總體的性能。

11.2、讀寫分離提高性能的原因：

• （1）增加物理服務器，負荷分攤；

• （2）主從只負責各自的寫和讀，極大程度的緩解X鎖和S鎖爭用；

• （3）從庫可配置MyISAM引擎，提升查詢性能以及節(jié)約系統(tǒng)開銷；

• （4）主從復制另外一大功能是增加冗余，提高可用性，當一臺數(shù)據(jù)庫服務器宕機后能通過調(diào)整另外一臺從庫來以最快的速度恢復服務。

11.3、Mysql讀寫分寫的實現(xiàn)方式：

• （1）基于程序代碼內(nèi)部實現(xiàn)：在代碼中根據(jù)select 、insert進行路由分類。優(yōu)點是性能較好，因為程序在代碼中實現(xiàn)，不需要增加額外的硬件開支，缺點是需要開發(fā)人員來實現(xiàn)，運維人員無從下手。

• （2）基于中間代理層實現(xiàn)：代理一般介于應用服務器和數(shù)據(jù)庫服務器之間，代理數(shù)據(jù)庫服務器接收到應用服務器的請求后根據(jù)判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫，有以下代表性的代理層。 

12、分庫分表：垂直分表、垂直分庫、水平分表、水平分庫

讀寫分離解決的是數(shù)據(jù)庫讀寫操作的壓力，但是沒有分散數(shù)據(jù)庫的存儲壓力，利用分庫分表可以解決數(shù)據(jù)庫的儲存瓶頸，并提升數(shù)據(jù)庫的查詢效率。

12.1、垂直拆分：

（1）垂直分表：將一個表按照字段分成多個表，每個表存儲其中一部分字段。一般會將常用的字段放到一個表中，將不常用的字段放到另一個表中。

優(yōu)點：

• （1）避免IO競爭減少鎖表的概率。因為大的字段效率更低，第一，大字段占用的空間更大，單頁內(nèi)存儲的行數(shù)變少，會使得IO操作增多；第二數(shù)據(jù)量大，需要的讀取時間長。

• （2）可以更好地提升熱門數(shù)據(jù)的查詢效率。

（2）垂直分庫：按照業(yè)務模塊的不同，將表拆分到不同的數(shù)據(jù)庫中，適合業(yè)務之間的耦合度非常低、業(yè)務邏輯清晰的系統(tǒng)。

優(yōu)點：

• 降低業(yè)務中的耦合，方便對不同的業(yè)務進行分級管理

• 可以提升IO、數(shù)據(jù)庫連接數(shù)、解決單機硬件存儲資源的瓶頸問題

（3）垂直拆分（分庫、分表）的缺點：

• 主鍵出現(xiàn)冗余，需要管理冗余列

• 事務的處理變得復雜

• 仍然存在單表數(shù)據(jù)量過大的問題

12.2、水平拆分：

（1）水平分表：在同一個數(shù)據(jù)庫內(nèi)，把同一個表的數(shù)據(jù)按照一定規(guī)則拆分到多個表中。

優(yōu)點：

• 解決了單表數(shù)據(jù)量過大的問題

• 避免IO競爭并減少鎖表的概率

（2）水平分庫：把同一個表的數(shù)據(jù)按照一定規(guī)則拆分到不同的數(shù)據(jù)庫中，不同的數(shù)據(jù)庫可以放到不同的服務器上。

優(yōu)點：

• 解決了單庫大數(shù)據(jù)量的瓶頸問題

• IO沖突減少，鎖的競爭減少，某個數(shù)據(jù)庫出現(xiàn)問題不影響其他數(shù)據(jù)庫，提高了系統(tǒng)的穩(wěn)定性和可用性

（3）水平拆分（分表、分庫）的缺點：

• 分片事務一致性難以解決

• 跨節(jié)點JOIN性能差，邏輯會變得復雜

• 數(shù)據(jù)擴展難度大，不易維護

12.3、分庫分表存在的問題的解決：

（1）事務的問題：

① 方案一：使用分布式事務：

• 優(yōu)點：由數(shù)據(jù)庫管理，簡單有效。

• 缺點：性能代價高，特別是shard越來越多。

② 方案二：程序與數(shù)據(jù)庫共同控制實現(xiàn)，原理就是將一個跨多個數(shù)據(jù)庫的分布式事務分解成多個僅存在于單一數(shù)據(jù)庫上面的小事務，并交由應用程序來總體控制各個小事務。

• 優(yōu)點：性能上有優(yōu)勢；

• 缺點：需要在應用程序在事務上做靈活控制。如果使用了spring的事務管理，改動起來會面臨一定的困難。

（2）跨節(jié)點 Join 的問題：

解決該問題的普遍做法是分兩次查詢實現(xiàn)：在第一次查詢的結(jié)果集中找出關聯(lián)數(shù)據(jù)的id，根據(jù)這些id發(fā)起第二次請求得到關聯(lián)數(shù)據(jù)。

（3）跨節(jié)點count，order by，group by，分頁和聚合函數(shù)問題：

由于這類問題都需要基于全部數(shù)據(jù)集合進行計算。多數(shù)的代理都不會自動處理合并工作，解決方案：與解決跨節(jié)點join問題的類似，分別在各個節(jié)點上得到結(jié)果后在應用程序端進行合并。和 join 不同的是每個結(jié)點的查詢可以并行執(zhí)行，因此速度要比單一大表快很多。但如果結(jié)果集很大，對應用程序內(nèi)存的消耗是一個問題。

12.4、分庫分表后，ID鍵如何處理？

分庫分表后不能每個表的ID都是從1開始，所以需要一個全局ID，設置全局ID主要有以下幾種方法：

（1）UUID：

• 優(yōu)點：本地生成ID，不需要遠程調(diào)用，全局唯一不重復。

• 缺點：占用空間大，不適合作為索引。

（2）數(shù)據(jù)庫自增ID：在分庫分表表后使用數(shù)據(jù)庫自增ID，需要一個專門用于生成主鍵的庫，每次服務接收到請求，先向這個庫中插入一條沒有意義的數(shù)據(jù)，獲取一個數(shù)據(jù)庫自增的ID，利用這個ID去分庫分表中寫數(shù)據(jù)。

• 優(yōu)點：簡單易實現(xiàn)。

• 缺點：在高并發(fā)下存在瓶頸。

（3）Redis生成ID：

• 優(yōu)點：不依賴數(shù)據(jù)庫，性能比較好。

• 缺點：引入新的組件會使得系統(tǒng)復雜度增加

（4）Twitter的snowflake算法：是一個64位的long型的ID，其中有1bit是不用的，41bit作為毫秒數(shù)，10bit作為工作機器ID，12bit作為序列號。

• 1bit：第一個bit默認為0，因為二進制中第一個bit為1的話為負數(shù)，但是ID不能為負數(shù).

• 41bit：表示的是時間戳，單位是毫秒。

• 10bit：記錄工作機器ID，其中5個bit表示機房ID，5個bit表示機器ID。

• 12bit：用來記錄同一毫秒內(nèi)產(chǎn)生的不同ID。

（5）美團的Leaf分布式ID生成系統(tǒng)，美團點評分布式ID生成系統(tǒng)： 

13、分區(qū)：

分區(qū)就是將表的數(shù)據(jù)按照特定規(guī)則存放在不同的區(qū)域，也就是將表的數(shù)據(jù)文件分割成多個小塊，在查詢數(shù)據(jù)的時候，只要知道數(shù)據(jù)數(shù)據(jù)存儲在哪些區(qū)域，然后直接在對應的區(qū)域進行查詢，不需要對表數(shù)據(jù)進行全部的查詢，提高查詢的性能。同時，如果表數(shù)據(jù)特別大，一個磁盤磁盤放不下時，我們也可以將數(shù)據(jù)分配到不同的磁盤去，解決存儲瓶頸的問題，利用多個磁盤，也能夠提高磁盤的IO效率，提高數(shù)據(jù)庫的性能。在使用分區(qū)表時，需要注意分區(qū)字段必須放在主鍵或者唯一索引中、每個表最大分區(qū)數(shù)為1024；常見的分區(qū)類型有：Range分區(qū)、List分區(qū)、Hash分區(qū)、Key分區(qū)，

• （1）Range分區(qū)：按照連續(xù)的區(qū)間范圍進行分區(qū)

• （2）List分區(qū)：按照給定的集合中的值進行選擇分區(qū)。

• （3）Hash分區(qū)：基于用戶定義的表達式的返回值進行分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL中有效的、產(chǎn)生非負整數(shù)值的任何表達式。

• （4）Key分區(qū)：類似于按照HASH分區(qū)，區(qū)別在于Key分區(qū)只支持計算一列或多列，且key分區(qū)的哈希函數(shù)是由 MySQL 服務器提供。

（1）表分區(qū)的優(yōu)點：

① 可伸縮性：

• 將分區(qū)分在不同磁盤，可以解決單磁盤容量瓶頸問題，存儲更多的數(shù)據(jù)，也能解決單磁盤的IO瓶頸問題。

② 提升數(shù)據(jù)庫的性能：

• 減少數(shù)據(jù)庫檢索時需要遍歷的數(shù)據(jù)量，在查詢時只需要在數(shù)據(jù)對應的分區(qū)進行查詢。

• 避免Innodb的單個索引的互斥訪問限制

• 對于聚合函數(shù)，例如sum()和count()，可以在每個分區(qū)進行并行處理，最終只需要統(tǒng)計所有分區(qū)得到的結(jié)果

③ 方便對數(shù)據(jù)進行運維管理：

• 方便管理，對于失去保存意義的數(shù)據(jù)，通過刪除對應的分區(qū)，達到快速刪除的作用。比如刪除某一時間的歷史數(shù)據(jù)，直接執(zhí)行truncate，或者直接drop整個分區(qū)，這比detele刪除效率更高；

• 在某些場景下，單個分區(qū)表的備份很恢復會更有效率。 

14、主鍵一般用自增ID還是UUID？

（1）自增ID：

使用自增ID的好處：

• 字段長度較 UUID 會小很多。

• 數(shù)據(jù)庫自動編號，按順序存放，利于檢索

• 無需擔心主鍵重復問題

使用自增ID的缺點：

• 因為是自增，在某些業(yè)務場景下，容易被其他人查到業(yè)務量。

• 發(fā)生數(shù)據(jù)遷移時，或者表合并時會非常麻煩

• 在高并發(fā)的場景下，競爭自增鎖會降低數(shù)據(jù)庫的吞吐能力

（2）UUID：通用唯一標識碼，UUID是基于當前時間、計數(shù)器和硬件標識等數(shù)據(jù)計算生成的。

使用UUID的優(yōu)點：

• 唯一標識，不用考慮重復問題，在數(shù)據(jù)拆分、合并時也能達到全局的唯一性。

• 可以在應用層生成，提高數(shù)據(jù)庫的吞吐能力。

• 無需擔心業(yè)務量泄露的問題。

使用UUID的缺點：

• 因為UUID是隨機生成的，所以會發(fā)生隨機IO，影響插入速度，并且會造成硬盤的使用率較低。

• UUID占用空間較大，建立的索引越多，造成的影響越大。

• UUID之間比較大小較自增ID慢不少，影響查詢速度。

一般情況下，MySQL推薦使用自增ID，因為在MySQL的 InnoDB 存儲引擎中，主鍵索引是聚簇索引，主鍵索引的B+樹的葉子節(jié)點按照順序存儲了主鍵值及數(shù)據(jù)，如果主鍵索引是自增ID，只需要按順序往后排列即可，如果是UUID，ID是隨機生成的，在數(shù)據(jù)插入時會造成大量的數(shù)據(jù)移動，產(chǎn)生大量的內(nèi)存碎片，造成插入性能的下降。 

15、視圖View：

視圖是從一個或者多個表（或視圖）導出的表，其內(nèi)容由查詢定義。視圖是一個虛擬表，數(shù)據(jù)庫中只存儲視圖的定義，不存儲視圖對應的數(shù)據(jù)，在對視圖的數(shù)據(jù)進行操作時，系統(tǒng)根據(jù)視圖的定義去操作相應的基本表。可以說，視圖是在基本表之上建立的表，它的結(jié)構(gòu)和內(nèi)容都來自基本表，依據(jù)基本表存在而存在。一個視圖可以對應一個基本表，也可以對應多個基本表。視圖是基本表的抽象和在邏輯意義上建立的新關系。

（1）視圖的優(yōu)點：

• 簡化了操作，把經(jīng)常使用的數(shù)據(jù)定義為視圖

• 安全性，用戶只能查詢和修改能看到的數(shù)據(jù)

• 邏輯上的獨立性，屏蔽了真實表的結(jié)構(gòu)帶來的影響

（2）視圖的缺點：

• 性能差，數(shù)據(jù)庫必須把對視圖的查詢轉(zhuǎn)化成對基本表的查詢，如果這個視圖是由一個復雜的多表查詢所定義，那么，即使是視圖的一個簡單查詢，數(shù)據(jù)庫也要把它變成一個復雜的結(jié)合體，需要花費一定的時間。 

16、存儲過程Procedure：

SQL語句需要先編譯然后執(zhí)行，而存儲過程就是一組為了完成特定功能的SQL語句集，經(jīng)過編譯后存儲在數(shù)據(jù)庫中，用戶通過制定存儲過程的名字并給定參數(shù)來調(diào)用它。

用程序也可以實現(xiàn)操作數(shù)據(jù)庫的復雜邏輯，那為什么需要存儲過程呢？主要是因為使用程序調(diào)用API執(zhí)行，其效率相對較慢，應用程序需通過引擎把SQL語句交給MYSQL引擎來執(zhí)行，那還不如直接讓MySQL負責它最精通最能夠完成的工作。

存儲過程的優(yōu)點：

• （1）標準組件式編程：存儲過程創(chuàng)建后，可以在程序中被多次調(diào)用，而不必重新編寫該存儲過程的SQL語句。并且DBA可以隨時對存儲過程進行修改，對應用程序源代碼毫無影響。

• （2）更快的執(zhí)行速度：如果某一操作包含大量的Transaction-SQL代碼或分別被多次執(zhí)行，那么存儲過程要比批處理的執(zhí)行速度快很多。因為存儲過程是預編譯的，在首次運行一個存儲過程時查詢，優(yōu)化器對其進行分析優(yōu)化，并且給出最終被存儲在系統(tǒng)表中的執(zhí)行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優(yōu)化，速度相對要慢一些。

• （3）增強SQL語言的功能和靈活性：存儲過程可以用控制語句編寫，有很強的靈活性，可以完成復雜的判雜的斷和較復運算。

• （4）減少網(wǎng)絡流量：針對同一個數(shù)據(jù)庫對象的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織進存儲過程，那么當在客戶計算機上調(diào)用該存儲過程時，網(wǎng)絡中傳送的只是該調(diào)用語句，從而大大減少網(wǎng)絡流量并降低了網(wǎng)絡負載。

• （5）作為一種安全機制來充分利用：通過對執(zhí)行某一存儲過程的權(quán)限進行限制，能夠?qū)崿F(xiàn)對相應的數(shù)據(jù)的訪問權(quán)限的限制，避免了非授權(quán)用戶對數(shù)據(jù)的訪問，保證了數(shù)據(jù)的安全。 

17、觸發(fā)器Trigger：

觸發(fā)器是與表有關的數(shù)據(jù)庫對象，當觸發(fā)器所在表上出現(xiàn)指定事件并滿足定義條件的時候，將執(zhí)行觸發(fā)器中定義的語句集合。觸發(fā)器的特性可以應用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性。觸發(fā)器是一個特殊的存儲過程，不同的是存儲過程要用call來調(diào)用，而觸發(fā)器不需要使用call，也不需要手工調(diào)用，它在插入，刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)庫本身標準的功能有更精細和更復雜的數(shù)據(jù)控制能力。

18、游標Cursor：

游標，就是游動的標識，可以充當指針的作用，使用游標可以遍歷查詢數(shù)據(jù)庫返回的結(jié)果集中的所有記錄，但是每次只能提取一條記錄，即每次只能指向并取出一行的數(shù)據(jù)，以便進行相應的操作。當你沒有使用游標的時候，相當于別人一下給你所有的東西讓你拿走；用了游標之后，相當于別人一件一件的給你，這時你可以先看看這個東西好不好，再自己進行選擇。

版權(quán)聲明：本文為博主原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接和本聲明。

本文鏈接：

https://blog.csdn.net/a745233700/article/details/114242960