Mysql面試總結

轉載自：https://www.cxyxiaowu.com/16302.html

Q1：MySQL 的邏輯架構了解嗎？

第一層是服務器層，主要提供連接處理、授權認證、安全等功能，

第二層實作了 MySQL 核心服務功能，包括查詢決議、分析、優化、快取以及日期和時間等所有內置函式，所有跨存盤引擎的功能都在這一層實作，例如存盤程序、觸發器、視圖等，

第三層是存盤引擎層，存盤引擎負責 MySQL 中資料的存盤和提取，服務器通過 API 與存盤引擎通信，這些介面屏蔽了不同存盤引擎的差異，使得差異對上層查詢程序透明，除了會決議外鍵定義的 InnoDB 外，存盤引擎不會決議 SQL，不同存盤引擎之間也不會相互通信，只是簡單回應上層服務器請求，

Q2：談一談 MySQL 的讀寫鎖

在處理并發讀或寫時，可以通過實作一個由兩種型別組成的鎖系統來解決問題，這兩種型別的鎖通常被稱為共享鎖和排它鎖，也叫讀鎖和寫鎖，讀鎖是共享的，相互不阻塞，多個客戶在同一時刻可以同時讀取同一個資源而不相互干擾，寫鎖則是排他的，也就是說一個寫鎖會阻塞其他的寫鎖和讀鎖，確保在給定時間內只有一個用戶能執行寫入并防止其他用戶讀取正在寫入的同一資源，

在實際的資料庫系統中，每時每刻都在發生鎖定，當某個用戶在修改某一部分資料時，MySQL 會通過鎖定防止其他用戶讀取同一資料，寫鎖比讀鎖有更高的優先級，一個寫鎖請求可能會被插入到讀鎖佇列的前面，但是讀鎖不能插入到寫鎖前面，

Q3：MySQL 的鎖策略有什么？

表鎖是MySQL中最基本的鎖策略，并且是開銷最小的策略，表鎖會鎖定整張表，一個用戶在對表進行寫操作前需要先獲得寫鎖，這會阻塞其他用戶對該表的所有讀寫操作，只有沒有寫鎖時，其他讀取的用戶才能獲取讀鎖，讀鎖之間不相互阻塞，

行鎖可以最大程度地支持并發，同時也帶來了最大開銷，InnoDB 和 XtraDB 以及一些其他存盤引擎實作了行鎖，行鎖只在存盤引擎層實作，而服務器層沒有實作，

Q4：資料庫死鎖如何解決？

死鎖是指多個事務在同一資源上相互占用并請求鎖定對方占用的資源而導致惡性回圈的現象，當多個事務試圖以不同順序鎖定資源時就可能會產生死鎖，多個事務同時鎖定同一個資源時也會產生死鎖，

為了解決死鎖問題，資料庫系統實作了各種死鎖檢測和死鎖超時機制，越復雜的系統，例如InnoDB 存盤引擎，越能檢測到死鎖的回圈依賴，并立即回傳一個錯誤，這種解決方式很有效，否則死鎖會導致出現非常慢的查詢，還有一種解決方法，就是當查詢的時間達到鎖等待超時的設定后放棄鎖請求，這種方式通常來說不太好，InnoDB 目前處理死鎖的方法是將持有最少行級排它鎖的事務進行回滾，

死鎖發生之后，只有部分或者完全回滾其中一個事務，才能打破死鎖，對于事務型系統這是無法避免的，所以應用程式在設計時必須考慮如何處理死鎖，大多數情況下只需要重新執行因死鎖回滾的事務即可，

Q5：事務是什么?

事務是一組原子性的 SQL 查詢，或者說一個獨立的作業單元，如果資料庫引擎能夠成功地對資料庫應用該組查詢的全部陳述句，那么就執行該組查詢，如果其中有任何一條陳述句因為崩潰或其他原因無法執行，那么所有的陳述句都不會執行，也就是說事務內的陳述句要么全部執行成功，要么全部執行失敗，

Q6：事務有什么特性？

