高性能mysql知识总结大全

高性能mysql全面总结知识

vii
目录
Contents
推荐序… I
前言… III
第 1 章 MySQL 架构… 1
1.1 MySQL 的逻辑架构 … 1
1.2 并发控制 … 3
1.3 事务… 4
1.4 并发控制多版本 … 10
1.5 MySQL 的存储引擎 … 11
第 2 章 寻找瓶颈：基准测试和性能分析… 25
2.1 为什么要进行基准测试？ … 25
2.2 基准测试策略 … 26
2.3 基准测试工具 … 32
2.4 基准测试样例 … 34
2.5 性能分析（Profiling） … 43
2.6 分析操作系统 … 60
第 3 章 结构优化和索引… 63
3.1 选择优化的数据类型 … 63
3.2 索引基础知识 … 74
3.3 高性能索引策略 … 83
3.4 索引实例研究 … 102
3.5 索引和表维护 … 105
3.6 正则化和非正则化 … 108
3.7 加速 ALTER TABLE … 113
3.8 说明存储引擎 … 115
第 4 章 查询性能优化…118
4.1 基本原则：优化数据访问… 118
4.2 重构查询方法 … 122
4.3 查询执行基础知识 … 124
4.4 MySQL 查询优化器的限制 … 139
4.5 优化特定类型的查询 … 146
4.6 查询优化提示 … 151
4.7 用户定义变量 … 154
第 5 章 MySQL 高级特性… 159
5.1 MySQL 查询缓存 … 159
5.2 在 MySQL 中存储代码 … 168
5.3 游标… 173
5.4 准备语句 … 174
5.5 用户定制函数 … 177
5.6 视图… 179
5.7 字符集和排序规则 … 182
5.8 全文搜索 … 188
5.9 外键约束 … 194
5.10 合并表和分区 … 194
5.11 分布式（XA）事务… 201
第 6 章 优化服务器设置… 203
6.1 配置基础知识 … 203
6.2 一般调优原则 … 207
6.3 MySQL I/O 调优… 214
6.4 MySQL 并发调优 … 224
6.5 基于工作负荷调整 … 226
6.6 每连接（Per-Connection）设置调优… 231
第 7 章 操作系统和硬件优化… 232
7.1 什么限制了 MySQL 的性能 … 232
7.2 如何为 MySQL 选择 CPU… 233
7.3 平衡内存和磁盘资源 … 235
7.4 从服务器中选择硬件 … 240
7.5 RAID 性能优化… 240
7.6 附加存储区域网络和网络… 246
7.7 使用多个磁盘卷 … 247
7.8 网络配置 … 248
7.9 选择操作系统 … 250
7.10 选择文件系统 … 250
7.11 线程处理 … 252
7.12 交换… 252
7.13 操作系统状态 … 254
viii │ 目录
第 8 章 复制… 259
8.1 复制概述 … 259
8.2 创建复制 … 262
8.3 揭示复制的真相 … 268
8.4 复制拓扑 … 273
8.5 复制和容量规划 … 284
8.6 复制管理和维护 … 285
8.7 复制问题和解决方案 … 292
8.8 复制有多快 … 305
8.9 MySQL 复制的未来 … 307
第 9 章 伸缩性和高可用性 … 308
9.1 术语… 308
9.2 MySQL 的伸缩性 … 310
9.3 负载平衡 … 328
9.4 高可用性 … 336
第 10 章 优化应用层面… 344
10.1 应用程序性能概述 … 344
10.2 Web 服务器问题… 346
10.3 缓存… 349
10.4 扩展 MySQL … 354
10.5 可替代的 MySQL … 354
第 11 章 备份与还原… 356
11.1 概况 … 356
11.2 权衡事项 … 360
11.3 管理和备份二进制日志… 367
11.4 数据备份 … 369
11.5 还原备份 … 377
11.6 备份和恢复的速度… 386
11.7 备份工具 … 387
11.8 脚本化备份 … 392
第 12 章 安全… 395
12.1 术语… 395
12.2 账户基础知识 … 396
12.3 操作系统安全 … 411
12.4 网络安全 … 412
12.5 数据加密 … 418
12.6 在 Chroot 环境里使用 MySQL … 421
目录 │ ix
第 13 章 MySQL 服务器状态… 423
13.1 系统变量 … 423
13.2 SHOW STATUS … 423
13.3 SHOW INNODB STATUS… 429
13.4 SHOW PROCESSLIST… 440
13.5 SHOW MUTEX STATUS … 441
13.6 复制的状态 … 442
13.7 INFORMATION_SCHEMA … 442
第 14 章 用于高性能 MySQL 的工具… 444
14.1 带界面的工具 … 444
14.2 监控工具 … 446
14.3 分析工具 … 453
14.4 MySQL 的辅助工具 … 455
14.5 更多信息来源 … 458
附录 A 大文件传输… 459
附录 B 使用 EXPLAIN… 463
附录 C 在 MySQL 里使用 Sphinx … 476
附录 D 锁的调试 … 497
索引… 505
豆瓣主页：http://www.douban.com/subject/4241826/
互动网热卖链接：http://www.china-pub.com/196341
x │ 目录
I
推荐序
Foreword
我认识 Peter、Vadim 和 Arjen 很长一段时间以来，他们一直在自己的项目中使用 MySQL 和为各类
优化高标准客户 MySQL 服务器的历史。另一方面，Baron 为增强 MySQL 该功能编写了许多客户端软件。
作者们的专业背景清晰地反映在了彻底重写《High Performance MySQL: Optimizations, Replication，Backups，
and More》在第二版的工作中。这本书不仅告诉你如何优化工作，以便比以前更好地使用它 MySQL，作者们还做
做了很多额外的工作，亲自准备了基准测试，并发布了结果来证明他们的观点。这些信息允许读者使用
了解很多有价值的东西 MySQL 内部工作机制-在其他书籍中很难获得；同样，这些信息也可以帮助读者避免
未来会导致性能不佳的错误。
我不仅要和刚接触 MySQL 服务器正准备编写第一个 MySQL 应用初学者推荐这本书，并向有经验的初学者推荐
用户推荐这本书，他们已经基于 MySQL 应用程序已经做了一些优化工作，现在需要朝着这个方向前进
小步”。
——Michael Widenius
2008 年 3 月
III
前言
Preface
对于这本书，我们脑子里有几个目标。大部分都是因为我们一直想在书架上找一本书却找不到。
神话般的完美 MySQL 书籍和其他目标来自与那些想要分享我们的经验的人 MySQL 在他们的环境中使用
户。
我们不希望这本书只是一本书 SQL 入门书，不想让书名随意使用一些时限词开始或结束(例如……
只需 30 天”，“7 天内提高……)，也不想说服读者什么。最重要的是，我们希望这本书能帮助你提高技能
个层次，用 MySQL 构建一个快速可用的系统——它可以回答这样的问题:我怎么能建立一个 MySQL
服务器集群，它可以处理数百万的要求，即使有几个服务器停机，它仍然可以提供正常的服务？
写这本书的重点不仅仅是迎合 MySQL 应用开发人员的需求也在于满足 MySQL 管理员的严格要求，
无论开发人员和用户如何辗转反侧，管理员都需要将服务器挂在网上正常运行。正如前面提到的，我们假设你已经有了
一些 MySQL 理想的是你读过一本书 MySQL 方面的入门书。我们同样也假定你具备一
一些常用的系统管理、网络和 Unix 风格操作系统等方面的经验。
第二版修展后的第二版对第一版的所有主题进行了更深入的解释，并增加了一些新的主题。这也是部分
反映了自本书首次出版后，MySQL 世界的一些变化：MySQL 现在它已经成为软件中更大、更复杂的部分
分。与其重要性一样，其普及度也有所提高：MySQL 社区变得越来越大，越来越多的大企业把 MySQL 应用到他
在我们的关键业务系统中。自本书第一版发布以来，MySQL 被广泛认可为企业级应用(注) 1)。人也越来越多
越多地把 MySQL 在互联网应用中，如果出现故障和其他问题，这些应用程序将无法被掩盖或容忍。
作为我们努力的结果，第二版的重点与第一版略有不同。我们强调可用性和准确性，就像强调性能一样，
因为这部分是我们自己的 MySQL 用于那些操作量巨大的业务系统。 Web 应用也有个人经验，
MySQL 在这方面越来越受欢迎。第二版将讨论 MySQL 周围扩展的世界，世界在第一版编写
时还不存在。
这本书是如何组织的
How This Book Is Organized
我们把许多复杂的主题放在一本书里所以，在这里我们要解释一下它们的编排次序，使读者能更易于学习它
们。

