mysql系列：全网最全索引类型汇总

谁借莪１个温暖的怀抱￠ 2022-12-10 11:26 214阅读 0赞

### 前言 ###

除了常见的普通索引，唯一索引，组合索引，大家还能说一下mysql中有哪些其他类型的索引吗？

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

今天和大家一起总结mysql中有哪些索引类型。

# 一、mysql中有哪些索引类型？ #

*  聚簇索引 (Clustered Index)
 *  非聚簇索引
 *  主键索引(PRIMARY KEY)
 *  辅助索引(Secondary Indexes)
 *  HASH索引
 *  BTREE索引
 *  T-TREE索引
 *  R-Tree索引
 *  自适应hash索引（Adaptive Hash Index）
 *  唯一索引 (UNIQUE Indexs)
 *  普通索引 (Normal index)
 *  全文索引 （FULLTEXT Indexes）
 *  空间索引 （Spatial indexes）
 *  组合索引 (Multiple-Column Indexes)
 *  覆盖索引
 *  倒序索引 （Descending Indexes）
 *  不可见索引（Invisible Indexes）

吓了一跳吧，你说出了几个索引类型。

### 二、索引类型说明 ###

#### 1、按索引是否包含记录数据分类 ####

**聚簇索引：**  
将数据存储与索引放到了一块，找到索引也就找到了数据，不需要根据主键或行号去进行回表查询。

**非聚簇索引：**  
非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中.  
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

### 2、按索引是否建立在主键上分类 ###

**主键索引：**  
在MySQL的主键上创建的索引就是主键索引，主键索引会自动创建，一个表只能有一个主键索引，同时主键索引也是唯一索引。

**辅助索引：**  
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，innodb中辅助索引叶子节点存储的不再是行的物理位置，而是键值和主键 ID。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center]  
注意：  
InnoDB引擎支持聚簇索引，MyISAM引擎不支持聚簇索引。  
所以，**主键索引不一定是聚簇索引。**

### 3、按索引是底层的数据结构分类 ###

**HASH索引**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center 1]  
Hash 索引的特性：  
1、等值查询较快，但是不稳定  
2、不能使用范围查询  
3、不能避免数据排序  
4、不能利用组合索引的部分字段进行查询  
5、不支持模糊查询  
6、避免不了回表查询

**B+Tree索引**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center 2]  
**B+Tree索引的特性：**  
1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；  
2.内节点不存储data，只存储key；叶子节点不存储指针。  
3.不可能在非叶子结点命中；  
4.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；  
5、B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能  
6.更适合文件索引系统；

**mysql各个存储引擎支持的索引类型：**  
![在这里插入图片描述][20200926005143953.png_pic_center]

### 4、按索引的常规功能分类 ###

**唯一索引 (UNIQUE Indexs)**  
要求索引列的所有值都只能出现一次，即必须唯一。

**普通索引 (Normal index)**  
仅用来提高查询速度，没有其他特性。

**全文索引 （FULLTEXT Indexes）**  
MySQL可以通过建立全文索引，利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。比如实现全匹配模糊查询。  
但是实际场景测试mysql的全文索引性能非常不稳定，不建议生产环境使用。需要使用全文检索的地方，还是推荐使用Elasticsearch

**空间索引 （Spatial indexes）**  
空间索引使用R树，R树是用于索引多维数据的专用数据结构。

这4类索引也是我们可以使用Navicat等客户端工具，能够主动创建的4类索引。  
![在这里插入图片描述][20200926010607294.png_pic_center]

### 5、按索引的是否包含多个字段分类 ###

**多字段索引 (Multiple-Column Indexes)**  
也叫组合索引（**composite indexes**），即索引中包含多个表字段。

样例语句：

CREATE TABLE test (
    	id INT NOT NULL,
    	last_name CHAR(30) NOT NULL,
    	1581Verifying Index Usage
    	first_name CHAR(30) NOT NULL,
    	PRIMARY KEY (id),
    	INDEX name (last_name,first_name)
    );

### 6、其他特性索引 ###

