劍指 MySQL — 架構、調優與運維

目錄

第1章　Linux平臺下MySQL的安裝與使用 1
1.1　MySQL概述 1
1.1.1　MySQL簡介 1
1.1.2　MySQL的發展歷程 2
1.1.3　MySQL高手是如何練就的 3
1.2　Linux平臺下MySQL的安裝 3
1.2.1　MySQL的四大版本 3
1.2.2　下載MySQL指定版本 3
1.2.3　Linux平臺下安裝MySQL的
三種方式 5
1.2.4　安裝MySQL前的必要操作 6
1.2.5　CentOS 7下MySQL的安裝
過程 7
1.3　卸載MySQL 10
1.4　登錄MySQL 10
1.4.1　首次登錄 10
1.4.2　修改密碼 11
1.4.3　設置遠程登錄 11
1.5　MySQL 8.0的密碼強度評估 14
1.5.1　MySQL不同版本的密碼設置 14
1.5.2　MySQL 8.0的密碼安全策略 15
1.6　字符集的設置 17
1.6.1　查看字符集 17
1.6.2　查看校對規則 18
1.6.3　各級別的字符集和校對規則 19
1.6.4　MySQL 5.7和MySQL 8.0中數據
庫級別默認字符集的變化 22
1.7　SQL語句中的字母大小寫規範 23
1.7.1　Windows和Linux平臺下字母
大小寫規範的區別 23
1.7.2　Linux平臺下字母大小寫規範
設置 23
1.8　sql_mode 24
1.8.1　sql_mode簡介 24
1.8.2　寬松模式與嚴格模式對比 24
1.8.3　模式的查看和設置 25
1.9　小結 26
第2章　MySQL的目錄結構 27
2.1　MySQL的主要目錄 27
2.1.1　MySQL的數據目錄 27
2.1.2　MySQL的數據庫命令目錄 27
2.2　數據庫和文件系統的關系 29
2.2.1　查看系統數據庫 29
2.2.2　數據庫在文件系統中的表示 29
2.2.3　表在文件系統中的表示 30
2.2.4　視圖在文件系統中的表示 33
2.2.5　其他文件 33
2.3　小結 34
第3章　用戶與權限管理 35
3.1　權限表 35
3.1.1　user表 35
3.1.2　db表 37
3.1.3　tables_priv表和
columns_priv表 38
3.1.4　procs_priv表 39
3.2　用戶管理 40
3.2.1　登錄MySQL服務器 40
3.2.2　創建用戶 40
3.2.3　刪除用戶 41
3.2.4　修改用戶信息 42
3.2.5　修改當前用戶的密碼 42
3.2.6　修改其他普通用戶的密碼 43
3.2.7　MySQL 8.0密碼管理 43
3.3　權限管理 45
3.3.1　權限列表 45
3.3.2　授予權限的原則 46
3.3.3　授予權限的方式 46
3.3.4　查看權限 47
3.3.5　回收權限 50
3.4　訪問控制 50
3.4.1　連接核實階段 50
3.4.2　請求核實階段 50
3.5　角色管理 51
3.5.1　角色概述 51
3.5.2　創建角色 52
3.5.3　給角色授權 52
3.5.4　查看角色擁有的權限 53
3.5.5　回收角色擁有的權限 53
3.5.6　刪除角色 54
3.5.7　賦予用戶角色 54
3.5.8　激活角色 55
3.5.9　撤銷用戶被賦予的角色 55
3.5.10　設置強制角色 56
3.6　配置文件的使用 57
3.6.1　配置文件的格式 57
3.6.2　啟動命令與選項組 58
3.6.3　特定MySQL版本的專用
選項組 59
3.6.4　同一配置文件中多個選項組的
優先級 59
3.6.5　命令行和配置文件中啟動選項
的區別 59
3.7　系統變量 59
3.7.1　系統變量簡介 59
3.7.2　查看系統變量 59
3.7.3　通過啟動選項設置系統變量 60
3.7.4　在服務器端程序運行過程中設置系統變量 61
3.7.5　啟動選項和系統變量之間的
關系 63
3.8　小結 63
第4章　邏輯架構 64
4.1　邏輯架構剖析 64
4.1.1　MySQL的邏輯架構 64
4.1.2　客戶端連接器 66
4.1.3　連接層 66
4.1.4　服務層 66
4.1.5　存儲引擎層 67
4.1.6　存儲層 68
4.2　SQL語句的執行流程 68
4.3　MySQL 5.7中的查詢緩存設置 73
4.4　數據庫緩沖池 75
4.4.1　緩沖池和查詢緩存 75
4.4.2　查看緩沖池的大小 76
4.4.3　將數據加載到緩沖池中 77
4.4.4　緩沖池中的free鏈表 78
4.4.5　緩沖池中的LRU鏈表 79
4.4.6　緩沖池中的flush鏈表 81
4.5　小結 82
第5章　存儲引擎 83
5.1　存儲引擎的相關操作 83
5.1.1　查看存儲引擎 83
5.1.2　設置系統默認的存儲引擎 85
5.1.3　設置表的存儲引擎 85
5.2　主要存儲引擎介紹 86
5.2.1　InnoDB存儲引擎：事務型存儲
引擎 86
5.2.2　MyISAM存儲引擎：主要的非事務處理存儲引擎 86
5.2.3　ARCHIVE存儲引擎：數據
存檔 86
5.2.4　BLACKHOLE存儲引擎：丟棄寫操作 87
5.2.5　CSV存儲引擎：存儲CSV
文件 87
5.2.6　MEMORY存儲引擎：內存表 88
5.2.7　其他存儲引擎 89
5.2.8　常用存儲引擎對比 89
5.3　小結 90
第6章　InnoDB存儲引擎中的數據存儲結構 91
6.1　數據庫的存儲架構 91
6.2　InnoDB存儲引擎中的行格式 91
6.2.1　查看和修改行格式 91
6.2.2　完整的記錄信息 92
6.2.3　行溢出 93
6.2.4　REDUNDANT行格式 94
6.2.5　COMPACT行格式 94
6.2.6　DYNAMIC和COMPRESSED
行格式 95
6.3　頁的結構 96
6.3.1　頁的組成部分 96
6.3.2　從頁的角度看B+樹是如何進行
查詢的 99
6.4　區和段 99
6.4.1　區 99
6.4.2　段 100
6.5　表空間 100
6.6　小結 102
第7章　索引 103
7.1　索引概述 103
7.2　合理選擇索引的數據結構 104
7.2.1　二叉搜索樹 104
7.2.2　AVL樹 105
7.2.3　B樹 105
7.2.4　B+樹 106
7.2.5　哈希結構 108
7.3　索引分類 109
7.3.1　按功能邏輯分類 110
7.3.2　按物理實現方式分類 111
7.3.3　按索引字段個數分類 112
7.4　索引的創建與刪除 113
7.4.1　創建索引 113
7.4.2　刪除索引 123
7.5　InnoDB和MyISAM存儲引擎中的索引
方案 126
7.5.1　InnoDB存儲引擎中的索引
方案 126
7.5.2　MyISAM存儲引擎中的索引
方案 128
7.5.3　InnoDB和MyISAM存儲引擎中
的索引方案對比 129
7.6　MySQL 8.0的新特性 130
7.6.1　支持降序索引 130
7.6.2　支持隱藏索引 132
7.7　適合創建索引的場景 135
7.7.1　數據準備 135
7.7.2　查詢操作的條件字段 137
7.7.3　分組和排序的字段 138
7.7.4　更新和刪除操作的條件字段 141
7.7.5　去重的字段 142
7.7.6　多表連接查詢 142
7.7.7　數據類型小的列 143
7.8　不適合創建索引的場景 144
7.8.1　WHERE條件用不到的字段 144
7.8.2　數據量小的表 144
7.8.3　有大量重復數據的字段 145
7.8.4　索引不要冗餘或重復創建 147
7.8.5　其他不適合創建索引的場景 147
7.9　小結 148
第8章　性能分析工具的使用 149
8.1　數據庫性能調優步驟 149
8.2　查看系統狀態信息 150
8.3　查看SQL查詢成本 151
8.4　定位執行時間長的SQL語句 153
8.4.1　開啟慢查詢日誌 153
8.4.2　測試及分析 155
8.4.3　慢查詢日誌分析工具 156
8.4.4　關閉慢查詢日誌 157
8.4.5　刪除慢查詢日誌 157
8.5　查看SQL語句的具體查詢成本 158
8.6　分析查詢語句：EXPLAIN 163
8.6.1　說明 163
8.6.2　EXPLAIN結果之id 164
8.6.3　EXPLAIN結果之select_type 166
8.6.4　EXPLAIN結果之table 172
8.6.5　EXPLAIN結果之partitions 172
8.6.6　EXPLAIN結果之type 173
8.6.7　EXPLAIN結果之possible_keys
和key 177
8.6.8　EXPLAIN結果之key_len 178
8.6.9　EXPLAIN結果之ref 179
8.6.10　EXPLAIN結果之rows 181
8.6.11　EXPLAIN結果之filtered 181
8.6.12　EXPLAIN結果之Extra 182
8.6.13　JSON格式的執行計劃 183
8.6.14　執行計劃的擴展信息 185
8.7　小結 185
第9章　索引優化 186
9.1　數據準備 186
9.2　索引優化原則 188
9.2.1　全值匹配法則 188
9.2.2　最左前綴法則 188
9.2.3　優先考慮覆蓋索引 190
9.3　索引失效的場景 191
9.3.1　查詢條件中包含函數、計算導致
索引失效 191
9.3.2　範圍查詢條件右邊的列不能被
使用導致索引失效 194
9.3.3　不等值查詢導致索引失效 195
9.3.4　判空條件對索引的影響 196
9.3.5　LIKE以通配符“%”開頭導致
索引失效 196
9.3.6　數據類型轉換導致索引失效 197
9.3.7　OR關鍵字導致索引失效 198
9.4　關聯查詢優化 199
9.4.1　數據準備 199
9.4.2　採用左連接：LEFT JOIN 200
9.4.3　採用內連接：INNER JOIN 203
9.4.4　JOIN語句的原理 206
9.5　排序優化 210
9.5.1　ORDER BY的索引規則 210
9.5.2　排序方式比較 214
9.5.3　影響排序效率的兩個系統
變量 217
9.6　索引下推 217
9.6.1　索引下推概述 217
9.6.2　索引下推案例 221
9.7　B+樹索引的優化 223
9.7.1　自適應哈希索引 223
9.7.2　覆蓋索引 224
9.8　其他索引優化場景 225
9.8.1　子查詢優化 225
9.8.2　分頁查詢優化 228
9.8.3　索引提示 229
9.8.4　前綴索引 231
9.9　常用SQL編寫建議 233
9.10　小結 235
第10章　數據庫的設計規範 236
10.1　範式 236
10.1.1　範式簡介 236
10.1.2　範式分類 236
10.1.3　數據表中的鍵 237
10.1.4　第一範式 238
10.1.5　第二範式 238
10.1.6　第三範式 239
10.1.7　巴斯-科德範式 240
10.1.8　第四範式 240
10.2　反範式化 241
10.2.1　反範式化概述 241
10.2.2　反範式化應用舉例 241
10.2.3　反範式化的使用建議 246
10.3　ER模型 246
10.3.1　ER模型中的要素 247
10.3.2　ER模型中實體的屬性 248
10.3.3　把ER模型轉換為數據表 249
10.3.4　把實體轉換為數據表 249
10.3.5　把多對多的關系轉換為
數據表 252
10.3.6　通過外鍵來表達一對多的
關系 252
10.3.7　把屬性轉換為表中的字段 252
10.4　數據庫對象的設計規範 253
10.4.1　庫的設計規範 253
10.4.2　表的設計規範 253
10.4.3　索引的設計規範 254
10.5　PowerDesigner的使用 255
10.6　小結 261
第11章　數據庫調優 262
11.1　數據庫調優的措施 262
11.1.1　如何解決定位問題 262
11.1.2　數據庫調優的維度和步驟 262
11.2　優化MySQL服務器 264
11.2.1　優化服務器硬件 264
11.2.2　優化MySQL服務的配置
參數 264
11.3　優化數據庫結構 268
11.3.1　拆分表：冷、熱數據分離 268
11.3.2　增加中間表或冗餘字段 269
11.3.3　優化字段的數據類型 270
11.3.4　優化插入記錄的速度 277
11.3.5　使用非空約束 279
11.3.6　分析表、檢查表和優化表 279
11.4　大表優化 284
11.4.1　讀寫分離 284
11.4.2　垂直拆分 285
11.4.3　水平拆分 286
11.5　其他調優策略 287
11.5.1　服務器語句超時處理 287
11.5.2　創建全局通用表空間 287
11.5.3　臨時表性能優化 288
11.6　小結 289
第12章　數據庫事務 291
12.1　事務概述 291
12.1.1　事務的ACID特性 291
12.1.2　事務的狀態 292
12.2　事務的基本使用 293
12.2.1　數據準備 293
12.2.2　事務的基本語法 294
12.2.3　隱式提交數據庫事務的情況 296
12.2.4　事務提交和回滾案例 297
12.2.5　不支持事務的表無法回滾
事務 299
12.2.6　事務保存點 300
12.3　事務隔離級別 301
12.3.1　數據庫並發問題 301
12.3.2　事務中的4種隔離級別 303
12.3.3　設置隔離級別 304
12.3.4　不同隔離級別下的並發情況 305
12.4　小結 309
第13章　redo日誌和undo日誌 310
13.1　為什麽需要日誌維護事務 310
13.2　redo日誌 311
13.2.1　redo日誌的優勢 311
13.2.2　redo日誌文件組 311
13.2.3　checkpoint 312
13.2.4　Log Sequence Number 312
13.2.5　redo日誌緩存 313
13.2.6　redo日誌刷盤策略 314
13.2.7　redo日誌相關參數 315
13.3　undo日誌 316
13.3.1　為什麽需要undo日誌 316
13.3.2　undo日誌的作用 316
13.3.3　undo日誌的存儲機制 317
13.3.4　undo日誌如何回滾事務 319
13.3.5　undo日誌相關參數 321
13.4　小結 322
第14章　鎖 323
14.1　鎖概述 323
14.1.1　並發事務訪問相同記錄 323
14.1.2　鎖的分類 324
14.2　共享鎖和獨占鎖 325
14.3　表級鎖 327
14.3.1　表級別的共享鎖和獨占鎖 327
14.3.2　意向鎖 331
14.3.3　自增鎖 333
14.3.4　元數據鎖 335
14.4　InnoDB存儲引擎中的行級鎖 336
14.4.1　記錄鎖 337
14.4.2　間隙鎖 338
14.4.3　臨鍵鎖 339
14.4.4　插入意向鎖 340
14.5　悲觀鎖和樂觀鎖 340
14.5.1　悲觀鎖 340
14.5.2　樂觀鎖 341
14.5.3　悲觀鎖和樂觀鎖的適用場景 342
14.6　隱式鎖和顯式鎖 342
14.6.1　隱式鎖 342
14.6.2　顯式鎖 343
14.7　死鎖 343
14.8　鎖監控 348
14.9　MySQL加鎖案例解析 352
14.9.1　案例一：唯一索引等值查詢 352
14.9.2　案例二：非唯一索引等值
查詢 354
14.9.3　案例三：主鍵索引範圍查詢 356
14.9.4　案例四：非唯一索引上存在
等值的情況 357
14.9.5　案例五：LIMIT語句加鎖 359
14.10　小結 361
第15章　多版本並發控制 362
15.1　什麽是MVCC 362
15.1.1　快照讀 363
15.1.2　當前讀 363
15.2　版本鏈 363
15.3　ReadView 367
15.3.1　什麽是ReadView 367
15.3.2　ReadView的設計思路 367
15.3.3　ReadView規則 368
15.3.4　MVCC整體操作流程 370
15.4　不同隔離級別下的ReadView 370
15.4.1　READ COMMITTED隔離級
別下的ReadView 371
15.4.2　REPEATABLE READ隔離級
別下的ReadView 373
15.5　小結 375
第16章　其他數據庫日誌 376
16.1　MySQL支持的日誌 376
16.2　通用查詢日誌 376
16.2.1　開啟/關閉通用查詢日誌
功能 377
16.2.2　查看通用查詢日誌 378
16.2.3　刪除（刷新）通用查詢
日誌 378
16.3　錯誤日誌 379
16.3.1　開啟錯誤日誌功能 379
16.3.2　查看錯誤日誌 379
16.3.3　刪除（刷新）錯誤日誌 380
16.4　二進制日誌 381
16.4.1　開啟/關閉二進制日誌功能 381
16.4.2　二進制日誌參數設置 382
16.4.3　查看二進制日誌 383
16.4.4　刪除（刷新）二進制日誌 388
16.4.5　使用二進制日誌恢復數據 389
16.4.6　二進制日誌的寫入機制 394
16.4.7　兩階段提交 396
16.5　中繼日誌 397
16.6　數據定義語句日誌 398
16.7　小結 398
第17章　主從復制 399
17.1　主從復制概述 399
17.2　主從復制的原理 400
17.3　主從復制架構的搭建 400
17.3.1　準備工作 401
17.3.2　修改配置文件 401
17.3.3　主機：創建賬戶並授權 402
17.3.4　從機：配置需要復制的主機
信息 403
17.3.5　測試和停止主從復制 405
17.3.6　雙主雙從架構 405
17.3.7　二進制日誌格式在主從復制架構中的優缺點 406
17.4　數據一致性 407
17.4.1　數據同步延遲問題 407
17.4.2　如何解決主從復制中數據不一致的問題 408
17.5　數據庫中間件 410
17.6　小結 410
第18章　數據庫備份與恢復 411
18.1　物理備份和邏輯備份的概念 411
18.2　邏輯備份之mysqldump命令 411
18.2.1　備份單個數據庫 412
18.2.2　備份全部數據庫 414
18.2.3　備份多個數據庫 414
18.2.4　備份部分表 414
18.2.5　備份單表中的部分數據 416
18.2.6　排除某些表的備份 416
18.2.7　只備份表結構或只備份
數據 416
18.2.8　備份中包含函數、存儲過程或
事件 417
18.2.9　以事務的形式備份 419
18.2.10　mysqldump命令的常用
參數 419
18.3　邏輯恢復之mysql命令 420
18.3.1　從單庫備份中恢復單庫 420
18.3.2　全量備份恢復 420
18.3.3　從全量備份中恢復單庫 421
18.3.4　從單庫備份中恢復單表 421
18.4　MyISAM存儲引擎類型的表的物理
備份 421
18.5　MyISAM存儲引擎類型的表的物理
備份的恢復流程 422
18.6　InnoDB存儲引擎類型的表的物理
導出、導入 423
18.7　導出不同格式的文件 424
18.7.1　使用SELECT…INTO OUTFILE語句導出文件 425

18.7.2　使用mysqldump命令導出
文件 426
18.7.3　使用mysql命令導出文件 428
18.8　導入文本文件 430
18.8.1　使用LOAD DATA INFILE
語句導入文本文件 430
18.8.2　使用mysqlimport命令導入
文本文件 431
18.9　數據遷移 433
18.9.1　數據遷移概述 433
18.9.2　遷移方案和註意事項 433
18.10　誤刪數據的預防方案和恢復方案 435
18.11　MySQL常用命令 436
18.11.1　mysql 436
18.11.2　mysqladmin 436
18.11.3　mysqlbinlog 437
18.11.4　mysqldump 438
18.11.5　mysqlshow 438
18.12　小結 439