全部產品
Search

搜尋本產品

    PolarDB：OSS_FILE_FILTER查詢加速功能

    文件中心

    PolarDB：OSS_FILE_FILTER查詢加速功能

    更新時間：Aug 20, 2024

    本文介紹了OSS冷資料的查詢加速功能。

    背景資訊

    隨著OSS應用的日益廣泛，儲存的資料量也迅速增加，查詢效能低下問題日益突出，嚴重影響體驗。儘管冷資料並行查詢可以提供更快的查詢速度，但在掃描OSS表的過程中，冷資料並行查詢的高效需要消耗大量記憶體、串連數和網路頻寬資源，業務體驗仍然會受到一定影響。因此，我們急需一種能夠快速過濾OSS表資料的方法，以提升查詢效能。

    為瞭解決這一問題，我們引入了OSS資料區塊過濾（OSS File Filter）功能。該功能根據查詢條件排除不需要掃描的OSS資料區塊，從而減少需要掃描的資料量，大幅提升查詢效能。這一創新不僅最佳化了資源使用效率，也顯著增強了使用體驗。

    功能說明

    查詢加速功能通過收集每個歸檔資料區塊的統計資訊產生過濾資料（filter data），並將其儲存在OSS上。在查詢過程中，系統會根據引擎下推的查詢條件結合過濾資料，排除不需要掃描的資料區塊，從而減少資料掃描量並縮短查詢時間。OSS冷資料可以通過OSS_FILE_FILTER對資料進行過濾。針對不同的資料類型OSS_FILE_FILTER採用了不同的過濾方式：數實值型別通過比較資料區塊最小值和最大值統計資訊來進行過濾；字串類型則通過比較字元對應表進行過濾。如果指定了過濾類型為BLOOM類型，則將使用布隆過濾器進行過濾。

    對於有序或局部有序的數實值型別資料，OSS_FILE_FILTER能夠提供更優異的處理效果。然而，對於全域無序的資料，建議使用布隆過濾器，以實現更有效過濾。

    由于歸檔資料通常具有較好的時間序列，因此使用時間類型的列進行過濾可以顯著提升查詢效能。

    前置條件

    • 資料庫引擎版本為MySQL 8.0.2，且核心小版本為8.0.2.2.25及以上。

    • 已開啟冷資料歸檔功能。

    • 已串連資料庫叢集。

    • 僅支援CSV格式的OSS冷資料建立 OSS_FILE_FILTER。

    • 要在當前表上支援OSS_FILE_FILTER，必須具有OSS META = 1的選項。通過SHOW CREATE TABLE命令，可以在CSV格式中查看當前表是否已開啟OSS META。如果返回結果中包含OSS META=1，則表示當前表已開啟OSS META。如需詳細瞭解請參考冷資料DDL。

      SHOW CREATE TABLE t;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */

1 row in set (0.00 sec)

    使用限制

    • 目前暫不支援在ePQ彈性並行查詢的情境下使用OSS_FILE_FILTER過濾資料。

    • 當前支援的OSS_FILE_FILTER函數：=、<=>、<、<=、>=、>、BETWEEN、LIKE、IS NULL、IS NOT NULL、LIKE只支援右百分比符號（LIKE 'ABC%')。

    • 當前支援OSS_FILE_FILTER的資料類型：

      類別

      資料類型

      整數

      TINYINT，SMALLINT，MEDIUMINT ，INT，BIGINT，TINYINT UNSIGNED，SMALLINT UNSIGNED，MEDIUMINT UNSIGNED，INT UNSIGNE，BIGINT UNSIGNED

      浮點數

      FLOAT，DOUBLE

      定點數

      DECIMAL

      時間類型

      DATE，DATETIME，TIME，TIMESTAMP

      字串類型

      CHAR，VARCHAR

    • 如果使用BLOOM FILTER則不限制列的資料類型。

    • 如果字串類型儲存的是UUID值，建議使用BLOOM FILTER進行過濾。

    • 對於字串類型的列，只支援大小寫敏感的比較方式。

    • 支援多個條件之間的AND運算，但不支援多個條件之間的OR運算。

    • 如果OSS表裡沒有資料，則不能使用OSS_FILE_FILTER查詢加速。

    操作步驟

    1. 請在控制台中將叢集參數loose_use_oss_meta設定為ON，開啟USE_OSS_META功能，叢集參數配置請參考設定叢集參數和節點參數。

    2. 請在控制台中將叢集參數loose_optimizer_switch設定為ENGINE_CONDITION_PUSHDOWN=ON，叢集參數配置請參考設定叢集參數和節點參數。

    3. 請在控制台中將叢集參數loose_csv_oss_file_filter設定為ON，開啟OSS_FILE_FILTER功能，叢集參數配置請參考設定叢集參數和節點參數。

    4. 請確保資料庫叢集已成功串連。如尚未串連，請參考串連資料庫叢集相關文檔進行串連操作，驗證當前參數配置是否已成功應用。

      -- 檢查oss_file_filter是否設定成功
SHOW VARIABLES LIKE 'oss_file_filter';
-- 檢查use_oss_meta是否設定成功
SHOW VARIABLES LIKE 'use_oss_meta';
-- 檢查engine_condition_pushdown是否設定成功
SHOW VARIABLES LIKE 'optimizer_switch';

    OSS_FILE_FILTER格式

    通過給CSV格式歸檔表增加OSS_FILE_FILTER的table option來建立表的OSS_FILE_FILTER資料。OSS_FILE_FILTER格式如下：

    OSS_FILE_FILTER = 'field_filter[,field_filter]'
