MySQL面试常见问题

zgcyyh

已于 2023-11-02 15:10:06 修改

阅读量49

点赞数

分类专栏： Mysql 文章标签： mysql

于 2023-11-02 14:52:47 首次发布

本文链接：https://blog.csdn.net/zgcyyh/article/details/134182190

版权

Mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

本文详细解释了MySQL中回表、索引覆盖、B树(B+树)的概念，以及InnoDB和MyISAM存储引擎的特点，涵盖了聚簇索引、非聚簇索引、组合索引和索引失效情况。强调了根据应用场景选择合适的索引策略和存储引擎的重要性。

摘要由CSDN通过智能技术生成

1、回表

回表是指在 MySQL中，使用普通索引进行查询时，如果查询所需的数据不在索引页中，就需要通过索引中的引用（主键）再次查找主表的数据，获取所需目标数据的过程。

- 当查询需要获取除索引列之外的其他列数据时，会发生回表操作。例如，如果一个查询需要根据索引列的条件进行筛选，并且还需要获取其他列的数据，那么就需要进行回表操作来获取这些额外的列数据。

2、索引覆盖

索引覆盖（covering index）是指在查询中，所需的数据可以完全通过普通索引来获取，而无需再次访问主表。这种情况下，查询可以直接从普通索引中获取所需的数据，而不需要通过主键回表操作。

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O.

- 索引覆盖发生在查询中只需要索引列数据的情况下。如果查询的列都在索引中，并且索引包含了查询所需的所有列数据，那么查询就可以直接从索引中获取所有需要的数据，而不需要回表操作。

使用索引覆盖可以提高查询性能，因为它避免了额外的回表操作，减少了磁盘I/O和数据访问的开销。但是，并非所有查询都可以实现索引覆盖，它需要根据具体的查询条件和索引设计来决定是否可行。

参考地址：https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_covering_index

3、B树（B-TREE）

B树是数据库索引中常用的树数据结构。它的整个结构始终保持有序，允许快速查找精确匹配（等于运算符）和范围（例如，大于、小于和BETWEEN运算符）。这种类型的索引可用于大多数存储引擎，如InnoDB和MyISAM。

A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM.

因为B树节点可以有许多子节点，所以B树与二叉树不同，后者每个节点只能有2个子节点。

Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.

使用术语B-tree是为了参考索引设计的一般类。MySQL存储引擎使用的B-tree结构可能被视为变体，因为在经典的B-tree设计中不存在简化。

The use of the term B-tree is intended as a reference to the general class of index design. B-tree structures used by MySQL storage engines may be regarded as variants due to sophistications not present in a classic B-tree design. For related information, refer to the InnoDB Page Structure Fil Header section of the MySQL Internals Manual.

参考地址：https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_b_tree

B-TREE（B树）的结构特点：

       1. B-tree是一种平衡的多路搜索树，每个节点可以存储多个键值对。
       2. B-tree的节点分为内部节点和叶子节点，内部节点存储索引键值和指向子节点的指针，叶子节点存储实际的数据。
       3. B-tree的节点按照键值的大小顺序进行排序，使得节点中的键值保持有序。
       4. B-tree的每个节点都有一个最小和最大键值范围，节点中的键值在这个范围内。
       5. B-tree的高度相对较低，通常可以保持在较小的范围内，使得查找效率高。

4、B+树（B+TREE）

B+树，是从B树优化而来的，被视为B树的变体。

   B+TREE（B+树）的结构特点
       1. B+tree是在B-tree的基础上进行了优化的索引结构。
       2. B+tree的内部节点仅存储键值，不存储实际的数据，所有的数据都存储在叶子节点中。
       3. B+tree的叶子节点使用链表进行连接，便于范围查询和顺序遍历。
       4. B+tree的叶子节点按照键值的大小顺序进行排序，形成一个有序链表。
       5. B+tree的叶子节点中存储了指向下一个叶子节点的指针，使得范围查询更加高效。

5、B-TREE 和 B+TREE的区别

   1. 存储方式：B-tree的节点既存储键值又存储数据，而B+tree的节点仅存储键值，数据存储在叶子节点中。
   2. 叶子节点连接：B-tree的叶子节点之间没有连接，而B+tree的叶子节点使用链表进行连接。
   3. 范围查询：B-tree的范围查询需要在内部节点和叶子节点中进行，而B+tree的范围查询只需要在叶子节点中进行。
   4. 适用场景：B-tree适用于随机查询和更新操作较多的场景，而B+tree适用于范围查询和顺序遍历较多的场景。