注 1：我们觉得这段话更像是市场营销的说辞，但是，它大概传达了这样一个意思：MySQL 对于许多人而言显得很
重要了。
内容广泛的概述
A Broad Overview
第 1 章，MySQL 架构，用于讲述基础知识——这些知识在你做更深入挖掘之前必须加以熟悉。你需要在高效利
用 MySQL 前理解整个框架是如何被组织起来的。这一章解释了 MySQL 的架构和它存储引擎的关键方面。如
果你还不熟悉关系数据库的一些基本概念及事务，它就能帮你更快地进入角色。如果本书就是你的 MySQL 入
门书，这一章也非常有用，不过，你最好还是事先熟悉了另外一种数据库，例如 Oracle。
构建一个坚实的基础
Building a Solid Foundation
接下来的 4 章涉及了你在使用 MySQL 时会几次三番来查阅的资料。
第 2 章，寻找瓶颈：基准测试与性能分析，讨论了基准测试和获取系统概况的基础。它们决定了你的系统能处
理哪一类型的工作负荷、执行某些任务时它能运行得多快等。你会希望在做重要更改的前后都能对你的应用做
一次基准测试，这样就可以判断出这些更改产生了多大的效果。有些看似正面的更改在真实世界的负载压力下
可能会变成负面的影响，除非你能精确地对其进行衡量，否则你是永远都不会知道到底是什么导致糟糕的系统
性能。
第 3 章，架构优化和索引，我们会介绍各数据类型的细微差别、表的设计和索引的创建。一个设计良好的数据
库能有助于 MySQL 获得更佳的性能表现。在接下来的那些章节里，我们讲到的很多内容的关键点都在于你的
应用是怎么使用 MySQL 索引的。深刻认识索引及如何巧妙地运用它们是高效使用 MySQL 的核心所在，所以，
你可能会经常地回过来重新阅读这一章。
第 4 章，查询性能优化，解释了 MySQL 是怎样执行查询的，以及怎么才能利用查询优化器的能力。深入领会
查询优化器的工作方法能帮你在编写查询时创造奇迹，也能帮你更好地理解索引（索引和查询优化的次序就像
“先有鸡还是先有蛋”的问题，读完第 4 章后再回过去读第 3 章可能会让你受益匪浅。）该一章里还特别展示了
那些常见的查询示例，用来说明 MySQL 所擅长的是哪方面的工作，怎么把查询转换成能够利用查询优化器强
大能力的形式。
为了更好地做到这一点，我们已经讲述过对任何数据库都适用的一些基本概念：表、索引、数据和查询。第 5
章，MySQL 高级特性，将在上述基础之上再进一步，向你展示 MySQL 内部那些更高层次的框架是如何运作的。
我们会介绍查询缓存、存储过程、触发器、字符集等内容。MySQL 实现这些功能特性的方法跟其他数据库有点
不一样，因此，对这些特性的深入理解能够帮你创造一个性能优化的新机会，这在以前你是不会想到的。
调优你的应用
Tuning Your Application
接下来的两章讨论如何修改你的基于 MySQL 的应用，使它能在性能上得到提升。
第 6 章，优化服务器设置，我们讨论的是如何调优 MySQL，使它能在最大程度上让硬件特性为你的特定应用服
务。第 7 章，操作系统和硬件优化，我们解释了如何充分利用你的操作系统和硬件配置，同时为大规模应用提
供了某些能提高性能的硬件配置建议。
IV │ 前言
配置更改之后的向上扩展
Scaling Upward After Making Changes
一台服务器往往是不够用的。第 8 章，复制，介绍如何把将数据自动地复制到多台服务器上。第 9 章，“伸缩性
与高可用性”，讲述如何将伸缩性、负载平衡和高可用性综合起来运用，为应用伸展到你所需要的程度提供基础
性工作。
当应用运行在一个大规模的 MySQL 后端之上时，它本身就蕴含了意义非凡的优化机会。设计一个大型应用有
更好的途径，也有更坏的途径，但这不是本书的着重点，我们不希望你把所有的时间都专注于 MySQL 之上。
第 10 章，“应用层面的优化”，帮助你发现那些悬挂在靠近地面枝头上的柿子，特别是对于 Web 应用。
增强应用的可靠性
Making Your Application Reliable
哪怕是世界上设计得最好、伸缩性最强的架构，如果它不能在掉电、恶意攻击、程序 Bug、程序员的过失，以 及其他灾难中幸存下来，那它也算不上是好的架构。
第 11 章，“备份和还原”，我们会讨论到不同的 MySQL 数据库备份和还原策略。这些策略都有助于在系统遭受
到不可避免的硬件错误时最小化故障停机时间，遭遇到各种灾难时确保你的数据安全。
第 12 章，“安全”，能让你对运行 MySQL 服务器涉及的安全因素有深入的认识。最重要的是，我们给你提供了
很多建议，防止来自外部的攻击威胁你苦心优化、配置过的服务器。我们还会指出几个很少见的暴露出数据库
安全问题的地方，并展示不同的实施方法的好处及对性能的影响。通常情况下，就性能方面而言，保持安全策
略简单化是值得的。
其他有用的主题
Miscellaneous Useful Topics
最后的几个章节和附录里，我们深入研究了几个既不“适合”放入前面任何一个章节中，又被多个章节反复引
用的内容，它们值得特别关注。
第 13 章，“MySQL 服务器的状态”，展示的是如何检查 MySQL 服务器运行情况。知道如何获取服务器的状态
信息很重要，知道那些信息包含的意思更加重要。我们针对 SHOW INNODB STATUS 作了特别具体的讲解，它
能提供关于 InnoDB 事务存储引擎的更深层次的操作信息。
第 14 章，“用于高性能的 MySQL 工具”，介绍了一些能帮你更有效管理 MySQL 的工具。这些工具包括监控和
分析工具，以及能帮你编写查询语句的工具等。其中提到的 Maatkit 是由 Baron 创建的，它能够增强 MySQL 的
功能性，使你的数据库管理员的生活更加轻松。在该章里也演示了一个名叫 innotop 的程序，这个程序是 Baron
写的，其目的是提供一个易于使用的查看 MySQL 正在做什么的用户接口，它的功能与 Uninx 的 top 命令类似。
在调优 MySQL 各阶段里，你若要监控 MySQL 和它的存储引擎里发生的情况，它就是一个很有价值的工具。
附录 A，“大文件传输”，展示如何高效地把很大的文件从一个地方复制到另一个地方——这在大数据量管理时
肯定会用到。附录 B，“使用 EXPLAIN”，展示如何真正理解和使用那个重要的 EXPLAIN 命令。附录 C，“在
MySQL 里使用 Sphinx”是对 Sphinx 的一个介绍，这个高性能全文索引系统是对 MySQL 自有功能的一个补充。
前言 │ V
VI │ 前言
最后的附录 D，“锁的调试”，向你展示的是当几个查询在请求锁时相互妨碍时，该如何去破译其中的缘由。
软件的版本和有效性
Software Versions and Availability
MySQL 就像个移动的目标。在 Jeremey 写出第一版提纲之后的几年里，有大量的不同版本 MySQL 发布出来。
在本书第一版发行的时候，MySQL4.1 和 5.0 还都是 alpha 版，但如今它们已经作为正式产品很多年了，并成为
今天许多大型在线应用的后台支撑。当我们完成本书第二版时，MySQL 5.1 和 6.0 也处于这样的边缘。（MySQL
5.1 是 candidate 版，而 6.0 是 alpha 版。）
在本书里，我们没有依赖于某个特定的 MySQL 版本。相反，我们讲述的是基于真实世界里各版本 MySQL 的
更广阔的知识。本书涉及的核心版本是 MySQL 5.0，因为我们把它看作是“当前”版本。书中的大多数示例都
假定你运行的是 MySQL 5.0 的某个相对比较稳定的版本，例如 MySQL 5.0.40 或更新的。我们会特意标注出哪
些框架或功能在那些老版本里不存在，或者会出现在即将到来的 5.1 版本系列里。然而，明确的功能特性与版
本的对应关系只有在 MySQL 的文档里才能找到，所以，我们希望你在阅读本书的时候能够经常访问带有注解
的在线文档（http://dev.mysql.com/doc/）。
MySQL 另一个伟大之处在于它能运行在当今所有流行的平台上：Mac OS X、Windows、GNU/Linux、Solaris、
FreeBSD，只要你能想到的都行！但是，我们偏向于 GNU/Linux（注 2）和其他 Unix 风格的操作系统。Windows
用户可能会有所不同，例如，文件路径就会完全不一样。书中也会用到一些标准的 Unix 命令行功能，我们假定
你知道它们在 Windows 上的对应命令。（注 3）
Perl 也是 MySQL 在 Windows 上运行时的麻烦之一。MySQL 自带的几个很有用的辅助功能都是用 Perl 写的，
本书某一些章节里展示的 Perl 脚本就是构建更复杂工具的基础，像 Maatkit 也是用 Perl 写的。可是，Perl 并没
有包含在 Windows 里。为了能使用这些脚本，你需要访问 ActiveState 下载一个 Windows 版的 Perl，然后安装
一个必需的插件模块（DBI 和 DBD::mysql）好让 MySQL 能够访问到它。
本书使用的书写约定
Conventions Used in This Book
本书使用了以下这些书写约定：
等宽字体（Constant width） 用于表示代码、配置选项、数据库和表名、变量和它们的值、函数、模块、文件内容，以及命令的输出结
果。
等宽粗体（Constant width Bold）
用于表示命令或要用户自己输入的内容，也用于强调命令的输出结果里的某些内容。

