当我们执行一条select语句后,究竟Mysql做了些什么?整个过程其实可以涉及到Mysql的组成结构,Innodb的页结构以及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中记录的用户权限:
- root用户登陆
mysql -uroot -p
- 进入系统默认的名字为“mysql”的数据库
use mysql;
- 在“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表中一行行的数据呢?
不完全是!User Records当中数据的确是以行为单位进行存储,但存储格式如下所示:
其中变长字段长度列表指的是:类似于varchar这样的变长字段,在该字段保存着实际的长度值;
NULL标志位:对于值为null的字段,不会记录到后面的列数据当中,而是放在null标志位;
记录头信息后面遇到了再进行介绍,行结构的详细理解可以参见
我们在创建数据库的时候默认的行格式为Dynamic,我们可以通过执行
show table status where name = 'table name' \G
看到对应的Row_Format:那么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;
我们看到主键这一列,虽然我输入的时候顺序是3-2-8-6(对照insert语句),但是查询的时候,发现Mysql,其实是InnoDB已经帮我排好了序,这样做当然有很大的好处,比如在做范围查询的时候,就可以很快速的进行定位!
现在问题来了,为什么InnoDB会在我们插入后,将主键排序?它是怎么做到的?首先我们注意一点,自定义主键是默认具备索引功能的,如果要实现这样的功能,我想应该有一个链表一样的,对于链表,我们要输入3-2-8-6过程如下:
我们知道对于链表来说上面所提到的插入不是难事,但是如果需要对这种存储方式进行搜索,就麻烦了,它没有数组连续存储的特点,无法快速定位。这时候我们就联系到实际的数据页来进行优化。
-
引入页内目录
如果我们想书的目录一样,在我们页内也设置一个目录,这个目录可以有效的划分我们存储的索引id,那么我们在查询的时候可以先根据页内目录确定范围,然后进行查询。下图所示,我们将页内数据两两分组,将每组最小索引值拿出来作为我们目录的key,value自然就是存放这个最小节点的内存引用了。可以看到,如果我们要访问8,就可以先通过目录6节点,找到链表的下半部分,然后再开始找next节点。
-
引入页目录
另一个问题随之而来,如果数据越来越多,数据需要分页存放了,自然,页面之间的关联需要有地方存储,数据页需要知道上一页和下一页在哪里。另外既然页内目录可以方便定位,现在页间岂不是页应该有个目录,不然当我拿到15这个索引值的时候,我并不知道去哪一个页面找数据呀,所以在页外面还有一层目录,当我们要查找13的时候,首先通过页目录找到了地址为300的页,然后进去找页内目录10,进而找到链表上半部分,找到13。
大家看这是个啥!不就是B+树的数据结构吗!所以为什么说Mysql索引的底层是B+树实现,包括B+树实现有什么好处,一目了然。
这里补充两点,对于主键索引,它在B+树上的叶子节点上存的key是索引值(也就是主键值),那么value是什么呢?value就是整行的数据。另外对于上面这个B+树,叶子节点当然还有点不完整的地方,那就是,叶子节点实际上是双向链表,而不是单向的,这也是方便我们进行范围查询,当我们需要找小于28的内容时,Mysql会先定位到28,然后根据prev指针,往前找。
这里我们要注意一点并不是有索引就一定会通过索引查询,优化器会判断索引查找和全表查找的效率。本文就不再赘述。总之以上就是一个查询语句在Mysql的Server端和InnoDB的整个过程。