這本書去年11月出的,今年中文版也出了,並且直接上了微信讀書,之後有空就讀一讀,分享下讀書筆記~ 原文內容比較充實,建議有時間可以讀一下原文. 第一章主要是個概覽. MySQL的邏輯架構 預設情況下,每個客戶端連接都會在伺服器進程中擁有一個線程,該連接的查詢只會在這個單獨的線程中執行,該線程駐留在一 ...
這本書去年11月出的,今年中文版也出了,並且直接上了微信讀書,之後有空就讀一讀,分享下讀書筆記~
原文內容比較充實,建議有時間可以讀一下原文.
第一章主要是個概覽.
MySQL的邏輯架構
預設情況下,每個客戶端連接都會在伺服器進程中擁有一個線程,該連接的查詢只會在這個單獨的線程中執行,該線程駐留在一個內核或者CPU上.
線程池
優化器會向存儲引擎詢問它的一些功能、某個具體操作的成本,以及表數據的統計信息.
query cache 5.7.20棄用 8.0移除
考慮應用自己在redis中緩存
併發控制
只要有多個查詢需要同時修改數據,就會產生併發控制問題
讀寫鎖
處理併發讀/寫訪問的系統通常實現一個由兩種鎖類型組成的鎖系統
這兩種鎖通常被稱為共用鎖(shared lock)和排他鎖(exclusive lock),也叫讀鎖(read lock)和寫鎖(write lock)
鎖的粒度
通過降低鎖的粒度提高共用資源併發性
只鎖定包含需要修改的部分數據
表鎖
table lock
最基本 開銷最小的鎖策略(鎖本身的開銷 不是指查詢/修改性能)
行級鎖
row lock
最大程度支持併發處理 最大的鎖開銷
行級鎖是在存儲引擎中實現
事務
事務就是一組SQL語句,作為一個工作單元以原子方式進行處理
要麼全部執行成功 要麼全部執行失敗
ACID
隔離級別
這裡談的是ANSI SQL中的定義
-
READ UNCOMMITTED: 未提交讀
在事務中可以查看其他事務中還沒有提交的修改
臟讀(dirty read): 讀取未提交的數據 -
READ COMMITTED: 已提交讀
大多數資料庫系統的預設級別
但MySQL不是
一個事務可以看到其他事務在它開始之後提交的修改,但在該事務提交之前,其所做的任何修改對其他事務都是不可見的.
允許不可重覆讀(nonrepeatable read) 同一個事務中兩次執行相同語句 可能看到不同結果 -
REPEATABLE READ: 可重覆讀
解決了不可重覆讀
無法解決幻讀(phantom read) 讀取範圍數據時 如果另一個事務在該範圍插入了新的 再次讀取會產生換行(phantom row)
InnoDB和XtraDB通過MVCC解決
這也是MySQL預設的隔離級別 -
SERIALIZABLE: 可串列化
該級別通過強制事務按序執行,使不同事務之間不可能產生衝突,從而解決了前面說的幻讀問題.
會在讀取的每一行數據上都加鎖,所以可能導致大量的超時和鎖爭用的問題.
使用場景較少 除非需要嚴格確保數據安全 並且接收併發性能下降
死鎖
兩個或多個事務相互持有和請求相同資源上的鎖,產生了迴圈依賴.
當多個事務試圖以不同的順序鎖定資源時會導致死鎖.
資料庫系統實現了各種是說檢測和鎖超時機制
InnoDB目前處理死鎖的方式是將持有最少行級排他鎖的事務回滾
鎖的行為和順序和存儲引擎相關
console1:
start transaction ;
update user set user_name='cc11' where user_id=1;
update user set user_name='cc22' where user_id=2;
commit ;
console2:
start transaction ;
update user set user_name='cc222' where user_id=2;
update user set user_name='cc111' where user_id=1;
commit ;
單步執行
[40001][1213] Deadlock found when trying to get lock; try restarting transaction
事務日誌
事務日誌有助於提高事務的效率.存儲引擎只需要更改記憶體中的數據副本,而不用每次修改磁碟中的表,這會非常快.
事務日誌只追加 順序I/O
WAL write-ahead logging 預寫日誌 修改數據最終要兩次磁碟寫入
MySQL中的事務
描述的是InnoDB引擎中的事務
理解AUTOCOMMIT
預設開啟 單個語句也是包裹在事務中 自動提交
可以通過set autocommit=0/1進行開關
用begin或者start transaction來開啟事務
用commit提交 rollback回滾
有一些命令,當在活動的事務中發出時,會導致MySQL在事務的所有語句執行完畢前提交當前事務
比如一些DDL命令 alter table等
可以通過SET TRANSACTION ISOLATION LEVEL改變隔離級別 下一個事務開始時生效
在事務中混合使用存儲引擎
MySQL的事務由下層存儲引擎實現
在同一個事務中,混合使用多種存儲引擎是不可靠的.
隱式鎖定和顯式鎖定
InnoDB使用兩階段鎖定協議(two-phase locking protocol).
事務執行期間,隨時都可以獲取鎖,但鎖只有在提交或回滾後才會釋放,並且所有的鎖會同時釋放.
前面描述的鎖定機制都是隱式的.InnoDB會根據隔離級別自動處理鎖.
顯式的(不屬於SQL規範)
SELECT … FOR SHARE
SELECT … FOR UPDATE
MySQL還支持LOCK TABLES和UNLOCK TABLES
這兩個命令在伺服器級別實現
因為InnoDB支持行級鎖 沒必要使用
建議: 除了在禁用AUTOCOMMIT的事務中可以使用之外,其他任何時候都不要顯式地執行LOCK TABLES,不管使用的是什麼存儲引擎.
多版本併發控制
MySQL的大多數事務型存儲引擎使用的都不是簡單的行級鎖機制.它們會將行級鎖和可以提高併發性能的多版本併發控制(MVCC)技術結合使用.
不同資料庫的實現細節不一樣
可以認為MVCC是行級鎖的一種變種 它在很多情況下避免加鎖 因此開銷更低
通過數據快照實現
- InnoDB為每個事務啟動時分配一個事務ID
- 該事務修改記錄時 向Undo log寫入一條如何恢復回去的undo記錄 事務回滾指針指向該記錄
- 當不同會話讀取聚簇主鍵索引記錄時 InnoDB會把記錄的事務ID和該會話的讀取視圖比較 如果更改他的事務未提交 則跟蹤undo log直到一個符合可見條件的事務ID
大多數讀取通過這種方式不需要獲取鎖(通過讀取快照) 缺點是存儲引擎會對每一行存儲更多數據 做更多工作
MVCC僅適用於REPEATABLE READ和READ COMMITTED隔離級別.
(可以想象對於可重覆讀 讀取的事務id固定為事務進行中第一次讀的可見事務id 對於讀已提交 讀最新的可見事務id
另外兩個因為不需要事務版本(一個是臟讀 一個是串列化的) 和MVCC不是很適配(當然要看不同引擎的實現)
複製
Replication
一主多從
數據文件結構
在8.0版本中,MySQL將表的元數據重新設計為一種數據字典,包含在表的.ibd文件中
使得表結構上的信息支持事務和原子級數據定義更改
除了以來information_schema檢索表定義和元數據
引入了字典對象緩存 LRU的記憶體緩存
使得伺服器訪問表的元數據減少了I/O
每個表的.ibd和.frm文件被替換為已經被序列化的字典信息(.sdi).
InnoDB引擎
為處理大量短期事務而設計 這些事務預期通常是正常提交 很少會被回滾
預設情況下,InnoDB將數據存儲在一系列的數據文件中,這些文件統被稱為表空間(tablespace)
InnoDB使用MVCC來實現高併發性,並實現了所有4個SQL標準隔離級別.
預設為REPEATABLE READ隔離級別,並且通過間隙鎖(next-key locking)策略來防止在這個隔離級別上的幻讀：
InnoDB不只鎖定在查詢中涉及的行,還會對索引結構中的間隙進行鎖定,以防止幻行被插入
基於聚簇索引構建
但是,因為二級索引（secondary index,非主鍵索引）需要包含主鍵列,如果主鍵較大,則其他索引也會很大.如果表中的索引較多,主鍵應當儘量小.
微信讀書: https://weread.qq.com/web/bookDetail/00a32b70813ab746fg018ec7
博客位置: https://bingowith.me/2022/11/08/high-performance-mysql-4th-ch01-note/
