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

[[322289]]

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

準(zhǔn)備工作

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

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)建了一個(gè)二級(jí)索引。這個(gè)hero表主要是為了存儲(chǔ)三國時(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語句來查看這個(gè)執(zhí)行計(jì)劃，比方說對(duì)于下邊語句來說：

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

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

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

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

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

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

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

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

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

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

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

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

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

上述的偽代碼雖然很粗糙，但也基本表明了意思哈～ 之后有機(jī)會(huì)我們?cè)賴Z叨嘮叨使用臨時(shí)表的情況以及使用filesort的情況是怎么執(zhí)行的。

本文轉(zhuǎn)載自微信公眾號(hào)「 我們都是小青蛙」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系 我們都是小青蛙公眾號(hào)。