MySQL

InnoDB架构

MySQL内存架构和索引说明 - 图1

内存架构

Buffer Pool

主内存的一块区域，InnoDB在表和索引数据被访问的时候缓存它。在专用服务器上，高达80%的物理内存通常分配给buffer pool。配置项：innodb_buffer_pool_size，正常推荐设置50%-75%的系统内存
实现为page链表，缓存里很少的数据会使用LRU算法过时掉MySQL内存架构和索引说明 - 图2

Change Buffer

当二级索引page不在buffer pool时，缓存对这些二级索引page的更改。对于buffer的更改，稍后当page被其他读操作加载进buffer pool的时候，会进行合并MySQL内存架构和索引说明 - 图3在内存中，change buffer占用部分的buffer pool。在磁盘上，change buffer是系统表空间的一部分，当数据库服务器关闭的时候，索引的更改将在其中进行缓存

Adaptive Hash Index

自适应Hash索引，使InnoDB在具有适当的工作负载和有足够内存的buffer pool组合的系统上，执行起来更像内存中的数据库，而不会牺牲事务特性和可靠性。
根据观察到的搜索模式，使用索引key的前缀建立hash索引。hash索引是根据需要为经常需要访问的索引page构建的。
如果一个表差不多可以整个放进主内存，那么hash索引通过启用任何元素的直接查找来加快查询，将索引值转换为一种指针。

Log Buffer

持有将要被写入磁盘上的日志文件的内存区域。log buffer的内容会定期刷写到磁盘。默认16MB。大的log buffer能让大的事务运行而不需要在事务提交前将redo log数据写到磁盘上。所以如果有事务更新、插入或者删除很多行，增大log buffer能减少磁盘IO

磁盘结构

  1. 表 Tables
  2. 索引 Indexes
    聚簇索引能加快查询，因为索引搜索能直接指向包含行数据的page。如果表很大，聚簇索引通常能够节省磁盘IO操作
    聚簇索引之外的其他所有叫做二级索引。在innodb里，二级索引的每个记录都包含了行的主键列，以及为二级索引指定的列
    如果主键key很长，二级索引也会使用更多的空间
    InnoDB索引是B-Tree结构，空间索引是R-Tree结构。索引记录存储在B-Tree或R-Tree的叶子page上
  3. 表空间 Tablespaces
  • system表空间
    存储change buffer的区域。如果表创建在system表空间而不是file-per-table或general表空间，它也可能存储表和索引数据。
    它可以有一个或多个数据文件，默认是单个系统表空间数据文件，名称为ibdata1
  • File-Per-Table表空间
    包含单个InnoDB表的数据和索引，存储在文件系统的单个数据文件里
    文件名为：table_name.ibd
  • General表空间
    通过使用CREATE TABLESPACE语法创建的共享InnoDB表空间
    和system表空间类似，能够存储多个表的数据的共享表空间
    相对于file-per-table表空间，通用表空间有潜在的内存优势。服务器会在表空间的生命周期内将表空间的元数据保留在内存内，多个表在数量更少的通用表空间的占用表空间元数据的内存会更少
  • Undo表空间
    包含undo日志，它是记录的集合，包含了怎么撤销事务对聚簇索引最新的修改的信息
    Undo日志默认存储在system表空间，但也可以存储在一个或多个undo表空间里
  • Temporary表空间
    包含Session临时表空间，一个全局临时表空间
    session临时表空间存储着用户创建的临时表，以及当innodb配置为磁盘内部临时表存储引擎时优化器创建的内部临时表(从MYSQL8.0.16起磁盘内部临时表使用的存储引擎永远是InnoDB)
    全局临时表空间(ibtmp1)存储用户创建的临时表所做更改的回滚块
  1. Doublewrite Buffer
    是个存储区域，InnoDB在将page写入InnoDB数据文件中的适当位置之前，会在其中写入从buffer pool中刷新的page。如果在page写的过程中有操作系统、存储子系统，或者mysqld的异常退出，InnoDB可以在崩溃恢复期间从double buffer找到一个好的副本
  2. Redo Log
    是一种基于磁盘的数据结构，用于在崩溃恢复期间用于更正由于不完整的事务写入的数据。在异常停机前没有完成更新数据文件的修改，在初始化期间和接收连接之前会自动重放
    默认在磁盘上有ib_logfile0和ib_logfile1。MYSQL以循环方式写入redo log
  3. Undo Logs
    一个undo log是与单个读写事务关联的undo log记录集合。一个undo log记录包含了如何撤销一个事务对一条聚簇索引记录最近修改的信息。如果其他事务需要将原始事务视为一致性读的一部分，则会从undo log记录中检索出未修改的数据。
    undo logs存在于undo log段里，undo log段包含于回滚段里，回滚段驻留于system表空间、undo表空间和全局临时表空间
    驻留于全局临时表空间中的undo logs用于在用户定义的临时表里修改数据的事务。这些undo logs没有被redo logged，因为崩溃恢复不需要它们。它们只用于在服务器运行期间回滚。这种类型的undo logs通过避免redo logging的IO而提升了性能

