mysql查詢性能優化的方法是什么

這篇文章主要講解了“mysql查詢性能優化的方法是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“mysql查詢性能優化的方法是什么”吧！

1. 為什么查詢速度會變慢

真正重要的是響應時間，如果把查詢看作是一個任務，那么它由一系列子任務組成，每個子任務都會消耗一定的時間。如果要優化查詢，實際上要優化其子任務，要么減少子任務的執行次數，要么讓子任務執行地更快。通常來說，查詢的生命周期大致可以按照順序來看：從客戶端，到服務器，然后在服務器上進行解析，生成執行計劃、執行，并返回結果給客戶端。其中執行可以認為是整個生命周期中最重要的階段，其中包括了大量為了檢索數據到存儲引擎的調用以及調用后的數據處理，包括排序、分組。

在完成這些任務的時候，查詢需要在不同的地方花費時間，包括網絡、CPU計算，生成統計信息和執行計劃、鎖等待（互斥等待）等操作，尤其是向底層存儲引擎檢索數據的調用操作，這些調用需要在內存操作、CPU操作和內存不足時導致的I/O操作上消耗時間。根據存儲引擎不同，可能還會產生大量的上下文切換和系統調用。

2. 慢查詢基礎：優化數據訪問

查詢性能低下最基本的原因是訪問的數據太多。某些查詢可能不可避免地需要篩選大量數據，但這并不常見。大部分性能低下的查詢都可以通過減少訪問數據量的方式進行優化。對于低效的查詢，可以通過下面兩個步驟來分析：

確認應用程序是否檢索了大量超過需要的數據。這通常意味著訪問了太多了行，但有時候可能是訪問太多的列；確認MySQL服務器層是否在分析大量超過需要的數據行。

2.1 是否向數據庫請求了不需要的數據

有些查詢會請求超過實際需要的數據，然后這些多余的數據會被應用程序丟掉。這會給MySQL服務器帶來額外的負擔，并增加網絡開銷，另外也會消耗應用服務器的CPU和內存資源。

例如查詢不需要的記錄、多表關聯返回全部行、總是取出全部列（覆蓋索引難以優化，額外增加I/O、內存和CPU消耗）、重復查詢相同的數據。

2.2 MySQL是否在掃描額外的記錄

在確定查詢只返回需要的數據以后，接下來應該看看查詢為了返回結果是否掃描了過多的數據。對于MySQL，最簡單的衡量查詢開銷的三個指標如下：

• 響應時間

• 掃描的行數

• 返回的行數

其中響應時間包括服務時間和排隊時間，服務時間是指數據庫處理這個查詢真正花了多長時間，排隊時間是指服務器因為等待某些資源沒有真正執行查詢的時間--例如I/O和鎖等待；理想情況下掃描的行數和返回的行數應該是相同的，但實際情況并非如此，例如在做關聯查詢時，服務器必須要掃描多行才能生成結果集中的一行，掃描的行數對返回的行數的比率通常很小；

3. 重構查詢的方式

3.1 一個復雜查詢還是多個簡單查詢

有時候將一個大查詢分解為多個小查詢是很有必要的，MySQL從設計上讓連接和斷開連接都很輕量級，在返回一個小的查詢結果方面很高效。

3.2 切分查詢

分而治之，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果。例如定期清理大量數據時，如果用一個大的語句一次性完成的話，則可能需要一次鎖住很多數據、占滿整個事務日志、耗盡系統資源、阻塞很多小的但重要的查詢。將一個大的DELETE語句切分成多個較小的查詢可以盡可能小地影響MySQL性能，同時還可以減少MySQL復制的延遲。需要注意的是，如果每次刪除數據后，都暫停一會兒再做下一次刪除，這樣也可以將服務器上原本一次性的壓力分散到一個很長的時間段中，就可以大大降低對服務器的影響，還可以大大減少刪除時鎖的持有時間。

3.3 分解關聯查詢

很多高性能的應用都會對關聯查詢進行分解。用分解關聯查詢的方式重構查詢有如下的優勢：讓緩存效率更高，可以使緩存更好的工作；執行單個查詢可以減少鎖的競爭；在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展；單個查詢本身效率也可能會有所提升；有利于減少冗余記錄的查詢，在應用層做關聯查詢，意味著對于某條記錄應用只需要查詢一次，而在數據庫中做關聯查詢，則可能需要重復地訪問一部分數據，從這點看，這樣的重構還可能會減少網絡和內存的消耗；

4. 查詢執行的基礎

弄清楚MySQL是如何優化和執行查詢，有利于優化查詢，很多查詢優化工作實際上就是遵循一些原則讓優化器能夠按照預想的合理的方式運行。

                                                                 圖-查詢執行路徑

5. MySQL查詢優化器的局限性

5.1 優化COUNT查詢

COUNT()作用是統計某個列值的數量及統計行數，也可以統計結果集的行數。在統計列值時要求列值非空（不統計NULL），如果在括號中指定了列或者列的表達式，則統計的是這個表達式有值的結果數。

count(expr)中的expr可以是col1<col2、col>number還可以是子查詢和case when語句等等；
count(expr)中的expr除了是case when語句，其余的都要加or null才能統計出正確的值，即便是子查詢也要加or null。

