夯實MySQL基礎的問題歸納

本篇文章為大家帶來了關於mysql的相關知識，其中主要總結了一些常見的問題，解決一下，包括了常規的，還有索引類、原理類和框架類的相關內容，希望對大家有幫助。

推薦學習：mysql影片教學

#常規篇

1、說一下資料庫的三大範式？ 

第一範式：字段原子性，第二範式：行唯一，有主鍵列，第三範式：每列和主鍵列都相關。

實際應用中會透過冗餘少量欄位來少關聯表，提升查詢效率。

2、只查詢一條數據，但也執行得非常慢，原因一般有哪些？

• MySQL資料庫本身就被堵住了，例如：系統或網路資源不夠
• SQL語句被堵住了，例如：表鎖，行鎖等，導致儲存引擎不會執行對應的SQL語句
• 確實是索引使用不當，沒有走索引
• 表中資料的特點導致的，走了索引，但回表次數龐大

3、count(*)、count(0)、count(id)實作方式的差別？

• 對於count(*)、count(常數)、count(主鍵)形式的count函數來說，優化器可以選擇掃描成本最小的索引執行查詢，從而提升效率，它們的執行過程是一樣的。
• 而對於count(非索引列)來說，最佳化器選擇全表掃描，說明只能在聚集索引的葉子結點順序掃描。
• count(二級索引列)只能選擇包含我們指定的列的索引去執行查詢，可能導致優化器選擇的索引執行的代價並不是最小。

4、誤刪資料怎麼辦？

1）如果資料量比較大,用實體備份xtrabackup。定期對資料庫進行全量備份,也可以做增量備份。

2）如果資料量較少,用mysqldump或mysqldumper，再利用binlog來恢復或是搭建主從的方式來恢復數據，可以從以下幾個點來恢復：

• DML誤操作語句：可以透過flashback，先解析binlog event,然後在進行反轉。
• DDL語句誤操作：只能透過全量備份 應用binlog的方式來還原資料。一旦資料量比較大,那麼恢復時間就特別長。
• rm 刪除：使用備份跨機房，或最好是跨城市保存。

5、drop、truncate 和delete 的區別

• #DELETE語句執行刪除的過程是每次從表中刪除一行，並且同時將該行的刪除操作作為交易記錄在日誌中保存以便進行回滾操作。
• TRUNCATE TABLE 則一次地從表中刪除所有的資料並不會把單獨的刪除操作記錄記入日誌保存，刪除行是不能恢復的。並且在刪除的過程中不會啟動與表格相關的刪除觸發器，執行速度快。
• drop語句將表所佔用的空間全釋放掉。

6、MySQL大表查詢為什麼不會爆記憶體？

• MySQL 是“邊讀邊發的”，這意味著，如果客戶端接收得慢，會導致MySQL 服務端因為結果發不出去，這個交易的執行時間變長。
• 服務端並不需要保存一個完整的結果集。取資料和發送資料的流程都是透過一個next_buffer來操作的。
• 記憶體的資料頁是在 Buffer Pool (BP) 中管理的。
• InnoDB 管理 Buffer Pool 使用改進的 LRU 演算法，是用鍊錶來實現的。在 InnoDB 實作上，以 5:3 的比例把整個 LRU 鍊錶分成了 young 區域和 old 區域，確保大批量載入冷資料時不會沖掉熱點資料。

7、深度分頁（超大分頁）怎麼處理?

• #用id優化：先找到上次分頁的最大ID,然後利用id上的索引來查詢,類似select * from user where id>1000000 limit 100。
• 用覆蓋索引優化：Mysql的查詢完全命中索引的時候,稱為覆蓋索引,是非常快的,因為查詢只需要在索引上進行查找,之後可以直接返回,而不用再回表拿資料.因此我們可以先查出索引的ID,然後根據Id拿資料。
• 在業務允許的情況下限制頁數

8、日常開發中你是怎麼優化SQL的？

• 新增適當索引：對作為查詢條件和order by的欄位建立索引，對於多個查詢欄位的考慮建立組合索引，同時注意組合索引欄位的順序,將最常用作限制條件的列放在最左邊，依序遞減，索引不宜太多，一般5個以內。
• 優化表格結構：數字型欄位優於字串類型，資料類型更小通常更好，盡量使用NOT NULL
• 優化查詢語句：分析SQl執行計劃，是否命中索引等，如果SQL很複雜，最佳化SQL結構，如果表格資料量太大，考慮分錶

