功能上
1、主键索引
主键索引通常是为了构建B+树的存储的时候用的,如果不是创建一个表不指定主键的话,mysql会自己生成一个来构建B+树存储。
主键索引是特殊的唯一索引,也就是主键索引不能为null,但唯一索引允许为null
如果是在innodb存储引擎的话,构建的通常是聚簇索引(也就是数据跟表结构存放在一起)。也就是叶子节点存放的是表中的数据。
如果是在myisam存储引擎的话,构建的是非聚簇索引,叶子节点存放的是该数据对应的索引地址。
2、普通索引
除了主键索引,其他的索引我们通常成为辅助索引,普通索引只是为了加快查找速度。没什么特点的。
3、唯一索引
有区别于主键索引,也就是该索引对应的值可以为null。
4、组合索引
其实就是个列组合而成的索引。这里通常有一个最左匹配算法。
create table test(
id int primary key,
name varchar(20),
sex int,
age int,
index(name,sex,age)
);
这样我们创建了一个组合索引(name,sex,age);如果我们要查询的时候,我们是通过name,age来进行查询,我们的组合索引会生效吗?通常我们要判断一条sql是否命中索引的时候,我们可以使用explain来进行查看。
我们可以看到上面的name,age这个组合就生效了,为什么呢?我们可以正常的想一下,如果我们第一列都不符合,我们找第二个列有什么用?但是你如果写成
explain select name,age from sheng12 where age=1 and name='a';
这个会生效吗?
也是会生效的,这个就要从mysql的一条sql怎样执行说起了,里面有一个优化器,会讲我们刚才要执行的sql调整为
explain select name,age from from sheng12 where name='a' and age=1;
存储结构上:
分成B+树跟hash两种存储
1、B+树
然后我们建立其他索引的时候就会变成这样。
辅助索引中叶子节点存储的是该索引值跟主键。
这里就会涉及到一个回表操作,也就是我们利用的索引来查询的时候,找到辅助索引中的叶子节点,叶子节点中的数据列不够的时候,就要利用叶子节点中的主键跳到主键表中查找,这个就是回表操作。
如果辅助索引中的列够的话,我们叫做索引覆盖,也就是不会发生回表操作。
比如我们要进行查询一个表中的数据:
create table sheng2(
id int primary key,
name varchar(20),
sex int,
age int,
index(name,age)
);
如果要查询的数据是:
select name,sex from sheng2 where name='a' and sex=1;
这个就不要进行回表操作,但是如果要查询的是:
select name,sex,age from sheng2 where name='a' and sex=1;
这个就要进行回表操作,因为你name跟sex索引构建的B+树中的叶子节点数据中没有age这个列,所以要进行回表操作。
2、hash
也就是将索引值映射到一个hash表中,如果命中了索引的时候,直接通过hash表来快速查找,就不用像b+树这样进行多次检索。
那为什么hash索引这个快,不用hash索引,要用B+树结构呢?
因为hash在多种情况会失效:
- Hash 索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询。
- 组合索引在hash中会失效
- Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
- Hash 索引在任何时候都不能避免表扫描