MySQL的查詢過程分析

關系型數據庫管理系統查詢處理一般分為4個階段：

見下圖

怎么驗證這幾個階段對應在MySQL的關系呢？

這里實驗的數據庫版本：5.6.16-64.2-56

OS：CentOS release 6.5

Kernel：2.6.32-431.el6.x86_64

創建測試庫及表、數據：

root@localhost[(none)]:14: >CREATE DATABASE querydb /!40100 DEFAULT CHARACTER SET utf8 /;

Query OK, 1 row affected (0.00 sec)

root@localhost[(none)]:15: >use querydb;

Database changed

root@localhost[querydb]:20: >create table t(id int auto_increment ,name varchar(50),primary key(id)) engine=innodb;

Query OK, 0 rows affected (0.02 sec)

root@localhost[querydb]:21: >insert into t values(NULL,'a');

Query OK, 1 row affected (0.00 sec)

root@localhost[querydb]:21: >insert into t values(NULL,'b');

Query OK, 1 row affected (0.00 sec)

root@localhost[querydb]:21: >insert into t values(NULL,'c');

Query OK, 1 row affected (0.01 sec)

打開MySQL的profile

root@localhost[querydb]:21: >set @@profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

首先我們來查詢一條正常的sql語句，看MySQL內部進行哪些操作。

root@localhost[querydb]:22: >select id,name from t;

+----+------+

| id | name |

+----+------+

| 1 | a |

| 2 | b |

| 3 | c |

+----+------+

3 rows in set (0.00 sec)

root@localhost[querydb]:24: >show profiles;

+----------+------------+-----------------------+

| Query_ID | Duration | Query |

+----------+------------+-----------------------+

| 1 | 0.00103500 | select id,name from t |

+----------+------------+-----------------------+

1 row in set, 1 warning (0.00 sec)

root@localhost[querydb]:24: >show profile for query 1;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000313 |

| checking permissions | 0.000029 |

| Opening tables | 0.000073 |

| init | 0.000058 |

| System lock | 0.000066 |

| optimizing | 0.000007 |

| statistics | 0.000044 |

| preparing | 0.000025 |

| executing | 0.000002 |

| Sending data | 0.000321 |

| end | 0.000007 |

| query end | 0.000018 |

| closing tables | 0.000018 |

| freeing items | 0.000017 |

| cleaning up | 0.000038 |

+----------------------+----------+

15 ows in set, 1 warning (0.00 sec)

從上面大體上可以看出，

首先檢查權限，權限檢查完后open table操作，然后進行對元數據進行lock操作、然后優化、預編譯、最后執行，到Sending data時，這時已經推送到存儲引擎層了進行拉取數據。最后釋放lock、關閉表并進行清理操作。

先說說各個階段的特征：

starting:語法分析與詞法分析階段

checking permissions:用戶權限檢查

Opening tables:表權限檢查

init:表的列權限檢查

System lock:獲得表的一些lock信息

optimizing:邏輯優化（代數優化），主要RBO優化

statistics:物理優化（非代數優化），主要是CBO優化

preparing和executing:生成代碼并執行

Sending data:也有可能包括執行、提取和發送數據的過程中。

一、查詢分析

1.模擬sql關鍵字錯誤

root@localhost[querydb]:31: >selectt id,name from t;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selectt id,name from t' at line 1

root@localhost[querydb]:31: >show profiles;

+----------+------------+------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------+

| 1 | 0.00103500 | select id,name from t |

| 2 | 0.00022225 | selectt id,name from t |

+----------+------------+------------------------+

2 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:32: >show profile for query 2;

+---------------+----------+

| Status | Duration |

+---------------+----------+

| starting | 0.000154 |

| freeing items | 0.000026 |

| cleaning up | 0.000043 |

+---------------+----------+

3 rows in set, 1 warning (0.00 sec)

再來一個

root@localhost[querydb]:45: >select id,name fr0m t;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't' at line 1

root@localhost[querydb]:45: >show profiles;

+----------+------------+------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

+----------+------------+------------------------+

3 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:45: >show profile for query 3;

+---------------+----------+

| Status | Duration |

+---------------+----------+

| starting | 0.000134 |

| freeing items | 0.000018 |

| cleaning up | 0.000036 |

+---------------+----------+

3 rows in set, 1 warning (0.00 sec)

通過對比發現，starting應該是進行語法分析和詞法分析。

為什么要進行語法分析和詞法分析？

其實跟我們平時的母語的造句一樣，比如：

去 我 飯 吃 這個四個字

