MySQL資料庫性能優化

来源:https://www.cnblogs.com/yl0604/archive/2022/05/13/16266493.html
-Advertisement-
Play Games

前言 由於部分企業要求本地部署系統(使用企業伺服器進行部署系統且資料庫也部署在同台伺服器),本地部署系統的伺服器往往達不到我們的雲部署伺服器,速度性能更是有所欠缺,特別是在查詢統計報表的時候,雲上幾秒鐘的速度,本地企業需要幾分鐘以上,所以最近對企業資料庫進行了性能優化,簡單一點其實主要進行查詢緩存優 ...


前言

由於部分企業要求本地部署系統(使用企業伺服器進行部署系統且資料庫也部署在同台伺服器),本地部署系統的伺服器往往達不到我們的雲部署伺服器,速度性能更是有所欠缺,特別是在查詢統計報表的時候,雲上幾秒鐘的速度,本地企業需要幾分鐘以上,所以最近對企業資料庫進行了性能優化,簡單一點其實主要進行查詢緩存優化和記憶體管理優化速度便可以明顯提升。

一、應用優化

 

  1. 使用連接池

    對於訪問資料庫來說,建立連接的代價是比較昂貴的,因為我們頻繁的創建關閉連接,是比較耗費資源的。

  2. 避免對數據進行重覆檢索

    能夠一次性從資料庫中讀取的,不要分多次讀取,最好一次讀取完成。

  3. 增加cache層

    在應用中,我們可以在應用中增加 緩存 層來達到減輕資料庫負擔的目的。緩存層有很多種,也有很多實現方式,只要能達到降低資料庫的負擔又能滿足應用需求就可以。因此可以部分數據從資料庫中抽取出來放到應用端以文本方式存儲, 或者使用框架(Mybatis, Hibernate)提供的一級緩存/二級緩存,或者使用redis資料庫來緩存數據 。

  4. 負載均衡

  負載均衡是應用中使用非常普遍的一種優化方法,它的機制就是利用某種均衡演算法,將固定的負載量分佈到不同的伺服器上, 以此來降低單台伺服器的負載,達到優化的效果。

實現負債均衡的方式:

  1.利用MySQL複製分流查詢
   通過MySQL的主從複製,實現讀寫分離,使增刪改操作走主節點,查詢操作走從節點,從而可以降低單台伺服器的讀寫壓力。

 

 

 

 

 

