观其大纲

第1章 MySQL体系结构和存储引擎
第2章 InnoDB存储引擎
第3章 文件
第4章 表
第5章 索引与算法
第6章 锁
第7章 事务
第8章 备份与恢复
第9章 性能调优
第10章 InnoDB存储引擎源代码的编译和调试

大纲细节

第1章 MySQL体系结构和存储引擎 1
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 命名管道和共享内存 15
1.5.3 UNIX域套接字 15
1.6 小结 15
第2章 InnoDB存储引擎 17
2.1 InnoDB存储引擎概述 17
2.2 InnoDB存储引擎的版本 18
2.3 InnoDB体系架构 19
2.3.1 后台线程 19
2.3.2 内存 22
2.4 Checkpoint技术 32
2.5 Master Thread工作方式 36
2.5.1 InnoDB 1.0.x版本之前的Master Thread 36
2.5.2 InnoDB1.2.x版本之前的Master Thread 41
2.5.3 InnoDB1.2.x版本的Master Thread 45
2.6 InnoDB关键特性 45
2.6.1 插入缓冲 46
2.6.2 两次写 53
2.6.3 自适应哈希索引 55
2.6.4 异步IO 57
2.6.5 刷新邻接页 58
2.7 启动、关闭与恢复 58
2.8 小结 61
第3章 文件 62
3.1 参数文件 62
3.1.1 什么是参数 63
3.1.2 参数类型 64
3.2 日志文件 65
3.2.1 错误日志 66
3.2.2 慢查询日志 67
3.2.3 查询日志 72
3.2.4 二进制日志 73
3.3 套接字文件 83
3.4 pid文件 83
3.5 表结构定义文件 84
3.6 InnoDB存储引擎文件 84
3.6.1 表空间文件 85
3.6.2 重做日志文件 86
3.7 小结 90
第4章 表 91
4.1 索引组织表 91
4.2 InnoDB逻辑存储结构 93
4.2.1 表空间 93
4.2.2 段 95
4.2.3 区 95
4.2.4 页 101
4.2.5 行 101
4.3 InnoDB行记录格式 102
4.3.1 Compact行记录格式 103
4.3.2 Redundant行记录格式 106
4.3.3 行溢出数据 110
4.3.4 Compressed和Dynamic行记录格式 117
4.3.5 CHAR的行结构存储 117
4.4 InnoDB数据页结构 120
4.4.1 File Header 121
4.4.2 Page Header 122
4.4.3 Infimum和Supremum Records 123
4.4.4 User Records和Free Space 123
4.4.5 Page Directory 124
4.4.6 File Trailer 124
4.4.7 InnoDB数据页结构示例分析 125
4.5 Named File Formats机制 132
4.6 约束 134
4.6.1 数据完整性 134
4.6.2 约束的创建和查找 135
4.6.3 约束和索引的区别 137
4.6.4 对错误数据的约束 137
4.6.5 ENUM和SET约束 139
4.6.6 触发器与约束 139
4.6.7 外键约束 142
4.7 视图 144
4.7.1 视图的作用 144
4.7.2 物化视图 147
4.8 分区表 152
4.8.1 分区概述 152
4.8.2 分区类型 155
4.8.3 子分区 168
4.8.4 分区中的NULL值 172
4.8.5 分区和性能 176
4.8.6 在表和分区间交换数据 180
4.9 小结 182
第5章 索引与算法 183
5.1 InnoDB存储引擎索引概述 183
5.2 数据结构与算法 184
5.2.1 二分查找法 184
5.2.2 二叉查找树和平衡二叉树 185
5.3 B+树 187
5.3.1 B+树的插入操作 187
5.3.2 B+树的删除操作 190
5.4 B+树索引 191
5.4.1 聚集索引 192
5.4.2 辅助索引 196
5.4.3 B+树索引的分裂 200
5.4.4 B+树索引的管理 202
5.5 Cardinality值 210
5.5.1 什么是Cardinality 210
5.5.2 InnoDB存储引擎的Cardinality统计 212
5.6 B+树索引的使用 215
5.6.1 不同应用中B+树索引的使用 215
5.6.2 联合索引 215
5.6.3 覆盖索引 218
5.6.4 优化器选择不使用索引的情况 219
5.6.5 索引提示 221
5.6.6 Multi-Range Read优化 223
5.6.7 Index Condition Pushdown（ICP）优化 226
5.7 哈希算法 227
5.7.1 哈希表 228
5.7.2 InnoDB存储引擎中的哈希算法 229
5.7.3 自适应哈希索引 230
5.8 全文检索 231
5.8.1 概述 231
5.8.2 倒排索引 232
5.8.3 InnoDB全文检索 233
5.8.4 全文检索 240
5.9 小结 248
第6章 锁 249
6.1 什么是锁 249
6.2 lock与latch 250
6.3 InnoDB存储引擎中的锁 252
6.3.1 锁的类型 252
6.3.2 一致性非锁定读 258
6.3.3 一致性锁定读 261
6.3.4 自增长与锁 262
6.3.5 外键和锁 264
6.4 锁的算法 265
6.4.1 行锁的3种算法 265
6.4.2 解决Phantom Problem 269
6.5 锁问题 271
6.5.1 脏读 271
6.5.2 不可重复读 273
6.5.3 丢失更新 274
6.6 阻塞 276
6.7 死锁 278
6.7.1 死锁的概念 278
6.7.2 死锁概率 280
6.7.3 死锁的示例 281
6.8 锁升级 283
6.9 小结 284
第7章 事务 285
7.1 认识事务 285
7.1.1 概述 285
7.1.2 分类 287
7.2 事务的实现 294
7.2.1 redo 294
7.2.2 undo 305
7.2.3 purge 317
7.2.4 group commit 319
7.3 事务控制语句 323
7.4 隐式提交的SQL语句 328
7.5 对于事务操作的统计 329
7.6 事务的隔离级别 330
7.7 分布式事务 335
7.7.1 MySQL数据库分布式事务 335
7.7.2 内部XA事务 340
7.8 不好的事务习惯 341
7.8.1 在循环中提交 341
7.8.2 使用自动提交 343
7.8.3 使用自动回滚 344
7.9 长事务 347
7.10 小结 349
第8章 备份与恢复 350
8.1 备份与恢复概述 350
8.2 冷备 352
8.3 逻辑备份 353
8.3.1 mysqldump 353
8.3.2 SELECT...INTO OUTFILE 360
8.3.3 逻辑备份的恢复 362
8.3.4 LOAD DATA INFILE 362
8.3.5 mysqlimport 364
8.4 二进制日志备份与恢复 366
8.5 热备 367
8.5.1 ibbackup 367
8.5.2 XtraBackup 368
8.5.3 XtraBackup实现增量备份 370
8.6 快照备份 372
8.7 复制 376
8.7.1 复制的工作原理 376
8.7.2 快照+复制的备份架构 380
8.8 小结 382
第9章 性能调优 383
9.1 选择合适的CPU 383
9.2 内存的重要性 384
9.3 硬盘对数据库性能的影响 387
9.3.1 传统机械硬盘 387
9.3.2 固态硬盘 387
9.4 合理地设置RAID 389
9.4.1 RAID类型 389
9.4.2 RAID Write Back功能 392
9.4.3 RAID配置工具 394
9.5 操作系统的选择 397
9.6 不同的文件系统对数据库性能的影响 398
9.7 选择合适的基准测试工具 399
9.7.1 sysbench 399
9.7.2 mysql-tpcc 405
9.8 小结 410
第10章 InnoDB存储引擎源代码的编译和调试 411
10.1 获取InnoDB存储引擎源代码 411
10.2 InnoDB源代码结构 413
10.3 MySQL 5.1版本编译和调试InnoDB源代码 415
10.3.1 Windows下的调试 415
10.3.2 Linux下的调试 418
10.4 cmake方式编译和调试InnoDB存储引擎 423
10.5 小结 424

