這本書去年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/

作者：fairjm 博客：https://bingowith.me 出處：http://www.cnblogs.com/fairjm/ 本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責任的權利.

高性能MySQL(第4版) 第一章 MySQL架構 讀書筆記