数据库原理

本文最后更新于：June 20, 2022 pm

MySQL 体系结构和存储引擎

MySQL 被设计成一个可移植数据库，几乎在当前所有系统上都能运行，如 Linux，Solaris，Mac 和 Windows。

数据库和实例

  • 数据库：物理操作系统文件或其他形式文件类型的集合。
  • 实例：MySQL 数据库由后台线程以及一个共享内存区组成。数据库实例才是真正用于操作数据库文件的。

MySQL 数据库实例在系统上的表现就是一个进程。

MySQL 体系结构

MySQL 主要由以下几部分组成：

  • 连接池组件
  • 管理服务和工具组件
  • SQL 接口组件
  • 查询分析器组件
  • 优化器组件
  • 缓冲（Cache）组件
  • 插件式存储引擎
  • 物理文件

MySQL 体系结构

MySQL 数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。存储引擎是基于表的，不是基于数据库的。

MySQL 存储引擎

存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。MySQL 默认使用 InnoDB 存储引擎，默认使用可重复读事务隔离级别。

功能 MYISAM Memory InnoDB Archive
存储限制 256 TB RAM 64 TB None
支持事务 No No Yes No
支持全文索引 Yes No No No
支持B 树索引 Yes Yes Yes No
支持哈希索引 No Yes No No
支持数据缓存 No N/A Yes No
支持外键 No No Yes No

MYISAM 和 InnoDB 的对比

比较常用的是 MYISAM 和 InnoDB，下面是两者的区别：

  1. 存储结构上的区别。每个MyISAM在磁盘上存储成三个文件，分别是：表定义文件、数据文件和索引文件。而 InnoDB 所有的表都保存在同一个数据文件中；
  2. InnoDB 支持事务，MYISAM 不支持事务。这也是 MySQL 将迷人存储引擎从 MYISAM 变成 InnoDB 的重要原因之一；
  3. InnoDB 支持外键，而 MYISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
  4. InnoDB 是聚集索引，MYISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的；
  5. InnoDB 不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。但是在加了 where 条件后， MyISAM 和 InnoDB 处理的方式都一样。
  6. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。
  7. 如果执行大量的 SELECT ，MyISAM 是更好的选择。如果你的数据执行大量的 INSERT 或 UPDATE ，出于性能方面的考虑，应该使用 InnoDB 表。

InnoDB 的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。

索引

索引分类

  • 单列索引
    • 普通索引：MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，只是为了查询数据更快一点。
    • 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
    • 主键索引：是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。
  • 组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
  • 全文索引：在大量数据中，通过其中的某个关键字，就能找到该字段所属的记录行。全文索引在开发中很少用，因为其占用很大的物理空间和降低了记录修改性。

索引是一种数据结构 。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。InnoDB 存储引擎支持以下几种常见的索引：

  • B+ 树索引

    B+ 树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+ 树索引类似于二叉树，根据键值快速找到数据，但它不是二叉树，B 代表 balance 而不是 binary。

  • 全文索引

    在 B 树索引中，当执行 select * from blog where content like %xxxx% 语句时，索引会失效。全文索引可以有效的解决这种语句查询。

  • 哈希索引

    InnoDB 存储引擎支持的哈希索引是自适应的，InnoDB 存储引擎会根据表的使用情况自动为表生成哈希索引，不能认为干预是否在一张表中生成哈希索引。

【注意】B+ 树索引并不能找到一个给定键值的具体行，B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

B 树

B 树建立在二叉树和平衡二叉树的基础上。二叉树的每个节点只有一个元素而 B 树的每个节点可能包含多个元素，并且非叶子节点在元素的左右都有指向子节点的指针。

B 树的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少磁盘操作次数。B 树为系统最优化大块数据的读和写操作。

一颗 m 阶的 B 树的特性如下：

  1. 每个节点最多 m 个子节点
  2. 除了根节点和叶子节点以外，每个节点最少有 m/2 个节点
  3. 如果根节点不是叶子节点，那根节点至少包含两个子节点
  4. 所有的叶子节点都位于同一层
  5. 每个节点都会包含 k 个元素（关键字），这里 m/2<= k < m
  6. 每个节点中的元素（关键字）从小到大排列
  7. 左节点元素的值小于该元素的值小于右节点元素的值

B 树