原子性 atomicity

一個事務在邏輯上是必須不可分割的最小作業單元，整個事務中的所有操作要么全部提交成功，要么全部失敗回滾，對于一個事務來說不可能只執行其中的一部分，

一致性 consistency

資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態，

隔離性 isolation

針對并發事務而言，隔離性就是要隔離并發運行的多個事務之間的相互影響，一般來說一個事務所做的修改在最終提交以前，對其他事務是不可見的，

持久性 durability

一旦事務提交成功，其修改就會永久保存到資料庫中，此時即使系統崩潰，修改的資料也不會丟失，

Q7：MySQL 的隔離級別有哪些？

未提交讀 READ UNCOMMITTED

在該級別事務中的修改即使沒有被提交，對其他事務也是可見的，事務可以讀取其他事務修改完但未提交的資料，這種問題稱為臟讀，這個級別還會導致不可重復讀和幻讀，性能沒有比其他級別好很多，很少使用，

提交讀 READ COMMITTED

多數資料庫系統默認的隔離級別，提交讀滿足了隔離性的簡單定義：一個事務開始時只能"看見"已經提交的事務所做的修改，換句話說，一個事務從開始直到提交之前的任何修改對其他事務都是不可見的，也叫不可重復讀，因為兩次執行同樣的查詢可能會得到不同結果，

可重復讀 REPEATABLE READ（MySQL默認的隔離級別）

可重復讀解決了不可重復讀的問題，保證了在同一個事務中多次讀取同樣的記錄結果一致，但還是無法解決幻讀，所謂幻讀指的是當某個事務在讀取某個范圍內的記錄時，會產生幻行，InnoDB 存盤引擎通過多版本并發控制MVCC 解決幻讀的問題，

可串行化 SERIALIZABLE

最高的隔離級別，通過強制事務串行執行，避免幻讀，可串行化會在讀取的每一行資料上都加鎖，可能導致大量的超時和鎖爭用的問題，實際應用中很少用到這個隔離級別，只有非常需要確保資料一致性且可以接受沒有并發的情況下才考慮該級別，

Q8：MVCC 是什么？

MVCC 是多版本并發控制，在很多情況下避免加鎖，大都實作了非阻塞的讀操作，寫操作也只鎖定必要的行，

InnoDB 的MVCC 通過在每行記錄后面保存兩個隱藏的列來實作，這兩個列一個保存了行的創建時間，一個保存行的過期時間間，不過存盤的不是實際的時間值而是系統版本號，每開始一個新的事務系統版本號都會自動遞增，事務開始時刻的系統版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較，

MVCC 只能在 READ COMMITTED 和 REPEATABLE READ 兩個隔離級別下作業，因為 READ UNCOMMITTED 總是讀取最新的資料行，而不是符合當前事務版本的資料行，而 SERIALIZABLE 則會對所有讀取的行都加鎖，

Q9：談一談 InnoDB

InnoDB 是 MySQL 的默認事務型引擎，用來處理大量短期事務，InnoDB 的性能和自動崩潰恢復特性使得它在非事務型存盤需求中也很流行，除非有特別原因否則應該優先考慮 InnoDB，

InnoDB 的資料存盤在表空間中，表空間由一系列資料檔案組成，MySQL4.1 后 InnoDB 可以將每個表的資料和索引放在單獨的檔案中，

InnoDB 采用 MVCC 來支持高并發，并且實作了四個標準的隔離級別，其默認級別是 REPEATABLE READ，并通過間隙鎖策略防止幻讀，間隙鎖使 InnoDB 不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定防止幻行的插入，

InnoDB 表是基于聚簇索引建立的，InnoDB 的索引結構和其他存盤引擎有很大不同，聚簇索引對主鍵查詢有很高的性能，不過它的二級索引中必須包含主鍵列，所以如果主鍵很大的話其他所有索引都會很大，因此如果表上索引較多的話主鍵應當盡可能小，

InnoDB 的存盤格式是平***立的，可以將資料和索引檔案從一個平臺復制到另一個平臺，

