内容简介：
本书是国内目前唯一的一本关于InnoDB的著作，由资深MySQL专家亲自执笔，中外数据库专家联袂推荐，权威性毋庸置疑。
内容深入，从源代码的角度深度解析了InnoDB的体系结构、实现原理、工作机制，并给出了大量最佳实践，能帮助你系统而深入地掌握InnoDB，更重要的是，它能为你设计和管理高性能、高可用的数据库系统提供绝佳的指导。注重实战，全书辅有大量的案例，可操作性极强。
全书首先全景式地介绍了MySQL独有的插件式存储引擎，分析了MySQL的各种存储引擎的优势和应用环境；接着以InnoDB的内部实现为切入点，逐一详细讲解了InnoDB存储引擎内部的各个功能模块，包括InnoDB存储引擎的体系结构、内存中的数据结构、基于InnoDB存储引擎的表和页的物理存储、索引与算法、文件、锁、事务、备份，以及InnoDB的性能调优等重要的知识；最后深入解析了InnoDB存储引擎的源代码结构，对大家阅读和理解InnoDB的源代码有重要的指导意义。
本书适合所有希望构建和管理高性能、高可用性的MySQL数据库系统的开发者和DBA阅读。

作者简介：
姜承尧，资深MySQL数据库专家，不仅擅长于数据库的管理和维护，还擅长于数据库的开发。一直致力于MySQL数据库底层实现原理的研究和探索，对高性能数据库和数据仓库也有深刻而独到的理解。曾为MySQL编写了许多开源工具和性能扩展补丁，如广受好评的InnoDB引擎二级缓存项目。现任久游网数据库工程部经理，曾领导并参与了多个大型核心数据库的设计、实施、管理和维护，实战经验非常丰富。活跃于开源数据库以及开源软件领域，是著名开源社区ChinaUnix MySQL版块的版主，热衷于与网友分享自己的心得和体会，深受社区欢迎。

专家评论：
作者是一位非常有经验的DBA，他将自己多年在MySQL存储引擎上积累的经验融汇成了这本书，并形成了自己的研究成果，实在是让人敬佩。本书内容深入且全面，像这样有内容的书已经越来越少，像这样有研究精神的人也越来越少。我不仅要向InnoDB的初学者推荐这本书，而且还要向富有经验的DBA推荐这本书，或许他们已经掌握了一些InnoDB的应用和调优知识，但这本书能帮助他们在这个方向上再前进“一小步”。——顾懿 久游网 COO

SQL Server企业版的一颗CPU License要人民币贰拾多万，Oracle的License更是按照CPU内核数计算，这样的价格不是一般的成长型企业能承受得起的，对于有海量数据存储需求的大企业，License费用就更夸张了。可见，MySQL在数据库市场上的重要性是勿容置疑的。
然而，MySQL在教育上的投入远没有Microsoft和Oracle那样多，可以参考的书不多，其中大多数书又和帮助文档没有太多区别。毕竟，在这个心浮气躁的年代，愿意抛开杂念一心热衷于钻研IT技术的人越来越少了，坚持下来的，愿意花时间写书来分享自己经验的，就更少了。David就是这少数人当中的一个，认识他有十多年了，他给我的印象就是快乐的IT人，每次有新的发现或研究成果，都会很兴奋地跟我分享。这次，他把多年的InnoDB经验拿出来跟大家分享，完全是原创哦，希望MySQL的同行多多支持！——Tom Sawyer 玫琳凯（中国）化妆品有限公司数据库架构师

InnoDB作为MySQL非常重要的存储引擎之一，在MySQL的企业级应用中占据着非常重要的地位。目前市场上介绍MySQL的书不算多，专门讲解InnoDB引擎的书更是凤毛麟角。本书不仅剖析了InnoDB引擎的实现原理，而且结合源代码揭示了InnoDB的工作机制，是作者多年应用经验的结晶。作者本人不仅在MySQL企业级应用上具有丰富的实战经验，在数据库开发方面也有很高的造诣，开发了性能不错的InnoDB引擎二级缓存项目，为开源社区贡献了自己的一份力量。
对于广大DBA和开发者而言，要知其然，更要知其所以然，只有明白了实现原理和工作机制才能更好地解决工作中遇到的问题。不管你是学习MySQL的新手还是能熟练使用MySQL的专家级人物，不管你是一般的应用开发人员还是专业的MySQL DBA，如果你想从容面对在工作中遇到的InnoDB问题，想以更优化的方式使用InnoDB引擎，想做出满足自己业务需要的InnoDB引擎特性，你一定能从本书中找到答案。 ——杨海朝 新浪网（中国）技术有限公司高级DBA

目 录 [ - ]
《MySQL技术内幕:InnoDB存储引擎》前言
《MySQL技术内幕:InnoDB存储引擎》目录
第1章 MySQL体系结构和存储引擎
1.1 定义数据库和实例
1.2 MySQL体系结构
1.3 MySQL表存储引擎
1.4 各种存储引擎之间的比较
1.5 连接MySQL
1.6 小结
第2章 InnoDB存储引擎
2.1 InnoDB存储引擎概述
2.2 InnoDB体系架构
2.3 master thread
2.4 关键特性
2.5 启动、关闭与恢复
2.6 InnoDB Plugin = 新版本的InnoDB存储引擎
2.7 小结
第3章 文件
3.1 参数文件
3.2 日志文件
3.3 套接字文件
3.4 pid文件
3.5 表结构定义文件
3.6 InnoDB存储引擎文件
3.7 小结

《MySQL技术内幕:InnoDB存储引擎》前言
    过去这些年，我一直在和各种不同的数据库打交道，见证了MySQL从一个小型的关系型数据库发展成为各大企业的核心数据库系统的过程，并且参与了一些大大小小的项目的开发工作，成功地帮助开发人员构建了一些可靠、健壮的应用程序。在这个过程中积累了一些经验，正是这些不断累积的经验赋予了我灵感，于是有了本书。《MySQL技术内幕:InnoDB存储引擎》实际上反映了这些年来我做了哪些事情，汇集了很多同行每天可能都会遇到的一些问题，并给出了解决方案。
    MySQL数据库独有的插件式存储引擎架构使得它与其他任何数据库都不同，不同的存储引擎有着完全不同的功能，而InnoDB存储引擎的存在使得MySQL跃入了企业级数据库领域。本书完整地讲解了InnoDB存储引擎中最重要的一些内容，即InnoDB的体系结构和工作原理，并结合InnoDB的源代码讲解了它的内部实现机制。
    本书不仅介绍了InnoDB存储引擎的诸多功能和特性，而且还阐述了如何正确地使用这些功能和特性。更重要的是，它还尝试教大家如何Think Different。Think Different是20世纪90年代苹果公司在其旷日持久的宣传活动中提出的一个口号，借此来重振公司的品牌，更重要的是改变人们对技术在日常生活中的作用的看法。需要注意的是，苹果的口号不是Think Differently，而是Think Different。这里的Different是名词，意味该思考些什么。
    很多DBA和开发人员都相信某些“神话”，然而这些“神话”往往都是错误的。无论计算机技术发展的速度变得多快、数据库的使用变得多么简单，任何时候WHY都比WHAT重要。只有真正地理解了内部实现原理、体系结构，才能更好地去使用。这正是人类正确思考问题的原则。因此，对于当前出现的技术，尽管学习应用层面的技术很重要，但更重要的是，应当正确地理解和使用这些技术。
    关于这本书，我想实现好几个目标，但最重要的是想告诉大家如下几个简单的观点：

不要相信任何“神话”，学会自己思考。
不要墨守成规，大部分人都知道的事情可能是错误的。
不要相信网上的传言，去测试，根据自己的实践做出决定。
花时间充分地思考，敢于提出质疑。
为什么写本书
    当前有关MySQL的书籍大部分都集中在教读者如何使用MySQL，例如SQL语句的使用、复制的搭建、数据的切分等。没错，这对快速掌握和使用MySQL数据库非常有好处，但是真正的数据库工作者需要了解的不仅仅是应用，更多的是内部的具体实现。
    MySQL数据库独有的插件式存储引擎结构使得想要在一本书内完整地讲解各个存储引擎变得十分困难。有的书可能偏重于对MyISAM的介绍，有的书则可能偏重于对InnoDB存储引擎的介绍。对于初级的DBA来说，这可能会使他们的理解变得更困难。对于大多数MySQL DBA和开发人员来说，他们往往更希望了解作为MySQL企业级数据库应用的第一存储引擎—InnoDB。我想在本书中，他们可以找到他们想要的内容。
    再强调一遍，任何时候WHY都比WHAT重要。本书从源代码的角度对InnoDB的存储引擎的整个体系架构的各个组成部分进行了系统的分析和讲解，剖析了InnoDB存储引擎的核心实现和工作机制，相信这在其他书中是很难找到的。

本书面向的读者
    本书不是一本面向应用的数据库类书籍，也不是一本参考手册，更不会教你如何在MySQL中使用SQL语句。本书面向那些使用MySQL InnoDB存储引擎作为数据库后端开发应用程序的开发者和有一定经验的MySQL DBA。书中的大部分例子都是用SQL语句来展示关键特性的，如果想通过本书来了解如何启动MySQL，如何配置Replication环境，可能并不能如愿。不过，通过本书，你将理解InnoDB存储引擎是如何工作的，它的关键特性的功能和作用是什么，以及如何正确地配置和使用这些特性。
    如果想更好地使用InnoDB存储引擎，如果想让你的数据库应用获得更好的性能，就请阅读本书。从某种程度上讲，技术经理或总监也要非常了解数据库，要知道数据库对于企业的重要性。如果技术经理或总监想安排员工参加MySQL数据库技术方面的培训，完全可以利用本书来“充电”，相信你一定不会失望的。
    要想更好地学习本书，要求具备以下条件：

掌握SQL。
掌握基本的MySQL操作。
接触过一些高级语言，如C、C++、Python或Java。
对一些基本算法有所了解，因为本书会分析InnoDB存储引擎的部分源代码，如果你能看懂这些代码，这会对你的理解非常有帮助。
如何阅读这本书
    本书一共有10章，每一章都像一本“迷你书”，可以单独成册，也就说，你完全可以从书中任何一章开始阅读。例如，要了解第10章中的InnoDB源代码编译和调试的知识，就不必先去阅读第3章有关文件的知识。当然，如果你不太确定自己是否已经对本书所涉及的内容已经完全掌握，建议你系统地阅读本书。
    本书不是一本入门书，不会一步步引导你去如何操作。倘若你尚不了解InnoDB存储引擎，本书对你来说可能就显得沉重了些，建议你先查阅官方的API文档，大致掌握InnoDB的基础知识，然后再来阅读本书，相信你会领略到不同的风景。
    需要特别说明的是，附录B～D详细给出了Master Thread、Doublewrite、哈希算法和哈希表的源代码，前面学习了这些理论知识，再适当地阅读这些源代码，相信有经验的DBA可以更好地掌握和理解InnoDB存储引擎的本质。为了便于大家阅读，本书在提供源代码下载的同时也将源代码附在了书中，因此占去了一些篇幅，还请大家理解。