普通的 B 树的节点中，元素就是一个个数字。上图中把元素部分拆分成了 key-data 的形式，key 就是数据的主键，data 就是具体的数据。这样查找的时候沿着根节点往下就可以了，因此效率比较高。

B+ 树

B+ 树是在 B 树基础上的优化，使其更适合实现外存储索引结构。

B+ 树的特性如下：

  1. 所有的非叶子节点只存储关键字信息
  2. 所有具体数据存在叶子节点中
  3. 所有的叶子节点中包含了全部元素的信息
  4. 相邻叶子节点之间有一个链指针

B+ 树

B 树与 B+树的对比

  • B 树因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的 B树 和 B+ 树中，B 树查找某个关键字的效率更高。
  • 由于 B+ 树所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+ 树只需要找到该关键字然后沿着链表遍历就可以了，而B 树还需要遍历该关键字结点的根结点去搜索。
  • 由于 B 树的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而 B+ 树非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的 B 树的数据会比B+ 树存储的更少。这样同样总量的数据，B 树的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。
  • B+ 树的非叶子节点不包含数据信息，所有内存页中能存放更多的 key 。数据存放得更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据页具有更好的缓存命中率。
  • B 树只适合随机检索，而 B+ 树同时支持随机检索和顺序检索。
  • B+ 树增删文件（节点）时，效率更高。因为 B+ 树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

B+ 树的插入操作

当节点元素数量大于 m-1 的时候，按中间元素分裂成左右两部分，中间元素分裂到父节点当做索引存储，但是，本身中间元素还是分裂右边这一部分的。

B+ 树的删除操作

由于叶子节点有指针的存在，向兄弟节点借元素时，不需要通过父节点了，而是可以直接通过兄弟节移动即可（前提是兄弟节点的元素大于 m/2 ），然后更新父节点的索引；如果兄弟节点的元素不大于 m/2（兄弟节点也没有多余的元素），则将当前节点和兄弟节点合并，并且删除父节点中的 key。

插入与删除操作图示可查看面试官问你B树和B+树，就把这篇文章丢给他 - 知乎

数据库中的 B+ 树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引

聚集索引就是按照每张表的主键构造一颗 B+ 树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。由于实际的数据页只能按照一颗 B+ 树进行排序，因此每张表只能拥有一个聚集索引。

辅助索引

对于辅助索引，也称为非聚集索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引对应的行数据。由于 InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。每张表上可以有多个辅助索引。

聚集索引与辅助索引的区别

  • 一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。
  • 聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
  • 聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
  • MYISAM 和 InooDB 的主键索引实现方式不一样，InooDB 的叶子节点存储的完整的 data 记录（整行数据） ，而 MYISAM 的叶子节点存储的是数据的地址指针。
  • 通过聚集索引可以一次查到需要查找的数据，而通过非聚集索引第一次只能查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据。从非聚集索引上找到对应的主键值然后到聚集索引上查找对应记录的过程为回表。

索引覆盖：覆盖索引指的是所查询的数据就在索引当中，无需回表重新再查一遍。

索引的缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
  • 空间方面：索引需要占额外的物理空间

创建索引时的注意事项

  1. 选择合适的字段创建索引

    • 不为 null 的字段

    • 被频繁查询的字段

    • 被作为条件查询的字段

    • 频繁需要排序的字段

  2. 被频繁更新的字段应该慎重建立索引

  3. 尽可能的考虑联合索引而不是单列索引

    • 因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的

  4. 注意避免冗余索引

    • 冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引

最左匹配原则

  • 最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边；
  • 当我们创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

索引失效的情况

  1. 在 where 子句中进行 null 值判断的话会导致引擎放弃索引而产生全表扫描；
  2. 在 where 子句中使用!=，< >这样的符号,否则会导致引擎放弃索引而产生全表扫描；
  3. 在 where 子句中使用or来连接条件，因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描；
  4. 在 where 子句中=的左边使用表达式操作或者函数操作；
  5. 在 where 子句中使用like模糊查询；
  6. 在使用联合索引是要注意最左原则，例如如果当前联合索引是index(a,b,c)，那么如果 where 子句中有a就会用到联合索引，但是如果只用到b,c就会失去索引效果。

主键索引和唯一索引的区别

  1. 对于主键/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；
  2. 主键不一定只包含一个字段，所以在主键的其中一个字段建唯一索引还是有必要的；
  3. 主键可作外键，唯一索引不可；
  4. 主键不可为空，唯一索引可；
  5. 主键可是多个字段的组合；
  6. 主键索引一定是唯一索引， 唯一索引不是主键索引；
  7. 主键可以与外键 构成 参照完整性约束， 防止数据不一致

