mysql的索引

一、索引的定义

索引是一种特殊的文件，包含着对数据表中所有记录的引用指针。（书的目录，快速寻找相应内容）
可以对表中的一列或多列创建索引，并指定索引类型。

（所学索引基于mysql5.5之后的数据引擎InnoDB）

一、mysql常见的两种数据引擎：
5.5版本之后InnoDB和5.5版本之前的MyISAM
区别：
（1）InnoDB支持事务（保持数据的稳定性），稳定性比MyISAM好，MyISAM不支持事务
（2）MyISAM性能比较高，比InnoDB高

二、存储数据模组：
1.磁盘：容量大、价格便宜、操作速度慢、可持久化（电脑重启数据还存在）
2.内存：容量小、价格比较贵、操作速度快、不可持久化
3.CPU缓存：容量小、操作速度极快、不可以持久化

索引中数据存储在磁盘中，存储的是数据的位置
查询数据的存储目录：
show variables like ‘%dir%’;

二、为什么使用索引

1.索引避免了顺序查询，提高查询速率
2.使用索引可以将数据库中的关键索引信息存储到内存中，内存的操作速度比磁盘快

一张表可以有多个索引，每个索引就是一个目录。

三、索引的优缺点和使用场景

1.优点
可以提高查询效率
2.缺点
（1）维护成本高，因为索引使用B+树，每次新增、删除数据都需要整理树结构
（2）增加存储成本（磁盘、内存）
（3）索引过多会对MYSQL的优化器（选择合适索引）造成一定负担
3.使用场景
（1）数据量大
（2）经常查询的列
4.不适用场景
（1）数据使用频率低，而且添加和删除比较高频的业务
（2）电脑本身磁盘和内存空间不足
5.索引使用注意事项
对已经存在的很多数据的表新增索引的时候，不能在生产环境上执行（找一个没有用户使用的时间段）。

四、索引的分类

1.按照是否为主键
（1）主键索引（聚簇索引）：不允许有空值，一般在建表的时候通过主键约束同时创建主键索引
（2）非主键索引（非聚簇索引/二级索引）：除主键索引的其他索引
2.按照特征
（1）普通索引
（2）唯一索引（创建索引的字段具有唯一性）
（3）联合索引（由一个表中的多个字段组成的索引）

五、索引的创建

1.主键索引、唯一索引在创建表时，创建了主键约束或者唯一约束时会自动创建主键索引和唯一索引

2.查看索引：show index from table_name;

3.手动创建索引
普通索引：create index 索引名 on 表名（字段名）

联合索引：create index 索引名 on 表名（字段名，字段名）

唯一索引：create unique index 索引名 on 表名（字段名）
或create unique index 索引名 on 表名（字段名，字段名）

主键索引：alter table 表名 add primary key（字段名）

在创建表时创建索引：
creat table student（
id …,
name…,
address…,
age…,
key index_address (address）,（普通索引）
key index_name_age(name,age) （联合索引，注意字段先后顺序）
);

注意：
索引和约束联系和区别
1.创建索引时会自动创建约束，在创建约束时也会自动创建索引
2.索引和约束是不同的业务，约束是用来规定数据的正确性，索引是用来提升数据库的程序性

六、索引的删除

drop index 索引名 on 表名；
（每个索引名在一张表中是唯一的，不能重复）

注意事项：
（1）创建索引会创建对应约束，删除索引会删除对应约束
（2）唯一索引创建前提：原先的数据具有唯一性

七、InnoDB MySQ索引实现原理

三阶段：
1.二叉树（数据多时 时间久，维护困难，性能低）
2.B-树 （叶子结点和非叶子结点存储所有信息，内存太大，索引加载到内存中很慢）
3.B+树（优化：1.非叶子结点不存储表的
所有信息 2.叶子结点存储的非数据本身，而是数据地址 3.每层数据之间使用链表相连）

聚簇索引（主键索引/聚集索引）和非聚簇索引（非主键索引/二级索引/非聚集索引）区别

聚簇索引：叶子结点存储的是数据，一遍直接能查询到数据（只要查询到相应的ID就能直接得到id对应的所有信息）效率高，一张表只能有一个
非聚簇索引：叶子结点存储的是主键ID，拿到主键ID后再从聚簇索引中查找，效率低（非聚簇查找过程叫做回表查询，查两边），一张表可以有多个
（每一个结点对应的都是一个B+树）

八、索引失效的最常见的场景

索引效验：确认索引是否生效
explain select * from student where id=1;

type：all是全表查询
判断条件：
key：key为null表示索引无效或者没有索引，不为空有值，表明执行时使用的是这个索引，索引有效

索引失效场景：
一、索引不满足最左匹配原则
*最左匹配原则（前缀匹配）：根据联合索引的创建，以最左边为起点字段查询可以使用联合索引，联合索引才能生效

eg：
创建索引：create index sno_sname_age on student (sno,sname,age);
最左匹配原则校验索引的各种对错方式：
1.explain select * from student where sno=‘2001’ and sname=‘zhangsan’ and age=18;（正确）
2.explain select * from student where sno=‘2001’ and sname=‘zhangsan’ ;（正确）
3.explain select * from student where sno='2001’and age=18;（正确）
4.explain select * from student where sno=‘2001’;（正确）
5.explain select * from student where sname=‘zhangsan’ and sno=‘2001’ ；（正确，系统优化为sno and sname）
6.explain select * from student where sno=‘2001’ and age=18 and sname=‘zhangsan’ and age=18;（正确）

key:sn_sname_age

7.explain select * from student where sname=‘zhangsan’ and age=18;（错误）
8.explain select * from student where age=18;（错误）
key:sn_sname_age
9.explain select * from student where age=18 and sname=‘zhangsan’;（错误）

注意：只要有最左边的列就就可以正确查询，因为在B+树中，保存的只是联合索引最左边的列的值

二、使用错误的模糊查询
like模糊查询的常用方式：
1.前面确定：字段名 like ‘张%’;
2.中间确定：字段名 like ‘%张%’;
3.后面确定：字段名 like ‘%张’;

只有1.前面确定能触发索引，其他不行
key:null

三、索引使用运算操作（+/-/*…）
eg:explain select * from student where id+1=2;
key:null（索引失效）

四、列使用函数（ifnull/count…）
eg:explain select * from student where ifnull(id,0)>5;
key:null

五、查询列使用了隐式类型转换
eg：id创建为varchar,查询时赋值为int型
key:null

六、使用is not null
eg:explain select * from student where address is null;（正确）
explain select * from student where address is not null;（错误）key:null