MySQL性能調(diào)優(yōu)之查詢優(yōu)化的方法

本篇內(nèi)容介紹了“MySQL性能調(diào)優(yōu)之查詢優(yōu)化的方法”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

一、查詢慢的原因

1、網(wǎng)絡(luò)

2、CPU

3、IO

4、上下文切換

5、系統(tǒng)調(diào)用

6、生成統(tǒng)計信息

7、鎖等待時間

二、優(yōu)化數(shù)據(jù)訪問

1、查詢性能低下的主要原因是訪問的數(shù)據(jù)太多，某些查詢不可避免的需要篩選大量的數(shù)據(jù)，我們可以通過減少訪問數(shù)據(jù)量的方式進行優(yōu)化

（1）確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)

（2）確認(rèn)mysql服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行

2、是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)

（1）查詢不需要的記錄（我們常常會誤以為mysql會只返回需要的數(shù)據(jù)，實際上mysql卻是先返回全部結(jié)果再進行計算，在日常的開發(fā)習(xí)慣中，經(jīng)常是先用select語句查詢大量的結(jié)果，然后獲取前面的N行后關(guān)閉結(jié)果集。優(yōu)化方式是在查詢后面添加limit）

（2）多表關(guān)聯(lián)時返回全部列（select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';select actor.* from actor...;）

（3）總是取出全部列（在公司的企業(yè)需求中，禁止使用select *,雖然這種方式能夠簡化開發(fā)，但是會影響查詢的性能，所以盡量不要使用）

（4）重復(fù)查詢相同的數(shù)據(jù)（如果需要不斷的重復(fù)執(zhí)行相同的查詢，且每次返回完全相同的數(shù)據(jù)，因此，基于這樣的應(yīng)用場景，我們可以將這部分?jǐn)?shù)據(jù)緩存起來，這樣的話能夠提高查詢效率。）

三、執(zhí)行過程的優(yōu)化

1、查詢緩存

在解析一個查詢語句之前，如果查詢緩存是打開的，那么mysql會優(yōu)先檢查這個查詢是否命中查詢緩存中的數(shù)據(jù)，如果查詢恰好命中了查詢緩存，那么會在返回結(jié)果之前會檢查用戶權(quán)限，如果權(quán)限沒有問題，那么mysql會跳過所有的階段，就直接從緩存中拿到結(jié)果并返回給客戶端

2、查詢優(yōu)化處理

mysql查詢完緩存之后會經(jīng)過以下幾個步驟：解析SQL、預(yù)處理、優(yōu)化SQL執(zhí)行計劃，這幾個步驟出現(xiàn)任何的錯誤，都可能會終止查詢。

（1）語法解析器和預(yù)處理

mysql通過關(guān)鍵字將SQL語句進行解析，并生成一顆解析樹，mysql解析器將使用mysql語法規(guī)則驗證和解析查詢，例如驗證使用使用了錯誤的關(guān)鍵字或者順序是否正確等等，預(yù)處理器會進一步檢查解析樹是否合法，例如表名和列名是否存在，是否有歧義，還會驗證權(quán)限等等
（2）查詢優(yōu)化器

當(dāng)語法樹沒有問題之后，相應(yīng)的要由優(yōu)化器將其轉(zhuǎn)成執(zhí)行計劃，一條查詢語句可以使用非常多的執(zhí)行方式，最后都可以得到對應(yīng)的結(jié)果，但是不同的執(zhí)行方式帶來的效率是不同的，優(yōu)化器的最主要目的就是要選擇最有效的執(zhí)行計劃。

mysql使用的是基于成本的優(yōu)化器，在優(yōu)化的時候會嘗試預(yù)測一個查詢使用某種查詢計劃時候的成本，并選擇其中成本最小的一個。

a、select count(*) from film_actor;        show status like 'last_query_cost';

可以看到這條查詢語句大概需要做1104個數(shù)據(jù)頁才能找到對應(yīng)的數(shù)據(jù)，這是經(jīng)過一系列的統(tǒng)計信息計算來的.

(a) 每個表或者索引的頁面?zhèn)€數(shù)