注 2：为了避免混淆，当我们写到关于内核的内容时，就以 Linux 称呼；当我们讲到支持应用的整个操作系统架构时，
就以 GNU/Linux 称呼。
注 3：你可以在http://unxutils.sourceforge.net或http://gnuwin32.sourceforge.net.下载到与Windows兼容的Unix辅助工具。
这个图标表示提示、建议或一般性注解。
这个图标表示的是提醒或警告。
使用本书示例代码
Using Code Examples
本书的目的是帮你把事情做好。一般来说，你无需特地联系我们就可以在你的程序和文档里任意使用本书的代
码，除非你要把其中的关键代码以你的名义重新发布。举例来说，你的程序中使用到了书中的几段代码，不需
要获得许可；出售或发布 O’Reilly 的随书光盘，需要获得许可；引用本书内容和示例代码去解答一个问题，不
需要获得许可；把本书中大量代码合并到你的产品文档里时，需要获得许可。
本书的示例代码在 http://www.highperfmysql.com 上可以获取到，并经常会有更新。但是，我们不能保证会为所
有次要版本的 MySQL 更新和测试这些代码。
我们会感谢，但是不要求写上代码所属权的声明。这个所有权声明一般包括书名、作者、出版商和 ISBN，例如
“High Performance MySQL: Optimization, Backups, Replication, and More, Second Edition, by Baron Schwartz et al.
Copyright 2008 O’Reilly Media, Inc., 9780596101718.”
如果你觉得你对示例代码的使用超过了正当使用范围或如上所述的授权使用的范围了，请跟我们联系：
permissions@oreilly.com。
如何联系我们
How to Contact Us
我们已尽力核验本书所提供的信息，尽管如此，仍不能保证本书完全没有瑕疵，而网络世界的变化之快，也使
得本书永不过时的保证成为不可能。如果读者发现本书内容上的错误，不管是赘字、错字、语意不清，甚至是
技术错误，我们都竭诚虚心接受读者指教。如果您有任何问题，请按照以下的联系方式与我们联系。
奥莱理软件（北京）有限公司
北京市 西城区 西直门 南大街2号 成铭大厦C座807室
邮政编码：100080
网页：http://www.oreilly.com.cn
E-mail：info@mail.oreilly.com.cn
O’Reilly Media, Inc.
1005 Gravenstein Highway North
Sebastopol, CA 95472
800-998-9938 (in the United States or Canada)
707-829-0515 (international/local)
707-829-0104 (fax)
前言 │ VII
与本书有关的在线信息如下所示。
http://www.oreilly.com/catalog/9780596101718（原书）
http://www.oreilly.com.cn/book.php?bn=978-7-121-10245-5（中文版）
北京博文视点资讯有限公司（武汉分部）
湖北省 武汉市 洪山区 吴家湾 邮科院路特1号 湖北信息产业科技大厦1402室
邮政编码：430074
电话： (027)87690813 传真：(027)87690813转817
读者服务网页：http://bv.csdn.net
E-mail：
reader@broadview.com.cn（读者信箱）
bvtougao@gmail.com（投稿信箱）
本书第二版的致谢
Acknowledgments for the Second Edition
Sphinx 的开发者 Andrew Aksyonoff 编写了附录 C，“在 MySQL 里使用 Sphinx”。我们非常感谢他首次对此进行
深入讨论。
在本书编写的时候，我们也得到了来自于许多人的无私帮助，在这里我们不可能把他们都一一列举出来——我 们真地非常感谢 MySQL 社区和 MySQL AB 公司的每一个人。下面是对本书作出了直接贡献的人，如果我们遗
漏了任何一个人，还请原谅，他们是：Tobias Asplund、Igor Babaev、Pascal Borghino、Roland Bouman、Ronald
Bradford、Mark Callaghan、Jeremy Cole、Britt Crawfordh 和他的 HiveDB 项目、Vasil Dimov、Harrison Fisk、Florian
Haas、Dmitri Joukovski、Zmanda (感谢他为解释 LVM 快照配上的图表)、Alan Kasindorf、Sheeri Kritzer Cabral、
Marko Makela、Giuseppe Maxia、Paul McCullagh、B. Keith Murphy、Dhiren Patel、Sergey Petrunia、Alexander Rubin、
Paul Tuckfield、Heikki Tuuri 和 Michael“Monty”Widenius。
有一份特别的感谢要送给 O’Reilly 的编辑 Andy Oram 和助理编辑 Isabel Kunkle，以及审稿人 Rachel Wheeler，
同时也感谢 O’Reilly 团队里的其他成员。
来自 Baron
From Baron
我要感谢我的妻子 Lynn Rainville 和我们的小狗 Carbo。如果你也曾写过一本书，我确信你可体会到我有多么感
谢他们。我也非常感谢 Alan Rimm-Kaufman 和我在 Rimm-Kaufman 集团的同事们，在写书的过程中，他们给了
我支持和鼓励。我要感谢 Peter、Vadim 和 Arjen，是他们给了我这个机会让梦想成真。最后，我要感谢 Jeremy
和 Derek 为我们开了个好头。
VIII │ 前言
来自 Peter
From Peter
我从事 MySQL 性能和伸缩性方面的讲演、培训和咨询已经很多年了，我一直想把它们扩大到更多的受众，因
此，当 Andy Oram 邀请我加入本书编写中时，我感到非常兴奋。此前我没写过书，所以，我对所需要的时间和
精力都毫无把握。我们先谈到只对第一版做一些更新，以跟上 MySQL 最近的版本升级，但我们想把很多新素
材加到书里去，结果就几乎重写了整本书。
这本书是真正的团队合作的结晶。因为我忙于 Percona 的事情——我和 Vadim 的咨询公司，又因为英语并非我
的第一语言，所以我们有着不同的角色。我负责提供大纲和技术性内容，然后，我把素材都过一遍，在写作的
时候再对它进行修订和扩展。当 Arjen（MySQL 文档团队的前任负责人）加入之后，我们就开始勾划出整个提
纲。在 Baron 到来后，一切才真正开动起来，他能够以不可思议的速度编写出高质量的内容。Vadim 在深入检
查 MySQL 源代码和提供基准测试或其他探索来巩固我们的论点时发挥了很大的作用。
当我们编写这本书时，我们发现有越来越多的领域需要刨根问底。本书主题里的大多数，例如复制、查询优化、
InnoDB、架构和设计都可以分别轻易地写成一本书，因此，我们不得不在某一个阶段时停止，把余下的材料用
在将来可能要出的新版上、我们的博客上、我们的讲演上，以及我们的技术文章里。
本书的评审者给予了我们巨大的帮助，无论是来自 MySQL AB 公司内部的还是外部的，他们都是这个世界上最
顶级的 MySQL 专家，他们包括 MySQL 的创建者 Michael Widenius、InnoDB 的创建者 Heikki Tuuri、MySQL
优化器团队的负责人 Igor Babaev，以及其他人。
我还要感谢我的妻子 Katya Zaytseva、我的孩子 Ivan 和 Nadezhda，他们容许了我把家庭时间花在本书写作上。
我也要感谢 Percona 的雇员们，当我在公司里“人间蒸发”去写书的时候，是他们处理了日常的事务。当然，
我也要感谢 O’Reilly 和 Andy Oram 让这一切成为了可能。
来自 Vadim
From Vadim
我要感谢 Peter，能在本书中与他合作，我感到十分开心，期望在其他项目中能继续共事；我也要感谢 Baron，
他在本书写作过程中起了很大的作用；还有 Arjen，跟他一起工作非常好玩。我还要感谢我们的编辑 Andy Oram，
他抱着十二万分的耐心与我们一起工作。还要感谢 MySQL 团队，是他们创造了这个伟大的软件；我还要感谢
我们的客户给予我调优 MySQL 的机会。最后，我要特别感谢我的妻子 Valerie 及我们的儿子 Myroslav 和 Timur，
他们一直支持我，帮助我一步步前进。
来自 Arjen
From Arjen
我要感谢 Andy 的睿智、指导和耐心，感谢 Baron 中途加入到我们当中来，感谢 Peter 和 Vadim 坚实的背景信息
和基准测试。也要感谢 Jeremy 和 Derek 在第一版里打下的基础，在我的书上，Derek 题写着：“要诚实——这就
是我所有的要求。”
我也要感谢所有我在 MySQL AB 时的同事们，在那里我获得了关于本书主题的大多数知识。在此，我还要特别
提到 Monty，我一直认为他是令人自豪的 MySQL 之父，尽管他的公司如今已成为 SUN 公司的一部分。我要感
前言 │ IX
谢全球 MySQL 社区里的每一个人。
最后但同样重要的是，我要感谢我的女儿 Phoebe，在她尚年少的生活舞台上，不用关心什么叫 MySQL，也不
用考虑 Wiggles 所指的到底是何物。从某些方面来讲，无知就是福，它能给予我们一个全新的视角来看清生命
中真正重要的是什么。对于读者，祝愿你们的书架上又增添了一本有用的书，还有，不要忘记你的生活。
本书第一版的致谢
Acknowledgments for the First Edition
像这样一本书的写成离不开许许多多人的帮助。没有他们的无私援助，你手上的这本书可能仍然是我们显示器
屏幕四周的那一堆小贴纸。这是本书的一部分，在这里，我们可以感谢每一个曾经帮我们脱离困境的人，而无
须担心突然奏响的背景音乐催促我们闭上嘴巴赶快走掉——如同你在电视里看到的颁奖晚会那样。
如果没有编辑 Andy Oram 坚决的督促、请求、央求和支持，我们就无法完成这个项目。如果要找出本书最负责的
一个人，那就是 Andy。我们真地非常感激每周一次的唠唠叨叨的会议。
其实，Andy 也不是孤独的，在 O’Reilly 里，还有一批人参与了把那些小贴纸转换成一本已装订好的你正要阅读
的图书的工作，所以，我们也要感谢那些在生产、插画和销售环节的人们，感谢你们把本书合在一起。当然，
还要感谢 Tim O’Reilly，是他持久不变的承诺为广大的开源软件出版了一批行业里最好的文档。
最后，我们要把感谢给予那些同意审阅本书不同阶段版本，并告诉我们哪里有错误的人们：我们的评审者。他
们把 2003 年假期的一部分时间用在了审阅这些格式粗糙，充满了打字符号、误导性的语句和彻底的数学错误的
文本上。我们要感谢（排名不分先后次序）：Brian “Krow”Aker、Mark“JDBC”Matthews、Jeremy“the other
Jeremy”Cole、Mike“VBMySQL.com”Hillyer、Raymond“Rainman”De Roo、Jeffrey“Regex Master”Friedl、
Jason DeHaan、Dan Nelson、Steve“Unix Wiz”Friedl，最后还有 Kasia“Unix Girl”Trapszo。
来自 Jeremy
From Jeremy
我要再次感谢 Andy，是他同意接纳这个项目，并持续不断鞭策我们加入更多的章节内容。Derek 的帮助非常关
键，本书最后的 20%~30%内容都是他来完成的，这使得我们不再错失下一个目标日期。感谢他同意中途加入
进来，代替我只能零星爆发一下的生产力，完成了关于 XML 的繁琐工作、第 10 章、附录 C，以及我丢给他的
其他那些活儿。
我也要感谢我的父母，在多年以前他们就给我买了 Commodore 64 电脑，他们不仅在前 10 年里容忍了我那如同
一辈子漫长的对电子和计算机技术的沉迷，在之后还成为我不懈学习和探索的支持者。
接下来，我要感谢在过去几年里在 Yahoo!推广 MySQL 信仰时遇到的那一群人，跟他们共事，我感到非常愉快。
在本书的筹备阶段，Jeffrey Friedl 和 Ray Goldberger 给了我鼓励和反馈意见。在他们之后就是 Steve Morris、James
Harvey 和 Sergey Kolychev 容忍了我在 Yahoo! Finance MySQL 服务器上做着看似固定不变的实验，即使打扰到
了他们的重要工作。我也要感谢 Yahoo!的其他成员，是他们帮我发现了 MySQL 上的那些有趣的问题和解决方
法。还有，最重要地是要感谢他们对我有足够的信任和信念，让我把 MySQL 用在 Yahoo!'s 业务的重要和可见
的那一部分上。
X │ 前言
Adam Goodman，出版家和 Linux Magazine 的所有者，他帮助我轻装上阵开始为技术受众撰写文章，并在 2001
年后半年第一次出版了我的长篇 MySQL 文章。自那以后，他教授给我更多他所能认识到的关于编辑和出版的
技能，还鼓励我通过在杂志上开设月度专栏在这条路上继续走下去。谢谢你，Adam。
我要感谢 Monty 和 David 与这个世界分享 MySQL。说到 MySQL AB，也要感谢在那里的其他伟大的人们，是
他们鼓励我写成这本书：Kerry、Larry、Joe、Marten、Brian、Paul、Jeremy、Mark、Harrison、Matt 和团队的其
他那些人。他们真的非常棒！
我要感谢我 Weblog 的所有读者，是他们鼓励我撰写基于日常工作的非正式的 MySQL 及其他技术文章。最后但
同样重要的是，感谢 Goon Squad。
来自 Derek
From Derek
就像 Jeremy 一样，因为太多相同的原因，我也要感谢我的家庭。我要感谢我的父母，是他们不停地鼓动我去写
一本书，哪怕他们头脑中都没任何跟它相关的东西。我的祖父母给我上了两堂很有价值的课：美金的含义，以
及我跟电脑相爱有多深，他们还借钱给我去购买了我平生第一台电脑：Commodore VIC-20。
我万分感谢 Jeremy 邀请我加入他那旋风般的写作过山车（bookwriting roller coaster）中来。这是一个很棒的体
验，我希望将来还能跟他一起工作。
我要特别感谢 Raymond De Roo、Brian Wohlgemuth、David Calafrancesco、Tera Doty、Jay Rubin、Bill Catlan、
Anthony Howe、Mark O’Neal、George Montgomery、George Barber，以及其他无数耐心听我抱怨的人，我从他
们那里了解到我所努力讲述的是否能让门外汉也能理解，或者仅仅得到一个我迫切希望的笑脸。没有他们，这
本书可能也会写出来，但是，我几乎可以肯定我在这过程中会疯掉。
前言 │ XI
v
译者序
在进入 MySQL 世界之前，先照例介绍一下 MySQL 的历史（况且本书里也没提到这些）。
真正以 MySQL 为名的数据库是从 1994 年开始开发的，并于 1995 年第一次呈现在小范围的用户面前，它的开
发者刚好不是美国人，而是两个瑞典人 Michael Widenius 和 David Axmark。那时的 MySQL 还非常简陋，除了
在一个表上做一些 Insert、Update、Delete 和 Select 操作，恐怕没有更多的功能给用户使用。这种情况直到 2001
年左右发布 3.23 版的时候，才有了显著的进步——它支持大多数的基本 SQL 操作了，而且还集成了我们现在
熟识的 MyISAM 和 InnoDB 存储引擎。然后又是几年不断完善的过程，到了 2004 年 10 月，这个夯实基础的过
程到达了顶峰——4.1 这个经典版本发布了。次年 10 月，又一里程碑式的 MySQL 版本发布了，在新出的 MySQL
5.0 里加入了游标、存储过程、触发器、视图和事务的支持，准备进入中高端应用领域。在 5.0 之后的版本里，
MySQL 明确地表现出迈向高性能数据库的发展步伐。
到今天，MySQL 已经上升到了 600 多万的装机量，著名的 WordPress、phpBB 都以 MySQL 为后台数据库，很
多大型的 WWW 应用例如 Wikipedia、Google 和 Facebook，也都采用了 MySQL 作为它们的数据存储系统。
反观国内，鉴于心照不宣的原因，MySQL 的普及程度还不如 SQL Server。就我这些年来的所见所闻而言，一直
作为 MySQL 黄金搭档的 PHP 都常常使用别的数据库，更别提其他开发语言了。好在那些上规模企业，尤其是
外资企业里，多数明智的 IT 负责人在项目前期都会提议使用 MySQL，原因之一是它是免费的，一般不会产生
授权费用问题，原因之二是它足够用了，不是吗？你想要的增、删、改、联接（Joint）、嵌套查询它都有；你想
要的视图、存储过程、触发器、事务它也有；如果你要集群，它也能提供。
但是，使用 MySQL 是一回事，用好 MySQL 又是另外一回事。市面上更多的是关于 MySQL 开发的书籍，这些
书籍的很多篇幅都花费在 SQL 语句的学习上。若要获得关于 MySQL 性能提高方面的资料，我们只能在网上的
各个论坛或博客上披沙拣金了，而本书则系统性地从各个方面讲述一个高性能 MySQL 应用应该怎么来做。作
者们都是这方面的行家里手，所以内容也是全面、充实，无论是架构师设计师、程序开发人员，还是系统管理
员都能找到感兴趣的方面。在阅读正文前，最好能够先读一下作者精心编写的前言部分，通过它把握整本书内
容的构成方式和相互关联，之后，带有目的性地阅读本书会更富有成效。
本书由李军、王小东、康建勋三人合作翻译完成，其中，康建勋翻译第 1 章和第 2 章前 31 页；王小东翻译第 2
章的后 17 页，以及第 3 章至第 8 章；李军翻译了序言、前言、第 9 章至第 14 章、所有附录，以及作者介绍、
封面、封底等内容，并撰写了内容简介。翻译的过程也是译者与编辑、审阅人员之间交互的过程，在这个过程
里，编辑徐定翔老师，审阅人金照林老师、柳安意老师给予了我们很大的帮助。如果说译者是生产毛坯的工匠，
那么他们就是把毛坯打磨成精品呈现给读者的人，在此十分感谢他们！
同样地，我们也要感谢家人和朋友。我们把那些本来应该陪伴家人出游，或者参加朋友聚会的时间，都“自私”
地用在翻译本书上了。他们都比较宽容，一句“到时要请客哦”就原谅了我们，谢谢他们的支持！
最后，得向读者们说声抱歉，由于术业专攻不同、识见浅深有别之故，译文中难免会有诘屈聱牙、词不达意甚
至疏误之处，还请读者不吝指正（译注 1）。
译者
2009 年 12 月

