MySQL（8.0.19）索引失效的分析

不念不忘少年蓝@ 2024-03-25 23:05 6阅读 0赞

#### 目录 ####

*  索引数据结构
 *   *  主键索引
     *  非主键索引
 *  索引在什么时候是有效的？
 *   *  字符串比较大小
     *   *  btween and
         *  索引字段重复对索引的影响
 *  联合索引
 *  为什么普通索引的叶子节点不直接存储数据行的地址呢？
 *  关于普通索引值的重复问题
 *  为什么对普通索引的全链表搜索会比主键索引的全表搜索效率高？

## 索引数据结构 ##

### 主键索引 ###

我们先来看看索引的数据结构，以及我们是如何利用索引来搜索数据的。MySQL的数据存储结构是B+树，在叶子节点存储了数据行，非叶子节点是主键索引。（MySQL的叶子节点是用双向链表链接的）

![在这里插入图片描述][35c17f9cefd64becabda6d2a1878e883.png_pic_center]  
![在这里插入图片描述][a00b2f70d8744acd8af09799eb3bccf2.png_pic_center]

![在这里插入图片描述][e4665da78b26412e9d9fa98400b7abe7.png_pic_center]

在MySQL中，表的数据行只存在于主键索引的叶子节点；因此查询任何数据都必须要搜索到叶子节点。B+树是一个多路平衡搜索树可以看出整个树结构是有序的（搜索过程与二叉搜索树的搜索过程相似）；（PS：如果不了解B+树可以先了解下它的结构和特性）

如果要搜索id =7的数据行它的搜索过程是怎样的呢？select \* from test\_a where id = 7;(id是表主键)  
![在这里插入图片描述][e86436b206af4b408fec5b6f8b8953db.png_pic_center]

因为搜索条件是id这是一个主键索引，因此在搜索数据时会直接利用B+树的结构做搜索。只用3次查找就可以找到id=7的数据行了。如果我们用年龄来做搜索条件呢？(select \* from test\_a where age = 78;)  
![在这里插入图片描述][fff9ef1c5f4c41ffb29fbada94385a81.png_pic_center]

只能全表挨个搜索了，因为年龄字段的存储是乱序的没有办法像搜索id那样利用id的有序性来做搜索。搜索7次才找到明显比上面的搜索次数多。并且有索引的行搜索所有的数据行都是3次（id=1~10），效率是很稳定的；实际在利用age做搜索条件时会搜索整个表，因为不确定后面是否有age=78的行。在数据量很大的时候，明显能感受到有索引和没有索引的差距。因为利用没有索引的字段做搜索条件会搜索整个表，而有索引的字段只需要根据B+树做搜索搜索次数是成指数减少的。

怎样让age作为搜索条件时，也可以减少搜索次数呢？给age建立一个B+树的结构—>建立索引。将表中所有行的age值用来建立一个B+树，那它的叶子节点也存储整个数据行吗？ 显然不能这样做，这样做太浪费空间了。MySQL的做法是叶子节点存储主键id的值，这样就可以跟数据行关联起来了。（**可以思考下：为什么不直接存储数据地址呢，岂不是更方便直接？**）

### 非主键索引 ###

![在这里插入图片描述][9d3dc364815b4b7a8bd4086a1c98e0ff.png_pic_center]

现在仍然用age做搜索条件：select \* from test\_a where age = 78；因为给age加了索引，因此搜索首先会搜索age的索引，也就是age值组成的B+树；

![在这里插入图片描述][54c3da55a15d4d66b224195b800d8805.png_pic_center]

当搜索找到了age=7的时候只能够拿到主键id的值，而我们需要的是整个数据行的值，因此还需要用id的值去搜索主键的索引（B+树）也就是所谓的回表。从这里看出，如果只需要id字段就不要使用`select *` 来查询了。（**在实际操作的时候，很少会使用select \* 但是有时侯就是需要查询其他字段比如 ：select id，name from test\_a where age =78.这个时候该如何优化呢？有没有一种方法能够避免回表的操作呢？**）

虽然使用索引可能会导致查找2个索引，但是在数据量特别大的时候相比较于没有索引的搜索也会快很多；使用索引搜索查找的次数与不使用索引相比是呈指数减少的。（**这里有一个问题，age的值可能有重复不是唯一索引，因此当搜索到叶子节点age=7的时候搜索还没有完成，MySQL会如何处理普通索引呢？是所有字段都适合建立索引吗？**）

