MySQL 索引（超详细面试重点！）

最新推荐文章于 2025-05-08 21:19:17 发布

&星辰入梦来&

最新推荐文章于 2025-05-08 21:19:17 发布

阅读量1.3k

点赞数 34

文章标签：面试职场和发展

本文链接：https://blog.youkuaiyun.com/rc1596919047/article/details/146046057

版权

1 索引介绍

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的作用就相当于书的目录。打个比方：我们在查字典的时候，如果没有目录，那我们就只能一页一页的去查我们需要的那个字，如果有目录就直接按照目录翻到那一页就行。

索引的底层数据结构存在很多种类型，常见的有：B树，B+树和Hash、红黑树。在MySQL中，无论是Innodb还是MyIsam，都使用了B+树作为索引结构。

2 索引的优缺点

优点：

使用索引可以大大加快数据的检索速度（大大减少检索的数据量），减少IO次数，这也是创建索引的最主要原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删查改的时候，如果数据有索引，那么索引也需要动态的修改，会降低SQL执行的速率。
索引需要使用物理文件存储，也会耗费一定空间。

注意，索引并不一定能提高查询的性能，如果数据量不是那么大的情况下，我们也并不一定需要用到索引哦！

3 索引底层数据结构选型

3.1 Hash表

Hash表是键值对的集合，通过键（key）即可快速取出对应的值（value），因此哈希表可以快速检索数据（接近O(1)）。

为什么能够通过 Key 快速取出 Value 呢？原因在于哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到Key对应的index，找到index也就找到了对应的value。

hash = hashfunc(key)
index = hash % array_size

但是Hash算法有个Hash冲突的问题，也就是说多个不同的Key最后的index相同。通常情况下，我们常用的解决办法是链地址法，链地址法就是将哈希冲突数据存放在链表中。在JDK1.8之前HashMap是通过链地址法来解决哈希冲突的。不过，1.8之后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

为了减少Hash冲突的发生，一个好的哈希函数应该均匀地将数据分布在整个可能的哈希值集合中。

MySQL的InnoDB存储引擎不直接支持常规的哈希索引，但是InnoDB存储引擎中存在一种特殊的“自适应哈希索引”，自适应哈希索引并不是传统意义上的纯哈希索引，而是结合了 B+ Tree和哈希索引的特点，以便更好地适应实际应用中的数据访问模式和性能需求。自适应哈希索引的每个哈希桶实际上是一个小型的B+Tree结构。这个B+Tree结构可以存储多个键值对，而不仅仅是一个键。这有助于减少哈希冲突链的长度，提高索引的效率。

既然哈希表这么快，为什么MySQL没有使用哈希表作为其索引的数据结构呢？主要是因为Hash索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那Hash索引就不行了。并且，每次IO只能取一个。

SELECT * FROM tb1 WHERE id < 500;

在这种范围查询中，优势非常大，直接遍历比500小的叶子结点就够了。而Hash索引是根据hash算法来定位的，我们不能够进行500次hash计算来得到最终的结果。这就是Hash最大的缺点。

3.2 二叉查找树（BST）

二叉查找树（Binary Search Tree）是一种基于二叉树的数据结构，它具有以下特点：

某一元素左子树所有的结点的值均小于该结点的值.
某一元素右子树的所有结点的值均大于该结点的值.
左右子树也为二叉查找树。

当二叉查找树是平衡的时候，也就是树的每个结点的左右子树深度相差不超过1的时候，查询的时间复杂度为O(log2(N))，具有比较高的效率，然而，当二叉查找树不平衡的时候，例如“斜树情况时”，查询效率会急剧下降，时间复杂度退化为O(N)。

也就是说，二叉查找树的性能非常依赖于它的平衡程度，这就导致其不适合作为MySQL底层索引的数据结构。

为了解决这个问题，并提高查询效率，人们发明了多种在二叉查找树基础上的改进型数据结构，如平衡二叉树，B-树，B+树等等。

3.3 AVL树

