MYSQL（四）查詢性能優化

優化數據訪問

1.是否向數據庫請求了不需要的數據

解決方式：

A. 查詢后加limit

B. Select后寫需要的列而不是*

2. 是否掃描了額外的數據

數據庫的訪問方式速度由慢到快：全表掃描，索引掃描，范圍掃描，唯一索引查詢，常數引用

MYSQL Explain命令 的type(數據庫引擎訪問表的方式)：Const > ref > range > index > all

1. const 常數引用

如果是根據主鍵查詢，將會將查詢轉化為一個常數，只取出確定的一行數據。是最快的一種。

2. Ref

查找條件列使用了索引而且不為主鍵和unique（值允許重復），只取出確定值的數據，可能多行。

3. ref_eq 唯一索引查詢

ref_eq 與 ref相比，這種類型的查找結果集只有一個

4. range 范圍掃描

索引或主鍵，在某個范圍內時

4. index 索引掃描

僅僅只有索引被掃描

5. all 全表掃描

一般mysql應用where條件的方式由好到壞：

1. 在索引中使用where條件過濾，這是在存儲引擎層完成；

2. 使用索引覆蓋掃描，直接從索引中過濾不需要的數據并返回結果，這是在mysql服務器層完成，無需再回表查詢（在extra中出現using index）

3. 從數據表中返回數據，然后過濾不滿足條件的數據，在服務器層完成，mysql需要先從數據表讀出記錄然后過濾（在extra中出現using where）

好的索引可以讓查詢使用合適的訪問類型，減少掃描的數據行數。

執行查詢的基礎：

    1. 客戶端發送一條查詢給服務器

    2. 服務器先檢查緩存，如果命中緩存，立刻返回結果

    3. 服務器進行sql解析，預處理，再由優化器生成對應執行計劃

    4. Mysql根據優化器生成的執行計劃，調用存儲引擎API執行查詢計劃

    5. 將結果返回給客戶端

第一步（客戶端發送一條查詢給服務器）：

Mysql客戶端與服務器之間的通信是半雙工的，要么由服務器向客戶端發送數據，要么由客戶端向服務器發送數據，不能同時進行；

所以為了進行流量控制，客戶端發送查詢語句過長時，超過max_allowed_packet參數，服務器會拋出相應錯誤。

客戶端從服務器獲取數據時，多數連接mysql的庫函數都可以獲得全部結果集并緩存到內存里，mysql需要等所有數據都發給客戶端才能釋放這條查詢所占用的資源；

第三步（服務器進行sql解析、預處理、查詢優化）：

首先，通過關鍵字將sql語句進行解析，生成一顆“解析樹”；

解析器驗證語法規則；

預處理器檢查解析樹是否合法，驗證權限；

查詢優化器使用優化策略生成一個最優的執行計劃：

    1. 重新定義關聯表的順序

    2. 將外連接轉化為內連接

    3. 優化count(),min(),max()（根據b-tree只讀取第一條或最后一條數據）

    4. 預估并轉化為常數表達式

    5. 提前終止查詢

    6. 列表in()的比較（將in列表的數據先排序，通過二分查找確定值是否滿足條件）

生成一個執行計劃——指令樹：因為mysql的關聯從一張表開始嵌套，所以執行計劃是一顆左側深度優先的樹。

第四步（調用存儲引擎API執行查詢計劃）

查詢優化器在服務器層，而統計信息（每個表或索引有多少頁，每個表的每個索引的基數是多少，數據行和索引長度，索引的分布信息等）在存儲引擎層；

MYSQL執行關聯查詢方式：

Mysql認為任何一次查詢都是一次關聯，并不僅僅一次查詢關系到兩張表時。

在MySQL 中，只有一種 Join 算法，就是 Nested Loop Join嵌套迭代。

Simple Nested-Loop Join簡單嵌套循環：從驅動表中取出R1匹配S表所有列，然后R2，R3,直到將R表中的所有數據匹配完，然后合并數據，可以看到這種算法要對S表進行RN次訪問，雖然簡單，但是相對來說開銷還是太大了。

Index Nested-Loop Join索引嵌套循環：由于非驅動表上有索引，所以比較的時候不再需要一條條記錄進行比較，而可以通過索引來減少比較，從而加速查詢。

優化：

選擇記錄數少的作為驅動表；

優先優化NestedLoop的內層循環；

保證被驅動表上Join條件字段已經被索引

Mysql查詢優化器的局限性

1.關聯子查詢

使用in加子查詢，性能非常糟糕

//未完

2. 最大值和最小值

對于max()和min()查詢，mysql的優化并不好，如：

Select min(actor_id) from sakila.actor where first_name = “pene”;

因為first_name字段上沒有索引，所以mysql會進行一次全表掃描；

一個優化辦法是：（使mysql進行主鍵掃描）

select actor_id from sakila.actor use index(primary) where first_name = “pene” limit 1;

用主建索引查詢，因為b-tree是按照主鍵順序排序，所以limit 1 = min（actor_id)，查找索引直到復合where條件的第一條數據