簡(jiǎn)介：?本篇文章總結(jié)了AnalyticDB表的設(shè)計(jì)的最佳經(jīng)驗(yàn)、數(shù)據(jù)寫(xiě)入的最佳經(jīng)驗(yàn)、高效查詢的最佳實(shí)踐，以及一些常見(jiàn)的問(wèn)題。

隨著云原生數(shù)據(jù)倉(cāng)庫(kù)AnalyticDB for MySQL（下文統(tǒng)一簡(jiǎn)稱：AnalyticDB）在阿里集團(tuán)各個(gè)業(yè)務(wù)線、社會(huì)上各行各業(yè)的推廣應(yīng)用，我們沉淀了一些最佳實(shí)踐，現(xiàn)在筆者整理在這里，供大家參考，希望對(duì)大家有幫助。本篇文章總結(jié)了AnalyticDB表的設(shè)計(jì)的最佳經(jīng)驗(yàn)、數(shù)據(jù)寫(xiě)入的最佳經(jīng)驗(yàn)、高效查詢的最佳實(shí)踐，以及一些常見(jiàn)的問(wèn)題。

說(shuō)明：

1.在讀這篇文章之前，請(qǐng)先了解AnalyticDB的產(chǎn)品官方文檔，以提前適當(dāng)了解AnalyticDB；

2.本文寫(xiě)的最佳實(shí)踐主要針對(duì)AnalyticDB 3.0，AnalyticDB 2.0在原理上也同樣適用。

01 表設(shè)計(jì)的最佳實(shí)踐

AnalyticDB，作為一個(gè)分布式數(shù)據(jù)倉(cāng)庫(kù)，能夠?yàn)楹Ａ繑?shù)據(jù)的實(shí)時(shí)分析帶來(lái)卓越的性能體驗(yàn)。為了充分發(fā)揮AnalyticDB在數(shù)據(jù)分析方面的性能優(yōu)勢(shì)，設(shè)計(jì)表時(shí)，需要注意以下幾點(diǎn)規(guī)則。

（一）選擇合適的表類(lèi)型（維度表or普通表）

· 維度表：又稱廣播表，是數(shù)據(jù)倉(cāng)庫(kù)中的一個(gè)概念，一般存儲(chǔ)維度數(shù)據(jù)。在AnalyticDB中建表語(yǔ)句中有DISTRIBUTED BY BROADCAST的關(guān)鍵字，這些表會(huì)在集群的每個(gè)節(jié)點(diǎn)存儲(chǔ)一份數(shù)據(jù)，因此維度表的數(shù)據(jù)量不宜太大，建議每張維度表存儲(chǔ)的數(shù)據(jù)不超過(guò)2萬(wàn)行。

注意：維度表太大，會(huì)導(dǎo)致數(shù)據(jù)存儲(chǔ)空間的膨脹，節(jié)點(diǎn)越多膨脹越大，同時(shí)也會(huì)導(dǎo)致實(shí)時(shí)寫(xiě)入時(shí)性能下降，IOPS會(huì)比較高。

· 普通表：也叫作分區(qū)表、事實(shí)表，一般存儲(chǔ)業(yè)務(wù)的主題數(shù)據(jù)。普通表可存儲(chǔ)的數(shù)據(jù)量通常比較大，可以存儲(chǔ)千萬(wàn)條甚至萬(wàn)億條數(shù)據(jù)，可以通過(guò)一級(jí)分區(qū)對(duì)數(shù)據(jù)做分片以及二級(jí)分區(qū)對(duì)數(shù)據(jù)進(jìn)行生命周期管理。

（二）選擇合適的分布鍵（一級(jí)分區(qū)鍵）

AnalyticDB中創(chuàng)建普通表時(shí)，默認(rèn)需要通過(guò)DISTRIBUTED BY HASH(column_name,...)指定分布鍵，按照column_name的HASH值進(jìn)行分區(qū)。

AnalyticDB支持將多個(gè)字段作為分布鍵。

分布鍵的選擇依據(jù)：

• 盡可能選擇值分布均勻的字段作為分布鍵，例如交易ID、設(shè)備ID、用戶ID或者自增列作為分布鍵；
• 盡可能選擇參與JOIN的字段作為分布鍵，例如進(jìn)行用戶畫(huà)像分析時(shí)，可以選擇user_id作為分布鍵。