致谢
    在编写本书的过程中，我得到了很多朋友的热心帮助。首先要感谢Pecona公司的CEO Peter Zaitsev和CTO Vadim Tkachenko，通过与他们的不断交流，使得我对InnoDB存储引擎有了更进一步的了解，同时知道了怎样才能正确地将InnoDB存储引擎的补丁应用到生产环境。
    其次，我要感谢久游网公司的各位同事们。能在才华横溢、充满创意的团队中工作，我感到非常荣幸和兴奋。也因为在这个开放的工作环境中，我才得以不断地进行研究和创新。
    此外，我还要感谢我的母亲，写书不是一件容易的事，特别是本书还想传达一些思想，在这个过程中我遇到了很多的困难，感谢她在这个过程中给予我的支持和鼓励。
    最后，一份特别的感谢要送给本书的策划编辑杨福川先生和曾珊女士，他们使得本书变得生动和更具有灵魂。

《MySQL技术内幕:InnoDB存储引擎》目录
推荐序
前言
致谢
第1章   MySQL体系结构和存储引擎 1
第2章   InnoDB存储引擎 17
第3章   文件 45
第4章   表 72
第5章   索引与算法 160
第6章   锁 204
第7章   事务 230
第8章   备份与恢复 260
第9章   性能调优 298
第10章   InnoDB存储引擎源代码的编译和调试 325
附录A   Secondary Buffer Pool For InnoDB 339
附录B   Master Thread源代码 342
附录C   Doublewrite源代码 353
附录D   哈希算法和哈希表源代码 361

第1章 MySQL体系结构和存储引擎
MySQL被设计为一个可移植的数据库，几乎能在当前所有的操作系统上运行，如Linux、Solaris、FreeBSD、Mac和Windows。尽管各种系统在底层（如线程）实现方面各有不同，但是MySQL几乎能保证在各平台上的物理体系结构的一致性。所以，你应该能很好地理解MySQL在所有这些平台上是如何工作的。

1.1   定义数据库和实例    1

1.2   MySQL体系结构    3

1.3   MySQL表存储引擎    5

1.3.1   InnoDB存储引擎    6
1.3.2   MyISAM存储引擎    7
1.3.3   NDB存储引擎    7
1.3.4   Memory存储引擎    8
1.3.5   Archive存储引擎    9
1.3.6   Federated存储引擎    9
1.3.7   Maria存储引擎    9

1.3.8   其他存储引擎    9

1.4   各种存储引擎之间的比较    10
1.5   连接MySQL    13

1.5.1   TCP/IP    13
1.5.2   命名管道和共享内存    14
1.5.3   Unix域套接字    15

1.6   小结    15

1.1 定义数据库和实例
在数据库领域中有两个词很容易混淆，它们就是“实例”（instance）和“数据库”（database）。作为常见的数据库术语，这两个词的定义如下。

数据库：物理操作系统文件或其他形式文件类型的集合。在MySQL中，数据库文件可以是frm、myd、myi、ibd结尾的文件。当使用NDB引擎时，数据库的文件可能不是操作系统上的文件，而是存放于内存之中的文件，但是定义仍然不变。
数据库实例：由数据库后台进程/线程以及一个共享内存区组成。共享内存可以被运行的后台进程/线程所共享。需要牢记的是，数据库实例才是真正用来操作数据库文件的。
这两个词有时可以互换使用，但两者的概念完全不同。在MySQL中，实例和数据库的通常关系是一一对应，即一个实例对应一个数据库，一个数据库对应一个实例。但是，在集群情况下可能存在一个数据库可被多个实例使用的情况。

MySQL被设计为一个单进程多线程架构的数据库，这点与SQL Server比较类似，但与Oracle多进程的架构有所不同（Oracle的Windows版本也是单进程多线程的架构）。这也就是说，MySQL数据库实例在系统上的表现就是一个进程。

在Linux操作系统中启动MySQL数据库实例，命令如下所示：

请注意进程号为3578的进程，该进程就是MySQL实例。这里我们使用了mysqld_safe命令来启动数据库，启动MySQL实例的方法还有很多，在各种平台下的方式可能会有所不同。在这里不一一举例。
当启动实例时，MySQL数据库会去读取配置文件，根据配置文件的参数来启动数据库实例。这与Oracle的参数文件（spfile）相似，不同的是，在Oracle中，如果没有参数文件，启动时会提示找不到该参数文件，数据库启动失败。而在MySQL数据库中，可以没有配置文件，在这种情况下，MySQL会按照编译时的默认参数设置启动实例。用以下命令可以查看，当MySQL数据库实例启动时，它会在哪些位置查找配置文件。

可以看到，MySQL是按/etc/my.cnf→/etc/mysql/my.cnf→/usr/local/mysql/etc/my.cnf→~/.my.cnf的顺序读取配置文件的。可能有人会问：“如果几个配置文件中都有同一个参数，MySQL以哪个配置文件为准？”答案很简单，MySQL会以读取到的最后一个配置文件中的参数为准。在Linux环境下，配置文件一般放在/etc/my.cnf下。在Windows平台下，配置文件的后缀名可以是.cnf，也可能是.ini。运行mysql -help命令，可以找到以下的内容：

配置文件中有一个datadir参数，该参数指定了数据库所在的路径。在Linux操作系统下，datadir默认为/usr/local/mysql/data。当然，你可以修改该参数，或者就使用该路径，但该路径只是一个链接，如下所示：

从上面可以看到，其实data目录是一个链接，该链接指向了/opt/mysql_data目录。当然，必须保证/opt/mysql_data的用户和权限，使得只有mysql用户和组可以访问。

1.2 MySQL体系结构
由于工作的缘故，我很大一部分时间都花在对开发人员进行数据库方面的沟通和培训上。此外，不论他们是DBA，还是开发人员，似乎都对MySQL的体系结构了解得不够透彻。很多人喜欢把MySQL与他们以前使用过的SQL Server、Oracle、DB2作比较。因此，我常常会听到这样的疑问：

为什么MySQL不支持全文索引？
MySQL速度快是因为它不支持事务？
数据量大于1 000W时，MySQL的性能会急剧下降吗？
对于MySQL的疑问还有很多很多，在我解释这些问题之前，我认为，不管使用哪种数据库，了解数据库的体系结构都是最为重要的。
在给出体系结构图之前，我想你应该理解了前一节提出的两个概念：数据库和数据库实例。很多人会把这两个概念混淆，即MySQL是数据库，MySQL也是数据库实例。你这样来理解Oracle和SQL Server可能是正确的，但这对于以后理解MySQL体系结构中的存储引擎可能会带来问题。从概念上来说，数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合；数据库实例是应用程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等，都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。
如果这样讲解后你还是觉得不明白，那我再换一种更直白的方式来解释：数据库是由一个个文件组成（一般来说都是二进制的文件）的，如果要对这些文件执行诸如SELECT、INSERT、UPDATE和DETELE之类的操作，不能通过简单的操作文件来更改数据库的内容，需要通过数据库实例来完成对数据库的操作。所以，如果你把Oracle、SQL Server、MySQL简单地理解成数据库，可能是有失偏颇的，虽然在实际使用中我们并不会这么强调两者之间的区别。好了，在给出上述这些复杂枯燥的定义后，现在我们可以来看看MySQL数据库的体系结构了，如图1-1所示。

图1-1   MySQL体系结构

从图1-1我们可以发现，MySQL由以下几部分组成：

连接池组件。
管理服务和工具组件。
SQL接口组件。
查询分析器组件。
优化器组件。
缓冲（Cache）组件。
插件式存储引擎。
物理文件。
从图1-1还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。

注意：存储引擎是基于表的，而不是数据库。请牢牢记住图1-1所示的MySQL体系结构图，它对于你以后深入了解MySQL有极大的帮助。

1.3 MySQL表存储引擎
1.2节大致讲解了MySQL数据库独有的插件式体系结构，并介绍了存储引擎是MySQL区别于其他数据库的一个最重要特性。存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同的存储引擎表。对于开发人员来说，存储引擎对其是透明的，但了解各种存储引擎的区别对于开发人员来说也是有好处的。对于DBA来说，他们应该深刻地认识到MySQL的核心是存储引擎。

由于MySQL是开源的，你可以根据MySQL预定义的存储引擎接口编写自己的存储引擎，或者是如果你对某种存储引擎不满意，可以通过修改源码来实现自己想要的特性，这就是开源的魅力所在。比如，eBay的Igor Chernyshev工程师对MySQL Memory存储引擎的改进（http://code.google.com/p/mysql-heap-dynamic-rows/），并应用于eBay的Personalization Platform，Google和Facebook等公司也对MySQL进行了相类似的修改。我曾尝试过对InnoDB存储引擎的缓冲池进行扩展，为其添加了基于SSD的辅助缓冲池，通过利用SSD的高随机读取性能来进一步提高数据库本身的性能。当然，MySQL自身提供的存储引擎已经足够满足绝大多数应用的需求。如果你有兴趣，完全可以开发自己的存储引擎来满足自己特定的需求。MySQL官方手册的第16章给出了编写自定义存储引擎的过程，不过这已超出了本书的范围。

由于MySQL的开源特性，存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。有些第三方存储引擎很强大，如大名鼎鼎的InnoDB存储引擎（现已被Oracle收购），其应用就极其广泛，甚至是MySQL数据库OLTP（Online Transaction Processing，在线事务处理）应用中使用最广泛的存储引擎。还是那句话，我们应该根据具体的应用选择适合的存储引擎，以下是对一些存储引擎的简单介绍，以便于大家选择时参考。

1.3.1   InnoDB存储引擎

InnoDB存储引擎支持事务，主要面向在线事务处理（OLTP）方面的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认情况下读取操作不会产生锁。MySQL 在Windows版本下的InnoDB是默认的存储引擎，同时InnoDB默认地被包含在所有的MySQL二进制发布版本中。

InnoDB存储引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由InnoDB自身进行管理。从MySQL 4.1（包括4.1）版本开始，它可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中。与Oracle类似，InnoDB存储引擎同样可以使用裸设备（row disk）来建立其表空间。

InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。同时使用一种被称为next-key locking的策略来避免幻读（phantom）现象的产生。除此之外，InnoDB储存引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用的功能。

对于表中数据的存储，InnoDB存储引擎采用了聚集（clustered）的方式，这种方式类似于Oracle的索引聚集表（index organized table，IOT）。每张表的存储都按主键的顺序存放，如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

1.3.2   MyISAM存储引擎

MyISAM存储引擎是MySQL官方提供的存储引擎。其特点是不支持事务、表锁和全文索引，对于一些OLAP（Online Analytical Processing，在线分析处理）操作速度快。除Windows版本外，是所有MySQL版本默认的存储引擎。

MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。可以通过使用myisampack工具来进一步压缩数据文件，因为myisampack工具使用赫夫曼（Huffman）编码静态算法来压缩数据，因此使用myisampack工具压缩后的表是只读的，当然你也可以通过myisampack来解压数据文件。

