MySQL速度變慢，怎么辦

MySQL 在崩潰恢復(fù)時(shí)，會(huì)遍歷打開(kāi)所有 ibd 文件的 header page 驗(yàn)證數(shù)據(jù)字典的準(zhǔn)確性，如果 MySQL 中包含了大量表，這個(gè)校驗(yàn)過(guò)程就會(huì)比較耗時(shí)。 MySQL 下崩潰恢復(fù)確實(shí)和表數(shù)量有關(guān)，表總數(shù)越大，崩潰恢復(fù)時(shí)間越長(zhǎng)。另外磁盤 IOPS 也會(huì)影響崩潰恢復(fù)時(shí)間，像這里開(kāi)發(fā)庫(kù)的 HDD IOPS 較低，因此面對(duì)大量的表空間，校驗(yàn)速度就非常緩慢。另外一個(gè)發(fā)現(xiàn)，MySQL 8 下正常啟用時(shí)居然也會(huì)進(jìn)行表空間校驗(yàn)，而故障恢復(fù)時(shí)則會(huì)額外再進(jìn)行一次表空間校驗(yàn)，等于校驗(yàn)了 2 遍。不過(guò) MySQL 8.0 里多了一個(gè)特性，即表數(shù)量超過(guò) 5W 時(shí)，會(huì)啟用多線程掃描，加快表空間校驗(yàn)過(guò)程。

創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)和服務(wù)器主機(jī)托管的網(wǎng)絡(luò)公司，有著豐富的建站經(jīng)驗(yàn)和案例。

如何跳過(guò)校驗(yàn)MySQL 5.7 下有方法可以跳過(guò)崩潰恢復(fù)時(shí)的表空間校驗(yàn)過(guò)程嘛？查閱了資料，方法主要有兩種：

1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ，那么 validate = false，即可以跳過(guò)表空間校驗(yàn)。實(shí)際測(cè)試的時(shí)候設(shè)置 innodb_force_recovery =1，也就是強(qiáng)制恢復(fù)跳過(guò)壞頁(yè)，就可以跳過(guò)校驗(yàn)，然后重啟就是正常啟動(dòng)了。通過(guò)這種臨時(shí)方式可以避免崩潰恢復(fù)后非常耗時(shí)的表空間校驗(yàn)過(guò)程，快速啟動(dòng) MySQL，個(gè)人目前暫時(shí)未發(fā)現(xiàn)有什么隱患。2. 使用共享表空間替代獨(dú)立表空間這樣就不需要打開(kāi) N 個(gè) ibd 文件了，只需要打開(kāi)一個(gè) ibdata 文件即可，大大節(jié)省了校驗(yàn)時(shí)間。自從聽(tīng)了姜老師講過(guò)使用共享表空間替代獨(dú)立表空間解決 drop 大表時(shí)性能抖動(dòng)的原理后，感覺(jué)共享表空間在很多業(yè)務(wù)環(huán)境下，反而更有優(yōu)勢(shì)。

臨時(shí)冒出另外一種解決想法，即用 GDB 調(diào)試崩潰恢復(fù)，通過(guò)臨時(shí)修改 validate 變量值讓 MySQL 跳過(guò)表空間驗(yàn)證過(guò)程，然后讓 MySQL 正常關(guān)閉，重新啟動(dòng)就可以正常啟動(dòng)了。但是實(shí)際測(cè)試發(fā)現(xiàn)，如果以 debug 模式運(yùn)行，確實(shí)可以臨時(shí)修改 validate 變量，跳過(guò)表空間驗(yàn)證過(guò)程，但是 debug 模式下代碼運(yùn)行效率大打折扣，反而耗時(shí)更長(zhǎng)。而以非 debug 模式運(yùn)行，則無(wú)法修改 validate 變量，想法破滅。

MySQL常用優(yōu)化方案

語(yǔ)句執(zhí)行后，會(huì)顯示三個(gè)字段： Query_ID（執(zhí)行ID） | Duration（持續(xù)時(shí)間）| Query（查詢語(yǔ)句） ；

拿到后Query_ID后，可執(zhí)行 show profile for query Query_ID ，查看詳細(xì)的準(zhǔn)備時(shí)間，執(zhí)行時(shí)間、執(zhí)行結(jié)束（ preparing、executing、end ）等。

