一条select语句,Mysql的执行过程

当我们执行一条select语句后,究竟Mysql做了些什么?整个过程其实可以涉及到Mysql的组成结构,Innodb的页结构以及Mysql的索引原理。本文中我们会一一讲到:
首先这个问题会涉及到Mysql的组成结构:


Mysql组成结构

很清晰可以看见Mysql的组成结构包括3个主要部分:客户端(Client)、服务端(Server)、执行引擎(Engine)。用户就是通过客户端输入sql指令,其实另一种说法Client不算是Mysql的组成部分,比如我们知道的Mysql Workbench,命令行工具,它们不算Mysql的一部分,我这里把它放进来,是希望读者可以整体的理解这个结构,客户端算与不算不影响讨论实际问题。服务端会接受客户端来的数据,然后对其进行缓存查询,词义语义的分析,sql语句优化等等,最终通过执行器找存储引擎对磁盘上的表进行操作。这就是Mysql整体的观感,之后我们会对各个部分进行详细的解释:

连接器:

顾名思义是用于Mysql和客户端进行连接,当我们在terminal中输入:mysql -u username -p后,Mysql客户端就会跟你服务器上的Mysql进行友好的TCP三次握手,双方状态都变为established之后,连接器开始验证你输入的用户名和密码;

连接器处理连接请求

连接器在处理连接请求的时候,除了会验证用户名密码以外,还会去检验用户权限,读者可以通过以下方式查看Mysql中记录的用户权限:

  1. root用户登陆
mysql -uroot -p
  1. 进入系统默认的名字为“mysql”的数据库
use mysql;
  1. 在“mysql”数据库中存在user表(show tables)
select * from user \G
用户权限

用户权限如上图所示,对于peita1这个user,在数据库subscribe_message_microservice中select权限、insert权限……都可以看到了。
这个权限被连接器拿到后会记录下来,这意味着在这一次数据库连接的过程中,peita1用户的权限就是如此了,如果此时root用户取消了peita1用户对某数据库的insert权限,在此次数据库连接没有断开的情况下,peita1的insert权限不会受到影响。

查询缓存:

首先声明Mysql8.0之后查询缓存模块被拿掉了,查询缓存仅对于select语句,当查询缓存开启的时候,比如对表A的select * from A;该语句和查询结果会以HashMap的形式,将查询语句(select * from A)作为key,结果作为value存储起来,当你下一次进行查询的select查询的时候,如果是一模一样的查询语句,则会命中,且Mysql不往下执行,直接返回结果。当然即便A表的查询经过缓存,但是任意时候对A表进行了增删改操作,这条与A表相关的缓存也会被清空。这里就有一个问题!在查询缓存打开的清空下,每一张表进行了增删改之后都要检查缓存,看看是否需要删除记录,这对相应的操作性能有影响,所以看起来这是一个不错的功能,但并不推荐使用,更绝的是8.0之后就直接拿掉了。
我们就以查询语句:select * from food where id = 3;为例,来看一看这样的SQL语句是如何执行的。

分析器

如果查询缓存关闭或是没有命中缓存的情况下,SQL语句会进入分析器,分析器做两个事:一是词义分析,也就是说根据你这个SQL字符和空格组成的字符串,对关键字进行分析,比如发现有“select”,Mysql就知道这是一个查询语句,发现from后面的“food”,就知道food是表名;在确定了这个SQL字符串是来干嘛的之后,就开始第二件事,语义分析,这就是我们熟悉的语法检查,一旦发现错误就报出:You have an error in your sql syntax。


分析器的两个步骤

优化器:

优化器会在连表查询的时候确定怎样的查询顺序比较好,或是有多个索引的时候决定用哪一个索引,不用哪一个索引等等,它会根据执行的效率进行判断。当优化器决定了最终的执行方案后,就会交由执行器进行执行。

执行器:

在执行select * from food where id = 3之前,执行器会先判断当前的登陆用户是否有权限访问user这个表。根据优化器的分析如果是全表扫描的话就会调用InnoDB执行引擎调取第一行,记录之后调取“下一行”接口……直至找到;如果是通过索引查找,下文会详细介绍查找过程,最后执行器将所有结果的集合返回给用户。


数据页结构

当目前为止这个SQL语句算是走完了Mysql的Server端,进而来到了存储引擎,从版本5.5.5之后,Mysql默认的存储引擎就是InnoDB,这里我们就以它为例,进行介绍。上面我们说了,执行器在查询SQL语句的时候,会调InnoDB的接口一行一行查询,这里有一个问题,InnoDB当中是如何执行的呢?如果查询条件中有索引呢?为了更准确的描述这条语句的执行,我们先了解一下,InnoDB中数据的存储结构和索引的实现原理。
InnoDB当中数据是按照页的方式进行存储,我们看到下图,就是InnoDB将数据按照页的结构进行存储。暂时分不清没关系,我们看到User Records就是用户的数据,在它下面Free Space字面意思是空闲空间,其实可以理解成User Records的备胎空间,新一条数据存储进来就从Free Space当中拿一点空间来给到User Records。

数据页结构

好!既然User Records的存放用户数据知道了,那么在这里面存放的用户数据是不是我们平时看到的Mysql表中一行行的数据呢?
Mysql当中的表数据

