标签归档:innodb

MySQL 技术内幕:InnoDB存储引擎 笔记

第一章 MySQL体系结构和存储引擎
1.1 定义数据库和实例
>数据库:物理操作系统文件或其它形式文件类型的集合。在MySQL中,数据库文件可以是frm、myd、myi、ibd结尾的文件。当使用NDB引擎时,数据库文件可能不是操作系统上的文件,而是存放于内存之中的文件。
>数据库实例:由数据库后台进程/线程以及一个共享内存区组成。共享内存可以被运行的后台进程/线程所共享。数据库实例才是真正用来操作数据库文件的。

在MySQL中,实例和数据库通常关系是一一对应(这里说的数据是指DBMS,不是指具体的库)。但是在集群情况下可能存在一个数据库被多个实例使用的情况。MySQL被设计为一个单进程多线程架构的数据库,也就是说,MySQL数据库实例在系统上的表现就是一个进程。

#启动MySQL数据库实例
./mysqld_safe &

当启动实例时,MySQL数据库会去读取配置文件,根据配置文件的参数来启动数据库实例。在MySQL中,可以没有配置文件,这个情况,MySQL会按照编译时默认参数设置启动实例。用如下命令查看实例启动时,会去哪些位置查找配置文件(Linux下配置文件为cnf,Windows下为ini):

./mysql –help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

MySQL按照如上顺序读取配置文件。如果配置文件中都有同一个参数,MySQL会以读取到的最后一个配置文件中的参数为准。

配置文件中有一个datadir参数,该参数指定了数据库所在的路径。在Linux下,datadir默认为/usr/local/mysql/data(也不一定,决定安装方式)。这个路径可能是一个链接文件,必须保证MySQL的用户和用户组可以操作这个目录(读写执行)。

1.2 MySQL体系结构

MySQL由以下几部分组成:
链接池组件,管理服务和工具组件, SQL接口组件, 查询分析器组件,优化器组件,缓冲(Cache)组件,插件式存储引擎,物理文件

MySQL区别于其他数据库的最重要的特点是其插入式的表存储引擎。MySQL插入式的存储引擎架构提供了一系列标准的管理和服务支持,这些标准与存储引擎本身无关,可能是每个数据库系统本身都必需的,如SQL分析器和优化器等。而存储引擎是底层物理结构的实现。(存储引擎是基于表的,而不是数据库)

1.3 MySQL表存储引擎
由于MySQL数据库的开源特性,用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎。

1.3.1 InnoDB存储引擎
InnoDB存储引起支持事务,主要面向在线事务处理方面的应用(OLTP)。其特点是行锁设计、支持外键,并支持类似于Oracle的非锁定读,即默认情况下读取操作不会产生锁。从MySQL5.5.8版本开始,InnoDB存储引擎是默认的存储引擎。InnoDB存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由InnoDB自身进行管理。每个InnoDB存储引擎的表也可以单独存放到一个独立的ibd文件中,还可以使用裸设备(row disk)来建立其表空间(文件系统)。

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

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

1.3.2 MyISAM存储引擎
不支持事务、表锁和全文索引,主要面向一些OLAP数据库应用。MyISAM存储引擎的缓冲池只缓存索引文件而不缓冲数据文件,这和大多数数据库都非常不同。MyISAM存储引擎表有MYD和MYI组成,MYD用来存放数据文件,MYI用来存放索引文件。

1.3.3 NDB存储引擎
NDB的特点是数据全部放在内存中(从5.1开始,可以将非索引数据库放在磁盘上),因此主键查找速度极快,并且通过添加NDB数据存储节点可以线性地提供数据库性能,是高可用,高性能的集群系统。

1.3.4 Memory存储引擎
默认使用哈希索引。只支持表锁,并发性能较差,不支持TEXT和BLOB列类型。存储变长字段(varchar)时是按照定长字段(char)的方式进行的。MySQL数据库使用Memory存储引擎作为临时表存放查询中间结果集,如果中间结果集大于Memory存储引擎表的容量设置,或中间结果含有TEXT或BLOB列类型字段,则MySQL数据库会把其装换到MyISAM存储引擎表而存放到磁盘(MyISAM不缓存数据库文件,性能下降)。

