【MySQL 面试】Java面试八股必背系列之MySQL（中篇）

索引

什么是索引？（★★）

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。

索引的优缺点？（★★★）

优点：

• 加快数据查找的速度 (提高数据检索效率，降低数据库的IO成本)

• 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度(通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗)

• 加快表与表之间连接的速度

缺点：

• 建立索引需要占用物理空间

• 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

MySQL 使用索引的原因？（★★★）

（可以从索引的优点考虑回答）

根本原因

• 索引的出现，就是为了提高数据查询的效率，就像书的目录一样。

• 对于数据库的表而言，索引其实就是它的“目录”。

扩展

• 创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

• 帮助引擎层避免排序和临时表

• 将随机 IO 变为顺序 IO，加速表和表之间的连接。

索引的作用？（★★★）

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为 B+ 树的高度一般在 2-4 层，最多只需要读取 2-4 次磁盘，查询速度大大提升。

什么情况下需要建索引？（★★★★）

1. 针对于数据量较大，且查询比较频繁的表建立索引。

2. 针对于常作为查询条件(where)、排序（order by)、分组(group by)操作的字段建立索引。

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。

8. 经常用于连接的字段建立索引，可以加快连接的速度

9. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

什么情况下不应该建索引？（★★★★）

1. where条件中用不到的字段不适合建立索引

2. 表记录较少不适合建索引

3. 经常增删改操作不适合建索引

4. 参与列计算的列不适合建索引

5. 区分度不高的字段不适合建立索引，如性别等

创建索引有哪几种方式（★★）

