做视频网站的挣钱吗萧山seo
熟悉PostgreSQL的都知道有一个pg_hint_plan插件，可以通过该插件让用户自己指定生成定制的执行计划，而不是完全让优化器自己生成。这样的好处是让优化器不能生成最优的执行计划时，用户可以自己指定生成最优的执行计划，从而使性能大幅提高。具体的就是可以使用pg_hint_plan插件以特殊的注释形式来提示SQL语句应该如何执行。 在LightDB中，lt_hint_plan功能用法与之类似。
用法示例：
下面我们以实际的例子来进行说明，lt_hint_plan是如何使用的。
预备工作：造表，造数据，通过ltbench -i postgres，造表造数据。
-- 表ltbench_accounts
postgres@postgres=# \d ltbench_accounts;Table "public.ltbench_accounts"Column | Type | Collation | Nullable | Default
----------+----------------+-----------+----------+---------aid | integer | | not null | bid | integer | | | abalance | integer | | | filler | character(184) | | |
Indexes:"ltbench_accounts_pkey" PRIMARY KEY, btree (aid)-- 表ltbench_branches
postgres@postgres=# \d ltbench_branches;Table "public.ltbench_branches"Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------bid | integer | | not null | bbalance | integer | | | filler | character(88) | | |
Indexes:"ltbench_branches_pkey" PRIMARY KEY, btree (bid)-- 表ltbench_history
postgres@postgres=# \d ltbench_history Table "public.ltbench_history"Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------tid | integer | | | bid | integer | | | aid | integer | | | delta | integer | | | mtime | timestamp without time zone | | | filler | character(22) | | | --表ltbench_tellers
postgres@postgres=# \d ltbench_tellers;Table "public.ltbench_tellers"Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------tid | integer | | not null | bid | integer | | | tbalance | integer | | | filler | character(84) | | |
Indexes:"ltbench_tellers_pkey" PRIMARY KEY, btree (tid)
造好数据表后，我们看一下lt_hint_plan用法示例，使用非常简单，我们只要在SQL中加入提示注释，以字符/*+开头，以字符*/结束。提示由提示的名称和参数组成。
--为使用lt_hint_plan，生成的执行计划
postgres@postgres=# explain select * from ltbench_branches b join ltbench_accounts a on b.bid=a.bid order by a.aid;QUERY PLAN
----------------------------------------------------------------------------------------------------------------Nested Loop (cost=0.29..6135.31 rows=100000 width=564)Join Filter: (b.bid = a.bid)-> Index Scan using ltbench_accounts_pkey on ltbench_accounts a (cost=0.29..4634.29 rows=100000 width=200)-> Materialize (cost=0.00..1.01 rows=1 width=364)-> Seq Scan on ltbench_branches b (cost=0.00..1.01 rows=1 width=364)
(5 rows)-- 指定Join的方式为HashJoin
postgres@postgres=# /*+ HashJoin(a b)*/ explain select * from ltbench_branches b join ltbench_accounts a on b.bid=a.bid order by a.aid;QUERY PLAN
----------------------------------------------------------------------------------------Sort (cost=27992.84..28242.84 rows=100000 width=564)Sort Key: a.aid-> Hash Join (cost=1.02..5234.02 rows=100000 width=564)Hash Cond: (a.bid = b.bid)-> Seq Scan on ltbench_accounts a (cost=0.00..3858.00 rows=100000 width=200)-> Hash (cost=1.01..1.01 rows=1 width=364)-> Seq Scan on ltbench_branches b (cost=0.00..1.01 rows=1 width=364)
(7 rows)
提示表
虽然可以使用注释提示的方式对SQL进行提示，但是当SQL语句不可编辑时，这种提示方式就很不方便。对于这种情况，可以将提示放在一张特殊的表hint_plan.hints中
postgres@postgres=# \d+ hint_plan.hintsTable "hint_plan.hints"Column | Type | Collation | Nullable | Default | Storage | Stats target | D
escription
-------------------+---------+-----------+----------+---------------------------------------------+----------+--------------+--
-----------id | integer | | not null | nextval('hint_plan.hints_id_seq'::regclass) | plain | | norm_query_string | text | | not null | | extended | | application_name | text | | not null | | extended | | hints | text | | not null | | extended | | -- 提示语句
Indexes:"hints_pkey" PRIMARY KEY, btree (id)"hints_norm_and_app" UNIQUE, btree (norm_query_string, application_name)
Access method: heap
其中，norm_query_string表示：与要提示的查询匹配的模式，查询中的常量必须替换为?。application_name表示应用会话的名称，置空表示任意应用。hints表示提示语句，不需要注释标记。
用法示例：
insert into hint_plan.hints(norm_query_string, application_name,hints) values ('explain (costs false) select * from t1 where t1.id = ?;','','SeqScan(t1)');
提示的类型
根据提示短语影响执行计划的方式，有如下几类：
- 扫描方法提示
扫描方法提示对目标表强制执行特定的扫描方法。比如：SeqScan,IndexScan等。
-- 指定表扫描方式
postgres@postgres=# /*+ SeqScan(ltbench_accounts) IndexScan(ltbench_branches)*/ explain select * from ltbench_accounts join ltbench_branches on ltbench_accounts.aid = ltbench_branches.bid;QUERY PLAN
------------------------------------------------------------------------------------------------------------Hash Join (cost=2.15..4122.66 rows=1 width=564)Hash Cond: (ltbench_accounts.aid = ltbench_branches.bid)-> Seq Scan on ltbench_accounts (cost=0.00..3858.00 rows=100000 width=200)-> Hash (cost=2.14..2.14 rows=1 width=364)-> Index Scan using ltbench_branches_pkey on ltbench_branches (cost=0.12..2.14 rows=1 width=364)
(5 rows)
- 连接方法提示
连接方法提示强制指定Join的方法
postgres@postgres=# /*+ NestLoop(ltbench_accounts ltbench_branches)*/ explain (costs false) select * from ltbench_accounts join ltbench_branches on ltbench_accounts.aid = ltbench_branches.bid;QUERY PLAN
------------------------------------------------------------------Nested Loop-> Seq Scan on ltbench_branches-> Index Scan using ltbench_accounts_pkey on ltbench_accountsIndex Cond: (aid = ltbench_branches.bid)
(4 rows)
- 连接顺序提示
连接顺序提示两个或者多个表的连接顺序。
postgres@postgres=# /*+ Leading(ltbench_accounts ltbench_branches ltbench_history) */ explain (costs false) select * from ltbench_accounts join ltbench_branches on ltbench_accounts.aid = ltbench_branches.bid join ltbench_history on ltbench_branches.bid = ltbench_history.bid;QUERY PLAN
------------------------------------------------------------------Nested LoopJoin Filter: (ltbench_accounts.aid = ltbench_history.bid)-> Hash JoinHash Cond: (ltbench_accounts.aid = ltbench_branches.bid)-> Seq Scan on ltbench_accounts-> Hash-> Seq Scan on ltbench_branches-> Seq Scan on ltbench_history
(8 rows)postgres@postgres=# /*+ Leading(ltbench_branches ltbench_history ltbench_accounts) */ explain (costs false) select * from ltbench_accounts join ltbench_branches on ltbench_accounts.aid = ltbench_branches.bid join ltbench_history on ltbench_branches.bid = ltbench_history.bid;QUERY PLAN
-------------------------------------------------------------------Nested LoopJoin Filter: (ltbench_branches.bid = ltbench_accounts.aid)-> Nested LoopJoin Filter: (ltbench_branches.bid = ltbench_history.bid)-> Seq Scan on ltbench_branches-> Seq Scan on ltbench_history-> Index Scan using ltbench_accounts_pkey on ltbench_accountsIndex Cond: (aid = ltbench_history.bid)
(8 rows)
- 行号纠正提示：
行号纠正提示会纠正由于查询优化器而导致的行号错误。
/*+ Rows(a b #10)*/ SELECT ...
- 并行执行提示
并行提示会指定并行的执行计划。
postgres@postgres=# /*+ Parallel(ltbench_accounts 3 hard) Parallel(ltbench_branches 3 hard) */ explain (costs false) select * from ltbench_accounts join ltbench_branches on (ltbench_accounts.aid = ltbench_branches.bid);QUERY PLAN
------------------------------------------------------------------Nested Loop-> GatherWorkers Planned: 3-> Parallel Seq Scan on ltbench_branches-> Index Scan using ltbench_accounts_pkey on ltbench_accountsIndex Cond: (aid = ltbench_branches.bid)
(6 rows)
- 设置临时GUC参数
在计划的时候临时改变GUC参数。执行计划中的GUC参数会有预期的效果，除非提示语句与其他计划冲突。同样的GUC参数设置以最后一次为准。
postgres@postgres=# /*+ Set(random_page_cost 4.0)*/ explain select * from ltbench_accounts;QUERY PLAN
--------------------------------------------------------------------------Seq Scan on ltbench_accounts (cost=0.00..3858.00 rows=100000 width=200)
(1 row)
更多可参考官方文档。
参考文档：
pg_hint_plan
唯我所控，PostgreSQL执行计划控制工具PG_HINT_PLAN