秋春招總結之MySQL

前言

對于Web三大模塊的后端資料庫模塊，只要你是做過專案都會有涉及與了解，無論你是簡單的增刪改查，對于所寫SQL陳述句都需要去查詢才能夠完善，亦或是對SQL陳述句都熟爛于心，無論是單表的查詢操作，或是多表的組合查詢都能夠信手拈來，但這些也都是SQL陳述句部分，對于SQL資料庫的基礎真的是太多太多，面試時候遇到的每一家公司問到的也都很少出現重復，但是主要的知識點也就擺在哪里，

在下面會詳細總結出來自己面試時候遇到過的SQL資料庫問題，和查閱別人的面經整理出來的SQL復習資訊，主要分為基礎，索引，鎖，和SQL陳述句等相關，也是盡力來網羅目前的SQL面試的大部分題目來增添自己的知識儲備，

基礎

資料庫的三大范式

第一范式：每個列都不可以再拆分

第二范式：在第一范式的基礎上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分

第三范式：在第二范式的基礎上，非主鍵列只依賴于主鍵，不依賴于其他非主鍵

資料庫存盤引擎的分類與區別

回答這個問題時候，首先說明MySQL在什么時候使用到的是那個存盤引擎，然后再回答兩者之間的區別，也不需要將那么多的區別都回答出來，只需要回答出幾個更改比較大的點

答： 對于MySQL來說在5.5 版本之前使用到的是MyISAM，在5.5版本之后默認的存盤引擎就是InnoDB，

區別：

	MyISAM	InnnoDB
是否支持外鍵	不支持	支持
是否支持事務	不支持	支持
是否記錄表格中行數	是	否
鎖支持	表級鎖定	行級鎖，表級鎖
哈希索引	不支持	支持
記錄存盤的順序	按記錄插入順序保存	按主鍵大小有序插入
是否支持全文索引	支持	不支持

根據兩個存盤引擎可能出現的問題

1. 有一個表中其ID是自增鍵，當插入了17條陳述句以后，洗掉了三條陳述句 15,16,17，此時重啟MySQL資料庫之后再插入一條陳述句，這個陳述句的id是15 還是18，

   當我們在使用到InnoDB做為存盤引擎時候，若是說不進行重啟的操作時候，這個id的值是18 但是若是進行了重新啟動時候，對于InnoDB來說InnoDB表只把自增主鍵的最大ID記錄到記憶體中，所以重啟資料庫或者對表OPTIMIZE操作，都會使最大ID丟失，所以這個時候id值就是15.

   但是我們使用到MyISAM時候，那么這條記錄的ID就是8，因為MylSAM表會把自增主鍵的最大ID記錄到資料檔案里面，重啟MYSQL后，自增主鍵的最大ID也不會丟失，

2. 對于兩個存盤引擎，那個使用 select count(*) 更快

對于MyISAM來說 ，速度會更快一點，因為對于MyISAM來說，內部維持了一個計數器，所以進行這個操作時候，時間復雜度是O(1),但是對于 InnoDB來說，需要進行計算，所以時間復雜度是O(n)，

兩個存盤引擎的選擇

如果沒有特別的需求，使用默認的Innodb即可，

MyISAM：以讀寫插入為主的應用程式，比如博客系統、新聞門戶網站，

Innodb：更新（洗掉）操作頻率也高，或者要保證資料的完整性；并發量高，支持事務和外鍵，比如OA自動化辦公系統，

資料庫事務的四大特性以及實作原理

事務是一個不可分割的資料庫操作序列，也是資料庫并發控制的基本單位，其執行的結果必須使資料庫從一種一致性狀態變到另一種一致性狀態，事務是邏輯上的一組操作，要么都執行，要么都不執行，

事務最經典也經常被拿出來說例子就是轉賬了，

假如小明要給小紅轉賬1000元，這個轉賬會涉及到兩個關鍵操作就是：將小明的余額減少1000元，將小紅的余額增加1000元，萬一在這兩個操作之間突然出現錯誤比如銀行系統崩潰，導致小明余額減少而小紅的余額沒有增加，這樣就不對了，事務就是保證這兩個關鍵操作要么都成功，要么都要失敗，

按照嚴格的標準，只有同時滿足ACID特性才是事務；但是在各大資料庫廠商的實作中，真正滿足ACID的事務少之又少，例如MySQL的NDB Cluster事務不滿足持久性和隔離性；InnoDB默認事務隔離級別是可重復讀，不滿足隔離性；Oracle默認的事務隔離級別為READ COMMITTED，不滿足隔離性……因此與其說ACID是事務必須滿足的條件，不如說它們是衡量事務的四個維度，

原子

概念：原子性是指一個事務是一個不可分割的作業單位，其中的操作要么都做，要么全部做，如果對于一個事務來說其中的sql陳述句執行失敗，則已經執行的陳述句也必須要回滾，資料庫退回到事務之前的狀態，

實作原理：對介紹原子性之前，對于Mysql的事務日志來說，InnoDB提供了兩種事務日志： redo log(重做日志)和 undo log(回滾日志)，其中的重做日志保證的是事物的持久性，回滾日志保證的是事務的原子性和隔離性實作的基礎，

下面來具體介紹對于 原子性實作的關鍵： 當事務出現回滾時候能夠撤銷所有已經成功執行的sql陳述句，
InnoDB能夠實作回滾的主要原因就是靠undo log： 當事務對資料庫進行修改的時候，InnoDB會生成對應的undo log ；如果此時事務執行失敗或者調動了 rollback，導致事務出現回滾情況，可以利用undo log中的資訊將資料回滾到修改前的樣子

undo log 屬于一個邏輯日志，它用來記錄的是sql執行相關的資訊，對于一個insert陳述句在回滾時候會執行delete，相反也是如此，例如對于一個update在執行的時候，其生成的undolog 中會包含被修改的主鍵（以便知道修改了哪些行，修改了哪些列）以便在回滾時候能夠使用這些記錄的資訊將資料還原到執行之前，

一致

概念： 一致性就是MySQL 資料庫最后追求的目標，資料庫事務不能破壞關系資料的完整性以及業務邏輯上的一致性，例如在完成一次的轉賬之后，無論是成功還是失敗，總金額不能夠發生變化，在對一個表完成一次操作之后，對于任意欄位的型別屬性不能夠被修改，

隔離

概念：與原子性，持久性側重于研究事務本身不同，隔離性研究的是不同事務之間的相互影響，是指，事務內部的操作與其他的事務是隔離的，并發執行的各個事務之間是不能相互干擾，

隔離性追求的是并發情形下事務之間不會相互干擾，簡單起見，我們僅考慮最簡單的讀操作和寫操作(暫時不考慮帶鎖讀等特殊操作)，那么隔離性的探討，主要可以分為兩個方面：

• (一個事務)寫操作對(另一個事務)寫操作的影響：鎖機制保證隔離性
• (一個事務)寫操作對(另一個事務)讀操作的影響：MVCC保證隔離性

持久

概念： 事務一旦提交，他對資料庫的改變就是永久的，接下來的其他操作或是故障都不應該對其造成影響，

實作原理：前面提到了InnoDB實作了兩個事務日志，首先我們來聊一下redo log 存在的背景，

InnoDB作為MySQL的存盤引擎，資料是存放在磁盤中的，但如果每次讀寫資料都需要磁盤IO，效率會很低，為此，InnoDB提供了快取(Buffer Pool)，Buffer Pool中包含了磁盤中部分資料頁的映射，作為訪問資料庫的緩沖：當從資料庫讀取資料時，會首先從Buffer Pool中讀取，如果Buffer Pool中沒有，則從磁盤讀取后放入Buffer Pool；當向資料庫寫入資料時，會首先寫入Buffer Pool，Buffer Pool中修改的資料會定期重繪到磁盤中（這一程序稱為刷臟），

Buffer Pool的使用大大提高了讀寫資料的效率，但是也帶了新的問題：

如果MySQL宕機，而此時Buffer Pool中修改的資料還沒有重繪到磁盤，就會導致資料的丟失，事務的持久性無法保證，

于是，redo log被引入來解決這個問題：當資料修改時，除了修改Buffer Pool中的資料，還會在redo log記錄這次操作；當事務提交時，會呼叫fsync介面對redo log進行刷盤，如果MySQL宕機，重啟時可以讀取redo log中的資料，對資料庫進行恢復，redo log采用的是WAL（Write-ahead logging，預寫式日志），所有修改先寫入日志，再更新到Buffer Pool，保證了資料不會因MySQL宕機而丟失，從而滿足了持久性要求，

既然redo log也需要在事務提交時將日志寫入磁盤，為什么它比直接將Buffer Pool中修改的資料寫入磁盤(即刷臟)要快呢？主要有以下兩方面的原因：