不完全是!User Records当中数据的确是以行为单位进行存储,但存储格式如下所示:
Compact行格式

其中变长字段长度列表指的是:类似于varchar这样的变长字段,在该字段保存着实际的长度值;
NULL标志位:对于值为null的字段,不会记录到后面的列数据当中,而是放在null标志位;
记录头信息后面遇到了再进行介绍,行结构的详细理解可以参见
我们在创建数据库的时候默认的行格式为Dynamic,我们可以通过执行show table status where name = 'table name' \G看到对应的Row_Format:
查看table信息

那么Dynamic和Compact格式的区别这里解释一下:
数据页中,一行数据可以存放的大小是65535个字节,那么一个数据页可以存放多少字节呢?执行:show global status like 'Innodb_page_size';可见是16384个字节大小。
数据页默认大小

因此会出现行大小大于页容量的行溢出情况,Compact行格式和Dynamic行格式对于这个地方的处理不一样:
![Compact和Dynamic对行溢出的处理]!(https://upload-images.jianshu.io/upload_images/4995250-74d7787b41fc5510.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

可以看到Compact和Dynamic对于行溢出的处理的不同,在第一页Compact存放的是行数据的第一部分和下一部分的地址;而在Dynamic行格式的第一页存放的仅仅是第一部分的地址,而不会存放数据,这样做有一个好处,因为第一页只存放地址,需要的行空间很小,这就意味着第一页可以存放更多的行,而这些行其实后面就是要讲的索引的目录!


索引原理

数据页结构还有一些没有解释的字段,我会放在后面,用到的时候进行描述,这样理解起来更生动。
我们讲到查询,在Mysql中避不开索引,在印象当中有索引的情况下,查询效率会变高,如果问起原因,可能有人会说B+树查找,本文准备用倒推的方式,从现象倒推出B+的形成过程,我想这样一来,你对于为什么是B+树有更好的理解!我们先看这样一个现象:执行下面的SQL语句,我们发现数据展示和数据导入的顺序不一样。

create table tab_1 (id integer primary key, name varchar(20));
insert into tab_1 (id, name) values (3,'tony'), (2, 'wang'), (8, 'zhang'), (6, 'lily');
select * from tab_1;

tab_1

我们看到主键这一列,虽然我输入的时候顺序是3-2-8-6(对照insert语句),但是查询的时候,发现Mysql,其实是InnoDB已经帮我排好了序,这样做当然有很大的好处,比如在做范围查询的时候,就可以很快速的进行定位!
现在问题来了,为什么InnoDB会在我们插入后,将主键排序?它是怎么做到的?首先我们注意一点,自定义主键是默认具备索引功能的,如果要实现这样的功能,我想应该有一个链表一样的,对于链表,我们要输入3-2-8-6过程如下:
链表输入数据

我们知道对于链表来说上面所提到的插入不是难事,但是如果需要对这种存储方式进行搜索,就麻烦了,它没有数组连续存储的特点,无法快速定位。这时候我们就联系到实际的数据页来进行优化。

  1. 引入页内目录
    如果我们想书的目录一样,在我们页内也设置一个目录,这个目录可以有效的划分我们存储的索引id,那么我们在查询的时候可以先根据页内目录确定范围,然后进行查询。下图所示,我们将页内数据两两分组,将每组最小索引值拿出来作为我们目录的key,value自然就是存放这个最小节点的内存引用了。可以看到,如果我们要访问8,就可以先通过目录6节点,找到链表的下半部分,然后再开始找next节点。


    页内目录
  2. 引入页目录
    另一个问题随之而来,如果数据越来越多,数据需要分页存放了,自然,页面之间的关联需要有地方存储,数据页需要知道上一页和下一页在哪里。另外既然页内目录可以方便定位,现在页间岂不是页应该有个目录,不然当我拿到15这个索引值的时候,我并不知道去哪一个页面找数据呀,所以在页外面还有一层目录,当我们要查找13的时候,首先通过页目录找到了地址为300的页,然后进去找页内目录10,进而找到链表上半部分,找到13。


    页目录

    大家看这是个啥!不就是B+树的数据结构吗!所以为什么说Mysql索引的底层是B+树实现,包括B+树实现有什么好处,一目了然。
    这里补充两点,对于主键索引,它在B+树上的叶子节点上存的key是索引值(也就是主键值),那么value是什么呢?value就是整行的数据。另外对于上面这个B+树,叶子节点当然还有点不完整的地方,那就是,叶子节点实际上是双向链表,而不是单向的,这也是方便我们进行范围查询,当我们需要找小于28的内容时,Mysql会先定位到28,然后根据prev指针,往前找。


    B+树构建索引底层

这里我们要注意一点并不是有索引就一定会通过索引查询,优化器会判断索引查找和全表查找的效率。本文就不再赘述。总之以上就是一个查询语句在Mysql的Server端和InnoDB的整个过程。

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 203,456评论 5 477
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,370评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 150,337评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,583评论 1 273
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,596评论 5 365
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,572评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,936评论 3 395
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,595评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,850评论 1 297
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,601评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,685评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,371评论 4 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,951评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,934评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,167评论 1 259
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 43,636评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,411评论 2 342

推荐阅读更多精彩内容