MySQL索引

本系列参考动力节点老杜MySQL视频教程

1. 什么是索引?

索引是一种能够提高检索（查询）效率的提前排好序的数据结构。例如：书的目录就是一种索引机制。索引是解决SQL慢查询的一种方式。

2. 索引的创建和删除

2.1 主键会自动添加索引

主键字段会自动添加索引，不需要程序员干涉，主键字段上的索引被称为主键索引

2.2 unique约束的字段自动添加索引

unique约束的字段也会自动添加索引，不需要程序员干涉，这种字段上添加的索引称为唯一索引

2.3 给指定的字段添加索引

• 建表时添加索引

  CREATE TABLE emp (
      name varchar(255),
      INDEX idx_name (name)
  );
  

• 表已经创建好了，后期给字段添加索引

  ALTER TABLE emp ADD INDEX idx_name (name);
  

• 直接创建索引

  create index idx_name on emp(name);
  

2.4 删除指定字段上的索引

ALTER TABLE emp DROP INDEX idx_name;

2.5 查看某张表上添加了哪些索引

show index from 表名;

3. 索引的分类

不同的存储引擎有不同的索引类型和实现：

• 按照数据结构分类：
  • B+树 索引（mysql的InnoDB存储引擎采用的就是这种索引）采用 B+树 的数据结构
  • Hash 索引（仅 memory 存储引擎支持）：采用 哈希表 的数据结构
• 按照物理存储分类：
  • 聚集索引：索引和表中数据在一起，数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引。
  • 非聚集索引：索引和表中数据是分开的，索引是独立于表空间的，一张表可以有多个非聚集索引。
• 按照字段特性分类：
  • 主键索引（primary key）
  • 唯一索引（unique）
  • 普通索引（index）
  • 全文索引（fulltext：仅 InnoDB和MyISAM 存储引擎支持）：要求字段的类型都是文本内容采可以使用全文索引。
• 按照字段个数分类：
  • 单列索引、联合索引（也叫复合索引、组合索引）

4. MySQL索引采用了B+树数据结构

常见的树相关的数据结构包括：

• 二叉树
• 红黑树
• B树
• B+树

区别：树的高度不同。树的高度越低，性能越高。这是因为每一个节点都是一次I/O

4.1 二叉树

有这样一张表

如果不给id字段添加索引，默认进行全表扫描，假设查询id=10的数据，那至少要进行10次磁盘IO。效率低。可以给id字段添加索引，假设该索引使用了二叉树这种数据结构，这个二叉树是这样的（推荐一个数据结构可视化网站Data Structure Visualizations，是旧金山大学（USFCA）的一个网站）：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

如果这个时候要找id=10的数据，需要的IO次数是？4次。效率显著提升了。
但是MySQL并没有直接使用这种普通的二叉树，这种普通二叉树在数据极端的情况下，效率较低。比如下面的数据：

如果给id字段添加索引，并且该索引底层使用了普通二叉树，这棵树会是这样的：

你虽然使用了二叉树，但这更像一个链表。查找效率等同于链表查询O(n)【查找算法的时间复杂度是线性的】。查找效率极低。
因此对于MySQL来说，它并没有选择这种数据结构作为索引。

4.2 红黑树（自平衡二叉树）

通过自旋平衡规则进行旋转，子节点会自动分叉为2个分支，从而减少树的高度，当数据有序插入时比二叉树数据检索性能更好。
例如有以下数据

给id字段添加索引，并且该索引使用了红黑树数据结构，那么会是这样：

如果查找id=10的数据，磁盘IO次数为：5次。效率比普通二叉树要高一些。

但是如果数据量庞大，例如500万条数据，也会导致树的高度很高，磁盘IO次数仍然很多，查询效率也会比较低。

因此MySQL并没有使用红黑树这种数据结构作为索引。

4.3 B Trees（B树）

B Trees中的B指的是：Balanced（平衡）

