一、什么是索引

索引是一种用于提高数据库表中数据检索效率的数据结构，将原本无规则一行一行排列的原始数据按照特定的数据结构排列起来而形成一个新的排序+原始数据的结构。这种数据结构主要以「平衡树」(非二叉)，也就是b tree或者 b+ tree为主，当然有的数据库也使用哈希桶作用索引的数据结构。

二、索引的作用

1. 提高查询速度‌：如果没有索引，通常会全表扫描数据，如果表的数据非常大的话，一条一条的去匹配的话，最坏的情况下需要匹配O(n)最坏时间复杂度；而通过索引的话，不需要全表扫描，一般只需要O(logn)次就可以定位到具体的数据，大大减少了查询速度。
2. 加速排序和分组‌：索引可以优化ORDER BY和GROUP BY操作，MySQL可以利用索引中已经排序的数据，避免额外的排序操作‌。
3. ‌优化连接查询‌：索引还可以提高JOIN操作的效率，使得连接查询更加快速‌。
4. 管理数据库约束。索引通常还会用于数据库约束，例如：UNIQUE，PRIMARY KEY，FOREIG KEY，当一个索引被定义成UNIQUE时，数据库同时创建一个隐式的约束。

三、索引的缺点

• 索引本身需要存储空间，特别是对于大型表，索引文件可能会占用大量的磁盘空间，从而增加了存储成本‌；
• 索引会影响写操作的性能。当对表进行插入、更新或删除操作时，索引也需要相应地进行维护，这会增加这些操作的时间开销。在高并发写入的场景下，性能可能会显著下降‌；
• 索引的维护也需要额外的开销。随着数据的不断变化，索引可能会变得不再高效，需要定期进行重建或优化。在数据频繁变化的场景下，维护索引可能会成为性能瓶颈‌。

四、索引的创建与分类

4.1 索引的创建方式:

--1、主键索引：即根据主键pk_clolum（length）建立索引，不允许重复；
ALTER TABLE table_name ADD PRIMARY KEY index_name(id);

--2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值；
ALTER TABLE table_name ADD UNIQUE index_name(col1(10));