（1）刷臟是隨機IO，因為每次修改的資料位置隨機，但寫redo log是追加操作，屬于順序IO，

（2）刷臟是以資料頁（Page）為單位的，MySQL默認頁大小是16KB，一個Page上一個小修改都要整頁寫入；而redo log中只包含真正需要寫入的部分，無效IO大大減少，

ACID的總結

• 原子性： 陳述句要么都執行，要么都不是執行，是事務最核心的特性，事務本身來說就是以原子性歷來定義的，實作主要是基于undo log
• 持久性： 保證事務提交之后，不會因為宕機等其他的原因而導致資料的丟失，主要是基于 redo log實作
• 隔離性： 保證事務與事務之間的執行是相互隔離的，事務的執行不會受到其他事務的影響，InnoDB存盤引擎默認的資料庫隔離級別是 RR ，RR又主要是基于鎖機制，資料的隱藏列，undo log類 以及 next-key lock機制
• 一致性： 事務追求的最終目標，一致性的實作即需要資料庫層面的保障，也需要應用層面的保障，

redo log 與 binlog

我們知道，在MySQL中還存在binlog(二進制日志)也可以記錄寫操作并用于資料的恢復，但二者是有著根本的不同的：

（1）作用不同：redo log是用于crash recovery的，保證MySQL宕機也不會影響持久性；binlog是用于point-in-time recovery的，保證服務器可以基于時間點恢復資料，此外binlog還用于主從復制，
（2）層次不同：redo log是InnoDB存盤引擎實作的，而binlog是MySQL的服務器層(可以參考文章前面對MySQL邏輯架構的介紹)實作的，同時支持InnoDB和其他存盤引擎，
（3）內容不同：redo log是物理日志，內容基于磁盤的Page；binlog的內容是二進制的，根據binlog_format引數的不同，可能基于sql陳述句、基于資料本身或者二者的混合，
（4）寫入時機不同：binlog在事務提交時寫入；redo log的寫入時機相對多元：

• 前面曾提到：當事務提交時會呼叫fsync對redo log進行刷盤；這是默認情況下的策略，修innodb_flush_log_at_trx_commit引數可以改變該策略，但事務的持久性將無法保證，
• 除了事務提交時，還有其他刷盤時機：如master thread每秒刷盤一次redo log等，這樣的好處是不一定要等到commit時刷盤，commit速度大大加快，

資料庫可能出現的問題：

臟讀：某個事務已更新一份資料，另一個事務在此時讀取了同一份資料，由于某些原因，前一個RollBack了操作，則后一個事務所讀取的資料就會是不正確的，此時讀的是未提交，

幻讀：在一個事務的兩次查詢中資料筆數不一致，例如有一個事務查詢了幾列(Row)資料，而另一個事務卻在此時插入了新的幾列資料，先前的事務在接下來的查詢中，就會發現有幾列資料是它先前所沒有的，讀取到的行數不一樣，

不可重復讀： 在一個事務的兩次查詢之中資料不一致，這可能是兩次查詢程序中間插入了一個事務更新的原有的資料，兩次讀取到的值不一樣，

事務的隔離級別與MySQL 的默認隔離級別

為了達到事務的四大特性，資料庫定義了4種不同的事務隔離級別，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級別可以逐個解決臟讀、不可重復讀、幻讀這幾類問題，

隔離級別	臟讀	幻讀	不可重復讀
讀未提交	√	√	√
讀已提交	×	√	√
可重復讀	×	×	×
可串行化	×	×	×

讀未提交： 顧名思義就是可以讀取到未提交的資料，就會導致，臟讀，幻讀，和不可重復讀，

讀已提交：允許讀取并發事務已經提交的資料，可以阻止臟讀，但是幻讀或不可重復讀仍有可能發生，

可重復讀： 對同一欄位的多次讀取結果都是一致的，除非資料是被本身事務自己所修改，可以阻止臟讀和不可重復讀，但幻讀仍有可能發生，

可串行化： 最高的隔離級別，完全服從ACID的隔離級別，所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重復讀以及幻讀，

注意：對于MySQL來說默認的隔離級別是可重復度，對于Oracle 來說默認的隔離級別就是 讀已提交，

MySQL 有哪些的資料型別

1. 整數型別包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示1位元組、2位元組、3位元組、4位元組、8位元組整數，任何整數型別都可以加上UNSIGNED屬性，表示資料是無符號的，即非負整數，

   長度：整數型別可以被指定長度，例如：INT(11)表示長度為11的INT型別，長度在大多數場景是沒有意義的，它不會限制值的合法范圍，只會影響顯示字符的個數，而且需要和UNSIGNED ZEROFILL屬性配合使用才有意義，
   例子，假定型別設定為INT(5)，屬性為UNSIGNED ZEROFILL，如果用戶插入的資料為12的話，那么資料庫實際存盤資料為00012，

2. 實數型別 包括FLOAT、DOUBLE、DECIMAL，

   DECIMAL可以用于存盤比BIGINT還大的整型，能存盤精確的小數，
   而FLOAT和DOUBLE是有取值范圍的，并支持使用標準的浮點進行近似計算，
   計算時FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字串進行處理，

3. 字串型別

   包括VARCHAR、CHAR、TEXT、BLOB
   VARCHAR用于存盤可變長字串，它比定長型別更節省空間，
   VARCHAR使用額外1或2個位元組存盤字串長度，列長度小于255位元組時，使用1位元組表示，否則使用2位元組表示，
   VARCHAR存盤的內容超出設定的長度時，內容會被截斷，
   CHAR是定長的，根據定義的字串長度分配足夠的空間，
   CHAR會根據需要使用空格進行填充方便比較，
   CHAR適合存盤很短的字串，或者所有值都接近同一個長度，
   CHAR存盤的內容超出設定的長度時，內容同樣會被截斷，

4. 列舉型別

   把不重復的資料存盤為一個預定義的集合，
   有時可以使用ENUM代替常用的字串型別，
   ENUM存盤非常緊湊，會把串列值壓縮到一個或兩個位元組，
   ENUM在內部存盤時，其實存的是整數，
   盡量避免使用數字作為ENUM列舉的常量，因為容易混亂，
   排序是按照內部存盤的整數

5. 日期和時間型別

   盡量使用timestamp，空間效率高于datetime，
   用整數保存時間戳通常不方便處理，
   如果需要存盤微妙，可以使用bigint存盤，

索引

什么是索引？

索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含著對資料表里所有記錄的參考指標，

索引是一種資料結構，資料庫索引，是資料庫管理系統中一個排序的資料結構，以協助快速查詢、更新資料庫表中資料，索引的實作通常使用B樹及其變種B+樹，

更通俗的說，索引就相當于目錄，為了方便查找書中的內容，通過對內容建立索引形成目錄，索引是一個檔案，它是要占據物理空間的，

有哪幾種索引？

主鍵索引: 資料列不允許出現重復欄位，不允許為NULL值，并且對于一個表來說只能有一個主鍵，

唯一索引： 資料列不允許出現重復，但是允許為NULL值，一個表允許多個列創建唯一索引，

• 可以通過 ALTER TABLE table_name ADD UNIQUE (column); 創建唯一索引，
• 可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 創建唯一組合索引，

普通索引：基本的索引型別，沒有唯一性的限制，允許為NULL值，

• 可以通過ALTER TABLE table_name ADD INDEX index_name (column);創建普通索引

• 可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創建組合索引

全文索引：

• 通過ALTER TABLE table_name ADD FULLTEXT (column);創建全文索引（目前只用MyISAM 支持全文索引）

怎么創建索引？

在 Create table時候進行創建，

可以創建其他型別的索引，但是不能夠創建全文索引，

 create table text(
 id int auto_increment PRIMARY key,
 first_name varchar(20),
 last_name varchar(20),
 age int,
 address text,
 key name (first_name,last_name),// 聯合索引
 fulltext key (address),// 全域索引
 UNIQUE key (id)// 唯一索引
);

完成之后使用explain執行計劃查看：發現 type型別為 ref：表示此時索引的使用型別為：使用非唯一索引或非唯一索引前綴進行的查找，

在ALTER　TABLE 時候來創建索引

可以來創建普通，UNIQUE，或者是PRIMIARY KEY 索引

其中 table_name是要增加索引的表名，column_list表示是對哪些列進行創建索引

• ALTER TABLE table_name ADD INDEX index_name (column_list)
• ALTER TABLE table_name ADD UNIQUE (column_list)
• ALTER TABLE table_name ADD PRIMARY KEY (column_list)

直接使用到CREATE INDEX 來創建索引

可以直接對表增加普通索引或者UNIQUE索引

• CREATE INDEX index_name ON table_name (column_list)
• CREATE UNIQUE INDEX index_name ON table_name (column_list)

