MySQL基礎

原文地址🔗

drop、delete 與 truncate#

用法不同#

類型不同#

DML 語言：資料庫操作語言（Data Manipulation Language），資料庫增刪改查，針對表中資料
DDL 語言：資料定義語言（Data Definition Language），對資料庫內部物件創建、刪除、修改的語言，針對資料庫結構。

delete 為 DML 語言，執行之後 delete 操作會放到 rollback segment 中，事務提交之後生效，也可回滾。

drop 和 truncate 為 DDL 語言，執行之後立即生效，資料不能回滾。

執行速度不同#

drop > truncate > delete

utf8 和 utf8mb4#

utf8 ：
- utf8編碼只支持1-3個字節
- 中文佔 3 個字節，其他數字、英文、符號佔一個字節。
- emoji 符號佔 4 個字節，所以 utf8 不能存儲 emoji 符號
utf8mb4 ： UTF-8 的完整實現。最多支持使用 4 個字節表示字符。可以用來存儲 emoji 符號。

資料庫範式#

第一範式 1NF#

原子性：資料庫中屬性（資料表中的列）不可再分割。

第二範式 2NF#

在第一範式的基礎上，表中的每一列都應該與主鍵關聯。

如在同一張表中存儲學生基本資訊和學生成績，違背了第二範式。

每個表中的非主屬性完全依賴於碼

第三範式 3NF#

表中出非主屬性之間不能互相依賴

在第二範式的基礎上，消除非主屬性之間的依賴關係，只保留非主屬性與碼的依賴關係

MySQL 資料類型#

整型#

tinyint,smallint,middleint,int,bigint，分別佔用 8、16、24、32、64 存儲空間

浮點數#

float
double
decimal：高精度小數類型。

三種浮點數都可以指定列寬，例如使用decimal(18,9)表示總共 18 位，取 9 位保存小數部分，剩下的保存整數部分。

字串#

char：定長，會自動去除末尾的空格
varchar：變長，不會刪除末尾的空格
blog：使用二進制形式保存
text：使用字串形式保存

時間#

datatime：保存從 1001 年到 999 年的日期和時間，精度為秒，使用 8 字節存儲空間，沒有時區資訊。
timestamp：保存 1970-01-01 開始的時間。使用 4 字節保存，只能表示到 2038 年，有時區資訊。

MySQL 基礎架構#

MySQL 存儲引擎#

使用show engines命令查看 MySQL 的存儲引擎

常用的有 InnoDB 和 MyISAM 引擎

默認使用 InnoDB 引擎

MyISAM#

不支持事務
不支持行級鎖，只能對整張表加鎖。讀時加共享鎖，寫時加排他鎖。
資料丟失風險

InnoDB#

支持事務，實現了四個隔離級別，默認級別是可重複讀，通過 MVCC（多版本並發控制）+Next-KeyLocking（間隙鎖）防止幻讀
主索引是聚集索引

MyISAM 和 InnoDB#

鎖：MyISAM 使用表級鎖，InnoDB 使用行級鎖
事務：MyISAM 不支持事務，InnoDB 支持事務
外鍵：MyISAM 不支持外鍵，InnoDB 支持外鍵
崩潰恢復：MyISAM 不支持崩潰後安全恢復，InnoDB 支持崩潰後通過日誌安全恢復
索引實現方式不同：兩者都使用 B + 樹的資料結構。MyISAM 中索引文件和資料文件分離，InnoDB 資料文件同時是索引文件

MySQL 事務#

事務：邏輯上的一組操作，要麼都執行，要麼都不執行。

事務的特性#

原子性（Atomicity）：一個事務包含的所有操作，要麼全部成功，要麼全部失敗。
一致性（Consistency）：事務執行前後狀態一致。例如轉帳：轉帳完成錢的總數不變。
隔離性（Isolation）：是否讀未提交。
持久性（Durability）：事務提交後，資料庫的更改是永久的。

簡稱 ACID

事務引發的問題#

髒讀：未提交的事務讀到了另一個事務未提交的資料。
不可重複讀：一個運行較慢事務在事務讀取用一記錄，兩次讀到的資料不同，主要針對 update 和 delete。
幻讀：兩次讀取到資料條數不同，針對 insert。

事務隔離級別#

(READ-UNCOMMITTED) 讀未提交：最低級別，允許讀取尚未提交的資料變更，可能會導致髒讀，不可重複讀，幻讀。
(READ-COMMITTED) 讀已提交：允許讀取已提交的資料，只能防止髒讀。
(REPEATABLE-READ) 可重複讀：一個事務開始，事務過程中所讀取到的所有資料不允許被其他事物修改，不能防止幻讀。
(SERIALIZABLE) 串行化：最高級別，防止所有問題，所有事務以串行化方式逐個執行。