总体而言，B+tree在范围查询和顺序遍历方面具有更好的性能，而B-tree在随机查询和更新操作方面稍微优于B+tree。选择使用哪种索引结构取决于具体的应用场景和需求。

6、InnoDB索引

   1. 主键索引（Primary Key Index）
       主键索引用于唯一标识表中的每一行数据。它可以保证数据的唯一性，并且在主键上进行的查询操作非常高效。主键索引适用于需要快速定位和访问特定行数据的场景。

   2. 唯一索引（Unique Index）
       唯一索引用于确保某一列或多列的值在表中是唯一的。它可以防止重复数据的插入，并且在唯一索引上进行的查询操作也比较高效。唯一索引适用于需要保证数据唯一性并进行快速查询的场景。

   3. 普通索引（Non-Unique Index）
       普通索引也称为非唯一索引，它用于提高特定列或多列的查询性能。普通索引可以加快查询速度，但允许重复值存在。普通索引适用于需要加速查询操作的场景。

   4. 全文索引（Full-Text Index）
       全文索引用于在文本数据中进行全文搜索。它可以快速查找包含特定关键词或短语的文本数据。全文索引适用于需要进行全文搜索的场景，如文章内容、产品描述等。

   5. 空间索引（Spatial Index）
       空间索引用于处理具有空间关系的数据，如地理位置坐标。它可以加速空间查询和空间计算操作，如距离计算、范围查询等。空间索引适用于需要处理地理位置数据的场景。

7、聚簇索引（Clustered Index）

是一种索引结构，它决定了数据在磁盘上的物理存储顺序。在聚簇索引中，索引的顺序与数据的物理存储顺序一致。每个表只能有一个聚簇索引，通常是主键索引。聚簇索引的优势是可以提高特定顺序的查询性能，因为相关的数据行在物理上相邻存储。

8、非聚簇索引（Non-clustered Index）

是另一种索引结构，它的顺序与数据的物理存储顺序无关。非聚簇索引包含索引列的值和指向实际数据行的指针。一个表可以有多个非聚簇索引，用于加速根据不同列进行的查询操作。

9、组合索引（Composite Index）

是指由多个列组成的索引。它可以包含两个或多个列的值，并按照这些列的顺序进行排序。组合索引可以在多个列上进行联合查询，并且在满足左侧列的条件时，可以更快地定位到特定的数据行。组合索引的优势是可以减少索引的数量，节省存储空间，并提高查询性能。

10、MySQL InnoDB 和 MyISAM 存储引擎的区别

   10.1. 事务支持
       InnoDB 是一个支持事务的存储引擎，它遵循 ACID（原子性、一致性、隔离性、持久性）特性，可以实现事务的原子性和一致性。
       MyISAM 不支持事务，只支持表级锁定。

   10.2. 并发性能
       InnoDB 支持行级锁定，可以实现更好的并发性能。多个事务可以同时操作不同行的数据，提高了并发处理的能力。适合大量的写操作。
       MyISAM 只支持表级锁定，多个事务同时操作同一表时会发生锁冲突，影响并发性能。适合大量的读操作。

   10.3. 数据完整性
       InnoDB 支持外键约束，可以确保数据的完整性和一致性。它可以实现关系型数据库的常见特性，如级联删除和级联更新。
       MyISAM 不支持外键约束，需要应用程序自行维护数据的完整性。

   10.4. 数据存储方式
       InnoDB 使用聚簇索引（Clustered Index）来组织数据，将数据存储在主键索引的叶子节点中。
       MyISAM 使用堆表（Heap Table）的方式存储数据，数据在磁盘上以插入的顺序存储。

   10.5. 效率和性能
       InnoDB 在处理大量并发读写操作时性能较好，特别适合于高并发的 OLTP（联机事务处理）场景。
       MyISAM 在处理大量读操作时性能较好，适合于读密集型的应用。

   10.6 数据存储位置
       InnoDB表的数据存储在以 .ibd 为扩展名的文件中。每个 InnoDB 表都有一个对应的数据文件，包含了表的行数据和索引数据。
       MyISAM 表的数据存储在以 .MYD 为扩展名的文件中。每个 MyISAM 表都有一个对应的数据文件，包含了表的行数据。
       MyISAM 表的索引存储在以 .MYI 为扩展名的文件中。每个 MyISAM 表都有一个对应的索引文件，包含了表的索引数据。

需要根据具体的应用场景和需求来选择适合的存储引擎。如果需要事务支持和更好的并发性能，可以选择 InnoDB。如果对于读操作的性能要求较高且不需要事务支持，可以选择 MyISAM。