夯實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基礎的問題歸納的詳細內容，更多請關注TW511.COM其它相關文章！