InnoDB 內部做了很多優化，包括從磁盤讀取資料時采用的可預測性預讀，能夠自動在記憶體中創建加速讀操作的自適應哈希索引，以及能夠加速插入操作的插入緩沖區等，

Q10：談一談 MyISAM

MySQL5.1及之前，MyISAM 是默認存盤引擎，MyISAM 提供了大量的特性，包括全文索引、壓縮、空間函式等，但不支持事務和行鎖，最大的缺陷就是崩潰后無法安全恢復，對于只讀的資料或者表比較小、可以忍受修復操作的情況仍然可以使用 MyISAM，

MyISAM 將表存盤在資料檔案和索引檔案中，分別以 .MYD 和 .MYI 作為擴展名，MyISAM 表可以包含動態或者靜態行，MySQL 會根據表的定義決定行格式，MyISAM 表可以存盤的行記錄數一般受限于可用磁盤空間或者作業系統中單個檔案的最大尺寸，

MyISAM 對整張表進行加鎖，讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排它鎖，但是在表有讀取查詢的同時，也支持并發往表中插入新的記錄，

對于MyISAM 表，MySQL 可以手動或自動執行檢查和修復操作，這里的修復和事務恢復以及崩潰恢復的概念不同，執行表的修復可能導致一些資料丟失，而且修復操作很慢，

對于 MyISAM 表，即使是 BLOB 和 TEXT 等長欄位，也可以基于其前 500 個字符創建索引，MyISAM 也支持全文索引，這是一種基于分詞創建的索引，可以支持復雜的查詢，

MyISAM 設計簡單，資料以緊密格式存盤，所以在某些場景下性能很好，MyISAM 最典型的性能問題還是表鎖問題，如果所有的查詢長期處于 Locked 狀態，那么原因毫無疑問就是表鎖，

Q11：談一談 Memory

如果需要快速訪問資料且這些資料不會被修改，重啟以后丟失也沒有關系，那么使用 Memory 表是非常有用的，Memory 表至少要比 MyISAM 表快一個數量級，因為所有資料都保存在記憶體，不需要磁盤 IO，Memory 表的結構在重啟后會保留，但資料會丟失，

Memory 表適合的場景：查找或者映射表、快取周期性聚合資料的結果、保存資料分析中產生的中間資料，

Memory 表支持哈希索引，因此查找速度極快，雖然速度很快但還是無法取代傳統的基于磁盤的表，Memory 表使用表級鎖，因此并發寫入的性能較低，它不支持 BLOB 和 TEXT 型別的列，并且每行的長度是固定的，所以即使指定了 VARCHAR 列，實際存盤時也會轉換成CHAR，這可能導致部分記憶體的浪費，

如果 MySQL 在執行查詢的程序中需要使用臨時表來保持中間結果，內部使用的臨時表就是 Memory 表，如果中間結果太大超出了Memory 表的限制，或者含有 BLOB 或 TEXT 欄位，臨時表會轉換成 MyISAM 表，

Q12：查詢執行流程是什么？

簡單來說分為五步：① 客戶端發送一條查詢給服務器，② 服務器先檢查查詢快取，如果命中了快取則立刻回傳存盤在快取中的結果，否則進入下一階段，③ 服務器端進行 SQL 決議、預處理，再由優化器生成對應的執行計劃，④ MySQL 根據優化器生成的執行計劃，呼叫存盤引擎的 API 來執行查詢，⑤ 將結果回傳給客戶端，

Q3：VARCHAR 和 CHAR 的區別？

VARCHAR 用于存盤可變字串，是最常見的字串資料型別，它比 CHAR 更節省空間，因為它僅使用必要的空間，VARCHAR 需要 1 或 2 個額外位元組記錄字串長度，如果列的最大長度不大于 255 位元組則只需要 1 位元組，VARCHAR 不會洗掉末尾空格，

VARCHAR 適用場景：字串列的最大長度比平均長度大很多、列的更新很少、使用了 UTF8 這種復雜字符集，每個字符都使用不同的位元組數存盤，

