mysql基础问题总结（锁、执行流程、事务、日志、mvcc）

用户3291044225041
599 阅读32分钟

  1. 在MySQL中，默认情况下，每个SQL语句都是自成一个事务？

在MySQL中，默认情况下，每个SQL语句都是自成一个事务的，即每个语句都会立即执行并提交（commit），因此每个语句都会产生一个事务。这种自动提交模式是MySQL的默认模式，也称为自动提交模式（Autocommit Mode）。 在自动提交模式下，如果要开启一个事务，需要使用START TRANSACTION或BEGIN语句来显式地开启一个新的事务。如果执行多条SQL语句，并且这些语句需要保持在同一个事务中执行，需要在它们之间加上START TRANSACTION或BEGIN语句，然后在所有语句都执行完毕后执行COMMIT或ROLLBACK语句。 要注意的是，对于某些特定的SQL操作，如DDL语句（ALTER、CREATE、DROP等）和查询语句（SELECT），虽然它们本身并不需要修改任何数据，但它们也会开启一个事务。这些语句执行完成后，事务会自动提交。 总之，除非显式地使用START TRANSACTION或BEGIN语句开启事务，否则每个SQL语句都是自成一个事务的。

  1. 在MYSQL中，使用innodb引擎时，事务和session的关系？

事务也是基于“session”来实现的。在MySQL中，当一个客户端开始一个事务时，该事务会在当前“session”中占用同一连接，直到该事务提交或回滚。在事务期间，这个“session”内的所有SQL语句都要参与到该事务中，这些语句是在同一个事务内执行的，要么全部执行成功，要么全部执行失败。在事务结束后，该“session”中的连接将被释放，以允许其他客户端使用该连接。 此外，MySQL的事务实现采用了ACID（原子性、一致性、隔离性和持久性）的属性，以确保事务能够正确地处理并且不会破坏数据的完整性和一致性。在MySQL中，你可以使用BEGIN或START TRANSACTION语句开始一个事务，并使用COMMIT或ROLLBACK语句提交或回滚该事务。该事务期间所有的操作都会在这一“session”内执行。

  1. 我基于springboot-mysql的依赖下，不使用事务的前提下多次去执行查询、修改mysql，那么 对于每一次的查询或修改操作都是单独的session?且都会被mysql作为不同的客户端吗？

如果你在Spring Boot中使用连接池来管理MySQL连接，并且没有使用事务，那么每次查询或修改操作都会在单独的连接上执行，这些连接是连接池中的可用连接之一。 每个连接都有自己的连接属性和上下文，所以每次查询或修改操作都会在一个新的连接上执行。连接池会在启动时创建一些连接并将其放入池中，当你需要访问MySQL数据库时，连接池会从池中取出一个连接并将其分配给你，以供你执行查询或修改操作。然后，当这次操作完成时，连接会被归还到连接池中以供重用。 在MySQL中，服务器为每个连接创建一个“session”，所以每个连接都会作为一个不同的MySQL客户端，但是你不能直接控制每个连接所处的MySQL客户端的标识，因为这是MySQL服务器的内部细节。然而，你可以通过查询MySQL的连接列表来查看当前客户端连接的状态和信息。

  1. 在MySQL中，服务器为每个连接创建一个“session”但是连接是可以复用的，而“session”却不能？

MySQL服务器为每个客户端连接创建一个“session”，而连接可以在该“session”上执行多个查询或修改操作，但是该“session”只能与一个客户端连接相关联。也就是说，如果一个连接被用于执行查询或修改操作，那么这个连接上的“session”会一直存在，直到连接被关闭或者重置。 当连接被释放并重复使用时，之前的“session”会被丢弃，同时创建一个新的“session”来与新的客户端连接相关联。这就是为什么连接可以被复用，但是“session”不能复用的原因。 需要注意的是，每个“session”都是独立的，它们之间不会共享状态或者上下文信息。所以如果你想要在多个“session”之间共享数据或者状态，你需要使用一些特定的技术和工具，例如分布式缓存、消息队列等等。

  1. 我直接使用了多个INSERT语句写在mapper.xml文件中，这样会被认为是一个事务吗？