常見的錯誤用法是在括號內指定了列卻希望統計結果集的行數。如果希望知道的是結果集的行數，最好使用count(*)，這樣寫意義清晰，性能也會很好。可以通過條件反轉，例如：

select count(*) from world.city where id > 5;
#條件反轉，查詢優化階段其中的子查詢將直接作為常數，可以大大減少需要掃描的行數
select (select count(*) from world.city) -count(*) from world.city where city <=5

同時在某些業務場景并不完全要求精確的COUNT值，此時可以用近似值來代替。

#結果集的“rows”字段的值就是整個表的記錄數,explain的數字不保證準確,雖然大部情況下它和實際記錄數字是一致的，但是成本很低，并不需要真正地執行查詢
explain select * from ehr_post_station_adjust

通常來說，COUNT()都需要掃描大量的行(意味著要訪問大量數據)才能獲得精確的結果，因此很難優化。除了前面的方法，在MySQL層面還能做的就只有索引覆蓋掃描了，如果還不夠可以考慮修改應用的架構，增加匯總表。但是會發現，"快速、精確和實現簡單"，三者永遠只能滿足其二，必須舍掉其中之一。

5.2 優化關聯查詢

確保ON或者USING子句中的列上有索引。在創建索引的時候就要考慮到關聯的順序，當表A和表B用列c關聯的時候，如果優化器的關聯順序是B、A，那么就不需要在B表的對應列上建立索引。沒有用到的索引只會帶來額外的負擔。一般而言，只需要在關聯順序中的第二個表的相應列上創建索引。

確保任何的GROUP BY和ORDER BY中的表達式只涉及一個表中列，這樣MySQL才有可能使用索引來優化這個過程。

5.3 優化子查詢

建議盡可能使用關聯查詢代替子查詢，但是并非絕對，在5.6及更新版本或者MariaDB中則可以忽略這一建議。

5.4 優化GROUP BY和DISTINCT

MySQL優化器會在內部處理的時候相互轉化這兩類查詢，它們都可以使用索引來優化，這也是最有效的優化辦法。采用標志列分組的效率會比其它列更高。在MySQL中，當無法使用索引的時候，GROUP BY使用兩種策略來完成：使用臨時表或者文件排序來分組。

如果沒有通過ORDER BY子句顯式指定排序列，當查詢使用GROUP BY子句的時候，結果集會自動按照分組的字段進行排序，如果不關心結果集的順序，而這種默認排序又導致需要文件排序，則可以使用ORDER BY NULL，讓MySQL不再進行文件排序，也可以在GROUP BY子句中直接使用DESC或ASC
關鍵值，使分組的結果集按需要的方向排序。

超級聚合GROUP BY WITH ROLLUP可能不夠優化，最好的辦法是盡可能的將聚合的功能轉移到應用程序中處理。

5.5 優化LIMIT分頁

在偏移量非常大的時候，例如LIMIT 10000,20，這時MySQL需要查詢10020條記錄然后只返回最后20條記錄，前面的10000條記錄都將被拋棄，這樣的代價非常高。要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。優化此類分頁查詢的一個最簡單的辦法就是盡可能地使用索引覆蓋掃描，而不是查詢所有的列，然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候，這樣做的效率會提升非常大。考慮下面的查詢，延遲關聯將大大提升查詢效率，它讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄后再根據關聯列回原表查詢需要的所有列。

select film_id,discription from film order by title limit 50,5;
#延遲關聯，通過使用覆蓋索引查詢返回需要的主鍵,再根據主鍵關聯原表獲得需要的數據。
select film_id, film.discription from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

LIMIT和OFFSET的問題，其實是OFFSET的問題，它會導致MySQL掃描大量不需要的行然后再拋棄掉。如果可以使用書簽記錄上次取數據的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET。

#指定邊界，無論翻頁到多么后面，其性能都會很好
select * from rental where rental_id < 106030 order by rental_id desc limit 20

除此之外，還可以考慮如下辦法：

• 基于索引使用prepare（參考MySQL prepare 原理）

第一個問號表示pageNum，第二個問號表示每頁元組數
語句樣式: MySQL中,可用如下方法: PREPARE stmt_name FROM SELECT * FROM 表名稱 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M
適應場景: 大數據量
原因: 索引掃描,速度會很快。prepare語句又比一般的查詢語句快一點。

5.6 優化SQL_CALC_FOUND_ROWS

mysql的SQL_CALC_FOUND_ROWS 使用 類似count(*) 使用性能更高

5.7 優化UNION查詢

MySQL總是通過創建并填充臨時表的方式來執行UNION查詢，因此很多優化策略在UNION查詢中都沒法很好地使用。經常需要手動地將WHERE、LIMIT、ORDER BY等子句下推到UNION的各個子查詢中，以便優化器可以充分利用這些條件進行優化。（例如，直接將這些子句冗余地寫一份到各個子查詢）

5.8 使用用戶自定義變量

如果能夠用好自定義變量，發揮其潛力，在某些場景可以寫出非常高效的查詢語句。

感謝各位的閱讀，以上就是“mysql查詢性能優化的方法是什么”的內容了，經過本文的學習后，相信大家對mysql查詢性能優化的方法是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！