通过实验理解MogDB5.0.3的SQL Bypass

原作者：姜殿斌

SQL Bypass 特性描述:

在典型的OLTP（联机事务处理）场景中，简单查询通常占据了很大一部分的比例。这类查询的特点是只涉及单表和简单表达式的查询，不涉及复杂的联接、子查询和聚合操作等。为了加速这类简单查询的执行，5.0增强了SQL Bypass的功能。

SQL Bypass框架的主要思想是在解析(parse)阶段对这类简单查询做简单的模式判断，然后进入到特殊的执行路径中，跳过经典的执行器执行框架，包括算子的初始化与执行、表达式与投影等经典框架。相反，SQL Bypass框架会直接重写一套简洁的执行路径，并直接调用存储接口来执行查询，从而大大加速简单查询的执行速度。

通过使用SQL Bypass框架，可以避免复杂查询的开销，减少不必要的计算和数据传输，在满足简单查询需求的前提下，提高查询的性能和效率。这对于OLTP系统来说尤为重要，因为在该场景中，高并发的查询请求需要快速响应和处理。

实验：

1、开启SQL Bypass功能：

set enable_opfusion = on;
SET enable_bitmapscan to off;

2、创建表，并插入数据：

CREATE TABLE MogDB_sql_bypass_1 (id int, pname name, match text);
INSERT INTO MogDB_sql_bypass_1
 VALUES (
     generate_series(1, 20000),
     'player# ' || generate_series(1, 20000),
     'match# ' || generate_series(1, 11)
   );

3、创建索引：

CREATE index on MogDB_sql_bypass_1(id);

4、执行Explain查看索引扫描查询结果

4.1、简单的索引扫描场景

MogDB=# explain (costs on) select id from MogDB_sql_bypass_1 where id = 1;
               QUERY PLAN
   -----------------------------------------------------------------------------------------------------------

[Bypass]
Index Only Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1 (cost=0.00..48.07 rows=11 width=4)
Index Cond: (id = 1)
(3 rows)
4.2、带过滤条件的索引扫描场景``

MogDB=# explain (costs off) select id, pname, match from MogDB_sql_bypass_1 where id = 1;
               QUERY PLAN
 ------------------------------------------------------------------
 [Bypass]
 Index Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
  Index Cond: (id = 1)
 (3 rows)

MogDB=# explain (costs off) select id, pname, match from MogDB_sql_bypass_1 where id = 1;
         QUERY PLAN
 ------------------------------------------------------------------
 [Bypass]
 Index Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
  Index Cond: (id = 1)
 (3 rows)

4.3、PBE场景中的索引扫描

MogDB=# PREPARE p1 AS SELECT id FROM MogDB_sql_bypass_1 WHERE id = $1;
MogDB=# PREPARE p2 AS SELECT id, pname, match FROM MogDB_sql_bypass_1 WHERE id = $1;
MogDB=# PREPARE p3 AS SELECT id, pname, match FROM MogDB_sql_bypass_1 WHERE id = $1 AND match <> $2;

MogDB=# explain(costs OFF)EXECUTE p1(2);
        QUERY PLAN
 -----------------------------------------------------------------------
 [Bypass]
 Index Only Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
  Index Cond: (id = $1)
 (3 rows)

MogDB=# explain (costs off) EXECUTE p2(2);
        QUERY PLAN
 ------------------------------------------------------------------
 [Bypass]
 Index Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
  Index Cond: (id = $1)
 (3 rows)

MogDB=# explain (costs off) EXECUTE p3(2, 'match# 7');
         QUERY PLAN
 ------------------------------------------------------------------
 [Bypass]
 Index Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
  Index Cond: (id = $1)
  Filter: (match <> $2)
 (4 rows)

5、实验环境清理

将参数改回系统默认，删除表和索引：

SET enable_bitmapscan to on；
set enable_opfusion = off;
drop index mogdb_sql_bypass_1_id_idx；
drop table MogDB_sql_bypass_1

6、参考：

sql bypass

https://docs.mogdb.io/zh/mogdb/v5.0/sql-bypass

SQL调优关键参数调整

https://docs.mogdb.io/zh/mogdb/v5.0/resetting-key-parameters-during-sql-tuning

参数说明：

https://docs.mogdb.io/zh/mogdb/v5.0/optimizer-method-configuration#enable_bitmapscan