python

MySQL大表最佳化方案

背景

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

方案概述

20201030141518

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

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)均衡地分發到多個只讀節點,實現自動的讀寫分離。對於應用程式來說,就像使用一個單點的資料庫一樣簡單。

在離線混合場景:不同業務用不同的連線地址,使用不同的資料節點,避免相互影響

20201029160013

Sysbench效能壓測報告:

  • PloarDB 4核16G 2臺

20201029160525
20201029160550

  • PloarDB 8核32G 2臺

20201029160755
20201029160845

三、分表歷史資料遷移到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和計算代價,有效緩解了合併過程中的空間增大。
  • 同時使用更細粒度的訪問控制和快取機制,最佳化讀的效能。

20201029162440

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

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

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

20201029163124

並行查詢適用於大部分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倍

lALPDhmOtqINirTNAl_NBIw_1164_607

lALPDgQ9vsVjxDbNAl7NBHk_1145_606

五、互動式分析Hologre

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

20201030151537

六、後記

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

本文章已修改原文用詞符合繁體字使用者習慣使其容易閱讀

版權宣告:此處為CSDN博主「福祿網路研發團隊」的原創文章,依據CC 4.0 BY-SA版權協議,轉載請附上原文出處連結及本宣告。

原文連結:https://blog.csdn.net/fuluadmin/article/details/109443822