CHAR 是定長的，根據定義的字串長度分配足夠的空間，CHAR 會洗掉末尾空格，

CHAR 適合存盤很短的字串，或所有值都接近同一個長度，例如存盤密碼的 MD5 值，對于經常變更的資料，CHAR 也比 VARCHAR更好，因為定長的 CHAR 不容易產生碎片，對于非常短的列，CHAR 在存盤空間上也更有效率，例如用 CHAR 來存盤只有 Y 和 N 的值只需要一個位元組，但是 VARCHAR 需要兩個位元組，因為還有一個記錄長度的額外位元組，

Q14：DATETIME 和 TIMESTAMP 的區別？

DATETIME 能保存大范圍的值，從 1001~9999 年，精度為秒，把日期和時間封裝到了一個整數中，與時區無關，使用 8 位元組存盤空間，

TIMESTAMP 和 UNIX 時間戳相同，只使用 4 位元組的存盤空間，范圍比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依賴于時區，

Q15：資料型別有哪些優化策略？

更小的通常更好

一般情況下盡量使用可以正確存盤資料的最小資料型別，更小的資料型別通常也更快，因為它們占用更少的磁盤、記憶體和 CPU 快取，

盡可能簡單

簡單資料型別的操作通常需要更少的 CPU 周期，例如整數比字符操作代價更低，因為字符集和校對規則使字符相比整形更復雜，應該使用 MySQL 的內建型別 date、time 和 datetime 而不是字串來存盤日期和時間，另一點是應該使用整形存盤 IP 地址，

盡量避免 NULL

通常情況下最好指定列為 NOT NULL，除非需要存盤 NULL值，因為如果查詢中包含可為 NULL 的列對 MySQL 來說更難優化，可為 NULL 的列使索引、索引統計和值比較都更復雜，并且會使用更多存盤空間，當可為 NULL 的列被索引時，每個索引記錄需要一個額外位元組，在MyISAM 中還可能導致固定大小的索引變成可變大小的索引，

如果計劃在列上建索引，就應該盡量避免設計成可為 NULL 的列，

Q16：索引有什么作用？

索引也叫鍵，是存盤引擎用于快速找到記錄的一種資料結構，索引對于良好的性能很關鍵，尤其是當表中資料量越來越大時，索引對性能的影響愈發重要，在資料量較小且負載較低時，不恰當的索引對性能的影響可能還不明顯，但資料量逐漸增大時，性能會急劇下降，

索引大大減少了服務器需要掃描的資料量、可以幫助服務器避免排序和臨時表、可以將隨機 IO 變成順序 IO，但索引并不總是最好的工具，對于非常小的表，大部分情況下會采用全表掃描，對于中到大型的表，索引就非常有效，但對于特大型的表，建立和使用索引的代價也隨之增長，這種情況下應該使用磁區技術，

在MySQL中，首先在索引中找到對應的值，然后根據匹配的索引記錄找到對應的資料行，索引可以包括一個或多個列的值，如果索引包含多個列，那么列的順序也十分重要，因為 MySQL 只能使用索引的最左前綴，

Q17：談一談 MySQL 的 B-Tree 索引

大多數 MySQL 引擎都支持這種索引，但底層的存盤引擎可能使用不同的存盤結構，例如 NDB 使用 T-Tree，而 InnoDB 使用 B+ Tree，

B-Tree 通常意味著所有的值都是按順序存盤的，并且每個葉子頁到根的距離相同，B-Tree 索引能夠加快訪問資料的速度，因為存盤引擎不再需要進行全表掃描來獲取需要的資料，取而代之的是從索引的根節點開始進行搜索，根節點的槽中存放了指向子節點的指標，存盤引擎根據這些指標向下層查找，通過比較節點頁的值和要查找的值可以找到合適的指標進入下層子節點，這些指標實際上定義了子節點頁中值的上限和下限，最終存盤引擎要么找到對應的值，要么該記錄不存在，葉子節點的指標指向的是被索引的資料，而不是其他的節點頁，

