第2章 InnoDB存储引擎

事务安全。

2.1 InnoDB存储引擎概述

Mysql5.5开始是默认的表存储引擎(之前尽在Window下是)。第一个完整支持ACID事务的MySQL存储引擎。其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读,同时被设计用来最有效地利用以及使用内存和CUP。

高性能、高可用、高扩展。

2.2 InnoDB存储引擎的版本

2.3 InnoDB体系结构

1567518137685

后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据。

此外将已修改的数据文件刷新到磁盘文件,同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

2.3.1 后台线程

多线程模型。不同的后台线程处理不同的任务。

  1. Master Thread

将缓冲池的数据异步刷新到磁盘,保证数据一致性,包括脏页的刷新、合并插入缓冲(INSERT BUFFER)、UNDO页的回收。

  1. IO Thread

大量使用AIO(Async IO),极大提高性能。IO Thread主要负责这些IO请求的回调处理。

第3章 文件

  • 参数文件:告诉MySQL启动时查找文件的地址,指定初始化参数;
  • 日志文件:错误日志文件、二进制文件日志文件、慢查询日志文件、查询日志文件;
  • socket:当用UNIX域套接字方式进行连接时需要的文件
  • pid文件:MySQL实例的进程ID文件
  • MySQL表结构文件:用来存放MySQL表结构定义文件
  • 存储引擎文件:每个存储引擎都会有自己的文件来保存各种数据,这些存储引擎真正存储了记录和索引等数据。

3.1 参数文件

MySQL实例也可以不需要参数文件,这是编译MySQL时指定的默认值。但是如果在默认的数据库目录下找不到mysql架构,则会启动失败。

3.1.1 什么是参数

数据库中的参数是键值对。MySQL中无类似Oracle中的隐藏参数。

3.1.2 参数类型

  • 动态参数:运行时可修改
  • 静态参数:在整个实例的生命周期都不能修改。

3.2 日志文件

日志文件记录了MySQL数据库的各种类型活动。

3.2.1 错误日志

对MySQL的启动、运行、关闭过程进行了记录。当出现MySQL数据库不能正常启动时,第一个必须查找的文件应该就是错误日志文件。

3.2.2 慢查询日志

帮助DBA定位可能存在问题的SOL语句,从而进行SQL语句层面的优化。设置一个阈值(最小精度是微秒),超过(必须是大于,等于不行)时将该SQL语句记录到慢查询日志中。

默认情况下并不启动慢查询日志。

如果没有使用索引也会被记录。

3.2.3 查询日志

记录了所有对MySQL数据库请求的信息,无论这些请求是否得到了正确的执行。

3.2.4 二进制文件

记录了对MySQL数据库执行更改的所有操作,但是不包括SELEC和SHOW这类操作。

作用:

  • 恢复:某些数据的恢复需要二进制日志。
  • 复制:主从复制
  • 审计:通过审计二进制文件,判断是否有数据库进行注入的攻击。

二进制日志文件在默认情况下并没有启动,需要手动指定参数来启动。会影响性能,但仅仅1%。

缓冲写宕机问题,无缓冲写宕机问题

3.3 套接字文件

3.4 pid文件

3.5 表结构定义文件

记录该存储引擎对应的表结构

3.6 InnoDB存储引擎文件

InnoDB独有的文件

3.6.1 表空间文件

InnoDB采用将存储的数据按表空间进行存放的设计。

还可设置独立表空间,用户不用将所有数据都存放于默认的表空间中。

单独的表空间仅存储表的数据、索引和插入缓冲BITMAP等信息,其余的信息存放到默认表空间。

3.6.2 重做日志文件

宕机问题。

重做日志文件与二进制文件的区别:

  • 记录范围,二进制日志会记录所有与MySQL数据库有关的日志记录,而InnoDB仅记录该存储引擎的事务日志。
  • 记录内容,二进制日志记录具体操作(逻辑日志),而InnoDB的重做日志文件是关于每个Page的更改的物理情况
  • 写入时间,二进制日志文件只在事务提交前进行一次写入,而重做日志文件在事务的进行过程中,一直会被写入。

3.7 小结

重做日志文件使得InnoDB存储引擎可以提供可靠的事务。

第4章 表

4.1 索引组织表

在InnoDB存储引擎中,表都是根据主键顺序组织存放。

在InnoDB存储引擎中,每张表都有个主键,如果没有显示定义,则按如下方式创建:

  • 首先判断表中是否含有唯一非空索引(Unique NOT NULL),如果有,则该列即为主键。(按定义索引的顺序选择)
  • 如果不符合上述条件,InnoDB存储引擎自动创建一个6字节大小的指针。

_rowid仅适用于单个列为主键的情况。