熟知概念

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

定义数据库和实例

数据库（database）：物理操作系统文件或其他形式文件的集合;是依照某种数据模型组织起来并存放于二级存储器中的数据集合。通常表现为：frm、MYD、MYI、ibd结尾的文件。
实例(instance)：MySQL数据库由后台线程及一个共享内存区组成。共享内存可以被运行的后台线程所共享。数据库实例是真正用于操作数据库文件的。
通俗理解：
实例：程序，是位于用户与操作系统之间的一层数据管理软件，用户对与数据库数据的任何操作（增删改查），都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。
数据库：由一个个文件组成（一般来说都是二进制文件），要对这些文件进行增删改查，则需要通过数据库实例来完成。
关系：MySQL数据库中，实例和数据库的关系通常是一一对应的，即一个实例对应一个数据库。集群情况下，可能存在一个数据库被多个实例使用的情况。
MySQL数据库实例在系统上的表现为：一个进程。

MySQL体系结构

image

Parser:查询分析器组件SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

• 主要功能：
  a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 。
  b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

Optimizer：优化器组件，QL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。用一个例子就可以理解： select uid,name fromuser where gender = 1;这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件联接起来生成最终查询结果
Cache和Buffer：缓冲组件，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
Engine：插件式存储引擎，存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准和存储引擎本书无关。注意：存储引擎是基于表的，而不是基于数据库
物理文件：不展开。

MYSQL存储引擎

好处：每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。

InnoDB

1.从mysql5.5.8开始，InnoDB是默认的存储引擎。
2.设计目标：面向在线事物处理（OLTP）
3.特点：行锁设计，支持外键，支持非锁定读。即默认读取操作不会产生锁。
InnoDB把数据放在一个逻辑的表空间中，这个表空间像黑盒一样由InnoDB进行管理。可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中。支持用裸设备（row disk）来建立其表空间。通过多版本并发控制来获得高并发性。实现了SQL标准的4中隔离级别，默认为REPEATABLE级别。对表中数据的存储，采用了聚集的方式。因此，每张表的存储都是按照主键的顺序进行存放。如果没有显式的在表定义中指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

MyISAM存储引擎

不支持事务、表锁设计，支持全文索引，主要面向一些OLAP数据库应用。5.5.8之前，mysql的默认存储引擎。另外，它的缓冲池只缓冲索引文件，而不缓冲数据文件。MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

NDB存储引擎

集群存储引擎，其结构是share nothing的集群机构，因此能提供更高的可用性。特点是数据全部放在内存中，5.1开始，可以将非索引数据放在磁盘上，因此主键查找的速度极快，并通过添加NDB数据存储节点可以线性的提高数据库性能，是高可用，高性能的集群系统。
NDB存储引擎的链接操作（JOIN）是在mysql数据库层完成的，而不是在存储引擎层完成的。这意味着复杂的链接操作需要巨大的网络开销，因此查询速度很慢。

Memory存储引擎

将表中数据存放在内存中，如果数据库重启或发生奔溃，表中数据都将消失。适合存数据临时表及数据仓库中的纬度表。默认使用哈希索引，而不是B+树索引。只支持表锁，并发性能较差，不支持TEXT和BLOB列类型。存储变长（varchar）字段时是按照定长（char）字段的方式进行的，浪费内存。

Archive存储引擎

只支持insert和select操作，5.1以后支持索引。使用zlib算法将数据行进行压缩后存储，压缩比可达1:10。适合存储归档数据，如日志信息。使用行锁来实现高并发的插入操作，但是其本身不是事务安全的存储引擎，设计目标主要是提供高速的插入和压缩功能。

Federated存储引擎

不存放数据，只是指向一台远程mysql数据库服务器上的表。只支持mysql数据库表，不支持异构数据库表。

Maria存储引擎

新开发的引擎，主要是用来取代原有的MyISAM存储引擎，从而成为MySQL的默认存储引擎。支持缓存数据和索引文件，应用了行锁设计，提供了MVCC功能，支持事务和非事务安全的选项，以及更好的BLOB字符类型的处理性能。
其他存储引擎：不展开
show engines:查看当前mysql所支持的存储引擎。
通过查找information_schema架构下的engines表查看当前mysql所支持的存储引擎。
create table mytest Engine=MyISAM;
create table mytest Engine=InnoDB;
create table mytest Engine=ARCHIVE;

连接MySQL

一个连接进程和MySQL数据库实例进行通信。本质上是进程通信。
TCP/IP:我们平时所用的连接方式。通过TCP/IP连接到MySQL实例时，MySQL数据库会先检查一张权限视图，用来判断发起请求的客户端IP是否允许连接到MySQL实例。该视图在mysql架构下，表名为user.

