一、一條查詢SQL是如何執行的？
程序或者工具要操作數據庫，第一步是跟數據庫建立連接。
1、通信協議
首先，MySQL 必須要運行一個服務，監聽默認的端口（3306）。
通信協議
MySQL 支持多種通信協議。
第一個就是 TCP/IP 協議，編程語言的連接模塊都是用 TCP 協議連接到 MySQL 服務器的，比如
mysql-connector-JAVA-x.x.xx.jar。
第二種是 Unix Socket。比如我們在 linux 服務器，不用通過網絡協議，也可以連接到 MySQL 地服服務器，它需要用到服務器上的一個物理文件（mysql.sock）。
mysql -uroot -p123456
show variables like 'socket';
另外還有命名管道（Named Pipes）和內存共享（Share Memory）的方式。
通信方式
第二個是通信方式。
MySQL 使用半雙工的通信方式。
半雙工意味著要么是客戶端向服務端發送數據，要么是服務端向客戶端發送數據，這兩個動作不能同時發生。
所以客戶端發送 SQL 語句給服務端的時候，（在一次連接里面）數據是不能分成小塊發送的，不管你的 SQL 語句有多大，都是一次性發送。
如果發送給服務器的數據包過大，我們必須要調整 MySQL 服務器配置 max_allowed_packet 參數的值（默認是 4M）。
另一方面，對于服務端來說，也是一次性發送所有的數據，不能因為你已經取到了想要的數據就中斷操作。
所以，我們一定要在程序里面避免不帶 limit 的這種操作。
連接方式
第三個就是連接這一塊。
MySQL 既支持短連接，也支持長連接。短連接就是操作完畢以后，馬上 close 掉。長連接可以保持打開，后面的程序訪問的時候還可以使用這個連接。
長時間不活動的連接，MySQL 服務器會斷開。
show global variables like 'wait_timeout'; （非交互式超時時間，如 JDBC 程序）
show global variables like 'interactive_timeout'; （交互式超時時間，如數據庫工具）
默認是 28800 秒，8 小時。
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_interactive_timeout
MySQL 默認的最大連接數是 151 個（5.7 版本），最大是 16384（2^14）。
show variables like 'max_connections';
查看 3306 端口當前連接數
使用 SHOW FULL PROCESSLIST;查看查詢的執行狀態。
一些常見的狀態：
2、查詢緩存(Query Cache)
MySQL 內部自帶了一個緩存模塊。默認是關閉的。主要是因為 MySQL 自帶的緩存的應用場景有限，第一個是它要求 SQL 語句必須一模一樣。第二個是表里面任何一條數據發生變化的時候，這張表的所有緩存都會失效。
在 MySQL 5.8 中，查詢緩存已經被移除了。
3、語法解析和預處理(Parser & Preprocessor)
下一步我們要做什么呢？
假如隨便執行一個字符串 fkdljasklf ，服務器報了一個 1064 的錯：
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to yourMySQL server version for the right syntax to use near 'fkdljasklf' at line 1
服務器是怎么知道我輸入的內容是錯誤的？
或者，當我輸入了一個語法完全正確的 SQL，但是表名不存在，它是怎么發現的？
這個就是 MySQL 的 Parser 解析器和 Preprocessor 預處理模塊。
這一步主要做的事情是對 SQL 語句進行詞法和語法分析和語義的解析。
詞法解析
詞法分析就是把一個完整的 SQL 語句打碎成一個個的單詞。
比如一個簡單的 SQL 語句：
select name from user where id = 1;
它會打碎成 8 個符號，記錄每個符號是什么類型，從哪里開始到哪里結束。
語法解析
第二步就是語法分析，語法分析會對 SQL 做一些語法檢查，比如單引號有沒有閉合，然后根據 MySQL定義的語法規則，根據 SQL 語句生成一個數據結構。這個數據結構我們把它叫做解析樹。
預處理器（Preprocessor）
如果表明錯誤，會在預處理器處理時報錯。
它會檢查生成的解析樹，解決解析器無法解析的語義。比如，它會檢查表和列名是否存在，檢查名字和別名，保證沒有歧義。
4、查詢優化（Query Optimizer）與查詢執行計劃
什么優化器？
問題：一條 SQL 語句是不是只有一種執行方式？或者說數據庫最終執行的 SQL 是不是就是我們發送的 SQL？
這個答案是否定的。一條 SQL 語句是可以有很多種執行方式的。但是如果有這么多種執行方式，這些執行方式怎么得到的？最終選擇哪一種去執行？根據什么判斷標準去選擇？這個就是 MySQL 的查詢優化器的模塊（Optimizer）。
查詢優化器的目的就是根據解析樹生成不同的執行計劃，然后選擇一種最優的執行計劃，MySQL 里面使用的是基于開銷（cost）的優化器，那種執行計劃開銷最小，就用哪種。
使用如下命令查看查詢的開銷：
show status like 'Last_query_cost';
--代表需要隨機讀取幾個 4K 的數據頁才能完成查找。
如果我們想知道優化器是怎么工作的，它生成了幾種執行計劃，每種執行計劃的 cost 是多少，應該怎么做？
優化器是怎么得到執行計劃的？
https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
首先我們要啟用優化器的追蹤（默認是關閉的）：
SHOW VARIABLES LIKE 'optimizer_trace';
set optimizer_trace="enabled=on";
注意開啟這開關是會消耗性能的，因為它要把優化分析的結果寫到表里面，所以不要輕易開啟，或者查看完之后關閉它（改成 off）。
接著我們執行一個任務 SQL 語句，優化器會生成執行計劃：
select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
這個時候優化器分析的過程已經記錄到系統表里面了，我們可以查詢：
select * from information_schema.optimizer_traceG
expanded_query 是優化后的 SQL 語句。
considered_execution_plans 里面列出了所有的執行計劃。
記得關掉它：
set optimizer_trace="enabled=off";
• SHOW VARIABLES LIKE 'optimizer_trace';
優化器可以做什么？
MySQL 的優化器能處理哪些優化類型呢？
比如：
1、當我們對多張表進行關聯查詢的時候，以哪個表的數據作為基準表。
2、select * from user where a=1 and b=2 and c=3，如果 c=3 的結果有 100 條，b=2 的結果有200 條， a=1 的結果有 300 條，你覺得會先執行哪個過濾？
3、如果條件里面存在一些恒等或者恒不等的等式，是不是可以移除。
4、查詢數據，是不是能直接從索引里面取到值。
5、count()、min()、max()，比如是不是能從索引里面直接取到值。
6、其他。
優化器得到的結果
優化器最終會把解析樹變成一個查詢執行計劃，查詢執行計劃是一個數據結構。
當然，這個執行計劃是不是一定是最優的執行計劃呢？不一定，因為 MySQL 也有可能覆蓋不到所有的執行計劃。
MySQL 提供了一個執行計劃的工具。我們在 SQL 語句前面加上 EXPLAIN，就可以看到執行計劃的信息。
EXPLAIN select name from user where id=1;
5、存儲引擎（Storage Engine）
我們的數據是放在哪里的？執行計劃在哪里執行？是誰去執行？
存儲引擎基本介紹
在關系型數據庫里面，數據是放在表里面的。我們可以把這個表理解成 Excel 電子表格的形式。所以我們的表在存儲數據的同時，還要組織數據的存儲結構，這個存儲結構就是由我們的存儲引擎決定的，所以我們也可以把存儲引擎叫做表類型。
在 MySQL 里面，支持多種存儲引擎，他們是可以替換的，所以叫做插件式的存儲引擎。為什么要搞這么多存儲引擎呢？一種還不夠用嗎？是因為我們在不同的業務場景中對數據操作的要求不同，這些不同的存儲引擎通過提供不同的存儲機制、索引方式、鎖定水平等功能，來滿足我們的業務需求。
查看存儲引擎
查看數據庫表的存儲引擎：
show table status from `training`;
在 MySQL 里面，我們創建的每一張表都可以指定它的存儲引擎，它不是一個數據庫只能使用一個存儲引擎。而且，創建表之后還可以修改存儲引擎。
數據庫存放數據的路徑：
show variables like 'datadir';
每個數據庫有一個自己的文件夾，以 trainning 數據庫為例。
任何一個存儲引擎都有一個問題 frm 文件，這個是表結構的定義文件。
我們在數據庫中建了三張表，使用了不同的存儲引擎。
不同的存儲引擎存放數據的方式不一樣，產生的文件也不一樣。
存儲引擎比較
常見存儲引擎
在 MySQL 5.5 版本之前，默認的存儲引擎是 MyISAM，它是 MySQL 自帶的。5.5 版本之后默認的存儲引擎改成了InnoDB，它是第三方公司為MySQL開發的。為什么要改呢？最主要的原因還是InnoDB支持事務，支持行級別的鎖，對于業務一致性要求高的場景來說更適合。
數據庫支持的存儲引擎
我們可以用這個命令查看數據庫對存儲引擎的支持情況：
SHOW ENGINES ;
其中有存儲引擎的描述和對事務、XA 協議和 Savepoints 的支持。
官網對于存儲引擎的介紹：
https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
MyISAM（3個文件）
These tables have a small footprint. Table-level locking limits the performance in read/writeworkloads, so it is often used in read-only or read-mostly workloads in Web and datawarehousing configurations.
應用范圍比較小。表級鎖定限制了讀/寫的性能，因此在 Web 和數據倉庫配置中，它通常用于只讀或譯讀為主的工作。
- 支持表級別的鎖（插入和更新鎖表）。不支持事務。
- 擁有較高的插入（insert）和查詢（select）速度。
- 存儲了表的行數（count 速度更快）。
- 適合：只讀之類的數據分析的項目。
InnoDB（2 個文件）
The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant)storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protectuser data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDBstores user data in clustered indexes to reduce I/O for common queries based on primary keys.To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
mysql 5.7 中的默認存儲引擎。InnoDB 是一個事務安全（與 ACID 兼容）的 MySQL 存儲引擎，它具有提交、回滾和崩潰恢復功能來保護用戶數據。InnoDB 行級鎖（不升級為更粗粒度的鎖）和 Oracle風格的一致非鎖讀提高了多用戶并發性和性能。InnoDB 將用戶數據存儲在聚集索引中，以減少基于 主鍵的常見查詢的 I/O。為了保持數據完整性，InnoDB 還支持外鍵引用完整性約束。
特點：
- 支持事務，支持外鍵，因此數據的完整性、一致性更高。
- 支持行級別的鎖和表級別的鎖。
- 支持讀寫并發，寫不阻塞速度。
- 特殊的索引存放方式，可以減少 IO，提升查詢效率。
- 適合：經常更新的表，存在并發讀寫或者有事務處理的業務系統。
Memory（1個文件）
Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing;InnoDB with its buffer pool memory area provides a general-purpose and durable way to keepmost or all data in memory, and NDBCLUSTER provides fast key-value lookups for hugedistributed data sets.
將所有數據存儲在 RAM 中，以便在需要快速查找非關鍵數據的環境中快速訪問。這個引擎以前被稱為堆引擎。其使用案例正在減少；InnoDB 及其緩沖池內存區域提供了一種通用、持久的方法來 將大部分或所有數據保存在內存中，而 ndbcluster 為大型分布式數據集提供了快速的鍵值查找。
特點：
把數據放在內存里面，讀寫的速度很快，但是數據庫重啟或者崩潰，數據會全部消失。只適合 做臨時表。默認使用哈希索引。 將表中的數據存儲到內存中。
CSV（3個文件）
Its tables are really text files with comma-separated values. CSV tables let you import or dumpdata in CSV format, to exchange data with scripts and Applications that read and write that sameformat. Because CSV tables are not indexed, you typically keep the data in InnoDB tables duringnormal operation, and only use CSV tables during the import or export stage.
它的表實際上是帶有逗號分隔值的文本文件。csv 表允許以 csv 格式導入或轉儲數據，以便與讀寫相同格式的腳本和應用程序交換數據。因為 csv 表沒有索引，所以通常在正常操作期間將數據保存在 innodb表中，并且只在導入或導出階段使用 csv 表。
特點：
不允許空行，不支持索引。格式通用，可以直接編輯，適合在不同數據庫之間導入導出。
Archive（2 個文件）
These compact, unindexed tables are intended for storing and retrieving large amounts ofseldom-referenced historical, archived, or security audit information.
這些緊湊的未索引表用于存儲和檢索大量很少引用的歷史、存檔或安全審計信息。
特點：
不支持索引，不支持 update delete。
6、執行引擎（Query Execution Engine），返回結果
執行引擎，它利用存儲引擎提供了相應的 API 完成對存儲引擎的操作。最后把數據返回給客戶端，即使沒有結果也要返回。
二、MySQL 體系結構總結
架構分層
總體上，我們可以把握 MySQL 分成三層。
模塊詳解
- Connector：用來支持各種語言和 SQL 的交互，比如 php，Python/ target=_blank class=infotextkey>Python，Java 的 JDBC
- Management Serveices & Utilities：系統管理和控制工具，包括備份恢復、MySQL 復制、集群等等
- Connection Pool：連接池，管理需要緩沖的資源，包括用戶密碼權限線程等等
- SQL Interface：用來接收用戶的 SQL 命令，返回用戶需要的查詢結果
- Parser：用來解析 SQL 語句
- Optimizer：查詢優化器
- Cache and Buffer：查詢緩存，除了行記錄的緩存之外，還有表緩存，Key 緩存，權限緩存等等。
- Pluggable Storage Engines：插件式存儲引擎，它提供 API 給服務層使用，跟具體的文件打交道。
三、一條更新SQL 是如何執行的？
在數據庫里面，我們說的是 update 操作其實包括了更新、插入和刪除。更新流程和查詢流程有什么不同呢？
基本流程也是一致的，也就是說，它也要經過解析器、優化器的處理，最后交給執行器。區別就在于拿到符合條件的數據之后的操作。
首先，在 InnoDB 里面有個內存的緩沖池（buffer pool）。我們對數據的更新，不會每次都直接寫到磁盤上，因為 IO 的代價太大了，所以先寫入到 buffer pool 里面。內存的數據頁和磁盤數據是不一致的時候，我們把它叫做臟頁。
InnoDB 里面有專門的把 buffer pool 的數據寫入到磁盤的線程，每隔一段時間就一次性地把多個修改寫入磁盤，這個就叫做刷臟。
這里面就有一個問題，如果在網頁還沒有寫入磁盤的時候，服務器出問題了，內存里面的數據丟失了。或者是刷臟刷到一半，甚至會破壞數據文件。所以我們必須要有一個持久化的機制。
redo log
InnoDB 引入了一個日志文件，叫做 redo log（重做日志），我們把所有對內存數據的修改操作寫完入日志文件，如果服務器出問題了，我們就從這個日志文件里面讀取數據，恢復數據——用它來實現事務的持久性。
redo log 有什么特點？
- 記錄修改后的值，屬于物理日志
- redo log 的大小是固定的，前面的內容會被覆蓋，所以不能用于數據回滾/數據恢復。
- redo log 是 InnoDB 存儲引擎實現的，并不是所有存儲引擎都有。
binlog
MySQL Server 層也有一個日志文件，叫做 binlog，它可以被所有的存儲引擎使用。binlog 以事件的形式記錄了所有的 DDL 和 DML 語句（因為它記錄的是操作而不是數據值，屬于邏輯日志），可以用來做主從復制和數據恢復。
主從復制
數據恢復
跟 redo log 不一樣，它的文件內容是可以追加的，沒有固定大小限制。
有了這兩個日志之后，我們來看一下一條更新語句是怎么執行的：
例如一條語句：update teacher set name='jim' where name =‘666’
- 先查詢到這條數據，如果有緩存，也會用到緩存。
- 把 name 改成jim，然后調用引擎的 API 接口，寫入這一行數據到內存，同時記錄 redo log。這時 redo log 進入 prepare 狀態，然后告訴執行器，執行完成了，就可以隨時提交。
- 執行器收到通知后記錄 binlog，然后調用存儲引擎接口，設置 redo log 為 commit 狀態。
- 更新完成。
問題：為什么要用兩階段提交（XA）呢？
舉例：
如果我們執行的是把 name 改成jim，如果寫完 redo log，還沒有寫 bin log 的時候，MySQL 重啟了。
因為 redo log 可以恢復數據，所以寫入磁盤的是jim。但是 bin log 里面沒有記錄這個邏輯日志，所以以這時候用 binlog 去恢復數據或者同步到從庫，就會出現數據不一致的情況。所以在寫兩個日志的情況下，binlog 就充當了一個事務的協調者。通知 InnoDB 來執行 prepare 或commit 或者 rollback。
簡單地來說，這里有兩個寫日志的操作，類似于分布式事務，不用兩階段提交，就不能保證都成功或者都失敗。
