為什么查詢速度會(huì)慢

查詢的生命周期: 客戶端->服務(wù)器->在服務(wù)器上進(jìn)行解析, 生成執(zhí)行計(jì)劃->執(zhí)行, 并返回結(jié)果給客戶端

查詢花費(fèi)時(shí)間的地方: 網(wǎng)絡(luò), CPU計(jì)算, 生成統(tǒng)計(jì)信息和執(zhí)行計(jì)劃, 鎖(互斥等待)等待, 尤其是想底層存儲(chǔ)引擎檢索數(shù)據(jù)的調(diào)用操作, 這些調(diào)用需要在內(nèi)存操作, CPU操作和內(nèi)存不足時(shí)導(dǎo)致的I/O操作上消耗時(shí)間; 根據(jù)存儲(chǔ)引擎不同, 可能還會(huì)產(chǎn)生大量的上下文切換以及系統(tǒng)調(diào)用.

慢查詢基礎(chǔ): 優(yōu)化數(shù)據(jù)訪問

1. 確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù). 這通常意味著訪問了太多的行, 也可能是訪問了太多的列.
2. 確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行.

是否向數(shù)據(jù)庫(kù)請(qǐng)求了不需要的數(shù)據(jù)

1. 查詢不需要的記錄. 好的方案是: 加上limit
2. 多表關(guān)聯(lián)時(shí)返回全部列. 避免 select *
3. 總是取出全部列. 有時(shí)候獲取全部列是為了增加復(fù)用性，比如緩存所有的數(shù)據(jù)，但要知道這樣做的代價(jià)
4. 總是查詢相同的數(shù)據(jù). 好的方案是: 當(dāng)初次查詢的時(shí)候?qū)⑦@個(gè)數(shù)據(jù)緩存起來(lái), 以后從緩存取.

MySQL是否在掃描額外的記錄

在確定查詢只返回需要的數(shù)據(jù)后, 接下來(lái)看查詢?yōu)榱朔祷亟Y(jié)果是否掃描了過多的數(shù)據(jù), 三個(gè)指標(biāo)如下:

• 響應(yīng)時(shí)間
• 掃描的行數(shù)
• 返回的行數(shù)

響應(yīng)時(shí)間

響應(yīng)時(shí)間 = 服務(wù)時(shí)間+ 排隊(duì)時(shí)間
服務(wù)時(shí)間: 數(shù)據(jù)庫(kù)處理這個(gè)查詢真正花了多長(zhǎng)時(shí)間.
排隊(duì)時(shí)間: 服務(wù)器因?yàn)榈却Y源而沒有真正執(zhí)行查詢的時(shí)間，一般最常見的等待是IO和鎖等待.
可以估算查詢的響應(yīng)時(shí)間: 了解這個(gè)查詢需要哪些索引以及它的執(zhí)行計(jì)劃是什么, 然后計(jì)算所需順序和隨機(jī)I/O的次數(shù), 乘以一次I/O的時(shí)間, 最后把這些消耗都加起來(lái)得到大概參考值, 來(lái)評(píng)估響應(yīng)時(shí)間是否合理.

掃描的函數(shù)和返回的行數(shù)

掃描的行數(shù)一般不同于返回的行數(shù), 例如做關(guān)聯(lián)查詢時(shí)服務(wù)器必須要掃描多行才能返回一行.

掃描的行數(shù)和訪問類型

掃描的行數(shù)比較:
全表掃描>掃描索引>范圍訪問>唯一索引查詢>常數(shù)引用
通過explain命令查看rows可以預(yù)估出掃描行數(shù):

image.png

刪除索引后, 變?yōu)槿頀呙?

image.png

? 從數(shù)據(jù)表中返回?cái)?shù)據(jù)，過濾不滿足條件的記錄（extra 出現(xiàn)了 Using Where）。這在Mysql服務(wù)器層完成的，先從數(shù)據(jù)表讀出記錄然后過濾

如果發(fā)現(xiàn)大量的數(shù)據(jù)但只返回少數(shù)的行，通?？梢試L試下面的技巧去優(yōu)化它：
? 使用索引覆蓋掃描，把所有需要用的列都放到索引中，這樣存儲(chǔ)引擎無(wú)須回表獲取對(duì)應(yīng)行
? 改變庫(kù)表結(jié)構(gòu)。例如使用單獨(dú)的匯總表
? 重寫這個(gè)復(fù)雜的查詢，讓MYSQL優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個(gè)查詢

重構(gòu)查詢的方式

一個(gè)復(fù)雜查詢還是多個(gè)簡(jiǎn)單查詢

目前很多公司都傾向于將復(fù)雜查詢切分成多個(gè)簡(jiǎn)單查詢.

