談到MySQL性能優(yōu)化，查詢(xún)優(yōu)化作為優(yōu)化的源頭，它也是最能體現(xiàn)一個(gè)系統(tǒng)是否更快。本章以及接下來(lái)的幾章將會(huì)著重講解關(guān)于查詢(xún)性能優(yōu)化的內(nèi)容，從中會(huì)介紹一些查詢(xún)優(yōu)化的技巧，幫助大家更深刻地理解MySQL如何真正地執(zhí)行查詢(xún)、究竟慢在哪里、如何讓其快起來(lái)，并明白高效和低效的原因何在，這樣更有助于你更好的來(lái)優(yōu)化查詢(xún)SQL語(yǔ)句。

本章從“為什么查詢(xún)速度這么慢”開(kāi)始談起，讓你能夠清楚的知道查詢(xún)可能會(huì)慢在哪些環(huán)節(jié)，這樣將有助于你更好的優(yōu)化查詢(xún)，做到心中有數(shù)，高人一籌。

一、慢在哪？

**真正衡量查詢(xún)速度的是響應(yīng)時(shí)間。**如果把查詢(xún)看作是一個(gè)任務(wù)，那么它是由一系列子任務(wù)組成的，每個(gè)任務(wù)都會(huì)消耗一定的時(shí)間。如果要優(yōu)化查詢(xún)，實(shí)際上要優(yōu)化其子任務(wù)，那么消除其中一些子任務(wù)，那么減少子任務(wù)的執(zhí)行次數(shù)，要么讓子任務(wù)運(yùn)行的更快。

MySQL在執(zhí)行查詢(xún)的時(shí)候，有哪些子任務(wù)，哪些子任務(wù)花費(fèi)的時(shí)間最多？這就需要借助一些工具，或者一些方法(如：執(zhí)行計(jì)劃)對(duì)查詢(xún)進(jìn)行剖析，來(lái)定位發(fā)現(xiàn)究竟慢在哪。

通常來(lái)說(shuō)，查詢(xún)的生命周期大致大致可以按照順序來(lái)看：**從客戶(hù)端到服務(wù)器，然后在服務(wù)器上進(jìn)行解析，生成執(zhí)行計(jì)劃，執(zhí)行，并返回結(jié)果給客戶(hù)端。**其中，“執(zhí)行”可以認(rèn)為是整個(gè)生命周期中最重要的階段，這其中包括了大量為了檢索數(shù)據(jù)到存儲(chǔ)引擎的調(diào)用以及調(diào)用后的數(shù)據(jù)處理，包括排序、分組等。

在完成這些任務(wù)的時(shí)候，查詢(xún)需要在不同階段的不同地方花費(fèi)時(shí)間，包括網(wǎng)絡(luò)、CPU計(jì)算，生成統(tǒng)計(jì)信息和執(zhí)行計(jì)劃、鎖等待等操作，尤其是向底層存儲(chǔ)引擎檢索數(shù)據(jù)的調(diào)用操作，這些調(diào)用需要在內(nèi)存操作、CPU操作，還可能會(huì)產(chǎn)生大量的上下文切換以及系統(tǒng)調(diào)用。

在上述這些操作中，都會(huì)消耗大量的時(shí)間，其中會(huì)存在一些不必要的額外操作，其中有些操作可能被額外地重復(fù)執(zhí)行了很多次、某些操作執(zhí)行的很慢等等。這也就是查詢(xún)真正可能慢的地方，優(yōu)化查詢(xún)的目的就是減少和消除這些操作所花費(fèi)的時(shí)間。

通過(guò)上面的分析，我們對(duì)查詢(xún)的過(guò)程有了整體的了解，能夠清楚的知道查詢(xún)可能在哪些地方會(huì)存在問(wèn)題，最終導(dǎo)致整個(gè)查詢(xún)很慢，為實(shí)際查詢(xún)優(yōu)化提供方向。

換言之，查詢(xún)優(yōu)化可以從以下兩個(gè)角度來(lái)出發(fā)：

• 減少子查詢(xún)次數(shù)
• 減少額外、重復(fù)的操作
  

查詢(xún)性能低下常見(jiàn)的原因是訪問(wèn)的數(shù)據(jù)太多。在數(shù)據(jù)量小的時(shí)候，查詢(xún)速度還不錯(cuò)，一旦數(shù)據(jù)量上來(lái)，查詢(xún)速度將會(huì)發(fā)生巨變，讓人抓狂、體驗(yàn)極差。針對(duì)查詢(xún)優(yōu)化方面，可以從以下方面進(jìn)行排查：

• 是否查詢(xún)了不需要的數(shù)據(jù)
• 是否掃描了額外的記錄
  

