一篇文章解決你大部分資料庫所遇到的問題

java閘瓦發表於2019-04-10
資料庫

寫在前面

本文對面試/筆試過程中經常會被問到的一些關於資料庫(MySQL)的問題進行了梳理和總結，包括資料庫索引、資料庫鎖、資料庫事務和MySQL優化等基礎知識點

一篇文章解決你大部分資料庫所遇到的問題

資料庫正規化

  • 第一正規化：列不可分，eg:【聯絡人】（姓名，性別，電話），一個聯絡人有家庭電話和公司電話，那麼這種表結構設計就沒有達到 1NF；
  • 第二正規化：有主鍵，保證完全依賴。eg:訂單明細表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（數量）完全依賴（取決）於主鍵（OderID，ProductID），而 UnitPrice，ProductName 只依賴於 ProductID，不符合2NF；
  • 第三正規化：無傳遞依賴(非主鍵列 A 依賴於非主鍵列 B，非主鍵列 B 依賴於主鍵的情況)，eg:訂單表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主鍵是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依賴的是 CustomerID（非主鍵列），而不是直接依賴於主鍵，它是通過傳遞才依賴於主鍵，所以不符合 3NF。

資料庫索引

索引是對資料庫表中一個或多個列的值進行排序的資料結構，以協助快速查詢、更新資料庫表中資料

一篇文章解決你大部分資料庫所遇到的問題

索引的底層實現原理和優化

在資料結構中，我們最為常見的搜尋結構就是二叉搜尋樹和AVL樹(高度平衡的二叉搜尋樹，為了提高二叉搜尋樹的效率，減少樹的平均搜尋長度)了。然而，無論二叉搜尋樹還是AVL樹，當資料量比較大時，都會由於樹的深度過大而造成I/O讀寫過於頻繁，進而導致查詢效率低下，因此對於索引而言，多叉樹結構成為不二選擇。特別地，B-Tree的各種操作能使B樹保持較低的高度，從而保證高效的查詢效率。

B-Tree(平衡多路查詢樹)

  • 樹中每個結點最多有m個孩子結點；
  • 若根結點不是葉子節點，則根結點至少有2個孩子結點；
  • 除根結點外，其它結點至少有(m/2的上界)個孩子結點； *所有的葉結點都在同一層上，並且不帶資訊（可以看作是外部結點或查詢失敗的結點，實際上這些結點不存在，指向這些結點的指標為空）。

B+Tree ： InnoDB儲存引擎的索引實現

  • n 棵子樹的結點中含有n個關鍵碼；
  • 所有的葉子結點中包含了全部關鍵碼的資訊，及指向含有這些關鍵碼記錄的指標，且葉子結點本身依關鍵碼的大小自小而大的順序連結；
  • 非終端結點可以看成是索引部分，結點中僅含有其子樹根結點中最大（或最小）關鍵碼。

為什麼說B+-tree比B 樹更適合實際應用中作業系統的檔案索引和資料庫索引？

  • B+tree的磁碟讀寫代價更低：B+tree的內部結點並沒有指向關鍵字具體資訊的指標(紅色部分)，因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入記憶體中的需要查詢的關鍵字也就越多，相對來說IO讀寫次數也就降低了；
  • B+tree的查詢效率更加穩定：由於內部結點並不是最終指向檔案內容的結點，而只是葉子結點中關鍵字的索引，所以，任何關鍵字的查詢必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當；
  • 資料庫索引採用B+樹而不是B樹的主要原因：B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷，而且在資料庫中基於範圍的查詢是非常頻繁的，而B樹只能中序遍歷所有節點，效率太低。

檔案索引和資料庫索引為什麼使用B+樹?

  • 檔案與資料庫都是需要較大的儲存，也就是說，它們都不可能全部儲存在記憶體中，故需要儲存到磁碟上。而所謂索引，則為了資料的快速定位與查詢，那麼索引的結構組織要儘量減少查詢過程中磁碟I/O的存取次數，因此B+樹相比B樹更為合適
  • B+樹還有一個最大的好處：方便掃庫。B樹必須用中序遍歷的方法按序掃庫，而B+樹直接從葉子結點挨個掃一遍就完了，B+樹支援range-query非常方便，而B樹不支援，這是資料庫選用B+樹的最主要原因