在mybatis中，如果你在mapper.xml中使用多个INSERT语句，mybatis并不会默认将它们作为一个事务来处理，每个INSERT语句都会被当做单独的sql语句来执行，也就是说，每个INSERT语句都会获取并释放一次数据库连接，也就是每个INSERT操作都是一个独立的事务

  1. 在使用innodb引擎的前提下， 批量插入数据到mysql中，会锁表吗？

当使用 InnoDB 存储引擎进行批量插入数据时，每个 insert 语句将被视为单独的事务，并会获取一个行级排他锁，以确保数据的完整性和一致性。而不是对整个表进行排它锁。在批量插入数据时，如果进行了足够的调整和优化，理论上可以避免锁定整个表。

  1. 批量插入时，尽量保持每个INSERT语句插入的数据量较小，以便减少锁竞争的可能性。
  2. 使用事务控制，将多个INSERT语句包含在一个事务中，从而减少锁定表的时间。
  3. 对插入数据的表进行优化，如使用分区表，从而降低锁定表的风险。
  4. 在插入较大数据集时，使用LOAD DATA INFILE命令批量插入，这比使用INSERT语句更快，并且可以减少锁定表的时间。

  1. 使用事务控制，将多个INSERT语句包含在一个事务中，从而减少锁定表的时间。请解释

在批量插入操作中，如果多个 INSERT 语句之间存在对同一数据行的操作，将这些操作放在同一个事务中，可以避免等待上一个 INSERT 操作完成的情况，提高数据库的执行效率，同时避免了对同一个数据行的重复加锁操作，进而可以减少锁定表的时间，提高并发性能。

具体而言，在一个事务中，对于多个需要插入的数据行，每个数据行只会被加一次行锁，而不是对每个 INSERT 语句都加锁。这样可以将锁的数量和锁定时间减少到最小，进而可以减少锁定表的时间，提高并发性能。

  1. mysql支持那些引擎？

MySQL支持多种存储引擎，以下是常见的引擎：

  1. InnoDB：MySQL 5.5.5及以上版本默认的事务型存储引擎，支持事务、行级锁、外键等特性，适合于高并发的OLTP应用。
  2. MyISAM：MySQL较早版本的默认存储引擎，不支持事务和行级锁，但是它有较高的插入和查询速度，适合于只读和少写的数据集。
  3. Memory：将数据存储在内存中的存储引擎，数据易失（即如果MySQL进程退出，数据将丢失），但是性能非常高，适合于缓存表、临时表等。
  4. CSV：将数据存储在CSV文件中的存储引擎，适用于需要处理大量数据的环境。
  5. NDB Cluster：一种分布式数据库存储引擎，能够分布式处理数据，并且具有高可用性和伸缩性。

此外，MySQL还支持其他存储引擎，如Merge、Archive、FederatedX等。

  1. MySQL基于innodb引擎在各个版本中有哪些优化？请基于：5.5-8.0分析

好的，下面是基于InnoDB存储引擎在MySQL 5.5-8.0版本中的优化情况：

  1. MySQL 5.5版本引入的InnoDB优化
  • 锁机制优化：引入了自适应哈希索引锁、功能锁和元数据锁等锁机制，提高了并发性能。
  • 事务处理优化：引入了多版本并发控制（MVCC）技术，支持非阻塞读取和一致性读取，提高了事务性能和可靠性。
  • 内存缓存优化：引入了InnoDB缓冲池拆分技术，将缓冲池划分成多个区域，提高了缓存效率。
  1. MySQL 5.6版本引入的InnoDB优化
  • 索引优化：引入了全文索引和空间索引等新的索引算法和数据结构，提高了索引查询的性能和准确性。
  • 查询优化：引入了分区表查询优化、优化器选择、并行查询和子查询优化等查询优化功能，提高了查询性能和响应速度。
  • 可靠性优化：引入了崩溃安全、错误日志记录、存储格式验证和数据恢复等可靠性优化，提高了系统的可靠性和稳定性。
  1. MySQL 5.7版本引入的InnoDB优化
  • 锁机制优化：引入了半同步复制和分布式事务提交等锁机制，提高了并发性能和可靠性。
  • 条件索引优化：优化了InnoDB条件索引的处理能力，提高了查询性能。
  • JSON支持：引入了对JSON数据类型的支持，提供了一种更灵活的数据处理方式。
  1. MySQL 8.0版本引入的InnoDB优化
  • 锁机制优化：引入了基于原子变量的自旋锁机制，提高了并发性能。
  • 分区表优化：优化了分区表的存储结构和管理方式，提高了分区表查询的性能和可靠性。
  • 事务处理优化：引入了多版本缓存技术，提高了事务处理性能和可靠性。
  • 其他优化：包括全文索引优化、空间数据索引支持、在线DDL优化等。