二、查詢緩存優化(MySQL8.0:不在支持查詢緩存;官方解釋

開啟Mysql的查詢緩存,當執行完全相同的SQL語句的時候,伺服器就會直接從緩存中讀取數據,當數據被修改,之前的緩存會失敗,修改比較頻繁的表不適合做查詢緩存。

  1. 開啟查詢緩存後,MySQL查詢流程

    a. 客戶端發送一條查詢給伺服器

    b. 伺服器先回檢查查詢緩存,如果命中了緩存,則立即返回儲存在緩存中的結果。否則進入下一階段;

    c. 伺服器端進行SQL解析、預處理,再由優化器生成對應的執行計劃;

    d. MySQL根據優化器生成的執行計劃,調用存儲引擎的API來執行查詢;

    e. 將結果返回給客戶端,同時將查詢結果放入緩存

  2.查詢緩存配置

  • 查看當前的MySQL是否支持查詢緩存(YES/NO)
show variables like 'have_query_cache';
  • 查看當前MySQL是否開啟了查詢緩存(OFF/NO)
show variables like 'query_cache_type';
  • 查看查詢緩存的占用大小
show VARIABLES like 'query_cache_size';
  • 查詢緩存的狀態變數
show status like 'Qcache%';

  3. 開啟查詢緩存配置

Linux:在/etc/my.cnf配置中,Windows:在my.ini配置中,添加以下配置(配置完畢之後,重啟伺服器即可生效):

query-cache-type=1

  4. select可以指定不適用緩存

sql_cache:如果查詢結果是可緩存的,並且query_cache_type系統變數的值為ON或DEMAND,則緩存查詢結果。

sql_no_cache:伺服器不適用查詢緩存。它既不檢查查詢緩存,也不檢查結果是否已緩存,也不緩存查詢結果。

SELECT SQL_CACHE id,name FROM customer;
SELECT SQL_NO_CACHE id,name FROM customer;

  5. 查詢緩存失敗的情況

  1. SQL語句不一致的情況,要想命中查詢緩存,查詢的SQL語句必須一致。
  2. 當查詢語句中有一些不確定方法時,則不會緩存。如:now(),current_date(),curdate(),curtome(),rand(),uuid(),user(),database()。
  3. 不使用任何表查詢語句。如 select 'A'
  4. 查詢mysql,information_schema或performance_schema資料庫中的表時,不會走查詢緩存。
  5. 如果表更改,則使用該表的所有高速緩存查詢都將變為無效並從高速緩存中刪除

三、記憶體管理及優化

  1.記憶體優化原則

   a. 將儘量多的記憶體分配給MySQL做緩存,但要給操作系統和其他程式預留足夠的記憶體。

   b. MyISAM存儲引擎的數據文件讀取依賴於操作系統自身的IO緩存,因此,如果有MyISAM表,就要預留更多的記憶體給操作系統做IO緩存。

   c. 排序區、連接區等緩存是分配給每個資料庫會話(session)專用的,其預設值的設置要根據最大連接數合理分配,如果設置太大,不但浪費資源,而且在併發連接較高時會導致物理記憶體耗盡。

  2. MyISAM記憶體優化

myisam存儲引擎使用key_buffer緩存索引塊,加速myisam索引的讀寫速度,等對於myisam表的數據塊,mysql沒有特別的緩存機制,完全依賴於操作系統的IO緩存。

  1. key_buffer_size決定MyISAM索引塊緩存區的大小,直接影響到MyISAM表的存取效率。修改其記憶體大小,可以在my.ini/cnf中做如下配置:key_buffer_size=512
  2. read_buffer_size:如果需要經常順序掃描myisam表,可以通過增大read_buffer_size值來改善性能。但需要註意的是read_buffer_size是每個session獨占的,如果預設值設置太大,就會造成記憶體浪費。
  3. read_rnd_buffer_size:對於需要做排序的myisam表的查詢,如帶有order by子句的sql,適當增加read_rnd_buffer_size的值,可以改善此類sql性能。但需要註意的是read_rnd_buffer_size是每個session獨占的,如果預設值設置太大,就會造成記憶體浪費。

  3. InnoDB記憶體優化

innodb用一塊記憶體區做IO緩存池,該緩存池不僅用來緩存innodb的索引塊,而且也用來緩存innodb的數據塊。

  1. innodb_buffer_pool_size:該變數決定了innodb存儲引擎表數據和索引數據的最大緩存區大小。在保證操作系統及其他程式有足夠記憶體可用的情況下,innodb_buffer_pool_size的值越大,緩存命中率越高,訪問InnoDB表需要的磁碟I/O就越少,性能也能越高。
  2. innodb_log_buffer_size:決定了innodb重做日誌緩存的大小,對於可能產生大量更新記錄的大事務,增加innodb_log_buffer_size的大小,可以避免innodb在事務提交前就執行不必要的日誌寫入磁碟操作。

四、併發參數調整

從實現上來說,MySQL Server 是多線程結構,包括後臺線程和客戶服務線程。多線程可以有效利用伺服器資源,提高資料庫的併發性能。在Mysql中,控制併發連接和線程的主要參數包括 max_connections、back_log、thread_cache_size、table_open_cahce。

  1. max_connections:採用max_connections 控制允許連接到MySQL資料庫的最大數量,預設值是 151。如果狀態變數connection_errors_max_connections 不為零,並且一直增長,則說明不斷有連接請求因資料庫連接數已達到允許最大值而失敗,這是可以考慮增大max_connections 的值。
     -- 64G記憶體最大連接數,併發連接數
    max_connections=100000  
    
    -- 8G記憶體配置
    max_connections=5000
  2. back_log:back_log 參數控制MySQL監聽TCP埠時設置的積壓請求棧大小。如果MySql的連接數達到max_connections時,新來的請求將會被存在堆棧中,以等待某一連接釋放資源後,該堆棧中的請求去連接,該堆棧的數量即back_log,如果等待連接的數量超過back_log,將不被授予連接資源,將會報錯。5.6.6 版本之前預設值為 50 , 之後的版本預設為 50 +(max_connections / 5), 但最大不超過900。
     -- 64GB
    back_log=4096 
    -- 8GB
    back_log=500
  3. table_open_cache:該參數用來控制所有SQL語句執行線程可打開表緩存的數量, 而在執行SQL語句時,每一個SQL執行線程至少要打開 1 個表緩存。該參數的值應該根據設置的最大連接數 max_connections 以及每個連接執行關聯查詢中涉及的表的最大數量來設定 :
    -- 64GB
    table_open_cache=16384
    -- 8GB
    table_open_cache=2048
  4. thread_cache_size:為了加快連接資料庫的速度,MySQL 會緩存一定數量的客戶服務線程以備重用(類似線程池,避免重覆打開關閉線程資源),通過參數 thread_cache_size 可控制 MySQL 緩存客戶服務線程的數量
    -- 64GB
    thread_cache_size=64
    -- 8GB
    table_open_cache=64
  5. innodb_lock_wait_timeout:該參數是用來設置InnoDB 事務等待行鎖的時間,預設值是50ms , 可以根據需要進行動態設置。對於需要快速反饋的業務系統來說,可以將行鎖的等待時間調小,以避免事務長時間掛起; 對於後臺運行的批量處理程式來說,可以將行鎖的等待時間調大, 以避免發生大的回滾操作。
    -- 64GB
    innodb_lock_wait_timeout=30s
    -- 8GB
    innodb_lock_wait_timeout=30s

 

(不同記憶體系統具體參數調優可參考https://www.it610.com/article/1174760213530599424.htm)


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

-Advertisement-
Play Games
更多相關文章
  • 最近使用WPF開發項目,為了對WPF知識點進行總結,所以利用業餘時間,開發一個學生信息管理系統【Student Information Management System】。本文主要簡述如何通過WPF+Prism+MAH+WebApi進行開發基於三層架構的桌面版應用程式,僅供學習分享使用,如有不足之... ...
  • extcon,是External Connector的簡稱,用於抽象外部連接器,比如說Audio Jack、USB MicroB/TypeC介面等。 ...
  • 一、輸入重定向和輸出重定向 輸入重定向就是把文件作為命令的參數,輸出重定向就是把原本要輸出到屏幕上的內容寫到文件裡面。 (1)輸入重定向: (2)輸出重定向; 對於重定向中的標準輸出模式,可以省略文件描述符1不寫,而錯誤輸出模式的文件描述符2是必須要寫的。 示例: 重定向中的覆蓋寫入和追加寫入的不同 ...
  • 7、 1 滑鼠 // 7-1-滑鼠.cpp : 定義應用程式的入口點。 // #include "framework.h" #include "7-1-滑鼠.h" #define MAX_LOADSTRING 100 #define MAXPOINTS 1000 // 全局變數: HINSTANCE ...
  • 一、安裝zabbix-server 操作系統:CentOS 7.5 1、首先關閉防火牆與SElinux 關閉防火牆 systemctl stop firewalld&&systemctl disable firewalld 關閉SELinux sed -i 's/SELINUX=enforcing/ ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 一、前言 就是我們需要集群部署hadoop,Flink時,寫ip地址太長了,然後大家想的就是能不能用比如:node1,node2,node3去代替IP地址,在一個區域網中,每台機器都有一個主機名,便於主機與主機之間的區分,因此為每台機器設置主機名 ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 最近換了台新電腦,系統是Win11的,因為之前用Win10的時候,基本都是裝上就能用的,Win11裝好了一打開突然就重啟了,還是有一點驚嚇的。 百度搜索問題,找到了的解決辦法大致分為兩個方面: 1.當前使用的電腦是否支持虛擬化 1.1 CPU是 ...
  • 本文先給出“win10找不到無線網路報錯”的通用解決方案,併在方案中介紹本次出現的“Windows無法自動將IP協議堆棧綁定到網路適配器”問題。 ...
一周排行
    -Advertisement-
    Play Games
  • 分組和樹形結構是不一樣的。 樹形結構是以遞歸形式存在。分組是以鍵值對存在的形式,類似於GroupBy這樣的形式。 舉個例子 ID NAME SEX Class 1 張三 男 1 2 李四 女 2 3 王二 男 1 當以Sex為分組依據時則是 Key Value 男 1 張三 男 1 3 王二 男 1 ...
  • NetCore中將SQLServer資料庫備份為Sql腳本 描述: 最近寫項目收到了一個需求, 就是將SQL Server資料庫備份為Sql腳本, 如果是My Sql之類的還好說, 但是在網上搜了一大堆, 全是教你怎麼操作SSMS的, 就很d疼! 解決方案: 通過各種查找資料, 還有一些老哥的幫助, ...
  • 我的Notion Clowd.Squirrel Squirrel.Windows 是一組工具和適用於.Net的庫,用於管理 Desktop Windows 應用程式的安裝和更新。 Squirrel.Windows 對 Windows 應用程式的實現語言沒有任何要求,甚至無需服務端即可完成增量更新。 ...
  • 轉載請註明來源 https://www.cnblogs.com/brucejiao/p/16188865.html 謝謝! 轉載請註明來源 https://www.cnblogs.com/brucejiao/p/16188865.html 謝謝! 轉載請註明來源 https://www.cnblog ...
  • 1. Netty源碼研究筆記(3)——Channel系列 依舊是通過先縱向再橫向的研究方法,在開篇中,我們發現不管是Sever還是Client,最終的啟動是通過調用channel的對應方法來完成的,而這個動作實際在channel綁定的eventLoop中執行。 接下來,我們繼續EchoSever、E ...
  • 大家好,今天給大家介紹一款輕量、快速、穩定可編排的組件式規則引擎框架LiteFlow。 一、LiteFlow的介紹 LiteFlow官方網站和代碼倉庫地址 官方網站:https://yomahub.com/liteflow Gitee托管倉庫:https://gitee.com/dromara/li ...
  • 我使用Spring AOP實現了用戶操作日誌功能 今天答辯完了,復盤了一下系統,發現還是有一些東西值得拿出來和大家分享一下。 需求分析 系統需要對用戶的操作進行記錄,方便未來溯源 首先想到的就是在每個方法中,去實現記錄的邏輯,但是這樣做肯定是不現實的,首先工作量大,其次違背了軟體工程設計原則(開閉原 ...
  • 《零基礎學Java》 繪製幾何圖形 Java可以分別使用 Graphics 和 Graphics2D 繪製圖形,Graphics類 使用不同的方法繪製不同的圖形(drawLine()方法可f以繪製線、drawRect()方法用於繪製矩形、drawOval()方法用於繪製橢圓形)。 Graphics類 ...
  • 本期教程人臉識別第三方平臺為虹軟科技,本文章講解的是人臉識別RGB活體追蹤技術,免費的功能很多可以自行搭配,希望在你看完本章課程有所收穫。 ...
  • 很多人都喜歡使用黑色的主題樣式,包括我自己,使用了差不多三年的黑色主題,但是個人覺得在進行視窗轉換的時候很廢眼睛。 比如IDEA是全黑的,然後需要看PDF或者WORD又變成白色的了,這樣來回切換導致眼睛很累,畢竟現在網頁以及大部分軟體的界面都是白色的。那麼還是老老實實的使用原來比較順眼的模式吧。 1 ...