网站提交地址,wordpress导航菜单 设置,中国建设银行安徽省分行招聘网站,医院网站php源码一、什么是回表查询#xff1f;
通俗的讲就是#xff0c;如果索引的列在 select 所需获得的列中#xff08;因为在 mysql 中索引是根据索引列的值进行排序的#xff0c;所以索引节点中存在该列中的部分值#xff09;或者根据一次索引查询就能获得记录就不需要回表#x…一、什么是回表查询
通俗的讲就是如果索引的列在 select 所需获得的列中因为在 mysql 中索引是根据索引列的值进行排序的所以索引节点中存在该列中的部分值或者根据一次索引查询就能获得记录就不需要回表如果 select 所需获得列中有大量的非索引列索引就需要到表中找到相应的列的信息这就叫回表。
InnoDB聚集索引的叶子节点存储行记录因此 InnoDB必须要有且只有一个聚集索引
1如果表定义了主键则PK就是聚集索引 2如果表没有定义主键则第一个非空唯一索引not NULL unique列是聚集索引 3否则InnoDB会创建一个隐藏的row-id作为聚集索引
先创建一张表sql 语句如下
create table xttblog(id int primary key, k int not null, name varchar(16),index (k)
)engine InnoDB;然后我们再执行下面的 SQL 语句插入几条测试数据。
INSERT INTO xttblog(id, k, name) VALUES(1, 2, xttblog),(2, 1, 业余草),(3, 3, 业余草公众号);假设现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式则只需要搜索 ID 这棵 B 树。主键是唯一的根据这个唯一的索引MySQL 就能确定搜索的记录。
但当我们使用 k 这个索引来查询 k 2 的记录时就要用到回表。select * from xttblog where k 2; 原因是通过 k 这个普通索引查询方式则需要先搜索 k 索引树然后得到主键 ID 的值为 1再到 ID 索引树搜索一次。这个过程虽然用了索引但实际上底层进行了两次索引查询这个过程就称为回表。
也就是说基于非主键索引的查询需要多扫描一棵索引树。因此我们在应用中应该尽量使用主键查询。
我这里表里的数据量比较少如果数据量大的话你能很明显的看出两次查询所用的时间很明显使用主键查询效率更高。
更多如下图
1先通过普通索引定位到主键值id5 2在通过聚集索引定位到行记录
这就是所谓的回表查询先定位主键值再定位行记录它的性能较扫一遍索引树更低
小总结
使用聚集索引主键或第一个唯一索引就不会回表普通索引就会回表。
二、什么是索引覆盖
只需要在一棵索引树上就能获取SQL所需的所有列数据无需回表速度更快。
explain的输出结果Extra字段为Using index时能够触发索引覆盖。
三、如何实现索引覆盖
1、常见的方法是将被查询的字段建立到联合索引里去。 例子
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engineinnodb;第一个sql
select id,name from user where nameshenjian;能够命中name索引索引叶子节点存储了主键id通过name的索引树即可获取id和name无需回表符合索引覆盖效率较高。
ExtraUsing index。
第二个sql
select id,name,sex from user where nameshenjian;能够命中name索引索引叶子节点存储了主键id没有储存sexsex字段必须回表查询才能获取到不符合索引覆盖需要再次通过id值扫描聚集索引获取sex字段效率会降低。
ExtraUsing index condition。
如果把(name)单列索引升级为联合索引(name, sex)就不同了。 create table user1 (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engineinnodb; 可以看到
select id,name … where name‘shenjian’; select id,name,sex … where name‘shenjian’;
单列索升级为联合索引(name, sex)后索引叶子节点存储了主键idnamesex都能够命中索引覆盖无需回表。
画外音ExtraUsing index。
四、哪些场景可以利用索引覆盖来优化SQL
场景1全表count查询优化 原表为 user(PK id, name, sex)
直接 select count(name) from user; 不能利用索引覆盖。
添加索引 alter table user add key(name); 就能够利用索引覆盖提效。
场景2列查询回表优化
这个例子不再赘述将单列索引(name)升级为联合索引(name, sex)即可避免回表。
场景3分页查询
将单列索引(name)升级为联合索引(name, sex)也可以避免回表。
五、什么是索引下推
索引下推Index condition pushdown简称ICP是一种优化数据库查询的技术它利用了数据库索引的特性在一定条件下在索引层面就过滤掉不需要的数据从而减少查询时需要访问的数 据块提高查询效率。
在普通的查询中数据库需要先从表中读取所有的数据记录然后再根据查询条件过滤不需要的记录最后返回查询结果。而在索引下推中数据库会在索引树的节点上进行条件过滤只将满足条件的数据块返回而不是读取整个数据记录。这样可以避免从磁盘读取不必要的数据降低IO开销提升查询速度。
索引下推的主要优点是减少了回表操作即减少了访问磁盘的次数和需要传输的数据量从而提高了查询效率和响应速度。具体来说如果查询条件涉及到的字段都可以通过索引直接获取而不需要回表操作那么查询速度将大大提高。
需要注意的是索引下推并不是适用于所有类型的查询它涉及到查询中所使用的索引类型和查询条件的限制。通常只有涉及到等值查询或范围查询的情况下才能使用索引下推技术实现优化。同时索引下推也会产生额外的开销需要消耗更多的CPU资源因此需要在实际应用中进行评估和优化。
MySQL的大概框架为 索引下推的下推其实就是指将部分上层服务层负责的事情交给了下层引擎层去处理。
假设有这样一个用户表
创建一个联合索引(age, birthday)并查询出年龄20且生日为03-01的用户select * from user where age20 and birthday“03-01”为在没有索引下推的情况下执行步骤如下
存储引擎根据索引查找出age20的用户id分别是4,5,7存储引擎到表格中取出id in (4,5,7)的3条记录返回给服务层服务层过滤掉不符合birthday03-01条件的记录最后返回查询结果为id4的1行记录。
如果开启了索引下推优化执行步骤如下
存储引擎根据索引查找出age20的用户id并使用索引中的birthday字段过滤掉不符合birthday03-01条件的记录最后得到id4存储引擎到表格中取出id4的1条记录返回给服务层服务层过滤掉不符合birthday03-01条件的记录最后返回查询结果为id4的1行记录。启用索引下推后把where条件由MySQL服务层放到了存储引擎层去执行带来的好处就是存储引擎根据id到表格中读取数据的次数变少了。在上面这个例子中没有索引下推时需要多回表查询2次。并且回表查询很可能是离散IO在某些情况下对数据库性能会有较大提升。 假设有这么个需求查询表中“名字第一个字是张性别男年龄为10岁的所有记录”。那么查询语句是这么写的
mysq select * from tuser where name like 张 % and age10 and ismale1;根据前面说的“最左前缀原则”该语句在搜索索引树的时候只能匹配到名字第一个字是‘张’的记录即记录ID3接下来是怎么处理的呢当然就是从ID3开始逐个回表到主键索引上找出相应的记录再比对age和ismale这两个字段的值是否符合。
但是MySQL 5.6引入了索引下推优化可以在索引遍历过程中对索引中包含的字段先做判断过滤掉不符合条件的记录减少回表字数。 下面图1、图2分别展示这两种情况。 图 1 中在 (name,age) 索引里面我特意去掉了 age 的值这个过程 InnoDB 并不会去看 age 的值只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此需要回表 4 次。
图 2 跟图 1 的区别是InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10对于不等于 10 的记录直接判断并跳过。在我们的这个例子中只需要对 ID4、ID5 这两条记录回表取数据判断就只需要回表 2 次。
总结
如果没有索引下推优化或称ICP优化当进行索引查询时首先根据索引来查找记录然后再根据where条件来过滤记录在支持ICP优化后MySQL会在取出索引的同时判断是否可以进行where条件过滤再进行索引查询也就是说提前执行where的部分过滤操作在某些场景下可以大大减少回表次数从而提升整体性能。