• 在执行CREATE TABLE时创建索

  CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name`(`name`) USING BTREE
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;

   

• 使用ALTER TABLE命令添加索引

   ALTER TABLE table_name ADD INDEX index_name (column);

   

• 使用CREATE INDEX命令创建

  CREATE INDEX index_name ON table_name (column);

   

列值为NULL时，查询是否会用到索引？（★★）

列值为NULL也是可以走索引的，计划对列进行索引，应尽量避免把它设置为可空，因为这会让 MySQL 难以优化引用了可空列的查询，同时增加了引擎的复杂度。

MySQL索引的原理，为什么要用B+树，为什么不用二叉树？（★★★★）

为什么不是一般二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

那为什么不是B树而是B+树呢？

1）B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

2）B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

索引的数据结构（★★★★）

索引的数据结构主要有 B+ 树和哈希表，对应的索引分别为 B+ 树索引和哈希索引。InnoDB 引擎的索引类型有 B+ 树索引和哈希索引，默认的索引类型为 B+ 树索引。

B+ 树索引

B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于 B+ 树数据结构来实现。

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的 key 值，将指向数据行的指针作为哈希表的 value 值。这样查找一个数据的时间复杂度就是 O(1)，一般多用于精确查找。

Hash 索引和 B+ 树索引的区别？（★★★）

• 哈希索引不支持排序，因为哈希表是无序的。

• 哈希索引不支持范围查找。

• 哈希索引不支持模糊查询及联合索引的最左前缀匹配。

• 哈希索引在等值查询上比B+树效率更高。

• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而 B+ 树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

B树和B+树的区别（★★）

• 在B树中，键和值即存放在内部节点又存放在叶子节点；在B+树中，内部节点只存键，叶子节点则同时存放键和值。

• B+树的叶子节点有一条链相连，而B树的叶子节点各自独立的。

为什么 B+ 树比 B 树更适合实现数据库索引？（★★★★）

• 由于 B+ 树的所有数据都存储在叶子结点中，而且数据是按照顺序排列的，并且使用双向链表连接，那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。但是 B 树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以 B+ 树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常 B+ 树用于数据库索引。

• B+树非叶子节点上是只存储索引 key 值不存储数据的，而B树节点中不仅存储键值，也会存储数据。这就使以页为单位的索引中可以存放更多的节点，相应的树的阶数就会更小，减少更多的 I/O 支出，数据查询的效率也会更快。

• B+ 树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

索引有什么分类？（★★★）

1、主键索引：名为 primary 的唯一非空索引，数据列不允许重复，不允许为NULL，一个表只能有一个主键。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值，一个表允许多个列创建唯一索引。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。

3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

4、全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

5、普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。

什么是最左前缀匹配原则？（★★★★）

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。如果查询条件为a = 1 and b > 2 and c = 3，那么 a、b 两个字段能用到索引，而 c 无法使用索引，因为 b 字段是范围查询，导致后面的字段无法使用索引。

什么是聚集索引？（★★★）

聚集索引也叫聚簇索引，聚集索引的表中数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

InnoDB 使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为 6 个字节，它的值会随着数据的插入自增。

聚集索引与非聚集索引的区别？（★★★）

• 索引数量：一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。

• 是否影响记录行的物理顺序：聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

  聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

• 叶子节点的存储内容：聚集索引的叶节点就是存储的数据。而非聚簇索引点仍然是索引节点，只不过有一个指针指向对应的数据块。

何时使用聚簇索引与非聚簇索引？（★★★）

非聚簇索引一定会回表查询吗？（★★★）

不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询（覆盖索引就是这么回事）。假设我们在学生表的上建立了索引，那么当进行select age from student where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

什么是覆盖索引？（★★★）

当select后的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以 MySQL 使用 b+ 树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的 extra 列会显示为using index。

覆盖索引是常用的数据查询优化技术，可以极大的提升数据库性能，有以下几个原因：

• 减少树的搜索次数，显著提升查询性能。

• 索引是按照值的顺序存储，所以对于 I/O 密集型的范围查询比随机从磁盘中读取每一行的 I/O 要少很多。

• 索引的条目远小于数据的条目，在索引树上读取会极大的减小数据库的访问量。

索引的设计原则？（★★★★）

• 索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘 I/O 较少，查询速度更快。

• 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。

• 利用最左前缀原则。

• 频繁作为查询条件的字段才去创建索引且频繁更新的字段不适合创建索引。

• 索引列不能参与计算，不能有函数操作。

• 优先考虑扩展索引，而不是新建索引，避免不必要的索引

• 在order by或者group by子句中，创建索引需要注意顺序

• 定义有外键的数据列一定要建立索引。

• 对于定义为text、image数据类型的列不要建立索引。

索引什么时候会失效？（★★★★）

导致索引失效的情况：

• 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引，索引失效。

• 对索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。原因在于查询的结果可能是多个，不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

• 查询条件中列类型是字符串，并且没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

• 索引字段上使用（!= 或者 < >，not in）时，可能会导致索引失效。

• 索引字段上使用is null， is not null，可能导致索引失效。

• 对索引进行函数或者对索引进行表达式计算，因为索引保持的是索引字段的原始值，而不是经过函数计算的值，自然就没办法走索引。

• WHERE 子句中查询条件包含or，只要有条件列不是索引列，就会进行全表扫描，导致索引失效。

• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

• MySQL估计使用全表扫描要比使用索引快,则不使用索引。

什么是前缀索引？（★★★★）

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的数据行。

建立前缀索引的方式：

// email列创建前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

如何写SQL能够有效的使用到复合索引。（★★）

复合索引，也叫组合索引，用户可以在多个列上建立索引,这种索引叫做复合索引。当我们创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

有关于复合索引，我们需要关注查询SQL条件的顺序，确保最左匹配原则有效，同时可以删除不必要的冗余索引。

什么是覆盖索引和索引下推？（★★★）

覆盖索引：

• 在某个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。

• 覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

索引下推：

• MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

应该如何给字符串字段加索引？（★★★）

• 首先如果是直接创建完整索引，这样可能会比较占用空间。

• 那么我们可以创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。

• 还可以倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。

• 另外，创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

事务

什么是事务？举例子说说你的理解。（★★★）

所谓事务就是逻辑上的一组连续操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作就是：

1. 将小明的余额减少 1000 元

2. 将小红的余额增加 1000 元。

事务会把这两个操作就可以看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。

这样就不会出现小明余额减少而小红的余额却并没有增加的情况。

什么是数据库事务？（★★）

平时，我们在谈论事务的时候，如果没有特指分布式事务，往往指的就是数据库事务。

数据库事务的作用简单来说：数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

另外，关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务都有 ACID 特性：原子性、一致性、隔离性和持久性。

事务的四大特性？（★★★★）

事务特性 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性是指事务是一个不可分割的工作单位，也是是最小的执行单位，不允许分割，要么全部提交，要么全部失败回滚。

• 一致性是指一个事务执行之前和执行之后数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态 是 语义上 的而不是语法上的，跟具体的业务有关。

  那什么是合法的数据状态呢？满足 预定的约束 的状态就叫做合法的状态。通俗一点，这状态是由你自己来定义的（比如满足现实世界中的约束）。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！

• 隔离性是指并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。与隔离级别相关，如read committed，一个事务只能读到已经提交的修改。

• 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的 ，接下来的其他操作和数据库故障不应该对其有任何影响。

  持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志 。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

事务有哪些状态呢？（★★）

事务是一个抽象的概念，它其实对应着一个或多个数据库操作，MySQL根据这些操作所执行的不同阶段把 事务 大致划分成几个状态：

• 活动的（active）

  事务对应的数据库操作正在执行过程中时，我们就说该事务处在 活动的 状态。

• 部分提交的（partially committed）

  当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并 没有刷新到磁盘时，我们就说该事务处在 部分提交的 状态。

• 失败的（failed）

  当事务处在 活动的 或者 部分提交的 状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在 失败的 状态。

• 中止的（aborted）

  如果事务执行了一部分而变为 失败的 状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了 中止的 状态。

• 提交的（committed）

  当一个处在 部分提交的 状态的事务将修改过的数据都 同步到磁盘 上之后，我们就可以说该事务处在了 提交的 状态。

MySQL中如何开启事务？（★★）

在MySQL中使用事务有两种方式，分别为 显式事务 和 隐式事务 。

显示事务

• 使用命令START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务

  START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个修饰符 ：

  ① READ ONLY ：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。 ② READ WRITE ：标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。 ③ WITH CONSISTENT SNAPSHOT ：启动一致性读。

  mysql> BEGIN;
  #或者
  mysql> START TRANSACTION;
  
  mysql> START TRANSACTION READ ONLY;
  Query OK, 0 rows affected (0.01 sec)
  mysql> update user set username="admin11" where id = 1;
  ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

   

• 一系列事务中的操作（主要是DML，不含DDL）

• 提交事务 或 中止事务（即回滚事务）

  # 提交事务。当提交事务后，对数据库的修改是永久性的。
  mysql> COMMIT;
  # 回滚事务。即撤销正在进行的所有没有提交的修改
  mysql> ROLLBACK;
  Query OK, 0 rows affected (0.01 sec)
  
  # 将事务回滚到某个保存点。
  mysql> ROLLBACK TO [SAVEPOINT]

   

隐式事务

MySQL中有一个系统变量 autocommit ：

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.03 sec)

当然，如果我们想关闭这种 自动提交 的功能，可以使用下边两种方法之一：

• 显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。

• 把系统变量 autocommit 的值设置为 OFF ，就像这样：

  SET autocommit = OFF;
  #或
  SET autocommit = 0;

隐式自动提交的SQL语句情况：

• 数据定义语言（Data definition language，缩写为：DDL）

• 隐式使用或修改MySQL数据库中的表

• 事务控制或关于锁定的语句

  • ① 当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了 另一个事务时，会隐式的提交上一个事务。

  • ② 当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会 隐式的提交 前边语 句所属的事务。

  • ③ 使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会 隐式的提交 前边语句所属的事 务。

• 加载数据的语句

• 关于MySQL复制的一些语句

• 其它的一些语句

MySQL中事务的实现原理？（★★★）

我们这里以 MySQL 的 InnoDB 引擎为例来简单说一下。

• 原子性：使用 undo log(回滚日志) 来保证事务的原子性，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。

• 持久性：使用 redo log(重做日志) 保证事务的持久性，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。

• 隔离性：通过 锁机制、MVCC 等手段来保证事务的隔离性，使事务相互隔离开。

• 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。也就是说保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

并发事务带来哪些问题?（★★★★）

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发显然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）:当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

• 不可重复读（Unrepeatable read）:指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别（★★）

不可重复读和幻读都是读取了另一条已经提交的事务，不同的是不可重复读的重点是修改，幻读的重点在于新增或者删除。不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次查询同一条查询语句（DQL）时，记录发现记录增多或减少了。

不可重复读和脏读的区别（★★）

脏读是某一事务读取了另一个事务提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

事务隔离级别有哪些？（★★★★）

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。(所有事务都可以看到其他未提交事务的执行结果)

• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。(一个事务只能看见已经提交事务所做的改变。可避免脏读的发生)

• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。(MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题)。

• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。(通过强制事务排序，使之不可能相互冲突，从而解决幻读问题)

查看隔离级别：

select @@transaction_isolation;

设置隔离级别：

set session transaction isolation level read uncommitted;

MySQL 的默认隔离级别是什么?（★★★★）

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

MySQL 在 REPEATABLE READ 隔离级别下，是可以解决幻读问题发生的，主要有下面两种情况：

• 快照读 ：这种情况下是可以解决幻读的，它是由 MVCC 机制来解决的。

• 当前读 ： 这种情况下并不保证避免幻读，需要应用使用加锁读来保证，这个加锁读使用到的机制就是 Next-Key Lock（间隙锁）。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

事务的各个隔离级别都是如何实现的？（★★★）

读未提交

采取的是读不加锁原理。

• 事务读不加锁，不阻塞其他事务的读和写

• 事务写阻塞其他事务写，但不阻塞其他事务读；

读取已提交&可重复读

读取已提交和可重复读级别利用了 ReadView 和 MVCC ，也就是每个事务只能读取它能看到的 版本（ReadView）。

• READ COMMITTED：每次读取数据前都生成一个

• ReadView REPEATABLE READ ：在第一次读取数据时生成一个ReadView

串行化

串行化的实现采用的是读写都加锁的原理。

串行化的情况下，对于同一行事务， 写会加写锁 ， 读会加读锁 。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

缓存

查询缓存（★★）

执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用

开启查询缓存：在my.cnf 加入以下配置，重启 MySQL

query_cache_type=1
query_cache_size=600000

MySQL 执行以下命令也可以开启查询缓存

set global  query_cache_type=1;
set global  query_cache_size=600000;

如上，开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。

查询缓存不命中的情况：

• （1）任何两个查询在任何字符上的不同都会导致缓存不命中。

• （2）如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果也不会被缓存。

• （3）缓存建立之后，MySQL 的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十 MB 比较合适。此外，还可以通过 sql_cache 和 sql_no_cache 来控制某个查询语句是否需要缓存：

 select sql_no_cache count(*) from usr;

AI模拟面试

本文章中全部题目已经收录到了我的AI模拟面试客户端软件中，关于这个软件看这里：使用Python开发一个AI模拟面试系统 (imyjs.cn)

微信关注

编程那点事儿