1.慢查詢的優(yōu)化思路

1.1優(yōu)化更需要優(yōu)化的SQL

優(yōu)化SQL是有成本的
高并發(fā)低消耗的比低并發(fā)高消耗影響更大

優(yōu)化示例

顯然 前者更容器容易優(yōu)化

1.2定位優(yōu)化對象的性能瓶頸

1.3明確的優(yōu)化目標

1.4 慢查詢的優(yōu)化思路

• 從explain執(zhí)行計劃入手
• 永遠用小結(jié)果集驅(qū)動大的結(jié)果集
• 盡可能在索引中完成排序
• 只取出自己需要的列，不要用select *
• 僅使用最有效的過濾條件
• 盡可能避免復雜的join和子查詢
• 小心使用order by,group by,distinct 語句

2. join優(yōu)化

永遠用小結(jié)果集驅(qū)動大的結(jié)果集(join操作表小于百萬級別)
驅(qū)動表的定義

當進行多表連接查詢時，[驅(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)）

image.png

• Block Nested-Loop Join（快嵌套循環(huán)）

image.png

• 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 ;

image.png

country ：106條
city ：600條
country_id 都為索引

explain select * from country join city on   country.country_id = city.country_id;

image.png

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;

image.png

join的優(yōu)化思路總結(jié)

• 并發(fā)量太高的時候，系統(tǒng)整體性能可能會急劇下降
• 復雜的join語句，所需要鎖定的資源也就越多，所阻塞的其他線程也就越多
• 復雜的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;

image.png

• 若不加鎖索引的話會可能啟用一個臨時文件輔助排序(落盤，物理io)
• order by排序可利用索引進行優(yōu)化，order by子句中只需要是索引的前導列都可以
  使索引生效，可以直接在索引中排序，不需要在額外的內(nèi)存或者文件中排序
• 不能利用索引避免額外排序的情況，例如：排序字段中有多個索引，排序順序和索引鍵順序不一致(非前導列)

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ī)排序，雙路排序

1. 從表t1中獲取滿足WHERE條件的記錄
2. 對于每條記錄，將記錄的主鍵+排序鍵(id,col2)取出放入sort buffer
3. 如果sort buffer可以存放所有滿足條件的(id,col2)對，則進行排序；否則sort buffer滿后，進行排序并寫到臨時文件中。(排序算法采用的是快速排序算法)
4. 若排序中產(chǎn)生了臨時文件，需要利用歸并排序算法，保證臨時文件中記錄是有序的
5. 循環(huán)執(zhí)行上述過程，直到所有滿足條件的記錄全部參與排序
6. 掃描排好序的(id,col2)隊，即sort buffer，并利用主鍵id去取SELECT需要返回的其他列(col1,col2,col3)
7. 將獲取的結(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)生大量的隨機IO。對于第二次IO取MySQL本身會優(yōu)化，即在取之前先將主鍵id排序，并放入緩沖區(qū)，這個緩存區(qū)大小由參數(shù)read_rnd_buffer_size控制，然后有序去取記錄，將隨機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不夠大，可能導致需要寫臨時文件，造成額外的IO。當然MySQL提供了參數(shù)max_length_for_sort_data，只有當排序sql里出現(xiàn)的所有字段小于max_length_for_sort_data時，才能利用優(yōu)化排序方式，否則只能用常規(guī)排序方式。

c.優(yōu)先隊列排序

為了得到最終的排序結(jié)果，我們都需要將所有滿足條件的記錄進行排序才能返回。那么相對于優(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不夠而導致需要臨時文件進行歸并排序的問題。對于升序，采用大頂堆，最終堆中的元素組成了最小的N個元素，對于降序，采用小頂堆，最終堆中的元素組成了最大的N的元素。

總結(jié)：分別在查詢字段、where條件、排序字段上做出各種可能的組合，主要就是看有無索引，索引在以上三個關(guān)注點上的生效情況

4.group by 分組優(yōu)化

由于GROUP BY 實際上也同樣會進行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作。當然，如果在分組的時候還使用了其他的一些聚合函數(shù)，那么還需要一些聚合函數(shù)的計算。所以，在GROUP BY 的實現(xiàn)過程中，與 ORDER BY 一樣也可以利用到索引。
category 中的name 沒加索引

explain select min(name) from category group by name;

image.png

film 中的 title 加了索引

explain select min(title) from film group by title;

image.png

4.1 group by的類型

• 三種實現(xiàn)類型
  Loose Index Scan(松散的索引掃描)

explain  select  actor_id, max(film_id) FROM film_actor GROUP BY actor_id;

image.png

explain  select  actor_id, max(film_id) FROM film_actor where film_id > 10 GROUP BY actor_id;

image.png

Tight Index Scan(緊湊的索引掃描)
Using temporary 臨時表實現(xiàn)(非索引掃描)

explain  select   max(first_name) FROM actor GROUP BY first_name;

image.png

5.distinct 分組優(yōu)化

DISTINCT 實際上和 GROUP BY 操作的實現(xiàn)非常相似，只不過是在 GROUP BY 之后的每組中只取出一條記錄而已。所以，DISTINCT 的實現(xiàn)和 GROUP BY 的實現(xiàn)也基本差不多，沒有太大的區(qū)別。同樣可以通過松散索引掃描或者是緊湊索引掃描來實現(xiàn)，當然，在無法僅僅使用索引即能完成 DISTINCT 的時候，MySQL 只能通過臨時表來完成。但是，和 GROUP BY 有一點差別的是，DISTINCT 并不需要進行排序。也就是說，在僅僅只是 DISTINCT 操作的 Query 如果無法僅僅利用索引完成操作的時候，MySQL 會利用臨時表來做一次數(shù)據(jù)的“緩存”，但是不會對臨時表中的數(shù)據(jù)進行 filesort 操作。當然，如果我們在進行 DISTINCT 的時候還使用了 GROUP BY 并進行了分組，并使用了類似于 MAX 之類的聚合函數(shù)操作，就無法避免 filesort 了。

explain  select  distinct  country_id from  city;

image.png

explain  select  distinct  country_id from  city where city_id > 100;

image.png