--3、普通索引：用表中的普通列构建的索引，没有任何限制；
ALTER TABLE table_name ADD INDEX index_name(col2(20);

--4、全文索引：用大文本对象的列构建的索引；
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(col3(255));

--5、组合索引：用多个列组合构建的索引；
ALTER TABLE table_name ADD INDEX index_name(col1,col2,col3)；

--删除索引
ALTER  TABLE  table_name DROP  INDEX  index_name;

4.2 索引分类：

在MySQL中，InnoDB存储引擎中，索引主要分为聚集索引（Clustered Index）和非聚集索引（Non-Clustered Index，也称为二级索引Secondary Index），两者主要的区别如下：

1. 数据存储方式：
   • 聚集索引：叶子节点存储的是完整的数据行。
   • 非聚集索引：叶子节点存储的是主键值（对于InnoDB）或指向数据行的物理地址（对于MyISAM）。
2. 数量限制：
   • 聚集索引：一个表只能有一个聚集索引。
   • 非聚集索引：一个表可以有多个非聚集索引。
3. 查询性能：
   • 聚集索引：对于主键的查询速度很快，因为直接通过叶子节点就可以获取数据。
   • 非聚集索引：通过非聚集索引查询需要先找到主键值，然后再通过聚集索引（在InnoDB中）查找数据行，这个过程称为回表。因此，除非查询只需要索引列（覆盖索引），否则需要两次索引查找。
4. 索引键值：
   • 聚集索引的键值决定了数据行的物理存储顺序。
   • 非聚集索引的键值不影响数据行的物理存储顺序。

五、索引的底层数据结构

索引常用的数据结构主要有B-tree（平衡树）、B+tree、Hashes（哈希）。

5.1 B+tree与B-tree的主要区别在于：

1. 非叶子节点存储内容的不同‌：

   • B+tree中，非叶子节点只存储索引键值和指向子节点的指针；
   • B-tree中，非叶子节点除了存储索引键值、指向子节点的指针外还会存储数据指针或直接存储数据。

2. 树的高度差异‌：

   • B+Tree非叶子节点只存键值，每个节点能存储更多键值，导致B+tree的树高度更低。
   • B-Tree非叶子节点会额外存储数据 ，每个节点存储的键值更少，导致B-Tree的树高度也更高。

3. 查询性能的差异‌：

   • B+Tree树高度比B-Tree的更低，查询时的IO次数更少，所以B+Tree查询性能也更好。
   • B+Tree叶子节点形成有序链表，范围查询只需要遍历叶子节点链表；而B-Tree需要多次树遍历，所以B+Tree范围查询性能也更好。
   • B+Tree所有查询都要到叶子节点，查询路径长度固定 = 树的高度；而B-Tree可能在非叶子节点就找到数据，路径长度不固定，越是靠近根节点就能越快查询到数据，所以B+Tree查询性能更稳定。

5.2 InnoDB索引实现：

InnoDB引擎使用B+Tree作为索引实现，所有的物理数据都会存储在主键索引的叶子节点上，通过主键索引可以直接在叶子节点上获取到具体的物理数据。而通过普通索引在叶子节点上只能拿到获取具体数据的主键id，然后再通过主键去查找主键索引，最终从主键索引的叶子节点上找到具体的物理数据。下面借用网上的图片作为参考：
InnoDB的主键索引图：
InnoDB的二级索引图：

5.3 MyISAM索引实现：

MyISAM引擎也是使用B+Tree作为索引实现，并且所有的索引都是非聚集索引。在MyISAM引擎中都是先通过索引找到指向具体数据的指针，再通过指针找到具体的数据。下面同样借用网上的图片来体现下：
MyISM的主键索引：
MyISAM的普通索引：

六、联合索引

6.1 创建联合索引

CREATE INDEX IDX_XXX ON TABLE(COL3, COL2);

联合索引在查找的时候，先根据最左边的字段（COL3）去查，找到符合最左边的字段的条件数据后，再从得到的数据中，根据第二个字段（COL2）去查，最后找到最终符合的联合索引条件的数据。比如要找 Alice，34 这条记录，根据联合索引的筛选条件WHERE COL3 = ‘Alice’ AND COL2 = 34

1. 先根据COL3 = 'Alice’的筛选条件在联合索引中进行查找key为Alice的结果，找到了2条记录，34和77；
2. 在根据COL2 = 34的筛选条件从1中查出的结果数据中查找key为34的结果，找到了1条记录 ，最后再根据这条记录的主键去主键索引中查找具体的数据。

6.2 联合索引最左匹配原则

联合索引最左原则的原理是：在创建联合索引时，索引的排序和存储是按照从左到右的顺序进行的，因此在查询时也必须遵循这一顺序，从联合索引的最左边开始匹配，才能有效利用索引‌。如果查询条件中不包含最左列，那么索引将无法被有效利用，可能导致查询性能下降‌。

假设我们有一个表test，其中包含列col1、col2和col3，并且我们为这三列创建了一个联合索引KEY test_col1_col2_col3 on test(col1,col2,col3)‌。分析下几种查询情况：

1. 查询语句1‌：SELECT * FROM test WHERE col1=“1”
   • 这个查询条件中包含了联合索引的最左列col1，因此可以充分利用索引进行查询，查询性能会较高‌.。
2. ‌查询语句2‌：SELECT * FROM test WHERE col1=“1” AND col2=“2”
   • 这个查询条件中包含了联合索引的前两列col1和col2，同样可以充分利用索引进行查询‌。
3. 查询语句3‌：SELECT * FROM test WHERE col1=“1” AND col2=“2” AND col3=“3”
   • 这个查询条件中包含了联合索引的所有列col1、col2和col3，索引将被完全利用，查询性能会达到最佳‌。
4. 查询语句4‌：SELECT * FROM test WHERE col2=“2” AND col1=“1” AND col3=“3”
   • 这个查询仍然可以利用联合索引进行查询，但可能无法完全发挥索引的效能‌。由于MySQL的查询优化器在处理查询时，会尝试对查询条件进行重排和优化，以便更有效地利用索引。
5. 查询语句5：SELECT * FROM test WHERE col2=“2”
   • 这个查询条件中只包含了联合索引的第二列col2，而没有包含最左列col1。因此，这个查询将无法有效利用联合索引，查询性能可能会较差‌。
6. 查询语句6：SELECT * FROM test WHERE col2=“2” AND col3=“3”
   • 同样，这个查询条件中也没有包含联合索引的最左列col1，因此索引同样无法被有效利用。
7. 查询语句7：SELECT * FROM test WHERE col1=“1” AND col3=“3”
   • 在这种情况下，col2就是断点，因为col2没有被使用在查询条件中。所以，只有col1发挥了索引效果，col3没有起到索引作用‌。

6.3 联合索引的优点

1. ‌减少存储空间‌：与单个索引相比，联合索引将多个列组合在一起，可以避免创建多个单独的索引，从而减少存储空间的消耗‌。
2. ‌提高数据完整性‌：联合索引可以为表中的多个列同时设置唯一性约束，确保数据的完整性和一致性。如果业务要求两个或更多列的组合必须唯一，那么联合索引是实现这一要求的理想选择‌。
3. ‌提高查询效率‌：联合索引可以有效减少数据库的扫描次数，当查询条件包含联合索引的所有列时，可以利用索引直接定位到目标记录，而无需回表查询，从而显著提高查询效率‌。
4. 降低索引维护开销‌：维护多个单独的索引需要较高的开销，而联合索引通过整合多个列到一个索引中，降低了索引的维护成本。当对表中的数据进行增加、删除和修改时，联合索引的维护效率通常高于多个单独索引‌。

七、回表

7.1 什么是回表？

回表指的是无法直接从索引里获取完整的结果，需要根据索引中的记录再回到原表里查找完整的行数据，这个过程就称为回表查询‌。回表都是发生在二级索引中，就是第一次通过二级索引无法直接获取到想要的数据，只是获取到了数据对应的主键值，然后通过主键值再次去主键索引中查找完整数据行。

7.2 什么情况下会触发回表？

例如，假设有一个员工表（employees），其中有主键id、姓名name和年龄age三个字段，且只在name字段上建立了普通索引。

1. 查询需要获取的列没有完全包含在二级索引中：
   • 例如：当执行查询SELECT name, age FROM employees WHERE name='张三'时，MySQL会首先通过name索引找到满足条件的主键id值。由于查询的字段中除了name之外的其他列（age）的数据，而这些数据并不能从二级索引中获取，所以需要通过主键id回到聚集索引中去查找age字段的数据。
2. WHERE 子句中过滤条件涉及的列没有完全包含在二级索引中：
   • 例如：当执行查询SELECT name FROM employees WHERE name='张三' and age=18时，假设表中有100条name为张三age不同的数据，首先可以在索引中找到这100条数据的id和name的值，但是还要找到具体age=18的数据，就必须通过主键id回到聚簇索引中去找出age=18的数据了。

7.3 回表会带来哪些影响？

1. 额外的 I/O 操作： 回表意味着至少多一次磁盘 I/O（从二级索引跳到聚簇索引查找）。如果通过二级索引查找到很多行（即使 WHERE 条件过滤后结果集不大，但如果二级索引扫描范围大），就需要进行多次回表操作，性能开销显著增加。
2. 性能下降： 相比于直接从聚簇索引获取数据或者使用覆盖索引，回表操作会明显降低查询速度，尤其是在数据量大、随机 I/O 多的情况下。
3. 增加 CPU 开销： 额外的索引查找和数据加载也会消耗更多的 CPU 资源。

7.4 如何识别是否发生了回表？

查看 SQL 语句的 EXPLAIN 输出：

• type 列： 显示 ref, range, index 等表示使用了索引扫描。
• key 列： 显示实际使用的索引名称（是一个二级索引）。
• Extra 列：
  • 如果出现 Using index，则表示查询所需的所有列都包含在使用的索引中（即覆盖索引），没有发生回表。
  • 如果出现 NULL 或者 其他信息（如 Using where）但 没有 Using index，则很可能发生了回表。Using where 表示在从存储引擎获取行后，还需要在 Server 层应用额外的 WHERE 条件过滤，这通常发生在回表拿到完整数据后。
  • 如果 Extra 列有 Using index condition (索引条件下推)，这发生在索引扫描阶段，但最终可能还是需要回表获取其他列。

7.5 如何避免回表？

1. 使用覆盖索引‌：
   • 覆盖索引是指索引中包含了查询所需的所有列，这样查询时就可以直接从索引中获取数据，而无需回表。例如，如果查询经常需要获取name和age列的数据，可以创建一个包含这两列的覆盖索引‌。