不同存储结构的文件磁盘io操作次数_MySQL InnoDB存储引擎

第1章 MySQL体系结构和存储引擎

1.1数据库和实例

数据库：物理操作系统文件或其他形式文件类型的集合。实例：MySQL数据库由后台线程以及一个共享内存区组成。共享内存可以被运行 的后台线程所共享。数据库实例才是真正用于操作数据库文件的。

启动MySQL数据库实例，并通过命令ps观察MySQL数据库启动后的进程情况：

./mysqld_safe&ps -ef I grep mysqld

读取配置文件顺序，以读取到的最后一个配置文件中的参数为准

mysql --help | grep my.cnf #/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

1.2 MySQL体系结构

从概念上来说数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合；

数据库实例是程序，是位 于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据 库定义、数据査询、数据维护、数据库运行控制等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

MySQL数据库的体系结构。

MySQL由以下几部分组成:

• 连接池组件

• 管理服务和工具组件

• SQL接口组件

• 査询分析器组件

• 优化器组件

• 缓冲(Cache)组件

• 插件式存储引擎

• 物理文件

MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。存储引擎是基于表的，而不是数据库。

1.3 MySQL存储引擎

MySQL数据库独有的插件式体系结构。存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。

InnoDB存储引擎

• InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。

• InnoDB通过使用多版本并发控制(MVCC)来获得高并发性，并且实现了 SQL 标准的4种隔离级别，默认为REPEATABLE级别。

• 同时，使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生。

• 除此之外，InnoDB储存引擎还提供了插入缓冲(insert buffer)>二次写(double write)、自适应哈希索引(adaptive hash index).预读(read ahead)等高性能和高可用的功能。

• 对于表中数据的存储，InnoDB存储引擎采用了聚集(clustered)的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB存 储引擎会为每一行生成一个6字节的ROWID,并以此作为主键。

MylSAM存储引擎

MylSAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些OLAP 数据库应用。在MySQL 5.5.8版本之前MylSAM存储引擎是默认的存储引擎(除 Windows版本外)。MylSAM存储引擎的缓冲池只缓存(cache)索引文件，而不缓冲数据文件。

MylSAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存 放索引文件。

1.4各存储引擎之间的比较

常用MySQL存储引擎之间的不同之处，包括存储容量的限制、事务支持、锁的粒度、MVCC支持、支持的索引、备份和复制等。

查看当前使用的MySQL数据库所支持的存储引擎

SHOW ENGINES\G

示例数据库来简单显示各存储引擎之间的不同

CREATE TABLE mytest Engine=MyISAM AS SELECT * FROM A;ALTER TABLE mytest Engine=InnoDB;ALTER TABLE mytest Engine=ARCHIVE;

从表的大小方面简单地揭示了各存储引擎的不同：InnoDB>MyISAM>ARCHIVE

第2章InnoDB存储引擎

InnoDB是事务安全的MySQL存储引擎。

2.1 InnoDB存储引擎概述

MySQL 5.5版本开始是默认的表存储引擎。支持ACID事务，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和CPU。

2.3 InnoDB体系架构

InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责 如下工作：维护所有进程/线程需要访问的多个内部数据结构。缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里 缓存。重做日志(redo log)缓冲...

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

2.3.1后台线程

InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不

