在线观看www成人影院-在线观看www日本免费网站-在线观看www视频-在线观看操-欧美18在线-欧美1级

0
  • 聊天消息
  • 系統消息
  • 評論與回復
登錄后你可以
  • 下載海量資料
  • 學習在線課程
  • 觀看技術視頻
  • 寫文章/發帖/加入社區
會員中心
創作中心

完善資料讓更多小伙伴認識你,還能領取20積分哦,立即完善>

3天內不再提示

MySQL性能優化淺析及線上案例

京東云 ? 來源:jf_75140285 ? 作者:jf_75140285 ? 2024-10-22 15:17 ? 次閱讀

作者:京東健康 孟飛

1、 數據庫性能優化的意義

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

2、 性能優化的幾個常見措施

數據庫性能優化的常見手段有很多,比如添加索引、分庫分表、優化連接池等,具體如下:

序號 類型 措施 說明
1 物理級別 提升硬件性能 將數據庫安裝到更高配置的服務器上會有立竿見影的效果,例如提高CPU配置、增加內存容量、采用固態硬盤等手段,在經費允許的范圍可以嘗試。
2 應用級別 連接池參數優化 我們大部分的應用都是使用連接池來托管數據庫的連接,但是大部分都是默認的配置,因而配置好超時時長、連接池容量等參數就顯得尤為重要。 1、 如果鏈接長時間被占用,新的請求無法獲取到新的連接,就會影響到業務。 2、 如果連接數設置的過小,那么即使硬件資源沒問題,也無法發揮其功效。之前公司做過一些壓測,但就是死活不達標,最后發現是由于連接數太小。
3 單表級別 合理運用索引 如果數據量較大,但是又沒有合適的索引,就會拖垮整個性能,但是索引是把雙刃劍,并不是說索引越多越好,而是要根據業務的需要進行適當的添加和使用。 缺失索引、重復索引、冗余索引、失控索引這幾類情況其實都是對系統很大的危害。
4 庫表級別 分庫分表 當數據量較大的時候,只使用索引就意義不大了,需要做好分庫分表的操作,合理的利用好分區鍵,例如按照用戶ID、訂單ID、日期等維度進行分區,可以減少掃描范圍。
5 監控級別 加強運維 針對線上的一些系統還需要進一步的加強監控,比如訂閱一些慢SQL日志,找到比較糟糕的一些SQL,也可以利用業務內一些通用的工具,例如druid組件等。

3、 MySQL底層架構

首先了解一下數據的底層架構,也有助于我們做更好優化。

wKgaoWcXUWKABMR_AAGJMTJ9NfA542.png

一次查詢請求的執行過程

我們重點關注第二部分和第三部分,第二部分其實就是Server層,這層主要就是負責查詢優化,制定出一些執行計劃,然后調用存儲引擎給我們提供的各種底層基礎API,最終將數據返回給客戶端。

4、MySQL索引構建過程

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

首先來做個實驗:

創建一個表,目前是只有一個主鍵索引

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

插入一些數據:

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從磁盤讀取數據到內存是按照一頁讀取的,一頁默認是16K,而一頁的格式大概如下。

wKgZoWcXUWOAfXTkAAHjK7RoJnU397.png

每一頁都包括了這么幾個內容,首先是頁頭、其次是頁目錄、還有用戶數據區域。

1)剛才插入的幾條數據就是放到這個用戶數據區域的,這個是按照主鍵依次遞增的單向鏈表。

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

當數據量變多的時候,那么這一頁就裝不下這么多數據,就要分裂頁,而每頁之間都會雙向鏈接,最終形成一個雙向鏈表。

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

wKgaoWcXUXGAQ2C7AA1vpNIj-J0768.png

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

wKgZoWcXUXOAQ6t6AADheB1AMgM815.png

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

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

wKgZoWcXUXSAdJcxAAs_R4ytCHQ910.png

推薦1個網站,可以可視化的查看一些算法原型:

目錄:

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

B+樹

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

而在MySQL官網上介紹的索引的葉子節點是雙向鏈表。

wKgZoWcXUXaAShxsAAgrphUJqQY336.png

關于索引結構的小結:

對于B-Tree而言,葉子節點是沒有鏈接的,而B+Tree索引是單向鏈表,但是MySQL在B+Tree的基礎之上加以改進,形成了雙向鏈表,雙向的好處是在處理> <,between and等'范圍查詢'語法時可以得心應手。

5、MySQL索引的一些使用規范

1、 只為用于搜索、排序或分組的列創建索引。

重點關注where語句后邊的情況