9、MySQL 的並發連接與並發查詢什麼不同？

• 在執行show processlist的結果裡，看到了數千個連接，指的是並發連接。
• 而"目前正在執行"的語句，才是並發查詢。
• 並發連線數多影響的是記憶體。
• 並發查詢太高對CPU不利。一個機器的CPU核數有限，線程全衝進來，上下文切換的成本就會太高。
• 要注意的是，在執行緒進入鎖等待以後，並發執行緒計數減一，所以等行鎖或間隙鎖時的執行緒是不算在計數範圍內的。也就是說進入鎖等待的執行緒不吃CPU，從而避免整個系統鎖死。

10、MySQL更新欄位值為原來的值內部是怎麼操作呢？

• 相同的資料時，不會做update更新。
• 不過對不同的binlog格式，處理的日誌方式有所不同：
  • 1）基於row模式時，server層匹配到要更新的記錄，發現新值和舊值一致，不做更新，就直接返回，也不記錄binlog。
  • 2）基於 statement 或 mixed格式時，MySQL執行 update 語句，並且把更新語句記錄到binlog。

11、datetime和timestamp有什麼不同？

• datetime 的日期範圍是1001——9999 年；timestamp 的時間範圍是1970——2038 年
• datetime 儲存時間與時區無關；timestamp 儲存時間與時區有關，顯示的值也依賴時區
• datetime 的儲存空間為8 位元組；timestamp 的儲存空間為4 位元組
• datetime 的預設值為null；timestamp 的欄位預設不為空(not null)，預設值為當前時間(current_timestamp)

#12、交易的隔離等級有哪些？

• 「讀取未提交」（Read Uncommitted）最低級別，任何情況都無法保證
• 「讀取已提交」（Read Committed）可避免髒讀的發生
• 「可重複讀取」（Repeatable Read）可避免髒讀、無法重複讀取的發生
• 「序列化」（Serializable）可避免髒讀、無法重複讀取、幻讀的發生
• Mysql預設的交易隔離等級是「可重複讀取」(Repeatable Read)

13、在MySQL 中有兩個kill 指令

• kill query 執行緒id，表示終止這個執行緒中正在執行的語句
• kill connection 執行緒id，這裡connection 可缺省，表示斷開這個執行緒的連線

索引篇

1、索引分類有哪些？

• 根據葉子節點的內容，索引類型分為主鍵索引和非主鍵索引。
• 主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵索引也被稱為叢集索引（clustered index）。
• 非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引（secondary index）。

2、聚集索引和非聚集索引有什麼不同？

•  聚集索引：聚集索引就是以主鍵建立的索引，聚集索引在葉子節點儲存的是表格中的資料。

    

• 非聚集索引：非主鍵建立的索引，在葉子節點儲存的是主鍵和索引列，使用非聚集索引查詢出資料時，拿到葉子上的主鍵再去查到想要找的數據。 (拿到主鍵再找這個過程叫做回表)。

  

• 覆蓋索引：假設所查詢的列，剛好都是索引對應的列，不用再回表查，那麼這個索引列就叫覆蓋索引。  

3、InnoDB 為什麼設計B 樹，而不是B-Tree，Hash，二元樹，紅黑樹？

• 雜湊索引能夠以 O(1) 的速度處理單一資料行的增刪改查，但是面對範圍查詢或排序時就會導致全表掃描的結果。
• B樹可以在非葉結點中儲存數據，由於所有的節點都可能包含目標數據，我們總是要從根節點向下遍歷子樹查找滿足條件的數據行，這個特點帶來了大量的隨機I/O，造成效能下降。
• B 樹所有的資料行都儲存在葉節點中，而這些葉節點可以透過『指標』依次依序連接，當我們在如下所示的B 樹遍歷資料時可以直接在多個子節點之間進行跳轉，這樣就能夠節省大量的磁碟I/O 時間。

• 二元樹：樹的高度不均勻，無法自平衡，找出效率跟資料有關（樹的高度），且IO代價高。

• 紅黑樹：樹的高度隨著資料量增加而增加，IO代價高。

4、講一講叢集索引與非叢集索引？

