Mysql 之 索引的作用 以及 不走索引的情况_没走索引 添加一个等值条件走索引

Mysql 之 索引的作用 以及 不走索引的情况

写一下mysql索引吧，提及索引失效的原因的时候，当初只记得两个，虽然笔记有，当时的脑子可能是这样的。

温故而知新，看一遍不如写一遍

1. 为什么要创建索引

没有加索引的表就像 一本 没有目录的字典，而索引相当于目录， 能大大加速查询的速度。

1.1 如何创建索引

可以看到索引的类型有B-Tree 和 Hash

Hash索引

先说Hash, 若是对Java的 HashMap 有所了解的话，就很容易理解了，网文很多~

**哈希索引：**通过哈希算法随机算出字段值所对应的数组下标， 排序在哈希数组上 ，跟HashMap一样会有哈希冲突的可能。

**查询：**建立以 age 列 为Hash索引， 注意等值查询。

select * from t_person WHERE age = 20;
1

将条件的值 ‘age’ 进行hash计算后获取下标，取到对应的数据，因为是 select *, 进而回表查询整体数据。

所以Hash索引可以一次定位，效率很高，而Btree索引需要经过多次的磁盘IO，但是innodb和myisam之所以没有采用它，是因为它存在着好多缺点：

1、因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询

2、由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

3、不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。

4、当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。

因为 hash冲突，hash索引适合用在选择性好的列上，例如身份证/电话号码等重复少的少。

特点： 可以快速的精确查询，但是不支持范围查询。

BTREE索引

BTree索引 是以B+树的结构来存储数据的。 先看B+树的结构：

可以看到 B+树中的非叶子节点会冗余一份在叶子节点中，且叶子节点之间用指针相连。 B树索引是顺序存储的，适合进行范围查找

**冗余目的：**提高范围查找的效率。

这个 BPlusTree Visualization 网址可以模拟B-， B+， 二叉 等结构图。

2. 回表

说一下回表，不想自个画图了，网上有就直接找了，没想到找到了一个大神写得，就直接摘抄了这一部分了。此节为摘抄 + 补充。

画图不易，就不狗尾续貂了侵删, 直接搬了。

执行建表语句：

CREATE TABLE `student` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '主键id',
  `student_no` VARCHAR(64) COMMENT '学号',
  `name` VARCHAR(64) COMMENT '学生姓名',
  `age` INT COMMENT '学生年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='学生信息表';
1
2
3
4
5
6
7

插入 5 条数据：

insert into student(student_no,name,age) values(101,"Alice",18);
insert into student(student_no,name,age) values(102,"Bob",19);
insert into student(student_no,name,age) values(104,"Brandt",15);
insert into student(student_no,name,age) values(105,"David",19);
insert into student(student_no,name,age) values(109,"David",18);
1
2
3
4
5

2.1 聚簇索引

一般建表会用一个自增主键做 聚簇索引，没有的话MySQL会默认用你指定的主键来创建，在这里是递增主键，维护起一棵 B+树。如下图：

建好了以name 列 的BTREE索引

create index idx_name on student(name);
1

这时候 MySQL 又会建一棵新的 B+树：

然后执行一下语句

select * from student WHERE name = 'David';
1

MySQL 到你刚刚创建的这棵 B+树 查询，快速查到有两条姓名是“David”的记录，并且拿到它们的主键，分别是 4 和 5，但是你要的是select *呀，怎么办？

别忘了，MySQL 在一开始就给你建了一棵 B+树 了，把这两棵树，放在一起，拿着从这棵树上查到的两个主键ID，去聚簇索引找，事情不就解决了？这就是回表

图片来源于： http://bridgeforyou.cn/2020/02/15/how-mysql-use-index/ 侵删 ， 作者 柳树的絮叨叨 写得很棒，此处案例也为摘抄, 然后加点自己的小补充， 很经典的解释了回表。

为将索引失效的铺垫还不够_{再摘抄一点点}

2.2 联合索引

这个索引名字很多， 也有人说是覆盖索引，也有复合索引，我习惯叫他联合~ 珠联璧合嘛~

继续，如果我还想根据姓名和年龄同时查询呢？

select * from student where name = "David" and age = 18;
1
2

还是那个道理，数据虽然按照 name 有规律的组织了，但是没有按照 age 有规律组织，所以我们要给 name 和 age同时建索引：

create index idx_name_age on student(name,age);
1

这时候 MySQL 又会建一棵 B+树，这下 B+树 的节点里面，不只有 name，还有 age 了：

注意观察我用红色虚线框出来的那两个节点，这是这棵树和上面那棵只给 name 建索引的树的唯一区别，两个元素换了个位，因为排序时，是先用 name 比较大小，如果 name 相同，则用 age 比较。 补充 ： 此处用的是Mysql5.6的新特新， 索引下推， 找到name之后 下推到age，即继续匹配age，这样一来就非常高效了。

还是那句话，这里举的例子数据量很少，你可以想象下有一万个叫“David”的学生，年龄随机分布在 13 到 20 之间，这时候如果没有按照 age 进行有规律的存储，你还是得扫描一万行数据。

3. 索引失效的原因

这是我最想写的重点，没记起来就是因为没理解透彻。原本想着写一遍博客来达到温故而知新，没想到呀，再次看到图的时候，还没看完 图的来源的文章，仅仅看到如此精妙的图，就让我直接蹦出了索引失效的场景。

照看 2 节 的图，一看就知道回不回 回表, 走不走索引。

简单举例： 可自行Explain

Explain
select * from student where  age + 10 = 18;  // 不走索引
select * from student where  age != 18;  // 不走索引

select * from student where  age = 18; //走索引
select * from student where  age = "18%David"; //能查出结果但不会走索引（会进行隐形的数据类型转换）

select * from student where  name  like "%David"; // 不走索引

select * from student where name = 2; //不走索引  字符串类型，而条件中未加引号
select * from student where name = '2' //走索引


1
2
3
4
5
6
7
8
9
10
11
12
13

这就能很容易看出来了

不走索引总结;

1. like查询是以%开头
2. 条件中带有不等于，where id !=2 或者 where id <> 2
3. 左侧进行了计算
4. 查询条件里使用了函数 （相当于进行了计算）
5. 用跟条件字段不同类型字段，会进行隐形的数据类型转换， 不走索引。
6. 如果条件中有or(并且其中有or的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
7. 如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

​ 小结： 索引走不走， 实际中还是直接Explain测试就知道了， 但是理解后更加印象深刻， 也能跟面试官多过几招。

3.1 Explain

通过 EXPLAIN可以查看 SQL语句的执行计划，是否走索引等。后续会详解EXPLAIN （被问到是只记得一两个参数了我的

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/381399