高性能MySQL（第3版）

推荐序....................................................................... xxiii 前言............................................................................xxv 第1 章 MySQL 架构与历史 ............................................ 1 1.1 MySQL 逻辑架构............................................................................................1 1.1.1 连接管理与安全性.................................................................................2 1.1.2 优化与执行............................................................................................3 1.2 并发控制.........................................................................................................3 1.2.1 读写锁...................................................................................................4 1.2.2 锁粒度...................................................................................................4 1.3 事务.................................................................................................................6 1.3.1 隔离级别...............................................................................................8 1.3.2 死锁.......................................................................................................9 1.3.3 事务日志.............................................................................................10 1.3.4 MySQL 中的事务................................................................................10 1.4 多版本并发控制 ............................................................................................12 1.5 MySQL 的存储引擎......................................................................................13 1.5.1 InnoDB 存储引擎................................................................................16 1.5.2 MyISAM 存储引擎..............................................................................17 1.5.3 MySQL 内建的其他存储引擎..............................................................19 1.5.4 第三方存储引擎..................................................................................22 1.5.5 选择合适的引擎..................................................................................24 1.5.6 转换表的引擎......................................................................................27 1.6 MySQL 时间线（Timeline）..........................................................................29 1.7 MySQL 的开发模式......................................................................................32 1.8 总结...............................................................................................................33 第2 章 MySQL 基准测试 ............................................. 35 2.1 为什么需要基准测试....................................................................................35 2.2 基准测试的策略 ............................................................................................37 2.2.1 测试何种指标......................................................................................38 2.3 基准测试方法................................................................................................40 2.3.1 设计和规划基准测试...........................................................................41 2.3.2 基准测试应该运行多长时间................................................................42 2.3.3 获取系统性能和状态 ...........................................................................43 2.3.4 获得准确的测试结果...........................................................................44 2.3.5 运行基准测试并分析结果...................................................................46 2.3.6 绘图的重要性......................................................................................47 2.4 基准测试工具................................................................................................49 2.4.1 集成式测试工具..................................................................................49 2.4.2 单组件式测试工具...............................................................................50 2.5 基准测试案例................................................................................................52 2.5.1 http_load ..............................................................................................53 2.5.2 MySQL 基准测试套件.........................................................................54 2.5.3 sysbench ..............................................................................................55 2.5.4 数据库测试套件中的dbt2 TPC-C 测试 ...............................................60 2.5.5 Percona 的TPCC-MySQL 测试工具 ....................................................63 2.6 总结 ...............................................................................................................65 第3 章 服务器性能剖析............................................... 67 3.1 性能优化简介................................................................................................67 3.1.1 通过性能剖析进行优化.......................................................................69 3.1.2 理解性能剖析......................................................................................71 3.2 对应用程序进行性能剖析.............................................................................72 3.2.1 测量PHP 应用程序.............................................................................74 3.3 剖析MySQL 查询.........................................................................................77 3.3.1 剖析服务器负载..................................................................................77 3.3.2 剖析单条查询......................................................................................81 3.3.3 使用性能剖析......................................................................................87 3.4 诊断间歇性问题............................................................................................88 3.4.1 单条查询问题还是服务器问题............................................................89 3.4.2 捕获诊断数据 ......................................................................................93 3.4.3 一个诊断案例......................................................................................98 3.5 其他剖析工具.............................................................................................. 106 3.5.1 使用USER_STATISTICS 表 ............................................................. 106 3.5.2 使用strace ......................................................................................... 107 3.6 总结 ............................................................................................................. 108 第4 章 Schema 与数据类型优化 ................................ 111 4.1 选择优化的数据类型.................................................................................. 111 4.1.1 整数类型........................................................................................... 113 4.1.2 实数类型........................................................................................... 113 4.1.3 字符串类型........................................................................................ 114 4.1.4 日期和时间类型................................................................................ 121 4.1.5 位数据类型........................................................................................ 123 4.1.6 选择标识符（identifier）.................................................................... 125 4.1.7 特殊类型数据.................................................................................... 127 4.2 MySQL schema 设计中的陷阱.................................................................... 127 4.3 范式和反范式.............................................................................................. 129 4.3.1 范式的优点和缺点............................................................................. 130 4.3.2 反范式的优点和缺点......................................................................... 130 4.3.3 混用范式化和反范式化..................................................................... 131 4.4 缓存表和汇总表.......................................................................................... 132 4.4.1 物化视图........................................................................................... 134 4.4.2 计数器表........................................................................................... 135 4.5 加快ALTER TABLE 操作的速度 ............................................................... 136 4.5.1 只修改.frm 文件............................................................................... 137 4.5.2 快速创建MyISAM 索引 .................................................................... 139 4.6 总结............................................................................................................. 140 第5 章 创建高性能的索引.......................................... 141 5.1 索引基础..................................................................................................... 141 5.1.1 索引的类型........................................................................................ 142 5.2 索引的优点................................................................................................. 152 5.3 高性能的索引策略...................................................................................... 153 5.3.1 独立的列........................................................................................... 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 冗余和重复索引................................................................................ 178 5.3.10 未使用的索引.................................................................................. 181 5.3.11 索引和锁.......................................................................................... 181 5.4 索引案例学习.............................................................................................. 183 5.4.1 支持多种过滤条件............................................................................. 183 5.4.2 避免多个范围条件............................................................................. 185 5.4.3 优化排序........................................................................................... 186 5.5 维护索引和表.............................................................................................. 187 5.5.1 找到并修复损坏的表......................................................................... 187 5.5.2 更新索引统计信息............................................................................. 188 5.5.3 减少索引和数据的碎片..................................................................... 190 5.6 总结 ............................................................................................................. 192 第6 章 查询性能优化................................................. 195 6.1 为什么查询速度会慢.................................................................................. 195 6.2 慢查询基础：优化数据访问....................................................................... 196 6.2.1 是否向数据库请求了不需要的数据.................................................. 196 6.2.2 MySQL 是否在扫描额外的记录........................................................ 198 6.3 重构查询的方式.......................................................................................... 201 6.3.1 一个复杂查询还是多个简单查询...................................................... 201 6.3.2 切分查询........................................................................................... 202 6.3.3 分解关联查询.................................................................................... 203 6.4 查询执行的基础.......................................................................................... 204 6.4.1 MySQL 客户端/ 服务器通信协议 ..................................................... 205 6.4.2 查询缓存........................................................................................... 208 6.4.3 查询优化处理.................................................................................... 208 6.4.4 查询执行引擎.................................................................................... 222 6.4.5 返回结果给客户端............................................................................. 223 6.5 MySQL 查询优化器的局限性..................................................................... 223 6.5.1 关联子查询........................................................................................ 223 6.5.2 UNION 的限制.................................................................................. 228 6.5.3 索引合并优化.................................................................................... 228 6.5.4 等值传递........................................................................................... 229 6.5.5 并行执行 ........................................................................................... 229 6.5.6 哈希关联........................................................................................... 229 6.5.7 松散索引扫描.................................................................................... 229 6.5.8 最大值和最小值优化......................................................................... 231 6.5.9 在同一个表上查询和更新................................................................. 232 6.6 查询优化器的提示（hint） ........................................................................... 232 6.7 优化特定类型的查询.................................................................................. 236 6.7.1 优化COUNT() 查询 .......................................................................... 236 6.7.2 优化关联查询.................................................................................... 239 6.7.3 优化子查询........................................................................................ 239 6.7.4 优化GROUP BY 和DISTINCT ........................................................ 239 6.7.5 优化LIMIT 分页............................................................................... 241 6.7.6 优化SQL_CALC_FOUND_ROWS ................................................... 243 6.7.7 优化UNION 查询.............................................................................. 243 6.7.8 静态查询分析.................................................................................... 244 6.7.9 使用用户自定义变量......................................................................... 244 6.8 案例学习..................................................................................................... 251 6.8.1 使用MySQL 构建一个队列表 ........................................................... 251 6.8.2 计算两点之间的距离......................................................................... 254 6.8.3 使用用户自定义函数......................................................................... 257 6.9 总结............................................................................................................. 258 第7 章 MySQL 高级特性 ........................................... 259 7.1 分区表......................................................................................................... 259 7.1.1 分区表的原理.................................................................................... 260 7.1.2 分区表的类型.................................................................................... 261 7.1.3 如何使用分区表................................................................................ 262 7.1.4 什么情况下会出问题......................................................................... 263 7.1.5 查询优化........................................................................................... 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 内部存储代码............................................................................ 276 7.4.1 存储过程和函数................................................................................ 278 7.4.2 触发器............................................................................................... 279 7.4.3 事件................................................................................................... 281 7.4.4 在存储程序中保留注释..................................................................... 283 7.5 游标............................................................................................................. 283 7.6 绑定变量..................................................................................................... 284 7.6.1 绑定变量的优化................................................................................ 286 7.6.2 SQL 接口的绑定变量........................................................................ 286 7.6.3 绑定变量的限制................................................................................ 288 7.7 用户自定义函数.......................................................................................... 289 7.8 插件............................................................................................................. 290 7.9 字符集和校对.............................................................................................. 291 7.9.1 MySQL 如何使用字符集................................................................... 292 7.9.2 选择字符集和校对规则..................................................................... 295 7.9.3 字符集和校对规则如何影响查询...................................................... 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 全文索引的配置和优化................................................................... 306 7.11 分布式（XA）事务 ................................................................................... 307 7.11.1 内部XA 事务.................................................................................. 307 7.11.2 外部XA 事务 .................................................................................. 308 7.12 查询缓存................................................................................................... 309 7.12.1 MySQL 如何判断缓存命中............................................................. 309 7.12.2 查询缓存如何使用内存 ................................................................... 311 7.12.3 什么情况下查询缓存能发挥作用.................................................... 313 7.12.4 如何配置和维护查询缓存............................................................... 316 7.12.5 InnoDB 和查询缓存......................................................................... 319 7.12.6 通用查询缓存优化........................................................................... 320 7.12.7 查询缓存的替代方案....................................................................... 321 7.13 总结........................................................................................................... 321 第8 章 优化服务器设置............................................. 325 8.1 MySQL 配置的工作原理............................................................................. 326 8.1.1 语法、作用域和动态性..................................................................... 327 8.1.2 设置变量的副作用............................................................................. 328 8.1.3 入门 ................................................................................................... 331 8.1.4 通过基准测试迭代优化..................................................................... 332 8.2 什么不该做................................................................................................. 333 8.3 创建MySQL 配置文件............................................................................... 335 8.3.1 检查MySQL 服务器状态变量 ........................................................... 339 8.4 配置内存使用.............................................................................................. 340 8.4.1 MySQL 可以使用多少内存？............................................................ 340 8.4.2 每个连接需要的内存......................................................................... 341 8.4.3 为操作系统保留内存......................................................................... 341 8.4.4 为缓存分配内存................................................................................ 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 数据字典（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 并发 ....................................................................................... 363 8.6.1 InnoDB 并发配置.............................................................................. 364 8.6.2 MyISAM 并发配置............................................................................ 365 8.7 基于工作负载的配置.................................................................................. 366 8.7.1 优化BLOB 和TEXT 的场景............................................................. 367 8.7.2 优化排序（Filesorts）......................................................................... 368 8.8 完成基本配置.............................................................................................. 369 8.9 安全和稳定的设置...................................................................................... 371 8.10 高级InnoDB 设置..................................................................................... 374 8.11 总结........................................................................................................... 376 第9 章 操作系统和硬件优化....................................... 377 9.1 什么限制了MySQL 的性能........................................................................ 377 9.2 如何为MySQL 选择CPU ........................................................................... 378 9.2.1 哪个更好：更快的CPU 还是更多的CPU......................................... 378 9.2.2 CPU 架构........................................................................................... 380 9.2.3 扩展到多个CPU 和核心 ................................................................... 381 9.3 平衡内存和磁盘资源 .................................................................................. 382 9.3.1 随机I/O 和顺序I/O ........................................................................... 383 9.3.2 缓存，读和写.................................................................................... 384 9.3.3 工作集是什么.................................................................................... 385 9.3.4 找到有效的内存/ 磁盘比例 .............................................................. 386 9.3.5 选择硬盘........................................................................................... 387 9.4 固态存储..................................................................................................... 389 9.4.1 闪存概述........................................................................................... 390 9.4.2 闪存技术........................................................................................... 391 9.4.3 闪存的基准测试................................................................................ 392 9.4.4 固态硬盘驱动器（SSD