MySQL資料庫性能優化-有解無憂

前言

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

一、應用優化

　　1. 使用連接池

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

　　2. 避免對資料進行重復檢索

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

　　3. 增加cache層

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

　　4. 負載均衡

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

實作負債均衡的方式：

　　1.利用MySQL復制分流查詢
　　　通過MySQL的主從復制，實作讀寫分離，使增刪改操作走主節點，查詢操作走從節點，從而可以降低單臺服務器的讀寫壓力，

二、查詢快取優化（MySQL8.0：不在支持查詢快取；官方解釋）

開啟Mysql的查詢快取，當執行完全相同的SQL陳述句的時候，服務器就會直接從快取中讀取資料，當資料被修改，之前的快取會失敗，修改比較頻繁的表不適合做查詢快取，

開啟查詢快取后，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. 查詢快取失敗的情況

SQL陳述句不一致的情況，要想命中查詢快取，查詢的SQL陳述句必須一致，
當查詢陳述句中有一些不確定方法時，則不會快取，如：now(),current_date(),curdate(),curtome(),rand(),uuid(),user(),database()，
不使用任何表查詢陳述句，如 select 'A'
查詢mysql,information_schema或performance_schema資料庫中的表時，不會走查詢快取，
如果表更改，則使用該表的所有高速快取查詢都將變為無效并從高速快取中洗掉

三、記憶體管理及優化

　　1.記憶體優化原則

　　　a. 將盡量多的記憶體分配給MySQL做快取，但要給作業系統和其他程式預留足夠的記憶體，

　　　b. MyISAM存盤引擎的資料檔案讀取依賴于作業系統自身的IO快取，因此，如果有MyISAM表，就要預留更多的記憶體給作業系統做IO快取，

　　　c. 排序區、連接區等快取是分配給每個資料庫會話（session）專用的，其默認值的設定要根據最大連接數合理分配，如果設定太大，不但浪費資源，而且在并發連接較高時會導致物理記憶體耗盡，

　　2. MyISAM記憶體優化

myisam存盤引擎使用key_buffer快取索引塊，加速myisam索引的讀寫速度，等對于myisam表的資料塊，mysql沒有特別的快取機制，完全依賴于作業系統的IO快取，

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

　　3. InnoDB記憶體優化

innodb用一塊記憶體區做IO快取池，該快取池不僅用來快取innodb的索引塊，而且也用來快取innodb的資料塊，

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

四、并發引數調整

從實作上來說，MySQL Server 是多執行緒結構，包括后臺執行緒和客戶服務執行緒，多執行緒可以有效利用服務器資源，提高資料庫的并發性能，在Mysql中，控制并發連接和執行緒的主要引數包括 max_connections、back_log、thread_cache_size、table_open_cahce，

max_connections：采用max_connections 控制允許連接到MySQL資料庫的最大數量，默認值是 151，如果狀態變數connection_errors_max_connections 不為零，并且一直增長，則說明不斷有連接請求因資料庫連接數已達到允許最大值而失敗，這是可以考慮增大max_connections 的值，
```
 -- 64G記憶體最大連接數，并發連接數
max_connections=100000  

-- 8G記憶體配置
max_connections=5000
```
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
```
table_open_cache：該引數用來控制所有SQL陳述句執行執行緒可打開表快取的數量，而在執行SQL陳述句時，每一個SQL執行執行緒至少要打開 1 個表快取，該引數的值應該根據設定的最大連接數 max_connections 以及每個連接執行關聯查詢中涉及的表的最大數量來設定：
```
-- 64GB
table_open_cache=16384
-- 8GB
table_open_cache=2048
```
thread_cache_size：為了加快連接資料庫的速度，MySQL 會快取一定數量的客戶服務執行緒以備重用（類似執行緒池，避免重復打開關閉執行緒資源），通過引數 thread_cache_size 可控制 MySQL 快取客戶服務執行緒的數量，
```
-- 64GB
thread_cache_size=64
-- 8GB
table_open_cache=64
```
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)

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/473489.html

標籤：其他

上一篇：報告解讀下載 | 5月《中國資料庫行業分析報告》重磅發布！精彩搶先看！

下一篇：在不更改資料的情況下重塑numpy陣列

MySQL資料庫性能優化

前言

一、應用優化

1. 使用連接池

2. 避免對資料進行重復檢索

3. 增加cache層

4. 負載均衡

實作負債均衡的方式：

二、查詢快取優化（MySQL8.0：不在支持查詢快取；官方解釋）

開啟查詢快取后，MySQL查詢流程

2.查詢快取配置

5. 查詢快取失敗的情況

三、記憶體管理及優化

1.記憶體優化原則

2. MyISAM記憶體優化

3. InnoDB記憶體優化

四、并發引數調整

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

　　1. 使用連接池

　　2. 避免對資料進行重復檢索

　　3. 增加cache層

　　4. 負載均衡

　　2.查詢快取配置

　　5. 查詢快取失敗的情況

　　1.記憶體優化原則

　　2. MyISAM記憶體優化

　　3. InnoDB記憶體優化