需要注意的是，上述优化仅是各个版本中新增的或者改进的优化，在各个版本中还可能有其它的优化或者改进。同时，每个版本的优化都具有其特定的优化方案，具体的优化策略需要根据具体版本进行选择和实施。

  1. mysql中给得重做日志（redo log）有什么作用？

MySQL中的重做日志（redo log）是用于数据恢复的一种日志记录机制。它记录了对MySQL数据文件进行的所有修改操作，包括插入、更新和删除等操作。

具体来说，当对MySQL数据库进行修改操作时，MySQL将这些修改操作记录到重做日志中，而并不是直接将修改操作写入磁盘。这样做的好处是可以提高数据库的性能，因为写入磁盘是一项相对比较耗时的操作。同时，通过将修改操作记录到重做日志中，MySQL还能够保证数据的一致性。

在MySQL数据库意外崩溃或者重启时，重做日志就发挥了作用。MySQL会从重做日志中读取之前未写入磁盘的操作，并将其重新应用到数据文件中，以保证数据的完整性和一致性。因此，重做日志是MySQL数据恢复的关键所在，也是非常重要的一项数据库技术。

  1. 举一个redolog具体的示例

假设有一个MySQL数据库表sales，其中有以下记录：

order_idcustomerproductquantityprice
1AliceiPhone21000
2BobiPad1800

现在，我们执行了一条UPDATE语句：

UPDATE sales SET quantity = 3 WHERE order_id = 1;

该语句将order_id为1的记录的quantity字段值从2更新为3。

如果此时MySQL崩溃，那么order_id为1的记录更新操作将无法写入磁盘。但是，该操作已经记录到了重做日志中。

当MySQL重新启动时，它会检查重做日志中未写入磁盘的操作，并将它们重新应用到数据文件中。在本示例中，MySQL将会检查重做日志中的更新操作，并将其重新应用到sales表中。这样，sales表的记录就变成了：

order_idcustomerproductquantityprice
1AliceiPhone31000
2BobiPad1800

这就是 MySQL 利用重做日志来恢复数据的一个具体示例。

  1. redolog也不能100%保证数据的完整性和安全性?

尽管MySQL重做日志（redo log）可以在数据恢复时起到重要的作用，但是它并不能完全保证数据的安全性和完整性。即使重做日志记录了所有的修改操作，但是如果这些修改操作没有及时写入磁盘，那么在MySQL崩溃之前这些操作可能会丢失。

而且在重做日志写入磁盘的过程中，也存在一定的风险。例如，如果MySQL在写入重做日志的过程中出现故障，就有可能导致重做日志的损坏或丢失，进而影响到数据的恢复。

因此，在生产环境中，除了使用重做日志之外，还需要采用其他措施来确保数据的安全性和完整性，例如使用备份和恢复技术、多节点复制等。

  1. 如何从redolog中分别那些是MySQL会从重做日志中读取之前未写入磁盘的操作？

具体来说，当MySQL启动时，它会检查重做日志中未写入磁盘的最后一个操作，并将其应用到数据文件中。如果检查到日志中还有未写入磁盘的操作，那么MySQL就会继续应用这些操作，直到所有未写入磁盘的操作都被应用到数据文件中为止。

在重做日志中，每个操作都包括一个事件头和事件内容。事件头包括时间戳、操作类型、事务ID等信息，而事件内容则包括对数据文件的操作，例如插入、更新和删除等。MySQL会根据事件头的信息来判断该操作是否已经被写入磁盘，以及该操作是否需要被重新应用到数据文件中。

需要注意的是，重做日志只记录了数据修改操作，不包括查询操作。因此，如果MySQL崩溃或者出现其他故障时，我们需要确保查询操作也能够被恢复，例如使用备份和恢复技术。

  1. 在mysql中回滚日志有哪些作用？

