MySQL索引分类和操作（增删查）、聚集索引、二级索引（索引篇 二）

具体类型索引分类

分类	主要作用	特点
主键索引(primary)	针对于表中主键创建的索引	默认自动创建, 只能有一个
唯一索引(unique)	避免同一个表中某数据列中的值重	可以有多个
常规索引	最基本类型，可以加快查询速度	可以有多个
全文索引(fulltext)	查找的是文本中的关键词，而不是比较索引中的值	可以有多个
组合索引	由多个列组成的索引，可以根据多个列进行搜索	可以有多个

索引操作语法

查看索引：

show index from 表名;

创建索引：

create  [unique | fulltext] index 索引名 on 表名(列名1,列名2, ...);

删除索引：

drop index 索引名 on 表名;

修改的话：先删除再创建

---------------------------------

InnoDB存储引擎中，根据索引的存储形式，可以分为以下两种：

聚集索引（Clustered Index）：

在InnoDB中，表数据的物理存储顺序与聚集索引的顺序一致。每个InnoDB表只能有一个聚集索引，通常是主键索引(根据主键id查询数据)。

聚集索引将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。（查找数据过程简单理解就是1颗B+树结构数据查找过程，B+树结构链接：B+树结构）

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（unique）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索
• 引。

----------------------------------

二级索引（Secondary Index），也叫非聚集索引：

二级索引的叶子节点存储了索引列的值和对应的聚集索引键值，通过二级索引可以快速定位到对应的聚集索引位置，然后获取到相应的数据行。

它的查询过程分为两步，第一步根据索引列找到聚集索引的键值范围，第二步通过聚集索引查询到对应叶子节点数据（也就是这一行数据），第二步也叫做回表查询。（简单理解就是用到2颗B+树，通过二级索引B+树查到聚集索引的键值范围，然后回到聚集索引B+树查找数据）

回表查询：

先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。回表查询的过程会增加额外的I/O操作，这会导致查询的性能下降。 为了减少回表查询的次数，可以使用覆盖索引。

---------------------------------------

覆盖索引（Covering Index）

覆盖索引是指二级索引包含了查询所需的所有列（该列数据），因此可以直接从索引中获取所需的数据，而无需回表到聚集索引。覆盖索引的数据结构与其他索引类型相似，它存储了索引列的值以及对应的行指针（就是B+树）。

当我们创建一个覆盖索引时，MySQL会将索引列的值和对应的行指针存储在索引结构中。当执行查询时，MySQL会首先检查覆盖索引，如果索引中包含了查询所需的所有列，就可以直接从索引中读取数据，而无需回表操作。

使用覆盖索引可以提高查询性能，因为它减少了回表操作和额外的I/O操作。但是，覆盖索引适用于查询较少的列，如果需要查询的列较多，可能会导致索引变得庞大，影响性能。

上一篇：MySQL 索引的作用、索引结构及执行流程介绍（索引篇 一）

下一篇：MySQL 索引失效情况（索引篇 三）