注意：分布鍵不均勻容易導(dǎo)致數(shù)據(jù)分布不均，嚴(yán)重影響寫(xiě)入和查詢的效率，此外也容易使單節(jié)點(diǎn)磁盤(pán)寫(xiě)滿從而導(dǎo)致整個(gè)集群鎖定不可用。除特殊的業(yè)務(wù)場(chǎng)景外，建表優(yōu)先考慮數(shù)據(jù)是否均勻，然后再考慮JOIN KEY對(duì)齊的問(wèn)題。

（三）選擇合適的分區(qū)鍵（二級(jí)分區(qū)鍵）

對(duì)于表的數(shù)據(jù)量非常大的表，需要考慮創(chuàng)建二級(jí)分區(qū)表來(lái)對(duì)數(shù)據(jù)做進(jìn)一步的切分，設(shè)置了二級(jí)分區(qū)后，也能帶來(lái)兩個(gè)好處：

1）對(duì)數(shù)據(jù)進(jìn)行生命周期管理，比如設(shè)置了一定數(shù)量的二級(jí)分區(qū)數(shù)量后，過(guò)期的二級(jí)分區(qū)會(huì)自動(dòng)被淘汰掉；

2）當(dāng)查詢條件帶上了二級(jí)分區(qū)字段時(shí)，是可以對(duì)二級(jí)分區(qū)進(jìn)行裁剪的，從而提升查詢的性能。

• 直接用ds的值來(lái)做分區(qū) PARTITION BY VALUE(ds)
• ds轉(zhuǎn)換后的天做分區(qū) PARTITION BY VALUE(DATE_FORMAT(ds, '%Y%m%d'))
• ds轉(zhuǎn)換后的月做分區(qū) PARTITION BY VALUE(DATE_FORMAT(ds, '%Y%m'))
• ds轉(zhuǎn)換后的年做分區(qū) PARTITION BY VALUE(DATE_FORMAT(ds, '%Y'))

二級(jí)分區(qū)的注意事項(xiàng)：

請(qǐng)?zhí)崆耙?guī)劃好實(shí)例中所有表的二級(jí)分區(qū)鍵，充分利用二級(jí)分區(qū)，不要讓每個(gè)二級(jí)分區(qū)的數(shù)據(jù)量過(guò)小，假如，用天進(jìn)行二級(jí)分區(qū)，每天數(shù)據(jù)量很小，那么可以考慮用月作為二級(jí)分區(qū)。二級(jí)分區(qū)數(shù)據(jù)量過(guò)小，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)中需要保存分區(qū)數(shù)據(jù)的元數(shù)據(jù)特別多，而這些元數(shù)據(jù)存放在內(nèi)存中，過(guò)多的元數(shù)據(jù)會(huì)占據(jù)較多的內(nèi)存空間，導(dǎo)致系統(tǒng)的GC或者OOM，同時(shí)也會(huì)導(dǎo)致實(shí)時(shí)寫(xiě)入的IOPS較高。

二級(jí)分區(qū)的數(shù)據(jù)量建議：

（四）選擇合適的主鍵

在表中定義主鍵可以實(shí)現(xiàn)數(shù)據(jù)消重(REPLACE INTO)和數(shù)據(jù)更新(DELETE、UPDATE)。只有定義過(guò)主鍵的表支持?jǐn)?shù)據(jù)更新操作（DELETE、UPDATE）。

主鍵的選擇依據(jù)：

• 盡可能選擇數(shù)值類(lèi)型的單個(gè)字段作為主鍵，表的性能相對(duì)更好。
• 如果數(shù)值類(lèi)型的單一主鍵無(wú)法滿足業(yè)務(wù)需要，也可以使用字符串或者多字段組合作為主鍵。
• 主鍵中必須包含分布鍵和分區(qū)鍵，如果表中定義了二級(jí)分區(qū)鍵的話，主鍵必須包含二級(jí)分區(qū)鍵。

注意：作為主鍵的字段不宜太大，字段的長(zhǎng)度不宜過(guò)長(zhǎng)，否則會(huì)影響寫(xiě)入的性能。

