MySQL 中的索引

一、索引的创建和删除
- 1.主键会自动添加索引
- 2.unique 约束的字段自动添加索引
- 3.给指定的字段添加索引
- 4.删除指定索引
- 5.查询表上的索引
二、索引的分类
三、MySQL索引采用了B+树数据结构
- 1.B+树的经典面试题
四、其他索引及相关调优
- 1.Hash索引
- 2.聚集索引和非聚集索引
- 3.二级索引
- 4.覆盖索引
- 5.索引下推
- 6.单列索引（单一索引）
- 7.复合索引（组合索引）
五、索引的优缺点
六、何时使用索引

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

一、索引的创建和删除

1.主键会自动添加索引

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

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

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

3.给指定的字段添加索引

建表时添加索引：

drop table if exists test;
create table test (
	id int primary key auto_increment,
	name varchar(255),
	index index_name(name)
);

如果表已经建好了，后期给字段添加索引：
```
alter table test add index index_age (age);
```
直接创建索引：
```
create index index_age on test(age);
```

4.删除指定索引

删除索引：

alter table test drop index index_name;

5.查询表上的索引

查询指定表上的所有索引：
```
show index from test;
```

二、索引的分类

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

三、MySQL索引采用了B+树数据结构

关于这部分知识可以参考我博客===>查找中常见的树数据结构

1.B+树的经典面试题

经典面试题： MySQL为什么选择B+树作为索引的数据结构，而不是B树？
1. 非叶子节点上可以存储更多的键值，阶数可以更大，更矮胖，磁盘IO次数少，数据查询效率高。
2. 所有数据都是有序存储在叶子节点上的，让范围查找，分组查找效率更高。
3. 数据页之间、数据记录之间采用指针链接，让升序降序更加方便操作。
经典面试题： 如果一张表没有主键索引，那还会创建B+树吗？
- 当一张表没有主键索引时，默认会使用一个隐藏的内置的聚集索引（clustered index）。这个聚集索引是基于表的物理存储顺序构建的，通常是使用B+树实现的。

四、其他索引及相关调优

1.Hash索引

支持Hash索引的存储器引擎有：
- InnoDB（不支持手动创建Hash索引，系统会自动维护一个自适应的Hash索引）
  - 对于 InnoDB 来说，即使手动指定了某字段采用Hash索引，最终show index from 表明的时候，还是BTREE。
- Memory（支持Hash索引）。
Hash索引底层的数据结构就是哈希表。一个数组，数组中每个元素是链表。和Java中的HashMap一样。哈希表中每个元素都是key value结构。key存储索引值，value存储行指针。
注意：不同的字符串，经过哈希算法得到的数组下标可能相同，这种叫做哈希碰撞/哈希冲突。【不过，好的哈希算法应该具有很低的碰撞概率。常用的哈希算法如 MD5、SHA-1、SHA-256 等都被设计为尽可能减少碰撞的发生。】
Hash索引的优缺点：
- 优点：只能用在点查询中效率很高。例如：age=10。
- 缺点：不支持排序，不支持范围查找。

2.聚集索引和非聚集索引

按照数据的物理存储方式不同，可以将索引分为聚集索引（聚簇索引）和非聚集索引（非聚簇索引）。
- 存储引擎是InnoDB的，主键上的索引属于聚集索引。
  - InnoDB的物理存储方式：当创建一张表user，并使用InnoDB存储引擎，会在硬盘上生成这样的文件：
    - user.ibl（InnoDB data表索引+数据）
    - user.frm（存储表结构信息）
- 存储引擎是MyISAM的，任意字段上的索引都是非聚集索引。
  - MyISMA的物理存储方式：当创建一张表user，并使用MyISAM存储引擎，会在硬盘上生成这样的文件：
    - user.MYD（表数据）
    - user.MYI（表索引）
    - user.frm（表结构）
- 注意：从 MySQL8.0开始，不再生成frm文件了，引入了数据字典，用数据字典来统一存储表结构信息，例如：
  - information_schema.TABLES（表包含了数据库中所有表的信息，例如表名、数据库名、存储引擎类型等。）
  - information_schema.COLUMNS（表包含了数据库中所有表的列信息，例如列名、数据类型、默认值等。）
聚集索引的优点和缺点：
- 优点：聚集索引将数据存储到索引树的叶子节点上。可以减少一次查询，因为查询索引树的同时可以获取数据。
- 缺点：对数据进行修改或删除时需要更新索引树，会增加系统的开销。

