PolarDB MySQL冷存查询加速之OSS数据筛选

PolarDB MySQL冷数据查询功能已经支持了OSS文件筛选 OSS FILE FILTER 功能，该功能通过利用查询条件预前锁定数据存储位置，实现了数据检索策略的优化升级。相较于传统的并行查询方法，OSS FILE FILTER 展现出更高的资源效率，在具备有利筛选条件的场景下，其查询性能的提升效果尤为显著，这不仅归功于减少了不必要的数据扫描，还有效提升了处理速度与系统响应能力。以下我们进行详细介绍。

使用方法：

这里以经典的 lineitem 表作为分析案例，介绍如何使用 OSS FILE FILTER 加速查询。我们提前生成了一个大约 10G 的 TPCH lineitem 标准数据集，表定义如下：

mysql> show create table lineitem \G
*************************** 1. row ***************************
       Table: lineitem
Create Table: 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(10,2) NOT NULL,
  `l_extendedprice` decimal(10,2) NOT NULL,
  `l_discount` decimal(10,2) NOT NULL,
  `l_tax` decimal(10,2) NOT NULL,
  `l_returnflag` char(1) NOT NULL,
  `l_linestatus` char(1) NOT NULL,
  `l_shipDATE` date NOT NULL,
  `l_commitDATE` date NOT NULL,
  `l_receiptDATE` date NOT NULL,
  `l_shipinstruct` char(25) NOT NULL,
  `l_shipmode` char(10) NOT NULL,
  `l_comment` varchar(44) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */ CONNECTION='default_oss_server'
1 row in set (0.01 sec)
复制

可以看到，当前表已经开启了 OSS META，这是冷数据 OSS File Filter 的前提，我们以 Q6 为例，查看其执行计划：

首先，打开 OSS File Filter 的开关：

SET csv_oss_file_filter = ON;
复制

执行计划为：

mysql> explain select
    ->     sum(l_extendedprice * l_discount) as revenue
    -> from
    ->     lineitem
    -> where
    ->     l_shipdate >= date '1994-01-01'
    ->     and l_shipdate < date '1994-01-01' + interval '1' year
    ->     and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    ->     and l_quantity < 24;
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59986051 |     0.41 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
复制

实际执行时间大约为 5min

mysql> select
    ->     sum(l_extendedprice * l_discount) as revenue
    -> from
    ->     lineitem
    -> where
    ->     l_shipdate >= date '1994-01-01'
    ->     and l_shipdate < date '1994-01-01' + interval '1' year
    ->     and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    ->     and l_quantity < 24;
+-----------------+
| revenue         |
+-----------------+
| 1230113636.0101 |
+-----------------+
1 row in set (5 min 11.25 sec)
复制

而 Q6 上主要用了 l_shipdate ，l_discount ，l_quantity 做 Filter，我们在这几列上增加 OSS FILE FILTER，语法为：

mysql> ALTER TABLE lineitem OSS_FILE_FILTER = 'l_shipdate,l_quantity,l_discount';
复制

执行完成后，可以看到表定义变为：

mysql> show create table lineitem \G
*************************** 1. row ***************************
       Table: lineitem
Create Table: 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(10,2) NOT NULL,
  `l_extendedprice` decimal(10,2) NOT NULL,
  `l_discount` decimal(10,2) NOT NULL,
  `l_tax` decimal(10,2) NOT NULL,
  `l_returnflag` char(1) NOT NULL,
  `l_linestatus` char(1) NOT NULL,
  `l_shipDATE` date NOT NULL,
  `l_commitDATE` date NOT NULL,
  `l_receiptDATE` date NOT NULL,
  `l_shipinstruct` char(25) NOT NULL,
  `l_shipmode` char(10) NOT NULL,
  `l_comment` varchar(44) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */ /*!99990 800020223 OSS_FILE_FILTER='l_shipdate,l_quantity,l_discount' */ CONNECTION='default_oss_server'
1 row in set (0.00 sec)
复制

此时，再执行 Q6，explain 看了看到使用了查询加速功能，同时执行时间缩短了很多。

mysql> explain select
    ->     sum(l_extendedprice * l_discount) as revenue
    -> from
    ->     lineitem
    -> where
    ->     l_shipdate >= date '1994-01-01'
    ->     and l_shipdate < date '1994-01-01' + interval '1' year
    ->     and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    ->     and l_quantity < 24 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 59986051
     filtered: 0.41
        Extra: Using where; With pushed engine condition ((`multiply`.`lineitem`.`l_shipDATE` >= DATE'1994-01-01') and (`multiply`.`lineitem`.`l_shipDATE` < <cache>((DATE'1994-01-01' + interval '1' year))) and (`multiply`.`lineitem`.`l_discount` between <cache>((0.06 - 0.01)) and <cache>((0.06 + 0.01))) and (`multiply`.`lineitem`.`l_quantity` < 24.00))
1 row in set, 1 warning (0.01 sec)

mysql> select
    ->     sum(l_extendedprice * l_discount) as revenue
    -> from
    ->     lineitem
    -> where
    ->     l_shipdate >= date '1994-01-01'
    ->     and l_shipdate < date '1994-01-01' + interval '1' year
    ->     and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    ->     and l_quantity < 24;
+-----------------+
| revenue         |
+-----------------+
| 1230113636.0101 |
+-----------------+
1 row in set (3 min 59.63 sec)
复制

由于 Q6 的查询条件筛选率并不高，如果是在 l_orderkey 上做等值查询，查询时间可以直接缩短到秒级。首先，在 l_orderkey 上增加 OSS FILE FILTER：

mysql> ALTER TABLE lineitem OSS_FILE_FILTER = 'l_orderkey,l_shipdate,l_quantity,l_discount';
复制