B-Tree索引的限制：

• 如果不是按照索引的最左列開始查找，則無法使用索引，
• 不能跳過索引中的列，例如索引為 (id,name,sex)，不能只使用 id 和 sex 而跳過 name，
• 如果查詢中有某個列的范圍查詢，則其右邊的所有列都無法使用索引，

Q18：了解 Hash 索引嗎？

哈希索引基于哈希表實作，只有精確匹配索引所有列的查詢才有效，對于每一行資料，存盤引擎都會對所有的索引列計算一個哈希碼，哈希碼是一個較小的值，并且不同鍵值的行計算出的哈希碼也不一樣，哈希索引將所有的哈希碼存盤在索引中，同時在哈希表中保存指向每個資料行的指標，

只有 Memory 引擎顯式支持哈希索引，這也是 Memory 引擎的默認索引型別，

因為索引自身只需存盤對應的哈希值，所以索引的結構十分緊湊，這讓哈希索引的速度非常快，但它也有一些限制：

• 哈希索引資料不是按照索引值順序存盤的，無法用于排序，
• 哈希索引不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內容來計算哈希值的，例如在資料列(a,b)上建立哈希索引，如果查詢的列只有a就無法使用該索引，
• 哈希索引只支持等值比較查詢，不支持任何范圍查詢，

Q19：什么是自適應哈希索引？

自適應哈希索引是 InnoDB 引擎的一個特殊功能，當它注意到某些索引值被使用的非常頻繁時，會在記憶體中基于 B-Tree 索引之上再創鍵一個哈希索引，這樣就讓 B-Tree 索引也具有哈希索引的一些優點，比如快速哈希查找，這是一個完全自動的內部行為，用戶無法控制或配置，但如果有必要可以關閉該功能，

Q20 ：什么是空間索引？

MyISAM 表支持空間索引，可以用作地理資料存盤，和 B-Tree 索引不同，這類索引無需前綴查詢，空間索引會從所有維度來索引資料，查詢時可以有效地使用任意維度來組合查詢，必須使用 MySQL 的 GIS 即地理資訊系統的相關函式來維護資料，但 MySQL 對 GIS 的支持并不完善，因此大部分人都不會使用這個特性，

Q21：什么是全文索引？

通過數值比較、范圍過濾等就可以完成絕大多數需要的查詢，但如果希望通過關鍵字匹配進行查詢，就需要基于相似度的查詢，而不是精確的數值比較，全文索引就是為這種場景設計的，

MyISAM 的全文索引是一種特殊的 B-Tree 索引，一共有兩層，第一層是所有關鍵字，然后對于每一個關鍵字的第二層，包含的是一組相關的"檔案指標"，全文索引不會索引檔案物件中的所有詞語，它會根據規則過濾掉一些詞語，例如停用詞串列中的詞都不會被索引，

Q22：什么是聚簇索引？

聚簇索引不是一種索引型別，而是一種資料存盤方式，InnoDB 的聚簇索引實際上在同一個結構中保存了 B-Tree 索引和資料行，當表有聚餐索引時，它的行資料實際上存放在索引的葉子頁中，因為無法同時把資料行存放在兩個不同的地方，所以一個表只能有一個聚簇索引，

優點：① 可以把相關資料保存在一起，② 資料訪問更快，聚簇索引將索引和資料保存在同一個 B-Tree 中，因此獲取資料比非聚簇索引要更快，③ 使用覆寫索引掃描的查詢可以直接使用頁節點中的主鍵值，

缺點：① 聚簇索引最大限度提高了 IO 密集型應用的性能，如果資料全部在記憶體中將會失去優勢，② 更新聚簇索引列的代價很高，因為會強制每個被更新的行移動到新位置，③ 基于聚簇索引的表插入新行或主鍵被更新導致行移動時，可能導致頁分裂，表會占用更多磁盤空間，④ 當行稀疏或由于頁分裂導致資料存盤不連續時，全表掃描可能很慢，

Q23：什么是覆寫索引？