1.3.5 Archive存储引擎
1.3.6 Federated存储引擎
1.3.7 Maria存储引擎
设计目标主要是用来取代原有的MyISAM存储引擎,可以看作是MyISAM的后续版本。特定:缓存数据和索引文件(MyISAM不缓存数据),行锁设计(MyISAM表锁),提供MVCC功能(MyISAM无),支持事务和非事务安全的选项支持(MyISAM不支持),以及更好的BLOB字符类型的处理性能。
1.3.7 其他存储引擎
1.4 各种存储引擎之间的比较
容量限制(Memory InnoDB 64TB限制) 事务支持 锁的粒度(Memory表锁) MVCC支持 支持的索引(Memory InnoDB都支持哈希和B+树索引) 备份和复制

show engines\G;

1.5 链接MySQL
本质是进程通信。常用进程通信方式有管道、命名管道、命名字、TCP/IP套接字、Unix域名套接字。
1.5.1 TCP/IP
通过TCP/IP连接到MySQL实例时,MySQL数据库会先检查权限视图(mysql库的user表),判断客户端IP是否允许连接。
1.5.2 命名管道
Windows系列….。在MySQL数据库中,需在配置中启用—enable-named-piple选项。MySQL4.1之后,MySQL还提供了共享内存的链接方式,在配置文件中添加—shared-memory。如果想使用共享内存的方式,在链接时,MySQL客户端还不行使用—protocal=memory选项。
1.5.3 Unix域套接字
在配置文件中指定套接字文件的路径,如:–socket=/tmp/mysql.sock。

第2章 InnoDB存储引擎
2.1 InnoDB存储引擎概述
InnoDB存储引擎是第一个完整支持ACID事务的MySQL存储引擎,其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读。
2.2 InnoDB存储引擎的版本
2.3 InnoDB体系架构
InnoDB有多个内存块,这些内存块组成了一个大的内存池,负责:
>为何所有进程/线程需要访问的多个内部数据结构
>缓存磁盘上的数据,方便快速地读取,并且在对磁盘文件的数据进行修改之前在这里缓存
>重做日志缓冲
…….
mysqlinnodb
后台线程的主要作用是负责刷新内存池中的数据,保存缓冲池中的内存缓存的是最近的数据。将已修改的数据文件刷新到磁盘文件,同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

2.3.1 后台线程
InnoDB存储引擎是多线程的模型,后台有多个不同的后台线程,负责处理不同的任务。
1.Master Thread
Master Thread是一个非常核心的后台线程,主要负责将缓冲池中的数据库一步刷新到磁盘,保证数据的一致性,包括脏页的刷新、合并插入缓冲、UNDO页的回收等。
2 IO Thread
在InnoDB存储引擎中大量使用了AIO(Async IO)来处理写IO请求。IO Thread的工作主要是负责这些IO请求的回调处理。从InnoDB1.0.x版本开始,read thread和write thread分别增大到了4个,并且不再使用innodb_file_io_threads参数,而分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置:

show variables like 'innodb_%io_threads'\G

#观察InnoDB的IO Thread
show engine innodb status\G

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)

IO Thread 0为insert buffer thread。IO Thread 1为log thread。之后是innodb_read_io_threads和innodb_write_io_threads参数设置的读写线程。
3、Purge Thread
事务被提交后,其所使用的undolog可能不再需要,因此需要PurgeThread来回收已经使用并分配的undo页。在InnoDB1.1版本之前,purge操作仅在InnoDB存储引擎的Master Thread中完成。之后,purge操作可以独立到单独的线程中进行,在配置文件中添加如下命令来启用独立的Purge Thread:

innodb_purge_threads=1

4、Page Cleaner Thread
脏页刷新操作放入到单独的线程中完成

2.3.1 内存
1 缓冲池
InnoDB存储引擎是基于磁盘存储的,并将其中的纪律按照页的方式进行管理。基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。(一般过程就是把数据读入缓冲池,读数据时先从缓冲池读,对于写,首先修改缓冲池中的页,再以一定的频率刷新到磁盘上),注意:页从缓冲池刷新回磁盘的操作并不是每次也发生更新时触发,而是通过一种称为Checkpoint的机制刷新回磁盘。

缓冲池的大小直接影响着数据库的整体性能。对于InnoDB,其缓冲池的配置通过参数innodb_buffer_pool_size来设置:

show variables like 'innodb_buffer_pool_size'\G

缓冲池中缓存的数据页类型有:索引页、数据页、undo页、插入缓存(insert bufeer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)、数据字典信息(data dictionary)等。

从InnoDB 1.0.x版本开始,允许有多个缓冲池实例。每个页根据哈希平均分配到不同缓冲池实例中。通过innodb_buffer_pool_instances来进行配置:

show variables like 'innodb_buffer_pool_instances'\G

通过命令show engine innodb status可以观察到每个缓冲池实例对象运行的状态。MySQL5.6开始,还可以通过information_schema架构下的表INNODB_BUFFER_POOL_STATS来观察缓冲的状态:

SELECT POOL_ID, POOL_SIZE, Free_BUFFERS, DATABASE_PAGES FROM INNODB_BUFFER_POOL_STATS\G

*************************** 1. row ***************************
       POOL_ID: 0
     POOL_SIZE: 8191
  Free_BUFFERS: 4188
DATABASE_PAGES: 3917
1 row in set (0.00 sec)

2 LRU List、Free List和Flush List
通常,数据库中的缓冲池通过LRU算法来进行管理。InnoDB对LRU算法进行了改进。。。。(这里涉及到InnoDB的几个配置项)

3 重做日志缓冲
InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区,然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置很大,因为一般情况下每一秒会将重做日志缓冲刷新到日志文件。由配置参数innodb_log_buffer_size控制,默认为8M(满足绝大部分应用)。

4 额外的内存池

show variables like ‘innodb_buffer_pool_size’\G;
show variables like ‘innodb_log_buffer_size’\G;
show variables like ‘innodb_additional_mem_pool_size’\G

2.4 Checkpoint技术
为了避免发生数据丢失的问题,当前事务数据库系统普遍都采用了Write Ahead Log策略,即当事务提交时,先写重做日志,在修改也(ACID中的D)。
Checkpoint(检查点,保持一个点,点之后的是需要刷新的数据)技术的目的解决以下问题:
缩短数据库的恢复时间;缓冲池不够用时,将脏页刷新到磁盘;重做日志不可用时,刷新脏页。

这里描述的原理设计到几个配置参数。

2.5 Master Thread工作方式
2.5.1 InnodB 1.0.x版本之前
2.5.2 InnodB 1.2.x版本之前
2.5.3 InnodB 1.2.x版本的Master Thread
对于刷新脏页的操作,从Master Thread线程分离到一个单独的Page cleaner Thread。

2.6 InnoDB关键特性
2.6.1 插入缓冲
1 Insert Buffer
2 Change Buffer
3 Insert Buffer的内部实现
4 Merge Insert Buffer
2.6.2 两次写
2.6.3 自适应哈希索引
2.6.4 异步IO
2.6.5 刷新邻接页

2.7 启动、关闭与恢复
在关闭时,参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。参数innodb_force_recovery影响了整个InnoDB存储引擎恢复的状况。

第3章 文件
3.1 参数文件
如果MySQL在默认的数据库目录下找不到mysql架构(必须存在),则启动会失败。
3.1.1 什么是参数
从MySQL 5.1开始,可以通过information_schema架构下的GLOBAL_VARIABLES视图进行查找。
3.1.2 参数类型
MySQL参数文件中的参数可以分为:动态参数和静态参数。可以通过SET命令对动态参数值进行修改。
3.2 日志文件
3.2.1 错误日志
通过show variables lik ‘log_error’来定位该文件。默认情况下错误文件的文件名为服务器的主机名。
3.2.2 慢查询日志
默认情况下,MySQL数据库并不启动慢查询日志。MySQL5.1开始可以将慢查询的日志记录放入一张表中,慢查询表在mysql架构下,名为slow_log。
3.2.3 查询日志
默认文件名为:主机名.log。从MySQL5.1开始,可以将查询日志的记录放入到mysql架构的general_log表。
3.2.4 二进制日志
二进制日志记录了对数据库执行更改的所有操作,但是不包括SELECT和SHOW这类操作,因为这类操作对数据本身没有修改。此外,二进制还包括了执行数据库更改操作的时间和执行时间等信息。二进制日志主要有以下两种作用:恢复 和 复制。

通过配置参数log-bin[=name]可以启动二进制日志。如果不指定name,则默认二进制日志文件名为主机名,后缀名为二进制日志的序列号,所在路径为数据库所在目录(datadir)。

以下配置参数影响二进制日志记录的信息和行为:
max_binlog_size
binlog_cache_size
sync_binlog
binlog-do-db
binlog-ingore-db
log-slave-update
binlog_format
参数max-binlog-size指定了单个二进制日志文件的最大值,如果超过该值,则产生新的二进制日志文件,后缀名+1,并记录到.index文件。从MySQL5开始默认值是1G。

