MySQL優(yōu)化查詢速度的方法

這篇文章給大家分享的是有關MySQL優(yōu)化查詢速度的方法的內容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。

如何選擇優(yōu)化的數(shù)據(jù)類型、如何高效的使用索引，這些對于高性能的MySQL來說是必不可少的。 但這些還完全不夠，還需要合理的設計查詢。 如果查詢寫的很糟糕，即使表結構再合理、索引再合適，也是無法實現(xiàn)高性能的。

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

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

一、慢在哪

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

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

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

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

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

通過上面的分析，我們對查詢的過程有了整體的了解，能夠清楚的知道查詢可能在哪些地方會存在問題，最終導致整個查詢很慢，為實際查詢優(yōu)化提供方向。

換言之，查詢優(yōu)化可以從以下兩個角度來出發(fā)：

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

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

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

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

在實際查詢中很多時候，會查詢了實際需要的數(shù)據(jù)，然后這些多余的數(shù)據(jù)會被應用程序丟棄。 這對MySQL來說是額外的開銷，同時也會消耗應用服務器的CPU和內存資源。
一些典型案例如下：

1. 查詢不需要的記錄

這是一個常見的錯誤，常常會誤以為MySQL只會返回需要的數(shù)據(jù)，實際上MySQL卻是先返回全部結果集再進行計算。

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

最有效的解決方法是需要多少記錄就查詢多少記錄，通常會在查詢后面加上LIMIT，即： 分頁查詢。

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

如果你想查詢所有在電影Academy Dinosaur中出現(xià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';

這樣將會返回三張表的全部數(shù)據(jù)列，而實際需求是要查詢演員信息，正確的寫法應該是：

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. 總是查詢出全部列

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

在大部分情況下，是不需要的。 select *會導致進行全表掃描，會讓優(yōu)化器無法完成索引掃描這類優(yōu)化，過多的列還會為服務器帶來額外的I/O、內存和CPU的消耗。 即使真的需要查詢出全部列，應該逐個羅列出全部列而不是*。

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

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

例如，在用戶評論的地方需要查詢用戶頭像的URL，那么用戶多次評論的時候，可能就會反復來查詢這個數(shù)據(jù)。 比較好處理方法是，在初次查詢的時候將這個數(shù)據(jù)緩存起來，后續(xù)使用時直接從緩存中取出。

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

確 定查詢只查詢了需要的數(shù)據(jù)以后，接下來應該看看查詢過程中是否掃描了過多的數(shù)據(jù)。 對于MySQL，最簡單衡量查詢開銷的三個指標如下：

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

沒有哪個指標能夠完全來衡量查詢的開銷，但它們能夠大致反映MySQL內部執(zhí)行查詢時需要訪問多少數(shù)據(jù)，并可以大概推算出查詢運行的實際。 這三個指標都會記錄到MySQL的慢日志中，所以 檢查慢日志記錄是找出掃描行數(shù)過多查詢的辦法 。

慢查詢： 用于記錄在MySQL中響應時間超過閾值(long_query_time,默認10s)的語句，并會將慢查詢記錄到慢日志中。 可通過變量slow_query_long來開啟慢查詢，默認是關閉狀態(tài)，可以將慢日志記錄到表slow_log或文件中，以供檢查分析。

1. 響應時間

響應時間是兩個部分之和： 服務時間和排隊時間。 服務時間是指數(shù)據(jù)庫處理這個查詢真正花費了多長時間。 排隊時間是指服務器因為等待某些資源而沒有真正執(zhí)行查詢的時間，可能是等待I/O操作，也可能是等待 行 鎖等等。

在不同類型的應用壓力下，響應時間并沒有什么一致的規(guī)律或者公式。 諸如存儲引擎的鎖（表鎖，行鎖），高并發(fā)資源競爭，硬件響應等諸多因素都會影響響應時間，所以，響應時間既可能是一個問題的結果也可能是一個問題的原因，不同案例情況不同。

當你看到一個查詢的響應時間的時候，首先需要問問自己，這個響應時間是否是一個合理的值。

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

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

對于找出那些糟糕查詢，這個指標可能還不夠完美，因為并不是所有行的訪問代價都是相同的。 較短的行的訪問速度相當快，內存中的行也比磁盤中的行的訪問速度要快的多。

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

3. 掃描的行數(shù)和訪問類型

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

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

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

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

• 使用索引覆蓋掃描，把所有需要用的列都放到索引中，這樣存儲引擎無需回表獲取對應的行就可以返回結果了。
  
• 優(yōu)化表結構。 例如使用單獨的匯總表來完成查詢。
• 重寫復雜查詢，讓MySQL優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個查詢。

感謝各位的閱讀！關于MySQL優(yōu)化查詢速度的方法就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！