您现在的位置是:首页 > 正文

回表与覆盖索引,索引下推

2024-04-01 01:18:17阅读 1

一、什么是回表查询?

通俗的讲就是,如果索引的列在 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 索引树搜索一次。这个过程虽然用了索引,但实际上底层进行了两次索引查询,这个过程就称为回表。

也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

我这里表里的数据量比较少,如果数据量大的话,你能很明显的看出两次查询所用的时间,很明显使用主键查询效率更高。

更多如下图:

img

(1)先通过普通索引定位到主键值id=5;
(2)在通过聚集索引定位到行记录;
这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。

小总结

使用聚集索引(主键或第一个唯一索引)就不会回表,普通索引就会回表。

二、什么是索引覆盖?

只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

explain的输出结果Extra字段为Using index时,能够触发索引覆盖。

三、如何实现索引覆盖?

1、常见的方法是:将被查询的字段,建立到联合索引里去。

例子

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

第一个sql:
select id,name from user where name=‘shenjian’;

img

能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。

Extra:Using index。

第二个sql:
select id,name,sex from user where name=‘shenjian’;

img

能够命中name索引,索引叶子节点存储了主键id,没有储存sex,sex字段必须回表查询才能获取到,不符合索引覆盖,需要再次通过id值扫描聚集索引获取sex字段,效率会降低。