索引的優點

  • 大大加快資料的檢索速度，這也是建立索引的最主要的原因；
  • 加速表和表之間的連線；
  • 在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間； *通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性；

什麼情況下設定了索引但無法使用？

  • 以“%(表示任意0個或多個字元)”開頭的LIKE語句，模糊匹配；
  • OR語句前後沒有同時使用索引；
  • 資料型別出現隱式轉化（如varchar不加單引號的話可能會自動轉換為int型）；
  • 對於多列索引，必須滿足 最左匹配原則 (eg：多列索引col1、col2和col3，則 索引生效的情形包括 *col1或col1，col2或col1，col2，col3)。

什麼樣的欄位適合建立索引？

  • 經常作查詢選擇的欄位
  • 經常作表連線的欄位
  • 經常出現在order by, group by, distinct 後面的欄位

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

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

索引的分類

  • 普通索引和唯一性索引：索引列的值的唯一性
  • 單個索引和複合索引：索引列所包含的列數
  • 聚簇索引與非聚簇索引：聚簇索引按照資料的物理儲存進行劃分的。對於一堆記錄來說，使用聚集索引就是對這堆記錄進行堆劃分，即主要描述的是物理上的儲存。正是因為這種劃分方法，導致聚簇索引必須是唯一的。聚集索引可以幫助把很大的範圍，迅速減小範圍。但是查詢該記錄，就要從這個小範圍中Scan了；而非聚集索引是把一個很大的範圍，轉換成一個小的地圖，然後你需要在這個小地圖中找你要尋找的資訊的位置，最後通過這個位置，再去找你所需要的記錄。

主鍵、自增主鍵、主鍵索引與唯一索引概念區別

  • 主鍵：指欄位 唯一、不為空值 的列；
  • 主鍵索引：指的就是主鍵，主鍵是索引的一種，是唯一索引的特殊型別。建立主鍵的時候，資料庫預設會為主鍵建立一個唯一索引；
  • 自增主鍵：欄位型別為數字、自增、並且是主鍵；
  • 唯一索引：索引列的值必須唯一，但允許有空值。主鍵是唯一索引，這樣說沒錯；但反過來說，唯一索引也是主鍵就錯誤了，因為* 唯一索引允許空值，主鍵不允許有空值，所以不能說唯一索引也是主鍵。

主鍵就是聚集索引嗎？主鍵和索引有什麼區別

主鍵是一種特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引

資料庫事務

事務的特徵

  • 原子性(Atomicity)：事務所包含的一系列資料庫操作要麼全部成功執行，要麼全部回滾；
  • 一致性(Consistency)：事務的執行結果必須使資料庫從一個一致性狀態到另一個一致性狀態；
  • 隔離性(Isolation)：併發執行的事務之間不能相互影響； *永續性(Durability)：事務一旦提交，對資料庫中資料的改變是永久性的。

事務併發帶來的問題

  • 髒讀：一個事務讀取了另一個事務未提交的資料；
  • 不可重複讀：不可重複讀的重點是修改，同樣條件下兩次讀取結果不同，也就是說，被讀取的資料可以被其它事務修改；
  • 幻讀：幻讀的重點在於新增或者刪除，同樣條件下兩次讀出來的記錄數不一樣。

隔離級別

  • READ UNCOMMITTED：最低階別的隔離，通常又稱為dirty read，它允許一個事務讀取另一個事務還沒commit的資料，這樣可能會提高效能，但是會導致髒讀問題；
  • READ COMMITTED：在一個事務中只允許對其它事務已經commit的記錄可見，該隔離級別不能避免不可重複讀問題；
  • REPEATABLE READ：在一個事務開始後，其他事務對資料庫的修改在本事務中不可見，直到本事務commit或rollback。但是，其他事務的insert/delete操作對該事務是可見的，也就是說，該隔離級別並不能避免幻讀問題。在一個事務中重複select的結果一樣，除非本事務中update資料庫。
  • SERIALIZABLE：最高階別的隔離，只允許事務序列執行。

