當(dāng)前位置:首頁 > IT技術(shù) > 數(shù)據(jù)庫 > 正文

MySQL“被動(dòng)”性能優(yōu)化匯總!
2022-02-14 14:16:09


年少不知優(yōu)化苦,遇坑方知優(yōu)化難。 ——村口王大爺

本文內(nèi)容導(dǎo)圖如下:

MySQL“被動(dòng)”性能優(yōu)化匯總!_性能優(yōu)化

我之前有很多文章都在講性能優(yōu)化的問題,比如下面這些:


??《switch 的性能提升了 3 倍,我只用了這一招!》????《String性能提升10倍的幾個(gè)方法!(源碼+原理分析)》????《局部變量竟然比全局變量快 5 倍?》????《池化技術(shù)到達(dá)有多牛?看了線程和線程池的對(duì)比嚇我一跳!》????《鏈表竟然比數(shù)組慢了1000多倍?(動(dòng)圖+性能評(píng)測)》????《HashMap 的 7 種遍歷方式與性能分析!》????更多性能優(yōu)化文章??

當(dāng)然,本篇也是關(guān)于性能優(yōu)化的,那性能優(yōu)化就應(yīng)該一把梭子嗎?還是要符合一些規(guī)范和原則呢?

MySQL“被動(dòng)”性能優(yōu)化匯總!_java_02

所以,在開始之前(MySQL 優(yōu)化),咱們先來聊聊性能優(yōu)化的一些原則。

性能優(yōu)化原則和分類

性能優(yōu)化一般可以分為:


  • 主動(dòng)優(yōu)化
  • 被動(dòng)優(yōu)化

所謂的主動(dòng)優(yōu)化是指不需要外力的推動(dòng)而自發(fā)進(jìn)行的一種行為,比如當(dāng)服務(wù)沒有明顯的卡頓、宕機(jī)或者硬件指標(biāo)異常的情況下,自我出發(fā)去優(yōu)化的行為,就可以稱之為主動(dòng)優(yōu)化。

MySQL“被動(dòng)”性能優(yōu)化匯總!_mysql_03

而被動(dòng)優(yōu)化剛好與主動(dòng)優(yōu)化相反,它是指在發(fā)現(xiàn)了服務(wù)器卡頓、服務(wù)異常或者物理指標(biāo)異常的情況下,才去優(yōu)化的這種行為。

性能優(yōu)化原則

無論是主動(dòng)優(yōu)化還是被動(dòng)優(yōu)化都要符合以下性能優(yōu)化的原則:


  1. 優(yōu)化不能改變服務(wù)運(yùn)行的邏輯,要保證服務(wù)的正確性;
  2. 優(yōu)化的過程和結(jié)果都要保證服務(wù)的安全性;
  3. 要保證服務(wù)的穩(wěn)定性,不能為了追求性能犧牲程序的穩(wěn)定性。比如不能為了提高 Redis 的運(yùn)行速度,而關(guān)閉持久化的功能,因?yàn)檫@樣在 Redis 服務(wù)器重啟或者掉電之后會(huì)丟失存儲(chǔ)的數(shù)據(jù)。

MySQL“被動(dòng)”性能優(yōu)化匯總!_性能優(yōu)化_04

以上原則看似都是些廢話,但卻給了我們一個(gè)啟發(fā),那就是我們性能優(yōu)化手段應(yīng)該是:預(yù)防性能問題為主+被動(dòng)優(yōu)化為輔。

也就是說,我們應(yīng)該以預(yù)防性能問題為主,在開發(fā)階段盡可能的規(guī)避性能問題,而在正常情況下,應(yīng)盡量避免主動(dòng)優(yōu)化,以防止未知的風(fēng)險(xiǎn)(除非是為了 KPI,或者是閑的沒事),尤其對(duì)生產(chǎn)環(huán)境而言更是如此,最后才是考慮被動(dòng)優(yōu)化


PS:當(dāng)遇到性能緩慢下降、或硬件指標(biāo)緩慢增加的情況,如今天內(nèi)存的占用率是 50%,明天是 70%,后天是 90% ,并且絲毫沒有收回的跡象時(shí),我們應(yīng)該提早發(fā)現(xiàn)并處理此類問題(這種情況也屬于被動(dòng)優(yōu)化的一種)。


MySQL 被動(dòng)性能優(yōu)化

