码农必备SQL调优（下）
目录 1. Plan Hint调优概述 2. Join顺序的Hint 3. Join方式的Hint 4. 行数的Hint 5. Scan方式的Hint 6. 子链接块名的hint 7. Hint的错误、冲突及告警 8. 优化器GUC参数的Hint 9. Custom Plan和Generic Plan选择的Hint 10. 指定子查询不展开的Hint 11. 指定不使用全局计划缓存的Hint 12. 同层参数化路径的Hint 1. Plan Hint调优概述 Plan Hint为用户提供了直接影响执行计划生成的手段，用户可以通过指定join顺序、join、scan方法、指定结果行数等多个手段来进行执行计划的调优，以提升查询的性能。 功能描述 Plan Hint支持在SELECT关键字后通过如下形式指定： /*+ <plan hint>*/ 可以同时指定多个hint，之间使用空格分隔。hint只能hint当前层的计划，对于子查询计划的hint，需要在子查询的select关键字后指定hint。 例如： select /*+ <plan_hint1> <plan_hint2> */ * from t1, (select /*+ <plan_hint3> */ from t2) where 1=1; 其中，为外层查询的hint，为内层子查询的hint。 须知： 如果在视图定义（CREATE VIEW）时指定hint，则在该视图每次被应用时会使用该hint。 当使用random plan功能（参数plan_mode_seed不为0）时，查询指定的plan hint不会被使用。 支持范围 当前版本Plan Hint支持的范围如下，后续版本会进行增强。 指定Join顺序的Hint - leading hint 指定Join方式的Hint，仅支持除semi/anti join、unique plan之外的常用hint。 指定结果集行数的Hint 指定Scan方式的Hint，仅支持常用的tablescan、indexscan和indexonlyscan的hint。 指定子链接块名的Hint 注意事项 不支持Agg、Sort、Setop和Subplan的hint。 示例 本章节使用同一个语句进行示例，便于Plan Hint支持的各方法作对比，示例语句及不带hint的原计划如下所示： create table store ( s_store_sk integer not null, s_store_id char(16) not null, s_rec_start_date date , s_rec_end_date date , s_closed_date_sk integer , s_store_name varchar(50) , s_number_employees integer , s_floor_space integer , s_hours char(20) , s_manager varchar(40) , s_market_id integer , s_geography_class varchar(100) , s_market_desc varchar(100) , s_market_manager varchar(40) , s_division_id integer , s_division_name varchar(50) , s_company_id integer , s_company_name varchar(50) , s_street_number varchar(10) , s_street_name varchar(60) , s_street_type char(15) , s_suite_number char(10) , s_city varchar(60) , s_county varchar(30) , s_state char(2) , s_zip char(10) , s_country varchar(20) , s_gmt_offset decimal(5,2) , s_tax_precentage decimal(5,2) , primary key (s_store_sk) ); create table store_sales ( ss_sold_date_sk integer , ss_sold_time_sk integer , ss_item_sk integer not null, ss_customer_sk integer , ss_cdemo_sk integer , ss_hdemo_sk integer , ss_addr_sk integer , ss_store_sk integer , ss_promo_sk integer , ss_ticket_number integer not null, ss_quantity integer , ss_wholesale_cost decimal(7,2) , ss_list_price decimal(7,2) , ss_sales_price decimal(7,2) , ss_ext_discount_amt decimal(7,2) , ss_ext_sales_price decimal(7,2) , ss_ext_wholesale_cost decimal(7,2) , ss_ext_list_price decimal(7,2) , ss_ext_tax decimal(7,2) , ss_coupon_amt decimal(7,2) , ss_net_paid decimal(7,2) , ss_net_paid_inc_tax decimal(7,2) , ss_net_profit decimal(7,2) , primary key (ss_item_sk, ss_ticket_number) ); create table store_returns ( sr_returned_date_sk integer , sr_return_time_sk integer , sr_item_sk integer not null, sr_customer_sk integer , sr_cdemo_sk integer , sr_hdemo_sk integer , sr_addr_sk integer , sr_store_sk integer , sr_reason_sk integer , sr_ticket_number integer not null, sr_return_quantity integer , sr_return_amt decimal(7,2) , sr_return_tax decimal(7,2) , sr_return_amt_inc_tax decimal(7,2) , sr_fee decimal(7,2) , sr_return_ship_cost decimal(7,2) , sr_refunded_cash decimal(7,2) , sr_reversed_charge decimal(7,2) , sr_store_credit decimal(7,2) , sr_net_loss decimal(7,2) , primary key (sr_item_sk, sr_ticket_number) ); create table customer ( c_customer_sk integer not null, c_customer_id char(16) not null, c_current_cdemo_sk integer , c_current_hdemo_sk integer , c_current_addr_sk integer , c_first_shipto_date_sk integer , c_first_sales_date_sk integer , c_salutation char(10) , c_first_name char(20) , c_last_name char(30) , c_preferred_cust_flag char(1) , c_birth_day integer , c_birth_month integer , c_birth_year integer , c_birth_country varchar(20) , c_login char(13) , c_email_address char(50) , c_last_review_date char(10) , primary key (c_customer_sk) ); create table promotion ( p_promo_sk integer not null, p_promo_id char(16) not null, p_start_date_sk integer , p_end_date_sk integer , p_item_sk integer , p_cost decimal(15,2) , p_response_target integer , p_promo_name char(50) , p_channel_dmail char(1) , p_channel_email char(1) , p_channel_catalog char(1) , p_channel_tv char(1) , p_channel_radio char(1) , p_channel_press char(1) , p_channel_event char(1) , p_channel_demo char(1) , p_channel_details varchar(100) , p_purpose char(15) , p_discount_active char(1) , primary key (p_promo_sk) ); create table customer_address ( ca_address_sk integer not null, ca_address_id char(16) not null, ca_street_number char(10) , ca_street_name varchar(60) , ca_street_type char(15) , ca_suite_number char(10) , ca_city varchar(60) , ca_county varchar(30) , ca_state char(2) , ca_zip char(10) , ca_country varchar(20) , ca_gmt_offset decimal(5,2) , ca_location_type char(20) , primary key (ca_address_sk) ); create table item ( i_item_sk integer not null, i_item_id char(16) not null, i_rec_start_date date , i_rec_end_date date , i_item_desc varchar(200) , i_current_price decimal(7,2) , i_wholesale_cost decimal(7,2) , i_brand_id integer , i_brand char(50) , i_class_id integer , i_class char(50) , i_category_id integer , i_category char(50) , i_manufact_id integer , i_manufact char(50) , i_size char(20) , i_formulation char(20) , i_color char(20) , i_units char(10) , i_container char(10) , i_manager_id integer , i_product_name char(50) , primary key (i_item_sk) ); explain select i_product_name product_name ,i_item_sk item_sk ,s_store_name store_name ,s_zip store_zip ,ad2.ca_street_number c_street_number ,ad2.ca_street_name c_street_name ,ad2.ca_city c_city ,ad2.ca_zip c_zip ,count(*) cnt ,sum(ss_wholesale_cost) s1 ,sum(ss_list_price) s2 ,sum(ss_coupon_amt) s3 FROM store_sales ,store_returns ,store ,customer ,promotion ,customer_address ad2 ,item WHERE ss_store_sk = s_store_sk AND ss_customer_sk = c_customer_sk AND ss_item_sk = i_item_sk and ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number and c_current_addr_sk = ad2.ca_address_sk and ss_promo_sk = p_promo_sk and i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and i_current_price between 35 and 35 + 10 and i_current_price between 35 + 1 and 35 + 15 group by i_product_name ,i_item_sk ,s_store_name ,s_zip ,ad2.ca_street_number ,ad2.ca_street_name ,ad2.ca_city ,ad2.ca_zip ; 2. Join顺序的Hint 功能描述 指明join的顺序，包括内外表顺序。 语法格式 仅指定join顺序，不指定内外表顺序。 leading(join_table_list) 同时指定join顺序和内外表顺序，内外表顺序仅在最外层生效。 leading((join_table_list)) 参数说明 join_table_list为表示表join顺序的hint字符串，可以包含当前层的任意个表（别名），或对于子查询提升的场景，也可以包含子查询的hint别名，同时任意表可以使用括号指定优先级，表之间使用空格分隔。 须知： 表只能用单个字符串表示，不能带schema。 表如果存在别名，需要优先使用别名来表示该表。 join table list中指定的表需要满足以下要求，否则会报语义错误。 list中的表必须在当前层或提升的子查询中存在。 list中的表在当前层或提升的子查询中必须是唯一的。如果不唯一，需要使用不同的别名进行区分。 同一个表只能在list里出现一次。 如果表存在别名，则list中的表需要使用别名。 例如： leading(t1 t2 t3 t4 t5)表示：t1、t2、t3、t4、t5先join，五表join顺序及内外表不限。 leading((t1 t2 t3 t4 t5))表示：t1和t2先join，t2做内表；再和t3 join，t3做内表；再和t4 join，t4做内表；再和t5 join，t5做内表。 leading(t1 (t2 t3 t4) t5)表示：t2、t3、t4先join，内外表不限；再和t1、t5 join，内外表不限。 leading((t1 (t2 t3 t4) t5))表示：t2、t3、t4先join，内外表不限；在最外层，t1再和t2、t3、t4的join表join，t1为外表，再和t5 join，t5为内表。 leading((t1 (t2 t3) t4 t5)) leading((t3 t2))表示：t2、t3先join，t2做内表；然后再和t1 join，t2、t3的join表做内表；然后再依次跟t4、t5做join，t4、t5做内表。 示例 对sh示例对中原语句使用如下hint: explain select /*+ leading((((((store_sales store) promotion) item) customer) ad2) store_returns) leading((store store_sales))*/ i_product_name product_name ... 该hint表示：表之间的join关系是：store_sales和store先join，store_sales做内表，然后依次跟promotion, item, customer, ad2, store_returns做join。生成计划如下所示： 图中计划顶端warning的提示详见Hint的错误、冲突及告警的说明。 3. Join方式的Hint 功能描述 指明Join使用的方法，可以为Nested Loop、Hash Join和Merge Join。 语法格式 [no] nestloop|hashjoin|mergejoin(table_list) 参数说明 no表示hint的join方式不使用。 table_list为表示hint表集合的字符串，该字符串中的表与join_table_list相同，只是中间不允许出现括号指定join的优先级。 例如： no nestloop(t1 t2 t3)表示：生成t1、t2、t3三表连接计划时，不使用nestloop。三表连接计划可能是t2 t3先join，再跟t1 join，或t1 t2先join，再跟t3 join。此hint只hint最后一次join的join方式，对于两表连接的方法不hint。如果需要，可以单独指定，例如：任意表均不允许nestloop连接，且希望t2 t3先join，则增加hint：no nestloop(t2 t3)。 示例 对示例中原语句使用如下hint: explain select /*+ nestloop(store_sales store_returns item) */ i_product_name product_name ... 该hint表示：生成store_sales、store_returns和item三表的结果集时，最后的两表关联使用nestloop。生成计划如下所示： 4. 行数的Hint 功能描述 指明中间结果集的大小，支持绝对值和相对值的hint。 语法格式 rows(table_list #|+|-|* const) 参数说明 #、+、-、*，进行行数估算hint的四种操作符号。#表示直接使用后面的行数进行hint。+、-、*表示对原来估算的行数进行加、减、乘操作，运算后的行数最小值为1行。table_list为hint对应的单表或多表join结果集，与Join方式的Hint中table_list相同。 const可以是任意非负数，支持科学计数法。 例如： rows(t1 #5)表示：指定t1表的结果集为5行。 rows(t1 t2 t3 *1000)表示：指定t1、 t2、t3 join完的结果集的行数乘以1000。 建议 推荐使用两个表*的hint。对于两个表的采用*操作符的hint，只要两个表出现在join的两端，都会触发hint。例如：设置hint为rows(t1 t2 * 3)，对于(t1 t3 t4)和(t2 t5 t6)join时，由于t1和t2出现在join的两端，所以其join的结果集也会应用该hint规则乘以3。 rows hint支持在单表、多表、function table及subquery scan table的结果集上指定hint。 示例 对示例中原语句使用如下hint： explain select /*+ rows(store_sales store_returns *50) */ i_product_name product_name ... 该hint表示：store_sales、store_returns关联的结果集估算行数在原估算行数基础上乘以50。生成计划如下所示： 5. Scan方式的Hint 功能描述 指明scan使用的方法，可以是tablescan、indexscan和indexonlyscan。 语法格式 [no] tablescan|indexscan|indexonlyscan(table [index]) 参数说明 no表示hint的scan方式不使用。 table表示hint指定的表，只能指定一个表，如果表存在别名应优先使用别名进行hint。 index表示使用indexscan或indexonlyscan的hint时，指定的索引名称，当前只能指定一个。 说明： 对于indexscan或indexonlyscan，只有hint的索引属于hint的表时，才能使用该hint。 scan hint支持在行列存表、obs表、子查询表上指定。 示例 为了hint使用索引扫描，需要首先在表item的i_item_sk列上创建索引，名称为i。 create index i on item(i_item_sk); 对示例中原语句使用如下hint： explain select /*+ indexscan(item i) */ i_product_name product_name ... 该hint表示：item表使用索引i进行扫描。生成计划如下所示： 6. 子链接块名的hint 功能描述 指明子链接块的名称。 语法格式 blockname (table) 参数说明 table表示为该子链接块hint的别名的名称。 说明： -blockname hint仅在对应的子链接块没有提升时才会被上层查询使用。目前支持的子链接提升包括IN子链接提升、EXISTS子链接提升和包含Agg等值相关子链接提升。该hint通常会和前面章节提到的hint联合使用。 - 对于FROM关键字后的子查询，则需要使用子查询的别名进行hint，blockname hint不会被用到。 - 如果子链接中含有多个表，则提升后这些表可与外层表以任意优化顺序连接，hint也不会被用到。 示例 explain select /*+nestloop(store_sales tt) */ * from store_sales where ss_item_sk in (select /*+blockname(tt)*/ i_item_sk from item group by 1); 该hint表示：子链接的别名为tt，提升后与上层的store_sales表关联时使用nestloop。生成计划如下所示： 7. Hint的错误、冲突及告警 Plan Hint的结果会体现在计划的变化上，可以通过explain来查看变化。 Hint中的错误不会影响语句的执行，只是不能生效，该错误会根据语句类型以不同方式提示用户。对于explain语句，hint的错误会以warning形式显示在界面上，对于非explain语句，会以debug1级别日志显示在日志中，关键字为PLANHINT。 hint的错误分为以下类型： 语法错误 语法规则树归约失败，会报错，指出出错的位置。 例如：hint关键字错误，leading hint或join hint指定2个表以下，其它hint未指定表等。一旦发现语法错误，则立即终止hint的解析，所以此时只有错误前面的解析完的hint有效。 例如： leading((t1 t2)) nestloop(t1) rows(t1 t2 #10) nestloop(t1)存在语法错误，则终止解析，可用hint只有之前解析的leading((t1 t2))。 语义错误 表不存在，存在多个，或在leading或join中出现多次，均会报语义错误。 scanhint中的index不存在，会报语义错误。 另外，如果子查询提升后，同一层出现多个名称相同的表，且其中某个表需要被hint，hint会存在歧义，无法使用，需要为相同表增加别名规避。 hint重复或冲突 如果存在hint重复或冲突，只有第一个hint生效，其它hint均会失效，会给出提示。 hint重复是指，hint的方法及表名均相同。例如：nestloop(t1 t2) nestloop(t1 t2)。 hint冲突是指，table list一样的hint，存在不一样的hint，hint的冲突仅对于每一类hint方法检测冲突。 例如：nestloop (t1 t2) hashjoin (t1 t2)，则后面与前面冲突，此时hashjoin的hint失效。注意：nestloop(t1 t2)和no mergejoin(t1 t2)不冲突。 须知： leading hint中的多个表会进行拆解。例如：leading ((t1 t2 t3))会拆解成：leading((t1 t2)) leading(((t1 t2) t3))，此时如果存在leading((t2 t1))，则两者冲突，后面的会被丢弃。（例外：指定内外表的hint若与不指定内外表的hint重复，则始终丢弃不指定内外表的hint。） 子链接提升后hint失效 子链接提升后的hint失效，会给出提示。通常出现在子链接中存在多个表连接的场景。提升后，子链接中的多个表不再作为一个整体出现在join中。 列类型不支持重分布 对于skew hint来说，目的是为了进行重分布时的调优，所以当hint列的类型不支持重分布时，hint将无效。 hint未被使用 非等值join使用hashjoin hint或mergejoin hint。 不包含索引的表使用indexscan hint或indexonlyscan hint。 通常只有在索引列上使用过滤条件才会生成相应的索引路径，全表扫描将不会使用索引，因此使用indexscan hint或indexonlyscan hint将不会使用。 indexonlyscan只有输出列仅包含索引列才会使用，否则指定时hint不会被使用。 多个表存在等值连接时，仅尝试有等值连接条件的表的连接，此时没有关联条件的表之间的路径将不会生成，所以指定相应的leading、join、rows hint将不使用，例如：t1 t2 t3表join，t1和t2，t2和t3有等值连接条件，则t1和t3不会优先连接，leading(t1 t3)不会被使用。 生成stream计划时，如果表的分布列与join列相同，则不会生成redistribute的计划；如果不同，且另一表分布列与join列相同，只能生成redistribute的计划，不会生成broadcast的计划，指定相应的hint则不会被使用。 如果子链接未被提升，则blockname hint不会被使用。 对于skew hint，hint未被使用可能由于： 计划中不需要进行重分布。 hint指定的列为包含分布键。 hint指定倾斜信息有误或不完整，如对于join优化未指定值。 倾斜优化的GUC参数处于关闭状态。 8. 优化器GUC参数的Hint 功能描述 设置本次查询执行内生效的查询优化相关GUC参数。hint的推荐使用场景可以参考各guc参数的说明，此处不作赘述。 语法格式 set(param value) 参数说明 param表示参数名。 value表示参数的取值。 目前支持使用Hint设置生效的参数有 布尔类： enable_bitmapscan、enable_hashagg、enable_hashjoin、enable_indexscan、enable_indexonlyscan、enable_material、enable_mergejoin、enable_nestloop、enable_index_nestloop、enable_seqscan、enable_sort、enable_tidscan 整形类： query_dop 浮点类： cost_weight_index、default_limit_rows、seq_page_cost、random_page_cost、cpu_tuple_cost、cpu_index_tuple_cost、cpu_operator_cost、effective_cache_size 枚举类型： try_vector_engine_strategy 说明： - 设置不在白名单中的参数，参数取值不合法，或hint语法错误时，不会影响查询执行的正确性。使用explain(verbose on)执行可以看到hint解析错误的报错提示。 - GUC参数的hint只在最外层查询生效——子查询内的GUC参数hint不生效。 - 视图定义内的GUC参数hint不生效。 - CREATE TABLE … AS … 查询最外层的GUC参数hint可以生效。 9. Custom Plan和Generic Plan选择的Hint 功能描述 对于以PBE方式执行的查询语句和DML语句，优化器会基于规则、代价、参数等因素选择生成Custom Plan或Generic Plan执行。用户可以通过use_cplan/use_gplan的hint指定使用哪种计划执行方式。 语法格式 指定使用Custom Plan： use_cplan 指定使用Generic Plan： use_gplan 说明：- 对于非PBE方式执行的SQL语句，设置本hint不会影响执行方式。 - 本Hint的优先级仅高于基于代价的选择和plan_cache_mode参数，即plan_cache_mode无法强制选择执行方式的语句本hint也无法生效。 示例 强制使用Custom Plan create table t (a int, b int, c int); prepare p as select /*+ use_cplan */ * from t where a = $1; explain execute p(1); 可以看到过滤条件为入参的实际值，即此计划为Custom Plan。 强制使用Generic Plan deallocate p; prepare p as select /*+ use_gplan */ * from t where a = $1; explain execute p(1); 可以看到过滤条件为待填充的入参，即此计划为Generic Plan。 10. 指定子查询不展开的Hint 功能描述 数据库在对查询进行逻辑优化时通常会将可以提升的子查询提升到上层来避免嵌套执行，但对于某些本身选择率较低且可以使用索引过滤访问页面的子查询，嵌套执行不会导致性能下降过多，而提升之后扩大了查询路径的搜索范围，可能导致性能变差。对于此类情况，可以使用no_expand Hint进行调试。大多数情况下不建议使用此hint。 语法格式 no_expand 示例 正常的查询执行 explain select * from t1 where t1.a in (select t2.a from t2); 加入no_expand explain select * from t1 where t1.a in (select /*+ no_expand*/ t2.a from t2); 11. 指定不使用全局计划缓存的Hint 功能描述 全局计划缓存打开时，可以通过no_gpc Hint来强制单个查询语句不在全局共享计划缓存，只保留会话生命周期的计划缓存。 语法格式 no_gpc 说明：本参数仅在enable_global_plancache=on时对PBE执行的语句生效。 示例 dbe_perf.global_plancache_status视图中无结果即没有计划被全局缓存。 12. 同层参数化路径的Hint 功能描述 通过predpush_same_level Hint来指定同层表或物化视图之间参数化路径生成。 语法格式 predpush_same_level(src, dest) predpush_same_level(src1 src2 ..., dest) 说明：本参数仅在rewrite_rule中的predpushforce选项打开时生效。 示例 准备参数和表及索引： openGauss=# set rewrite_rule = 'predpushforce'; SET openGauss=# create table t1(a int, b int); CREATE TABLE openGauss=# create table t2(a int, b int); CREATE TABLE openGauss=# create index idx1 on t1(a); CREATE INDEX openGauss=# create index idx2 on t2(a); CREATE INDEX 执行语句查看计划： openGauss=# explain select * from t1, t2 where t1.a = t2.a; QUERY PLAN ------------------------------------------------------------------ Hash Join (cost=27.50..56.25 rows=1000 width=16) Hash Cond: (t1.a = t2.a) -> Seq Scan on t1 (cost=0.00..15.00 rows=1000 width=8) -> Hash (cost=15.00..15.00 rows=1000 width=8) -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) (5 rows) 可以看到t1.a = t2.a条件过滤在Join上面，此时可以通过predpush_same_level(t1, t2)将条件下推至t2的扫描算子上： openGauss=# explain select /*+predpush_same_level(t1, t2)*/ * from t1, t2 where t1.a = t2.a; QUERY PLAN --------------------------------------------------------------------- Nested Loop (cost=0.00..335.00 rows=1000 width=16) -> Seq Scan on t1 (cost=0.00..15.00 rows=1000 width=8) -> Index Scan using idx2 on t2 (cost=0.00..0.31 rows=1 width=8) Index Cond: (a = t1.a) (4 rows) 须知：- predpush_same_level可以指定多个src，但是所有的src必须在同一个条件中。 - 如果指定的src和dest条件不存在，或该条件不符合参数化路径要求，则本hint不生效。
