淺談MySQL大表優(yōu)化方案

背景

阿里云RDS FOR MySQL（MySQL5.7版本）數(shù)據(jù)庫業(yè)務(wù)表每月新增數(shù)據(jù)量超過千萬,隨著數(shù)據(jù)量持續(xù)增加,我們業(yè)務(wù)出現(xiàn)大表慢查詢,在業(yè)務(wù)高峰期主業(yè)務(wù)表的慢查詢需要幾十秒嚴重影響業(yè)務(wù)

方案概述

一、數(shù)據(jù)庫設(shè)計及索引優(yōu)化

MySQL數(shù)據(jù)庫本身高度靈活，造成性能不足，嚴重依賴開發(fā)人員的表設(shè)計能力以及索引優(yōu)化能力,在這里給幾點優(yōu)化建議

• 時間類型轉(zhuǎn)化為時間戳格式,用int類型儲存,建索引增加查詢效率
• 建議字段定義not null，null值很難查詢優(yōu)化且占用額外的索引空間
• 使用TINYINT類型代替枚舉ENUM
• 存儲精確浮點數(shù)必須使用DECIMAL替代FLOAT和DOUBLE
• 字段長度嚴重根據(jù)業(yè)務(wù)需求來,不要設(shè)置過大
• 盡量不要使用TEXT類型，如必須使用建議將不常用的大字段拆分到其它表
• MySQL對索引字段長度是有限制的, innodb引擎的每個索引列長度默認限制為767字節(jié)（bytes），所有組成索引列的長度和不能大于3072字節(jié)(mysql8.0單索引可以創(chuàng)建1024字符)
• 大表有DDL需求時請聯(lián)系DBA
  

最左索引匹配規(guī)則

顧名思義就是最左優(yōu)先，在創(chuàng)建組合索引時，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊。復(fù)合索引很重要的問題是如何安排列的順序，比如where后面用到c1, c2 這兩個字段，那么索引的順序是(c1,c2)還是(c2,c1)呢，正確的做法是，重復(fù)值越少的越放前面，比如一個列 95%的值都不重復(fù)，那么一般可以將這個列放最前面

• 復(fù)合索引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
• 其實相當(dāng)于創(chuàng)建了多個索引：key(a)、key(a,b)、key(a,b,c)

二、數(shù)據(jù)庫切換到PloarDB讀寫分離

PolarDB是阿里云自研的下一代關(guān)系型云數(shù)據(jù)庫，100%兼容MySQL存儲容量最高可達100 TB，單庫最多可擴展到16個節(jié)點，適用于企業(yè)多樣化的數(shù)據(jù)庫應(yīng)用場景。PolarDB采用存儲和計算分離的架構(gòu)，所有計算節(jié)點共享一份數(shù)據(jù)，提供分鐘級的配置升降級、秒級的故障恢復(fù)、全局數(shù)據(jù)一致性和免費的數(shù)據(jù)備份容災(zāi)服務(wù)。

集群架構(gòu)，計算與存儲分離
PolarDB采用多節(jié)點集群的架構(gòu)，集群中有一個Writer節(jié)點（主節(jié)點）和多個Reader節(jié)點（只讀節(jié)點），各節(jié)點通過分布式文件系統(tǒng)（PolarFileSystem）共享底層的存儲（PolarStore）

讀寫分離
當(dāng)應(yīng)用程序使用集群地址時，PolarDB通過內(nèi)部的代理層（Proxy）對外提供服務(wù)，應(yīng)用程序的請求都先經(jīng)過代理，然后才訪問到數(shù)據(jù)庫節(jié)點。代理層不僅可以做安全認證和保護，還可以解析SQL，把寫操作（例如事務(wù)、UPDATE、INSERT、DELETE、DDL等）發(fā)送到主節(jié)點，把讀操作（例如SELECT）均衡地分發(fā)到多個只讀節(jié)點，實現(xiàn)自動的讀寫分離。對于應(yīng)用程序來說，就像使用一個單點的數(shù)據(jù)庫一樣簡單。

在離線混合場景：不同業(yè)務(wù)用不同的連接地址，使用不同的數(shù)據(jù)節(jié)點，避免相互影響

Sysbench性能壓測報告：

PloarDB 4核16G 2臺

PloarDB 8核32G 2臺

三、分表歷史數(shù)據(jù)遷移到MySQL8.0 X-Engine存儲引擎

分表業(yè)務(wù)表保留3個月數(shù)據(jù)(這個根據(jù)公司需求來),歷史數(shù)據(jù)按月分表到歷史庫X-Engine存儲引擎表, 為什么要選用X-Engine存儲引擎表,它有什么優(yōu)點?

