行存和列存自動分流的方案說明 - PolarDB

如果您的業務中，OLAP類型的查詢與OLTP類型的查詢是通過同一個應用程式訪問資料庫，此時如果要實現分流，您需要設定叢集地址的讀寫入模式，並開啟自動分流功能。自動分流功能開啟後，資料庫代理基於業務流入的SQL語句的預估執行代價來實現自動分流，從而實現SQL查詢的最大效能。當SQL語句的預估執行代價超過一定值時，資料庫代理可自動將該請求分流至唯讀列存節點處理，反之該請求則由行存唯讀節點或主節點來處理。

自動分流方案說明

PolarDB MySQL版的資料庫代理功能支援將SQL語句的預估執行代價作為判斷指標，通過判斷預估執行代價是否會超過特定閾值（該閾值可配置），決定將該SQL請求分流到行存或是列存節點上處理，從而發揮出行存和列存的最佳效能。

分流規則：

OLTP類業務：一般包含讀和寫的請求。寫請求統一由主節點處理，讀請求由唯讀行存節點或主節點處理。
OLAP類業務：一般僅包含讀請求。讀請求統一由唯讀列存節點處理。

自動分流方案：

主節點和唯讀列存節點分流：由於主節點也屬於行存節點，因此也可處理OLTP類讀請求。該方案下，寫請求以及OLTP類讀請求分流至主節點，OLAP類讀請求分流至唯讀列存節點。
唯讀行存節點和唯讀列存節點分流：該方案下，寫請求依舊分流至主節點，OLTP類讀請求分流至唯讀行存節點或主節點，OLAP類讀請求分流至唯讀列存節點。

混合下分流

使用限制

服務節點至少包含一個唯讀列存節點和一個行存節點。

步驟1：開啟列存和行存自動引流功能

登入PolarDB控制台。
在左上方，選擇叢集所在地區。
找到目的地組群，單擊叢集ID。
在叢集基本信息頁的数据库代理企业版地區，找到目的地組群地址，單擊目的地組群位址名稱右側的配置。
根據實際情況選擇合適的读写模式。
- 叢集地址的讀寫入模式設定為可读可写（自动读写分离）。
- 叢集地址的讀寫入模式設定為只读，並且叢集地址的負載平衡原則設定為基於活躍請求數負載平衡。
在服务节点中，選擇主節點和需要參與處理請求的行存和列存唯讀節點。並在右側HTAP优化類別中，選擇開啟行存/列存自动引流。單擊確定。
說明
服务节点中需要至少選擇一個唯讀列存節點。
樣本1：在下圖中，除了主節點外，服務節點還包含1個唯讀節點（即唯讀行存節點）和2個唯讀列存節點。若此時開啟自動引流功能，則：
- 寫請求將引流至主節點。
- OLAP類讀請求引流至唯讀列存節點。
- OLTP類讀請求引流至唯讀行存節點。如果負載平衡設定中將主庫是否接受讀設定為是時，也可能會引流至主節點。
樣本2：在下圖中，服務節點包括主節點和1個唯讀列存節點。若此時開啟自動引流功能後，寫請求將引流至主節點，OLAP類讀請求將引流至唯讀列存節點，OLTP類讀請求將引流至主節點。
說明
可读可写（自动读写分离）模式下，無論主節點是否已被添加在服務節點中，所有寫請求只會發往主節點。

步驟2：配置自動引流閾值

開啟了列存和行存自動引流後，您需要設定SQL語句的預估執行代價閾值。閾值配置完成後，資料庫代理將此閾值作為判斷依據。若業務側請求的SQL語句的預估執行代價大於該閾值，則將該請求引流至列存節點執行；若小於該閾值，則將該請求引流至行存節點執行。

當前SQL語句的預估執行代價閾值由下表中的參數決定，您可以在叢集的参数配置頁面，根據自身業務情況修改參數的值，從而調整自動分流效果。