怎么洗掉索引？

可利用ALTER TABLE或DROP INDEX陳述句來洗掉索引，類似于CREATE INDEX陳述句，DROP INDEX可以在ALTER TABLE內部作為一條陳述句處理，語法如下，

• DROP INDEX index_name ON talbe_name
• ALTER TABLE table_name DROP INDEX index_name
• ALTER TABLE table_name DROP PRIMARY KEY

其中，前兩條陳述句是等價的，洗掉掉table_name中的索引index_name，
第3條陳述句只在洗掉PRIMARY KEY索引時使用，因為一個表只可能有一個PRIMARY KEY索引，因此不需要指定索引名，如果沒有創建PRIMARY KEY索引，但表具有一個或多個UNIQUE索引，則MySQL將洗掉第一個UNIQUE索引，

如果從表中洗掉了某列，則索引會受到影響，對于多列組合的索引，如果洗掉其中的某列，則該列也會從索引中洗掉，如果洗掉組成索引的所有列，則整個索引將被洗掉，

索引有哪些的優缺點

優點

• 因為對于索引來說相當于我們的資訊的目錄，可以幫助我們快速的查詢到我們的資料，不用在每一次的查詢中，都需要對表進行遍歷處理，
• 通過使用索引，可以在查詢的程序中，使用優化隱藏器，提高系統的性能，

缺點

• 時間方面：創建索引和維護索引要耗費時間，具體地，當對表中的資料進行增加、洗掉和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率；

• 空間方面：索引需要占物理空間，

索引的資料結構

對于目前主流的資料結構就是B+樹索引，與Hash索引，而我們經常使用的InnoDB存盤引擎的默認索引實作為：B+樹索引，對于哈希索引來說，底層的資料結構就是哈希表，因此在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其余大部分場景，建議選擇BTree索引，

B+樹索引原理與性質

對于現在MySQL索引基本上都是B+樹，現在基本上所說的也都是B+樹，一般都稱作是B樹，但是對于B樹和B+樹來說差別還是很大，

B+tree性質：

1.）n棵子tree的節點包含n個關鍵字，不用來保存資料而是保存資料的索引，

2.）所有的葉子結點中包含了全部關鍵字的資訊，及指向含這些關鍵字記錄的指標，且葉子結點本身依關鍵字的大小自小而大順序鏈接，

3.）所有的非終端結點可以看成是索引部分，結點中僅含其子樹中的最大（或最小）關鍵字，

4.）B+ 樹中，資料物件的插入和洗掉僅在葉節點上進行，

5.）B+樹有2個頭指標，一個是樹的根節點，一個是最小關鍵碼的葉節點，

可以看到其實對于B+樹來說所有的資訊都存盤在葉子節點，這個時候在進行資料庫查詢時候，進行磁盤的查詢，可以進行更快的查詢，因為對于每次的查詢的量是固定的，但是對于非葉子節點不存盤資訊，所有能夠遍歷更多的非葉子節點，并且所有的葉子節點的查詢的路徑的長度都是相同的，

為什么用B+樹而不是B樹

因為還是最開始的思想 減少磁盤的io的次數，因為盡可能的少減少磁盤的io的次數：
樹的深度過大會造成磁盤IO頻繁讀寫，根據磁盤查找存取的次數往往由樹的高度所決定，所以，只要我們通過某種較好的樹結構減少樹的結構盡量減少樹的高度

1. b隨機 b+支持隨機和順序
2. B+樹空間利用率更高，可減少I/O次數，磁盤讀寫代價更低，一般來說，索引本身也很大，不可能全部存盤在記憶體中，因此索引往往以索引檔案的形式存盤的磁盤上，這樣的話，索引查找程序中就要產生磁盤I/O消耗，B+樹的內部結點并沒有指向關鍵字具體資訊的指標，只是作為索引使用，其內部結點比B樹小，盤塊能容納的結點中關鍵字數量更多，一次性讀入記憶體中可以查找的關鍵字也就越多，相對的，IO讀寫次數也就降低了，而IO讀寫次數是影響索引檢索效率的最大因素；
3. B+樹的查詢效率更加穩定，B樹搜索有可能會在非葉子結點結束，越靠近根節點的記錄查找時間越短，只要找到關鍵字即可確定記錄的存在，其性能等價于在關鍵字全集內做一次二分查找，而在B+樹中，順序檢索比較明顯，隨機檢索時，任何關鍵字的查找都必須走一條從根節點到葉節點的路，所有關鍵字的查找路徑長度相同，導致每一個關鍵字的查詢效率相當，
4. B-樹在提高了磁盤IO性能的同時并沒有解決元素遍歷的效率低下的問題，B+樹的葉子節點使用指標順序連接在一起，只要遍歷葉子節點就可以實作整棵樹的遍歷，而且在資料庫中基于范圍的查詢是非常頻繁的，而B樹不支持這樣的操作，
   增刪檔案（節點）時，效率更高，因為B+樹的葉子節點包含所有關鍵字，并以有序的鏈表結構存盤，這樣可很好提高增刪效率，

哈希索引

類似于資料結構中簡單實作的HASH表（散串列）一樣，當我們在mysql中用哈希索引時，主要就是通過Hash演算法（常見的Hash演算法有直接定址法、平方取中法、折疊法、除數取余法、亂數法），將資料庫欄位資料轉換成定長的Hash值，與這條資料的行指標一并存入Hash表的對應位置；如果發生Hash碰撞（兩個不同關鍵字的Hash值相同），則在對應Hash鍵下以鏈表形式存盤，當然這只是簡略模擬圖，

為什么要用B+樹呢？

對于這個問題算是單獨獨立出來，來進行深刻的刨析以及底層的原理，

因為檔案很大的時候，不可能將所有的資訊記錄都保存在記憶體中，需要一部分保存在磁盤上，但是當訪問時候，訪問磁盤需要進行查找，索引的目的就是能夠快速的進行查找作業，索引索引的結構很關鍵，要盡量得減少在查找程序中磁盤I/O的存取次數，對于B+樹來說 所有的資料域都保存在葉子節點上，并且沒層的資料會比較多，為了就是能夠分得更少的層次，在進行優化查找時候，每個節點到葉子節點的高度都是相同的 就是將所有的葉子節點使用指標串起來，這樣就能夠使用遍歷葉子節點來獲取資料，非葉子節點只保存索引，葉子節點才會保存資料，
為什么能夠加快訪訪問速度

什么型別的sql陳述句不適合建立索引

• 在查詢中很少使用到的列不必要建立索引
• 對于那些只有很少數值的列不適合建立索引
• 對于那些定義為 text image 和 bit資料型別的列就不必要在增加索引，是因為這些列 要么資料量很列要么取值很小
• 當修改性能遠遠超過檢索性能時候 就沒有必要再建立索引 是因為 修改性能和檢索性能是相互矛盾的 增加索引會增阿基檢索性能 但是降低修改性能，
  在那些情況下 索引會出現無法使用的情況：
• 模糊查詢時候 以 % 開頭的like查詢
• 查詢時候 資料型別出現隱式的轉換
• 在復合索引的情況下 查詢條件不滿足索引最左的原則，
• 用 or分隔開的條件 or前條件有索引，但是or后的列沒有索引，
  此時出現的情況就是 由于 Or后的列并沒有索引的存在，所有在進行查詢時候還是要進行全表的掃描，在存在全表掃描時候 就沒有必要再進行一次索引掃描增加 IO訪問，
• 獨立的列 索引不能是運算式的一部分，必須是獨立的列，
• 負向查詢（not not in not like <> != !> !<） 等不能使用
• 在估計全表掃描時候 比全表要快就可以不必要使用索引
• 索引無法存盤null值，

什么時候用索引

首先明白一點就是對于索引來說是為了給我們加快資料查詢時候的速度，當我們在查詢資料的時候難免會對資料進行檢索操作，這個時候若是不走索引時候，where陳述句后面的檢索就會對表進行遍歷的處理，所以一般對于經常出現在where，等判斷條件的欄位上面添加索引，

例如如下操作：我們在沒有創建索引時候，可以看到沒有走索引資訊，走的是全文查詢 type的型別是ALL，

（具體的explain 執行計劃會在下面講到）

現在為tags 來增添一個索引表示添加一個普通的索引：

然后再來查詢一遍，可以看到type的型別發生了改變，同時可能出現索引的key也發生了改變，

當然在當前資料量比較小的情況下，暫時不會出現什么資料庫訪問壓力問題，但是若是在資料庫欄位比較多時候查詢就會出現查詢時間過長的問題，

order by