事務隔離級別實現方式#

基於鎖和 MVCC 機制實現。

串行化隔離級別通過鎖實現，其餘隔離級別基於 MVCC 實現。

默認隔離級別#

通過執行SELECT @@transaction_isolation;命令查看

默認隔離級別是可重複讀

MySQL 鎖#

表級鎖和行級鎖#

表級鎖：鎖定粒度最大的鎖，針對非索引字段加鎖，對整張表加鎖，實現簡單，資源消耗少，加鎖快，不會出現死鎖。
行級鎖：鎖定粒度最小的鎖，針對索引字段加鎖，對操作行加鎖。加鎖慢，可能出現死鎖。

行級鎖使用#

InnoDB 使用行級鎖針對索引字段加鎖。

當執行update，delete方法時，如果where條件字段沒有命中唯一索引，或者索引失效時，會導致掃描全表並對全表行加鎖。

常見鎖的類型#

共享鎖（S 鎖）：又稱讀鎖，事務在讀取資料（select）時取得共享鎖，允許多個事務同時獲取（鎖兼容）。
排他鎖（X 鎖）：又稱寫鎖、獨占鎖，事務在修改記錄（update）時獲得排他鎖，不允許多個事務同時獲取。如果一個記錄已經被加了排他鎖，則其他事務不能再加其它類型的鎖（鎖不兼容）。

默認不加鎖，可以顯示聲明使用鎖
```
SELECT ... LOCK IN SHARE MODE; # 共享鎖
SELECT ... FOR UPDATE; # 排他鎖
```
意向鎖：表級鎖，用於快速判斷表中有沒有行鎖。
- 意向共享鎖：如果事務有意向對表中資料加共享鎖，必須先加意向共享鎖。
- 意向排他鎖：如果事務有意向對表中資料加排他鎖，必須先加意向排他鎖。

InnoDB 中的鎖#

InnoDB 有三種行鎖：

記錄鎖（Record Lock）：單個行記錄上的鎖。
間隙鎖（Gap Lock）：鎖定一個範圍，不包含記錄本身。
臨鍵鎖（Next-Key Lock）：同時使用記錄鎖和臨鍵鎖。

當前讀和快照讀#

InnoDB 默認隔離級別（可重讀）可以解決幻讀問題，通過當前讀和快照讀。

當前讀（一致性鎖定讀）：使用臨鍵鎖（Next-Key Lock）進行加鎖保證不出現幻讀。
快照讀（一致性非鎖定讀）：有 MVCC 機制保障不出現幻讀。

兩者的區別

快照讀：即正常使用select時，使用快照讀。如果讀取到的資料正在執行update/delete操作，讀取操作不會等待記錄上的排他鎖釋放，而是會讀取行的上一个快照。

當前讀：當在事務隔離級別 RC（讀已提交）和 RR（可重讀）下，InnoDB 會使用鎖定讀。

在 RC 級別下：對於快照資料，當前讀會讀取被鎖定行的最新一份快照資料。
在 RR 級別下：對於快照資料，當前讀會讀取本事務開始時的行資料版本。

快照即記錄的歷史版本，每行記錄可能存在多個歷史版本。

MySQL 性能優化#

文件存儲#

不建議使用 MySQL 存儲二進制文件資料。

建議使用物件存儲保存文件，MySQL 保存文件地址。

IP 地址存儲#

可以將 IP 地址轉換成整型存儲。

INET_ATON() ：把 ip 轉為無符號整型 (4-8 位)
INET_NTOA() ：把整型的 ip 轉為地址

儘可能將列定義為 NOT NULL#

除非有特別的原因使用 NULL 值，應該總是讓字段保持 NOT NULL。

索引 NULL 列需要額外的空間來保存，所以要佔用更多的空間；
進行比較和計算時要對 NULL 值做特別的處理。

MySQL 索引#

什麼是索引#

索引是一種用於快速查找和檢索資料的資料結構，常見索引結構有：B 樹、B + 樹、Hash 表等。

索引的資料結構#

Hash 表#

哈希表是鍵值對的集合，通過 key 可以快速得出 value。O (1)。通過對 key 使用哈希算法計算索引，在通過索引找到 value。

缺點：

Hash 衝突
Hash 索引不支持順序和範圍查詢

B 樹#

二叉搜索樹，平衡二叉樹，紅黑樹🔗

