1. 查詢的過(guò)程
- 查詢的生命周期: 從客戶端, 到服務(wù)器, 然后在服務(wù)器上進(jìn)行解析, 生成執(zhí)行計(jì)劃, 執(zhí)行, 返回結(jié)果給客戶端.
- 耗時(shí)的地方: 網(wǎng)絡(luò), CPU計(jì)算, 生成統(tǒng)計(jì)信息和執(zhí)行計(jì)劃, 鎖互等(互斥等待).
2. 優(yōu)化數(shù)據(jù)訪問(wèn)
- 訪問(wèn)的數(shù)據(jù)太多是低性能查詢的根源.
- 確認(rèn)應(yīng)用是否在檢索大量超過(guò)需要的數(shù)據(jù). 這可能是訪問(wèn)了太多的行或列.
- 確認(rèn)服務(wù)器是否在分析大量超過(guò)需求的數(shù)據(jù)行.
2.1 是否向數(shù)據(jù)庫(kù)請(qǐng)求了不需要的數(shù)據(jù)
- 查詢不需要的數(shù)據(jù).
- 典型情況: 先使用select 查詢大量的結(jié)果,然后獲取前面的N行后關(guān)閉結(jié)果集. 最好使用limit.
- 多表關(guān)聯(lián)時(shí)返回全部列.
- 總是取出全部列.
- 這會(huì)讓優(yōu)化器無(wú)法完成索引覆蓋查詢掃描優(yōu)化, 并帶來(lái)額外的I/O,CPU和內(nèi)存消耗.
- 重復(fù)查詢相同的數(shù)據(jù).
2.2 Mysql 是否在掃描額外的記錄
- 衡量查詢開(kāi)銷(xiāo)的三個(gè)指標(biāo)
- 響應(yīng)時(shí)間.
- 掃描的行數(shù).
- 返回的行數(shù).
- 響應(yīng)時(shí)間
- 等于服務(wù)時(shí)間和排隊(duì)時(shí)間(等待I/O和鎖)的和.
- 在得到一個(gè)響應(yīng)時(shí)間的值后, 先懷疑其?合理性.
- 掃描的行數(shù)和返回的行數(shù)
- 它說(shuō)明了查詢找到需要的數(shù)據(jù)的效率.
- 掃描的行數(shù)和訪問(wèn)類(lèi)型
- 從表中找到某一行數(shù)據(jù)的成本(對(duì)掃描的需求).
- explain 中的type 列反應(yīng)了訪問(wèn)類(lèi)型.
- 應(yīng)用where 條件的三種方式, 從好到壞:
- 在索引中使用where 過(guò)濾. 這在存儲(chǔ)引擎層完成.
- 使用索引覆蓋掃描來(lái)返回記錄. 在服務(wù)器層完成, 無(wú)需回表查詢.
- 從數(shù)據(jù)表返回?cái)?shù)據(jù),然后進(jìn)行過(guò)濾. 服務(wù)器層完成.
3. 查詢的執(zhí)行
3.1 客戶端/服務(wù)器通信協(xié)議
- "半雙工的": 同一時(shí)刻只能發(fā)生一個(gè)方向的數(shù)據(jù)發(fā)送.
- 優(yōu)勢(shì)是簡(jiǎn)單快速
- 限制: 無(wú)法進(jìn)行流量控制.
- 客戶端用一個(gè)單獨(dú)的數(shù)據(jù)包將查詢傳遞給服務(wù)器, 接著就只能等待結(jié)果, 且必須完整的接收整個(gè)返回結(jié)果. 而不能在中途停止數(shù)據(jù)接收.
- 所以, max_allowed_packet 參數(shù), 以及查詢語(yǔ)句中的limit 限制是特別重要的.
- Mysql 要等所有的數(shù)據(jù)都已經(jīng)發(fā)送給客戶端時(shí)才能釋放這條查詢鎖占用的資源.
-
接收全部結(jié)果并緩存- 能夠讓查詢?cè)琰c(diǎn)結(jié)束, 減少服務(wù)器壓力.
- 但當(dāng)結(jié)果集很大時(shí),會(huì)耗費(fèi)更多的時(shí)間和內(nèi)存.
-
盡早開(kāi)始處理結(jié)果集, 逐行獲取需要的數(shù)據(jù)- 節(jié)省庫(kù)函數(shù)處理查詢所需的內(nèi)存和時(shí)間.
- 缺陷是會(huì)在和客戶端交互的整個(gè)過(guò)程中占用服務(wù)器資源.
-
- Mysql 連接(線程) 的狀態(tài)
- sleep, query, locked, analyzing and statistics, coping to tem table, sorting table, sending data.
3.2 查詢緩存
- 在解析查詢語(yǔ)句之前, 如果查詢緩存是打開(kāi)的, Mysql 會(huì)優(yōu)先檢查該查詢是否命中了查詢緩存中的數(shù)據(jù).
- 檢查是通過(guò)一個(gè)對(duì)大小寫(xiě)敏感的哈希查找實(shí)現(xiàn)的.
- 若有命中, 再檢查用戶權(quán)限.
- 如果都沒(méi)問(wèn)題, 跳過(guò)后續(xù)階段, 直接從緩存中拿結(jié)果并返回給客戶端.
3.3 查詢優(yōu)化處理
- 過(guò)程: 將SQL 轉(zhuǎn)換成一個(gè)執(zhí)行計(jì)劃, 再依照該執(zhí)行計(jì)劃和存儲(chǔ)引擎進(jìn)行交互.
- 子階段: 解析SQL, 預(yù)處理, 優(yōu)化SQL 執(zhí)行計(jì)劃.
- 過(guò)程中任何錯(cuò)誤都可能終止查詢.
- 語(yǔ)法解析器和預(yù)處理
- 解析器生成一顆"解析樹(shù)".
- 預(yù)處理器根據(jù)Mysql 規(guī)則進(jìn)一步檢查解析樹(shù)是否合法.
- 查詢優(yōu)化器.
一個(gè)查詢有多種執(zhí)行方式, 最后都返回相同的結(jié)果.
優(yōu)化器會(huì)從中找出最好的執(zhí)行計(jì)劃.
-
Mysql 使用基于成本的優(yōu)化器. 評(píng)估成本時(shí)不考慮任何層面上的緩存.
- 導(dǎo)致優(yōu)化器選擇錯(cuò)誤的原因:
統(tǒng)計(jì)信息不準(zhǔn)確.-
成本估算不等于實(shí)際的執(zhí)行成本(訪問(wèn)數(shù)據(jù)的成本不同). 有時(shí)無(wú)法估算所有可能的執(zhí)行計(jì)劃.-
不會(huì)考慮不受其控制的操作的成本. 如執(zhí)行存儲(chǔ)過(guò)程和用戶自定義函數(shù)的成本. -
有時(shí)是基于固定的規(guī)則而非成本進(jìn)行的優(yōu)化.如存在Match()就一定會(huì)使用全文索引,即使別的索引會(huì)更快. -
Mysql 的最優(yōu)可能不是我們想要的最優(yōu). 我們想要的是最快的,而Mysql 只是基于成本上的最優(yōu).
- 導(dǎo)致優(yōu)化器選擇錯(cuò)誤的原因:
-
Mysql 能夠處理的優(yōu)化類(lèi)型
- 重新定義關(guān)聯(lián)表的順序.
- 將外連接轉(zhuǎn)換為內(nèi)連接.
- 使用等價(jià)變化規(guī)則.
- 優(yōu)化Count, Min, Max 函數(shù).
- 預(yù)估并轉(zhuǎn)換為常數(shù)表達(dá)式.
- 覆蓋索引掃描.
- 子查詢優(yōu)化.
- 提前終止查詢.
- 等值傳播.
- 列表IN() 的比較.
- 多數(shù)數(shù)據(jù)庫(kù)的In() 完全等同于多個(gè)OR 條件的子句, 其復(fù)雜度為O(n).
- Mysql 會(huì)先排序,再二分查找確定列表值是否滿足條件. 復(fù)雜度為O(log n).
?
大多數(shù)情況下, 讓優(yōu)化器按自己的方式工作. 除非發(fā)現(xiàn)它進(jìn)行了錯(cuò)誤的優(yōu)化, 并且知道原因時(shí),再進(jìn)行手工干預(yù).-
數(shù)據(jù)和索引的統(tǒng)計(jì)信息.
優(yōu)化器存在于服務(wù)層, 而統(tǒng)計(jì)信息是由存儲(chǔ)引擎構(gòu)建并傳遞給優(yōu)化器的.
-
Mysql 的關(guān)聯(lián)查詢.
- Mysql 的概念中, 每個(gè)查詢都是一次關(guān)聯(lián).
- 會(huì)將一系列的單個(gè)查詢結(jié)果放入一個(gè)臨時(shí)表中,然后執(zhí)行"嵌套循環(huán)關(guān)聯(lián)" .
select t1.c1, t2.c2 from t1 inner join t2 Using (c3) where ...; outer_iter = iterator over t1 where... outer_row = outer_iter.next while outer_row inner_iter = iterator oever t2 where .... inner_row = inner_iter.next while inner_row .... inner_row = inner_iter.next end outer_row = outer_iter.next end
* 本質(zhì)上, 所有類(lèi)型的查詢都以此類(lèi)方式運(yùn)行.
* `但全外連接是個(gè)例外, 因?yàn)樗鼰o(wú)法通過(guò)嵌套循環(huán)和回溯的方式完成. 所以Mysql 并不執(zhí)行全外連接.`
* full join :表中數(shù)據(jù)=內(nèi)連接+左邊缺失數(shù)據(jù)+右邊缺失數(shù)據(jù).
* 執(zhí)行計(jì)劃
* Mysql 并不會(huì)生成查詢字節(jié)碼來(lái)執(zhí)行查詢, 而是生成查詢的一顆指令樹(shù), 然后通過(guò)存儲(chǔ)引擎執(zhí)行完成這顆指令數(shù)并返回結(jié)果.
* 最終的執(zhí)行計(jì)劃, 包含了重構(gòu)查詢的所有信息.
* 關(guān)聯(lián)查詢優(yōu)化器
* 由于Mysql 嵌套循環(huán)方式的關(guān)聯(lián)查詢執(zhí)行方式, 所以關(guān)聯(lián)順序變得非常重要.
* 關(guān)聯(lián)優(yōu)化器會(huì)嘗試在所有的關(guān)聯(lián)順序中選擇一個(gè)成本最小的執(zhí)行.
* 但是, 當(dāng)管理表過(guò)多時(shí), 只能使用"貪婪" 搜索方式查找最優(yōu)值.
* 當(dāng)10個(gè)表進(jìn)行關(guān)聯(lián)時(shí),. 一共有3628800種不同的關(guān)聯(lián)順序.
* 排序優(yōu)化
* 當(dāng)無(wú)法使用索引進(jìn)行排序時(shí), Mysql 需要進(jìn)行排序, 成為文件排序(可能在內(nèi)存,硬盤(pán)中進(jìn)行,根據(jù)數(shù)據(jù)量大小).
* 內(nèi)存不夠時(shí), 將數(shù)據(jù)分塊, 對(duì)每個(gè)塊使用"快速排序"并將結(jié)果存放在磁盤(pán)上, 最后進(jìn)行merge.
* 兩種排序算法.
* 兩次傳輸排序(舊版本)
* 讀取行指針和需要排序的字段, 對(duì)其進(jìn)行排序. 然后再更加排序結(jié)果讀取所需要的數(shù)據(jù)行.
* 單次傳輸排序(新版本)
* 先讀取查詢需要的所有列, 然后再根據(jù)給定列進(jìn)行排序, 最后直接返回排序結(jié)果.
* `Mysql 進(jìn)行文件排序時(shí), 所需要使用的臨時(shí)存儲(chǔ)空間可能很大. 因?其對(duì)每個(gè)排序記錄都會(huì)分配一個(gè)足夠長(zhǎng)的定長(zhǎng)空間存放.`
* 查詢執(zhí)行引擎
* 經(jīng)過(guò)了解析和優(yōu)化階段, 會(huì)生成執(zhí)行計(jì)劃(數(shù)據(jù)結(jié)構(gòu)而非字節(jié)碼). 查詢執(zhí)行引擎則根據(jù)這個(gè)執(zhí)行計(jì)劃來(lái)完成整個(gè)查詢.
* 過(guò)程: 簡(jiǎn)單地根據(jù)執(zhí)行計(jì)劃給出的指令逐步執(zhí)行.
* 返回結(jié)果給客戶端
* 即使沒(méi)有結(jié)果數(shù)據(jù), 也會(huì)返回一些查詢信息,如影響的行數(shù).
* 增量, 逐步返回的過(guò)程. ?開(kāi)始生成第一條結(jié)果時(shí),就逐步地進(jìn)行返回.
* 好處: 服務(wù)器無(wú)需存儲(chǔ)太多結(jié)果. 讓客戶端盡快的得到了結(jié)果.
## 4. Mysql 查詢優(yōu)化器的局限性
### 4.1 關(guān)聯(lián)子查詢
* Mysql 的子查詢實(shí)現(xiàn)的非常糟糕.
* 其中, 最差的是where 條件中包含IN() 的子查詢語(yǔ)句.
select * from people
where city_id IN(
select city_id from country where province = 'zhejiang')
* 直覺(jué)上最優(yōu)的執(zhí)行方式:
Step1 : select city_id from country where name = 'shanghai'
Result : 4,5,6,7,8,9
Step2: select * from people where city_id IN (4,5,6,7,8,9).
* Mysql 的做法: 將相關(guān)的外層表壓到子查詢中.
select * from people
where Exists(
select city_id from country where province = 'zhejiang'
Ande people.city_id = country.city_id)
* 這會(huì)造成Mysql 無(wú)法先執(zhí)行子查詢. 而是先對(duì)people 執(zhí)行全表掃描, 然后根據(jù)返回的city 行集合, 逐行執(zhí)行子查詢.
* 改造
select * from people
Inner join country Using(city_id)
where province = 'zhejiang')
#### 4.2 Union 的限制
* Mysql 的限制: 無(wú)法將限制條件(如limit) 從外層"下推"到內(nèi)層, 使得原本能夠限制部分返回結(jié)果的條件無(wú)法應(yīng)用到內(nèi)層查詢的優(yōu)化上.
` (select * from t1) Union All (select * from t2) limit 20 `
* 執(zhí)行過(guò)程: 首先將t1 和t2 所有符合條件的結(jié)果存放在臨時(shí)表中, 讓其年后從臨時(shí)表中取出前20條.
` (select * from t1 limit 20) Union All (select * from t2 limit 20) limit 20.`
* 直接在篩選t1,t2 的結(jié)果時(shí), 只取前20 條到臨時(shí)表中.
#### 4.3 在同一表上查詢和更新
* Mysql 的限制: 不允許對(duì)同一張表同時(shí)進(jìn)行查詢和更新.
` Update tbl as outer_tbl set
cnt = (select count(*) from tbl as inner_tbl where outer_tbl.type = inner_tbl.type);`
* 該條Sql 是無(wú)法執(zhí)行的, 適用生成表的形式來(lái)繞過(guò)上面的限制:
` Update tbl Inner join
( select type, count(*) as cnt from tbl group by type) as der Using(type)
set tbl.cnt =der.cnt;`
#### 4.4 最大值和最小值優(yōu)化
* Mysql 對(duì)Min() 和Max() 查詢?并沒(méi)有做很好的優(yōu)化.
` select Min(id) from people where name ='haha' `
* 由于name 字段上并沒(méi)有索引, 因此會(huì)進(jìn)行一次全表掃描.
* 其實(shí), 進(jìn)行一次主鍵掃描, 當(dāng)找到第一個(gè)name為haha 的記錄其實(shí)就是正確的最小值.
` select id from people use Index(primary) where name = 'haha' limit 1`
* 缺點(diǎn)是通過(guò)SQL, 并不能看出其本意是取最小值.
#### 4.5 松散索引掃描
* 類(lèi)似于Oracle 中的skip index scan.
* Mysql 并不支持松散索引掃描, 也就無(wú)法按照不連續(xù)的方式掃描一個(gè)索引.
* 索引掃描需要先定義個(gè)起點(diǎn)和終點(diǎn).
* 例如` where b between 2 and 3;` 如果索引的前導(dǎo)字段是列a. 那么無(wú)法被使用, 只能全表掃描.
* 而根據(jù)索引的存儲(chǔ)特性, 其實(shí)可以使用跳躍的方式來(lái)進(jìn)行查詢
* 先掃描a列的第一個(gè)值對(duì)應(yīng)的b列的范圍, 然后再跳到a列第二個(gè)不同值來(lái)掃描對(duì)應(yīng)的b列的范圍.
* 可以**給前列的加上可能的常數(shù)值**的方式來(lái)繞過(guò)該限制.
* 在5.6 版本后, 使用**索引條件下推**的方式, 可以解決松散索引掃描的一些限制.