一个事务最多分配4个undo logs，每个都是下面的操作类型之一：

  1. INSERT操作，用户定义的表
  2. UPDATE和DELETE操作，用户定义的表
  3. INSERT操作，用户定义的临时表
  4. UPDATE和DELETE操作，用户定义的临时表

    InnoDB锁和事务模型

    InnoDB锁

    共享锁和排他锁

  • 共享锁 shared (s)锁
    允许持有锁的事务读取一行

  • 排他锁 exclusive (x)锁
    运行持有锁的事务更新或者删除行

    意向锁 Intention Locks

    表级锁，用于指示事务稍后在表里需要哪种类型的锁(共享或排他)

  • intention shared lock (IS)表明事务将在表里独立的行上设置共享锁

  • intention exclusive lock (IX)表明事务将在表里独立的行上设置排他锁

SELECT … FOR SHARE设置IS锁，SELECT … FOR UPDATE设置IX锁
事务在能获取表里行的共享锁之前，必须先获取表的IS锁或更强的锁
事务在能获取表里行的排他锁之前，必须先获取表的IX锁或更强的锁

记录锁 Record Locks

在索引记录上的锁
记录锁始终锁定索引记录，即使表上没有定义索引。这种情况下，InnoDB会创建一个隐式的聚簇索引，使用这个索引锁定记录

间隙锁 Gap Locks

索引记录间隙之间的锁，或者第一个索引记录之前或最后一个索引记录之后的间隙的锁
e.g. SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
会阻止其他事务插入15的值到t.c1列，而不用管在这个列中有没有这个值
对于使用唯一索引搜索唯一行的语句，不需要间隙锁。e.g.列id有唯一索引，下面这语句只对id=100的行使用索引记录锁，其他会话是否在前面的间隙中插入行并不重要

  1. SELECT * FROM child WHERE id = 100;

如果id没有被索引，或者没有唯一索引，则语句会锁定前面的间隙
InnoDB的间隙锁是“纯抑制性的”，它的唯一目的是阻止其他事务插入到这个间隙，间隙锁可以共存，共享和排他间隙锁没有差别
间隙锁是可以被禁用的，如果被禁用可能会导致幻读问题，因为其他session可以将新行插入间隙

Next-Key锁

是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合
如果一个session在索引的记录R上有一个共享或排他锁，则另一个session不能在索引顺序中紧靠R之前的间隙中立即插入新的索引记录
假设索引包含值10，11，13，20。那么这个索引上可能的next-key锁包含下面的几种间隔
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
默认InnoDB运行在REPEATABLE READ事务隔离级别。这种情况下，InnoDB使用next-key锁来进行搜索和索引扫描

插入意向锁 Insert Intention Locks