B 樹推薦閱讀🔗

B 樹的詳細解釋以及插入刪除操作等相關內容上文有詳細說明。

個人對於 B 樹的理解：普通二叉樹的缺點，每個節點只能有兩個子節點，每個節點只能容納一個資料，這導致了兩個問題：1. 樹的高度很高；2. 邏輯上挨著的節點資料可能樹裡離得很遠，這對於內存中操作資料來說問題不大。

但是在資料庫裡，資料首先會存儲於磁碟上而不是內存上。過高的樹需要多次查找，取出結果，比較，再查找，比較，樹的高度越高，需要訪問磁碟的次數也越多。對於磁碟來說並不是很好的選擇。如果滿足 “局部性原理”，即邏輯上相鄰的資料，物理上盡量存儲在一起。這樣可以減少磁碟訪問次數。

B 樹解決了這一問題，B 樹的特點：1. 多路樹，不再局限於一個節點只能有兩個子節點；2. 所有子節點的高度相同。

B 樹每個節點能容納更多的資料，這樣降低了樹的高度，同時邏輯上相鄰的資料在物理上存儲在相鄰的磁碟空間中。在查詢時減少磁碟交互次數。

B 樹：

在上圖中，葉子節點有多個資料，查找是會將葉子節點上的資料一次讀入內存中，通過在內存中遍歷比較，找到需要的資料。

B 樹每個節點能保存 4K 的資料，由於磁碟存儲資料採用分塊的方式，而每個塊的大小為 4K。每次讀取資料都會讀取一個節點，4K 的資料到內存中。

B + 樹#

B + 樹子節點不保存資料，只保存關鍵字的索引，所有的資料都保存在葉子節點上。同時所有葉子節點上的資料構成了一個有序鏈表。

B + 樹和 B 樹比較#

B + 樹查詢速度更穩定。B + 樹所有資料都存放在葉子節點，而葉子節點高度相同，每次查找次數相同，所以查詢速度穩定。
B + 樹葉子節點有序。B + 樹所有葉子節點資料構成一個有序鏈表，查詢大小區間的資料時更快。
B + 樹全節點遍歷更快。B + 樹只需要遍歷葉子節點，B 樹需要遍歷全部節點。
B 樹如果訪問的資料離根節點近，此時查找速度快於 B + 樹。

MyISAM 和 InnoDB 索引#

兩者都是用 B + 樹作為索引結構

MyISAM 使用非聚簇索引：B + 樹節點 data 域中保存資料的地址，在查找時，先找到節點，再通過節點裡的地址找到資料。
InnoDB 使用聚簇索引：B + 樹節點 data 域中保存完整資料，索引的 key 是資料庫主鍵。

索引類型#

主鍵索引#

資料表的主鍵列使用的是主鍵索引。

如果一張表沒有設置主鍵，InnoDB 會先查找表中是否有唯一且不允許為空的字段，如果有則使用該字段為主鍵，如果沒有 InnoDB 會自己創建一個 6Byte 的自增主鍵。

二級索引（輔助索引）#

二級索引的葉子節點保存的資料是主鍵，通過二級索引可以找到主鍵。

二級索引包含唯一索引、普通索引、前綴索引、全文索引

唯一索引（UNIQUE）：資料表中設置了唯一的字段，不允許重複，允許為 null
普通索引（INDEX）：資料表中手動設置的索引字段，可以重複，可以為 null
前綴索引：對於字串類型的資料，截取字串前幾個字符創建索引。
全文索引（FULLTEXT）：檢索大文本資料中的關鍵字資訊。

聚集索引和非聚集索引#

聚集索引#

聚集索引即索引結構和資料存放在一次的索引。主鍵索引就是聚集索引。特點：查詢快，修改慢。

優點：查詢速度快，因為葉子節點存放資料並且資料有序，所以查到葉子節點就是查到資料
缺點：
- 依賴於有序的資料：查詢依賴於資料的物理存儲地址按照索引的順序排列。
- 更新代價大：由於資料的物理存儲地址按照索引的順序排列，所以每次插入刪除都需要移動資料，使資料在物理上有序，連續。

非聚集索引#

索引結構和資料分開存放。二級索引是非聚集索引。二級索引的葉子節點就存放的是主鍵，根據主鍵再回表查資料。

優點：更新快，葉子節點不存放資料。
缺點：
- 依賴於有序的資料。
- 可能需要回表二次查詢。

覆蓋索引#

覆蓋索引即需要查詢的字段正好是索引的字段，那麼直接根據該索引，就可以查到資料了，而無需回表查詢。

