Mysql索引失效的底层原理

1.索引失效的原因

联合索引排序的原理：先对第一个字段进行排序，在第一个字段相同的情况下考虑第二个字段，然后在第二个字段相同的情况下才考虑第三个字段...

CREATE TABLE 'test_user'(

'id' int(11) not null auto_increment comment '主键id',

‘user_id’ varchar(36) not null comment '用户id',

'phone' varchar(20) not null comment '用户名称',

'lan_id' int(9) not null comment '本地网',

'region_id' int(9) not null comment '区域'

)ENGINE=InnoDB Auto_increment=4057960 Default charset=utf8mb4

假设将('phone', 'lan_id', 'region_id')组成的联合索引

Explain select * from test_user where lan_id = 1

此时的索引是失效的，因为联合索引是遵循最左前缀法则即第一个字段有序的情况下lan_id才有序。现在是跳过phone,直接搜索lan_id相当于在一个无序的B+树上搜索，所以只能全表扫描。

例1下例范围查找的右边索引会失效

Explain select * from test_user where a >1 and b = 1

为什么索引会失效？

因为我们可以找到a >1的所有的节点，但是此时的b索引是无序的，仍然不可以通过二分查找法来查找

例2. like查询中，如果%放在两边或者放到左边，它都是不走索引的。只有%放到右边，它某些情况才会走这个索引。这是什么原因？

字符串在B+树里面存储的时候，它也是按照字母的大小去排序。首先按照第一个字母去比较，如果第一个字母相同则按照第二个字母去比较和最佳左前缀法则相似。如果左边用了%，那后面的字符是无序的，此时就不能使用二分查找来定位元素还是退化为了全表扫描。

3.Mysql中的索引查询为什么使用了B+树结构，而不使用哈希索引或者B树？

首先哈希值是无序的，不能够进行范围查找。

平衡二叉树的缺点是当数据量非常大的时候，其深度也会非常深这样也会导致查找效率慢。其次其存在回旋查找的问题。比如说当存在范围查询>5的时候定位到该元素之后还得回溯到前面的节点元素6，7

B树的最大特点是一个节点可以存储多个值，这样可以使得树的高度变矮，从而使得树的查找速度变快。但是其也存在回旋查找的问题。

B+树则解决了这个问题，它的非叶子节点存储的是key,其叶子节点既存储了key也存储了value并且其叶子节点是有序的，节点之间用指针相连也正是因为这一点使得B+树在范围查询的时候不存在回旋问题。

where条件==>order by 条件==>group by 条件 按顺序遵守 最佳左前缀法则

假设创建了复合索引：a,b,c

不在索引列上做任何的 *** 作（计算、函数、显式或隐式的类型转换），否则会导致索引失效而转向全表扫描

1、字符不加单引号会导致索引失效

name字段为varchar类型

这条sql发生了隐式的类型转换：数值==>字符串。所以导致了全表扫描，索引失效

应尽量避免在 where 子句中对字段进行表达式 *** 作，这将导致引擎放弃使用索引而进行全表扫描。如：

mysql中的范围条件有：in/not in、 like、 <>、BETWEEN AND ；

<>后面的索引失效

in会导致索引全部失效！！！

BETWEEN AND 范围条件不会导致索引失效！！！

尽量让索引列和查询列一致；减少select * 的使用

1、查询表结构

2、查询表的索引结构

联合索引：name,age,post；说明add_time字段没有添加索引

3、查看select * 的执行计划

4、查看 select name,age,pos的执行计划

5、如果select只用一部分索引

like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的 *** 作。

解决：可以使用 覆盖索引 来解决这个问题！

1、先查看表上的索引

id、name、age、pos 四个字段上都有索引； 注意：name是联合索引中的第一个，带头大哥！

2、查看表结构

有个add_time字段没有用到索引

3、查看执行计划

使用UNION ALL

假设创建了联合索引 x(a,b,c)

ps：like虽然也是范围查询但是区别于>、<，%用在最前面就只用到索引a了；%用在最后面可以用到a+b+c！

下面的sql几乎违背了上面的所有原则，索引依然全部生效。因为select是索引覆盖的，select里不包含没有建立索引的字段。因此总是用到索引的。可以看出来索引覆盖在sql优化中的作用性

---