所以我們本文會(huì)重點(diǎn)介紹 MySQL 被動(dòng)性能優(yōu)化的知識(shí),根據(jù)被動(dòng)性能優(yōu)化的知識(shí),你就可以得到預(yù)防性能問題發(fā)生的一些方法,從而規(guī)避 MySQL 的性能問題

本文我們會(huì)從問題入手,然后考慮這個(gè)問題產(chǎn)生的原因以及相應(yīng)的優(yōu)化方案。我們在實(shí)際開發(fā)中,通常會(huì)遇到以下 3 個(gè)問題:


  1. 單條 SQL 運(yùn)行慢;
  2. 部分 SQL 運(yùn)行慢;
  3. 整個(gè) SQL 運(yùn)行慢。

MySQL“被動(dòng)”性能優(yōu)化匯總!_性能優(yōu)化_05

問題 1:單條 SQL 運(yùn)行慢

問題分析

造成單條 SQL 運(yùn)行比較慢的常見原因有以下兩個(gè):


  1. 未正常創(chuàng)建或使用索引;
  2. 表中數(shù)據(jù)量太大。

解決方案 1:創(chuàng)建并正確使用索引

索引是一種能幫助 MySQL 提高查詢效率的主要手段,因此一般情況下我們遇到的單條 SQL 性能問題,通常都是由于未創(chuàng)建或?yàn)檎_使用索引而導(dǎo)致的,所以在遇到單條 SQL 運(yùn)行比較慢的情況下,你首先要做的就是檢查此表的索引是否正常創(chuàng)建。

如果表的索引已經(jīng)創(chuàng)建了,接下來就要檢查一下此 SQL 語句是否正常觸發(fā)了索引查詢,如果發(fā)生以下情況那么 MySQL 將不能正常的使用索引:


  1. 在 where 子句中使用 != 或者 <> 操作符,查詢引用會(huì)放棄索引而進(jìn)行全表掃描;
  2. 不能使用前導(dǎo)模糊查詢,也就是 '%XX' 或 '%XX%',由于前導(dǎo)模糊不能利用索引的順序,必須一個(gè)個(gè)去找,看是否滿足條件,這樣會(huì)導(dǎo)致全索引掃描或者全表掃描;
  3. 如果條件中有 or 即使其中有條件帶索引也不會(huì)正常使用索引,要想使用 or 又想讓索引生效,只能將 or 條件中的每個(gè)列都加上索引才能正常使用;
  4. 在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作。

因此你要盡量避免以上情況,除了正常使用索引之外,我們也可以使用以下技巧來優(yōu)化索引的查詢速度


  1. 盡量使用主鍵查詢,而非其他索引,因?yàn)橹麈I查詢不會(huì)觸發(fā)回表查詢;
  2. 查詢語句盡可能簡單,大語句拆小語句,減少鎖時(shí)間;
  3. 盡量使用數(shù)字型字段,若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型;
  4. 用 exists 替代 in 查詢;
  5. 避免在索引列上使用 is null 和 is not null。


回表查詢:普通索引查詢到主鍵索引后,回到主鍵索引樹搜索的過程,我們稱為回表查詢。


解決方案 2:數(shù)據(jù)拆分

當(dāng)表中數(shù)據(jù)量太大時(shí) SQL 的查詢會(huì)比較慢,你可以考慮拆分表,讓每張表的數(shù)據(jù)量變小,從而提高查詢效率。

1.垂直拆分

指的是將表進(jìn)行拆分,把一張列比較多的表拆分為多張表。比如,用戶表中一些字段經(jīng)常被訪問,將這些字段放在一張表中,另外一些不常用的字段放在另一張表中,插入數(shù)據(jù)時(shí),使用事務(wù)確保兩張表的數(shù)據(jù)一致性。

垂直拆分的原則:


  • 把不常用的字段單獨(dú)放在一張表;
  • 把 text,blob 等大字段拆分出來放在附表中;
  • 經(jīng)常組合查詢的列放在一張表中。

2.水平拆分

指的是將數(shù)據(jù)表行進(jìn)行拆分,表的行數(shù)超過200萬行時(shí),就會(huì)變慢,這時(shí)可以把一張的表的數(shù)據(jù)拆成多張表來存放。

