2021秋招面試計算機基礎總結 - 資料庫，Redis

Borris發表於2020-09-24
面試計算機資料庫Redis
系列文章：
2021秋招面試計算機基礎總結 - 演算法，資料結構，設計模式，Linux 2021秋招面試計算機基礎總結 - Java基礎， JVM， Spring框架相關 2021秋招面試計算機基礎總結 - 資料庫，Redis 2021秋招面試計算機基礎總結 - 作業系統 2021秋招面試計算機基礎總結 - 計算機網路基礎 個人專案相關問題

事務

ACID

  • 原子性
    一個事務是無法分割的，也就是說一個事務中的所有操作要麼同時成功，要麼同時失敗。
  • 一致性
    一個事務前後的資料完整性要一致。（銀行轉賬案例，A給B轉賬，B增加金額，A必須減少相應金額）
  • 永續性
    事務一旦提交，對資料的改變就是永久性的。即使資料庫故障也不會對其有任何影響。
  • 隔離性
    多個事務併發執行時，別的事務中對資料的修改在提交前對當前事務中資料是沒有影響的。別的事務提交後根據不同的隔離級別可能會對當前事務造成影響。

隔離級別

從低至高：

  • 未提交讀 問題：髒讀、不可重複讀、幻讀
  • 已提交讀 問題：不可重複讀、幻讀
  • 可重複讀 問題：幻讀
  • 可序列化 解決所有問題

設定：SET GLOBAL transaction_isolation = '隔離級別';

髒讀、不可重複度、幻讀

  • 髒讀：事務A讀到了事務B未提交的資料，事務B發生回滾，事務A就讀到了髒資料
  • 不可重複讀：在事務A多次讀取一組資料的過程中，事務B對該組資料進行了修改並提交，那麼事務A會讀到不一樣的數值。（針對update操作，解決：使用行級鎖，不允許別的事務對這行資料修改）
  • 幻讀：事務A多次讀取資料總量過程中，事務B新增或刪除了資料並提交，導致事務A前後讀取到的資料總量不一致。（針對insert, delete，解決：使用表級鎖，事務A結束前不允許別的事務對該表進行修改）

三大正規化

  • 1NF 強調列的原子性，即一列不能夠被拆分成多個列
  • 2NF 基於1NF，還要滿足一個表必須有一個主鍵，沒有包含在主鍵中的列必須完全依賴於主鍵
  • 3NF 基於1、2NF，非主鍵列必須直接依賴主鍵，不能存在依賴傳遞，就是說：不能存在非主鍵列依賴於非主鍵列B，非主鍵列B依賴於主鍵。

可能舉個具體的例子有利於完全理解三大正規化，稍後更新。

SQL分類

  • DQL：資料查詢語言，SELECT,WHERE,FROM
  • DML：資料操作語言，INSERT,UPDATE,DELETE
  • DCL：資料控制語言，REVOKE,GRANT,設定使用者組訪問許可權
  • DDL：資料定義語言，CREATE,DROP新建刪除表

索引

型別（主要的）

  • 全文索引
    查詢的是文字中的關鍵詞，而不是直接比較索引中的值，為了解決一些模糊查詢效率較低的問題。
  • 雜湊索引
    基於雜湊表實現，只有精確匹配索引所有列的查詢才有效。對於每一行資料，儲存引擎都會對所有的索引列計算一個雜湊碼，並且不同鍵值的行計算出的雜湊碼也不一樣。雜湊索引將所有的雜湊碼儲存在索引中，同時在雜湊表中儲存指向每個資料行的指標。
  • B-TREE索引
    是將索引使用B-TREE的形式建立起來。InnoDB引擎使用的是B+樹，類似於二叉查詢樹。
    根節點的槽中存放了指向子節點的指標，儲存引擎根據這些指標向下層查詢。通過比較節點頁的值和要查詢的值可以找到合適的指標進入下層子節點，這些指標實際上定義了子節點頁中值的上限和下限。最終葉子節點的指標指向的是被索引的資料。
    B+樹索引所有的使用者資料儲存在葉子節點，要通過上層節點的目錄項，從根節點層層查詢，找到對應的資料。
  • 覆蓋索引
    一個索引包含或覆蓋了所有需要查詢的欄位的值，不再需要根據索引回表查詢資料。覆蓋索引必須要儲存索引列的值，因此 MySQL 只能使用 B-Tree 索引做覆蓋索引。

種類

  • 普通索引：使用KEY或INDEX關鍵字建立
  • 唯一索引：使用UNIQUE，索引列的值必須唯一，組合索引的組合必須唯一，允許空值
  • 主鍵索引：PRIMARY，特殊的唯一索引，不允許有空值，InnoDB會自動為主鍵建立聚簇索引
  • 組合索引：基於多個欄位建立的索引，查詢時必須遵循最左字首原則（腦子裡詳細過一下）。
  • 全文索引：FULL TEXT，用來查詢文字中關鍵字，為了解決一些模糊查詢效率較低的問題

聚簇索引和非聚簇索引

  • 聚簇索引：將資料儲存與索引放到了一塊，索引結構的葉子節點儲存了行資料
  • 非聚簇索引：將資料與索引分開儲存，索引結構的葉子節點指向了資料對應的位置