2、 當列中不重復值的個數在總記錄條數中的占比很大時,才為列建立索引。

例如手機號、用戶ID、班級等,但是比如一張全校學生表,每條記錄是一名學生,where語句是查詢所有’某學校‘的學生,那么其實也不會提高性能。

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

無論是主鍵還是索引列都盡量選擇小的,如果很大則會占據很大的索引空間。

4、 可以只為索引列前綴創建索引,減少索引占用的存儲空間。

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

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

select key1 from single_table order by key1

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

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

冗余索引:

單列索引:(字段1)

聯合索引:(字段1 字段2)

重復索引:

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

6、 執行計劃

wKgZoWcXUXeAXQ01AADUa636FSY014.png

其中常用的是:

possible_keys: 可能用到的索引

key: 實際使用的索引

rows:預估的需要讀取的記錄條數

7、 線上案例

案例1:

在建設互聯網醫院系統中,問診單表當時量級23萬左右,其中有一個business_id字符串字段,這個字段用來記錄外部訂單的ID,并且在該字段上也加了索引,但是'根據該ID查詢詳情'的SQL語句卻總是時好時壞,性能不穩定,快則10ms,慢則2秒左右,SQL大體如下:

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

因為business_id是記錄第三方系統的訂單ID,為了兼容不同的第三方系統,因而設計成了字符串類型,但如果傳入的是一個數字類型是無法使用索引的,因為MySQL只能將字符串轉數字,而不能將數字轉字符串,由于外部的ID有的是數字有的是字符串,因而導致索引一會可以走到,一會走不到,最終導致了性能的不穩定。

?

案例2:

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

wKgaoWcXUXiAUxzQAAXVJpky_w0166.png

wKgZoWcXUXmAaYmBAAOaulpCaH0917.png

相關SQL語句:

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};

當時的索引情況

wKgZoWcXUXqAf-MUAAIfIOMzun8202.png

當時的執行計劃

wKgaoWcXUXuANMpDAAGnCpCkqKk203.png

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

案例3:

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

wKgZoWcXUX2AazBAAATGcrAp_Bo189.png

當時的SQL語句:

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;