在MySQL 5.0版本之前，MyISAM默认支持的表大小为4G，如果需要支持大于4G的MyISAM表时，则需要制定MAX_ROWS和AVG_ROW_LENGTH属性。从MySQL 5.0版本开始，MyISAM默认支持256T的单表数据，这足够满足一般应用的需求。

注意：对于MyISAM存储引擎表，MySQL数据库只缓存其索引文件，数据文件的缓存交由操作系统本身来完成，这与其他使用LRU算法缓存数据的大部分数据库大不相同。此外，在MySQL 5.1.23版本之前，无论是在32位还是64位操作系统环境下，缓存索引的缓冲区最大只能设置为4G。在之后的版本中，64位系统可以支持大于4G的索引缓冲区。

1.3.3   NDB存储引擎

2003年，MySQL AB公司从Sony Ericsson公司收购了NDB 集群引擎（图1-1中Cluster引擎）。NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC集群；不过，与Oracle RAC share everything结构不同的是，其结构是share nothing的集群架构，因此能提供更高级别的高可用性。NDB的特点是数据全部放在内存中（从5.1版本开始，可以将非索引数据放在磁盘上），因此主键查找（primary key lookup）的速度极快，并且通过添加NDB数据存储节点（Data Node）可以线性地提高数据库性能，是高可用、高性能的集群系统。

关于NDB存储引擎，有一个问题值得注意，那就是NDB存储引擎的连接操作（JOIN）是在MySQL数据库层完成的，而不是在存储引擎层完成的。这意味着，复杂的连接操作需要巨大的网络开销，因此查询速度很慢。如果解决了这个问题，NDB存储引擎的市场应该是非常巨大的。

注意：MySQL NDB Cluster存储引擎有社区版本和企业版本，并且NDB Cluster已作为Carrier Grade Edition单独下载版本而存在，可以通过http://dev.mysql.com/ downloads/cluster/index.html获得最新版本的NDB Cluster存储引擎。

1.3.4   Memory存储引擎

Memory存储引擎（之前称为HEAP存储引擎）将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。它默认使用哈希索引，而不是我们熟悉的B+树索引。

虽然Memory存储引擎速度非常快，但在使用上还是有一定的限制。比如，其只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型。最重要的是，存储变长字段（varchar）时是按照定常字段（char）的方式进行的，因此会浪费内存（这个问题之前已经提到，eBay的Igor Chernyshev工程师已经给出了Patch方案）。

此外有一点常被忽视的是，MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集（intermediate result）。如果中间结果集大于Memory存储引擎表的容量设置，又或者中间结果含有TEXT或BLOB列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘。之前提到MyISAM不缓存数据文件，因此这时产生的临时表的性能对于查询会有损失。

1.3.5   Archive存储引擎

Archive存储引擎只支持INSERT和SELECT操作，MySQL 5.1开始支持索引。其使用zlib算法将数据行（row）进行压缩后存储，压缩比率一般可达1∶10。正如其名称所示，Archive存储引擎非常适合存储归档数据，如日志信息。Archive存储引擎使用行锁来实现高并发的插入操作，但是本身并不是事物安全的存储引擎，其设计目标主要是提供高速的插入和压缩功能。

1.3.6   Federated存储引擎

Federated存储引擎表并不存放数据，它只是指向一台远程MySQL数据库服务器上的表。这非常类似于SQL Server的链接服务器和Oracle的透明网关，不同的是，当前Federated存储引擎只支持MySQL数据库表，不支持异构数据库表。

1.3.7   Maria存储引擎

Maria存储引擎是新开发的引擎，设计目标主要是用来取代原有的MyISAM存储引擎，从而成为MySQL的默认存储引擎，开发者是MySQL的创始人之一的Michael Widenius。因此，它可以看作是MyISAM的后续版本。其特点是：缓存数据和索引文件，行锁设计，提供MVCC功能，支持事务和非事务安全的选项支持，以及更好的BLOB字符类型的处理性能。

1.3.8   其他存储引擎

除了上面提到的7种存储引擎外，还有很多其他的存储引擎，包括Merge、CSV、Sphinx和Infobright，它们都有各自适用的场合，这里不再一一做介绍了。了解了MySQL拥有这么多存储引擎后，现在我可以回答1.2节中提到的问题了。

为什么MySQL不支持全文索引？不！MySQL支持，MyISAM、Sphinx存储引擎支持全文索引。
MySQL快是因为不支持事务吗？错！MySQL MyISAM存储引擎不支持事务，但是InnoDB支持。快是相对于不同应用来说的，对于ETL这种操作，MyISAM当然有其优势。
当表的数据量大于1 000W时，MySQL的性能会急剧下降吗？不！MySQL是数据库，不是文件，随着数据行数的增加，性能当然会有所下降，但是这些下降不是线性的，如果你选择了正确的存储引擎以及正确的配置，再大的数据量MySQL也是能承受的。如官方手册上提及的，Mytrix和Inc.在InnoDB上存储了超过1TB的数据，还有一些其他网站使用InnoDB存储引擎处理平均每秒800次插入/更新的操作。

1.4 各种存储引擎之间的比较
通过1.3节的介绍，我们了解了存储引擎是MySQL体系结构的核心。本节我们将通过简单比较几个存储引擎来让读者更直观地理解存储引擎的概念。图1-2取自于MySQL的官方手册，展现了一些常用MySQL存储引擎之间的不同之处，包括存储容量的限制、事务支持、锁的粒度、MVCC支持、支持的索引、备份和复制等。

 图1-2   不同MySQL存储引擎相关特性的比较

可以看到，每种存储引擎的实现都不相同。有些竟然不支持事务，我相信在任何一本关于数据库原理的书中，都可能会提到数据库与传统文件系统的最大区别在于数据库是支持事务的。而MySQL的设计者在开发时却认为不是所有的应用都需要事务，所以存在不支持事务的存储引擎。更有不明其理的人把MySQL称作文件系统数据库，其实不然，只是MySQL的设计思想和存储引擎的关系可能让人产生了理解上的偏差。
可以通过SHOW ENGINES语句查看当前使用的MySQL数据库所支持的存储引擎，也可以通过查找information_schema架构下的ENGINES表来查看，如下所示：

下面我将通过MySQL提供的示例数据库来简单显示各种存储引擎之间的区别。我们将分别运行以下语句，然后统计每次使用各种存储引擎后表的大小。

通过每次的统计我们发现，当最初的表使用MyISAM存储引擎时，表的大小为40.7MB，使用InnoDB存储引擎时表增大到了113.6MB，而使用Archive存储引擎时表的大小却只有20.2MB。该示例只从表的大小方面简单地揭示了各存储引擎的不同。
注意：MySQL提供了一个非常好的用来演示MySQL各项功能的示例数据库，如SQL Server提供的AdventureWorks示例数据库和Oracle提供的示例数据库。就我所知，知道MySQL示例数据库的人很少，可能是因为这个示例数据库没有在安装的时候提示用户是否安装（如Oracle和SQL Server），以及这个示例数据库的下载竟然和文档放在一起。可以通过以下链接找到示例数据库的下载地址：http://dev. mysql.com/doc/。

1.5 连接MySQL
本节将介绍连接MySQL数据库的常用方式。需要理解的是，连接MySQL操作是连接进程和MySQL数据库实例进行通信。从开发的角度来说，本质上是进程通信。如果对进程通信比较了解，应该知道常用的进程通信方式有管道、命名管道、命名字、TCP/IP套接字、Unix域名套接字。MySQL提供的连接方式从本质上看都是上述提及的进程通信方式。

1.5.1   TCP/IP

TCP/IP套接字方式是MySQL在任何平台下都提供的连接方式，也是网络中使用得最多的一种方式。这种方式在TCP/IP连接上建立一个基于网络的连接请求，一般情况下客户端在一台服务器上，而MySQL实例在另一台服务器上，这两台机器通过一个TCP/IP网络连接。例如，我可以在Windows服务器下请求一台远程Linux服务器下的MySQL实例，如下所示。

这里的客户端是Windows，它向一台Host IP为192.168.0.101的MySQL实例发起了TCP/IP连接请求，并且连接成功。之后，就可以对MySQL数据库进行一些数据库操作，如DDL和DML等。

这里需要注意的是，在通过TCP/IP连接到MySQL实例时，MySQL会先检查一张权限视图，用来判断发起请求的客户端IP是否允许连接到MySQL实例。该视图在mysql库下，表名为user，如下所示。

从这张权限表中可以看到，MySQL允许david这个用户在任何IP段下连接该实例，并且不需要密码。此外，还给出了root用户在各个网段下的访问控制权限。

1.5.2   命名管道和共享内存

在Windows 2000、Windows XP、Windows 2003和Windows Vista以及在此之后的Windows操作系统中，如果两个需要通信的进程在同一台服务器上，那么可以使用命名管道，SQL Server数据库默认安装后的本地连接也使用命名管道。在MySQL数据库中，需在配置文件中启用--enable-named-pipe选项。在MySQL 4.1之后的版本中，MySQL还提供了共享内存的连接方式，在配置文件中添加--shared-memory。如果想使用共享内存的方式，在连接时，Mysql客户端还必须使用-protocol=memory选项。

1.5.3   Unix域套接字

在Linux和Unix环境下，还可以使用Unix域套接字。Unix域套接字其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在同一台服务器上的情况下使用。你可以在配置文件中指定套接字文件的路径，如-socket=/tmp/mysql.sock。当数据库实例启动后，我们可以通过下列命令来进行Unix域套接字文件的查找：

知道了Unix套接字文件的路径后，就可以使用该方式进行连接了，如下所示：

1.6 小结
本章首先介绍了数据库和数据库实例的定义，紧接着分析了MySQL的体系结构，从而进一步突出强调了“实例”和“数据库”的区别。我相信，不管是MySQL DBA还是MySQL的开发人员，都应该已经从宏观上了解了MySQL的体系结构，特别是MySQL独有的插件式存储引擎的概念。因为很多MySQL用户很少意识到这一点，从而给他们的管理、使用和开发带来了困扰。

本章还详细讲解了各种常见的表存储引擎的特性、适用情况以及它们之间的区别，以便于大家在选择存储引擎时作为参考。最后强调一点，虽然MySQL有许多存储引擎，但是它们之间不存在优劣性的差异，我们应根据不同的应用选择适合自己的存储引擎。当然，如果你能力很强，完全可以修改存储引擎的源代码，甚至是创建自己需要的特定的存储引擎，这不就是开源的魅力吗？

第2章 InnoDB存储引擎

InnoDB是事务安全的MySQL存储引擎，设计上采用了类似于Oracle的架构。一般而言，在 OLTP的应用中，InnoDB应该作为核心应用表的首选存储引擎。同时，也是因为InnoDB的存在，才使得MySQL变得更有魅力。本章将详细介绍InnoDB存储引擎的体系架构及其不同于其他数据库的特性。