当使用事务的表存储引擎时,所有未提交的二进制日志会被记录到一个缓存中,等该事务提交时直接将缓存中的二进制日志吸入二进制日志文件,而该缓存的大小有binglog_cache_size决定,默认大小为32KB。此外,binglog_cache_size是基于会话的,也就是说,当一个线程开始一个事务时,MySQL会自动分配一个大小binglog_cache_size的缓存,因此这个值不能设置过大。当一个事务的记录大于设定的binlog_cache_size时,MySQL会把缓冲中的日志写入一个临时文件中,因此该值又不能设置的太小。通过show global status命令查看binlog_cache_use(使用缓存次数)、binlog_cache_disk_use(使用临时文件次数)的状态,可以判断当前binlog_cache_size的设置是否合适。

默认情况下,二进制日志并不是在每次写的时候同步到磁盘(缓冲写)。因此,当发生突然停机时,可能会有最后一部分数据没有写入二进制日志文件中。这回给恢复和复制带来问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N改为1,表示采用同步写磁盘的方式来写二进制日志(写一次就同步)。该默认值为0。

参数binlog-do-db和binlog-ignore-db表示需要写入或忽略写入那些库日志。默认为空。

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

MySQL 5.1开始引入了binlog_format参数,该参数可设的值有STATEMENT ROW和MIXED。通常,将参数binlog_format设置为ROW,这可以为数据库的恢复和复制带来更好的可靠性。

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

3.3 套接字文件
3.4 pid文件
3.5 表结构定义文件
不论采用何种存储引擎,MySQL都有一个以frm为后缀的文件,这个文件记录了该表的表结构定义。frm还用来存放视图的定义。
3.6 InnoDB存储引擎文件
3.6.1 表空间文件
默认配置下,会有一个初始化大小为10MB,名为ibdata1的文件。该文件就是默认的表空间文件。可以通过参数innodb_data_file_path对其进行设置。也可以用多个文件组成一个表空,同时制定文件的属性:

innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

这里讲两个文件来组成表空间。若两个文件位于不同的磁盘上,则可以对性能带来一定程度的提升。两个文件都跟了属性,表示大小,如果用满,该文件可以自动增长(autoextend)。

设置了innodb_data_file_path参数后,之后对于所有基于InnoDB存储引擎的表的数据都会记录到该文件内。而通过设置参数innodb_file_per_table,可以将每个基于InnoDb存储引擎的表单独产生一个表空间,文件名为表名.ibd,这样不用将所有数据都存放在默认的表空间中。

第4章 表
4.1 InnoDB存储引起表类型
4.2 InnoDB逻辑存储结构
所有数据都被咯及地存放在一个空间,称之为表空间,表空间有由段 区 页组成。
4.2.1 表空间
对于启用了innodb_file_per_table的参数选项,每张表的表空间内存放的只是数据集、索引和插入缓冲,其它类的数据,如撤销信息、系统事务信息、二次写缓冲等还是存放在原来的共享表空间内。
4.2.2 段
4.2.3 区
区是由64个连续的也组成的,每个页大小为16KB,即每个区的大小为1MB。对于大数据段,InnoDB存储引擎最大每次可以申请4个区,以此来保证数据的顺序性能。
4.2.4 页
4.2.5 行

4.3 InnoDB物理存储结构
从物理上看,InnoDB表有共享表空间、日志文件组、表结构定义文件组成。若innodb_file_per_table设置为on,则每个表将独立地产生一个表空间文件,以ibd结尾,数据、索引、表的内部数据字典信息都将保持在这个单独的表空间文件中。表结构定义文件以frm结尾,这个与存储引擎无关。

4.4 InnoDB行记录格式
4.5 InnoDB数据页结构
4.6 Named File Formats
4.7 约束
4.8 视图
4.9 分区表
这个内容与另一本书的内容重复。

3.6.2 重做日志文件
默认情况下会有两个文件,名称分别为ib_logfile0和ib_logfile1。重做日志文件对于InnoDB存储引擎至关重要,它们记录了对于InnoDB存储引擎的事务日志。