通常情況下,我們使用取模的方式來進(jìn)行表的拆分,比如,一張有 400W 的用戶表 users,為提高其查詢效率我們把其分成 4 張表 users1,users2,users3,users4,然后通過用戶 ID 取模的方法,同時(shí)查詢、更新、刪除也是通過取模的方法來操作。

表的其他優(yōu)化方案:

  1. 使用可以存下數(shù)據(jù)最小的數(shù)據(jù)類型;
  2. 使用簡單的數(shù)據(jù)類型,int 要比 varchar 類型在 MySQL 處理簡單;
  3. 盡量使用 tinyint、smallint、mediumint 作為整數(shù)類型而非 int;
  4. 盡可能使用 not null 定義字段,因?yàn)?null 占用 4 字節(jié)空間;
  5. 盡量少用 text 類型,非用不可時(shí)最好考慮分表;
  6. 盡量使用 timestamp,而非 datetime;
  7. 單表不要有太多字段,建議在 20 個(gè)字段以內(nèi)。

問題 2:部分 SQL 運(yùn)行慢

問題分析

部分 SQL 運(yùn)行比較慢,我們首先要做的就是先定位出這些 SQL,然后再看這些 SQL 是否正確創(chuàng)建并使用索引。也就是說,我們先要使用慢查詢工具定位出具體的 SQL,然后再使用問題 1 的解決方案處理慢 SQL。

解決方案:慢查詢分析

MySQL 中自帶了慢查詢?nèi)罩镜墓δ埽_啟它就可以用來記錄在 MySQL 中響應(yīng)時(shí)間超過閥值的語句,具體指運(yùn)行時(shí)間超過 long_query_time 值的 SQL,則會(huì)被記錄到慢查詢?nèi)罩局小ong_query_time 的默認(rèn)值為 10,意思是運(yùn)行 10S 以上的語句。默認(rèn)情況下,MySQL 數(shù)據(jù)庫并不啟動(dòng)慢查詢?nèi)罩?,需要我們手?dòng)來設(shè)置這個(gè)參數(shù),如果不是調(diào)優(yōu)需要的話,一般不建議啟動(dòng)該參數(shù),因?yàn)殚_啟慢查詢?nèi)罩緯?huì)給 MySQL 服務(wù)器帶來一定的性能影響。慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?,也支持將日志記錄寫入?shù)據(jù)庫表。

使用 ??mysql> show variables like '%slow_query_log%';?? 來查詢慢查詢?nèi)罩臼欠耖_啟,執(zhí)行效果如下圖所示:

MySQL“被動(dòng)”性能優(yōu)化匯總!_sql_06

slow_query_log 的值為 OFF 時(shí),表示未開啟慢查詢?nèi)罩尽?/p>

開啟慢查詢?nèi)罩?/h5>

開啟慢查詢?nèi)罩?,可以使用如?MySQL 命令:


mysql> set global slow_query_log=1


不過這種設(shè)置方式,只對(duì)當(dāng)前數(shù)據(jù)庫生效,如果 MySQL 重啟也會(huì)失效,如果要永久生效,就必須修改 MySQL 的配置文件 my.cnf,配置如下:


slow_query_log =1
slow_query_log_file=/tmp/mysql_slow.log


當(dāng)你開啟慢查詢?nèi)罩局?,所有的慢查?SQL 都會(huì)被記錄在 slow_query_log_file 參數(shù)配置的文件內(nèi),默認(rèn)是 /tmp/mysql_slow.log 文件,此時(shí)我們就可以打開日志查詢到所有慢 SQL 進(jìn)行逐個(gè)優(yōu)化。

問題 3:整個(gè) SQL 運(yùn)行慢

問題分析

當(dāng)出現(xiàn)整個(gè) SQL 都運(yùn)行比較慢就說明目前數(shù)據(jù)庫的承載能力已經(jīng)到了峰值,因此我們需要使用一些數(shù)據(jù)庫的擴(kuò)展手段來緩解 MySQL 服務(wù)器了。

解決方案:讀寫分離

一般情況下對(duì)數(shù)據(jù)庫而言都是“讀多寫少”,換言之,數(shù)據(jù)庫的壓力多數(shù)是因?yàn)榇罅康淖x取數(shù)據(jù)的操作造成的,我們可以采用數(shù)據(jù)庫集群的方案,使用一個(gè)庫作為主庫,負(fù)責(zé)寫入數(shù)據(jù);其他庫為從庫,負(fù)責(zé)讀取數(shù)據(jù)。這樣可以緩解對(duì)數(shù)據(jù)庫的訪問壓力。

