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

来源:https://www.cnblogs.com/Jcloud/archive/2023/01/19/17058501.html
-Advertisement-
Play Games

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


作者:京東健康 孟飛

1、 資料庫性能優化的意義

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

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

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

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

3、 MySQL底層架構

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

一次查詢請求的執行過程

我們重點關註第二部分和第三部分,第二部分其實就是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,而一頁的格式大概如下。

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

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

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

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

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

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

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

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

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

目錄:

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

B+樹

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

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

關於索引結構的小結:

對於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、 執行計劃

其中常用的是:

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%了,影響了部分線上功能和體驗,遇到這種情況當時大部分人都比較緊張,下圖為當時的資料庫流量情況:

相關SQL語句:

當時的索引情況

當時的執行計劃

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

案例3:

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

當時的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,當時沒事,但是第二天卻出了事故。

加索引前後走的索引不同,一個是走的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

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

9、一些感悟

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 我國目前並未出台專門針對網路爬蟲技術的法律規範,但在司法實踐中,相關判決已屢見不鮮,K 哥特設了“K哥爬蟲普法”專欄,本欄目通過對真實案例的分析,旨在提高廣大爬蟲工程師的法律意識,知曉如何合法合規利用爬蟲技術,警鐘長鳴,做一個守法、護法、有原則的技術人員。 案情介紹 2019年8、9月期間,被告人袁 ...
  • 2023-01-18 一、AOP前奏-代理模式 1、手動實現動態代理環境搭建 (1)基於介面實現動態代理:JDK動態代理 (2)基於繼承實現動態代理:Cglib、javassist動態代理 2、實現動態代理的步驟 (1)一個類:Proxy ①概述:Proxy代理類的基類(類似於Object) ②作用 ...
  • 1 簡介 Solace是一個強大的實時性的事件驅動消息隊列。本文將介紹如何在Spring中使用,雖然代碼使用的是Spring Boot,但並沒有使用相關starter,跟Spring的整合一樣,可通用。JMS是通過的消息處理框架,可以深入學習一下,不同的MQ在JMS的整合上都是類似的。 2 通過Do ...
  • 2023-01-18 一、Spring中組件掃描 1、預設使用的情況 <context:component-scan base-package="com.hh"></context:component-scan> 2、包含掃描 註:使用包含掃描之前,必須設置use-default-filters=" ...
  • EntityFrameworkCore是微軟官網提供的ORM框架,是輕量化、可擴展、開源和跨平臺的數據訪問技術框架,但是在.Net 開發圈的評論卻褒貶不一。很多人認為EFCore 執行的效能比較差,很多複雜的查詢場景很難實現,也有很多人認為,EFCore 開發簡單,在中小型項目中,能夠快速的實現業務 ...
  • 運行環境:Window10,.Net7.0; 編者:烏龍哈里 2023-01-13 經常碰到兩個控制項需要相互傳遞消息,這時候需要用到事件。比如現有兩個類如下: class AA { private string Text = "AA"; public void SetText(string s) { ...
  • Cortex M 的中斷和優先順序, 首先要區分開 中斷 和 中斷優先順序 這是兩個不同的東西, 對於 Cortex-M0 和 Cortex-M0+ 內核, 除了系統內建中斷外, 支持最多 32 個中斷. 對於 Cortex-M3 內核, 除了 16 個內核中斷外, 支持最多 240 個中斷, 有8-b... ...
  • 分組數據 數據分組 分組允許把數據分為多個邏輯組,以便能對每個組進行聚集計算。 創建分組 分組是在SELECT語句的GROUP BY子句中建立的: SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id; 註意: GR ...
一周排行
    -Advertisement-
    Play Games
  • 一:背景 1. 講故事 年前遇到了好幾例托管堆被損壞的案例,有些運氣好一些,從被破壞的托管堆記憶體現場能觀測出大概是什麼問題,但更多的情況下是無法做出準確判斷的,原因就在於生成的dump是第二現場,借用之前文章的一張圖,大家可以理解一下。 為了幫助更多受此問題困擾的朋友,這篇來整理一下如何 快狠準 的 ...
  • 前言 .NET6 開始,.NET Croe API 項目取消了 Startup.cs 文件,在 Program.cs 文件的 Main 函數中完成服務的註冊和中間件管道的管理。但當我們項目引入更多包的時候,Program.cs 文件也會看起來很臃腫。 而且,我們不只會有一個後端項目,為了方便快速創建 ...
  • 目錄 背景 get 與 post 的區別 所有介面都用 post 請求? 背景 最近在逛知乎的時候發現一個有趣的問題:公司規定所有介面都用 post 請求,這是為什麼? 看到這個問題的時候其實我也挺有感觸的,因為我也曾經這樣問過我自己。在上上一家公司的時候接到一個項目是從零開始搭建一個微服務,當時就 ...
  • *以下內容為本人的學習筆記,如需要轉載,請聲明原文鏈接 微信公眾號「englyf」https://mp.weixin.qq.com/s/2GFLTstDC7w6u3fTJxflNA 本文大概 1685 個字,閱讀需花 6 分鐘內容不多, 但也花了一些精力如要交流, 歡迎關註我然後評論區留言 謝謝你的 ...
  • 在新版本的pandas中,上述代碼會引起警告,建議改成SQLAlchemy connectable(engine/connection),後續代碼將引入這種升級的連接方式。 ...
  • 幾乎所有的高級編程語言都有自己的垃圾回收機制,開發者不需要關註記憶體的申請與釋放,Python 也不例外。Python 官方團隊的文章 https://devguide.python.org/internals/garbage-collector 詳細介紹了 Python 中的垃圾回收演算法,本文是這篇 ...
  • 如果您想查找高於或低於平均值的數字,可以不必計算該平均值,就能查看更高或更低的值。通過Java應用程式,可以自動突出顯示這些數字。除了快速突出顯示高於或低於平均值的值外,您還可以查看高於或低於的值的個數。現在讓我們看看如何在 Java應用程式中實現此操作。 引入jar包 導入方法1: 手動引入。將  ...
  • 第一種方式:使用{} firstDict = {"name": "wang yuan wai ", "age" : 25} 說明:{}為創建一個空的字典對象 第二種方式:使用fromkeys()方法 second_dict = dict.fromkeys(("name", "age")) #valu ...
  • 在golang中可以使用a := b這種方式將b賦值給a,只有當b能進行深拷貝時a與b才不會互相影響,否則就需要進行更為複雜的深拷貝。 下麵就是Go賦值操作的一個說明: Go語言中所有賦值操作都是值傳遞,如果結構中不含指針,則直接賦值就是深度拷貝;如果結構中含有指針(包括自定義指針,以及切片,map ...
  • 本文結合京東監控埋點場景,對解決樣板代碼的技術選型方案進行分析,給出最終解決方案後,結合理論和實踐進一步展開。通過關註文中的技術分析過程和技術場景,讀者可收穫一種樣板代碼思想過程和解決思路,並對Java編譯器底層有初步瞭解。 ...