1、基本架構(gòu)：一條SQL語(yǔ)句查詢(xún)語(yǔ)句是如何執(zhí)行？

下面介紹一下MySQL的基本架構(gòu)示意圖拓哟。

MySQL的邏輯架構(gòu)圖

MySQL 可以分為Server層和存儲(chǔ)引擎層兩個(gè)部分几缭。

1 河泳、客戶(hù)端
用來(lái)跟連接器建立連接的應(yīng)用程序。
2 年栓、 Server層

• Server層包括連接器拆挥、查詢(xún)緩存、分析器某抓、優(yōu)化器纸兔、執(zhí)行器等，包涵所有MySQL的大多數(shù)核心服務(wù)功能否副，以及所有的內(nèi)置函數(shù)（日期汉矿、時(shí)間、數(shù)學(xué)备禀、和加密函數(shù)）洲拇，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過(guò)程曲尸、觸發(fā)器赋续、視圖等。
• 存儲(chǔ)引擎負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取队腐。其架構(gòu)是插件式的蚕捉，支持InnoDB、MyISAM柴淘、Memory等多個(gè)存儲(chǔ)引擎迫淹。從MySQL5.5.5版本以后InnoDB為默認(rèn)存儲(chǔ)引擎秘通。你在create table 建表時(shí)，若不指定引擎類(lèi)型敛熬，默認(rèn)就是InnoDB引擎肺稀。若想指定存儲(chǔ)引擎，通過(guò)engine=memory 方式指定应民。不同的存儲(chǔ)引擎卻共用一個(gè)Server層 话原，它是從連接器到執(zhí)行器的部分。
  3 诲锹、連接器
  你連接數(shù)據(jù)庫(kù)就需要通過(guò)連接器繁仁，與客戶(hù)端建立連接、獲取權(quán)限
  權(quán)限归园、維持和管理連接黄虱。
  eg.1

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

輸完命令之后，你就需要輸入密碼就可以登錄庸诱，也可以在p參數(shù)后直接跟密碼捻浦，但這樣你就想密碼暴露了，所有不建議這么做桥爽。
連接命令行中mysql就是客戶(hù)端工具朱灿，用來(lái)跟服務(wù)器建立連接。在完成經(jīng)典TCP握手后钠四，連接器就要開(kāi)始通過(guò)用戶(hù)名和密碼來(lái)驗(yàn)證你的身份盗扒。若用戶(hù)名和密碼不對(duì)，就會(huì)收到一個(gè)“Access denied for user”的錯(cuò)誤形导，然后客戶(hù)端程序結(jié)束執(zhí)行环疼。若認(rèn)證通過(guò)，連接器會(huì)到權(quán)限表里查出你所擁有的權(quán)限朵耕。之后這個(gè)連接里的權(quán)限判斷邏輯炫隶，都將依賴(lài)于此時(shí)讀到的權(quán)限。
這就意味著阎曹，一個(gè)用戶(hù)建立連接后伪阶，即使你用管理員賬號(hào)對(duì)這個(gè)用戶(hù)的權(quán)限做了修改，也不會(huì)影響已經(jīng)存在連接的權(quán)限处嫌。
連接完成之后栅贴，如果你沒(méi)有后續(xù)的動(dòng)作，這個(gè)連接就處于空閑狀態(tài)熏迹，你可以在show processlist 命令中看到它檐薯。下圖是該命令顯示的結(jié)果，其中Command 列顯示為Sleep 這一行在系統(tǒng)中有一個(gè)空閑連接。

• 1咧纠、定期斷開(kāi)長(zhǎng)連接。使用一段時(shí)間泻骤，或者程序里判斷執(zhí)行一個(gè)占內(nèi)存的大查詢(xún)后漆羔，斷開(kāi)連接，之后要查詢(xún)?cè)僦剡B接狱掂。
• 2演痒、如果你用MySQL 5.7或者更新的版本，可以在每次執(zhí)行一個(gè)比較大的操作后趋惨，通過(guò)執(zhí)行mysql_connection來(lái)重新初始化連接資源鸟顺。這個(gè)過(guò)程不需要重連和重新做權(quán)限驗(yàn)證，但是會(huì)將連接恢復(fù)到剛剛創(chuàng)建完時(shí)的狀態(tài)器虾。
  4讯嫂、查詢(xún)緩存
  連接建立完成后养筒，就可以進(jìn)行查詢(xún)select語(yǔ)句。執(zhí)行邏輯就會(huì)來(lái)到第二步：查詢(xún)緩存端姚。
  MySQL 拿到一個(gè)查詢(xún)請(qǐng)求后晕粪，會(huì)先到查詢(xún)緩存看看，之前是不是執(zhí)行這條語(yǔ)句渐裸。之前執(zhí)行過(guò)的語(yǔ)句及結(jié)果可能會(huì)以key-value對(duì)形式巫湘，被直接緩存在內(nèi)存中。key是查詢(xún)的語(yǔ)句昏鹃，value是查詢(xún)的結(jié)果尚氛。如果你的查詢(xún)能夠直接在這個(gè)緩存中找到key，那么這個(gè)value就會(huì)被直接返回給客戶(hù)端洞渤。
  如果語(yǔ)句不在查詢(xún)緩存中阅嘶，就會(huì)繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后载迄，執(zhí)行結(jié)果會(huì)被存入查詢(xún)緩存中讯柔。你可以看到，如果查詢(xún)命中緩存护昧，MySQL不需要執(zhí)行后面的復(fù)雜操作魂迄，就可以直接返回結(jié)果，這個(gè)效率會(huì)很高惋耙。
  但是大多數(shù)情況下我會(huì)建議你不要使用查詢(xún)緩存捣炬，為什么呢？因?yàn)椴樵?xún)緩存往往弊大于利绽榛。
  查詢(xún)緩存的失效非常頻繁湿酸，只要對(duì)一個(gè)表的更新，這個(gè)表上所有的查詢(xún)緩存都會(huì)被清空灭美。
  好在MySQL也提供了這種''按需使用''的方式推溃。你可以將參數(shù)query_cache_type設(shè)置成demand ,這樣對(duì)于默認(rèn)的SQL語(yǔ)句都不使用查詢(xún)緩存。而對(duì)于你確認(rèn)要使用查詢(xún)緩存的語(yǔ)句冲粤，可以用SQL_cache顯示指定美莫，像下面的語(yǔ)句一樣：

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