译注 1：由于本书篇幅较大，为了节约成本和便于读者阅读，我们将原书版式作了压缩，原书页码用“ ”表示，供
读者对照。本书的索引（包括正文中的交叉索引）所列页码为原英文版页码。
vi │ 译者序
159
第 5 章
MySQL 高级特性
Advanced MySQL Features
MySQL5.0 和 5.1 引入了许多特性，例如存储过程、视图和触发器。对于使用过其他数据库产品的用户来说，它
们都是很熟悉的概念。这些新增特性也吸引了很多 MySQL 的新用户。但是只有在人们大规模地使用这些特性
之后，才能知道它们的性能。
本章讨论了这些新增特性及其他的高级主题，包括一些在 MySQL4.1 或更老版本中就存在的特性。性能是讨论
的重点，但是也展示了如何从这些特性中得到最大的益处。
5.1 MySQL 查询缓存
The MySQL Query Cache
许多数据库系统都可以缓存查询计划，服务器可以根据缓存对相同的查询跳过解析和优化阶段。在某些情况下，
MySQL 也能做到这一点。但是它还有一种不同的缓存机制，叫做“查询缓存（Query Cache）”。这种缓存保存
了 SELECT 语句的完整结果集（Complete Result Set）。本节的主题就是这种缓存。
MySQL 查询缓存保留了查询返回给客户端的完整结果。当缓存命中的时候，服务器马上返回保存的结果，并跳
过解析、优化和执行步骤。
查询缓存保留了查询使用过的表，如果表发生了改变，那么缓存就失效了。这种失效的方法比较粗糙，看上去
也不够高效，因为某些表的改变不会导致查询结果的改变。但是这种简单方式的开销比较小，而这对于繁忙的
系统是很重要的。
查询缓存对应用程序完全透明。程序不用知道 MySQL 是从缓存中返回结果还是通过实际计算返回结果。两种
方式返回的结果是一样的。换句话说，查询缓存不会改变语义。不管缓存是打开的还是关闭的，服务器的行为
都一样（注 1）。
5.1.1 MySQL 如何检查缓存命中
How MySQL Checks for a Cache Hit
MySQL 检查缓存命中的方式相当简单快捷。缓存就是一个查找表（Lookup Table）。查找的键就是查询文本、
当前数据库、客户端协议的版本，以及其他少数会影响实际查询结果的因素之哈希值。