參數名稱	描述
loose_imci_ap_threshold	分發至列存節點的SQL語句的預估執行代價閾值。預設值：50000。說明開啟列存和行存自動引流功能後，如果SQL語句的預估執行代價閾值大於50000，則路由至列存節點。重要 PolarDB MySQL版8.0.1.1.39與8.0.2.2.23及之後的版本，該參數被棄用，分流參數統一使用`loose_cost_threshold_for_imci`。
loose_cost_threshold_for_imci	列存節點內部當前SQL語句的預估執行代價閾值。預設值：50000。說明開啟列存和行存自動引流功能後，如果SQL語句的預估執行代價閾值大於50000，則選擇列存執行計畫。否則，選擇行存執行計畫。

您可以通過SHOW STATUS LIKE 'Last_query_cost'命令精確查詢上一條SQL語句的預估執行代價，從而判斷如何調整上表中的參數值。

說明

如果使用叢集地址串連資料庫，您需要在SHOW STATUS LIKE 'Last_query_cost'命令前添加HINT文法/* ROUTE_TO_LAST_USED*/，以確保能夠在正確的節點查詢到上一條語句的預估執行代價。如下：

/*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost';

例如，執行以下語句，可查看上一條SQL語句的預估執行代價：

SHOW STATUS LIKE 'Last_query_cost';

查詢結果如下：

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Last_query_cost      | 2     |
+----------------------+-------+
1 row in set (0.01 sec)

從以上查詢結果可以看出，該SQL語句的預估執行代價為2。

此時若要實現將該SQL查詢分流至列存節點的列存計劃執行，則需要設定的參數值如下：

PolarDB MySQL版8.0.1.1.38與8.0.2.2.22及之前的版本
需要將loose_imci_ap_threshold和loose_cost_threshold_for_imci參數的值分別設定為1。
PolarDB MySQL版8.0.1.1.39與8.0.2.2.23及之後的版本
需要將參數loose_cost_threshold_for_imci的值設定為1。

通過`HINT`文法強制執行行存或列存執行計畫

如果使用行存和列存自動分流沒有達到預期效果，您可以使用HINT文法強制執行行存或列存執行計畫。

說明

HINT文法僅對指定的SQL語句生效，對其它串連或同一個串連下的其它SQL語句沒有影響。
如果在5.7.7版本之前的MySQL用戶端執行HINT文法，則在串連資料庫引擎時需要添加--comments選項。您可以使用mysql --version命令來查看MySQL用戶端版本。

強制執行列存執行計畫。
- PolarDB MySQL版8.0.1.1.38與8.0.2.2.22及之前的版本
  使用資料庫代理進行行存和列存自動分流時，還可以通過HINT文法使得資料庫代理將SQL語句強制分發至列存節點執行，而不受loose_imci_ap_threshold影響。具體方式為：在SQL關鍵字前添加/* FORCE_IMCI_NODES */。例如：
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;
```
  路由至列存節點的SQL語句依然會受loose_cost_threshold_for_imci影響，如果需要強制SQL語句選擇列存執行計畫，可以通過HINT文法降低loose_cost_threshold_for_imci的值。例如：
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```
- PolarDB MySQL版8.0.1.1.39與8.0.2.2.23及之後的版本
  只需要通過HINT文法將參數loose_cost_threshold_for_imci設定為0即可。
```
EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```
說明
通過/*+SET_VAR()*/修改閾值時，需要刪掉參數首碼loose_ ，否則HINT語句不生效。
強制執行行存執行計畫。
您可以通過HINT文法將USE_IMCI_ENGINE的值修改為OFF，來強制SQL語句使用行存執行計畫。樣本如下：
```
EXPLAIN SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```

自動分流方案說明

使用限制

步驟1：開啟列存和行存自動引流功能

步驟2：配置自動引流閾值

通過HINT文法強制執行行存或列存執行計畫

通過`HINT`文法強制執行行存或列存執行計畫