MySQL高级【索引分类】

逃离我推掉我的手 2023-10-03 00:21 39阅读 0赞

**目录**

1：索引分类

1.1：索引分类

1.2：聚集索引&二级索引

2：索引语法

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

### 1：索引分类 ###

#### 1.1：索引分类 ####

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

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>分类</td> 
   <td>含义</td> 
   <td>特点</td> 
   <td>关键字</td> 
  </tr> 
  <tr> 
   <td>主键 索引</td> 
   <td>针对于表中主键创建的索引</td> 
   <td>默认自动创建, 只能 有一个</td> 
   <td>PRIMARY</td> 
  </tr> 
  <tr> 
   <td>唯一 索引</td> 
   <td>避免同一个表中某数据列中的值重复</td> 
   <td>可以有多个</td> 
   <td>UNIQUE</td> 
  </tr> 
  <tr> 
   <td>常规 索引</td> 
   <td>快速定位特定数据</td> 
   <td>可以有多个</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>全文 索引</td> 
   <td>全文索引查找的是文本中的关键词，而不是比 较索引中的值</td> 
   <td>可以有多个</td> 
   <td>FULLTEXT</td> 
  </tr> 
 </tbody> 
</table>

#### 1.2：聚集索引&二级索引 ####

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

<table style="width:536px;"> 
 <tbody> 
  <tr> 
   <td>分类</td> 
   <td>含义</td> 
   <td style="width:134px;">特点</td> 
  </tr> 
  <tr> 
   <td>聚集索引(Clustered Index)</td> 
   <td>将数据存储与索引放到了一块，索引结构的叶子 节点保存了行数据</td> 
   <td style="width:134px;">必须有,而且只 有一个</td> 
  </tr> 
  <tr> 
   <td>二级索引(Secondary Index)</td> 
   <td>将数据与索引分开存储，索引结构的叶子节点关 联的是对应的主键</td> 
   <td style="width:134px;">可以存在多个</td> 
  </tr> 
 </tbody> 
</table>

聚集索引选取规则:

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

聚集索引和二级索引的具体结构如下：

![a550d9e484434f5a9ecdbb5f42214087.png][]

*  聚集索引的叶子节点下挂的是这一行的数据 。
 *  二级索引的叶子节点下挂的是该字段值对应的主键值。

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

![69342decd5c64e3490099ac8100e7b38.png][]

具体过程如下:

①. 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查 找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是\*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最 终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

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

思考题： **以下两条SQL语句，那个执行效率高? 为什么?**

A. select \* from user where id = 10 ;

B. select \* from user where name = 'Arm' ;

备注: id为主键，name字段创建的有索引；

解答： **A 语句的执行性能要高于B 语句。 因为A语句直接走聚集索引，直接返回数据。因为聚集索引在建立B+TREE数据结构中，B+TREE叶子节点保存了对应的索引和挂载的这一行的数据，因此在查询的时候直接就可以在虚拟的索引表中取出对应的数据！**

**B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。因为二级索引在建立索引结构的时候，在叶子节点会保存对应的索引和该字段值对应的主键值。**

### 2：索引语法 ###

1). 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
    index_col_name,... ) ;

2). 查看索引

SHOW INDEX FROM table_name ;

3). 删除索引

DROP INDEX index_name ON table_name ;

**案例演示:**

A. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

CREATE INDEX idx_user_name ON tb_user(name);

B. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

C. 为profession、age、status创建联合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

D. 为email建立合适的索引来提升查询效率。

CREATE INDEX idx_email ON tb_user(email);

查看tb\_user表的所有的索引数据。

show index from tb_user;

![48254dccaec14e62ab69d282c147cb04.png][]

[a550d9e484434f5a9ecdbb5f42214087.png]: https://img-blog.csdnimg.cn/a550d9e484434f5a9ecdbb5f42214087.png
[69342decd5c64e3490099ac8100e7b38.png]: https://img-blog.csdnimg.cn/69342decd5c64e3490099ac8100e7b38.png
[48254dccaec14e62ab69d282c147cb04.png]: https://img-blog.csdnimg.cn/48254dccaec14e62ab69d282c147cb04.png