天天看點
傳回

一條SQL查詢語句是如何執行的？

一條SQL查詢語句是如何執行的？

還不知道SQL查詢語句是如何執行的？看一下這篇文章吧

MySQL是典型的

C/S架構

（用戶端/伺服器架構），用戶端程序向服務端程序發送一段文本（MySQL指令），伺服器程序進行語句處理然後傳回執行結果。

問題來了。伺服器程序對用戶端發送的請求究竟做了什麼處理呢？本文以查詢請求為例，講解MySQL伺服器程序的處理流程。

如下圖所示，伺服器程序在處理用戶端請求的時候，大緻需要進行3個步驟：

  • 處理連接配接
  • 解析與優化
  • 存儲引擎

接下來我們來詳細了解一下這3步具體都做了什麼。

一條SQL查詢語句是如何執行的？

1. 處理連接配接

用戶端向伺服器發送請求并最終收到響應，本質上是一個程序間通信的過程。

MySQL有專門用于處理連接配接的子產品——連接配接器。

1.1 用戶端和服務端的通信方式

1.1.1 TCP/IP協定

TCP/IP

協定是MySQL用戶端和伺服器最常用的通信方式。

我們平時所說的MySQL伺服器預設監聽的端口是

3306

，這句話的前提是用戶端程序和伺服器程序使用的是

TCP/IP

協定進行通信。

我們在使用

mysql

指令啟動用戶端程式時，隻要在

-h

參數後跟随IP位址作為伺服器程序所在的主機位址，那麼通訊方式便是

TCP/IP

協定。

如果用戶端程序和伺服器程序位于同一台主機，且要使用

TCP/IP

 協定進行通信，則IP位址需要指定為127.0.0.1，而不能使用localhost

1.1.2 UNIX域套接字

如果用戶端程序和伺服器程序都位于類UNIX作業系統（MacOS、Centos、Ubuntu等）的主機之上，并且在啟動用戶端程式時沒有指定主機名，或者指定的主機名為

localhost

，又或者指定了

--protocol=socket

的啟動參數，那麼用戶端程序和伺服器程序就會使用

UNIX域套接字

進行程序間通信。

MySQL伺服器程序預設監聽的

UNIX域套接字

檔案為

/temp/mysql.sock

，用戶端程序啟動時也預設會連接配接到這個UNIX域套接字檔案之上。

如果不明白

UNIX域套接字

 到底是什麼也沒關系，隻要知道這是程序之間的一種通訊方式就可以了，這裡提及的主要目的是希望讀者知曉MySQL用戶端和程序通訊方式不止于

TCP/IP

 協定

1.1.3 命名管道和共享記憶體

如果你的MySQL是安裝在Windows主機之上，用戶端和伺服器程序可以使用命名管道和共享記憶體的方式進行通信。

不過使用這些通信方式需要在服務端和用戶端啟動時添加一些啟動參數。

  • 使用命名管道進行通信。需要在啟動伺服器時添加

    --enable-named-pipe

     參數，同時在啟動用戶端程序時添加

    --pipe

     或者

    --protocol=pipe

     參數
  • 使用共享記憶體進行通信。需要在啟動伺服器時添加

    --shared-memory

     參數，啟動成功後，共享記憶體便成為本地用戶端程式的預設連接配接方式；也可以在啟動用戶端程序的指令中加上

    --protocol=memory

     參數明确指定使用共享記憶體進行通信
如果不明白命名管道和共享記憶體到底是什麼沒關系，隻要知道這是程序之間的一種通訊方式就可以了，這裡提及的主要目的是希望讀者知曉MySQL用戶端和程序通訊方式不止于

TCP/IP

1.2 權限驗證

确認通信方式并且成功建立連接配接之後，連接配接器就要開始驗證你的身份了，使用的資訊就是你的使用者名和密碼。

  • 如果使用者名或者密碼錯誤，用戶端連接配接會立即斷開
  • 如果使用者名密碼認證通過，連接配接器會到權限表裡面查出目前登陸使用者擁有的權限。之後這個連接配接裡面的權限判斷邏輯，都将依賴于此時讀到的權限。

1.3 檢視MySQL連接配接

每當一個用戶端連接配接到服務端時，服務端程序都會建立一個單獨的線程來處理目前用戶端的互動操作。

那麼如何檢視MySQL目前所有的連接配接？

mysql> show global status like 'Thread%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+

各字段含義如下表