聯合索引#

使用表中多個字段創建索引

最左前綴匹配原則#

使用聯合索引是，根據聯合索引中字段的順序，從左到右依次匹配查詢條件中與聯合索引中最左字段匹配的字段。

索引字段創建原則#

需要頻繁查詢、作查詢條件、關聯查詢的字段
不為 null 的字段
需要頻繁排序的字段
不會頻繁更新的字段
儘量使用聯合索引而不是單列索引

MySQL 日誌#

MySQL 日誌主要由二進制日誌（binlog，歸檔日誌），事務日誌（redo log）、回滾日誌（undo log）組成

redo log#

Redo log 是 InnoDB 所使用的日誌系統，記錄在每個資料頁上做的修改，通過 redo log 可以用於從錯誤中恢復資料。保證資料的持久性和完整性。如果 MySQL 服務異常停機，重啟 MySQL 之後會通過 redo log 恢復資料。

MySQL 資料查詢時，從磁碟讀取資料以頁為單位，查詢時會一次性加載一頁資料（16kb）Buffer Pool中。後續查詢會優先在Buffer Pool中查找。在執行更新操作時，也是有限更新Buffer Pool中的資料，同時記錄重做日誌緩存（redo log buffer），之後在適當的時機，將緩存刷盤到 redo log 文件中。

刷盤時機#

刷盤：將資料從內存寫入磁碟

使用innodb_flush_log_at_trx_commit參數可以控制刷盤時機：
- 0：每次事務提交時不進行刷盤操作（資料丟失風險）
- 1：每次事務提交都進行刷盤（默認值）（不會有資料丟失，因為丟失的都是未提交的事務）
- 2：每次事務提交都把redo log buffer內容寫入系統緩存page cache
  在默認情況下，每當事務提交時都會調用fsync對 redo log 進行刷盤
同時，InnoDB 每隔 1 s，會自動將redo log buffer內容寫入系統緩存page cache，之後調用fsync刷盤。
當redo log buffer佔用的空間超過innodb_log_buffer_size一半時，觸發刷盤。

日誌文件組#

多個文件存儲 redo log 日誌，一個文件寫滿寫寫一個，最後一個寫滿再回頭寫第一個。

binlog#

binlog 記錄資料庫操作邏輯，記錄每次資料表更新。

MySQL 主從同步時，會使用 binlog 來同步資料。

記錄格式#

通過binlog_format參數指定記錄格式

statement：記錄 SQL 語句原文，但是在執行update_time=now()會導致時間不同。
row：記錄執行 SQL 的參數，記錄具體時間
mixed：自動判斷該 SQL 是否會有資料不一致問題，如果有使用 row，沒有使用 statement 記錄

寫入機制#

事務執行過程中，先將日誌寫到binlog cache，事務提交之後，再將binlog cache寫入 binlog。

兩階段提交#

通過 redo log 和 binlog 互相配合來儘量保證資料恢復能力，和資料不丟失。

日誌記錄順序。

執行更新操作時先寫入 redo log，並將狀態設置為 prepare，事務提交後，將更新的操作寫入 binlog，在將 redo log 設置為 commit 階段。

如果在 redo log 狀態為 prepare 階段時，MySQL 服務異常，在恢復資料時由於事務沒有提交，沒有 binlog，MySQL 會選擇回滾這一事務。

如果在事務提交，並且有 binlog 時，MySQL 會選擇通過 binlog 恢復資料，並且將 redo log 狀態設置為 commit 階段。

undo log#

undo log 保證事務的原子性，用於在事務異常時回滾操作。

所有的事務操作都會先記錄回滾日誌，在實際執行操作。同時，回滾日誌先於資料持久化到磁碟上。

三種日誌的作用#

使用 redo log 保證事務的持久性
使用 undo log 保證事務的原子性
使用 redo log 和 undo log 保證資料不丟失
使用 binlog 完整資料備份，主從，保證資料的一致性

MVCC#

Multi-Version Concurrency Control，多版本並發控制。是一種並發控制的方法，在資料庫管理系統中，實現對資料庫的並發訪問。

實現原理#

隱式字段#

DB_ROW_ID：隱式自增主鍵，如果資料表沒有主鍵，InnoDB 自動以 DB_ROW_ID 等字段生成聚集索引
DB_TRX_ID：最近插入 / 修改的事務 ID，記錄當前記錄最後一次修改該記錄的事務 ID
DB_ROLL_PTR：回滾指針，指向這條記錄的上一个版本
DRFAULT_BIT：刪除 flag

undo log#

ReadView#

