1. 單表查詢 SQL 在 MySQL 架構(gòu)中的各個(gè)組件的執(zhí)行過程
簡單用一張圖說明下,MySQL 架構(gòu)有哪些組件,接下來給大家用 SQL 語句分析
假如 SQL 語句是這樣
SELECT class_no FROM student WHERE name = 'lcy' AND age > 18 GROUP BY class_no其中 name 為索引,我們按照時(shí)間順序來分析一下
1. 客戶端:客戶端(如 MySQL 命令行工具、Navicat、MySQL Workbench 或其他應(yīng)用程序)發(fā)送 SQL 查詢到 MySQL 服務(wù)器。
2. 連接器:連接器負(fù)責(zé)與客戶端建立連接、管理連接和維護(hù)連接。當(dāng)客戶端連接到 MySQL 服務(wù)器時(shí),連接器驗(yàn)證客戶端的用戶名和密碼,然后分配一個(gè)線程來處理客戶端的請(qǐng)求。
3. 查詢緩存:查詢緩存用于緩存先前執(zhí)行過的查詢及其結(jié)果。當(dāng)收到新的查詢請(qǐng)求時(shí),MySQL 首先檢查查詢緩存中是否已有相同的查詢及其結(jié)果。如果查詢緩存中有匹配的查詢結(jié)果,MySQL 將直接返回緩存的結(jié)果,而無需再次執(zhí)行查詢。但是,如果查詢緩存中沒有匹配的查詢結(jié)果,MySQL 將繼續(xù)執(zhí)行查詢。查詢緩存在 MySQL 8.0 中已被移除,不詳細(xì)解釋。
4. 分析器:
解析查詢語句,檢查語法。
驗(yàn)證表名和列名的正確性。
生成查詢樹。
5. 優(yōu)化器:分析查詢樹,考慮各種執(zhí)行計(jì)劃,估算不同執(zhí)行計(jì)劃的成本,選擇最佳的執(zhí)行計(jì)劃。在這個(gè)例子中,優(yōu)化器可能會(huì)選擇使用 name 索引進(jìn)行查詢,因?yàn)?name 是索引列。
6. 執(zhí)行器:根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃,向存儲(chǔ)引擎發(fā)送請(qǐng)求,獲取滿足條件的數(shù)據(jù)行。
7. 存儲(chǔ)引擎(如 InnoDB):
負(fù)責(zé)實(shí)際執(zhí)行索引掃描,如在 student 表的 name 索引上進(jìn)行等值查詢,因查詢?nèi)苛?,涉及到回表訪問磁盤。
在訪問磁盤之前,先檢查 InnoDB 的緩沖池(Buffer Pool)中是否已有所需的數(shù)據(jù)頁。如果緩沖池中有符合條件的數(shù)據(jù)頁,直接使用緩存的數(shù)據(jù)。如果緩沖池中沒有所需的數(shù)據(jù)頁,從磁盤加載數(shù)據(jù)頁到緩沖池中。
8. 執(zhí)行器:
對(duì)于每個(gè)找到的記錄,再次判斷記錄是否滿足索引條件 name。這是因?yàn)榛谒饕龡l件加載到內(nèi)存中是數(shù)據(jù)頁,數(shù)據(jù)頁中也有可能包含不滿足索引條件的記錄,所以還要再判斷一次 name 條件,滿足 name 條件則繼續(xù)判斷 age > 18 過濾條件。
根據(jù) class_no 對(duì)滿足條件的記錄進(jìn)行分組。
執(zhí)行器將處理后的結(jié)果集返回給客戶端。
在整個(gè)查詢執(zhí)行過程中,這些組件共同協(xié)作以高效地執(zhí)行查詢??蛻舳素?fù)責(zé)發(fā)送查詢,連接器管理客戶端連接,查詢緩存嘗試重用先前查詢結(jié)果,解析器負(fù)責(zé)解析查詢,優(yōu)化器選擇最佳執(zhí)行計(jì)劃,執(zhí)行器執(zhí)行優(yōu)化器選擇的計(jì)劃,存儲(chǔ)引擎(如 InnoDB)負(fù)責(zé)管理數(shù)據(jù)存儲(chǔ)和訪問。這些組件的協(xié)同作用使得 MySQL 能夠高效地執(zhí)行查詢并返回結(jié)果集。 根據(jù)索引列過濾條件加載索引的數(shù)據(jù)頁到內(nèi)存這個(gè)操作是存儲(chǔ)引擎做的。加載到內(nèi)存中之后,執(zhí)行器會(huì)進(jìn)行索引列和非索引列的過濾條件判斷。
2. SELECT 的各個(gè)關(guān)鍵字在哪里執(zhí)行?
根據(jù)執(zhí)行順序,如下:
(1)FROM:FROM 子句用于指定查詢所涉及的數(shù)據(jù)表。在查詢執(zhí)行過程中,執(zhí)行器需要根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃從存儲(chǔ)引擎中獲取指定表的數(shù)據(jù)。
(2)ON:ON 子句用于指定連接條件,它通常與 JOIN 子句一起使用。在查詢執(zhí)行過程中,執(zhí)行器會(huì)根據(jù) ON 子句中的條件從存儲(chǔ)引擎獲取滿足條件的記錄。如果連接條件涉及到索引列,存儲(chǔ)引擎可能會(huì)使用索引進(jìn)行優(yōu)化。
(3)JOIN:JOIN 子句用于指定表之間的連接方式(如 INNER JOIN, LEFT JOIN 等)。在查詢執(zhí)行過程中,執(zhí)行器會(huì)根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃,從存儲(chǔ)引擎中獲取需要連接的表的數(shù)據(jù)。然后,執(zhí)行器根據(jù) JOIN 子句的類型和 ON 子句中的連接條件,對(duì)數(shù)據(jù)進(jìn)行連接操作。
(4)WHERE:執(zhí)行器對(duì)從存儲(chǔ)引擎返回的數(shù)據(jù)進(jìn)行過濾,只保留滿足 WHERE 子句條件的記錄。部分過濾條件如果涉及到索引,在存儲(chǔ)引擎層就已經(jīng)進(jìn)行了過濾。
(5)GROUP BY:執(zhí)行器對(duì)滿足 WHERE 子句條件的記錄按照 GROUP BY 子句中指定的列進(jìn)行分組。
(6)HAVING:執(zhí)行器在進(jìn)行分組后,根據(jù) HAVING 子句條件對(duì)分組后的記錄進(jìn)行進(jìn)一步過濾。
(7)SELECT:執(zhí)行器根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃來獲取查詢結(jié)果。
(8)DISTINCT:執(zhí)行器對(duì)查詢結(jié)果進(jìn)行去重,只返回不重復(fù)的記錄。
(9)ORDER BY:執(zhí)行器對(duì)查詢結(jié)果按照 ORDER BY 子句中指定的列進(jìn)行排序。
(10)LIMIT:執(zhí)行器根據(jù) LIMIT 子句中指定的限制條件對(duì)查詢結(jié)果進(jìn)行截?cái)啵环祷夭糠钟涗?/p>
3. 表關(guān)聯(lián)查詢 SQL 在 MySQL 架構(gòu)中的各個(gè)組件的執(zhí)行過程
SELECT s.id, s.name, s.age, sc.subject, sc.score FROM student s JOIN score sc ON s.id = sc.student_id WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;這個(gè)例子中,student_id 和 subject 是聯(lián)合索引,age 是索引。 我們按照時(shí)間順序來分析一下
1. 連接器:當(dāng)客戶端連接到 MySQL 服務(wù)器時(shí),連接器負(fù)責(zé)建立和管理連接。它驗(yàn)證客戶端提供的用戶名和密碼,確定客戶端具有相應(yīng)的權(quán)限,然后建立連接。
2. 查詢緩存:MySQL 服務(wù)器在處理查詢之前,會(huì)先檢查查詢緩存。如果查詢緩存中已經(jīng)存在相同的查詢及其結(jié)果集,服務(wù)器將直接返回緩存中的結(jié)果,而不再執(zhí)行后續(xù)的查詢處理。由于查詢緩存在 MySQL 8.0 中已被移除,我們?cè)谶@個(gè)示例中不再詳細(xì)討論。
3. 解析器:解析器的主要任務(wù)是解析 SQL 查詢語句,確保查詢語法正確。解析器會(huì)將查詢語句分解成多個(gè)組成部分,例如表、列、條件等。在這個(gè)示例中,解析器會(huì)識(shí)別出涉及的表(student 和 score)以及需要的列(id、name、age、subject、score)。
4. 優(yōu)化器:優(yōu)化器的職責(zé)是根據(jù)解析器提供的信息生成執(zhí)行計(jì)劃。它會(huì)分析多種可能的執(zhí)行策略,并選擇成本最低的策略。在這個(gè)示例中,優(yōu)化器可能會(huì)分析各種表掃描和索引掃描的組合,最終選擇一種成本最低的執(zhí)行計(jì)劃。
5. 執(zhí)行器:根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃處理查詢,向存儲(chǔ)引擎發(fā)送請(qǐng)求,獲取滿足條件的數(shù)據(jù)行。
6. 存儲(chǔ)引擎(如 InnoDB):存儲(chǔ)引擎負(fù)責(zé)管理數(shù)據(jù)的存儲(chǔ)和檢索。
存儲(chǔ)引擎首先接收來自執(zhí)行器的請(qǐng)求。請(qǐng)求可能包括獲取滿足查詢條件的數(shù)據(jù)行,以及使用哪種掃描方法(如全表掃描或索引掃描)。
假設(shè)執(zhí)行器已經(jīng)決定使用索引掃描。在這個(gè)示例中,存儲(chǔ)引擎可能會(huì)先對(duì) student 表進(jìn)行索引掃描(使用 age 索引),然后對(duì) score 表進(jìn)行索引掃描(使用 student_id 和 subject 的聯(lián)合索引)。
存儲(chǔ)引擎會(huì)根據(jù)請(qǐng)求查詢相應(yīng)的索引結(jié)構(gòu)。在 student 表中,存儲(chǔ)引擎會(huì)找到滿足 age > 18 條件的記錄。在 score 表中,存儲(chǔ)引擎會(huì)找到滿足 subject = 'math' AND score > 80 條件的記錄。
一旦找到了滿足條件的記錄,存儲(chǔ)引擎需要將這些記錄所在的數(shù)據(jù)頁從磁盤加載到內(nèi)存中。存儲(chǔ)引擎首先檢查緩沖池(InnoDB Buffer Pool),看這些數(shù)據(jù)頁是否已經(jīng)存在于內(nèi)存中。如果已經(jīng)存在,則無需再次從磁盤加載。如果不存在,存儲(chǔ)引擎會(huì)將這些數(shù)據(jù)頁從磁盤加載到緩沖池中。
加載到緩沖池中的記錄可以被多個(gè)查詢共享,這有助于提高查詢效率。
7. 執(zhí)行器:處理連接、排序、聚合、過濾等操作。
在內(nèi)存中執(zhí)行連接操作,將 student 表和 score 表的數(shù)據(jù)行連接起來。
對(duì)連接后的結(jié)果集進(jìn)行過濾,只保留滿足查詢條件(age > 18、subject = 'math'、score > 80)的數(shù)據(jù)行。
將過濾后的數(shù)據(jù)行作為查詢結(jié)果返回給客戶端。
前面說過,根據(jù)存儲(chǔ)引擎根據(jù)索引條件加載到內(nèi)存的數(shù)據(jù)頁有多數(shù)據(jù),可能有不滿足索引條件的數(shù)據(jù),如果執(zhí)行器不再次進(jìn)行索引條件判斷, 則無法判斷哪些記錄滿足索引條件的,雖然在存儲(chǔ)引擎判斷過了,但是在執(zhí)行器還是會(huì)有索引條件 age > 18、subject = 'math'、score > 80 的判斷。
4. LEFT JOIN 將過濾條件放在子查詢中再關(guān)聯(lián)和放在 WHERE 子句上有什么區(qū)別?
先看例子 查詢 1
SELECT s.id, s.name, s.age, sc.subject, sc.score FROM student s LEFT JOIN score sc ON s.id = sc.student_id WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
查詢 2
SELECT s.id, s.name, s.age, sc.subject, sc.score FROM (SELECT id, name, age FROM student WHERE age > 18) s LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc ON s.id = sc.student_id
查詢 3
SELECT s.id, s.name, s.age, sc.subject, sc.score FROM student s LEFT JOIN score sc ON s.id = sc.student_id AND s.age > 18 AND sc.subject = 'math' AND sc.score > 80;先給出結(jié)論:查詢 2 和 3 是一樣的,也就是過濾條件放在子查詢中和放在 on 上面是一樣的,后面就只討論查詢 1、2,查詢 1 和查詢 2 是不一樣的,過濾條件放在 where 子句中和放在子查詢?cè)訇P(guān)聯(lián)查詢出的結(jié)果也是有區(qū)別的。 分析一下 從運(yùn)行結(jié)果來看,對(duì)于查詢 1
SELECT s.id, s.name, s.age, sc.subject, sc.score FROM student s LEFT JOIN score sc ON s.id = sc.student_id WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;在這個(gè)查詢中,首先執(zhí)行 LEFT JOIN,將 student 表和 score 表連接起來。連接操作是基于 s.id = sc.student_id 條件進(jìn)行的。LEFT JOIN 操作會(huì)保留左表(student 表)中的所有行,即使它們?cè)谟冶恚╯core 表)中沒有匹配的行。如果右表中沒有匹配的行,那么右表的列將顯示為 NULL。 然后,WHERE 子句會(huì)過濾連接后的結(jié)果集,只保留那些滿足 s.age > 18 and sc.subject = 'math' and sc.score > 80 條件的行。這意味著,右表為 NULL 的記錄將被排除,因?yàn)橛冶淼倪^濾條件 sc.subject = 'math' and sc.score > 80 條件不滿足。 對(duì)于查詢 2:
SELECT s.id, s.name, s.age, sc.subject, sc.score FROM (select id, name, age from student where age > 18) s LEFT JOIN (select subject, score from score where subject = 'math' AND score > 80) sc ON s.id = sc.student_id在這個(gè)查詢中,我們首先執(zhí)行兩個(gè)子查詢。第一個(gè)子查詢從 student 表中選擇所有 age > 18 的行,而第二個(gè)子查詢從 score 表中選擇所有 subject = 'math' and score > 80 的行。這意味著,在進(jìn)行連接操作之前,我們已經(jīng)對(duì)兩個(gè)表分別進(jìn)行了過濾。 接下來,執(zhí)行 LEFT JOIN 操作,將過濾后的 s 和 sc 子查詢的結(jié)果集連接起來,基于 s.id = sc.student_id 條件。因?yàn)?LEFT JOIN 操作會(huì)保留左表(s 子查詢的結(jié)果集)中的所有行,右表為 NULL 的記錄包含了。 結(jié)果差異: 查詢 1 和查詢 2 的主要區(qū)別在于 WHERE 子句和子查詢的使用。
查詢 1 在連接操作后應(yīng)用過濾條件,這可能導(dǎo)致右表為 NULL 的關(guān)聯(lián)記錄因?yàn)橛冶淼倪^濾條件而被排除在外。而查詢 2 在連接操作之前就已經(jīng)過濾了表中的數(shù)據(jù),這意味著查詢結(jié)果會(huì)包含所有左表過濾條件的記錄,以及右表過濾條件的記錄和 NULL 的記錄。 如果查詢 1 想保留右表為 NULL 的記錄,只需要改為 WHERE s.age > 18 AND (sc.student_id is null OR (sc.subject = 'math' AND sc.score> 80)); 這樣查詢 1 和 2 會(huì)有相同的結(jié)果集。 我們分析一下這兩個(gè)查詢?cè)?MySQL 架構(gòu)中各個(gè)組件中執(zhí)行的區(qū)別 對(duì)于查詢 1:
SELECT s.id, s.name, s.age, sc.subject, sc.score FROM student s LEFT JOIN score sc ON s.id = sc.student_id WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
連接器:客戶端與服務(wù)器建立連接。
查詢緩存:檢查緩存是否存在此查詢的結(jié)果。如果有,直接返回結(jié)果。否則,繼續(xù)執(zhí)行。
解析器:解析查詢語句,檢查語法是否正確。
優(yōu)化器:對(duì)查詢進(jìn)行優(yōu)化,生成執(zhí)行計(jì)劃,決定連接和過濾條件的順序等。
執(zhí)行器:開始請(qǐng)求執(zhí)行查詢。
存儲(chǔ)引擎(InnoDB):從磁盤或者緩沖池讀取滿足條件的數(shù)據(jù)行(s.id = sc.student_id),因?yàn)槭?left join,所以即便 sc.student_id 為 null 也會(huì)被關(guān)聯(lián)。
執(zhí)行器:將從存儲(chǔ)引擎獲取的數(shù)據(jù)行進(jìn)行左連接,應(yīng)用過濾條件 s.age > 18 and sc.subject = 'math' and sc.score > 80 進(jìn)行過濾,將結(jié)果集返回給客戶端。
當(dāng)查詢包含索引列的條件時(shí),MySQL 的存儲(chǔ)引擎會(huì)首先利用索引在磁盤上定位到滿足索引條件的記錄。接著,將這些索引數(shù)據(jù)對(duì)應(yīng)的數(shù)據(jù)頁加載到內(nèi)存中的緩沖池。然后,執(zhí)行器在內(nèi)存中對(duì)這些記錄進(jìn)行進(jìn)一步的過濾,根據(jù)索引條件和非索引列的條件來過濾數(shù)據(jù)。
當(dāng)查詢涉及到非聚集索引時(shí),需要回表的操作會(huì)導(dǎo)致聚集索引和非聚集索引都被加載到內(nèi)存中。但是,如果查詢只涉及到聚集索引(如主鍵查詢),那么只需要加載聚集索引的數(shù)據(jù)頁即可。 對(duì)于查詢 2
SELECT s.id, s.name, s.age, sc.subject, sc.score FROM (SELECT id, name, age FROM student WHERE age > 18) s LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc ON s.id = sc.student_id
連接器:客戶端與服務(wù)器建立連接。
查詢緩存:檢查緩存是否存在此查詢的結(jié)果。如果有,直接返回結(jié)果。否則,繼續(xù)執(zhí)行。
解析器:解析查詢語句,檢查語法是否正確。
優(yōu)化器:決定使用哪些索引進(jìn)行查詢優(yōu)化,以及確定連接順序。
執(zhí)行器:開始請(qǐng)求執(zhí)行子查詢。
存儲(chǔ)引擎(InnoDB):首先,對(duì) student 表進(jìn)行掃描,將滿足條件 s.age > 18 的記錄對(duì)應(yīng)的數(shù)據(jù)頁加載到緩沖池 (如果緩沖池沒有這個(gè)頁的數(shù)據(jù))。然后,使用 subject = 'math' AND score > 80 對(duì) score 表進(jìn)行掃描,將滿足條件的記錄對(duì)應(yīng)的數(shù)據(jù)頁加載到緩沖池 (如果緩沖池沒有這個(gè)頁的數(shù)據(jù))。
執(zhí)行器:對(duì)從存儲(chǔ)引擎獲取的數(shù)據(jù)應(yīng)用所有的過濾條件,過濾后的結(jié)果存入臨時(shí)表,執(zhí)行主查詢,從臨時(shí)表中獲取數(shù)據(jù),將 s 和 sc 進(jìn)行左連接,根據(jù) s.id = sc.student_id 組合結(jié)果。將連接后的結(jié)果返回給客戶端。
從這里我們可以看出,查詢 2 是先過濾后連接,每張表的索引都很重要,如果沒設(shè)置好索引,單表過濾會(huì)全表掃描。 寫 SQL 的時(shí)候,查詢 1 和查詢 2 到底采用哪種方式呢? 根據(jù)不同情況各有應(yīng)用場景,需要注意的是,對(duì)于查詢 2,子查詢的結(jié)果集被存儲(chǔ)在一個(gè)臨時(shí)表中,臨時(shí)表不會(huì)繼承原始索引,包括聚集索引和非聚集索引,所以剛剛的例子中,臨時(shí)表中 s.id 和 sc.student_id 已經(jīng)不是任何索引列了。對(duì)于查詢 1,最終滿足關(guān)聯(lián)條件 s.id = sc.student_id 的所有記錄都會(huì)被加載到內(nèi)存后再進(jìn)行過濾。
當(dāng)單表過濾后的數(shù)據(jù)量較小時(shí),查詢 2 可能是一個(gè)更好的選擇,因?yàn)樗梢詼p少關(guān)聯(lián)操作的數(shù)據(jù)量,從而提高查詢效率。子查詢階段,MySQL 依然會(huì)利用原始表上的索引進(jìn)行過濾。子查詢執(zhí)行完成后,將過濾后的數(shù)據(jù)存儲(chǔ)在臨時(shí)表中。所以查詢 2 的方式可以優(yōu)化的點(diǎn)就是在單表查詢時(shí)盡可能的利用索引。
當(dāng)單表過濾后的數(shù)據(jù)量較大時(shí),查詢 1 可能更合適,因?yàn)樗梢愿玫乩盟饕M(jìn)行關(guān)聯(lián)操作。這樣可以減少關(guān)聯(lián)操作的時(shí)間開銷,查詢 2 因?yàn)榕R時(shí)表不繼承索引,表關(guān)聯(lián)的時(shí)間開銷比較大。
5. 聚集索引和全表掃描有什么區(qū)別呢?
走PRIMARY 索引(聚集索引)和全表掃描有什么區(qū)別呢?準(zhǔn)確來說,使用 InnoDB 存儲(chǔ)引擎的情況下,全表掃描的數(shù)據(jù)和聚集索引的數(shù)據(jù)在 InnoDB 表空間中的存儲(chǔ)位置是相同的,也就是說它們的內(nèi)存地址也是相同的。所以你也可以理解為,他們其實(shí)都是在聚集索引上操作的(聚集索引 B + 樹的葉子結(jié)點(diǎn)是根據(jù)主鍵排好序的完整的用戶記錄,包含表里的所有字段),區(qū)別就在于 全表掃描將聚集索引 B + 樹的葉子結(jié)點(diǎn)從左到右依次順序掃描并判斷條件。 聚集索引是利用二分思想將聚集索引 B + 樹到指定范圍區(qū)間進(jìn)行掃描,比如 select * from demo_info where id in (1, 2) 這種條件字段是主鍵 id,可以很好的利用 PRIMARY 索引進(jìn)行二分的快速查詢。
在 MyISAM 中,全表掃描的數(shù)據(jù)和索引數(shù)據(jù)的存儲(chǔ)位置是分開的。然而 MyISAM 已經(jīng)被 InnoDB 取代,不再是 MySQL 的推薦存儲(chǔ)引擎,從 MySQL5.5 開始,InnoDB 就成了 MySQL 的默認(rèn)存儲(chǔ)引擎。 默認(rèn)情況下,InnoDB 使用一個(gè)名為 ibdata1 的共享表空間文件存儲(chǔ)所有的數(shù)據(jù)和索引,包括聚集索引和二級(jí)索引(又稱非聚集索引或輔助索引)。
審核編輯:劉清
-
連接器
+關(guān)注
關(guān)注
98文章
14084瀏覽量
135734 -
SQL
+關(guān)注
關(guān)注
1文章
751瀏覽量
43995 -
MYSQL數(shù)據(jù)庫
+關(guān)注
關(guān)注
0文章
95瀏覽量
9372
原文標(biāo)題:一條SQL如何被MySQL架構(gòu)中的各個(gè)組件操作執(zhí)行的?
文章出處:【微信號(hào):OSC開源社區(qū),微信公眾號(hào):OSC開源社區(qū)】歡迎添加關(guān)注!文章轉(zhuǎn)載請(qǐng)注明出處。
發(fā)布評(píng)論請(qǐng)先 登錄
相關(guān)推薦
評(píng)論