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