AVL树是计算机中最早被发明的自平衡二叉查找树。AVL树的特点是保证任何节点的左右子树高度之差不超过1，因此也被称为高度平衡二叉树，它的查找插入和删除在平均和最坏的情况下时间复杂度都是O(logn)

AVL树采用了旋转操作来保持平衡。主要有四种旋转操作：LL旋转、RR旋转|LR旋转和RL旋转。其中LL旋转和RR旋转分别用于处理左左和右右平衡，而LR旋转和RL旋转则用于处理左右和右左失衡。

由于AVL树需要频繁的进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了数据库写操作的性能，如果需要查询的数据分布在多个结点上，那么就需要进行多次磁盘IO。磁盘IO是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度减少磁盘IO操作的次数。

实际应用中，AVL树使用的并不多。

3.4 红黑树

红黑树是一种自平衡二叉查找树，通过在插入和删除结点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

1.每个节点非红即黑；

2.根结点总是黑色的；

3.每个叶子结点都是黑色的空结点（NIL结点）；

4.如果结点是红色的，则它的子结点一定是黑色的（反之不一定）；

5.从任一结点到它的叶子结点或者空子结点的每条路径，必须包含相同数目的黑色结点（即相同黑色高度）。

和AVL树不同的是，红黑树并不追求严格的平衡，而是大致的平衡。正因如此，红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘IO操作才能查询到，这也是MySQL没有选择红黑树的主要原因。也正因如此，红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除结点时只需进行O(1)次数的旋转和变色操作，即可保持基本平衡状态，而不需要像AVL树一样进行O(logn)次数的旋转操作。

红黑树的应用还是比较广泛的，TreeMap、TreeSet以及1.8的HashMap底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。

3.4 B树&B+树

B树也被称为B-树，全称为多路平衡查找树，B+树是B树的一种变体。B树和B+树中的B是Balance（平衡）的意思。

目前大部分数据库系统以及文件系统都采用B-TRee或其变种B+Tree作为索引结构。

B树&B+树两者有何异同呢？

B树的所有结点既存放键key也存放数据data,而B+树只有叶子结点存放Key和data，其他内结点只存放key。
B树的叶子结点都是独立的；B+树的叶子结点有一条引用链指向与他相邻的叶子结点。
B树的检索过程相当于对范围内的每个结点的关键字做二分查找，可能还没有到达叶子结点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根结点到叶子结点的过程，叶子结点的顺序检索很明显。
在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与B树相比，具备更少的IO次数、更稳定的查询效率和更适用范围查询这些优势

在MySQL中，MyISAM引擎和InnoDB引擎都是使用B+Tree作为索引结构，但是两者实现的方式不太一样。

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引（非聚集索引）”。

InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（聚集索引）”，而其余的索引都作为 辅助索引 ，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

4 索引类型总结

4.1 按照数据结构维度划分

BTree索引：MySQL里默认和最常用的索引类型。只有叶子结点存储value，非叶子结点只有指针和Key。存储引擎MyISAM和InnoDB实现BTree索引都是使用B+Tree，但二者实现方式不一样（前面已经介绍了）。
Hash索引：类似键值对的形式，一次即可定位。
RTree索引：一般不会使用，仅支持geometry数据类型，优势在于范围查找，效率较低，通常使用搜索引擎代替。
全文引擎：对文本的内容进行分词，进行搜索。目前只有char，varchar，text列上可以创建全文索引。一般不会使用，效率比较低，通常使用搜索引擎代替。

4.2 按照底层存储方式角度划分

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引（辅助索引）就属于非聚簇索引。MySQL的MyISAM引擎，不管是主键还是非主键，使用的都是非聚簇索引。

4.3 按照应用维度划分

主键索引：加速查询+列值唯一（不可以有NULL）+表中只有一个。
普通索引：仅加速查询
唯一索引：加速查询+列值唯一（可以有空）
覆盖索引：一个索引包含（覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有char、varchar、text列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
前缀索引：对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

4.4 MySQL 8.x版本实现的索引新特性

隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会在软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。

