如何为GeoServer图层添加SQL HINT优化SAP Hana查询？

问题背景 #

使用GeoServer 2.21.1、GeoTools 27.1及SAP Hana插件，数据存储于SAP Hana数据库，涉及复杂查询与视图。默认基于成本的查询优化导致部分查询耗时过长，尝试添加WITH HINT OPTIMIZATION_LEVEL (RULE_BASED)提示，但未达到预期效果。

原始生成SQL #

SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC" 
FROM 
(select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
) as "vtable" 
WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1 LIMIT 1000000 OFFSET 0

期望目标SQL #

SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC" 
FROM 
(select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
) as "vtable" 
WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1 LIMIT 1000000 OFFSET 0
WITH HINT OPTIMIZATION_LEVEL (RULE_BASED) 

实际错误生成SQL #

SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC" 
FROM 
(select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
WITH HINT OPTIMIZATION_LEVEL (RULE_BASED) 
) as "vtable" 
WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1 LIMIT 1000000 OFFSET 0

可行解决方案 #

1. 扩展GeoTools SAP Hana Dialect #

GeoServer的SQL生成逻辑由GeoTools的JDBC Dialect控制，默认Hana Dialect不会在全局查询末尾追加Hint。可以通过自定义Dialect实现：

• 创建类继承org.geotools.data.hana.HanaDialect
• 重写prepareQuery或getSql相关方法，在生成的完整SQL语句末尾拼接WITH HINT OPTIMIZATION_LEVEL (RULE_BASED)
• 将编译后的类打包为JAR，替换GeoServerWEB-INF/lib目录下的原Hana插件JAR（或确保自定义Dialect被优先加载）

2. 数据库层面设置优化规则 #

若修改GeoServer代码成本较高，可直接在SAP Hana中配置：

• 针对目标视图创建计划提示，强制使用规则优化：

  CREATE PLAN HINT FOR VIEW MG_SPATIAL.V_GIS_DISTRICT 
  OPTIMIZATION_LEVEL (RULE_BASED);
  

• 或为GeoServer所用数据库用户设置默认优化级别（注意会影响该用户所有查询）：

  ALTER USER <GeoServer数据库用户> SET PARAMETER OPTIMIZATION_LEVEL = 'RULE_BASED';
  

3. 调整GeoServer SQL视图封装逻辑 #

针对特定图层，通过多层嵌套SQL视图让Hint出现在外层：

• 在GeoServer的SQL视图中使用如下结构，避免GeoServer额外嵌套外层查询（需关闭图层自动分页或调整查询设置）：

  SELECT * FROM (
      SELECT ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC 
      FROM MG_SPATIAL.V_GIS_DISTRICT
      WHERE GEOLOC.ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1
  ) AS t
  WITH HINT OPTIMIZATION_LEVEL (RULE_BASED)
  LIMIT 1000000 OFFSET 0
  

内容的提问来源于stack exchange，提问作者Hannu Huttunen