mysql索引常见面试题

1. mysql 为什么使用B+树？

• 二叉树：单边增长的场景会导致全表扫描。

• 红黑树：相对平衡，比二叉树性能好，大数据下，红黑树的高度过高，会造成磁盘IO频繁。

• Hash: 不支持范围查找和排序

• B树 VS B+树：

1. 更高的节点利用率

B+树与B树的一个关键区别在于B+树的所有数据都存储在叶子节点，而非叶子节点只存储键值和指向子节点的指针。这样可以使非叶子节点包含更多的键值和指针，提高了节点的利用率，从而减少了树的高度。

2. 更高的查询效率

由于B+树的叶子节点形成了一个链表，所有的实际数据都存储在叶子节点中，这使得范围查询和顺序遍历更加高效。可以很容易地通过顺序访问叶子节点来实现范围查询，而不需要回溯。

3. 更适合磁盘存储

数据库系统通常需要处理大量的数据，这些数据大部分存储在磁盘上。B+树设计更加适合磁盘存储，因为它减少了树的高度，进而减少了磁盘I/O操作的次数。每个节点可以存储更多的指针，从而降低了访问数据时需要读取的磁盘块数量。

4. 更稳定的查询性能

在B+树中，所有实际数据都在叶子节点上，非叶子节点仅作为索引使用，这意味着所有的查找操作所需的路径长度相同。因此，B+树的查询性能更加稳定。

具体比较：B树 vs B+树

• 节点结构：

  • B树：每个节点既包含键值也包含数据。
  • B+树：所有数据都存储在叶子节点，非叶子节点只包含键值和指针。

• 数据存储位置：

  • B树：数据存储在各个节点中。
  • B+树：数据存储在叶子节点中，且叶子节点形成链表。

• 查询性能：

  • B树：查找过程中可能在不同深度找到数据。
  • B+树：查找路径长度相同，查询性能更稳定。

• 范围查询：

  • B树：范围查询需要遍历多个节点，不如B+树高效。

  • B+树：叶子节点形成链表，顺序遍历和范围查询非常高效。

2. 聚簇索引和非聚簇索引

1. 聚簇索引 (Clustered Index)

定义： 聚簇索引将数据行的物理存储顺序与索引顺序一致。每张表只能有一个聚簇索引，因为数据行的物理存储顺序只能有一种。

特点：

• 数据行和索引存储在同一个结构中，索引叶子节点包含完整的数据行。
• 通常，表的主键会被作为聚簇索引。如果没有显式定义主键，MySQL 会选择一个唯一的非空索引作为聚簇索引；如果没有这样的索引，MySQL 会生成一个隐藏的行 ID 作为聚簇索引。
• 聚簇索引提高了基于主键的查询性能，因为索引和数据在同一个结构中，无需二次查找。
• 插入、更新和删除操作的性能可能会受到影响，因为数据行的物理位置可能需要调整以维持索引顺序。

示例： 假设有一张表 employees，它的主键是 employee_id，则 employee_id 字段是聚簇索引：

2. 非聚簇索引 (Non-clustered Index)

定义： 非聚簇索引的叶子节点包含指向数据行的指针（通常是聚簇索引键值或行 ID），而不是实际的数据行。

特点：

• 数据行的物理存储顺序与索引顺序无关。
• 每个表可以有多个非聚簇索引。
• 非聚簇索引的叶子节点存储索引键和指向数据行的指针。
• 通过非聚簇索引进行查询时，首先通过索引找到指针，然后根据指针找到实际的数据行，这需要一次额外的查找操作。

假设有一张表 employees，你可以为 name 字段创建一个非聚簇索引：

3 聚簇索引与非聚簇索引的区别

1. 数据存储方式：
   • 聚簇索引：数据行按索引顺序存储，叶子节点包含实际的数据行。
   • 非聚簇索引：索引结构和数据分开存储，叶子节点包含指向数据行的指针。
2. 查询性能：
   • 聚簇索引：对主键查询非常高效，因为不需要额外的查找操作。
   • 非聚簇索引：查询需要先通过索引找到指针，再通过指针找到数据行，有一定的查找开销。
3. 插入、更新和删除性能：
   • 聚簇索引：可能需要重排数据行以保持顺序，因此插入、更新和删除操作可能会更慢。
   • 非聚簇索引：只需更新索引结构，不影响数据行的物理顺序，操作相对较快。