（五）選擇合適聚集索引

聚集索引會(huì)將一個(gè)或者多個(gè)字段排序，保證該字段相同或者相近的數(shù)據(jù)存儲(chǔ)在磁盤(pán)的相同或相近位置，當(dāng)以聚集索引中的字段作為查詢條件時(shí)，查詢結(jié)果保持在磁盤(pán)的相同位置，可以減少磁盤(pán)的IO。

聚集索引的選擇依據(jù)：

查詢一定會(huì)攜帶的過(guò)濾條件的字段可以設(shè)計(jì)為聚集索引。例如，電商賣(mài)家透視平臺(tái)中每個(gè)賣(mài)家只訪問(wèn)自己的數(shù)據(jù)，賣(mài)家ID可以定義為聚集索引，保證數(shù)據(jù)的局部性，提升數(shù)據(jù)查詢性能。

注意：目前只支持一個(gè)聚集索引，但一個(gè)聚集索引可以包含多列。目前除非對(duì)非常分散的數(shù)據(jù)進(jìn)行點(diǎn)查，否則聚集索引對(duì)性能的幫助很少。

（六）設(shè)計(jì)合適的數(shù)據(jù)類(lèi)型

建議用戶盡可能使用數(shù)值類(lèi)型，減少使用字符串類(lèi)型。

AnalyticDB處理數(shù)值類(lèi)型的性能遠(yuǎn)好于處理字符串類(lèi)型，原因在于：

• 數(shù)值類(lèi)型定長(zhǎng)，占用內(nèi)存少，存儲(chǔ)空間小。
• 數(shù)值類(lèi)型計(jì)算更快，尤其是在數(shù)據(jù)關(guān)聯(lián)場(chǎng)景。
• 從內(nèi)部索引機(jī)制上，字符串類(lèi)型適合等值查詢和范圍查詢，而時(shí)間類(lèi)型、數(shù)值類(lèi)型性能更好。
• 選擇盡可能小的字段長(zhǎng)度，比如，性別可以使用Boolean或者Byte類(lèi)型，數(shù)據(jù)長(zhǎng)度不大的可以用Int類(lèi)型。
• 在同一個(gè)業(yè)務(wù)模型內(nèi)，相同字段設(shè)計(jì)成相同的數(shù)據(jù)類(lèi)型和字段長(zhǎng)度，字段命名也保持一致，特別是涉及到主外鍵關(guān)聯(lián)的字段更要注意，避免不同的數(shù)據(jù)類(lèi)型的字段關(guān)聯(lián)導(dǎo)致隱式轉(zhuǎn)換。

常見(jiàn)字符串?dāng)?shù)據(jù)的處理建議：

• 包含字符前綴或后綴，例如E12345，E12346等。建議去掉前綴或者將前綴映射為數(shù)字。
• 字段只有少數(shù)幾個(gè)值，例如國(guó)家名。建議對(duì)每個(gè)國(guó)家編碼，每個(gè)國(guó)家對(duì)應(yīng)一個(gè)唯一數(shù)字。
• 時(shí)間/日期類(lèi)型數(shù)據(jù)，避免使用Varchar字符類(lèi)型存儲(chǔ)，盡量使用Date，Timestamp或者Int類(lèi)型。
• 地理的經(jīng)度/緯度數(shù)據(jù)，建議采用Double數(shù)據(jù)類(lèi)型進(jìn)行存儲(chǔ)。

如果您在建表前，不清楚自身業(yè)務(wù)的數(shù)據(jù)分布特征，可在數(shù)據(jù)導(dǎo)入后，使用優(yōu)化建議進(jìn)行優(yōu)化。具體請(qǐng)?jiān)L問(wèn)AnalyticDB控制臺(tái)的建表診斷頁(yè)面：數(shù)據(jù)建模優(yōu)化 - 云原生數(shù)倉(cāng) AnalyticDB MySQL - 阿里云，查看建表問(wèn)題及優(yōu)化建議。

02 數(shù)據(jù)寫(xiě)入的最佳實(shí)踐

（一）實(shí)時(shí)寫(xiě)入

1.批量打包的方式提交

