MySQL數(shù)據(jù)庫性能優(yōu)化的意義及其措施

1、 數(shù)據(jù)庫性能優(yōu)化的意義

業(yè)務(wù)發(fā)展初期，數(shù)據(jù)庫中量一般都不高，也不太容易出一些性能問題或者出的問題也不大，但是當(dāng)數(shù)據(jù)庫的量級達(dá)到一定規(guī)模之后，如果缺失有效的預(yù)警、監(jiān)控、處理等手段則會對用戶的使用體驗造成影響，嚴(yán)重的則會直接導(dǎo)致訂單、金額直接受損，因而就需要時刻關(guān)注數(shù)據(jù)庫的性能問題。

2、 性能優(yōu)化的幾個常見措施

數(shù)據(jù)庫性能優(yōu)化的常見手段有很多，比如添加索引、分庫分表、優(yōu)化連接池等，具體如下：

|序號|類型|措施|說明|

| 1 | 物理級別 | 提升硬件性能 | 將數(shù)據(jù)庫安裝到更高配置的服務(wù)器上會有立竿見影的效果，例如提高 CPU 配置、增加內(nèi)存容量、采用固態(tài)硬盤等手段，在經(jīng)費允許的范圍可以嘗試。|

| 2 | 應(yīng)用級別 | 連接池參數(shù)優(yōu)化 | 我們大部分的應(yīng)用都是使用連接池來托管數(shù)據(jù)庫的連接，但是大部分都是默認(rèn)的配置，因而配置好超時時長、連接池容量等參數(shù)就顯得尤為重要。1、 如果鏈接長時間被占用，新的請求無法獲取到新的連接，就會影響到業(yè)務(wù)。2、 如果連接數(shù)設(shè)置的過小，那么即使硬件資源沒問題，也無法發(fā)揮其功效。之前公司做過一些壓測，但就是死活不達(dá)標(biāo)，最后發(fā)現(xiàn)是由于連接數(shù)太小。|

| 3 | 單表級別 | 合理運用索引 | 如果數(shù)據(jù)量較大，但是又沒有合適的索引，就會拖垮整個性能，但是索引是把雙刃劍，并不是說索引越多越好，而是要根據(jù)業(yè)務(wù)的需要進行適當(dāng)?shù)奶砑雍褪褂?。缺失索引、重?fù)索引、冗余索引、失控索引這幾類情況其實都是對系統(tǒng)很大的危害。|

| 4 | 庫表級別 | 分庫分表 | 當(dāng)數(shù)據(jù)量較大的時候，只使用索引就意義不大了，需要做好分庫分表的操作，合理的利用好分區(qū)鍵，例如按照用戶 ID、訂單 ID、日期等維度進行分區(qū)，可以減少掃描范圍。|

| 5 | 監(jiān)控級別 | 加強運維 | 針對線上的一些系統(tǒng)還需要進一步的加強監(jiān)控，比如訂閱一些慢 SQL 日志，找到比較糟糕的一些 SQL，也可以利用業(yè)務(wù)內(nèi)一些通用的工具，例如 druid 組件等。|

3、 MySQL 底層架構(gòu)

首先了解一下數(shù)據(jù)的底層架構(gòu)，也有助于我們做更好優(yōu)化。



一次查詢請求的執(zhí)行過程 我們重點關(guān)注第二部分和第三部分，第二部分其實就是 Server 層，這層主要就是負(fù)責(zé)查詢優(yōu)化，制定出一些執(zhí)行計劃，然后調(diào)用存儲引擎給我們提供的各種底層基礎(chǔ) API，最終將數(shù)據(jù)返回給客戶端。

4、MySQL 索引構(gòu)建過程

目前比較常用的是 InnoDB 存儲引擎，本文討論也是基于 InnoDB 引擎。我們一直說的加索引，那到底什么是索引、索引又是如何形成的呢、索引又如何應(yīng)用呢？這個話題其實很大也很小，說大是因為他底層確實很復(fù)雜，說小是因為在大部分場景下程序員只需要添加索引就好，不太需要了解太底層原理，但是如果了解不透徹就會引發(fā)線上問題，因而本文平衡了大家的理解成本和知識深度，有一定底層原理介紹，但是又不會太過深入導(dǎo)致難以理解。

首先來做個實驗：

創(chuàng)建一個表，目前是只有一個主鍵索引



插入一些數(shù)據(jù)：



MYSQL 從磁盤讀取數(shù)據(jù)到內(nèi)存是按照一頁讀取的，一頁默認(rèn)是 16K，而一頁的格式大概如下。



每一頁都包括了這么幾個內(nèi)容，首先是頁頭、其次是頁目錄、還有用戶數(shù)據(jù)區(qū)域。

1）剛才插入的幾條數(shù)據(jù)就是放到這個用戶數(shù)據(jù)區(qū)域的，這個是按照主鍵依次遞增的單向鏈表。