4.2 InnoDB逻辑存储结构

表空间由段(segment),区(extent),页(page)组成。

4.2.1 表空间

独立表空间:存放数据、索引和插入缓冲Bitmap页。

共享表空间:存放其他类的数据,如回滚(undo)信息,插入缓冲索引页、系统事务信息,二次写缓冲。

回滚不会导致共享表空间缩小,只是会把不需要的undo信息标记为可用空间。

4.2.2 段

段有InnoDB存储引擎自身完成,DBA不能也没有必要控制段。常见的有数据段、索引段、回滚段等。

数据段为B+树的叶子节点。

索引段为B+树的非叶子节点。

4.2.3 区

区是由连续页组成的空间,每个区大小都是1MB。

默认页大小为16KB,一个区中有64个连续的页。

不论页的大小如何变,区的大小都是1M。

4.2.4 页

InnoDB管理磁盘的最小单位。

在InnoDB存储引擎中,常见的页有:

  • 数据页
  • undo页
  • 系统页
  • 事务数据页
  • 插入缓冲位图页
  • 插入缓冲空闲列表页
  • 未压缩的二进制大对象页
  • 压缩的二进制大对象页

4.2.5 行

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

每个页最多允许存放16KB/2-200行的记录,也就是7992行记录。

4.3 InnoDB 行记录格式

新版本都使用Compact格式。旧版本使用Redundant格式。

数据库实例的作用之一就是读取页中存放的行记录。

4.3.1 Compact行记录格式

NULL值不占用空间。

4.3.2 Redundant行记录格式

4.3.3 行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。

VARCHAR最大长度65535字节(建表时填写的是字符数)。

每个页中至少应该有两条行记录(否则失去了B+Tree的意义,变成了链表)。

第6章 锁

一个开发难点:利用数据库的并发访问,同时又要确保一致性读与写。数据库系统区别于文件系统的关进特性。

人们认为行级锁总会增加开销,实际上,只有当实现本身会增加开销时,行级锁才会增加开销。InnoDB不需要锁升级,因为一个锁和多个锁的开销时相同的。

6.1 什么是锁?

锁机制用于管理对共享资源的并发访问。不只是行级锁,同时会在内部其他地方使用锁。例如操作缓冲池LRU列表。

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

不同数据库实现对于锁的实现不同。

MyISAM引擎是表锁设计。并发情况下读没有问题,但是并发插入的性能差一些。

InnoDB存储引擎,提供一致性的非锁定读、行级锁支持。行级锁没有额外的开销,并可以同时得到并发性和一致性。

6.2 lock和latch

都可以被称为“锁”,但含义不同。这里主要说lock。

latch分为mutex(互斥锁)和rwlock(读写锁)。其目的是用来保证并发线程操作资源的正确性,没有死锁检测机制。

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

查看latch的命令:SHOW ENGINE INNODE MUTEX;

6.3 InnoDB存储引擎中的锁

6.3.1 锁的类型

两种标准类型的行级锁

  • 共享锁(S Lock)
  • 排他锁(X Lock)

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

InnoDB存储引擎支持多粒度锁定,允许行级锁和表级锁同时存在。为了支持不同粒度上加锁,InnoDB支持了一种额外的锁方式,意向锁(Intention Lock)。

意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望再更细力度上进行加锁。

即先用意向锁锁粗粒度,最后用行级锁锁住行。

  • 意向共享锁(IS Lock),事务想要获得一张表中某几行的共享锁。
  • 意向排他锁(IX Lock),事务想要获得一张表中某几行的排他锁。

InnoDB支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫描以外的任何请求。

1567084311609

lock_data不可信。当用户运行一个范围查找时,lock_data只返回第一行的主键值。

6.3.2 一致性非锁定读

一致性非锁定读是指InnoDB通过多版本控制的方式来读取当前执行时间数据库中行的数据。

如果读取的行正在执行DELETE或UPDATE操作,这是读取操作不会因此去等待行上锁的释放。而是去读取行的一个快照数据。

非锁定是因为不需要等待X锁地释放。快照通过undo实现,没有额外开销。而且读快照不需要上锁。

RC和RR下,InnoDB使用非锁定地一致性读。但对快照地定义不同。

RC违反了隔离性。

6.3.3 一致性锁定读

两种一致性锁定读操作:

  • SELECT … FOR UPDATE;
  • SELECT … LOCK IN SHARE MODE;

SELECT … FOR UPDATE;对读取地行记录加一个X锁,其他事务不能对已锁定地行加上任何锁。

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

事务提交时锁就释放了。

6.3.4 自增长与锁

