一條查詢SQL是如何執(zhí)行的？

sql執(zhí)行

1.通訊協(xié)議
首先，MySQL 必須要運行一個服務(wù)，監(jiān)聽默認的端口（3306）
MySQL 支持多種通信協(xié)議。
第一個就是 TCP/IP 協(xié)議，編程語言的連接模塊都是用 TCP 協(xié)議連接到 MySQL 服務(wù)器的

[root@iZwz~]# netstat -an | grep 3306
tcp6       0      0 :::33060                :::*                    LISTEN
tcp6       0      0 :::3306                 :::*                    LISTEN

第二種是 Unix Socket。比如我們在 Linux 服務(wù)器，不用通過網(wǎng)絡(luò)協(xié)議，也可以連接到 MySQL 的服務(wù)器，它需要用到服務(wù)器上的一個物理文件（mysql.sock）值得一提的是，這是所有協(xié)議中最高效的一個。

mysql> show variables like 'socket';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| socket        | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.01 sec)

另外還有命名管道（Named Pipes）和內(nèi)存共享（Share Memory）的方式。
Share Memory協(xié)議，這個協(xié)議一般人不知道，肯定也沒用過，因為這個只有windows可以使用，使用這個協(xié)議需要在配置文件中在啟動的時候使用–shared-memory參數(shù)，注意的是，使用此協(xié)議，一個host上只能有一個server，所以這個東西一般沒啥用的，除非你懷疑其他協(xié)議不能正常工作，實際上微軟的SQL Sever也支持這個協(xié)議
Named Pipes協(xié)議，這個協(xié)議也是只有windows才可以用，同shared memory一樣，使用此協(xié)議，一個host上依然只能有一個server，即使是使用不同的端口也不行，Named Pipes 是為局域網(wǎng)而開發(fā)的協(xié)議。內(nèi)存的一部分被某個進程用來向另一個進程傳遞信息，因此一個進程的輸出就是另一個進程的輸入。第二個進程可以是本地的（與第一個進程位于同一臺計算機上），也可以是遠程的（位于聯(lián)網(wǎng)的計算機上）。正因為如此，假如你的環(huán)境中沒有或者禁用TCP/IP環(huán)境，而且是windows服務(wù)器，那么好歹你的數(shù)據(jù)庫還能工作。使用這個協(xié)議需要在啟動的時候添加–enable-named-pipe選項

第二個是通信方式。

image.png

MySQL 使用半雙工的通信方式。
半雙工意味著要么是客戶端向服務(wù)端發(fā)送數(shù)據(jù)，要么是服務(wù)端向客戶端發(fā)送數(shù)據(jù)，這兩個動作不能同時發(fā)生。
所以客戶端發(fā)送 SQL 語句給服務(wù)端的時候，（在一次連接里面）數(shù)據(jù)是不能分成小塊發(fā)送的，不管你的 SQL 語句有多大，都是一次性發(fā)送。
如果發(fā)送給服務(wù)器的數(shù)據(jù)包過大，我們必須要調(diào)整 MySQL 服務(wù)器配置 max_allowed_packet 參數(shù)的值

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

另一方面，對于服務(wù)端來說，也是一次性發(fā)送所有的數(shù)據(jù)，不能因為你已經(jīng)取到了想要的數(shù)據(jù)就中斷操作。
所以，我們一定要在程序里面避免不帶 limit 的這種操作。
連接方式
第三個是連接這一塊。
MySQL 既支持短連接，也支持長連接。短連接就是操作完畢以后，馬上 close 掉。長連接可以保持打開，后續(xù)的程序訪問的時候還可以使用當前連接。
長時間不活動的連接，MySQL 服務(wù)器會斷開。

# 非交互式超時時間，如 JDBC 程序
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
# 交互式超時時間，如數(shù)據(jù)庫工具
mysql> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

MySQL 默認的最大連接數(shù)是 151 個（5.7 版本），最大是 16384（2^14）

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 128   |
+-----------------+-------+
1 row in set (0.00 sec)

查看 3306 端口當前連接數(shù)

[root@iZw ~]# netstat -an|grep 3306|wc -l
2

查看查詢的執(zhí)行狀態(tài)。

SHOW FULL PROCESSLIST;

image.png

image.png

2、查詢緩存(Query Cache)

MySQL 內(nèi)部自帶了一個緩存模塊。默認是關(guān)閉的。主要是因為 MySQL 自帶的緩存的應(yīng)用場景有限，第一個是它要求 SQL 語句必須一模一樣。第二個是表里面任何一條數(shù)據(jù)發(fā)生變化的時候，這張表所
有緩存都會失效。
當我們開啟Mysql的查詢緩存，當執(zhí)行完全相同的SQL語句的時候，服務(wù)器就會直接從緩存中讀取結(jié)果。當數(shù)據(jù)被修改， 之前的緩存會失效，所以修改比較頻繁的表不適合做查詢緩存。

查看當前mysql是否開啟了查詢緩存
SHOW VARIABLES LIKE 'query_cache_type';
查看查詢緩存的占用大小
SHOW VARIABLES LIKE 'query_cache_size';
查看查詢緩存的狀態(tài)變量
SHOW STATUS LIKE 'Qcache%'

