理解索引(中):MySQL查詢過程和高階查詢

情情說發表於2018-05-29

上一篇 提到,最近有個需求,要修改現有儲存結構,涉及查詢條件和查詢效率的考量,看了幾篇索引和HBase相關的文章,回憶了相關知識,結合專案需求,說說自己的理解和總結。

總體目錄如下,上篇介紹了前3小節,分析了索引為什麼快,總結了它的優點和分類,以及索引的演化過程,中篇會重點介紹索引分析方法和常見索引優化。

  • 為什麼需要索引
  • 索引的類別
  • MySQL索引演化
  • MySQL索引優化
  • HBase介紹
  • HBase儲存結構
  • HBase索引介紹
  • 業務需求及設計

部分內容摘錄了幾個博友的文章,最後會給出文章連結,感謝他們的精彩分析。

通過中篇的介紹,你會了解到:

  • MySQL查詢過程
  • 高階查詢相關概念
  • explain命令詳細介紹
  • 索引優化建議

MySQL查詢過程

想要更好的優化查詢,首先要了解其整體查詢過程,從客戶端傳送查詢請求,到接收到查詢結果,MySQL伺服器做了很多工作。

邏輯架構

MySQL邏輯架構整體分為三層,分別為客戶端層、核心服務層、儲存引擎層,共同協作完成。

MySQL邏輯架構

最上層為客戶端層,比如:連線處理、授權認證、安全等功能等。

中間層是MySQL的核心服務,包括查詢解析、分析、優化、快取、內建函式(比如:時間、數學、加密等),另外,所有的跨儲存引擎的功能也在這一層實現:儲存過程、觸發器、檢視等。

最下層為儲存引擎,負責資料儲存和提取,中間的服務層通過API與儲存引擎通訊,這些API介面遮蔽了不同儲存引擎間的差異。

具體執行過程

重點看下MySQL是如何優化和執行查詢的,很多的查詢優化工作就是遵循一些原則讓MySQL的優化器能夠按照預想的方式執行而已。

查詢具體過程

先說下總體流程:

  • 客戶端傳送一條查詢SQL給伺服器;
  • 伺服器先檢查查詢快取,如果命中了快取,則立即返回儲存在快取中的結果;
  • 伺服器端進行SQL解析、預處理,再由優化器生成對應的執行計劃;
  • 查詢執行引擎根據優化器生成的執行計劃,呼叫儲存引擎的API來執行查詢;
  • 將結果返回給客戶端;

1.客戶端/服務端通訊協議

MySQL客戶端和伺服器之間的通訊協議是「半雙工」:在任何一個時刻,要麼由伺服器向客戶端傳送資料,要麼由客戶端向伺服器傳送資料,不能同時發生,這也就意味著沒法進行流量控制。

客戶端用一個單獨的資料包將查詢請求傳送給伺服器,伺服器響應給使用者的資料通常會很多,由多個資料包組成,需要注意的是當伺服器響應客戶端請求時,客戶端必須完整的接收整個返回結果,而不能簡單的只取前面幾條結果,然後讓伺服器停止傳送。

2.查詢快取

如果查詢快取是開啟的,會檢查這個查詢語句是否命中查詢快取中的資料,如果命中,在檢查一次使用者許可權後直接返回快取中的結果。

查詢快取系統會跟蹤查詢中涉及的每個表,在任何的寫操作時,MySQL必須將對應表的所有快取都設定為失效,如果查詢快取非常大或者碎片很多,這個操作就可能帶來很大的系統消耗。

另外,任何的查詢語句在開始之前都必須經過檢查,即使這條SQL語句永遠不會命中快取,如果查詢結果可以被快取,那麼執行完成後,會將結果存入快取,也會帶來額外的系統消耗。

所以,開啟快取要慎重,只有當快取帶來的資源節約大於其本身消耗的資源時,才會給系統帶來效能提升,可以將query_cache_type設定為DEMAND,這時只有加入SQL_CACHE的查詢才會走快取,其他查詢則不會。

3.語法解析和預處理

通過關鍵字將SQL語句進行解析,生成一顆解析樹,預處理則會根據MySQL規則進一步檢查解析樹是否合法。

4.查詢優化

一條查詢可以有很多種執行方式,優化器的作用就是找到這其中最好的執行計劃,MySQL使用基於成本的優化器,它嘗試預測一個查詢使用某種執行計劃時的成本,並選擇其中成本最小的一個。

5.查詢執行引擎

儲存引擎介面提供了非常豐富的功能,但其底層僅有幾十個介面,這些介面像搭積木一樣完成了一次查詢的大部分操作。

6.返回結果給客戶端

結果集返回客戶端是一個增量且逐步返回的過程,這樣服務端就無須儲存太多結果而消耗過多記憶體,也可以讓客戶端第一時間獲得返回結果。

SELECT執行順序

下面來看看SQL查詢語句的執行順序,每一步都會生成一個虛擬臨時表,作為下一步的輸入。

標準的SQL語法如下:

