最全MySQL面試20題和答案(一)

ikestu小猪發表於2024-08-06
MySql面試

資料庫基礎知識

  1. 為什麼要使用資料庫？

    • 資料儲存在記憶體
      • 優點：存取速度快
      • 缺點：資料不能永久儲存
    • 資料儲存在檔案
      • 優點：資料永久儲存
      • 缺點：
        1. 速度比記憶體操作慢，頻繁的IO操作。
        2. 查詢資料不方便
    • 資料儲存在資料庫
      1. 資料永久儲存
      2. 使用SQL語句，查詢方便效率高。
      3. 管理資料方便

  2. 什麼是SQL?

    • 結構化查詢語言(Structured Query Language)簡稱SQL，是一種資料庫查詢語言。
    • 作用：用於存取資料、查詢、更新和管理關聯式資料庫系統。

  3. 什麼是MySQL?

    • MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，屬於 Oracle 旗下產品。MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關聯式資料庫管理系統) 應用軟體之一。在Java企業級開發中非常常用，因為 MySQL 是開源免費的，並且方便擴充套件。

  4. 資料庫三大正規化是什麼？

    • 第一正規化：每個列都不可以再拆分。
    • 第二正規化：在第一正規化的基礎上，非主鍵列完全依賴於主鍵，而不能是依賴於主鍵的一部分。
    • 第三正規化：在第二正規化的基礎上，非主鍵列只依賴於主鍵，不依賴於其他非主鍵。
    • 在設計資料庫結構的時候，要儘量遵守三正規化，如果不遵守，必須有足夠的理由。比如效能。事實上我們經常會為了效能而妥協資料庫的設計。

  5. MySQL有關許可權的表都有哪幾個？

    • MySQL伺服器透過許可權表來控制使用者對資料庫的訪問，許可權表存放在mysql資料庫裡，由mysql_install_db指令碼初始化。這些許可權表分別user，db，table_priv，columns_priv和host。
      • 下面分別介紹一下這些表的結構和內容：
        • user許可權表：記錄允許連線到伺服器的使用者帳號資訊，裡面的許可權是全域性級的。
        • db許可權表：記錄各個帳號在各個資料庫上的操作許可權。
        • table_priv許可權表：記錄資料表級的操作許可權。
        • columns_priv許可權表：記錄資料列級的操作許可權。
        • host許可權表：配合db許可權表對給定主機上資料庫級操作許可權作更細緻的控制。這個許可權表不受GRANT和REVOKE語句的影響。

  6. MySQL的binlog有有幾種錄入格式？分別有什麼區別？

    • 有三種格式，statement，row和mixed。
      • statement模式下，每一條會修改資料的sql都會記錄在binlog中。不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高效能。由於sql的執行是有上下文的，因此在儲存的時候需要儲存相關的資訊，同時還有一些使用了函式之類的語句無法被記錄複製。
      • row級別下，不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改。記錄單元為每一行的改動，基本是可以全部記下來但是由於很多操作，會導致大量行的改動(比如alter table)，因此這種模式的檔案儲存的資訊太多，日誌量太大。
      • mixed，一種折中的方案，普通操作使用statement記錄，當無法使用statement的時候使用row。此外，新版的MySQL中對row級別也做了一些最佳化，當表結構發生變化的時候，會記錄語句而不是逐行記錄。

引擎

  1. MySQL儲存引擎？

    • 常用的儲存引擎有以下：
      • Innodb引擎：Innodb引擎提供了對資料庫ACID事務的支援。並且還提供了行級鎖和外來鍵的約束。它的設計的目標就是處理大資料容量的資料庫系統。
      • MyIASM引擎(原本Mysql的預設引擎)：不提供事務的支援，也不支援行級鎖和外來鍵。
      • MEMORY引擎：所有的資料都在記憶體中，資料的處理速度快，但是安全性不高。

  2. MyISAM與InnoDB區別

    • MyISAM與InnoDB區別

  3. InnoDB引擎的4大特性

    • 插入緩衝（insert buffer)
    • 二次寫(double write)
    • 自適應雜湊索引(ahi)
    • 預讀(read ahead)

  4. 儲存引擎選擇

    • 如果沒有特別的需求，使用預設的Innodb即可。
    • MyISAM：以讀寫插入為主的應用程式，比如部落格系統、新聞入口網站。
    • Innodb：更新（刪除）操作頻率也高，或者要保證資料的完整性；併發量高，支援事務和外來鍵。比如OA自動化辦公系統。

