MySQL基礎概念知多少

  • 2019 年 11 月 6 日
  • 筆記

MySQL基礎概念相關的名詞還是挺多的，比如3大範式、4種隔離界別、ACID、DQL、DML、DDL，還有redo、undo、binlog等，本文就統一整理下MySQL常見的基礎概念，方便小夥伴們翻閱~

MySQL相關的名詞概念還是挺多的，但是常用的也不多，因此將常用的統計整理下，便於回顧：

•DQL：data query language，指SELECT查詢語句；•DML：data manipulation language，指SELECT、UPDATE、INSERT、DELETE這4種對數據操控語句；•DDL：data definition language，數據定義語句，創建/修改/刪除表結構，主要有CREATE、ALTER、DROP等；•MDL鎖：Metadata Locking，這裡的metalock指的是資料庫及表的結構資訊。MySQL中，DDL不屬於事務範疇，如果事務和DDL並行執行，操作相關聯的表的話，會出現各種意想不到問題，導致事務特性被破壞或者binlog順序錯亂[1]等，為解決這些問題而引入MDL鎖機制。

三大範式

•第一範式：每個欄位都是原子的，也就說不可再分解；•第二範式：有主鍵，非主鍵欄位依賴主鍵欄位；•第三範式：非主鍵之間不能相互依賴。

注意，三大範式是數據表的建議設計原則，並不是非得完全按照這個來設計，具體設計還要根據實際場景來分析。任何給定的數據通常有多種表示方法，完全的範式話和反範式化，以及二者的折中。在範式化資料庫中，任何數據都會出現且只出現一次，相反在反範式化中，數據是冗餘的。

ACID

ACID是事務的4個特性，分別是原子性、一致性、隔離性和持久性。

•A：atomicity，原子性，一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾；•C：consistency，一致性，資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態；•I：isolation，隔離性，通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的（隔離級別在非提交讀時不滿足）；•D：一旦事務提交，則其所做的修改就會永久保存到資料庫中。

隔離級別

資料庫事務的4種隔離級別：

•未提交讀：一個事務可以讀到另外一個事務未提交的數據。•提交度：一個事務更新數據過程中，如果事務還未提交，其他事務讀不到該數據。•可重複讀：該級別保證了在同一個事務中，多次讀取同樣記錄的結果是一樣的，解決了「提交讀」中不可重複讀的問題。但是理論上還是無法解決幻讀問題（通過間隙鎖可解決幻讀問題）。•串列化：將所有事務都進行串列化處理，等級最高的隔離級別。

幻讀問題

幻讀就是當事務在讀取某個範圍數據時，另一個事務又在該範圍插入了新的數據，當之前的事務再次讀取該範圍數據時，就會產生幻行。產生幻讀的原因是之前的事務在讀取數據的範圍沒有增加範圍鎖（range-locks），也就是讀取時只是鎖定的行級共享鎖，沒有鎖定整個查詢區間或者表。

常見索引結構

•B+樹索引：B+ 樹是關係型資料庫中常見的索引類型。注意：B+樹所以並不能找到一個給定鍵值的具體行，只能找到被查找數據行所在的頁，然後資料庫將頁讀入記憶體，在記憶體中進行查找，最後得到要查找的數據；•哈希索引：InnoDB支援的哈希索引是自適應的，不能人為干預在一張表中生成哈希索引，innodb會根據表的使用情況自動生成哈希索引；•全文索引：InnoDB支援全文索引，但是每張表只能有一個全文檢索的索引，一般都是使用倒排索引技術來實現。

聚集索引和非聚集索引

聚集索引就是主鍵索引，其葉子節點就是記錄的數據（頁）。非聚集索引也叫做輔助索引，其葉子結點記錄的是主鍵值。以表t為例說明如下：

create table T (  ID int primary key,  k int NOT NULL DEFAULT 0,  s varchar(16) NOT NULL DEFAULT '',  index k(k)) engine=InnoDB;    insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff');

表T對應的主鍵索引和輔助索引如下：

幾個日誌

•redo log：記錄的是頁的物理操作，InnoDB通過將事務操作先寫redo log，而不是將數據頁的更新寫磁碟，相當於將磁碟隨機寫(data文件)變成了順序寫(redo log)，後續在MySQL"空閑"時再慢慢寫磁碟，提高伺服器性能；•undo log：undo log保存了事務發生之前的數據的版本，可用於回滾，同時可提供多版本並發控制讀（MVCC），也就是非鎖定讀。undo log是邏輯日誌，在執行undo時，僅僅是將數據邏輯上恢復至事務之前的狀態，而不是從物理頁上操作的，這一條不同於redo log。事務開始時將當前版本生成undo log，undo也會產生redo來保證undo log可靠性；•binlog：binlog是mysql層面的歸檔日誌，可用於主從複製和資料庫基於時間點的還原。binlog記錄的是邏輯日誌，記錄的是DDL和DML操作日誌，可以簡單認為是執行過的事務中的更新sql語句。•慢查詢、錯誤日誌等。

幾個文件

•.ibd文件和.ibdata文件：.ibd文件和ibdata文件都是存放innodb數據的文件，之所有有2個，因為innodb支援配置來決定是使用共享表空間還是獨享表空間。獨享表空間使用".ibd"文件存儲數據，並且每個表有一個.ibd文件；如果使用共享表空間，則會使用ibdata文件，所有表公用一個（或者配置多個）ibdata文件。•.ifm文件：存放表相關的元數據資訊。

References

[1] binlog順序錯亂: https://bugs.mysql.com/bug.php?id=989

VirMach 便宜 VPS

QNews

QNews