(b) 索引的基數(shù)

(c) 索引和數(shù)據(jù)行的長度

(d) 索引的分布情況

b、在很多情況下mysql會選擇錯誤的執(zhí)行計劃，原因如下：

（a）統(tǒng)計信息不準(zhǔn)確（InnoDB因為其mvcc的架構(gòu)，并不能維護一個數(shù)據(jù)表的行數(shù)的精確統(tǒng)計信息）

（b） 執(zhí)行計劃的成本估算不等同于實際執(zhí)行的成本(有時候某個執(zhí)行計劃雖然需要讀取更多的頁面，但是他的成本卻更小，因為如果這些頁面都是順序讀或者這些頁面都已經(jīng)在內(nèi)存中的話，那么它的訪問成本將很小，mysql層面并不知道哪些頁面在內(nèi)存中，哪些在磁盤，所以查詢之際執(zhí)行過程中到底需要多少次IO是無法得知的)

(c) mysql的最優(yōu)可能跟你想的不一樣(mysql的優(yōu)化是基于成本模型的優(yōu)化，但是有可能不是最快的優(yōu)化)

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

(e) mysql不會考慮不受其控制的操作成本(執(zhí)行存儲過程或者用戶自定義函數(shù)的成本)

c、優(yōu)化器的優(yōu)化策略

（a）靜態(tài)優(yōu)化（直接對解析樹進行分析，并完成優(yōu)化）

（b）動態(tài)優(yōu)化（動態(tài)優(yōu)化與查詢的上下文有關(guān)，也可能跟取值、索引對應(yīng)的行數(shù)有關(guān)）

（c）mysql對查詢的靜態(tài)優(yōu)化只需要一次，但對動態(tài)優(yōu)化在每次執(zhí)行時都需要重新評估

d、優(yōu)化器的優(yōu)化類型

（a）重新定義關(guān)聯(lián)表的順序(數(shù)據(jù)表的關(guān)聯(lián)并不總是按照在查詢中指定的順序進行，決定關(guān)聯(lián)順序時優(yōu)化器很重要的功能)

（b）將外連接轉(zhuǎn)化成內(nèi)連接，內(nèi)連接的效率要高于外連接

（c）使用等價變換規(guī)則，mysql可以使用一些等價變化來簡化并規(guī)劃表達式

（d）優(yōu)化count(),min(),max()（索引和列是否可以為空通常可以幫助mysql優(yōu)化這類表達式：例如，要找到某一列的最小值，只需要查詢索引的最左端的記錄即可，不需要全文掃描比較）

（e）預(yù)估并轉(zhuǎn)化為常數(shù)表達式，當(dāng)mysql檢測到一個表達式可以轉(zhuǎn)化為常數(shù)的時候，就會一直把該表達式作為常數(shù)進行處理。（explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1）

（f）索引覆蓋掃描，當(dāng)索引中的列包含所有查詢中需要使用的列的時候，可以使用覆蓋索引

（g）子查詢優(yōu)化（mysql在某些情況下可以將子查詢轉(zhuǎn)換一種效率更高的形式，從而減少多個查詢多次對數(shù)據(jù)進行訪問，例如將經(jīng)常查詢的數(shù)據(jù)放入到緩存中。）

（h）等值傳播（如果兩個列的值通過等式關(guān)聯(lián)，那么mysql能夠把其中一個列的where條件傳遞到另一個上：

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

這里使用film_id字段進行等值關(guān)聯(lián)，film_id這個列不僅適用于film表而且適用于film_actor表

explain select film.film_id from film inner join film_actor using(film_id
 ) where film.film_id > 500 and film_actor.film_id > 500;）

e、關(guān)聯(lián)查詢

mysql的關(guān)聯(lián)查詢很重要，但其實關(guān)聯(lián)查詢執(zhí)行的策略比較簡單：mysql對任何關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作，即mysql先在一張表中循環(huán)取出單條數(shù)據(jù)，然后再嵌套到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止。然后根據(jù)各個表匹配的行，返回查詢中需要的各個列。mysql會嘗試再最后一個關(guān)聯(lián)表中找到所有匹配的行，如果最后一個關(guān)聯(lián)表無法找到更多的行之后，mysql返回到上一層次關(guān)聯(lián)表，看是否能夠找到更多的匹配記錄，以此類推迭代執(zhí)行。整體的思路如此，但是要注意實際的執(zhí)行過程中有多個變種形式：

