前言
hello,大家好,我是喜欢完基金的秀总,作为开发了4年之久的Java大佬,肯定难免与mysql打交道,那么第一个就是查询速度问题,索引那么就是跑不掉的,第一步优化肯定就是索引上下功夫,那么有很多场景会导致索引失效导致全表扫描,那么今天就针对这个索引类型来个大梳理。
一、功能划分
- 普通索引
- 唯一索引
- 主键索引
- 全文索引
好了,说到这就一一来讲解每一个意义
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 的叶子结点就是完整的数据行,查找的时候,找到了主键也就找到了完整的数据行。
在聚集索引里,表中数据行按索引的排序方式进行存储,对查找行很有效。只有当表包含聚集索引时,表内的数据行才会按找索引列的值在磁盘上进行物理排序和存储。每张表只能有一个聚集索引,原因很简单,因为数据行本身只能按一个顺序存储。
当我们基于 InnoDB 引擎创建一张表的时候,都会创建一个聚集索引,每张表都有唯一的聚集索引
1、如果这张表定义了主键索引,那么这个主键索引就作为聚集索引。
2、如果这张表没有定义主键索引,那么该表的第一个唯一非空索引作为聚集索引。
3、如果这张表也没有唯一非空索引,那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个 6 个字节的列,该列的值会随着数据的插入自增。
优势
查询快,如果要查询完整的数据行,使用非聚集索引往往需要回表才能实现,而使用聚集索引则能一步到位。
劣势
- 聚集索引可以减少磁盘 IO 的次数,这在传统的机械硬盘中是很有优势的,不过要是固态硬盘或者内存(有时候为了提高操作效率,数据库服务器会整一个比较大的内存),这个优势就不明显了。
- 聚集索引在插入的时候,最好是主键自增,自增主键插入的时候比较快,直接插入即可,不会涉及到叶子节点分裂等问题(不需要挪动其他记录);而其他非自增主键插入的时候,可能要插入到两个已有的数据中间,就有可能导致叶子节点分裂等问题,插入效率低(要挪动其他记录)。如果聚集索引在插入的时候不是自增主键,插入效率就会比较低。
2.2、非聚集索引
非聚集索引我们一般也称为二级索引或者辅助索引,对于非聚集索引,单独的存储空间来存放。
如果我们在查询中用到了非聚集索引,那么就会搜索两棵 B+Tree,第一次搜索 B+Tree 拿到主键值后再去搜索聚集索引的 B+Tree,这个过程就是所谓的回表。
注意
一张表只能有一个聚集索引,但可以有多个非聚集索引。使用聚集索引的时候,数据的查询效率高,但如果对数据进行插入,删除,更新等操作,效率会比非聚集索引低。
总结
以上讲解、数据库分为两种区分,按照功能划分和物理划分
功能划分
- 普通索引
- 唯一索引
- 主键索引
- 全文索引
功能划分
- 聚集索引
- 非聚集索引