字段 含義
Threads_cached 緩存中的線程連接配接數
Threads_connected 目前打開的連接配接數
Threads_created 為處理連接配接建立的線程數
Threads_running 非睡眠狀态的連接配接數，通常指并發連接配接數

建立連接配接之後，除非用戶端主動斷開連接配接，否則伺服器會等待用戶端發送請求。但是線程的建立和保持是需要消耗伺服器資源的，是以伺服器會把長時間不活動的用戶端連接配接斷開。

有2個參數控制這個自動斷開連接配接的行為，每個參數都預設為28800秒，8小時。

-- 非互動式逾時時間，如JDBC連接配接
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

-- 互動式逾時時間，如資料庫檢視工具Navicat等
mysql> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+

既然連接配接消耗資源，那是不是MySQL的最大連接配接數也有預設限制呢？沒錯！預設最大連接配接數為151。

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
題外話：細心的讀者可能會發現MySQL某些查詢語句帶有

global

 關鍵字，這個關鍵字有什麼含義呢？

MySQL的系統變量有兩個作用範圍（不區分大小寫），分别是

  • GLOBAL

     （全局範圍）：變量的設定影響伺服器和所有用戶端

  • SESSION

     （會話範圍）：變量的設定僅影響目前連接配接（會話）

但是并非每個參數都具有兩個作用範圍，比如允許同時連接配接到伺服器的用戶端的數量

max_connections

就隻有全局級别。

當沒有帶作用範圍關鍵字時，預設是

SESSION

級别，包括查詢和修改操作。

比如修改一個參數之後，在目前視窗生效了，但是在其他視窗卻沒有生效

show VARIABLES like 'autocommit';
set autocommit = on;

是以，如果隻是臨時修改，請使用

SESSION

級别，如果需要目前設定在其他會話中生效，需要使用

GLOBAL

關鍵字。

到此為止，伺服器程序已經和用戶端程序建立了連接配接，下一步将處理用戶端傳來的請求了。

2. 解析與優化

伺服器收到用戶端傳來的請求之後，還需要經過查詢緩存、詞法文法解析和預處理、查詢優化的處理。

2.1 查詢緩存

如果我們兩次都執行同一條查詢指令，第二次的響應時間會不會比第一次的響應時間短一些？

之前使用過Redis緩存工具的讀者應該會有這個很自然的想法，MySQL收到查詢請求之後應該先到緩存中檢視一下，看一下之前是不是執行過這條指令。如果緩存命中，則直接傳回結果；否則重新進行查詢，然後加入緩存。

MySQL确實内部自帶了一個緩存子產品。

現在有一張500W行且沒有添加索引的資料表，我執行以下指令兩次，第二次會不會變得很快？

SELECT * FROM t_user WHERE user_name = '蟬沐風'

并不會！說明緩存沒有生效，為什麼？MySQL預設是關閉自身的緩存功能的，檢視一下

query_cache_type

變量設定。

mysql> show variables like 'query_cache_type';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_type             | OFF     |
+------------------------------+---------+

預設關閉就意味着不推薦，MySQL為什麼不推薦使用者使用自己的緩存功能呢？

  1. MySQL自帶的緩存系統應用場景非常有限，它要求SQL語句必須一模一樣，多一個空格，變一個大小寫都被認為是兩條不同的SQL語句
  2. 緩存失效非常頻繁。隻要一個表的資料有任何修改，針對該表的所有緩存都會失效。對于更新頻繁的資料表而言，緩存命中率非常低！

是以緩存的功能還是交給專業的ORM架構（比如MyBatis預設開啟一級緩存）或者獨立的緩存服務Redis更加适合。

MySQL8.0已經徹底移除了緩存功能

2.2 解析器 & 預處理器（Parser & Preprocessor）

現在跳過緩存這一步了，接下來需要做什麼了？

如果我随便在用戶端終端裡輸入一個字元串

chanmufeng

，伺服器傳回了一個1064的錯誤

mysql> chanmufeng;
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 'chanmufeng' at line 1

伺服器是怎麼判斷出我的輸入是錯誤的呢？這就是MySQL的Parser解析器的作用了，它主要包含兩步，分别是詞法解析和文法分析。

2.2.1 詞法解析

以下面的SQL語句為例

SELECT * FROM t_user WHERE user_name = '蟬沐風' AND age > 3;