切分查詢

刪除大量數(shù)據(jù), 如果用一條語(yǔ)句一次完成, 可能需要鎖住很多數(shù)據(jù) 占滿整個(gè)事務(wù)日志, 耗盡系統(tǒng)資源, 阻塞很多小的但重要的查詢. 用存儲(chǔ)過程分批刪除就好很多.

image.png

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

下面的例子:

image.png

看起來(lái)是將一條變?yōu)榱硕鄺l，且結(jié)果一致，但優(yōu)勢(shì)如下:
? 讓緩存的效率更高
? 將查詢分解后，執(zhí)行單個(gè)查詢可以減少鎖的競(jìng)爭(zhēng)
? 在應(yīng)用層做關(guān)聯(lián)，可以更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，更容易做到高性能和可擴(kuò)展
? 查詢本身效率有所提升
? 減少冗余記錄的查詢。在應(yīng)用層做關(guān)聯(lián)查詢，某條記錄只需查詢一次，而在數(shù)據(jù)庫(kù)中關(guān)聯(lián)查詢，則可能需要重復(fù)地訪問一部分?jǐn)?shù)據(jù)，重構(gòu)可能會(huì)減少網(wǎng)絡(luò)和內(nèi)存的消耗
? 在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)，而不是使用mysql的嵌套循環(huán)關(guān)聯(lián)，某些場(chǎng)景哈希關(guān)聯(lián)的效率高很多

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

image.png

MySQL客戶端/服務(wù)器通信協(xié)議

MySQL客戶端和服務(wù)器之間的通信協(xié)議是 "半雙工"的，即Mysql客戶端<-->服務(wù)器互相發(fā)送數(shù)據(jù)不能同時(shí)進(jìn)行.

此外, MySQL不支持流控, 這意味著:

1. 客戶端用一個(gè)單獨(dú)的數(shù)據(jù)包將查詢請(qǐng)求發(fā)送給服務(wù)器，所以當(dāng)查詢語(yǔ)句很長(zhǎng)的時(shí)候，需要設(shè)置 max_allowed_packet 參數(shù)。但是需要注意的是，如果查詢實(shí)在是太大，服務(wù)端會(huì)拒絕接收更多數(shù)據(jù)并拋出異常。
2. 相反的，服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會(huì)很多，由多個(gè)數(shù)據(jù)包組成。但是當(dāng)服務(wù)器響應(yīng)客戶端請(qǐng)求時(shí)，客戶端必須完整的接收整個(gè)返回結(jié)果，而不能簡(jiǎn)單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送。因而在實(shí)際開發(fā)中，盡量保持查詢簡(jiǎn)單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個(gè)非常好的習(xí)慣，這也是查詢中盡量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。

客戶端一般會(huì)緩存整個(gè)結(jié)果集, 也可以設(shè)置客戶端參數(shù)使其不緩存直接處理結(jié)果集, 這可以節(jié)省客戶端的內(nèi)存資源, 壞處是導(dǎo)致服務(wù)器端查詢資源不釋放. 一種解決方法是: 服務(wù)器端hint SQL_BUFFER_RESULT, 這樣服務(wù)器端的查詢結(jié)果會(huì)放到一個(gè)臨時(shí)表, 盡快釋放表鎖. 好處是這樣無(wú)須在客戶緩存結(jié)果,減少客戶端內(nèi)存消耗 ,還可以盡早釋放對(duì)應(yīng)表鎖, 壞處是服務(wù)器端將消耗更多的內(nèi)存.

查詢狀態(tài)

對(duì)每個(gè)MySQL連接或者說(shuō)一個(gè)線程, 任何時(shí)刻都有一個(gè)狀態(tài), 該狀態(tài)表示了MySQL目前在作甚嗎. 使用SHOW FULL PROCESSLIST命令可以查看當(dāng)前的狀態(tài)

查詢緩存

查詢中即使只有一個(gè)字節(jié)不同, 也不會(huì)匹配緩存結(jié)果, MySQL8.0已經(jīng)去除緩存.

查詢優(yōu)化處理

• 語(yǔ)法解析器和預(yù)處理
  通過關(guān)鍵字對(duì)sql語(yǔ)句進(jìn)行解析，生成一顆'解析樹'，mysql解析器開始驗(yàn)證關(guān)鍵字或順序是否正確, 語(yǔ)法是否正確等.
  預(yù)處理器會(huì)檢查數(shù)據(jù)表和列是否存在, 別名是否有歧義等.