覆寫索引指一個索引包含或覆寫了所有需要查詢的欄位的值，不再需要根據索引回表查詢資料，覆寫索引必須要存盤索引列的值，因此 MySQL 只能使用 B-Tree 索引做覆寫索引，

優點：① 索引條目通常遠小于資料行大小，可以極大減少資料訪問量，② 因為索引按照列值順序存盤，所以對于 IO 密集型防偽查詢回避隨機從磁盤讀取每一行資料的 IO 少得多，③ 由于 InnoDB 使用聚簇索引，覆寫索引對 InnoDB 很有幫助，InnoDB 的二級索引在葉子節點保存了行的主鍵值，如果二級主鍵能覆寫查詢那么可以避免對主鍵索引的二次查詢，

Q24：你知道哪些索引使用原則？

建立索引

對查詢頻次較高且資料量比較大的表建立索引，索引欄位的選擇，最佳候選列應當從 WHERE 子句的條件中提取，如果 WHERE 子句中的組合比較多，應當挑選最常用、過濾效果最好的列的組合，業務上具有唯一特性的欄位，即使是多個欄位的組合，也必須建成唯一索引，

使用前綴索引

索引列開始的部分字符，索引創建后也是使用硬碟來存盤的，因此短索引可以提升索引訪問的 IO 效率，對于 BLOB、TEXT 或很長的 VARCHAR 列必須使用前綴索引，MySQL 不允許索引這些列的完整長度，前綴索引是一種能使索引更小更快的有效方法，但缺點是 MySQL 無法使用前綴索引做 ORDER BY 和 GROUP BY，也無法使用前綴索引做覆寫掃描，

選擇合適的索引順序

當不需要考慮排序和分組時，將選擇性最高的列放在前面，索引的選擇性是指不重復的索引值和資料表的記錄總數之比，索引的選擇性越高則查詢效率越高，唯一索引的選擇性是 1，因此也可以使用唯一索引提升查詢效率，

洗掉無用索引

MySQL 允許在相同列上創建多個索引，重復的索引需要單獨維護，并且優化器在優化查詢時也需要逐個考慮，這會影響性能，重復索引是指在相同的列上按照相同的順序創建的相同型別的索引，應該避免創建重復索引，如果創建了索引 (A,B) 再創建索引 (A) 就是冗余索引，因為這只是前一個索引的前綴索引，對于 B-Tree 索引來說是冗余的，解決重復索引和冗余索引的方法就是洗掉這些索引，除了重復索引和冗余索引，可能還會有一些服務器永遠不用的索引，也應該考慮洗掉，

Q25：索引失效的情況有哪些？

如果索引列出現了隱式型別轉換，則 MySQL 不會使用索引，常見的情況是在 SQL 的 WHERE 條件中欄位型別為字串，其值為數值，如果沒有加引號那么 MySQL 不會使用索引，

如果 WHERE 條件中含有 OR，除非 OR 前使用了索引列而 OR 之后是非索引列，索引會失效，

MySQL 不能在索引中執行 LIKE 操作，這是底層存盤引擎 API 的限制，最左匹配的 LIKE 比較會被轉換為簡單的比較操作，但如果是以通配符開頭的 LIKE 查詢，存盤引擎就無法做比較，這種情況下 MySQL 只能提取資料行的值而不是索引值來做比較，

如果查詢中的列不是獨立的，則 MySQL 不會使用索引，獨立的列是指索引列不能是運算式的一部分，也不能是函式的引數，

對于多個范圍條件查詢，MySQL 無法使用第一個范圍列后面的其他索引列，對于多個等值查詢則沒有這種限制，

如果 MySQL 判斷全表掃描比使用索引查詢更快，則不會使用索引，

索引檔案具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引，

Q26：如何定位低效 SQL？

