上一篇 提到,最近有個需求,要修改現有儲存結構,涉及查詢條件和查詢效率的考量,看了幾篇索引和HBase相關的文章,回憶了相關知識,結合專案需求,說說自己的理解和總結。
總體目錄如下,上篇介紹了前3小節,分析了索引為什麼快,總結了它的優點和分類,以及索引的演化過程,中篇會重點介紹索引分析方法和常見索引優化。
- 為什麼需要索引
- 索引的類別
- MySQL索引演化
- MySQL索引優化
- HBase介紹
- HBase儲存結構
- HBase索引介紹
- 業務需求及設計
部分內容摘錄了幾個博友的文章,最後會給出文章連結,感謝他們的精彩分析。
通過中篇的介紹,你會了解到:
- MySQL查詢過程
- 高階查詢相關概念
- explain命令詳細介紹
- 索引優化建議
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: 去重,預設選項;
又寫多了,再加一篇吧,中篇未完待續。。。
參考文章:
歡迎掃描下方二維碼,關注我的個人微信公眾號,檢視更多文章 ~