首页 面试指北 数据库

# 事务

数据库中的事务是指对数据库执行一批操作，在同一个事务当中，这些操作最终要么全部执行成功，要么全部失败，不会存在部分成功的情况。

# ACID

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持久性（ Durability ）。这四个特性简称为 ACID 特性。

  • 原子性（Atomicity）：一个事务中的多组操作，要么全部成功，要么全部失败。在事务提交（commit）成功之后，所有的操作都生效，提交失败，所有的操作都会回滚。
  • 一致性（Consistency）：一个事务执行之前和执行之后数据库都必须处于一致性状态。在事务执行的过程中，只要事务未提交，就不会改变数据库的状态。提交之后事务已完成，此时数据库状态发生变化。
  • 隔离性（Isolation）：事务在执行过程中，是与外界完全隔离的，即使数据库发生了变更，事务中也获取不到。A 事务对数据库做的变更，在事务未提交之间，数据库中也看不到，B 事务中也看不到。
  • 持久性（Durability）：事务一旦提交，对数据库的变更就会持久化到磁盘，即使数据库发生异常重启，数据也不会丢失。

# 隔离级别

SQL 标准定义了 4 类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

  • READ UNCOMMITTED（未提交读）：事务的修改，即使没有提交，对其他事物也都是可见的。会存在脏读、不可重复读、幻读的问题。
  • READ COMMITTED（提交读）：一个事务从开始直到提交之前，所做的任何修改对其它事物都是不可见的。可以避免脏读问题，但可能存在不可重复读和幻读问题。
  • REPEATABLE READ（可重复读）：同一个事务中多次读取同样记录的结果是一致的。避免了脏读和不可重复读问题，但可能存在幻读问题。Repeatable Read 是 MySQL 默认隔离级别。
  • SERIALIZABLE（可串行化）：最高级别的隔离性，事务串行执行，避免了所有并发问题，可能导致大量的超时现象和锁竞争，通常不会用这个隔离级别。

# 并发问题

如果多个事务要是对缓存里的同一条数据同时进行更新或者查询，此时会涉及到脏写、脏读、不可重复读、幻读等问题。
脏写：一个事务去更新了另外一个还没提交的事务更新过的数据。
脏读：一个事务去查询了另外一个还没提交的事务更新过的数据。
不可重复读：在同一个事务中，对于同一组数据读取到的结果不一致。比如，事务 B 在事务 A 提交前读到的结果，和在事务 A 提交后读到的结果可能不同。
幻读：当某个事物在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

# 死锁

死锁是两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。
解决：为了解决这种问题，数据库系统实现了各种死锁检测和超时机制。一般会在检测到死锁后立即返回一个错误。
死锁的出现有时候是由于存储引擎导致的，而有的则是业务中真正的数据冲突，而且基本无法避免。死锁发生后只有回滚其中的一个事务才能打破死锁。所以程序设计的时候必须考虑如何处理死锁。

# MVCC

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

# MVCC 下的 SQL 操作

InnoDB 的 MVCC ，是通过在每行纪录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号（可以理解为事务的 ID ）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。在 REPEATABLE READ 隔离级别下， MVCC 具体的操作如下：
SELECT
InnoDB 会根据以下两个条件检查每行纪录：

  • InnoDB 只查找版本早于当前事务版本的数据行，即，行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
  • 行的删除版本，要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的纪录，才能作为查询结果返回。

INSERT
InnoDB 为插入的每一行保存当前系统版本号作为行版本号。

DELETE
InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE
InnoDB 为插入一行新纪录，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为行删除标识。

# 版本链

在 InnoDB 引擎表中，它的聚簇索引记录中有两个必要的隐藏列：

  • trx_id： 这个 id 用来存储的每次对某条聚簇索引记录进行修改的时候的事务 id。
  • roll_pointer：每次对哪条聚簇索引记录有修改的时候，都会把老版本写入 undo log 中。这个 roll_pointer 就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。（注意插入操作的 undo log 没有这个属性，因为它没有老版本）

# ReadView

当我们用 select 读取数据时，这一时刻的数据会有很多个版本（例如上图有四个版本），但我们并不知道读取哪个版本，这时就靠 readview 来对我们进行读取版本的限制，通过 readview 我们才知道自己能够读取哪个版本。

# Read View 几个属性
  • trx_ids: 当前系统活跃（未提交）事务版本号集合。
  • low_limit_id: 创建当前 read view 时 “当前系统最大事务版本号 + 1”。
  • up_limit_id: 创建当前 read view 时 “系统正处于活跃事务最小版本号”
  • creator_trx_id: 创建当前 read view 的事务版本号；
# mvcc 如何实现 RC 和 RR 的隔离级别
  • RC 的隔离级别下，每个快照读都会生成并获取最新的 readview。
  • RR 的隔离级别下，只有在同一个事务的第一个快照读才会创建 readview，之后的每次快照读都使用的同一个 readview，所以每次的查询结果都是一样的。

# 索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。

索引的优点：

  • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的查询速度，这也是创建索引的主要原因。
  • 在实现数据的参考完整性方面，可以加速表和表之间的连接。
  • 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

索引的缺点：

  • 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

# 索引结构

# Hash

MySQL 中，只有 Memory 存储引擎支持 Hash 索引，是 Memory 表的默认索引类型。hash 索引把数据的索引以 hash 值形式组织起来，因此检索效率非常高，可以一次定位，不像 B+Tree 索引需要进行从根节点到叶节点的多次 IO 操作。

# B+Tree

B+Tree 是 MySQL 使用最频繁的一个索引数据结构，是 Innodb 和 Myisam 存储引擎模式的索引类型。相对 Hash 索引，B+Tree 在查找单条记录的速度比不上 Hash 索引，但是更适合排序等操作。

# B-Tree & B+Tree

B+ 树叶是 m 叉搜索树，在 B 树的基础上，做了一些改进：

  • 非叶子节点不再存储数据，数据只存储在同一层的叶子节点上；
  • 叶子之间，增加了链表，获取所有节点，不再需要中序遍历；
  • 叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；- 非叶子节点存储记录的主键，用于查询加速，适合内存存储；
  • 非叶子节点，不存储实际记录，而只存储记录的 KEY 的话，那么在相同内存的情况下，B+ 树能够存储更多索引；

# MyISAM 索引

MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址，MyISAM 索引的原理图如下。这里假设表一共有三列，假设我们以 Col1 为主键，则上图是一个 MyISAM 表的主索引（Primary key）示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。

如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗 B+Tree ， data 域保存数据记录的地址。因此， MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

# InnoDB 索引

虽然 InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。

第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道， MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

下图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（ MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形。

第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说， InnoDB 的所有辅助索引都引用主键作为 data 域。下图为定义在 Col3 上的一个辅助索引。这里以英文字符的 ASCII 码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

# 创建索引准则

# 应该创建索引的列

  • 在经常需要搜索的列上，可以加快搜索的速度
  • 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
  • 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
  • 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
  • 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
  • 在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

# 不该创建索引的列

  • 在 WHERE 中使用不到的字段，不要设置索引。
  • 频繁更新的字段不适合建立索引。
  • 数据比较少的表不需要建索引。
  • 数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值。
  • 参与列计算的列不适合建索引。

# 索引失效

  • 模糊查询 like 以 % 开头；
  • 在索引字段字段上使用函数
  • 在索引字段上进行（+，-，*，/，!, !=, <>）等运算，会导致索引失效。
  • 数据类型隐式转换。
    MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
  • 联合索引没有遵循最左匹配原则。
  • 在 WHERE 子句中， OR 前后没有同时使用索引。
  • 优化器选错索引