查詢緩存狀態(tài)參數(shù)表

在 MySQL 5.8 中，查詢緩存已經(jīng)被移除了？

3.語法解析和預處理(Parser & Preprocessor)

服務(wù)器是怎么知道我輸入的內(nèi)容是錯誤的？或者，當我輸入了一個語法完全正確的 SQL，但是表名不存在，它是怎么發(fā)現(xiàn)的？這個就是 MySQL 的 Parser 解析器和 Preprocessor 預處理模塊。這一步主要做的事情是對 SQL 語句進行詞法和語法分析和語義的解析。
詞法解析
詞法分析就是把一個完整的 SQL 語句打碎成一個個的單詞。
比如一個簡單的 SQL 語句：
select name from user where id = 1;
它會打碎成 8 個符號，記錄每個符號是什么類型，從哪里開始到哪里結(jié)束。
語法解析
第二步就是語法分析，語法分析會對 SQL 做一些語法檢查，比如單引號有沒有閉合，然后根據(jù) MySQL
定義的語法規(guī)則，根據(jù) SQL 語句生成一個數(shù)據(jù)結(jié)構(gòu)。這個數(shù)據(jù)結(jié)構(gòu)我們把它叫做解析樹。

image.png

4、查詢優(yōu)化（Query Optimizer）與查詢執(zhí)行計劃

問題：一條 SQL 語句是不是只有一種執(zhí)行方式？或者說數(shù)據(jù)庫最終執(zhí)行的 SQL 是不是就是我們發(fā)送的 SQL？
這個答案是否定的。
一條 SQL 語句是可以有很多種執(zhí)行方式的。但是如果有這么多種執(zhí)行方式，這些執(zhí)行方式怎么得到的？最終選擇哪一種去執(zhí)行？根據(jù)什么判斷標準去選擇？
這個就是 MySQL 的查詢優(yōu)化器的模塊（Optimizer）。
查詢優(yōu)化器的目的就是根據(jù)解析樹生成不同的執(zhí)行計劃，然后選擇一種最優(yōu)的執(zhí)行計劃，MySQL 里面使用的是基于開銷（cost）的優(yōu)化器，那種執(zhí)行計劃開銷最小，就用哪種。
[MySQL]中可以通過show status like 'last_query_cost' 來查看查上一個查詢的代價，而且它是io_cost和cpu_cost的開銷總和，它通常也是我們評價一個查詢的執(zhí)行效率的一個常用指標。

SELECT * from `xllm_order` limit 100;
show status like 'Last_query_cost';
--代表需要隨機讀取幾個 4K 的數(shù)據(jù)頁才能完成查找。

優(yōu)化器是怎么得到執(zhí)行計劃的？
首先我們要啟用優(yōu)化器的追蹤（默認是關(guān)閉的）

mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.00 sec)

mysql> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+-------------------------+
| Variable_name   | Value                   |
+-----------------+-------------------------+
| optimizer_trace | enabled=on,one_line=off |
+-----------------+-------------------------+
1 row in set (0.00 sec)

mysql>

注意開啟這開關(guān)是會消耗性能的，因為它要把優(yōu)化分析的結(jié)果寫到表里面，所以不要輕易開啟，或者查看完之后關(guān)閉它（改成 off）。
接著我們執(zhí)行一個 SQL 語句，優(yōu)化器會生成執(zhí)行計劃：
這個時候優(yōu)化器分析的過程已經(jīng)記錄到系統(tǒng)表里面了，我們可以查詢：

mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT * FROM `crawler_list`
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `crawler_list`.`id` AS `id`,`crawler_list`.`user_id` AS `user_id`,`crawler_list`.`title` AS `title`,`crawler_list`.`moment_type` AS `moment_type`,`crawler_list`.`status` AS `status` from `crawler_list`"
          }
        ]
      }

expanded_query 是優(yōu)化后的 SQL 語句。
considered_execution_plans 里面列出了所有的執(zhí)行計劃。
關(guān)閉優(yōu)化器的跟蹤

mysql> set optimizer_trace="enabled=off";
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.00 sec)

優(yōu)化器可以做什么？
MySQL 的優(yōu)化器能處理哪些優(yōu)化類型呢？
比如：
1、當我們對多張表進行關(guān)聯(lián)查詢的時候，以哪個表的數(shù)據(jù)作為基準表。
2、select * from user where a=1 and b=2 and c=3，如果 c=3 的結(jié)果有 100 條，b=2 的結(jié)果有200 條， a=1 的結(jié)果有 300 條，你覺得會先執(zhí)行哪個過濾？
3、如果條件里面存在一些恒等或者恒不等的等式，是不是可以移除。
4、查詢數(shù)據(jù)，是不是能直接從索引里面取到值。
5、count()、min()、max()，比如是不是能從索引里面直接取到值。
6、其他。
優(yōu)化器得到的結(jié)果
優(yōu)化器最終會把解析樹變成一個查詢執(zhí)行計劃，查詢執(zhí)行計劃是一個數(shù)據(jù)結(jié)構(gòu)。
當然，這個執(zhí)行計劃是不是一定是最優(yōu)的執(zhí)行計劃呢？不一定，因為 MySQL 也有可能覆蓋不到所
有的執(zhí)行計劃。
MySQL 提供了一個執(zhí)行計劃的工具。我們在 SQL 語句前面加上 EXPLAIN，就可以看到執(zhí)行計劃的信息。