第5章 索引与算法
5.1 InnoDB存储引擎索引概述
InnoDB存储引擎支持:(B+树索引 全文索引 哈希索引)
InnoDB存储引擎支持的哈希索引是自适应的,InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引。B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页面。然后把页读入到内存,再在内存中进行查找。
5.2 数据结构与算法
5.2.1 二分查找法
5.2.2 二叉查找树和平衡二叉树(AVL树)
二叉树可以任意构建,由此引入了平衡二叉树(任何节点的两个子树的高度最大差为1),平衡二叉树的查询速度很快,但维护一颗平衡二叉树的代价非常大。
5.3 B+树
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中,所有记录节点都是按键值的大小顺序放在同一层的叶子节点上,由各叶子节点指针进行连接。
5.3.1 B+树的插入操作
B+树的插入必须保证插入后叶子节点中的记录依然排序。(比如一个颗4层的B+树,最后一层是叶子节点,存放数据,前三层是非叶子节点,存放索引,当存放索引的节点满时,需要增加,当叶子节点满时需要增加叶子节点)
5.3.2 B+树的删除操作
5.4 B+树索引
在数据库中,B+树的高度一般都在2-4层,所以查找某一键值的行记录时最多只需要2到4次IO。

数据库中的B+树索引可以分为聚集索引(clustered index)和辅助索引(secondary index),但其内部都是B+树(即高度平衡的,叶子节点存放所有的数据)。聚集索引与辅助索引不同的是,聚集索引存放的是整行的信息。

5.4.1 聚集索引
聚集索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的即为整张表的行记录数据(多行),也将聚集索引的叶子节点称为数据页。由于实际的数据页只能按照一个B+树进行排序,因此每张表只能拥有一个聚集索引。
5.4.2 辅助索引
对于辅助索引,叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引中还包含了一个书签,该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表,因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
5.4.3 B+树索引的分裂(B+树的维护方面的问题)
5.4.4 B+树索引的管理
5.5 Cardinality值
5.6 B+树索引的使用
5.6.1 不同应用中B+树索引的使用
5.6.2 联合索引
5.6.3 覆盖索引
要查找的数据直接可以从索引树中取回,不需要二次搜索,比如直接查询ID,或直接查找索引,这些数据查找索引树本身就可以得到,所谓的覆盖索引。
5.6.4 优化器选择不使用索引的情况
通过辅助索引查找的数据是少量的,优化器选择辅助索引。(当扫描辅助索引得到表的很多数据,然后还要根据这个索引去聚集索引中扫描取回数据,这个取回操作是离散的,索引还不如直接全部扫描)
5.6.5 索引提示
MySQL数据库支持索引提示(Index Hint),显式地告诉优化器使用哪个索引。
5.7 哈希算法
5.8 全文检索

第6章 锁
6.1 什么是锁
数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。

InnoDB存储引擎锁提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销,并可以同时得到并发性和一致性。
6.2 lock与latch
6.3 InnoDB存储引擎中的锁
6.3.1 锁的类型
InnoDB存储引擎实现了如下两种标准的行级锁:
共享锁(S Lock),允许事务读一行数据。
排他锁(X Lock),允许事务删除或更新一行数据。

X锁与任何的锁都不兼容,而S锁仅和S锁兼容。

三个表,与事务,锁,锁等待相关。
6.3.2 一致性非锁定读
简单来说就是行被锁时,会保存一个快照(undo),在读这行时不会被堵塞,而是读取快照数据。

在REPEATABLE READ事务隔离级别下,对于快照数据,非一致性读总是读取事务开始时的行数据版本。
6.3.3 一致性锁定读
InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读:
SELECT FOR UPDATE
SELECT LOCK IN SHARE MODE(加S锁)
以上语句需要套入一个事务中。
6.3.4 自增长与锁
6.3.5 外键和锁

6.4 锁算法
6.4.1 行锁的3种算法
Record Lock
Gap Lock 锁定一个范围,但不包含记录本身
Next-Key Lock Gap Lock + Record Lock,锁定一个范围,包含记录本身

Next-Key Lock降级为Record Lock仅在查询的列是唯一索引的情况下。Gap Lock 的作用是为了阻止多个事务将记录插入到同一范围内。

在InnoDB存储引擎中,对于INSERT的操作,其会检查插入记录的下一条记录是否被锁定,若已经被锁定,则不允许插入。

6.4.2 解决Phantom Problem
Phantom Problem是指在同一个事务下,连续执行两次同样的SQL语句可能导致不同的结果。

6.5 锁问题
6.6 堵塞
InnoDB存储引擎中,参数innodb_lock_wait_timeout用来控制等待的时间(默认是50秒),innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是Off,代表不回滚)。

当发生超时时,MySQL数据库会抛出一个1205的错误。在默认情况下InnoDB存储引擎不会回滚超时引发的错误一异常。实际上InnoDB存储引擎在大部分情况下都不会对异常进行回滚。(注:检测到死锁时会自动回滚权重的低的事务)
6.7 死锁
6.7.1 死锁概念
死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象。