MySQL中的回滚日志（rollback logs）主要用于支持事务的回滚操作。当在MySQL中开启一个事务时，所有的修改操作都会被保存到回滚日志中。如果事务被回滚，则可以通过回滚日志来撤销这些修改操作，将数据库恢复到事务开始之前的状态。

  1. 举一个例子来说明回滚日志的作用?

假设在MySQL数据库中有一张名为student的表，其中包含了学生的信息。我们需要将所有学生的成绩都增加10分，现在需要使用事务来实现该操作。

  1. 开启事务
START TRANSACTION;
  1. 执行修改操作
UPDATE student SET grade = grade + 10;
  1. 提交事务
COMMIT;

在执行以上操作的过程中，MySQL会将所有的修改操作记录到回滚日志中。如果在事务提交之前出现了错误，导致事务需要回滚，则可以使用回滚日志来撤销对student表的修改操作，恢复到执行事务之前的状态。

例如，如果在执行上述操作时，出现了磁盘空间不足的错误，导致事务回滚，使用回滚日志可以将student表的成绩恢复到事务开始之前的值，从而避免了数据的不一致性。

  1. undolog日志中到底存了什么数据？

Undo Log中存储了数据修改前的原始值，用于在事务回滚时将数据恢复到之前的状态，同时也存储了修改操作相关的元数据，如事务ID和时间戳等，用于支持多版本并发控制（MVCC）机制。具体来说，Undo Log中存储的数据包括：

  1. 数据修改前的原始值；
  2. 用于撤销修改的操作类型（如插入、更新或删除）；
  3. 事务ID；
  4. 数据所在的页号和偏移量；
  5. 版本号；
  6. 修改的时间戳。

在MySQL中，Undo Log主要用于回滚事务以及恢复被Kill掉的事务。当一个事务需要回滚时，MySQL会从Undo Log中读取相应的数据，根据其中保存的原始值将数据还原到之前的状态。当一个事务被Kill掉时，MySQL也会使用Undo Log将事务对数据库的修改撤销，从而保证数据的一致性。

  1. innodb的 crash-safe是指什么？

InnoDB的crash-safe特性是指数据库可以在系统崩溃后，恢复到一致性状态而不会丢失任何数据。这个特性包括两个方面的内容：

  1. 同步刷新（Synchronous Flushing）：：数据修改后，首先将数据写入redo log中的内存缓冲区，然后将数据更新到内存中的buffer pool中。同步刷新操作指的就是在将数据更新到磁盘上之前，需要将已经写入redo log缓冲区的数据先刷入磁盘中，然后再将新的数据写入磁盘中。这里的刷入磁盘和写入磁盘都是同步的操作，需要等待磁盘I/O操作完成之后才能继续执行其他操作。
  2. 重做日志（Redo Log）：InnoDB会将所有的修改操作都记录到重做日志中，包括数据修改前和修改后的状态。在系统崩溃后，InnoDB可以使用重做日志重新执行所有的修改操作，从而将数据恢复到崩溃前的状态。

InnoDB的crash-safe特性可以通过两种方式实现：

  1. 数据库自动完成：在默认情况下，InnoDB会自动开启同步刷新机制和重做日志，因此数据库可以自动实现crash-safe特性。这种方式可以简化应用程序的开发和维护，但可能会对性能产生一定的影响。
  2. 手动控制：开发人员可以通过手动控制重做日志的大小和位置等信息，来控制数据库的crash-safe特性。这种方式需要开发人员承担更多的管理和维护工作，但可以更加灵活地控制数据库的特性和性能。

总之，InnoDB的crash-safe特性保证了数据库的可靠性和完整性，提高了数据的安全性和可用性。

  1. 为什么innodb不选择直接将数据更新到磁盘，而是先写入缓冲区并且引入一系列操作呢？

如果一条sql进来就直接写入磁盘，那这种写入就数据随机读写磁盘，效率是非常低的，而通过缓冲区一次读取一页数据，接下来的sql操作的行如果也在缓冲区中，那就先修改缓冲区，而修改缓冲区的速度是非常非常快的，然后到了某个时机在将所有的sql进行写入磁盘，这无形减少了磁盘io。当然，在写入之前肯定会在缓冲区里对数据进行了处理。而引入一系列操作是为了保证数据库的高可用性，undo日志用于事务的回滚以及mvcc机制，这是innodb的特性。而redo日志则保证了出现断电等极端现象重启数据库时数据能够恢复等

  1. 使用mysql的innodb存储引擎中一条sql写入的详细流程

  1. 在mysql中binlog有什么作用？

