當需要查詢大量資料的時候，引擎層訪問資料、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功能的效能對比如下：