B Trees就是平衡树。

B Trees首先是一个自平衡的。
B Trees每个节点下的子节点数量 > 2。
B Trees每个节点中也不是存储单个数据，可以存储多个数据。
B Trees又称为平衡多路查找树。

B Trees分支的数量不是2，是大于2，具体是多少个分支，由阶决定。例如：

• 3阶的B Trees，一个节点下最多有3个子节点，每个节点中最多有2个数据。
• 4阶的B Trees，一个节点下最多有4个子节点，每个节点中最多有3个数据。
• 5阶（5, 4）
• 6阶（6, 5）
• …
• 16阶（16, 15）【MySQL采用了16阶】

采用B Trees，你会发现相同的数据量，B Tree 树的高度更低。磁盘IO次数更少。
3阶的B Trees：

假设id字段添加了索引，并且采用了B Trees数据结构，查找id=10的数据，只需要3次磁盘IO。
4阶的B Trees：

更加详细的存储是这样的，请看下图：


在B Trees中，每个节点不仅存储了索引值，还存储该索引值对应的数据行。
并且每个节点中的p1 p2 p3是指向下一个节点的指针。

B Trees数据结构存在的缺点是：不适合做区间查找，对于区间查找效率较低。假设要查id在[3~7]之间的，需要查找的是3,4,5,6,7。那么查这每个索引值都需要从头节点开始。
因此MySQL使用了B+ Trees解决了这个问题。

4.4 B+ Trees（B+ 树）

B+ Trees 相较于 B Trees改进了哪些？

• B+树将数据都存储在叶子节点中。并且叶子节点之间使用链表连接，这样很适合范围查询。
• B+树的非叶子节点上只有索引值，没有数据，所以非叶子节点可以存储更多的索引值，这样让B+树更矮更胖，提高检索效率。

假设有这样一张表：

B+ Trees方式存储的话如下图所示：

**经典面试题：**mysql为什么选择B+树作为索引的数据结构，而不是B树？

1. 非叶子节点上可以存储更多的键值，阶数可以更大，更矮更胖，磁盘IO次数少，数据查询效率高。
2. 所有数据都是有序存储在叶子节点上，让范围查找，分组查找效率更高。
3. 数据页之间、数据记录之间采用链表链接，让升序降序更加方便操作。

经典面试题： 如果一张表没有主键索引，那还会创建B+树吗？
当一张表没有主键索引时，默认会使用一个隐藏的内置的聚集索引（clustered index）。这个聚集索引是基于表的物理存储顺序构建的，通常是使用B+树来实现的。

5. 其他索引及相关调优

5.1 Hash索引

支持Hash索引的存储引擎有：

• InnoDB（不支持手动创建Hash索引，系统会自动维护一个自适应的Hash索引）
  • 对于InnoDB来说，即使手动指定了某字段采用Hash索引，最终show index from 表名的时候，还是BTREE。
• Memory（支持Hash索引）

Hash索引底层的数据结构就是哈希表。一个数组，数组中每个元素是链表。和java中HashMap一样。哈希表中每个元素都是key value结构。key存储索引值，value存储行指针。

原理如下：

如果name字段上添加了Hash索引idx_name

Hash索引长这个样子：

检索原理：假设 name=‘孙行者’。通过哈希算法将’孙行者’转换为数组下标，通过下标找链表，在链表上遍历找到孙行者的行指针。

注意：不同的字符串，经过哈希算法得到的数组下标可能相同，这叫做哈希碰撞/哈希冲突。【不过，好的哈希算法应该具有很低的碰撞概率。常用的哈希算法如MD5、SHA-1、SHA-256等都被设计为尽可能减少碰撞的发生。】

Hash索引优缺点：

• 优点：只能用在等值比较中，效率很高。例如：name=‘孙悟空’
• 缺点：不支持排序，不支持范围查找。

5.2 聚集索引和非聚集索引

