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

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

mysql or不走索引 mysql不走索引的原理_数据

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



1. 为什么要创建索引

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

1.1 如何创建索引

mysql or不走索引 mysql不走索引的原理_mysql_02

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



Hash索引

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

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

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

select * from t_person WHERE age = 20;

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

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

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

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

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

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



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

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



BTREE索引

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

mysql or不走索引 mysql不走索引的原理_数据_03

可以看到 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='学生信息表';

插入 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);

2.1 聚簇索引

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

mysql or不走索引 mysql不走索引的原理_主键_04



建好了以name 列 的BTREE索引

create index idx_name on student(name);

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

mysql or不走索引 mysql不走索引的原理_mysql_05



然后执行一下语句

select * from student WHERE name = 'David';



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

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

mysql or不走索引 mysql不走索引的原理_mysql or不走索引_06

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

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

2.2 联合索引

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

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

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



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

create index idx_name_age on student(name,age);



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

mysql or不走索引 mysql不走索引的原理_mysql_07

注意观察我用红色虚线框出来的那两个节点，这是这棵树和上面那棵只给 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. 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 （被问到是只记得一两个参数了我的🐎，而自己的调优经历也只是看那几个参数就够了~时间带走了我的脑子）



用法

EXPLAIN select * from student where name = 'David' ;

输出

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4DUoM798-1595416141328)(C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20200722175605894.png)]

1）Table：

显示这一行的数据是关于哪张表的

2）possible_keys：

显示可能应用在这张表中的索引。

3）key：实际使用的索引。

如果为NULL，则没有使用索引。MYSQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX（index）来强制使用一个索引或者用IGNORE INDEX（index）来强制忽略索引

4）key_len：使用的索引的长度。

在不损失精确性的情况下，长度越短越好

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

5）ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

6）rows：MySQL认为必须检索的用来返回请求数据的行数

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

7）select_type：查询中每个select子句的类型

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

8）type：这是最重要的字段之一

显示查询使用了何种类型。从最好到最差的连接类型为

NULL > system > const > eq_ref > ref > range > index > ALL

NULL:

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

system、const：

可以将查询的变量转为常量. 如id=1; id为 主键或唯一键。当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

eq_ref：

访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)。类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

ref：

访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生。表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

range：

索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询。

**index：**以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描。index与ALL区别为index类型只遍历索引树

ALL：全表扫描，应该尽量避免。 MySQL将遍历全表以找到匹配的行。

今天不学习，明天变辣鸡

参考：

全表扫描，优点是不用排序,缺点是还要全表扫描。index与ALL区别为index类型只遍历索引树

ALL：全表扫描，应该尽量避免。 MySQL将遍历全表以找到匹配的行。

今天不学习，明天变辣鸡

参考：
MySQL的索引是怎么加速查询的数据类型的隐式转换个人网站（基于Docker）就是生怕哪一天服务器不续费了 (～￣▽￣)～"