f、排序優(yōu)化

無論如何排序都是一個成本很高的操作，所以從性能的角度出發(fā)，應(yīng)該盡可能避免排序或者盡可能避免對大量數(shù)據(jù)進行排序。
推薦使用利用索引進行排序，但是當(dāng)不能使用索引的時候，mysql就需要自己進行排序，如果數(shù)據(jù)量小則再內(nèi)存中進行，如果數(shù)據(jù)量大就需要使用磁盤，mysql中稱之為filesort。
如果需要排序的數(shù)據(jù)量小于排序緩沖區(qū)(show variables like '%sort_buffer_size%';),mysql使用內(nèi)存進行快速排序操作，如果內(nèi)存不夠排序，那么mysql就會先將樹分塊，對每個獨立的塊使用快速排序進行排序，并將各個塊的排序結(jié)果存放再磁盤上，然后將各個排好序的塊進行合并，最后返回排序結(jié)果，以下是排序的算法：

（a）兩次傳輸排序

第一次數(shù)據(jù)讀取是將需要排序的字段讀取出來，然后進行排序，第二次是將排好序的結(jié)果按照需要去讀取數(shù)據(jù)行。
這種方式效率比較低，原因是第二次讀取數(shù)據(jù)的時候因為已經(jīng)排好序，需要去讀取所有記錄而此時更多的是隨機IO，讀取數(shù)據(jù)成本會比較高
兩次傳輸?shù)膬?yōu)勢，在排序的時候存儲盡可能少的數(shù)據(jù)，讓排序緩沖區(qū)可以盡可能多的容納行數(shù)來進行排序操作
（b）單次傳輸排序

先讀取查詢所需要的所有列，然后再根據(jù)給定列進行排序，最后直接返回排序結(jié)果，此方式只需要一次順序IO讀取所有的數(shù)據(jù)，而無須任何的隨機IO，問題在于查詢的列特別多的時候，會占用大量的存儲空間，無法存儲大量的數(shù)據(jù)

（c）如何選擇

當(dāng)需要排序的列的總大小超過max_length_for_sort_data定義的字節(jié)，mysql會選擇雙次排序，反之使用單次排序，當(dāng)然，用戶可以設(shè)置此參數(shù)的值來選擇排序的方式

四、優(yōu)化特定類型的查詢

1、優(yōu)化count()查詢

count()是特殊的函數(shù)，有兩種不同的作用，一種是某個列值的數(shù)量，也可以統(tǒng)計行數(shù)。

（1）總有人認(rèn)為myisam的count函數(shù)比較快，這是有前提條件的，只有沒有任何where條件的count(*)才是比較快的。

（2）使用近似值

在某些應(yīng)用場景中，不需要完全精確的值，可以參考使用近似值來代替，比如可以使用explain來獲取近似的值
其實在很多OLAP的應(yīng)用中，需要計算某一個列值的基數(shù)，有一個計算近似值的算法叫hyperloglog。

（3）更復(fù)雜的優(yōu)化

一般情況下，count()需要掃描大量的行才能獲取精確的數(shù)據(jù)，其實很難優(yōu)化，在實際操作的時候可以考慮使用索引覆蓋掃描，或者增加匯總表，或者增加外部緩存系統(tǒng)。

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

（1）確保on或者using子句中的列上有索引，在創(chuàng)建索引的時候就要考慮到關(guān)聯(lián)的順序

當(dāng)表A和表B使用列C關(guān)聯(lián)的時候，如果優(yōu)化器的關(guān)聯(lián)順序是B、A，那么就不需要再B表的對應(yīng)列上建上索引，沒有用到的索引只會帶來額外的負(fù)擔(dān)，一般情況下來說，只需要在關(guān)聯(lián)順序中的第二個表的相應(yīng)列上創(chuàng)建索引。

