一、MySQL 體系架構(gòu)

- 連接池組件

• 1、負(fù)責(zé)與客戶(hù)端的通信，是半雙工模式，這就意味著某一固定時(shí)刻只能由客戶(hù)端向服務(wù)器請(qǐng)求或者服務(wù)器向客戶(hù)端發(fā)送數(shù)據(jù)，而不能同時(shí)進(jìn)行。
• 2、驗(yàn)證用戶(hù)名和密碼是否正確（數(shù)據(jù)庫(kù) MySQL 的 user 表中進(jìn)行驗(yàn)證），如果錯(cuò)誤返回錯(cuò)誤通知Access denied for user 'root'@'localhost'（using password：YES）；如果正確，則會(huì)去 MySQL 的權(quán)限表查詢(xún)當(dāng)前用戶(hù)的權(quán)限。

- 緩存組件

也稱(chēng)為查詢(xún)緩存，存儲(chǔ)的數(shù)據(jù)是以鍵值對(duì)的形式進(jìn)行存儲(chǔ)，如果開(kāi)啟了緩存，那么在一條查詢(xún) SQL 語(yǔ)句進(jìn)來(lái)時(shí)會(huì)先判斷緩存中是否包含當(dāng)前的 SQL 語(yǔ)句鍵值對(duì)，如果存在直接將其對(duì)應(yīng)的結(jié)果返回，如果不存在再執(zhí)行后面一系列操作。如果沒(méi)有開(kāi)啟則直接跳過(guò)。

show ?variables ?like ?'have_query_cache'; # 查看緩存配置：
show ?variables ?like ?'query_cache_type'; # 查看是否開(kāi)啟
show ?variables ?like ?'query_cache_size'; # 查看緩存占用大小
show ?status ?like ?'Qcache%'; # 查看緩存狀態(tài)信息

緩存失效場(chǎng)景：

• 查詢(xún)語(yǔ)句不一致。前后兩條查詢(xún) SQL 必須完全一致；
• 查詢(xún)語(yǔ)句中含有一些不確定的值時(shí)，則不會(huì)緩存。比如 now()、current_date()、curdate()、curtime()、rand()、uuid() 等；
• 不使用任何表查詢(xún)。如 select 'A'；
• 查詢(xún) mysql、information_schema 或 performance_schema 數(shù)據(jù)庫(kù)中的表時(shí)，不會(huì)走查詢(xún)緩存；
• 在存儲(chǔ)的函數(shù)，觸發(fā)器或事件的主體內(nèi)執(zhí)行的查詢(xún)；
• 如果表更改，則使用該表的所有高速緩存查詢(xún)都變?yōu)闊o(wú)效并從緩存中刪除，這包括使用 MERGE 映射到已更改表的表的查詢(xún)。一個(gè)表可以被許多類(lèi)型的語(yǔ)句改變，如 insert、update、delete、truncate table、alter table、drop table、drop database。

通過(guò)上面的失效場(chǎng)景可以看出緩存是很容易失效的，所以如果不是查詢(xún)次數(shù)遠(yuǎn)大于修改次數(shù)的話，使用緩存不僅不能提升查詢(xún)效率還會(huì)拉低效率（每次讀取后需要向緩存中保存一份，而緩存又容易被清除）。所以在 MySQL5.6 默認(rèn)是關(guān)閉緩存的，并且在 8.0 直接被移除了。當(dāng)然，如果場(chǎng)景需要用到，還是可以使用的。

開(kāi)啟：

在配置文件（linux 下是安裝目錄的 cnf 文件，windows 是安裝目錄下的 ini 文件）中，增加配置： query_cache_type = 1

# 指定 SQL_NO_CACHE，SQL_CACHE 同理。
SELECT ?SQL_NO_CACHE ?* ?FROM ?student ?WHERE age > 20;?

- 分析器

對(duì)客戶(hù)端傳來(lái)的 SQL 進(jìn)行分析，這將包括預(yù)處理與解析過(guò)程，并進(jìn)行關(guān)鍵詞的提取、解析，并組成一個(gè)解析樹(shù)。具體的解析詞包括但不局限于 select/update/delete/or/in/where/group by/having/count/limit 等，如果分析到語(yǔ)法錯(cuò)誤，會(huì)直接拋給

客戶(hù)端異常：ERROR:You have an error in your SQL syntax.。

select * ?from user where userId = 1234;

在分析器中就通過(guò)語(yǔ)義規(guī)則器將 select from where 這些關(guān)鍵詞提取和匹配出來(lái)，MySQL 會(huì)自動(dòng)判斷關(guān)鍵詞和非關(guān)鍵詞，將用戶(hù)的匹配字段和自定義語(yǔ)句識(shí)別出來(lái)。這個(gè)階段也會(huì)做一些校驗(yàn)：比如校驗(yàn)當(dāng)前數(shù)據(jù)庫(kù)是否存在 user 表，同時(shí)假如 user 表中不存在 userId 這個(gè)字段同樣會(huì)報(bào)錯(cuò)：unknown column in field list.。