注 1：查询缓存实际以一种微妙的方式改变了语义。在默认情况下，一个查询中使用的表即使被 LOCK TABLES 命令
锁住了，查询也能被缓存下来。可以通过设置 query_cache_wlock_ invalidate 来关闭这个功能。
204 205
160 │ 第 5 章：MySQL 高级特性
在检查缓存的时候，MySQL 不会对语句进行解析、正则化或者参数化，它精确地使用客户端传来的查询语句和
其他数据。只要字符大小写、空格或者注释有一点点不同，查询缓存就认为这是一个不同的查询。这是书写查
询语句时要注意的一点。不管怎么说，使用一致的格式和风格是一个好习惯，而且在这种情况下还能得到更高
的性能。
另外一件值得注意的事情就是查询缓存不会存储有不确定结果的查询。因此，任何一个包含不确定函数（比如
NOW()或 CURRENT_DATE()）的查询不会被缓存。同样地，CURRENT_USER()或 CONNECTION_ID()这些由不同用户
执行，将会产生不同的结果的查询也不会被缓存。事实上，查询缓存不会缓存引用了用户自定义函数、存储函
数、用户自定义变量、临时表、mysql 数据库中的表或者任何一个有列级权限的表的查询。请参阅 MySQL 手册
了解所有不会被缓存的查询类型。
经常可以听到“如果查询包含不确定函数，MySQL 就不会检查缓存”这样的说法。其实这是不对的。MySQL
只有在解析查询的时候才知道里面是否有不确定函数。缓存查找发生在解析之前。服务器会执行一次不区分大
小写的检查来验证查询是否以字母 SEL 打头。这就是服务器在进行缓存查找前所做的所有事情。
但是，“如果查询包含 NOW()这样的函数，服务器就不会在缓存中找到结果”这种说法是正确的。因为即使服务
器在早些时候执行了同样的查询，服务器也不会有缓存的结果。MySQL 一旦发现有阻止缓存的元素存在，它里
面就把查询标记为不可缓存，并且产生的结果也不会被保持下来。
对于引用了当天日期的查询，如果想让它被缓存下来，一个有用的技巧就是用一个字面常量来代替函数，比如
下面的例子：
… DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) – 不可缓存！
… DATE_SUB(‘2007-07-14’, INTERVAL 1 DAY) – 可缓存
因为查询缓存只针对服务器第一次收到的完整 SELECT 语句，所以查询里面的子查询或视图不能使用缓存，存
储过程中的查询也不能使用缓存。MySQL 5.1 之前的准备语句（Prepared Statement）也不能使用缓存。
MySQL 查询缓存可以改善性能，但是在使用的时候有一些问题值得注意。首先，开启查询缓存对于读写都增加
了某些额外的开销。
 读取查询在开始之前必须要检查缓存。
 如果查询是可以被缓存的，但是不在缓存中，那么在产生结果之后进行保存会带来一些额外的开销。
 最后，写入数据的查询也会有额外的开销，因为它必须使缓存中相关的数据表失效。