行插入之前通过INSERT操作设置的一种间隙锁。此锁表示插入的意图，如果插入到同一个索引间隙的多个事务不在间隙的同个位置插入，则它们无需互相等待
假设有索引记录值4和7。不同的事务尝试插入5和6，在获得插入行的排它锁之前，每个事务都会使用插入意向锁锁住4和7之间的间隙，但因为行不冲突而不会互相阻塞
e.g. 客户端A创建一个表，包含了两个索引记录90和102，然后启动一个事务，对ID大于100的索引记录进行排它锁。这个排它锁包含了记录102前面的间隙

  1. mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
  2. mysql> INSERT INTO child (id) values (90),(102);
  4. mysql> START TRANSACTION;
  5. mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
  6. +-----+
  7. | id  |
  8. +-----+
  9. | 102 |
  10. +-----+

客户端B开始一个插入记录到间隙的事务。这个事务在它等待获取排它锁时接受插入意向锁

  1. mysql> START TRANSACTION;
  2. mysql> INSERT INTO child (id) VALUES (101);

AUTO-INC 锁

是一种特殊的表锁，事务插入有AUTO_INCREMENT列的表的时候使用。如果一个事务正在向表插入值，任何其他事务在插入这个表的时候都必须等待，以便第一个事务接收连续的主键值

InnoDB事务模型

事务隔离级别

  • REPEATABLE READ （默认级别）
    相同事务里的一致性读，使用第一次读的时候建立的快照。意味着在同一个事务里，如果发出了几个普通(非阻塞)的SELECT语句，这些SELECT语句彼此之间也是一致的
    对于锁定读(SELECT带有FOR UPDATE或FOR SHARE)，UPDATE, DELETE语句，锁定依赖于语句是否在唯一搜索条件里使用了唯一索引，或者范围搜索条件
    • 对于唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，不包含它前面的间隙
    • 对于其他搜索条件，InnoDB锁定索引扫描的范围，使用间隙锁或next-key锁阻止其他session插入间隙所覆盖的范围。
  • READ COMMITTED
    即使在同一个事务里，每次一致性读都会设置并读取自己的新快照。
    对于锁定读，InnoDB只锁定索引记录，不包含他们之前的间隙，因此允许在锁定记录旁自由插入新记录
    因为禁用了间隙锁定，可能会出现不可重复读，因为其他session可以插入新行到间隙中
    影响：
    • 对于UPDATE或DELETE语句，InnoDB只对它更新或删除的行持有锁。在MYSQL评估WHERE条件后，将释放不匹配行的锁，这大大降低了死锁的概率
    • 对于UPDATE语句，如果一个行已经锁定，InnoDB执行“半一致”读，将最新提交的版本返回给MySQL，以便MySQL可以决定该行是否匹配更新的WHERE条件

e.g. 可重复读 VS. 读已提交

  1. CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
  2. INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
  3. COMMIT;

这种情况下，表没有索引，因此搜索和索引扫描时使用隐式的聚簇索引来进行记录锁定

  1. ### Session A 执行更新语句
  2. START TRANSACTION;
  3. UPDATE t SET b = 5 WHERE b = 3;
  5. ### Session B 在sessionA之后执行
  6. UPDATE t SET b = 4 WHERE b = 2;