事務在進行快照操作時生成的讀視圖，在該事務執行快照的時候，會生成資料庫系統當前的快照，記錄並維護系統當前活躍事務的 ID

ReadView 的三個屬性

trx_list：未提交事務 ID 列表
up_limit_id：記錄 trx_list 列表中最小的事務 ID
low_limit_id：下一个事務 ID，就是當前最大 ID + 1

SQL 執行流程#

連接器：資料庫連接，身份認證，權限校驗
分析器：詞法分析（提取關鍵詞，select、表名、查詢條件等）、語法分析（檢查 SQL 是否符合 MySQL 規範）
優化器：使用（IO 成本 + CPU 成本）最小的索引來執行查詢
執行器：調用存儲引擎接口執行 SQL
- 查詢 BufferPool 中是否有需要查詢的資料，如果沒有去磁碟查詢，之後將資料放入 BufferPool
- 如果執行的是 update，記錄 undo log，在 BufferPool 中更新資料。（BufferPool 中的資料又可能和資料庫中的資料不一致）
- BufferPool 中的資料已更新，記錄 redo log（先寫入到 redo log buffer 中，再在合適時間寫入磁碟中。）
- 記錄 bin log
- 將 bin log 文件名和當前語句更新內容記錄到 redo log
- redo log 後添加 commit（兩階段提交）

索引失效#

操作符（ = ）左右兩邊的資料類型不同时，會發生隱式轉換。

失效的情況#

當 where 左邊為數值類型時會發生隱式轉換，索引不會失效，對效率影響不大。
當 where 左邊為字串類型時會發生隱式轉換，索引會失效，查詢變成全表掃描，效率低。

字串 => 數值轉換方式：#

所有不以數字開頭的字串都轉換為 0。（例如abc，abc123 => 0）
以數字開頭的字串只截取讀一個數字段。（例如123abc4 => 123，012ab4 => 12）

失效案例#

索引失效案例：num2 為varchar類型

SELECT * FROM `test1` WHERE num2 = 10000;

執行這條 SQL 時索引失效，發生全表掃描，因為'10000a'，'010000'，'10000'等等都能轉為浮點數10000，這樣的情況，是不能用到索引的

索引失效#

上述例子中，由於類型轉換導致索引失效
使用！= > < 導致索引失效
模糊查詢，如 % S%，會導致索引失效，S% 不會導致索引失效

MySQL 中語句執行優先級#

單表#

from > where > group by > select > order by > limit

多表#

from > join > on > where > group by > AVG,SUM > having > select > distinct > order by > limit

慢查詢優化#

使用 EXPLAIN 關鍵字分析查詢語句

查詢使用索引#

使用 LIKE 模糊查詢時，第一个是 % 查詢不走索引

優化資料庫結構#

將字段較多的表拆分，將不經常查詢的資料新建表

相關概念#

drop、delete 與 truncate#

用法不同#

類型不同#

執行速度不同#

utf8 和 utf8mb4#

資料庫範式#

第一範式 1NF#

第二範式 2NF#

第三範式 3NF#

MySQL 資料類型#

整型#

浮點數#

字串#

時間#

MySQL 基礎架構#

MySQL 存儲引擎#

MyISAM#

InnoDB#

MyISAM 和 InnoDB#

MySQL 事務#

事務的特性#

事務引發的問題#

事務隔離級別#

事務隔離級別實現方式#

默認隔離級別#

MySQL 鎖#

表級鎖和行級鎖#

行級鎖使用#

常見鎖的類型#

InnoDB 中的鎖#

當前讀和快照讀#

MySQL 性能優化#

文件存儲#

IP 地址存儲#

儘可能將列定義為 NOT NULL#

MySQL 索引#

什麼是索引#

索引的資料結構#

Hash 表#

B 樹#

B + 樹#

B + 樹和 B 樹比較#

MyISAM 和 InnoDB 索引#

索引類型#

主鍵索引#

二級索引（輔助索引）#

聚集索引和非聚集索引#

聚集索引#

非聚集索引#

覆蓋索引#

聯合索引#

最左前綴匹配原則#

索引字段創建原則#

MySQL 日誌#

redo log#

刷盤時機#

日誌文件組#

binlog#

記錄格式#

寫入機制#

兩階段提交#

undo log#

三種日誌的作用#

MVCC#

MVCC#

實現原理#

隱式字段#

undo log#

ReadView#

SQL 執行流程#

索引失效#

失效的情況#

字串 => 數值轉換方式：#

失效案例#

索引失效#

MySQL 中語句執行優先級#

單表#

多表#