按照数据的物理存储方式不同，可以将索引分为聚集索引（聚簇索引）和非聚集索引（非聚簇索引）。

存储引擎是InnoDB的，主键上的索引属于聚集索引。
存储引擎是MyISAM的，任意字段上的索引都是非聚集索引。

InnoDB的物理存储方式：当创建一张表t_user，并使用InnoDB存储引擎时，会在硬盘上生成这样一个文件：

• t_user.ibd （InnoDB data表索引 + 数据）
• t_user.frm （存储表结构信息）

MyISAM的物理存储方式：当创建一张表t_user，并使用MyISAM存储引擎时，会在硬盘上生成这样一个文件：

• t_user.MYD （表数据）
• t_user.MYI （表索引）
• t_user.frm （表结构）

注意：从MySQL8.0开始，不再生成frm文件了，引入了数据字典，用数据字典来统一存储表结构信息，例如：

• information_schema.TABLES （表包含了数据库中所有表的信息，例如表名、数据库名、引擎类型等）
• information_schema.COLUMNS（表包含了数据库中所有表的列信息，例如列名、数据类型、默认值等）

聚集索引的原理图：（B+树，叶子节点上存储了索引值 + 数据）

非聚集索引的原理图：（B+树，叶子节点上存储了索引值 + 行指针）

聚集索引的优点和缺点：

1. 优点：聚集索引将数据存储在索引树的叶子节点上。可以减少一次查询，因为查询索引树的同时可以获取数据。
2. 缺点：对数据进行修改或删除时需要更新索引树，会增加系统的开销。

5.3 二级索引

二级索引也属于非聚集索引。也有人把二级索引称为辅助索引。
有表t_user，id是主键。age是非主键。在age字段上添加的索引称为二级索引。（所有非主键索引都是二级索引）

二级索引的数据结构：

二级索引的查询原理：
假设查询语句为：

select * from t_user where age = 30;

为什么会“回表”？因为使用了select *
避免“回表【回到原数据表】”是提高SQL执行效率的手段。例如：select id from t_user where age = 30; 这样的SQL语句是不需要回表的。

5.4 覆盖索引

覆盖索引是指一个该索引包含了查询所需的所有列，不需要再去回表查询数据。当使用覆盖索引时，MySQL可以直接通过索引，也就是索引上的数据来获取所需的结果，而不必再去查找表中的数据。这样可以显著提高查询性能。

假设有一个用户表（user）包含以下列：id, username, email, age。

常见的查询是根据用户名查询用户的邮箱。如果为了提高这个查询的性能，可以创建一个覆盖索引，包含（username, email）这两列。

创建覆盖索引的SQL语句可以如下：

CREATE INDEX idx_user_username_email ON user (username, email);

当执行以下查询时：

SELECT email FROM user WHERE username = 'lucy';

MySQL可以直接使用覆盖索引（idx_user_username_email）来获取查询结果，而不必再去查找用户表中的数据。这样可以减少磁盘I/O并提高查询效率。而如果没有覆盖索引，MySQL会先使用索引（username）来找到匹配的行，然后再回表查询获取邮箱，这个过程会增加更多的磁盘I/O和查询时间。

值得注意的是，覆盖索引的创建需要考虑查询的字段选择。如果查询需要的字段较多，可能需要创建包含更多列的覆盖索引，以满足完全覆盖查询的需要。

使用覆盖索引有以下几个优点：

1. 提高查询性能：减少IO操作，加快查询速度。
2. 减少存储空间：覆盖索引只包含需要的列，可以减小索引的大小。
3. 减少锁的竞争：由于不需要回表操作，减少了对表的访问，减少了锁的竞争。

但是覆盖索引也有一些限制：

1. 创建覆盖索引会增加索引的大小，可能会占用更多的存储空间。
2. 更新数据时，需要同时更新索引，可能会增加写操作的成本。

因此，在设计数据库索引时，需要权衡覆盖索引所带来的性能提升和存储成本，并根据具体的查询需求进行选择。

