執行如下 SQL，我們看到的只是輸入一條語句，返回一個結果，卻不知道這條語句在 MySQL 內部的執行過程。

select * from where id ='1';

上圖給出的是 MySQL 的基本架構示意圖，從中你可以清楚地看到 SQL 語句在 MySQL 的各個功能模塊中的執行過程。大體來說，MySQL 可以分為 Server 層和存儲引擎層兩部分。

Server 層包括連接器、查詢緩存、分析器、優化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的內置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。

而存儲引擎層負責數據的存儲和提取。其架構模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認存儲引擎。也可以通過指定存儲引擎的類型來選擇別的引擎，比如在 create table 語句中使用 engine=memory, 來指定使用內存引擎創建表。

一條 SQL 查詢的完整執行流程如上圖所示。

Server 服務層

連接器

連接數據庫最開始肯定是連接器。連接器負責跟客戶端建立連接、獲取權限、維持和管理連接。連接命令一般是這么寫的：

mysql -h$ip -P$port -u$user -p

輸完命令之后，你就需要在交互對話里面輸入密碼。雖然密碼也可以直接跟在 -p 后面寫在命令行中，但這樣可能會導致你的密碼泄露。如果你連的是生產服務器，強烈建議你不要這么做。

連接命令中的 mysql 是客戶端工具，用來跟服務端建立連接。在完成經典的 TCP 握手后，連接器就要開始認證你的身份，這個時候用的就是你輸入的用戶名和密碼。

• 如果用戶名或密碼不對，你就會收到一個 "Access denied for user" 的錯誤，然后客戶端程序結束執行。
• 如果用戶名密碼認證通過，連接器會到權限表里面查出你擁有的權限。之后，這個連接里面的權限判斷邏輯，都將依賴于此時讀到的權限。

這就意味著，一個用戶成功建立連接后，即使你用管理員賬號對這個用戶的權限做了修改，也不會影響已經存在連接的權限。修改完成后，只有再新建的連接才會使用新的權限設置。

連接完成后，如果你沒有后續的動作，這個連接就處于空閑狀態，你可以在 show processlist 命令中看到它。文本中這個圖是 show processlist 的結果，其中的 Command 列顯示為 “Sleep” 的這一行，就表示現在系統里面有一個空閑連接。

客戶端如果太長時間沒動靜，連接器就會自動將它斷開。這個時間是由參數 wait_timeout 控制的，默認值是 8 小時。

如果在連接被斷開之后，客戶端再次發送請求的話，就會收到一個錯誤提醒： Lost connection to MySQL server during query。這時候如果你要繼續，就需要重連，然后再執行請求了。

數據庫里面，長連接是指連接成功后，如果客戶端持續有請求，則一直使用同一個連接。短連接則是指每次執行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個。

建立連接的過程通常是比較復雜的，所以我建議你在使用中要盡量減少建立連接的動作，也就是盡量使用長連接。但是全部使用長連接后，你可能會發現，有些時候 MySQL 占用內存漲得特別快，這是因為 MySQL 在執行過程中臨時使用的內存是管理在連接對象里面的。這些資源會在連接斷開的時候才釋放。所以如果長連接累積下來，可能導致內存占用太大，被系統強行殺掉（OOM），從現象看就是 MySQL 異常重啟了。

怎么解決這個問題呢？你可以考慮以下兩種方案。

• 定期斷開長連接。使用一段時間，或者程序里面判斷執行過一個占用內存的大查詢后，斷開連接，之后要查詢再重連。
• 如果你用的是 MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作后，通過執行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做權限驗證，但是會將連接恢復到剛剛創建完時的狀態。

查詢緩存

連接建立完成后，你就可以執行 select 語句了。執行邏輯就會來到第二步：查詢緩存。

MySQL 拿到一個查詢請求后，會先到查詢緩存看看，之前是不是執行過這條語句。之前執行過的語句及其結果可能會以 key-value 對的形式，被直接緩存在內存中。key 是查詢的語句，value 是查詢的結果。如果你的查詢能夠直接在這個緩存中找到 key，那么這個 value 就會被直接返回給客戶端。

