MySQL 是怎樣運行的 -- 從根兒上理解 MySQL

第0章　楔子——閱讀前必看 1

第 1章　裝作自己是個小白——初識MySQL　3

1.1　MySQL的客戶端/服務器架構　3

1.2　MySQL的安裝　3

1.3　啟動MySQL服務器程序　5

1.3.1　在類UNIX系統中啟動服務器程序　5

1.3.2　在Windows系統中啟動服務器程序　6

1.4　啟動MySQL客戶端程序　7

1.5　客戶端與服務器連接的過程　10

1.5.1　TCP/IP　10

1.5.2　命名管道和共享內存　10

1.5.3　UNIX域套接字　11

1.6　服務器處理客戶端請求　11

1.6.1　連接管理　12

1.6.2　解析與優化　12

1.6.3　存儲引擎　14

1.7　常用存儲引擎　14

1.8　關於存儲引擎的一些操作　16

1.8.1　查看當前服務器程序支持的存儲引擎　16

1.8.2　設置表的存儲引擎　16

1.9　總結　17

第 2章　MySQL的調控按鈕——啟動選項和系統變量　19

2.1　啟動選項和配置文件　19

2.1.1　在命令行上使用選項　19

2.1.2　配置文件中使用選項　21

2.1.3　在命令行和配置文件中啟動選項的區別　26

2.2　系統變量　27

2.2.1　系統變量簡介　27

2.2.2　查看系統變量　27

2.2.3　設置系統變量　28

2.3　狀態變量　32

2.4　總結　32

第3章　字符集和比較規則　34

3.1　字符集和比較規則簡介　34

3.1.1　字符集簡介　34

3.1.2　比較規則簡介　34

3.1.3　一些重要的字符集　35

3.2　MySQL中支持的字符集和比較規則　36

3.2.1　MySQL中的utf8和utf8mb4　36

3.2.2　字符集的查看　36

3.2.3　比較規則的查看　38

3.3　字符集和比較規則的應用　39

3.3.1　各級別的字符集和比較規則　39

3.3.2　客戶端和服務器通信過程中使用的字符集　44

3.3.3　比較規則的應用　52

3.4　總結　53

第4章　從一條記錄說起——InnoDB記錄存儲結構　55

4.1　準備工作　55

4.2　InnoDB頁簡介　55

4.3　InnoDB行格式　56

4.3.1　指定行格式的語法　56

4.3.2　COMPACT行格式　56

4.3.3　REDUNDANT行格式　64

4.3.4　溢出列　68

4.3.5　DYNAMIC行格式和COMPRESSED行格式　70

4.4　總結　71

第5章　盛放記錄的大盒子——InnoDB數據頁結構　72

5.1　不同類型的頁簡介　72

5.2　數據頁結構快覽　72

5.3　記錄在頁中的存儲　73

5.4　Page Directory（頁目錄）　80

5.5　Page Header（頁面頭部）　85

5.6　File Header（文件頭部）　86

5.7　File Trailer（文件尾部）　88

5.8　總結　88

第6章　快速查詢的秘籍——B+樹索引　90

6.1　沒有索引時進行查找　90

6.1.1　在一個頁中查找　90

6.1.2　在很多頁中查找　91

6.2　索引　91

6.2.1　一個簡單的索引方案　92

6.2.2　InnoDB中的索引方案　94

6.2.3　InnoDB中B+樹索引的註意事項　102

6.2.4　MyISAM中的索引方案簡介　104

6.2.5　MySQL中創建和刪除索引的語句　105

6.3　總結　106

第7章　B+樹索引的使用　107

7.1　B+樹索引示意圖的簡化　107

7.2　索引的代價　109

7.3　應用B+樹索引　110

7.3.1　掃描區間和邊界條件　110

7.3.2　索引用於排序　122

7.3.3　索引用於分組　125

7.4　回表的代價　126

7.5　更好地創建和使用索引　127

7.5.1　只為用於搜索、排序或分組的列創建索引　127

7.5.2　考慮索引列中不重復值的個數　127

7.5.3　索引列的類型盡量小　127

7.5.4　為列前綴建立索引　128

7.5.5　覆蓋索引　129

7.5.6　讓索引列以列名的形式在搜索條件中單獨出現　129

7.5.7　新插入記錄時主鍵大小對效率的影響　129

7.5.8　冗餘和重復索引　130

7.6　總結　131

第8章　數據的家——MySQL的數據目錄　132

8.1　數據庫和文件系統的關系　132

8.2　MySQL數據目錄　132

8.2.1　數據目錄和安裝目錄的區別　132

8.2.2　如何確定MySQL中的數據目錄　132

8.3　數據目錄的結構　133

8.3.1　數據庫在文件系統中的表示　133

8.3.2　表在文件系統中的表示　134

8.3.3　其他的文件　137

8.4　文件系統對數據庫的影響　137

8.5　MySQL系統數據庫簡介　138

8.6　總結　138