顯示用戶正在運(yùn)行的線程，需要注意的是，除了 root 用戶能看到所有正在運(yùn)行的線程外，其他用戶都只能看到自己正在運(yùn)行的線程，看不到其它用戶正在運(yùn)行的線程。除非單獨(dú)個(gè)這個(gè)用戶賦予了PROCESS 權(quán)限。

顯示字段包含： User| Host| db | Command | Time| State| Info 等。

解析語(yǔ)句，查詢是否命中索引，及，命中何種索引，用以判斷是否符合我們的預(yù)期。

返回字段包含： select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 等。

select_type 常見(jiàn)類型：

(1) SIMPLE(簡(jiǎn)單SELECT，不使用UNION或子查詢等)

(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY)

(3) UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句)

(4) SUBQUERY(子查詢中的第一個(gè)SELECT，結(jié)果不依賴于外部查詢)

table 常見(jiàn)類型：

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的.

有時(shí)不是真實(shí)的表名字,看到的是derivedx(x是個(gè)數(shù)字,我的理解是第幾步執(zhí)行的結(jié)果)

type 常見(jiàn)類型：

對(duì)表訪問(wèn)方式，表示MySQL在表中找到所需行的方式，又稱“訪問(wèn)類型”。

常用的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL （從左到右，性能從差到好）

possible_keys

指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒(méi)有任何索引顯示 null）

該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。

如果該列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下，可以通過(guò)檢查WHERE子句看是否它引用某些列或適合索引的列來(lái)提高你的查詢性能。如果是這樣，創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢

key

key列顯示MySQL實(shí)際決定使用的鍵（索引），必然包含在possible_keys中

如果沒(méi)有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度，非實(shí)際長(zhǎng)度，為最大可能長(zhǎng)度。

注：不損失精確性的情況下，長(zhǎng)度越短越好。

ref

列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。

rows

估算出結(jié)果集行數(shù)，表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)；

extra

該列包含MySQL解決查詢的詳細(xì)信息,有以下幾種情況：

(1).Distinct

一旦MYSQL找到了與行相聯(lián)合匹配的行，就不再搜索了

(2).Not exists

MYSQL優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行，就不再搜索了

(3).Range checked for each

Record（index map:#）

沒(méi)有找到理想的索引，因此對(duì)于從前面表中來(lái)的每一個(gè)行組合，MYSQL檢查使用哪個(gè)索引，并用它來(lái)從表中返回行。這是使用索引的最慢的連接之一

(4).Using filesort

看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行；

(5).Using temporary

看到這個(gè)的時(shí)候，查詢需要優(yōu)化了。這里，MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果，這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上；

(6).Using index

列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候。

(7).Using where

使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會(huì)發(fā)生，或者是查詢有問(wèn)題。

mysql 存儲(chǔ)過(guò)程執(zhí)行太慢怎么優(yōu)化

1.當(dāng)我們請(qǐng)求mysql服務(wù)器的時(shí)候,MySQL前端會(huì)有一個(gè)監(jiān)聽(tīng),請(qǐng)求到了之后,服務(wù)器得到相關(guān)的SQL語(yǔ)句,執(zhí)行之前(虛線部分為執(zhí)行),還會(huì)做權(quán)限的判斷

2.通過(guò)權(quán)限之后,SQL就到MySQL內(nèi)部,他會(huì)在查詢緩存中,看該SQL有沒(méi)有執(zhí)行過(guò),如果有查詢過(guò),則把緩存結(jié)果返回,說(shuō)明在MySQL內(nèi)部,也有一個(gè)查詢緩存.但是這個(gè)查詢緩存,默認(rèn)是不開(kāi)啟的,這個(gè)查詢緩存,和我們的Hibernate，Mybatis的查詢緩存是一樣的,因?yàn)椴樵兙彺嬉骃QL和參數(shù)都要一樣,所以這個(gè)命中率是非常低的（沒(méi)什么卵用的意思）。

3.如果我們沒(méi)有開(kāi)啟查詢緩存,或者緩存中沒(méi)有找到對(duì)應(yīng)的結(jié)果,那么就到了解析器,解析器主要對(duì)SQL語(yǔ)法進(jìn)行解析

