全部产品
Search

搜索本产品

    云原生数据库 PolarDB：OSS_FILE_FILTER查询加速功能

    文档中心

    云原生数据库 PolarDB：OSS_FILE_FILTER查询加速功能

    更新时间：Aug 19, 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;