这些开销相对来说较小，所以查询缓存还是很有好处的。但是，稍后你会看到，额外的开销有可能也会增加。
对于 InnoDB 的用户，另外的问题就是事务限制了查询缓存的失效。当事务内部的语句更改了表，即使 InnoDB
的多版本机制应当对其他语句隐藏事务的变化，服务器也会使所有引用了该表的查询缓存失效。直到事务提交
之前，该表会全局地不可缓存。所以不会有任何引用了该表的查询，不管它是在事务的内部还是外部，在事务
提交之前都能被缓存。因此，长期运行的事务可以增加查询缓存未命中（Cache Miss）的数量。
失效对于大型查询缓存也会是一个问题。如果缓存中有许多查询，缓存失效就会需要很长的时间并且延缓整个
系统的工作。这是因为查询缓存有一个全局锁，它会阻塞所有访问缓存的查询。在检查查询是否命中，以及是
否有查询失效的时候都会发生访问动作。
206
5.1 MySQL 查询缓存 │ 161
5.1.2 缓存如何使用内存
How the Cache Uses Memory
MySQL 将查询缓存完全存储在内存中，所以在对它进行调优之前需要了解它如何使用内存。缓存不仅仅存储了
查询结果，它在某种程度上像一个文件系统，它保持了自身的结构，而这些结构有助于它了解哪块内存是空闲
的、表和查询之间的映射关系、查询文本、查询结果。
除了用于自身的 40KB 内存，查询缓存的内存池被分为大小可变的块。每一块都知道自己的类型、大小、数据
量和指向前一个和后一个逻辑块和物理块的指针。内存块可以分为存储查询结果、查询使用的表的列表、查询
文本等类型。然而，不同类型的块的处理方式都一样，所以对查询缓存调优的时候不用区分它们的类型。
服务器启动的时候会初始化查询缓存使用的内存。内存池最开始只有一个块。它的大小是被配置用于缓存的内
存大小减去自身需要的 40KB。
在缓存查询结果时，服务器会为查询分配一块空间。如果服务器知道正在缓存一个较大的结果，这个块就会大
一些。但是它至少等于 query_cache_min_res_unit 的值。不幸的是，服务器不能精确地分配大小，因为分配
发生在结果产生之前。服务器不会在内存中生成最终的结果然后发送到客户端，而是每产生一行数据，就发送
一行，因为这种方式效率更高。这造成的结果就是：当服务器开始缓存结果的时候，它无法知道结果最终会有
多大。
分配内存块的速度相对较慢，因为服务器需要查看可用内存的列表并且找到大小合适的块。因此，服务器会尽
量减少分配的次数。当需要缓存结果时，它会创建一个大小至少为最小值的块，并且把结果放入到块中。如果
块已经满了，但是还有数据没有保存，服务器就会产生一个新块并且继续存储数据。在保存完成后，如果数据
块还有剩余空间，服务器就会裁剪该块，并且把空间并入剩下的空闲空间中。图 5-1 显示了该过程（注 2）。
所谓的服务器“分配块”，并不是意味着向操作系统使用 malloc()这样的函数请求内存空间。Malloc 过程只会
在创建查询缓存的时候发生一次。这儿的意思是服务器检查数据块的列表并且选择一个最佳的位置放置新块。
如果有需要，还会移除最旧的查询以腾出空间。MySQL 服务器管理自己的内存，不依赖于操作系统。
到目前为止，这些过程都非常简单。但是，实际过程比图 5-1 要复杂一些。假设平均结果都很小，服务器同时
把结果发送到两个客户端。对数据块的裁剪结果就可能留下比 query_cache_min_res_unit 还小的空间，这些
空间将来不能用于存储结果。

