為什么查詢會慢？

響應(yīng)時間、掃描的行數(shù)、返回的行數(shù)，是衡量查詢開銷的三個指標(biāo)（記錄在慢日志中）

• 查詢了不需要的數(shù)據(jù)

  • 查詢不需要的記錄：比如查詢了100條記錄到應(yīng)用，取其中的10條顯示，最好的解決辦法是加上LIMIT
  • 多表關(guān)聯(lián)時返回全部列：應(yīng)該只取需要的列，否則會返回所有關(guān)聯(lián)表的所有記錄
  • 總是取出全部列：比如SELECT * FROM xxx
  • 重復(fù)查詢相同的數(shù)據(jù)：最好使用緩存

• 掃描了額外的記錄

  Explain語句中的type列的類型：

  • 全表掃描
  • 范圍掃描
  • 唯一索引查詢
  • 常數(shù)引用

  MySQL能夠使用三種方式應(yīng)用Where過濾條件，從好到壞依次為：

  • 在索引中使用Where條件來過濾不匹配的記錄。這是在儲存引擎層完成的
  • 使用覆蓋索引掃描（Using index）來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結(jié)果。這是在MySQL服務(wù)器層，但無須再回表查詢記錄
  • 從數(shù)據(jù)表中返回數(shù)據(jù)，然后過濾不滿足條件的記錄（Using where）。這是在服務(wù)器層完成，MySQL需要先從數(shù)據(jù)表讀出記錄然后過濾

怎么優(yōu)化查詢？

• 可以將一個復(fù)雜查詢改成多個簡單查詢

  因為MySQL從設(shè)計上讓連接和斷開都非常輕量級，加上現(xiàn)代網(wǎng)絡(luò)速度非常快，多個小查詢不成問題

• 切分查詢

  將一個大查詢，切分成小查詢，每個小查詢的功能完全一樣，每次只返回小部分查詢結(jié)果。比如刪除舊數(shù)據(jù)，如果用一個很大的語句一次性刪除大量數(shù)據(jù)，會鎖住很多數(shù)據(jù)、占滿事務(wù)日志、耗盡系統(tǒng)資源、阻塞很多重要的小查詢，可以切分成每次刪除1000條，循環(huán)刪除

• 分解關(guān)聯(lián)查詢

  • 讓緩存效率更高
  • 減少鎖的競爭
  • 更容易拆分?jǐn)?shù)據(jù)庫，方便擴(kuò)展
  • 提升查詢效率，比如用IN()代替關(guān)聯(lián)查詢
  • 減少冗余記錄的查詢

一條查詢是如何執(zhí)行的？

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

MySQL客戶端和服務(wù)器之間的通信協(xié)議是“半雙工”的，同一時刻，要么是由服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是由客戶端向服務(wù)器端發(fā)送數(shù)據(jù)，這兩個動作不能同時發(fā)生

2、服務(wù)器先檢查查詢緩存，如果命中緩存，則立即返回儲存在緩存中的結(jié)果。否則進(jìn)入下一階段

查詢的狀態(tài)

Sleep：線程正在等待客戶端發(fā)送新的請求

Query：線程正在查詢或者正在將結(jié)果發(fā)送給客戶端

Locked：服務(wù)器層等待標(biāo)鎖

Analyzing and statistics：線程正在收集存儲引擎的統(tǒng)計信息，并生成查詢的執(zhí)行計劃

Copying to tmp table [on disk]：將結(jié)果拷貝到一個臨時表，要么在做GROUP BY，要么文件排序、UNION表。如果后面還有“on disk”，表示MySQL正在將一個內(nèi)存臨時表放到磁盤上操作

Sorting result：線程正在對結(jié)果集進(jìn)行排序

Sending data：線程正在傳送數(shù)據(jù)

查詢緩存

解析SQL語句之前，優(yōu)先檢查查詢緩存，這是通過哈希查找實現(xiàn)的，如果命中緩存，那么在返回數(shù)據(jù)之前，還要檢查一次用戶權(quán)限

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

MySQL通過關(guān)鍵字對SQL語句進(jìn)行解析，生成“解析樹”，驗證是否關(guān)鍵字錯誤、語法錯誤、括號是否匹配，然后驗證權(quán)限

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

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

查詢優(yōu)化器

一條查詢可以有很多種執(zhí)行方式，最后都返回相同的結(jié)果，優(yōu)化器的作用就算找到這其中最好的執(zhí)行計劃

• 導(dǎo)致優(yōu)化器選擇錯誤的執(zhí)行計劃的原因

  • 統(tǒng)計信息不準(zhǔn)確

    MySQL依賴儲存引擎提供的統(tǒng)計信息來評估成本，但是儲存引擎提供的統(tǒng)計信息不一定準(zhǔn)確，比如InnoDB因為有MVCC的架構(gòu)，并不能維護(hù)一個數(shù)據(jù)表的行數(shù)精確統(tǒng)計信息

  • 執(zhí)行計劃中的成本估算不等同與實際的執(zhí)行成本

    MySQL層面并不知道那些頁面在內(nèi)存中，哪些在磁盤上，所以查詢實際執(zhí)行過程中到底需要多少次物理IO是無法得知的

  • MySQL并不考慮其他并發(fā)執(zhí)行的查詢

  • MySQL不會考慮不受其控制的操作的成本

• MySQL能夠處理的優(yōu)化類型

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

  • 將外連接轉(zhuǎn)化成內(nèi)連接

  • 使用等價變換規(guī)則

    比如 5=5 AND a > 5 會改寫成 a > 5

  • 優(yōu)化COUNT()、MIN()和MAX()

    比如查找最小值，如果列在B-Tree中，那只要把最左的值拿出來就可以了；最大值把最右值拿出來就可以了

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

    比如表達(dá)式轉(zhuǎn)化為常數(shù)，有時候甚至一個查詢也能轉(zhuǎn)化為一個常數(shù)

  • 覆蓋索引掃描

  • 子查詢優(yōu)化

  • 提前終止查詢

    發(fā)現(xiàn)已經(jīng)滿足查詢需求的時候，MySQL總是能夠立刻終止查詢。比如LIMIT

  • 等值傳播

    如果兩個列的值通過等式關(guān)聯(lián)，那么MySQL能夠把其中一個列的WHERE條件傳遞到另一個上。

  • 列表IN()的比較

    在很多數(shù)據(jù)系統(tǒng)中，IN()完全等同于多個OR條件的子句，因為這兩者完全等價，但是在MySQL中不是這樣的，MySQL將IN()列表中的數(shù)據(jù)先進(jìn)行排序然后通過二分查找的方式確定列表中的值是否滿足條件，這是一個O(logn)復(fù)雜度的查詢，等級轉(zhuǎn)換成OR是O(n)，所以如果IN()列表中有大量取值的時候，MySQL的處理速度回更快

MySQL如何執(zhí)行關(guān)聯(lián)查詢

按照where條件遍歷第一個表，然后循環(huán)查詢第二個表，使用on的篩選；
on的條件最好要是索引

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

一旦服務(wù)器處理完最后一個關(guān)聯(lián)表，開始生成第一條結(jié)果時，MySQL就可以開始向客戶端逐步返回結(jié)果集了；比如Java的rs.next()，一條一條取，避免服務(wù)器內(nèi)存壓力過大；