专栏首页谭小谭如何让mysql索引更快一点

如何让mysql索引更快一点

后端开发,公众号内容包括但不限于 python、mysql、数据结构和算法、网络协议、Linux。技术人怎能只有技术和代码,如果你对投资理财、保险,英语学习、读书写作有兴趣,都欢迎来公众号【谭某人】与我交流,你总会有些收获。

在 InnoDB 中,从二级索引回到主键索引查询数据,这个过程称作回表过程,而且这个回表过程是可以被优化的,这个优化就是利用覆盖索引

先说结论,如果一个索引的字段包含了所有要查询的字段,这个索引就称作覆盖索引,覆盖索引可以减少回表过程,能有效提高查询效率

前面我们有说过,在 InnoDB 中数据都是保存在 B+ 树上,主键索引保存了整行记录,二级索引保存了主键的值。

一次查询操作,要么是遍历主键索引,要么是遍历二级索引,要么就是先遍历二级索引得到主键 id 的值,然后再到主键索引上通过主键 id 查找满足要求的记录。

如果只遍历一次 B+ 树就能获取到我们要的数据,即没有回表过程,这个效率显然是不错的,这就是覆盖索引的优势。下面看个具体的例子。

mysql> create table user(
id int(11) primary key, 
name varchar(20) not null, 
age int(11),
sex int(11),
index (age)) engine=InnoDB;

依然是新建一个表,创建索引,插入一些测试数据,注意这里只是为了解释说明覆盖索引,并不表示 mysql 的真实执行方式,因为会涉及到 mysql 的优化器机制,这里暂且不说了,以后再写。

1    bob 16  1
2    kom 19  0
3    gum 18  1
4    tt  20  1
5    yy  25  1

创建一个 user 表,给 age 字段添加一个二级索引,并插入上面五条数据,然后看下面这条查询语句。

select name from user where age between 18 and 21

我们来分析下这条 sql 的执行过程:

1、age 字段上有索引,mysql 会先到 age 字段的 B+ 树上找到满足条件的第一个叶子节点(age=19),这个叶子节点上保存了对应主键 id 的值 2,然后再到主键索引上找到 id 为 2 的这条记录,同时把 name 字段拿出来。

2、重复第一步的操作,继续从 age 索引上的叶子节点往后遍历找出满足条件的第二个叶子节点,同样回到主键上拿出 name 字段的值,直到遍历到不满足条件的叶子节点(age=25)。

也就是说,这条 sql 语句虽然用到了索引,但是 age 索引上并没有要查询的 name 字段,所以只能回表到主键索引上查出 name 字段,所以这个过程其实是遍历了个两个 B+ 树。

那么我们删除 age 这个单列索引,创建一个覆盖索引 (age,name), 把要查询的 name 字段也添加到索引中来。

#删除原索引
drop INDEX age on USER
#新建覆盖索引
ALTER TABLE USER add index age_name(age,name)

由于现在这个覆盖索引上的字段包含了要查询的 age 和 name 字段,免去了到主键索引上查询数据的过程,其实也就是只遍历了一个 B+ 树,可以大大提升查询效率。

添加索引虽然能提升查询效率,但索引也是需要占用额外空间的,而且索引还需要维护成本,所以通常加不加索引需要根据实际需求来权衡。

总之,在设计索引或者优化 sql 语句的时候,要尽量避免回表操作,所以使用覆盖索引是一种常用的 sql 优化手段。

所以我们平时写 sql 语句的时候,select 后面只写查询需要用到的字段,去掉不需要的字段,避免回表操作。

有问题欢迎大家留言交流,原创不易,如果文章对你有帮助,欢迎点在看,感谢支持。

推荐文章:

mysql为什么加索引就能快

mysql索引为啥要选择B+树 (下)

本文分享自微信公众号 - 谭小谭(tanstory),作者:谭小谭

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-04-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • mysql为什么加索引就能快

    平时我们要优化 mysql 查询效率的时候,最常见的就是给表加上合适的索引了,那今天就来聊聊为什么加了索引就快了呢。

    谭小谭
  • 听说mysql还会选错索引

    大家都知道,mysql 一个表中可以创建多个索引,但是在执行一条查询语句的时候,mysql 只能选一个索引,如果我们没有指定 mysql 使用某个索引,那么就是...

    谭小谭
  • python 自动监测并拷贝U盘文件

    今天给大家介绍一个可以偷偷拷贝别人 U 盘里文件的 python 程序,没错,程序自动监测 U 盘插入并读取 U 盘内文件,拷贝到本地电脑。

    谭小谭
  • 如何让MySQL索引更快一点?

    在 InnoDB 中,从二级索引回到主键索引查询数据,这个过程称作回表过程,而且这个回表过程是可以被优化的,这个优化就是利用覆盖索引。

    江南一点雨
  • MySQL 索引

    数据库的索引是一个要点, 无论是面试还是在工作中, 这个知识点都很常会用到, 你可能只是用过索引, 知道加了索引可以提高查询的性能, 但不知道为什么这样, 今天...

    一份执着✘
  • 我去,为什么最左前缀原则失效了?

    最近,在 mysql 测试最左前缀原则,发现了匪夷所思的事情。根据最左前缀原则,本来应该索引失效,走全表扫描的,但是,却发现可以正常走索引。

    烟雨星空
  • 超实用的索引知识介绍

    索引是MySQL数据库中的重要对象之一,索引的目的在于提高查询效率。可以类比字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。索引...

    MySQL技术
  • 数据库主键和外键

    聚集索引,在索引页里直接存放数据,而非聚集索引在索引页里存放的是索引,这些索引指向专门的数据页的数据。

    一觉睡到小时候
  • Elasticsearch 索引生命周期管理详解与实践汇总篇

    Elasticsearch 从版本6.8开始已经免费开放索引生命周期管理的功能,通过该功能我们可以实现日志索引不同阶段的细化管理进而达到实际需求。本文基于以往的...

    南非骆驼说大数据
  • MySQL索引原理以及查询优化

    一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,...

    yaphetsfang

扫码关注云+社区

领取腾讯云代金券