向表中寫(xiě)入數(shù)據(jù)時(shí)，可以通過(guò)批量打包方式INSERT INTO和REPLACE INTO提高數(shù)據(jù)寫(xiě)入性能。注意事項(xiàng)如下：

• 通過(guò)每條INSERT或者REPLACE語(yǔ)句寫(xiě)入的數(shù)據(jù)行數(shù)需大于1000行，但寫(xiě)入的總數(shù)據(jù)量不宜太大，不能超過(guò)16MB。
• 通過(guò)批量打包方式寫(xiě)入數(shù)據(jù)時(shí)，單個(gè)批次的寫(xiě)入延遲相對(duì)較高，但是整體性能有所提升。
• 寫(xiě)入報(bào)錯(cuò)時(shí)，需要重試以確保數(shù)據(jù)被成功寫(xiě)入，重試導(dǎo)致的數(shù)據(jù)重復(fù)可以通過(guò)表的主鍵來(lái)消除。
• 如果不需要對(duì)原始的數(shù)據(jù)進(jìn)行修改，可以使用INSERT INTO寫(xiě)入數(shù)據(jù)，效率是REPLACE INTO的3倍以上。

樣例：

INSERT INTO test ?(id, name,sex,age,login_time) ? VALUES ?(1,'dcs',0,23,'2018-03-02 10:00:00'), ?(2,'hl',0,23,'2018-03-02 10:01:00'), ?(3,'xx',0,23,'2018-03-02 10:02:00') ? ......;

2.更新數(shù)據(jù)

數(shù)據(jù)更新有多種方式，使用區(qū)別如下：

• 高頻基于主鍵的行級(jí)覆蓋更新, 且應(yīng)用可以補(bǔ)齊所有列，請(qǐng)使用REPLACE INTO VALUES批量打包。
• 高頻基于主鍵的行級(jí)覆蓋更新, 應(yīng)用不能補(bǔ)齊所有列，請(qǐng)使用INSERT ON DUPLICATE KEY UPDATE批量打包。
• 低頻任意條件更新，請(qǐng)使用UPDATE SET WHERE。

注意：UPDATE需要查表來(lái)填補(bǔ)更新中缺失的舊值，因此比REPLACE INTO多一次查詢，性能較低，不建議做高頻、大批量的UPDATE操作。如果線上UPDATE性能無(wú)法滿足需求，需考慮替換成REPLACE INTO，由應(yīng)用端填補(bǔ)舊值。

3.刪除數(shù)據(jù)

數(shù)據(jù)刪除有多種方式，使用區(qū)別如下：

• 低頻主鍵條件刪除，請(qǐng)使用 DELETE FROM WHERE primary key = xxx。
• 低頻任意條件刪除，請(qǐng)使用 DELETE FROM WHERE。
• 刪除單個(gè)二級(jí)分區(qū)，請(qǐng)使用 TRUNCATE PARTITION。
• 刪除單表（包括所有二級(jí)分區(qū)），請(qǐng)使用TRUNCATE TABLE或DROP TABLE。

（二）批量導(dǎo)入

1.如何選擇批量導(dǎo)入還是實(shí)時(shí)導(dǎo)入

• 從ODPS、OSS導(dǎo)入AnalyticDB，推薦使用INSERT OVERWRITE SELECT做批量導(dǎo)入，有以下兩個(gè)原因：一，批量導(dǎo)入適合大數(shù)據(jù)量導(dǎo)入，性能好；二，批量導(dǎo)入適合數(shù)倉(cāng)語(yǔ)義，即導(dǎo)入過(guò)程中舊數(shù)據(jù)可查，導(dǎo)入完成一鍵切換新數(shù)據(jù)，如果導(dǎo)入失敗，新數(shù)據(jù)會(huì)回滾，不影響舊數(shù)據(jù)的查詢。
• 從RDS、MySQL、AnalyticDB等導(dǎo)入AnalyticDB，根據(jù)數(shù)據(jù)量情況，如果數(shù)據(jù)量不大（百萬(wàn)級(jí)別的表），推薦使用INSERT INTO SELECT做實(shí)時(shí)導(dǎo)入；如果數(shù)據(jù)量較大，推薦使用INSERT OVERWRITE SELECT做批量導(dǎo)入。
• 對(duì)相同的一張表，不能既采用INSERT OVERWRITE SELECT又采用INSERT INTO SELECT操作，否則數(shù)據(jù)會(huì)被覆蓋。