降序索引：之前的版本就支持通过desc来指定索引为降序，但实际上创建的仍然是常规的升序索引。直到MySQL 8.x版本才开始真正支持降序索引。另外，在MySQL8.x版本中，不再对GROUP BY语句进行隐式排序。

函数索引：从MySQL8.0.13版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式。

5 主键索引（Primary Key）

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为空，不能重复。

在MySQL的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段作为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

6 二级索引

二级索引的叶子结点存储的数据是主键的值，也就是说，通过二级索引可以定位主键的位置，二级索引又称为辅助索引/非主键索引。

唯一索引，普通索引，前缀索引等索引都属于二级索引。

唯一索引：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。建立索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
前缀索引：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
全文索引：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

二级索引：

7 聚簇索引与非聚簇索引

7.1 聚簇索引（聚集索引）

7.1.1 聚簇索引介绍

聚簇索引即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB中的主键索引就属于聚簇索引。

在MySQL中，InnoDB引擎的表的.ibd文件就包含了该表的索引和数据，对于InnoDB引擎表来说，该表的索引（B+树）的每个非叶子结点存储索引，叶子结点存储索引和索引对应的数据。

7.1.2 聚簇索引的优缺点

优点：

查询速度非常快：聚簇索引的查询速度非常的快，因为整个B+树本身就是一棵多叉平衡树，叶子结点也都是有序的定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引，聚簇索引少了一次读取数据的IO操作。
对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

依赖于有序的数据：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据还是整型还好，否则类似于字符串或者UUID这种又长又难比较的数据，插入或者查找的速度肯定比较慢。
更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

7.2 非聚簇索引（非聚集索引）

7.2.1 非聚簇索引介绍

非聚簇索引即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引（辅助索引）就属于非聚簇索引。MySQL的MyISAM引擎，不管主键还是非主键。使用的都是非聚簇索引。

非聚簇索引的叶子结点并不一定存放数据的指针，因为二级索引的叶子结点就存放的是主键，根据主键再回表查数据

7.2.2 非聚簇索引的优缺点

优点：

更新代价比聚簇索引要小。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子结点是不存放数据的。

缺点：

依赖于有序的数据：跟聚簇索引一样，非聚簇索引也依赖于有序的数据。
可能会二次查询（回表）：这应该是非聚簇索引最大的缺点了。当查到索引对应的指针或者主键后，可能还需要根据指针或者主键再到数据文件或表中查询。

MySQL表文件截图：

聚簇索引和非聚簇索引：

B+树，如果为聚簇索引，那么data将会存放数据；

如果为非聚簇索引，那么data将存放指向数据的指针。

8 覆盖索引和联合索引

8.1 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。

在InnoDB存储引擎中，非主键索引的叶子结点包含的是主键的值。这意味着，当使用非主键索引进行查询时，数据库会先找到对应的主键值，然后再通过主键索引来定位和检索完整的行数据。这个过程被称为“回表”。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

如主键索引，如果一条SQL需要查询主键，那么正好根据主键索引就可以查到主键。再如普通索引，如果一条SQL需要name，name字段正好有索引，那么直接根据这个索引就可以查到数据，也无需回表。

8.2 覆盖索引效果

1.创建一个名为cus_order的表，来实际测试一下这种排序方式。为了测试方便，cus_order这张表只有id、score、name这3个字段。

