這篇文章主要講解了“MySQL查詢性能優(yōu)化的方法是什么”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“mysql查詢性能優(yōu)化的方法是什么”吧！

成都創(chuàng)新互聯(lián)公司是一家專業(yè)提供蒙山企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站制作、做網(wǎng)站、H5開發(fā)、小程序制作等業(yè)務(wù)。10年已為蒙山眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計(jì)公司優(yōu)惠進(jìn)行中。

1. 為什么查詢速度會(huì)變慢

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

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

2. 慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問

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

確認(rèn)應(yīng)用程序是否檢索了大量超過需要的數(shù)據(jù)。這通常意味著訪問了太多了行，但有時(shí)候可能是訪問太多的列；確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行。

2.1 是否向數(shù)據(jù)庫(kù)請(qǐng)求了不需要的數(shù)據(jù)

有些查詢會(huì)請(qǐng)求超過實(shí)際需要的數(shù)據(jù)，然后這些多余的數(shù)據(jù)會(huì)被應(yīng)用程序丟掉。這會(huì)給MySQL服務(wù)器帶來(lái)額外的負(fù)擔(dān)，并增加網(wǎng)絡(luò)開銷，另外也會(huì)消耗應(yīng)用服務(wù)器的CPU和內(nèi)存資源。

例如查詢不需要的記錄、多表關(guān)聯(lián)返回全部行、總是取出全部列（覆蓋索引難以優(yōu)化，額外增加I/O、內(nèi)存和CPU消耗）、重復(fù)查詢相同的數(shù)據(jù)。

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

在確定查詢只返回需要的數(shù)據(jù)以后，接下來(lái)應(yīng)該看看查詢?yōu)榱朔祷亟Y(jié)果是否掃描了過多的數(shù)據(jù)。對(duì)于MySQL，最簡(jiǎn)單的衡量查詢開銷的三個(gè)指標(biāo)如下：

• 響應(yīng)時(shí)間

• 掃描的行數(shù)

• 返回的行數(shù)

其中響應(yīng)時(shí)間包括服務(wù)時(shí)間和排隊(duì)時(shí)間，服務(wù)時(shí)間是指數(shù)據(jù)庫(kù)處理這個(gè)查詢真正花了多長(zhǎng)時(shí)間，排隊(duì)時(shí)間是指服務(wù)器因?yàn)榈却承┵Y源沒有真正執(zhí)行查詢的時(shí)間--例如I/O和鎖等待；理想情況下掃描的行數(shù)和返回的行數(shù)應(yīng)該是相同的，但實(shí)際情況并非如此，例如在做關(guān)聯(lián)查詢時(shí)，服務(wù)器必須要掃描多行才能生成結(jié)果集中的一行，掃描的行數(shù)對(duì)返回的行數(shù)的比率通常很小；

3. 重構(gòu)查詢的方式

3.1 一個(gè)復(fù)雜查詢還是多個(gè)簡(jiǎn)單查詢

有時(shí)候?qū)⒁粋€(gè)大查詢分解為多個(gè)小查詢是很有必要的，MySQL從設(shè)計(jì)上讓連接和斷開連接都很輕量級(jí)，在返回一個(gè)小的查詢結(jié)果方面很高效。

3.2 切分查詢

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

3.3 分解關(guān)聯(lián)查詢

很多高性能的應(yīng)用都會(huì)對(duì)關(guān)聯(lián)查詢進(jìn)行分解。用分解關(guān)聯(lián)查詢的方式重構(gòu)查詢有如下的優(yōu)勢(shì)：讓緩存效率更高，可以使緩存更好的工作；執(zhí)行單個(gè)查詢可以減少鎖的競(jìng)爭(zhēng)；在應(yīng)用層做關(guān)聯(lián)，可以更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，更容易做到高性能和可擴(kuò)展；單個(gè)查詢本身效率也可能會(huì)有所提升；有利于減少冗余記錄的查詢，在應(yīng)用層做關(guān)聯(lián)查詢，意味著對(duì)于某條記錄應(yīng)用只需要查詢一次，而在數(shù)據(jù)庫(kù)中做關(guān)聯(lián)查詢，則可能需要重復(fù)地訪問一部分?jǐn)?shù)據(jù)，從這點(diǎn)看，這樣的重構(gòu)還可能會(huì)減少網(wǎng)絡(luò)和內(nèi)存的消耗；