2.導(dǎo)入并發(fā)和資源說(shuō)明

• 單張表的導(dǎo)入會(huì)在系統(tǒng)內(nèi)部排隊(duì)串行，而多張表的導(dǎo)入，會(huì)產(chǎn)生n個(gè)并行導(dǎo)入任務(wù)（并行度可調(diào)整，默認(rèn)并行度是2），出于資源控制的考慮，超出并行度的任務(wù)也會(huì)排隊(duì)。
• 數(shù)據(jù)導(dǎo)入，同查詢一樣，會(huì)消耗AnalyticDB實(shí)例的計(jì)算資源。因此，建議在查詢QPS較低時(shí)執(zhí)行數(shù)據(jù)導(dǎo)入，并推薦通過(guò)定時(shí)任務(wù)進(jìn)行錯(cuò)峰導(dǎo)入。

03 高效查詢的最佳實(shí)踐

AnalyticDB的優(yōu)勢(shì)是能在海量數(shù)據(jù)場(chǎng)景下，面對(duì)復(fù)雜查詢，做到實(shí)時(shí)的在線分析。AnalyticDB的查詢調(diào)優(yōu)，不僅兼容數(shù)據(jù)庫(kù)查詢優(yōu)化的通用方法，還提供一些專(zhuān)門(mén)的優(yōu)化方法，使其能夠充分發(fā)揮出分布式計(jì)算的性能優(yōu)勢(shì)。

（一）查詢優(yōu)化的通用法則

按照葉正盛早些年在《ORACLE DBA手記》上寫(xiě)的文章，數(shù)據(jù)訪問(wèn)優(yōu)化滿足以下漏斗法則：

1.減少數(shù)據(jù)訪問(wèn)（減少磁盤(pán)訪問(wèn)）

盡量多的使用過(guò)濾條件，盡早的提前過(guò)濾數(shù)據(jù)，從而減少參與計(jì)算的數(shù)據(jù)量，例如在子查詢里提前把能過(guò)濾的數(shù)據(jù)先過(guò)濾。

2.返回更少數(shù)據(jù)（減少網(wǎng)絡(luò)傳輸或磁盤(pán)訪問(wèn)）

在OLAP數(shù)據(jù)庫(kù)中，由于表的列數(shù)往往比較多，且是基于列存或者行列混存，所以SELECT * 的操作，會(huì)導(dǎo)致較多的請(qǐng)求IO。因此，請(qǐng)盡量避免SELECT * 的查詢。

3.減少交互次數(shù)（減少網(wǎng)絡(luò)傳輸）

建議使用上文提到的批量導(dǎo)入，減少交互次數(shù)。

4.減少服務(wù)器CPU開(kāi)銷(xiāo)（減少CPU及內(nèi)存開(kāi)銷(xiāo)）

• 減少不必要的排序和分頁(yè)，特別是子查詢中的排序。
• 在滿足業(yè)務(wù)前提下，盡量減少COUNT DISTINCT操作。
• 在滿足業(yè)務(wù)前提下，特別是在海量數(shù)據(jù)下，采用類(lèi)似Hyperloglog的近似計(jì)算代替準(zhǔn)確計(jì)算。

5.利用更多資源（增加資源）

• 設(shè)計(jì)表的時(shí)候，盡量避免分區(qū)傾斜， 不要把存儲(chǔ)和計(jì)算壓在某一個(gè)節(jié)點(diǎn)上。建議盡量把數(shù)據(jù)都均勻的散列到所有的節(jié)點(diǎn)上，充分利用所有機(jī)器的能力，最大程度地發(fā)揮分布式數(shù)據(jù)庫(kù)的效能。
• AnalyticDB本身就是MPP大規(guī)模并行處理的典型系統(tǒng)，在內(nèi)核層面做了大量的優(yōu)化處理，能夠充分利用更多的資源。

（二）AnalyticDB特殊場(chǎng)景的優(yōu)化

1.外表查詢的最佳實(shí)踐