• 在InnoDB裡，索引B Tree的葉子節點儲存了整行資料的是主鍵索引，也稱為叢集索引，即將資料儲存與索引放到了一塊，找到索引也就找到了數據。
• 而索引B Tree的葉子節點儲存了主鍵的值的是非主鍵索引，也稱為非聚集索引、二級索引。
• 第一次索引一般是順序IO，回表的運算屬於隨機IO。 需要回表的次數越多，也就是隨機IO次數越多，我們就越傾向於使用全表掃描 。

5、非叢集索引一定會回表查詢嗎？

• 不一定，這涉及到查詢語句所要求的欄位是否全部命中了索引，如果全部命中了索引，那麼就不必再進行回表查詢。一個索引包含（覆蓋）所有需要查詢欄位的值，被稱之為「覆蓋索引」。

6、講一講MySQL的最左字首原則？

• 最左前綴原則就是最左優先，在建立多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。
• 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的順序可以任意調整。
• =和in可以亂序，例如a = 1 and b = 2 and c = 3 建立（a，b，c）索引可以任意順序，MySQL的查詢最佳化器會幫你優化成索引可以辨識的形式。

7、什麼是索引下推？

• 滿足最左前綴原則的時候，最左前綴可以用來在索引中定位記錄。
• 在 MySQL 5.6 之前，只能從ID開始一個個回表。到主鍵索引找出資料行，再比較欄位值。
• 而MySQL 5.6 引入的索引下推優化（index condition pushdown)， 可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

8、Innodb為什麼要用自增id當主鍵？

• 如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序添加到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁。如果使用非自增主鍵（如果身分證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要插到現有索引頁得中間某個位置， 頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不透過OPTIMIZE TABLE（optimize table）來重建表並優化填充頁面。

9、事務ACID特性的實作原理？

• 「原子性」：是使用 undo log 來實現的，如果在事務執行過程中出錯或使用者執行了rollback，系統透過undo log日誌傳回交易開始的狀態。
• 「持久性」：使用 redo log 來實現，只要redo log日誌持久化了，當系統崩潰，即可透過redo log把資料恢復。
• 「隔離性」：透過鎖定以及 MVCC,使交易相互隔離。
• 「一致性」：透過回溯、恢復，以及並發情況下的隔離性，從而實現一致性。

10、MyISAM和InnoDB實作B樹索引方式的差異是什麼？

• InnoDB 儲存引擎：B 樹索引的葉子節點保存資料本身；

• MyISAM 儲存引擎：B 樹索引的葉子節點保存資料的實體位址；

• InnoDB，其資料檔案本身就是索引文件，相較於MyISAM，索引檔案和資料檔案是分離的，其表資料檔案本身就是依B Tree組織的一個索引結構，樹的節點data域保存了完整的資料記錄，這個索引的key是資料表的主鍵，因此InnoDB表資料檔本身就是主索引，這稱為「叢集索引」或聚集索引，而其餘的索引都作為輔助索引，輔助索引的data域儲存會對應記錄主鍵的值而不是位址，這也是和MyISAM不同的地方。

11、索引有哪些分類？

• 根據葉子節點的內容，索引類型分為主鍵索引和非主鍵索引。
• 主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵索引也被稱為叢集索引（clustered index）。
• 非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引（secondary index）。 

12、有哪些場景會導致索引失效？

背景：B 樹提供的這個快速定位能力，來自同一層兄弟節點的有序性，所以說破壞了這個有序性，大概率就失效了，具體有以下幾種情況：

• 對索引使用左或左右模糊配對：也就是like %xx 或like %xx% 這兩種方式都會造成索引失效。原因在於查詢的結果可能是「陳林、張林、周林」等之類的，所以不知道從哪個索引值開始比較，於是就只能透過全表掃描的方式來查詢。

• 對索引使用函數/對索引進行表達式計算：因為索引保存的是索引欄位的原始值，而不是經過函數計算的值，自然就沒辦法走索引了。

• 對索引隱含型別轉換：相當於用了新函數

• #WHERE 子句中的OR：的意義就是兩個只要滿足一個即可，因此只有一個條件列是索引列是沒有意義的，只要有條件列不是索引列，就會進行全表掃描。

方案篇

1、有一個未分庫分錶的系統，如何設計才可以讓系統動態切換到分庫分錶上？

• 停機擴容（不建議）
• 雙寫遷移方案：設計好擴容後的表結構方案，然後對單庫和分庫實現雙寫，觀察一週沒問題後，關閉單一庫的讀取流量，再觀察一段時間，持續穩定後，關閉單一庫的寫入流量，平滑切換到分庫分錶。

