文档章节

MySql索引那些事

木木匠
 木木匠
发布于 04/01 09:22
字数 2492
阅读 5180
收藏 167

概述

上一篇文章《一条sql语句在mysql中是如何执行的》我们聊到了sql语句内部的执行,包括InnoDB引擎是如何支持事务的,如何做到可以备份恢复的,那么今天我们来聊一聊MySql索引的那些事,在这篇文章中,我会主要聊聊InnoDB下索引的数据结构,索引如何起作用的,如何更好的利用索引提高效率。

一、什么是索引

数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。就像我们以前用的新华字典的目录一样,能帮助我们快速查询到某一个字。

二、索引的分类

分类角度索引名称
数据结构B+树,Hash索引,R-Tree等
存储层面聚簇索引,非聚簇索引
逻辑层面主键索引,普通索引,复合索引,唯一索引,空间索引等

三、索引实例分析(以InnoDB为例)

3.1 InnoDB下索引的结构

InnoDB下,表都是根据主键顺序以索引的形式存放的,这种数据存储方式也被称为聚簇索引,“聚簇”就是表示数据行和相邻的键值紧凑的存储在一起,也就是数据行实际上是存储在索引的叶子页中。我们创建一张表来实际说明下InnoDB下的索引结构,建表语句如下:

create table person(
id int primary key, 
age int not null
index (age)engine=InnoDB;

然后我们插入五条数据分别为(1,15),(2,17),(6,20),(10,18),(19,21),索引的树结构如下:

上图中展示了两部分内容,第一个图为聚簇索引(主键索引)的内容,可以看到,数据按照Id的大小排序,对应的索引会包含该索引的整行数据。

第二个图展示了用age做索引的索引结构图,也就是非聚簇索引(非主键索引),可以看到索引以年龄排序,但是和主键索引不同的是,年龄索引对应的却是Id,所以我们可以知道非主键索引记录的内容就是主键索引的值。

这里可能有同学会有疑问,如果我建表的时候没有指定主键的话,索引结构又是如何的呢?其实在InnoDB中,如果没有定义主键,那么他会选择一个唯一的非空索引代替。如果没有这样的索引,那么他会隐式的定义一个主键来作为聚簇索引。所以无论你是否设置主键,InnoDB还是会帮你满足以上图的形式来索引数据。接下来我们分析下索引查询的流程。

3.2 索引查询分析

假设我们执行一条查询语句 select * from person where ID = 6,因为直接使用的是主键ID查询,所以就会用主键索引,由于主键索引直接关联了整行所有数据,所以,引擎只要执行一次就能查询出结果。

如果执行的sql语句是非主键索引

select * from person where age = 18

上述语句会走age的普通索引,索引先根据age搜索等于18的索引记录,找到ID=10的记录,然后再到主键索引搜索一次,然后拿出需要查询的数据。

从普通索引查出主键索引,然后查询出数据的过程叫做回表。由于回表需要多执行一次查询,这也是为什么主键索引要比普通索引要快的原因,所以,我们要尽量使用主键查询。

3.3 覆盖索引

我们通常创建索引的依据都是根据查询的where条件,但是这只是我们通常的做法,我们根据上面的分析可以知道,如果要想查询效率高,第一,使用主键索引,第二,避免回表,也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段,那么我们就叫做“覆盖索引”。

那么如何建立一个覆盖索引呢?答案是通过联合索引来实现,通过联合索引的字段来覆盖要查询的字段,从而达到索引覆盖的效果。

我们把上面的建表语句改造下,来分析下如何实现覆盖索引。

 CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

上面我创建了一个name和age的联合索引,索引结构图表示如下:

我们根据图可以知道,联合索引是和创建索引字段顺序有关的,上面这个例子就是先以name排序,然后name相同再以age为标准排序。那么我们建表后该如何达到覆盖索引的效果呢?相信有些同学已经知道了怎么写sql可以达到覆盖索引效果,sql如下:

select name,age from person where name = "Barry"

因为我们需要查询的字段name和age,都在索引中可以直接查询到了,所以不需要查找到主键ID,然后再回表了。

看到这里,肯定有同学会说,既然这样的话,我把所有需要查询的字段组合都建上联合索引不就行了吗?答案是:不行。因为索引也是需要消耗空间的,而且维护索引也是需要成本的,这一点我会在后面的优缺点中提到。那么有没有别的方式可以尽可能的实现不回表的效果呢?这里我们就要引入MySql的最左前缀原则了。

什么叫最左前缀原则呢?就是在索引的匹配中,可以以索引的最左N个字段,也可以是字符串索引的最左N个字符。比如在上图中,要查询以A开头的名字,查询语句就是

select name from person where name like 'A%'

这个时候就可以满足最左前缀规则来使用索引查询了,这里就会依赖索引查询到第一个首字母是A的名字,然后向后遍历,直到不满足条件为止。

那么最左N个字段是什么意思呢?意思就是索引(name,age),可以直接利用 name来当做单独索引使用,可以只使用联合索引的部分字段,但是必须是顺序一致,比如索引(a,b,c),如果要想使用最左前缀规则,可以使用索引a,ab。

我们也可以利用该规则来少维护一个或多个索引,比如我们需要 a,ab,abc的查询,那就只需要(a,b,c)联合索引就满足要求了。

3.4 索引下推

在MySql 5.6版本中引入了一个新特性,叫做“索引条件推送(index condition pushdown)”,这也称为索引下推。那么索引下推是这个什么东东呢?其实从“索引条件推送”这个名字就可以表明,这个特性是可以在索引中的字段进行条件判断,然后过滤不满足条件的记录,减少回表的次数。

比如以上图中的数据为准,sql如下:

select * from person where name like 'A%' and age =19;

那么如果没有索引下推的情况下,首先会根据索引查询出名字以A开头的所有记录,然后查询出ID,然后回表去查询对应的ID记录,最后再判断age=19,返回满足条件的语句。因为满足A开头的记录有2条,所以这种情况下,会回表2次。

在索引下推情况下,InnoDB会在索引内部直接判断age=19是否满足条件,过滤掉不满足条件的记录,所以只返回了一条,也就是只需要回表一次。从而提高了性能。

3.5 索引的优点与缺点

说了这么多关于索引的内容,我们来谈谈索引的优缺点。

优点:

  • 减少服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机IO变为顺序IO

缺点

  • 索引会占用额外的存储空间
  • 索引的维护需要一定的成本,插入数据后需要保证原来的索引有序,所以也会影响一定的数据库性能。

五、总结

这篇博文我主要说了,索引的定义,索引的分类,索引按照不同的角度可以分为常见的哪几种。然后我重点说了在InnoDB下索引的索引的数据结构。 主键索引和非主键索引的区别就是查询主键索引可以直接返回数据,非主键索引需要先查询出主键ID,然后再查询出数据,这个过程就叫做回表。我们可以通过覆盖索引减少回表的次数,从而达到提高性能的效果。在mysql5.6以后,InnoDB可以支持索引下推,在使用联合索引的时候,如果可以在索引判断条件,那么就在索引中过滤不满足条件的行,从而减少回表次数。

六、参考

  • 《高性能MySql》第3版
  • 《MySql45讲》专栏

© 著作权归作者所有

木木匠
粉丝 105
博文 30
码字总数 65486
作品 0
广州
高级程序员
私信 提问
加载中

评论(9)

qnloft
qnloft
图片挂了
木木匠
木木匠 博主
应该是缓存吧,我昨天更新过地址,我这边访问是ok的,您再刷新试试?
三亚
三亚
谢谢你的贡献
doublen
doublen
写的挺好的,通俗易懂!
_似水流年
_似水流年
卧槽!卧槽!牛逼!牛逼!
引鸩怼孑
引鸩怼孑
牛逼!
时尔夕
时尔夕
很好。期待b+树节点剖析。
木木匠
木木匠 博主

引用来自“whaon”的评论

select name,age from person where age = 'Barry'
这行笔误
谢谢指出,已经修正。👍
whaon
whaon
select name,age from person where age = 'Barry'
这行笔误
MySQL的前缀索引及Oracle的类似实现

MySQL的前缀索引及Oracle的类似实现 运维派2018-01-102 阅读 oracle索引mysql MySQL有一个很有意思的索引类型,叫做前缀索引,它可以给某个文本字段的前面部分单独做索引,从而降低索引的大小...

运维派
2018/01/10
0
0
mysql-innodb笔记和Spring的那些事

一直觉得mysql怎么用用就行,深得公司DBA大神的影响,当各种细节需要处理的时候就会知道深入的太少,各种拙荆见肘,各种靠猜的武断决策,决心系统化通读下innodb的知识。 Mysql从使用到现在差...

Hi徐敏
2016/11/09
841
3
MySQL——通过EXPLAIN分析SQL的执行计划

在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。 下面分别对EXPLAIN命令结果的每一列进行说明: select_type:...

撸码那些事
2018/08/03
0
0
MySQL使用单列索引和多列索引

只有唯一索引才真正提高速度,一般的索引只能提高30%左右 讨论MySQL选择索引时单列单列索引和多列索引使用,以及多列索引的最左前缀原则。 1. 单列索引 在性能优化过程中,选择在哪些列上创建...

vshcxl
2016/04/25
74
0
mysql联合索引 sql索引使用

注意:Index(Name,Age)表示在Name,Age两列上建立联合索引 由于索引对数据库的查询性能有着至关重要的影响,下面是我的一些总结和体会: 一个查询一次只能使用一个索引:select name fro...

五大三粗
2015/04/30
376
0

没有更多内容

加载失败,请刷新页面

加载更多

计算机实现原理专题--二进制减法器(二)

在计算机实现原理专题--二进制减法器(一)中说明了基本原理,现准备说明如何来实现。 首先第一步255-b运算相当于对b进行按位取反,因此可将8个非门组成如下图的形式: 由于每次做减法时,我...

FAT_mt
昨天
6
0
好程序员大数据学习路线分享函数+map映射+元祖

好程序员大数据学习路线分享函数+map映射+元祖,大数据各个平台上的语言实现 hadoop 由java实现,2003年至今,三大块:数据处理,数据存储,数据计算 存储: hbase --> 数据成表 处理: hive --> 数...

好程序员官方
昨天
7
0
tabel 中含有复选框的列 数据理解

1、el-ui中实现某一列为复选框 实现多选非常简单: 手动添加一个el-table-column,设type属性为selction即可; 2、@selection-change事件:选项发生勾选状态变化时触发该事件 <el-table @sel...

everthing
昨天
6
0
【技术分享】TestFlight测试的流程文档

上架基本需求资料 1、苹果开发者账号(如还没账号先申请-苹果开发者账号申请教程) 2、开发好的APP 通过本篇教程,可以学习到ios证书申请和打包ipa上传到appstoreconnect.apple.com进行TestF...

qtb999
昨天
10
0
再见 Spring Boot 1.X,Spring Boot 2.X 走向舞台中心

2019年8月6日,Spring 官方在其博客宣布,Spring Boot 1.x 停止维护,Spring Boot 1.x 生命周期正式结束。 其实早在2018年7月30号,Spring 官方就已经在博客进行过预告,Spring Boot 1.X 将维...

Java技术剑
昨天
18
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部