区分一级二级索引，索引失效情况

一级索引和二级索引

简单区分，一般的主键索引都是一级索引，而二级索引就相当于除了主键的其他字段的索引

结构区别：

众所周知，索引的底层结构采用的是B+树，B+树的特点是叶子节点存储数据，除了叶子节点的其他节点只存储索引值，不存储数据，这也是与B树的一大区别，这样能在同一层中，B+树能存储更多的索引，高度更低，而且B+叶子节点存储傍边叶子节点的位置信息，相当于叶子节点之间是有一条双向列表，B树叶子节点不存其他叶子节点的任何信息，索引速度更高。

一级索引：叶子节点只存储索引值，不存储对应的数据

二级索引：叶子节点存储对应的索引值和主键值（一级索引）

关系

一级索引可以单独存在，而二级索引不能单独存在，依赖于一级索引。

为什么为依赖于一级索引呢？

​ 因为二级索引存储的索引值和一级索引的主键值，先获取到索引值后拿到对应的主键索引值根据主键索引值再去获取主键对应的数据，这个过程也叫回表查询。

回表查询：

我们常说要避免回表查询，为什么要避免回表查询呢？怎么避免回表查询

假设我们存在id、name、age三个字段，di为主键索引（一级索引），name为普通索引（二级索引）

当我们利用id查询这条数据时：

首先我们需要查询到id，根据id获取到name，age字段，需要读取三个磁盘块可以获取到整条数据

当我们利用name这个索引值进行索引数据时：

首先从索引树结构上查询到name字段得值，然后获取到该值对应得主键id（一级索引），根据主键id（一级索引）再次进行进行索引获取对饮数据的位置，即回表查询，需要读取五个磁盘块

这就是需要避免回表查询的原因，回表查询的性能较低。

（注：没有索引值的时候是进行全表扫描，再海量数据中性能低的可怕）

全表扫描

将所要查询的数据进行一一比对，直到获取到目标数据为止。（复杂度）比对次数为n，使用索引（B+树的数据结构）比对次数log2（n）

索引失效

使用explain查看sql的执行情况

情况一：出现隐式类型转换

当使用索引时，查询条件为索引值相同，类型不同，发生转换时，索引失效

情况二：索引字段使用表达式计算，索引失效

如weher index+2=3;

情况三：索引字段使用了函数

如: where left(index_num,3)=‘133’

情况四：模糊查询like关键字的索引字段采用左模糊匹配

如：‘%index_name’ 和 ’%index_name%’ 均失效，而’index_name%’ 不失效

情况五：对于联合索引，未使用最左原则

如有联合索引index1和index2

查询为 where index2 失效，必须采用最左原则，即查询的索引一定要包含左边索引的字段值

情况五：列与列进行对比，索引失效

select * from test where id=c_id

情况六：列存在NULL值，索引失效

在我们开发中应避免NULL值的出现呢，如果避不了，最好应该给一个default默认值（默认值可以为空字符串和0）

情况七：where查询条件中存在一些关键字

• <> 不等于
• in
• not in
• not eists

情况八：若mysql觉得全表扫描优于索引时索引失效，

不使用索引情况：

• 字段唯一性差
• 频繁更新的字段
• where条件中不用字段
• 索引使用<>
• 索引性能低于全表扫描