• MySQL体系结构
  • InnoDB存储引擎特点
  • MyISAM存储引擎特点
• 存储
  • 内存结构
  • 聚集索引索引和非聚集索引
  • 插入缓冲
  • 两次写
  • 自适应哈希
• 表
  • 表结构
• 锁
  • 一致性的非锁定行读
  • 锁的类型
• 事务
• 日志

前段时间看的《MySQL技术内幕：InnoDB存储引擎》,对关键的地方做个总结

MySQL体系结构

InnoDB存储引擎特点

基于表 支持事务，用于在线事务处理（OLTP），行锁，支持外键 高并发，且支持SQL的四种隔离级别 通过聚集方式存储数据，也就是每张表的数据按照主键进行顺序存储

MyISAM存储引擎特点

不支持事务、表锁设计

存储

内存结构

聚集索引索引和非聚集索引

这是一个数据库名词,聚集索引(clusteredindex)表示索引的逻辑顺序和表中相应行的物理顺序一致;而非聚集索引(nonclusteredindex)则表示不一致. Mysql 没有聚集索引的概念,InnoDB引擎的主键是按照聚集索引的方式存放的.

插入缓冲

数据页的存放按照聚集索引(也就是主键)的执行顺序存放，但非聚集索引的叶子节点需要离散地访问非聚集索引页，导致插入性能变低；为了解决这个问题，InnoDB设计了插入缓冲。非聚集索引的插入或更新操作，先判断插入的非聚集索引页是否在缓冲池中。如果在，直接插入索引页；否则，先放入插入缓冲区中，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作。这样多个插入在一个索引页，可以合并到一个操作中。
插入缓冲的使用条件：非聚集索引&非唯一索引

两次写

缓冲池的脏页刷新到磁盘文件之前，会先拷贝到内存中的doublewritebuffer（2M），然后通过doublewritebuffer分两次写入到物理磁盘的共享表空间（每次1M）；然后后再把缓冲池的脏页同步到磁盘的数据文件中。通过这种操作保证了数据的可靠性.
表现在:当操作系统在将页写入磁盘的过程中崩溃,在恢复过程中,可以从共享表空间的doublewrite中找到页的一个副本,将其拷贝到表空间文件,再重做日志.避免了部分数据失效的情况.

自适应哈希

InnoDB存储引擎会自动根据访问频率和模式为某些页建立哈希索引.
哈希索引只能用来搜索等值的查询,对于其他查找类型,如范围查找,是不能使用的.(show engine innodb status)

表

表结构

主键默认指定的话怎么指定：1. 非空的唯一索引 2. 自动创建6字节的指针。

存储结构：

• 逻辑存储结构：所有数据被逻辑存放在一个空间中，即表空间。表空间分为段（segment）、区（extent）、页（page/block）组成
• 物理存储结构：共享表空间、日志文件组、表结构定义文件

行数据： compact行数据:变长字段列表;null标示位,数据行头信息,列1…,事务ＩＤ(隐藏)，回滚指针（隐藏）

页内部通过链表结构串联各个行记录,每行的头部最后四个字节表示下一条记录的偏移量
页中存放的是具体的数据记录
数据页结构:

关系型数据库的约束机制保证数据的完整性.

B+树的结构保证一个页中至少存放两条数据

InnoDBPlugin引入的新的文件格式:Barracuda,这个文件格式下的行记录有Compressed和Dynamic两种

从Mysql4.1之后,char(N)中的N指字符长度,而不是之前版本的字节长度.

锁

一致性的非锁定行读

在ReadCommited和RepeatableRead下,InnoDB存储引擎使用非锁定的一致性读.但在ReadCommitted事务隔离级别下,对于快照数据,非一致性读总是读取被锁定行的最新一份快照数据.在Repeatable事务隔离级别下和RepeatableRead事务隔离级别下,对于快照数据,非一致性读总是读取事务开始时的行数据版本.
InnoDB存储引擎中,通过NextKeyLock算法来避免不可重复读的问题,在这个算法下,对于索引的扫描,不仅仅锁住扫描到的索引,而且还锁住这些索引覆盖的范围,避免了另外的事务在这个范围内插入数据导致不可重复读的问题.因此InnoDB存储引擎默认的事务隔离级别是ReadRepeatable

锁的类型

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

• 共享锁（S Lock）：允许事务读取一行数据
• 排它锁（X Lock）：运行事务删除或更新一行数据

共享锁和排它锁都是mysql实现的悲观锁。

说明：

悲观锁和乐观锁是两个通用的概念，乐观锁是假定更新互相操作没有影响，在更新提交前检查要更新的数据是否和预期的一致，乐观锁一般使用版本号进行并发控制；悲观锁则是操作前加锁。