## 索引在什么时候是有效的？ ##

可以回想下，MySQL是如何利用索引进行搜索的？其实很简单，就是利用被搜索的字段值和B+树节点的值作比较，通过结果选择该搜索哪个分支。每搜索一层就会过滤掉其他分支减少搜索的次数，B+树就是通过这种方式来提高搜索效率的。（可以类比二叉搜索树的过程）**简单来说，如果可以利用被搜索的字段值与B+树的节点值作比较，那就可以利用B+树来做搜索。如果被搜索字段不能与B+树的节点值比较，那索引就失效了。MySQL之所以要建立B+树来做搜索是因为查询效率很高，如果一个SQL语句经过MySQL的分析觉得用索引效率不高就不会走索引了，要看是否走了索引可以用explain关键字来查看。**

### 字符串比较大小 ###

在MySQL中比较字符串规则：是从第一个字符开始比较大小：如果第一个字符分出大小就返回；如果相同继续比较后面的字符；

![在这里插入图片描述][9412450115fa4f04a1e1f264e080d382.png_pic_center]

![在这里插入图片描述][91b10fc43c6c4516a0b2ddc1a008d86d.png_pic_center]

![在这里插入图片描述][632f38713c9541c2a2123f971d7f4bb7.png_pic_center]

CREATE TABLE test_a(
    id INT PRIMARY KEY ,
    NAME VARCHAR(20),
    age INT ,
    gender char(1)
    );
    
    INSERT INTO test_a VALUES(1,'qwe',12,'m');
    INSERT INTO test_a VALUES(2,'wer',122,'m');
    INSERT INTO test_a VALUES(3,'qa',23,'f');
    INSERT INTO test_a VALUES(4,'we',44,'m');
    INSERT INTO test_a VALUES(5,'wsx',67,'m');
    INSERT INTO test_a VALUES(6,'dc',89,'m');
    INSERT INTO test_a VALUES(7,'rfv',78,'m');
    INSERT INTO test_a VALUES(8,'yhn',54,'f');
    INSERT INTO test_a VALUES(9,'ikm',43,'f');
    INSERT INTO test_a VALUES(10,'pol',32,'f');
    
    SELECT * FROM test_a ;
    
    CREATE INDEX index_age ON test_a(age);

#### btween and ####

现在举几个例子判断一下索引是否失效:

# test_a有一个主键id索引，age索引
     
    explain select id from test_a   where  age between 10 AND  100;
    
    explain select * from test_a   where  age between 10 AND  100;
    
    explain select * from test_a   where  age between 10 AND  40;

*  第一个使用了索引；explain select id from test\_a where age between 10 AND 100;  
    ![在这里插入图片描述][2bc09b39ee7443efb7214579aa919806.png_pic_center]
 *  第二个没有使用索引；explain select \* from test\_a where age between 10 AND 100;  
    ![在这里插入图片描述][922a2194d59f41ad9b4ddc281118b66b.png_pic_center]
 *  第三个使用了索引；explain select \* from test\_a where age between 10 AND 40;  
    ![在这里插入图片描述][c918a673ae894aa4b029f9e03186c2d5.png_pic_center]

**前面2个SQL语句对比起来看：第一个sql为什么走了age的索引？第二个SQL不走索引？**

分析一下这2个SQL有什么不同？第一个SQL直接查询id，不会回表。第二个SQL查询所有字段，拿到id会返回主键索引查询。从这2条SQL的结果对比来看会得出一个结论：在索引上使用（between and 或者 age > mm and age <nn）来查询，如果要进行回表查询的话就不会走索引了。如果不回表还是会走索引。

**后面2个SQL语句对比起来看：第三个sql为什么走了age的索引？第二个SQL不走索引？**

可以看到（10-100）占表90%的数据，基本上没怎么过滤数据，而（10-40）占表30%的数据，过滤了大部分的数据。后者拿到了一个较小了结果集，前者基本算是没有过滤了。因为还要拿着查询到的主键id到主键索引树再查询一次，如果没有筛选掉大部分的数据，拿着90%的id主键到主键的B+树上查询。这种情况下MySQL认为不走age的索引，直接全表查询效率会更高。

后续将范围扩至（10-78）包含70%数据也走了索引，情况有点诡异。后续往表里面添加100条数据（age：1-100）总数居113条，范围(10-78),没有走索引。当范围降至（10-41）时又会走索引。  
![在这里插入图片描述][15ca12ad31814ce08278fee1ecb51b18.png_pic_center]