可以通過兩種方式來定位執行效率較低的 SQL 陳述句，一種是通過慢查詢日志定位，可以通過慢查詢日志定位那些已經執行完畢的 SQL 陳述句，另一種是使用 SHOW PROCESSLIST 查詢，慢查詢日志在查詢結束以后才記錄，所以在應用反應執行效率出現問題的時候查詢慢查詢日志不能定位問題，此時可以使用 SHOW PROCESSLIST 命令查看當前 MySQL 正在進行的執行緒，包括執行緒的狀態、是否鎖表等，可以實時查看 SQL 的執行情況，同時對一些鎖表操作進行優化，找到執行效率低的 SQL 陳述句后，就可以通過 SHOW PROFILE、EXPLAIN 或 trace 等豐富來繼續優化陳述句，

Q27：SHOW PROFILE 的作用？

通過 SHOW PROFILE 可以分析 SQL 陳述句性能消耗，例如查詢到 SQL 會執行多少時間，并顯示 CPU、記憶體使用量，執行程序中系統鎖及表鎖的花費時間等資訊，例如 SHOW PROFILE CPU/MEMORY/BLOCK IO FOR QUERY N 分別查詢 id 為 N 的 SQL 陳述句的 CPU、記憶體以及 IO 的消耗情況，

Q28：trace 是干什么的？

從 MySQL5.6 開始，可以通過 trace 檔案進一步獲取優化器是是如何選擇執行計劃的，在使用時需要先打開設定，然后執行一次 SQL，最后查看 information_schema.optimizer_trace 表而都內容，該表為聯合i表，只能在當前會話進行查詢，每次查詢后回傳的都是最近一次執行的 SQL 陳述句，

Q29：EXPLAIN 的欄位有哪些，具有什么含義？

執行計劃是 SQL 調優的一個重要依據，可以通過 EXPLAIN 命令查看 SQL 陳述句的執行計劃，如果作用在表上，那么該命令相當于 DESC，EXPLAIN 的指標及含義如下：

Q30：有哪些優化 SQL 的策略？

優化 COUNT 查詢

COUNT 是一個特殊的函式，它可以統計某個列值的數量，在統計列值時要求列值是非空的，不會統計 NULL 值，如果在 COUNT 中指定了列或列的運算式，則統計的就是這個運算式有值的結果數，而不是 NULL，

COUNT 的另一個作用是統計結果集的行數，當 MySQL 確定括號內的運算式不可能為 NULL 時，實際上就是在統計行數，當使用 COUNT() 時，\ 不會擴展成所有列，它會忽略所有的列而直接統計所有的行數，

某些業務場景并不要求完全精確的 COUNT 值，此時可以使用近似值來代替，EXPLAIN 出來的優化器估算的行數就是一個不錯的近似值，因為執行 EXPLAIN 并不需要真正地執行查詢，

通常來說 COUNT 都需要掃描大量的行才能獲取精確的結果，因此很難優化，在 MySQL 層還能做的就只有覆寫掃描了，如果還不夠就需要修改應用的架構，可以增加匯總表或者外部快取系統，

優化關聯查詢

確保 ON 或 USING 子句中的列上有索引，在創建索引時就要考慮到關聯的順序，

確保任何 GROUP BY 和 ORDER BY 的運算式只涉及到一個表中的列，這樣 MySQL 才有可能使用索引來優化這個程序，

在 MySQL 5.5 及以下版本盡量避免子查詢，可以用關聯查詢代替，因為執行器會先執行外部的 SQL 再執行內部的 SQL，

優化 GROUP BY

如果沒有通過 ORDER BY 子句顯式指定要排序的列，當查詢使用 GROUP BY 時，結果***自動按照分組的欄位進行排序，如果不關心結果集的順序，可以使用 ORDER BY NULL 禁止排序，

優化 LIMIT 分頁

在偏移量非常大的時候，需要查詢很多條資料再舍棄，這樣的代價非常高，要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能，最簡單的辦法是盡可能地使用覆寫索引掃描，而不是查詢所有的列，然后根據需要做一次關聯操作再回傳所需的列，

還有一種方法是從上一次取資料的位置開始掃描，這樣就可以避免使用 OFFSET，其他優化方法還包括使用預先計算的匯總表，或者關聯到一個冗余表，冗余表只包含主鍵列和需要做排序的資料列，