如果語句不在查詢緩存中，就會繼續后面的執行階段。執行完成后，執行結果會被存入查詢緩存中。你可以看到，如果查詢命中緩存，MySQL 不需要執行后面的復雜操作，就可以直接返回結果，這個效率會很高。

但是大多數情況下我會建議你不要使用查詢緩存，為什么呢？因為查詢緩存往往弊大于利。

查詢緩存的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會被清空。因此很可能你費勁地把結果存起來，還沒使用呢，就被一個更新全清空了。對于更新壓力大的數據庫來說，查詢緩存的命中率會非常低。除非你的業務就是有一張靜態表，很長時間才會更新一次。比如，一個系統配置表，那這張表上的查詢才適合使用查詢緩存。

好在 MySQL 也提供了這種 “按需使用” 的方式。你可以將參數 query_cache_type 設置成 DEMAND，這樣對于默認的 SQL 語句都不使用查詢緩存。而對于你確定要使用查詢緩存的語句，可以用 SQL_CACHE 顯式指定，像下面這個語句一樣：

mysql> select SQL_CACHE * from T where ID=10；

需要注意的是，MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了，也就是說 8.0 開始徹底沒有這個功能了。

分析器

如果沒有命中查詢緩存，就要開始真正執行語句了。首先，MySQL 需要知道你要做什么，因此需要對 SQL 語句做解析。

分析器先會做 “詞法分析”。你輸入的是由多個字符串和空格組成的一條 SQL 語句，MySQL 需要識別出里面的字符串分別是什么，代表什么。

MySQL 從你輸入的 "select" 這個關鍵字識別出來，這是一個查詢語句。它也要把字符串 “T” 識別成 “表名 T”，把字符串 “ID” 識別成 “列 ID”。

做完了這些識別以后，就要做 “語法分析”。根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。

如果你的語句不對，就會收到 “You have an error in your SQL syntax” 的錯誤提醒，比如下面這個語句 select 少打了開頭的字母 “s”。

mysql> elect * from t where ID=1;
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 where ID=1' at line 1

一般語法錯誤會提示第一個出現錯誤的位置，所以你要關注的是緊接 “use near” 的內容。

優化器

經過了分析器，MySQL 就知道你要做什么了。在開始執行之前，還要先經過優化器的處理。

優化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序。通常兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇使用哪一個方案。優化器階段完成后，這個語句的執行方案就確定下來了，然后進入執行器階段。

執行 SQL 查詢的時候優化器主要執行如下任務：

• 選擇最合適的索引；
• 選擇表掃還是走索引；
• 選擇表關聯順序；
• 優化 where 子句；
• 排除管理中無用表；
• 決定 order by 和 group by 是否走索引；
• 嘗試使用 inner join 替換 outer join；
• 簡化子查詢，決定結果緩存；

MySQL 查詢優化器有幾個目標，但是其中最主要的目標是盡可能地使用索引，并且使用最嚴格的索引來消除盡可能多的數據行。

優化器試圖排除數據行的原因在于它排除數據行的速度越快，那么找到與條件匹配的數據行也就越快。如果能夠首先進行最嚴格的測試，查詢就可以執行地更快。

執行器

MySQL 通過分析器知道了你要做什么，通過優化器知道了該怎么做，于是就進入了執行器階段，開始執行語句。

開始執行的時候，要先判斷一下你對這個表 T 有沒有執行查詢的權限，如果沒有，就會返回沒有權限的錯誤，如下所示 (在工程實現上，如果命中查詢緩存，會在查詢緩存返回結果的時候，做權限驗證。查詢也會在優化器之前調用 precheck 驗證權限)。

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有權限，就打開表繼續執行。打開表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的接口。

比如我們這個例子中的表 T 中，ID 字段沒有索引，那么執行器的執行流程是這樣的：