MySQL預設的隔離級別是REPEATABLE READ。

mysql的事務支援

  • MyISAM：不支援事務，用於只讀程式提高效能；
  • InnoDB：支援ACID事務、行級鎖、併發；
  • Berkeley DB：支援事務。

索引優化

  • 以“%(表示任意0個或多個字元)”開頭的LIKE語句，模糊匹配；
  • OR語句前後沒有同時使用索引；
  • 資料型別出現隱式轉化（如varchar不加單引號的話可能會自動轉換為int型）；
  • 對於多列索引，必須滿足最左匹配原則(eg,多列索引col1、col2和col3，則 索引生效的情形包括col1或col1，col2或col1，col2，col3)。

MySQL儲存引擎中的MyISAM和InnoDB區別詳解

  • 儲存結構：每個MyISAM在磁碟上儲存成三個檔案：第一個檔案的名字以表的名字開始，副檔名指出檔案型別。.frm檔案儲存表定義，資料檔案的副檔名為.MYD (MYData)，索引檔案的副檔名是.MYI (MYIndex)。InnoDB所有的表都儲存在同一個資料檔案中（也可能是多個檔案，或者是獨立的表空間檔案），InnoDB表的大小隻受限於作業系統檔案的大小，一般為2GB。
  • 儲存空間：MyISAM可被壓縮，佔據的儲存空間較小，支援靜態表、動態表、壓縮表三種不同的儲存格式。InnoDB需要更多的記憶體和儲存，它會在主記憶體中建立其專用的緩衝池用於高速緩衝資料和索引。
  • 可移植性、備份及恢復：MyISAM的資料是以檔案的形式儲存，所以在跨平臺的資料轉移中會很方便，同時在備份和恢復時也可單獨針對某個表進行操作。InnoDB免費的方案可以是拷貝資料檔案、備份 binlog，或者用 mysqldump，在資料量達到幾十G的時候就相對痛苦了。
  • 事務支援：MyISAM強調的是效能，每次查詢具有原子性，其執行數度比InnoDB型別更快，但是不提供事務支援。InnoDB提供事務、外來鍵等高階資料庫功能，具有事務提交、回滾和崩潰修復能力。
  • AUTO_INCREMENT：在MyISAM中，可以和其他欄位一起建立聯合索引。引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，它可以根據前面幾列進行排序後遞增。InnoDB中必須包含只有該欄位的索引，並且引擎的自動增長列必須是索引，如果是組合索引也必須是組合索引的第一列。
  • 表鎖差異：MyISAM只支援表級鎖，使用者在操作MyISAM表時，select、update、delete和insert語句都會給表自動加鎖，如果加鎖以後的表滿足insert併發的情況下，可以在表的尾部插入新的資料。InnoDB支援事務和行級鎖。行鎖大幅度提高了多使用者併發操作的新能，但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。
  • 全文索引：MyISAM支援 FULLTEXT型別的全文索引；InnoDB不支援FULLTEXT型別的全文索引，但是innodb可以使用sphinx外掛支援全文索引，並且效果更好。
  • 表主鍵：MyISAM允許沒有任何索引和主鍵的表存在，索引都是儲存行的地址。對於InnoDB，如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6位元組的主鍵(使用者不可見)，資料是主索引的一部分，附加索引儲存的是主索引的值。
  • 表的具體行數：MyISAM儲存表的總行數，select count() from table;會直接取出出該值；而InnoDB沒有儲存表的總行數，如果使用select count() from table；就會遍歷整個表，消耗相當大，但是在加了wehre條件後，myisam和innodb處理的方式都一樣。
  • CURD操作：在MyISAM中，如果執行大量的SELECT，MyISAM是更好的選擇。對於InnoDB，如果你的資料執行大量的INSERT或UPDATE，出於效能方面的考慮，應該使用InnoDB表。DELETE從效能上InnoDB更優，但DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除，在innodb上如果要清空儲存有大量資料的表，最好使用truncate table這個命令。
  • 外來鍵：MyISAM不支援外來鍵，而InnoDB支援外來鍵。

相關文章