MySQL 中的索引

小宝945

已于 2024-03-25 14:58:07 修改

阅读量1k

点赞数 20

分类专栏：数据库文章标签： mysql 数据库

于 2024-03-23 00:03:26 首次发布

本文链接：https://blog.csdn.net/weixin_65032328/article/details/136919390

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

如果您看到了这篇博客并且对您有所帮助，麻烦给个评论🙏（第78篇博客，几乎没人给我评论😭）
如果内容存在任何的错误（内容错误，错别字等任何形式的错误，麻烦评论或者私信我改正🙏）
在此 小宝 感谢大家了

索引（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.二级索引

二级索引也属于非聚集索引。也有人把二级索引称为辅助索引。
二级索引的优点和缺点：
- 优点：
  1. 索引更新更高效：减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需要修改聚簇索引就可以了，不需要重新构建二级索引）
  2. 支持覆盖索引查询：由于二级索引存储的是主键值，而不是数据的内存地址，所以在某些情况下，可以通过二级索引直接获取到查询所需的数据，而不需要再去访问主键索引或数据页，从而提高查询效率。
- 缺点：
  1. 二级索引体积可能会变大，因为二级索引中存储了主键的信息。
  2. 二级索引的访问需要两次索引查找。第一次通过查找 二级索引 找二级索引中叶子节点存储的 主键的值；第二次通过这个主键的值去 聚簇索引 中查找对应的行。

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 会在索引的叶节点层执行查询的过滤条件，过滤掉无用的索引记录，仅返回符合条件的记录的主键，这样就可以避免查询时回表读取表格的数据行，从而缩短整个查询过程的时间。
假设有以下表结构：表名 user

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 user add index index_name_city_age (name, city ,age);
```
- 假设我们要查询年龄大于30岁，并且所在城市在“London”的用户，假设只给age字段添加索引，它就不会使用索引下推。传统的查询优化器会将所有满足年龄大于30岁的记录读入内存，然后再根据城市进行筛选。
- 使用索引下推优化后，在索引范围扫描的过程中，优化器会判断只有城市列为“London”的情况下，才会将满足年龄大于30岁的记录加载到内存中。这样就可以避免不必要的IO和数据传输，提高查询性能。
- 具体的查询语句可以是：
```
select * from user where age > 30 and city = 'London';
```
- 在执行这个查询时，优化器会使用索引下推技术，先根据索引范围扫描所有满足条件的记录，然后再回到原数据表中获取完整的行数据，最终返回结果。
通过使用索引下推优化技术，可以减少不必要的数据加载和IO操作，提高查询性能。
在一般情况下，索引下推是MySQL优化器自动处理的，并不需要程序员进行干预。
MySQL优化器会根据查询条件和索引的定义，自动决定是否使用索引下推优化技术。当条件满足索引下推的使用场景时，优化器会自动选择使用索引下推。这个决策是根据优化器的统计信息和查询的成本估算来进行的。

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

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

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

复合索引（Compound Index） 页称为多列索引（Multi-Column Index），是指对数据库表中多个列进行索引创建。
与单列索引不同，复合索引可以包含多个列。这样可以将多个列的值组合起来作为索引的键，以提高多列条件的查询的效率。
例如：我们有一个订单表（order），其中包含以下几个列：订单编号（order_id）、客户编号（customer_id）、订单日期（order_date）和订单金额（order_amount）。
- 如果我们为订单表的客户编号和订单日期这两列创建复合索引（customer_id，order_date），那么可以在查询同时根据客户编号和订单日期来快速定位到匹配的记录。
```
select * from order where customer_id = "21166" and order_date = '2021-01-01';
```
- 由于我们创建了复合索引，数据库可以使用这个索引来快速定位到符合条件的记录，从而加快查询速度。复合索引的使用能够提高多列条件的查询效率，但需要注意的是，复合索引的创建和维护可能会增加索引的存储空间和对于写操作的影响。

五、索引的优缺点

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

六、何时使用索引

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