4. 查詢執(zhí)行的基礎(chǔ)

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

                                

                                                                 圖-查詢執(zhí)行路徑

5. MySQL查詢優(yōu)化器的局限性

5.1 優(yōu)化COUNT查詢

COUNT()作用是統(tǒng)計(jì)某個(gè)列值的數(shù)量及統(tǒng)計(jì)行數(shù)，也可以統(tǒng)計(jì)結(jié)果集的行數(shù)。在統(tǒng)計(jì)列值時(shí)要求列值非空（不統(tǒng)計(jì)NULL），如果在括號(hào)中指定了列或者列的表達(dá)式，則統(tǒng)計(jì)的是這個(gè)表達(dá)式有值的結(jié)果數(shù)。

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

常見的錯(cuò)誤用法是在括號(hào)內(nèi)指定了列卻希望統(tǒng)計(jì)結(jié)果集的行數(shù)。如果希望知道的是結(jié)果集的行數(shù)，最好使用count(*)，這樣寫意義清晰，性能也會(huì)很好。可以通過條件反轉(zhuǎn)，例如：

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

同時(shí)在某些業(yè)務(wù)場(chǎng)景并不完全要求精確的COUNT值，此時(shí)可以用近似值來(lái)代替。

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

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

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

確保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)列上建立索引。沒有用到的索引只會(huì)帶來(lái)額外的負(fù)擔(dān)。一般而言，只需要在關(guān)聯(lián)順序中的第二個(gè)表的相應(yīng)列上創(chuàng)建索引。

確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及一個(gè)表中列，這樣MySQL才有可能使用索引來(lái)優(yōu)化這個(gè)過程。

5.3 優(yōu)化子查詢

建議盡可能使用關(guān)聯(lián)查詢代替子查詢，但是并非絕對(duì)，在5.6及更新版本或者M(jìn)ariaDB中則可以忽略這一建議。

5.4 優(yōu)化GROUP BY和DISTINCT

MySQL優(yōu)化器會(huì)在內(nèi)部處理的時(shí)候相互轉(zhuǎn)化這兩類查詢，它們都可以使用索引來(lái)優(yōu)化，這也是最有效的優(yōu)化辦法。采用標(biāo)志列分組的效率會(huì)比其它列更高。在MySQL中，當(dāng)無(wú)法使用索引的時(shí)候，GROUP BY使用兩種策略來(lái)完成：使用臨時(shí)表或者文件排序來(lái)分組。

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

超級(jí)聚合GROUP BY WITH ROLLUP可能不夠優(yōu)化，最好的辦法是盡可能的將聚合的功能轉(zhuǎn)移到應(yīng)用程序中處理。

5.5 優(yōu)化LIMIT分頁(yè)

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

select film_id,discription from film order by title limit 50,5;
#延遲關(guān)聯(lián)，通過使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。
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的問題，其實(shí)是OFFSET的問題，它會(huì)導(dǎo)致MySQL掃描大量不需要的行然后再拋棄掉。如果可以使用書簽記錄上次取數(shù)據(jù)的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET。

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

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

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

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

5.6 優(yōu)化SQL_CALC_FOUND_ROWS

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

5.7 優(yōu)化UNION查詢

MySQL總是通過創(chuàng)建并填充臨時(shí)表的方式來(lái)執(zhí)行UNION查詢，因此很多優(yōu)化策略在UNION查詢中都沒法很好地使用。經(jīng)常需要手動(dòng)地將WHERE、LIMIT、ORDER BY等子句下推到UNION的各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化。（例如，直接將這些子句冗余地寫一份到各個(gè)子查詢）

5.8 使用用戶自定義變量

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

感謝各位的閱讀，以上就是“mysql查詢性能優(yōu)化的方法是什么”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)mysql查詢性能優(yōu)化的方法是什么這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

當(dāng)前名稱：mysql查詢性能優(yōu)化的方法是什么
文章網(wǎng)址：http://www.chinadenli.net/article4/igpjie.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、微信小程序、手機(jī)網(wǎng)站建設(shè)、全網(wǎng)營(yíng)銷推廣、網(wǎng)站收錄、靜態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)