（2）確保任何的groupby和order by中的表達式只涉及到一個表中的列，這樣mysql才有可能使用索引來優(yōu)化這個過程

3、優(yōu)化子查詢

子查詢的優(yōu)化最重要的優(yōu)化建議是盡可能使用關(guān)聯(lián)查詢代替

4、優(yōu)化limit分頁

在很多應(yīng)用場景中我們需要將數(shù)據(jù)進行分頁，一般會使用limit加上偏移量的方法實現(xiàn)，同時加上合適的orderby 的子句，如果這種方式有索引的幫助，效率通常不錯，否則的話需要進行大量的文件排序操作，還有一種情況，當(dāng)偏移量非常大的時候，前面的大部分?jǐn)?shù)據(jù)都會被拋棄，這樣的代價太高。
要優(yōu)化這種查詢的話，要么是在頁面中限制分頁的數(shù)量，要么優(yōu)化大偏移量的性能。

優(yōu)化此類查詢的最簡單的辦法就是盡可能地使用覆蓋索引，而不是查詢所有的列。

select film_id,description from film order by title limit 50,5;
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

5、優(yōu)化union查詢

mysql總是通過創(chuàng)建并填充臨時表的方式來執(zhí)行union查詢，因此很多優(yōu)化策略在union查詢中都沒法很好的使用。經(jīng)常需要手工的將where、limit、order by等子句下推到各個子查詢中，以便優(yōu)化器可以充分利用這些條件進行優(yōu)化

除非確實需要服務(wù)器消除重復(fù)的行，否則一定要使用union all，因此沒有all關(guān)鍵字，mysql會在查詢的時候給臨時表加上distinct的關(guān)鍵字，這個操作的代價很高。

6、推薦使用用戶自定義變量

用戶自定義變量是一個容易被遺忘的mysql特性，但是如果能夠用好，在某些場景下可以寫出非常高效的查詢語句，在查詢中混合使用過程化和關(guān)系話邏輯的時候，自定義變量會非常有用。
用戶自定義變量是一個用來存儲內(nèi)容的臨時容器，在連接mysql的整個過程中都存在。

（1）自定義變量的使用

set @one :=1
set @min_actor :=(select min(actor_id) from actor)
set @last_week :=current_date-interval 1 week;

（2）自定義變量的限制

a、無法使用查詢緩存

b、不能在使用常量或者標(biāo)識符的地方使用自定義變量，例如表名、列名或者limit子句

c、用戶自定義變量的生命周期是在一個連接中有效，所以不能用它們來做連接間的通信

d、不能顯式地聲明自定義變量地類型

e、mysql優(yōu)化器在某些場景下可能會將這些變量優(yōu)化掉，這可能導(dǎo)致代碼不按預(yù)想地方式運行

f、賦值符號：=的優(yōu)先級非常低，所以在使用賦值表達式的時候應(yīng)該明確的使用括號。

g、使用未定義變量不會產(chǎn)生任何語法錯誤。

（3）自定義變量的使用案例

a、優(yōu)化排名語句

在給一個變量賦值的同時使用這個變量

select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;

查詢獲取演過最多電影的前10名演員，然后根據(jù)出演電影次數(shù)做一個排名

select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;

b、避免重新查詢剛剛更新的數(shù)據(jù)

當(dāng)需要高效的更新一條記錄的時間戳，同時希望查詢當(dāng)前記錄中存放的時間戳是什么

update t1 set  lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;
update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;

c、確定取值的順序

在賦值和讀取變量的時候可能是在查詢的不同階段

（a）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;

因為where和select在查詢的不同階段執(zhí)行，所以看到查詢到兩條記錄，這不符合預(yù)期

（b）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name

當(dāng)引入了orde;r by之后，發(fā)現(xiàn)打印出了全部結(jié)果，這是因為order by引入了文件排序，而where條件是在文件排序操作之前取值的  。

（c）解決這個問題的關(guān)鍵在于讓變量的賦值和取值發(fā)生在執(zhí)行查詢的同一階段：

set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;

“MySQL性能調(diào)優(yōu)之查詢優(yōu)化的方法”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！