锁

分类

数据库对数据的操作无非两种类型，读和写，针对这两个特点，目前有两种数据库的锁，乐观锁和悲观锁，区别在 于是否认为并发问题一定存在。

数据库中的锁

  • 按锁的粒度划分：
    • 表级锁：表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用。
    • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。
    • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
  • 按锁的级别划分：共享锁、排他锁
  • 按加锁方式分：自动锁（存储引擎自行根据需要施加的锁）、显式锁（用户手动请求的锁）
  • 按操作划分：DML 锁（对数据进行操作的锁)、DDL 锁（对表结构进行变更的锁）
  • 最后按使用方式划分：悲观锁、乐观锁

MySQL 的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）；BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL 锁分类

相关概念

共享锁 S

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

1
SELECT ... LOCK IN SHARE MODE;

排他锁 X

排他锁又称为写锁，简称 X 锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

1
SELECT ... FOR UPDATE;

意向锁

意向锁属于表锁，用于行锁和表锁共存。

意向共享锁：在给一个数据行加共享锁时必须获取该表的 IS 锁。

意向排他锁：在给一个数据行加共享锁时必须获取该表的 IX 锁。

表级锁兼容性

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，该事务就要等待锁释放。

【注意】

  1. IX ，IS 是表级锁，不会和行级的 X，S 锁发生冲突。只会和表级的 X，S 锁发生冲突。
  2. MySQL 行锁锁的是索引而不是实际的数据行。

加意向锁表明某个事务正在锁定一行或者将要锁定一行。首先申请意向锁的动作是 InnoDB 完成的，怎么理解意向锁呢？

例如：事务 A 要对一行记录 r 进行上 X 锁，那么 InnoDB 会先申请表的 IX 锁，再锁定记录 r 的 X 锁。在事务 A 完成之前，事务 B 想要来个全表操作，此时直接在表级别的 IX 就告诉事务 B 需要等待而不需要在表上判断每一行是否有锁。意向排它锁存在的价值在于节约 InnoDB 对于锁的定位和处理性能。

InnoDB 有3种行锁的算法

  • Record Lock：单个行记录上的锁

  • Gap Lock：间隙锁，锁定一个范围，而非记录本身，是 Innodb 在 Repeatable Read 隔离级别下为了解决幻读问题时引入的锁机制。

  • Next-Key Lock：结合 Gap Lock 和 Record Lock ，锁定一个范围，并且锁定记录本身。主要解决的问题是 Repeatable Read 隔离级别下的幻读问题。

脏读

在事务 A 执行过程中，事务 A 对数据资源进行了修改，事务B读取了事务A修改后的数据。由于某些原因，事务 A 并没有完成提交，发生了 RollBack 操作，则事务B读取的数据就是脏数据。这种读取到另一个事务未提交的数据的现象就是脏读（Dirty Read）。

幻读

事务 B 前后两次读取同一个范围的数据，在事务 B 两次读取的过程中事务 A 新增了数据，导致事务 B 后一次读取到前一次查询没有看到的行。幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新。

不可重复读

事务 B 读取了两次数据资源，在这两次读取的过程中事务 A 修改了数据，导致事务 B 在这两次读取出来的数据不一致。这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读（Nonrepeatable Read）。

MVCC

MVCC，全程是(Multi-Version Concurrency Control)，即多版本并发控制，实际上就是保存了数据在某个时间节点的快照。

MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和 MVCC 不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。

最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读：读取的是记录数据的可见版本（可能是过期的数据），不用加锁。像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读，而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

MySQL 事务日志

  • Bin Log：是 MySQL 服务层产生的日志，常用来进行数据恢复、数据库复制，常见的 MySQL 主从架构，就是采用 slave 同步 maste r的 binlog 实现的。
  • Redo Log：记录了数据操作在物理层面的修改，MySQL 中使用了大量缓存，修改操作时会直接修改内存，而不是立刻修改磁盘，事务进行中时会不断的产生 redo log，在事务提交时进行一次 flush 操作，保存到磁盘中。当数据库或主机失效重启时，会根据 redo log 进行数据的恢复，如果 redo log 中有事务提交，则进行事务提交修改数据。
  • Undo Log：除了记录 redo log 外，当进行数据修改时还会记录 undo log ，undo log 用于数据的撤回操作，它记录了修改的反向操作，比如，插入对应删除，修改对应修改为原来的数据，通过undo log可以实现事务回滚，并且可以根据 undo log 回溯到某个特定的版本的数据，实现MVCC。

