前言

hello，大家好，我是喜欢完基金的秀总，作为开发了4年之久的Java大佬，肯定难免与mysql打交道，那么第一个就是查询速度问题，索引那么就是跑不掉的，第一步优化肯定就是索引上下功夫，那么有很多场景会导致索引失效导致全表扫描，那么今天就针对这个索引类型来个大梳理。

一、功能划分

  • 普通索引
  • 唯一索引
  • 主键索引
  • 全文索引
    image-1648464832518

好了，说到这就一一来讲解每一个意义

1.1、普通索引

言外之意，很普通的索引、没有任何约束条件、他的作用只有一个，那就是提高查询效率

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

name 字段就是一个普通索引（括号外面的是索引名，里边的是索引的字段）。

1.2、唯一索引

在基础索引之上加了一个唯一性的约束，可以存在多个交叉唯一索引

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

name 字段就是唯一性索引。

2.3、主键索引

顾名思义，他在唯一索引基础上加了一个不为NULL限制，那就是这个索引是主键，一个表只能有一个主键索引，但是主键索引里面可以包含多个字段

即：NOT NULL+UNIQUE

2.4、全文索引

这个其实咋们很少用，他一般通过Elasticsearch 或者 Solr 来做

FULLTEXT KEY name (name)
版本注意

  • MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。
  • MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引。

字段类型要求
只有字段的数据类型为 CHAR、VARCHAR 以及 TEXT 等才可以建立全文索引。

背景：
MySQL 的全文索引最开始只支持英文，因为英文分词比较方便；中文分词就比较麻烦，所以最早的 MySQL 全文索引是不支持中文的。从 MySQL5.7.6 版本开始，引入了 ngram 全文分析器来解决分词问题，并且这个分词器对 MyISAM 和 InnoDB 引擎都有效。

二、存储方式分

上面讲了从功能划分，但是他还有一种方法，那就是物理方式划分，他大概可划为两种

  • 聚集索引（有的人也称之为“聚簇索引”）
  • 非聚集索引（有的人也称之为“非聚簇索引”）

2.1、聚集索引

聚集索引在存储的时候，可以按照主键（不是必须，看情况）来排序存储数据，B+Tree 的叶子结点就是完整的数据行，查找的时候，找到了主键也就找到了完整的数据行。
image-1648465921738
在聚集索引里，表中数据行按索引的排序方式进行存储，对查找行很有效。只有当表包含聚集索引时，表内的数据行才会按找索引列的值在磁盘上进行物理排序和存储。每张表只能有一个聚集索引，原因很简单，因为数据行本身只能按一个顺序存储。

当我们基于 InnoDB 引擎创建一张表的时候，都会创建一个聚集索引，每张表都有唯一的聚集索引

1、如果这张表定义了主键索引，那么这个主键索引就作为聚集索引。
2、如果这张表没有定义主键索引，那么该表的第一个唯一非空索引作为聚集索引。
3、如果这张表也没有唯一非空索引，那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个 6 个字节的列，该列的值会随着数据的插入自增。

优势
查询快，如果要查询完整的数据行，使用非聚集索引往往需要回表才能实现，而使用聚集索引则能一步到位。
劣势

  • 聚集索引可以减少磁盘 IO 的次数，这在传统的机械硬盘中是很有优势的，不过要是固态硬盘或者内存（有时候为了提高操作效率，数据库服务器会整一个比较大的内存），这个优势就不明显了。
  • 聚集索引在插入的时候，最好是主键自增，自增主键插入的时候比较快，直接插入即可，不会涉及到叶子节点分裂等问题（不需要挪动其他记录）；而其他非自增主键插入的时候，可能要插入到两个已有的数据中间，就有可能导致叶子节点分裂等问题，插入效率低（要挪动其他记录）。如果聚集索引在插入的时候不是自增主键，插入效率就会比较低。

2.2、非聚集索引

非聚集索引我们一般也称为二级索引或者辅助索引，对于非聚集索引，单独的存储空间来存放。

如果我们在查询中用到了非聚集索引，那么就会搜索两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索聚集索引的 B+Tree，这个过程就是所谓的回表。

注意

一张表只能有一个聚集索引，但可以有多个非聚集索引。使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

总结

以上讲解、数据库分为两种区分，按照功能划分和物理划分

功能划分

  • 普通索引
  • 唯一索引
  • 主键索引
  • 全文索引

功能划分

  • 聚集索引
  • 非聚集索引