MySQL

典型回答 在Innodb中，通过MVCC解决脏读和不可重复读，通过MVCC+间隙锁解决幻读的。 **脏读的解决。**脏读指一个事务可以读取另一个事务未提交的数据，导致数据不一致。在读已提交（Read Committed）隔离级别下，事务只能读取到其他事务已经提交的数据版本。因此，如果一个事务在读取数据时，另一个事务已经修改了这些数据但尚未提交，那么读取事务将不会看到这些未提交的更改。 脏读的解决依赖了Read View，Read View会来告诉我们本次事务应该看到哪个快照，不应该看到哪个快照。 ✅什么是ReadView，什么样的ReadView可见？ 简单点说就是：当事务在“读已提交”隔离级别下执行读取操作时， InnoDB获取当前最新的全局事务ID，这个ID表示在当前时刻所有已提交事务的最新状态。InnoDB会检查每个数据行的版本，如果该版本是由一个小于或等于当前事务ID的事务修改的，并且该事务已提交，则这个版本是可见的。这保证了事务只能看到在它开始之前已经提交的数据版本。 **不可重读的解决。**不可重复读指一个事务读取同一行数据两次，但是在两次读取之间另一个事务修改了该行数据，导致两次读取的结果不同。InnoDB 通过使用 MVCC 来解决不可重复读的问题。在RR这种隔离级别下，当我们使用快照读进行数据读取的时候，只会在第一次读取的时候生成一个Read View，后续的所有快照读都是用的同一个快照，所以就不会发生不可重复读的问题了。 **幻读的解决。**InnoDB的RR级别中，基于MVCC+间隙锁，是在某种程度上是可以避免幻读的发生的，但是没有办法完全避免，当一个事务中发生当前读的时候，会导致幻读的发生。详情参考： ✅Innodb的RR到底有没有解决幻读？ 总结下： 读现象 解决方式 脏读 每次查询生成新 ReadView，读取已提交的最新版本。 不可重复读 事务开始时生成 ReadView，后续所有读操作基于此视图。 幻读 MVCC+间隙锁可解决一部分，另外就是需要强制加锁，所有操作串行执行。

典型回答 我们都知道，在InnoDB存储引擎中，所有的修改操作都必须在事务中进行的， 那么，而**SELECT * **这种普通的读取操作其实也会在事务的上下文中执行，即使没有明确的开启事务语句，InnoDB存储引擎也会为查询自动开启一个隐式事务。 因此，InnoDB的所有操作都可以说是在事务的上下文中执行的，包括读取操作和修改操作。 虽然查询语句也会在事务的上下文中执行，但是由于没有进行任何修改操作，因此事务不会持有任何锁，并且在查询结束后立即提交。这种隐式事务通常被称为自动提交事务（autocommit）。

典型回答 虽然RR的隔离级别可以在一定程度上避免脏读、不可重复读和幻读等问题，但是，对于很多大型的互联网来说，会愿意将数据库的默认隔离级别调整成并发度更高的RC级别，从而，提升并发度并且降低发生死锁的概率。 扩展知识 RR 和 RC 的区别 我们需要先来弄清楚一下 RR 和 RC 的区别，分析下各自的优缺点。 一致性读 一致性读，又称为快照读。快照即当前行数据之前的历史版本。快照读就是使用快照信息显示基于某个时间点的查询结果，而不考虑与此同时运行的其他事务所执行的更改。 在MySQL 中，只有READ COMMITTED 和 REPEATABLE READ这两种事务隔离级别才会使用一致性读。 在 RR 中，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照。 在 RC 中，每次读取都会重新生成一个快照，总是读取行的最新版本。 在数据库的 RC 这种隔离级别中，还支持"半一致读" ，一条update语句，如果 where 条件匹配到的记录已经加锁，那么InnoDB会返回记录最近提交的版本，由MySQL上层判断此是否需要真的加锁。 锁机制 数据库的锁，在不同的事务隔离级别下，是采用了不同的机制的。在 MySQL 中，有三种类型的锁，分别是Record Lock、Gap Lock和 Next-Key Lock。 Record Lock表示记录锁，锁的是索引记录。 Gap Lock是间隙锁，锁的是索引记录之间的间隙。 Next-Key Lock是Record Lock和Gap Lock的组合，同时锁索引记录和间隙。他的范围是左开右闭的。 在 RC 中，只会对索引增加Record Lock，不会添加Gap Lock和Next-Key Lock。 在 RR 中，为了解决幻读的问题，在支持Record Lock的同时，还支持Gap Lock和Next-Key Lock； 主从同步 在数据主从同步时，不同格式的 binlog 也对事务隔离级别有要求。 MySQL的binlog主要支持三种格式，分别是statement、row以及mixed，但是，RC 隔离级别只支持row格式的binlog。如果指定了mixed作为 binlog 格式，那么如果使用RC，服务器会自动使用基于row 格式的日志记录。 ...

