1.索引的类型
按照数据结构分为:B+树,Hash索引,Full-text索引
按照物理存储分为:聚集索引和非聚集索引
按照字段的特性分为:主键索引,唯一索引,普通索引,全文索引
按照字段的个数分为:单列索引,联合索引(复合索引,组合索引)
2.为什么用B+树做索引结构
B+树是在B树的基础上进行一种优化,更适合做存储索引结构,在B+树中,非叶子节点上仅存储键值,不存储数据,所有数据记录在叶子节点上,并且按照顺序有序排列
优点:
非叶子节点上可以存储更多的键值,相对应的树的阶数(子树节点)就大,树也会变得更矮更胖,减少磁盘IO次数,提高查询效率
所有的数据都有序记录在叶子节点上,使得范围查找,排序查找,分组查找,去重查找方便很多
3.前缀索引
当字符列比较长选择字符列的前n个字符作为索引,可以节约索引空间,
无法用前缀索引做order by,group by和覆盖索引
4.覆盖索引
select 查询的数据列只用在索引中就能获取,不必再回表,查询被索引覆盖
5.聚簇索引和非聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式
表示索引结构和数据放一起存放的是聚簇索引,分开存放的是非聚簇索引
区别:
Mysql Innodb
最大的区别在于叶子节点是否存放一整行记录
聚簇索引存放的是一整行记录,非聚簇索引存放的是主键信息,还要进行回表操作
一般一个表只能右边一个聚簇索引(主键索引),非聚簇索引可以存在多个
MyIsm
在Mylsm存储引擎中,主键索引,普通索引都是非聚簇索引,因为数据和索引是分开的,叶子节点都是用一个地址指向真正的表数据
6.创建索引常用语句
建表时创建:index 索引名字(字段名字)
建表后创建:create index 索引名字 on 表名(字段名)
修改表时创建:alter table 表名 add index 索引名(字段名)
删除索引:drop index 索引名 on 表名 或者 alter table 表名 drop index 索引名
显示索引信息:show index
7.索引失效的原因
1.查询条件包含or,可能会导致索引失效
2.like通配符可能会导致索引失效
3.联合索引,查询时的条件不是联合索引中的第一个列会导致索引失效
4.在索引列上使用函数,运算符会导致索引失效
5.索引字段上使用(!=或者<>,not in)时会导致索引失效
6.左连接和右连接查询关联的字段编码格式不同,会导致索引失效
7.若字段类型是字符串,where时一定要用引号括起来,会导致索引失效
8.mysql优化器估计使用全表扫要比使用索引快就不使用索引,会导致索引失效
8.那些场景不适合创建索引
1.数据量比较少的表,不适合加索引
2.更新比较频繁的字段也不适合加索引
3.where,group by,order by后面没有使用到的,不需要加索引
4.已经有冗余的索引了(比如已经有联合索引(a,b)就不要再单独创建索引a或者b)
9.为什么用B+树,而不用二叉树,平衡二叉树,B树?
首先从几个方面去看:查询得是否变快,效率是否稳定,存储数据多少,查找磁盘IO次数
为什么不是二叉树?
二叉树特殊化为一个链表,相当于全表扫描,平衡二叉树相比效率更高
为什么不是平衡二叉树?
若树作为索引,每查找一次数据就要从磁盘中获取一个节点,平衡二叉树每个节点只存储一个键值和数据
如果是B树,可以存更多的节点数据,树的高度也会更低,因此读取磁盘的次数就会下降
为什么不是B树而是B+树?
B+树非叶子节点上是不存储数据的,进存储键值,而B树节点中不仅存数据,也存键值
Innodbm中页默认大小是16kb,如果不存储数据,就能存储更多的键值,相应的树的阶数就会更大,书就会更矮更胖,减少磁盘IO次数
B+树的所有数据都有序的存储在叶子节点,支持范围,分组,排序,去重查找
10.什么是回表
当查询的数据在索引列中找不到,就要回到主键索引树去获取,这个过程叫回表
11.索引的最左侧前缀原则
可以是联合索引的最左N个字段,建立一个索引(a,b,c)相当于建立了(a)(a,b)(a,b,c)三个索引,提高索引的复用能力
12.什么是索引下推
Mysql5.6引入了索引下推优化
可以在索引遍历过程中,对索引包含的字段进行优先判断,不满足的直接过滤掉,减少回表操作
13.Hash索引和B+树的区别
1.B+树可以进行范围查询,Hash不支持
2.B+树支持索引的最左侧原则,Hash不支持
3.B+树支持order by排序,Hash不支持
4.Hash在等值查询效率高(但如果索引列重复多,会出现冲突)
5.B+树用like进行模糊查询,like后面可以起到优化作用,Hash不支持