MySQL面试题-索引篇

目录

1.什么是索引

2.索引常见的数据结构？

3.MySQL有哪些索引？

4.什么是B+树？为什么B+树成为主要的SQL数据库的索引实现？

5.聚簇索引和非聚簇索引？

6.什么是页分裂

7.Mysql为何建议使用自增id作主键

 8.什么叫回表？

9.什么是覆盖索引？

10.什么是最左前缀原则？

11.什么是索引下推

12.正确使用索引的一些建议

---

MySQL的索引是一种数据结构，可以用于加快数据库中数据的查询速度。索引是基于表中一个或多个列的值排序的快速查找数据结构，可以大大提高查询效率。MySQL支持多种类型的索引，如B-tree索引、哈希索引、全文索引等。

索引可以在表创建时定义，也可以在表已经创建后通过ALTER TABLE语句来添加。通常情况下，应该将索引定义在经常用于查询的列上，以提高查询效率。不过，需要注意索引会占用额外的存储空间，并且对数据的插入、更新和删除操作也会产生额外的开销。因此，在使用索引时需要权衡使用索引的利弊，选

可以把MySQL中的索引比作一本字典。假设你要查找一个单词，如果你从头开始依次翻阅每一页，直到找到目标单词，这样的速度显然会非常慢。但是如果你使用字典的索引，可以先翻到字典的索引页，然后根据字母顺序快速找到目标单词所在的页数，这样可以大大加快查找的速度。择适当的索引类型和索引列，以提高查询性能。

1. B-tree索引：B-tree是一种平衡树，它可以用于高效地查找数据，B-tree索引是MySQL中最常见的索引类型。在B-tree索引中，每个节点包含多个键值，可以快速定位到满足查询条件的数据行。

2. B+tree索引：B+tree是一种变种的B-tree，它可以用于高效地查询和范围查询。在B+tree索引中，只有叶子节点包含数据，其它节点只包含键值信息。

3. 哈希索引：哈希索引是一种将索引列的值转换为哈希值的索引结构，可以用于快速定位到指定的数据行。哈希索引适用于等值查询，但不支持范围查询和排序。

4. 全文索引：全文索引可以用于对文本类型的数据进行全文搜索，它可以提高对文本类型数据的查询效率。在MySQL中，可以使用全文索引来查找包含某些关键词的文本。

按照应用维度，常见的 MySQL 索引包括以下几类：

1. 主键索引（Primary Key Index）：主键索引是一种特殊的唯一索引，用于保证表中每一行数据的唯一性，也可以加速根据主键进行的查询操作。

2. 唯一索引（Unique Index）：唯一索引用于保证表中某一列或某几列的唯一性，也可以加速根据这些列进行的查询操作。

3. 普通索引（Index）：普通索引也称作非唯一索引，用于加速根据索引列进行的查询操作。与唯一索引不同的是，普通索引允许索引列中存在重复的值。

4. 全文索引（Fulltext Index）：全文索引用于对表中某一文本字段进行全文检索，常用于搜索引擎等应用场景。

5. 组合索引（Composite Index）：组合索引是将多个索引列组合成一个索引，可以加速根据这些列的组合进行的查询操作。组合索引的建立需要根据查询的具体情况和列的选择进行权衡和优化。

B+树是一种基于B树的数据结构，它的特点是在内部节点不存储数据，只存储索引的值，而所有数据都保存在叶子节点。

在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

同时，B+树还具有以下几个特点：

1. 所有叶子节点都是按照顺序连接在一起的，可以很方便地遍历整棵树的所有叶子节点，也方便基于范围的查询操作。

2. 所有数据记录的指针都保存在叶子节点上，因此在进行数据查询时，只需要搜索一次B+树就可以找到所有需要的数据记录。

3. B+树的内部节点只存储索引的值，而不存储数据记录的指针，因此B+树的内部节点可以存储更多的索引，从而减少树的高度，提高查询效率。