按照我們人正常的邏輯造句應該是：我去吃飯

但計算機就不一樣了，可能會出現好多：

去我飯吃

去我吃飯

我去吃飯

我去飯吃

.......

......

計算機會根據一定的規則，就像我們的主謂賓格式造句一樣，從中組合，但有一種可能，就是組合了即沒有語法錯誤也沒有詞法錯誤但意思不一樣的情況。這里假設：我要吃飯和要我吃飯，語法和詞法都沒有錯誤，但語義不同，這時計算機就要進行語義分析了。

這里面的判斷規則有自動機進行判斷，也叫圖靈機（是偉大的計算機科學之父：圖靈 發明的，圖靈獎想必大家都熟悉吧^_^），不過龍書（編譯原理）里會有詳細介紹

二、查詢檢查

root@localhost[querydb]:45: >select ida,name from t; #表存在，字段不存在

ERROR 1054 (42S22): Unknown column 'ida' in 'field list'

root@localhost[querydb]:19: >show profiles;

+----------+------------+------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

+----------+------------+------------------------+

4 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:19: >show profile for query 4;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000531 |

| checking permissions | 0.000037 |

| Opening tables | 0.000133 |

| init | 0.000116 |

| end | 0.000017 |

| query end | 0.000018 |

| closing tables | 0.000027 |

| freeing items | 0.000032 |

| cleaning up | 0.000052 |

+----------------------+----------+

9 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:19: >select id,name from tab; #id,name是表t的字段，而tab表不存在

ERROR 1146 (42S02): Table 'querydb.tab' doesn't exist

root@localhost[querydb]:23: >show profiles;

+----------+------------+-------------------------+

| Query_ID | Duration | Query |

+----------+------------+-------------------------+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

+----------+------------+-------------------------+

5 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:23: >show profile for query 5;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000621 |

| checking permissions | 0.000039 |

| Opening tables | 0.000367 | 感覺是不是哪里

| query end | 0.000023 | 不對勁，是不是

| closing tables | 0.000006 | 少了init階段

| freeing items | 0.000055 |

| cleaning up | 0.000066 |

+----------------------+----------+

7 rows in set, 1 warning (0.00 sec)

所以從上面可以觀察到，這里checking permissions你可以進行測試。這里我不在進行測試?！举x予用戶test什么權限都沒有：grant usage on . to test@'%' identified by 'test';和賦予test2用戶只有查詢t表中id列的權限：grant select(id) on querydb.t to test2@'%' identified by 'test2';】，這里測試略

checking permissions： 對MySQL的連接用戶進行權限檢查。

Opening tables： 對表權限進行檢查。

init階段：對表中的列進行權限檢查。

三、檢查優化

實驗的sql語句：

select 1;

select id,name from t ;

select id,name from t where 0=1;

三個語句查看其變化。

root@localhost[querydb]:36: >select 1;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

root@localhost[querydb]:36: >show profiles;

+----------+------------+----------------------------+

| Query_ID | Duration | Query |

+----------+------------+----------------------------+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

| 6 | 0.00115800 | select id,name,abc from t |

| 7 | 0.00029450 | select 1 |

+----------+------------+----------------------------+

7 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:36: >show profile for query 7;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000196 |

| checking permissions | 0.000006 |

| Opening tables | 0.000007 |

| init | 0.000016 |

| optimizing | 0.000010 |

| executing | 0.000013 |

| end | 0.000008 |

| query end | 0.000006 |

| closing tables | 0.000001 |

| freeing items | 0.000014 |

| cleaning up | 0.000019 |

+----------------------+----------+

11 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:36: >select id,name from t ;

+----+------+

| id | name |

+----+------+

| 1 | a |

| 2 | b |

| 3 | c |

+----+------+

3 rows in set (0.00 sec)

root@localhost[querydb]:38: >show profiles;

+----------+------------+----------------------------+

| Query_ID | Duration | Query |

+----------+------------+----------------------------+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

| 6 | 0.00115800 | select id,name,abc from t |

| 7 | 0.00029450 | select 1 |

| 8 | 0.00074025 | select id,name from t |

+----------+------------+----------------------------+

8 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:38: >show profile for query 8;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000274 |

| checking permissions | 0.000024 |

| Opening tables | 0.000059 |

| init | 0.000036 |

| System lock | 0.000029 |

| optimizing | 0.000008 |

| statistics | 0.000031 |

| preparing | 0.000021 |

| executing | 0.000002 |

| Sending data | 0.000172 |