所谓快照读，就是读取的是快照数据，即快照生成的那一刻的数据，像我们**常用的普通的SELECT语句在不加锁情况下就是快照读。**如： 1 SELECT * FROM xx_table WHERE ... 和快照读相对应的另外一个概念叫做当前读，当前读就是读取最新数据，所以**，加锁的 SELECT，或者对数据进行增删改都会进行当前读**，比如： 1 2 3 4 5 6 7 8 9 SELECT * FROM xx_table LOCK IN SHARE MODE; SELECT * FROM xx_table FOR UPDATE; INSERT INTO xx_table ... DELETE FROM xx_table ... UPDATE xx_table ... 在MySQL 中，只有READ COMMITTED 和 REPEATABLE READ这两种事务隔离级别才会使用快照读。 在 RR 中，快照会在事务开始时生成，只有在本事务中对数据进行更改才会更新快照。 在 RC 中，每次读取都会重新生成一个快照，总是读取行的最新版本。

典型回答 MySQL的Innodb引擎中，支持多种锁级别，包括了行级锁和表级锁。当多个事务想要访问一个共享资源的时候，如果每个事务都直接请求获取锁，那么就可能会导致互相阻塞，甚至导致死锁。 举个例子： 事务A对表Table1中的一行加上了行级锁，这时候这行记录就不能写了。事务B申请对Table1增加了表级锁，如果他申请成功了，那么他就可以修改表中的任意一行记录。这就发生了冲突。 那么，想要解决这个问题，就需要让事务B在对Table1增加表级锁的时候，先判断一下是不是有事务增加过行级锁。但是，事务B总不能遍历表中数据逐条判断是否有加锁吧？ 所以，为了解决这个问题，MySQL引入了意向锁机制。所以，意向锁是数据库管理系统中用于实现锁协议的一种锁机制，旨在处理不同锁粒度（如行锁和表锁）之间的并发性问题。（相同锁粒度（如多个行级锁）之间的并发性问题通过行级互斥锁解决。） 注意： 1、意向锁并不是直接锁定资源，而是为了通知其他事务，以防止它们在资源上设置不兼容的锁。 2、意向锁并不是直接由用户请求的，而是由 MySQL 管理的。 **当一个事务请求获取一个行级锁或表级锁时，MySQL会自动获取相应的表的意向锁。**这样，其他事务请求获取表锁时，就可以先基于这个意向锁来发现是否有人加过锁，并根据该锁的类型（意向共享锁/意向排他锁）来判断自己是否可以获取锁。这样可以在不阻塞其他事务的情况下，为当前事务锁定资源。 意向锁有两种类型：意向共享锁和意向排他锁。**** 意向共享锁： 表示事务打算在资源上设置共享锁**（读锁）**。这通常用于表示事务计划读取资源，并不希望在读取时有其他事务设置排它锁。 意向排它锁： 表示事务打算在资源上设置排它锁**（写锁）**。这表示事务计划修改资源，并不希望有其他事务同时设置共享或排它锁。 意向锁是一个表级锁，并且他会在触发意向锁的事务提交或者回滚后释放。

典型回答 共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。 如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据。 1 SELECT ... LOCK IN SHARE MODE; 在查询语句后面增加LOCK IN SHARE MODE，MySQL会对查询结果中的每行都加共享锁，被加了共享锁的记录还可以被其他事务成功申请共享锁，但是不能被申请排他锁。 **排他锁又称写锁，**如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的锁。获得排他锁的事务既能读数据，又能修改数据。 1 SELECT ... FOR UPDATE; 在查询语句后面增加FOR UPDATE，MySQL会对查询命中的每条记录都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。 总结： 当这一行数据获取了排他锁，那么其他事务就不能在对这一行数据添加共享锁或者排他锁。 当这一行数据获取了共享锁，那么其他事务依然可以对这一行数据添加共享锁，但不能添加排他锁