4. B+树的高度很低，因为每个节点存储的索引值比B树要多，所以B+树相对于B树来说，可以更好地利用磁盘预读特性，从而减少I/O操作的次数。

由于B+树具有以上优点，因此它成为了主要的SQL数据库的索引实现。

聚簇索引：

是将数据按照索引顺序存储在磁盘上的一种索引结构，通常是在主键或唯一键上建立的。因为聚簇索引直接存储数据，所以通过聚簇索引查询数据时，可以减少磁盘I/O操作的次数，提高查询效率。

非聚簇索引:

则是将索引和数据分开存储的一种索引结构，它的叶子节点存储的是索引字段和指向数据行的指针，而数据行则是按照主键顺序存储在磁盘上的。因为非聚簇索引和数据是分开存储的，所以在查询时需要先通过索引找到对应的数据行的主键，然后再通过主键查找对应的数据，因此会增加磁盘I/O操作的次数，相对于聚簇索引来说，查询效率更低。

需要注意的是，InnoDB存储引擎中的主键索引是聚簇索引，而非主键索引则是非聚簇索引。另外，如果一个表没有主键或唯一键，InnoDB会选择一个唯一非空索引作为聚簇索引。如果表中没有合适的索引可以作为聚簇索引，InnoDB会创建一个隐藏的聚簇索引来存储数据。

举个栗子：

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。

这个表的建表语句是：

mysql> create table T(id int primary key, k int not null, name varchar(16),index (k))engine=InnoDB;

 表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下。

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

在B+树索引结构中，页是存储索引数据的基本单位。当一个页已经存储了最大数量的索引记录，再往该页插入新的索引记录时，该页需要将其中一部分记录移动到新的页中，这个过程就叫做页分裂。页分裂的目的是为了维持B+树的平衡性，避免某个页中记录过多或者过少，影响索引的效率和查询性能。

页分裂会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

如果采用非自增性的索引，就有可能导致频繁的页分裂。

MySQL建议使用自增ID作为主键的原因如下：

1. 避免主键重复：使用自增ID作为主键可以避免主键重复的问题，因为每次插入新记录时，MySQL会自动分配一个新的ID，保证主键唯一性。

2. 提高查询性能：自增ID作为主键可以提高查询性能，因为B+树索引会按照主键排序，自增ID的值是递增的，可以降低页分裂和页合并的频率，减少B+树索引的维护成本，从而提高查询效率。

3. 减少索引占用空间：使用自增ID作为主键可以减少索引占用的空间，因为整型ID通常只需要占用4个字节的存储空间，而使用其他类型的主键，如字符串等，则会占用更多的存储空间。

需要注意的是，如果表中没有明显的自然主键，可以使用自增ID作为主键。但如果有自然主键，如身份证号、手机号等唯一标识符，则应该使用自然主键作为主键，以避免数据冗余和不一致。

回表（Covering Index）是指查询数据时，如果需要从辅助索引中查询列数据，而不是直接从主键索引或者聚簇索引中获取，就需要回到原始的表中再查找对应的数据，这个过程就被称为回表。

在MySQL中，辅助索引只存储了索引列的值和指向对应主键索引的指针，因此如果查询的列不在辅助索引中，就需要通过回表从原始的表中获取对应数据。这会增加I/O的操作，降低查询效率。

为了避免回表的操作，我们可以使用覆盖索引（Covering Index），即在辅助索引上包含所有需要查询的列，这样在查询时就可以直接从索引中获取所需要的数据，而不需要回到原始的表中进行查找。这样可以大大提高查询效率。

覆盖索引（Covering Index）是指辅助索引包含了所有需要查询的字段，查询时可以直接从辅助索引中获取数据而无需回表到主键索引或聚簇索引所在的数据页中查找数据。

覆盖索引的优点在于可以减少回表的操作，避免了不必要的IO操作，提高了查询效率，特别是在数据量大的情况下，优势更加明显。

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