注 2：出于演示的目的，本节的图形已经进行了简化。服务器实际分配块的过程比这儿介绍的要复杂得多。如果想了
解详情，sql/sql_cache.cc 这个文件头部的注释将该过程解释得很清楚。
207
162 │ 第 5 章：MySQL 高级特性
图 5-1：查询缓存如何分配内存块以存储数据
这时，数据块的分配看上去就像图 5-2。 图 5-2：向查询缓存中存储结果引起的碎片
对第一个结果进行裁剪后，在两个结果之间留下了一块很小的空间，它不能存储结果，这就是碎片。它也是内
存和文件系统空间分配的经典问题。导致碎片的原因是多样的，比如缓存失效会留下不能复用的小块空间。
5.1.3 查询缓存何时有帮助
When the Query Cache Is Helpful
缓存并不会自动地比非缓存高效。缓存也需要开销，只有在节省的资源大于开销的时候，缓存才是真正有效率
的，这和服务器的负载相关。
208 209
5.1 MySQL 查询缓存 │ 163
在理论上，可以通过对比在缓存开启和关闭时服务器需要做的工作来了解缓存是否有帮助。在缓存关闭的时候，
读取操作需要执行查询语句并且把结果返回给客户，写入操作需要执行查询。在缓存启用的时候，读取首先会
检查查询缓存，然后要么直接返回结果，要么执行查询语句，保存结果，再返回结果。每一个写入操作都需要
执行查询语句并且检查是否有缓存过的查询要失效。
尽管这听上去很直接，但其实不是。实际上很难精确地计算或者预测查询缓存的好处。必须考虑外部因素。例
如，查询缓存可以减少产生结果的时间，但它不会减少将结果发送到客户端的时间，而这有可能是主要因素。
从缓存中受益最多的查询可能是需要很多资源来产生结果，但是不需要很多空间来保存的类型。所以用于存储、
返回和失效的代价都较小。聚集查询，比如从大表中利用 COUNT()产生较小的结果，就符合这个范畴。但是也
有其他很多种查询值得缓存。
检查是否从查询缓存中受益的最简单的办法就是检查缓存命中率。它是缓存提供的查询结果的数量，而不是服
务器执行的数量。当服务器收到 SELECT 语句的时候，Qcache_hits 和 Com_select 这两个变量会根据查询缓存
的情况进行递增，查询缓存命中率的计算公式是：Qcache_hits/(Qcache_hits + Com_select)。
命中率要多少才好？这视情况而定，即使 30%的命中率也可能很有帮助。因为对于每一个查询，不执行它所节
约的资源远大于在缓存中保存结果及让查询失效的开销。知道哪个查询被缓存了也很重要。如果缓存命中代表
了开销最大的查询，那么即使是很低的命中率也有很大的好处。
任何一个不在缓存中存在的查询都是缓存未命中。缓存未命中可能是因为下面的原因：
 查询不可缓存。原因可能是含有不确定函数，比如 CURRENT_DATE，也有可能是结果太大，无法缓存。状态
变量 Qchache_not_cached 会因为这两种无法缓存的查询而增加。
 服务器以前从来没见过这个缓存，所以它根本就没有机会缓存自身结果。
 查询的结果以前被缓存过，但是服务器把它移除了。发生移除的原因可能是内存空间不够，所以被人从服
务器上把它删除了，也可能是缓存失效了。
如果服务器有很多缓存未命中，但是不能缓存的查询却很少，那么原因应该是下面之一：
 查询缓存未被激活，也就是说服务器根本就没机会将结果存储到缓存中。
 服务器看到了以前未曾见过的查询。如果没有很多重复的查询，即使缓存被激活了，也有可能见到这种情
况。
 有很多缓存失效。
缓存可能会因为碎片、内存不足或数据改变而失效。如果已经给缓存分配了足够的内存，并且把
query_cache_min_res_unit 调整到了合适的值，那么大部分缓存失效都应该是由数据改变引起的。可以通过
检 查 Com_* （ Com_update, Com_delete 等）的值知道有多少查询修改了数据。 也可以通过检查
Qcache_lowmem_prunes 的值了解有多少查询因为内存不足而失效。
一个不错的想法就是把缓存失效的开销和命中率分开考虑。举一个极端的例子，假设有一个表只发生读取动作
并且命中率是 100%，另外一张表只发生更新。如果只简单地从状态变量计算命中率，它始终都是 100%。然而，
查询缓存在这种情况下仍然有可能效率不高，因为更新被减慢了。所有的更新查询在完成后都不得不访问缓存，
检查是否有其他的查询会因为数据变化而失效。但实际上答案始终是“否”。所以这些工作就全部浪费了。如果
不在检查命中率的同时检查不可缓存的查询数量，那么就不可能发现这些问题。
210
164 │ 第 5 章：MySQL 高级特性
如果对同一张表进行大致平均的读写，服务器也可能不会从查询缓存中得益。写入数据会不停地让缓存失效，
而读取数据会不停地把新结果插入缓存中。在这种情况下，只有发生后续读取，这种缓存才是有益的。
如果在服务器收到同一个查询语句之前，缓存就失效了，那么保持结果就只能是浪费时间和内存。检查
Com_select 和 Qcache_inserts 的相对大小可以确认这种情况是否发生。如果差不多所有的 SELECT 语句都是
缓存未命中（Com_select 会因此增加），并且接下来把结果保存到了缓存中，那么 Qcache_inserts 就会和
Com_select 差不多大小。因此，Qcache_inserts 一般比 Com_select 小得多，至少在缓存被正确地激活后是这
样的。
每个应用程序都有确定的潜在缓存大小，即使没有写入查询也是如此。潜在缓存用于保存应用程序所有可缓存
查询的内存数量。从理论上说，它对于大部分应用程序是极大的数字。在实际中，由于失效的数量，许多应用
程序可用的缓存比期望的小得多。即使把查询缓存设置得非常大，实际也不可能超过潜在缓存的大小。
你应该监视服务器实际使用的缓存数量。如果它没有用到分配的内存，那么就应该把分配给它的内存减少一点。
如果由于内存限制引起了缓存失效，那么就应该多分配一些内存。但是不用太在意缓存的大小，它比有实际影
响的稍大一点或小一点都没有问题。只有在内存有严重浪费或者缓存失效太多的时候才需要去考虑它的大小。
还应该在服务器其他缓存和查询缓存之间找到某种平衡，例如 InnoDB 的缓存池或 MyISAM 的键缓存。它们之
间没有简单的公式或固定的比例，因为这取决于应用程序。
5.1.4 如何对查询缓存进行维护和调优
How to Tune and Maintain the Query Cache
一旦了解查询缓存的工作机制，对它进行调优就是一件容易的工作。它只有几个“活动部分”。
Query_cache_type
这个选项表示缓存是否被激活。具体选项是 OFF、ON 或 DEMAND。DEMAND 的意思是只有包含了 SQL_CACHE
选项的查询才能被缓存。它既是会话级变量，也是全局性变量（更多关于会话变量和全局变量的话题请参
阅第 6 章）。
Query_cache_size
分配给查询的总内存，以字节为单位。它必须是 1024 的倍数。所以 MySQL 实际使用的值可能和定义的值
稍有不同。
Query_cache_min_res_unit
分配缓存块的最小值。第 206 页的“缓存如何使用内存”解释过该设置，下节会对它做进一步讨论。
Query_cache_limit
这个选项限制了 MySQL 存储的最大结果。如果查询的结果比这个值大，那么就不会被缓存。要知道的是
服务器在产生结果的同时进行缓存，它无法预先知道结果是否会超过这一限制。如果在缓存的过程中发现
已经超过了限制，MySQL 会增加 Qcache_not_cached 的值，并且丢掉已经缓存过的值。如果知道会发生
这样的事，那么给查询加上 SQL_NO_CACHE，可以避免这种开销。
211 212
5.1 MySQL 查询缓存 │ 165
Query_cache_wlock_invalidate
这个选项指是否缓存其他联接已经锁定了的表。默认值是 OFF，可以让你从其他联接已经锁定了的表中读
取缓存过的数据，这改变了服务器的语义。因为这种读取通常是不被允许的。把它改成 ON 会阻止读取数
据，但有可能增加锁等待。它对于大多数程序都没有影响，所以通常保持默认值就可以了。
在原则上，对缓存进行调优很简单，但是理解自己所做的改变的影响则要复杂得多。接下来的章节展示了如何
对查询缓存进行推理，以做出正确的决定。
减少碎片
没有办法避免所有的碎片，但是仔细地选择 query_cache_min_res_unit 可以避免在查询缓存中造成大量的内
存浪费。关键在于每一个新块和服务器已分配给存储结果的块的数量之间找到平衡。如果值过小，服务器将会
浪费较少的内存，但会更频繁地分配块，这对服务器意味着更多的工作。如果值过大，碎片将会很多。合适的
折中是在浪费内存和增加 CPU 处理时间上取得平衡。
最佳设置根据典型查询结果而定。可以用使用的内存（大致等于 query_cach_size-Qcache_ free_memory）除 以 Qcache_queries_in_cache 得到查询的平均大小。如果缓存由大结果和小结果混合而成，那么就很难找到一
个合适的大小，既能避免碎片，也能避免过多的内存分配。但是，有理由相信缓存大结果没有太大的益处（这
通常是真的）。可以通过降低 query_cache_limit 的值阻止缓存大结果，它有时有助于在碎片和在缓存中保存
结果的开销中得到平衡。
可以通过检查 Qcache_free_blocks 的值来探测缓存中碎片的情况，它可以显示缓存中有多少内存块处于 FREE
状态。图 5-2 中最后一步显示了两个处于 FREE 的块。碎片最严重的情况就是在每两个存储了数据的块之间都
有一个比最小值稍小的可用块。这样的话，每隔一个存储块就有一个自由块。因此，如果 Qcache_free_blocks
大致等于 Qcache_total_ blocks/2，则说明碎片非常严重。如果 Qcache_lowmem_prunes 的值正在增加，并且
有大量的自由块，这意味着碎片导致查询正被从缓存中永久删除。
可以使用 FLUSH QUERY CACHE 命令移除碎片。这个命令会把所有的存储块向上移动，并把自由块移到底部。当
它运行的时候，它会阻止访问查询缓存，这锁定了整个服务器。但它通常都很快，除非缓存非常大。和名字相
反，它不会从缓存中移除查询，RESET QUERY CACHE 才会这么做。
提高查询缓存的可用性
如果缓存没有碎片，但是命中率却不高，那么就应该给缓存分配较少的内存。如果服务器找不到足够大小的块
来存储结果，那么就应该从缓存中清理掉一些查询。
当服务器清理查询的时候，Qcache_lowmem_prunes 的值会增加。如果它的值增加得很快，那么可能有两个原因：
 如果有很多自由块，那么问题可能是由碎片引起的（参阅前一节）。  如果自由块比较少，那么这可能意味着工作负载使用的内存大小超过了所分配的内存。可以检查