第9章　存放頁面的大池子——InnoDB的表空間　140

9.1　回憶一些舊知識　140

9.1.1　頁面類型　140

9.1.2　頁面通用部分　141

9.2　獨立表空間結構　142

9.2.1　區的概念　142

9.2.2　段的概念　144

9.2.3　區的分類　145

9.2.4　段的結構　149

9.2.5　各類型頁面詳細情況　150

9.2.6　Segment Header結構的運用　156

9.2.7　真實表空間對應的文件大小　157

9.3　系統表空間　158

9.4　總結　164

第 10章　條條大路通羅馬——單表訪問方法　166

10.1　訪問方法的概念　167

10.2　const　167

10.3　ref　168

10.4　ref_or_null　170

10.5　range　171

10.6　index　171

10.7　all　172

10.8　註意事項　172

10.8.1　重溫二級索引+回表　172

10.8.2　索引合並　173

10.9　總結　177

第 11章　兩個表的親密接觸——連接的原理　178

11.1　連接簡介　178

11.1.1　連接的本質　178

11.1.2　連接過程簡介　180

11.1.3　內連接和外連接　181

11.2　連接的原理　185

11.2.1　嵌套循環連接　186

11.2.2　使用索引加快連接速度　187

11.2.3　基於塊的嵌套循環連接　188

11.3　總結　189

第 12章　誰最便宜就選誰——基於成本的優化　190

12.1　什麽是成本　190

12.2　單表查詢的成本　190

12.2.1　準備工作　190

12.2.2　基於成本的優化步驟　191

12.2.3　基於索引統計數據的成本計算　198

12.3　連接查詢的成本　201

12.3.1　準備工作　201

12.3.2　條件過濾（Condition Filtering）　201

12.3.3　兩表連接的成本分析　203

12.3.4　多表連接的成本分析　205

12.4　調節成本常數　206

12.4.1　mysql.server_cost表　206

12.4.2　mysql.engine_cost表　208

12.5　總結　209

第 13章　兵馬未動，糧草先行——InnoDB統計數據是如何收集的　210

13.1　統計數據的存儲方式　210

13.2　基於磁盤的永久性統計數據　211

13.2.1　innodb_table_stats　211

13.2.2　innodb_index_stats　214

13.2.3　定期更新統計數據　215

13.2.4　手動更新innodb_table_stats和innodb_index_stats表　216

13.3　基於內存的非永久性統計數據　217

13.4　innodb_stats_method的使用　217

13.5　總結　219

第 14章　基於規則的優化（內含子查詢優化二三事）　220

14.1　條件化簡　220

14.1.1　移除不必要的括號　220

14.1.2　常量傳遞　220

14.1.3　移除沒用的條件　221

14.1.4　表達式計算　221

14.1.5　HAVING子句和WHERE子句的合並　221

14.1.6　常量表檢測　221

14.2　外連接消除　222

14.3　子查詢優化　224

14.3.1　子查詢語法　225

14.3.2　子查詢在MySQL中是怎麽執行的　230

14.4　總結　244

第 15章　查詢優化的百科全書——EXPLAIN詳解　245

15.1　執行計劃輸出中各列詳解　246

15.1.1　table　246

15.1.2　id　247

15.1.3　select_type　249

15.1.4　partitions　252

15.1.5　type　252

15.1.6　possible_keys和key　255

15.1.7　key_len　256

15.1.8　ref　258

15.1.9　rows　258

15.1.10　filtered　259

15.1.11　Extra　260

15.2　JSON格式的執行計劃　266

15.3　Extented EXPLAIN　268

15.4　總結　269

第 16章　神兵利器——optimizer trace的神奇功效　270

16.1　optimizer trace簡介　270

16.2　通過optimizer trace分析查詢優化器的具體工作過程　271

第 17章　調節磁盤和CPU的矛盾——InnoDB的Buffer Pool　278

17.1　緩存的重要性　278

17.2　InnoDB的Buffer Pool　278

17.2.1　啥是Buffer Pool　278

17.2.2　Buffer Pool內部組成　278

17.2.3　free鏈表的管理　279

17.2.4　緩沖頁的哈希處理　280

17.2.5　flush鏈表的管理　281

17.2.6　LRU鏈表的管理　282

17.2.7　其他的一些鏈表　286

17.2.8　刷新臟頁到磁盤　287

17.2.9　多個Buffer Pool實例　287

17.2.10　innodb_buffer_pool_chunk_size　288

17.2.11　配置Buffer Pool時的註意事項　289

17.2.12　查看Buffer Pool的狀態信息　291

17.3　總結　293

第 18章　從貓爺借錢說起——事務簡介　294

18.1　事務的起源　294

18.1.1　原子性（Atomicity）　295

18.1.2　隔離性（Isolation）　295

18.1.3　一致性（Consistency）　296

18.1.4　持久性（Durability）　298

18.2　事務的概念　298

18.3　MySQL中事務的語法　300

18.3.1　開啟事務　300