分析器先會做“詞法分析”，就是把一條完整的SQL語句打碎成一個個單詞，比如一條簡單的SQL語句，會打碎成8個符号，每個符号是什麼類型，從哪裡開始到哪裡結束。

MySQL 從你輸入的

SELECT

這個關鍵字識别出來，這是一個查詢語句。它也要把字元串

t_user

識

别成“表名 t_user”，把字元串

user_name

識别成“列 user_name"。

2.2.2 文法分析

做完詞法解析，接下來需要做文法分析了。

根據詞法分析的結果，文法分析器會根據文法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 文法，比如單引号是否閉合，關鍵詞拼寫是否正确等。

解析器會根據SQL語句生成一個資料結構，這個資料結構我們成為解析樹。

​

我故意拼錯了

SELECT

關鍵字，MySQL報了文法錯誤，就是在文法分析這一步。

mysql> ELECT * FROM t_user WHERE user_name = '蟬沐風' AND age > 3;
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 'ELECT * FROM t_user WHERE user_name = '蟬沐風'' at line 1

詞法文法分析是一個非常基礎的功能，Java 的編譯器、百度搜尋引擎如果要識别語句，必須也要有詞法文法分析功能。

任何資料庫的中間件，要解析 SQL完成路由功能，也必須要有詞法和文法分析功能，比如 Mycat，Sharding-JDBC（用到了Druid Parser）等都是如此。在市面上也有很多的開源的詞法解析的工具，比如 LEX，Yacc等。

2.2.3 預處理器

如果我們寫了一條文法和詞法都沒有問題的SQL，但是字段名和表名卻不存在，這個錯誤是在哪一個階段爆出的呢？

詞法解析和文法分析是無法知道資料庫裡有什麼表，有哪些字段的。要知道這些資訊還需要解析階段的另一個工具——預處理器。

它會檢查生成的解析樹，解決解析器無法解析的語義。比如，它會檢查表和列名是否存在，檢查名字和别名，保證沒有歧義。預處理之後得到一個新的解析樹。

本質上，解析和預處理是一個編譯過程，涉及到詞法解析、文法和語義分析，更多細節我們不會探究，感興趣的讀者可以看一下編譯原理方面的書籍。

2.3 查詢優化器（Optimizer）與查詢執行計劃

到了這一步，MySQL終于知道我們想查詢的表和列以及相應的搜尋條件了，是不是可以直接進行查詢了？

還不行。MySQL作者擔心我們寫的SQL太垃圾，是以有設計出一個叫做查詢優化器的東東，輔助我們提高查詢效率。

2.3.1 什麼是查詢優化器？

一條 SQL語句是不是隻有一種執行方式？或者說資料庫最終執行的 SQL是不是就是我們發送的 SQL?

不是。一條 SQL 語句是可以有很多種執行方式的，最終傳回相同的結果，他們是等價的。

舉一個非常簡單的例子，比如你執行下面這樣的語句：

SELECT * FROM t1, t2 WHERE t1.id = 10 AND t2.id = 20
  • 既可以先從表 t1 裡面取出 id=10 的記錄，再根據 id 值關聯到表 t2，再判斷 t2 裡面 id 的值是否等于 20。
  • 也可以先從表 t2 裡面取出 id=20 的記錄，再根據 id 值關聯到表 t1，再判斷 t1 裡面 id 的值是否等于 10。

這兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，如果有這麼多種執行方式，這些執行方式怎麼得到的？最終選擇哪一種去執行？根據什麼判斷标準去選擇？

這個就是 MySQL的查詢優化器的子產品（Optimizer）的工作。

查詢優化器的目的就是根據解析樹生成不同的執行計劃（Execution Plan），然後選擇一種最優的執行計劃，MySQL 裡面使用的是基于開銷（cost）的優化器，哪種執行計劃開銷最小，就用哪種。

2.3.2 優化器究竟做了什麼？

舉兩個簡單的例子∶

  1. 當我們對多張表進行關聯查詢的時候，以哪個表的資料作為基準表。
  2. 有多個索引可以使用的時候，選擇哪個索引。

實際上，對于每一種資料庫來說，優化器的子產品都是必不可少的，他們通過複雜的算法實作盡可能優化查詢效率。

往細節上說，查詢優化器主要做了下面幾方面的優化：

  • 子查詢優化
  • 等價謂詞重寫
  • 條件化簡
  • 外連接配接消除
  • 嵌套連接配接消除
  • 連接配接消除
  • 語義優化