5.5 索引下推

索引下推是MySQL查询优化器的一种优化技术，它的目标是减少不必要的IO操作和减少查询的数据量，提高查询性能。

在MySQL中，当使用多列索引时，如果查询条件只涉及到了索引的一部分列，传统的查询优化器会将所有符合索引的记录都读入内存，然后再进行筛选。而索引下推则可以在索引的扫描过程中，对查询条件进行判断，只将符合条件的记录加载到内存中，减少不必要的IO和数据传输。

索引下推的过程分为两个阶段：

1. 索引范围扫描：首先，根据查询条件的前缀，定位到索引上的一个起始位置，然后按照索引的顺序逐个查找符合条件的记录，直到找到一个不满足条件的记录为止。
2. 回表操作：在索引扫描的过程中，对于满足条件的记录，需要回到原数据表中找到完整的行数据，以返回给查询结果。

索引下推的好处是可以减少不必要的IO和数据传输，提高查询性能。但它并不是适用于所有情况，对于某些查询条件较为复杂的情况，使用索引下推可能会导致性能下降。因此，在实际使用中，需要根据具体情况进行评估和测试，选择合适的优化方式。

假设有以下表结构：

表名：users

id	name	age	city
1	John	25	New York
2	Alice	30	London
3	Bob	40	Paris
4	Olivia	35	Berlin
5	Michael	28	Sydney

现在我们创建了一个多列索引：

ALTER TABLE users ADD INDEX idx_name_city_age (name, city, age);

假设我们要查询年龄大于30岁，并且所在城市是"London"的用户，传统的查询优化器会将所有满足年龄大于30岁的记录读入内存，然后再根据城市进行筛选。

使用索引下推优化后，在索引范围扫描的过程中，优化器会判断只有在城市列为"London"的情况下，才会将满足年龄大于30岁的记录加载到内存中。这样就可以避免不必要的IO和数据传输，提高查询性能。

具体的查询语句可以是：

SELECT * FROM users WHERE age > 30 AND city = 'London';

在执行这个查询时，优化器会使用索引下推技术，先根据索引范围扫描找到所有满足条件的记录，然后再回到原数据表中获取完整的行数据，最终返回结果。

通过使用索引下推优化技术，可以减少不必要的数据加载和IO操作，提高查询性能。

在一般情况下，索引下推是MySQL优化器自动处理的，并不需要程序员进行干预。

MySQL优化器会根据查询条件和索引的定义，自动决定是否使用索引下推优化技术。当条件满足索引下推的使用场景时，优化器会自动选择使用索引下推。这个决策是根据优化器的统计信息和查询的成本估算来进行的。

然而，对于某些特殊情况，MySQL优化器可能无法正确判断是否使用索引下推，或者错误地选择了不适合的优化策略。在这种情况下，程序员可以通过使用查询提示语句（Query Hints）来干预优化器的决策，强制使用或者禁用索引下推。

例如，使用查询提示语句来强制使用索引下推：

SELECT /*+ INDEX_MERGE(users idx_name_city_age) */ * FROM users WHERE age > 30 AND city = 'London';

或者使用查询提示语句来禁用索引下推：

SELECT /*+ NO_INDEX_MERGE(users) */ * FROM users WHERE age > 30 AND city = 'London';

这样，程序员可以在特定情况下，根据实际需求对索引下推的使用进行干预。但需要注意的是，不正确的干预可能会导致性能下降，因此在使用查询提示语句时，需要进行充分的测试和评估。

5.6 单列索引（单一索引）

单列索引是指对数据库表中的某一列或属性进行索引创建，对该列进行快速查找和排序操作。单列索引可以加快查询速度，提高数据库的性能。

举个例子，假设我们有一个学生表（student），其中有以下几个列：学生编号（student_id）、姓名（name）、年龄（age）和性别（gender）。