當我們使用order by將查詢結果按照某個欄位排序時，如果該欄位沒有建立索引，那么執行計劃會將查詢出的所有資料使用外部排序（將資料從硬碟分批讀取到記憶體使用內部排序，最后合并排序結果），這個操作是很影響性能的，因為需要將查詢涉及到的所有資料從磁盤中讀到記憶體（如果單條資料過大或者資料量過多都會降低效率），更無論讀到記憶體之后的排序了，

但是如果我們對該欄位建立索引alter table 表名 add index(欄位名)，那么由于索引本身是有序的，因此直接按照索引的順序和映射關系逐條取出資料即可，而且如果分頁的，那么只用取出索引表某個范圍內的索引對應的資料，而不用像上述那取出所有資料進行排序再回傳某個范圍內的資料，（從磁盤取資料是最影響性能的）

join

對join陳述句匹配關系（on）涉及的欄位建立索引能夠提高效率

對于這個問題，在面試時候會問到：“現在你已經為你表的某個欄位創建了一個索引，如何來判斷自己的這個索引有沒有被使用到，或者說查看哪個欄位能夠判斷索引的執行效率”

首先對我們需要執行的sql陳述句使用到explain執行計劃，如下圖所示，

下面來具體刨析每個欄位以及欄位屬性的具體值所代表的含義：

id：表示查詢中各個子查詢執行時候的執行順序，

• id相同執行順序由上至下，
• id不同，id值越大優先級越高，越先被執行，
• id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢陳述句中，

select_type: 表示子查詢的查詢型別，下面列舉常見的型別

id	select_type	具體描述
1	SIMPLE	不包含任何子查詢或union等查詢
2	PRIMARY	包含子查詢最外層查詢就顯示為 PRIMARY
3	SUBQUERY	在select或 where字句中包含的查詢
4	DERIVED	from字句中包含的查詢
5	UNION	出現在union后的查詢陳述句中

table： 表示在當前執行計劃中走的是哪一個表，

type： 表示在此條sql陳述句中走的索引的型別，一般也都是看此欄位，來判斷此條sql陳述句執行的效率高不高，

• ALL 掃描全表資料
• index 遍歷索引
• range 索引范圍查找
• index_subquery 在子查詢中使用 ref
• unique_subquery 在子查詢中使用 eq_ref
• ref_or_null 對Null進行索引的優化的 ref
• fulltext 使用全文索引
• ref 使用非唯一索引查找資料

possible_keys: 可能使用的索引，注意不一定會使用，查詢涉及到的欄位上若存在索引，則該索引將被列出來，當該列為 NULL時就要考慮當前的SQL是否需要優化了，

key : 顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL，

key_length: 索引長度

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值，

rows： 回傳估算的結果集數目，并不是一個準確的值，

extra 的資訊非常豐富，常見的有：

1. Using index 使用覆寫索引
2. Using where 使用了用where子句來過濾結果集
3. Using filesort 使用檔案排序，使用非索引列進行排序時出現，非常消耗性能，盡量優化，

注意：在上面的執行計劃中我們查看完成之后主要是看**type**屬性來判斷當前的sql陳述句是否需要進行優化的處理，
至少要達到 range 級別，要求是ref級別，如果能夠達到consts是最好的情況， 
說明： 
1） consts 單表中最多只有一個匹配行（主鍵或者唯一索引），在優化階段即可讀取到資料，
2） ref 指的是使用普通的索引（normal index）， 
3） range 對索引進行范圍檢索， 

設計索引的原則

1. 適合索引的列是出現在where子句中的列，或者連接子句中指定的列
2. 基數較小的類，索引效果較差，沒有必要在此列建立索引
3. 使用短索引，如果對長字串列進行索引，應該指定一個前綴長度，這樣能夠節省大量索引空間
4. 不要過度索引，索引需要額外的磁盤空間，并降低寫操作的性能，在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長，所以只保持需要的索引有利于查詢即可，

索引創建時候的原則

1. 最左前綴匹配原則，組合索引非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整，
2. 較頻繁作為查詢條件的欄位才去創建索引
3. 更新頻繁欄位不適合創建索引
4. 若是不能有效區分資料的列不適合做索引列(如性別，男女未知，最多也就三種，區分度實在太低)
5. 盡量的擴展索引，不要新建索引，比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可，
6. 定義有外鍵的資料列一定要建立索引，
7. 對于定義為text、image和bit的資料型別的列不要建立索引，

使用索引一定能夠提高性能？

通過建立索引并不一定一定能夠對性能進行一個提升，在我們通常的情況下，在建立索引的基礎上，一個查詢走索引確實能夠比走全表查詢消耗更少的性能，但是我們也需要注意有些情況：

索引是真實存在，也需要消耗一定的存盤空間，也需要進行定期的維護處理，每當有記錄在表中增級訓索引列被修改時，索引本身也會被修改， 這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁盤I/O， 因為索引需要額外的存盤空間和處理，那些不必要的索引反而會使查詢反應時間變慢，使用索引查詢不一定能提高查詢性能，索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:

1. 對于范圍的檢索：且查詢的結果集小于表中記錄數的30%，
2. 對于非唯一性索引的檢索可以索引查詢提高查詢的效率，

盡管有索引的存在，但是什么時候會失效？

1. 未使用最左匹配原則 例如就是說 組合索引 （A，B） 在where B=X 時候是不會使用到索引的
2. like 未使用最左匹配原則
3. 搜索一個索引時候 但是卻在另外一個索引上做order by操作： where A=a order by B，只使用A上的索引，因為查詢只使用一個索引 ；
4. or會使索引失效，如果查詢欄位相同，也可以使用索引，例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
5. 如果列型別是字串，要使用引號，例如where A=‘China’，否則索引失效（會進行型別轉換）；
6. 在索引列上的操作，函式（upper()等）、or、！=(<>)、not in等；

什么是聚簇索引與非聚簇索引

• 聚簇索引： 并不是之前我們看到的一種單獨的索引型別，而是一種資料存盤的方式，具體的細節依賴于其實作方式，但InnoDB的聚簇索引實際上在同一個結構中保存了B-Tree索引和資料行，當表中有聚簇索引時，它的資料行實際上存放在葉子頁中（leaf page），術語（聚簇）表示的是資料行和相鄰的鍵值緊湊地存盤在一起，因為無法同時把資料存放在兩個不同的地方，所以一個表中只能有一個聚簇索引，（不過覆寫引擎可以模擬多個聚簇索引的情況，在后面會進行介紹，
  聚簇索引的每一個葉子節點都包含了主鍵值，事務ID，用于事務和mvcc的回滾指標以及所有的剩余列，如果主鍵是一個列前綴索引，InnoDB也使包含完整的主鍵列和剩下的其他列，
• 非聚簇索引： 將資料存盤于索引分開結構，索引結構的葉子節點指向了資料的對應行，myisam通過key_buffer把索引先快取到記憶體中，當需要訪問資料時（通過索引訪問資料），在記憶體中直接搜索索引，然后通過索引找到磁盤相應資料，這也就是為什么索引不在key buffer命中時，速度慢的原因

什么是聯合索引，為什么需要注意聯合索引中的順序？

MySQL可以使用多個欄位同時建立一個索引，叫做聯合索引，在聯合索引中，如果想要命中索引，需要按照建立索引時的欄位順序挨個使用，否則無法命中索引，

具體原因為:

MySQL使用索引時需要索引有序，假設現在建立了"name，age，school"的聯合索引，那么索引的排序為: 先按照name排序，如果name相同，則按照age排序，如果age的值也相等，則按照school進行排序，

當進行查詢時，此時索引僅僅按照name嚴格有序，因此必須首先使用name欄位進行等值查詢，之后對于匹配到的列而言，其按照age欄位嚴格有序，此時可以使用age欄位用做索引查找，以此類推，因此在建立聯合索引的時候應該注意索引列的順序，一般情況下，將查詢需求頻繁或者欄位選擇性高的列放在前面，此外可以根據特例的查詢或者表結構進行單獨的調整，

什么是覆寫索引

mysql可以在使用索引來直接獲取列的資料，這樣就不需要來讀取資料行，如果索引的葉子節點中以及包含了要查詢的資料，那么還有什么必要再回表查詢呢，如果一個索引包含（或者說是覆寫）所有需要查詢的欄位的值，我們就稱為“覆寫索引”，
有什么好處：

• 索引條目通常遠小于資料行的大小，所以如果只需要讀取索引，那Mysql就會極大地減少資料訪問量，這對快取的負載非常重要，因為這種情況下回應時間大部分花費在資料拷貝上，
• 因為索引時按照列值順序存盤的（至少在當個頁內是如此），所以對于I/O密集型的范圍查詢會比隨機從磁盤讀取每一行資料的I/O 要小的多，
• 由于InnoDB的聚簇索引，覆寫索引對InnoDB表特別有用，InnoDB的二級索引在葉子節點中保存了行的主鍵值，所以如果二級主鍵能夠覆寫查詢，則可以避免對主鍵所以的二次查詢，