MVCC 实现

MVCC 模型在 MySQL 中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的。

隐式字段：

  • DB_TRX_ID：数据库默认该行记录生成的唯一隐式主键
  • DB_ROLL_PTR：回滚指针，用于配合 undo 日志，指向上一个旧版本
  • DB_ROW_ID：当前操作该记录的事务 ID

undo日志：

  • insert undo log，代表事务在insert新纪录时产生的undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
  • update undo log，事务在update或delete时产生的undo log，不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程同一清除。

对 MVCC 有帮助的实质是update undo log

Read View：

事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID 。ReadView 是一个包含当前已经开始但是没有提交的事务的列表，记录每个事务的事务id，记最小事务 ID 为 min_id ，最大事务 ID 为 max_id 。

版本比较规则：

  • 如果记录版本的事务 ID 小于 min_id ，说明这个记录版本是已经被提交过的，对其他事务可见；
  • 如果记录版本的事务 ID 大于 max_id ，说明这个记录版本是 ReadView 生成之后发生的，不能访问；
  • 如果记录版本的事务 ID 在 min_id 和 max_id 之间，判断事务 ID 是否在 ReadView 中：
    • 如果在 ReadView 中，说明事务还未提交，该记录版本不可访问
    • 反之说明该事务已经提交，该记录版本可以访问

如果当前记录版本不可读，就根据回滚指针 roll_pointer 找到旧版本的记录再进行判断。

对于 RC ，每次查询都会生成一个新的 ReadView 。

对于 RR ，一个事务在第一次 SELECT 的时候生成一个 ReadView ，之后的查询复用这个 ReadView 。

MVCC 实现的简单理解可以参考文章MYSQL MVCC实现原理 - 简书。

MVCC实现例子：

假设当前有一个事务 id 为100的事务 A ，修改记录的 name 字段为 李四，产生一个版本快照，有下面的版本链：

版本链示意图

假设事务 A 还没有提交，此时事务 B 进行 SELECT ，事务 id 为 120，查询 id 为 1 的记录（记为第一次查询），此时生成 ReadView 为[100,120]，根据版本读取规则，先找到事务 id 为100的记录版本，发现不可读，于是通过回滚指针找到事务 id 为60的记录，读取成功。

当事务 A 提交之后，事务 B 再次进行 SELECT 查询 id 为1的记录（第二次查询），在 RC 和 RR 两种隔离级别下有不同的情况：

  • 如果是 RC ，则会创建一个新的ReadView为[120]，此时读取事务 id 为100的记录成功，也就是读取到了在事务期间提交的数据；
  • 如果是 RR ，则会使用第一次查询时的ReadView为[100,120]，此时读取的是事务 id 为60的记录，从而实现了可重复读；

MVCC 如何在 RR 下解决幻读

产生幻读的原因：行锁只能锁住一行，不能避免新插入的记录。

解决：

  • 在两行记录之间加上间隙锁，阻止新记录的插入，与间隙锁产生冲突的只有“往这个间隙插入记录”这个操作；
  • 同时添加间隙锁与行锁称为 Next-key lock ，注意间隙锁只有在 InnoDB 的可重复读隔离级别下生效。

事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。

四大特性（ACID）

  • 原子性（Atomicity）：事务要么全部完成，要么全部取消。 如果事务崩溃，状态回到事务之前（事务回滚）。
  • 隔离性（Isolation）：如果2个事务 T1 和 T2 同时运行，事务 T1 和 T2 最终的结果是相同的，不管 T1和T2谁先结束。
  • 持久性（Durability）：一旦事务提交，不管发生什么（崩溃或者出错），数据要保存在数据库中。
  • 一致性（Consistency）：只有合法的数据（依照关系约束和函数约束）才能写入数据库。

事务的实现

事务的隔离性由锁来实现，原子性、一致性和持久性通过数据库的 redo log 和 undo log 来完成。redo log 称为重做日志，用来保证事务的持久性。undo log 用来保证事务的原子性。而事务的一致性是通过原子性，持久性，隔离性来实现的。

