mysql字符串索引原理_Mysql索引失效的底层原理

1.索引失效的原因

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

image.png

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索引是无序的，仍然不可以通过二分查找法来查找

image.png

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

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

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

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

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

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

image.png

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