field_filter := field_name[:filter_type]
filter_type := bloom

    各列類型均有預設的FILE_FILTER類型（數值和時間類型預設使用MIN/MAX統計過濾，字串類型預設使用CHARACTOR MAP過濾），所有列類型均可通過指定FILE_FILTER為BLOOM類型使用布隆過濾器。

    樣本

    歸檔冷存表時建立OSS_FILE_FITLER

    手動歸檔表時建立OSS_FILE_FITLER

    -- 表結構
CREATE TABLE `lineitem` (
  `L_ORDERKEY` int(11) NOT NULL,
  `L_PARTKEY` int(11) NOT NULL,
  `L_SUPPKEY` int(11) NOT NULL,
  `L_LINENUMBER` int(11) NOT NULL,
  `L_QUANTITY` decimal(15,2) NOT NULL,
  `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
  `L_DISCOUNT` decimal(15,2) NOT NULL,
  `L_TAX` decimal(15,2) NOT NULL,
  `L_RETURNFLAG` char(1) COLLATE utf8_bin NOT NULL,
  `L_LINESTATUS` char(1) COLLATE utf8_bin NOT NULL,
  `L_SHIPDATE` date NOT NULL,
  `L_COMMITDATE` date NOT NULL,
  `L_RECEIPTDATE` date NOT NULL,
  `L_SHIPINSTRUCT` char(25) COLLATE utf8_bin NOT NULL,
  `L_SHIPMODE` char(10) COLLATE utf8_bin NOT NULL,
  `L_COMMENT` varchar(44) COLLATE utf8_bin NOT NULL
) ENGINE = innodb;

-- 歸檔表同時在L_ORDERKEY、L_LINENUMBER、L_SHIPDATE列建立oss file filter
ALTER TABLE lineitem ENGINE = CSV STORAGE OSS
  OSS_FILE_FILTER = 'L_ORDERKEY,L_LINENUMBER,L_SHIPDATE';

    已歸檔的表新增OSS_FILE_FITLER

    對已經完成歸檔的表建立OSS_FILE_FITLER

    -- 表結構
CREATE TABLE `lineitem` (
  `L_ORDERKEY` int(11) NOT NULL,
  `L_PARTKEY` int(11) NOT NULL,
  `L_SUPPKEY` int(11) NOT NULL,
  `L_LINENUMBER` int(11) NOT NULL,
  `L_QUANTITY` decimal(15,2) NOT NULL,
  `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
  `L_DISCOUNT` decimal(15,2) NOT NULL,
  `L_TAX` decimal(15,2) NOT NULL,
  `L_RETURNFLAG` char(1) COLLATE utf8_bin NOT NULL,
  `L_LINESTATUS` char(1) COLLATE utf8_bin NOT NULL,
  `L_SHIPDATE` date NOT NULL,
  `L_COMMITDATE` date NOT NULL,
  `L_RECEIPTDATE` date NOT NULL,
  `L_SHIPINSTRUCT` char(25) COLLATE utf8_bin NOT NULL,
  `L_SHIPMODE` char(10) COLLATE utf8_bin NOT NULL,
  `L_COMMENT` varchar(44) COLLATE utf8_bin NOT NULL
) ENGINE = CSV STORAGE OSS;

-- 在已經歸檔的表的L_ORDERKEY、L_LINENUMBER、L_SHIPDATE、L_SHIPINSTRUCT 列建立oss file filter，其中 L_SHIPINSTRUCT 列上是BLOOM
ALTER TABLE lineitem OSS_FILE_FILTER = 'L_ORDERKEY,L_LINENUMBER,L_SHIPDATE,L_SHIPINSTRUCT:BLOOM';

    使用OSS_FILE_FILTER

    查詢條件如果使用OSS_FILE_FILTER，將會被下推到CSV格式的OSS冷資料中。通過查詢計劃可以查看被下推到引擎的條件。

    -- 表結構
CREATE TABLE `lineitem` (
  `L_ORDERKEY` int(11) NOT NULL,
  `L_PARTKEY` int(11) NOT NULL,
  `L_SUPPKEY` int(11) NOT NULL,
  `L_LINENUMBER` int(11) NOT NULL,
  `L_QUANTITY` decimal(15,2) NOT NULL,
  `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
  `L_DISCOUNT` decimal(15,2) NOT NULL,
  `L_TAX` decimal(15,2) NOT NULL,
  `L_RETURNFLAG` char(1) COLLATE utf8_bin NOT NULL,
  `L_LINESTATUS` char(1) COLLATE utf8_bin NOT NULL,
  `L_SHIPDATE` date NOT NULL,
  `L_COMMITDATE` date NOT NULL,
  `L_RECEIPTDATE` date NOT NULL,
  `L_SHIPINSTRUCT` char(25) COLLATE utf8_bin NOT NULL,
  `L_SHIPMODE` char(10) COLLATE utf8_bin NOT NULL,
  `L_COMMENT` varchar(44) COLLATE utf8_bin NOT NULL
) ENGINE = CSV STORAGE OSS OSS_FILE_FILTER = 'L_ORDERKEY,L_LINENUMBER,L_SHIPDATE';

-- 查詢計劃
explain select * from lineitem where l_orderkey=96;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------------------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                                                           |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------------------------------------------------------+
|  1 | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6001215 |    10.00 | Using where; With pushed engine condition (`test`.`lineitem`.`L_ORDERKEY` = 96) |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------------------------------------------------------+

explain format = tree select * from lineitem where l_orderkey=96 \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (lineitem.L_ORDERKEY = 96)  (cost=15010.00 rows=10000)
    -> Table scan on lineitem, extra (oss_file_filter conditions: (lineitem.L_ORDERKEY = 96))  (cost=15010.00 rows=100000)

-- 執行SQL
select count(*) from lineitem where l_orderkey=96;