2.1   InnoDB存储引擎概述

2.2   InnoDB体系架构

2.2.1   后台线程

2.2.2   内存

2.3   master thread

2.3.1   master thread源码分析

2.3.2   master thread的潜在问题

2.4   关键特性

2.4.1   插入缓冲

2.4.2   两次写

2.4.3   自适应哈希索引

2.5   启动、关闭与恢复

2.6   InnoDB Plugin = 新版本的InnoDB存储引擎

2.7   小结

2.1 InnoDB存储引擎概述

InnoDB由Innobase Oy公司开发，被包括在MySQL所有的二进制发行版本中，是Windows下默认的表存储引擎。该存储引擎是第一个完整支持ACID事务的MySQL存储引擎（BDB是第一个支持事务的MySQL存储引擎，现在已经停止开发），行锁设计，支持MVCC，提供类似于Oracle风格的一致性非锁定读，支持外键，被设计用来最有效地利用内存和CPU。如果你熟悉Oracle的架构，你会发现InnoDB与Oracle很类似，也许这也是为什么Oracle要急于在MySQL AB之前收购该公司的原因。

Heikki Tuuri（1964年出生于芬兰赫尔辛基）是InnoDB存储引擎的创始人，与著名的Linux创始人Linus同是芬兰赫尔辛基大学校友。在1990年完成赫尔辛基大学的数学逻辑博士学位后，他于1995年成立Innobase Oy公司并担任CEO。同时，我欣喜地注意到，在InnoDB存储引擎的开发团队中，有来自中国科技大学的Calvin Sun。

InnoDB存储引擎已经被许多大型网站使用，例如我们熟知的Yahoo、Facebook、youtube、Flickr，在网络游戏领域有Wow、SecondLife、神兵玄奇等。淘宝网正在有计划地将一部分核心应用由Oracle转到MySQL，目前他们的存储引擎选择是InnoDB。我不是MySQL的布道者，也不是InnoDB的鼓吹者，但是我认为，如果实施一个新的OLTP项目不使用MySQL InnoDB存储引擎将是多么愚蠢。

从MySQL的官方手册还能得知，著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其他站点在InnoDB上处理平均每秒800次插入/更新的操作。这些都证明了InnoDB是一个高性能、高可用、高可扩展的存储引擎。

InnoDB和MySQL一样，在GNU GPL 2下发行。有关更多MySQL证书的信息，可参考http://www.mysql.com/company/legal/licensing/，这里不再做过多介绍。

2.2 InnoDB体系架构

通过第1章我们了解了MySQL的体系结构，现在可能你想更深入地了解InnoDB的架构模型。图2-1简单显示了InnoDB的存储引擎的体系架构。InnoDB有多个内存块，你可以认为这些内存块组成了一个大的内存池，负责如下工作：

 维护所有进程/线程需要访问的多个内部数据结构。
 缓存磁盘上的数据，方便快速地读取，并且在对磁盘文件的数据进行修改之前在这里缓存。
 重做日志（redo log）缓冲。

 图2-1   InnoDB体系结构

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外，将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常情况下InnoDB能恢复到正常运行状态。

2.2.1   后台线程

由于Oracle是多进程的架构（Windows下除外），因此可以通过一些很简单的命令来得知Oracle当前运行的后台进程，如ipcs命令。一般来说，Oracle的核心后台进程有CKPT、DBWn、LGWR、ARCn、PMON、SMON等。

很多DBA问我，InnoDB存储引擎是否也是这样的架构，只不过是多线程版本的实现后，我决定去看InnoDB的源代码，发现InnoDB并不是这样对数据库进程进行操作的。InnoDB存储引擎是在一个被称做master thread的线程上几乎实现了所有的功能。

默认情况下，InnoDB存储引擎的后台线程有7个—4个IO thread，1个master thread，1个锁（lock）监控线程，1个错误监控线程。IO thread的数量由配置文件中的innodb_file_ io_threads参数控制，默认为4，如下所示。

可以看到，4个IO线程分别是insert buffer thread、log thread、read thread、write thread。在Linux平台下，IO thread的数量不能进行调整，但是在Windows平台下可以通过参数innodb_file_io_threads来增大IO thread。InnoDB Plugin版本开始增加了默认IO thread的数量，默认的read thread和write thread分别增大到了4个，并且不再使用innodb_file_ io_threads参数，而是分别使用innodb_read_io_threads和innodb_write_io_threads参数，如下所示。

在Windows下，我们还可以通过Visual Studio来调试MySQL，并设置断点来观察所有的线程信息，如图2-2所示。

 图2-2   Windows下InnoDB存储引擎的线程

2.2.2   内存

InnoDB存储引擎内存由以下几个部分组成：缓冲池（buffer pool）、重做日志缓冲池（redo log buffer）以及额外的内存池（additional memory pool），分别由配置文件中的参数innodb_buffer_pool_size和innodb_log_buffer_size的大小决定。以下显示了一台MySQL数据库服务器，它将InnoDB存储引擎的缓冲池、重做日志缓冲池以及额外的内存池分别设置为2.5G、8M和8M（分别以字节显示）。

缓冲池是占最大块内存的部分，用来存放各种数据的缓存。因为InnoDB的存储引擎的工作方式总是将数据库文件按页（每页16K）读取到缓冲池，然后按最近最少使用（LRU）的算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改，总是首先修改在缓存池中的页（发生修改后，该页即为脏页），然后再按照一定的频率将缓冲池的脏页刷新（flush）到文件。可以通过命令SHOW ENGINE INNODB STATUS来查看innodb_buffer_ pool的具体使用情况，如下所示。

在BUFFER POOL AND MEMORY里可以看到InnoDB存储引擎缓冲池的使用情况，buffer pool size表明了一共有多少个缓冲帧（buffer frame），每个buffer frame为16K，所以这里一共分配了65536*16/1024=1G内存的缓冲池。Free buffers表示当前空闲的缓冲帧，Database pages表示已经使用的缓冲帧，Modified db pages表示脏页的数量。就当前状态看来，这台数据库的压力并不大，因为在缓冲池中有大量的空闲页可供数据库进一步使用。

注意：show engine innodb status的命令显示的不是当前的状态，而是过去某个时间范围内InnoDB存储引擎的状态，从上面的示例中我们可以看到，Per second averages calculated from the last 24 seconds表示的信息是过去24秒内的数据库状态。

具体来看，缓冲池中缓存的数据页类型有: 索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等。不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。图2-3很好地显示了InnoDB存储引擎中内存的结构情况。

参数innodb_buffer_pool_size指定了缓冲池的大小，在32位Windows系统下，参数innodb_buffer_pool_awe_mem_mb还可以启用地址窗口扩展（AWE）功能，突破32位下对于内存使用的限制。但是，在使用这个参数的时候需要注意，一旦启用AWE功能，InnoDB存储引擎将自动禁用自适应哈希索引（adaptive hash index）的功能。

图2-3   InnoDB存储引擎内存结构

日志缓冲将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。该值一般不需要设置为很大，因为一般情况下每一秒钟就会将重做日志缓冲刷新到日志文件，因此我们只需要保证每秒产生的事务量在这个缓冲大小之内即可。

额外的内存池通常被DBA忽略，认为该值并不是十分重要，但恰恰相反的是，该值其实同样十分重要。在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的。在对一些数据结构本身分配内存时，需要从额外的内存池中申请，当该区域的内存不够时，会从缓冲池中申请。InnoDB实例会申请缓冲池（innodb_buffer_pool）的空间，但是每个缓冲池中的帧缓冲（frame buffer）还有对应的缓冲控制对象（buffer control block），而且这些对象记录了诸如LRU、锁、等待等方面的信息，而这个对象的内存需要从额外内存池中申请。因此，当你申请了很大的InnoDB缓冲池时，这个值也应该相应增加。

2.3 master thread

通过对前一小节的学习我们已经知道，InnoDB存储引擎的主要工作都是在一个单独的后台线程master thread中完成的。这一节我们将具体解释该线程的具体实现以及该线程可能存在的问题。

2.3.1   master thread源码分析

master thread的线程优先级别最高。其内部由几个循环（loop）组成：主循环（loop）、后台循环（background loop）、刷新循环（flush loop）、暂停循环（suspend loop）。master thread会根据数据库运行的状态在loop、background loop、 flush loop和suspend loop中进行切换。

loop称为主循环，因为大多数的操作都在这个循环中，其中有两大部分操作：每秒钟的操作和每10秒的操作。伪代码如下：

可以看到，loop循环通过thread sleep来实现，这意味着所谓的每秒一次或每10秒一次的操作是不精确的。在负载很大的情况下可能会有延迟（delay），只能说大概在这个频率下。当然，InnoDB源代码中还采用了其他的方法来尽量保证这个频率。

每秒一次的操作包括：

 日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）。
 合并插入缓冲（可能）。
 至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）。
 如果当前没有用户活动，切换到background loop（可能）。
即使某个事务还没有提交，InnoDB存储引擎仍然会每秒将重做日志缓冲中的内容刷新到重做日志文件。这一点是必须知道的，这可以很好地解释为什么再大的事务commit的时间也是很快的。

合并插入缓冲（insert buffer）并不是每秒都发生。InnoDB存储引擎会判断当前一秒内发生的IO次数是否小于5次，如果小于5次，InnoDB认为当前的IO压力很小，可以执行合并插入缓冲的操作。

同样，刷新100个脏页也不是每秒都在发生。InnoDB存储引擎通过判断当前缓冲池中脏页的比例（buf_get_modified_ratio_pct）是否超过了配置文件中innodb_max_ dirty_pages_pct这个参数（默认为90，代表90%），如果超过了这个阈值，InnoDB存储引擎认为需要做磁盘同步操作，将100个脏页写入磁盘。

总结上述3个操作，伪代码可以进一步具体化，如下所示：

接着来看每10秒的操作，包括如下内容：

 刷新100个脏页到磁盘（可能）。
 合并至多5个插入缓冲（总是）。
 将日志缓冲刷新到磁盘（总是）。
 删除无用的Undo页（总是）。
 刷新100个或者10个脏页到磁盘（总是）。
 产生一个检查点（总是）。
在以上的过程中，InnoDB存储引擎会先判断过去10秒之内磁盘的IO操作是否小于200次。如果是，InnoDB存储引擎认为当前有足够的磁盘IO操作能力，因此将100个脏页刷新到磁盘。接着，InnoDB存储引擎会合并插入缓冲。不同于每1秒操作时可能发生的合并插入缓冲操作，这次的合并插入缓冲操作总会在这个阶段进行。之后，InnoDB存储引擎会再执行一次将日志缓冲刷新到磁盘的操作，这与每秒发生的操作是一样的。

接着InnoDB存储引擎会执行一步full purge操作，即删除无用的Undo页。对表执行update、delete这类操作时，原先的行被标记为删除，但是因为一致性读（consistent read）的关系，需要保留这些行版本的信息。但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，比如有时候可能还有查询操作需要读取之前版本的Undo信息，如果可以，InnoDB会立即将其删除。从源代码中可以发现，InnoDB存储引擎在操作full purge时，每次最多删除20个Undo页。

