最佳實踐—偏分析場景的實踐和優化

少量的寫或者更新請求，大多數是讀請求；
每次查詢都從資料庫中讀取大量的行，但是同時又僅需要少量的列；
大多數查詢都是比較複雜的查詢，查詢的并發不會很大，但單個查詢需要高吞吐量；
對于簡單查詢，允許一定的延遲；
分析場景上分布式事務可能不是必須的；
大部分查詢中往往會涉及到事實表和維表的關聯，是典型的大小表關聯場景；
查詢結果明顯小于源資料，即資料被過濾或聚合後能夠被盛放在單台伺服器的記憶體中；
分析的資料往往是最近的業務資料，曆史資料可以被清理或者被歸檔。

依據上述對分析場景的歸納，分析場景做性能優化除了要沿用TP資料庫的優化思路，還會有自身不一樣的優化思路。這主要會展現在結構設計和查詢優化兩個方面。

結構設計

在結構設計上主要包括如何選擇表類型、分區鍵、主鍵以及聚簇，使表的性能達到最優。

設計為分區表或者廣播表

廣播表會在叢集的每個資料節點都存儲一份資料，建議廣播表的資料量不宜太大，每張廣播表存儲的資料不超過20萬行，這樣在大表和廣播表做關聯時，可以計算下推，讓關聯貼近資料層做計算，避免大表資料拉取到計算節點做計算。
其他業務資料盡可能做成分區表，可以充分利用分布式系統的查詢能力。理論上表的分區數量越多越好，這樣多個分區表可以做并行掃描。存儲層更易做到水準擴充，存儲千萬條甚至上億條資料。不過實際使用中建議一個分區表的數量在500w~5000w之間。

最佳實踐—偏分析場景的實踐和優化
選擇合适的分區鍵

PolarDB-X預設按照主鍵做分拆，主要為了降低您使用分布式資料庫的成本。同時我們也支援通過指定分區鍵建分區表，在分析場景建議您根據如下依據選擇的分區鍵：

盡可能選擇參與JOIN的字段作為分區鍵，這樣做的目的還是為了關聯條件下推，避免資料被拉取到計算層做計算。
盡可能選擇值分布均勻的字段作為分區鍵，這樣可以避免由于分布式不均導緻出現計算長尾現象，嚴重托慢大查詢性能。

合理設計二級分區

PolarDB-X支援二級分區。當資料量過大或者有資料傾斜時，二級分區的選擇至關重要，如果資料量大的表中沒有二級分區或者二級分區切分不合理，也會影響性能。如果業務明确有增量資料導入需求，主要是對最近資料的報表分析，那麼建議用日期格式做二級分區，避免對曆史過期資料的掃描。

//直接用col先做一級分區
PARTITION BY HASH(col) 
SUBPARTITION BY LIST (ds) 
//ds轉換後的月做分區
SUBPARTITION TEMPLATE (
   PARTITION p1 VALUES LESS THAN ('2021-08-00'),
   PARTITION p2 VALUES LESS THAN ('2021-09-00'),
)

合理設計索引

如果業務已經按照關聯字段，合理的設計了分區鍵。但依然還有部分複雜查詢涉及到對該表的其他列做關聯，無法做到關聯查詢下推，此時可以考慮基于該非分區鍵的列做全局二級索引。這樣複雜查詢對該表做關聯，可以轉化成與該全局二級索引做關聯。同時了為了避免回表的代價，對于分析場景建議所有的全局二級索引都建成聚簇索引。

查詢優化

在分析場景中，由于會涉及比較大的資料，且對簡單查詢的延遲有一定的容忍度，推薦您采用MPP執行模式，既利用多個計算節點（CN）的計算資源承擔複雜計算。一般隻在隻讀執行個體預設開啟MPP能力，如果您可以允許在主執行個體做分析需求，請聯系阿裡雲技術支援。

在查詢過程中，PolarDB-X首先會基于優化器選擇合适的分布式執行計劃，然後将計劃排程到各個計算節點，充分發揮整個叢集的計算資源加速查詢。這個過程生成的分布式執行計劃完全是基于統計資訊做代價選擇，是以及時的資訊采集至關重要；同時由于優化器生成的計劃不一定是最優的，是以這裡也給到您在SQL編寫和優化時的經驗。

收集統計資訊

PolarDB-X會及時定時收集統計資訊，如果發現PolarDB-X生成的分布式執行計劃不是最優的。可以通過ANALYZE TABLE手動對某個表做統計資訊收集。

SQL編寫技巧

去掉不必要的列由于分析場景大多數是高吞吐的，是以應該去除傳回過程中不必要的列，減少對帶寬的壓力。在編寫SQL時一定要确認業務需要傳回的列，不要直接使用星号（*）進行查詢。

//不合适寫法
select * from T1 where a1>100 and a2<1000;
//更合适寫法，隻需要傳回業務關心的列
select a1, a2 from T2 where a1>100 and a2<1000;