• 調用 InnoDB 引擎接口取這個表的第一行，判斷 ID 值是不是 10，如果不是則跳過，如果是則將這行存在結果集中；
• 調用引擎接口取 “下一行”，重復相同的判斷邏輯，直到取到這個表的最后一行。
• 執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

至此，這個語句就執行完成了。

對于有索引的表，執行的邏輯也差不多。第一次調用的是 “取滿足條件的第一行” 這個接口，之后循環取 “滿足條件的下一行” 這個接口，這些接口都是引擎中已經定義好的。

你會在數據庫的慢查詢日志中看到一個 rows_examined 的字段，表示這個語句執行過程中掃描了多少行。這個值就是在執行器每次調用引擎獲取數據行的時候累加的。

在有些場景下，執行器調用一次，在引擎內部則掃描了多行，因此引擎掃描行數跟 rows_examined 并不是完全相同的。

存儲引擎

通過 show engines; 查看引擎類型，可以看出來要用到事務只能用 InnoDB 引擎類型。

InnoDB 存儲引擎

InnoDB 是 MySQL 的默認事務型引擎，也是最重要、使用最廣泛的存儲引擎，并且有行級鎖定和外鍵約束。它被設計用來處理大量的短期 (short-lived) 事務，短期事務大部分情況是正常提交，很少會被回滾。InnoDB 的性能和自動崩潰恢復特性，使得它在非事務型存儲的需求中也很流行。除非有非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮 InnoDB 引擎。

InnoDB 的適用場景 / 特性，有以下幾種：

• 經常更新的表，適合處理多重并發的更新請求。
• 支持事務。
• 可以從災難中恢復（通過 bin-log 日志等）。
• 外鍵約束。只有他支持外鍵。
• 支持自動增加列屬性 auto_increment。

MyISAM 存儲引擎

MyISAM 提供了大量的特性，包括全文檢索、壓縮等，但不支持事務和行級鎖，支持表級鎖。 對于只讀的數據，或者表較小、可以忍受修復操作的場景，依然可以使用 MyISAM。

MyISAM 的適用場景 / 特性，有以下幾種：

• 不支持事務的設計，但是并不代表著有事務操作的項目不能用 MyISAM 存儲引擎，完全可以在程序層進行根據自己的業務需求進行相應的控制。
• 不支持外鍵的表設計。
• 查詢速度很快，如果數據庫 insert 和 update 的操作比較多的話比較適用。
• 整天 對表進行加鎖的場景。
• MyISAM 極度強調快速讀取操作。
• MyIASM 中存儲了表的行數，于是 SELECT COUNT (*) FROM TABLE 時只需要直接讀取已經保存好的值而不需要進行全表掃描。如果表的讀操作遠遠多于寫操作且不需要數據庫事務的支持，那么 MyIASM 也是很好的選擇。

MySQL 內建的其他存儲引擎

MySQL 還有一些特殊用途的存儲引擎，在一些特殊場景下用起來會很爽的。在 MySQL 新版本中，有些可能因為一些原因已經不再支持了，還有一些會繼續支持，但是需要明確地啟用后才能使用。

Archive 存儲引擎

Archive 引擎只支持 insert 和 select 操作，并且在 MySQL 5.1 之前連索引都不支持。

Archive 引擎會緩存所有的寫并利用 zlib 對插入的行進行壓縮，所以比 MyISAM 引擎的磁盤 I/O 更少。但是每次 select 查詢都需要進行全表掃描，所以 Archive 更適合日志和數據采集類應用，況且這類應用在做數據分析時往往需要全表掃描。

Archive 引擎支持行級鎖和專用的緩沖區，所以可以實現高并發的插入。在一個查詢開始直到返回表中存在的所有行之前，Archive 引擎會阻止其他的 select 執行，以實現一致性讀。另外，這也實現了批量插入在完成之前對讀操作是不看見的。

Blackhole 存儲引擎