4.解析結(jié)束后就變成一顆解析樹(shù),這個(gè)解析樹(shù)其實(shí)在Hibernate里面也是有的,大家回憶一下,在以前做過(guò)Hibernate項(xiàng)目的時(shí)候,是不是有個(gè)一個(gè)antlr.jar。這個(gè)就是專門做語(yǔ)法解析的工具.因?yàn)樵贖ibernate里面有HQL，它就是通過(guò)這個(gè)工具轉(zhuǎn)換成SQL的,我們編程語(yǔ)言之所以有很多規(guī)范、語(yǔ)法,其實(shí)就是為了便于這個(gè)解析器解析,這個(gè)學(xué)過(guò)編譯原理的應(yīng)該知道.

5.得到解析樹(shù)之后,不能馬上執(zhí)行,這還需要對(duì)這棵樹(shù)進(jìn)行預(yù)處理,也就是說(shuō),這棵樹(shù),我沒(méi)有經(jīng)過(guò)任何優(yōu)化的樹(shù),預(yù)處理器會(huì)這這棵樹(shù)進(jìn)行一些預(yù)處理,比如常量放在什么地方,如果有計(jì)算的東西,把計(jì)算的結(jié)果算出來(lái)等等...

6.預(yù)處理完畢之后,此時(shí)得到一棵比較規(guī)范的樹(shù),這棵樹(shù)就是要拿去馬上做執(zhí)行的樹(shù),比起之前的那棵樹(shù),這棵得到了一些優(yōu)化

7.查詢優(yōu)化器，是MySQL里面最關(guān)鍵的東西,我們寫(xiě)任何一條SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它會(huì)怎么去執(zhí)行?它是先執(zhí)行username = toby還是password = 1?每一條SQL的執(zhí)行順序查詢優(yōu)化器就是根據(jù)MySQL對(duì)數(shù)據(jù)統(tǒng)計(jì)表的一些信息,比如索引,比如表一共有多少數(shù)據(jù),MySQL都是有緩存起來(lái)的,在真正執(zhí)行SQL之前,他會(huì)根據(jù)自己的這些數(shù)據(jù),進(jìn)行一個(gè)綜合的判定,判斷這一次在多種執(zhí)行方式里面,到底選哪一種執(zhí)行方式,可能運(yùn)行的最快.這一步是MySQL性能中,最關(guān)鍵的核心點(diǎn),也是我們的優(yōu)化原則.我們平時(shí)所講的優(yōu)化SQL,其實(shí)說(shuō)白了,就是想讓查詢優(yōu)化器,按照我們的想法,幫我們選擇最優(yōu)的執(zhí)行方案,因?yàn)槲覀儽萂ySQL更懂我們的數(shù)據(jù).MySQL看數(shù)據(jù),僅僅只是自己收集到的信息,這些信息可能是不準(zhǔn)確的,MySQL根據(jù)這些信息選了一個(gè)它自認(rèn)為最優(yōu)的方案,但是這個(gè)方案可能和我們想象的不一樣.

8.這里的查詢執(zhí)行計(jì)劃,也就是MySQL查詢中的執(zhí)行計(jì)劃,比如要先執(zhí)行username = toby還是password = 1

9.這個(gè)執(zhí)行計(jì)劃會(huì)傳給查詢執(zhí)行引擎,執(zhí)行引擎選擇存儲(chǔ)引擎來(lái)執(zhí)行這一份傳過(guò)來(lái)的計(jì)劃,到磁盤中的文件中去查詢,這個(gè)時(shí)候重點(diǎn)來(lái)了,影響這個(gè)查詢性能最根本的原因是什么?就是硬盤的機(jī)械運(yùn)動(dòng),也就是我們平時(shí)熟悉的IO,所以一條查詢語(yǔ)句是快還是慢,就是根據(jù)這個(gè)時(shí)間的IO來(lái)確定的.那怎么執(zhí)行IO又是什么來(lái)確定的?就是傳過(guò)來(lái)的這一份執(zhí)行計(jì)劃.(優(yōu)化就是制定一個(gè)我們認(rèn)為最快的執(zhí)行方案,最節(jié)省IO,和執(zhí)行最快)

10.如果開(kāi)了查詢緩存,則返回結(jié)果給客戶端,并且查詢緩存也放一份。

新聞名稱：mysql性能差怎么解決,如何做 mysql 的性能優(yōu)化?
分享路徑：http://chinadenli.net/article48/dsiepep.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供關(guān)鍵詞優(yōu)化、品牌網(wǎng)站設(shè)計(jì)、服務(wù)器托管、面包屑導(dǎo)航、網(wǎng)站維護(hù)、小程序開(kāi)發(fā)

廣告

聲明：本網(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)