4. 空间使用：
   • 聚簇索引：由于数据行存储在索引结构中，可能会占用更多的存储空间。
   • 非聚簇索引：索引结构较小，因为只包含索引键和指针。

4 实际应用中的选择

• 聚簇索引：适用于频繁的基于主键的查询操作，尤其是范围查询。例如，按日期或ID范围查询数据时，聚簇索引可以显著提高性能。
• 非聚簇索引：适用于其他频繁查询的列，比如搜索、过滤和排序操作。非聚簇索引可以加快这些操作的速度，但要注意维护索引带来的额外开销。

3 InnoDB和MyISAM对比

存储引擎是对于表而言的，不同的表可以设置不同的存储引擎

1 InnoDb

• 适合需要事务和外键约束。

• 高度并发的读写操作性能较好。

• 支持行级锁定，提高并发处理能力。

• 支持聚簇索引和非聚簇索引。

  • InnoDB 使用 B+ 树结构实现聚簇索引。每个 InnoDB 表都有一个聚簇索引，通常是表的主键。如果没有显式定义主键，InnoDB 会选择一个唯一的非空索引作为聚簇索引；如果没有这样的索引，InnoDB 会生成一个隐藏的行 ID 作为聚簇索引。
  • 索引文件和数据文件是在一起的。 索引文件+ 数据文件 是xxx.ibd
  • 叶子结点包含了完整的数据记录，data里存储的是：索引所在行的其他所有数据

2 MyISAM索引

• 不支持事务和外键约束。

• 适合读操作多于写操作的应用。

• 表级锁定，写操作会锁定整个表，适合低并发写操作的应用。

• 索引文件较小，适合大量读操作和全文索引。

• 只支持非聚簇索引，不支持聚簇索引。
  • 故MyISAM索引文件和数据文件是分离的。索引文件是xxx.MYI, 数据文件是xxx.MYD

执行流程：当有一条查询语句：where Col1 = 49， 先判断有没有走索引，走索引的话，先根据49快速在MYI文件中定位到结点，获取该结点存储的索引所在行的磁盘文件指针0x90，再去MYD中定位数据。

4 最左前缀法则

1. 定义

对于一个多列索引，查询条件必须从索引的最左列开始并按顺序使用，否则索引不会被完全利用。MySQL 查询优化器会重新排列条件顺序，以充分利用索引。因此，即使条件顺序不同，只要包含索引的前缀部分，索引仍会被使用。

即：假设你建立了一个联合索引（A，B，C），那么在执行查询的时候 where A=xx1 或者 where A=xx1 and B=xx2 或者where A=xx1 and B=xx2 and C=xx3 会走索引进行查询优化，因为他们都是从最左列A开始。

比如： where B=xx1 and C=xx2 就不会走索引

where a=xx1 and C=xx2 就会走部分索引，即A的部分

2. 原因

最左匹配原则都是针对联合索引来说的，所以我们可以从联合索引的原理来了解最左匹配原则。

索引的底层是一颗 B+ 树，那么联合索引当然还是一颗 B+ 树，只不过联合索引的键值数量不是一个，而是多个。构建一颗 B+ 树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+ 树。形如(a,b,c)联合索引的 b+ 树。a 是有序的，而 b，c 都是无序的。但是当在 a 相同的时候，b 是有序的，b 相同的时候，c 又是有序的。即只有先确定了前一个（左侧的值）后，才能确定下一个值。

5 为什么InnoDb表要尽量设定一个主键

• innnoDb表数据文件本身就是按B+tree组织的一个索引文件
• 主键是数据库确保数据行在整张表唯一性的保障.
• 设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。
• 如果没有主键，InnoDB会选择一个唯一键来作为聚簇索 引，如果没有唯一键，会生成一个隐式的主键。

6 主键使用整型的自增ID而不是UUID？

• 查找元素的时候会涉及到大量的数据比较，整型比字符串快
• UUID占用的存储空间会大于整形
• 叶子结点是按顺序排列的，如果主键索 引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，在维护B+树的过程中，会造成非常多的数据插入，数据移动，然后导致产生很多的内 存碎片，进而造成插入性能的下降。

7 联合索引是什么

• 5个单值索引，对应5棵B+树，联合索引就只需要一棵树，所以日常推荐使用联合索引而不是单值索引。
• 索引排序的时候会按照字段顺序，逐个去排序