Blackhole 引擎沒有實現任何的存儲機制，它會丟失所有插入的數據，不做任何保存。怪哉，豈不是一無用處？但是服務器會記錄 Blackhole 的日志，所以可以用于復制數據到備庫，或者只是簡單地記錄到日志。這種特殊的存儲引擎可以在一些特殊的復制架構和日志審核時發揮作用。

但這種存儲引擎的存在，至今還是有些難以理解。

CSV 存儲引擎

CSV 引擎可以將普通的 CSV 文件作為 MySQL 的表來處理，但這種表不支持索引。

CSV 引擎可以在數據庫運行時拷入或者拷出文件，可以將 Excel 等電子表格軟件中的數據存儲為 CSV 文件，然后復制到 MySQL 數據目錄下，就能在 MySQL 中打開使用。同樣，如果將數據寫入到一個 CSV 引擎表中，其他的外部程序也能立即從表的數據文件中讀取 CSV 格式的數據。

因此，CSV 引擎可以作為一種數據交換的機制，是非常有用的。

Memory 存儲引擎

如果需要快速地訪問數據，并且這些數據不會被修改，重啟以后丟失也沒有關系，那么使用 Memory 引擎是非常有用的。Memory 引擎至少比 MyISAM 引擎要快一個數量級，因為所有的數據都保存在內存中，不需要進行磁盤 I/O。Memory 引擎的表結構在重啟以后還會保留，但數據會丟失。

Memory 引擎在很多場景下可以發揮很好的作用：

• 用于查找或者映射表，例如將郵箱和州名映射的表。
• 用于緩存周期性聚合數據的結果。
• 用于保存數據分析中產生的中間數據。

Memory 引擎支持 Hash 索引，因此查找非常快。雖然 Memory 的速度非常快，但還是無法取代傳統的基于磁盤的表。Memory 引擎是表級鎖，因此并發吸入的性能較低。

如果 MySQL 在執行查詢的過程中，需要使用臨時表來保存中間結果，內部使用的臨時表就是 Memory 引擎。如果中間結果太大超出了 Memory 的限制，或者含有 BLOB 或 TEXT 字段，則臨時表會轉換成 MyISAM 的引擎。

看了上面的說明，大家就會經常混淆 Memory 和臨時表了。臨時表是指使用 CREATE TEMPORARY TABLE 語句創建的表，它可以使用任何存儲引擎，因此和 Memory 不是一回事。臨時表只在單個連接中可見，當連接斷開時，臨時表也將不復存在。

關于臨時表和 Memory 引擎的那些事，可參考 MySQL?引擎特性?臨時表那些事兒。

MySQL 的存儲引擎及第三方存儲引擎，還有很多，在此就不一一介紹了，后續如有需要，再進一步來談談。

如何選擇合適的存儲引擎呢

這么多存儲引擎，真是眼花繚亂，我們該如何選擇呢？

大部分情況下，都會選擇默認的存儲引擎 ——InnoDB，并且這也是最正確的選擇，所以 Oracle 在 MySQL 5.5 版本時終于將 InnoDB 作為默認的存儲引擎了。

對于如何選擇合適的存儲引擎，可以簡單地歸納為一句話：” 除非需要用到某些 InnoDB 不具備的特性，并且沒有其他可以替代，否則都應該優先選擇 InnoDB 引擎”。

例如，如果要用到全文檢索，建議優先考慮 InnoDB 加上 Sphinx 的組合，而不是使用支持全文檢索的 MyISAM。當然，如果不需要用到 InnoDB 的特性，同時其他引擎的特性能夠更好地滿足需求，就可以考慮一下其他存儲引擎。

除非萬不得已，建議不要混合使用多種存儲引擎，否則可能帶來一系列復雜的問題，以及一些潛在的 bug 和邊界問題。

如果需要使用不同的存儲引擎，建議考慮從以下幾個因素進行衡量考慮。

• 事務
• 備份
• 恢復
• 特有的特性

其他查找引擎 SQL

通過下面的命令查看默認的存儲引擎。

mysql> show variables like '%storage_engine%';
--查看表的存儲引擎
show table status like "table_name" ;