什么是冗余索引與重復索引

重復索引指的是在相同列上按照相同的順序創建的相同型別的索引，
舉個例子

create table test(
ID int  NOT NULL primiary key,
A int not null,
B int not null,
unique(ID),
index(ID),
)engine=InnoDB;

此時我們看到的是，一個用戶想要創建一個主鍵，加上唯一限制，然后加上索引以供查詢使用，事實上，MySql的唯一限制和主鍵限制都是通過索引實作的，因此，上面的寫法實際上在相同的列上創建了三個重復的索引，通常沒有這樣做的理由，除非是在同一列上創建不同型別的索引來滿足不同的查詢需求，
冗余索引和重復索引有所不同，如果創建了索引（A,B),再創建索引 （A），就是冗余索引，因為這只是我們前面創建索引的一個前綴索引，因此索引（A,B）也可以當做索引（A）來使用，注意這里我們說的冗余只是對B-Tree來說的，但是如果再創建索引（B，A）卻不能被視為冗余索引，同時索引（B）也不是，因為B不是索引（A，B）的最左前綴列，另外，其他不同型別的索引（例如哈希索引，或者是全文索引）也不會是B-Tree索引的冗余索引，而無論覆寫的索引列是什么，

在日常的作業中，冗余索引通常發生在為表添加新索引的時候，例如，有人可能會增加一個新的索引（A，B）而不是擴展已有的索引（A），還有一種情況就是將一個索引擴展為（A，ID），其中ID是主鍵，對于InnoDB來說主鍵列已經包含在二級索引中，所以這樣的情況也是冗余的，

什么是回表查詢

對于這個題目，切實在面試中遇到中，當時在問我索引的型別等，然后就談論到“那你知道什么是回表查詢？”

對于這個問題想要理解需要先明白對于Mysql索引大體分為兩類：一個聚集索引，一個是普通索引：

InnoDB聚集索引的葉子節點存盤行記錄，因此InnoDB必須要有且只有一個聚集索引，
1.如果表定義了PK（Primary Key，主鍵），那么PK就是聚集索引，
2.如果表沒有定義PK，則第一個NOT NULL UNIQUE的列就是聚集索引，
3.否則InnoDB會另外創建一個隱藏的ROWID作為聚集索引，

就是說 當我們在使用非聚簇索引（對于那些非主鍵進行索引值時候）找到了記錄的行，但是還是需要根據主鍵再次找到資料快里面對應的資料，
一個是一個表的ID是主鍵 我們查詢
select * from stu where id=1;
是直接查詢到的
但是還有一個name不是主鍵 有普通的索引
查詢時候就需要進行一個回表的操作，

非聚簇索引來說一定會進行回表查詢？

不一定，這涉及到查詢陳述句所要求的欄位是否全部命中了索引，如果全部命中了索引，那么就不必再進行回表查詢，

舉個簡單的例子，假設我們在員工表的年齡上建立了索引，那么當進行select age from employee where age < 20的查詢時，在索引的葉子節點上，已經包含了age資訊，不會再次進行回表查詢，

鎖

MyISAM 支持表鎖，InnoDB 支持表鎖和行級鎖，默認是 行級鎖，

表級鎖：開銷小，加鎖快，不會出現死鎖，鎖定粒度大，發送鎖沖突的概率比較高，并發處理效果較低，
行級鎖： 開銷大，加鎖慢，會出現死鎖，鎖定粒度較大，發生鎖沖突的概率會小一點，并發處理的效果高，

什么是資料庫的死鎖，怎么解決

死鎖是指兩個或多個事務在同一資源上相互占用，并請求鎖定對方的資源，從而導致惡性回圈的現象，

常見的解決死鎖的方法

1、如果不同程式會并發存取多個表，盡量約定以相同的順序訪問表，可以大大降低死鎖機會，

2、在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；

3、對于非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；

什么是資料庫的樂觀鎖與悲觀鎖，是如何實作

資料庫管理系統（DBMS）中的并發控制的任務是確保在多個事務同時存取資料庫中同一資料時不破壞事務的隔離性和統一性以及資料庫的統一性，樂觀并發控制（樂觀鎖）和悲觀并發控制（悲觀鎖）是并發控制主要采用的技術手段，

悲觀鎖：假定會發生并發沖突，屏蔽一切可能違反資料完整性的操作，在查詢完資料的時候就把事務鎖起來，直到提交事務，實作方式：使用資料庫中的鎖機制

樂觀鎖：假設不會發生并發沖突，只在提交操作時檢查是否違反資料完整性，在修改資料的時候把事務鎖起來，通過version的方式來進行鎖定，實作方式：樂一般會使用版本號機制或CAS演算法實作，

兩種鎖的使用場景

從上面對兩種鎖的介紹，我們知道兩種鎖各有優缺點，不可認為一種好于另一種，像樂觀鎖適用于寫比較少的情況下（多讀場景），即沖突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量，

但如果是多寫的情況，一般會經常產生沖突，這就會導致上層應用會不斷的進行retry，這樣反倒是降低了性能，所以一般多寫的場景下用悲觀鎖就比較合適，

MySQL中InnoDB引擎的行鎖是如何實作的

答：InnoDB是基于索引來完成行鎖

例: select * from tab_with_index where id = 1 for update;

for update 可以根據條件來完成行鎖鎖定，并且 id 是有索引鍵的列，如果 id 不是索引鍵那么InnoDB將完成表鎖，并發將無從談起

一條SQL陳述句的執行流程

對于這個題目也是在面試中切實盡量過

例如我們有如下sql陳述句

select * from user where id=6

就是查詢一個id為6的用戶的資訊執行流程是

客戶端-》Server層-》 引擎層，

其中server層包括： 連接器，分析器，查詢器，優化器，執行器
引擎層： 就是底層的引擎 主要負責是存盤資料，提供讀寫的介面

連接

就是使用客戶端或者命令列時候輸入的用戶名與密碼進行的連接，在你連接成功以后，此用戶操作資料的權限判斷邏輯都將依賴查詢到的權限，此時修改用戶的權限也是無用，必須重新登錄才行，這里說明一下對于客戶端連接到的資料庫而言，如果連接一直處于空閑的狀態，那么到了一定的時間限制就會斷開連接，這個連接的默認時間是8個小時，

查詢快取

對于連接成功以后，執行SQL時候會先行查詢快取，如果有相對應的資料就會直接回傳給客戶端進行顯示，后面的步驟就不會再執行下去，
它的原理怎樣的呢？請接著往下看，一條查詢sql的首次執行完成后，會把sql陳述句作為key，把查詢出來的資料作為value放入到快取中，如果后面再有相同的查詢，那么直接從快取中取值便可，

看到這里也許你們會想快取這么好用，那以后要多用快取，別急，請接著往下看，查詢快取用起來確實好用，但是它有一個弊端，那就是當這個表做了更新操作時，那么此表的快取將會全部清空，也許當你辛辛苦苦快取起來的資料，還沒來得及用時就可能被一條update陳述句給全部干掉，所以如果更新比較頻繁的表是不適合使用快取的，如果是某些配置表倒是比較適合快取的使用，

分析器

當前面的不能夠快取命中的時候，sql就會開始真正執行流程，首先是分析器，包括語法分析，詞法分析，
詞法分析： 就是對sql陳述句中非每一個單詞都逐個的做分析，比如 對于 select來說 就會識別出來時查詢陳述句，user 就會分析出來時 表user 然后對應的資訊一一對應，
語法分析就會分析： 寫的這條陳述句是否滿足sql的語法，若是你對于 where陳述句以后沒有給定的限制資訊就會報錯，對于 select后來沒有選中值也會報錯等

優化器

對于 執行完分析器以后，就能夠知道所執行的是什么型別的陳述句 是查詢，還是插入等，進行優化，例如 使用表的哪個索引；或者是進行表的關聯，決定關聯的順序等等問題，

執行器

當完成了優化以后，就表示此sql陳述句選擇好了一個比較完拖的方案，可以進行執行階段，首先要先檢查你進行的操作是否有對應的表的權限，如果有了權限以后，執行器會根據定義的表的引擎，來執行引擎所對應的介面資訊 mysql5.5之后存盤引擎是 InnoDB，
其大致的流程如下：

1. 呼叫InnoDB引擎介面取這個表的第一行，判斷ID值是不是6，如果不是則跳過，如果是則將這行存在結果集中；
2. 呼叫引擎介面取“下一行”，重復相同的判斷邏輯，直到取到這個表的最后一行，
   3.執行器將上述遍歷程序中所有滿足條件的行組成的記錄集作為結果集回傳給客戶端，

