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+树在范围查询的时候不存在回旋问题。

如果开启了mysql的更新日志，日志文件会记录每一次更新的语句，自然可以回溯了

如12:00，你插入了一条记录，12:02某人对这项记录做了修改，12:03又有一个人做了修改。你想回到最开始，那么就是你12:00插入的语句了。

代码么？这个没有代码，自己打开日志文件看。至于日志文件再哪儿，这里篇幅有限，你可以搜一下mysql 日志文件，搜索结果会比这里回答更详细

PS：更新日志由于会记录每一条更新语句，所以会导致mysql速度变慢，而且磁盘占用也很大。所以mysql不推荐开启这种日志，默认也是关闭的。具体如何开启，请自行搜索，这里不详述

---