大家或多或少都用過 MySQL许布，起碼 select 還是會(huì)用的吧兴革，但是 select 執(zhí)行后，MySQL 內(nèi)部到底發(fā)生了什么蜜唾，你知道嗎杂曲？
比如，我們有個(gè)簡單的表 T袁余，它有個(gè) ID 字段擎勘，那么我們可以執(zhí)行下面的語句：
mysql> select * from T where ID=10;
語句執(zhí)行很簡單，但是具體到 MySQL 內(nèi)部颖榜，其實(shí)是一個(gè)完整的執(zhí)行流程棚饵。
MySQL 的基本架構(gòu)
從該圖可以看出，MySQL 主要分為 server 層和存儲引擎層掩完。
- server 層中包含連接器噪漾，查詢緩存，分析器且蓬，優(yōu)化器欣硼，執(zhí)行器，大多數(shù)核心功能以及內(nèi)置函數(shù)恶阴，存儲過程诈胜，觸發(fā)器，視圖等存淫。
- 存儲引擎層主要負(fù)責(zé)最終數(shù)據(jù)的存儲和提取耘斩，例如常用的存儲引擎 InnoDB、MyISAM 等桅咆。
好了括授，下面開始梳理一次完整的查詢流程。
若下面學(xué)習(xí)過程中懵逼了岩饼，建議隨時(shí)回來看這兩張圖荚虚！
1 連接
首先通過連接器連接到數(shù)據(jù)庫。
連接器的主要作用是建立連接籍茧，獲取用戶權(quán)限版述，維持連接，管理連接寞冯。
連接的一般命令就是我們常用的登陸數(shù)據(jù)庫的命令：
mysql -u$username -h$host -p$port -P
命令執(zhí)行后渴析，若用戶名或者密碼不對晚伙，或者數(shù)據(jù)庫做了登錄 ip 限制，都會(huì)收到異常信息俭茧。
若登陸成功咆疗，那么就代表連接成功建立。
之后連接器會(huì)維持當(dāng)前連接母债，接下來連接器會(huì)查詢出該用戶的權(quán)限午磁，后面所有的操作都會(huì)基于該權(quán)限，即使操作過程中有其他進(jìn)程修改了該用戶的權(quán)限毡们。
連接完成后迅皇，若沒有任何操作，連接就處于休眠狀態(tài)衙熔，用命令 show processlist;查看登颓，就是 Sleep 狀態(tài)的進(jìn)程：
當(dāng)然，連接器不會(huì)讓你一直握著連接不動(dòng)青责，若休眠時(shí)間超過 wait_timeout（默認(rèn)為 8 小時(shí)）挺据，則會(huì)斷開當(dāng)前連接取具。
若要再用脖隶，對不起，請重新連接~
長連接和短連接
其實(shí)這里的長短連接不是 MySQL 層面的概念暇检。
- 長連接：長連接是相對于短連接來說的产阱。長連接指在一個(gè)連接上可以連續(xù)發(fā)送多個(gè)數(shù)據(jù)包，在連接保持期間块仆，如果沒有數(shù)據(jù)包發(fā)送构蹬，需要雙方發(fā)鏈路檢測包。我理解 MySQL 默認(rèn)的超時(shí)時(shí)間 8 小時(shí)悔据，就屬于一個(gè)長鏈接庄敛。
客戶端連接--創(chuàng)建 socket 認(rèn)證連接--維護(hù)連接--數(shù)據(jù)傳輸--維護(hù)連接--數(shù)據(jù)傳輸.....-關(guān)閉連接
- 短連接：是指通訊雙方有數(shù)據(jù)交互時(shí)，就建立一個(gè)連接科汗，數(shù)據(jù)發(fā)送完成后藻烤，則斷開此連接，即每次連接只完成一項(xiàng)業(yè)務(wù)的發(fā)送头滔。
客戶端連接--創(chuàng)建 socket 認(rèn)證連接--維護(hù)連接--數(shù)據(jù)傳輸--關(guān)閉連接
長連接主要用于在少量客戶端與服務(wù)端的頻繁通信怖亭，因?yàn)檫@時(shí)候如果用短連接頻繁通信常會(huì)發(fā)生 Socket 出錯(cuò)，并且頻繁創(chuàng)建 Socket 連接也是對資源的浪費(fèi)坤检。
專欄中老師是建議使用長鏈接的兴猩，因?yàn)榻⑦B接的過程比較復(fù)雜，應(yīng)該盡量減少建立連接的動(dòng)作早歇。
長連接的管理
使用長連接后倾芝，隨著連接數(shù)不斷增加讨勤，會(huì)導(dǎo)致內(nèi)存占用升高，因?yàn)?MySQL 在操作過程中會(huì)占用內(nèi)存來管理連接對象晨另，只有等到連接斷開后才會(huì)釋放悬襟。
如果連接一直堆積，就會(huì)導(dǎo)致內(nèi)存占用過大拯刁，被系統(tǒng)強(qiáng)行殺掉脊岳，也就是會(huì)出現(xiàn) MySQL 重啟。
如何解決這個(gè)問題垛玻？
1割捅、定期斷開長連接；
2帚桩、MySQL 5.7+ 的版本中提供了 mysql_reset_connection 來重新初始化連接資源亿驾，這時(shí)不需要重新連接，就可以將連接恢復(fù)到剛剛創(chuàng)建完時(shí)的狀態(tài)账嚎；
- mysql_reset_connection
對于 mysql_reset_connection 莫瞬，MySQL 官網(wǎng)的描述是這樣的：
將連接重置，清空連接狀態(tài)郭蕉。
類似于重新連接疼邀，但是不會(huì)關(guān)閉當(dāng)前連接，也不會(huì)進(jìn)行重新鑒權(quán)召锈。
會(huì)產(chǎn)生如下影響：
1旁振、會(huì)回滾所有活動(dòng)事務(wù)，并重置自動(dòng)提交模式涨岁；
2拐袜、會(huì)釋放所有的鎖表；
3梢薪、所有的臨時(shí)表會(huì)被關(guān)閉并清除蹬铺；
4、Session 系統(tǒng)變量會(huì)被重新初始化為相應(yīng)的全局系統(tǒng)變量的值秉撇；
5甜攀、用戶自定義變量會(huì)丟失；
6畜疾、會(huì)釋放 Prepared statements赴邻；
7、HANDLER 變量會(huì)被關(guān)閉啡捶；
8姥敛、LAST_INSERT_ID() 函數(shù)的值會(huì)被重置為 0；
9瞎暑、通過 GET_LOCK() 函數(shù)獲得的鎖會(huì)被釋放彤敛；
以上影響与帆，翻譯自官方文檔，有些可能不太準(zhǔn)確墨榄，有興趣的可以到官網(wǎng)自行查閱原文玄糟。
- 數(shù)據(jù)庫連接池？
另外袄秩，不少實(shí)際的應(yīng)用框架中阵翎，大都使用連接池來維護(hù)連接數(shù)。
數(shù)據(jù)庫連接池之剧，就是服務(wù)器應(yīng)用建立多個(gè)連接到數(shù)據(jù)庫郭卫，還沒有用的連接就放到連接池上，要的時(shí)候就向連接池取背稼，這樣比沒有連接時(shí)再建立新的連接（TCP 建立連接是需要時(shí)間的）時(shí)要快很多贰军，從而提高傳輸效率。
如 Spring 框架中蟹肘，它實(shí)現(xiàn)了一個(gè)持久連接池词疼，允許其他程序、客戶端來連接帘腹，這個(gè)連接池將被所有連接的客戶端共享使用贰盗，連接池可以加速連接，也可以減少數(shù)據(jù)庫連接竹椒，降低數(shù)據(jù)庫服務(wù)器的負(fù)載童太。
2 查詢緩存
緩存米辐，就是提前預(yù)備好的數(shù)據(jù)胸完，數(shù)據(jù)庫查詢緩存也是緩存的一種。
在解析一個(gè)查詢語句之前翘贮，如果查詢緩存是打開的赊窥，那么 MySQL 會(huì)優(yōu)先檢查這個(gè)查詢是否命中查詢緩存中的數(shù)據(jù)。
如果當(dāng)前的查詢恰好命中了查詢緩存狸页，那么在返回查詢結(jié)果之前 MySQL 會(huì)檢查一次用戶權(quán)限锨能。若權(quán)限沒有問題，MySQL 會(huì)跳過所有其他階段（解析芍耘、優(yōu)化址遇、執(zhí)行等），直接從緩存中拿到結(jié)果并返回給客戶端斋竞。
這種情況下倔约，查詢不會(huì)被解析，不用生成執(zhí)行計(jì)劃坝初，不會(huì)被執(zhí)行浸剩。
緩存哪里來的钾军？
查詢時(shí)如果沒有命中查詢緩存，MYSQL 會(huì)判斷該查詢是否可以被緩存绢要，而且系統(tǒng)中還沒有對應(yīng)的緩存吏恭，則會(huì)將其結(jié)果寫入查詢緩存。
mysql query cache 的內(nèi)容為 select 的結(jié)果集重罪，在內(nèi)存中是以 HASH 結(jié)構(gòu)來進(jìn)行映射樱哼。
cache 會(huì)使用完整的 sql 字符串做 key，并區(qū)分大小寫剿配，空格等唇礁。即兩個(gè) sql 必須完全一致才會(huì)導(dǎo)致 cache 命中。
緩存何時(shí)失效惨篱？
在表的結(jié)構(gòu)或數(shù)據(jù)發(fā)生改變時(shí)盏筐，查詢緩存中的數(shù)據(jù)不再有效。
所以查詢緩存適合有大量相同查詢的應(yīng)用砸讳，不適合有大量數(shù)據(jù)更新的應(yīng)用琢融。
a) 一旦表數(shù)據(jù)進(jìn)行任何一行的修改，基于該表相關(guān) cache 立即全部失效簿寂，并且從緩沖區(qū)中移出漾抬；
b) 為什么不做聰明一點(diǎn)判斷修改的是否 cache 的內(nèi)容？因?yàn)榉治?cache 內(nèi)容太復(fù)雜常遂，服務(wù)器需要追求最大的性能纳令。
緩存可以提高查詢效率的？
當(dāng)有大量的查詢和大量的修改時(shí)克胳，cache 機(jī)制可能會(huì)造成性能下降平绩。
因?yàn)槊看涡薷臅?huì)導(dǎo)致系統(tǒng)去做 cache 失效操作，這就會(huì)造成不小的開銷漠另。
另外系統(tǒng) cache 的訪問由一個(gè)單一的全局鎖來控制捏雌，這時(shí)候大量的查詢將被阻塞，直至鎖釋放笆搓。
所以不要簡單認(rèn)為設(shè)置 cache 必定會(huì)帶來性能提升性湿。
參考：
https://www.cnblogs.com/duanxz/p/4385733.html
其實(shí)，在 8.0 版本開始满败，緩存功能被直接刪除肤频。
3 解析器
詞法解析
詞法分析的作用是將整個(gè)查詢分解為多個(gè)元素。
我們輸入的 MySQL 命令算墨，不過是一串長長的字符串宵荒，MySQL 的分析器會(huì)對其進(jìn)行詞法解析。
select * from T where ID=1;
比如，上述語句是由多個(gè)字符串和空格組成的一條 SQL 語句骇扇，MySQL 需要識別出里面的字符串分別是什么摔竿，代表什么。
MySQL 從你輸入的 select 這個(gè)關(guān)鍵字識別出來少孝，這是一個(gè)查詢語句继低。
它也要把字符串 T 識別成一個(gè)表名，把字符串 ID 識別成一個(gè)列稍走。
其實(shí)袁翁，大家也可以思考一下，若讓你手寫一個(gè)詞法分析的工具婿脸，你該如何實(shí)現(xiàn)呢粱胜？
語法分析
做完初步的詞法分析后，就要做語法分析狐树。
根據(jù)詞法分析的結(jié)果焙压，語法分析器會(huì)根據(jù)語法規(guī)則，判斷你輸入的這個(gè) SQL 語句是否滿足 MySQL 語法抑钟。
如果你的語句不對涯曲，就會(huì)收到 You have an error in your SQL syntax 的錯(cuò)誤提醒。
解析器的最終執(zhí)行結(jié)果就是解析樹在塔，提供給優(yōu)化器使用幻件。
4 優(yōu)化器
當(dāng)你提交一個(gè)查詢的時(shí)候，MySQL會(huì)分析它蛔溃，看是否可以做一些優(yōu)化使處理該查詢的速度更快绰沥。
優(yōu)化器到底干啥的？
MySQL 的優(yōu)化器有幾個(gè)重要任務(wù)：
1贺待、選擇最合適的索引徽曲；
2、選擇表掃還是走索引狠持；
3疟位、選擇表關(guān)聯(lián)順序；
4喘垂、優(yōu)化 where 子句；
5绍撞、排除管理中無用表正勒；
6、決定 order by 和 group by 是否走索引傻铣；
7章贞、嘗試使用 inner join 替換 outer join；
8非洲、簡化子查詢鸭限，決定結(jié)果緩存蜕径；
9、合并試圖败京；
MySQL 查詢優(yōu)化器有幾個(gè)目標(biāo)兜喻，但是其中最主要的目標(biāo)是盡可能地使用索引，并且使用最嚴(yán)格的索引來消除盡可能多的數(shù)據(jù)行赡麦。
優(yōu)化器試圖排除數(shù)據(jù)行的原因在于它排除數(shù)據(jù)行的速度越快朴皆，那么找到與條件匹配的數(shù)據(jù)行也就越快。如果能夠首先進(jìn)行最嚴(yán)格的測試泛粹，查詢就可以執(zhí)行地更快遂铡。
優(yōu)化器是如何工作的？
到底優(yōu)化器是如何進(jìn)行選擇的晶姊？如果每個(gè)點(diǎn)都展開扒接，那都需要很長的篇幅，我再網(wǎng)上翻閱了一些資料们衙，看得也是云里霧里珠增，后面結(jié)合專欄老師的講解再學(xué)習(xí)吧。
這里舉幾個(gè)優(yōu)化的示例：
- 示例 1
假設(shè)你的查詢檢驗(yàn)了兩個(gè)數(shù)據(jù)列砍艾，每個(gè)列上都有索引：
SELECT col3 FROM mytable WHERE col1 = 'value1' AND col2 = 'value2';
假設(shè) col1 上的測試匹配了 900 個(gè)數(shù)據(jù)行蒂教，col2 上的測試匹配了 300 個(gè)數(shù)據(jù)行，而同時(shí)進(jìn)行的測試只得到了 30 個(gè)數(shù)據(jù)行脆荷。
先測試 col1 會(huì)有 900 個(gè)數(shù)據(jù)行凝垛，需要檢查它們找到其中的 30 個(gè)與 col2 中的值匹配記錄，其中就有 870 次是失敗了蜓谋。
先測試 col2 會(huì)有 300 個(gè)數(shù)據(jù)行梦皮，需要檢查它們找到其中的 30 個(gè)與 col1 中的值匹配的記錄，只有 270 次是失敗的桃焕，因此需要的計(jì)算和磁盤 I/O 更少剑肯。
其結(jié)果是，優(yōu)化器會(huì)先測試 col2观堂，因?yàn)檫@樣做開銷更小让网。
- 示例 2
盡可能地讓索引列在比較表達(dá)式中獨(dú)立。如果你在函數(shù)調(diào)用或者更復(fù)雜的算術(shù)表達(dá)式條件中使用了某個(gè)數(shù)據(jù)列师痕，MySQL就不會(huì)使用索引溃睹，因?yàn)樗仨氂?jì)算出每個(gè)數(shù)據(jù)行的表達(dá)式值。
比如胰坟，下面的 WHERE 子句顯示了這種情況因篇。它們的功能相同，但是對于優(yōu)化目標(biāo)來說就有很大差異了：
WHERE mycol < 4 / 2
WHERE mycol * 2 < 4
對于第一行，優(yōu)化器把表達(dá)式 4/2 簡化為 2竞滓，接著使用 mycol 上的索引來快速地查找小于 2 的值咐吼。
對于第二個(gè)表達(dá)式，MySQL 必須檢索出每個(gè)數(shù)據(jù)行的 mycol 值商佑，乘以 2锯茄，接著把結(jié)果與 4 進(jìn)行比較。在這種情況下莉御，不會(huì)使用索引撇吞。數(shù)據(jù)列中的每個(gè)值都必須被檢索到，這樣才能計(jì)算出比較表達(dá)式左邊的值礁叔。
優(yōu)化器的內(nèi)容還可以有很多牍颈，這個(gè)專欄老師說后續(xù)會(huì)還有講。
5 執(zhí)行器
下面就到了最終的執(zhí)行階段琅关，執(zhí)行開始之前煮岁，會(huì)先判斷是否有操作權(quán)限，若沒有涣易，會(huì)拋出相關(guān)異常画机。
如果有權(quán)限，就打開表繼續(xù)執(zhí)行新症。打開表的時(shí)候步氏，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口徒爹。
比如我們這個(gè)例子中的表 T 中荚醒，ID 字段沒有索引，那么執(zhí)行器的執(zhí)行流程是這樣的：
1隆嗅、調(diào)用 InnoDB 引擎接口取這個(gè)表的第一行界阁，判斷 ID 值是不是 10，如果不是則跳過胖喳，如果是則將這行存在結(jié)果集中泡躯；
2、調(diào)用引擎接口取下一行丽焊，重復(fù)相同的判斷邏輯较剃，直到取到這個(gè)表的最后一行。
3粹懒、執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端重付。
至此，這個(gè)語句就執(zhí)行完成了凫乖。
對于有索引的表，執(zhí)行的邏輯也差不多。第一次調(diào)用的是取滿足條件的第一行這個(gè)接口帽芽，之后循環(huán)取滿足條件的下一行這個(gè)接口删掀，這些接口都是引擎中已經(jīng)定義好的。
可以看出导街，是否有索引披泪，執(zhí)行效率區(qū)別還是很大的，沒有索引需要取出所有數(shù)據(jù)搬瑰，一個(gè)個(gè)進(jìn)行比較款票；而有索引則是直接取滿足條件的數(shù)據(jù)；
課后題目 & 評論區(qū)精華
轉(zhuǎn)載自：https://mp.weixin.qq.com/s/k7KbnXmloqCCWdCTfXXsNw
