最全MySQL面試題和答案(三)

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

檢視

1. 為什麼要使用檢視？什麼是檢視？

  • 為了提高複雜 SQL 語句的複用性和表操作的安全性，MySQL 資料庫管理系統提供了檢視特性。所謂檢視，本質上是一種虛擬表，在物理上是不存在的，其內容與真實的表相似，包含一系列帶有名稱的列和行資料。但是，檢視並不在資料庫中以儲存的資料值形式存在。行和列資料來自定義檢視的查詢所引用基本表，並且在具體引用檢視時動態生成。
  • 檢視使開發者只關心感興趣的某些特定資料和所負責的特定任務，只能看到檢視中所定義的資料，而不是檢視所引用表中的資料，從而提高了資料庫中資料的安全性。

2. 檢視有哪些特點？
檢視的特點如下:

  • 檢視的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關係。
  • 檢視是由基本表（實表）產生的表（虛表）。
  • 檢視的建立和刪除不影響基本表。
  • 對檢視內容的更新（新增，刪除和修改）直接影響基本表。
  • 當檢視來自多個基本表時，不允許新增和刪除資料。
  • 檢視的操作包括建立檢視，檢視檢視，刪除檢視和修改檢視。

3. 檢視的使用場景有哪些？
檢視根本用途：簡化 SQL 查詢，提高開發效率。如果說還有另外一個用途，那就是相容老的表結構。

  • 重用 SQL 語句；
  • 簡化複雜的 SQL 操作。在編寫查詢後，可以方便地重用它而不必知道它的基本查詢細節；
  • 使用表的組成部分而不是整個表；
  • 保護資料。可以給使用者授予表的特定部分的訪問許可權而不是整個表的訪問許可權；
  • 更改資料格式和表示。檢視可返回與底層表的表示和格式不同的資料。

4. 檢視的優點？

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

5. 檢視的缺點？

  • 效能。資料庫必須把檢視的查詢轉化成對基本表的查詢，如果這個檢視是由一個複雜的多表查詢所定義，那麼，即使是檢視的一個簡單查詢，資料庫也把它變成一個複雜的結合體，需要花費一定的時間。
  • 修改限制。當使用者試圖修改檢視的某些行時，資料庫必須把它轉化為對基本表的某些行的修改。事實上，當從檢視中插入或者刪除時，情況也是這樣。對於簡單檢視來說，這是很方便的，但是，對於比較複雜的檢視，可能是不可修改的。

這些檢視有如下特徵：

  1. 有 UNIQUE 等集合運算子的檢視；
  2. 有 GROUP BY 子句的檢視；
  3. 有諸如 AVG、SUM、MAX 等聚合函式的檢視；
  4. 使用 DISTINCT 關鍵字的檢視；
  5. 連線表的檢視（其中有些例外）。

6. 什麼是遊標？
遊標是系統為使用者開設的一個資料緩衝區，存放 SQL 語句的執行結果，每個遊標區都有一個名字。使用者可以透過遊標逐一獲取記錄並賦給主變數，交由主語言進一步處理。

7. 如何定位及最佳化 SQL 語句的效能問題？建立的索引有沒有被使用到？或者說怎麼才可以知道這條語句執行很慢的原因？

8. 大表資料查詢，怎麼最佳化？

  • 最佳化 schema、SQL 語句 + 索引；
  • 加快取，如 memcached, redis；
  • 主從複製，讀寫分離；
  • 垂直拆分，根據你模組的耦合度，將一個大的系統分為多個小的系統，也就是分散式系統；
  • 水平切分，針對資料量大的表，這一步最麻煩，最能考驗技術水平，要選擇一個合理的 sharding key，為了有好的查詢效率，表結構也要改動，做一定的冗餘，應用也要改，SQL 中儘量帶 sharding key，將資料定位到限定的表上去查，而不是掃描全部的表。

9. MySQL 分頁？
LIMIT 子句可以被用於強制 SELECT 語句返回指定的記錄數。LIMIT 接受一個或兩個數字引數。引數必須是一個整數常量。如果給定兩個引數，第一個引數指定第一個返回記錄行的偏移量，第二個引數指定返回記錄行的最大數目。初始記錄行的偏移量是 0（而不是 1）。

mysql> SELECT * FROM table LIMIT 5,10; -- 檢索記錄行 6-15

為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個引數為 -1：

mysql> SELECT * FROM table LIMIT 95,-1; -- 檢索記錄行 96-last.

如果只給定一個引數，它表示返回最大的記錄行數目：

mysql> SELECT * FROM table LIMIT 5; -- 檢索前 5 個記錄行

換句話說，LIMIT n 等價於 LIMIT 0,n。

10. 慢查詢日誌？
用於記錄執行時間超過某個臨界值的 SQL 日誌，用於快速定位慢查詢，為我們的最佳化做參考。

開啟慢查詢日誌

配置項：slow_query_log
可以使用 SHOW VARIABLES LIKE 'slow_query_log' 檢視是否開啟，如果狀態值為 OFF，可以使用 SET GLOBAL slow_query_log = ON 來開啟，它會在 datadir 下產生一個 xx-slow.log 的檔案。

設定臨界時間

配置項：long_query_time
檢視：SHOW VARIABLES LIKE 'long_query_time'，單位秒
設定：SET long_query_time=0.5
實操時應該從長時間設定到短的時間，即將最慢的 SQL 最佳化掉。