Qcache_free_memory 知道未使用的内存数量。
如果有很多自由块，碎片很少，由于内存不足引起的清理工作也很少，但是命中率仍然不高，这说明工作负载
也许不能从缓存中受益。肯定有什么东西阻止查询使用缓存，很多 update 语句可能是原因，另外一个可能的原
因是查询是不可缓存的。
213
166 │ 第 5 章：MySQL 高级特性
如果已经估算过缓存命中率，但是还不确定服务器是否从缓存中受益，此时可以禁用缓存并且监控性能，然后
重新开启缓存并观察性能变化。为了禁用缓存，可以将 query_cache_size 设置为 0（改变 query_cache_type
不会从全局上影响已经打开了的连接，而且不会把内存归还给服务器）。也可以做基准测试，但是有时候很难得
到包含了可缓存的查询、不可缓存的查询，以及更新语句的测试样例。
图 5-3 用一个基本例子显示了分析和调整查询缓存的流程。 图 5-3：如何分析调整查询缓存
214
5.1 MySQL 查询缓存 │ 167
5.1.5 InnoDB 和查询缓存
InnoDB and the Query Cache
InnoDB 由于其 MVCC 架构，和查询缓存的交互比其他存储引擎要复杂得多。在 MySQL 4.0 中，查询缓存在事
务内部是被完全禁止的，但是在 MySQL 4.1 及其新版本中，InnoDB 会针对每一个表请示服务器一个事务是否
可以访问查询缓存。它控制了读（从缓存中获取数据）、写（向缓存中保存数据）操作对缓存的访问。
决定访问的因素是事务 ID 及表上是否有锁。每个表在 InnoDB 的内存内部数据字典中都有一个对应的事务 ID
计数器。ID 小于计数器值的事务会被禁止从缓存读取或写入数据。表上的任何锁都会导致使用该表的查询变得
不可缓存。例如，如果事务在表上面执行了 SELECT FOR UPDATE 查询，那么直到锁解除为止，不会有任何其他
的事务能从缓存对它进行读写操作。
当事务提交的时候，InnoDB 会在锁定的基础上更新计数器的值。锁可以作为粗略的提示，用来表示事务是否修
改了表。有可能事务锁住了表却没有更新它，但是不可能在没有获取锁的情况下修改表的内容。InnoDB 把每个
表的计数器设置为系统的事务 ID，它是已有的事务 ID 的最大值。
这会造成下面的结果：
 使用查询缓存的事务中的表的计数器位于绝对下限。如果系统事务 ID 是 5，并且一个事务请求了表的行锁，
然后提交了，事务 1 到 4 中引用的这个表的查询将再也不能对缓存进行读写操作。
 表的计数器没有按照使用表的事务 ID 进行更新，却按照系统事务 ID 进行了更新。这样的后果就是这个事
务发现自己以后再也不能在缓存中操作引用了这个表的查询。
查询缓存的存储、获取及失效都在服务器级进行，并且 InnoDB 不能绕过或延迟这种行为。但是，InnoDB 能显
式地告诉服务器让使用了某些特定的表的查询失效。在外键约束，比如 ON DELETE CASCADE，修改了查询中未
出现的表的内容时，这是有用的。
从原则上说，如果对表的改动不会影响其他事务看到的连续读取视图，InnoDB 的 MVCC 架构就可以让缓存为
查询服务。但是，实现这种方式是很复杂的。InnoDB 的算法出于简单性的考虑走了一些捷径，其代价就是在实
际并不需要的时候锁住了查询缓存之外的事务。
5.1.6 通用查询缓存优化方案
General Query Cache Optimizations
架构、查询以及应用程序设计会影响查询缓存。除了上节讨论的内容，下面还有一些需要注意的观点：
 使用多个较小的表，而不是用一个大表，对查询缓存有帮助。这种设计方式使失效策略工作在一个较好的
颗粒度上。但是不要让这个想法过度影响架构设计，因为其他的因素能轻易抵消它的好处。
 成批地进行写入操作，而不是逐个执行，会有效率得多。因为这种方法只会引起一次失效操作。
 我们已经注意到在让缓存失效或清理一个大型缓存的时候，服务器可能会挂起相当长时间。至少在 MySQL5.1
之前的版本中是这样。一个容易的解决办法就是不要让 query_cache_size 太大，256MB 已经太大了。
 不能在数据库或表的基础上控制查询缓存，但是可以使用 SQL_CACHE 和 SQL_NO_CACHE 决定是否缓存查询。
也可以基于某个连接来运行或禁止缓存，可以通过用适当的值设定 query_cache_size 来开启或关闭对
某个连接的缓存。
215 216
168 │ 第 5 章：MySQL 高级特性
 对于很多写入任务的应用程序，关闭查询缓存也许能改进性能。这样做可以消除缓存那些很快就会失效的
查询所带来的开销。要记住在禁用的时候需要把 query_cache_ size 设置到 0，这样就不会消耗任何内
存。
如果想让大多数查询都不使用缓存，但是有少部分查询能从缓存中极大地受益