介紹MySQL大表優化方案

#免費學習推薦：mysql資料庫(視頻)

背景

阿里雲RDS FOR MySQL（MySQL5.7版本）資料庫業務表每月新增資料量超過千萬,隨著資料量持續增加,我們業務出現大表慢查詢,在業務高峰期主業務表的慢查詢需要幾十秒嚴重影響業務

##方案概述

一、資料庫設計及索引最佳化

MySQL資料庫本身高度靈活，造成效能不足，嚴重依賴開發人員的表格設計能力以及索引最佳化能力,在這裡給幾點優化建議

時間類型轉換為時間戳格式,用int類型儲存,建索引增加查詢效率
• 建議欄位定義not null，null值很難查詢最佳化且佔用額外的索引空間
• 使用TINYINT類型取代枚舉ENUM
• #儲存精確浮點數必須使用DECIMAL取代FLOAT和DOUBLE
• #欄位長度嚴重根據業務需求來,不要設定過大
• 盡量不要使用TEXT類型，如必須使用建議將不常用的大字段拆分到其它表
• MySQL對索引字段長度是有限制的, innodb引擎的每個索引列長度預設限制為767位元組（bytes），所有組成索引列的長度和不能大於3072位元組(mysql8.0單索引可以建立1024字元)
• 大表有DDL需求時請聯絡DBA

最左索引匹配規則

顧名思義就是最左優先，在建立組合索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。複合索引很重要的問題是如何安排列的順序，例如where後面用到c1, c2 這兩個字段，那麼索引的順序是(c1,c2)還是(c2,c1)呢，正確的做法是，重複值越少的越放前面，例如一個列95%的值都不重複，那麼一般可以將這個列放最前面

複合索引index(a,b,c)
where a=3 只使用了a
• where a=3 and b=5 使用了a,b
• where a=3 and b=5 and c=4 使用了a, b,c
• where b=3 or where c=4 沒有使用索引
• where a=3 and c=4 僅使用了a
• where a=3 and b> 10 and c=7 使用了a,b
• where a=3 and b like 'xx%' and c=7 使用了a,b
• 其實相當於創建了多個索引：key (a)、key(a,b)、key(a,b,c)

#二、資料庫切換到PloarDB讀寫分離

PolarDB是阿里雲自研的下一代關聯式雲端資料庫，100%相容MySQL儲存容量最高可達100 TB，單庫最多可擴充到16個節點，適用於企業多樣化的資料庫應用場景。 PolarDB採用儲存和計算分離的架構，所有計算節點共享一份數據，提供分鐘級的配置升降級、秒級的故障恢復、全局數據一致性和免費的數據備份容災服務。

叢集架構，運算與儲存分離
• PolarDB採用多節點叢集的架構，叢集中有一個Writer節點（主節點）和多個Reader節點（唯讀節點），各節點透過分散式檔案系統（PolarFileSystem）共享底層的儲存（PolarStore）
  
讀寫分離
• 當應用程式使用叢集位址時，PolarDB透過內部的代理層（Proxy）對外提供服務，應用程式的請求都先經過代理，然後才存取到資料庫節點。代理層不僅可以做安全性認證和保護，還可以解析SQL，把寫入操作（例如交易、UPDATE、INSERT、DELETE、DDL等）送到主節點，把讀取操作（例如SELECT）均衡地分發到多個隻讀節點，實現自動的讀寫分離。對於應用程式來說，就像使用一個單點的資料庫一樣簡單。
  

在離線混合場景：不同業務用不同的連接位址，使用不同的資料節點，避免相互影響

Sysbench效能壓測報告：

PloarDB 4核心16G 2台

##PloarDB 8核心32G 2台

三、分錶歷史資料遷移到MySQL8.0 X-Engine儲存引擎

分錶業務表保留3個月資料(這個根據公司需求來),歷史資料按月分錶到歷史庫X-Engine儲存引擎表, 為什麼要選用X-Engine儲存引擎表,它有什麼優點?

