文章目錄

• MPP架構(gòu)數(shù)據(jù)庫優(yōu)化總結(jié)——華為LibrA(MPPDB、GuassDB)
• • 1. 簡介
  • 2. 優(yōu)化點
  • • 2.1 建表時選擇合適的數(shù)據(jù)類型
    • 2.2 選擇合理的存儲模型（行存和列存）
    • 2.3 選擇表的分布方式
    • 2.4 選擇合適的分區(qū)鍵
    • 2.5 創(chuàng)建索引，提高數(shù)據(jù)的訪問速度
    • 2.6 分析SQL執(zhí)行計劃
    • 2.7 SQL編寫優(yōu)化
    • 2.8 根據(jù)業(yè)務(wù)優(yōu)化表設(shè)計
    • 2.9 大批量的數(shù)據(jù)導(dǎo)入、導(dǎo)出
    • 2.10 壓縮，減少空間占用
    • 2.11 使用VACUUM和ANALYZE命令定期對每個表進(jìn)行維護(hù)
    • 2.12 減少數(shù)據(jù)庫存儲過程的使用
    • 2.13 結(jié)束長時間運(yùn)行的SQL

MPP架構(gòu)數(shù)據(jù)庫優(yōu)化總結(jié)——華為LibrA(MPPDB、GuassDB)

1. 簡介

• 大數(shù)據(jù)在關(guān)系型數(shù)據(jù)處理這塊，為了能夠快速的查詢、寫入海量的數(shù)據(jù)，通常會采用MPP (Massively Parallel Processing)架構(gòu)的分布式數(shù)據(jù)庫。華為LibrA(MPPDB、GuassDB)與GreenPlum正是這樣一款產(chǎn)品。通常實際生產(chǎn)環(huán)境中，每張表會存入海量的數(shù)據(jù)（例如我這里會有4TB、8TB、14TB等大小的表），為了解決這些存有海量數(shù)據(jù)的表的性能問題，需要給出很多優(yōu)化方案，在這里我總結(jié)出工作中常用的一些優(yōu)化手段。

2. 優(yōu)化點

2.1 建表時選擇合適的數(shù)據(jù)類型

• 正確地選擇字段的數(shù)據(jù)類型可以提高效率、減小空間占用
• 例如，人的年齡沒必要使用int，可以采用TINYINT（占用1字節(jié)，范圍為0~255）
• 例如，字段長度不確定時，優(yōu)先使用TEXT和VARCHAR類型，盡量不要使用CHAR，以降低存儲空間的使用。如果表中所有行該字段的長度基本一致，優(yōu)先使用CHAR。

2.2 選擇合理的存儲模型（行存和列存）

• 行存表：適用于對數(shù)據(jù)需要經(jīng)常更新的場景。
• 列存表： 適合數(shù)據(jù)批量插入、更新較少和以查詢?yōu)橹鹘y(tǒng)計分析類的場景。列存表不適合點查詢，插入單條記錄性能差。
• 如何選擇？
  • 如果更新頻繁，選擇行存
  • 如果經(jīng)常點查詢，選擇行存
  • 如果經(jīng)常進(jìn)行聚合查詢，選擇列存
  • 經(jīng)常一次插入大批量數(shù)據(jù)，選擇列存
  • 表字段較多，可以嘗試列存
  • 存儲空間有限，希望更好的壓縮數(shù)據(jù)，選擇列存

2.3 選擇表的分布方式

