MySQL性能優(yōu)化淺析及線上案例

作者：京東健康 孟飛

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

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

2、 性能優(yōu)化的幾個(gè)常見(jiàn)措施

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

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

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

一次查詢請(qǐng)求的執(zhí)行過(guò)程

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

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

目前比較常用的是InnoDB存儲(chǔ)引擎，本文討論也是基于InnoDB引擎。我們一直說(shuō)的加索引，那到底什么是索引、索引又是如何形成的呢、索引又如何應(yīng)用呢？這個(gè)話題其實(shí)很大也很小，說(shuō)大是因?yàn)樗讓哟_實(shí)很復(fù)雜，說(shuō)小是因?yàn)樵诖蟛糠謭?chǎng)景下程序員只需要添加索引就好，不太需要了解太底層原理，但是如果了解不透徹就會(huì)引發(fā)線上問(wèn)題，因而本文平衡了大家的理解成本和知識(shí)深度，有一定底層原理介紹，但是又不會(huì)太過(guò)深入導(dǎo)致難以理解。

首先來(lái)做個(gè)實(shí)驗(yàn)：

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

CREATE TABLE `t1`(

a int NOT NULL,

b int DEFAULT NULL,

c int DEFAULT NULL,

d int DEFAULT NULL,

e varchar(20) DEFAULT NULL,

PRIMARYKEY(a)

)ENGINE=InnoDB

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

insert into test.t1 values(4,3,1,1,'d');

insert into test.t1 values(1,1,1,1,'a');

insert into test.t1 values(8,8,8,8,'h');

insert into test.t1 values(2,2,2,2,'b');

insert into test.t1 values(5,2,3,5,'e');

insert into test.t1 values(3,3,2,2,'c');

insert into test.t1 values(7,4,5,5,'g');

insert into test.t1 values(6,6,4,4,'f');

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

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

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

2）頁(yè)目錄這個(gè)是用來(lái)指向具體的用戶數(shù)據(jù)區(qū)域，因?yàn)楫?dāng)用戶數(shù)據(jù)區(qū)域的數(shù)據(jù)變多的時(shí)候也就會(huì)形成分組，而頁(yè)目錄就會(huì)指向不同的分組，利用二分查找可以快速的定位數(shù)據(jù)。

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

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

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

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

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

推薦1個(gè)網(wǎng)站，可以可視化的查看一些算法原型：

目錄：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B+樹(shù)

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

而在MySQL官網(wǎng)上介紹的索引的葉子節(jié)點(diǎn)是雙向鏈表。

關(guān)于索引結(jié)構(gòu)的小結(jié)：

對(duì)于B-Tree而言，葉子節(jié)點(diǎn)是沒(méi)有鏈接的，而B(niǎo)+Tree索引是單向鏈表，但是MySQL在B+Tree的基礎(chǔ)之上加以改進(jìn)，形成了雙向鏈表，雙向的好處是在處理> <，between and等'范圍查詢'語(yǔ)法時(shí)可以得心應(yīng)手。

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

1、 只為用于搜索、排序或分組的列創(chuàng)建索引。

重點(diǎn)關(guān)注where語(yǔ)句后邊的情況

2、 當(dāng)列中不重復(fù)值的個(gè)數(shù)在總記錄條數(shù)中的占比很大時(shí)，才為列建立索引。

例如手機(jī)號(hào)、用戶ID、班級(jí)等，但是比如一張全校學(xué)生表，每條記錄是一名學(xué)生，where語(yǔ)句是查詢所有’某學(xué)?！膶W(xué)生，那么其實(shí)也不會(huì)提高性能。

3、 索引列的類型盡量小。

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

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

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

5、 盡量使用覆蓋索引進(jìn)行查詢，以避免回表操作帶來(lái)的性能損耗。

select key1 from single_table order by key1

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

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

冗余索引：

單列索引：（字段1）

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

重復(fù)索引：

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

6、 執(zhí)行計(jì)劃

其中常用的是：

possible_keys: 可能用到的索引

key: 實(shí)際使用的索引

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

7、 線上案例

案例1：

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

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

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

?

案例2：

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

相關(guān)SQL語(yǔ)句：

select count(1)from jdhe_medical_recordwhere status = 1 and is_test = #{isTest,jdbcType=INTEGER} and electric_medical_record_status in (2,3)and patient_id = #{patientId,jdbcType=BIGINT}and doctor_pin = #{doctorPin,jdbcType=VARCHAR}and created >#{dateStart,jdbcType=TIMESTAMP};

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

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

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

案例3：

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

當(dāng)時(shí)的SQL語(yǔ)句：

select rx_id, rx_create_time from nethp_rx_info where rx_status = 5 and status = 1 and rx_product_type = 0 and (parent_rx_id = 0 or parent_rx_id is null) and business_type != 7 and vender_id = 8888 order by rx_create_time asc limit 1;

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

PRIMARY KEY (`id`), UNIQUE KEY `uniq_rx_id` (`rx_id`), KEY `idx_diag_id` (`diag_id`), KEY `idx_doctor_pin` (`doctor_pin`) USING BTREE, KEY `idx_rx_storeId` (`store_id`), KEY `idx_parent_rx_id` (`parent_rx_id`) USING BTREE, KEY `idx_rx_status` (`rx_status`) USING BTREE, KEY `idx_doctor_status_type` (`doctor_pin`, `rx_status`, `rx_type`), KEY `idx_business_store` (`business_type`, `store_id`), KEY `idx_doctor_pin_patientid` (`patient_id`, `doctor_pin`) USING BTREE, KEY `idx_rx_create_time` (`rx_create_time`)

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

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

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

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

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

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

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

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

8、 推薦用書(shū)

這本書(shū)以一種詼諧幽默的風(fēng)格寫(xiě)了MySQL的一些運(yùn)行機(jī)制，非常適合閱讀，理解成本大幅降低。

https://item.jd.com/13009316.html

https://item.jd.com/10066181997303.html

9、一些感悟

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


審核編輯 黃宇