然后，InnoDB存储引擎会判断缓冲池中脏页的比例（buf_get_modified_ratio_pct），如果有超过70%的脏页，则刷新100个脏页到磁盘；如果脏页的比例小于70%，则只需刷新10%的脏页到磁盘。

最后，InnoDB存储引擎会产生一个检查点（checkpoint），InnoDB存储引擎的检查点也称为模糊检查点（fuzzy checkpoint）。InnoDB存储引擎在checkpoint时并不会把所有缓冲池中的脏页都写入磁盘，因为这样可能会对性能产生影响，而只是将最老日志序列号（oldest LSN）的页写入磁盘。

现在，我们可以完整地把主循环（main loop）的伪代码写出来了，内容如下：

接着来看background loop，若当前没有用户活动（数据库空闲时）或者数据库关闭时，就会切换到这个循环。这个循环会执行以下操作：

 删除无用的Undo页（总是）。
 合并20个插入缓冲（总是）。
 跳回到主循环（总是）。
 不断刷新100个页，直到符合条件（可能，跳转到flush loop中完成）。

如果flush loop中也没有什么事情可以做了，InnoDB存储引擎会切换到suspend_loop，将master thread挂起，等待事件的发生。若启用了InnoDB存储引擎，却没有使用任何InnoDB存储引擎的表，那么master thread总是处于挂起状态。

最后，master thread完整的伪代码如下:

从InnoDB Plugin开始，用命令SHOW ENGINE INNODB STATUS可以查看当前master thread的状态信息，如下所示：

这里可以看到主循环执行了45次，每秒sleep的操作执行了45次（说明负载不是很大），10秒一次的活动执行了4次，符合1∶10。background loop执行了6次，flush loop执行了6次。因为当前这台服务器的压力很小，所以能在理论值上运行。但是，如果是在一台压力很大的MySQL服务器上，我们看到的可能会是下面的情景：

可以看到当前主循环运行了2188次，但是循环中的每一秒钟SLEEP的操作只运行了

1537次。这是因为InnoDB对其内部进行了一些优化，当压力大时并不总是等待1秒。所以说，我们并不能认为1_second和sleeps的值总是相等的。在某些情况下，可以通过两者之间差值的比较来反映当前数据库的负载压力。

2.3.2   master thread的潜在问题

在了解了master thread的具体实现过程后，我们会发现InnoDB存储引擎对于IO其实是有限制的，在缓冲池向磁盘刷新时其实都做了一定的硬性规定（hard coding）。在磁盘技术飞速发展的今天，当固态磁盘出现时，这种规定在很大程度上限制了InnoDB存储引擎对磁盘IO的性能，尤其是写入性能。

从前面的伪代码来看，无论何时，InnoDB存储引擎最多都只会刷新100个脏页到磁盘，合并20个插入缓冲。如果是在密集写的应用程序中，每秒中可能会产生大于100个的脏页，或是产生大于20个插入缓冲，此时master thread似乎会“忙不过来”，或者说它总是做得很慢。即使磁盘能在1秒内处理多于100个页的写入和20个插入缓冲的合并，由于hard coding，master thread也只会选择刷新100个脏页和合并20个插入缓冲。同时，当发生宕机需要恢复时，由于很多数据还没有刷新回磁盘，所以可能会导致恢复需要很快的时间，尤其是对于insert buffer。

这个问题最初是由Google的工程师Mark Callaghan提出的，之后InnoDB对其进行了修正并发布了补丁。InnoDB存储引擎的开发团队参考了Google的patch，提供了类似的方法来修正该问题。因此InnoDB Plugin开始提供了一个参数，用来表示磁盘IO的吞吐量，参数为innodb_io_capacity，默认值为200。对于刷新到磁盘的数量，会按照innodb_io_capacity的百分比来刷新相对数量的页。规则如下：

 在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity数值的5%。
 在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity。

如果你使用了SSD类的磁盘，或者将几块磁盘做了RAID，当你的存储拥有更高的IO速度时，完全可以将innodb_io_capacity的值调得再高点，直到符合你的磁盘IO的吞吐量为止。

另一个问题是参数innodb_max_dirty_pages_pct的默认值，在MySQL 5.1版本之前（包括5.1），该值的默认值为90，意味着脏页占缓冲池的90%。但是该值“太大”了，因为你会发现，InnoDB存储引擎在每1秒刷新缓冲池和flush loop时，会判断这个值，如果大于innodb_max_dirty_pages_pct，才刷新100个脏页。因此，如果你有很大的内存或你的数据库服务器的压力很大，这时刷新脏页的速度反而可能会降低。同样，在数据库的恢复阶段可能需要更多的时间。

在很多论坛上都有对这个问题的讨论，有人甚至将这个值调到了20或10，然后测试发现性能会有所提高，但是将innodb_max_dirty_pages_pct调到20或10会增加磁盘的压力，对系统的负担还是会有所增加。Google对这个问题进行了测试，可以证明20并不是一个最优值。而从InnoDB Plugin开始，innodb_max_dirty_pages_pct默认值变为了75，和Google测试的80比较接近。这样既可以加快刷新脏页的频率，也能保证磁盘IO的负载。

InnoDB Plugin带来的另一个参数是innodb_adaptive_flushing（自适应地刷新），该值影响每1秒刷新脏页的数量。原来的刷新规则是：如果脏页在缓冲池所占的比例小于innodb_max_dirty_pages_pct时，不刷新脏页。大于innodb_max_dirty_pages_pct时，刷新100个脏页，而innodb_adaptive_flushing参数的引入，InnoDB存储引擎会通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页最合适的数量。粗略地翻阅源代码后你会发现，buf_flush_get_desired_flush_rate是通过判断产生重做日志的速度来判断最合适的刷新脏页的数量。因此，当脏页的比例小于innodb_max_dirty_pages_pct时，也会刷新一定量的脏页。

通过上述的讨论和解释，从InnoDB Plugin开始，master thread的伪代码最终变成了：

很多测试都显示，InnoDB Plugin较之以前的InnoDB存储引擎在性能方面有了极大的提高，其实这与以上master thread的改动是密不可分的，因为InnoDB存储引擎的核心操作大部分都是在master thread中。

2.4 关键特性

InnoDB存储引擎的关键特性包括插入缓冲、两次写（double write）、自适应哈希索引（adaptive hash index）。这些特性为InnoDB存储引擎带来了更好的性能和更高的可靠性。

2.4.1   插入缓冲

插入缓冲是InnoDB存储引擎关键特性中最令人激动的。不过，这个名字可能会让人认为插入缓冲是缓冲池中的一个部分。其实不然，InnoDB缓冲池中有Insert Buffer信息固然不错，但是Insert Buffer和数据页一样，也是物理页的一个组成部分。

我们知道，主键是行唯一的标识符，在应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。比如说我们按下列SQL定义的表。

id列是自增长的，这意味着当执行插入操作时，id列会自动增长，页中的行记录按id执行顺序存放。一般情况下，不需要随机读取另一页执行记录的存放。因此，在这样的情况下，插入操作一般很快就能完成。但是，不可能每张表上只有一个聚集索引，在更多的情况下，一张表上有多个非聚集的辅助索引（secondary index）。比如，我们还需要按照name这个字段进行查找，并且name这个字段不是唯一的。即，表是按如下的SQL语句定义的：

这样的情况下产生了一个非聚集的并且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键id的执行顺序存放，但是对于非聚集索引，叶子节点的插入不再是顺序的了。这时就需要离散地访问非聚集索引页，插入性能在这里变低了。然而这并不是这个name字段上索引的错误，因为B+树的特性决定了非聚集索引插入的离散性。

InnoDB存储引擎开创性地设计了插入缓冲，对于非聚集索引的插入或更新操作，不是每一次直接插入索引页中。而是先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放入一个插入缓冲区中，好似欺骗数据库这个非聚集的索引已经插到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对非聚集索引执行插入和修改操作的性能。

插入缓冲的使用需要满足以下两个条件：

 索引是辅助索引。
 索引不是唯一的。

当满足以上两个条件时，InnoDB存储引擎会使用插入缓冲，这样就能提高性能了。不过考虑一种情况，应用程序执行大量的插入和更新操作，这些操作都涉及了不唯一的非聚集索引，如果在这个过程中数据库发生了宕机，这时候会有大量的插入缓冲并没有合并到实际的非聚集索引中。如果是这样，恢复可能需要很长的时间，极端情况下甚至需要几个小时来执行合并恢复操作。

辅助索引不能是唯一的，因为在把它插入到插入缓冲时，我们并不去查找索引页的情况。如果去查找肯定又会出现离散读的情况，插入缓冲就失去了意义。

可以通过命令SHOW ENGINE INNODB STATUS来查看插入缓冲的信息：

seg size显示了当前插入缓冲的大小为11 336*16KB，大约为177MB，free list len代表了空闲列表的长度，size代表了已经合并记录页的数量。下面一行可能是我们真正关心的，因为它显示了提高性能了。Inserts代表插入的记录数，merged recs代表合并的页的数量，merges代表合并的次数。merges∶merged recs大约为3∶1，代表插入缓冲将对于非聚集索引页的IO请求大约降低了3倍。

目前插入缓冲存在一个问题是，在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可以占用1/2的缓冲池内存。以下是InnoDB存储引擎源代码中对insert buffer的初始化操作：

这对其他的操作可能会带来一定的影响。Percona已发布一些patch来修正插入缓冲占用太多缓冲池内存的问题，具体的可以到http://www.percona.com/percona-lab.html查找。简单来说，修改IBUF_POOL_SIZE_PER_MAX_SIZE就可以对插入缓冲的大小进行控制，例如，将IBUF_POOL_SIZE_PER_MAX_SIZE改为3，则最大只能使用1/3的缓冲池内存。

2.4.2   两次写

如果说插入缓冲带给InnoDB存储引擎的是性能，那么两次写带给InnoDB存储引擎的是数据的可靠性。当数据库宕机时，可能发生数据库正在写一个页面，而这个页只写了一部分（比如16K的页，只写前4K的页）的情况，我们称之为部分写失效（partial page write）。在InnoDB存储引擎未使用double write技术前，曾出现过因为部分写失效而导致数据丢失的情况。

有人也许会想，如果发生写失效，可以通过重做日志进行恢复。这是一个办法。但是必须清楚的是，重做日志中记录的是对页的物理操作，如偏移量800，写'aaaa'记录。如果这个页本身已经损坏，再对其进行重做是没有意义的。这就是说，在应用（apply）重做日志前，我们需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。InnoDB存储引擎doublewrite的体系架构如图2-4所示：

 图2-4   InnoDB存储引擎doublewrite架构

doublewrite由两部分组成：一部分是内存中的doublewrite buffer，大小为2MB；另一部分是物理磁盘上共享表空间中连续的128个页，即两个区（extent），大小同样为2MB。当缓冲池的脏页刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次写入1MB到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入则是离散的。可以通过以下命令观察到doublewrite运行的情况：