本文不會對優化的細節展開講解，大家先對MySQL的整體架構有所了解就可以了，具體細節之後單獨開篇介紹

但是優化器也不是萬能的，如果SQL語句寫得實在太垃圾，再牛的優化器也救不了你了。是以大家在編寫SQL語句的時候還是要有意識地進行優化。

2.3.3 執行計劃

優化完之後，得到一個什麼東西呢？優化器最終會把解析樹變成一個查詢執行計劃。

查詢執行計劃展示了接下來執行查詢的具體方式，比如多張表關聯查詢，先查詢哪張表，在執行查詢的時候有多個索引可以使用，實際上該使用哪些索引。

MySQL提供了一個檢視執行計劃的工具。我們在 SQL語句前面加上

EXPLAIN

就可以看到執行計劃的資訊。

mysql> EXPLAIN SELECT * FROM t_user WHERE user_name = '';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

如果要得到更加詳細的資訊，還可以用

FORMAT=JSON

，或者開啟

optimizer trace

。

mysql> EXPLAIN FORMAT=JSON SELECT * FROM t_user WHERE user_name = '';
文本不會帶大家詳細了解執行計劃的每一個參數，内容很龐雜，大家先對MySQL的整體架構有所了解就可以了，具體細節之後單獨開篇介紹

3. 存儲引擎

經曆千辛萬苦，MySQL終于算出了最終的執行計劃，然後就可以直接執行了嗎？

好吧。。。依然還不可以。

我們知道，表是由一行一行的記錄組成的，但這隻是邏輯上的概念，或者說隻是看上去是這樣而已。

3.1 什麼是存儲引擎

到底該把資料存儲在什麼位置，是記憶體還是磁盤？怎麼從表裡讀取資料，以及怎麼把資料寫入具體的表中，這都是

存儲引擎

 負責的事情。

好吧，看到這裡或許你還不知道存儲引擎到底是什麼。畢竟存儲引擎這個名字聽起來太玄乎了，它的前身叫做表處理器，是不是就接地氣了許多呢？

3.2 為什麼需要存儲引擎

因為存儲的需求不同。

試想一下：

  • 如果一張表，需要很高的通路速度，而不需要考慮持久化的問題，是不是最好把資料放在記憶體呢？
  • 如果一張表，是用來做曆史資料存檔的，不需要修改，也不需要索引，那是不是要支援資料的壓縮?
  • 如果一張表用在讀寫并發很多的業務中，是不是要支援讀寫互不幹擾，而且要保證比較高的資料一緻性呢?

大家應該明白了，為什麼要支援這麼多的存儲引擎，因為一種存儲引擎不能提供所有的特性。

存儲引擎是計算機抽象的典型代表，它的功能就是接受上層指令，然後對表中資料進行讀取和寫入，而這些操作對上層完全是屏蔽的。你甚至可以查閱MySQL文檔定義自己的存儲引擎，隻要對外實作同樣的接口就可以了。
存儲引擎就是MySQL對資料進行讀寫的插件而已，可以根據不同目的随意更換（插拔）

3.3 存儲引擎怎麼用

3.3.1 建立表的時候指定存儲引擎

在建立表的時候可以指定目前表的存儲引擎，如果沒有指定，預設的存儲引擎為

InnoDB

，如果想顯式指定存儲引擎，可以這樣

