本次课程讲义重点:

1、什么是聚集索引和辅助索引。

2、什么是索引最左原则。

3、什么是全表扫描、索引查找、索引范围扫描。

4、如何利用索引进行排序。

5、简单的单表带条件查询,是如何在索引数据结构 上运行的,分几种情况。

6、回表是什么意思,回表有哪些情况需要讨论。

7、什么是索引下推和MRR,都解决了什么问题。

8、什么是MYSQL执行计划评估。

过滤条件的几种情况讨论

建立主键索引

辅助索引

1、Using index

查询的列被索引覆盖,并且where筛选条件是索引的是前导列,Extra中为Using index

explain select id3 from t1 where id3 = 1;

采用的是索引查找,所谓索引查找就是在B+树上随机查找某一个值,索引查找后在在索引上能获取到所需字段,无需回表。

2、Using where Using index

查询的列被索引覆盖,并且where筛选条件是索引列之一但是不是索引的不是前导列,Extra中为Using where; Using index

explain select id3 from t1 where content like '%a%';

采用的是索引全表扫描,type为index,所谓索引全表扫描就是直接遍历整个B+树,content不是索引前导列,所以必须扫描整个B+树,才能查找到所有符合条件的记录,但是不需要回表,因为索引上本来就有id3这个字段。

查询的列被索引覆盖,并且where筛选条件是索引列前导列的一个范围。

explain select id3 from t1 where id3 > 3;

采用的是索引范围扫描,type为range,所谓索引范围扫描不需要遍历整个B+树,遍历B+树的一部分就可以了,因为id3是索引前导列,是先按照id3排序再按照content排序的,根据B+树的特点,id3的范围确定了,B+树的遍历范围也就确定了。

✔注意:

索引全表扫描和索引范围扫描都叫索引扫描,只是范围不同。

索引扫描在以上两个例子中指的只是使用索引,使用索引和索引查找是两个极易弄混的概念,索引本身就是一个含有数据的数据结构,也是包含了一定的数据信息的,是可以直接使用的,把索引看成原表的临时子表。

索引查找是根据B+树的查找特性,进行数据查找,是真正使用了B+树特性,降低时间复杂度查找的特性。

查询的列未被索引覆盖,并且where筛选条件是索引的前导列

explain select id2, id3 from t1 where id3 = 1;

筛选条件是索引的前导列,意味着可以使用索引,查询的列未被索引覆盖,意味着使用索引查找查到聚集索引的KEY后,还需要在聚集索引上进行回表查找,type为ref。

4、Using where

查询的列未被索引覆盖,where筛选条件非索引的前导列,Extra中为Using where

explain select id2 from t1 where content like '%a%';

这种情况只读取索引是不能获取到需要的数据了,那只能读取聚集索引,过滤条件也不是聚集索引的前导列,只能是全表扫描了。

查询的列未被索引覆盖,where筛选条件非索引列,Extra中为Using where

explain select id2 from t1 where id2 = 1;

where筛选条件非索引列的情况下,没有可用的索引,只能在聚集索引上进行全表扫描。

✔注意:

全表扫描指的是在聚集索引的叶子节点进行扫描,因为叶子节点存放了所有的行数据。

5、Using index condition

查询的列不全在索引中,where条件中是一个前导列的范围。

explain select id2, id3 from t1 where id3 < 1 and content like '%a%';

如果没有索引下推,id3 < 1会在存储引擎层过滤,而content like '%a%'会在服务层过滤

explain select id2, id3 from t1 where id3 = 1 and content like '%a%';

如果没有索引下推,id3 = 1会在存储引擎层过滤,而content like '%a%'会在服务层过滤

使用索引下推后,全部都在存储引擎层过滤。

索引下推 ICP

通俗说是在查询辅助索引时,涉及的辅助索引字段分为两部分,一部分是索引前导列,用于缩小索引范围,剩余部分字段虽然对缩小范围没有帮助,但是可以在索引扫描的时候进行过滤。

上述两个查询语句id3 < 1和id3 = 1都可以缩小范围,content like '%a%'在索引扫描时参与过滤。

关闭ICP后

set optimizer_switch='index_condition_pushdown=off';

上述第5点的查询语句,分别运行后如图

content like '%a%'在索引扫描时不参与过滤,而是在索引中找到id3 < 1的记录,然后在服务器层再进行content like '%a%'的过滤。

Extra中为Using where

同理,在索引中找到id3 = 1的记录,然后在服务器层再进行content like '%a%'的过滤。

还可以继续测试,如果在ICP的前提下,继续在查询条件上增加了非索引列的条件,这时情况就更复杂了,这种非索引列的条件会在回表后再过滤一次。

explain select id2, id3 from t1 where id3 = 1 and content like '%a%' and id2 = 1;

explain select id2, id3 from t1 where id3 < 1 and id2 = 1;

Extra中为Using index condition; Using where,先使用索引下推过滤索引列的数据,然后回表后再进行非索引列id2 = 1的过滤。

学习数据库底层原理时,这种细节的优化设计非常多,我们要深入理解设计意图,这才是可以提高能力的东西。

索引下推 MRR

第一步,先根据where条件中的辅助索引获取辅助索引与主键的集合。

第二步,将结果集rest放在buffer里面(read_rnd_buffer_size大小直到      buffer满了),然后对结果集rest按照pk_column排序。

第三步,利用已经排序过的结果集,访问表中的数据,此时是顺序IO。

SET SESSION optimizer_switch='mrr_cost_based=off';

explain select * from t1 where id3 < 1;

select @@optimizer_switch;

SET SESSION optimizer_switch='mrr_cost_based=on';

可以看到,在ICP的基础上使用了MRR进行回表的优化。

从图中可以看出,相当于是将随机IO转变为顺序IO,所以在IO量大时会有较大程度的性能提升。

THE  END

到这里《

MYSQL索引入门

》课件已发送完毕,如有疑问的同学,可以在群里沟通交流,一起来学习吧~~~