命名管道和共享内存：

如果两个需要进程通信的进程在同一台服务器上，那么可以适用命名管道，在配置文件中启用--enable-named-pipe选项。
共享内存的连接方式：通过在配置文件中添加--shared-memory实现。如果想使用共享内存的方式，在连接时，MySQL客户端还必须使用--protocol=memory选项。

UNIX域套接字：

Linux或UNIX环境下，mysql客户端和数据库实例在一台服务器上的情况下可以使用。用户可以在配置文件中指定套接字文件的路径，如--socket=/tmp/mysql.sock.当数据库实例启动后，用户可以通过下列命令来进行UNIX域套接字文件的查找：
mysql > SHOW VARTABLES LIKE 'socket';
感觉这种连接方式不会常用的。

第2章 InnoDB存储引擎

Innodb体系结构

首先以一张图简单展示 InnoDB 的存储引擎的体系架构.从图中可见, InnoDB 存储引擎有多个内存块,这些内存块组成了一个大的内存池,主要负责如下工作:

• 维护所有进程/线程需要访问的多个内部数据结构
• 缓存磁盘上的数据, 方便快速读取, 同时在对磁盘文件修改之前进行缓存

• 重做日志(redo log)缓冲

  
  
  image.png
  
  

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

后台线程

InnoDB 使用的是多线程模型, 其后台有多个不同的线程负责处理不同的任务

1. Master Thread

这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括赃页的刷新、合并插入缓冲、UNDO 页的回收等.

2. IO Thread

在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求, IO Thread 的工作主要是负责这些 IO 请求的回调.
可以通过命令来观察 InnoDB 中的 IO Thread:
SHOW ENGINE INNODB STATUS\G
可以看到, InnoDB 共有10个 IO Thread, 分别是 4个 write、4个 read、1个 insert buffer和1个 log thread.

3. Purge Thread

事物被提交之后, undo log 可能不再需要,因此需要 Purge Thread 来回收已经使用比分配的 undo页. InnoDB 支持多个 Purge Thread, 这样做可以加快 undo 页的回收
InnoDB 引擎默认设置为4个 Purge Thread:
SHOW VARIABLES LIKE "innodb_purge_threads"\G

4. Page Cleaner Thread

Page Cleaner Thread 是新引入的,其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成,这样减轻了 Master Thread 的工作及对于用户查询线程的阻塞

内存

1. 缓冲池

缓冲池简单来说就是一块内存区域.在数据库中进行读取页的操作,首先将从磁盘读到的页存放在缓冲池中,下一次读取相同的页时,首先判断该页是不是在缓冲池中,若在,称该页在缓冲池中被命中,直接读取该页.否则,读取磁盘上的页.
对于数据库中页的修改操作,首先修改在缓冲池中页,然后再以一定的频率刷新到磁盘,并不是每次页发生改变就刷新回磁盘.
缓冲池的大小直接影响数据库的整体性能,对于 InnoDB 存储引擎而言,缓冲池配置通过参数 innodb_buffer_pool_size来设置. 下面显示本机虚拟机上一台 MySQL 数据库配置:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'\G

缓冲池中缓存的数据页类型有:索引页、数据页、 undo 页、插入缓冲、自适应哈希索引、 InnoDB 的锁信息、数据字典信息等.索引页和数据页占缓冲池的很大一部分.
下图显示 InnoDB 存储引擎总内存的结构情况.

image.png

2. 重做日志缓冲

InnoDB 存储引擎先将重做日志信息放入这个缓冲区,然后以一定频率将其刷新到重做日志文件.重做日志文件一般不需要设置得很大,因为在下列三种情况下重做日志缓冲中的内容会刷新到磁盘的重做日志文件中.

1. Master Thread 每一秒将重做日志缓冲刷新到重做日志文件
2. 每个事物提交时会将重做日志缓冲刷新到重做日志文件
3. 当重做日志缓冲剩余空间小于1/2时,重做日志缓冲刷新到重做日志文件

3. 额外的内存池

在 InnoDB 存储引擎中, 对一些数据结构本身的内存进行分配时,需要从额外的内存池中进行申请.例如,分配了缓冲池,但是每个缓冲池中的帧缓冲还有对应的缓冲控制对象,这些对象记录以一些诸如 LRU, 锁,等待等信息,而这个对象的内存需要从额外的内存池中申请.

Checkpoint技术

1，checkpoint产生的背景
数据库在发生增删查改操作的时候，都是先在buffer pool中完成的，为了提高事物操作的效率，buffer pool中修改之后的数据，并没有立即写入到磁盘，这有可能会导致内存中数据与磁盘中的数据产生不一致的情况。
事物要求之一是持久性（Durability），buffer pool与磁盘数据的不一致性的情况下发生故障，可能会导致数据无法持久化。
为了防止在内存中修改但尚未写入到磁盘的数据，在发生故障重启数据之后产生事物未持久化的情况，是通过日志(redo log)先行的方式来保证的。
redo log可以在故障重启之后实现“重做”，保证了事物的持久化的特性，但是redo log空间不可能无限制扩大，对于内存中已修改但尚未提交到磁盘的数据，也即脏页，也需要写入磁盘。
对于内存中的脏页，什么时候，什么情况下，将多少脏页写入磁盘，是由多方面因素决定的。
checkpoint的工作之一，就是对于内存中的脏页，在一定条件下将脏页刷新到磁盘。

2，checkpoint的分类
按照checkpoint刷新的方式，MySQL中的checkpoint分为两种，也即sharp checkpoint和fuzzy checkpoint。
sharp checkpoint：在关闭数据库的时候，将buffer pool中的脏页全部刷新到磁盘中。
fuzzy checkpoint：数据库正常运行时，在不同的时机，将部分脏页写入磁盘，进刷新部分脏页到磁盘，也是为了避免一次刷新全部的脏页造成的性能问题。

Innodb关键特性

插入缓冲

