本篇文章給大家?guī)?lái)了關(guān)于mysql的相關(guān)知識(shí)，其中主要介紹了關(guān)于性能優(yōu)化的相關(guān)問(wèn)題，包括了查詢優(yōu)化等內(nèi)容，下面一起來(lái)看一下，希望對(duì)大家有幫助。
在編寫快速的查詢之前，需要清楚一點(diǎn)，真正重要的是響應(yīng)時(shí)間，而且要知道在整個(gè)SQL語(yǔ)句的執(zhí)行過(guò)程中每個(gè)步驟都花費(fèi)了多長(zhǎng)時(shí)間，要知道哪些步驟是拖垮執(zhí)行效率的關(guān)鍵步驟，想要做到這點(diǎn)，必須要知道查詢的生命周期，然后進(jìn)行優(yōu)化，不同的應(yīng)用場(chǎng)景有不同的優(yōu)化方式，不要一概而論，具體情況具體分析。
一、查詢慢的原因
1、網(wǎng)絡(luò)
2、CPU
3、IO
4、上下文切換
5、系統(tǒng)調(diào)用
6、生成統(tǒng)計(jì)信息
7、鎖等待時(shí)間
二、優(yōu)化數(shù)據(jù)訪問(wèn)
1、查詢性能低下的主要原因是訪問(wèn)的數(shù)據(jù)太多，某些查詢不可避免的需要篩選大量的數(shù)據(jù)，我們可以通過(guò)減少訪問(wèn)數(shù)據(jù)量的方式進(jìn)行優(yōu)化
（1）確認(rèn)應(yīng)用程序是否在檢索大量超過(guò)需要的數(shù)據(jù)
（2）確認(rèn)mysql服務(wù)器層是否在分析大量超過(guò)需要的數(shù)據(jù)行
2、是否向數(shù)據(jù)庫(kù)請(qǐng)求了不需要的數(shù)據(jù)
（1）查詢不需要的記錄（我們常常會(huì)誤以為mysql會(huì)只返回需要的數(shù)據(jù)，實(shí)際上mysql卻是先返回全部結(jié)果再進(jìn)行計(jì)算，在日常的開(kāi)發(fā)習(xí)慣中，經(jīng)常是先用select語(yǔ)句查詢大量的結(jié)果，然后獲取前面的N行后關(guān)閉結(jié)果集。優(yōu)化方式是在查詢后面添加limit）
（2）多表關(guān)聯(lián)時(shí)返回全部列（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 *,雖然這種方式能夠簡(jiǎn)化開(kāi)發(fā)，但是會(huì)影響查詢的性能，所以盡量不要使用）
（4）重復(fù)查詢相同的數(shù)據(jù)（如果需要不斷的重復(fù)執(zhí)行相同的查詢，且每次返回完全相同的數(shù)據(jù)，因此，基于這樣的應(yīng)用場(chǎng)景，我們可以將這部分?jǐn)?shù)據(jù)緩存起來(lái)，這樣的話能夠提高查詢效率。）
三、執(zhí)行過(guò)程的優(yōu)化
1、查詢緩存
在解析一個(gè)查詢語(yǔ)句之前，如果查詢緩存是打開(kāi)的，那么mysql會(huì)優(yōu)先檢查這個(gè)查詢是否命中查詢緩存中的數(shù)據(jù)，如果查詢恰好命中了查詢緩存，那么會(huì)在返回結(jié)果之前會(huì)檢查用戶權(quán)限，如果權(quán)限沒(méi)有問(wèn)題，那么mysql會(huì)跳過(guò)所有的階段，就直接從緩存中拿到結(jié)果并返回給客戶端
2、查詢優(yōu)化處理
mysql查詢完緩存之后會(huì)經(jīng)過(guò)以下幾個(gè)步驟：解析SQL、預(yù)處理、優(yōu)化SQL執(zhí)行計(jì)劃，這幾個(gè)步驟出現(xiàn)任何的錯(cuò)誤，都可能會(huì)終止查詢。
（1）語(yǔ)法解析器和預(yù)處理
mysql通過(guò)關(guān)鍵字將SQL語(yǔ)句進(jìn)行解析，并生成一顆解析樹(shù)，mysql解析器將使用mysql語(yǔ)法規(guī)則驗(yàn)證和解析查詢，例如驗(yàn)證使用使用了錯(cuò)誤的關(guān)鍵字或者順序是否正確等等，預(yù)處理器會(huì)進(jìn)一步檢查解析樹(shù)是否合法，例如表名和列名是否存在，是否有歧義，還會(huì)驗(yàn)證權(quán)限等等
（2）查詢優(yōu)化器
當(dāng)語(yǔ)法樹(shù)沒(méi)有問(wèn)題之后，相應(yīng)的要由優(yōu)化器將其轉(zhuǎn)成執(zhí)行計(jì)劃，一條查詢語(yǔ)句可以使用非常多的執(zhí)行方式，最后都可以得到對(duì)應(yīng)的結(jié)果，但是不同的執(zhí)行方式帶來(lái)的效率是不同的，優(yōu)化器的最主要目的就是要選擇最有效的執(zhí)行計(jì)劃。
mysql使用的是基于成本的優(yōu)化器，在優(yōu)化的時(shí)候會(huì)嘗試預(yù)測(cè)一個(gè)查詢使用某種查詢計(jì)劃時(shí)候的成本，并選擇其中成本最小的一個(gè)。
a、select count(*) from film_actor; show status like 'last_query_cost';
可以看到這條查詢語(yǔ)句大概需要做1104個(gè)數(shù)據(jù)頁(yè)才能找到對(duì)應(yīng)的數(shù)據(jù)，這是經(jīng)過(guò)一系列的統(tǒng)計(jì)信息計(jì)算來(lái)的.
(a) 每個(gè)表或者索引的頁(yè)面?zhèn)€數(shù)
(b) 索引的基數(shù)
(c) 索引和數(shù)據(jù)行的長(zhǎng)度
(d) 索引的分布情況
b、在很多情況下mysql會(huì)選擇錯(cuò)誤的執(zhí)行計(jì)劃，原因如下：
（a）統(tǒng)計(jì)信息不準(zhǔn)確（InnoDB因?yàn)槠鋗vcc的架構(gòu)，并不能維護(hù)一個(gè)數(shù)據(jù)表的行數(shù)的精確統(tǒng)計(jì)信息）
（b） 執(zhí)行計(jì)劃的成本估算不等同于實(shí)際執(zhí)行的成本(有時(shí)候某個(gè)執(zhí)行計(jì)劃雖然需要讀取更多的頁(yè)面，但是他的成本卻更小，因?yàn)槿绻@些頁(yè)面都是順序讀或者這些頁(yè)面都已經(jīng)在內(nèi)存中的話，那么它的訪問(wèn)成本將很小，mysql層面并不知道哪些頁(yè)面在內(nèi)存中，哪些在磁盤，所以查詢之際執(zhí)行過(guò)程中到底需要多少次IO是無(wú)法得知的)
(c) mysql的最優(yōu)可能跟你想的不一樣(mysql的優(yōu)化是基于成本模型的優(yōu)化，但是有可能不是最快的優(yōu)化)
(d) mysql不考慮其他并發(fā)執(zhí)行的查詢
(e) mysql不會(huì)考慮不受其控制的操作成本(執(zhí)行存儲(chǔ)過(guò)程或者用戶自定義函數(shù)的成本)
c、優(yōu)化器的優(yōu)化策略
（a）靜態(tài)優(yōu)化（直接對(duì)解析樹(shù)進(jìn)行分析，并完成優(yōu)化）
（b）動(dòng)態(tài)優(yōu)化（動(dòng)態(tài)優(yōu)化與查詢的上下文有關(guān)，也可能跟取值、索引對(duì)應(yīng)的行數(shù)有關(guān)）
（c）mysql對(duì)查詢的靜態(tài)優(yōu)化只需要一次，但對(duì)動(dòng)態(tài)優(yōu)化在每次執(zhí)行時(shí)都需要重新評(píng)估
d、優(yōu)化器的優(yōu)化類型
（a）重新定義關(guān)聯(lián)表的順序(數(shù)據(jù)表的關(guān)聯(lián)并不總是按照在查詢中指定的順序進(jìn)行，決定關(guān)聯(lián)順序時(shí)優(yōu)化器很重要的功能)
（b）將外連接轉(zhuǎn)化成內(nèi)連接，內(nèi)連接的效率要高于外連接
（c）使用等價(jià)變換規(guī)則，mysql可以使用一些等價(jià)變化來(lái)簡(jiǎn)化并規(guī)劃表達(dá)式
（d）優(yōu)化count(),min(),max()（索引和列是否可以為空通常可以幫助mysql優(yōu)化這類表達(dá)式：例如，要找到某一列的最小值，只需要查詢索引的最左端的記錄即可，不需要全文掃描比較）
（e）預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式，當(dāng)mysql檢測(cè)到一個(gè)表達(dá)式可以轉(zhuǎn)化為常數(shù)的時(shí)候，就會(huì)一直把該表達(dá)式作為常數(shù)進(jìn)行處理。（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)索引中的列包含所有查詢中需要使用的列的時(shí)候，可以使用覆蓋索引
（g）子查詢優(yōu)化（mysql在某些情況下可以將子查詢轉(zhuǎn)換一種效率更高的形式，從而減少多個(gè)查詢多次對(duì)數(shù)據(jù)進(jìn)行訪問(wèn)，例如將經(jīng)常查詢的數(shù)據(jù)放入到緩存中。）
（h）等值傳播（如果兩個(gè)列的值通過(guò)等式關(guān)聯(lián)，那么mysql能夠把其中一個(gè)列的where條件傳遞到另一個(gè)上：
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;
這里使用film_id字段進(jìn)行等值關(guān)聯(lián)，film_id這個(gè)列不僅適用于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)查詢很重要，但其實(shí)關(guān)聯(lián)查詢執(zhí)行的策略比較簡(jiǎn)單：mysql對(duì)任何關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作，即mysql先在一張表中循環(huán)取出單條數(shù)據(jù)，然后再嵌套到下一個(gè)表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止。然后根據(jù)各個(gè)表匹配的行，返回查詢中需要的各個(gè)列。mysql會(huì)嘗試再最后一個(gè)關(guān)聯(lián)表中找到所有匹配的行，如果最后一個(gè)關(guān)聯(lián)表無(wú)法找到更多的行之后，mysql返回到上一層次關(guān)聯(lián)表，看是否能夠找到更多的匹配記錄，以此類推迭代執(zhí)行。整體的思路如此，但是要注意實(shí)際的執(zhí)行過(guò)程中有多個(gè)變種形式：
f、排序優(yōu)化
無(wú)論如何排序都是一個(gè)成本很高的操作，所以從性能的角度出發(fā)，應(yīng)該盡可能避免排序或者盡可能避免對(duì)大量數(shù)據(jù)進(jìn)行排序。
推薦使用利用索引進(jìn)行排序，但是當(dāng)不能使用索引的時(shí)候，mysql就需要自己進(jìn)行排序，如果數(shù)據(jù)量小則再內(nèi)存中進(jìn)行，如果數(shù)據(jù)量大就需要使用磁盤，mysql中稱之為filesort。
如果需要排序的數(shù)據(jù)量小于排序緩沖區(qū)(show variables like '%sort_buffer_size%';),mysql使用內(nèi)存進(jìn)行快速排序操作，如果內(nèi)存不夠排序，那么mysql就會(huì)先將樹(shù)分塊，對(duì)每個(gè)獨(dú)立的塊使用快速排序進(jìn)行排序，并將各個(gè)塊的排序結(jié)果存放再磁盤上，然后將各個(gè)排好序的塊進(jìn)行合并，最后返回排序結(jié)果，以下是排序的算法：
（a）兩次傳輸排序
第一次數(shù)據(jù)讀取是將需要排序的字段讀取出來(lái)，然后進(jìn)行排序，第二次是將排好序的結(jié)果按照需要去讀取數(shù)據(jù)行。
這種方式效率比較低，原因是第二次讀取數(shù)據(jù)的時(shí)候因?yàn)橐呀?jīng)排好序，需要去讀取所有記錄而此時(shí)更多的是隨機(jī)IO，讀取數(shù)據(jù)成本會(huì)比較高
兩次傳輸?shù)膬?yōu)勢(shì)，在排序的時(shí)候存儲(chǔ)盡可能少的數(shù)據(jù)，讓排序緩沖區(qū)可以盡可能多的容納行數(shù)來(lái)進(jìn)行排序操作
（b）單次傳輸排序
先讀取查詢所需要的所有列，然后再根據(jù)給定列進(jìn)行排序，最后直接返回排序結(jié)果，此方式只需要一次順序IO讀取所有的數(shù)據(jù)，而無(wú)須任何的隨機(jī)IO，問(wèn)題在于查詢的列特別多的時(shí)候，會(huì)占用大量的存儲(chǔ)空間，無(wú)法存儲(chǔ)大量的數(shù)據(jù)
（c）如何選擇
當(dāng)需要排序的列的總大小超過(guò)max_length_for_sort_data定義的字節(jié)，mysql會(huì)選擇雙次排序，反之使用單次排序，當(dāng)然，用戶可以設(shè)置此參數(shù)的值來(lái)選擇排序的方式
四、優(yōu)化特定類型的查詢
1、優(yōu)化count()查詢
count()是特殊的函數(shù)，有兩種不同的作用，一種是某個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)。
（1）總有人認(rèn)為myisam的count函數(shù)比較快，這是有前提條件的，只有沒(méi)有任何where條件的count(*)才是比較快的。
（2）使用近似值
在某些應(yīng)用場(chǎng)景中，不需要完全精確的值，可以參考使用近似值來(lái)代替，比如可以使用explain來(lái)獲取近似的值
其實(shí)在很多OLAP的應(yīng)用中，需要計(jì)算某一個(gè)列值的基數(shù)，有一個(gè)計(jì)算近似值的算法叫hyperloglog。
（3）更復(fù)雜的優(yōu)化
一般情況下，count()需要掃描大量的行才能獲取精確的數(shù)據(jù)，其實(shí)很難優(yōu)化，在實(shí)際操作的時(shí)候可以考慮使用索引覆蓋掃描，或者增加匯總表，或者增加外部緩存系統(tǒng)。
2、優(yōu)化關(guān)聯(lián)查詢
（1）確保on或者using子句中的列上有索引，在創(chuàng)建索引的時(shí)候就要考慮到關(guān)聯(lián)的順序
當(dāng)表A和表B使用列C關(guān)聯(lián)的時(shí)候，如果優(yōu)化器的關(guān)聯(lián)順序是B、A，那么就不需要再B表的對(duì)應(yīng)列上建上索引，沒(méi)有用到的索引只會(huì)帶來(lái)額外的負(fù)擔(dān)，一般情況下來(lái)說(shuō)，只需要在關(guān)聯(lián)順序中的第二個(gè)表的相應(yīng)列上創(chuàng)建索引。
（2）確保任何的groupby和order by中的表達(dá)式只涉及到一個(gè)表中的列，這樣mysql才有可能使用索引來(lái)優(yōu)化這個(gè)過(guò)程
3、優(yōu)化子查詢
子查詢的優(yōu)化最重要的優(yōu)化建議是盡可能使用關(guān)聯(lián)查詢代替
4、優(yōu)化limit分頁(yè)
在很多應(yīng)用場(chǎng)景中我們需要將數(shù)據(jù)進(jìn)行分頁(yè)，一般會(huì)使用limit加上偏移量的方法實(shí)現(xiàn)，同時(shí)加上合適的orderby 的子句，如果這種方式有索引的幫助，效率通常不錯(cuò)，否則的話需要進(jìn)行大量的文件排序操作，還有一種情況，當(dāng)偏移量非常大的時(shí)候，前面的大部分?jǐn)?shù)據(jù)都會(huì)被拋棄，這樣的代價(jià)太高。
要優(yōu)化這種查詢的話，要么是在頁(yè)面中限制分頁(yè)的數(shù)量，要么優(yōu)化大偏移量的性能。
優(yōu)化此類查詢的最簡(jiǎn)單的辦法就是盡可能地使用覆蓋索引，而不是查詢所有的列。
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總是通過(guò)創(chuàng)建并填充臨時(shí)表的方式來(lái)執(zhí)行union查詢，因此很多優(yōu)化策略在union查詢中都沒(méi)法很好的使用。經(jīng)常需要手工的將where、limit、order by等子句下推到各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化
除非確實(shí)需要服務(wù)器消除重復(fù)的行，否則一定要使用union all，因此沒(méi)有all關(guān)鍵字，mysql會(huì)在查詢的時(shí)候給臨時(shí)表加上distinct的關(guān)鍵字，這個(gè)操作的代價(jià)很高。
6、推薦使用用戶自定義變量
用戶自定義變量是一個(gè)容易被遺忘的mysql特性，但是如果能夠用好，在某些場(chǎng)景下可以寫出非常高效的查詢語(yǔ)句，在查詢中混合使用過(guò)程化和關(guān)系話邏輯的時(shí)候，自定義變量會(huì)非常有用。
用戶自定義變量是一個(gè)用來(lái)存儲(chǔ)內(nèi)容的臨時(shí)容器，在連接mysql的整個(gè)過(guò)程中都存在。
（1）自定義變量的使用
set @one :=1 set @min_actor :=(select min(actor_id) from actor) set @last_week :=current_date-interval 1 week;
（2）自定義變量的限制
a、無(wú)法使用查詢緩存
b、不能在使用常量或者標(biāo)識(shí)符的地方使用自定義變量，例如表名、列名或者limit子句
c、用戶自定義變量的生命周期是在一個(gè)連接中有效，所以不能用它們來(lái)做連接間的通信
d、不能顯式地聲明自定義變量地類型
e、mysql優(yōu)化器在某些場(chǎng)景下可能會(huì)將這些變量?jī)?yōu)化掉，這可能導(dǎo)致代碼不按預(yù)想地方式運(yùn)行
f、賦值符號(hào)：=的優(yōu)先級(jí)非常低，所以在使用賦值表達(dá)式的時(shí)候應(yīng)該明確的使用括號(hào)。
g、使用未定義變量不會(huì)產(chǎn)生任何語(yǔ)法錯(cuò)誤。
（3）自定義變量的使用案例
a、優(yōu)化排名語(yǔ)句
在給一個(gè)變量賦值的同時(shí)使用這個(gè)變量
select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;
查詢獲取演過(guò)最多電影的前10名演員，然后根據(jù)出演電影次數(shù)做一個(gè)排名
select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;
b、避免重新查詢剛剛更新的數(shù)據(jù)
當(dāng)需要高效的更新一條記錄的時(shí)間戳，同時(shí)希望查詢當(dāng)前記錄中存放的時(shí)間戳是什么
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、確定取值的順序
在賦值和讀取變量的時(shí)候可能是在查詢的不同階段
（a）set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;
因?yàn)閣here和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é)果，這是因?yàn)閛rder by引入了文件排序，而where條件是在文件排序操作之前取值的 。
（c）解決這個(gè)問(wèn)題的關(guān)鍵在于讓變量的賦值和取值發(fā)生在執(zhí)行查詢的同一階段：
set @rownum:=0; select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;
