文档

Bloom Filter For HashJoin下推

更新时间:

当需要查询大量数据的时候,引擎层访问数据、SQL层的交互和计算都会占用大量的开销。PolarDB MySQL版会将Bloom Filter下推到引擎层进行计算,极大的减少性能开销,提升查询性能。

前提条件

  • 集群版本需为PolarDB MySQL版8.0版本且修订版本需为8.0.2.2.3或以上。如何查看集群版本,请参见查询版本号

  • 当前仅INT类型支持Bloom Filter For HashJoin下推。

背景信息

Bloom Filter是一种减少存储访问,提升计算效率的成熟方法。PolarDB MySQL版会利用Bloom Filter对HashJoin进行加速。基于代价会对大数量场景,在build hash table的时候创建Bloom Filter,然后下推到Probe端的引擎中,在Probe的时候利用Bloom Filter过滤掉SQL层计算不需要的数据。这可以使得引擎层和SQL层的数据转换、SQL层的计算都大幅减少,提升查询性能。

使用方法

您可以通过loose_bloom_filter_enabled参数开启Bloom Filter优化功能。具体操作请参见设置集群参数和节点参数

参数名称

级别

描述

loose_bloom_filter_enabled

Global、Session

Bloom Filter优化开关。取值范围如下:

  • ON(默认值):开启Bloom Filter优化开关。

  • OFF:关闭Bloom Filter优化开关。

示例

本文以TPCH不创建Primary key和Index的Schema为例。下文分别展示了TPCH中Q3、Q11和Q16的执行计划,在Extra列可以看到hash join with bloom filter的信息。

  • Q3:

    EXPLAIN
    SELECT l_orderkey,
           SUM(l_extendedprice * (1 - l_discount)) AS revenue,
           o_orderdate,
           o_shippriority
    FROM customer,
         orders,
         lineitem
    WHERE c_mktsegment = 'MACHINERY'
      AND c_custkey = o_custkey
      AND l_orderkey = o_orderkey
      AND o_orderdate < '1995-03-10'
      AND l_shipdate > '1995-03-10'
    GROUP BY l_orderkey,
             o_orderdate,
             o_shippriority
    ORDER BY revenue DESC,
             o_orderdate
    LIMIT 10\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: customer
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 148463
         filtered: 10.00
            Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: orders
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1486962
         filtered: 3.33
            Extra: Using where; Using join buffer (hash join with bloom filter)
    *************************** 3. row ***************************
               id: 1
      select_type: SIMPLE
            table: lineitem
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 5948979
         filtered: 3.33
            Extra: Using where; Using join buffer (hash join with bloom filter)
  • Q11:

    EXPLAIN
    SELECT ps_partkey,
           SUM(ps_supplycost * ps_availqty) AS value
    FROM partsupp,
         supplier,
         nation
    WHERE ps_suppkey = s_suppkey
      AND s_nationkey = n_nationkey
      AND n_name = 'INDIA'
    GROUP BY ps_partkey
    HAVING SUM(ps_supplycost * ps_availqty) >
      (SELECT SUM(ps_supplycost * ps_availqty) * 0.0001000000
       FROM partsupp,
            supplier,
            nation
       WHERE ps_suppkey = s_suppkey
         AND s_nationkey = n_nationkey
         AND n_name = 'INDIA' )
    ORDER BY value DESC\G
    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: nation
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 25
         filtered: 10.00
            Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
               id: 1
      select_type: PRIMARY
            table: supplier
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 10000
         filtered: 10.00
            Extra: Using where; Using join buffer (hash join with bloom filter)
    *************************** 3. row ***************************
               id: 1
      select_type: PRIMARY
            table: partsupp
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 791815
         filtered: 10.00
            Extra: Using where; Using join buffer (hash join with bloom filter)
    *************************** 4. row ***************************
               id: 2
      select_type: SUBQUERY
            table: nation
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 25
         filtered: 10.00
            Extra: Using where
    *************************** 5. row ***************************
               id: 2
      select_type: SUBQUERY
            table: supplier
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 10000
         filtered: 10.00
            Extra: Using where; Using join buffer (hash join with bloom filter)
    *************************** 6. row ***************************
               id: 2
      select_type: SUBQUERY
            table: partsupp
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 791815
         filtered: 10.00
            Extra: Using where; Using join buffer (hash join with bloom filter)
  • Q16:

    EXPLAIN SELECT p_brand,
           p_type,
           p_size,
           COUNT(DISTINCT ps_suppkey) AS supplier_cnt
    FROM partsupp,
         part
    WHERE p_partkey = ps_partkey
      AND p_brand <> 'Brand#33'
      AND p_type NOT LIKE 'PROMO POLISHED%'
      AND p_size IN (34,
                     45,
                     33,
                     42,
                     9,
                     24,
                     26,
                     7)
      AND ps_suppkey NOT IN
        (SELECT s_suppkey
         FROM supplier
         WHERE s_comment LIKE '%Customer%Complaints%' )
    GROUP BY p_brand,
             p_type,
             p_size
    ORDER BY supplier_cnt DESC,
             p_brand,
             p_type,
             p_size\G
    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: part
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 198116
         filtered: 40.00
            Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
               id: 1
      select_type: PRIMARY
            table: partsupp
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 791815
         filtered: 10.00
            Extra: Using where; Using join buffer (hash join with bloom filter)
    *************************** 3. row ***************************
               id: 2
      select_type: SUBQUERY
            table: supplier
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 10000
         filtered: 11.11
            Extra: Using where

性能效果

以TPCH不创建Primary key和Index为例,基于scale 1的数据,针对上文所述的查询示例Q3、Q11、Q16。开启与关闭Bloom Filter功能的性能对比如下:Bloom Filter