当插入数据需要更新非聚集索引时，如果每次都更新则需要进行多次随机IO，因此将这些值写入缓冲对相同页的进行合并提高IO性能。
插入非聚集索引时，先判断该索引页是否在缓冲池中，在则直接插入。否则写入到Insert Buffer对象。
条件：二级索引，索引不能是unique(因为如果是unique则必须保证唯一性，此时得检查所有索引页，还是随机IO了)
Change Buffer：包括Insert Buffer、Delete Buffer、Purge Buffer，update操作包括将记录标记为已删除和真正将记录删除两个过程，对应后两个Buffer。
Insert Buffer内部是一颗B+树
Merge Insert Buffer三种情况：
对应的索引页被读入缓冲池。
对应的索引页的可用空间小于1/32，则强制进行合并。
Master Thread中的合并插入缓冲。

两次写

在对脏页刷新到磁盘时，如果某一页还没写完就宕机，此时该页数据已经混乱无法通过redo实现恢复。innodb提供了doublewrite机制，其刷新脏页步骤如下：

1. 先将脏页数据复制到doublewrite buffer中（2MB内存）
2. 将doublewrite buffer分两次，每次1MB写入到doublewrite磁盘（2MB）中。
3. 马上同步脏页数据到磁盘。对于数据混乱的页则可以从doublewrite中读取到，该页写到共享表空间。

自适应哈希索引

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

异步IO

linux和windows中提供异步IO，其可以对连续的页做合并连续页的IO操作使随机IO变顺序IO。

刷新邻近页

刷新页时判断相邻页是否也是脏页。

一, InnoDB是什么
1、创始人为Heikki Tuuri(1964，芬兰赫尔辛基)，由Innobase Oy公司开发，mysql5.5版本开始是默认的表存储引擎。
2.特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，最有效的使用内存和CPU.
3.已应用于各大型网站，如：Google、Yahoo！、Facebook等。

二、InnoDB版本
1.早期版本随mysql数据库版本更新而更新
2.mysql5.1以后，支持存储引擎开发商以动态的形式加载。所以支持两个版本:
一个是静态编译的innoDB版本（老版本）【支持ACID、行锁设计、MVCC】
另一个是动态加载的InnoDB版本，即InnoDB Plugin,视为InnoDB 1.0.x【增加了compress和dynamic页格式】
3.mysql 5.5,innoDB升级为1.1.x【增加了Linux AIO、多回滚段】
4.mysql 5.6InnoDB升级为1.2.x【增加了全文索引支持，在线索引添加】
三、InnoDB的体系结构

image

第3章 文件

都有什么文件

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

MySQL参数文件的作用和Oracle的参数文件极其类似；不同的是，Oracle实例启动时若找不到参数文件，是不能进行装载（mount）操作的。MySQL稍微有所不同，MySQL实例可以不需要参数文件。

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

可以把数据库参数看成一个键/值对。可以通过show variables查看所有的参数，或通过like来过滤参数名。
从MySQL 5.1版本开始，可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找，如下所示：
select * from GLOBAL_VARIABLES where VARIABLE_NAME like 'innodb_buffer%'\G;
show variables like 'innodb_buffer%'\G

参数类型

MySQL参数文件中的参数可以分为两类：动态（dynamic）参数和静态（static）参数。动态参数意味着你可以在MySQL实例运行中进行更改；静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读（read only）的。
可以通过SET命令对动态的参数值进行修改，SET的语法如下：
SET
　　| [global|session] system_var_name=expr
　　| [@@global.|@@session.|@@] system_var_name=expr
这里可以看到global和session关键字，它们表明该参数的修改是基于当前会话还是整个实例的生命周期。
有些动态参数只能在会话中进行修改，如autocommit；
有些参数修改完后，在整个实例生命周期中都会生效，如binlog_cache_size；
而有些参数既可以在会话又可以在整个实例的生命周期内生效，如read_buffer_size。
例如： set read_buffer_size=524288;

日志文件:

日志文件类型

image.png

MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：
日志文件： 记入文件中的信息类型
错误日志：记录启动、运行或停止mysqld时出现的问题。
查询日志：记录建立的客户端连接和执行的语句。
更新日志：记录更改数据的语句。不赞成使用该日志。
二进制日志：记录所有更改数据的语句。还用于复制。
慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

mysql 日志包括：错误日志，二进制日志，通用查询日志，慢日志等
一：通用查询日志：
记录建立的客户端连接和执行的语句
1）show variables like '%verision%';
显示数据库版本号，存储引擎等信息
2)查看当前的通用日志是否开启
show variables like '%general%';
开启通用日志查询： set global general_log = on;
关闭通用日志查询：set global general_log = off;

3)查看当前慢文件的格式
show variables like '%log_output%';
设置通用日志输出为表和文件方式：
set global log_output = 'file,table';

二：慢查询日志：
记录所有执行时间超过long_query_time秒的所有查询或者不适用索引的查询
默认情况下，MySQL不开启慢查询日志，long_query_time的默认值为10，即运行时间超过10s的语句是慢查询语句。
一般来说，慢查询发生在大表中，且查询的字段没有建立索引，此时，要匹配查询的字段会对全表进行扫描，耗时查long_query_time表
查看当前慢查询日志的开启情况：
show variables like '%query%';
查询当前慢查询的语句个数：
show global status like '%slow%';
可以通过查询语句查看慢查询的语句：
select * from mysql.slow_log;
三：错误日志
MySQL错误日志是记录MySQL运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息。错误日志的命名通常为 服务器主机名.err
查看错误日志的详细信息：
show variables like '%log_err%';
错误日志归档，备份错误日志
shell>mv host_name.err host_name.err-old
shell> mysqladmin -u root -p flush-logs
shell>mv host_name.err-old back-directory

四：二进制日志
包含了所有更新了的数据或者潜在更新了的数据，
包含关于每个更新数据库的语句的执行时间信息
目的：
尽可能将数据库恢复到事故故障点，因为二进制日志包含备份后进行的所有更新，用于在主复制服务器上记录所有将发生送给从服务器的语句
删除所有二进制文件：
reset master
删除部分二进制文件：
purge master logs
查看是否启用二进制日志：
show variables like '%log_bin%';
查看所有的二进制参数
show variables like '%binlog%';

3.socket文件:一般在/tmp目录下，名为mysql.sock.
mysql有两种连接方式，常用的一般是tcp
mysql -h(ip) -uroot -pxxx #常用的
mysql -S /tmp/mysqld.sock
mysql 采用unix socket连接方式，比用tcp的方式更快，但只适用于mysql和应用同在一台PC上。如果不在同一台pc上，就没有办法连接了。