在InnoDB执行每个更新的时候，它先为每一行获取一个排它锁，然后决定是否修改它。如果InnoDB不修改这行，它就会释放这个锁。否则InnoDB持有锁，直到事务结束。
当使用默认的可重复读级别时，第一个UPDATE在它读取的每行上获取x锁，不释放它们中的任何一个：
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
第二个UPDATE在尝试获取任何锁的时候会立即阻塞(因为第一个更新在所有行上保留了锁)，并且在第一次UPDATE提交或回滚之前不会继续
x-lock(1,2); block and wait for first UPDATE to commit or roll back
如果使用READ COMMITTED，第一个UPDATE会在每行上获取一个x锁，并释放这些不修改的行
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)
对于第二个UPDATE，InnoDB执行“半一致”读，将读取的每一行的最新版本返回给MySQL，以便MySQL可以确定该行是否匹配UPDATE的WHERE条件
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock
然后如果WHERE条件包含索引列，并且InnoDB使用这个索引，那么在获取和保留记录锁的时候只考虑索引列。
在下面这个例子，第一个UPDATE在b=2的每一行上获取并持有x锁，第二个UPDATE当它尝试获取相同记录上的x锁的时候会阻塞，因为它也使用列b上定义的索引

  1. CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
  2. INSERT INTO t VALUES (1,2,3),(2,2,4);
  3. COMMIT;
  5. ### Session A
  6. START TRANSACTION;
  7. UPDATE t SET b = 3 WHERE b = 2 AND c = 3;
  9. ### Session B
  10. UPDATE t SET b = 4 WHERE b = 2 AND c = 4;
  • READ UNCOMMITTED 读未提交
    SELECT语句以非阻塞的方式执行，但可能会使用行的早期版本。因此读是不一致的，也叫做脏读
  • SERIALIZABLE 串行化
    InnoDB会在autocommit禁用的时候隐式的转换所有普通的SELECT语句为SELECT … FOR SHARE。如果启用autocommit，那么SELECT是它自己的事务。如果要强制普通的SELECT在其他事务修改了选中行的时候进行阻塞，需要禁用autocommit

    autocommit, Commit, Rollback

    InnoDB中，所有用户活动都是在事务里。如果autocommit模式启用，每个SQL语句都会形成它自己的事务。默认MySQL为每个新连接启动session的时候，会设置autocommit为启用，所以MySQL会在每个SQL语句没有返回错误之后进行一次提交。如果语句返回错误，会依赖具体的错误进行提交或回滚
    对于autocommit启用的session，可以通过START TRANSACTION或BEGIN语句开始事务，COMMIT或ROLLBACK结束事务。
    如果在一个session里通过SET autocommit = 0来禁用autocommit，那么这个session会始终有一个打开的事务。COMMIT或ROLLBACK语句会结束当前事务，启动新的事务。
    如果session禁用了autocommit，没有强制提交最后的事务，那么这事务会被回滚。

    一致非阻塞读

    一致读意味着InnoDB使用多版本控制呈现数据库在某个时间点的一个快照。查询可以看到在这个时间点之前其他事务已提交的更改，不能看得到之后或未提交的事务。
    如果事务隔离级别是默认的REPEATABLE READ，在相同事务里的所有一致读都会读取这个事务第一次读的时候建立的快照。可以通过提交当前事务并在之后发出新的查询来得到新的快照
    对于READ COMMITTED隔离级别，每个事务里的一致读都会设置并读取它自己的新快照
    一致读是InnoDB在READ COMMITTED和REPEATABLE READ隔离级别下处理SELECT语句的默认模式。一致读不会在它访问的表上设置任何锁，因此其他session可以同时自由修改这些表
    数据库的快照状态应用于事务里的SELECT语句，不一定应用于DML语句。如果插入或修改某些行然后提交该事务，则从另一个并发的可重复读事务会影响到这些刚提交的行，尽管这session查询不到它们。如果某个事务确实更新或删除了不同事务提交的行，则这些更改对当前事务是可见的。
    例子(其他session提交记录，原session查询不到，可以删除)： ```sql

    sessionA

    select count(name) from child where name = ‘hello100’;

    返回0行

sessionB插入两行并提交

insert into child(id,name) values(100, ‘hello100’); insert into child(id,name) values(101, ‘hello100’);

sessionA统计，返回0行

select count(name) from child where name = ‘hello100’;

sessionA删除，尽管查询不到，但可以删除2行

delete from child where name = ‘hello100’;

Query OK, 2 rows affected (0.00 sec)

  1. 可以通过提交事务，然后执行`SELECT`或`START TRANSACTION WITH CONSISTENT SNAPSHOT`来更新时间点。这就是多版本并发控制<br />下面的例子，sessionA只在B提交了插入，且A已提交后，才能看到B插入的记录
  2. ```sql
  3.             Session A              Session B
  5.            SET autocommit=0;      SET autocommit=0;
  6. time
  7. |          SELECT * FROM t;
  8. |          empty set
  9. |                                 INSERT INTO t VALUES (1, 2);
  10. |
  11. v          SELECT * FROM t;
  12.            empty set
  13.                                   COMMIT;
  15.            SELECT * FROM t;
  16.            empty set
  18.            COMMIT;
  20.            SELECT * FROM t;
  21.            ---------------------
  22.            |    1    |    2    |
  23.            ---------------------

