文档章节

05.MySQL实战45讲学习笔记---深入浅出索引(下)

scgaopan
 scgaopan
发布于 2019/12/07 23:47
字数 1623
阅读 15
收藏 0

     在下面这个表T中,如果我们执行select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?

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

下图为InnoDB的索引组织结构

现在,我们一起来看看这条SQL查询语句的执行流程:

  • 在k索引树上找到k=3的索引记录,取得ID=300;
  • 再到主键索引树查到ID=300对应的记录R3;
  • 在k索引树下到下一个值k=5,取得ID=500;
  • 再回到主键索引树查到ID=500对应的R4;
  • 在k索引树取下一个值k=6,不满足条件,循环结束。

在这个过程中,回到主键索引树搜索的过程,我们称之为回表。可以看到,这个查询过程读了k索引树的3条记录,回表2次,在这个例子中,由于查询结果所需的数据只在主键索引上有,所以不得不回表。那么,有没有可能通过索引优化,避免回表过程呢?

覆盖索引

  如果执行语句是select ID from T where k between 3 and 5,这时只需要ID的值,而ID的值已经在k索引树上了,因此可直接提供查询结果,不需要回表。也就是说,这个查询里,索引k已经覆盖了我们的查询需求,我们称为覆盖索引。

  由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段需要注意的是,在引擎内部使用覆盖索引在索引k上实际读了3个记录,但对于MySQL的server层来说,它就找引擎拿了两条记录,因此MySQL认为扫描行数是2。

下面讨论一个问题:在一个市民信息表上,是否有必要将身份证号和名字建立联合索引?

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+树这种索引结构,可以利用索引的“最左前缀”,来定位记录。为了直观地说明这个概念,我们用(name,age)这个联合索引来分析,下图为(name,age)的索引示意图:

可以看到,索引项是按照索引定义里面出现的字段顺序排序的。当你的逻辑需求是查到所有名字是“张三”的人时,可以快速定位到ID=4,然后向后遍历得到所有需要的结果。如果你要查的是所有名字第一个字是“张”的人,你的SQL语句的条件是“where name like '张%'”,这时你也能够用上这个索引,查找到第一个符合条件的记录是ID3,然后向后遍历,直到不满足条件为止。

可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引最左的N个字段,也可以是字符串索引的最左M个字符。

在建立联合索引时,如何安排索引字段的顺序

这里我们评估标准是:索引的复用能力。因为可以支持最左前缀,所以当已经有了(a,b)这个联合索引后,一般就不需要单独在a上建立索引了。因些,第一原则是,如果通过调整顺序可以少维护一个索引,那么这个顺序往往是需要优先考虑采用的。

索引下推

我们还是以市名表的联合索引(name,age)为例。如果现丰有一个需求:检萦出表中名字每个字是张,而且年龄是10的所有人,那么SQL应该这第写:

select * from tuser where name like '张%' and age=10

你知道前缀索引的规则,所以这个语句在搜索树的时候,只能用"张"找到第一个满足条件的记录ID3。当然还是不错的,总比全表扫描好。然后就是判断其它条件是否满足。在MySQL5.6之前,只能从ID3开始一个个回表。到主键索引上找出数据行,再对字段值。而MySQL5.6引入的索引下推优化,可以在索引遍历过程中。对索引中包含的字段先做判断,直接过虑掉不满足条件的记录,减少回表次数。以下是这两个过程的执行流程图:

图3.无索引下推执行流程

图4.索引下推执行流程

其中每一个虚线箭头表示回表一次。

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

图4跟图3的区别是,InnoDB在(name,age)索引内部判断了age是否等于10,对不等于10的记录,直接判断跳过,所以这里只需要回表2次。

 

© 著作权归作者所有

scgaopan
粉丝 4
博文 64
码字总数 42784
作品 0
成都
私信 提问
1月中旬值得一读的10本技术新书(机器学习、Java、大数据等)!文末有福利!

1月中旬,阿里云云栖社区 联合 博文视点 为大家带来十本技术书籍(机器学习、Java、大数据等)。以下为书籍详情,文末还有福利哦! 书籍名称:Oracle数据库问题解决方案和故障排除手册 内容简...

阿里云云栖社区
2018/01/12
0
0
计算机书籍目录

计算机系统与网络 《图灵的秘密》 《计算机系统概论》 《深入理解Linux内核》 《深入Linux内核架构》 《TCP/IP详解 卷1:协议》 《Linux系统编程(第2版)》 《Linux内核设计与实现(第3版)...

Reborn-D
2016/11/01
98
0
MySQL“必知必会”的36个知识点

毫无疑问,MySQL 是当下最流行的开源数据库。凭借强大的性能和易于使用性,它已被 Google、Facebook、YouTube、百度、网易和新浪等大型互联网公司所应用。更有统计,世界上一流的互联网公司中...

架构师技术联盟
2018/11/13
0
0
[干货] 一份架构师的技术课程表

年关将至,知识年货准备的怎么样了?如果因工作忙碌无暇备年货也无妨,备好这份课表让你来年满血再战。这份课表由极客时间倾力打造,内容覆盖产品和服务快速上线,规模扩展、并发处理和架构演...

架构师技术联盟
2019/01/29
0
0
经典编程书籍大全

经典编程书籍大全 100+ 经典技术书籍,涵盖:计算机系统与网络、系统架构、算法与数据结构、前端开发、后端开发、移动开发、数据库、测试、项目与团队、程序员职业修炼、求职面试 和 编程相关...

Oscarfff
2016/10/30
97
0

没有更多内容

加载失败,请刷新页面

加载更多

每天AC系列(六):有效的括号

1 题目 LeetCode第20题,这题比较简单,匹配括号. 2 栈 这是栈的典型应用,括号匹配,当然不需要直接使用栈,使用一个StringBuilder即可: if(s.isEmpty()) return true;char a = s.charAt(0);...

Blueeeeeee
今天
27
0
Spring AOP-06-切入点类型

切入点是匹配连接点的拦截规则。之前使用的是注解@Pointcut,该注解是AspectJ中的。除了这个注解之外,Spring也提供了其他一些切入点类型: • 静态方法切入点StaticMethodMatcherPointcut •...

moon888
昨天
90
0
Class Loaders in Java

1. Introduction to Class Loaders Class loaders are responsible for loading Java classes during runtime dynamically to the JVM (Java Virtual Machine). Also, they are part of the ......

Ciet
昨天
96
0
以Lazada为例,看电商系统架构演进

什么是Lazada? Lazada 2012年成立于新加坡,是东南亚第一电商,2016年阿里投资10亿美金,2017年完成对lazada的收购。 业务模式上Lazada更偏重自营,类似于亚马逊,自建仓储和为商家提供服务...

春哥大魔王的博客
昨天
62
0
【自用】 Flutter Timer 简单用法

dart: void _startTime() async { _timer = Timer(Duration(seconds: sec), () { fun(xxx,yyy,zzz); }); } @override void dispose() { _timer.cancel()......

Tensor丨思悟
昨天
65
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部