18.3.2　提交事務　301

18.3.3　手動中止事務　302

18.3.4　支持事務的存儲引擎　302

18.3.5　自動提交　303

18.3.6　隱式提交　304

18.3.7　保存點　305

18.4　總結　307

第 19章　說過的話就一定要做到——redo日誌　308

19.1　事先說明　308

19.2　redo日誌是啥　308

19.3　redo日誌格式　309

19.3.1　簡單的redo日誌類型　309

19.3.2　復雜一些的redo日誌類型　311

19.3.3　redo日誌格式小結　314

19.4　Mini-Transaction　315

19.4.1　以組的形式寫入redo日誌　315

19.4.2　Mini-Transaction的概念　319

19.5　redo日誌的寫入過程　319

19.5.1　redo log block　319

19.5.2　redo日誌緩沖區　320

19.5.3　redo日誌寫入log buffer　321

19.6　redo日誌文件　323

19.6.1　redo日誌刷盤時機　323

19.6.2　redo日誌文件組　323

19.6.3　redo日誌文件格式　324

19.7　log sequence number　327

19.7.1　flushed_to_disk_lsn　328

19.7.2　lsn值和redo日誌文件組中的偏移量的對應關系　330

19.7.3　flush鏈表中的lsn　330

19.8　checkpoint　332

19.9　用戶線程批量從flush鏈表中刷出臟頁　335

19.10　查看系統中的各種lsn值　335

19.11　innodb_flush_log_at_trx_commit的用法　336

19.12　崩潰恢復　336

19.12.1　確定恢復的起點　337

19.12.2　確定恢復的終點　337

19.12.3　怎麽恢復　337

19.13　遺漏的問題：LOG_BLOCK_HDR_NO是如何計算的　339

19.14　總結　340

第 20章　後悔了怎麽辦——undo日誌　342

20.1　事務回滾的需求　342

20.2　事務id　343

20.2.1　分配事務id的時機　343

20.2.2　事務id是怎麽生成的　343

20.2.3　trx_id隱藏列　344

20.3　undo日誌的格式　344

20.3.1　INSERT操作對應的undo日誌　345

20.3.2　DELETE操作對應的undo日誌　347

20.3.3　UPDATE操作對應的undo日誌　353

20.3.4　增刪改操作對二級索引的影響　357

20.4　通用鏈表結構　357

20.5　FIL_PAGE_UNDO_LOG頁面　359

20.6　Undo頁面鏈表　361

20.6.1　單個事務中的Undo頁面鏈表　361

20.6.2　多個事務中的Undo頁面鏈表　362

20.7　undo日誌具體寫入過程　363

20.7.1　段的概念　363

20.7.2　Undo Log Segment Header　364

20.7.3　Undo Log Header　365

20.7.4　小結　367

20.8　重用Undo頁面　368

20.9　回滾段　369

20.9.1　回滾段的概念　369

20.9.2　從回滾段中申請Undo頁面鏈表　371

20.9.3　多個回滾段　372

20.9.4　回滾段的分類　374

20.9.5　roll_pointer的組成　374

20.9.6　為事務分配Undo頁面鏈表的詳細過程　375

20.10　回滾段相關配置　376

20.10.1　配置回滾段數量　376

20.10.2　配置undo表空間　376

20.11　undo日誌在崩潰恢復時的作用　377

20.12　總結　377

第 21章　一條記錄的多副面孔——事務隔離級別和MVCC　379

21.1　事前準備　379

21.2　事務隔離級別　379

21.2.1　事務並發執行時遇到的一致性問題　382

21.2.2　SQL標準中的4種隔離級別　385

21.2.3　MySQL中支持的4種隔離級別　386

21.3　MVCC原理　388

21.3.1　版本鏈　388

21.3.2　ReadView　390

21.3.3　二級索引與MVCC　397

21.3.4　MVCC小結　397

21.4　關於purge　398

21.5　總結　399

第 22章　工作面試老大難——鎖　401

22.1　解決並發事務帶來問題的兩種基本方式　401

22.1.1　寫-寫情況　401

22.1.2　讀-寫或寫-讀情況　403

22.1.3　一致性讀　404

22.1.4　鎖定讀　404

22.1.5　寫操作　405

22.2　多粒度鎖　406

22.3　MySQL中的行鎖和表鎖　408

22.3.1　其他存儲引擎中的鎖　408

22.3.2　InnoDB存儲引擎中的鎖　409

22.3.3　InnoDB鎖的內存結構　417

22.4　語句加鎖分析　423

22.4.1　普通的SELECT語句　423

22.4.2　鎖定讀的語句　424

22.4.3　半一致性讀的語句　441

22.4.4　INSERT語句　442

22.5　查看事務加鎖情況　444

22.5.1　使用information_schema數據庫中的表獲取鎖信息　444

22.5.2　使用SHOW ENINGE INNODB STATUS獲取鎖信息　446

22.6　死鎖　450

22.7　總結　454

參考資料　455