索引数据结构

主键索引

   咱们先来看看索引的数据结构，以及咱们是怎么运用索引来查找数据的。MySQL的数据存储结构是B+树，在叶子节点存储了数据行，非叶子节点是主键索引。（MySQL的叶子节点是用双向链表链接的）

Mysql索引失效的分析（8.0.19）

Mysql索引失效的分析（8.0.19）

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

  假设要查找id =7的数据行它的查找进程是怎样的呢？select * from test_a where id = 7;(id是表主键)

Mysql索引失效的分析（8.0.19）

  由于查找条件是id这是一个主键索引，因而在查找数据时会直接运用B+树的结构做查找。只用3次查找就能够找到id=7的数据行了。假设咱们用年纪来做查找条件呢？(select * from test_a where age = 78;)

Mysql索引失效的分析（8.0.19）

  只能全表挨个查找了，由于年纪字段的存储是乱序的没有办法像查找id那样运用id的有序性来做查找。查找7次才找到显着比上面的查找次数多。并且有索引的行查找一切的数据行都是3次（id=1~10），功率是很稳定的；实际在运用age做查找条件时会查找整个表，由于不确认后边是否有age=78的行。在数据量很大的时分，显着能感遭到有索引和没有索引的差距。由于运用没有索引的字段做查找条件会查找整个表，而有索引的字段只需求依据B+树做查找查找次数是成指数削减的。

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

非主键索引


Mysql索引失效的分析（8.0.19）


  现在仍然用age做查找条件：select * from test_a where age = 78；由于给age加了索引，因而查找首要会查找age的索引，也便是age值组成的B+树；

Mysql索引失效的分析（8.0.19）

   当查找找到了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中比较字符串规则：是从榜首个字符开端比较巨细：假设榜首个字符分出巨细就回来；假设相同持续比较后边的字符；

Mysql索引失效的分析（8.0.19）


Mysql索引失效的分析（8.0.19）


Mysql索引失效的分析（8.0.19）

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;

    Mysql索引失效的分析（8.0.19）

  • 第二个没有运用索引；explain select * from test_a where age between 10 AND 100;

    Mysql索引失效的分析（8.0.19）

  • 第三个运用了索引；explain select * from test_a where age between 10 AND 40;

    Mysql索引失效的分析（8.0.19）

  前面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）时又会走索引。

Mysql索引失效的分析（8.0.19）

Mysql索引失效的分析（8.0.19）
  再往表里添加100条数据（age：1-100），一共213条数据，与数据113条时相比：（10-40）规模内的年纪占比还要小，但是没有走索引。这就需求成果集数据占比减小，才或许走索引。

Mysql索引失效的分析（8.0.19）

   综合上面的情况，运用规模查询到底要不要走索引？假设需求返表查询会遭到表的数据总量和查询到的成果集占总表数据量的占比这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';

Mysql索引失效的分析（8.0.19）

Mysql索引失效的分析（8.0.19）
Mysql索引失效的分析（8.0.19）

  上面三条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不或许这么简单。这个查找算法还能够改进：再加一个指针一个往大方向走确认最大值，这样就能确认一个大致的查找规模，很快就能够确认查找的鸿沟。确认好鸿沟之后只需求取鸿沟内的数据，不必挨个比较了。

Mysql索引失效的分析（8.0.19）

Mysql索引失效的分析（8.0.19）


联合索引


Mysql索引失效的分析（8.0.19）

  假设在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走了索引，第三个没有走索引。

Mysql索引失效的分析（8.0.19）

Mysql索引失效的分析（8.0.19）

Mysql索引失效的分析（8.0.19）

  榜首二条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();

Mysql索引失效的分析（8.0.19）

  经过上面几个比方，能够总结一下：在运用联合索引时（得到的成果需求返表查询，才干得到终究的数据），要不要走索引取决于联合索引的榜首个字段，假设经过榜首个字段查询到的成果集占比小，就大概率会走索引。假设经过榜首个字段查询道德成果集占比较大那么大概率就不会走索引。

  联合索引能够解决返表查询的问题。比方：select id ，name，age from test_a where name like ‘qwe%’;树立一个：(name,age)的联合索引，用name来查询时就不必返表，由于联合索引上存储的值已经覆盖了要查询的字段。

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

  假设直接存储数据行的地址，由于数据行的地址或许随时被改变：在添加数据时，或许会由于节点分裂而导致数据被转移到其他方位；在删除其他数据行时，或许会由于合并节点，而导致数据行的方位发生变化。假设直接存储id，就不会由这些问题了。

关于一般索引值的重复问题

  在本文的比方中运用了name作为一个索引，name或许是重复的。就比方有相同的name值：qwe，qwe；那么在运用一般索引进行查找时，还需求检查一下该叶子节点前后是否有相同的值；假设有就要沿着链表持续查下去，假设没有就完毕查找。

Mysql索引失效的分析（8.0.19）
  这也是一般索引查找功率没有仅有索引功率高的原因。假设是仅有索引那么查找到叶子节点整个查找进程就完毕了，不必再左右查找是否有相同的值。

为什么对一般索引的全链表查找会比主键索引的全表查找功率高？

  假设一张表里边有5000条数据，那么主键索引、一般索引的数据量都是5000条；主键索引、一般索引在全表查找时，都是从链表的表头开端遍历查找到表尾。问题是：都要遍历5000条数据，为什么一般索引遍历的功率要比主键索引的功率高呢？？

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

Mysql索引失效的分析（8.0.19）

  上图是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少一半，因而一般索引全表扫描的功率会主键索引的要高。