可以看到，doublewrite一共写了6 325 194个页，但实际的写入次数为100 399，基本上符合64∶1。如果发现你的系统在高峰时Innodb_dblwr_pages_written: Innodb_dblwr_writes远小于64∶1，那么说明你的系统写入压力并不是很高。

如果操作系统在将页写入磁盘的过程中崩溃了，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到改页的一个副本，将其拷贝到表空间文件，再应用重做日志。下面显示了由doublewrite进行恢复的一种情况：

参数skip_innodb_doublewrite可以禁止使用两次写功能，这时可能会发生前面提及的写失效问题。不过，如果你有多台从服务器（slave server），需要提供较快的性能（如slave上做的是RAID0），也许启用这个参数是一个办法。不过，在需要提供数据高可靠性的主服务器（master server）上，任何时候我们都应确保开启两次写功能。

注意：有些文件系统本身就提供了部分写失效的防范机制，如ZFS文件系统。在这种情况下，我们就不要启用doublewrite了。

2.4.3   自适应哈希索引

哈希（hash）是一种非常快的查找方法，一般情况下查找的时间复杂度为O（1）。常用于连接（join）操作，如SQL Server和Oracle中的哈希连接（hash join）。但是SQL Server和Oracle等常见的数据库并不支持哈希索引（hash index）。MySQL的Heap存储引擎默认的索引类型为哈希，而InnoDB存储引擎提出了另一种实现方法，自适应哈希索引（adaptive hash index）。

InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

根据InnoDB的官方文档显示，启用自适应哈希索引后，读取和写入速度可以提高2倍；对于辅助索引的连接操作，性能可以提高5倍。在我看来，自适应哈希索引是非常好的优化模式，其设计思想是数据库自优化（self-tuning），即无需DBA对数据库进行调整。

通过命令SHOW ENGINE INNODB STATUS可以看到当前自适应哈希索引的使用状况，如下所示：

现在可以看到自适应哈希索引的使用信息了，包括自适应哈希索引的大小、使用情况、每秒使用自适应哈希索引搜索的情况。值得注意的是，哈希索引只能用来搜索等值的查询，如select * from table where index_col = 'xxx'，而对于其他查找类型，如范围查找，是不能使用的。因此，这里出现了non-hash searches/s的情况。用hash searches : non-hash searches命令可以大概了解使用哈希索引后的效率。

由于自适应哈希索引是由InnoDB存储引擎控制的，所以这里的信息只供我们参考。不过我们可以通过参数innodb_adaptive_hash_index来禁用或启动此特性，默认为开启。

2.5 启动、关闭与恢复
InnoDB存储引擎是MySQL的存储引擎之一，因此InnoDB存储引擎的启动和关闭更准确地是指在MySQL实例的启动过程中对InnoDB表存储引擎的处理过程。

在关闭时，参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。该参数可取值为0、1、2。0代表当MySQL关闭时，InnoDB需要完成所有的full purge和merge insert buffer操作，这会需要一些时间，有时甚至需要几个小时来完成。如果在做InnoDB plugin升级，通常需要将这个参数调为0，然后再关闭数据库。1是该参数的默认值，表示不需要完成上述的full purge和merge insert buffer操作，但是在缓冲池的一些数据脏页还是会刷新到磁盘。2表示不完成full purge和merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。这样不会有任何事务会丢失，但是MySQL数据库下次启动时，会执行恢复操作（recovery）。

当正常关闭MySQL数据库时，下一次启动应该会很正常。但是，如果没有正常地关闭数据库，如用kill命令关闭数据库，在MySQL数据库运行过程中重启了服务器，或者在关闭数据库时将参数innodb_fast_shutdown设为了2，MySQL数据库下次启动时都会对InnoDB存储引擎的表执行恢复操作。

参数innodb_force_recovery影响了整个InnoDB存储引擎的恢复状况。该值默认为0，表示当需要恢复时执行所有的恢复操作。当不能进行有效恢复时，如数据页发生了corruption，MySQL数据库可能会宕机，并把错误写入错误日志中。

但是，在某些情况下，我们可能并不需要执行完整的恢复操作，我们自己知道如何进行恢复。比如正在对一个表执行alter table操作，这时意外发生了，数据库重启时会对InnoDB表执行回滚操作。对于一个大表，这需要很长时间，甚至可能是几个小时。这时我们可以自行进行恢复，例如可以把表删除，从备份中重新将数据导入表中，这些操作的速度可能要远远快于回滚操作。

innodb_force_recovery还可以设置为6个非零值：1～6。大的数字包含了前面所有小数字的影响，具体情况如下。

 1（SRV_FORCE_IGNORE_CORRUPT）：忽略检查到的corrupt页。
 2（SRV_FORCE_NO_BACKGROUND）：阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
 3（SRV_FORCE_NO_TRX_UNDO）：不执行事务回滚操作。
 4（SRV_FORCE_NO_IBUF_MERGE）：不执行插入缓冲的合并操作。
 5（SRV_FORCE_NO_UNDO_LOG_SCAN）：不查看撤销日志（Undo Log），InnoDB存储引擎会将未提交的事务视为已提交。
 6（SRV_FORCE_NO_LOG_REDO）：不执行前滚的操作。
需要注意的是，当设置参数innodb_force_recovery大于0后，可以对表进行select、create、drop操作，但insert、update或者delete这类操作是不允许的。

我们来做个实验，模拟故障的发生。在第一会话中，对一张接近1 000W行的InnoDB存储引擎表执行更新操作，但是完成后不要马上提交：

start transaction语句开启了事务，同时防止了自动提交的发生，update操作则会产生大量的回滚日志。这时，我们人为地kill掉MySQL数据库服务器。

通过kill命令，我们人为地模拟了一次数据库宕机故障，当MySQL数据库下次启动时会对update的这个事务执行回滚操作，而这些信息都会记录在错误日志文件中，默认后缀名为err。如果查看错误日志文件，可得到如下结果：

可以看到，如果采用默认的策略，即把innodb_force_recovery设为0，InnoDB会在每次启动后对发生问题的表执行恢复操作，通过错误日志文件，可知这次回滚操作需要回滚8 867 280行记录，总共耗时约9分多钟。

我们做再做一次同样的测试，只不过在启动MySQL数据库前将参数innodb_force_ recovery设为3，然后观察InnoDB存储引擎是否还会执行回滚操作，查看错误日志文件，可看到：

这里出现了“！！！”，InnoDB警告你已经将innodb_force_recovery设置为3，不会进行undo的回滚操作了。因此数据库很快启动完成，但是你应该很小心当前数据库的状态，并仔细确认是否不需要回滚操作。

2.6 InnoDB Plugin = 新版本的InnoDB存储引擎
MySQL 5.1这个版本的一个很大的变动是采用了插件式的架构。通过分析源代码会发现，每个存储引擎是通过继承一个handler的C++基类（之前的版本大多是通过函数指针来实现）。如果查看InnoDB存储引擎的源代码，会看到下面的内容：

这样设计的好处是，现在所有的存储引擎都是真正的插件式了。在以前，如果发现一个InnoDB存储引擎的Bug，你能做的就是等待MySQL新版本的发布，InnoDB公司本身对此只能通过补丁的形式来解决，你还需要重新编译一次MySQL才行。现在，你可以得到一个新版本的InnoDB存储引擎，用来替代有Bug的旧引擎。这样，当有重大问题时，不用等待MySQL的新版本，只要相应的存储引擎提供商发布新版本即可。
当然，InnoDB Plugin是上述的一个实现，但更重要的是InnoDB Plugin还给我们带来了其他非常棒的新特性，你应该把它看做是新版本的InnoDB存储引擎，我们通过参数innodb_version来查看当前InnoDB的版本：

在MySQL 5.1.38前的版本中，当你需要安装InnoDB Plugin时，必须下载Plugin的文件，解压后再进行一系列的安装。从MySQL 5.1.38开始，MySQL包含了2个不同版本的InnoDB存储引擎—一个是旧版本的引擎，称之为build-in innodb；另一个是1.0.4版本的InnoDB存储引擎。如果你想使用新的InnDB Plugin引擎，只需在配置文件做如下设置：

在写本书时，InnoDB Plugin的最新版本是1.0.4，并已经是MySQL 5.4版本默认的InnoDB存储引擎版本，其新功能包括以下几点。

 快速索引重建。
 更好的多核性能。
 新的页结构。
 页压缩功能。
 更好的BLOB处理能力。
感兴趣的读者可以在官网http://www.innodb.com/products/innodb_plugin/上获得更多的信息。本书在之后的章节中会对新版本InnoDB存储引擎进行详细讲解。

2.7 小结
本章首先介绍了InnoDB存储引擎的历史、InnoDB存储引擎的体系结构（包括后台线程和内存结构），之后又详细讲解了InnoDB存储引擎的关键特性，这些特性使得InnoDB存储引擎变得更具“魅力”，最后叙述了启动和关闭MySQL时一些配置文件参数对InnoDB存储引擎的影响。

通过本章的铺垫，后文就能更深入和全面地讲解InnoDB引擎。第3章开始介绍MySQL的文件，包括MySQL本身的文件和与InnoDB存储引擎有关的文件，之后的章节将介绍基于InnoDB存储引擎的表，并揭示其内部的存储构造。

第3章 文件
本章将分析构成MySQL数据库和InnoDB存储引擎表的各种类型文件，如下所示。

参数文件：告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。
日志文件：用来记录MySQL实例对某种条件做出响应时写入的文件。如错误日志文件、二进制日志文件、满查询日志文件、查询日志文件等。
socket文件：当用Unix域套接字方式进行连接时需要的文件。
pid文件：MySQL实例的进程ID文件。
MySQL表结构文件：用来存放MySQL表结构定义文件。
存储引擎文件：因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了数据和索引等数据。本章主要介绍与InnoDB有关的存储引擎文件。
3.1   参数文件

3.1.1   什么是参数

3.1.2   参数类型

3.2   日志文件

3.2.1   错误日志

3.2.2   慢查询日志

3.2.3   查询日志

3.2.4   二进制日志

3.3   套接字文件

3.4   pid文件

3.5   表结构定义文件

3.6   InnoDB存储引擎文件

3.6.1   表空间文件

3.6.2   重做日志文件

3.7   小结

3.1 参数文件

在第1章中已经介绍过了，当MySQL实例启动时，MySQL会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等设置。默认情况下，MySQL实例会按照一定的次序去取，你只需通过命令mysql --help | grep my.cnf来寻找即可。

MySQL参数文件的作用和Oracle的参数文件极其类似；不同的是，Oracle实例启动时若找不到参数文件，是不能进行装载（mount）操作的。MySQL稍微有所不同，MySQL实例可以不需要参数文件，这时所有的参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值。但是，如果MySQL在默认的数据库目录下找不到mysql架构，则启动同样会失败，你可能在错误日志文件中找到如下内容：

MySQL中的mysql架构中记录了访问该实例的权限，当找不到这个架构时，MySQL实例不会成功启动。

