欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

索引覆盖 最左前缀 索引下推

程序员文章站 2024-03-16 20:54:34
...

索引覆盖 最左前缀 索引下推

索引覆盖

总结问题

  1. 什么是索引覆盖?

  2. 怎么用到索引覆盖

  3. 索引覆盖的情况,using index ; using index using where

  4. select * from T where k betwee 3 and 5 这条语句的执行流程是什么样的? 边界查询

  5. 回表的概念是什么?

  6. 索引覆盖的概念是什么, 索引覆盖的优点是什么?

  7. 最左前缀原则的概念是什么? 说明最左前缀原则的执行过程.

  8. 索引下推的概念是什么? MySQL 5.6 的下推优化是怎么做的?

总结

  1. 查询的数据在索引树中可以找到,不需要回到数据树中去查找

  2. 查找select 的列必须被where的列(索引)覆盖,就能使用到索引覆盖(二级索引)

  3. 查询的列在索引树中可以查到,where条件为前导列

  4. 查询的列在索引树中,where条件不是联合索引的前导列

  5. 查询的列在索引树中,where是联合索引前导列的范围查询

    • 覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据
    • 最左前缀:联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符
    • 联合索引:根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。
    • 索引下推:like 'hello%’and age >10 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age<10的数据,再进行回表查询,减少回表率,提升检索速度

建表语句

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

索引覆盖 最左前缀 索引下推

select * from T where k between 3 and 5 

sql查询语句的执行流程:

  1. 在k索引树上找到k=3的记录,取得ID=300;

  2. 再到ID索引树查到ID=300对应的R3;

  3. 在k索引树上找到k=5的记录,取得ID=500;

  4. 再在ID索引树上找到ID=500对应的R4;

  5. 在k索引树上取得下一个值k=6,不满足条件,循环结束

    在这个过程中,**回到主键索引树搜索的过程,我们称为回表,**可以看到,这个查询流程读了k索引的3条记录(1.3.5),回表了两次(2.4)

索引覆盖

如果执行的sql语句是

select ID from T where k between 3 and 5

这时只需要查ID的值,而ID得值已经在k索引树上了,因此可以直接提供查询结果,不需要回表,也就是说,在这个查询里面,索引k已经”覆盖了”我们的查询需求,我们称为索引覆盖

由于索引覆盖可以减少树的搜索次数,显著提升性能,所以使用索引覆盖是一个常用的优化手段

扫描行数

需要注意的是,在引擎内部使用索引覆盖在索引k的记录上其实读了三个记录,R3~R5(还有后面的一条记录),但是对于MYSQL的server来说,他就是找引擎拿到了两条记录,因此认为扫描行数是2

执行计划中的索引覆盖情况

https://www.cnblogs.com/wy123/p/7366486.html

总结

查找select 的列必须被where的列(索引)覆盖,就能使用到索引覆盖

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

  • 执行计划中 using index using where,查询的列呗索引覆盖,并且where的筛选条件是索引列之一但是不是索引列的前导列,Extra中为using index using where,以为这无法直接通过索引查找来找到符合条件的数据

  • 查询的列呗索引覆盖,并且where筛选条件是索引列的前导列的一个范围,同样以为这无法直接通过索引查找到符合的数据

  • Using index不读数据文件,只从索引文件获取数据

  • Using where过滤元组和是否读取数据文件或索引文件没有关系

索引覆盖 最左前缀 索引下推

using index

https://www.cnblogs.com/wy123/p/7366486.html

using index and using shere

https://www.cnblogs.com/wy123/p/7366486.html

最左前缀

索引覆盖 最左前缀 索引下推

很好的问题,仔细考虑其中的原因,会有收获

解答:

  1. like ‘j’ 或 ‘j%’ 可以使用索引,并且快速定位记录。

  2. like ‘%j’ 或 ‘%j%’,只是在二级索引树上遍历查找记录,并不能快速 定位(扫描了整棵索引树)。

  3. 只有 id 和 uname 字段时,上述 4 种 like 查询,uname 索引能满足 id 和 uname 的查询情况,不需要回表,所以选择了使用 uname 的索引树解决问题。

  4. 添加了 age 但无联合索引 (uname, age) 的情况,如果使用 uname 索引树,需要回表。在 like ‘%j’ 或 ‘%j%’ 直接扫描主键索引树,现象就是没有使用 uname 索引。

  5. 添加了 age 字段,也添加了 (uname, age) 索引,和第 3 点同理,使用覆盖索引就能满足 select * 的字段查询,不需要回表,因此使用了 (uname, age) 索引树。但是只有 like ‘j’ 和 ‘j%’
    能快速定位记录,而 like ‘%j’ 和 ‘%j%’ 也能使用该索引树,但是不能快速定位,需要顺序遍历。

二级索引比主键索引快的场景

数据量大的时候,二级索引的覆盖索引会比主键查询快,主键索引的记录的数据量很大,二级索引的数据小,并且索引覆盖

索引覆盖 最左前缀 索引下推

索引下推

总结

  1. 索引下推是什么?

  2. 索引下推的好处?

  3. 索引下推的场景?

    select 列不是or不全包括在二级索引中,where中是索引的列有范围或者模糊查询的时候,不直接取最左前缀回表取数据在做比较,会先比较索引列的其他值,然后在回表,减少回表次数.

场景

索引覆盖 最左前缀 索引下推

索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段,查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引。

假设表t有联合索引(a,b),下面语句可以使用索引下推提高效率

select * from t where a > 2 and b > 10;

索引是name age的联合索引

查询名字第一个是张,并且年龄是10岁的所有男孩

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

这个sql只用到了索引前缀的ID3

索引下推

从5.6之前,只能从ID3开始一个一个回表,到主键索引上找到数据行,在对比字段值

而5.6以后引入的索引下推优化(index condition pushdown),可以在遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数.

索引覆盖 最左前缀 索引下推

图3中,在(name,age)索引里面我特意去掉了age的值,这个过程InnoDB并不会看age的值,只是顺序的吧”name”第一个是”张”的记录一条条取出来回表,因此需要回表4次

图4和图3的区别是,InnoDB在(name,age)索引内部就判断了age是否等于10,对于不等于10的记录,直接跳过,在这个例子中,只需要对ID4.ID5这两条记录回表去数据判断,就只需要回表2次

相关标签: MYSQL骑马的路

上一篇: 演算法 - 排序

下一篇: nuxt使用vuex