提高資料庫服務讀寫效能

提高資料庫服務讀寫效能

單臺（機）資料庫

一般資料庫讀寫是系統的瓶頸，也是最佳化收益最高的，單點最多的

1。查詢最佳化：針對select，以下維度

主鍵查詢 千萬條記錄1-10ms

唯一索引 千萬條記錄10-100ms；與唯一索引的差別為非聚簇索引，需要增加一次定址

普通索引（非唯一） 千萬條記錄100-1000ms

無 百萬條記錄1000ms+

2。批次寫：插入無法使用索引時的最佳化，批次寫是最佳化收益最高的一種方案

for each {insert into table values （） }，損耗最高，建議換成Execute once insert into table values （），（*），…；

以下維度

sql 編譯一次與N的時間與空間複雜度

網路消耗的時間複雜度

磁碟定址的複雜度

3。索引最佳化，檢視往期 https：//blog。csdn。net/weixin_49759486/article/details/123120024

4。innodb儲存引擎最佳化；配置最佳化

max_connection=1000 增加最大連線數，之前的版本預設為100，在海量的資料庫對接的應用伺服器叢集當中太少，1000位經驗判斷，還是需要根據物理機器資源情況作比較

innodb_file_per_table=1可以儲存每個innodb表和它的索引和b+樹獨立在自己的檔案中，減少定址和讀寫的開銷

innodb_buffer_pool_size=1G快取池大小，此引數非常關鍵，設定為當前資料庫服務記憶體的60-80%；決定innodb效能的好壞

innodb_log_file_size=256M一般取256M可以兼顧效能和recovery的速度，寫滿後只能切換日誌靠buffer儲存；重新整理時提交磁碟資料，設定大時會延遲log_file被列印滿之後所造成的事務卡頓，太大的話recovery的速度就會變慢，所有一般設定256

innodb_log_buffer_size=16M 此引數也很重要，該引數確保有足夠大的日誌緩衝區來保證髒資料在被寫入到日誌檔案之前可以繼續mysql事務操作，也可以儲存日誌的中間資料

innodb_flush_log_at_trx_commit=2

設定0時，效率更高但安全性差。每秒才write日誌，任何mysql程序的崩潰會刪除崩潰前最後一秒的事務。

設定1時，在每個事務提交時，日誌緩衝被寫到日誌檔案，對日誌檔案做到磁碟操作的重新整理。Truly ACID。速度慢。一般不會設定為1。

設定2時，在每個事務提交時，日誌緩衝被寫到系統緩衝（開啟作業系統的檔案控制代碼，寫到一個系統檔案當中），但不對日誌檔案做到磁碟操作的重新整理。然後根據innodb_flush_log_at_timeout（預設為1秒）時間flush disk只有作業系統崩潰或掉電才會刪除最後一秒的事務，不然不會丟失事務。一般設定為2，並且啟用備用電源儘可能的規避掉最後一秒資料的丟失問題

innodb_data_file_path-ibdata1：1G；ibdata2：1G；ibdata3：1G：autoextend 指定表資料和謳吟儲存的空間，可以使一個或多個檔案；做資料歸檔使用；表示提前設定3個，之後自動增長，保證每個檔案不會太大，降低磁碟定址的時間。

MySQL讀寫分離

master和slave透過bin log同步，寫時候寫入master，讀slave

一主多從；一個master對應多個slave；讀操作開啟事務會路由到master，未開啟事務路由到slave

讀庫延遲問題處理；mysql基本可以同步到ms級別

主從切換處理；master異常時可以將其中一個slave提升為master；mysql提供了半同步的解決方案：至少一個slave同步成功，master才提交事務，尋找bin log最靠前的

分庫分表

垂直拆分：同類型的放在一起，join三層以內，小表驅動大表，不要誇庫join

水平拆分：同一張表拆成多分資料，例如以不同的時間拆分成不同的表

多主多從

快取穿透、擊穿、雪崩

穿透：資料庫中資料不存在，前段使用者或者上游服務去訪問不存在的資料，造成快取的形同虛設，每次都查詢資料庫；嘗試設定無效標誌位代表資料不存在到快取

擊穿：同一資料擊穿到資料庫，同時訪問同一資料時快取不存在直接查詢資料庫，資料庫資料存在，但是同一時間多個請求，快取剛好到期的情況，多個請求都會查詢資料庫；使用排隊佇列解決，快取不存在時查詢放到佇列中聚集一下；也可同一資料加鎖

雪崩：不同的資料擊穿到資料庫，造成整個快取服務的宕機；大批次的key同一時間失效，同一時間超多請求；不讓大批次的key同一時間失效+randow；

快取髒讀和多級快取

1。髒資料問題

髒讀產生的原因：資料更新而快取未更新

髒讀如何避免：後臺資料更新時，通知快取，可清楚或直接更新

2。多級快取

前臺：前段頁面，比如提前靜態化好html放到cdn中，資料更新時更新cdn，可以保留一個check做緊急下架動作等

中臺：nginx；java建議放在java堆內或者堆外處理邏輯

後臺：redis和mysql，redis作快取