• 查詢優(yōu)化器
  一條查詢可以有很多種執(zhí)行方式，最終都返回相同的結(jié)果，優(yōu)化器的作用就是找到最好的執(zhí)行計(jì)劃.
  MySQL使用基于成本的優(yōu)化器, 可以在查詢某條語(yǔ)句后, 通過Last_query_cost評(píng)估該查詢的成本.
  

  image.png

有很多原因?qū)е翸ySQL優(yōu)化器選擇錯(cuò)誤的執(zhí)行計(jì)劃:
? 統(tǒng)計(jì)信息不準(zhǔn)確。Mysql依賴存儲(chǔ)引擎提供信息來(lái)評(píng)估版本，但有的存儲(chǔ)引擎不準(zhǔn)確，比如 InnoDB因?yàn)槠趍vcc的架構(gòu)，并不能維護(hù)一個(gè)數(shù)據(jù)表的行數(shù)的精確統(tǒng)計(jì)信息
? 執(zhí)行計(jì)劃中的成本估算不等同于實(shí)際執(zhí)行的成本。比如 Mysql并不知道哪些頁(yè)面在內(nèi)存中，哪些在硬盤上
? Msyql認(rèn)為的最優(yōu)和我們不同，比如我們認(rèn)為的是時(shí)間最快，mysql可能選擇其他最優(yōu)策略
? msyql不考慮其他并發(fā)執(zhí)行的查詢
? Mysql不是任何時(shí)候都基于成本的優(yōu)化，有時(shí)也會(huì)基于固定的規(guī)則. 比如存在全文索引的MATCH（）子句，則存在全文索引就是用全文索引，即使其他索引更快，mysql還是會(huì)使用全文索引
? Mysql不會(huì)考慮不受其控制的操作成本，例如執(zhí)行存儲(chǔ)或者用戶自定義函數(shù)的成本
? 優(yōu)化器可能無(wú)法估算所有可能的執(zhí)行計(jì)劃，so 可能錯(cuò)過 實(shí)際上最優(yōu)的執(zhí)行計(jì)劃

優(yōu)化策略可以分為兩種: 動(dòng)態(tài)優(yōu)化和靜態(tài)優(yōu)化
靜態(tài)優(yōu)化不依賴于特別的數(shù)值，第一次完成后一直有效，及時(shí)不同的參數(shù)重復(fù)執(zhí)行查詢也不會(huì)發(fā)生變化，可以認(rèn)為是編譯時(shí)優(yōu)化
動(dòng)態(tài)優(yōu)化和查詢的上下文相關(guān)，也可能和和很多其他因素相關(guān)，例如where條件中的取值 索引中條目對(duì)應(yīng)的數(shù)據(jù)行數(shù)等等，每次查詢的時(shí)候重新評(píng)估，可以認(rèn)為這是運(yùn)行時(shí)優(yōu)化。
Mysql 對(duì)查詢的靜態(tài)優(yōu)化只做一次，動(dòng)態(tài)優(yōu)化每次執(zhí)行時(shí)都需要重新評(píng)估。

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

• 重新定義關(guān)聯(lián)表的順序
• 將外連接轉(zhuǎn)化成內(nèi)連接: 有時(shí)候外連接和內(nèi)連接是等價(jià)的, mysql可能會(huì)重寫查詢
• 使用等價(jià)變換規(guī)則: 簡(jiǎn)化并規(guī)范表達(dá)式, 如 5=5 and a>5 簡(jiǎn)寫為a>5
• 優(yōu)化count() min() max(): 比如求最小值，可以直接使用B-Tree數(shù)的最左側(cè)，最大就是最右側(cè)的數(shù)據(jù), 沒有where條件的count(*) 也可以使用存儲(chǔ)引擎提供的優(yōu)化，在EXPLAIN中 可以看到 select tables optimized away, 表示優(yōu)化器已經(jīng)從執(zhí)行計(jì)劃中移除了該表, 以一個(gè)常數(shù)取代
• 預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式: 當(dāng)mysql檢測(cè)到一個(gè)表達(dá)式可以轉(zhuǎn)化為常數(shù)的時(shí)候，就會(huì)一直把該表達(dá)式座位常數(shù)進(jìn)行優(yōu)化處理
• 覆蓋索引掃描 當(dāng)索引中的列包含我們需要的數(shù)據(jù)時(shí)，可以使用索引返回需要的數(shù)據(jù)，不用查詢對(duì)應(yīng)的數(shù)據(jù)行
• 提前終止查詢 當(dāng)已滿足查詢需求時(shí)，會(huì)提前終止查詢，比如limit 或者 id=-1 等這種條件