• 小表選擇Replication方式（例如表大小為5MB），會在每一個DataNode上存儲一份全量表數(shù)據(jù)
• 大表選擇Hash方式，會根據(jù)hash值把數(shù)據(jù)映射到對應(yīng)的DataNode上
• 使用Hash分表策略時，需要選擇合理的分布列（即字段），選擇的列要具有隨機(jī)性，以保證數(shù)據(jù)均勻的分布到各個DataNode上。檢查數(shù)據(jù)是否分布均勻的SQL如下:-- 如果每個node_name對應(yīng)的count相差不大，即代表分布基本均勻 SELECT a.count,b.node_name FROM (SELECT COUNT(*) AS count,xc_node_id FROM 表名 GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count DESC;

2.4 選擇合適的分區(qū)鍵

• 合適的分區(qū)鍵可以有效改善數(shù)據(jù)庫的查詢性能，增強(qiáng)可用性，方便維護(hù)，以及均衡I/O等
• 通常根據(jù)業(yè)務(wù)，我們可以按照日期對表進(jìn)行分區(qū)（例如天、月）。查詢時，選擇對應(yīng)的分區(qū)查詢即可，可以提高效率

2.5 創(chuàng)建索引，提高數(shù)據(jù)的訪問速度

• 根據(jù)業(yè)務(wù)需求選擇合理的索引字段，例如經(jīng)常被用作查詢條件的字段、被要求排序的字段
• 如何選擇索引字段？
  • 經(jīng)常使用WHERE子句的字段
  • 經(jīng)常出現(xiàn)在ORDER BY、GROUP BY、DISTINCT后的字段
  • 經(jīng)常進(jìn)行多表連接的字段 JOIN
• 單鍵/聯(lián)合索引，滿足業(yè)務(wù)條件下，優(yōu)先選擇聯(lián)合索引。如果需要創(chuàng)建聯(lián)合索引，應(yīng)注意后續(xù)SQL中的where條件的字段（最左前綴）。
• 表的數(shù)據(jù)量較少（例如100條數(shù)據(jù)），不用創(chuàng)建索引-- 分區(qū)表需要在最后加上LOCAL，非分區(qū)表不用 CREATE INDEX 索引名 ON 表名 (索引字段) LOCAL;

2.6 分析SQL執(zhí)行計劃

• 查看執(zhí)行計劃的邏輯，檢查是否存在不合理的執(zhí)行，再進(jìn)行SQL優(yōu)化
• 執(zhí)行計劃分析內(nèi)容較多，請自行百度其他數(shù)據(jù)庫的執(zhí)行計劃分析，都是類似的

2.7 SQL編寫優(yōu)化

• 使用索引時，應(yīng)遵守最左前綴原則
• 不要在索引列上做任何操作（計算、函數(shù)等等），否則會導(dǎo)致索引失效
• !=、<>、IS NULL、IS NOT NULL會導(dǎo)致索引列失效
• OR可能會導(dǎo)致索引失效
• 關(guān)于like查詢，LIKE '%word%'會可能導(dǎo)致索引列失效，LIKE 'word%'仍能使用索引
• where中，能明確條件的，盡量少使用like模糊查詢（必須使用like時，盡量不要使用’%content%‘，應(yīng)盡量使用’content%’）。如果like的是分區(qū)字段，則可以不用太在意。
• 能在where中搞定的條件，不要用having
• 執(zhí)行較復(fù)雜的SQL，建議分多步執(zhí)行，創(chuàng)建unlogged table或temp table緩存中間臨時數(shù)據(jù)（非日志表的性能比普通表有大幅度提升）
• 在實際業(yè)務(wù)中，如果2個表做union，能夠提前確定2個表沒有交集，那么建議使用union all替代union
• 2個表做Join時，小表在前、大表在后(小表驅(qū)動大表)
• 2個表Join時，盡量使用inner join，少使用left join
• 2個表Join時，如果不需要Null，請盡量加上is not null條件，對Join之前的數(shù)據(jù)進(jìn)行過濾
• 做聚合分析時，可以提前做好where過濾，以減少聚合的數(shù)據(jù)量
• 查詢時不要使用SELECT * …，請直接指明所有字段名
• 針對同一個字段的多個or等于條件（name=‘xm’ or name=‘ls’ or name=‘xh’ …），請修改為in或者exist （規(guī)范：大表 in 小表，小表 exist 大表）
• 針對連續(xù)的數(shù)值條件查詢，不要使用in，盡量使用between(例如 WHERE id BETWEEN 2 AND 3)
• 對經(jīng)常要查詢的SQL，創(chuàng)建視圖View，以方便下次直接查詢

2.8 根據(jù)業(yè)務(wù)優(yōu)化表設(shè)計

• 沒有必要為了節(jié)省空間去設(shè)計多個關(guān)聯(lián)表（效率不高，大數(shù)據(jù)應(yīng)該提倡以空間換時間）
• 針對經(jīng)常要做統(tǒng)計的表，可以提前另作一個統(tǒng)計結(jié)果表，直接查詢該結(jié)果表既可
• 一個大表中，某個字段需要經(jīng)常單獨(dú)用來去重或者判斷exist，而又不要求實時性，同時又只是一個單一的業(yè)務(wù)需要，沒有必要為其創(chuàng)建索引，可以每天做一次去重，單獨(dú)存一個表
• 根據(jù)實際業(yè)務(wù)需求，可以對日期進(jìn)行分區(qū)。如果前臺每次默認(rèn)查詢需要做一個聚合請求，在能滿足業(yè)務(wù)需求下，不要直接查全表日期的聚合，可以嘗試查近期的聚合（例如近1~2月）。因為業(yè)務(wù)方面通常也是想看近期的數(shù)據(jù)。
• 如果業(yè)務(wù)中要使用分頁類似的查詢方式，表中需要設(shè)計id。如果只使用offset，隨著表數(shù)據(jù)量的增大，會越來越慢。添加id后，可以用該語句代替：-- SELECT id,name FROM product LIMIT 20 OFFSET 100000; SELECT id,name FROM product WHERE id> 100000 LIMIT 20
• 多數(shù)業(yè)務(wù)情況下，表中應(yīng)設(shè)計create_time、update_time字段，以表示該條數(shù)據(jù)的插入、更新時間，方便后續(xù)操作
• 如果一個表的業(yè)務(wù)通常是進(jìn)行聚合操作，應(yīng)該嘗試將該表設(shè)計為列存模式
• 利用業(yè)務(wù)需求，可以為表的字段設(shè)計二維索引（例如geohash），以做到某些特殊查詢需求

2.9 大批量的數(shù)據(jù)導(dǎo)入、導(dǎo)出

• 當(dāng)業(yè)務(wù)中需要大批量的數(shù)據(jù)導(dǎo)入時，請不要再使用JDBC/ODBC等方式插入數(shù)據(jù)，可以使用數(shù)據(jù)庫自帶的批量導(dǎo)入工具。（華為LibrA可以參考LibrA批量數(shù)據(jù)導(dǎo)入，GreenPlum也自帶導(dǎo)入工具）。
• 如果要快速插入大量數(shù)據(jù)，盡量不要使用約束

2.10 壓縮，減少空間占用

• 如果系統(tǒng)空間不足，又無法添加新的硬件，可以考慮對表數(shù)據(jù)進(jìn)行壓縮（會導(dǎo)致性能降低）。
• 示例，定義一個帶壓縮的列存表CREATE TABLE tb_name(code char(5),title varchar(40),did integer, ) WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH);
• 列存表的有效值為YES/NO/LOW/MIDDLE/HIGH，默認(rèn)值為LOW
• 行存表的有效值為YES/NO，默認(rèn)值為NO