優化 UNION 查詢

MySQL 通過創建并填充臨時表的方式來執行 UNION 查詢，除非確實需要服務器消除重復的行，否則一定要使用 UNION ALL，如果沒有 ALL 關鍵字，MySQL 會給臨時表加上 DISTINCT 選項，這會導致對整個臨時表的資料做唯一性檢查，這樣做的代價非常高，

使用用戶自定義變數

在查詢中混合使用程序化和關系化邏輯的時候，自定義變數可能會非常有用，用戶自定義變數是一個用來存盤內容的臨時容器，在連接 MySQL 的整個程序中都存在，可以在任何可以使用運算式的地方使用自定義變數，例如可以使用變數來避免重復查詢剛剛更新過的資料、統計更新和插入的數量等，

優化 INSERT

需要對一張表插入很多行資料時，應該盡量使用一次性插入多個值的 INSERT 陳述句，這種方式將縮減客戶端與資料庫之間的連接、關閉等消耗，效率比多條插入單個值的 INSERT 陳述句高，也可以關閉事務的自動提交，在插入完資料后提交，當插入的資料是按主鍵的順序插入時，效率更高，

Q31：MySQL 主從復制的作用？

復制解決的基本問題是讓一臺服務器的資料與其他服務器保持同步，一臺主庫的資料可以同步到多臺備庫上，備庫本身也可以被配置成另外一臺服務器的主庫，主庫和備庫之間可以有多種不同的組合方式，

MySQL 支持兩種復制方式：基于行的復制和基于陳述句的復制，基于陳述句的復制也稱為邏輯復制，從 MySQL 3.23 版本就已存在，基于行的復制方式在 5.1 版本才被加進來，這兩種方式都是通過在主庫上記錄二進制日志、在備庫重放日志的方式來實作異步的資料復制，因此同一時刻備庫的資料可能與主庫存在不一致，并且無法包裝主備之間的延遲，

MySQL 復制大部分是向后兼容的，新版本的服務器可以作為老版本服務器的備庫，但是老版本不能作為新版本服務器的備庫，因為它可能無法決議新版本所用的新特性或語法，另外所使用的二進制檔案格式也可能不同，

復制解決的問題：資料分布、負載均衡、備份、高可用性和故障切換、MySQL 升級測驗，

Q32：MySQL 主從復制的步驟？

① 在主庫上把資料更改記錄到二進制日志中，
② 備庫將主庫的日志復制到自己的中繼日志中，
③ 備庫讀取中繼日志中的事件，將其重放到備庫資料之上，

第一步是在主庫上記錄二進制日志，每次準備提交事務完成資料更新前，主庫將資料更新的事件記錄到二進制日志中，MySQL 會按事務提交的順序而非每條陳述句的執行順序來記錄二進制日志，在記錄二進制日志后，主庫會告訴存盤引擎可以提交事務了，

下一步，備庫將主庫的二進制日志復制到其本地的中繼日志中，備庫首先會啟動一個作業的 IO 執行緒，IO 執行緒跟主庫建立一個普通的客戶端連接，然后在主庫上啟動一個特殊的二進制轉儲執行緒，這個執行緒會讀取主庫上二進制日志中的事件，它不會對事件進行輪詢，如果該執行緒追趕上了主庫將進入睡眠狀態，直到主庫發送信號量通知其有新的事件產生時才會被喚醒，備庫 IO 執行緒會將接收到的事件記錄到中繼日志中，

備庫的 SQL 執行緒執行最后一步，該執行緒從中繼日志中讀取事件并在備庫執行，從而實作備庫資料的更新，當 SQL 執行緒追趕上 IO 執行緒時，中繼日志通常已經在系統快取中，所以中繼日志的開銷很低，SQL 執行緒執行的時間也可以通過配置選項來決定是否寫入其自己的二進制日志中，

標籤：其他

上一篇：工具 | PG 集群復制管理工具 repmgr

下一篇：Oracle資料庫入門(一)