如果想看到数据库的最新状态，那么要使用READ COMMITTED隔离级别，或者锁定读：

  1. SELECT * FROM t FOR SHARE;

读已提交隔离级别，一个事务里的每个一致读都会设置并读取它自己的新快照。对于FOR SHARE，SELECT一直阻塞直到包含最新行的事务结束

InnoDB中不同SQL语句设置的锁

锁定读，UPDATE或者DELETE通常会在SQL语句处理的过程中对每个被扫描到的索引记录设置记录锁。它不管语句中的WHERE条件是否会排除掉行。InnoDB不会记得准确的WHERE条件，只知道哪部分索引范围被扫描。
如果搜索中使用了二级索引，并且索引记录锁被设置为排他的，InnoDB也会检索出相应的聚簇索引记录并对它们设置锁。
如果没有索引适合执行语句，那么MySQL必须扫描整个表来处理语句，表的每一行都会被锁定，从而也会阻塞其他用户插入到这个表中。所以创建好的索引，让查询不会扫描超过需要的行是很重要的。

InnoDB中的死锁

因为不同的事务持有对方需要的锁，导致这些事务不能执行下去。由于每个事务都在等待资源变成可用，都不会释放它持有的锁。
可能出现的场景：多个事务以不同的顺序锁定多个表中的行(通过类似这样的语句：UPDATE或SELECT … FOR UPDATE)，锁定范围索引记录和间隙，每个事务因为时间的原因只获取一部分锁。
为了减少死锁的可能，

  • 使用事务而不是LOCK TABLES语句；
  • 保持insert或update的事务足够小，让他们不会长时间打开；
  • 当不同的事务更新多个表或者大范围的行，在每个事务里使用相同顺序的操作(例如SELECT … FOR UPDATE)；
  • 在SELECT … FOR UPDATE和UPDATE … WHERE 语句中使用到的列上创建好索引
  • 如果使用锁定读(SELECT … FOR UPDATE或者SELECT … FOR SHARE)，尝试使用例如READ COMMITTED这样的低隔离级别
  • 添加选择度高的索引到表中，这样查询只需要扫描更少的索引记录，相应的也就设置更少的锁。使用EXPLAIN SELECT来查看

死锁的可能不会被隔离级别影响，因为隔离级别只改变了读操作的行为，而死锁是因为写操作。

InnoDB多版本控制 MVCC

InnoDB是一个多版本的存储引擎。它持有被更改过的行的旧版本信息，以支持例如并发和回滚这样的事务特性。这个信息存储在undo表空间里面一个叫rollback segment的数据结构中。InnoDB使用回滚段里的信息来执行事务回滚里面的undo操作。它还使用这信息来构建更早版本的行用于实现一致性读。
InnoDB内部在数据库里存储的每个行里添加了三个字段：

  • 6-byte DB_TRX_ID，表示最近一个插入或更新行的事务的事务标识符。删除在内部也是被视为更新，行里面一个特定的bit会设置以标识它为已删除
  • 7-byte DB_ROLL_PTR，滚动指针。它指向回滚段里的一条undo日志记录。如果行被更新了，这条undo日志记录会包含更新前重建行所需要的必要信息
  • 6-byte DB_ROW_ID，包含了随着新行插入而单调递增的行ID。如果InnoDB自动生成了一个聚簇索引，索引会包含行ID的值。否则DB_ROW_ID列不会出现在任何索引里