2、如何設計可以動態擴容縮容的分庫分錶方案？ 

原理篇

1、一條 MySQL 語句執行步驟是什麼樣的？

#

• Server層依序執行sql的步驟為：
• 客戶端請求-> 連接器（驗證使用者身份，給予權限） -> 查詢快取（存在快取則直接傳回，不存在則執行後續操作）-> 分析器（對SQL進行詞法分析和語法分析操作） -> 優化器（主要對執行的sql優化選擇最優的執行方案方法） -> 執行器（執行時會先看使用者是否有執行權限，有才去使用這個引擎提供的介面）-> 去引擎層取得資料回傳（如果開啟查詢快取則會快取查詢結果）。

2、order by 排序內部原理是什麼樣的？

• MySQL會為每個執行緒分配一個記憶體（sort_buffer）用於排序該記憶體大小為sort_buffer_size。
• 如果排序的資料量小於sort_buffer_size，排序將會在記憶體中完成。
• 如果排序資料量很大，記憶體中無法存下這麼多數據，則會使用磁碟暫存檔案來輔助排序，也稱為外部排序。
• 在使用外部排序時，MySQL會分成好幾份單獨的暫存檔案用來存放排序後的數據，然後在將這些檔案合併成一個大檔案。

3、MVCC 實作原理？

• MVCC(Multiversion concurrency control) 是同一份資料保留多個版本的一種方式，進而實現並發控制。查詢的時候，透過read view和版本鏈找到對應版本的資料。
• 作用：提升並發效能。對於高並發場景，MVCC 比行級鎖定開銷更小。
• MVCC 的實作依賴版本鏈，版本鍊是透過表格的三個隱藏欄位實現。
  • 1）DB_TRX_ID：目前事務 id，透過交易 id 的大小來判斷交易的時間順序。
  • 2）DB_ROLL_PRT：回滾指針，指向目前行記錄的上一個版本，透過這個指針將資料的多個版本連接在一起構成undo log版本鏈。
  • 3）DB_ROLL_ID：主鍵，如果資料表沒有主鍵，InnoDB 會自動產生主鍵。

4、change buffer是什麼，有何作用？

5、MySQL是如何保證資料不會遺失?

• #只要redolog 和binlog 保證持久化磁碟就能確保MySQL 異常重新啟動後數據恢復binlog 寫入機制。
• redolog確保系統異常後，遺失的資料可以重做，binlog將資料歸檔，確保遺失的資料可以恢復。
• 事務執行前先寫redolog，事務執行過程中，先把日誌寫到 binlog cache 裡，事務提交的時候，再把 binlog cache 寫到 binlog 檔案中。

6、為什麼刪除了表，表檔案的大小還是沒變？

• 資料項目刪除之後InnoDB標記 page A 會被標記為可重複使用
• delete 指令把整個表的資料刪除呢？結果就是，所有的資料頁都會被標記為可重複使用。但是磁碟上，檔案不會變小。
• 經過大量增刪改的表，都是可能是存在空洞的。這些空洞也佔空間所以，如果能夠把這些空洞去掉，就能達到收縮表空間的目的。
• 重建表，就可以達到這樣的目的。可以使用 alter table A engine=InnoDB 指令來重建表格。

7、binlog三種格式比較

• row格式的binlog所記錄的操作行的主鍵id以及每個欄位的真實值，所以不會出現主備操作資料不一致的情況。
• statement：記錄的來源SQL語句
• mixed：前兩種混合，為什麼還需要有mixed格式的文件，因為有些statement 格式的binlog 可能會導致主備不一致，所以要使用row 格式。但 row 格式的缺點是，很佔空間。 MySQL 就取了個折中方案，MySQL 自己會判斷這條 SQL 語句是否可能造成主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。

8、MySQL加鎖規則

• #原則1：加鎖的基本單位是next-key lock，next-key lock 是前開後閉區間。
• 原則2：尋找過程中存取到的物件才會加鎖
• 優化1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。
• 最佳化2：索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖定
• 一個bug：唯一索引上的範圍查詢會存取到不滿足條件的第一個值為止。

9、什麼是髒讀、不可重複讀、幻讀呢？