基于局部索引做過濾很多分析場景都期望用時間做二級分區，這樣做大資料掃描的時候可以把時間做過濾條件，過濾掉絕大多數曆史資料。

select a1,c2 from T1 where time >='2010-01-01 00:00:00';

為了避免全部掃描，目前預設會在這個分區列上做局部索引。同樣的在很多高吞吐的掃描場景下，可以考慮基于過濾條件做局部索引。
避免低效的SQL文法如果表記錄數非常大，掃描會很慢，直接導緻查詢緩慢。是以在SQL編寫過程中我們需要注意以下幾點：第一，避免索引失效

1. 不在索引列上做任何操作，計算、函數、類型轉換（自動或手動），會導緻索引失效而轉向全表掃描。

mysql> explain execute select * from staffs where name= 'hu';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 |      100 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set , 1 warning (0.00 sec)
//在索引列上做了其他操作，導緻索引試下
mysql> explain execute select * from staffs where left(name,4)= 'hu';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  198 |      100 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set , 1 warning (0.00 sec)

1. 在使用不等于（！=或<>）的時候，無法使用索引導緻全表掃描。
2. is null，is not null也無法使用索引。

mysql>  explain execute select * from staffs where name is null ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set

1. like以通配符開頭，mysql索引失效會進行全表掃描的操作。

mysql>  explain exeucte select * from staffs where name like '%hu' ;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  198 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set
mysql>  explain execute select * from staffs where name like 'hu%' ;
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | NULL |    1 |      100 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set

第二，盡量少用like，like操作一般不會很高效，盡量使用範圍條件到達目的。比如between...and...

第三，多表關聯場景下：

1. 盡量包含分區列條件。如果不包含，則盡量通過WHERE條件過濾掉多餘的資料。
2. outer join的on和where作用域不同。on是作用于join的過程，where是作用于join之後的結果，是以應該将能在join的時候提前過濾的條件寫在on上，也可以寫在join表的子查詢裡，這樣可以減少join原始表的資料量。

資料傾斜的檢查和處理

如果出現查詢異常緩慢，或者資源使用率不均勻的情況，則需要确認是否出現了資料傾斜。一般解決傾斜有三種政策：

通過 show info from table 檢查某個分片在各個節點上的資料分布計數，如果各節點上的資料分布明顯不均勻，則可以考慮對該表的分區鍵進行調整。
如果是出現了嚴重Join Key熱點問題，将傾斜的Key用單獨的邏輯來處理。例如兩邊的Key中有大量NULL資料導緻了傾斜，則需要在Join前先過濾掉NULL資料或者補上随機數，然後再進行Join，示例如下。

SELECT * FROM A JOIN B ON CASE WHEN A.value IS NULL THEN CONCAT('value',RAND() ) ELSE A.value END = B.value;

在實際場景中，如果您發現已經資料傾斜，但無法擷取導緻資料傾斜的Key資訊，可以使用如下方法檢視資料傾斜。

--執行如下語句查詢資料傾斜。
SELECT * FROM a JOIN b ON a.key=b.key;  
--您可以執行如下SQL，檢視Key的分布，判斷執行Join操作時是否會有資料傾斜。
SELECT left.key, left.cnt * right.cnt FROM 
(select key, count(*) AS cnt FROM a GROUP BY key) LEFT 
JOIN
(SELECT key, COUNT(*) AS cnt FROM b GROUP BY key) RIGHT
ON left.key=right.key;

如果Group By Key出現了熱點問題，可以考慮對SQL進行改寫，添加随機數，把長Key進行拆分。例如：

SELECT Key,COUNT(*) AS Cnt FROM TableName GROUP BY Key;
//優化成以下SQL，先對熱點做打散預聚合，再做最終聚合
-- 假設長尾的Key已經找到是KEY001。
SELECT a.Key
  , SUM(a.Cnt) AS Cnt
FROM (
  SELECT Key
    , COUNT(*) AS Cnt
FROM TableName
GROUP BY Key, 
  CASE 
    WHEN Key = 'KEY001' THEN rand() % 50
    ELSE 0
   END
) a
GROUP BY a.Key;

調整執行政策

按照上述政策調整後，查詢性能依然不理想且計算和存儲資源都未到達瓶頸，這個時候可以調整下執行政策。主要有兩種方式去調整：

加大并發度，您可以通過HINT /*+TDDL:MPP_PARALLELISM=4*/ 指定MPP執行器并行度。

mysql> /*+TDDL:TDDL:MPP_PARALLELISM=4*/ select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 or
der by cnt limit 5, 10;

通過HINT指定特定的算法，如何調整更好的聚合算法和關聯算法，請參見聚合和關聯。

最佳實踐—偏分析場景的實踐和優化

結構設計

查詢優化

調整執行政策

繼續閱讀

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

資料結構與算法（27）——排序（二）

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Dijkstra--簡易版（最短路徑）

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

hdu7108哈希