MYSQL的server层和存储引擎层介绍

转自：微点阅读  https://www.weidianyuedu.com

SQL的全称是Structured Query Language，翻译成中国话就是结构化查询语言。这是一种声明式的语法，何为声明式？可以联想一下我们生活中的老板，老板在布置任务的时候会告诉你：小王啊，今天把这些砖从A地搬到B地啊，然后就没然后了。老板并不关心你是用手抬，还是用车拉，老板只关心结果：你把砖搬过去就好了。我们之于数据库而言，就是一个老板，SQL语句就是我们给数据库下达的任务，至于具体数据库怎么执行我们并不关心，我们只关心最后数据库给我们返回的结果。

对于设计数据库的人而言，语句怎么执行就得好好考虑了，老板不操心，事儿总还得干。设计MySQL的大叔人为的把MySQL分为server层和存储引擎层，但是什么操作是在server层做的，什么操作是在存储引擎层做的大家可能有些迷糊。本文将以一个实例来展示它们二者各自负责的事情。

准备工作

为了故事的顺利发展，我们先创建一个表：

CREATE TABLE hero (    id INT,    name VARCHAR(100),    country varchar(100),    PRIMARY KEY (id),    KEY idx_name (name)) Engine=InnoDB CHARSET=utf8;

我们为hero表的id列创建了聚簇索引，为name列创建了一个二级索引。这个hero表主要是为了存储三国时的一些英雄，我们向表中插入一些记录：

INSERT INTO hero VALUES    (1, "l刘备", "蜀"),    (3, "z诸葛亮", "蜀"),    (8, "c曹操", "魏"),    (15, "x荀彧", "魏"),    (20, "s孙权", "吴");

现在表中的数据就是这样的：

mysql> SELECT * FROM hero;+----+------------+---------+| id | name       | country |+----+------------+---------+|  1 | l刘备      | 蜀      ||  3 | z诸葛亮    | 蜀      ||  8 | c曹操      | 魏      || 15 | x荀彧      | 魏      || 20 | s孙权      | 吴      |+----+------------+---------+5 rows in set (0.00 sec)

准备工作就做完了。

正文

一条语句在执行之前需要生成所谓的执行计划，也就是该语句将采用什么方式来执行（使用什么索引，采用什么连接顺序等等），我们可以通过Explain语句来查看这个执行计划，比方说对于下边语句来说：

mysql> EXPLAIN SELECT * FROM hero WHERE name < "s孙权" AND country = "蜀";+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+|  1 | SIMPLE      | hero  | NULL       | range | idx_name      | idx_name | 303     | NULL |    2 |    20.00 | Using index condition; Using where |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+1 row in set, 1 warning (0.03 sec)

输出结果的key列值为idx_name，type列的值为range，表明会针对idx_name二级索引进行一个范围查询。很多同学在这里有一个疑惑：到底是一次性把所有符合条件的二级索引都取出来之后再统一进行回表操作，还是每从二级索引中取出一条符合条件的记录就进行回表一次？其实server层和存储引擎层的交互是以记录为单位的，上边这个语句的完整执行过程就是这样的：

1. server层第一次开始执行查询，把条件name < "s孙权"交给存储引擎，让存储引擎定位符合条件的第一条记录。

2. 存储引擎在二级索引idx_name中定位name < "s孙权"的第一条记录，很显然第一条符合该条件的二级索引记录的name列的值为"c曹操"。然后需要注意，我们看到EXPLAIN语句的输出结果的Extra列有一个Using index condition的提示，这表明会将有关idx_name二级索引的查询条件放在存储引擎层判断一下，这个特性就是所谓的索引条件下推（Index Condition Pushdown，简称ICP）。很显然这里的ICP条件就是name < "s孙权"。有的同学可能会问这不就是脱了裤子放屁么，name值为"c曹操"的这条记录就是通过name < "s孙权"这个条件定位的，为啥还要再判断一次？这就是设计MySQL 的大叔的粗暴设计，十分简单，没有为啥～

   小贴士： 对于使用二级索引进行等值查询的情况有些许不同，比方说上边的条件换成`name = "s孙权"`，对于等值查询的这种情况，设计MySQL的大叔在InnoDB存储引擎层有特殊的处理方案，是不作为ICP条件进行处理的。

   然后拿着该二级索引记录中的主键值去回表，把完整的用户记录都取到之后返回给server层（也就是说得到一条二级索引记录后立即去回表，而不是把所有的二级索引记录都拿到后统一去回表）。

3. 我们的执行计划输出的Extra列有一个Using Where的提示，意味着server层在接收到存储引擎层返回的记录之后，接着就要判断其余的WHERE条件是否成立（就是再判断一下country = "蜀"是否成立）。如果成立的话，就直接发送给客户端。

   小贴士： 什么？发现一条记录符合条件就发送给了客户端？那为什么我的客户端不是一条一条的显示查询结果，而是一下子全部展示呢？这是客户端软件的鬼，人家规定在接收完全部的记录之后再展示而已。

   如果不成立的话，就跳过该条记录。

4. 接着server层向存储引擎层要求继续读刚才那条记录的下一条记录。

5. 因为每条记录的头信息中都有next_record的这个属性，所以可以快速定位到下一条记录的位置，然后继续判断ICP条件，然后进行回表操作，存储引擎把下一条记录取出后就将其返回给server层。

6. 然后重复第3步的过程，直到存储引擎层遇到了不符合name < "s孙权"的记录，然后向server层返回了读取完毕的信息，这时server层将结束查询。

这个过程用语言描述还是有点儿啰嗦，我们写一个超级简化版的伪代码来瞅瞅（注意，是超级简化版）：

first_read = true;  //是否是第一次读取while (true) {    if (first_read) {        first_read = false;        err = index_read(...);  //调用存储引擎接口，定位到第一条符合条件的记录;    } else {        err = index_next(...); //调用存储引擎接口，读取下一条记录    }    if (err = 存储引擎的查询完毕信息) {        break;  //结束查询    }    if (是否符合WHERE条件) {        send_data();    //将该记录发送给客户端;    } else {        //跳过本记录    }}

上述的伪代码虽然很粗糙，但也基本表明了意思哈～ 之后有机会我们再唠叨唠叨使用临时表的情况以及使用filesort的情况是怎么执行的。