mysql架構以及innodb架構
Mysql架構分為Server層和引擎層
Server層
包括 連接器 查詢快取 分析器 優化器 執行器 以及一個binlog日志模塊(用于主從同步)
- 查詢陳述句: 分析=>優化=>執行(權限校驗)=>引擎
- 更新陳述句: 分析=>優化=>執行=>引擎=>記錄redo log(prepare 狀態)=>binlog=>redo log(commit 狀態)
引擎層(Innodb)
引擎層=用于快取的記憶體池 + 后臺執行緒(主要用于刷資料)
記憶體池 有用于資料緩沖的緩沖池( 資料頁 索引頁 插入緩沖 ,...), 還有 redo log 緩沖
后臺執行緒包括(主執行緒 負責定時刷資料; 讀寫執行緒, 回收執行緒 purge thread=>事務提交后回收undo頁), 臟頁清除執行緒Page Cleaner Thread)
不同log檔案的區別
redo log：為了持久化資料，當記憶體中的資料還沒寫入到磁盤而宕機時，會讀取該日志持久化資料到磁盤 (為了恢復已快取但是未持久化的資料=>引擎自帶)
在事務執行程序中一邊更新緩沖池中的頁面 一遍寫入redo log buffer.
持久化的時間=> 1. 引擎的后臺執行緒的主執行緒會每一秒持久化 2.innodb_flush_log_at_trx_commit引數 0:事務提交不寫入 1: 事務提交寫入 2:事務提交寫入 page cache(檔案系統提供)
undo log：事務執行程序中出現錯誤就會根據該檔案恢復事務之前的資料 or MVCC中查找舊版本的資料=>事務提交了就可以回收
binlog：為了復制和恢復資料(兩階段提交)
格式: statement row(保存資料) mixed
事件執行程序中會寫入 binlog cache 提交的時候在持久化到硬碟上., 可以通過sync_binlog控制何時持久化.
兩階段提交:
在提交事務前, 每次的redo log的持久化都處于prepare階段)
提交事務的時候 會持久化binlog 并設定commit階段回滾事務的時候會判斷commit & binlog
索引
聚集索引: 一般使用自增主鍵 葉子節點存放資料
非聚集索引: 葉子節點存放主鍵 需要回表查詢
非聚集索引一定回表查詢嗎(覆寫索引)?
聯合索引覆寫了要查詢的欄位
索引sql
CREATE TABLE `class_teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`class_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_teacher_id` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# 建表的時候添加索引
show index from class_teacher \G; #查詢索引
drop index 'idx_teacher_id' on class_teacher; #洗掉索引
alter class_teacher add index index2 (teacher_id); #增加索引
資料庫建表的范式
1NF: 欄位不可再分
2NF: 消除部分依賴( 聯合主鍵)
3NF: 消除傳遞依賴 (聯合表)
資料庫并發以及加鎖原理
事務的ACID
隔離性和一致性 在并發中 尤為重要
事務的隔離級別
讀未提交 讀已提交 可重復讀 可序列化
(對應的問題: 臟讀 不可重復讀 幻讀)
加鎖處理隔離級別(悲觀鎖)
可以 通過 加行鎖 來處理臟讀以及 不可重復讀的問題 的問題
可以通過 加表鎖 來處理幻讀
(讀寫互斥, 讀加共享鎖 S，寫加排他鎖 X)
Innodb的對于隔離級別的實作
Innodb使用了MVCC來實作非阻塞的讀, 可以認為是行級鎖的一個變種, 寫操作也只需要鎖定必要的行 目的是加快并發. (實際上MVCC是一種樂觀鎖 資料訪問不會排他 加快資料的并發訪問)
當前讀(鎖定讀)需要 加鎖 一致性非鎖定讀(快照讀) 不需要加鎖
MVCC 的實作依賴于：隱藏欄位、Read View、undo log，
當前讀 鎖定讀: select .. lock in share mode(S鎖) select ... for update 和 update(X鎖)=> 讀完都會鎖定
非鎖定讀 快照讀 : select ..
在RC級別下的MVCC:
當前讀=>需要加 行鎖. 并且 可能操作完成后 需要更新 DB_TRX_ID
快照讀=>則 在每一次的讀取前生成ReadView( 包含當前活躍事務m_ids (除自身以外) 與 m_low_limit_id,m_up_limit_id等引數), 通過與DB_TRX_ID判斷記錄可不可見,
通過每一次生成新的readview 保證每次讀取到的資料都是已經提交的資料.
在RR級別下的MVCC
快照讀=> 在第一次的讀取(select) 前生成一個readview 以后一直使用 從而保證了可重復讀取
MVCC plus 防止幻讀
一句話: MVCC+NEXT-KEY lock 可以處理幻讀 但是處理的并不完美 需要手動加鎖
Next-Key lock= record 鎖+ GAP鎖 (鎖住當前索引到 兩邊索引的區間 如果沒有索引 變成表鎖)
在RR 隔離下, 可以通過執行 select...for update/lock in share mode、insert、update、delete 等當前讀 , 從而對讀取到的記錄以及兩邊的區間加next-key lock來阻塞其他的事務插入資料. (阻塞很影響效率)
或者 不手動加鎖 保證事務只使用快照讀, 但是 如果 本事務 使用了update等(當前讀) 修改了其他事務插入的行的DB_TRX_ID為 自身 會導致 幻行 出現.
實驗
- 加鎖 防止換行
- 不加鎖 使用快照讀 不會阻塞, 但是其他執行緒使用update等當前讀之后 會修改DB_TRX_ID 導致幻讀
參考
https://javaguide.cn/database/mysql/innodb-implementation-of-mvcc/#undo-log
https://www.modb.pro/db/62503
https://tech.meituan.com/2014/08/20/innodb-lock.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/423922.html
標籤：其他