除了超时机制,当前数据库还都普遍采用wait-for graph(等待图)的方式来进行死锁检测。
6.7.2 死锁概率
6.7.3 死锁的示例

6.8 锁升级
锁升级(Lock Escalation)是指将当前锁的粒度降低。如行锁升级为一个页锁,或将页锁升级为表锁。InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的,其根据每个事务访问的每个页对锁进行管理的,采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录,其开销通常都是一致的。

第7章 事务
InnoDB存储引擎中的事务完全符合ACID的特性:
原子性(atomicity)
一致性(consistency)
隔离性(isolation) – 锁
持久性(durability)

7.1 认识事务
事务严格的定义必须同时满足四个特性(ACID)。InnoDB存储引擎默认的事务隔离级别为READ REPEATABLE,完全遵循和满足事务的ACID特性。

7.1.2 分类
从事务理论角度:
扁平事务(Flat Transactions) – 使用最多
带有保存点的扁平事务(Flat Transactions with Savepoints)
链接事务(Chained Transactions)
嵌套事务(Nested Transactions)
分布式事务(Distributed Transactions)

InnoDB存储引擎支持扁平事务,带有保存点的扁平事务,链接事务和分布式事务

7.2 事务的实现
事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志,用来保证事务的原子性和持久性。undo log用来保证事务的一致性。
7.2.1 redo
7.2.1 undo
7.2.3 purge
7.2.4 group commit

7.3 事务控制语句
在MySQL命令行的默认设置下,事务都是自动提交(auto commit)的,即执行SQL语句后马上执行COMMIT操作。要显式开启一个事务需要使用命令BEGIN、START TRANSACTION,或者执行命令SET AUTOCOMMIT=0,禁用当前会话的自动提交。

BEGIN(START TRANSACTION) 在存储过程中只能用START TRANSACTION(BEGIN被存储过程保留)
COMMIT(COMMIT WORK)
ROLLBACK(ROLLBACK WORK)
SAVEPOINT identifier: 创建一个保存点,一个事务中可以有多个SAVEPOINT
RELEASE SAVEPOINT identifier: 删除一个事务的保存点
ROLLBACK TO[SAVEPOINT]identifier: 和语句SAVEPOINT命令一起使用,把事务回滚到标记点。

SET TRANSACTION: 设置事务的隔离级别。InnoDB存储引擎提供的事务隔离级别有: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ(InnoDB默认), SERIALIZABLE.

7.4 隐式提交的SQL语句
7.5 对于事务操作的统计
7.6 事务的隔离级别
InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ(没有幻读保护),与标准SQL不同的是,InnoDB存储引擎在REPEATABLE READ事务隔离级别下,使用Next-Key Lock锁算法,隐藏避免幻读的产生。

隔离级别越低,事务请求的锁越少或保存锁的时间就越短(大部分数据库默认隔离级别是READ COMMITTED)。

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL 
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

#配置文件
transaction-isolation = READ COMMITTED

#查看
SELECT @@tx_isolation\G
SELECT @@global.tx_isolation\G

7.7 分布式事务
7.8 不好的事务习惯
7.8.1 在循环中提交
7.8.2 使用自动提交
7.8.3 使用自动回滚

7.9 长事务

第8章 备份与恢复
8.1 备份与恢复概述
Hot Backup
Cold Backup
Warm Backup(加一个全局读写)
8.2 冷备
对应InnoDB存储引擎的冷备,只需要备份MySQL数据库的frm文件,共享表空间文件,独立表空间文件(*。ibd),重做日志文件。另外,定期备份MySQL数据库的配置文件my.cnf。
8.3 逻辑备份
8.4 二进制日志备份与恢复
8.5 热备
8.6 快照备份
8.7 复制

第9章 性能调优
9.1 选择合适的CPU
数据库应用:OLTP(Online Transaction Processing,在线事务处理)和OLAP(Online Analytical Processing, 在线分析处理)。

OLAP是CPU密集型的操作,而OLTP是IO密集型的操作。如果CPU是多核的,可以通过修改参数innodb_read_io_threads和innodb_write_io_threads来增大IO的线程。

9.2 内存的重要性
9.3 硬盘对数据库性能的影响
9.4 合理地设置RAID
9.5 操作系统的选择
9.6 不同的文件系统对数据库性能的影响
9.7 选择合适的基准测试工具