如果我们针对学生表的学生编号（student_id）列创建了单列索引，那么可以快速地根据学生编号进行查询或排序操作。例如，我们可以使用以下SQL语句查询学生编号为123456的学生信息：

SELECT * FROM student WHERE student_id = 123456;

由于我们对学生编号列建立了单列索引，所以数据库可以直接通过索引快速定位到具有学生编号123456的那一行记录，从而加快查询速度。

5.7 复合索引（组合索引）

复合索引（Compound Index）也称为多列索引（Multi-Column Index），是指对数据库表中多个列进行索引创建。

与单列索引不同，复合索引可以包含多个列。这样可以将多个列的值组合起来作为索引的键，以提高多列条件查询的效率。

举个例子，假设我们有一个订单表（Order），其中包含以下几个列：订单编号（OrderID）、客户编号（CustomerID）、订单日期（OrderDate）和订单金额（OrderAmount）。

如果我们为订单表的客户编号和订单日期这两列创建复合索引（CustomerID, OrderDate），那么可以在查询时同时根据客户编号和订单日期来快速定位到匹配的记录。

例如，我们可以使用以下SQL语句查询客户编号为123456且订单日期为2021-01-01的订单信息：

SELECT * FROM Order WHERE CustomerID = 123456 AND OrderDate = '2021-01-01';

由于我们为客户编号和订单日期创建了复合索引，数据库可以使用这个索引来快速定位到符合条件的记录，从而加快查询速度。复合索引的使用能够提高多列条件查询的效率，但需要注意的是，复合索引的创建和维护可能会增加索引的存储空间和对于写操作的影响。

重点：最左前缀原则
最左前缀原则（Leftmost Prefix Rule）是指在复合索引中，索引的最左边的列会被优先使用。

在一个复合索引中，每一个索引键都包含了多个列的值。当查询中使用了复合索引的一部分列作为条件时，最左前缀原则会发挥作用。

简单来说，如果复合索引是按照列A、列B和列C的顺序创建的，那么当查询中使用了列A和列B作为条件时，这个复合索引会被使用。但是如果只使用了列B和列C作为条件，而没有使用列A，那么这个复合索引将无法被使用。

举个例子，假设我们有一个商品表（Product），其中包含以下几个列：商品名称（ProductName）、商品分类（Category）、商品价格（Price）和库存量（Stock）。

如果我们为商品表的商品分类和商品价格这两列创建复合索引（Category, Price），那么可以在查询时同时根据商品分类和商品价格来快速定位到匹配的记录。

例如，我们可以使用以下SQL语句查询商品分类为"电子产品"且价格大于1000的商品信息：

SELECT * FROM Product WHERE Category = '电子产品' AND Price > 1000;

由于我们为商品分类和商品价格创建了复合索引，数据库可以使用这个索引来快速定位到符合条件的记录。

然而，如果我们只是使用了商品价格作为条件，而没有使用商品分类，例如：

SELECT * FROM Product WHERE Price > 1000;

虽然我们创建了（Category, Price）的复合索引，但由于没有使用最左边的列（Category），这个复合索引不能被使用，查询的效率可能会降低。

因此，在设计复合索引时，需要根据查询的实际情况和需求考虑最左前缀原则，确保复合索引能够被查询所使用，从而提高查询性能。

注意：
最左边列在编写 SQL 查询语句时的位置是有要求的。

根据最左前缀原则，在 SQL 查询语句的 WHERE 子句中，最左边的条件列必须按顺序出现在其他条件列之前。

举个例子，假设我们有一个复合索引（Column1, Column2, Column3），其中 Column1 是最左边的列。

当我们编写查询语句时，如果要使用复合索引，必须在 WHERE 子句中将使用到的条件列按照索引的顺序写出来，并且最左边的条件列必须写在其他条件列之前。

例如，以下是一个符合最左前缀原则的查询语句：

SELECT * FROM TableName WHERE Column1 = 'Value1' AND Column2 = 'Value2' AND Column3 = 'Value3';