和Oracle参数文件不同的是，Oracle的参数文件分为二进制的参数文件（spfile）和文本类型的参数文件（init.ora），而MySQL的参数文件仅是文本的，方便的是，你可以通过一些常用的编辑软件（如vi和emacs）进行参数的编辑。

3.1.1   什么是参数

简单地说，可以把数据库参数看成一个键/值对。第2章已经介绍了一个对于InnoDB存储引擎很重要的参数innodb_buffer_pool_size。如我们将这个参数设置为1G，即innodb_buffer_pool_size=1G,，这里的“键”是innodb_buffer_pool_size，“值”是1G，这就是我们的键值对。可以通过show variables查看所有的参数，或通过like来过滤参数名。从MySQL 5.1版本开始，可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找，如下所示。

无论使用哪种方法，输出的信息基本上都一样的，只不过通过视图GLOBAL_ VARIABLES需要指定视图的列名。推荐使用show variables命令，因为这个命令使用更为简单，各版本的MySQL数据库都支持它。

Oracle的参数有所谓的隐藏参数（undocumented parameter），以供Oracle“内部人士”使用，SQL Server也有类似的参数。有些DBA曾问我，MySQL中是否也有这类参数。我的回答是：没有，也不需要。即使Oracle和SQL Server中都有些所谓的隐藏参数，在绝大多数情况下，这些数据库厂商也不建议你在生产环境中对其进行很大的调整。

3.1.2   参数类型

MySQL参数文件中的参数可以分为两类：动态（dynamic）参数和静态（static）参数。动态参数意味着你可以在MySQL实例运行中进行更改；静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读（read only）的。可以通过SET命令对动态的参数值进行修改，SET的语法如下：

这里可以看到global和session关键字，它们表明该参数的修改是基于当前会话还是整个实例的生命周期。有些动态参数只能在会话中进行修改，如autocommit；有些参数修改完后，在整个实例生命周期中都会生效，如binlog_cache_size；而有些参数既可以在会话又可以在整个实例的生命周期内生效，如read_buffer_size。举例如下：

上面我将read_buffer_size的会话值从2MB调整为了512KB，你可以看到全局的read_buffer_size的值仍然是2MB，也就是说，如果有另一个会话登录到MySQL实例，它的read_buffer_size的值是2MB，而不是512KB。这里使用了set global|session来改变动态变量的值。我们同样可以直接使用set @@globl|@@session来更改，如下所示：

这次我们把read_buffer_size全局值更改为1MB，而当前会话的read_buffer_size的值还是512KB。这里需要注意的是，对变量的全局值进行了修改，在这次的实例生命周期内都有效，但MySQL实例本身并不会对参数文件中的该值进行修改。也就是说下次启动时，MySQL实例还是会读取参数文件。如果你想让数据库实例下一次启动时该参数还是保留为当前修改的值，则必须修改参数文件。要想知道MySQL所有动态变量的可修改范围，可以参考MySQL官方手册的第5.1.4.2节（Dynamic System Variables）的相关内容。

对于静态变量，如果对其进行修改，会得到类似如下的错误：

3.2 日志文件

日志文件记录了影响MySQL数据库的各种类型活动。MySQL数据库中常见的日志文件有错误日志、二进制日志、慢查询日志、查询日志。这些日志文件为DBA对数据库优化、问题查找等带来了极大的便利。

3.2.1   错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQL DBA在遇到问题时应该首先查看该文件。该文件不但记录了出错信息，也记录一些警告信息或者正确的信息。总的来说，这个文件更类似于Oracle的alert文件，只不过在默认情况下是err结尾。你可以通过show variables like 'log_error'来定位该文件，如：

可以看到错误文件的路径和文件名，默认情况下错误文件的文件名为服务器的主机名。如上面我们看到的，该主机名为stargazer，所以错误文件名为startgazer.err。当出现MySQL数据库不能正常启动时，第一个必须查找的文件应该就是错误日志文件，该文件记录了出错信息，能很好地指导我们找到问题，如果当数据库不能重启，通过查错误日志文件可以得到如下内容：

这里，错误日志文件提示了你找不到权限库mysql，所以启动失败。有时我们可以直接在错误日志文件里得到优化的帮助，因为有些警告（warning）很好地说明了问题所在。而这时我们可以不需要通过查看 <script src="/javascripts/tinymce/themes/advanced/langs/zh.js" type="text/javascript"></script><script src="/javascripts/tinymce/plugins/javaeye/langs/zh.js" type="text/javascript"></script> 数据库状态来得知，如：

3.2.2   慢查询日志

前一小节提到可以通过错误日志得到一些关于数据库优化的信息帮助，而慢查询能为SQL语句的优化带来很好的帮助。可以设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。该阈值可以通过参数long_query_time来设置，默认值为10，代表10秒。

默认情况下，MySQL数据库并不启动慢查询日志，你需要手工将这个参数设为ON，然后启动，可以看到如下结果：

这里需要注意两点。首先，设置long_query_time这个阈值后，MySQL数据库会记录运行时间超过该值的所有SQL语句，但对于运行时间正好等于long_query_time的情况，并不会被记录下。也就是说，在源代码里是判断大于long_query_time，而非大于等于。其次，从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行时间，之前仅用秒为单位记录。这样可以更精确地记录SQL的运行时间，供DBA分析。对DBA来说，一条SQL语句运行0.5秒和0.05秒是非常不同的，前者可能已经进行了表扫，后面可能是走了索引。下面的代码中，是在MySQL 5.1中将long_query_time设置为了0.05：

另一个和慢查询日志有关的参数是log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件。首先，确认打开了log_queries_not_using_indexes:

这里详细记录了SQL语句的信息，如上述SQL语句运行的账户和IP、运行时间、锁定的时间、返回行等。我们可以通过慢查询日志来找出有问题的SQL语句，对其进行优化。随着MySQL数据库服务器运行时间的增加，可能会有越来越多的SQL查询被记录到了慢查询日志文件中，这时要分析该文件就显得不是很容易了。MySQL这时提供的mysqldumpslow命令，可以很好地解决这个问题：

如果我们想得到锁定时间最长的10条SQL语句，可以运行：

MySQL 5.1开始可以将慢查询的日志记录放入一张表中，这使我们的查询更加直观。慢查询表在mysql架构下，名为slow_log。其表结构定义如下：

参数log_output指定了慢查询输出的格式，默认为FILE，你可以将它设为TABLE，然后就可以查询mysql架构下的slow_log表了，如：

参数log_output是动态的，并且是全局的。我们可以在线进行修改。在上表中我设置了睡眠（sleep）10秒，那么这句SQL语句就会被记录到slow_log表了。

查看slow_log表的定义会发现，该表使用的是CSV引擎，对大数据量下的查询效率可能不高。我们可以把slow_log表的引擎转换到MyISAM，用来进一步提高查询的效率。但是，如果已经启动了慢查询，将会提示错误：

不能忽视的是，将slow_log表的存储引擎更改为MyISAM后，对数据库还是会造成额外的开销。不过好在很多关于慢查询的参数都是动态的，我们可以方便地在线进行设置或者修改。

3.2.3   查询日志

查询日志记录了所有对MySQL数据库请求的信息，不论这些请求是否得到了正确的执行。默认文件名为：主机名.log。我们查看一个查询日志：

通过上述查询日志你会发现，查询日志甚至记录了对access denied的请求。同样，从MySQL 5.1开始，可以将查询日志的记录放入mysql架构下的general_log表，该表的使用方法和前面小节提到的slow_log基本一样，这里不再赘述。

3.2.4   二进制日志

二进制日志记录了对数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，如果你还想记录SELECT和SHOW操作，那只能使用查询日志，而不是二进制日志了。此外，二进制还包括了执行数据库更改操作的时间和执行时间等信息。二进制日志主要有以下两种作用：

 恢复（recovery）。某些数据的恢复需要二进制日志，如当一个数据库全备文件恢复后，我们可以通过二进制日志进行point-in-time的恢复。
 复制（replication）。其原理与恢复类似，通过复制和执行二进制日志使得一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。
通过配置参数log-bin[=name]可以启动二进制日志。如果不指定name，则默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录（datadir），如：

这里的bin_log.00001即为二进制日志文件，我们在配置文件中指定了名称，所以没有用默认的文件名。bin_log.index为二进制的索引文件，用来存储过往生产的二进制日志序号，通常情况下，不建议手工修改这个文件。

二进制日志文件在默认情况下并没有启动，需要你手动指定参数来启动。可能有人会质疑，开启这个选项是否会对数据库整体性能有所影响。不错，开启这个选项的确会影响性能，但是性能的损失十分有限。根据MySQL官方手册中的测试表明，开启二进制日志会使得性能下降1%。但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以并且是应该被接受的。

以下配置文件的参数影响着二进制日志记录的信息和行为：

参数max-binlog-size指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件。从MySQL 5.0开始的默认值为

1 073 741 824，代表1GB（之前的版本max-binlog-size默认大小为1.1GB）。

当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中，等该事务提交时（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32KB。此外，binlog_cache_size是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态,可以判断当前binlog_cache_size的设置是否合适。Binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。现在来看一个数据库的状态：

使用缓冲次数33 553次，临时文件使用次数为0。看来，32KB的缓冲大小对于当前这个MySQL数据库完全够用，所以暂时没有必要增加binlog_cache_size的值。

默认情况下，二进制日志并不是在每次写的时候同步到磁盘（我们可以理解为缓冲写）。因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中。这会给恢复和复制带来问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。该默认值为0，如果使用InnoDB存储引擎进行复制，并且想得到最大的高可用性，建议将该值设为ON。不过该值为ON时，确实会对数据库的IO系统带来一定的影响。

但是，即使将sync_binlog设为1，还是会有一种情况会导致问题的发生。当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog设为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，因为COMMIT操作并没有发生，所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这个问题可以通过将参数innodb_support_xa设为1来解决，虽然innodb_support_xa与XA事务有关，但它同时也确保了二进制日志和InnoDB存储引擎数据文件的同步。

参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的日志。默认为空，表示需要将所有库的日志同步到二进制日志。

如果当前数据库是复制中的slave角色，则它不会将从master取得并执行的二进制日志写入自己的二进制日志文件中。如果需要写入，则需要设置log-slave-update。如果你需要搭建master=>slave=>slave架构的复制，则必须设置该参数。

binlog_format参数十分重要，这影响了记录二进制日志的格式。在MySQL 5.1版本之前，没有这个参数。所有二进制文件的格式都是基于SQL语句（statement）级别的，因此基于这个格式的二进制日志文件的复制（Replication）和Oracle 逻辑Standby有点相似。同时，对于复制是有一定要求的如rand、uuid等函数，或者有使用触发器等可能会导致主从服务器上表的数据不一致（not sync），这可能使得复制变得没有意义。另一个影响是，你会发现InnoDB存储引擎的默认事务隔离级别是REPEATABLE READ。这其实也是因为二进制日志文件格式的关系，如果使用READ COMMITTED的事务隔离级别（大多数数据库，如Oracle、Microsoft SQL Server数据库的默认隔离级别）会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致。