MySQL的binlog是用来记录数据库中的所有数据更新操作的日志文件，它是MySQL复制的核心组件。它可以实现以下功能：

  1. 数据恢复：通过binlog可以对数据库进行数据恢复，尽管在数据库意外宕机时丢失数据不能被完全避免，但通过binlog可以至少恢复到某一点之前的状态。
  2. 数据备份：通过binlog可以备份MySQL的数据，常用来作为恢复点和数据同步。
  3. 数据同步：通过binlog可以将一个MySQL实例上的数据同步到其他的MySQL实例上，可以用于实现主从复制、读写分离等高可用、高并发的架构。
  4. 数据安全：通过binlog可以对数据库进行监控，可以查看哪些数据被修改，谁修改了数据等，有助于保护数据的安全性。

总之，binlog是MySQL中非常重要的组件，可以提供数据恢复、备份、同步和安全等方面的支持，对于保证MySQL数据库的可靠性和可用性具有极其重要的作用。

  1. 你知道mysql中的mvcc机制吗？它主要解决了那些问题？

MVCC（Multi-Version Concurrency Control）机制是MySQL中保证高并发性、实现事务的一种重要技术。MySQL中的InnoDB存储引擎实现了MVCC机制。

MVCC机制主要解决了以下问题：

  1. 读写并发问题：在读取过程中，防止读取到正在被写入的数据，同时在写入过程中，防止写入覆盖已有的数据。
  2. 并发事务的一致性问题：保证同时多个事务执行时，各自的操作都能正确地被执行。
  3. 防止死锁问题：当多个事务同时互相等待对方释放锁时，可能会导致死锁，MVCC机制通过版本控制来避免这种情况的发生。

MVCC机制通过为每个事务版本化数据，将数据的状态隔离开来，不同的事务可以并发地读取和修改这些版本数据。每个事务读取的数据都是在读取时刻的一个静态版本，它不受其他事务的修改影响，也不会影响到其他事务的读取。同时，每个事务的修改都会另外生成一个新版本，其他事务仍然可以读取到旧版本，从而保证了并发读写的正确性和一致性。

  1. 如果我没有显示什么事务，执行批量sql时是在一个事务中进行吗？还是会为每一条执行单个的sql建立事务？

如果没有显示开启事务，则MyBatis默认是自动提交事务的，每一条SQL语句都在一个单独的事务中执行，也就是说，在批量更新时，每一条SQL语句都会建立一个单独的事务，这样会导致性能问题。

  1. 在使用innodb引擎的前提下，使用批量更新操作时，mysql应该会加锁锁定数据行，我想问的是即便批量更新的数据的主键不是连续的，也只会锁定数据行吗？

在使用 InnoDB 引擎的前提下，使用批量更新操作时，MySQL 会根据更新操作的条件加锁锁定数据行。如果更新操作使用的是索引条件，MySQL 会对符合条件的索引记录加锁；如果更新操作使用的是非索引条件，MySQL 会扫描整个表并对符合条件的记录加锁。

即便批量更新的数据的主键不是连续的，MySQL 也只会锁定符合更新条件的数据行。批量更新操作会获取锁并更新符合条件的记录，其他事务在该行上的读写操作会被阻塞，保证数据的完整性和一致性。

总之，MySQL 的锁机制是根据条件进行加锁的，而不是根据主键是否连续进行加锁的。

  1. 对于同一个事务中的多个查询或修改操作，系统会共享已经加的行锁，是共享锁还是排他锁？

在一个事务中多次操作修改 MySQL 中的某一个数据行，使用的是排他锁。

当一个事务开始修改一个数据行时，会自动获取该行的排他锁，其他事务在该行上的读写操作都会被阻塞，直到该事务提交或回滚后才会释放锁。如果该事务在修改该行后还需要修改该行的其他字段，则会继续持有该行的排他锁。

因此，在同一个事务中，每次修改都会获取同样的排他锁，而不是共享锁。这保证了所有操作对该行的同步性和一致性。

  1. mysql默认是会为每一个sql分配事务的，当不显示声明事务时，会为批量操作中的每一个sql加上事务，这样相比于把批量操作放在一个事务中，减少了事务中包含共享行锁的数量，一定程度减少了死锁的风险？