直接就用tcp方式mysql -h localhost -u root -proot直接登录了，没有用套接字这方式。由此也可看出不是必须。

Unix系统下本地连接mysql可以采用Unix域套接字方式，这种方式需要一个套接字文件。套接字文件由参数socket控制。一般在/tmp目录下，名为mysql.sock /tmp/mysq.sock
SHOW VARIABLES LOKE 'socket'\G

4.pid文件:实例的进程文件
mysql启动时，会将自己的进程ID写入一个文件中，该文件为pid文件。由参数pid_file控制，默认位于数据库目录下，文件名为主机名.pid
实验结果如下：
5.mysql表结构文件：
每个表或视图，都有一个以frm为后缀名的文本文件，记录该表的表结构定义。
6.InnoDB存储引擎文件
MySql中每个表存储引擎都有自己独有的文件，InnoDB存储引擎相关的文件主要包括：重做日志文件，表空间文件。

1.表空间文件

InnoDB采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。该文件就是默认的表空间文件（tablespace file），用户可以通过参数innodb_data_file_path对其进行设置.

mysql> show variables like 'innodb_data_file_path';

2.重做日志文件

在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。在MySql官方手册中将其称为InnoDB存储引擎的日志文件，不过更准确的定义应该是重做日志文件(redo log file)。
重做日志文件对于InnoDB存储引擎很重要，它们记录了对于InnoDB存储引擎的事务日志。
当实例或介质 failure时，重做日志文件就派上用场了。例如，数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。
每个InnoDB存储引擎至少有1个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。

第4章 表

4.1 索引组织表

在InnoDB存储引擎中，因为表都是按照主键的顺序进行存放的，这种存放方式，我们成为索引组织表(IOT)
那么，在整个创建表的过程中,InnoDB是怎么去创建主键的
1.显式的创建主键Praimary key
2.判断表中是否有非空唯一索引，如果有，则为主键
3.如果都不符合上述1/2的条件，则会生成UUID的一个隐式主键(6字节大)
在创建的的过程中，如果表中，有多个非空唯一索引的时候，则按照创建索引的顺序，以第一个非空唯一索引为准来创建。

SELECT a,b,c,d,_rowid FROM test;
如果表中有多个非空唯一索引时，InnoDB将选择建表时第一个定义的非空唯一索引为主键，通过_rowid可以显示表的主键，但是只能查看单个列作为主键的情况，对于多列组成的主键则不可以。

4.2InnoDB逻辑存储结构

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

image.png

表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。默认情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有数据都存放在这个表空间内。如果用户启动了innodb_file_per_table，则每个表内的数据可以单独放到一个表空间内，但要注意的是每张表的表空间内存放的只是数据、索引和插入缓存Bitmap页，而其他类的数据，如回滚(undo)信息，插入缓存索引页、系统事务信息、二次写缓存（Double write buffer）等还是存放在原来的共享表空间内。
即使设置了innodb_file_per_table为ON了，共享表空间还是会不断地增加其大小。

段

表空间由各个段组成，比如数据段，索引段，回滚段等。

区

区由连续的页组成，在任何情况下区的大小都是1M。InnoDB存储引擎一次从磁盘申请大概4-5个区。在默认情况下，页的大小为16KB，即一个区中有大概64个连续的页。

页

InnoDB磁盘管理的最小单位。