**T-TREE索引**  
BTREE索引由NDB存储引擎实现为T树索引，算是BTREE索引在NDB存储引擎中的升级实现。

**R-Tree索引**  
从MySQL 8.0.12开始，R-Tree索引开始在SPATIAL索引中使用。  
MySQL对空间列上的SPATIAL索引使用R-Trees进行二次分割。

**自适应hash索引（Adaptive Hash Index）**  
是InnoDB存储引擎中的内存结构的组成部分。  
InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立hash索引可以提高查询速度，则自动建立hash索引。这就是自适应哈希索引（Adaptive Hash Index，AHI）  
AHI是通过缓存池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建hash索引。  
InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点也建立hash索引。

**覆盖索引**  
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。  
这个概念非常重要，灵活运用对SQL优化非常有帮助。  
![在这里插入图片描述][20200926013839325.png_pic_center]  
![在这里插入图片描述][2020092601382651.png_pic_center]  
比如这2个查询语句，同样是全匹配模糊查询，第二个sql却能使用索引。原因是就是利用了覆盖索引的概念，减少了回表查询。

**降序索引 （Descending Indexes）**  
从MySQL 8.0开始支持降序索引了。其实，从语法上，MySQL 4就支持了，但正如官方文档所言，“they are parsed but ignored”，实际创建的还是升序索引。  
MySQL支持降序索引：**不再忽略索引定义中的DESC，而是导致键值的降序存储。**

*降序索引的意义：*  
如果一个查询，需要对多个列进行排序，且顺序要求不一致。在这种场景下，要想避免数据库额外的排序-“filesort”，只能使用降序索引。

CREATE TABLE  t (
    	c1 INT, c2 INT,
    	INDEX idx1 (c1 ASC, c2 ASC),
    	INDEX idx2 (c1 ASC, c2 DESC),
    	INDEX idx3 (c1 DESC, c2 ASC),
    	INDEX idx4 (c1 DESC, c2 DESC)
    );

ORDER BY c1 ASC, c2 ASC -- optimizer can use idx1
    ORDER BY c1 DESC, c2 DESC -- optimizer can use idx4
    ORDER BY c1 ASC, c2 DESC -- optimizer can use idx2
    ORDER BY c1 DESC, c2 ASC -- optimizer can use idx3

**不可见索引**  
MySQL支持不可见索引； 也就是说，优化器未使用的索引。 该功能适用于除主键（显式或隐式）以外的索引。  
不可见的索引可以测试删除索引对查询性能的影响，而无需  
进行破坏性的更改，如果最终需要索引，则必须撤消该更改。 下降和  
对于大型表，重新添加索引可能会非常昂贵，而使其不可见和可见则是快速的就地操作。

简单来说，就是可以使索引不起作用，对查询优化器不可见。一般在调试索引对查询性能影响的时候使用。相当于索引的一个开关。

创建不可见索引的三种方式：

CREATE TABLE t1 (
    		i INT,
    		j INT,
    		k INT,
    		INDEX i_idx (i) INVISIBLE
    ) ENGINE = InnoDB;
    CREATE INDEX j_idx ON t1 (j) INVISIBLE;
    ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

修改一个索引的可见性：

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
    ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

### 总结 ###

本文比较全面的介绍了mysql中索引类型。如果你还知道什么其他类型的索引，欢迎留言交流。

更多精彩，关注我吧。  
![图注:跟着老万学java][java]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/9a5eb70abd404984838a7929ecb9d885.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/ca692879bba64cc2a9adaf273c72630d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/0e799faefb984a3a829d75cf7c7d8206.png
[20200926005143953.png_pic_center]: /images/20221123/ee769098266e4864af0ffb214da424a7.png
[20200926010607294.png_pic_center]: /images/20221123/9d54be6e3d8242fcbc486bc88edcc379.png
[20200926013839325.png_pic_center]: /images/20221123/9010b1bc9ee84cc9a72a388b406e35ff.png
[2020092601382651.png_pic_center]: /images/20221123/c688d70820d040d2bb81b7ecbcc0e791.png
[java]: /images/20221123/419a39dc9110412fa2624c283ec85ce3.png