檢視日誌，一旦 SQL 超過了我們設定的臨界時間就會被記錄到 xxx-slow.log 中。

11. 關心過業務系統裡面的 SQL 耗時嗎？統計過慢查詢嗎？對慢查詢都怎麼最佳化過？
在業務系統中，除了使用主鍵進行的查詢，其他的我都會在測試庫上測試其耗時，慢查詢的統計主要由運維在做，會定期將業務中的慢查詢反饋給我們。

慢查詢的最佳化首先要搞明白慢的原因是什麼？是查詢條件沒有命中索引？是載入了不需要的資料列？還是資料量太大？

最佳化也是針對這三個方向：

  1. 首先分析語句，看看是否載入了額外的資料，可能是查詢了多餘的行並且拋棄掉了，可能是載入了許多結果中並不需要的列，對語句進行分析以及重寫；
  2. 分析語句的執行計劃，然後獲得其使用索引的情況，之後修改語句或者修改索引，使得語句可以儘可能地命中索引；
  3. 如果對語句的最佳化已經無法進行，可以考慮表中的資料量是否太大，如果是的話可以進行橫向或者縱向的分表。

12. 最佳化查詢過程中的資料訪問？

  • 訪問資料太多導致查詢效能下降；
  • 確定應用程式是否在檢索大量超過需要的資料，可能是太多行或列；
  • 確認 MySQL 伺服器是否在分析大量不必要的資料行；
  • 避免犯如下 SQL 語句錯誤：
    • 查詢不需要的資料。解決辦法：使用 LIMIT 解決；
    • 多表關聯返回全部列。解決辦法：指定列名；
    • 總是返回全部列。解決辦法：避免使用 SELECT *；
    • 重複查詢相同的資料。解決辦法：可以快取資料，下次直接讀取快取；
    • 是否在掃描額外的記錄。解決辦法：
      • 使用 EXPLAIN 進行分析，如果發現查詢需要掃描大量的資料，但只返回少數的行，可以透過如下技巧去最佳化：
        • 使用索引覆蓋掃描，把所有的列都放到索引中，這樣儲存引擎不需要回表獲取對應行就可以返回結果；
        • 改變資料庫和表的結構，修改資料表正規化；
        • 重寫 SQL 語句，讓最佳化器可以以更優的方式執行查詢。

13. 最佳化長難的查詢語句？

  • 一個複雜查詢還是多個簡單查詢；
  • MySQL 內部每秒能掃描記憶體中上百萬行資料，相比之下，響應資料給客戶端就要慢得多；
  • 使用盡可能小的查詢是好的，但是有時將一個大的查詢分解為多個小的查詢是很有必要的；
  • 切分查詢，將一個大的查詢分為多個小的相同的查詢；
  • 一次性刪除 1000 萬的資料要比一次刪除 1 萬，暫停一會的方案更加損耗伺服器開銷；
  • 分解關聯查詢，讓快取的效率更高。執行單個查詢可以減少鎖的競爭；
  • 在應用層做關聯更容易對資料庫進行拆分。查詢效率會有大幅提升；
  • 較少冗餘記錄的查詢。

14. 最佳化特定型別的查詢語句？

  • COUNT(*) 會忽略所有的列，直接統計所有列數，不要使用 COUNT(列名)；
  • 在 MyISAM 中，沒有任何 WHERE 條件的 COUNT(*) 非常快。當有 WHERE 條件時，MyISAM 的 COUNT 統計不一定比其他引擎快；
  • 可以使用 EXPLAIN 查詢近似值，用近似值替代 COUNT(*)；
  • 增加彙總表；
  • 使用快取。

15. 最佳化關聯查詢？

  • 確定 ON 或 USING 子句中是否有索引；
  • 確保 GROUP BY 和 ORDER BY 只有一個表中的列，這樣 MySQL 才有可能使用索引。

16. 最佳化子查詢？

  • 用關聯查詢替代；
  • 最佳化 GROUP BY 和 DISTINCT：
    • 這兩種查詢可以使用索引來最佳化，是最有效的最佳化方法；
    • 關聯查詢中，使用標識列分組的效率更高；
    • 如果不需要 ORDER BY，進行 GROUP BY 時加 ORDER BY NULL，MySQL 不會再進行檔案排序；
    • WITH ROLLUP 超級聚合，可以挪到應用程式處理。

17. 最佳化 LIMIT 分頁？

  • LIMIT 偏移量大的時候，查詢效率較低；
  • 可以記錄上次查詢的最大 ID，下次查詢時直接根據該 ID 來查詢。

18. 最佳化 UNION 查詢？

  • UNION ALL 的效率高於 UNION。

19. 最佳化 WHERE 子句？

  • 確保 WHERE 子句中的條件能有效利用索引；
  • 避免在 WHERE 子句中進行計算或轉換操作，儘量將計算移到查詢外部處理。

20. 資料庫為什麼要最佳化？

  • 系統的吞吐量瓶頸往往出現在資料庫的訪問速度上
  • 隨著應用程式的執行，資料庫的中的資料會越來越多，處理時間會相應變慢
  • 資料是存放在磁碟上的，讀寫速度無法和記憶體相比
  • 最佳化原則：減少系統瓶頸，減少資源佔用，增加系統的反應速度。

相關文章