二、是否查詢(xún)了不需要的數(shù)據(jù)

在實(shí)際查詢(xún)中很多時(shí)候，會(huì)查詢(xún)了實(shí)際需要的數(shù)據(jù)，然后這些多余的數(shù)據(jù)會(huì)被應(yīng)用程序丟棄。這對(duì)MySQL來(lái)說(shuō)是額外的開(kāi)銷(xiāo)，同時(shí)也會(huì)消耗應(yīng)用服務(wù)器的CPU和內(nèi)存資源。

一些典型案例如下：

1. 查詢(xún)不需要的記錄

這是一個(gè)常見(jiàn)的錯(cuò)誤，常常會(huì)誤以為MySQL只會(huì)返回需要的數(shù)據(jù)，實(shí)際上MySQL卻是先返回全部結(jié)果集再進(jìn)行計(jì)算。

開(kāi)發(fā)者習(xí)慣性的先使用SELECT語(yǔ)句查詢(xún)大量的結(jié)果，然后由應(yīng)用查詢(xún)或者前端展示層再獲取前面的N行數(shù)據(jù)，例如，在新聞網(wǎng)站中查詢(xún)100條記錄，但是只是在頁(yè)面上顯示前10條。

最有效的解決方法是需要多少記錄就查詢(xún)多少記錄，通常會(huì)在查詢(xún)后面加上LIMIT，即：分頁(yè)查詢(xún)。

2. 多表關(guān)聯(lián)時(shí)返回全部列

如果你想查詢(xún)所有在電影Academy Dinosaur中出現(xiàn)的演員，千萬(wàn)不要按下面的方式來(lái)進(jìn)行查詢(xún)：

select * fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

這樣將會(huì)返回三張表的全部數(shù)據(jù)列，而實(shí)際需求是要查詢(xún)演員信息，正確的寫(xiě)法應(yīng)該是：

select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

3. 總是查詢(xún)出全部列

每次看到select *的時(shí)候一定要用異樣的目光來(lái)審視它，是不是真的需要返回全部數(shù)據(jù)列？

在大部分情況下，是不需要的。select *會(huì)導(dǎo)致進(jìn)行全表掃描，會(huì)讓優(yōu)化器無(wú)法完成索引掃描這類(lèi)優(yōu)化，過(guò)多的列還會(huì)為服務(wù)器帶來(lái)額外的I/O、內(nèi)存和CPU的消耗。即使真的需要查詢(xún)出全部列，應(yīng)該逐個(gè)羅列出全部列而不是*。

4. 重復(fù)查詢(xún)相同的數(shù)據(jù)

如果你不太留意，很容易出現(xiàn)這樣的錯(cuò)誤：不斷地重復(fù)執(zhí)行相同的查詢(xún)，然后每次都返回完全相同的數(shù)據(jù)。

例如，在用戶(hù)評(píng)論的地方需要查詢(xún)用戶(hù)頭像的URL，那么用戶(hù)多次評(píng)論的時(shí)候，可能就會(huì)反復(fù)來(lái)查詢(xún)這個(gè)數(shù)據(jù)。比較好處理方法是，在初次查詢(xún)的時(shí)候?qū)⑦@個(gè)數(shù)據(jù)緩存起來(lái)，后續(xù)使用時(shí)直接從緩存中取出。

三、是否掃描了額外的記錄

確定查詢(xún)只查詢(xún)了需要的數(shù)據(jù)以后，接下來(lái)應(yīng)該看看查詢(xún)過(guò)程中是否掃描了過(guò)多的數(shù)據(jù)。對(duì)于MySQL，最簡(jiǎn)單衡量查詢(xún)開(kāi)銷(xiāo)的三個(gè)指標(biāo)如下：

• 響應(yīng)時(shí)間
• 掃描的行數(shù)
• 返回的行數(shù)
  

沒(méi)有哪個(gè)指標(biāo)能夠完全來(lái)衡量查詢(xún)的開(kāi)銷(xiāo)，但它們能夠大致反映MySQL內(nèi)部執(zhí)行查詢(xún)時(shí)需要訪問(wèn)多少數(shù)據(jù)，并可以大概推算出查詢(xún)運(yùn)行的實(shí)際。這三個(gè)指標(biāo)都會(huì)記錄到MySQL的慢日志中，所以檢查慢日志記錄是找出掃描行數(shù)過(guò)多查詢(xún)的辦法。

慢查詢(xún)：用于記錄在MySQL中響應(yīng)時(shí)間超過(guò)閾值(long_query_time,默認(rèn)10s)的語(yǔ)句，并會(huì)將慢查詢(xún)記錄到慢日志中。可通過(guò)變量slow_query_long來(lái)開(kāi)啟慢查詢(xún)，默認(rèn)是關(guān)閉狀態(tài)，可以將慢日志記錄到表slow_log或文件中，以供檢查分析。