1. 節約成本, X-Engine的儲存成本約為InnoDB的一半
2. X-Engine分層儲存提高QPS, 採用層次化的儲存結構，將熱資料與冷資料分別存放在不同的層次中，並預設對冷資料所在層級進行壓縮

X-Engine是阿里雲資料庫產品事業部自研的線上事務處理OLTP（On-Line Transaction Processing）資料庫儲存引擎。
X-Engine儲存引擎不僅可以無縫對接相容MySQL（得益於MySQL Pluginable Storage Engine特性），同時X-Engine使用分層儲存架構。因為目標是面向大規模的海量數據存儲，提供高並發事務處理能力和降低存儲成本，在大部分大數據量場景下，數據被訪問的機會是不均等的，訪問頻繁的熱數據實際上佔比很少，X-Engine根據資料存取頻度的不同將資料劃分為多​​個層次，針對每個層次資料的存取特點，設計對應的儲存結構，寫入適當的儲存裝置

• #X-Engine使用了LSM-Tree作為分層存儲的架構基礎，並進行了重新設計：
• 熱數據層和數據更新使用內存存儲，通過內存數據庫技術（Lock-Free index structure/ append only）提高事務處理的效能。
• 管線事務處理機制，把事務處理的幾個階段並行起來，大大提升了吞吐。
• 存取頻度低的資料逐漸淘汰或是合併到持久化的儲存層次中，並結合多層次的儲存設備（NVM/SSD/HDD）進行儲存。
• 對效能影響比較大的Compaction過程做了大量最佳化：
• 分割資料儲存粒度，利用資料更新熱點較為集中的特徵，盡可能的在合併過程中復用數據。
• 精細化控制LSM的形狀，減少I/O和計算代價，有效緩解了合併過程中的空間增大。
• 同時使用更細粒度的存取控制和快取機制，優化讀取的效能。

四、阿里雲PloarDB MySQL8.0版本並行查詢

分錶之後我們的資料量依然很大,並沒有完全解決我們的慢查詢問題,只是降低了我們業務表的體量,這部分慢查詢我們需要用到PolarDB的並行查詢優化

PolarDB MySQL 8.0重磅推出並行查詢框架，當您的查詢資料量到達一定閾值，就會自動啟動並行查詢框架，從而使查詢耗時指數級下降
在存儲層將資料分片到不同的線程上，多個線程並行計算，將結果管線匯總到總線程，最後總線程做些簡單歸回給用戶，提高查詢效率。
平行查詢（Parallel Query）利用多核心CPU的平行處理能力，以8核心32 GB配置為例，示意圖如下。

並行查詢適用於大部分SELECT語句，例如大表查詢、多表連接查詢、計算量較大的查詢。對於非常短的查詢，效果不太顯著。

並行查詢用法，使用Hint語法可以對單一語句進行控制，例如係統預設關閉並行查詢情況下，但需要對某個高頻的慢SQL查詢進行加速，此時就可以使用Hint對特定SQL進行加速。

SELECT / PARALLEL(x)/ … FROM …; – x >0

SELECT /* SET_VAR(max_parallel_degree=n) */ * FROM … // n > 0

查詢測試：資料庫配置16核心32G 單表資料量超3千萬

沒加並行查詢之前是4326ms，加了之後是525ms，效能提升8.24倍

五、互動式分析Hologre

大表慢查詢我們雖然用並行查詢優化提升了效率,但是一些特定的需求實時報表、實時大屏我們還是無法實現，只能依賴大數據去處理。
這裡推薦大家阿里雲的互動式分析Hologre（
https://help.aliyun.com/product/113622.html）

六、後記

千萬級大表優化是根據業務場景，以成本為代價優化的，不是一上來就資料庫水平切分擴展，這樣會給運維和業務帶來巨大挑戰，很多時候效果不一定好，我們的資料庫設計、索引最佳化、分錶策略是否做到位了，應該根據業務需求選擇合適的技術去實現。

更多相關免費學習推薦：mysql教學#(影片)

#

以上是介紹MySQL大表優化方案的詳細內容。更多資訊請關注PHP中文網其他相關文章！