高性能MySQL（第3版）

推薦序....................................................................... xxiii 前言............................................................................xxv 第1 章 MySQL 架構(gòu)與歷史 ............................................ 1 1.1 MySQL 邏輯架構(gòu)............................................................................................1 1.1.1 連接管理與安全性.................................................................................2 1.1.2 優(yōu)化與執(zhí)行............................................................................................3 1.2 并發(fā)控制.........................................................................................................3 1.2.1 讀寫鎖...................................................................................................4 1.2.2 鎖粒度...................................................................................................4 1.3 事務(wù).................................................................................................................6 1.3.1 隔離級別...............................................................................................8 1.3.2 死鎖.......................................................................................................9 1.3.3 事務(wù)日志.............................................................................................10 1.3.4 MySQL 中的事務(wù)................................................................................10 1.4 多版本并發(fā)控制 ............................................................................................12 1.5 MySQL 的存儲引擎......................................................................................13 1.5.1 InnoDB 存儲引擎................................................................................16 1.5.2 MyISAM 存儲引擎..............................................................................17 1.5.3 MySQL 內(nèi)建的其他存儲引擎..............................................................19 1.5.4 第三方存儲引擎..................................................................................22 1.5.5 選擇合適的引擎..................................................................................24 1.5.6 轉(zhuǎn)換表的引擎......................................................................................27 1.6 MySQL 時間線（Timeline）..........................................................................29 1.7 MySQL 的開發(fā)模式......................................................................................32 1.8 總結(jié)...............................................................................................................33 第2 章 MySQL 基準(zhǔn)測試 ............................................. 35 2.1 為什么需要基準(zhǔn)測試....................................................................................35 2.2 基準(zhǔn)測試的策略 ............................................................................................37 2.2.1 測試何種指標(biāo)......................................................................................38 2.3 基準(zhǔn)測試方法................................................................................................40 2.3.1 設(shè)計和規(guī)劃基準(zhǔn)測試...........................................................................41 2.3.2 基準(zhǔn)測試應(yīng)該運(yùn)行多長時間................................................................42 2.3.3 獲取系統(tǒng)性能和狀態(tài) ...........................................................................43 2.3.4 獲得準(zhǔn)確的測試結(jié)果...........................................................................44 2.3.5 運(yùn)行基準(zhǔn)測試并分析結(jié)果...................................................................46 2.3.6 繪圖的重要性......................................................................................47 2.4 基準(zhǔn)測試工具................................................................................................49 2.4.1 集成式測試工具..................................................................................49 2.4.2 單組件式測試工具...............................................................................50 2.5 基準(zhǔn)測試案例................................................................................................52 2.5.1 http_load ..............................................................................................53 2.5.2 MySQL 基準(zhǔn)測試套件.........................................................................54 2.5.3 sysbench ..............................................................................................55 2.5.4 數(shù)據(jù)庫測試套件中的dbt2 TPC-C 測試 ...............................................60 2.5.5 Percona 的TPCC-MySQL 測試工具 ....................................................63 2.6 總結(jié) ...............................................................................................................65 第3 章 服務(wù)器性能剖析............................................... 67 3.1 性能優(yōu)化簡介................................................................................................67 3.1.1 通過性能剖析進(jìn)行優(yōu)化.......................................................................69 3.1.2 理解性能剖析......................................................................................71 3.2 對應(yīng)用程序進(jìn)行性能剖析.............................................................................72 3.2.1 測量PHP 應(yīng)用程序.............................................................................74 3.3 剖析MySQL 查詢.........................................................................................77 3.3.1 剖析服務(wù)器負(fù)載..................................................................................77 3.3.2 剖析單條查詢......................................................................................81 3.3.3 使用性能剖析......................................................................................87 3.4 診斷間歇性問題............................................................................................88 3.4.1 單條查詢問題還是服務(wù)器問題............................................................89 3.4.2 捕獲診斷數(shù)據(jù) ......................................................................................93 3.4.3 一個診斷案例......................................................................................98 3.5 其他剖析工具.............................................................................................. 106 3.5.1 使用USER_STATISTICS 表 ............................................................. 106 3.5.2 使用strace ......................................................................................... 107 3.6 總結(jié) ............................................................................................................. 108 第4 章 Schema 與數(shù)據(jù)類型優(yōu)化 ................................ 111 4.1 選擇優(yōu)化的數(shù)據(jù)類型.................................................................................. 111 4.1.1 整數(shù)類型........................................................................................... 113 4.1.2 實數(shù)類型........................................................................................... 113 4.1.3 字符串類型........................................................................................ 114 4.1.4 日期和時間類型................................................................................ 121 4.1.5 位數(shù)據(jù)類型........................................................................................ 123 4.1.6 選擇標(biāo)識符（identifier）.................................................................... 125 4.1.7 特殊類型數(shù)據(jù).................................................................................... 127 4.2 MySQL schema 設(shè)計中的陷阱.................................................................... 127 4.3 范式和反范式.............................................................................................. 129 4.3.1 范式的優(yōu)點(diǎn)和缺點(diǎn)............................................................................. 130 4.3.2 反范式的優(yōu)點(diǎn)和缺點(diǎn)......................................................................... 130 4.3.3 混用范式化和反范式化..................................................................... 131 4.4 緩存表和匯總表.......................................................................................... 132 4.4.1 物化視圖........................................................................................... 134 4.4.2 計數(shù)器表........................................................................................... 135 4.5 加快ALTER TABLE 操作的速度 ............................................................... 136 4.5.1 只修改.frm 文件............................................................................... 137 4.5.2 快速創(chuàng)建MyISAM 索引 .................................................................... 139 4.6 總結(jié)............................................................................................................. 140 第5 章 創(chuàng)建高性能的索引.......................................... 141 5.1 索引基礎(chǔ)..................................................................................................... 141 5.1.1 索引的類型........................................................................................ 142 5.2 索引的優(yōu)點(diǎn)................................................................................................. 152 5.3 高性能的索引策略...................................................................................... 153 5.3.1 獨(dú)立的列........................................................................................... 153 5.3.2 前綴索引和索引選擇性 ..................................................................... 153 5.3.3 多列索引........................................................................................... 157 5.3.4 選擇合適的索引列順序..................................................................... 159 5.3.5 聚簇索引........................................................................................... 162 5.3.6 覆蓋索引........................................................................................... 171 5.3.7 使用索引掃描來做排序 ..................................................................... 175 5.3.8 壓縮（前綴壓縮）索引..................................................................... 177 5.3.9 冗余和重復(fù)索引................................................................................ 178 5.3.10 未使用的索引.................................................................................. 181 5.3.11 索引和鎖.......................................................................................... 181 5.4 索引案例學(xué)習(xí).............................................................................................. 183 5.4.1 支持多種過濾條件............................................................................. 183 5.4.2 避免多個范圍條件............................................................................. 185 5.4.3 優(yōu)化排序........................................................................................... 186 5.5 維護(hù)索引和表.............................................................................................. 187 5.5.1 找到并修復(fù)損壞的表......................................................................... 187 5.5.2 更新索引統(tǒng)計信息............................................................................. 188 5.5.3 減少索引和數(shù)據(jù)的碎片..................................................................... 190 5.6 總結(jié) ............................................................................................................. 192 第6 章 查詢性能優(yōu)化................................................. 195 6.1 為什么查詢速度會慢.................................................................................. 195 6.2 慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問....................................................................... 196 6.2.1 是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù).................................................. 196 6.2.2 MySQL 是否在掃描額外的記錄........................................................ 198 6.3 重構(gòu)查詢的方式.......................................................................................... 201 6.3.1 一個復(fù)雜查詢還是多個簡單查詢...................................................... 201 6.3.2 切分查詢........................................................................................... 202 6.3.3 分解關(guān)聯(lián)查詢.................................................................................... 203 6.4 查詢執(zhí)行的基礎(chǔ).......................................................................................... 204 6.4.1 MySQL 客戶端/ 服務(wù)器通信協(xié)議 ..................................................... 205 6.4.2 查詢緩存........................................................................................... 208 6.4.3 查詢優(yōu)化處理.................................................................................... 208 6.4.4 查詢執(zhí)行引擎.................................................................................... 222 6.4.5 返回結(jié)果給客戶端............................................................................. 223 6.5 MySQL 查詢優(yōu)化器的局限性..................................................................... 223 6.5.1 關(guān)聯(lián)子查詢........................................................................................ 223 6.5.2 UNION 的限制.................................................................................. 228 6.5.3 索引合并優(yōu)化.................................................................................... 228 6.5.4 等值傳遞........................................................................................... 229 6.5.5 并行執(zhí)行 ........................................................................................... 229 6.5.6 哈希關(guān)聯(lián)........................................................................................... 229 6.5.7 松散索引掃描.................................................................................... 229 6.5.8 最大值和最小值優(yōu)化......................................................................... 231 6.5.9 在同一個表上查詢和更新................................................................. 232 6.6 查詢優(yōu)化器的提示（hint） ........................................................................... 232 6.7 優(yōu)化特定類型的查詢.................................................................................. 236 6.7.1 優(yōu)化COUNT() 查詢 .......................................................................... 236 6.7.2 優(yōu)化關(guān)聯(lián)查詢.................................................................................... 239 6.7.3 優(yōu)化子查詢........................................................................................ 239 6.7.4 優(yōu)化GROUP BY 和DISTINCT ........................................................ 239 6.7.5 優(yōu)化LIMIT 分頁............................................................................... 241 6.7.6 優(yōu)化SQL_CALC_FOUND_ROWS ................................................... 243 6.7.7 優(yōu)化UNION 查詢.............................................................................. 243 6.7.8 靜態(tài)查詢分析.................................................................................... 244 6.7.9 使用用戶自定義變量......................................................................... 244 6.8 案例學(xué)習(xí)..................................................................................................... 251 6.8.1 使用MySQL 構(gòu)建一個隊列表 ........................................................... 251 6.8.2 計算兩點(diǎn)之間的距離......................................................................... 254 6.8.3 使用用戶自定義函數(shù)......................................................................... 257 6.9 總結(jié)............................................................................................................. 258 第7 章 MySQL 高級特性 ........................................... 259 7.1 分區(qū)表......................................................................................................... 259 7.1.1 分區(qū)表的原理.................................................................................... 260 7.1.2 分區(qū)表的類型.................................................................................... 261 7.1.3 如何使用分區(qū)表................................................................................ 262 7.1.4 什么情況下會出問題......................................................................... 263 7.1.5 查詢優(yōu)化........................................................................................... 266 7.1.6 合并表............................................................................................... 267 7.2 視圖............................................................................................................. 270 7.2.1 可更新視圖........................................................................................ 272 7.2.2 視圖對性能的影響............................................................................. 273 7.2.3 視圖的限制........................................................................................ 274 7.3 外鍵約束..................................................................................................... 275 7.4 在MySQL 內(nèi)部存儲代碼............................................................................ 276 7.4.1 存儲過程和函數(shù)................................................................................ 278 7.4.2 觸發(fā)器............................................................................................... 279 7.4.3 事件................................................................................................... 281 7.4.4 在存儲程序中保留注釋..................................................................... 283 7.5 游標(biāo)............................................................................................................. 283 7.6 綁定變量..................................................................................................... 284 7.6.1 綁定變量的優(yōu)化................................................................................ 286 7.6.2 SQL 接口的綁定變量........................................................................ 286 7.6.3 綁定變量的限制................................................................................ 288 7.7 用戶自定義函數(shù).......................................................................................... 289 7.8 插件............................................................................................................. 290 7.9 字符集和校對.............................................................................................. 291 7.9.1 MySQL 如何使用字符集................................................................... 292 7.9.2 選擇字符集和校對規(guī)則..................................................................... 295 7.9.3 字符集和校對規(guī)則如何影響查詢...................................................... 296 7.10 全文索引................................................................................................... 299 7.10.1 自然語言的全文索引....................................................................... 300 7.10.2 布爾全文索引.................................................................................. 302 7.10.3 MySQL5.1 中全文索引的變化......................................................... 303 7.10.4 全文索引的限制和替代方案............................................................ 304 7.10.5 全文索引的配置和優(yōu)化................................................................... 306 7.11 分布式（XA）事務(wù) ................................................................................... 307 7.11.1 內(nèi)部XA 事務(wù).................................................................................. 307 7.11.2 外部XA 事務(wù) .................................................................................. 308 7.12 查詢緩存................................................................................................... 309 7.12.1 MySQL 如何判斷緩存命中............................................................. 309 7.12.2 查詢緩存如何使用內(nèi)存 ................................................................... 311 7.12.3 什么情況下查詢緩存能發(fā)揮作用.................................................... 313 7.12.4 如何配置和維護(hù)查詢緩存............................................................... 316 7.12.5 InnoDB 和查詢緩存......................................................................... 319 7.12.6 通用查詢緩存優(yōu)化........................................................................... 320 7.12.7 查詢緩存的替代方案....................................................................... 321 7.13 總結(jié)........................................................................................................... 321 第8 章 優(yōu)化服務(wù)器設(shè)置............................................. 325 8.1 MySQL 配置的工作原理............................................................................. 326 8.1.1 語法、作用域和動態(tài)性..................................................................... 327 8.1.2 設(shè)置變量的副作用............................................................................. 328 8.1.3 入門 ................................................................................................... 331 8.1.4 通過基準(zhǔn)測試迭代優(yōu)化..................................................................... 332 8.2 什么不該做................................................................................................. 333 8.3 創(chuàng)建MySQL 配置文件............................................................................... 335 8.3.1 檢查MySQL 服務(wù)器狀態(tài)變量 ........................................................... 339 8.4 配置內(nèi)存使用.............................................................................................. 340 8.4.1 MySQL 可以使用多少內(nèi)存？............................................................ 340 8.4.2 每個連接需要的內(nèi)存......................................................................... 341 8.4.3 為操作系統(tǒng)保留內(nèi)存......................................................................... 341 8.4.4 為緩存分配內(nèi)存................................................................................ 342 8.4.5 InnoDB 緩沖池（Buffer Pool） .......................................................... 342 8.4.6 MyISAM 鍵緩存（Key Caches） ........................................................ 344 8.4.7 線程緩存........................................................................................... 346 8.4.8 表緩存（Table Cache） ....................................................................... 347 8.4.9 InnoDB 數(shù)據(jù)字典（Data Dictionary）................................................ 348 8.5 配置MySQL 的I/O 行為............................................................................ 349 8.5.1 InnoDB I/O 配置................................................................................ 349 8.5.2 MyISAM 的I/O 配置......................................................................... 361 8.6 配置MySQL 并發(fā) ....................................................................................... 363 8.6.1 InnoDB 并發(fā)配置.............................................................................. 364 8.6.2 MyISAM 并發(fā)配置............................................................................ 365 8.7 基于工作負(fù)載的配置.................................................................................. 366 8.7.1 優(yōu)化BLOB 和TEXT 的場景............................................................. 367 8.7.2 優(yōu)化排序（Filesorts）......................................................................... 368 8.8 完成基本配置.............................................................................................. 369 8.9 安全和穩(wěn)定的設(shè)置...................................................................................... 371 8.10 高級InnoDB 設(shè)置..................................................................................... 374 8.11 總結(jié)........................................................................................................... 376 第9 章 操作系統(tǒng)和硬件優(yōu)化....................................... 377 9.1 什么限制了MySQL 的性能........................................................................ 377 9.2 如何為MySQL 選擇CPU ........................................................................... 378 9.2.1 哪個更好：更快的CPU 還是更多的CPU......................................... 378 9.2.2 CPU 架構(gòu)........................................................................................... 380 9.2.3 擴(kuò)展到多個CPU 和核心 ................................................................... 381 9.3 平衡內(nèi)存和磁盤資源 .................................................................................. 382 9.3.1 隨機(jī)I/O 和順序I/O ........................................................................... 383 9.3.2 緩存，讀和寫.................................................................................... 384 9.3.3 工作集是什么.................................................................................... 385 9.3.4 找到有效的內(nèi)存/ 磁盤比例 .............................................................. 386 9.3.5 選擇硬盤........................................................................................... 387 9.4 固態(tài)存儲..................................................................................................... 389 9.4.1 閃存概述........................................................................................... 390 9.4.2 閃存技術(shù)........................................................................................... 391 9.4.3 閃存的基準(zhǔn)測試................................................................................ 392 9.4.4 固態(tài)硬盤驅(qū)動器（SSD