| end | 0.000011 |

| query end | 0.000012 |

| closing tables | 0.000013 |

| freeing items | 0.000018 |

| cleaning up | 0.000031 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:38: >select id,name from t where 0=1;

Empty set (0.00 sec)

root@localhost[querydb]:41: >show profiles;

+----------+------------+---------------------------------+

| Query_ID | Duration | Query |

+----------+------------+---------------------------------+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

| 6 | 0.00115800 | select id,name,abc from t |

| 7 | 0.00029450 | select 1 |

| 8 | 0.00074025 | select id,name from t |

| 9 | 0.00058500 | select id,name from t where 0=1 |

+----------+------------+---------------------------------+

9 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:41: >show profile for query 9;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000279 |

| checking permissions | 0.000015 |

| Opening tables | 0.000046 |

| init | 0.000057 |

| System lock | 0.000019 |

| optimizing | 0.000025 |

| executing | 0.000014 |

| end | 0.000005 |

| query end | 0.000008 |

| closing tables | 0.000010 |

| freeing items | 0.000020 |

| cleaning up | 0.000089 |

+----------------------+----------+

12 rows in set, 1 warning (0.00 sec)

為了便于觀察，我這里查看其語句的執行計劃然后看執行的語句。

下面輸出的有點不好看，可以看下面的截圖。

root@localhost[querydb]:10: >explain extended select id,name from t where 0=1;

+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |

+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

1 row in set, 1 warning (0.00 sec)

root@localhost[querydb]:11: >show warnings;

+-------+------+------------------------------------------------------------------------------------------------------------+

| Level | Code | Message |

+-------+------+------------------------------------------------------------------------------------------------------------+

| Note | 1003 | / select#1 / select querydb.t.id AS id,querydb.t.name AS name from querydb.t where 0 |

+-------+------+------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

通過三個sql語句的執行情況發現。

optimizing階段：為代數優化階段

statistics和preparing階段：為物理優化階段，從字面信息上來看，可以理解是收集統計信息，生成執行計劃，選擇最優的存取路徑進行執行。

同時有沒有發現Sending data,說明這時已經到存儲引擎層了去拉取數據，對比上面select 1和where 0=1 都沒有Sending data，更可以很好理解，但這里有個問題？MySQL層和存儲引擎層是通過接口實現的，當查詢的結果返回時怎么知道返回哪個線程呢？其實在內部，每個查詢都會分配一個查詢線程ID的，根據線程ID編號來的。

一、 查詢執行

root@localhost[querydb]:41: >select id,name from t;

+----+------+

| id | name |

+----+------+

| 1 | a |

| 2 | b |

| 3 | c |

+----+------+

3 rows in set (0.00 sec)

root@localhost[querydb]:15: >show profiles;

+----------+------------+---------------------------------+

| Query_ID | Duration | Query |

+----------+------------+---------------------------------+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

| 6 | 0.00115800 | select id,name,abc from t |

| 7 | 0.00029450 | select 1 |

| 8 | 0.00074025 | select id,name from t |

| 9 | 0.00058500 | select id,name from t where 0=1 |

| 10 | 0.00194675 | select id,name from t |

+----------+------------+---------------------------------+

10 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:15: >show profile for query 10;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000763 |

| checking permissions | 0.000050 |

| Opening tables | 0.000184 |

| init | 0.000178 |

| System lock | 0.000076 |

| optimizing | 0.000025 |

| statistics | 0.000095 |

| preparing | 0.000055 |

| executing | 0.000002 |

| Sending data | 0.000335 |

| end | 0.000018 |

| query end | 0.000028 |

| closing tables | 0.000023 |

| freeing items | 0.000041 |

| cleaning up | 0.000076 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

executing：為執行階段了。

這里還有一個System lock階段：其實當執行DDL、DML操作時，MySQL會在內部對表的元數據進行加鎖還有其他的鎖，比如S鎖，X鎖，IX鎖，IS鎖等，用于解決或者保證DDL操作與DML操作之間的一致性?！究梢詤⒖?lt;<InnoDB存儲引擎技術內幕>>或事務處理與實現】

參考書籍：

【編譯原理】 [美] Alfred V.Aho，[美] Monica S.Lam，[美] Ravi Sethi 等 著；趙建華，鄭滔 等 譯

【數據庫系統概論 】 王珊，薩師煊 著

【數據庫查詢優化器的藝術：原理解析與SQL性能優化】 李海翔 著

【InnoDB存儲引擎技術內幕】 姜承堯 著

【事務處理與實現】