专栏首页洁癖是一只狗Mysql索引解密(下)

Mysql索引解密(下)

今天继续学习索引相关知识,如下面查询语句需要搜索几次树和扫描几行

select  *  from  T where between 3 and 5

创建表T,并初始化

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');

上面查询语句执行的顺序如下

  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的记录,循环结束

上面ID索引树进行查找记录的过程叫回表,可以看出k树索引树进行了三次查询,Id索引树进行了两次查询。查询数据过程中是否可以避免回表查询呢,

覆盖索引

当我们使用下面语句

select  id from  T where between 3 and 5

由于上面语句在k索引树中查询直接可以获取结果id,覆盖了我们要查询的结果,不需要再回表查询,因此这种就叫做覆盖索引,覆盖索引是我们经常使用的一种优化手段。

基于上面覆盖索引,我们看看一张城市居民信息表

CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

我们是否有必要建立身份证和名称的联合索引,比如当我们要根据身份证查询居民信息时候,建立一个身份证索引就应该可以满足,但是如果有一种场景要根据身份中查询居民的名称的时候,这个时候建立联合索引就比较合理了,这就要用到了上面谈到的覆盖索引,不用回表就可以把需要的信息查询出来。

但是建立索引也是需要维护成本的,到底要不要添加一些冗余的索引,这就要根据业务场景来判断了。

最左前缀索引

如果给每一种查询都建立一个索引,是很浪费空间的,比如当我们根据身份证查询地址的需求是,虽然这种查询比较少,但是我们总不能让他全表扫描,因此我们必须采用一种手段解决这种问题,这里就要说到B+树的结构使用最左前缀索引定位记录,看下图

我要要查询张三的记录时候,可以快速定位到id2。我们也可以使用下面语句

select  *  from T where name like '张%'

使用最左前缀匹配数据,找到张三记录,循环遍历后面的语句直到不满足条件结束,我们看到索引的最左前缀可以理解为,索引的最左结果字段,也可以是字段的最左的字符.

那么我们如果定义联合索引的字段顺序呢,我们需要评判的标准是索引的复用能力,我们的原则就是,如果联合索引调整顺序,可以减少一个索引的建立,我们就会按照这个字段顺序建立联合索引。

当然这也不是必须,当我们有联合索引(a,b)和a,b单独的业务查询,我不得不建立联合索引(a,b)和b索引,但是这里你也要考虑空间的问题,如name的长度比age的长度大,因此你应该建立联合索引(name,age)和age索引。

索引下推

当我们使用联合索引(name,age),使用下面语句

select  *  from  T where name like '张%' and  age > 10

当然上面语句的name查询符合最左前缀查询,总比使用全表查询强吧。然后肯定就是比较age是否满足条件。

在mysql5.6之前,我们会根据最左前缀获取的id,回表查询记录数

在mysql5.6之后,我们使用索引下推,直接使用在联合索引中过滤调不符合条件的age,减少回表的性能消耗,

图1

图2

上面两种图说明的不同的策略,图1是特地的去掉age的值,他会直接到主表查询需要的数据,因此需要回表4次查询,而图2,直接在(name,age)进行过滤age的值,仅仅需要回表两次。

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

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

原始发表时间:2020-07-27

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql如何给字符串添加索引(前缀索引)

    在日常开发中,我们经常给字符串添加索引,那么给字段添加索引有什么技巧吗,我们看看下面的例子,我们给一个邮箱添加索引,应该如何添加呢

    小土豆Yuki
  • Mysql索引解密(上)

    索引是数据库概念最重要的概念之一,也是我们经常要使用的优化手段,索引的出现其实就是为了提高数据查询的效率,就像书的目录一样

    小土豆Yuki
  • SpringAOP---ProxyFactoryBean

    AOP为Aspect Oriented Programming的缩写,意为:面向切面编程,通过预编译方式和运行期动态代理实现程序功能的统一维护的一种技术。AOP...

    小土豆Yuki
  • 存储优化(3)-mongo大表加索引

    在存储优化(2)-排序引起的慢查询优化中我们提到过排序对查询选择索引的影响。但是的解决办法就是增加一个索引。在线上给mongo的大表增加一个索引要慎重。在增加索...

    方丈的寺院
  • mysql索引提高查询速度

      在web开发中,业务模版,业务逻辑(包括缓存、连接池)和数据库这三个部分,数据库在其中负责执行SQL查询并返回查询结果,是影响网站速度最重要的性能瓶颈。本文...

    超蛋lhy
  • 玩转Mysql系列 - 第24篇:如何正确的使用索引?

    学习索引,主要是写出更快的sql,当我们写sql的时候,需要明确的知道sql为什么会走索引?为什么有些sql不走索引?sql会走那些索引,为什么会这么走?我们需...

    路人甲Java
  • 重新学习Mysql数据库5:根据MySQL索引原理进行分析与优化

    本系列文章将整理到我在GitHub上的《Java面试指南》仓库,更多精彩内容请到我的仓库里查看

    Java技术江湖
  • MySQL索引原理以及查询优化

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

    yaphetsfang
  • MySQL索引设计概要

    在关系型数据库中设计索引其实并不是复杂的事情,很多开发者都觉得设计索引能够提升数据库的性能,相关的知识一定非常复杂。 ? 然而这种想法是不正确的,索引其实并不是...

    用户1263954
  • 数据库之索引总结

    索引在数据库中可以说是相当重要的一块知识点了,也是面试经常被问的,这篇文章就总结一下索引相关的知识点,包括索引的底层实现原理,索引的分类,最左匹配原则等。

    beifengtz

扫码关注云+社区

领取腾讯云代金券