当需要查询大量数据的时候，引擎层访问数据、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优化开关。取值范围如下：
|
示例
本文以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功能的性能对比如下：