1. 響應(yīng)時(shí)間

響應(yīng)時(shí)間是兩個(gè)部分之和：服務(wù)時(shí)間和排隊(duì)時(shí)間。服務(wù)時(shí)間是指數(shù)據(jù)庫(kù)處理這個(gè)查詢(xún)真正花費(fèi)了多長(zhǎng)時(shí)間。排隊(duì)時(shí)間是指服務(wù)器因?yàn)榈却承┵Y源而沒(méi)有真正執(zhí)行查詢(xún)的時(shí)間，可能是等待I/O操作，也可能是等待行鎖等等。

在不同類(lèi)型的應(yīng)用壓力下，響應(yīng)時(shí)間并沒(méi)有什么一致的規(guī)律或者公式。諸如存儲(chǔ)引擎的鎖（表鎖，行鎖），高并發(fā)資源競(jìng)爭(zhēng)，硬件響應(yīng)等諸多因素都會(huì)影響響應(yīng)時(shí)間，所以，響應(yīng)時(shí)間既可能是一個(gè)問(wèn)題的結(jié)果也可能是一個(gè)問(wèn)題的原因，不同案例情況不同。

當(dāng)你看到一個(gè)查詢(xún)的響應(yīng)時(shí)間的時(shí)候，首先需要問(wèn)問(wèn)自己，這個(gè)響應(yīng)時(shí)間是否是一個(gè)合理的值。

2. 掃描的行數(shù)和返回的行數(shù)

在分析查詢(xún)時(shí)，查看該查詢(xún)掃描的行數(shù)是非常有幫助的，在此之上也能夠分析是否掃描了額外的記錄。

對(duì)于找出那些糟糕查詢(xún)，這個(gè)指標(biāo)可能還不夠完美，因?yàn)椴⒉皇撬行械脑L問(wèn)代價(jià)都是相同的。較短的行的訪問(wèn)速度相當(dāng)快，內(nèi)存中的行也比磁盤(pán)中的行的訪問(wèn)速度要快的多。

**理想的情況下，掃描的行數(shù)和返回的行數(shù)應(yīng)該是相同的。**但實(shí)際上這種美事并不多，例如在做一個(gè)關(guān)聯(lián)查詢(xún)的時(shí)候，掃描的行數(shù)和對(duì)返回的行數(shù)的比率通常都很小，一般在1:1和10：1之間，不過(guò)有時(shí)候這個(gè)值也可能非常大。

3. 掃描的行數(shù)和訪問(wèn)類(lèi)型

在評(píng)估查詢(xún)開(kāi)銷(xiāo)的時(shí)候，需要考慮一下從表中找到某一行數(shù)據(jù)的成本。MySQL有好幾種訪問(wèn)方式可以查找并返回一行結(jié)果。這些訪問(wèn)方式可能需要訪問(wèn)很多行才能返回一條結(jié)果，也有些訪問(wèn)方式可能無(wú)需掃描就能返回結(jié)果。

在執(zhí)行計(jì)劃EXPLAIN語(yǔ)句中的type列反映了訪問(wèn)類(lèi)型。訪問(wèn)類(lèi)型有很多種，從全表掃描到索引掃描，范圍掃描，唯一索引，常數(shù)索引等。這里列的這些，速度是從慢到快，掃描的行數(shù)也是從多到少。

如果查詢(xún)沒(méi)有辦法找到合適的訪問(wèn)類(lèi)型，那么解決的最好辦法通常就是增加一個(gè)合適的索引，這也是我們之前討論索引的問(wèn)題?，F(xiàn)在應(yīng)該明白為什么索引對(duì)于查詢(xún)優(yōu)化如此重要了。索引讓MySQL以最高效，掃描行數(shù)最少的方式找到需要的記錄。

如果發(fā)現(xiàn)查詢(xún)掃描了大量的數(shù)據(jù)但只返回少數(shù)的行，通?？梢試L試下面的技巧去優(yōu)化它：

• 使用索引覆蓋掃描，把所有需要用的列都放到索引中，這樣存儲(chǔ)引擎無(wú)需回表獲取對(duì)應(yīng)的行就可以返回結(jié)果了。
• 優(yōu)化表結(jié)構(gòu)。例如使用單獨(dú)的匯總表來(lái)完成查詢(xún)。
• 重寫(xiě)復(fù)雜查詢(xún)，讓MySQL優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個(gè)查詢(xún)。

以上就是MySQL 查詢(xún)速度慢的原因的詳細(xì)內(nèi)容，更多關(guān)于MySQL 查詢(xún)速度慢的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！