Master ThreadMaster Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(INSERT BUFFER UNDO页的回收等

IO Thread在InnoDB存储引擎中大量使用了 AIO (Async IO)来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(callback) 处理。分别是 write、read、insert buffer 和 log 10 thread，分别使用 innodb_read_io_threads 和 innodb_write_io_threads 参数进行设置

SHOW VARIABLES LIKE 'innodb_%io_threads'\G

观察 InnoDB 中的 IO Thread

SHOW ENGINE INNODB STATUS\G

IO Thread 0 为 insert buffer thread，IO Thread 1 为 log thread之后就是根据参数innodb_read_io_threads及innodb_write_io_threads来设置的读写线程，并且读线 程的ID总是小于写线程。

Purge ThreadPurgeThread来回收已经使用并分配的undo页，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作。

从InnoDB 1.2版本开始，InnoDB支持多个Purge Thread,可以加快undo页的回收。同时由于Purge Thread需要离散地读取undo页，这样也褪更进 一步利用磁盘的随机读取性能。如用户可以设置4个Purge Thread：

SHOW VARIABLES LIKE 'innodb_purge_threads'\G

Page Cleaner Thread作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作 及对于用户査询线程的阻塞，进一步提高InnoDB存储引擎的性能。

2.3.2内存

缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中， 这个过程称为将页"FIX”在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘 上的页。对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生变更时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。缓冲池的配置通过参数innodb_buffer_pool_size来设置。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size' \G

缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲 (insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB 存储的锁信息(lock info)、数据字典信息(data dictionary)等。不能简单地认为，缓冲池只是缓存索引页和 数据页，它们只是占缓冲池很大的一部分而已。InnoDB存储引擎中内存的结构情况。

配置多个缓冲池实例。每个页根据哈希值平均分配到 不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。

SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'\G

观察到每个缓冲池实例对象运行的状态

SHOW ENGINE INNODB STATUS\GSELECT POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES FROM information_schema.INNODB_BUFFER_POOL_STATS\G

LRU List、Free List 和 Flush List

数据库中的缓冲池是通过LRU (Latest Recent Used)算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。在InnoDB存储引擎中，缓冲池中页的大小默认为16KB,同样使用LRU算法对缓冲池进行管理。

InnoDB的存储引擎中，LRU列表中还加入了 midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint 位置。这个算法在InnoDB存储引擎下称为midpoint insertion strategy。在默认配置下，该位置在LRU列表长度的5/8处。

midpoint位置可由参数innodb_old_blocks_pct控制，如：

SHOW VARIABLES LIKE 'innodb_old_blocks_pct'\G

默认值为37,表示新读取的页插入到LRU列表尾端的37%的位置(差不多3/8的位置)。在InnoDB存储引擎中，把 midpoint之后的列表称为old列表，之前的列表称为new列表。可以简单地理解为new 列表中的页都是最为活跃的热点数据。那为什么不采用朴素的LRU算法，直接将读取的页放入到LRU列表的首部呢？因为某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见扫描操作，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除。

InnoDB存储引擎引入了另一个参数来进一步管理LRU列表，表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。可以通过下面的方法尽可能使LRU列表中热点数据不被刷出。

SHOW VARIABLES LIKE 'innodb_old_blocks_time'\GSET GLOBAL innodb_old_blocks_time=1000;####data or index scan operationSET GLOBAL innodb_old_blocks_time=1000;

如果用户预估自己活跃的热点数据不止63%。

SHOW VARIABLES LIKE 'innodb_old_blocks_pct'\GSET GLOBAL innodb_old_blocks_pct=20;

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。当页从 LRU列表的old部分加入到new部分时，称此时发生的操作为page made young,而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为 page not made young

通过命令  来观察 LRU 列 表及Free列表的使用情况和运行状态。

SHOW ENGINE INNODB STATUS\G

当前Buffer 8191个页。Free buffers表示当前Free列表中页的数量，Database pages表示LRU列表中页的数量。可能的情况是Free buffers与 Database pages的数量之和不等于Buffer pool size，因为缓冲池 中的页还可能会被分配给自适应哈希索引、Lock信息、Insert Buffer等页，而这部分页 不需要LRU算法进行维护，因此不存在于LRU列表中。pages made young显示 LRU列表中页移到前端的次数，因为该服务器在运行阶段没改innodb_old_blocks_time 的值，因此 not young 为 0。youngs/s，non-youngs/s 表示每秒这两类操作的次数。

Buffer pool hit rate,表示缓冲池的命中率，100%说明缓冲池运行状态非常良好。通常该值不应该小于95%。若发生Buffer pool hit rate的值小于95%这种情况，需要观察是否是由于全表扫描引起的LRU列表被污染的问题。

还可以通过表INNODB_BUFFER_POOL_STATS来观察缓冲池的运行状态

SELECT POOL_ID, HIT_RATE,PAGES_MADE_YOUNG, PAGES_NOT_MADE_YOUNGFROM information_schema.INNODB_BUFFER_POOL_STATS\G

InnoDB存储引擎从1.0.x版本开始支持压缩页的功能，即将原本16KB的页压缩 为1KB、2KB、4KB和8KB。而由于页的大小发生了变化，LRU列表也有了些许的改变。对于非16KB的页，是通过unzip_LRU列表进行管理的。LRU中的页包含了 unzip_LRU列表中的页。

在LRU列表中的页被修改后，称该页为脏页(dirty page),即缓冲池中的页和磁盘 上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘, 而Flush列表中的页即为脏页列表。需要注意的是，脏页既存在于LRU列表中，也存在 于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新 回磁盘，二者互不影响。

前面例子中Modified db pages 就显示了脏页的数量。脏页同样存在于LRU列表中

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION> 0;

重做日志缓冲

内存区域除了有缓冲池外，还有重做日志缓冲(redo log buffer)。InnoDB存储引撃首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。

SHOW VARIABLES LIKE 'innodb_log_buffer_size'\G

重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

• Master Thread每一秒将重做日志缓冲刷新到重做日志文件；

• 每个事务提交时会将重做日志缓冲刷新到重做日志文件；

• 当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。

额外的内存池

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆(heap) 的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请

2.4 Checkpoint 技术

缓冲池的设计目的为了协调CPU速度与磁盘速度的鸿沟。将脏页从缓冲池刷新到磁盘。

1. 若每次一个页发生变化，就刷新到磁盘，开销非常大。

2. 若热点数据集中在某几个页中，那么数据库的性能将变得非常差。

3. 若在从缓冲池将页的新版本刷新到磁盘时发生了宕机，发生数据丢失的问题。

当前事务数据库系统普遍都采用了 Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来 完成数据的恢复。这也是事务ACID中D (持久性)的要求。Checkpoint的目的：

1. 缩短数据库的恢复时间；

2. 缓冲池不够用时，将脏页刷新到磁盘；

3. 重做日志不可用时，刷新脏页。

两种CheckpointShaip Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式

Fuzzy Checkpoint进行页的刷新，即只刷新一 部分脏页，而不是刷新所有的脏页回磁盘。

脏页的数量太多，当缓冲池中脏页的数量占据75%时，强制进行Checkpoint。

SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct'\G

2.6 InnoDB关键特性

InnoDB存储引擎的关键特性包括：插入缓冲(Insert Buffer)两次写(DoubleWrite)自适应哈希索引(Adaptive Hash Index)异步 IO (Async IO)刷新邻接页(Flush Neighbor Page)2.6.1插入缓冲

Insert BufferInsert Buffer和数据页一样，也是物理页的一个组成部分。在InnoDB存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引(Primary Key) 一般是顺序的，不需要随机读取另一个页中的记录。因此，速度是非常快的。若主键类是UUID这样的类，在进行插入操作时，数据页的存放还是按主键进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的了，这时就离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作 性能下降。因为B+树的特性决定了非聚集索引插入的离散性。

Insert Buffer,对于非聚集索引的插入或更新操作， 不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中，好似欺骗。数据库这 个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以 一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge (合并)操作，这时通 常能将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对于非聚 集索引插入的性能。然而Insert Buffer的使用需要同时满足以下两个条件：索引是辅助索引(secondary index)；索引不是唯一(unique)的。当满足以上两个条件时，InnoDB存储引擎会使用Insert Buffer,这样就能提高插入操作的性能了。

Change BufferInnoDB存储引擎可以对DML操作——INSERT, DELETE, UPDATE都进行缓冲

1. 将记录标记为已删除;

2. 真正将记录删除。

Insert Buffer的内部实现Insert Buffer是一棵B+树，因此其也由叶节点和非叶节点组成。非叶节点存放的是査询的search key (键值)，由space-marker-offset组成，space表示待插入记录所在表的表空间id,每个表有一个唯一的space id,可以通过space id査询得知是哪张表。space占用4字节。marker占用1字节，用来兼容老版本的Insert Buffer，offset 表示页所在的偏移量，占用4字节。当一个辅助索引要插入到页(space, offset)时，将这条记录插入到Insert Buffer B+树的叶子节点中。由space-marker-offset-matadata组成,前面一样，matadata会记录进入 Insert Buffer的顺序。第5列开始，就是实际插入记录的各个字段了。因此较之原插入记录，Insert Buffer B+树的叶子节点记录需要额外13字节的开销。

Merge Insert BufferInsert/Change Buffer是一棵B+树。合并(merge)Insert Buffer中的记录到真正的辅助索引中

2.6.2两次写

doublewrite (两次写)带给InnoDB存储引擎的是数据页的可靠性。当发生数据库宕机时，可能InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，发生了宕机，导致数据丢失的情况。可以通过重做日志进行恢复。这是一 个办法。但是必须清楚地认识到，重做日志中记录的是对页的物理操作，如偏移量800, 写-aaaa-记录。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的。这 就是说，在应用(apply)重做日志前，用户需要一个页的副本，当写入失效发生时，先 通过页的副本来还原该页，再进行重做，这就是doublewrite。doublewrite由两部分组成，一部分是内存中的doublewrite buffer,大小为2MB,另一部分是物理磁盘上共享表空间中连续的128个页，即2个区(extent),大小同样为 2M，在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将 脏页先复制到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次，每次 1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免 缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序

2.6.3自适应哈希索引

而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3〜4层，故需要3~4次的査询。InnoDB存储引擎会监控对表上各索引页的査询。如果观察到建立哈希索引可以带 来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。

2.6.4 异步

为了提高磁盘操作性能，当前的数据库系统都采用异步IO (Asynchronous IO, AI0)的方式来处理磁盘操作。

2.6.5刷新邻接页

InnoDB存储引擎还提供了 Flush Neighbor Page (刷新邻接页)的特性。其工作原理 为：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新。

2.7启动、关闭与恢复

在关闭时，参数innodb_fast_shutdown影响着InnoDB的行为

• 0表示在MySQL数据库关闭时，InnoDB需要完成所有的fiill purge和merge insert buffer,并且将所有的脏页刷新回磁盘。这需要一些时间，有时甚至需要几 个小时来完成。如果在进行InnoDB升级时，必须将这个参数调为0,然后再关闭数据库。

• 1是参数innodb fast shutdown的默认值，表示不需要完成上述的fiill purge和 merge insert buffer操作，但是在缓冲池中的一些数据脏页还是会刷新回磁盘。

• 2表示不完成full purge和merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。这样不会有任何事务的丢失，但是下次 MySQL数据库启动时，会进行恢复操作(recovery)。

第3章 文件

• 参数文件：告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数

• 日志文件：用来记录MySQL实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢査询日志文件、查询日志文件等。

• socket文件：当用UNIX域套接字方式进行连接时需要的文件。

• pid文件：MySQL实例的进程ID文件。

• MySQL表结构文件：用来存放MySQL表结构定义文件。

• 存储引擎文件：因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文 件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

3.1参数文件

启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数

mysql --help | grep my.cnf

3.1.2参数类型MySQL数据库中的参数可以分为两类：动态(dynamic)参数静态(static)参数

全局/当前会话

SELECT @@session.read_buffer_size\GSELECT @@global.read_buffer_size\G

3.2日志文件

常见的日志文件有：

• 错误日志(error log)

• 二进制日志(binlog)

• 慢査询日志(slow query log)

• 査询日志(log)

对运行状态进行诊断，从而更好地进行数据库层面的优化。

3.2.1错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。

SHOW VARIABLES LIKE 'log_error'\Gtail -n 50 /var/lib/mysql/node3.err

3.2.2慢查询日志

慢査询日志定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。可以在MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中,默认关闭

SHOW VARIABLES LIKE 'long_query_time'\GSHOW VARIABLES LIKE 'log_slow_queries'\G

如果运行的SQL 语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢査询日志文件。

SHOW VARIABLES LIKE 'log_queries_not_using_indexes'\G

DBA可以通过慢查询日志来找出有问题的SQL语句，对其进行优化。

提供的mysqldumpslow命令，分析该文件：

mysqldumpslow xxx-190-slow.log

如果用户希望得到执行时间最长的10条SQL语句，可以运行如下命令：

mysqldumpslow -s al -n 10 david.log

将慢査询的日志记录放入一张表中，査询更加方便和直观。

select * from mysql.slow_log;

参数long_query_io将超过指定逻辑10次数的SQL语句记录到 slow log中。表示对于逻辑读取次数大于100的SQL语句，记录到 slow log中。

3.2.3查询日志

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

3.2.4二进制日志

二进制日志(binary log)记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作

SHOW MASTER STATUS\GSHOW BINLOG EVENTS IN 'mysql-bin.000002'\G

二进制日志主要有以下几种作用。恢复、复制、审计

show variables like 'datadir';

mysql-bin.000001即为二进制日志文件，mysql-bin.index为二进制的索引文件，来存过往产生的二进制日志序号

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

max_binlog_size指定了单个二进制日志文件的最大值，如果超过该值，则产生 新的二进制日志文件，后缀名+1，并记录到.index文件。当使用事务的表存储引擎时，所有未提交的二进制日志会被记录到一个缓存中去，等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32K。

在默认情况下，二进制日志并不是在每次写的时候同步到磁盘。sync_binlog= [N]表示每写缓冲 多少次就同步到磁盘。如果将N设为1,即sync_binlog= 1表示采用同步写磁盘的方式来 写二进制日志参数binlog-do-db和binlog-ignore-db表示需要写入或忽略写入哪些库的日志。默认为空，表示需要同步所有库的日志到二进制日志。binlog_format参数十分重要，它影响了记录二进制日志的格式。

要査看二进制日志文件的内容，通过MySQL提供的工具mysqlbinlogo对于STATEMENT格式的二进制日志文件，在 使用mysqlbinlog后，看到的就是执行的逻辑SQL语句

mysqlbinlog --start-position=203 test.000004

3.3套接字文件

在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字(socket)文件。套接字文件可由参数socket控制。一般在/tmp 目录下，名为mysql.sock

3.4 pid文件

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

show variables like 'pid_file'\G

3.5表结构定义文件

MySQL数据的存储是根据表进行 的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，MySQL都有一个以 frm为后缀名的文件，这个文件记录了该表的表结构定义。frm还用来存放视图的定义，如用户创建了一个v_a视图，那么对应地会产生v_a.frm文件

3.6 InnoDB存储引擎文件

与InnoDB存储引擎密切相关的文件，这些文件包括重做日志文件、表空间文件。

表空间文件

InnoDB采用将存储的数据按表空间(tablespace)进行存放的设计。在默认配置 下会有一个初始大小为10MB,名为ibdatal的文件。该文件就是默认的表空间文件 (tablespace file)

设置innodb_data_file_path参数后，所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。若设置了参数innodb_Ele_per_table,则用户可以将每个基于 InnoDB存储引擎的表产生一个独立表空间。独立表空间的命名规则为：表名.ibd。通过 这样的方式，用户不用将所有数据都存放于默认的表空间中。下面这台MySQL数据库 服务器设置了 innodb_file_per_table,故可以观察到：

SHOW VARIABLES LIKE 'innodb_file_per_table'\G

这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空冋中。

3.6.2重做日志文件

在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_ logfile1的文件。它们记录了对于InnoDB存储引擎的事务日志。主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。每个InnoDB存储引擎至少有1个重做日志文件组(group),每个文件组下至少有 2个重做日志文件，如默认的ibJogfile0和ibJogfile1为了得到更高的可靠性，

innodb_log_file_size指定每个重做日志文件的大小。1.2.x版本将该限制扩大为了 512G

innodb_log_files_in_group指定了日志文件组中重做日志文件的数量，默认为 2参数

innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1,表示只 有一个日志文件组，没有镜像

innodb_log_group_home_dir指定了日志文 件组所在路径，默认为./,表示在MySQL数据库的数据目录下

innodb_flush_log_at_trx_commit设置为1，因此为了保证事务的ACID中的持久性，也就是每当有事务提交时，就必须确保事务都已经写入重做日志文件

第4章 表

4.1索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized tabl，每张表都有个主键 (Primary Key),如果在创建表时没有显式地定义主键，则InnoDB存储引擎会按如下方 式选择或创建主键：首先判断表中是否有非空的唯一索引(Unique NOT NULL),如果有，则该列即为主键。如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯 一索引为主键

通过下面的SQL语句判断表的主键值

SELECT a,b,c,_rowid FROM z;

rowid可以显示表的主键，rowid R能用于查看单个列为主键的情况如：

4.2 InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称 之为表空间(tablespace)。表空间又由段(segment)＞区(extent)、页(page)组成。页在 一些文档中有时也称为块(block)

4.2.1 表空间

默认情况下InnoDB存储引擎有一个共享表空间ibdatal, 即所有数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间内。每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚(undo)信息，插入缓冲 索引页、系统事务信息，二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。 

4.2.2 段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。因为前面已经介绍过了 InnoDB存储引擎表是索引组织的(index organized),因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点(图4-1的Leaf node segment), 索引段即为B+树的非索引节点(图4-1的Non-leaf node segment),回滚段较为特殊,后面单独介绍。

4.2.3 区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页 的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。在默认情况下，InnoDB存储引擎页的大小为16KB,即一个区中一共有64个连续的页。InnoDB 1.0.x版本开始引入压缩页，即每个页的大小可以通过参数KEY_BLOCK_ SIZE设置为2K、4K、8K,因此每个区对应页的数量就应该为512、256、128。InnoDB 1.2.x版本新增了参数innodb_page_size.通过该参数可以将默认页的大小设置为4K、8K,但是页中的数据不是压缩。这时区中页的数量同样也为256、128。总之， 不论页的大小怎么变化，区的大小总是为IM.在每个段开始时，先用32个页大小的碎片页(fragment page)来存放数据， 在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小表，在开始时申请较少的空间，节省磁盘容量的开销。

4.2.4 页

页是InnoDB 磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。而从 InnoDB 1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K、8K、 16K。若设置完成，则所有表中页的大小都为innodb_page_size,不可以对其再次进行修改。常见的页类型有：

• 数据页(B-treeNode)

• undo 页(undo Log Page)

• 系统页(System Page)

• 事务数据页(Transaction system Page)

• 插入缓冲位图页(Insert Buffer Bitmap )

• 插入缓冲空闲列表页(Insert Buffer Free List)

• 未压缩的二进制大对象页(Uncompressed BLOB Page)

• 压缩的二进制大对象页(compressed BLOB Page)

4.2.5 行

InnoDB存储引擎是面向列的(row-oriented),也就说数据是按行进行存放的。

4.3 InnoDB行记录格式

InnoDB存储引擎是以行的形式存储的。这意味着页中保存着表中一行行的数据。在InnoDB 1.0.x版本之前，提供了 Compact和Redundant两种格式来存放行记录数据，在MySQL 5.1版本中，默认设置为Compact行格式。查看当前表使用的行格式

SHOW TABLE STATUS like 'user'\G

Compressed 和 Dynamic 行记录格式

InnoDB 1.0.x版本开始引入了新的文件格式称为Barracuda 文件格式。Barracuda文件格式下拥有两种新的行记录格式：Compressed和Dynamic。

4.4 InnoDB数据页结构

页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-treeNode的页存放的即是表中行的实际数据了。在这一节中，我们将从底层具体地介绍InnoDB数据页的内部存储结构。

4.4.1 File Header

记录页的一些头信息，共占用38字节。

4.4.2 Page Header

该部分用来记录数据页的状态信息，由14个部分组成，共占用56字节

4.4.3 Infimum 和 Supremum Record

每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能大的值还要大的值。

4.4.4 User Record 和 Free Space

实际存储行记录的内容。再次强调，InnoDB 存储引擎表总是B+树索引组织的。Free Space很明显指的就是空闲空间，同样也是个链表数据结构。

4.4.5 Page Directory

存放了记录的相对位置(存放的是页相对位置，而不是偏移量)由于在InnoDB存储引擎中Page Direcotry是稀疏目录，二叉査找的结果只是一个粗略的结果，因此InnoDB存储引擎必须通过recorder header中的next_record来继续査找相关记录。

需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉査找。只不过二 叉查找的时间复杂度很低，同时在内存中的査找很快

4.4.6 File Trailer

为了检测页是否已经完整地写入磁盘(如可能发生的写入过程中磁盘损坏、机器关 机等)，InnoDB存储引擎的页中设置了 File Trailer部分。保证页的完整性(not corrupted)。

4.7视图

在MySQL数据库中，视图(View)是一个命名的虚表，视图中的数据没有实际的物理存储。

4.8分区表

4.8.1分区概述分区功能并不是在存储引擎层完成的,分区的过程是将一个表或索引分解为多个更小、更可管理的部分。MySQL 数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据不支持全局分区。查看当前数据库是否启用了分区功能：

SHOW VARIABLES LIKE '%partition%'\G

几种类型的分区。

• RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。5.5 开始支持RANGE COLUMNS的分区。

• LIST分区：和RANGE分区类型，只是LIST分区面向的是离散的值。5.5 开始支持LIST COLUMNS的分区。

• HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。

• KEY分区：根据MySQL数据库提供的哈希函数来进行分区。

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索 引的一个组成部分

4.8.2分区类型

PARTITION BY RANGE (id)(PARTITION p0 VALUES LESS THAN (10),PARTITION p1 VALUES LESS THAN (20))；PARTITION BY LIST(b)(PARTITION p0 VALUES IN (1,3,5,7,9),PARTITION p1 VALUES IN (0,2,4,6,8));PARTITION BY HASH (YEAR(b))PARTITIONS 4;PARTITION BY KEY (b)PARTITIONS 4;

第5章索引与算法

5.1 InnoDB存储引擎索引概述

几种常见的索引：

• B+树索引

• 全文索引

• 哈希索引

InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引B+树索引就是传统意义上的索引，这是目前关系型数据库系统中最有效的索引。

B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被査找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

5.4 B+树索引

B+树索引的本质就是B+树在数据库中的实现。但B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+ 树的高度一般都在2-4层数据库中的B+树索引可以分为聚集索引(clustered inex)和辅助索引(secondary index)。其内部都是B+树的，即高度平衡的，叶子 节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

5.4.1聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存 放。而聚集索引(clustered index)就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引 的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。

在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引 的叶子节点上直接找到数据。能够特别快 地访问针对范围值的查询。

数据页上存放的是完整的每行的记录， 而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。

聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

5.4.2 辅助索引

对于辅助索引(Secondary Index,也称非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签 (bookmark),辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过页级别的指针，获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

5.4.4 B+树索引的管理

1 .索引管理索引的创建和删除可以通过两种方法，一种是ALTER TABLE,另一种是CREATE/ DROP INDEX.

ALTER TABLE user ADD KEY idx_id (id);  SHOW INDEX FROM user;ALTER TABLE user DROP INDEX idx_id ;create index idx_id on user(id);drop index idx_id on user;#联合索引ALTER TABLE user ADD KEY idx_id_bc (id,bc);

mysql> show index from user\G*************************** 1. row ***************************        Table: user   Non_unique: 1     Key_name: idx_id Seq_in_index: 1  Column_name: id    Collation: A  #A或者null，B+树总是A，即排序的  Cardinality: 1  #表示索引中唯一值的数目的估值，应尽可能接近1     Sub_part: NULL       Packed: NULL         Null:    Index_type: BTREE      Comment: Index_comment: 

优化器会根据Cardinality值来判断是否使用这个索引， 但他不是实时更新的,更新

analyze table user\G

5.5 Cardinality 值

什么时候添加B+树索 取值的范围很小，称为低选择性，这时添加B+树索引是完全没有必要的。

取值范围很广，几乎没有重复，即属于高选择性，使用B+树索引是最适合的

Cardinality值非常关键，表示索引中不重复记录数量的预估值。是一个预估值，而不是一个准确值，应尽可能地接近1。

怎样来统计Cardinality信息的呢？

数据库对于Cardinality的统计都是通过采样(Sample)的方法来完成的。Cardinality统计信息的更新发生在两个操作中：INSERT 和UPDATE。更新Cardinality 的策略为：

1. 表中1/16的数据已发生过变化。

2. stat_modified_counter > 2000000000

怎样来进行Cardinality信息的统计和更新操作？

同样是通过采样的方法。默认InnoDB存储引擎对8个叶子节点(Leaf Page)进 行采用。采样的过程如下：

1. 取得B+树索引中叶子节点的数量，记为A。

2. 统计每个页不同记录的个数，即为P1,…P8。

3. 给出 Cardinality 的预估值：Cardinality= (P1+P2+...+P8) *A/8。

5.6 B+树索引的使用

5.6.1不同应用中B+树索引的使用

B+树索引建立后，对该索引的使用应该只是通过该索引取得表中少部分的数据。这时建立B+树索引才是有意义的，否则即使建立了，优化器也可能选择不使用索引。

5.6.2联合索引

联合索引是指对表上的多个列进行索引。

CREATE TABLE t (a INT,b INT,PRIMARY KEY (a),KEY idx_a_b (a,b))ENGINE=INNODB

联合索引也是一棵 B+树，不同的是联合索引的键值的数量不是1,而是大于等于2。

和单个键值的B+树 并没有什么不同，键值都是排序的，通过叶子节点可以逻辑上顺序地读出所有数据

数据按(a, b)的顺序进行了存放。对于b列的査询使用不到(a, b)的索引。联合索引的第二个好处是已经对第二个键值进行了排序处理。索引本身在叶子节点已经排序了。

5.6.3覆盖索引

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到査询的记录，而不需要査询聚集索引中的记录。好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量 的IO操作。

对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息

SELECT COUNT (*) FROM buy_logInnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择为userid索引，而列Extra列的Using index就是代表了优化器进行了覆盖索引操作。

5.6.4优化器选择不使用索引的情况

优化器并没有选择索引去査找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。

用户要选取的数据是整行信息，而辅助索引不能覆盖到我们要査询的信息，因此在对OrderlD索引查询到指定数据后，还需要一次书签访问来査找整行数据的信息。虽然OrderlD索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。

如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时 (一般是20%左右)，优化器会选择通过聚集索引来査找数据。因为顺序读要远远快于离散读。

因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引 查找的数据是少量的。也可以强制使用辅助索引

select * from t force index(id) where id >10000 nad id <10200;

5.6.5索引提示

SELECT * FROM t USE INDEX(a) WHERE a=l AND b = 2;

5.6.6 Multi-Range Read 优化

目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，适用于range, ref, eq_ref类型的查询。好处：

• MRR使数据访问变得较为顺序

• 减少缓冲池中页被替换的次数

• 批量处理对键值的査询操作

MRR的工作方式如下：

• 将査询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索 引键值排序的。

• 将缓存中的键值根据RowID进行排序。

• 根据RowID的排序顺序来访问实际的数据文件。

在列Extra会看见Using MRR选项。

若启用了 Multi-RangeRead优化，优化器会先将査询条件进行拆分，然后再进行数据査询。优化器会将查询条件拆分为(1000, 1000), (1001, 1000),...最后再根据这些拆分出的条件进行数据的査询。

5.6.7 Index Condition Pushdown (ICP)优化

根据索引进行査询的优化方式。之前的MySQL进行索引査询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。在支持Index Condition Pushdown后，MySQL数据库会在取出索引的同时， 判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取(fetch),从而提高数 据库的整体性能。

当优化器选择Index Condition Pushdown优化时， 在Extra看到Using index condition提示。

5.7哈希算法

5.7.1 哈希表

哈希表技术很好地解决了直接寻址遇到的问题，但是会碰撞，在数据库中一般采用最简单的碰撞解决技术，这种技术被称为链接法 (chaining).

在链接法中，把散列到同一槽中的所有元素都放在一个链表中，槽i中有一个指针，它指向由所有散列到i的元素构成的链表的头；如果不存在这样的元素， 则i中为NULL

5.7.2 InnoDB存储引擎中的哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。

对于缓冲池页的哈希表来说，在缓冲池中的页都有一个chain指针，它指向相同哈希函数值的页。而对于除法散列，m的取值为略大于2倍的缓冲池页数量的质数。例如：当前参数innodb_buffer_pool_size的大小为10M,则共有 640个16KB的页。对于缓冲池页内存的哈希表来说，需要分配640X2=1280个槽，但 是由于1280不是质数，需要取比1280略大的一个质数，应该是1399,所以在启动时会 分配1399个槽的哈希表，用来哈希查询所在缓冲池中的页。那么InnoDB存储引擎的缓冲池对于其中的页是怎么进行査找的呢？上面只是给出 了一般的算法，怎么将要查找的页转换成自然数呢？其实也很简单，InnoDB存储引擎的表空间都有一个space_id,用户所要査询的应该 是某个表空间的某个连续16KB的页，即偏移量offset。InnoDB存储引擎将space_id左 移 20 位，然后加上这个 space_id 和 offset,即关键字 K=space_i<<20+space_id+offset, 然后通过除法散列到各个槽中去。

5.7.3自适应哈希索引

自适应哈希索引是数据库自身创建并使用的，自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的査找非常快速，但是对于范围査找就无能为力了。

哈希索引只能用来搜索等值的査询

innodb_adaptive_hash_index来禁用或启动此特性，默认为开启。

5.8全文检索

5.8.1概述

B+树索引可以通过索引字段的前缀 (prefix)进行查找。比如 a like 'xxx%'，只要a添加了 B+ 树索引，就能利用索引进行快速査询。但是 a like '%xxx%'就不行了根据B+树索引的特性，上述SQL语句即便添加了 B+树索引也是需要进行索引的扫描来得到结果。

从InnoDB 1.2.x版本开始，InnoDB存储引擎开始支持全文检索，其支持MylSAM存储引擎的全部功能

SELECT * FROM fts_a WHERE body LIKE '%Pease%';SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('Pease'):

type这列显示了 fulltext,即表示使用全文检索的倒排索引，而key这 列显示了 idx_Rs,表示索引的名字。

第六章 锁

开发多用户、数据库驱动的应用时，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据。为 此就有了锁(locking)的机制

6.1什么是锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问，InnoDB存储引擎会在行级别上对表数据上锁，数据库系统使用锁是为了支持对共享资源进行并发访问， 提供数据的完整性和一致性。

对于MylSAM引擎，其锁是表锁设计。并发情况下的读没有问题，但是并发插入时的性能就要差一些了InnoDB存储引擎锁的实现提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销，并可以同时得到并发性和一致性。

6.2 lock 与 latch

latch一般称为闩锁(轻量级的锁)，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，latch又可以分为mutex (互斥量)和rwlock (读写锁)。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock 的对象仅在事务commit或rollback后进行释放(不同事务隔离级别释放的时间可能不同)。此外，lock是有死锁机制的

show engine innodb mutex;#latch

6.3 InnoDB存储引擎中的锁

6.3.1锁的类型

InnoDB存储引擎实现了如下两种标准的行级锁：

• 共享锁(SLock),允许事务读一行数据。

• 排他锁(XLock),允许事务删除或更新一行数据。

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r 的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容(Lock Compatible)。但若有其他的事务T3想获得行r的排他锁，则其必须等待事务Tl、T2释放行r上的共享锁——这种情况称为锁不兼容。S和X锁都是行锁，兼容是指对同一记录(row)锁的兼容性情况。

此外，InnoDB存储引擎支持多粒度(granular)锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。

为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持 一种额外的锁方式，称之为意向锁(Intention Lock)。

意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁

6.3.2 一致性非锁定读

一致性的非锁定读是指InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中 行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。而是会去读取行的一个快照数据。快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。非锁定读机制极大地提高了数据库的并发性。这是默认的读取方式，即读取不会占用和等待表上的锁。

但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读。

快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制(Multi Version Concurrency Control. MVCC)。

在事务隔离级别READ COMMITTED和REPEATABLE READ (默认事务隔离级别)下，InnoDB存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在READ COMMITTED事务隔离级别下，对于快照数据，非一致 性读总是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别 下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

6.3.3 一致性锁定读

即事务的隔离级别为REPEATABLE READ模 式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。持两种一致性的锁定读(locking read)操作：

SELECT...FOR UPDATESELECT...LOCK IN SHARE MODE

SELECT...FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。

SELECT...LOCK IN SHARE MODE对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

6.3.4自増长与锁

在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器(auto-increment counter),当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做 AUTO-INC Locking.这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

6.5锁问题

6.5.1脏读

脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。

脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交。

对于脏页的读取，是非常正常的。脏页是因为数据库实例内存和磁盘的异步造成的, 这并不影响数据的一致性(或者说两者最终会达到一致性，即当脏页都刷回到磁盘)。并且因为脏页的刷新是异步的，不影响数据库的可用性，因此可以带来性能的提高。

脏数据是指未提交的数据，如果读到了脏数据，即一个事务可脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。违反了事务的隔离性。

6.5.2不可重复读

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时, 另外一个事务也访问该同一数据集合，并做了一些DML操作。在第一个事务中的两次读数据之间，由于第二个事务的修改，发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。

不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求。InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE,采用Next-Key Lock算法，避免了不可重复读的现象。

6.5.3丢失更新

丢失更新是另一个锁导致的问题，简单来说其就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。

6.6阻塞

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其是为了确保事务可以 并发且正常地运行。innodb_lock_wait_timeout用来控制等待的时间(默认 是50秒)

innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是OFF,代表不回滚)。

6.7死锁

6.7.1死锁的概念

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。参数innodb_lock_wait_timeout用来设置超时的时间。

除了超时机制，当前数据库还都普遍采用wait-for graph (等待图)的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。

6.8锁升级

锁升级(Lock Escalation)是指将当前锁的粒度降低。举例来说，数据库可以把一 个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。如果在数据库的设计中认为锁是一种稀有资源，而且想避免锁的开销，那数据库中会频繁出现锁升级现象。

InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一 个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

第7章 事务

数据库系统引入事务的主要目的：事务会把数据库从一种一致状态转换为另 一种一致状态。在数据库提交工作时，要么所有修改都已经保存了，要么所有修改都不保存。InnoDB存储引擎中的事务完全符合ACID的特性：

• 原子性(atomicity)

• 一致性(consistency)

• 隔离性(isolation )

• 持久性(durability)

7.1认识事务

7.1.1概述

事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都做修改，要么都不做，这就是事务的目的，也是事务模型区别与文件系统的重要特 征之一。

原子性。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个SQL语句执行失败，已经执行成功的 SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。一致性。一致性指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏，事务是一致性的单位，如果事务中某个动作失败了，系统可以自动撤销事务——返 回初始化的状态。隔离性。隔离性还有其他的称呼，如并发控制(concurrency control)、 可串行化(serializability)、锁(locking)等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，通常这使用锁来实现。当前数据库系统中都提供了一种粒度锁(granular lock)的策略，允许事务仅锁住一个实体对象的子集，以此来提高事务之间的并发度。持久性。事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

7.1.2分类

从事务理论的角度来说，可以把事务分为以下儿种类型：扁平事务(Flat Transaction)，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。

扁平事务的主要限制是不能提交或者回滚事务的某一部分，或分几个步骤提交。

带有保存点的扁平事务(Flat Transactions with Savepoint),除了支持扁平事务支 持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。保存点(Savepoint)用来通知系统应该记住事务当前的状态，以便 当之后发生错误时，事务能回到保存点当时的状态。

链事务(Chained Transaction)可视为保存点模式的一种变种。链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下 文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。链事务与带有保存点的扁平事务不同的是，带有保存点的扁平事务能回滚到任意正 确的保存点。而链事务中的回滚仅限于当前事务，即只能恢复到最近一个的保存点。

嵌套事务(Nested Transaction)是一个层次结构框架。由一个顶层事务(top- level transaction)控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务 (subtransaction),其控制每一个局部的变换。

分布式事务(Distributed Transactions)通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务，其同样需要满足ACID特性，要么都发生，要么都失效。

7.2事务的实现

事务隔离性锁来实现。

原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。

7.2.1 redo

1.基本概念重做日志用来实现事务的持久性，由两部分组成：一是内存中的重做日志缓冲(redo log buffer),其是易失的；二是重做日志文件(redo log file), 其是持久的。

InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交(COMMIT)时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。这里的日志是指重做日志，在 InnoDB存储引擎中，由两部分组成，即redo log和undo log。redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的， 在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次fsync操作。由于重做日志文件打开并没有使用 O_DIRECT选项，因此重做日志缓冲先写入文件系统缓存。为了确保重做日志写入磁盘，必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能，因此磁盘的性能确定了事务提交的性能，也就是数据库的性能。

InnoDB存储引擎允许用户手工设置非持久性的情况发生，以此提高数据库的性能。即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync操 作。由于并非强制在事务提交时进行一次fsync操作，显然这可以显著提高数据库的性能。但是当数据库发生宕机时，由于部分日志未刷新到磁盘，因此会丢失最后一段时间的事务。参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。该参数 的默认值为1,表示事务提交时必须调用一次fsync操作。还可以设置该参数的值为0和 2。表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而 在master thread中每1秒会进行一次重做日志文件的fsync操作。2表示事务提交时将 重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。在这个 设置下，当MySQL数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部 分事务。

在MySQL数据库中还有一种二进制日志(binlog),其用来进行PONIT-IN-TIME (PIT)的恢复及主从复制(Replication)环境的建立。从表面上看其和重做日志罪常相似，都是记录了对于数据库操作的日志。首先，重做日志是在InnoDB存储引擎层产生，而二进制日志是在MySQL数据库的上层产生的，并且二进制日志不仅仅针对于InnoDB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。其次，两种日志记录的内容形式不同。MySQL数据库上层的二进制日志是一种逻辑日志，其记录的是对应的SQL语句。而InnoDB存储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改。此外，两种日志记录写入磁盘的时间点不同。二进制日志只在事务提交完成后进行一次写入。而InnoDB存储引擎的重做日志在事务进行中不断地被写入， 这表现为日志并不是随事务提交的顺序进行写入的。

7.2.2 undo

1 .基本概念重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo.因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo,还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段 (segment)中，这个段称为undo段，undo段位于共享表空间内。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

除了回滚操作，undo的另一个作用是MVCC,即在InnoDB存储引擎中MVCC的 实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。最后也是最为重要的一点是，undo log会产生redo log,也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

7.6事务的隔离级别

SQL标准定义的四个隔离级别为：

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

READ UNCOMMITTED称为浏览访问(browse access),仅仅针对事务而言， READ COMMITTED 称为游标稳定， REPEATABLE READ 是 2.9999° 的隔离，没有幻读的保护。SERIALIZABLE称为隔离，或3°的隔离。SQL和SQL2标 准的默认事务隔离级别是SERIALIZABLEInnoDB存储引擎默认支持的隔离级别是REPEATABLE READ,但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock锁的算法，因此避免幻读的产生。

InnoDB存储引擎在默认的REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE隔离级别。隔离级别越低，事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是READ COMMITTED在InnoDB存储引擎中，可以使用以下命令来设置当前会话或全局的事务隔离级别：如果想在MySQL数据库启动时就设置事务的默认隔离级别，那就需要修改MySQL 的配置文件，在[mysqld]中添加如下行：

[mysqld]transaction-isolation = READ-COMMITTED

查看当前会话的事务隔离级别

SELECT @@tx_isolation\G

査看全局的事务隔离级别

SELECT @@global.tx_isolation\G

7.7分布式事务

7.7.1 MySQL数据库分布式事务

InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源(transactional resources)参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的ACID要 求又有了提高。另外，在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置 为SERIALIZABLE，XA事务允许不同数据库之间的分布式事务，如一台服务器是MySQL数据库的，另 一台是Oracle数据库的，又可能还有一台服务器是SQL Server数据库的，只要参与在 全局事务中的每个节点都支持XA事务。

分布式事务使用两段式提交(two-phase commit)的方式。在第一阶段，所有参与全局事务的节点都开始准备(PREPARE),告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。

第8章备份与恢复

8.1备份与恢复概述

可以根据不同的类型来划分备份的方法。根据备份的方法不同可以将备份分为：

• Hot Backup (热备)

• Cold Backup (冷备)

• Warm Backup (温备)

Hot Backup是指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响。这种方式在MySQL官方手册中称为Online Backup (在线备份)。Cold Backup是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即诃。这种方式在MySQL官方手册中称为Offline Backup (离线备份)。Warm Backup备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加 一个全局读锁以保证备份数据的一致性。

按照备份后文件的内容，备份又可以分为：

• 逻辑备份

• 裸文件备份

逻辑备份是指备份出的文件内容是可读的，一般是文本 文件。内容一般是由一条条SQL语句，或者是表内实际数据组成。如mysqldump和SELECT*INTO OUTFILE的方法。这类方法的好处是可以观察导出文件的内容，一般适 用于数据库的升级、迁移等工作。但其缺点是恢复所需要的时间往往较长。

裸文件备份是指复制数据库的物理文件，既可以是在数据库运行中的复制(如 ibbackup. xtrabackup这类工具)，也可以是在数据库停止运行时直接的数据文件复制。这类备份的恢复时间往往较逻辑备份短很多。

若按照备份数据库的内容来分，备份又可以分为：

• 完全备份

• 增量备份

• 日志备份

完全备份是指对数据库进行一个完整的备份。

增量备份是指在上次完全备份的基础上，对于更改的数据进行备份。

日志备份主要是指对MySQL数据库二进制日志的备份， 通过对一个完全备份进行二进制日志的重做(replay)来完成数据库的point-in-time的恢复工作。

MySQL数据库复制(replication)的原理就是异步实时地将二进制日志重做传送并应用到从(slave/standby)数据库。

8.2冷备

对于InnoDB存储引擎的冷备非常简单，只需要备份MySQL数据库的fan文件，共享表空间文件，独立表空间文件(*.ibd),重做日志文件。另外建议定期备份MySQL数据库的配置文件my.cnf,这样有利于恢复的操作

冷备的优点是：

• 备份简单，只要复制相关文件即可。

• 备份文件易于在不同操作系统，不同MySQL版本上进行恢复。

• 恢复相当简单，只需要把文件恢复到指定位置即可。

• 恢复速度快，不需要执行任何SQL语句，也不需要重建索引。

冷备的缺点是：

• InnoDB存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他数据

• 冷备也不总是可以轻易地跨平台。操作系统、MySQL的版本、文件大小写敏感 和浮点数格式都会成为问题。

8.3逻辑备份

8.3.1 mysqldump

通常用来完成转存(dump) 数据库的备份及不同数据库之间的移植，如从MySQL低版本数据库升级到MySQL高版本数据库,又或者从MySQL数据库移植到Oracle、Microsoft SQL Server数据库等。mysqldump的语法如下：

shell>mysqldump (arguments] >file_naine备份所有的数据库，可以使用-all-databases选项：mysqldump --all-databases >dump.sql备份指定的数据库，可以使用-databases选项：mysqldump --databases dbl db2 db3 >dump.sql如果想要对test这个架构进行备份，可以使用如下语句：mysqldump --single-transaction test >test_backup.sql 

8.3.2 SELECT...INTO OUTFILE

SELECT...INTO语句也是一种逻辑备份的方法，更准确地说是导出一张表中的数据。

 #默认导出的文件是以TAB进行列分割的 select * into outfile '/home/mysql/a.txt' from a; cat /home/mysql/a.txt  #使用其他分割符 mysql test -e "select * into outfile '/home/mysql/a.txt' fields terminated by *, * from a";

8.3.3逻辑备份的恢复

mysqldump的恢复操作比较简单，因为备份的文件就是导出的SQL语句，一般只需要执行这个文件就可以了

mysql -uroot -p source /home/mysql/test_backup.sql

8.3.4 LOAD DATA INFILE

若通过mysqldump-tab,或者通过SELECT INTO OUTFILE导出的数据需要恢复,可以通过命令LOAD DATA INFILE来进行导入。

load data infile '/home/mysql/a.txt' into table a;

8.4二进制日志备份与恢复

二进制日志非常关键，用户可以通过它完成point-in-time的恢复工作。MySQL数据库的replication同样需要二进制日志。在默认情况下并不启用二进制日志，要使用二进制日志首先必须启用它。如在配置文件中进行设置：

[mysqld]log-bin=mysql-bin

只简单启用二进制日志是不够的， 还需要启用一些其他参数来保证最为安全和正确地记录二进制日志,推荐的二进制日志的服务器配置应该是：

[mysqld]log-bin = mysql-binsync_binlog = 1innodb_support_xa = 1

在备份二进制日志文件前，可以通过FLUSH LOGS命令来生成一个新的二进制日志文件，然后备份之前的二进制日志。要恢复二进制日志也是非常简单的，通过mysqlbinlog即可。mysqlbinlog的使用方法如下：

#恢复二进制日志mysqlbinlog binlog. 0000001 I mysql-uroot -p test#恢复多个二进制日志文件mysqlbinlog binlog.[0-10]* I mysql -u root -p test

也可以先通过mysqlbinlog命令导出到一个文件，然后再通过SOURCE命令来导入

mysqlbinlog binlog.000001 > /tmp/statements.sqlmysqlbinlog binlog.000002 >> /tmp/statements.sqlmysql -u root -p -e "source /tmp/statements.sql"

8.5热备

8.5.1 ibbackup

ibbackup是InnoDB存储引擎官方提供的热备工具，可以同时备份MylSAM存储引擎和InnoDB存储引擎表

8.5.2 XtraBackup

XtraBackup备份工具是由Percona公司开发的开源热备工具。支持MySQL5.0以上的版本。

8.5.3 XtraBackup实现增量备份

MySQL数据库本身提供的工具并不支持真正的增量备份，更准确地说，二进制日志的恢复应该是point-in-time的恢复而不是增量备份。而XtraBackup工具支持对于 InnoDB存储引擎的增量备份

第9章性能调优

9.1选择合适的CPU

InnoDB存储引擎一般都应用于OLTP的数据库应用，这种应用的特点如下：

• 用户操作的并发量大

• 事务处理的时间一般比较短

• 查询的语句较为简单，一般都走索引

• 复杂的査询较少

本身对CPU的要求并不是很高，可以说OLAP是CPU密集型的操作，而OLTP是IO密集型的操作。在采购设备时，将更多的注意力放在提高IO的配置上。

此外，为了获得更多内存的支持，用户采购的CPU必须支持64位，否则无法支持64位操作系统的安装。因此，为新的应用选择64位的CPU是必要的前提。如果CPU是多核的，可以通过修改参数innodb_read_io_ threads和innodb_write_io_threads来增大IO的线程，充分有效地利用CPU 的多核性能。

9.2内存的重要性

内存的大小是最能直接反映数据库的性能。InnoDB存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即 InnoDB Buffer PooL因此，内存的大小直接影响了数据库的性能。

应该在开发应用前预估“活跃”数据库的大小是多少，并以此确定数据库服 务器内存的大小。如何判断当前数据库的内存是否已经达到瓶颈了呢？比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，通常InnoDB存储引擎的缓冲池的命中率不应该小于99%

SHOW GLOBAL STATUS LIKE 'innodb%read%'\G; 缓冲池的命中率=Innodb_buffer_pool_read_requests/Innodb_buffer_pool_read_ahead_evicted+Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads

9.3硬盘对数据库性能的影响

9.3.1传统机械硬盘

机械硬盘有两个重要的指标：一个是寻道时间，另一个是转速。

传统机械硬盘最大的问题在于读写磁头，读写磁头的设计使硬盘可以不再像磁带一样，只能进行顺序访问，而是可以随机访问。但是，机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找，因此顺序访问的速度要远高于随机访问。

9.3.2固态硬盘

固态硬盘，更准确地说是基于闪存的固态硬盘，

9.4合理地设置RAID

9.4.1 RAID 类型

RAID (Redundant Array of Independent Disks,独立磁盘冗余数组)的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。由于将多个硬盘组合成为一个逻辑扇区，RAID看起来就像一个单独的硬盘或逻辑存储单元，因此操作系统只会把它当作一个硬盘。RAID的作用是：

• 增强数据集成度 

• 增强容错功能

• 增加处理量或容量

9.7选择合适的基准测试工具

基准测试工具可以用来对数据库或操作系统调优后的性能进行对比。MySQL数据 库本身提供了一些比较优秀的工具，这里将介绍另外两款更为优秀和常用的基准测试工 具：sysbench 和 mysql-tpcco

9.7.1 sysbench

sysbench是一个模块化的、跨平台的多线程基准测试工具，主要用于测试各种不同 系统参数下的数据库负载情况。它主要包括以下几种测试方式：CPU性能、磁盘IO性能、调度程序性能、内存分配及传输速度、POSIX线程性能、数据库OLTP基准测试

9.7.2 mysql-tpcc

TPC是一个用来评价大型数据库系统软硬件性能的非盈利组织。TPC-C是TPC协会制定的，目前在学术界和工业界普遍采用TPC-C 来评价OLTP应用的性能。

tpcc-mysql由以下两个工具组成。

• tpcc_load：根据仓库数量，生成9张表中的数据。

• tpcc_start：根据不同选项进行TPC-C测试。