2）頁目錄這個是用來指向具體的用戶數(shù)據(jù)區(qū)域，因為當(dāng)用戶數(shù)據(jù)區(qū)域的數(shù)據(jù)變多的時候也就會形成分組，而頁目錄就會指向不同的分組，利用二分查找可以快速的定位數(shù)據(jù)。

當(dāng)數(shù)據(jù)量變多的時候，那么這一頁就裝不下這么多數(shù)據(jù)，就要分裂頁，而每頁之間都會雙向鏈接，最終形成一個雙向鏈表。

頁內(nèi)的單向鏈表是為了查找快捷，而頁間的雙向鏈表是為了在做范圍查詢的時候提效，下圖為示意圖，其中其二頁和第三頁是復(fù)制的第一頁，并不真實。



而如果數(shù)據(jù)還繼續(xù)累加，光這幾個頁也不夠了，那就逐步的形成了一棵樹，也就是說索引 B-Tree 是隨著數(shù)據(jù)的積累逐步構(gòu)建出來的。



最下邊的一層叫做葉子節(jié)點，上邊的叫做內(nèi)節(jié)點，而葉子節(jié)點中存儲的是全量數(shù)據(jù)，這樣的樹就是聚簇索引。一直有同學(xué)的理解是說索引是單獨一份而數(shù)據(jù)是一份，其實 MySQL 中有一個原則就是數(shù)據(jù)即索引、索引即數(shù)據(jù)，真實的數(shù)據(jù)本身就是存儲在聚簇索引中的，所謂的回表就是回的聚簇索引。

但是我們也不一定每次都按照主鍵來執(zhí)行 SQL 語句，大部分情況下都是按照一些業(yè)務(wù)字段來，那就會形成別的索引樹，例如，如果按照 b,c,d 來創(chuàng)建的索引就會長這樣。





關(guān)于索引結(jié)構(gòu)的小結(jié)： 對于 B-Tree 而言，葉子節(jié)點是沒有鏈接的，而 B+Tree 索引是單向鏈表，但是 MySQL 在 B+Tree 的基礎(chǔ)之上加以改進，形成了雙向鏈表，雙向的好處是在處理 > <，between and 等 ' 范圍查詢 ' 語法時可以得心應(yīng)手。

5、MySQL 索引的一些使用規(guī)范

1、 只為用于搜索、排序或分組的列創(chuàng)建索引。 重點關(guān)注 where 語句后邊的情況

2、 當(dāng)列中不重復(fù)值的個數(shù)在總記錄條數(shù)中的占比很大時，才為列建立索引。 例如手機號、用戶 ID、班級等，但是比如一張全校學(xué)生表，每條記錄是一名學(xué)生，where 語句是查詢所有’某學(xué)?！膶W(xué)生，那么其實也不會提高性能。

3、 索引列的類型盡量小。 無論是主鍵還是索引列都盡量選擇小的，如果很大則會占據(jù)很大的索引空間。

4、 可以只為索引列前綴創(chuàng)建索引，減少索引占用的存儲空間。

alter table single_table add index idx_key1(key1(10))

5、 盡量使用覆蓋索引進行查詢，以避免回表操作帶來的性能損耗。

select key1 from single_table order by key1

6、 為了盡可能的少的讓聚簇索引發(fā)生頁面分裂的情況，建議讓主鍵自增。

7、 定位并刪除表中的冗余和重復(fù)索引。

冗余索引：

單列索引：（字段 1）

聯(lián)合索引：（字段 1 字段 2）

重復(fù)索引：

在一個字段上添加了普通索引、唯一索引、主鍵等多個索引

6、 執(zhí)行計劃

其中常用的是：

possible_keys: 可能用到的索引

key: 實際使用的索引

rows：預(yù)估的需要讀取的記錄條數(shù)

7、 線上案例

案例 1： 在建設(shè)互聯(lián)網(wǎng)醫(yī)院系統(tǒng)中，問診單表當(dāng)時量級 23 萬左右，其中有一個 business_id 字符串字段，這個字段用來記錄外部訂單的 ID，并且在該字段上也加了索引，但是 ' 根據(jù)該 ID 查詢詳情 ' 的 SQL 語句卻總是時好時壞，性能不穩(wěn)定，快則 10ms，慢則 2 秒左右，SQL 大體如下：

select 字段 1、字段 2、字段 3 from nethp_diag where business_Id = ?

因為 business_id 是記錄第三方系統(tǒng)的訂單 ID，為了兼容不同的第三方系統(tǒng)，因而設(shè)計成了字符串類型，但如果傳入的是一個數(shù)字類型是無法使用索引的，因為 MySQL 只能將字符串轉(zhuǎn)數(shù)字，而不能將數(shù)字轉(zhuǎn)字符串，由于外部的 ID 有的是數(shù)字有的是字符串，因而導(dǎo)致索引一會可以走到，一會走不到，最終導(dǎo)致了性能的不穩(wěn)定。