![在这里插入图片描述][e31c6a679f66415695251e205f08a37b.png_pic_center]  再往表里添加100条数据（age：1-100），总共213条数据，与数据113条时相比：（10-40）范围内的年龄占比还要小，但是没有走索引。这就需要结果集数据占比减小，才可能走索引。

![在这里插入图片描述][e45f4cb47087465ca8ab56a6b1269682.png_pic_center]

综合上面的情况，使用范围查询到底要不要走索引？如果需要返表查询会受到**表的数据总量**和**查询到的结果集占总表数据量的占比**这2个因素的影响。**表的数据量越大，想要走索引那么查询的结果集就必须占比越小。表的数据量小，即使结果集占比较高也会走索引。**

可以思考下面2个sql语句是否会走索引：

explain select *  from test_A where age != 67;
    explain select id  from test_A where age != 67;

#### 索引字段重复对索引的影响 ####

上面增加了200条数据，name值是重复的都是"wer3"；现在给name字段增加索引。测试下面的SQL是否走索引:

create index name_index  on test_a(name);
    
    explain select * from test_a where name like 'q%';
    
    explain select *  from test_a where name like 'wer%';
    
    explain select *  from test_a where name != 'wer3';

![在这里插入图片描述][e8dc96976e204ed5b73345f1ce6128a1.png_pic_center]

![在这里插入图片描述][b37462d188bc4ce6ab4bb6b14b9f907e.png_pic_center]  
![在这里插入图片描述][f755de8ecdac488d8567f5f88bab90d7.png_pic_center]

上面三条SQL中第1，3条都是走索引。他们的结果集有一个共同点：很小。总共213条数据：name=wer3占了200条，`name like 'q%' ,name!=wer3`能匹配的结果就很少，而`name like 'wer%'`会匹配到200多条结果。这个例子能得到与范围查询相似的结论：如果有回表查询操作，那么通过索引查询的结果集要越小才有可能走索引。同时通过这个例子也可以看出，如果字段值重复出现概率较高是不适合建立索引的。

前面2个例子：**between and 、模糊查询** 可能会出现不走索引的情况，不是因为索引不能用而是MySQL可能考虑到效率不高导致不使用索引。模糊查询还有一种是大家会谨慎使用的 ：‘%qwr%’ ,这种情况就是根本不能使用索引来检索数据了。前面分析了是如何利用索引要搜索的值在B+树上进行搜索的：利用要搜索的值在B+树上与节点值作比较来决定搜索的分支。对字符的比较是从第一个字符开始的：如果相同就继续比较下一个字符，不同就可以分出大小。而使用 "%qwr%"进行搜索时，不知道第一个字符是什么也就没有办法进行比较了。不能比较也就没有办法在B+树上进行高效的搜索了。只能遍历叶子节点来搜索结果，如果使用这种方式搜索那和没有索引是一样的。(除非要搜索的字段在索引上，这样可以在索引的叶子节点链表上遍历整个链表)。

那为什么可以利用 "wqe%“这种方式来进行搜索呢？其实很简单，在搜索的时候只需要比较"wqe"这三个字符，如果B+树上的节点值头三个字符是"wqe”,就只需要走小的分支(往左走)，这样搜索到叶子节点时再沿着链表往前搜索，如果搜索到前三个字符不匹配就可以结束搜索了，因为MySQL的判断大小的方式决定了’qwe%'匹配的结果一定是连续的；比如：qwe1,qwe7,qwer,qwez,wer,werr。。。；从qwe1 ->qwez结束，wer后面不可能再出现qwe开头的字符。

这只是用来理解为什么可以用类似于：qwe%，这种方式来进行模糊查询，实际MySQL不可能这么简单。这个搜索算法还可以改进：再加一个指针一个往大方向走确定最大值，这样就能确定一个大致的搜索范围，很快就可以确定搜索的边界。确定好边界之后只需要取边界内的数据，不用挨个比较了。

![在这里插入图片描述][3b95401a4eb641418eabd7430bf5d375.png_pic_center]

![在这里插入图片描述][20c60415b1aa4cf09a9addd6d0069cb6.png_pic_center]

## 联合索引 ##

![在这里插入图片描述][c2a6bd822d4f4e1e97698994a1e48ea6.png_pic_center]

假如在test\_a上，建立一个联合索引：create index name\_age\_index on test\_a(name,age);那么这个联合索引的结构就如上图，这个时候怎么构建B+树呢？