• 等值傳播: MySQL知道這里的WHERE子句不僅適用于film表, 而且對(duì)于film_actor表同樣適用
  image.png
  
  

  *列表 IN() 的比較: 別的數(shù)據(jù)庫(kù)中, IN 和 or 條件一樣, 但在msyql中, 會(huì)先將數(shù)據(jù)進(jìn)行排序, 通過二分查找的方式來(lái)確定列表的值是否滿足條件, 這是一個(gè)O(log n) 的復(fù)雜度操作

數(shù)據(jù)和索引的統(tǒng)計(jì)信息

在服務(wù)層有查詢優(yōu)化器, 缺沒有保存數(shù)據(jù)和索引的統(tǒng)計(jì)信息, 統(tǒng)計(jì)信息由存儲(chǔ)引擎實(shí)現(xiàn). 因此MySQL查詢優(yōu)化器在生成查詢的執(zhí)行計(jì)劃時(shí), 需要向存儲(chǔ)引擎獲取相應(yīng)的統(tǒng)計(jì)信息.

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

關(guān)聯(lián)策略：用一個(gè)表中的一條，依次去遍歷另外一張表的全部數(shù)據(jù)，如此循環(huán)，直到找到所有數(shù)據(jù)，叫嵌套循環(huán)關(guān)聯(lián)，如:

image.png

本質(zhì)上, MySQL對(duì)所有類型的查詢都是上述方式, 即使子查詢或者UNION也是:
MySQL在from子句中遇到子查詢時(shí), 先執(zhí)行子查詢并將其結(jié)果放到一個(gè)臨時(shí)表中, 然后將這個(gè)臨時(shí)表當(dāng)做一個(gè)普通表對(duì)的. UNION查詢也使用類似的臨時(shí)表. 右外連接, MySQL改寫為等價(jià)的左外連接.

執(zhí)行計(jì)劃

Mysql不會(huì)生成查詢字節(jié)碼來(lái)執(zhí)行查詢，Mysql生成查詢的一顆指令樹，通過執(zhí)行引擎完成這課指令書并返回結(jié)果。對(duì)某個(gè)查詢執(zhí)行 EXPLAIN EXTENDED后，再執(zhí)行SHOW WARNINGS就可以看到重構(gòu)出的查詢語(yǔ)句

image.png

關(guān)聯(lián)查詢優(yōu)化器

關(guān)聯(lián)查詢優(yōu)化器通過評(píng)估不同順序時(shí)的成本選擇一個(gè)代價(jià)最小的關(guān)聯(lián)順序，可以使用STRAIGHT_JOIN 關(guān)鍵字重寫查詢。當(dāng)關(guān)聯(lián)的表過多時(shí)，搜索空間的增持速度非?？欤瑑?yōu)化器不可能逐一評(píng)估每種關(guān)聯(lián)順序的成本，這叫做"貪婪"搜索方式,可能不是最優(yōu)的.

排序優(yōu)化

當(dāng)不能使用索引生成排序結(jié)果時(shí), mysql需要自己排序, 如果數(shù)據(jù)量小則在內(nèi)存中進(jìn)行, 數(shù)據(jù)量大就需要用磁盤. 不過這2種方式MySQL統(tǒng)一稱為文件排序(filesort).

更具體的, 如果所需排序的數(shù)據(jù)量小于"排序緩沖區(qū)", MySQL使用內(nèi)存進(jìn)行"快速排序". 若內(nèi)存不夠用, 則MySQL先將數(shù)據(jù)分塊, 對(duì)每個(gè)獨(dú)立的塊使用"快速排序", 然后各個(gè)塊的排序結(jié)果放到磁盤上, 然后對(duì)排好序的塊進(jìn)行合并(merge), 最后返回排序結(jié)果.

Mysql有如下兩種排序算法
? 兩次傳輸排序(舊版本使用)
讀取行指針和需要排序的字段，對(duì)其進(jìn)行排序，然后再根據(jù)排序結(jié)果讀取所需要的數(shù)據(jù)行，但可能產(chǎn)生大量隨機(jī)I/O,所以兩次數(shù)據(jù)傳輸?shù)某杀痉浅８?，?yōu)點(diǎn)是在排序的時(shí)候存儲(chǔ)盡可能少的數(shù)據(jù).
? 單次傳輸排序（新版本使用4.1后）
直接讀取查詢所需要所有列，再根據(jù)給定列進(jìn)行排序，直接返回結(jié)果，優(yōu)點(diǎn)是只需要一次順序I/O, 無(wú)須隨機(jī)IO, 缺點(diǎn)是如果需要的列很多，就會(huì)額外占用大量的空間.

關(guān)聯(lián)查詢時(shí)的排序, 若所有列都來(lái)自關(guān)聯(lián)的第一個(gè)表, 則效率會(huì)高些.