MYSQL——索引分类&索引语法

索引分类

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

当你在创建表的时候，给某个字段加上主键约束，实际上就是在这个字段上创建了一个主键索引。
给某个字段加上唯一约束，实际上就是在这个字段上创建了一个唯一索引

总的来说，主键索引和唯一索引是MySQL中的索引类型，而主键约束和唯一约束则是数据库表的约束条件。当你在数据库表中设置主键约束或唯一约束时，实际上就是在对应的字段上创建了主键索引或唯一索引。

聚集索引&二级索引

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

聚集索引

聚集索引的作用是保证数据的唯一性，即表中每一行的主键值都是唯一的。
聚集索引的叶子节点存储的是整行数据，其特点是能够快速找到一条记录，用于确定主键和整张表的存储顺序。

聚集索引的叶子节点包含所有用户记录（包括隐藏列），每层节点都是按照主键从小到大排序。
聚集索引的存储并不是物理上的连续，而是逻辑上的连续。这是因为页通过双向链表链接，页按照主键的顺序排序，每个页中的记录是通过单向链表进行维护的

二级索引

二级索引也称为非聚集索引，它是除主键外其他字段的索引。在MySQL中，每一个非聚集索引对应一个B+树。

在查询时，系统先通过二级索引找到主键，然后再通过主键找到数据记录。这就意味着二级索引的查询效率低于聚集索引

聚集索引的选取规则

1. 聚集索引就是主键索引。
2. 如果表中没有定义主键，那么会选择第一个不允许为NULL的唯一索引作为聚集索引。
3. 如果表中既没有定义主键，也没有定义唯一索引，那么数据库管理系统会自动生成一个隐藏的rowid作为聚集索引。

回表查询

指在数据库查询过程中，由于某些原因，不能直接通过索引获取所需数据，需要先通过索引找到主键或其它标识符，然后再通过这些标识符在原表中查找实际数据的过程。

先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

举一个例子，假设我们有一个名为students的表，其中包含学生的ID、姓名、年龄等信息，并且ID被设置为主键索引。如果我们想要查询某个特定姓名的学生信息，可能会用到回表查询。首先，我们可以通过姓名这一非主键字段在普通索引中找到对应的ID，然后我们再通过ID这一主键在聚集索引中找到完整的行数据。在这个过程中，我们就进行了回表查询。

在实际开发中，我们应该尽可能地避免回表查询.

索引语法

创建索引

在MySQL中，你可以使用CREATE INDEX语句或者ALTER TABLE语句来创建索引。以下是这两种方法的示例：

使用CREATE INDEX语句创建索引

CREATE INDEX index_name ON table_name (column_list);

其中，index_name是你为索引指定的名称，table_name是你要为其创建索引的表名，而column_list是你想要索引的列名，多个列名之间用逗号分隔。

使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

同样，index_name是索引的名称，table_name是表名，column_list是列名列表。。

删除索引

你可以使用DROP INDEX语句或者ALTER TABLE语句来删除索引。以下是这两种方法的示例：

使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

这里，index_name是你想要删除的索引的名称，而table_name是包含该索引的表名。

使用ALTER TABLE语句删除索引

ALTER TABLE table_name DROP INDEX index_name;

这里的含义和DROP INDEX语句相同。

查询索引

要查询MySQL中的索引信息，你可以使用SHOW INDEX FROM语句，如下所示：

SHOW INDEX FROM table_name;

其中，table_name是你想要查询索引信息的表名。