3.二级索引

二级索引也属于非聚集索引。也有人把二级索引称为辅助索引。

4.覆盖索引

覆盖索引（Covering Index），顾名思义，是指某个查询语句可以通过索引的覆盖来完成，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中提取到，而不需要再去查询实际数据行获取查询所需数据。
假设有一个用户表（user）包含以下列：id，username，email，age。
常见的查询是根据用户名查询用户的邮箱。如果为了提高这个查询的性能，可以创建一个覆盖索引，包含（username，email）这两列。

创建覆盖索引：

create index index_uername_email on user(username, email);

当执行以下查询时：

select email from user where username='jack';

MySQL可以直接使用覆盖索引（index_username_email）来获取查询结果，而不必再去查找用户表中的数据。这样可以减少磁盘IO并提高查询效率。而如果没有覆盖索引，MySQL会先使用索引（username）来匹配行，然后再回到表查询获取email，这个过程会增加更多的磁盘IO和查询时间。
值得注意的是：覆盖索引的创建需要考虑查询的字段选择。如果查询需要的字段较多，可能需要创建包含更多列的覆盖索引，以满足完全覆盖查询的需要。

5.索引下推

索引下推（Index Condition Pushdown）是一种MySQL中的优化方法，它可以将查询中的过滤条件下推到索引层级中处理，从而减少回表次数，优化查询性能。
具体来说，在使用索引下推时，MySQL

6.单列索引（单一索引）

单一索引是指对数据库表中的某一列或属性进行索引创建，对该列进行快速查找和排序操作。单一索引可以加快查询速度，提高数据库的性能。
例如：假设我们有一个学生表（student），其中有以下几个列：学生编号（stu_id）、姓名（name）、年龄（age）和性别（sex）。
如果我们针对学生表的信息编号（stu_id）列创建单列索引，那么可以快速地根据学生编号进行查询或者排序操作。例如，我们可以使用下面的SQL语句查询学生编号为123456的学生信息：
```
select * from student where stu_id='123456';
```
由于我们对学生编号建立了单一索引，所以数据库可以直接通过索引快速定位到具有学生123456的那一行记录，从而加快查询速度。

7.复合索引（组合索引）

在这里插入图片描述

五、索引的优缺点

索引是数据库中一种重要的数据结构，用于加速数据的检索和查询操作。它的优点和缺点如下:
优点：
1. 提高查询性能：通过创建索引，可以大大减少数据库查询的数据量，从而提高查询的速度。
2. 加速排序：当查询需要按照某个字段进行排序时，索引可以加速排序的过程，提高排序的效率。
3. 减少磁盘IO：索引可以减少磁盘IO的次数，这对于磁盘读写速度较低的场景，尤其重要。
缺点：
1. 占据额外的存储空间：索引需要占据额外的存储空间，特别是在大型数据库系统中，索引可能占据较大的空间。
2. 增删改操作的性能损耗：每次对数据表进行插入、更新、删除等操作时，需要更新索引，会导致操作的性能降低。
3. 资源消耗较大：索引需要占用内存和CPU资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响。

六、何时使用索引

在以下情况下建议使用索引：
1. 在需要经常搜索的列上创建索引。
2. 主键上创建索引（MySQL中主键自动创建索引）。
3. 经常用于连接的列上创建索引。
4. 经常需要根据范围查询进行搜索的列上创建索引。
5. 在 Where 查询子句中引用效率高的列上创建索引。
6. 在 Order by 和 Group by 子句中出现的列上创建索引。
7. 大表：当表的数据量较大时，使用索引可以快速定位到所需的数据，提高查询效率。
- 注意：在某一范围内频繁搜索的属性，只有在当使用索引的查询结果不超过总记录的20%时才有明显效果
在以下情况下不建议使用索引：】
1. 频繁执行更新操作的表：如果表经常被更新数据，使用索引可能会降低更新操作的性能，因为每次更新都需要维护索引。
2. 小表：对于数据量小的表，使用索引可能并不会带来明显的性能提升，反而会占用额外的存储空间。
3. 对于唯一性很差的字段，一般不建议添加索引。当一个字段的唯一性很差时，查询操作基本上需要扫描表的大部分数据。如果为这样的字段创建索引，索引的大小可能会比数据本身还大，导致索引的存储空间占用过高，同时也会导致查询操作的性能下降。
总之，索引需要根据具体情况进行使用和权衡，需要考虑到表的大小、查询频率、更新频率以及业务需求等多方面的因素。