節(jié)約成本, X-Engine的存儲成本約為InnoDB的一半

X-Engine分層存儲提高QPS, 采用層次化的存儲結(jié)構(gòu)，將熱數(shù)據(jù)與冷數(shù)據(jù)分別存放在不同的層次中，并默認對冷數(shù)據(jù)所在層次進行壓縮

X-Engine是阿里云數(shù)據(jù)庫產(chǎn)品事業(yè)部自研的聯(lián)機事務(wù)處理OLTP（On-Line Transaction Processing）數(shù)據(jù)庫存儲引擎。
X-Engine存儲引擎不僅可以無縫對接兼容MySQL（得益于MySQL Pluginable Storage Engine特性），同時X-Engine使用分層存儲架構(gòu)。因為目標(biāo)是面向大規(guī)模的海量數(shù)據(jù)存儲，提供高并發(fā)事務(wù)處理能力和降低存儲成本，在大部分大數(shù)據(jù)量場景下，數(shù)據(jù)被訪問的機會是不均等的，訪問頻繁的熱數(shù)據(jù)實際上占比很少，X-Engine根據(jù)數(shù)據(jù)訪問頻度的不同將數(shù)據(jù)劃分為多個層次，針對每個層次數(shù)據(jù)的訪問特點，設(shè)計對應(yīng)的存儲結(jié)構(gòu)，寫入合適的存儲設(shè)備

• X-Engine使用了LSM-Tree作為分層存儲的架構(gòu)基礎(chǔ)，并進行了重新設(shè)計：
• 熱數(shù)據(jù)層和數(shù)據(jù)更新使用內(nèi)存存儲，通過內(nèi)存數(shù)據(jù)庫技術(shù)（Lock-Free index structure/append only）提高事務(wù)處理的性能。
• 流水線事務(wù)處理機制，把事務(wù)處理的幾個階段并行起來，極大提升了吞吐。
• 訪問頻度低的數(shù)據(jù)逐漸淘汰或是合并到持久化的存儲層次中，并結(jié)合多層次的存儲設(shè)備（NVM/SSD/HDD）進行存儲。
• 對性能影響比較大的Compaction過程做了大量優(yōu)化：
• 拆分數(shù)據(jù)存儲粒度，利用數(shù)據(jù)更新熱點較為集中的特征，盡可能的在合并過程中復(fù)用數(shù)據(jù)。
• 精細化控制LSM的形狀，減少I/O和計算代價，有效緩解了合并過程中的空間增大。
• 同時使用更細粒度的訪問控制和緩存機制，優(yōu)化讀的性能。
  

四、阿里云PloarDB MySQL8.0版本并行查詢

分表之后我們的數(shù)據(jù)量依然很大,并沒有完全解決我們的慢查詢問題,只是降低了我們業(yè)務(wù)表的體量,這部分慢查詢我們需要用到PolarDB的并行查詢優(yōu)化

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

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

并行查詢用法，使用Hint語法可以對單個語句進行控制，例如系統(tǒng)默認關(guān)閉并行查詢情況下，但需要對某個高頻的慢SQL查詢進行加速，此時就可以使用Hint對特定SQL進行加速。

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

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

查詢測試：數(shù)據(jù)庫配置 16核32G 單表數(shù)據(jù)量超3千萬

沒加并行查詢之前是4326ms，加了之后是525ms，性能提升8.24倍

五、交互式分析Hologre

大表慢查詢我們雖然用并行查詢優(yōu)化提升了效率,但是一些特定的需求實時報表、實時大屏我們還是無法實現(xiàn)，只能依賴大數(shù)據(jù)去處理。
這里推薦大家阿里云的交互式分析Hologre（
https://help.aliyun.com/product/113622.html）

六、后記

千萬級大表優(yōu)化是根據(jù)業(yè)務(wù)場景，以成本為代價優(yōu)化的，不是一上來就數(shù)據(jù)庫水平切分擴展，這樣會給運維和業(yè)務(wù)帶來巨大挑戰(zhàn)，很多時候效果不一定好，我們的數(shù)據(jù)庫設(shè)計、索引優(yōu)化、分表策略是否做到位了，應(yīng)該根據(jù)業(yè)務(wù)需求選擇合適的技術(shù)去實現(xiàn)。

到此這篇關(guān)于淺談MySQL大表優(yōu)化方案的文章就介紹到這了,更多相關(guān)MySQL大表優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！