CREATE TABLE `cus_order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  `name` varchar(11) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8mb4;

2.定义一个简单的存储过程，来插入100w的测试数据。

DELIMITER ;;
CREATE  PROCEDURE `BatchinsertDataToCusOder`(IN start_num INT,IN max_num INT)
BEGIN
      DECLARE i INT default start_num;
      WHILE i < max_num DO
          insert into `cus_order`(`id`, `score`, `name`)
          values (i,RAND() * 1000000,CONCAT('user', i));
          SET i = i + 1;
      END WHILE;
  END;;
DELIMITER ;

存储定义完成之后，我们执行存储过程即可！

CALL BatchinsertDataToCusOder(1, 1000000); # 插入100w+的随机数据

3.创建覆盖索引并使用 EXPLAIN 命令分析

我们以 score 和 name 两个字段建立联合索引（下文介绍）

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

创建完成之后，我们对这100w个数据按照 score 进行排序，我们需要执行下面的SQL语句

#降序排序
SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;

再执行EXPLAIN语句，你就会发现Extra这一列Using index就说明我们成功使用了覆盖索引

EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;

额外（Extra）信息：

8.2 联合索引

使用表中的多个字段创建索引，就是联合索引，也叫组合索引 或者 复合索引。

以 score 和 name 两个字段建立联合索引：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

8.2.1 最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么MySQL就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如大于、小于为止）。对于大于等于、小于等于、 BETWEEN 及前缀匹配 LIKE 的范围查询，不会停止匹配。

假设有一个联合索引(column1, column2, column3)，其从左到右的所有前缀为(column1)、(column1，column2）、(column1,column2,column3) 即创建一个联合索引相当于创建了3个索引，包含这些列的所有查询都会走索引而不会全表扫描。

我们在使用联合索引时，可以将区分度搞高的放在最左边，这也可以过滤更多的数据。

1.创建一个名为 student 的表，这张表只有id，name，class这3个字段。

CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `class` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_class_idx` (`name`,`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

以下是给出的三条语句，我们分别来测试一下。

EXPLAIN SELECT * FROM student WHERE name = 'Anne Henry';
EXPLAIN SELECT * FROM student WHERE class = 'lIrm08RYVk';
EXPLAIN SELECT * FROM student WHERE name = 'Anne Henry' AND class = 'lIrm08RYVk';

2.测试

EXPLAIN SELECT * FROM student WHERE name = 'Anne Henry';

EXPLAIN SELECT * FROM student WHERE class = 'lIrm08RYVk';

EXPLAIN SELECT * FROM student WHERE name = 'Anne Henry' AND class = 'lIrm08RYVk';

很明显，上下两条语句 可以命中索引 而中间语句 无法命中索引

提问？如果有索引 联合索引（a，b，c），查询 a=1 AND c=1会走索引么？c=1 呢？b=1 AND c=1呢？

查询 a=1 AND c=1：根据最左前缀匹配原则，查询可以使用索引的前缀部分。因此，该查询仅在 a=1 上使用索引，然后对结果进行 c=1 的过滤。
查询 c=1 ：由于查询中不包含最左列 a，根据最左前缀匹配原则，整个索引都无法被使用。
查询b=1 AND c=1：和第二种一样的情况，整个索引都不会使用。

9 如何正确使用索引？

9.1 字段尽量不为NULL

索引字段的数据应该尽量不为NULL，因为数据为NULL的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为NULL，建议使用0,1，true，false这样语义较为清晰地短值或短字符作为代替。

9.2 被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的，如果一个字段不经常被查询，反而被经常修改，那么就没必要在这种字段上建立索引了。

9.3 限制每张表上的索引数量

索引并不是越多越好，建议单表索引不超过5个，索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率

因为MySQL优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加MySQL优化器生成执行计划的时间，同样会降低查询性能。

9.4 尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一棵B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大的磁盘空间，且修改数据的操作效率也会提升。

9.5 避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引（a，b）就肯定能命中索引（a），那么（a）就是冗余索引。在大多数情况下都应该尽量扩展已有的索引而不是创建新索引。

9.6 字符串类型的字段使用前缀索引代替不同索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑前缀索引代替普通索引。

9.7 避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况如下：

SELECT *不会直接导致索引失效（如果不走索引大概率是因为where查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖；
创建了组合索引，但是查询条件未遵守最左匹配原则；
在索引列上进行计算、函数、类型转换等操作；
以%开头的LIKE查询比如LIKE'%abc'；
查询条件中使用or，且or前后条件中有一个列没有索引，涉及的索引都不会被使用到；
IN的取值范围较大时会导致索引失效，走全表扫描（NOT IN 和 IN 的失效场景相同）
发生隐式转换