MYSQL（四）查詢性能優(yōu)化

優(yōu)化數(shù)據(jù)訪問

1.是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)

解決方式：

A. 查詢后加limit

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

2. 是否掃描了額外的數(shù)據(jù)

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

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

1. const 常數(shù)引用

如果是根據(jù)主鍵查詢，將會將查詢轉(zhuǎn)化為一個(gè)常數(shù)，只取出確定的一行數(shù)據(jù)。是最快的一種。

2. Ref

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

3. ref_eq 唯一索引查詢

ref_eq 與 ref相比，這種類型的查找結(jié)果集只有一個(gè)

4. range 范圍掃描

索引或主鍵，在某個(gè)范圍內(nèi)時(shí)

4. index 索引掃描

僅僅只有索引被掃描

5. all 全表掃描

一般mysql應(yīng)用where條件的方式由好到壞：

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

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

3. 從數(shù)據(jù)表中返回?cái)?shù)據(jù)，然后過濾不滿足條件的數(shù)據(jù)，在服務(wù)器層完成，mysql需要先從數(shù)據(jù)表讀出記錄然后過濾（在extra中出現(xiàn)using where）

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

執(zhí)行查詢的基礎(chǔ)：

    1. 客戶端發(fā)送一條查詢給服務(wù)器

    2. 服務(wù)器先檢查緩存，如果命中緩存，立刻返回結(jié)果

    3. 服務(wù)器進(jìn)行sql解析，預(yù)處理，再由優(yōu)化器生成對應(yīng)執(zhí)行計(jì)劃

    4. Mysql根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲引擎API執(zhí)行查詢計(jì)劃

    5. 將結(jié)果返回給客戶端

第一步（客戶端發(fā)送一條查詢給服務(wù)器）：

Mysql客戶端與服務(wù)器之間的通信是半雙工的，要么由服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么由客戶端向服務(wù)器發(fā)送數(shù)據(jù)，不能同時(shí)進(jìn)行；

所以為了進(jìn)行流量控制，客戶端發(fā)送查詢語句過長時(shí)，超過max_allowed_packet參數(shù)，服務(wù)器會拋出相應(yīng)錯(cuò)誤。

客戶端從服務(wù)器獲取數(shù)據(jù)時(shí)，多數(shù)連接mysql的庫函數(shù)都可以獲得全部結(jié)果集并緩存到內(nèi)存里，mysql需要等所有數(shù)據(jù)都發(fā)給客戶端才能釋放這條查詢所占用的資源；

第三步（服務(wù)器進(jìn)行sql解析、預(yù)處理、查詢優(yōu)化）：

首先，通過關(guān)鍵字將sql語句進(jìn)行解析，生成一顆“解析樹”；

解析器驗(yàn)證語法規(guī)則；

預(yù)處理器檢查解析樹是否合法，驗(yàn)證權(quán)限；

查詢優(yōu)化器使用優(yōu)化策略生成一個(gè)最優(yōu)的執(zhí)行計(jì)劃：

    1. 重新定義關(guān)聯(lián)表的順序

    2. 將外連接轉(zhuǎn)化為內(nèi)連接

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

    4. 預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式

    5. 提前終止查詢

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

生成一個(gè)執(zhí)行計(jì)劃——指令樹：因?yàn)閙ysql的關(guān)聯(lián)從一張表開始嵌套，所以執(zhí)行計(jì)劃是一顆左側(cè)深度優(yōu)先的樹。

第四步（調(diào)用存儲引擎API執(zhí)行查詢計(jì)劃）

查詢優(yōu)化器在服務(wù)器層，而統(tǒng)計(jì)信息（每個(gè)表或索引有多少頁，每個(gè)表的每個(gè)索引的基數(shù)是多少，數(shù)據(jù)行和索引長度，索引的分布信息等）在存儲引擎層；

MYSQL執(zhí)行關(guān)聯(lián)查詢方式：

Mysql認(rèn)為任何一次查詢都是一次關(guān)聯(lián)，并不僅僅一次查詢關(guān)系到兩張表時(shí)。

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

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

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

優(yōu)化：

選擇記錄數(shù)少的作為驅(qū)動表；

優(yōu)先優(yōu)化NestedLoop的內(nèi)層循環(huán)；

保證被驅(qū)動表上Join條件字段已經(jīng)被索引

Mysql查詢優(yōu)化器的局限性

1.關(guān)聯(lián)子查詢

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

//未完

2. 最大值和最小值

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

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

因?yàn)閒irst_name字段上沒有索引，所以mysql會進(jìn)行一次全表掃描；

一個(gè)優(yōu)化辦法是：（使mysql進(jìn)行主鍵掃描）

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

用主建索引查詢，因?yàn)閎-tree是按照主鍵順序排序，所以limit 1 = min（actor_id)，查找索引直到復(fù)合where條件的第一條數(shù)據(jù)