再InnoDB的内存结构中,对每个含有自增长值得表都有一个自增长计数器。当对含有自增长技术器得表进行插入操作时,这个计数器会被初始化,执行如下得语句来得到计数器的值:

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作将其加1。这个实现风湿称作AUTO-INC Locking。这种锁其实是采用一种特殊得表锁机制,为了提高插入得性能,锁不在事务完成后释放,而是完成对自增长得SQL语句后立即释放。

虽然AUTO-INC Locking从一定程度上提高了并发插入得效率,但还是有性能问题。事务必须等待前一个插入的完成。其次对于INSERT…SELECT 的大数量的插入会影响插入的性能,因为另一个事务中的插入会被阻塞。

=========

5.1.22之后对这部分进行了一个优化:提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长插入的性能。

并且从该版本开始,InnoDB存储引擎提供了一个参数innodb_autonic_lock_mode来控制自增长,默认值为1。

自增长的插入分类图:

1567087007266

innodb_autoinc_lock_mode各个设置下对自增的影响:

1567087055534

特别注意:InnoDB的自增长实现与与MyISAM不同,MyISAM存储引擎是表锁设计,自增长不用考虑并发插入的问题。

在InnoDB中,自增长值得列必须是索引,同时必须是索引得第一个列。

6.3.5 外键和锁

6.4 锁的算法

6.4.1 行锁的3种算法

  • Record Lock:单个行记录上的锁
  • GapLock:间隙锁,锁定一个范围,但不包含记录本身
  • Next-Key Lock:Record Lock + GapLock,锁定一个范围并且锁定记录本身。

Record Lock总是会去锁住索引记录,如果建表时没有设置索引,则会使用隐式的主键来进行锁定。

Next-Key Lock锁定的区间为左开右闭区间,例如一个索引有10,11,13,20,那么可以锁定的区间有:

1567132480322

采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计目的时为了解决幻读。而利用这种锁定技术,锁定的时范围。

如果事务T1已经通过Next-Key Locking锁定了如果下范围:1567132624636

那么插入新的记录12时,锁定的范围会变成:1567132668860

特别注意三种情况:

  1. 就是说当查询的索引含有唯一属性,Next-Key Lock会被降级为Record Lock,即仅锁住索引本身,而不是范围。

  2. Next-Key Lock仅在查询的列时唯一索引的情况下。若是辅助索引,则还是使用传统的Next-Key Locking技术加锁,因为有两个索引,则需要分别锁定。对于聚集索引,则加Record Lock。而且辅助索引的下一索引加上gap lock。

  3. 若唯一索引由多个列组成,而查询仅是查找多个唯一索引的其中一个,还是使用Next-Key Lock。

显示的关闭Gap Lock:

  • 将事务的隔离级别设置为RC;
  • 将参数innodb_locks_unsafe_for_binlog设置为1

使用INSERT INTO SELECT进行复制表时,若查询的范围被锁定则插入操作会被阻塞。

6.4.2 解决幻象问题(幻读与不可重复读)

索引1,2,5的情况,执行SELECT * FROM t WHERE a > 2 FOR UPDATE;

锁住的是1567134017690

如果用户通过索引查询一个值,并对该行加上了一个S Lock,那么即使查询的值不存在,其锁定的也是一个范围,因此若没有返回任何行,那么新插入的值一定是唯一的。

若此时有并发情况则会死锁,所以并不会改变唯一性:

1567134404341

6.5 锁问题

6.5.1 脏读

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

脏读即一个事务可以读到另外一个事务中未提交的数据,违反了隔离性。

RU下会产生脏读。主从复制的从节点的查询不需要特别精确的返回值,所以可以利用脏读。

6.5.2 不可重复读

RC下会有,且允许不可重复读。

6.5.3 丢失更新

当前所有的隔离级别下都不会导致丢失更行。因为对于行的DML操作都会对行或其他粗粒度级别的对象加锁。

银行转账问题,或者超卖问题。通过加排他锁(悲观锁思想)解决。(我在秒杀系统中解决超卖使用的是乐观锁思想)

6.6 阻塞

阻塞会导致超时,InnoDB默认不会回滚超时引发的异常其实InnoDB在大部分情况下都不会对异常进行回滚,这个情况非常危险!!

6.7 死锁

6.7.1 死锁的概念

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

  • 超时机制,超时就回滚。

  • 等待图(类似操作系统中的资源分配图),主动检测死锁。深度优先遍历图,并且在后来的版本中将递归改为非递归。

6.7.2 死锁概率

  • 系统中事务的数量越多发生死锁的概率越大;
  • 每个事务操作的数量越多,发生死锁的概率越大;
  • 操作数据的集合越小发生死锁的概率越大。

死锁异常会回滚!

6.8 锁升级

锁升级会导致锁粒度将低而导致并发性能降低。

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

InnoDB的加锁开销小。