之前建立了两个普通索引：name\_index，age\_index ;在构建name\_index的时候，只需要根据name来构建B+树。在构建age\_index也是与name\_index一样，只需要根据age的值来构建一颗B+树；

现在想要根据name，age的值组建一个联合索引：name，age的值都要考虑了。在构建B+树时首先会比较name值的大小：如果name不同就可以决定大小，这个时候不用对age进行比较；如果name相同才会对age进行排序决定（name，age）的位置。

正是因为这种排序的机制，搜索的时候必定是从name开始比较，name相同才会与age比较，根据比对的结果来决定搜索的分支。这也是为什么写SQL的时候会有**最左原则**的原因。

为了排除干扰，把之前建立的普通索引name\_index，age\_index删除掉

drop index name_index on test_a
    drop index age_index on test_a

现在来看下面3个SQL是否会走索引:

EXPLAIN SELECT * FROM test_a WHERE NAME LIKE 'q%' AND age >0;
    
    EXPLAIN SELECT * FROM test_a WHERE NAME LIKE 'q%' AND age >60;
    
    EXPLAIN SELECT * FROM test_a WHERE NAME LIKE 'wer%' AND age >60

前面2个SQL走了索引，第三个没有走索引。

![在这里插入图片描述][2f3584edd8b0481790c9462e42f4204f.png_pic_center]

![在这里插入图片描述][a893e6494c2448b69f925422830ad1e9.png_pic_center]

![在这里插入图片描述][dba10b3a80e34e2fa4567e10af0d3545.png_pic_center]

第一二条SQL之所以会走索引是因为用name 过滤掉了大部分的数据。得到的结果集占比很小，所以走了索引。第三条SQL，用name过滤不了太多数据，得到的结果集占比很大因此没有走索引。要不要走索引应该是由name过滤掉的数据量大小来决定的和age关系不大，即使把搜索条件改为 `name like 'wer%' and age >300`也一样不会走索引。

随后往表里添加600条数据，总共813条数据，name是以其他字符开头，再用 `name like 'wer%' and age >60`来查询，结果就是用了索引。

delimiter $$  # 定义结束符
    drop procedure if exists addTestData1; 
    create procedure addTestData1()
    begin
    declare number int;
    set number = 1;
    while NUMBER <= 200 
    do
    insert into test_a(NAME,age,gender) VALUES(CONCAT('ggmk3',NUMBER),number,'m');
    set number = number + 1;
    end
    while;
    END $$;
    
    call addTestData1();

![在这里插入图片描述][8828126be14d410fb9b04f33c1bd3cfe.png_pic_center]

经过上面几个例子，可以总结一下：在使用联合索引时（得到的结果需要返表查询，才能得到最终的数据），要不要走索引取决于联合索引的第一个字段，如果通过第一个字段查询到的结果集占比小，就大概率会走索引。如果通过第一个字段查询道德结果集占比较大那么大概率就不会走索引。

联合索引可以解决返表查询的问题。比如：select id ，name，age from test\_a where name like ‘qwe%’;建立一个：(name,age)的联合索引，用name来查询时就不用返表，因为联合索引上存储的值已经覆盖了要查询的字段。

## 为什么普通索引的叶子节点不直接存储数据行的地址呢？ ##

如果直接存储数据行的地址，因为数据行的地址可能随时被改变：在增加数据时，可能会因为节点分裂而导致数据被转移到其他位置；在删除其他数据行时，可能会因为合并节点，而导致数据行的位置发生变化。如果直接存储id，就不会由这些问题了。

## 关于普通索引值的重复问题 ##

在本文的例子中使用了name作为一个索引，name可能是重复的。就比如有相同的name值：qwe，qwe；那么在利用普通索引进行搜索时，还需要检查一下该叶子节点前后是否有相同的值；如果有就要沿着链表继续查下去，如果没有就结束搜索。  
![在这里插入图片描述][d7fe71b6b24241038e6a5eced285db88.png_pic_center]  
  这也是普通索引搜索效率没有唯一索引效率高的原因。如果是唯一索引那么搜索到叶子节点整个搜索过程就结束了，不用再左右查找是否有相同的值。

## 为什么对普通索引的全链表搜索会比主键索引的全表搜索效率高？ ##

假如一张表里面有5000条数据，那么主键索引、普通索引的数据量都是5000条；主键索引、普通索引在全表搜索时，都是从链表的表头开始遍历搜索到表尾。问题是：都要遍历5000条数据，为什么普通索引遍历的效率要比主键索引的效率高呢？？

