文档

OSS_FILE_FILTER查询加速功能

更新时间:

本文介绍了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的数据类型:

    类别

    数据类型

    整数

    TINYINTSMALLINTMEDIUMINT INTBIGINTTINYINT UNSIGNEDSMALLINT UNSIGNEDMEDIUMINT UNSIGNEDINT UNSIGNEBIGINT UNSIGNED

    浮点数

    FLOATDOUBLE

    定点数

    DECIMAL

    时间类型

    DATEDATETIMETIMETIMESTAMP

    字符串类型

    CHARVARCHAR

  • 如果使用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;