• 1
  • 2
  • 3
  • 4
  • 5
mssql数据库问题 首 页  »  帮助中心  »  数据库  »  mssql数据库问题
RDS for PG加入Plan Hint功能说明
发布日期:2016-4-29 16:4:7

  有一个功能,是社区官方版"永远"不考虑引入的(参见PG TODO,查找"Oracle-style"),就是类似Oracle的Plan Hint。社区开发者的理念是,引入Hint功能,会掩盖优化器本身的问题,导致缺陷不被暴露出来。但是对于我们的使用者来说,遇到某些SQL的查询计划不好,性能出了问题,使用了其他方法又不奏效的情况下,首先的目标还是想尽快解决问题。而Hint就可以在这种时候帮助到我们。可喜的是,通过集成“民间”的pg_hint_plan插件(文档),RDS for PG已经支持了Hint功能(RDS for PPAS也是支持的)。现在我们来研究一下这个插件如何使用,又是如何做到改变优化器优化过程,让优化器听我们“指挥”的。

  使用

  Plan Hint在RDS for PG里面缺省是没有打开的,可以LOAD命令启用:

  postgres=# LOAD 'pg_hint_plan';

  LOAD

  但是注意这只在会话级别有效,重新连接后将失效。如果想要每次连接都自动启用Hint,可以使用下面的命令(注意必须以RDS的根用户执行,否则会遇到权限错误)。这样,下次连接时,Hint就默认启用了。

  postgres=> alter role all set session_preload_libraries = 'pg_hint_plan';

  ALTER ROLE

  为了便于说明,我们使用下面的shell脚本来创建2张表,如下所示:

  for i in `seq 2` ; do

  psql -c "drop table t${i}"

  psql -c "create table t${i}(a int, b int);"

  psql -c "insert into t${i} select generate_series(1,1000), random() *1000+1"

  psql -c "create index t${i}_i_a on t${i}(a)"

  psql -c "create index t${i}_i_b on t${i}(b)"

  done

  然后在t1上进行查询,不使用和使用Hint的查询计划分别如下所示:

  postgres=> explain select * from t1 where a = 1;

  QUERY PLAN

  -----------------------------------------------------------------

  Index Scan using t1_i_a on t1 (cost=0.28..8.29 rows=1 width=8)

  Index Cond: (a = 1)

  (2 rows)

  postgres=> /*+ SeqScan(t1) */ explain select * from t1 where a = 1;

  QUERY PLAN

  ---------------------------------------------------

  Seq Scan on t1 (cost=0.00..17.50 rows=1 width=8)

  Filter: (a = 1)

  (2 rows)

  可以看出,利用Hint后,我们成功强制使用了表扫描。Hint一般以SQL注释的形式,出现在SQL的前面,并以/*+开头,以*/结尾。注意/*和+之间不能有空格。

  Hint的种类

  pg_hint_plan插件支持的Hint有很多种,分成如下几类(具体可以参见pg_hint_plan文档):

  扫描类(Scan Method),指定表的访问路径,举例如下:

  --顺序扫描,参数为表名,也可以带模式名

  SeqScan(t1)

  --索引扫描,参数为表名和索引名,注意两者之间是空格,没有逗号

  IndexScan(t1 t1_i_a)

  --TID扫描

  TidScan(t1)

  --禁止索引扫描

  NoIndexScan(t1)

  --禁止顺序扫描

  NoSeqScan(t1)

  连接类(Join Method),指定表连接的方法,举例如下:

  NestLoop(t1 t2)

  MergeJoin(t1 t2)

  HashJoin(t1 t2)

  连接顺序类(Join Order),指定连接的顺序,举例如下:

  --使t3和t1先连接,最后和t2连接

  Leading(t2 (t3 t1))

  SET类,即改变任意的GUC变量,举例如下所示:

  --改变random_page_cost

  Set(random_page_cost 3.0)

  ROW类型,改变表的连接结果集的估计大小,举例如下:

  --将t1和t2的连接结果的估计大小扩大10倍

  Rows(t1 t2 *10)

  内核实现

  看完了形形色色的Hint,我们会想,这些Hint是怎么改变复杂的优化器逻辑,使其生成我们需要的查询计划的呢?我们从其源码看起。

  插件主要的代码集中在pg_hint_plan.c里面。从其中PG_init函数的代码可以看出,它利用了planner_hook(优化器的函数钩子,实际上是全局变量,存放函数地址,可以被插件更改,换成插件自定义函数的地址),用pg_hint_plan_planner取代了原来的优化器逻辑。这样,PG在处理一个SQL时,将调用pg_hint_plan_planner来做优化。而pg_hint_plan_planner会调用get_hints_from_comment,来读取Hint,并调用create_hintstate进行语法分析。这里要说明的是,create_hintstate遇到一张表上的多个同类型Hint(包括重复的Hint),只保留最后一个,前面的会忽略。

  另外,还有两个函数钩子被利用:get_relation_info_hook和join_search_hook。这两个钩子分别被修改指向了pg_hint_plan_get_relation_info和pg_hint_plan_join_search。前者是在优化器处理基本表(非视图、非函数的表)获取表信息时被调用,调用栈如下所示:

  query_planner -> add_base_rels_to_query -> build_simple_rel -> get_relation_info -> get_relation_info_hook(即pg_hint_plan_get_relation_info)

  这个pg_hint_plan_get_relation_info做了什么呢?仔细看会惊讶的发现,它是用来删除索引的!对,它在优化器获取表的基本信息后被调用,然后其从基本信息删除了那些在Hint中未使用的索引。比如,t1上有两个索引t1_i_a和t1_i_b,如果指定了IndexScan(t1 t_i_b)这个Hint,那么t1_i_a的索引信息在这里被删除,这样在后续的优化中,就永远不会考虑t1_i_a这个索引了!

  再看pg_hint_plan_join_search,其被调用的位置如下所示:

  query_planner -> make_one_rel -> make_rel_from_joinlist ->join_search_hook(即pg_hint_plan_join_search)

  可见,它是在为一个SQL语句生成连接结果时被调用,其输入为待连接的表,输出为连接后生成的表及其最优的查询计划。它主要做了两件事:

  1)调用pg_hint_plan_standard_join_search生成连接的计划。这里是应用连接方法和连接顺序Hint的地方。要想改变连接方法或顺序,需要进一步修改优化器的整个逻辑,但优化器没那么多的预定义钩子可用了,采用函数钩子的方法不可行。于是,插件便“自备”了优化器的主流程代码(其实是从同版本的PG里面拷贝出来的),见插件代码中的core.c和make_join_rel.c两个文件。里面很多地方是被插件修改过的。其中核心的是修改对add_paths_to_joinrel的调用,使优化器实际调用add_paths_to_joinrel_wrapper。这个函数是用于为输入的两张表(可能是连接生成的中间表),生成一个连接计划。可以看到add_paths_to_joinrel_wrapper会先去查找有没有对应的Hint,如果有就直接利用,并舍弃掉不符合Hint的连接方法和顺序(这是连接顺序Hint其作用的地方)。

  2)调用rebuild_scan_path重新生成基本表的访问路径。为什么要重新生成呢?因为在基本表的访问计划生成阶段,扫描类的Hint并未实际起作用(只是对索引做过删除处理)。例如,即使指定了IndexScan(t1 t1_i_a),但外部的GUC变量enable_indexscan被设置为了off,在这里也只会看到一个表扫描(SeqScan)的查询计划。因此这里需要重新设置好GUC变量(例如如果遇到IndexScan Hint,需要把GUC变量enable_indexscan重置为on),再做一遍访问计划。由于基本表一般数量较少,访问计划也只需再生成一次,所以此步开销是可接受的。

  可以看到,这个插件的实现并不复杂,它巧妙利用了优化器优化流程中的关键点,来应用Hint,达到固定查询计划的目的。

  性能测试

  从内核实现可以看出,指定Hint后会带来如下开销:基本表的访问路径要生成两次;每次连接两个中间表时,要检查是否有对应的Hint;很多地方需要反复更新GUC变量来影响计划生成。当然,由于直接指定了表的连接方法、顺序等,减少了生成的中间计划,这一点又节省了很多开销。所以,对使用Hint后的编译时间是否比原来长,不能一概而论。下面,我们对Hint造成的编译开销做一下粗略测试。测试用例如下:

  \timing

  DO $$DECLARE count int;

  BEGIN

  count := 1;

  LOOP

  count := count + 1;

  begin

  EXECUTE 'explain select * from t1,t2,t3,t4,t5,t6,t7,t8 where t1.a=t2.b and t2.a=t3.b and t3.a=t4.b and t4.a=t5.b and t5.a=t6.b and t6.a=t7.b and t7.a=t8.b';

  IF count > 10000 THEN

  EXIT;

  END IF;

  exception when others then

  end;

  END LOOP;

  END$$;

  DO $$DECLARE count int;

  BEGIN

  count := 1;

  LOOP

  count := count + 1;

  begin

  EXECUTE '/*+ IndexScan(t1 t1_i_a) IndexScan(t2 t2_i_a) IndexScan(t3 t3_i_a) IndexScan(t4 t4_i_a) IndexScan(t5 t5_i_a) IndexScan(t6 t6_i_a) IndexScan(t7 t7_i_a) IndexScan(t8 t8_i_a) Leading ( t1 t2 t3 t4 t5 t6 t7 t8 ) */ explain select * from t1,t2,t3,t4,t5,t6,t7,t8 where t1.a=t2.b and t2.a=t3.b and t3.a=t4.b and t4.a=t5.b and t5.a=t6.b and t6.a=t7.b and t7.a=t8.b' ;

  IF count > 10000 THEN

  EXIT;

  END IF;

  exception when others then

  end;

  END LOOP;

  END$$;

  这里我们使用了8张表,每张表都只有a,b两个int字段。用两个DO语句,每个都执行同一SQL语句10000次。一个DO语句是不带Hint的,另一个带了较复杂的Hint。测试结果,不带Hint的执行耗时17秒左右,带Hint的14秒左右。即带Hint的反而编译时间更短(注意这里只执行了explain,未真正执行SQL语句)。