CREATE TABLE `t_user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

3.3.2 修改表的存儲引擎 

ALTER TABLE 表名 ENGINE = 存儲引擎名稱;

3.4 存儲引擎底層差別

下面我們分别建立3張設定了不同存儲引擎的表，t_user_innodb、t_user_myisam、t_user_memory

一條SQL查詢語句是如何執行的？

我們看一下不同存儲引擎在底層存儲方面的差異，首先找到MySQL的資料存儲目錄

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

進入到目标目錄之後，找到目前資料庫對應的目錄（MySQL會為一個資料庫建立一個同名的目錄），資料庫中表的存儲結構如下

一條SQL查詢語句是如何執行的？

不同的存儲引擎存放資料的方式不一樣，産生的檔案數量和格式也不一樣，InnoDB檔案包含2個，MEMORY檔案包含1個，MYISAM檔案包含3個。

3.5 常見存儲引擎比較

首先我們檢視一下目前MySQL伺服器支援的存儲引擎都有哪一些。

mysql> SHOW ENGINES;
+--------------------+---------+--------------+------+------------+
| Engine             | Support | Transactions | XA   | Savepoints |
+--------------------+---------+--------------+------+------------+
| InnoDB             | DEFAULT | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | NO           | NO   | NO         |
| MEMORY             | YES     | NO           | NO   | NO         |
| BLACKHOLE          | YES     | NO           | NO   | NO         |
| MyISAM             | YES     | NO           | NO   | NO         |
| CSV                | YES     | NO           | NO   | NO         |
| ARCHIVE            | YES     | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | NO           | NO   | NO         |
| FEDERATED          | NO      | NULL         | NULL | NULL       |
+--------------------+---------+--------------+------+------------+

其中，

  • Support表示該存儲引擎是否可用；
  • DEFAULT表示目前MySQL伺服器預設的存儲引擎；
  • Transactions表示該存儲引擎是否支援事務；
  • XA表示該存儲引擎是否支援分布式事務；
  • Savepoints表示該存儲引擎是否支援事務的部分復原。

3.5.1 MylSAM

應用範圍比較小，表級鎖定限制了讀/寫的性能，是以在Web和資料倉庫配置中，通常用于隻讀或以讀為主的工作。

特點:

  • 支援表級别的鎖（插入和更新會鎖表），不支援事務；
  • 擁有較高的插入（insert）和查詢（select）速度；
  • 存儲了表的行數（count速度更快）。
怎麼快速向資料庫插入100萬條資料？

可以先用MylSAM插入資料，然後修改存儲引擎為InnoDB。

3.5.2 InnoDB

MySQL 5.7及更新版中的預設存儲引擎。InnoDB是一個事務安全（與ACID相容）的MySQL 存儲引擎，它具有送出、復原和崩潰恢複功能來保護使用者資料。InnoDB行級鎖（不更新為更粗粒度的鎖）和Oracle風格的一緻非鎖讀提高了多使用者并發性。InnoDB将使用者資料存儲在聚集索引中，以減少基于主鍵的常見查詢的I/O。為了保持資料完整性，InnoDB還支援外鍵引用完整性限制。

特點：

  • 支援事務，支援外鍵，是以資料的完整性、一緻性更高；
  • 支援行級别的鎖和表級别的鎖；
  • 支援讀寫并發，寫不阻塞讀（MVCC）；
  • 特殊的索引存放方式，可以減少IO，提升査詢效率。

番外：InnoDB本來是InnobaseOy公司開發的，它和MySQL AB公司合作開源了InnoDB的代碼。

但是沒想到MySQL的競争對手Oracle把InnobaseOy收購了。後來08年Sun公司（開發Java語言的Sun）收購了MySQL AB，09年Sun公司又被Oracle收購了，是以MySQL和 InnoDB又是一家了。

有人覺得MySQL越來越像Oracle,其實也是這個原因。

3.5.3 Memory

将所有資料存儲在RAM中，以便快速通路。這個引擎以前被稱為堆引擎。

  • 把資料放在記憶體裡面，讀寫的速度很快，但是資料庫重新開機或者崩潰，資料會全部消失；
  • 隻适合做臨時表。

3.5.4 CSV

它的表實際上是帶有逗号分隔值的文本檔案。csv表允許以CSV格式導入或轉儲資料, 以便與讀寫相同格式的腳本和應用程式交換資料。因為CSV表沒有索引，是以通常在正常操作期間将資料儲存在InnoDB表中，隻在導入或導出階段使用csv表。

  • 不允許空行，不支援索引；
  • 格式通用，可以直接編輯，适合在不同資料庫之間導入導出。

3.5.5 Archive

專用與存檔，空間經過壓縮，用于存儲和檢索大量很少引用的資訊。

  • 不支援索引；
  • 不支援update、delete。

3.6 如何選擇存儲引擎

  • 如果對資料一緻性要求比較高，需要事務支援，可以選擇InnoDB。
  • 如果資料查詢多更新少，對查詢性能要求比較高，可以選擇MyISAM。
  • 如果需要一個用于查詢的臨時表，可以選擇Memory。

如果所有的存儲引擎都不能滿足你的需求，并且技術能力足夠，可以根據官網内部手冊用C語言開發一個存儲引擎：https://dev.mvsql.com/doc/internals/en/custom-engine.html

上一篇: 30分鐘使用Docsify+Github Pages搭建個人部落格
下一篇: MongoDB給資料庫建立使用者