• 不推薦使用外表進(jìn)行復(fù)雜計(jì)算。外表計(jì)算會(huì)拉取全部數(shù)據(jù)，因此外表的復(fù)雜計(jì)算會(huì)導(dǎo)致嚴(yán)重的GC，也會(huì)給網(wǎng)絡(luò)帶寬造成較大壓力。
• 外部表不支持DML操作（DELETE、UPDATE、TRUNCATED）。如果需要修改外表數(shù)據(jù)，請(qǐng)到源表中進(jìn)行DML操作。

2.合理的使用索引

合理使用索引是數(shù)據(jù)庫(kù)調(diào)優(yōu)的一個(gè)非常重要的手段，AnalyticDB也不例外。在AnalyticDB中，默認(rèn)每列都會(huì)創(chuàng)建索引。但是也有例外情況。如果某列的Cardinality值較低，索引的選擇性不高，通過(guò)索引查詢，性能可能會(huì)更差。此時(shí)，建議在建表時(shí)關(guān)閉自動(dòng)創(chuàng)建索引的功能。如果表已經(jīng)建好，可以使用如下SQL語(yǔ)句，刪除索引或者通過(guò)hint繞過(guò)索引。

ALTER TABLE table_name DROP INDEX index_name; --方法一：刪除枚舉列的索引 /+no_index_columns=[t_order_content.fdelete;fdbid]/ --方法二：通過(guò)hint使查詢繞過(guò)索引

3.巧妙的使用聚集索引

當(dāng)查詢條件一定包含某列，特別是該列數(shù)據(jù)在存儲(chǔ)上非常分散時(shí)，對(duì)該列建立聚集索引，性能會(huì)有明顯的提升。您可以采用類(lèi)似如下的SQL語(yǔ)句添加聚集索引：

ALTER TABLE table_name ADD CLUSTERED INDEX index_cls (d_fdbid);

注意：如果表中已經(jīng)有了數(shù)據(jù)，直接ADD CLUSTER INDEX不會(huì)對(duì)存量的數(shù)據(jù)排序，需要重新建表，并在建表的時(shí)候加上聚集列關(guān)鍵字；或者在添加完聚集索引后對(duì)該表做一次build操作：build table table_name force=true。

4.減少節(jié)點(diǎn)間的數(shù)據(jù)交互

分布式數(shù)據(jù)庫(kù)，在充分發(fā)揮分布式計(jì)算優(yōu)勢(shì)的同時(shí)，有時(shí)也會(huì)加大跨節(jié)點(diǎn)間的網(wǎng)絡(luò)開(kāi)銷(xiāo)。特別是請(qǐng)求的數(shù)據(jù)量較少，數(shù)據(jù)卻分散在較多節(jié)點(diǎn)的情況，跨網(wǎng)絡(luò)開(kāi)銷(xiāo)的情況就非常明顯。本文提供以下兩個(gè)思路：

• 盡量在本地節(jié)點(diǎn)內(nèi)進(jìn)行Join，充分利用Local Join特性，大大減少跨網(wǎng)絡(luò)訪問(wèn)。具體做法為：盡量采用一級(jí)分區(qū)鍵關(guān)聯(lián)；
• 盡量在本地節(jié)點(diǎn)內(nèi)進(jìn)行聚合分析，減少跨網(wǎng)絡(luò)訪問(wèn)shuffle的數(shù)據(jù)量。具體做法為：盡量對(duì)一級(jí)分區(qū)鍵進(jìn)行GROUP BY。

04 AnalyticDB連接的最佳實(shí)踐

在使用方法上，AnalyticDB與MySQL的兼容程度高達(dá)99%以上，支持多種連接方式，包括MySQL命令行，JDBC連接，Python連接，C#連接，PHP連接等等。更詳細(xì)地使用方法，請(qǐng)參考官方文檔：連接集群 - 云原生數(shù)倉(cāng) AnalyticDB MySQL - 阿里云。

原文鏈接
本文為阿里云原創(chuàng)內(nèi)容，未經(jīng)允許不得轉(zhuǎn)載。?

總結(jié)

以上是生活随笔為你收集整理的全网首发｜阿里资深技术专家数仓调优经验分享（上）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。