• B树节点= 一个物理Page（16K）
• 数据按16KB切片为Page 并编号
• 编号可映射到物理文件偏移(16K * N）
• B+树叶子节点前后形成双向链表
  
  image
• 增删改查之后
  • 有效Node组成双向链表
  • 中间存在空洞
  • 全表扫描时IO可能不连续
    关于页的详细结构，参考：MySQL的InnoDB索引原理详解

行

数据是按行进行存放的。

行记录格式

InnoDB 1.0.x之前：

• Compact
• Redundant 为了兼容之前的版本

InnoDB 1.0.x之后

• Compressed
• Dynamic

查看行格式的方法，注意row_format字段。
show table status like 'table_name'\G

4.3InnoDB行记录格式

Compact行记录格式

Compact行设计目标是能高效存放数据。简单来说，如果一个页中存放的行数据越多，其性能就越高。

Redundant行记录格式

Redundant是Mysql5.0之前的，不过多介绍。

行溢出数据

行溢出数据
InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外，即作为行溢出数据。一般认为BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。但是，这个理解有点偏差，BLOB可以不将数据放在溢出页面，而即使是varchar列数据类型，依然有可能存放为行溢出数据。
MySQL数据库的varchar字段，它可以存放65536字节的数据，比oracle和sqlserver大多了，但是在使用varchar时也有几点要注意；

• 1、65536只是这么说，要建表的时候指定一个字段65536仍然会报错，因为字段本身还有其他开销，实际只能存放65532字节。
• 2、65532字节并不是每个varchar字段都可以设置的，他是一个总和，也就是说如果有2个varchar字段的表，那么每个varchar字段只能设置65532/2的值。
• 3、建表时要注意编码格式哦，varchar(65532)代表的是字节数，如果使用GBK或者UTF-8那就无法建立成功了，因为GBK一个字符占用2字节，UTF-8一个字符占用3字节
  页（PAGE）是innoDB存储引擎的最小存储单位，默认大小为16KB，及16384字节，行数据存储在页中；那么一行数据如果超过了一个页能够存储的大小怎么办？比如上面说的varchar(65532)，65532字节该如何存储？这个时候就会发生行溢出。

行溢出：
InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外，一般为BLOB\LOB这类的大对象列类型。但是也不是绝对，BLOB可以不将数据放在溢出页面，而且即便是VARCHAR列数据类型，依然有可能被存放为行溢出数据

Compressed和Dynamic行记录格式

InnoDB Plugin引入了新的文件格式（file format，可以理解为新的页格式），对于以前支持的Compact和Redundant格式将其称为Antelope文件格式，新的文件格式称为Barracuda。Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic两种。新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。
下图是Barracuda文件格式的溢出行：

image

Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能进行非常有效的存储。

char的行结构存储

在InnoDB引擎内部对于char类型在多字节字符集类型（如utf8）的存储，char很明确的被视为了变长类型，对于未能占满长度的字符还是填充长度。可以说，在多字节字符集的情况下，char和varchar的行存储基本是没有区别的。

4.4InnoDB数据页结构

InnoDB数据页由以下七个部分组成：

页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-tree node的页，存放的即是表中行的实际数据了。
InnoDB数据页由以下七个部分组成，如图所示：

1. File Header（文件头）。
2. Page Header（页头）。
3. Infimun+Supremum Records。
4. User Records（用户记录，即行记录）。
5. Free Space（空闲空间）。
6. Page Directory（页目录）。
7. File Trailer（文件结尾信息）。
   !](http://upload-images.jianshu.io/upload_images/2830277-3092659511a48a88.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
   File Header、Page Header、File Trailer的大小是固定的，用来标示该页的一些信息，如Checksum、数据所在索引层等。其余部分为实际的行记录存储空间，因此大小是动态的。

File Header

File Header用来记录页的一些头信息。

Page Header

接着File Header部分的是Page Header，用来记录数据页的状态信息

Infimum和Supremum记录

在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Supremum和Infimum分别是主键值得上界和下界，这两个值在页创建时被建立，并且在任何情况下不会被删除。

User Records与FreeSpace

User Records即实际存储行记录的内容。再次强调，InnoDB存储引擎表总是B+树索引组织的。
Free Space指的就是空闲空间，同样也是个链表数据结构。当一条记录被删除后，该空间会被加入空闲链表中。

Page Directory

Page Directory（页目录）中存放了记录的相对位置。需要记住，B+树索引本身并不能找到具体的一条记录，B+树索引能找到的只是该记录所在的页。数据库把页载入内存，然后通过Page Directory再进行二分查找。

File Trailer

用来保证页能够完整的写入磁盘，来作比较以此来保证页的完整性。

Named File Formats机制

InnoDB存储引擎通过Named File Formats机制来解决不同版本下页结构兼容性的问题。

4.6约束

数据完整性

完整性是指数据的准确性和一致性，而完整性检查就是指检查数据的准确性和一致性。mysql数据库管理系统提供了一直机制来检查数据库中的数据是否满足规定的条件。以保证数据库中数据的准确性和一致性，这种机制就是约束。

• mysql所支持的完整性约束
  NOT NULL(NK)
  DEFAULT
  UNIQUE KEY(UK)约束字段的值是唯一的
  PRIMATYKEY(PK)
  AUTO_INCREMENT
  FOREIGN KEY(FK)

束和索引的区别

mysql 约束和索引
相同点: 保证证数据的完整性
区别: 索引是从数据结构的角度来保证数据完整性, 而 约束是一个逻辑概念,用来保证数据完整性.

保证数据完整性的方法:

一. 对错误数据的约束
设置sql_mode 为 严格模式, 来提示报错而不是警告
服务器配置: my.cnf sql_mode ='STRICT_TRANS_TABLES' ;
客户端 使用 : set sql_mode = 'STRICT_TRANS_TABLES' ;
比如在非严格模式下, 对字段已设置为not null , 插入了 非法日期的值: 比如 2009-02-30.

二. ENUM 和 SET 约束(针对数据类型).
比如假设 性别 只允许两种, male, female. enum('male', 'female'), 也可以 enum(0, 1). 如果设置为 tinyint(1), 值就可以存在 0-9.

三. 触发器约束
通过创建触发器来过滤错误的数据.一般用在字读间的计算.

四.外键
InnoDB支持外键, 可以通过外键来保证数据的完整性. 比如 一个用户表, 对应有张子表来存储一些额外的信息,
子表通过外键就可以达到只要主表有 delete 和 update 的操作,对应的数据也会 delete 和 update.
MyISAM 表不支持外键,可以只用 触发器来控制数据完整性.

4.7 视图

视图的主要用途之一是被用作一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表的结构，只需要按照视图定义来获取数据或者更新数据。因此，视图同时在一定程度上起到一个安全层的作用。
Mysql本身不支持物化视图，需要采用SELECT然后INSERT类似这种的方式导入视图的数据到表中。

4.8 分区

Mysql数据库表分区深入详解

0、mysql数据库分区的由来？

1）传统不分区数据库痛点

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），
一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。

2）数据库分区处理

如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。
表分区是Mysql被Oracle收购后推出的一个新特性。

一、表分区通俗解释

通俗地讲表分区是将一大表，根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。
如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

二、为什么要对表进行分区？

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

2.1 表分区要解决的问题：

当表非常大，或者表中有大量的历史记录，而“热数据”却位于表的末尾。如日志系统、新闻。。此时就可以考虑分区表。【注：此处也可以使用分表，但是会增加业务的复杂性。】

2.2 表分区有如下优点：

1）与单个磁盘或文件系统分区相比，可以存储更多的数据。
2）对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。
相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
同样的，你可以很快的通过删除分区来移除旧数据。你还可以优化、检查、修复个别分区。
3）一些查询可以得到极大的优化。 可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。
这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。
PS：因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
4）涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。
这种查询的一个简单例子如
“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。
通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
5）通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

三、mysql分区类型

根据所使用的不同分区规则可以分成几大分区类型。

分区类型

子分区
分区其实是对每个分区表的每个分区进行再次分隔，目前只有RANGE和LIST分区的表可以再进行子分区，子分区只能是HASH或者KEY分区。子分区可以将原本的数据进行再次的分区划分。

四、常见分区操作

常见分区操作

五、获取分区表信息的方法

5.1 show create table 表名

可以查看创建分区表的create语句
举例：

mysql> show create table foo_list;

5. 2 show table status

可以查看表是不是分区表
举例：
SHOW TABLE STATUS LIKE ‘foo_range’;

5.3 查看information_schema.partitions表

如下命令可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息
mysql> select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitions where
-> table_schema = schema()
-> and table_name='foo_range';

六、分区适用场景

7.1常见使用场景

1）当数据量很大(过T)时，肯定不能把数据再如到内存中，这样查询一个或一定范围的item是很耗时。另外一般这情况下，历史数据或不常访问的数据占很大部分，最新或热点数据占的比例不是很大。这时可以根据有些条件进行表分区。
2）分区表的更易管理，比如删除过去某一时间的历史数据，直接执行truncate，或者狠点drop整个分区，这比detele删除效率更高
3）当数据量很大，或者将来很大的，但单块磁盘的容量不够，或者想提升IO效率的时候，可以把没分区中的子分区挂载到不同的磁盘上。
4）使用分区表可避免某些特殊的瓶颈，例如Innodb的单个索引的互斥访问..
5）单个分区表的备份很恢复会更有效率，在某些场景下
总结：可伸缩性，可管理性，提高数据库查询效率。

7.2 业务场景举例

项目中需要动态新建、删除分区。如新闻表，按照时间维度中的月份对其分区，为了防止新闻表过大，只保留最近6个月的分区，同时预建后面3个月的分区，这个删除、预建分区的过程就是分区表的动态管理。

第5章 索引与算法

第6章 锁

锁 lock latch

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。
锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

MySQL大致可归纳为以下3种锁：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

lock与latch区别
这里要区分锁中容易令人混淆的概念lock与latch。在数据库中，lock与latch都可以成为锁，但两者有截然不同的含义

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

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

  
  
  image

InnoDB引擎中的锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。下面我们先介绍一点背景知识，然后详细讨论InnoDB的锁问题。

背景知识

事务（Transaction）及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

• 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
  　　银行转帐就是事务的一个典型例子。

并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文 档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问 题。
• 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加 控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

事务隔离级别

• 在上面讲到的并发事务处理带来的问题中，“更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
• “脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库实现事务隔离的方式，基本上可分为以下两种。

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一 致 性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

数据库的事务隔离越严格，并发副作用越 小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏 感，可能更关心数据并发访问的能力。

为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。表20-5很好地概括了这4个隔离级别的特性。

image.png

　最后要说明的是：各具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供Read committed和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别；SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读，但某些情况下又不是

一致性非锁定读（consistent nonlocking read）

一致性非锁定读是InnoDB存储引擎通过多版本控制（multi versioning）的方式来读取当前执行时间数据库中的数据。如果被读的数据行被加了排他锁，在读取这行数据的时候并不会等待锁释放，而是读取该行的一个快照数据。 之所以称为非锁定读，因为不需要等待被访问行的X锁的释放。快照数据是指修改行之前的数据版本，该实现通过undo段来完成。非锁定读的方式极大提高了数据库的并发性。在InnoDB存储引擎中，这是默认的读取方式。

一致性锁定读

在默认情况下，InnoDB存储引擎对数据采用的是一致性非锁定读。但是有些情况下为了保证数据逻辑的一致性，需要对SELECT的操作加锁。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read）操作。
1、 SELECT …… FOR UPDATE
2、 SELECT …… LOCK IN SHARE MODE
　　其中，SELECT …… FOR UPDATE对读取的记录加一个X锁（排它锁），其他事务不能对已锁定的行加任何锁。而SELECT …… LOCK IN SHARE MODE是对读取的记录加一个S锁（共享锁），其他事物可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

自增长与锁

自增长在数据库中是一种非常常见的一种属性，也是很多DBA或开发人员或者DBA人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器(auto_increment counter)。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：
select max(auto_inc_col) from test for update;
插入操作会根据这个自增长的计数器值加1赋予自增长列。这个实现方式称作为AUTO-INC Locking。这种锁采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

外键与锁

简单说一下外键，外键主要用于引用完整性的约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显示地对这个列加索引，InnoDB存储引擎会自动对其加一个索引，因为这样可以避免表锁。这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。

锁的算法

阻塞

数据库阻塞的现象：第一个连接占有资源没有释放，而第二个连接需要获取这个资源。如果第一个连接没有提交或者回滚，第二个连接会一直等待下去，直到第一个连接释放该资源为止。对于阻塞，数据库无法处理，所以对数据库操作要及时地提交或

者回滚。

死锁

是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去，解决死锁的最简单问题是不要有等待，任何的等待都转换为回滚，并且事务重新开始，但在线上环境，这可能会导致并发性能下降，甚至任何一个事务都不能进行，而这所带来的问题远比死锁的问题更严重

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

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式处理，或者说其是根据FIFO的顺序选择回滚对象，但若超时的事务所占权重比较大，如事务操作更新了很多航，占用了较多的undo log,这是采用FIFO方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会更多

除了超时机制，当前的数据库还采用wait-for graph(等待图)的方式来进行死锁检测，较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也是采用这种方式。wait-for graph要求数据库保存以下两种信息

• 锁的信息链表
• 事务等待链表

锁升级

第7章 事务

第8章 备份与恢复

第9章 性能调优

第10章 InnoDB存储引擎源代码的编译和调试

目录

• 获取InnoDB存储引擎源代码
• InnoDB源代码结构
• 编译和调试InnoDB源代码
  • Windows下的调试
  • Linux下的调试

InnoDB存储引擎是开源的，这意味着你可以获得其源代码，并查看内部的具体实现。任何时候，WHY都比WHAT重要。通过研究源代码，可以更好地理解数据库是如何工作的，从而知道如何使数据库更好地为你工作。如果你有一定的编程能力，则完全可以对InnoDB存储引擎进行扩展，开发出新的功能模块来更好地支持你的数据库应用。

回到顶部

获取InnoDB存储引擎源代码

InnoDB存储引擎的源代码被包含在MySQL数据库的源代码中，在MySQL的官方网站链接为：http：//www.mysql.com/downloads/mysql/。下载MySQL数据库的源代码即可。 这里有不同操作系统下的源代码可供下载，一般只需下载Generic Linux的版本即可。通过MySQL官网首页的Download链接，可以迅速地找到GA版本的下载。但是，如果想要下载目前正在开发的MySQL版本，可能在官网找了很久都找不到链接。这时，只要把下载的链接从www换到dev即可：如http：//dev.mysql.com/downloads/mysql，在这里可以找到开发中的MySQL版本的源代码了。单击“Download”下载标签后可以进入下载页面。当然，如果你有mysql.com账户，可以进行登录。MySQL官方提供了大量的镜像用来分流下载，你可以根据所在的位置选择下载速度最快的地址，中国用户一般可以在“Asia”这里的镜像下载。

下载的文件是tar.gz结尾的文件，可以通过Linux的tar命令、Windows的WinRAR工具来进行解压，解压后得到一个文件夹，这里面就包含了MySQL数据库的所有源代码。所有存储引擎的源代码都被放在storage的文件夹下，其源代码结构如图所示。

image

可以看到，所有存储引擎的源代码都在这里。文件夹名一般就是存储引擎的名称，如archive、blackhole、csv、fedorated、heap、ibmdb2i、myisam、innobase。从MySQL 5.5版本开始，InnoDB Plugin已经作为默认的InnoDB存储引擎版本；而在MySQL 5.1的源代码中，应该可以看到两个版本的InnoDB存储引擎源代码。可以看到有innobase和innodb_plugin两个文件夹：innobase文件夹是旧的InnoDB存储引擎的源代码；innodb_plugin文件夹是InnoDB Plugin存储引擎的源代码。如果你想将InnoDB Plugin直接静态编译到MySQL数据库中，那么需要删除innobase文件夹，再将innodb_plugin文件夹重命名为innobase。

回到顶部

InnoDB源代码结构

进入InnoDB存储引擎的源代码文件夹,可以看到源代码结构 :

下面介绍一些主要文件夹内源代码的具体作用：

btr：B+树的实现。

buf：缓冲池的实现，包括LRU算法、Flush刷新算法等。

dict：InnoDB存储引擎内存数据字典的实现。

dyn：InnoDB存储引擎动态数组的实现。

fil：InnoDB存储引擎中文件数据结构以及对于文件的一些操作。

fsp：你可以理解为file space，即对InnoDB存储引擎物理文件的管理，如页、区、段等。

ha：哈希算法的实现。

handler：继承于MySQL的handler，插件式存储引擎的实现。

ibuf：插入缓冲的实现。

include：InnoDB将头文件（.h，.ic）都统一放在这个文件夹下。

lock：InnoDB存储引擎锁的实现，如S锁、X锁以及定义锁的一系列算法。

log：日志缓冲和重组日志文件的实现。对重组日志感兴趣的，应该好好阅读该源代码。

mem：辅助缓冲池的实现，用来申请一些数据结构的内存。

mtr：事务的底层实现。

os：封装一些对于操作系统的操作。

page：页的实现。

row：对于各种类型行数据的操作。

srv：对于InnoDB存储引擎参数的设计。

sync：InnoDB存储引擎互斥量（Mutex）的实现。

thr：InnoDB储存引擎封装的可移植的线程库。

trx：事务的实现。

ut：工具类。

回到顶部

编译和调试InnoDB源代码

Windows下的调试

在Windows平台下，可以通过Visual Studion 2003、2005和2008开发工具对MySQL的源代码进行编译和调试。在此之前，需要预先安装如下的工具：

CMake：可以从http：//www.cmake.org下载。

bison：可以从http：//gnuwin32.sourceforge.net/packages/bison.htm下载。

安装之后，还需要通过configure.js这个命令进行配置：

C：\workdir＞win\configure.js options

option比较重要的选项如下所示。

WITH_INNOBASE_STORAGE_ENGINE：支持InnoDB存储引擎。

WITH_PARTITION_STORAGE_ENGINE：分区支持。

WITH_ARCHIVE_STORAGE_ENGINE：支持Archive存储引擎。

WITH_BLACKHOLE_STORAGE_ENGINE：支持Blackhole存储引擎。

WITH_EXAMPLE_STORAGE_ENGINE：支持Example存储引擎，这个存储引擎是展示给开发人员的，你可以从这个存储引擎开始构建自己的存储引擎。

WITH_FEDERATED_STORAGE_ENGINE：支持Federated存储引擎。

WITH_NDBCLUSTER_STORAGE_ENGINE：支持NDB Cluster存储引擎。

如果只是比较关心InnoDB存储引擎，可以这样进行设置，如图所示。

image

之后，可以根据你使用的是Visual Studio 2005还是Visual Studio 2008，在win文件下运行build-vsx.bat文件来生成Visual Studio的工程文件。build-vs8.bat表示Visual Studio 2005，build-vs8_x64.bat表示需要编译64位的MySQL数据库。如我们需要在32位的操作系统下使用Visual Studio 2008进行调试工作，则可以使用如下命令：

D：\Project\mysql-5.5.5-m3＞win\build-vs9.bat

这样就生成了MySQL.sln的工程文件，打开这个工程文件并将mysqld这个项目设置为默认的启动项，就可以进行MySQL的编译和调试了。

之后的编译、断点的设置和调试，与在Visual Studio下操作一般的程序没有什么区别。

Linux下的调试

Linux下的调试，通常使用Eclipse。其他一些类Unix操作系统，如Solaris、FreeBSD、MAC，同样可以使用Eclipse进行调试。

1. 到http：//www.eclipse.org/downloads/下载并安装Eclipse IDE for C/C++Developers。
2. 解压MySQL源代码到指定目录，如解压到/root/workspace/mysql-5.5.5-m3，
3. 运行如下命令产生Make文件（Eclipse会使用产生的这些Make文件）：[root mysql-5.5.5-m3]#BUILD/compile-amd64-debug-max-no-ndb-c,BUILD下有很多compile文件，你可以选择你所需要的文件。编译的平台是64位的Linux系统，并且希望可以进行Debug调试，因此选择了compile-amd64-debug-max-no-ndb文件。注意-c选项，这个选项只生产Make文件，不进行编译。
4. 接着打开Eclipse，新建一个C++的项目。给项目取个名称，如这里的项目名为mysql_5_5_5，并选择一个空的项目。选择Finish按钮后，可以看到新产生的一个空项目。
5. 之后选择左边的Project Explorer，右击项目mysql_5_5_5，选择新建文件夹，将文件夹/root/workspace/mysql-5.5.5-m3导入工程中。
6. 导入文件夹后，再右击项目名mysql_5_5_5，选择项目属性，在C/C++Build选项这里进行设置，需要将Build directory选择为源代码所在路径。 编译配置完后，程序就会自动开始执行编译工作了。
7. 上述的这个过程只是编译的过程，换句话说，编译完后就产生了mysqld这样的执行文件。如果想要进行调试，还需要在Debug这里进行如下的配置。 另外如果需要配置一些额外的参数，需要切换到Arguments选项。
8. 之后就可以设置断点，进行调试工作了，这和一般的程序并没有什么不同。