Extra:Using index condition。

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create table user1 (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;

img

可以看到:

select id,name … where name=‘shenjian’;
select id,name,sex … where name=‘shenjian’;
单列索升级为联合索引(name, sex)后,索引叶子节点存储了主键id,name,sex,都能够命中索引覆盖,无需回表。

画外音,Extra:Using index。

四、哪些场景可以利用索引覆盖来优化SQL?

场景1:全表count查询优化

img

原表为:
user(PK id, name, sex);

直接:
select count(name) from user;
不能利用索引覆盖。

添加索引:
alter table user add key(name);
就能够利用索引覆盖提效。

场景2:列查询回表优化

这个例子不再赘述,将单列索引(name)升级为联合索引(name, sex),即可避免回表。

场景3:分页查询

将单列索引(name)升级为联合索引(name, sex),也可以避免回表。

五、如何创建有效的索引

如果需要索引很长的字符串,此时需要考虑前缀索引
  • 前缀索引即选择所需字符串的一部分前缀作为索引,这时候,需要引入一个概念叫做索引选择性,索引选择性是指不重复的索引值与数据表的记录总数的比值,可以看出索引选择性越高则查询效率越高,当索引选择性为1时,效率是最高的,但是在这种场景下,很明显索引选择性为1的话我们会付出比较高的代价,索引会很大,这时候我们就需要选择字符串的一部分前缀作为索引,通常情况下一列的前缀作为索引选择性也是很高的
如何选择前缀
  • 计算该列完整列的选择性,使得前缀选择性接近于完整列的选择性
使用多列索引
  • 尽量不要为多列上创建单列索引,因为这样的情况下最多只能使用一星索引,这样的话,不如去创建一个全覆盖索引,在多列上创建单列索引大部分情况下并不能提高 MySQL 的查询性能,MySQL 5.0 中引入了合并索引,在一定程度上可以表内多个单列索引来定位指定的结果,但是 5.0 以前的版本,如果 where 中的多个条件是基于多个单列索引,那么 MySQL 是无法使用这些索引的,这种情况下,还不如使用 union
选择合适的索引列顺序
  • 经验是将选择性最高的列放到索引最前列,可以在查询的时候过滤出更少的结果集
  • 但这样并不总是最好的,如果考虑到 group by 或者 order by 等情况,再比如考虑到一些特别场景下的 guest 账号等数据情况,上面的经验法则可能就不是最适用的
覆盖索引
  • 所谓覆盖索引就是指索引中包含了查询中的所有字段,这种情况下就不需要再进行回表查询了
  • MySQL 中只能使用 B-Tree 索引做覆盖索引,因为哈希索引等都不存储索引的列的值,覆盖索引对于 MyISAM 和 InnoDB 都非常有效,可以减少系统调用和数据拷贝等时间
  • Tips:减少 select * 操作
使用索引扫描来做排序
  • MySQL 生成有序的结果有两种方法:通过排序操作,或者按照索引顺序扫描;使用排序操作需要占用大量的 CPU 和内存资源,而使用 index 性能是很好的,所以,当我们查询有序结果时,尽量使用索引顺序扫描来生成有序结果集
怎样保证使用索引顺序扫描:
  • 索引列顺序和 ORDER BY 顺序一致
  • 所有列的排序方向一致
  • 如果关联多表,那么只有当 ORDER BY 子句引用的字段全部为第一张表时,才能使用索引做排序,限制依然是需要满足索引的最左前缀要求
压缩索引
  • MyISAM 中使用了前缀压缩技术,会减少索引的大小,可以在内存中存储更多的索引,这部分优化默认也是只针对字符串的,但是可以自定义对整数做压缩
  • 这个优化在一定情况下性能比较好,但是对于某些情况可能会导致更慢,因为前缀压缩决定了每个关键字都必须依赖于前面的值,所以无法使用二分查找等,只能顺序扫描,所以如果查找的是逆序那么性能可能不佳
减少重复、冗余以及未使用的索引
  • MySQL 的唯一限制和主键限制都是通过索引实现的,所以不需要在同一列上增加主键、唯一限制再创建索引,这样是重复索引
  • 再举个例子,如果已经创建了索引(A,B),那么再创建索引(A)的话,就属于重复索引,因为 MySQL 索引是最左前缀,所以索引(A,B)本身就可以使用索引(A),但是创建索引(B)的话不属于重复索引
  • 尽量减少新增索引,而应该扩展已有的索引,因为新增索引可能会导致 INSERT、UPDATE、DELETE 等操作更慢
  • 可以考虑删除没有使用到的索引,定位未使用的索引,有两个办法,在 Percona Server 或者 MariaDB 中打开 userstates 服务器变量,然后等服务器运行一段时间后,通过查询 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查询到每个索引的使用频率
索引和锁
  • InnoDB 支持行锁和表锁,默认使用行锁,而 MyISAM 使用的是表锁,所以使用索引可以让查询锁定更少的行,这样也会提升查询的性能,如果查询中锁定了1000行,但实际只是用了100行,那么在 5.1 之前都需要提交事务之后才能释放这些锁,5.1 之后可以在服务器端过滤掉行之后就释放锁,不过依然会导致一些锁冲突
减少索引和数据碎片
  • 首先我们需要了解一下为什么会产生碎片,比如 InnoDB 删除数据时,这一段空间就会被留空,如果一段时间内大量删除数据,就会导致留空的空间比实际的存储空间还要大,这时候如果进行新的插入操作时,MySQL 会尝试重新使用这部分空间,但是依然无法彻底占用,这样就会产生碎片
  • 产生碎片带来的后果当然是,降低查询性能,因为这种情况会导致随机磁盘访问
  • 可以通过 OPTIMIZE TABLE 或者重新导入数据表来整理数据

三、什么是索引下推

假设有这么个需求,查询表中“名字第一个字是张,性别男,年龄为10岁的所有记录”。那么,查询语句是这么写的:

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

根据前面说的“最左前缀原则”,该语句在搜索索引树的时候,只能匹配到名字第一个字是‘张’的记录(即记录ID3),接下来是怎么处理的呢?当然就是从ID3开始,逐个回表,到主键索引上找出相应的记录,再比对age和ismale这两个字段的值是否符合。

但是!MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表字数
下面图1、图2分别展示这两种情况。

imgimg

图 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的部分过滤操作,在某些场景下,可以大大减少回表次数,从而提升整体性能。

网站文章

  • Mybatis框架基础案例实践

    Mybatis框架基础案例实践

    mybatis关于数据库的基本操作,增删改查(入门级)

    2024-04-01 01:18:04
  • 预言自证有意义,但不能包治百病

    最近的系列文章,其实是希望给读者厘清,当前一些经济问题,社会问题背后的因果根源,当然,鉴于某些众所周知或不周知的原因,有的文章已被删除,有些内容也不方便展开讨论。那么有读者会觉得,你不过说了一些很浅显...

    2024-04-01 01:17:38
  • Github搭建个人博客(2018最新版)

    Github搭建个人博客(2018最新版)

    前言 之前一直使用CSDN记录和分享自己的所学,突发奇想就想搞一个自己的博客,在网上搜索了很多教程后发现,有的教程要么已经是很老,要么过于复杂,然后就决定写一篇比较适合小白搭建博客的方法。然后就想到了...

    2024-04-01 01:17:31
  • 请帮我写一个2023年春季医药人才网络招聘会的企业参会邀约文案

    尊敬的医药企业负责人,您好!2023年春季医药人才网络招聘会即将举行,诚邀贵企业参会。本次招聘会由医药人才网主办,将于3月底至4月初在线举行,为企业提供优质的人才招聘服务。作为医药行业领先的企业,贵企业在人才招聘方面一直保持着敏锐的洞察力和高水平的专业性。此次招聘会将为贵企业提供一个展示企业实力、招揽优秀人才的平台,让您的企业在竞争激烈的市场中占据优势。我们相信,贵企业的加入将为本次招聘会...

    2024-04-01 01:17:25
  • popupwindow通过外部控件进行popupwindow间的切换

    最近有个项目,用到了popupwindow,还是在一个activity中用到了多个popupwindow,这就是涉及到popupwindow间的切换了。但每次弹出popupwindow后,想通过点击外部控件进行进行popupwindow间的切换,会发现点击外部控件的时候,只响应了隐藏当前popupwindow的事件,外部控件的点击事件并没有执行,需再次点击外部控件,才弹出popupwindow,这

    2024-04-01 01:17:00
  • Elasticsearch 插入时间字段时数据格式问题

    elasticsearch 时间格式 elasticsearch创建index的之后,可以设置mapping。 如果mapping中没有设置date的format,那么默认为两种格式 strict_d...

    2024-04-01 01:16:53
  • Box Shadow(阴影)-Css3写法示例

    Box Shadow(阴影)-Css3演示 -moz-box-shadow:2px 2px 5px #333333; -webkit-box-shadow:2px 2px 5px #333333; box-shadow:2px 2px 5px #333333; transform(变形)和transform-origin(变形原点) -moz-transform:rotate(0deg)...

    2024-04-01 01:16:47
  • Python程序设计教程 第十五章下

    Python程序设计教程 第十五章下

    2024-04-01 01:16:41
  • java.sql.SQLException: Unable to load authentication plugin ‘caching_sha2_password‘.

    错误描述java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'. at com.mysq...

    2024-04-01 01:16:16
  • html 合并table列,EL表达式合并table列rowspan

    html 合并table列,EL表达式合并table列rowspan

    如下图问题的描述: 如图所示需要将发货批次相同的列合并,对应的收货状态和查看备注合并。刚开始的思路只限于用js写,后来经过同事的指点发现永EL表达式就能很好的解决,看代码:发货情况发货批次商品发货数量...

    2024-04-01 01:16:09