2.11 使用VACUUM和ANALYZE命令定期對每個表進(jìn)行維護(hù)

• VACUUM可以回收表或B-Tree索引中已經(jīng)刪除的行所占據(jù)的存儲空間（DELETE實際不會真正刪除數(shù)據(jù)）
• ANALYZE會收集與數(shù)據(jù)庫相關(guān)的統(tǒng)計信息，以便最有效的查詢執(zhí)行計劃
• 可以嘗試每日自動對表進(jìn)行維護(hù)，SQL示例如下：VACUUM ANALYZE tb_name;
• 另外可以嘗試VACUUM FULL，可以恢復(fù)更多的空間（耗時更長）

2.12 減少數(shù)據(jù)庫存儲過程的使用

• 該類型數(shù)據(jù)庫，使用存儲過程的性能并不好

2.13 結(jié)束長時間運(yùn)行的SQL

• 有的SQL執(zhí)行時間過長，很可能是數(shù)據(jù)庫BUG、表數(shù)據(jù)存在問題、SQL自身問題導(dǎo)致的，應(yīng)該定期進(jìn)行分析，結(jié)束掉這部分SQL
• 查詢長時間運(yùn)行的SQL:SELECT current_timestamp - query_start AS runtime, datname, usename, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY 1 DESC;
• 查看語句執(zhí)行的線程狀態(tài):SELECT * FROM PG_THREAD_WAIT_STATUS WHERE db_name='db_name';
• 殺掉對應(yīng)的tid的SQL語句:SELECT pg_terminate_backend(140532470773504);

總結(jié)

以上是生活随笔為你收集整理的MPP架构数据库优化总结——华为LibrA(MPPDB、GuassDB)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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