典型回答 InnoDB存储引擎支持两种常见的索引数据结构：B+树索引、Hash索引，其中B+树索引是目前关系型数据库系统中最常见、最有效的索引。 数据库中的B+树索引分为聚集索引和非聚集索引。聚集索引就是按照每张表的主键构造一个B+树，B+树的叶子节点中记录着表中一行记录的所有值。只要找到这个叶子节点也就得到了这条记录的所有值。非聚簇索引的叶节点中不包含行记录的所有值。只包含索引值和主键的值。 根据索引的唯一性，又可以把索引分为唯一索引和普通索引。唯一索引要求索引的列值必须唯一，不能重复。 另外，在MySQL 5.6中还增加了全文索引，5.7版本之后通过使用ngram插件开始支持中文。 扩展知识 B+树索引和Hash索引 InnoDB为什么使用B+树实现索引？ 聚簇索引和非聚簇索引 什么是聚簇索引和非聚簇索引？ 唯一性索引 MySQL是如何保证唯一性索引的唯一性的？ ✅唯一索引和主键索引的区别？

典型回答 MySQL通常使用B+树作为唯一性索引的数据结构。这种结构允许高效的数据检索和插入操作。当插入一个新行或更新现有行的索引列时，MySQL首先在索引中检查是否已经存在相同的键值。如果发现索引列的新值已经存在于唯一性索引中，MySQL将阻止该插入或更新操作，并返回一个错误。 在支持事务的存储引擎（如InnoDB）中，事务机制和锁定协议帮助维护索引的唯一性。当一个事务正在修改索引列时，其他事务对相同键值的修改会被适当地阻塞，直到第一个事务提交或回滚，确保了数据的一致性和唯一性。 并且，在实际写入数据到磁盘之前，MySQL也会执行约束检查，确保不会违反唯一性约束。 扩展知识 唯一索引允许NULL值吗？ 唯一索引在 MySQL 中可以允许 NULL 值的，但是这些NULL的表现是未知的，未知就是他们不相等，但是也不能说他们不等。 并且，InnoDB存储引擎在MySQL中支持在唯一索引中有多个NULL值。这是因为在MySQL中，NULL被认为是“未知”的，每个NULL值都被视为互不相同。因此，即使一个列被定义了唯一索引，它也可以包含多个NULL值。 唯一性索引查询更快吗？ 在数据库中，通过唯一性索引来创建唯一性约束，可以保证表中指定列的值唯一，避免数据重复和错误插入。 唯一性索引查询通常会比非唯一性索引查询更快（有差异，但是不大。），因为唯一性索引能够快速定位到唯一的记录，而非唯一性索引则需要扫描整个索引，从找到匹配的数据之后，要继续执行匹配，直到找到不匹配的数据之后。 在应用中，如果我们能够设计合适的唯一性索引，也可以有效地提升查询性能和数据质量。 唯一性索引有什么缺点吗？ 没有银弹，所以一定有缺点。 首先，唯一性索引需要保证索引列的唯一性，因此在插入数据时需要检查是否存在相同的索引值，这会对插入性能产生一定的影响。 如果需要更新唯一性索引列的值，需要先删除旧记录，再插入新记录，这会对更新操作的成本产生影响。

典型回答 在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。 在存储的数据方面，主键（聚簇）索引的B+树的叶子节点直接就是我们要查询的整行数据了。而非主键（非聚簇）索引的叶子节点是主键的值。 那么，当我们根据非聚簇索引查询的时候，会先通过非聚簇索引查到主键的值，之后，还需要再通过主键的值再进行一次查询才能得到我们要查询的数据。而这个过程就叫做回表。 所以，在InnoDB 中，使用主键查询的时候，是效率更高的， 因为这个过程不需要回表。另外，依赖覆盖索引、索引下推等技术，我们也可以通过优化索引结构以及SQL语句减少回表的次数。 假如有一个SQL 查询语句，只用到非聚簇索引而不需要用到聚簇索引，那么就可能是发生了索引覆盖或者索引下推。（这也是个单独的面试题） 扩展知识 覆盖索引&索引下推 ✅什么是索引覆盖、索引下推？

典型回答 覆盖索引 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。 当我们通过SQL语句： select key2 from covering_index_sample where key1 = ‘keytest’; 的时候，就可以通过覆盖索引查询，无需回表。 但是以下SQL，因为不符合最左前缀匹配，虽然是索引覆盖，但是也无法用到索引（会扫描索引树）： select key1 from covering_index_sample where key2 = ‘keytest’; 但是如果SQL中查询的信息不包含在联合索引中，那么就不会走索引覆盖。如： select key2,key3 from covering_index_sample where key1 = ‘keytest’; 索引下推 索引下推是 MySQL 5.6引入了一种优化技术，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。 官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引 SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’; 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’来判断数据是否符合条件。 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’来判断索引是否符合条件。 如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。 ...