當時的索引情況:

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`)

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

最后的排查結果:

在頭天晚上的時候添加了一條索引rx_create_time,當時沒事,但是第二天卻出了事故。

wKgaoWcXUX6AbAG-AALk8IH8jSw003.png

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

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

2、新加索引之后,就分兩種情況:

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

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

8、 推薦用書

這本書以一種詼諧幽默的風格寫了MySQL的一些運行機制,非常適合閱讀,理解成本大幅降低。

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

wKgZoWcXUX-ANG1mAATj5UgAXI0013.png

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

wKgaoWcXUYGAWK1jAAQoT7i8D3g323.png

9、一些感悟

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


審核編輯 黃宇

聲明:本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人,不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用,如有內容侵權或者其他違規問題,請聯系本站處理。 舉報投訴
  • 數據庫
    +關注

    關注

    7

    文章

    3799

    瀏覽量

    64388
  • MySQL
    +關注

    關注

    1

    文章

    809

    瀏覽量

    26564
收藏 人收藏

    評論

    相關推薦

    MySQL的執行過程 SQL語句性能優化常用策略

    回顧 MySQL 的執行過程,幫助介紹如何進行 sql 優化
    的頭像 發表于 12-12 10:26 ?662次閱讀
    <b class='flag-5'>MySQL</b>的執行過程 SQL語句<b class='flag-5'>性能</b><b class='flag-5'>優化</b>常用策略

    mysql數據庫優化方案

    MySQL千萬級大表優化解決方案
    發表于 08-19 12:18

    mysql的查詢優化

    mysql查詢優化
    發表于 03-12 11:06

    MySQL優化之查詢性能優化之查詢優化器的局限性與提示

    MySQL優化三:查詢性能優化之查詢優化器的局限性與提示
    發表于 06-02 06:34

    MySQL索引使用優化和規范

    MySQL - 索引使用優化和規范
    發表于 06-15 16:01

    MySql5.6性能優化最佳實踐

    MySql5.6性能優化最佳實踐
    發表于 09-08 08:47 ?13次下載
    <b class='flag-5'>MySql</b>5.6<b class='flag-5'>性能</b><b class='flag-5'>優化</b>最佳實踐

    幫助優化MySQL數據庫性能的7個技巧

    隨著尺寸和負載的增長,MySQL性能會趨于下降。記住這些訣竅,便可保持MySQL的流暢運行。 測量應用程序的方法之一是看性能。而性能的指標
    發表于 11-30 15:03 ?798次閱讀
    幫助<b class='flag-5'>優化</b><b class='flag-5'>MySQL</b>數據庫<b class='flag-5'>性能</b>的7個技巧

    詳解MySQL的查詢優化 MySQL邏輯架構分析

    說起MySQL的查詢優化,相信大家收藏了一堆奇技淫巧:不能使用SELECT *、不使用NULL字段、合理創建索引、為字段選擇合適的數據類型..... 你是否真的理解這些優化技巧?是否理解其背后
    的頭像 發表于 05-28 16:43 ?4350次閱讀
    詳解<b class='flag-5'>MySQL</b>的查詢<b class='flag-5'>優化</b> <b class='flag-5'>MySQL</b>邏輯架構分析

    MySQL數據庫:理解MySQL性能優化優化查詢

    最近一直在為大家更新MySQL相關學習內容,可能有朋友不懂MySQL的重要性。在程序,語言,架構更新換代頻繁的今天,MySQL 恐怕是大家使用最多的存儲數據庫了。由于MySQL
    的頭像 發表于 07-02 17:18 ?3100次閱讀
    <b class='flag-5'>MySQL</b>數據庫:理解<b class='flag-5'>MySQL</b>的<b class='flag-5'>性能</b><b class='flag-5'>優化</b>、<b class='flag-5'>優化</b>查詢

    MySQL 5.7與MySQL 8.0 性能對比

    背景 測試mysql5.7和mysql8.0分別在讀寫,選定,只寫模式下不同并發時的性能(tps,qps) 最早 測試使用版本為mysql5.7.22和
    的頭像 發表于 11-03 09:26 ?1.7w次閱讀
    <b class='flag-5'>MySQL</b> 5.7與<b class='flag-5'>MySQL</b> 8.0 <b class='flag-5'>性能</b>對比

    分享幾個mysql優化的工具

    對于正在運行的mysql 性能如何?參數設置的是否合理?賬號設置的是否存在安全隱患?
    的頭像 發表于 09-22 14:52 ?2212次閱讀

    你會從哪些維度進行MySQL性能優化?1

    你會從哪些維度進行MySQL性能優化?你會怎么回答? 所謂的性能優化,一般針對的是MySQL
    的頭像 發表于 03-03 10:23 ?513次閱讀
    你會從哪些維度進行<b class='flag-5'>MySQL</b><b class='flag-5'>性能</b><b class='flag-5'>優化</b>?1

    你會從哪些維度進行MySQL性能優化?2

    你會從哪些維度進行MySQL性能優化?你會怎么回答? 所謂的性能優化,一般針對的是MySQL
    的頭像 發表于 03-03 10:23 ?504次閱讀
    你會從哪些維度進行<b class='flag-5'>MySQL</b><b class='flag-5'>性能</b><b class='flag-5'>優化</b>?2

    MySQL高級進階:索引優化

    MySQL官方對于索引的定義:索引是幫助MySQL高效獲取數據的數據結構。
    的頭像 發表于 06-11 11:13 ?576次閱讀
    <b class='flag-5'>MySQL</b>高級進階:索引<b class='flag-5'>優化</b>

    MySQL性能優化方法

    MySQL 性能優化是一項關鍵的任務,可以提高數據庫的運行速度和效率。以下是一些優化方法,包括具體代碼和詳細優化方案。
    的頭像 發表于 11-22 09:59 ?610次閱讀
    主站蜘蛛池模板: 午夜国产精品免费观看| 韩国朴银狐诱感在线观看| 国产免费一区二区三区最新| 国产成人精品日本亚洲直接| 美女天天操| 都市禁忌猎艳风流美妇| 性高清| 狠狠色丁香婷婷综合橹不卡| www.xxx.国产| 手机看片1024福利| 日本免费一区视频| 婷婷六月天在线| 美女黄18| 影音先锋五月天| 黄色午夜| 日本免费网站| 丁香综合| 在线播放视频网站| a亚洲| 国产亚洲人成网站天堂岛| 天天天天做夜夜夜夜做| 宅男lu66国产在线播放| 五月婷六月婷婷| 一本大道香蕉大vr在线吗视频| avbobo在线| 女人张开腿 让男人桶视频 | 优优国产在线视频| 国产一区二区三区乱码| 在线网站黄色| 日本成人在线网址| a网在线| 永久黄网站色视频免费观看99| 人人看人人添人人爽| 人人干网站| 亚洲国产成人久久三区| 激情婷婷六月| 久综合色| 精品精品国产自在久久高清| 欧美一级视频免费看| 免费在线观看的视频| 我要看18毛片|