需要注意的是梯捕，MySQL8.0版本直接將查詢(xún)緩存的整塊功能刪掉厢呵，也就是8.0開(kāi)始徹底沒(méi)有這個(gè)功能。
5傀顾、分析器
如果沒(méi)有命中查詢(xún)緩存襟铭，就要開(kāi)始真正執(zhí)行語(yǔ)句，首先在查詢(xún)前，MySQL需要知道你要做什么寒砖，因此需要對(duì)SQL語(yǔ)句進(jìn)行語(yǔ)句解析赐劣。
分析器先會(huì)做詞法分析，你所輸入的SQL語(yǔ)句是由多個(gè)字符串和空格組成哩都，MySQL需要識(shí)別這些字符串分別是什么魁兼，代表什么意思。
MySQL從你輸入的select這個(gè)關(guān)鍵字識(shí)別出來(lái)漠嵌，這是一個(gè)查詢(xún)語(yǔ)句咐汞。它要把字符串"T"識(shí)別成表名''T'' ，把字符串"ID"識(shí)別成''列ID'' 儒鹿。

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

一般語(yǔ)法錯(cuò)誤會(huì)提示第一個(gè)出現(xiàn)錯(cuò)誤的位置化撕，所以你要關(guān)注的就是緊接"use near"的內(nèi)容。
6约炎、優(yōu)化器
經(jīng)過(guò)了分析器植阴，MySQL就知道你要做什么了。在開(kāi)始執(zhí)行之前圾浅，還要經(jīng)過(guò)優(yōu)化器處理掠手。
優(yōu)化器是在表里有很多個(gè)索引的時(shí)候，決定使用哪個(gè)索引贱傀；或者在一個(gè)語(yǔ)句有多表關(guān)聯(lián)（join）的時(shí)候惨撇，決定各個(gè)表的連接順序。比如你執(zhí)行下面這樣的語(yǔ)句府寒，這個(gè)語(yǔ)句是執(zhí)行兩個(gè)表的join:

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

• 既可以先從表t1里面取出c=10的記錄的ID值，在根據(jù)ID值關(guān)聯(lián)到表t2报腔，再判斷t2里面的d的值是否等于20株搔。
• 也可以先從表t2里面取出d=20的記錄的ID值，再根據(jù)ID值關(guān)聯(lián)到t1,在判斷t1里面的c的值是否等于10纯蛾。

t1-t2表

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

如果有權(quán)限巷波，就打開(kāi)表繼續(xù)執(zhí)行河闰。當(dāng)打開(kāi)表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義褥紫，去使用這個(gè)引擎提供的接口姜性。
比如我們這個(gè)例子中的表T中，ID字段沒(méi)有索引髓考，那么執(zhí)行流程是這樣的：

• 1部念、調(diào)用InnoDB引擎接口取這個(gè)表的第一行，判斷ID值是不是10氨菇，如果不是則跳過(guò)儡炼，如果是則將這行結(jié)果集中；
• 2查蓉、調(diào)用引擎接口取"下一行"乌询，重復(fù)相同的判斷邏輯，直到取到這個(gè)表的最后一行豌研。
• 3妹田、執(zhí)行器將上述遍歷過(guò)程中所有滿(mǎn)足條件的行組成的記錄集作為結(jié)果返回給客戶(hù)端。
  至此鹃共，這個(gè)執(zhí)行語(yǔ)句就執(zhí)行結(jié)束鬼佣。
  對(duì)于有索引的表，執(zhí)行的邏輯也差不多霜浴。第一次調(diào)用的是"取滿(mǎn)足條件的第一行"這個(gè)接口晶衷，之后循環(huán)取"滿(mǎn)足條件的下一行"這個(gè)接口，這些接口都是引擎中已經(jīng)定義好的阴孟。
  你會(huì)在數(shù)據(jù)庫(kù)的慢查詢(xún)?nèi)罩局锌吹揭粋€(gè) rows_examined的字段晌纫，表示這個(gè)語(yǔ)句執(zhí)行過(guò)程中掃描了多少行。這個(gè)值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)行的時(shí)候累加的永丝。
  在有些場(chǎng)景下锹漱，執(zhí)行器調(diào)用一次，在引擎獲內(nèi)部則掃描了多少行类溢，因此引擎描行數(shù)跟rows_examined 并不是完全相同的凌蔬。
  問(wèn)題
  如果表T中沒(méi)有字段K露懒，而你執(zhí)行了這個(gè)語(yǔ)句 select * from T where k=1 , 那肯定會(huì)報(bào)"不存在"錯(cuò)誤："Unknown column 'k' in 'where clause' "。
  解：
  是分析器