例如：select ID from T where k between 3 and 5这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。

通过联合索引实现覆盖索引的目的，例如在一个市民信息表上，将身份证号和名字建立联合索引，那么通过身份证号查名字可以直接覆盖，不需要回表

最左前缀原则是指，在使用复合索引进行查询时，如果查询条件中只使用了复合索引的左边一部分列，那么索引只能用于查找第一个列，无法用于查找后面的列。这个原则也被称为“最左匹配原则”。这个原则的应用非常广泛，特别是在联合索引中。通过遵循最左前缀原则，可以尽可能地利用索引，提高查询效率。

查询的时候：

最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
(a,b)这个联合索引,单独用a查也可以，where a like ‘xxx%’也可以走索引，就不需要单独在a上建立索引了

设计索引的时候，也可以考虑：

(a,b)(b)和(b,a)(a)可以覆盖单独查和联合查的所有场景，这时考虑的原则就是空间，看（a）,(b)哪个占用空间更小，使用哪个

索引下推（Index Condition Pushdown，简称ICP）是 MySQL 的一种优化技术，用于优化查询语句的性能。在传统的查询执行过程中，MySQL 会先扫描索引获取匹配行的主键，然后再根据主键到数据表中查找行数据。而索引下推则是在索引上完成查询，并尽可能减少扫描数据行的数量，从而提高查询性能。

具体来说，当 MySQL 执行一条 SELECT 语句时，如果该语句中的 WHERE 条件包含一个或多个索引列，MySQL 就会尝试将这些条件下推到存储引擎中进行处理。这意味着 MySQL 在扫描索引时，会同时评估 WHERE 条件，只有符合条件的索引记录才会被返回，而不是扫描所有的索引记录。这样就可以减少存储引擎需要扫描的数据行数，从而提高查询效率。

例：

select * from tuser where name like '张%' and age=10 and ismale=1;

InnoDB在(name,age)索引内部就判断了age是否等于10，不用回表

需要注意的是，索引下推仅适用于使用非覆盖索引的查询语句，即查询语句需要返回表中的列，而不仅仅是索引列。如果查询语句使用的是覆盖索引，则不会进行索引下推优化。

1. 使用合适的数据类型：为了减少索引的大小，应该尽可能使用短的数据类型。例如，如果只需要存储年份，可以使用 SMALLINT 数据类型，而不是 INT 或 BIGINT 数据类型。

2. 精简索引数量：索引越多，查询速度越慢，因为查询需要扫描更多的索引。因此，应该仅在必要时使用索引，避免创建过多的索引。

3. 选择合适的索引类型：不同类型的索引适用于不同的查询场景。因此，应该根据查询的特点选择合适的索引类型，例如 B+ 树索引或哈希索引。

4. 避免过长的索引：索引长度越长，索引越大，查询速度就越慢。因此，应该避免创建过长的索引，例如超过 100 个字符。

5. 考虑索引列的顺序：按照查询条件的顺序创建索引，可以最大限度地利用索引的优势。这是因为索引在执行查询时只能使用索引的最左前缀。

6. 避免在索引列上进行函数操作：在索引列上进行函数操作会使索引失效，无法利用索引进行查询。因此，应该避免在索引列上进行函数操作，例如在索引列上使用函数 LOWER() 或 UPPER()。

7. 使用覆盖索引：如果查询只需要返回索引列，可以使用覆盖索引。覆盖索引只需要扫描索引，而不需要扫描数据行，可以加快查询速度。

8. 避免使用 NOT IN 和 OR 条件：使用 NOT IN 和 OR 条件会导致 MySQL 无法使用索引进行查询，应该尽可能避免使用这些条件。

9. 定期维护索引：定期维护索引可以提高查询性能。例如，可以删除不必要的索引，重新生成索引统计信息等。

来源地址：https://blog.csdn.net/qq_33129875/article/details/129313635