索引

  1. 什麼是索引？

    • 索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含著對資料表裡所有記錄的引用指標。
    • 索引是一種資料結構。資料庫索引，是資料庫管理系統中一個排序的資料結構，以協助快速查詢、更新資料庫表中資料。索引的實現通常使用B樹及其變種B+樹。
    • 更通俗的說，索引就相當於目錄。為了方便查詢書中的內容，透過對內容建立索引形成目錄。索引是一個檔案，它是要佔據物理空間的。

  2. 索引有哪些優缺點？

    • 索引的優點
      • 可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。
      • 透過使用索引，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。
    • 索引的缺點
      • 時間方面：建立索引和維護索引要耗費時間，具體地，當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率；
      • 空間方面：索引需要佔物理空間。

  3. 索引有哪幾種型別？

    • 主鍵索引：資料列不允許重複，不允許為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); 建立全文索引

  4. 索引的資料結構（B樹，hash）

    • 索引的資料結構和具體儲存引擎的實現有關，在MySQL中使用較多的索引有Hash索引，B+樹索引等，而我們經常使用的InnoDB儲存引擎的預設索引實現為：B+樹索引。對於雜湊索引來說，底層的資料結構就是雜湊表，因此在絕大多數需求為單條記錄查詢的時候，可以選擇雜湊索引，查詢效能最快；其餘大部分場景，建議選擇BTree索引。

  5. 索引演算法有哪些？

    • 索引演算法有BTree演算法和Hash演算法
      • BTree演算法：BTree是最常用的mysql資料庫索引演算法，也是mysql預設的演算法。它不僅可以用於=，>，>=，<，<=和between這些比較運算子上，而且還可以用於like運算子，只要它的查詢條件是一個不以萬用字元開頭的常量。
      • Hash演算法：Hash索引只能用於對等比較，例如=，<=>（相當於=）運算子。由於是一次定位資料，不像BTree索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高於BTree索引。

  6. 索引設計的原則？

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

  7. 建立索引的原則

    • 索引雖好，但也不是無限制的使用，最好符合以下幾個原則：
      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. 對於那些查詢中很少涉及的列，重複值比較多的列不要建立索引。
      8. 對於定義為text、image和bit的資料型別的列不要建立索引。

  8. 建立索引的三種方式，刪除索引

    • 建立索引的三種方式，刪除索引

  9. 建立索引時需要注意什麼？

    • 非空欄位：應該指定列為NOT NULL，除非你想儲存NULL。在MySQL中，含有空值的列很難進行查詢最佳化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值；
    • 取值離散大的欄位：（變數各個取值之間的差異程度）的列放到聯合索引的前面，可以透過count()函式檢視欄位的差異值，返回值越大說明欄位的唯一值越多欄位的離散程度高；
    • 索引欄位越小越好：資料庫的資料儲存以頁為單位一頁儲存的資料越多一次IO操作獲取的資料越大效率越高。

  10. 使用索引查詢一定能提高查詢的效能嗎？為什麼？

    • 通常，透過索引查詢資料比全表掃描要快。但是我們也必須注意到它的代價。
    • 索引需要空間來儲存，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁碟I/O。
    • 因為索引需要額外的儲存空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢效能，索引範圍查詢(INDEX RANGE SCAN)適用於兩種情況:
      • 基於一個範圍的檢索，一般查詢返回結果集小於表中記錄數的30%
      • 基於非唯一性索引的檢索

好了，今天先分享20題，之後的部分會在後面幾期分享出來，大家在面試之前可以多看看這部分的內容，萬一面試官考到，你答對了，你就比別人更有優勢，一起加油吧！！！

相關文章