MySQL 5.1开始引入了binlog_format参数，该参数可设的值有STATEMENT、ROW和MIXED。

（1）STATEMENT格式和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句。

（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况。基于ROW格式的复制类似于Oracle的物理Standby（当然，还是有些区别）。同时，对于上述提及的Statement格式下复制的问题给予了解决。MySQL 5.1版本开始，如果设置了binlog_format为ROW，你可以将InnoDB的事务隔离基本设为READ COMMITTED，以获得更好的并发性。

（3）MIXED格式下，MySQL默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况有：

1）表的存储引擎为NDB，这时对于表的DML操作都会以ROW格式记录。

2）使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数。

3）使用了INSERT DELAY语句。

4）使用了用户定义函数（UDF）。

5）使用了临时表（temporary table）。

此外，binlog_format参数还有对于存储引擎的限制，如表3-1所示。

表3-1   存储引擎二进制日志格式支持情况

binlog_format是动态参数，因此可以在数据库运行环境下进行更改，例如，我们可以将当前会话的binlog_format设为ROW，如：

当然，也可以将全局的binlog_format设置为你想要的格式。不过通常情况下，这个操作可能会带来问题，运行时，请确保更改后不会对你的复制带来影响。如：

通常情况下，我们将参数binlog_format设置为ROW，这可以为数据库的恢复和复制带来更好的可靠性。但是不能忽略一点的是，这会带来二进制文件大小的增加，有些语句下的ROW格式可能需要更大的容量。比如我们有两张一样的表，大小都为100W，执行UPDATE操作，观察二进制日志大小的变化：

可以看到，在binlog_format格式为STATEMENT下，执行UPDATE语句二进制日志大小只增加了200字节（306-106）。如果我们使用ROW格式，同样来操作t2表，可以看到：

这时你会惊讶地发现，同样的操作在ROW格式下竟然需要13 782 094字节，二进制日志文件差不多增加了13MB，要知道t2表的大小也不超过17MB。而且执行时间也有所增加（这里我设置了sync_binlog=1）。这就是因为，这时MySQL数据库不再将逻辑的SQL操作记录到二进制日志，而是记录对于每行的更改记录信息。

上面的这个例子告诉我们，将参数binlog_format设置为ROW，对于磁盘空间要求有了一定的增加。而由于复制是采用传输二进制日志方式实现的，因此复制的网络开销也有了增加。

二进制日志文件的文件格式为二进制（好像有点废话），不能像错误日志文件，慢查询日志文件用cat、head、tail等命令来查看。想要查看二进制日志文件的内容，须通过MySQL提供的工具mysqlbinlog。对于STATEMENT格式的二进制日志文件，使用mysqlbinlog后，看到就是执行的逻辑SQL语句，如：

update t2 set username=upper（username） where id=1，这个可以看到日志的记录以SQL语句的方式（为了排版的方便，省去了一些开始的信息）。在这个情况下，mysqlbinlog和Oracle LogMiner类似。但是如果这时使用ROW格式的记录方式，则会发现mysqlbinlog的结果变得“不可读”（unreadable），如：

我们看不到执行的SQL语句，反而是一大串我们看不到的字符。其实只要加上参数-v或者-vv，就能清楚地看到执行的具体信息了，-vv会比-v多显示出更新的类型，这次我们加上-vv选项，得到：

现在mysqlbinlog向我们解释了具体做的事情。可以看到，一句简单的update t2 set username=upper（username）where id=1语句记录为了对于整个行更改的信息，这也解释了为什么前面我们更新了10万行的数据，在ROW格式下，二进制日志文件会增大了13MB。

3.3 套接字文件
前面提到过，Unix系统下本地连接MySQL可以采用Unix域套接字方式，这种方式需要一个套接字（socke）文件。套接字文件可由参数socket控制。一般在/tmp目录下，名为mysql.sock：

3.4 pid文件
当MySQL实例启动时，会将自己的进程ID写入一个文件中—该文件即为pid文件。该文件可由参数pid_file控制。默认路径位于数据库目录下，文件名为主机名.pid。

3.5 表结构定义文件
因为MySQL插件式存储引擎的体系结构的关系，MySQL对于数据的存储是按照表的，所以每个表都会有与之对应的文件（对比SQL Server是按照每个数据库下的所有表或索引都存在mdf文件中）。不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。

frm还用来存放视图的定义，如我们创建了一个v_a视图，那么对应地会产生一个v_a.frm文件，用来记录视图的定义，该文件是文本文件，可以直接使用cat命令进行查看：

3.6 InnoDB存储引擎文件
之前介绍的文件都是MySQL数据库本身的文件，和存储引擎无关。除了这些文件外，每个表存储引擎还有其自己独有的文件。这一节将具体介绍和InnoDB存储引擎密切相关的文件，这些文件包括重做日志文件、表空间文件。

3.6.1   表空间文件
InnoDB存储引擎在存储设计上模仿了Oracle，将存储的数据按表空间进行存放。默认配置下，会有一个初始化大小为10MB、名为ibdata1的文件。该文件就是默认的表空间文件（tablespace file）。你可以通过参数innodb_data_file_path对其进行设置。格式如下：

你也可以用多个文件组成一个表空间，同时制定文件的属性，如：

这里将/db/ibdata1和/dr2/db/ibdata2两个文件用来组成表空间。若这两个文件位于不同的磁盘上，则可以对性能带来一定程度的提升。两个文件的文件名后都跟了属性，表示文件idbdata1的大小为2000MB，文件ibdata2的大小为2000MB，但是如果用满了这2000MB后，该文件可以自动增长（autoextend）。
设置innodb_data_file_path参数后，之后对于所有基于InnoDB存储引擎的表的数据都会记录到该文件内。而通过设置参数innodb_file_per_table，我们可以将每个基于InnoDB存储引擎的表单独产生一个表空间，文件名为表名.ibd，这样不用将所有数据都存放于默认的表空间中。下面这台服务器设置了innodb_file_per_table，可以看到：

表Profile、t1、t2都是InnoDB的存储引擎，由于设置参数innodb_file_per_table=ON，因此产生了单独的.ibd表空间文件。需要注意的是，这些单独的表空间文件仅存储该表的数据、索引和插入缓冲等信息，其余信息还是存放在默认的表空间中。图3-1显示了InnoDB存储引擎对于文件的存储方式：

 图3-1   InnoDB表存储引擎文件

3.6.2   重做日志文件
默认情况下会有两个文件，名称分别为ib_logfile0和ib_logfile1。MySQL官方手册中将其称为InnoDB存储引擎的日志文件，不过更准确的定义应该是重做日志文件（redo log file）。为什么强调是重做日志文件呢？因为重做日志文件对于InnoDB存储引擎至关重要，它们记录了对于InnoDB存储引擎的事务日志。
重做日志文件的主要目的是，万一实例或者介质失败（media failure），重做日志文件就能派上用场。如数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。
每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有2个重做日志文件，如默认的ib_logfile0、ib_logfile1。为了得到更高的可靠性，你可以设置多个镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上。日志组中每个重做日志文件的大小一致，并以循环方式使用。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再切换到重做日志文件1中。图3-2显示了一个拥有3个重做日志文件的重做日志文件组。

图3-2   日志文件组

参数innodb_log_file_size、innodb_log_files_in_group、innodb_mirrored_log_groups、innodb_log_group_home_dir影响着重做日志文件的属性。参数innodb_log_file_size指定了重做日志文件的大小；innodb_log_files_in_group指定了日志文件组中重做日志文件的数量，默认为2；innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，代表只有一个日志文件组，没有镜像；innodb_log_group_home_dir指定了日志文件组所在路径，默认在数据库路径下。以下显示了一个关于重做日志组的配置：

重做日志文件的大小设置对于MySQL数据库各方面还是有影响的。一方面不能设置得太大，如果设置得很大，在恢复时可能需要很长的时间；另一方面又不能太小了，否则可能导致一个事务的日志需要多次切换重做日志文件。在错误日志中可能会看到如下警告：

上面错误集中在InnoDB: ERROR: the age of the last checkpoint is 9433645,InnoDB: which exceeds the log group capacity 9433498。这是因为重做日志有一个capacity变量，该值代表了最后的检查点不能超过这个阈值，如果超过则必须将缓冲池（innodb buffer pool）中刷新列表（flush list）中的部分脏数据页写回磁盘。
也许有人会问，既然同样是记录事务日志，那和我们之前的二进制日志有什么区别？首先，二进制日志会记录所有与MySQL有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关其本身的事务日志。其次，记录的内容不同，不管你将二进制日志文件记录的格式设为STATEMENT还是ROW，又或者是MIXED，其记录的都是关于一个事务的具体操作内容。而InnoDB存储引擎的重做日志文件记录的关于每个页（Page）的更改的物理情况（如表3-2所示）。此外，写入的时间也不同，二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目（redo entry）被写入重做日志文件中。

表3-2   重做日志结构

在第2章中已经提到，对于写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redo log buffer）中，然后根据按照一定的条件写入日志文件。图3-3很好地表示了这个过程。

图3-3   重做日志写入过程

上面提到了从日志缓冲写入磁盘上的重做日志文件是按一定条件的，那这些条件有哪些呢？第2章分析了主线程（master thread），知道在主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。另一个触发这个过程是由参数innodb_ flush_log_at_trx_commit控制，表示在提交（commit）操作时，处理重做日志的方式。
参数innodb_flush_log_at_trx_commit可设的值有0、1、2。0代表当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新。而1和2不同的地方在于：1是在commit时将重做日志缓冲同步写到磁盘；2是重做日志异步写到磁盘，即不能完全保证commit时肯定会写入重做日志文件，只是有这个动作。

3.7 小结
本章介绍了与MySQL数据库相关的一些文件，并了解了文件可以分为MySQL数据库文件以及和各存储引擎有关的文件。与MySQL数据库有关的文件中，错误文件和二进制日志文件非常重要。当MySQL数据库发生任何错误时，DBA首先就应该去查看错误文件，从文件提示的内容中找出问题的所在。当然，错误文件不仅记录了错误的内容，也记录了警告的信息，通过一些警告也有助于DBA对于数据库和存储引擎的优化。

二进制日志的作用非常关键，可以用来进行point in time的恢复以及复制（replication）环境的搭建。因此，建议在任何时候时都启用二进制日志的记录。从MySQL 5.1开始，二进制日志支持STATEMENT、ROW、MIX三种格式，用来更好地同步数据库。DBA应该十分清楚三种不同格式之间的差异。

本章的最后介绍了和InnoDB存储引擎相关的文件，包括表空间文件和重做日志文件。表空间文件是用来管理InnoDB存储引擎的存储，分为共享表空间和独立表空间。重做日志非常重要，用来记录InnoDB存储引擎的事务日志，也因为重做日志的存在，才使得InnoDB存储引擎可以提供可靠的事务。

--转自