MySQL的Server層和存儲引擎層是如何交互的

作者：小孩子4919 2019-08-09 16:14:33

設(shè)計(jì)MySQL的大叔人為的把MySQL分為server層和存儲引擎層，但是什么操作是在server層做的，什么操作是在存儲引擎層做的大家可能有些迷糊。本文將以一個實(shí)例來展示它們二者各自負(fù)責(zé)的事情。

SQL的全稱是Structured Query Language，翻譯成中國話就是結(jié)構(gòu)化查詢語言。這是一種聲明式的語法，何為聲明式?可以聯(lián)想一下我們生活中的老板，老板在布置任務(wù)的時(shí)候會告訴你：小王啊，今天把這些磚從A地搬到B地啊，然后就沒然后了。老板并不關(guān)心你是用手抬，還是用車?yán)习逯魂P(guān)心結(jié)果：你把磚搬過去就好了。我們之于數(shù)據(jù)庫而言，就是一個老板，SQL語句就是我們給數(shù)據(jù)庫下達(dá)的任務(wù)，至于具體數(shù)據(jù)庫怎么執(zhí)行我們并不關(guān)心，我們只關(guān)心最后數(shù)據(jù)庫給我們返回的結(jié)果。

[[273260]]

對于設(shè)計(jì)數(shù)據(jù)庫的人而言，語句怎么執(zhí)行就得好好考慮了，老板不操心，事兒總還得干。設(shè)計(jì)MySQL的大叔人為的把MySQL分為server層和存儲引擎層，但是什么操作是在server層做的，什么操作是在存儲引擎層做的大家可能有些迷糊。本文將以一個實(shí)例來展示它們二者各自負(fù)責(zé)的事情。

準(zhǔn)備工作

為了故事的順利發(fā)展，我們先創(chuàng)建一個表：

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

我們?yōu)閔ero表的id列創(chuàng)建了聚簇索引，為name列創(chuàng)建了一個二級索引。這個hero表主要是為了存儲三國時(shí)的一些英雄，我們向表中插入一些記錄：

INSERT INTO hero VALUES  
    (1, 'l劉備', '蜀'),  
    (3, 'z諸葛亮', '蜀'),  
    (8, 'c曹操', '魏'),  
    (15, 'x荀彧', '魏'),  
    (20, 's孫權(quán)', '吳');

現(xiàn)在表中的數(shù)據(jù)就是這樣的：

mysql> SELECT * FROM hero; 
+----+------------+---------+ 
| id | name       | country | 
+----+------------+---------+ 
|  1 | l劉備      | 蜀      | 
|  3 | z諸葛亮    | 蜀      | 
|  8 | c曹操      | 魏      | 
| 15 | x荀彧      | 魏      | 
| 20 | s孫權(quán)      | 吳      | 
+----+------------+---------+ 
5 rows in set (0.00 sec)

準(zhǔn)備工作就做完了。

正文

一條語句在執(zhí)行之前需要生成所謂的執(zhí)行計(jì)劃，也就是該語句將采用什么方式來執(zhí)行(使用什么索引，采用什么連接順序等等)，我們可以通過Explain語句來查看這個執(zhí)行計(jì)劃，比方說對于下邊語句來說：

mysql> EXPLAIN SELECT * FROM hero WHERE name < 's孫權(quán)' 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)

輸出結(jié)果的key列值為idx_name，type列的值為range，表明會針對idx_name二級索引進(jìn)行一個范圍查詢。很多同學(xué)在這里有一個疑惑：到底是一次性把所有符合條件的二級索引都取出來之后再統(tǒng)一進(jìn)行回表操作，還是每從二級索引中取出一條符合條件的記錄就進(jìn)行回表一次?其實(shí)server層和存儲引擎層的交互是以記錄為單位的，上邊這個語句的完整執(zhí)行過程就是這樣的：

1.server層第一次開始執(zhí)行查詢，把條件name < 's孫權(quán)'交給存儲引擎，讓存儲引擎定位符合條件的第一條記錄。

2.存儲引擎在二級索引idx_name中定位name < 's孫權(quán)'的第一條記錄，很顯然第一條符合該條件的二級索引記錄的name列的值為'c曹操'。然后需要注意，我們看到EXPLAIN語句的輸出結(jié)果的Extra列有一個Using index condition的提示，這表明會將有關(guān)idx_name二級索引的查詢條件放在存儲引擎層判斷一下，這個特性就是所謂的索引條件下推(Index Condition Pushdown，簡稱ICP)。很顯然這里的ICP條件就是name < 's孫權(quán)'。有的同學(xué)可能會問這不就是脫了褲子放屁么，name值為'c曹操'的這條記錄就是通過name < 's孫權(quán)'這個條件定位的，為啥還要再判斷一次?這就是設(shè)計(jì)MySQL 的大叔的粗暴設(shè)計(jì)，十分簡單，沒有為啥～

小貼士：對于使用二級索引進(jìn)行等值查詢的情況有些許不同，比方說上邊的條件換成`name = 's孫權(quán)'`，對于等值查詢的這種情況，設(shè)計(jì)MySQL的大叔在InnoDB存儲引擎層有特殊的處理方案，是不作為ICP條件進(jìn)行處理的。

然后拿著該二級索引記錄中的主鍵值去回表，把完整的用戶記錄都取到之后返回給server層(也就是說得到一條二級索引記錄后立即去回表，而不是把所有的二級索引記錄都拿到后統(tǒng)一去回表)。

3.我們的執(zhí)行計(jì)劃輸出的Extra列有一個Using Where的提示，意味著server層在接收到存儲引擎層返回的記錄之后，接著就要判斷其余的WHERE條件是否成立(就是再判斷一下country = '蜀'是否成立)。如果成立的話，就直接發(fā)送給客戶端。

小貼士：什么?發(fā)現(xiàn)一條記錄符合條件就發(fā)送給了客戶端?那為什么我的客戶端不是一條一條的顯示查詢結(jié)果，而是一下子全部展示呢?這是客戶端軟件的鬼，人家規(guī)定在接收完全部的記錄之后再展示而已。

如果不成立的話，就跳過該條記錄。

4.接著server層向存儲引擎層要求繼續(xù)讀剛才那條記錄的下一條記錄。

5.因?yàn)槊織l記錄的頭信息中都有next_record的這個屬性，所以可以快速定位到下一條記錄的位置，然后繼續(xù)判斷ICP條件，然后進(jìn)行回表操作，存儲引擎把下一條記錄取出后就將其返回給server層。

6.然后重復(fù)第3步的過程，直到存儲引擎層遇到了不符合name < 's孫權(quán)'的記錄，然后向server層返回了讀取完畢的信息，這時(shí)server層將結(jié)束查詢。

這個過程用語言描述還是有點(diǎn)兒啰嗦，我們寫一個超級簡化版的偽代碼來瞅瞅(注意，是超級簡化版)：

first_read = true;  //是否是第一次讀取 
while (true) { 
 
    if (first_read) { 
        first_read = false; 
        err = index_read(...);  //調(diào)用存儲引擎接口，定位到第一條符合條件的記錄; 
    } else { 
        err = index_next(...); //調(diào)用存儲引擎接口，讀取下一條記錄 
    } 
     
    if (err = 存儲引擎的查詢完畢信息) { 
        break;  //結(jié)束查詢 
    } 
     
    if (是否符合WHERE條件) { 
        send_data();    //將該記錄發(fā)送給客戶端; 
    } else { 
        //跳過本記錄 
    } 
}