如果是 id 是有索引的，第一次呼叫的是“取滿足條件的第一行”這個介面，之后回圈取“滿足條件的下一行”這個介面，基本和上面差不多，這些介面都是引擎中已經定義好的，至此 一條 sql 便執行完成，

一條SQL陳述句的生命周期

1. 應用服務器與資料庫服務器建立一個連接

2. 資料庫行程拿到請求sql

3. 決議并生成執行計劃，執行

4. 讀取資料到記憶體并進行邏輯處理

5. 通過步驟一的連接，發送結果到客戶端

6. 關掉連接，釋放資源

   

如何實作MySQL的主從復制

首先在實作主從復制之前，先對一些基礎的術語有一個認識

DDL（Data Definition Languages）陳述句：即資料庫定義陳述句，用來創建資料庫中的表、索引、視圖、存盤程序、觸發器等，常用的陳述句關鍵字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME，

DML（Data Manipulation Language）陳述句：即資料操縱陳述句，用來查詢、添加、更新、洗掉等，常用的陳述句關鍵字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增刪改查，
DCL（Data Control Language）陳述句：即資料控制陳述句，用于授權/撤銷資料庫及其欄位的權限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.），常用的陳述句關鍵字有：GRANT,REVOKE，TCL（Transaction Control Language）陳述句：事務控制陳述句，用于控制事務，常用的陳述句關鍵字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION，

主從復制

主從的區別：

因為對于MySQL來說性能是完完全全有限的，對于單點資料的讀取的性能也是有很嚴重的上限要求，所以我們一般都是對主資料庫進行寫入的操作，對從資料庫進行資訊的讀取，

基本程序

1. Mysql會在主資料庫發生變化的時候，將變化實時同步到從資料庫上，
2. 主從復制可以水平擴展資料庫的負載能力，容錯，高并發，資料備份，
3. 不管是delete update insert 還是創建函式，存盤程序都是在 master上進行，Slave會快速接受到這些操作，實作同步

用途和條件

1)、mysql主從復制用途
●實時災備，用于故障切換
●讀寫分離，提供查詢服務
●備份，避免影響業務
2)、主從部署必要條件：
●主庫開啟binlog日志（設定log-bin引數）
●主從server-id不同
●從庫服務器能連通主庫

粒度 原理與形式

實作的三種形式：
binlog 的記錄模式：

1. statement：
   ** 把對資料庫的sql陳述句寫到 binlog中 每一條修改資料的sql陳述句都會記錄在 binlog日志中**記錄的是sql陳述句不需要記錄每一行的變化，只需要記錄下來sql陳述句即可，

缺點： 就是 由于只是記錄下來sql陳述句 為了能夠讓sql陳述句正常的在 Slave上運行，還必須記錄每條陳述句在執行的程序中一些相關的新，以確保得到與 主資料庫相同的結果

2. row：

   把每一條資料的變化寫入到binlog中，不記錄sql陳述句的背景關系資訊，僅保存哪條記錄被修改， 就是會清楚記錄下來每一行的變化，但是這樣回產生大量的日志，例如一個更改表的陳述句，就會涉及到很多條的陳述句都會進行修改，就會導致比較冗余

3. mixed statement與row的混合，

   Mysql決定何時寫statement格式的binlog, 何時寫row格式的binlog， 將兩則的混合起來，起到最少的記錄行資訊

實作的流程

首先看一張圖片：
需要三個執行緒來完成的，在從端有兩個執行緒，sql執行緒與 I/O執行緒，
主端有 一個 I/O執行緒，在實作主從復制的時候 ，首先會開啟 Master端的 binLog記錄功能 因為整個的復制流程就是 Slave從Master端獲取到 binlog日志，然后再Slave上以相同的順序執行獲取到的binlog日志中的記錄中的各種的SQL操作，

1. 在從端打開主從復制的開關，開始進行復制操作，
2. 此時 對于 從的 I/O執行緒會通過 master上已經授權的賦值用戶權限請求建立連接master服務，并請求從執行binlog日志的指定位置之后開始發送binlog日志的內從（注意這里的日志檔案名和位置就是在配置主從服務質量執行 change master命令指定的）
3. Mater 服務器接收到來自 Salve服務器的IO請求以后，其上負責復制的IO執行緒會根據Slave服務器的IO執行緒請求的資訊分批讀取指定binlog日志檔案指定位置之后的binlog日志資訊，然后 回傳給Slave端的IO執行緒，回傳的除了基礎的binlog日志內容以外，還有Master服務端記錄的IO執行緒，回傳的資訊還有binlog中下一個指定更新的位置，
4. 當slave 服務器的IO執行緒讀取到 Master服務器上 IO執行緒發送過來的日志內容，日志檔案，及位置以后，會將binlog日志內容依次寫到Slave端自身的Relay Log （即中繼日志）檔案（Mysq-relay-bin.xxx）的最末端，并將新的binlog檔案名和位置記錄到 master-info檔案中，以便能夠在下一次讀取master端新binlog日志時能告訴Master服務器從新binlog日志的指定檔案及位置開始讀取新的binlog日志內容，
5. Slave服務器端的SQL執行緒會實時檢測本地Relay Log 中IO執行緒新增的日志內容，然后及時把Relay LOG 檔案中的內容決議成sql陳述句，并在自身Slave服務器上按決議SQL陳述句的位置順序執行應用這樣sql陳述句，并在relay-log.info中記錄當前應用中繼日志的檔案名和位置點

需要的條件

1. 開啟 binlog功能
2. 主庫要建立賬號
3. 從庫要配置master.info (相當于配置密碼檔案的相關資訊)
4. start slave 開啟復制的功能

小結：

主從復制是異步的邏輯的SQL陳述句級的復制
復制時，主庫有一個I/O執行緒，從庫有兩個執行緒，I/O和SQL執行緒
實作主從復制的必要條件是主庫要開啟記錄binlog功能
作為復制的所有Mysql節點的server-id都不能相同
binlog檔案只記錄對資料庫有更改的SQL陳述句（來自主庫內容的變更），不記錄任何查詢（select，show）陳述句

從庫同步延遲問題：

1）.
MySQL資料庫主從同步延遲原理mysql主從同步原理：主庫針對寫操作，順序寫binlog，從庫單執行緒去主庫順序讀”寫操作的binlog”，從庫取到binlog在本地原樣執行（隨機寫），來保證主從資料邏輯上一致，

mysql的主從復制都是單執行緒的操作，主庫對所有DDL和DML產生binlog，binlog是順序寫，所以效率很高，slave的Slave_IO_Running執行緒到主庫取日志，效率比較高，下一步，問題來了，slave的Slave_SQL_Running執行緒將主庫的DDL和DML操作在slave實施，DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產生lock爭用，由于Slave_SQL_Running也是單執行緒的，所以一個DDL卡主了，需要執行10分鐘，那么所有之后的DDL會等待這個DDL執行完才會繼續執行，這就導致了延時，有朋友會問：“主庫上那個相同的DDL也需要執行10分，為什么slave會延時？”，答案是master可以并發，Slave_SQL_Running執行緒卻不可以，
2) .
MySQL資料庫主從同步延遲是怎么產生的？當主庫的TPS并發較高時，產生的DDL數量超過slave一個sql執行緒所能承受的范圍，那么延時就產生了，當然還有就是可能與slave的大型query陳述句產生了鎖等待，首要原因：資料庫在業務上讀寫壓力太大，CPU計算負荷大，網卡負荷大，硬碟隨機IO太高次要原因：讀寫binlog帶來的性能影響，網路傳輸延遲，

什么是異步復制，全同步復制，與半同步復制

在面試程序中切實遇到，

異步復制：

邏輯上：
對于Mysql來說默認上就是異步的，主庫在執行完客戶端提交的事務后會立即將結果返給給客戶端，并不關心從庫是否已經接收并處理，這樣就會有一個問題，主如果crash掉了，此時主上已經提交的事務可能并沒有傳到從庫上，如果此時，強行將從提升為主，可能導致新主上的資料不完整，
技術上：
主庫將事務 Binlog 事件寫入到 Binlog 檔案中，此時主庫只會通知一下 Dump 執行緒發送這些新的 Binlog，然后主庫就會繼續處理提交操作，而此時不會保證這些 Binlog 傳到任何一個從庫節點上，

同步復制：

邏輯上：
指當主庫執行完一個事務，所有的從庫都執行了該事務才回傳給客戶端，因為需要等待所有從庫執行完該事務才能回傳，所以全同步復制的性能必然會收到嚴重的影響，
技術上：
當主庫提交事務之后，所有的從庫節點必須收到、APPLY并且提交這些事務，然后主庫執行緒才能繼續做后續操作，但缺點是，主庫完成一個事務的時間會被拉長，性能降低，

半同步復制