InnoDB存储引擎支持多粒度锁定，允许行级锁和表级别的锁同时存在。为了实现多粒度锁定，InnoDB存储引擎提供了意向锁，用来在一个事务中展示下一行将被请求的锁类型，意向锁是表级别的锁。

• 意向共享锁（IS Lock）：事务想要获得一个表中某几行的共享锁
• 意向排它锁（IX Lock）：事务想要获得一个表中某几行的排它锁

对读取操作进行加锁：

select for update:对读取的行数据加一个X锁

select lock in share mode:对读取的行记录加一个s锁

需要注意的是行锁是基于索引的，如果要查询的数据未建立索引，将会对整个表加锁。

InnoDB存储引擎中的三种行锁算法：

RecordLock:单个索引行记录的锁,锁索引记录本身,如果没有设置任何索引,InnoDB会使用隐式的主键进行锁定

GapLock:锁定一个范围,但不包括记录本身

NextKeyLock:GapLock+RecordLock,锁定一个范围,包括锁定记录本身

对于单个值的索引查询,加RecordLock InnoDB对于行的查询,在RepeatableRead模式下,默认采用NextKeyLock的行记录锁定算法

默认情况下,InnoDB存储引擎不会回滚超时引发的错误异常

InnoDB存储引擎不会回滚大部分的错误异常,但是死锁除外,发生死锁后,InnoDB存储引擎会马上回滚一个事务.(也就是说发生死锁的时候,会自动回滚一个事务,那么另一个事务会得到锁资源,继续做后续操作)

锁升级:会提高数据库性能,但同时并发性能降低.InnoDB存储引擎不存在锁升级的问题,因为InnoDB的锁是没有开销的.

查看锁的相关命令:

查看当前会话的事务隔离级别:select @@tx_isolation 查看全局事务隔离级别:select @@global.tx_isolation

begin
commit;
show engine innodb status

查看当前事务中锁的情况:
select * from information_schema.INNODB_TRX
select * from information_schema.INNODB_LOCKS
select * from information_schema.INNODB_LOCK_WAITS
设置事务隔离级别:set (global|session) transaction isolation level

• 快照读:读取的时候不会加锁,读取事务开始前的数据 事务一: mysql> begin;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from users;

+—-+————+———-+———+———————+———————+ | id | username | password | enabled | create_time | update_time | +—-+————+———-+———+———————+———————+ | 1 | userupdate | user | 1 | 2018-03-12 15:55:21 | 2018-03-13 19:13:44 | | 2 | admin | admin | 1 | 2018-03-12 15:55:21 | 2018-03-12 15:55:21 | +—-+————+———-+———+———————+———————+

2 rows in set (0.00 sec)

mysql> update users set username =’user’ where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from users;

+—-+———-+———-+———+———————+———————+ | id | username | password | enabled | create_time | update_time | +—-+———-+———-+———+———————+———————+ | 1 | user | user | 1 | 2018-03-12 15:55:21 | 2018-03-13 19:16:10 | | 2 | admin | admin | 1 | 2018-03-12 15:55:21 | 2018-03-12 15:55:21 | +—-+———-+———-+———+———————+———————+

2 rows in set (0.00 sec)

mysql> commit ; Query OK, 0 rows affected (0.01 sec)

事务二: mysql> select * from users;

+—-+————+———-+———+———————+———————+ | id | username | password | enabled | create_time | update_time | +—-+————+———-+———+———————+———————+ | 1 | userupdate | user | 1 | 2018-03-12 15:55:21 | 2018-03-13 19:13:44 | | 2 | admin | admin | 1 | 2018-03-12 15:55:21 | 2018-03-12 15:55:21 | +—-+————+———-+———+———————+———————+

2 rows in set (0.00 sec)

mysql> select * from users;

+—-+———-+———-+———+———————+———————+ | id | username | password | enabled | create_time | update_time | +—-+———-+———-+———+———————+———————+ | 1 | user | user | 1 | 2018-03-12 15:55:21 | 2018-03-13 19:16:10 | | 2 | admin | admin | 1 | 2018-03-12 15:55:21 | 2018-03-12 15:55:21 | +—-+———-+———-+———+———————+———————+

2 rows in set (0.00 sec)

事务二的两次查询操作分别位于commit前后.

事务

确保数据库提交工作时,要么所有修改已经保存了,要么所有修改都不保存. InnoDB存储引擎的事务符合ACID特性:atomic,consistency,isolation,durability. 隔离性由锁来保证;原子性,一致性,持久性由redo和undo来完成.

日志

InnoDB的日志分为两种：逻辑日志和物理日志，物理日志的恢复速度要快于逻辑日志

undo log和bin log属于逻辑日志，记录操作相关的信息

redo log记录对每个页的修改