MySQL的server层和存储引擎层是如何交互的

自己的臆想：

首先，where是在server层工作的，sever层与存储引擎层是以记录为单位进行交互的，就是存储引擎层查到一条数据，就返回给server层做where条件判断，如果符合条件，就把这天记录发送到net_buffer中，等到net_buffer写满了，就发给客户端，存储引擎不断的查询结果发送到server层，以此类推。

server层做好explain执行计划，explain包含一行及以上的行记录，每次都是单表查询，一步一步知道存储引擎怎么找数据，由type列来展示：

1.system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system。

 

2.const

·等值查询之主键列

·等值查询之 unique 列

就是可以通过主键或者唯一唯一二级索引列来定位一条数据

 

3.eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref

 

4.ref

搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref

 

5.ref_or_null

查询的某一列既有等值查询，又有 NULL 值查询。

 

6.index_merge

where条件中的列都有索引

Index Merge Optimization支持三种合并算法

·intersection索引合并

对应SQL 中的 AND 场景

·union索引合并

对应SQL中的 OR 场景（where条件是等值判断）

·sort索引合并

对应SQL中的 OR 场景（where条件是范围查询)

 

7.range

查询索引列的某个范围的值。

 

8.index

select 中所有的列正好是某个二级索引使用的列。直接遍历二级索引的叶子节点得到符合条件的数据。不用回表查询聚簇索引。

 

9.all

遍历整个聚簇索引，就是全表扫描

 

举例：

测试表：
mysql> desc sbtest1;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int(11)   | NO   | PRI | NULL    | auto_increment |
| k     | int(11)   | NO   | MUL | 0       |                |
| c     | char(120) | NO   |     |         |                |
| pad   | char(60)  | NO   |     |         |                |
+-------+-----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

 

测试表索引：
mysql> show index from sbtest1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sbtest1 |          0 | PRIMARY  |            1 | id          | A         |     4377009 |     NULL | NULL   |      | BTREE      |         |               |
| sbtest1 |          1 | k_1      |            1 | k           | A         |     1046316 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 

执行计划：
mysql> explain select * from sbtest1 where k=2506783;
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sbtest1 | NULL       | ref  | k_1           | k_1  | 4       | const |   91 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+

 

说明：表sbtest1中k列有一个非唯一二级索引，查询k=2506783时，执行计划type列是ref，key是k_1，就是告诉存储引擎,去在k_1这颗索引树上查找，把where k=2506783发送给存储引擎进行查找，且k列是非唯一二级索引，可能有重复值，所以会扫描整颗索引树，每找到一条k=2506783的记录后，就根据主键值回表，找到整条记录发送给sevrer层进行判断，以此类推，server层把符合where条件的记录发送到net_buffer，最后一起发送给客户端。由于是二级索引的等值查询，所以不会使用索引下推，具体是每在二级索引树上扫描一条记录就去server层判断然后再回表，还是直接在存储引擎层判断后直接回表再返回整条记录到server层进行二次判断，我也搞不清楚！

 

以下是网上找到的，做个参考把！！

参考地址：https://juejin.cn/post/6844903856682303495

多谢这位老哥！！

测试准备：

1.创建测试表

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列创建了一个二级索引。

2.插入数据

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

 

一条语句在执行之前需要生成所谓的执行计划，也就是该语句将采用什么方式来执行（使用什么索引，采用什么连接顺序等等），我们可以通过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 的大叔的粗暴设计，十分简单，没有为啥。然后拿着该二级索引记录中的主键值去回表，把完整的用户记录都取到之后返回给server层（也就是说得到一条二级索引记录后立即去回表，而不是把所有的二级索引记录都拿到后统一去回表）。


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


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 {
        //跳过本记录
    }
}