邏輯上：
前面提到的 異步復制，是不查看從庫是否已經完成了對于主庫的賦值，全同步復制是，接收完所有的從庫復制資訊以后才會回傳給客戶端，但是對于 半同步復制來說，主庫在執行完客戶端提交的事務之后并不會立刻回傳給客戶端，而是等待至少一個從庫接收到并寫到Relay
olg中才回傳給客戶端，相當于一個簡約版的異步復制，提高了一定的安全性，但是也造成了一定程度的延遲情況，這個延遲最少是一個TCP/IP 往返時間，所以來說 版同步復制最好在低延遲的網路中使用到，

SQL陳述句相關

SQL陳述句執行時各個關鍵字的順序

1. 對于order by來說 表示按照什么類進行分類的處理 例如對于學生的成績而言，就可以直接查詢然后利用到order by 進行一個升序的排列，
2. 對于 group by 來說就是按照什么進行分類，常常和一些聚合的函式在一起來使用 count，avg max min 這里舉一個列子如下圖所示
   

select cno,count(sno) from stu group by cno;

表示就是說 以cno進行分類處理 對于 cno來說有幾個分類就列舉出來幾行 同時計算出 以cno為相同時候的count sno的值，

3. 就是表的相連接以及 聚合函式的使用 對于兩個表時候，我們使用到直接逗號的方法進行連接即可 但是對于相同的欄位要保持前綴的存在，
   舉個栗子：

   查詢選修了3門以上課程，且所有課程成績都高于60分的學生學號及課程數

select sno,count(cno)
from stu
where grade>60
group by sno having count(cno)>3;

sql陳述句的相關聯的連接查詢

• 交叉連接（CROSS JOIN）
• 內連接（INNER JOIN）
• 外連接（LEFT JOIN/RIGHT JOIN）
• 聯合查詢（UNION與UNION ALL）
• 全連接（FULL JOIN）

舉個栗子

內連接

• 等值連接：ON A.id=B.id

• 不等值連接ON A.id > B.id

• 自連接 就是說對于同一個表自己和自己相連接

select e.emp,d.emp from employ e 
inner join employ b where e.bossid==b.id;

外連接

• 左外連接：LEFT OUTER JOIN, 以左表為主，先查詢出左表，按照ON后的關聯條件匹配右表，沒有匹配到的用NULL填充，可以簡寫成LEFT JOIN
• 右外連接：RIGHT OUTER JOIN, 以右表為主，先查詢出右表，按照ON后的關聯條件匹配左表，沒有匹配到的用NULL填充，可以簡寫成RIGHT JOIN

表連接面試題

有兩個表，一個是X表，其中有三個欄位ABC，一個是Y表有兩個欄位CD,兩張表之間只有c欄位是共同的欄位，來進行不同的操作，驗證我們的各個連接的情況，如下圖所示：

X表：

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

Y表

C	D
c1	d1
c2	d2
c4	d3

1. 交叉連接（笛卡爾積）

   select x.*,y.* from r,s;// 最后應該是X表的行乘于Y表的行
   

   A	B	C	C	D
   a1	b1	c1	c1	d1
   a2 a3
   a1	b1	c1	c2	d2
   a2 a3
   a1	b1	c1	c4	d3
   a2 a3

2. 內連接

   select x.*,y.* from x inner join y on x.c=y.c;// 要求要完完全全相等
   

   A	B	C	C	D
   a1	b1	c1	c1	d1
   a2	b2	c2	c2	d2

3. 左連接

select x.*,y.* from x left join y on x.c=y.c;// 對于左連接出現的情況是先將左邊的表進行全查詢處理，與右邊表有相對應位置時候顯示對應的值，若是沒有相等的值，該列的值為空，

A	B	C	C	D
a1	b1	c1	c1	d1
a2	b2	c2	c2	d2
a3	b3	c3

4. 右連接

   select r.*,s.* from r right join s on r.c=s.c
   

A	B	C	C	D
a1	b1	c1	c1	d1
a2	b2	c2	c2	d2
			c4	d3

5. 全連接(暫時情況是對于MySQL來說，不支持全連接的情況，全連接只能由Oracle支持)

select r.*,s.* from r full join s on r.c=s.c

A	B	C	C	D
a1	b1	c1	c1	d1
a2	b2	c2	c2	d2
a3	b3	c3
			c4	d3

子查詢

1. 條件：一條SQL陳述句的查詢結果做為另一條查詢陳述句的條件或查詢結果
2. 嵌套：多條SQL陳述句嵌套使用，內部的SQL查詢陳述句稱為子查詢，

Where 型別的子查詢

1. 子查詢是單行單列的情況：結果集是一個值，父查詢使用：=、 <、 > 等運算子

   -- 查詢工資最高的員工是誰？ 
   select  * from employee where salary=(select max(salary) from employee);   
   

2. 子查詢是多行單列的情況：結果集類似于一個陣列，父查詢使用：in 運算子

   select * from employee where salary in(select salary from employee group by card_id)
   
   

3. 子查詢是多行多列的情況：結果集類似于一張虛擬表，不能用于where條件，用于select子句中做為子表

   -- 1) 查詢出2011年以后入職的員工資訊
   -- 2) 查詢所有的部門資訊，與上面的虛擬表中的資訊比對，找出所有部門ID相等的員工，
   select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    
   
   -- 使用表連接：
   select d.*, e.* from  dept d inner join employee e on d.id = e.dept_id where e.join_date >  '2011-1-1'  
   
   

from 型別的子查詢

在學習 from 子查詢之前，需要理解一個概念：查詢結果集在結構上可以當成表看，那就可以當成臨時表對他進行再次查詢：

1. 取出每個欄目下 goods_id 最大的商品資訊：

首先執行下面這句陳述句以后，能得到一個結果，其中每個欄目的第一行都是 goods_id 最大的行：

select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc;

假設存在這張表，表名叫 tmp ,那么：

select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as tmp group by cat_id;

exists類子查詢

exists 型子查詢是指外層 sql 的結果，拿到內層 sql 去測驗，如果內層 sql 成立，則該行取出，學習之前先建一張表：

create table category(
    -> cat_id int auto_increment primary key,
    -> cat_name varchar(20) not null default ''
    -> )engine myisam;

向里面插入原來 ecshop.ecs_category 中的部分資料：

insert into test.category select cat_id,cat_name from ecshop.ecs_category;

1. 取欄目表且只取出下面有商品的欄目表

那么假設某個欄目的 cat_id 為 N，則 select * from goods where cat_id = N如果能取出，則說明該欄目下有商品：

select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);

可以理解為，先把第一個 cat_id=1 取出來，帶入到內層，那么內層就變成了：

select * from goods where goods.cat_id=1;

查詢發現沒有對應的值，回傳 empty ，那么category 中 cat_id=1 這一行就不要了，接下來看 cat_id=2，代入，發現可以取出貨物 goods 來，那么 category 中 cat_id=2 這一行就取出來

in 和exist 區別

在前面講到和學習到in 和exists都是在子查詢時候來使用達到更高的效率，那兩者之間的區別是什么呢？

1. 如果查詢的兩個表大小相當，那么用in和exists差別不大，
2. 如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in，(理解起來就是說想象一下對于 in來說，表示先將子表中的資料查詢出來以后，然后在父表中進行篩選，所以說對于子表比較小的時候使用到in 能夠提交效率，對于exists來說 表示存在于，對于父表較小的時候，子表中的查詢也就小一些)

SQL 陳述句的優化

關于大表的優化處理 某個表有近千萬資料，CRUD比較慢，如何優化？分庫分表了是怎么做的？分表分庫了有什么問題？有用到中間件么？他們的原理知道么？

首先就是說 對于資料比較多的情況下 crud 會導致比較低情況，這個時候就可以

1. 限制資料的查詢的范圍 就是說 我們不允許出現不帶任何限制條件的查詢陳述句， 我們當用戶在查詢訂單歷史的時候，我們可以控制在一個月的范圍內，；
2. 讀/寫分離： 經典的資料庫拆分方案，主庫負責寫，從庫負責讀；
3. 快取： 使用MySQL的快取，另外對重量級、更新少的資料可以考慮使用應用級別的快取；

對于垂直磁區

就是說 將一個表差分為兩個表，同時擁有主鍵，例如，用戶表中既有用戶的登錄資訊又有用戶的基本資訊，可以將用戶表拆分成兩個單獨的表，甚至放到單獨的庫做分庫，

優點就是說： 可以使得行資料變小，在查詢的時候減少io的次數此外，垂直磁區可以簡化表的結構，易于維護，
垂直拆分的缺點： 主鍵會出現冗余，需要管理冗余列，并會引起Join操作，可以通過在應用層進行Join來解決，此外，垂直磁區會讓事務變得更加復雜；