InnonDB的B+樹索引查詢如何實現？

  • B+樹每層中頁面之間由雙向連結串列連線，頁面中的記錄項由單向連結串列連線，記錄項根據索引列值從小到大排列

  • 索引建立的過程

    • 一個表建立一個 B+ 樹索引時，會建立一個根節點頁面
      • 這個根節點是不會再移動的，InnoDB 用到該索引時會從資料字典中取出這個索引根節點頁面號，進行訪問。
    • 每當表中有使用者記錄插入，都會把使用者記錄儲存到根節點
    • 當根節點可用空間用完的時候（InnoDB 每頁大小 16KB），根節點的所有使用者記錄都複製到一個新分配的頁面，通過頁分裂，得到兩個頁，使用者記錄根據索引值從小到大分配到兩個新頁，成為葉子節點，根節點成為儲存目錄項記錄的頁。
    • 目錄項記錄中記錄兩個頁中的最小索引值和頁號，並將記錄根據索引值從小到大排列。
    • 當葉子節點全部存滿之後，再進行上一步的分裂操作，始終保證葉子節點儲存使用者資料，而上級頁面成為目錄項記錄頁。不斷重複這個過程，形成一個多級目錄。

  • 根據主鍵索引查詢

    • 獲取到要查詢的主鍵值之後，訪問主鍵根節點所在頁面
    • 由於根節點中目錄項記錄是根據主鍵索引值從小到大排列，我們可以用二分查詢找到主鍵大概所在的目錄項記錄頁
    • 再到下一層的這個目錄項中繼續二分查詢，找到下層的目錄項記錄頁
    • 直到找到葉子節點的目錄項，在目錄項中查詢到具體的主鍵值

  • 根據自己建立的二級索引查詢

    • 由二級索引建立的B+樹，葉子節點儲存的是二級索引值+主鍵值
    • 根據索引值在由這個索引建立的B+樹中找到所有匹配的二級索引值，再根據它們對應的主鍵值一一回表查詢，得到最終要查詢的完整結果。

回表的代價

  • 二級索引得到的結果過多，回表次數會過多，造成使用這個二級索引查詢的效能低下。
    • 原因：訪問二級索引使用的是順序I/O，因為資料依據大小順序是存放在連續頁中，用連結串列連線的。取出二級索引查詢結果後，去主鍵索引中查詢，並不是按照主鍵大小依次查詢的，所以是隨機I/O，會訪問較多資料頁，造成效能低下。

為什麼主鍵索引是整型且自增的？

B+樹索引的適用條件

  • 全值匹配
  • 最左列匹配
  • 列字首匹配
  • 範圍值匹配
  • orderBy排序，且desc,asc不混用
  • groupBy

索引的選擇

  • 只為用於搜尋、分組、排序的列建立索引
  • 列的基數儘量大（重複的值少，查出的結果就少，回表次數少）
  • 索引列資料型別儘量小（MEDIUMINT，INT，BIGINT）
  • 為字串字首建立索引（只取字串前幾位，節約B+樹空間，節省查詢比較的時間）

InnoDB 和 MyISAM 引擎的區別

兩者的索引結構都是B+樹！

InnoDB MyISAM
支援事務、外來鍵 不支援事務、外來鍵
必須有主鍵，並作為聚集索引 沒有聚集索引，所有索引都是二級索引，資料和索引分開存放，索引儲存的是資料檔案的指標
不儲存具體行數，要全表掃描得到具體行數 儲存行數資訊
支援表級鎖和行級鎖 只支援表級鎖

連線

內連線: 只連線匹配的行
左外連線: 包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行
右外連線: 包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行

優化查詢

  • 避免全表掃描Z 考慮在常用的列，比如 WHERE 和 ORDER BY 涉及的列上建立索引，IN 和 NOT IN 也要慎用，儘量用 BETWEEN 取代。
  • 優化 COUNT 查詢 COUNT 可以統計列的數量，統計列值時要求列非空；COUNT 還可以統計行數，當 MySQL 確定括號內的表示式不可能為 NULL 時，實際上就是在統計行數。當使用 COUNT(*) 時，會忽略所有列而直接統計行數。某些業務場景不要求完全精確的 COUNT 值，此時可以使用近似值來代替，EXPLAIN 估算的行數就是一個不錯的近似值。
  • 避免子查詢
    在 MySQL5.5 及以下版本避免子查詢，因為執行器會先執行外部的 SQL 再執行內部的 SQL，可以用關聯查詢代替。
  • 禁止排序
    當查詢使用 GROUP BY 時，結果集預設會按照分組的欄位進行排序，如果不關心順序，可以使用 ORDER BY NULL 禁止排序。
  • 優化分頁
    在偏移量非常大的時候，需要查詢很多條資料再捨棄，代價非常高。最簡單的優化是儘可能地使用覆蓋索引掃描，而不是查詢所有的列，然後做關聯操作再返回所需的列。還有一種方法是從上一次取資料的位置開始掃描，避免使用 OFFSET。
  • 優化 UNION
    MySQL 通過建立並填充臨時表的方式來執行 UNION 查詢，除非確實需要消除重複的行，否則使用 UNION ALL，如果沒有 ALL 關鍵字，MySQL 會給臨時表加上 DISTINCT 選項，對整個臨時表的資料做唯一性檢查，代價非常高。
    UNION去重且排序 UNION ALL不去重不排序。
  • 使用使用者自定義變數
    使用者自定義變數是一個用來儲存內容的臨時容器，在連線 MySQL 的整個過程中都存在，可以在任何可以使用表示式的地方使用自定義變數，避免重複查詢剛剛更新過的資料。

MyBatis中 ${} 和 #{}

  • 使用${}方式傳入的引數，mybatis不會對它進行特殊處理，而使用#{}傳進來的引數，mybatis預設會將其當成字串。
  • #和$在預編譯處理中是不一樣的。#類似jdbc中的PreparedStatement，對於傳入的引數，在預處理階段會使用?代替，可以有效防止sql注入
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章