案例 2：

在某次大促的當(dāng)天，突然接到 DBA 運維的報警，說數(shù)據(jù)庫突然流量激增，CPU 也打到 100% 了，影響了部分線上功能和體驗，遇到這種情況當(dāng)時大部分人都比較緊張，下圖為當(dāng)時的數(shù)據(jù)庫流量情況：



相關(guān) SQL 語句：



當(dāng)時的索引情況



當(dāng)時的執(zhí)行計劃



其實在 patientId 和 doctor_pin 兩個字段上是有索引的，但是由于線上情況的改變，導(dǎo)致 test 判斷沒有進入，這樣的通用查詢導(dǎo)致這兩個字段沒有設(shè)置上，進而導(dǎo)致了數(shù)據(jù)庫掃描的量激增，對數(shù)據(jù)庫產(chǎn)生了很大壓力。

案例 3：

2020 年某日上午收到數(shù)據(jù)庫 CPU 異常報警，對線上有一定的影響，后續(xù)檢查數(shù)據(jù)庫 CPU 情況如下，從 7 點 51 分開始，CPU 從 8% 瞬間達(dá)到 99.92%，絲毫沒有給程序員留任何情面。





當(dāng)時這張表量級 2000 多萬，而當(dāng)這條慢 SQL 執(zhí)行較少的時候，數(shù)據(jù)庫的 CPU 也就下來了，恢復(fù)到了 49.91%，基本可以恢復(fù)線上業(yè)務(wù)，從而表象就是線上間歇性的一會可以開方一會不可以，這條 SQL 當(dāng)時總共執(zhí)行了 230 次，當(dāng)時的 CPU 情況也是忽高忽低，伴隨這條 SQL 語句的執(zhí)行情況，從而最終證明 CPU 的飆升是由于這條慢 SQL。當(dāng)線上業(yè)務(wù)邏輯復(fù)雜的時候，你很難第一時間知道到底是由于那條 SQL 引起的，這個就需要對業(yè)務(wù)非常熟悉，對 SQL 很熟悉，否則就會白白浪費大量的排查時間。

最后的排查結(jié)果：

在頭天晚上的時候添加了一條索引 rx_create_time，當(dāng)時沒事，但是第二天卻出了事故。



加索引前后走的索引不同，一個是走的 rx_status（處方審核狀態(tài)）單列索引，一個是走的 rx_create_time (處方提交事件) 單列索引，這個就要回到業(yè)務(wù)，因為處方狀態(tài)是個枚舉，且枚舉范圍不到 10 個，也就說線上 29,000,000 的數(shù)據(jù)量也就是被分成了不到 10 份，rx_status=5 的值是其中一份，因而通過這個索引就可以命中很多行，這是業(yè)務(wù)規(guī)則，再套用 MySQL 的特性，主要是以下幾條：

1、沒加新索引 rx_create_time 的時候，由于 order by 后邊沒有索引，就看 where 條件中是否有合適的索引，查詢選擇器選定 rx_status 這個單列索引，而 rx_status=5 這個條件下限制的數(shù)據(jù)行在索引中是連續(xù)，即使需要的 rx_id 不在索引中，再回主鍵聚簇索引也來得及，由于 order by 后邊沒有索引，所以走磁盤級別的排序 filesort，高峰積壓的時候處方就 1 萬到 2 萬，跑到了 100ms, 白天低谷的時候幾百單也就 20ms。

2、新加索引之后，就分兩種情況：

2.1、加索引是在晚上，當(dāng)前命中的行數(shù)比較少，由于當(dāng)天晚上的時候待審核的處方確實很少，也就是 rx_status=5 的確實很少，查詢優(yōu)化器感覺反正沒多少行，排序不重要，因而就還是選擇 rx_status 索引。

2.2、第二天白天，待審核的處方數(shù)量很多了（rx_status=5 的數(shù)據(jù)量多了），當(dāng)時可以命中幾萬數(shù)據(jù)，如果當(dāng)前命中的行數(shù)比較多，查詢優(yōu)化器就開始算成本，感覺排序的成本會更高，那就優(yōu)先保排序吧，所以就選擇 rx_create_time 這個字段，但是這個索引樹上沒有別的索引字段的信息，沒辦法，幾乎每條數(shù)據(jù)都要回表，進而引發(fā)了災(zāi)難。

8、一些感悟

關(guān)于數(shù)據(jù)庫的性能優(yōu)化其實是一個很復(fù)雜的大課題，很難通過一篇帖子講的很全面和深刻，這也就是為什么我的標(biāo)題是‘淺析’，程序員的成長一定是要付出代價和成本，因為只有真的在一線切身體會到當(dāng)時的緊張和壓力，對于一件事情才能印象深刻，但反之也不能太過于強調(diào)代價，如果可以通過一些別人的分享就可以規(guī)避一些自己業(yè)務(wù)的問題和錯誤的代價也是好的。

審核編輯：劉清