回滚段里的Undo日志被分成了插入和更新的undo日志。插入的undo日志只在事务回滚的时候需要，可以在事务提交的时候立即丢弃掉。更新的undo日志也用于一致性读，但只有在不存在InnoDB已为其分配快照的事务时才能丢弃。在一致性读中，快照需要更新的undo日志中的信息用于构建数据库行的早期版本。
建议定期提交事务，包括仅发出一致性读的事务。否则InnoDB不能从更新的undo日志里丢弃数据，这样回滚段可能会增长得太大，充满它驻留的整个undo表空间。
回滚段里的undo日志记录的物理大小通常比相应的插入或更新行更小。可以使用这个信息来记录回滚段需要的空间。
在InnoDB多版本控制方案中，当使用SQL语句删除的时候，该行并不会立即从数据库中物理删除。InnoDB仅在丢弃为删除而写入的更新undo日志的时候，才会物理删除相应的行和索引记录。

多版本和二级索引

InnoDB 多版本并发控制(MVCC)对待二级索引不同于聚簇索引。聚簇索引中的记录会就地更新，其隐藏的系统列指向undo日志项，从中可以重构早期版本的记录。二级索引不包含隐藏的系统列，也不进行就地更新。
更新二级索引列时，旧的二级索引被删除标记，新记录被插入，删除标记的记录最终被清除。当二级索引记录被删除标记，或者二级索引页被更新的一个事务更新时，InnoDB会在聚簇索引中查找数据库记录。在聚簇索引中，检查记录的DB_TRX_ID，如果在读取事务启动后修改了记录，则从undo日志里检索出记录的正确版本。
如果二级索引记录被标记为删除，或者二级索引页被新的事务更新，覆盖索引记录就不会被使用。InnoDB不会从索引结构里返回值，而是会从聚簇索引里查找记录

MVCC和幻读

幻读，同一个事务里连续执行两次同样的SQL，可能导致不同结果的问题。第二次sql语句可能会返回之前不存在的行。

  • 在快照读情况下，通过mvcc来避免幻读
    mvcc利用历史版本信息(快照)来控制它能读取的数据范围
  • 在当前读情况下，通过next-key锁来避免幻读
  • 快照读
    简单的select操作
    Read Committed: 每次select都生成一个快照读
    Repeatable read: 开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读

  • 当前读
    读取最新版本的记录（即使别的事务提交的数据也能查询到），并且对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题。
    会让insert\update\delete阻塞

    1. select ... lock in share mode
    2. select ... for update
    3. insert
    4. update
    5. delete

    在RR级别:

  • 快照读通过MVCC和undo log来实现

  • 当前读通过加record lock(记录锁)和gap lock(间隙锁)来实现

    MySQL事务的实现原理

  • 事务的原子性是通过undo log来实现的

  • 事务的持久性是通过redo log来实现的
  • 事务的隔离性是通过(读写锁+MVCC)来实现的

  • 事务的一致性，是通过原子性、持久性、隔离性来实现的

    binlog

    binlog包含描述数据库更改(如表创建操作或表数据变更)的“事件”。除非使用基于行的日志记录，否则它还包含可能已进行更改的语句的事件(例如不匹配任何行的删除)。binlog还包含更新数据时每个语句花费了多长的时间。
    类型

  • STATEMENT 日志记录基于语句

  • ROW 日志记录基于行。默认方式

  • MIXED 日志记录使用混合格式(默认基于语句，特定情况下切换到基于行)

    MySQL默认隔离级别为什么是可重复读

  • 数据库默认隔离级别
    mysql-可重复读; oracle, PostgreSQL-读已提交

  • 为什么mysql用可重复读(RR)而不是读已提交(RC)
    5.0之前只有statement一种格式，主从复制会存在大量的不一致，故选用RR
  • 为什么默认隔离级别很多选用RC
    — 可重复读RR存在间隙锁，会使死锁的概率增大；在可重复读下，条件列未命中索引会锁表；在读已提交RC下，只锁行
    — 在读已提交RC下，引入半一致读(semi-consistent)特性增加了update操作的并发性能
    — 不可重复读在开发中是可以接受的