MySQL 常見的讀寫分離方案有以下兩種:

1.應(yīng)用層解決方案

可以通過應(yīng)用層對(duì)數(shù)據(jù)源做路由來實(shí)現(xiàn)讀寫分離,比如,使用 SpringMVC + MyBatis,可以將 SQL 路由交給 Spring,通過 AOP 或者 Annotation 由代碼顯示的控制數(shù)據(jù)源。

優(yōu)點(diǎn):路由策略的擴(kuò)展性和可控性較強(qiáng)。

缺點(diǎn):需要在 Spring 中添加耦合控制代碼。

2.中間件解決方案

通過 MySQL 的中間件做主從集群,比如:Mysql Proxy、Amoeba、Atlas 等中間件都能符合需求。

優(yōu)點(diǎn):與應(yīng)用層解耦。

缺點(diǎn):增加一個(gè)服務(wù)維護(hù)的風(fēng)險(xiǎn)點(diǎn),性能及穩(wěn)定性待測試,需要支持代碼強(qiáng)制主從和事務(wù)。

擴(kuò)展知識(shí):SQL 語句分析

在 MySQL 中我們可以使用 explain 命令來分析 SQL 的執(zhí)行情況,比如:


explain select * from t where id=5;


如下圖所示:

MySQL“被動(dòng)”性能優(yōu)化匯總!_性能優(yōu)化_07

其中:


  • id — 選擇標(biāo)識(shí)符,id 越大優(yōu)先級(jí)越高,越先被執(zhí)行;
  • select_type — 表示查詢的類型;
  • table — 輸出結(jié)果集的表;
  • partitions — 匹配的分區(qū);
  • type — 表示表的連接類型;
  • possible_keys — 表示查詢時(shí),可能使用的索引;
  • key — 表示實(shí)際使用的索引;
  • key_len — 索引字段的長度;
  • ref— ?列與索引的比較;
  • rows — 大概估算的行數(shù);
  • filtered — 按表?xiàng)l件過濾的行百分比;
  • Extra — 執(zhí)行情況的描述和說明。

其中最重要的就是 type 字段,type 值類型如下:


  • all — 掃描全表數(shù)據(jù);
  • index — 遍歷索引;
  • range — 索引范圍查找;
  • index_subquery — 在子查詢中使用 ref;
  • unique_subquery — 在子查詢中使用 eq_ref;
  • ref_or_null — 對(duì) null 進(jìn)行索引的優(yōu)化的 ref;
  • fulltext — 使用全文索引;
  • ref — 使用非唯一索引查找數(shù)據(jù);
  • eq_ref — 在 join 查詢中使用主鍵或唯一索引關(guān)聯(lián);
  • const — 將一個(gè)主鍵放置到 where 后面作為條件查詢, MySQL 優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個(gè)常量,如何轉(zhuǎn)化以及何時(shí)轉(zhuǎn)化,這個(gè)取決于優(yōu)化器,這個(gè)比 eq_ref 效率高一點(diǎn)。

總結(jié)

本文我們介紹了 MySQL 性能優(yōu)化的原則和分類,MySQL 的性能優(yōu)化可分為:主動(dòng)優(yōu)化和被動(dòng)優(yōu)化,但無論何種優(yōu)化都要保證服務(wù)的正確性、安全性和穩(wěn)定性。它帶給我們的啟發(fā)是應(yīng)該采用:預(yù)防 + 被動(dòng)優(yōu)化的方案來確保 MySQL 服務(wù)器的穩(wěn)定性,而被動(dòng)優(yōu)化常見的問題是:


  • 單條 SQL 運(yùn)行慢;
  • 部分 SQL 運(yùn)行慢;
  • 整個(gè) SQL 運(yùn)行慢。

因此我們給出了每種被動(dòng)優(yōu)化方案的問題分析和解決方案,希望本文可以幫助到你。


關(guān)注下面二維碼,訂閱更多精彩內(nèi)容。

MySQL“被動(dòng)”性能優(yōu)化匯總!_sql_08



作者: 王磊的博客

本文摘自 :https://blog.51cto.com/u

開通會(huì)員,享受整站包年服務(wù)立即開通 >