5、存儲引擎（Storage Engine）

我們的數(shù)據(jù)是放在哪里的？執(zhí)行計劃在哪里執(zhí)行？是誰去執(zhí)行？
存儲引擎基本介紹
在關(guān)系型數(shù)據(jù)庫里面，數(shù)據(jù)是放在表里面的。我們可以把這個表理解成 Excel 電子表格的形式。所以我們的表在存儲數(shù)據(jù)的同時，還要組織數(shù)據(jù)的存儲結(jié)構(gòu)，這個存儲結(jié)構(gòu)就是由我們的存儲引擎決定
的，所以我們也可以把存儲引擎叫做表類型。
在 MySQL 里面，支持多種存儲引擎，他們是可以替換的，所以叫做插件式的存儲引擎。為什么要搞這么多存儲引擎呢？一種還不夠用嗎？是因為我們在不同的業(yè)務(wù)場景中對數(shù)據(jù)操作的要求不同，這些不同的存儲引擎通過提供不同的存儲機制、索引方式、鎖定水平等功能，來滿足我們的業(yè)務(wù)需求。

查看數(shù)據(jù)庫所有表
show table status from `mysql`;

image.png

在 MySQL 里面，我們創(chuàng)建的每一張表都可以指定它的存儲引擎，它不是一個數(shù)據(jù)庫只能使用一
個存儲引擎。而且，創(chuàng)建表之后還可以修改存儲引擎。
數(shù)據(jù)庫存放數(shù)據(jù)的路徑：

mysql> show variables like 'datadir';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| datadir       | /usr/local/mysql8/data/ |
+---------------+-------------------------+
1 row in set (0.00 sec)

每個數(shù)據(jù)庫有一個自己文件夾，以 trainning 數(shù)據(jù)庫為例。
任何一個存儲引擎都有一個 frm 文件，這個是表結(jié)構(gòu)定義文件。

image.png

我們在數(shù)據(jù)庫中建了三張表，使用了不同的存儲引擎。
不同的存儲引擎存放數(shù)據(jù)的方式不一樣，產(chǎn)生的文件也不一樣。

6、執(zhí)行引擎（Query Execution Engine），返回結(jié)果

執(zhí)行引擎，它利用存儲引擎提供了相應(yīng)的 API 來完成對存儲引擎的操作。最后把數(shù)據(jù)返回給客戶端，即
使沒有結(jié)果也要返回。

二、MySQL 體系結(jié)構(gòu)總結(jié)

架構(gòu)分層
總體上，我們可以把 MySQL 分成三層。
連接層（與客戶端對接）
服務(wù)層（執(zhí)行操作）
存儲引擎層（跟硬件打交道

image.png

模塊詳解

1.Connector：用來支持各種語言和 SQL 的交互，比如 PHP，Python，Java 的 JDBC
2.Management Serveices & Utilities：系統(tǒng)管理和控制工具，包括備份恢復、MySQL 復制、集群等等
3.Connection Pool：連接池，管理需要緩沖的資源，包括用戶密碼權(quán)限線程等等
4.SQL Interface：用來接收用戶的 SQL 命令，返回用戶需要的查詢結(jié)果
5.Parser：用來解析 SQL 語句
6.Optimizer：查詢優(yōu)化器
7.Cache and Buffer：查詢緩存，除了行記錄的緩存之外，還有表緩存，Key 緩存，權(quán)限緩存等等。
8.Pluggable Storage Engines：插件式存儲引擎，它提供 API 給服務(wù)層使用，跟具體的文件打交道。

三、一條更新SQL 是如何執(zhí)行的？

在數(shù)據(jù)庫里面，我們說的 update 操作其實包括了更新、插入和刪除。更新流程和查詢流程有什么不同呢？
基本流程也是一致的，也就是說，它也要經(jīng)過解析器、優(yōu)化器的處理，最后交給執(zhí)行器。
區(qū)別就在于拿到符合條件的數(shù)據(jù)之后的操作。
首先，在 InnoDB 里面有個內(nèi)存的緩沖池（buffer pool）。我們對數(shù)據(jù)的更新，不會每次都直接寫到磁盤上，因為 IO 的代價太大了，所以先寫入到 buffer pool 里面。內(nèi)存的數(shù)據(jù)頁和磁盤數(shù)據(jù)不一致的時候，我們把它叫做臟頁。
InnoDB 里面有專門的把 buffer pool 的數(shù)據(jù)寫入到磁盤的線程，每隔一段時間就一次性地把多個修改寫入磁盤，這個就叫做刷臟。

image.png

一條更新語句是怎樣執(zhí)行的