在这个查询语句中，我们按照复合索引的顺序，首先使用了最左边的条件列 Column1，然后是 Column2，最后是 Column3。这样，数据库系统可以根据复合索引快速定位到符合条件的记录。

然而，如果我们改变了条件列的顺序，使最左边的条件列不在最前面，例如：

SELECT * FROM TableName WHERE Column3 = 'Value3' AND Column2 = 'Value2' AND Column1 = 'Value1';

在这个查询语句中，虽然我们还是使用了复合索引的所有条件列，但最左边的条件列 Column1 现在在其他条件列之后。根据最左前缀原则，这个复合索引将无法被使用，查询的性能可能会受到影响。

因此，在编写 SQL 查询语句时，需要注意保持最左边的条件列按照复合索引的顺序，并且放在其他条件列之前，以利用最左前缀原则提高查询性能。

相对于单列索引，复合索引有以下几个优势：

1. 减少索引的数量：复合索引可以包含多个列，因此可以减少索引的数量，减少索引的存储空间和维护成本。
2. 提高查询性能：当查询条件中涉及到复合索引的多个列时，数据库可以使用复合索引进行快速定位和过滤，从而提高查询性能。
3. 覆盖查询：如果复合索引包含了所有查询需要的列，那么数据库可以直接使用索引中的数据，而不需要再进行表的读取，从而提高查询性能。
4. 排序和分组：由于复合索引包含多个列，因此可以用于排序和分组操作，从而提高排序和分组的性能。

6. 索引的优缺点

索引是数据库中一种重要的数据结构，用于加速数据的检索和查询操作。它的优点和缺点如下：

优点：

1. 提高查询性能：通过创建索引，可以大大减少数据库查询的数据量，从而提升查询的速度。
2. 加速排序：当查询需要按照某个字段进行排序时，索引可以加速排序的过程，提高排序的效率。
3. 减少磁盘IO：索引可以减少磁盘IO的次数，这对于磁盘读写速度较低的场景，尤其重要。
4. 提供唯一性约束：可以通过创建唯一索引来确保某个字段的值是唯一的，避免数据重复插入。

缺点：

1. 占据额外的存储空间：索引需要占据额外的存储空间，特别是在大型数据库系统中，索引可能占据较大的空间。
2. 增删改操作的性能损耗：每次对数据表进行插入、更新、删除等操作时，需要更新索引，会导致操作的性能降低。
3. 资源消耗较大：索引需要占用内存和CPU资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响。
4. 索引可能会过期：当表中的数据发生变化时，索引可能会过期，需要额外的处理来保持索引的一致性。

7. 何时用索引

在以下情况下建议使用索引：

1. 频繁执行查询操作的字段：如果这些字段经常被查询，使用索引可以提高查询的性能，减少查询的时间。
2. 大表：当表的数据量较大时，使用索引可以快速定位到所需的数据，提高查询效率。
3. 需要排序或者分组的字段：在对字段进行排序或者分组操作时，索引可以减少排序或者分组的时间。
4. 外键关联的字段：在进行表之间的关联查询时，使用索引可以加快关联查询的速度。

在以下情况下不建议使用索引：

1. 频繁执行更新操作的表：如果表经常被更新数据，使用索引可能会降低更新操作的性能，因为每次更新都需要维护索引。
2. 小表：对于数据量较小的表，使用索引可能并不会带来明显的性能提升，反而会占用额外的存储空间。
3. 对于唯一性很差的字段，一般不建议添加索引。当一个字段的唯一性很差时，查询操作基本上需要扫描整个表的大部分数据。如果为这样的字段创建索引，索引的大小可能会比数据本身还大，导致索引的存储空间占用过高，同时也会导致查询操作的性能下降。

总之，索引需要根据具体情况进行使用和权衡，需要考虑到表的大小、查询频率、更新频率以及业务需求等因素。