要回答这个问题，要从MySQL的存储结构说起，下图是MySQL的存储结构：

![在这里插入图片描述][47b76808bfbe4332a1915831ebf907ca.png_pic_center]

上图是MySQL的存储结构，MySQL的叶子节点就是存储在数据页：Page中；每一个Page都是固定的，默认16K。可以用`show global status like 'innodb_page_size';`来查看，在创建MySQL实例时可以设置Page的大小。

每个page的大小都是固定的，如果每一行的数据size比较小，就可以多存储数据行。一个主键索引的叶子节点数据行肯定比普通索引的数据行小，因为主键索引叶子节点包含了所有列而普通索引或者联合索引没有包含所有列。举个例子：如果主键索引一行数据是1K，那么一个Page就只能存储16行；一个普通索引0.5k，那么就可以存储32行。而MySQL从磁盘加载数据时都会从磁盘上读取1Page的数据；都是5000条数据，加载普通索引的磁盘io要比主键索引的io少一半，因此普通索引全表扫描的效率会主键索引的要高。

[35c17f9cefd64becabda6d2a1878e883.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/60e8e69cc3504246b7f8c77db850be4e.png
[a00b2f70d8744acd8af09799eb3bccf2.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e17eeeba2b4c4ad58853aaf69ff52273.png
[e4665da78b26412e9d9fa98400b7abe7.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1a80d85b82f54851a93885f469799a5f.png
[e86436b206af4b408fec5b6f8b8953db.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/94f8c9caf17e4cada8be4775ef50c548.png
[fff9ef1c5f4c41ffb29fbada94385a81.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a2b96949bd09492aa01c9c01eed2f6a4.png
[9d3dc364815b4b7a8bd4086a1c98e0ff.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/28ca5573d17943339fa770e8b726b8eb.png
[54c3da55a15d4d66b224195b800d8805.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/067080dfc2a14b5e9b83d6b8bb19b9ad.png
[9412450115fa4f04a1e1f264e080d382.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/afcdb102b2d8425f96f96168fd2812f0.png
[91b10fc43c6c4516a0b2ddc1a008d86d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c7e424a376354b16b87409244e05595e.png
[632f38713c9541c2a2123f971d7f4bb7.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c631dfebac13482d8496f9966788467d.png
[2bc09b39ee7443efb7214579aa919806.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ce575a3287544a9bbb5ae76873a61328.png
[922a2194d59f41ad9b4ddc281118b66b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/093e52226da24a16b20e25448717a9eb.png
[c918a673ae894aa4b029f9e03186c2d5.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a27fbcb79d1a444db958cce916ee6da7.png
[15ca12ad31814ce08278fee1ecb51b18.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/da8a310f584f44a8be7c138956976752.png
[e31c6a679f66415695251e205f08a37b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5205437652c94b60b0590974e305e366.png
[e45f4cb47087465ca8ab56a6b1269682.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/19bd5cb0ece041d69d3c715431fb3226.png
[e8dc96976e204ed5b73345f1ce6128a1.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/01bb46e2c4c24fad8b6dba379bf1d50f.png
[b37462d188bc4ce6ab4bb6b14b9f907e.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ca058d542b5940b0a143a70d2d524368.png
[f755de8ecdac488d8567f5f88bab90d7.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/325330936c3c407f9d4ae305152e38bb.png
[3b95401a4eb641418eabd7430bf5d375.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4619ab68351f4ec1ac834afd25d48816.png
[20c60415b1aa4cf09a9addd6d0069cb6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2206fce795784e7c90b15c54c56816f1.png
[c2a6bd822d4f4e1e97698994a1e48ea6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/068fb957633849dc9b15024e66714264.png
[2f3584edd8b0481790c9462e42f4204f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e461273d7274428184aec29d615d49f3.png
[a893e6494c2448b69f925422830ad1e9.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1bbfb77fd75c4aa48e3fc52801d0c52f.png
[dba10b3a80e34e2fa4567e10af0d3545.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4564677f46e349648627fcbce391945f.png
[8828126be14d410fb9b04f33c1bd3cfe.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a050b005254e4490a968da745fc79f4f.png
[d7fe71b6b24241038e6a5eced285db88.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/efc35c5814b741b2b97240f8f9d54f9d.png
[47b76808bfbe4332a1915831ebf907ca.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f0092f19992542c291ce33465e64719d.png