redo 恢复提交事务修改的页操作，而 undo 回滚行记录到某个特定版本。redo 通常是物理日志，记录的是页的物理修改操作。undo 是逻辑日志，根据每行记录进行记录。

原子性的实现

当进行插入，删除以及更新操作时生成 undo log，记录事务修改之前版本的数据信息，当系统发生错误或者执行 rollback 操作时需要根据 undo log 进行回滚。

生成 undo log

回滚

持久性的实现

MySQL 的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘 IO ，然而即使是使用 SSD 磁盘 IO 也是非常消耗性能的，因此为了提升性能 InnoDB 提供了缓冲池（Buffer Pool），其中包含了含自盘数据页的映射，可以当作缓存来用。但这种机制也有新的问题，在 MySQL 系统宕机或者断电的时候可能会丢失数据。因为可能数据已经提交了但还在缓冲池中，没来得及磁盘持久化。

所以需要一种机制存一下已提交的事务，为恢复数据使用。red log 可达到这种效果，事务开始之后就产生 redo log ，redo log 的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo log 文件中。

隔离性的实现

读写锁 + MVCC。

一致性的实现

通过原子性，持久性，隔离性来共同实现。

事务的隔离级别

  • Read Uncommited（读取未提交内容）

    在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

  • Read Commited（读取提交内容）

    这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。

    这种隔离级别 也支持所谓的 不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理期间可能会有新的 commit ，所以同一 select 可能返回不同结果。

  • Repeatable Read（可重读）

    这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

    可重复读隔离级别只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务不得更新该记录。

    不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决幻读的问题。

  • Serializable（可串行化）

    这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

在 MySQL 中，实现了这四种隔离级别，分别有可能产生问题如下表所示：

隔离级别 脏读 不可重复读 幻读
Read Uncommited ✔ ✔ ✔
Read Commited ✘ ✔ ✔
Repeatable Read ✘ ✘ ✔
Serializable ✘ ✘ ✘

其他

主从复制

将主数据库中的 DDL 和 DML 操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

主从复制的作用

  1. 主数据库出现问题，可以切换到从数据库。
  2. 可以进行数据库层面的读写分离。
  3. 可以在从数据库上进行日常备份。

大表数据查询，怎么优化

  1. 优化 shema、sql 语句+索引；
  2. 加缓存，memcached，redis；
  3. 主从复制，读写分离；
  4. 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
  5. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的 sharding key , 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql 中尽量带 sharding key ，将数据定位到限定的表上去查，而不是扫描全部的表。

MySQL 慢查询

原因：

  1. 没有索引或者没有用到索引（这是查询慢最常见的问题，是程序设计的缺陷，面试最核心的问答点）；
  2. IO吞吐量小，形成了瓶颈；
  3. 没有 创建计算列导致查询不优化；
  4. 内存不足；
  5. 网络速度慢；
  6. 一次查询的数量过大；
  7. 锁或者死锁（这是查询慢最常见的问题，是程序设计的缺陷)。

MySQL 慢查询就是在日志中记录运行比较慢的SQL语句，下面是慢查询的优化步骤：

  1. 查看运行效果，是否真的很慢，主要设置 SQL_NO_CACHE；
  2. WHERE 条件单表查询，锁定最小返回记录表。这句话的意思是，把查询语句的 WHERE 都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高；
  3. EXPLAIN 查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）；
  4. ORDER BY LIMIT 形式的 SQL 语句，让排序的表优先查；
  5. 多去了解业务的使用场景；
  6. 加索引时，要参照建立索引的几大原则；
  7. 观察结果，不符合预期，则重新从1开始分析。

参考

  1. 《MySQL 技术内幕 InnoDB 存储引擎 第2版》
  2. Mysql 中 MyISAM 和 InnoDB 的区别有哪些？- 知乎
  3. B、B+树的理解（数据库索引预热）- 简书
  4. 什么是B+树 - 博客园
  5. 面试官问你B树和B+树，就把这篇文章丢给他 - 知乎
  6. 详解 BAT 面试中常考的数据库「锁」问题 - 知乎
  7. MVCC详解 - xuwc - 博客园
  8. 关于MVCC，我之前写错了，这次我改好了！- 知乎
  9. MYSQL MVCC实现原理 - 简书
  10. 你说熟悉MySQL事务，那来谈谈事务的实现原理吧！- 知乎
Java后端
数据库 MySQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！