• 「髒讀」: 髒讀指的是讀到了其他交易未提交的數據，未提交意味著這些數據可能會回滾，也就是可能最終不會存到資料庫中，也就是不存在的資料。讀到了不一定最終存在的數據，這就是髒讀。
• 「不可重複讀」: 不可重複讀指的是在一個事務內，最開始讀到的資料和事務結束前的任意時刻讀到的同一批資料出現不一致的情況。
• 「幻讀」: 幻讀，並不是說兩次讀取所獲得的結果集不同，幻讀側重的面向是某一次的select 操作得到的結果的資料狀態無法支撐後續的業務操作。更具體一些：select 某記錄是否存在，不存在，準備插入此記錄，但執行 insert 時發現此記錄已存在，無法插入，此時就發生了幻讀。

10、MySQL都有哪些鎖呢?像上面那樣子進行鎖定豈不是有點阻礙並發效率了?

• 從鎖的類別上來講,有共享鎖和排他鎖。
  • 1）共享鎖定: 又叫做讀鎖. 當使用者要進行資料的讀取時,對資料加上共享鎖定.共享鎖定可以同時加上多個。
  • 2）排他鎖: 又叫做寫鎖. 當使用者要進行資料的寫入時,對資料加上排他鎖.排他鎖只可以加一個,他和其他的排他鎖,共享鎖都相斥。
• 鎖定的粒度取決於特定的儲存引擎,InnoDB實作了行級鎖定,頁級鎖定,表格層級鎖定。

• 他們的加鎖開銷從大大小,並發能力也是從大到小。

框架篇

1、Mysql 主從複製原理的是啥？

• Master的更新事件(update、insert、delete)會依照順序寫入bin-log。當Slave連接到Master的後,Master機器會為Slave開啟binlog dump線程,該線程會去讀取bin-log日誌。
• Slave連接到Master後,Slave庫有一個I/O線程 透過請求binlog dump thread讀取bin-log日誌,然後寫入從庫的relay log日誌中。
• Slave還有一個 SQL執行緒,即時監控 relay-log日誌內容是否有更新,解析檔案中的SQL語句,在Slave資料庫中去執行。 

2、Mysql主從複製同步方式有哪些？

• 非同步複製：Mysql主從同步 預設是非同步複製的。就是上面三步驟中,只有第一步是同步的(也就是Mater寫入bin log日誌),就是主庫寫入binlog日誌後即可成功返回客戶端，無須等待binlog日誌傳遞給從庫的過程。
• 同步複製：對於同步複製而言，Master主機將事件傳送給Slave主機後會觸發一個等待，直到所有Slave節點（如果有多個Slave）返回資料複製成功的訊息給Master。
• 半同步複製：對於半同步複製而言，Master主機將事件傳送給Slave主機後會觸發一個等待，直到其中一個Slave節點（如果有多個Slave）回傳資料複製成功的訊息給Master。

3、Mysql主從同步延遲產生原因?怎麼最佳化？

• 主節點如果執行一個很大的事務，那麼就會對主從延遲產生較大的影響

• 網絡延遲，日誌較大，slave數量過多

• 主上多執行緒寫入，從節點只有單執行緒同步

• ##機效能問題，從節點是否使用了「爛機器」

• 鎖定衝突問題也可能導致從機的SQL執行緒執行慢

4、Mysql主從同步延遲產生原因?怎麼最佳化？

• 大交易：將大交易分成小事務，分批更新資料
• #減少Slave的數量，不要超過5個，減少單次交易的大小
• Mysql 5.7之後，可以使用多執行緒複製，使用MGR複製架構
• 在磁碟、raid卡、調度策略有問題的情況下可能會出現單一IO延遲很高的情況，可用iostat指令查看DB資料盤的IO情況，再進一步判斷
• 針對鎖定問題可以透過抓去processlist以及查看information_schema下面和鎖定以及事務相關的表來查看。

6、bin log/redo log/undo log是什麼？

• bin log是Mysql資料庫層級的文件，記錄對Mysql資料庫執行修改的所有操作，不會記錄select和show語句。
• redo log中記錄的是要更新的數據，例如一條數據已提交成功，並不會立即同步到磁碟，而是先記錄到redo log中，等待合適的時機再刷盤，為了實現事務的持久性。
• undo log用於資料的撤回操作，它保留了記錄修改前的內容。透過undo log可以實現事務回滾，並且可以根據undo log回溯到某個特定的版本的數據，實現MVCC。

推薦學習：mysql影片教學

#

以上是夯實MySQL基礎的問題歸納的詳細內容。更多資訊請關注PHP中文網其他相關文章！