MySQL的Server層和存儲引擎層是如何交互的
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的情況是怎么執行的。
本文轉載自微信公眾號「 我們都是小青蛙」，可以通過以下二維碼關注。轉載本文請聯系 我們都是小青蛙公眾號。