垂直分表

把主鍵和一些列放在一個表，然后把主鍵和另外的列放在另一個表中

水平磁區

保持資料表結構不變，通過某種策略存盤資料分片，這樣每一片資料分散到不同的表或者庫中，達到了分布式的目的， 水平拆分可以支撐非常大的資料量，

水平拆分是指資料表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的資料拆成多張表來存放，舉個例子：我們可以將用戶資訊表拆分成多個用戶資訊表，這樣就可以避免單一表資料量過大對性能造成影響，

水平分表

表很大，分割后可以降低在查詢時需要讀的資料和索引的頁數，同時也降低了索引的層數，提高查詢次數

可以結合explain 執行計劃來具體查看

1. 盡量避免使用非運算子號，在索引上使用 not，<>等非運算子，資料庫管理系統時不會對此使用索引的，可以將查詢陳述句轉換成為可以使用索引的查詢，
2. 避免對查詢的列的操作，任何對列的操作都可能導致全表的掃描，這個所謂的操作包括資料庫函式，計算運算式等，查詢時候要盡可能把操作移到等式的右邊，甚至去掉函式，
3. 避免不必要的型別轉換，需要注意的是盡量避免潛在的資料型別轉換，如將字符型資料域數值型資料比較，會自動將字符進行轉換，從而導致全表的掃描，
4. 適當增加查詢的范圍限制，
5. 合理使用in與exists，例如有兩個表有A與B分別有下面的情況：

• 當我們想要顯示表A中的資料，關系條件只有一個ID，對ID進行檢
  索，

SELECT * FORM A WHERE ID IN(SELECT ID FROM B);

• 當我們在對表A對資料進行顯示，但是條件不止有ID還會有其他的color時候，使用 esists

SELECT * FROM A WHERE ESISTS(SELECT 1 FROM B WHERE ID =A.ID AND COLOR= A.COLOR);

6. 去掉where 陳述句中的 is null 或者是is not null 將不會使用 索引而是使用全域搜索，就會降低查詢的效率，在查詢時候 使用分情況討論會更好一點將其改為 > 0 或者是 >’ '，
7. 避免使用前導模糊查詢，使用模糊查詢時候 是不能夠使用 索引的，
8. 在查詢的程序中避免使用 * ，當我們在查詢的程序中要是想要列出所用的column 時候，使用動態Sql列參考* 是一個方便的方法，但是不幸的是，這是一個非常低效的方法，在實際的程序中，* 會轉換成為所有的列名，這個作業是通過查詢資料字典完成的，就意味著消耗更過的時間，
9. 規范使用 要么全部大寫 要么全部小寫，不要混用，
10. 為經常出現在where字句中的列創建索引；為經常出現在 order by ，disinct后面的欄位建立索引，如果有時候會建立的復合索引，此時索引的欄位順序要和這些關鍵字后面的字符段順序一致；為經常作為表的連接條件的列上創建索引，
11. 減少表與表之間的關聯，盡量不使用全域查詢，把資料量大的表排在前面，

其他

視圖

什么是視圖？

為了提高復雜SQL陳述句的復用性和表操作的安全性，MySQL資料庫管理系統提供了視圖特性，所謂視圖，本質上是一種虛擬表，在物理上是不存在的，其內容與真實的表相似，包含一系列帶有名稱的列和行資料，但是，視圖并不在資料庫中以儲存的資料值形式存在，行和列資料來自定義視圖的查詢所參考基本表，并且在具體參考視圖時動態生成，

視圖使開發者只關心感興趣的某些特定資料和所負責的特定任務，只能看到視圖中所定義的資料，而不是視圖所參考表中的資料，從而提高了資料庫中資料的安全性，

有哪些的使用場景

視圖根本用途：簡化sql查詢，提高開發效率，如果說還有另外一個用途那就是兼容老的表結構，

下面是視圖的常見使用場景：

1. 重用SQL陳述句；

2. 簡化復雜的SQL操作，在撰寫查詢后，可以方便的重用它而不必知道它的基本查詢細節；

3. 使用表的組成部分而不是整個表；

4. 保護資料，可以給用戶授予表的特定部分的訪問權限而不是整個表的訪問權限；

5. 更改資料格式和表示，視圖可回傳與底層表的表示和格式不同的資料，

6. 限制資料訪問，視圖在本質上其實就是一條select陳述句，所以當在 訪問視圖的時候，只能訪問到Select陳述句所對應的列，對基表中的其他列起到了保護的作用，

視圖的優缺點

優點

1. 查詢簡單化，視圖能簡化用戶的操作
2. 資料安全性，視圖使用戶能以多種角度看待同一資料，能夠對機密資料提供安全保護
3. 邏輯資料獨立性，視圖對重構資料庫提供了一定程度的邏輯獨立性

缺點

1. 性能，資料庫必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個復雜的多表查詢所定義，那么，即使是視圖的一個簡單查詢，資料庫也把它變成一個復雜的結合體，需要花費一定的時間，

2. 修改限制，當用戶試圖修改視圖的某些行時，資料庫必須把它轉化為對基本表的某些行的修改，事實上，當從視圖中插入或者洗掉時，情況也是這樣，對于簡單視圖來說，這是很方便的，但是，對于比較復雜的視圖，可能是不可修改的

這些視圖有如下特征：1.有UNIQUE等集合運算子的視圖，2.有GROUP BY子句的視圖，3.有諸如AVG\SUM\MAX等聚合函式的視圖， 4.使用DISTINCT關鍵字的視圖，5.連接表的視圖（其中有些例外）

什么是觸發器

觸發器是用戶定義在關系表上的一類由事件驅動的特殊的存盤程序，觸發器是指一段代碼，當觸發某個事件時，自動執行這些代碼，

使用場景

1. 可以通過資料庫中的相關表實作級聯更改，
2. 實時監控某張表中的某個欄位的更改而需要做出相應的處理，
3. 例如可以生成某些業務的編號，
4. 注意不要濫用，否則會造成資料庫及應用程式的維護困難，

觸發器的型別

在MySQL資料庫中有如下六種觸發器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

基本型別長度問題

varchar(50)中50的涵義

最多存放50個字符，varchar(50)和(200)存盤hello所占空間一樣，但后者在排序時會消耗更多記憶體，因為order by col采用fixed_length計算col長度(memory引擎也一樣)，在早期 MySQL 版本中， 50 代表位元組數，現在代表字符數，

int(20)中20的涵義

是指顯示字符的長度，20表示最大顯示寬度為20，但仍占4位元組存盤，存盤范圍不變；

不影響內部存盤，只是影響帶 zerofill 定義的 int 時，前面補多少個 0，易于報表展示

mysql為什么這么設計

對大多數應用沒有意義，只是規定一些工具用來顯示字符的個數；int(1)和int(20)存盤和計算均一樣；

mysql中int(10)和char(10)以及varchar(10)的區別

int(10)的10表示顯示的資料的長度，不是存盤資料的大小；chart(10)和varchar(10)的10表示存盤資料的大小，即表示存盤多少個字符，

int(10) 10位的資料長度 9999999999，占32個位元組，int型4位
char(10) 10位固定字串，不足補空格 最多10個字符
varchar(10) 10位可變字串，不足補空格 最多10個字符

char(10)表示存盤定長的10個字符，不足10個就用空格補齊，占用更多的存盤空間

varchar(10)表示存盤10個變長的字符，存盤多少個就是多少個，空格也按一個字符存盤，這一點是和char(10)的空格不同的，char(10)的空格表示占位不算一個字符

基礎的MySQL練手

超過5名學生的課

有如下表：

最后輸出：

SQL陳述句：

select class from courses group by class  
 having count( distinct student) >= 5;

部門工資最高的員工

Employee 表包含所有員工資訊，每個員工有其對應的 Id, salary 和 department Id，

Department 表包含公司所有部門的資訊，

撰寫一個 SQL 查詢，找出每個部門工資最高的員工，例如，根據上述給定的表格，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資，

SELECT
	Department.NAME AS Department,
	Employee.NAME AS Employee,
	Salary 
FROM
	Employee,
	Department 
WHERE
	Employee.DepartmentId = Department.Id 
	AND ( Employee.DepartmentId, Salary ) 
    IN (SELECT DepartmentId, max( Salary ) 
        FROM Employee 
        GROUP BY DepartmentId )

分數的排名

撰寫一個 SQL 查詢來實作分數排名，

如果兩個分數相同，則兩個分數排名（Rank）相同，請注意，平分后的下一個名次應該是下一個連續的整數值，換句話說，名次之間不應該有“間隔”，

例如，根據上述給定的 Scores 表，你的查詢應該回傳（按分數從高到低排列）：

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as `Rank`
from Scores a
order by a.Score DESC