MySQL 默认会为每个 SQL 语句分配一个事务，即每个 SQL 语句都会被视为单独的事务进行处理。当在批量操作中没有显式声明事务时，每个 SQL 语句都会被分配一个单独的事务，这样做可以减少在事务中包含共享行锁的数量，一定程度上减少了死锁的风险。

但是，将批量操作放在一个事务中可以更好地控制事务的边界和锁的范围，一次性提交多个 SQL 语句，减少了提交次数，从而提高了性能。而且，将多个 SQL 语句放在同一个事务中，可以保证事务的原子性，一旦其中一个 SQL 语句执行失败，整个事务将回滚，保证了数据的一致性和完整性。

因此，在实际应用中需要根据具体情况来决定是否将批量操作放在一个事务中。如果事务中需要共享行锁的数量很少，可以将批量操作分开提交，避免死锁的风险。如果需要保证事务的原子性和数据的一致性，可以将批量操作放在一个事务中进行处理。

  1. 我基于springboot写了一个方法，在方法中会执行新增、修改数据库操作，这方法有显示申明事务，那么我想问在这个事务中操作两次mysql使用得是同一个mysql客户端连接吗？

在一个带有事务的方法中，同一个线程中执行的数据库操作都会使用同一个数据库连接。也就是说，如果在同一个事务中执行两次MySQL操作，那么这两次操作会使用同一个MySQL客户端连接。

Spring事务管理器默认采用ThreadLocal存储事务上下文，即同一个线程中的所有数据库操作会共享同一个数据库连接。当进入带有@Transactional注解的方法时，Spring会为该方法开启一个事务，并在当前线程的ThreadLocal中存储一个与该事务相关的事务上下文，包括数据库连接对象等。

在此事务上下文中，所有的数据库操作都会使用该事务对应的连接对象，从而保证了事务中多个数据库操作使用的是同一个数据库连接。

  1. 如果没有申明事务，在方法中会执行新增、修改数据库操作使用得是不同的连接吗？

如果在一个方法中执行多个数据库操作，但是没有使用Spring事务管理器来管理事务，那么这些数据库操作之间会使用不同的数据库连接，即使是在同一个线程中执行。

使用Spring事务管理器管理事务可以解决这个问题，它会在同一个事务上下文中共享连接对象，从而保证了多个数据库操作之间使用的是同一个数据库连接。如果需要使用相同的数据库连接，可以在同一个事务上下文中执行多个数据库操作，即使用@Transactional注解来开启事务。

  1. 事务的传播机制

事务的传播行为就是在两个都启用了事务的方法进行嵌套调用时，外层事务和内层事务的执行动作，Spring支持七种传播方式，他们的效果如下：

REQUIRED：如果有外层事务，则加入这个事务，否则开启一个新的事务，这个是默认的传播机制

SUPPORTS：如果有外层事务，则加入这个事务，否则就以非事务方式执行

MANDATORY：如果有外层事务，则加入这个事务，否则抛出异常

REQUIRES_NEW：开启一个独立的新事务执行

NOT_SUPPORTED：方法不应该以事务方法执行，如果有外层事务，就以非事务方式执行

NEVER：方法不应该以事务方法执行，如果有外层事务，就抛出异常

NESTED：开启一个外层事务的子事务执行

  1. mysql中的数据在磁盘索引的存储数据结构B树和B+树分析

B树（Balance Tree）即为平衡树的意思，下图即是一颗B树。

图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。 – 【图中的每个节点称为页，页就是我们上面说的磁盘块（空间大小：16k），在mysql中数据读取的基本单位都是页，】所以我们这里叫做页更符合mysql中索引的底层数据结构。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。 基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

B+树是对B树的进一步优化。让我们先来看下B+树的结构图：

根据上图可以看出B+树与B树有什么不同

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

2、因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

3、还发现上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

  1. B+树和B树的适用场景？

B树和B+树的适用场景各有所长，主要取决于需要优化的操作类型和数据大小。

B树：

  1. 适用于随机读写和顺序读写混合的工作负载。由于B树的每个节点都包含数据，因此对于随机读写的工作负载，B树可以减少磁盘访问次数，提高查询性能。
  2. 适用于较小的数据集。B树的节点包含数据，因此对于较大的数据集，B树需要更多的磁盘访问次数，从而降低查询性能。
  3. 适用于高并发的读写操作。B树的每个节点包含数据，可以减少读取磁盘的次数，从而提高查询性能和数据的处理速度。