SELECT DISTINCT    <
select_list >
FROM <
left_table >
<
join_type >
JOIN <
right_table >
ON <
join_condition >
WHERE <
where_condition >
GROUP BY <
group_by_list >
HAVING <
having_condition >
ORDER BY <
order_by_condition >
LIMIT <
limit_number >
複製程式碼

但執行順序是這樣的:

FROM     <
left_table>
ON <
join_condition>
<
join_type>
JOIN <
right_table>
WHERE <
where_condition>
GROUP BY <
group_by_list>
HAVING <
having_condition>
SELECT DISTINCT <
select_list>
ORDER BY <
order_by_condition>
LIMIT <
limit_number>
複製程式碼

1.FROM

當涉及多個表的時候,左邊表的輸出會作為右邊表的輸入,之後會生成一個虛擬表VT1:

  • 計算兩個相關聯表的笛卡爾積(CROSS JOIN) ,生成虛擬表VT1-J1;
  • 基於虛擬表VT1-J1進行過濾,過濾出所有滿足ON謂詞條件的行,生成虛擬表VT1-J2;
  • 如果使用了外連線(LEFT,RIGHT,FULL),主表(保留表)中的不符合ON條件的列也會被加入到VT1-J2中,生成虛擬表VT1-J3;

2.WHERE

對VT1過程中生成的臨時表進行過濾,滿足WHERE子句的列被插入到VT2表中:

  • 與ON的區別:如果有外連線,ON針對過濾的是關聯表,主表會返回所有的列,如果沒有外連線,效果相同;
  • 對主表的過濾應該放在WHERE;
  • 於關聯表,先條件查詢後連線則用ON,先連線後條件查詢則用WHERE;

3.GROUP BY

這個子句會把VT2中生成的表按照GROUP BY中的列進行分組,生成VT3表:

  • 其後處理過程的語句,如SELECT,HAVING,所用到的列必須包含在GROUP BY中,對於沒有出現的,得用聚合函式;

4.HAVING

對VT3表中的不同的組進行過濾,只用於分組後的資料,滿足HAVING條件的子句被加入到VT4表中。

5.SELECT

這個子句對SELECT子句中的元素進行處理,生成VT5表:

  • 計算SELECT子句中的表示式,生成VT5-J1;
  • DISTINCT:尋找重複列,並刪掉,會建立一張記憶體臨時表VT5-J2,和虛擬表VT5-J1一樣,不同的是對DISTINCT的列增加唯一索引,以此來除重複資料;

6.ORDER BY

從VT5-J2中的表中,根據ORDER BY 子句的條件對結果進行排序,生成VT6表,這是唯一可使用SELECT中別名的地方。

7.LIMIT

從上一步得到的VT6虛擬表中選出從指定位置開始的指定行資料。

高階查詢相關概念

本小節介紹下常用的高階查詢概念。

連線查詢

將多張表按照某個指定的條件進行資料拼接,SQL中將連線查詢分成四類: 內連線、外連線、自然連線、交叉連線,其中自然連線和交叉連線很少用到,就不過多介紹了。

1.內連線 inner join

從左表中取出每一條記錄,分別與右表中所有的記錄進行匹配,匹配必須左表和右表中都滿足條件,匹配的會保留結果,否則不保留。

2.外連線 left/right join

外連線分為兩種:

  • left join: 左外連線(左連線),以左表為主表
  • right join: 右外連線(右連線),以右表為主表

以某張表為主,取出裡面的所有記錄,不管能不能匹配上條件,主表最終都會保留,然後與另外一張表進行連線,如果不能匹配,其他表的欄位都置空NULL。

子查詢

是在某個查詢結果之上再進行查詢,也就是一條select語句內部包含了另外一條select語句。

按子查詢所在位置,可以劃分為:

  • From子查詢:子查詢跟在from之後;
  • Where子查詢: 子查詢出where條件中;
  • exists子查詢: 子查詢出現在exists裡面;

下面舉幾個例子:

查詢部門名稱字首為「小米」的所有員工:

 SELECT name , sex ,  sal        FROM emp        WHERE no in (             SELECT no FROM dept                 WHERE name LIKE '小米%'        );複製程式碼

檢視所有員工的薪水,並按薪水排序:

SELECT name , sal       FROM (           SELECT name , sal               FROM emp ORDER BY sal       );複製程式碼
聯合查詢

將多次查詢, 將結果進行拼接,欄位不會增加,每一條select語句獲取的欄位數必須嚴格一致。

語法如下:

Select 語句1Union [union選項]Select語句2...複製程式碼

Union選項:

  • All: 保留所有;
  • Distinct: 去重,預設選項;

又寫多了,再加一篇吧,中篇未完待續。。。

參考文章:

  1. MySQL優化原理
  2. 步步深入:SQL解析順序

歡迎掃描下方二維碼,關注我的個人微信公眾號,檢視更多文章 ~

情情說

來源:https://juejin.im/post/5b0c4f0b518825158160ac21

相關文章