Mysql索引类型

1.索引类型

索引类型有以下几种:
1.主键索引(PRIMARY)
2.唯一索引(Unique)
3.一般索引(Normal)
4.全文索引(Full Text)

主键索引(PRIMARY key):表中当且只有一个,并且不能为空,不能重复.
唯一索引(Unique):对数据的一个约束,保证对应数据的唯一性,可以设置多个唯一索引,并且可以为空.
一般索引(Normal):可以设置多个,也可以重复和空.
全文索引(Full Text):为了检索大文本信息使用的.

2.索引文件存储

myisam和innodb索引的数据结构使用的都是B+tree,但是它们内部存储的数据方式不一样.
myisam:
对应表会包含3种数据文件,分别是.frm .MYD .MYI,分别对应着表结构,表数据文件,表索引文件.
它的叶子节点存放的data是数据的地址,通过地址去数据文件中查找对应的数据.
innodb:
对应表会包含2种数据文件,分别是.frm .ibd,分别对应着表结构,数据和索引文件.
索引文件和数据文件是存放在一起的,对于主键索引来说,叶子节点存放就是完整的行数据,但是对于非主键索引来说,叶子节点data里面存放的是主键的id.如果我们根据非主键索引查找数据,就需要查询2棵B+tree,先遍历非主键索引的B+tree,然后获取到主键id以后,再次查询主键索引的B+tree.

3.聚簇索引和非聚簇索引

除了主键索引,我们一般也称其他索引为二级索引.
对于这些索引,我们还有其他的称谓,叫做聚簇索引和非聚簇索引.
为什么这么叫呢?其实也是和它的数据结构有一定关系,且听下面一一道来.
对于聚簇索引和非聚簇索引我们有个简单的定义:
如果数据索引放在一起,那么它就是聚簇索引,反之它就是非聚簇索引.

Innodb存储引擎中,如果要插入数据,那么这个插入的数据必须与索引绑定,这个索引可以是主键索引,如果没有主键,那么用唯一键索引,如果也没有唯一键索引,那么就会创建一个6字节的自增主键.

在innodb存储引擎中,一定存在着聚簇索引,其他的都是非聚簇索引.
在innodb存储引擎中,数据只会存储一份,并且聚簇索引和数据是存放在一起的.
在myisam存储引擎中,都是非聚簇索引,因为它的数据和索引是分开存放的,上面我们也简单介绍了.

如果我们通过非聚簇索引进行查找数据,要走2棵B+tree,第一次查询对应的主键id,然后根据主键id再去查询对应的行数据,这个过程我们也称之为回表.

非聚簇索引的B+tree结构:

聚簇索引的B+tree结构:

4.索引相关名词

1.回表
2.覆盖索引
3.最左匹配
4.索引下推
这里我们举个例子:
创建一个表: user
字段: id name age address
id是主键
name age是组合索引

1.回表
select * from user where name = ?
执行这个sql,需要查询2棵B+tree树,首先根据name查询对应的主键id,然后根据主键id查询对应的行数据.
这个过程其实就称之为回表.

2.覆盖索引
select id,name from user where name = ?
执行这个sql,首先查找name,age索引的B+tree树,但是这个sql我们只返回id和name,在第一棵树里面就可以拿到我们需要的所有数据,这个时候就不需要再回表了.
这种情况就是覆盖索引,正常情况下,我们是推荐使用覆盖索引这种的,可以减少回表的次数.

3.最左匹配
select * from user where name = ?
select * from user where name = ? and age = ?
select * from user where age = ?
select * from user where age = ? and name = ?
上述4种情况下,只有第三个不会使用索引,其他3个都会使用索引进行查找数据,至于第四个mysql的优化器会对sql进行优化.
这就是最左匹配的概念.

坑点:
如果组合索引使用的是全字段索引(不包含主键),那么无论使用哪个字段查询都是会使用索引的.

4.索引下推
select * from user where name = ? and age = ?
不适用索引下推:
根据name进行查找所有符合的数据,然后在server端再进行age的过滤.
使用索引下推:
根据name和age同时进行条件的筛选,而不会在server端进行数据的过滤,这样就减少了回表的次数,大大的提高了效率.

下推索引一般出现的场合是在组合索引的情况下,单列索引的情况下也会出现,只是条件比价苛刻,具体的可以查看添加链接描述

建议建立索引:

1. 频繁查询的字段
2. 用户表级联的字段
3. 频繁需要排序的字段
4. 频繁需要where过滤的字段

不建议建立索引:
在增删改的时候,也需要修改索引,进行维护,也会影响性能,所有有些字段上尽量不要建立索引.

1. 空值的字段
2. 频繁更新的字段
3. 差异化比较小的字段,比如说状态只有0和1

面试点:
1.如果索引文件大小约为500M,对应表记录有1kw条,现在删除500w数据,那么索引文件会变小吗?为什么?
2.有个user表,主键为id,其他列是name,age,address,建立普通索引name,age,address,那么请问:
select * from user where name = ? and age = ?
select * from user where age = ? and address = ?
这个2个sql语句分别使用什么索引?数据需要回表吗?
3.同样使用上面user表
select * from user where name = ? and age = ?
select * from user where name = ?
这2个sql语句使用索引长度是多少?

索引长度
如果字段允许为空,字段类型占用字节+1字节
如果字段不为空,字段类型占用字节

5. mysql交互结构
6.