- 優(yōu)化器

進(jìn)入優(yōu)化器說(shuō)明 SQL 語(yǔ)句是符合標(biāo)準(zhǔn)語(yǔ)義規(guī)則并且可以執(zhí)行。優(yōu)化器會(huì)根據(jù)執(zhí)行計(jì)劃選擇最優(yōu)的選擇，匹配合適的索引，選擇最佳的方案。比如一個(gè)典型的例子是這樣的：

表 T，對(duì) A、B、C 列建立聯(lián)合索引 —— (A,B,C)，在進(jìn)行查詢(xún)的時(shí)候，當(dāng) SQL 查詢(xún)條件是：select xx where B=x and A=x and C=x。很多人會(huì)以為是用不到索引的，但其實(shí)會(huì)用到，雖然索引必須符合最左原則才能使用，但是本質(zhì)上，優(yōu)化器會(huì)自動(dòng)將這條 SQL 優(yōu)化為：where A=x and B=x and C=x，這種優(yōu)化會(huì)為了底層能夠匹配到索引，同時(shí)在這個(gè)階段是自動(dòng)按照?qǐng)?zhí)行計(jì)劃進(jìn)行預(yù)處理，MySQL 會(huì)計(jì)算各個(gè)執(zhí)行方法的最佳時(shí)間，最終確定一條執(zhí)行的 SQL 交給最后的執(zhí)行器。

優(yōu)化器會(huì)根據(jù)掃描行數(shù)、是否使用臨時(shí)表、是否排序等來(lái)判斷是否使用某個(gè)索引，其中掃描行數(shù)的計(jì)算可以通過(guò)統(tǒng)計(jì)信息來(lái)估算得出，而統(tǒng)計(jì)信息可以看作是索引唯一數(shù)的數(shù)量，可以使用部分采樣來(lái)估算，具體就是選擇 N 個(gè)數(shù)據(jù)頁(yè)，統(tǒng)計(jì)這些頁(yè)上數(shù)據(jù)的不同值，得到一個(gè)平均值，然后乘以這個(gè)索引的頁(yè)面數(shù)，就得到了。但是因?yàn)樗饕龜?shù)據(jù)會(huì)變化，所以索引的統(tǒng)計(jì)信息也會(huì)變化。當(dāng)變更的數(shù)據(jù)行數(shù)超過(guò) 1/M 的時(shí)候，就會(huì)重新計(jì)算一次統(tǒng)計(jì)信息。

關(guān)于統(tǒng)計(jì)信息可以選擇是否持久化：：通過(guò) innodb_stats_persistent，設(shè)置為 on 的時(shí)候，表示統(tǒng)計(jì)信息會(huì)持久化存儲(chǔ)。這時(shí)，默認(rèn)的 N 是 20，M 是 10。設(shè)置為 off 的時(shí)候，表示統(tǒng)計(jì)信息只存儲(chǔ)在內(nèi)存中。這時(shí)，默認(rèn)的 N 是 8，M 是 16。

沒(méi)有使用最優(yōu)索引如何優(yōu)化：：

• 1、雖然會(huì)自動(dòng)更新統(tǒng)計(jì)信息，但是但是不能保證統(tǒng)計(jì)信息是最新值，這就可能導(dǎo)致優(yōu)化器選擇了不同的索引導(dǎo)致執(zhí)行變慢，所以可以通過(guò) analyze table 表名 來(lái)重新計(jì)算索引的統(tǒng)計(jì)信息;
• 2、在表名后面添加 force index(索引名) 語(yǔ)句來(lái)強(qiáng)制使用索引（不建議）;
• 3、將 SQL 進(jìn)行修改成優(yōu)化器可以選最優(yōu)索引的實(shí)現(xiàn)方式；
• 4、新建一個(gè)最優(yōu)索引或者刪除優(yōu)化器誤用的索引；

- 執(zhí)行器

執(zhí)行器會(huì)調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎執(zhí)行 SQL，主流的是 MyISAM 和 Innodb。

二、寫(xiě)操作執(zhí)行過(guò)程

三、讀操作執(zhí)行過(guò)程

在 MySQL 5.6 之后引入了 索引下推（Index Condition Pushdown），所以在查詢(xún)操作上會(huì)有一個(gè) Index Filter 和 Table Filter 的過(guò)程，查詢(xún)的流程圖大致可以用下面這張圖來(lái)概括：

四、SQL執(zhí)行順序

到此這篇關(guān)于一條 SQL 語(yǔ)句執(zhí)行過(guò)程的文章就介紹到這了,更多相關(guān)SQL 執(zhí)行過(guò)程內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論