B+树：

  1. 适用于顺序读写工作负载。B+树的叶子节点只包含数据的指针，因此对于顺序读写的工作负载，B+树可以减少磁盘访问次数，提高查询性能。
  2. 适用于大数据集。B+树的叶子节点只包含数据指针，因此对于较大的数据集，B+树的高度更低，可以减少磁盘访问次数，提高查询性能。
  3. 适用于范围查询和排序操作。B+树的叶子节点形成了有序链表，因此对于范围查询和排序操作，B+树可以快速定位数据，提高查询性能。

综上所述，B树和B+树各有所长，应根据具体的应用场景和数据大小来选择合适的索引类型。在实际应用中，也可以根据需要使用B树和B+树的混合索引，以兼顾两者的优点。

  1. 举一个覆盖索引例子？

      当查询语句只需要使用到辅助索引中的列时，就可以使用覆盖索引。下面是一个覆盖索引的例子：

假设我们有一个表 t，其中包含列 a, b 和 c，我们创建了一个包含列 a 和 b 的索引：

CREATE INDEX idx_ab ON t(a, b);

现在我们需要查询列 a 和 b 以及列 c 的值，查询语句如下：

SELECT a, b, c FROM t WHERE a = 1 AND b = 2;

由于查询语句中只包含了索引中的列 a 和 b，因此 MySQL 可以直接在索引中找到满足条件的行，并返回列 a 和 b 的值。但是要获取列 c 的值，需要回到原表中再查询一次，这个过程称为回表查询。

如果我们改为创建一个包含列 a、b 和 c 的索引：

CREATE INDEX idx_abc ON t(a, b, c);

那么查询语句不需要回表查询：

SELECT a, b, c FROM t WHERE a = 1 AND b = 2;

这是因为索引中包含了所有需要查询的列的值，MySQL 可以直接从索引中获取所有需要的列值，不需要回到原表中再查询一次。这个过程称为覆盖索引。

  1. 普通索引和主键索引在B+树上的存储是什么样的？通过普通索引回表查询时走了怎么样的流程？请举列解释

普通索引和主键索引在 B+ 树上的存储是类似的，它们都是按照索引列的大小顺序存储在 B+ 树的叶子节点上，但是有一点不同的是，主键索引的叶子节点存储的是整行数据，而普通索引的叶子节点存储的是索引列和主键列的值。

当使用普通索引进行查询时，MySQL 首先会在普通索引树上进行查找，找到满足条件的索引值，然后将这些索引值存放在结果集中，接下来需要回表查询。回表查询的过程包含以下步骤：

  1. 通过索引值找到主键值
  2. 使用主键值去聚集索引树中查找对应记录的整行数据

举个例子，假设有一个表 t，其中包含三列：id(int), name(varchar) 和 age(int)，id 是主键，我们创建了一个普通索引 idx_name_age(name, age)。查询语句如下：

SELECT name, age FROM t WHERE name = 'Tom';

MySQL 首先在普通索引 idx_name_age 树上查找所有 name = 'Tom' 的索引值，找到后将这些索引值存放在结果集中。接着，MySQL 需要回表查询，将这些索引值转换成完整的行数据。具体过程如下：

  1. 通过普通索引 idx_name_age 找到所有匹配的主键值。假设有 3 个主键值分别是 1, 5, 9。
  2. 使用主键值，去聚集索引树中查询对应记录的整行数据。MySQL 会先找到叶子节点，然后再从叶子节点开始向上查找主键值对应的数据行，最终得到完整的行数据。

可以看到，回表查询的过程会增加查询的时间和成本，因此在设计表结构时，应该尽量避免回表查询，可以通过使用覆盖索引、索引包含所有需要查询的列等方式来避免回表查询。

  1. 索引失效的场景有哪些？

1.查询条件中有or,即使有部分条件带索引也会失效

2.like查询是以%开头

3.如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引

4.索引列上参与计算会导致索引失效

5.违背最左匹配原则

6.如果mysql估计全表扫描要比使用索引要快，会不适用索引

  1. 索引列用了函数

8.主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。

9.not exists关键字

avatar
文章
阅读
粉丝