1.慢查詢的優(yōu)化思路
1.1優(yōu)化更需要優(yōu)化的SQL
優(yōu)化SQL是有成本的
高并發(fā)低消耗的比低并發(fā)高消耗影響更大
優(yōu)化示例
| 并發(fā)形式 | 優(yōu)化前 | 假設(shè)優(yōu)化后 |
|---|---|---|
| 高并發(fā)低消耗 | 每小時10000次耍共，每次20個IO | 每小時節(jié)約20000次IO烫饼，sql要優(yōu)化后到18個IO |
| 低并發(fā)高消耗 | 每小時10次，每次20000個IO | 每小時節(jié)約20000次IO试读，sql要優(yōu)化到18000個IO |
顯然 前者更容器容易優(yōu)化
1.2定位優(yōu)化對象的性能瓶頸
1.3明確的優(yōu)化目標(biāo)
1.4 慢查詢的優(yōu)化思路
- 從explain執(zhí)行計劃入手
- 永遠(yuǎn)用小結(jié)果集驅(qū)動大的結(jié)果集
- 盡可能在索引中完成排序
- 只取出自己需要的列，不要用select *
- 僅使用最有效的過濾條件
- 盡可能避免復(fù)雜的join和子查詢
- 小心使用order by,group by,distinct 語句
2. join優(yōu)化
永遠(yuǎn)用小結(jié)果集驅(qū)動大的結(jié)果集(join操作表小于百萬級別)
驅(qū)動表的定義
當(dāng)進(jìn)行多表連接查詢時钩骇，[驅(qū)動表]的定義為：
1）指定了聯(lián)解條件時，滿足查詢條件的記錄行數(shù)少的表為[驅(qū)動表]
2）未指定連接條件時倘屹，行數(shù)少的表為[驅(qū)動表]
mysql關(guān)聯(lián)查詢的概念
MySQL表關(guān)聯(lián)的算法是Nest Loop Join，是通過驅(qū)動表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)纽匙，然后一條一條地通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個表中查詢數(shù)據(jù)，最后合并結(jié)果馏段。
join的實現(xiàn)原理
- mysql只支持一種join算法：
Nested-Loop Join(嵌套循環(huán)連接)
但Nested-Loop Join有三種變種： -
Simple Nested-Loop Join （簡單嵌套循環(huán)）
image.png
- Index Nested-Loop Join（索引嵌套循環(huán)）
- Block Nested-Loop Join（快嵌套循環(huán)）
-
Block Nested-Loop Join（3表快嵌套循環(huán)）
image.png
join的優(yōu)化思路
- 盡可能減少join語句中的Nested Loop的循環(huán)總次數(shù)
- 優(yōu)先優(yōu)化Nested Loop的內(nèi)層循環(huán)
- 保證join語句中被驅(qū)動表上join條件字段已經(jīng)被索引
- 無法保證被驅(qū)動表的Join條件字段被索引且內(nèi)存資源充足的前提下，不要太吝惜join Buffer的設(shè)置
country ：106條
city ：600條
explain select * from country join city ;
country ：106條
city ：600條
country_id 都為索引
explain select * from country join city on country.country_id = city.country_id;
film_actor ：5462條
film：1000條
film 中 film_id 為索引亡蓉， film_actor 不為索引
explain select * from film join film_actor on film.film_id = film_actor.film_id;
join的優(yōu)化思路總結(jié)
- 并發(fā)量太高的時候够坐，系統(tǒng)整體性能可能會急劇下降
- 復(fù)雜的join語句，所需要鎖定的資源也就越多元咙，所阻塞的其他線程也就越多
- 復(fù)雜的Query語句分拆成多個較為簡單的Query語句分布執(zhí)行（超過3張表 不要用join，一個表一個表的查）
3.order by 排序優(yōu)化
- order by 字句中的字段加索引(掃描索引即可庶香，內(nèi)存中完成，邏輯io)
explain select city_id from city order by city_id;
- 若不加鎖索引的話會可能啟用一個臨時文件輔助排序(落盤赶掖，物理io)
- order by排序可利用索引進(jìn)行優(yōu)化，order by子句中只需要是索引的前導(dǎo)列都可以
使索引生效陪白，可以直接在索引中排序，不需要在額外的內(nèi)存或者文件中排序 - 不能利用索引避免額外排序的情況咱士，例如：排序字段中有多個索引，排序順序和索引鍵順序不一致(非前導(dǎo)列)
order by排序算法
對于不能利用索引避免排序的SQL轧钓，數(shù)據(jù)庫不得不自己實現(xiàn)排序功能以滿足用戶需求，此時SQL的執(zhí)行計劃中會出現(xiàn)“Using filesort”毕箍，這里需要注意的是filesort并不意味著就是文件排序，其實也有可能是內(nèi)存排序而柑，這個主要由sort_buffer_size參數(shù)與結(jié)果集大小確定。MySQL內(nèi)部實現(xiàn)排序主要有3種方式媒咳，常規(guī)排序，優(yōu)化排序和優(yōu)先隊列排序伟葫，主要涉及3種排序算法：快速排序院促、歸并排序和堆排序斧抱。
a.常規(guī)排序渐溶，雙路排序
- 從表t1中獲取滿足WHERE條件的記錄
- 對于每條記錄辉浦，將記錄的主鍵+排序鍵(id,col2)取出放入sort buffer
- 如果sort buffer可以存放所有滿足條件的(id,col2)對茎辐，則進(jìn)行排序；否則sort buffer滿后拖陆，進(jìn)行排序并寫到臨時文件中。(排序算法采用的是快速排序算法)
- 若排序中產(chǎn)生了臨時文件依啰，需要利用歸并排序算法，保證臨時文件中記錄是有序的
- 循環(huán)執(zhí)行上述過程速警，直到所有滿足條件的記錄全部參與排序
掃描排好序的(id,col2)隊，即sort buffer闷旧，并利用主鍵id去取SELECT需要返回的其他列(col1,col2,col3)- 將獲取的結(jié)果集返回給用戶。
從上述流程來看匠襟，是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)的結(jié)果集，這個buffer的大小由
sort_buffer_size參數(shù)控制宅此。此外一次排序還需要兩次IO，一次是取排序字段(id,col2)到sort buffer中父腕，第二次是通過上面取出的主鍵id再來取其他所需要返回列(col1,col2,col3)，由于返回的結(jié)果集是按col2排序璧亮，因此id是亂序的，通過亂序的id取(col1,col2,col3)時會產(chǎn)生大量的隨機(jī)IO枝嘶。對于第二次IO取MySQL本身會優(yōu)化，即在取之前先將主鍵id排序哑诊，并放入緩沖區(qū)群扶，這個緩存區(qū)大小由參數(shù)read_rnd_buffer_size控制，然后有序去取記錄竞阐，將隨機(jī)IO轉(zhuǎn)為順序IO。
b.優(yōu)化排序颗搂，單路排序，max_length_for_sort_data
常規(guī)排序方式除了排序本身丢氢，還需要額外兩次IO。優(yōu)化排序方式相對于常規(guī)排序疚察，減少了第二次IO。主要區(qū)別在于稍浆，一次性取出sql中出現(xiàn)的所有字段放入sort buffer中而不是只取排序需要的字段(id,col2)猜嘱。由于sort buffer中包含了查詢需要的所有字段衅枫，因此排序完成后可以直接返回朗伶，無需二次取數(shù)據(jù)。這種方式的代價在于论皆，同樣大小的sort buffer，能存放的(col1,col2,col3)數(shù)目要小于(id,col2)点晴，如果sort buffer不夠大，可能導(dǎo)致需要寫臨時文件粒督，造成額外的IO。當(dāng)然MySQL提供了參數(shù)
max_length_for_sort_data族跛，只有當(dāng)排序sql里出現(xiàn)的所有字段小于max_length_for_sort_data時，才能利用優(yōu)化排序方式礁哄，否則只能用常規(guī)排序方式。
c.優(yōu)先隊列排序
為了得到最終的排序結(jié)果桐绒，我們都需要將所有滿足條件的記錄進(jìn)行排序才能返回夺脾。那么相對于優(yōu)化排序方式掏膏，是否還有優(yōu)化空間呢？5.6版本針對Order by limit M馒疹，N語句乙墙，在空間層面做了優(yōu)化，加入了一種新的排序方式--優(yōu)先隊列听想，這種方式采用堆排序?qū)崿F(xiàn)。堆排序算法特征正好可以解limit M,N 這類排序的問題汉买，雖然仍然需要所有字段參與排序，但是只需要M+N個元組的sort buffer空間即可蛙粘，對于M，N很小的場景出牧，基本不會因為sort buffer不夠而導(dǎo)致需要臨時文件進(jìn)行歸并排序的問題。對于升序评抚，采用大頂堆，最終堆中的元素組成了最小的N個元素慨代，對于降序，采用小頂堆侍匙，最終堆中的元素組成了最大的N的元素组底。
總結(jié)：分別在查詢字段丈积、where條件债鸡、排序字段上做出各種可能的組合，主要就是看有無索引厌均，索引在以上三個關(guān)注點上的生效情況
4.group by 分組優(yōu)化
由于GROUP BY 實際上也同樣會進(jìn)行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作擒悬。當(dāng)然，如果在分組的時候還使用了其他的一些聚合函數(shù)懂牧，那么還需要一些聚合函數(shù)的計算。所以僧凤，在GROUP BY 的實現(xiàn)過程中元扔，與 ORDER BY 一樣也可以利用到索引躯保。
category 中的name 沒加索引
explain select min(name) from category group by name;
film 中的 title 加了索引
explain select min(title) from film group by title;
4.1 group by的類型
- 三種實現(xiàn)類型
Loose Index Scan(松散的索引掃描)
explain select actor_id, max(film_id) FROM film_actor GROUP BY actor_id;
explain select actor_id, max(film_id) FROM film_actor where film_id > 10 GROUP BY actor_id;
Tight Index Scan(緊湊的索引掃描)
Using temporary 臨時表實現(xiàn)(非索引掃描)
explain select max(first_name) FROM actor GROUP BY first_name;
5.distinct 分組優(yōu)化
DISTINCT 實際上和 GROUP BY 操作的實現(xiàn)非常相似途事，只不過是在 GROUP BY 之后的每組中只取出一條記錄而已擅羞。所以，DISTINCT 的實現(xiàn)和 GROUP BY 的實現(xiàn)也基本差不多祟滴，沒有太大的區(qū)別。同樣可以通過松散索引掃描或者是緊湊索引掃描來實現(xiàn)垄懂，當(dāng)然，在無法僅僅使用索引即能完成 DISTINCT 的時候草慧，MySQL 只能通過臨時表來完成。但是漫谷，和 GROUP BY 有一點差別的是，DISTINCT 并不需要進(jìn)行排序舔示。也就是說，在僅僅只是 DISTINCT 操作的 Query 如果無法僅僅利用索引完成操作的時候惕稻，MySQL 會利用臨時表來做一次數(shù)據(jù)的“緩存”，但是不會對臨時表中的數(shù)據(jù)進(jìn)行 filesort 操作公给。當(dāng)然借帘，如果我們在進(jìn)行 DISTINCT 的時候還使用了 GROUP BY 并進(jìn)行了分組淌铐，并使用了類似于 MAX 之類的聚合函數(shù)操作，就無法避免 filesort 了腿准。
explain select distinct country_id from city;
explain select distinct country_id from city where city_id > 100;
