您现在的位置是:首页 > 博客网站制作 > 个人博客搭建个人博客搭建

postgresql关于like%xxx%的优化操作

杨青青2022-02-20【个人博客搭建】人已围观

简介任何一个关系型数据库关于模糊匹配(like)的优化都是一件痛苦的事,相对而言,诸如like'abc%'之类的还好一点,可以通过创建索引来优化,但对于like'c%'之类的,真的就没有办法了。这里

任何一个关系型数据库关于模糊匹配(like)的优化都是一件痛苦的事,相对而言,诸如like'abc%'之类的还好一点,可以通过创建索引来优化,但对于like'c%'之类的,真的就没有办法了。

这里介绍一种postgresql关于like'c%'的优化方法,是基于全文检索的特性来实现的。

测试数据准备(环境centos6.5+postgresql9.6.1)。

postgres=#createtablets(idint,nametext);
CREATETABLE
postgres=#\dts
Table"public.ts"
Column|Type|Modifiers
--------+---------+-----------
id|integer|
name|text|
postgres=#insertintotsselectn,n||'_pjy'fromgenerate_series(1,2000)n;
INSERT02000
postgres=#insertintotsselectn,n||'_mdh'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_lmm'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_syf'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_wbd'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_hhh'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_sjw'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_jjs'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_ymd'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_biu'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#insertintotsselectn,n||'_dfl'fromgenerate_series(1,2000000)n;
INSERT02000000
postgres=#selectcount(*)fromts;
count
----------
20002000
(1row)

开始测试:

postgres=#explainanalyzeselect*fromtswherenamelike'%pjy%';
QUERYPLAN
-----------------------------------------------------------------------------------------------------------
SeqScanonts(cost=0.00..358144.05rows=2000width=15)(actualtime=0.006..1877.087rows=2000loops=1)
Filter:(name~~'%pjy%'::text)
RowsRemovedbyFilter:20000000
Planningtime:0.031ms
Executiontime:1877.178ms
(5rows)

关键一步:

postgres=#createindexidx_nameontsusinggin(to_tsvector('english',name));
CREATEINDEX
postgres=#vacuumanalyzets;
VACUUM
postgres=#\dts
Table"public.ts"
Column|Type|Modifiers
--------+---------+-----------
id|integer|
name|text|
Indexes:
"idx_name"gin(to_tsvector('english'::regconfig,name))
postgres=#explainanalyzeselect*fromtswhereto_tsvector('english',name)@@to_tsquery('pjy');
QUERYPLAN
---------------------------------------------------------------------------------------------------------------------
BitmapHeapScanonts(cost=39.75..8187.70rows=2000width=15)(actualtime=0.016..0.016rows=0loops=1)
RecheckCond:(to_tsvector('english'::regconfig,name)@@to_tsquery('pjy'::text))
->BitmapIndexScanonidx_name(cost=0.00..39.25rows=2000width=0)(actualtime=0.016..0.016rows=0loops=1)
IndexCond:(to_tsvector('english'::regconfig,name)@@to_tsquery('pjy'::text))
Planningtime:0.094ms
Executiontime:0.036ms
(6rows)

大家可以看到,执行时间从2秒下降到了0.04毫秒!!!

关于pg的全文检索,tsvector和tsquery,这里就不详细介绍了,大家可以自己查阅手册。

补充:postgresql子查询优化(提升子查询)

问题背景

在开发项目过程中,客户要求使用gbase8s数据库(基于informix),简单的分页页面响应很慢。排查发现分页sql是先查询出数据在外面套一层后再取多少条,如果去掉嵌套的一层,直接获取则很快。日常使用中postgresql并没有这样的操作也很快,这是为什么呢?

说明

在数据库实现早期,查询优化器对子查询一般采用嵌套执行的方式,即父查询中的每一行,都要执行一次子查询,这样子查询会执行很多次,效率非常低。

本篇主要讲postgresql针对子查询的优化。

项目中使用子查询的地方非常多,如何写出高效的sql,掌握子查询的优化是非常有必要的。

执行计划对比(gbase8svspostgresql):

gbase8s慢sql执行计划:

--gbase8s执行计划

SETEXPLAINON;
SETEXPLAINFILETO'/home/gbasedbt/sqexplain.out';
selectskip0first15*from(select*fromT_SZGL_JDRYorderbyT_SZGL_JDRY.updatetimedesc)EstimatedCost:3207Estimated#ofRowsReturned:61721)gbasedbt.t_szgl_jdry:INDEXPATH(1)IndexName:gbasedbt.i_t_szgl_jdry_updatetimeIndexKeys:updatetime(Reverse)(Serial,fragments:ALL)QUERY:(OPTIMIZATIONTIMESTAMP:12-21-201703:20:43)------selectskip0first15*from(select*fromT_SZGL_JDRYorderbyT_SZGL_JDRY.updatetimedesc)EstimatedCost:232Estimated#ofRowsReturned:61721)(TempTableForCollectionSubquery):SEQUENTIALSCANQuerystatistics:-----------------ThefinalcostoftheplanisreducedbecauseoftheFIRSTnspecificationinthequery.Tablemap:----------------------------InternalnameTablename----------------------------t1t_szgl_jdryt2(TempTableForCollectionSubquery)typetablerows_prodest_rowsrows_scantimeest_cost-------------------------------------------------------------------scant161736172617300:00.053207--查询执行用222ms,15行受影响

gbase8s修改后执行计划

1selectskip0first15*fromT_SZGL_JDRYorderbyT_SZGL_JDRY.updatetimedescEstimatedCost:7Estimated#ofRowsReturned:61721)gbasedbt.t_szgl_jdry:INDEXPATH(1)IndexName:gbasedbt.i_t_szgl_jdry_updatetimeIndexKeys:updatetime(Reverse)(Serial,fragments:ALL)Querystatistics:-----------------ThefinalcostoftheplanisreducedbecauseoftheFIRSTnspecificationinthequery.Tablemap:----------------------------InternalnameTablename----------------------------t1t_szgl_jdrytypetablerows_prodest_rowsrows_scantimeest_cost-------------------------------------------------------------------scant11561721500:00.008QUERY:(OPTIMIZATIONTIMESTAMP:12-21-201703:23:25)------select1fromsysusersEstimatedCost:2Estimated#ofRowsReturned:11)gbasedbt.sysusers:SEQUENTIALSCAN...--查询执行用18ms,15行受影响

第一个执行计划中(1)(TempTableForCollectionSubquery):SEQUENTIALSCAN)可以看出是将子查询的结果查询出来后,在这个基础上获取了15条记录

对比postgresql执行计划

--分页执行计划-不嵌套

1db_jcxxzypt=#explainselect*fromdb_jcxx.t_jcxxzy_tjajorderbyd_slrqlimit15offset0;QUERYPLAN-------------------------------------------------------------------------Limit(cost=0.44..28.17rows=15width=879)->IndexScanusingidx_ttjaj_dslrqont_jcxxzy_tjaj(cost=0.44..32374439.85rows=17507700width=879)(2rows)--子查询执行计划-嵌套一层db_jcxxzypt=#explaindb_jcxxzypt-#select*from(db_jcxxzypt(#select*fromdb_jcxx.t_jcxxzy_tjajorderbyd_slrqdb_jcxxzypt(#)tab1limit15offset0;QUERYPLAN-------------------------------------------------------------------------Limit(cost=0.44..28.32rows=15width=879)->IndexScanusingidx_ttjaj_dslrqont_jcxxzy_tjaj(cost=0.44..32374439.85rows=17507700width=879)(2rows)--子查询执行计划-嵌套两层db_jcxxzypt=#explaindb_jcxxzypt-#select*from(db_jcxxzypt(#select*from(db_jcxxzypt(#select*fromdb_jcxx.t_jcxxzy_tjajorderbyd_slrqdb_jcxxzypt(#)tab1)tab2limit15offset0;QUERYPLAN-------------------------------------------------------------------------Limit(cost=0.44..28.32rows=15width=879)->IndexScanusingidx_ttjaj_dslrqont_jcxxzy_tjaj(cost=0.44..32374439.85rows=17507700width=879)(2rows)

postgresql的子查询即使嵌套多层,执行计划还是和未嵌套一样。原因就是postgresql在重写sql的阶段上拉子查询(提升子查询),把子查询合并到父查询中。

postgresql子查询优化

子查询可分为三类:一、([not]in/all/any/some),二、([not]exists),三、其他子查询(sjp子查询选择、投影、连接)

子查询可以出现在目标列、form子句、where子句、join/on子句、groupby子句、having子句、orderby子句等位置。

1db_jcxxzypt=#explainselect*fromt_jcxxzy_tjajaj,(select*fromt_jcxxzy_ajdsr)dsrwheredsr.c_ajbm='1301020400000120090101';QUERYPLAN-------------------------------------------------------------------------NestedLoop(cost=0.56..1252119.58rows=17507700width=1098)->IndexScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..8.57rows=1width=219)IndexCond:(c_ajbm='1301020400000120090101'::bpchar)->SeqScanont_jcxxzy_tjajaj(cost=0.00..1077034.00rows=17507700width=879)(4rows)Time:1.101ms

postgresql子链接([not]in,[not]exists,all,some,any)

子查询和子链接区别:子查询是不在表达式中的子句,子链接在表达式中的子句

--in子链接

(1).

1db_jcxxzypt=#explainselect*fromt_jcxxzy_tjajajwhereaj.c_ajbmin(selectdsr.c_ajbmfromt_jcxxzy_ajdsrdsr);转化为:select*fromt_jcxxzy_tjajajjoint_jcxxzy_ajdsrdsraj.c_ajbm=dsr.c_ajbm;QUERYPLAN-------------------------------------------------------------------------HashSemiJoin(cost=362618.61..5537768.07rows=7957409width=879)HashCond:(t_jcxxzy_tjaj.c_ajbm=t_jcxxzy_ajdsr.c_ajbm)->SeqScanont_jcxxzy_tjaj(cost=0.00..1077034.00rows=17507700width=879)->Hash(cost=237458.59..237458.59rows=6817202width=23)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..237458.59rows=6817202width=23)(5rows)--in等价于=anyhashsemijoin表示执行的是两张表的hash半连接,原始sql中没有(t_jcxxzy_tjaj.c_ajbm=t_jcxxzy_ajdsr.c_ajbm),表明此in子查询被优化,优化后采用hashsemijoin算法。(2).相关子查询--当加入条件whereaj.d_slrq='2001-06-14'后不能提升子链接,如果把whereaj.d_slrq='2001-06-14'放到父查询是支持子链接优化的db_jcxxzypt=#explaindb_jcxxzypt-#select*fromt_jcxxzy_tjajajwherec_ajbmin(selectc_ajbmfromt_jcxxzy_ajdsrdsrwhereaj.d_slrq='2001-06-14');QUERYPLAN-------------------------------------------------------------------------SeqScanont_jcxxzy_tjajaj(cost=0.00..2227874766580.75rows=8753850width=879)Filter:(SubPlan1)SubPlan1->Result(cost=0.56..237458.59rows=6817202width=23)One-TimeFilter:(aj.d_slrq='2001-06-14'::date)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsrdsr(cost=0.56..237458.59rows=6817202width=23)(6rows(3).--notin不能提升子链接db_jcxxzypt=#explainselect*fromdb_jcxx.t_jcxxzy_tjajwherec_ajbmnotin(selectc_ajbmfromdb_jcxx.t_jcxxzy_ajdsr);QUERYPLAN-------------------------------------------------------------------------SeqScanont_jcxxzy_tjaj(cost=0.56..2875921362927.06rows=8753850width=879)Filter:(NOT(SubPlan1))SubPlan1->Materialize(cost=0.56..311489.60rows=6817202width=23)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..237458.59rows=6817202width=23)(5rows)--notin与<>all含义相同

in子句存在不被优化的可能、当in子句中包含了主查询的表字段,和主查询有相关性时不能提升子链接。

exists子链接

--exists子链接

db_jcxxzypt=#explain
db_jcxxzypt-#select*fromt_jcxxzy_tjajajwhereexists(selectc_ajbmfromt_jcxxzy_ajdsrdsrwhereaj.c_ajbm=dsr.c_ajbm);QUERYPLAN-------------------------------------------------------------------------HashSemiJoin(cost=362618.61..5537768.07rows=7957409width=879)HashCond:(aj.c_ajbm=dsr.c_ajbm)->SeqScanont_jcxxzy_tjajaj(cost=0.00..1077034.00rows=17507700width=879)->Hash(cost=237458.59..237458.59rows=6817202width=23)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsrdsr(cost=0.56..237458.59rows=6817202width=23)(5rows)--当加入whereaj.c_xzdm='150622'条件在子链接时,仍然支持上拉db_jcxxzypt=#explaindb_jcxxzypt-#select*fromt_jcxxzy_tjajajwhereexists(selectc_ajbmfromt_jcxxzy_ajdsrdsrwhereaj.c_xzdm='150622');QUERYPLAN-------------------------------------------------------------------------NestedLoopSemiJoin(cost=0.56..1361779.20rows=5436width=879)->SeqScanont_jcxxzy_tjajaj(cost=0.00..1120803.25rows=5436width=879)Filter:((c_xzdm)::text='150622'::text)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsrdsr(cost=0.56..237458.59rows=6817202width=0)(4rows)--exists子链接db_jcxxzypt=#explaindb_jcxxzypt-#select*fromt_jcxxzy_tjajajwhereexists(selectc_ajbmfromt_jcxxzy_ajdsrdsrwheredsr.c_ajbm='1101120300000120030101')db_jcxxzypt-#;QUERYPLAN-------------------------------------------------------------------------Result(cost=4.58..1077038.57rows=17507700width=879)One-TimeFilter:$0InitPlan1(returns$0)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsrdsr(cost=0.56..4.58rows=1width=0)IndexCond:(c_ajbm='1101120300000120030101'::bpchar)->SeqScanont_jcxxzy_tjajaj(cost=0.00..1077034.00rows=17507700width=879)(6rows)

子查询只执行了一次,作为aj表的参数。

1--notexists子链接db_jcxxzypt=#explaindb_jcxxzypt-#select*fromt_jcxxzy_tjajajwherenotexists(selectc_ajbmfromt_jcxxzy_ajdsrdsr);QUERYPLAN-------------------------------------------------------------------------Result(cost=0.04..1077034.04rows=17507700width=879)One-TimeFilter:(NOT$0)InitPlan1(returns$0)->SeqScanont_jcxxzy_ajdsrdsr(cost=0.00..281210.02rows=6817202width=0)->SeqScanont_jcxxzy_tjajaj(cost=0.00..1077034.00rows=17507700width=879)(5rows)

从执行计划上看,notexists子查询并没有被消除,子查询只是执行了一次,将结果作为aj表的参数。

in和exists都存在不被优化的可能,对于in和exists的选择,当父查询结果集小于子查询结果集则选择exists,如果父查询结果集大于子查询结果集选择in。

所有的all子链接都不支持上拉

1db_jcxxzypt=#explainselect*fromdb_jcxx.t_jcxxzy_tjajwherec_ajbm>all(selectc_ajbmfromdb_jcxx.t_jcxxzy_ajdsr);QUERYPLAN-------------------------------------------------------------------------SeqScanont_jcxxzy_tjaj(cost=0.56..2875921362927.06rows=8753850width=879)Filter:(SubPlan1)SubPlan1->Materialize(cost=0.56..311489.60rows=6817202width=23)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..237458.59rows=6817202width=23)(5rows)db_jcxxzypt=#explainselect*fromdb_jcxx.t_jcxxzy_tjajwherec_ajbm=all(selectc_ajbmfromdb_jcxx.t_jcxxzy_ajdsr);QUERYPLAN-------------------------------------------------------------------------SeqScanont_jcxxzy_tjaj(cost=0.56..2875921362927.06rows=8753850width=879)Filter:(SubPlan1)SubPlan1->Materialize(cost=0.56..311489.60rows=6817202width=23)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..237458.59rows=6817202width=23)(5rows)db_jcxxzypt=#explainselect*fromdb_jcxx.t_jcxxzy_tjajwherec_ajbmMaterialize(cost=0.56..311489.60rows=6817202width=23)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..237458.59rows=6817202width=23)(5rows)

关于all的查询都都是以子查询的形式,不会上拉

some/any

--some和any是等效的

1db_jcxxzypt=#explainselect*fromdb_jcxx.t_jcxxzy_tjajwherec_ajbm>some(selectc_ajbmfromdb_jcxx.t_jcxxzy_ajdsr);QUERYPLAN--------------------------------------------------------------------------NestedLoopSemiJoin(cost=0.56..11316607.35rows=5835900width=879)->SeqScanont_jcxxzy_tjaj(cost=0.00..1077034.00rows=17507700width=879)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..64266.97rows=2272401width=23)IndexCond:(c_ajbmSeqScanont_jcxxzy_tjaj(cost=0.00..1077034.00rows=17507700width=879)->Hash(cost=237458.59..237458.59rows=6817202width=23)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..237458.59rows=6817202width=23)(5rows)db_jcxxzypt=#explainselect*fromdb_jcxx.t_jcxxzy_tjajwherec_ajbmSeqScanont_jcxxzy_tjaj(cost=0.00..1077034.00rows=17507700width=879)->IndexOnlyScanusingidx_tajdsr_cajbmont_jcxxzy_ajdsr(cost=0.56..64266.97rows=2272401width=23)IndexCond:(c_ajbm>t_jcxxzy_tjaj.c_ajbm)(4rows)--some中未出现子查询,dsr表都被上拉到父查询中,与aj表进行嵌套半连接和hash半连接

这些查询中all是完全不支持上拉子子链接的,而in和exists存在不被上拉的可能。

不可上拉的子查询

不支持带有with子句的格式,集合操作、聚集函数(aggregates、group、distinct)、cte、having、limit/offset等子句格式

1db_jcxxzypt=#explainselect*fromt_jcxxzy_tjajaj,(select*fromt_jcxxzy_ajdsrlimit10)dsrwheredsr.c_ajbm='1301020400000120090101';QUERYPLAN-------------------------------------------------------------------------NestedLoop(cost=0.00..1252111.54rows=17507700width=1098)->SubqueryScanondsr(cost=0.00..0.54rows=1width=219)Filter:(dsr.c_ajbm='1301020400000120090101'::bpchar)->Limit(cost=0.00..0.41rows=10width=219)->SeqScanont_jcxxzy_ajdsr(cost=0.00..281210.02rows=6817202width=219)->SeqScanont_jcxxzy_tjajaj(cost=0.00..1077034.00rows=17507700width=879)(6rows)Time:0.958ms

上拉子查询后,父级的多个表之间的连接顺序是怎么样的呢?会有什么变化吗?

对于被上拉的子查询,abase把子查询的关系并入主from-list中,这样关系的个数会增加,按照多表连接顺序算法就会产生更多的连接路径比如A、B、C三张表的关联就有{A,B}、{A,C}、{B,A}、{B,C}、{C,A}、{C,B}六种连接方式

join与子查询固化或rewrite

join或子查询的优化,属于优化器优化JOIN的范畴。

当用户的QUERY涉及到多个JOIN对象,或者涉及到多个子查询时,优化器可以选择是否改变当前的SQL,产生更多的plan选择更优的执行计划。

postgresql.conf文件中:

1#from_collapse_limit=8

当from列表的对象少于from_collapse_limit时,优化器可以将子查询提升到上层进行JOIN,从而可能选择到更优的执行计划。

#join_collapse_limit=8#1disablescollapsingofexplicit#JOINclauses当使用显示的JOIN时(除了fulljoin),例如ajoinbjoincjoind,优化器可以重排JOIN的顺序,以产生更多的PLAN选择更优的执行计划。如果join_collapse_limit=1,则不重排,使用SQL写法提供的顺序。如果用户要固化JOIN顺序,请使用显示的JOIN,同时将join_collapse_limit设置为1。如果用户不打算提升子查询,同样的,将from_collapse_limit设置为1即可。

等价改写

子查询中没有groupby子句,也没有聚集函数,则可使用下面的等价转换

val>all(select...)toval>max(select...)
valval>any(select...)toval>min(select...)val=all(select...)toval>=max(select...)val<=all(select...)toval<=min(select...)val>=any(select...)toval>=min(select...)val<=any(select...)toval<=max(select...)

通常,聚集函数min(),max()的执行效率要比any、all效率高

相关子查询和非相关子查询

相关子查询子查询的执行依赖于外层父查询的一些属性值。子查询因依赖于父查询的参数,当父查询的参数改变时,子查询需要根据新参数值重新执行(查询优化器对相关子查询进行优化有一定意义),如:

1select*fromt_jcxxzy_tjajajwherec_ajbmin(selectc_ajbmfromt_jcxxzy_ajdsrdsrwheredsr.c_ajbm=aj.c_ajbm)/*子查询语句中存在父查询的列*/

非相关子查询子查询的执行,不依赖于外层父查询的任何属性值。这样子查询具有独立性,可独自求解,形成一个子查询计划先于外层的查询求解,如:

1select*fromt_jcxxzy_tjajajwherec_ajbmin(selectc_ajbmfromt_jcxxzy_ajdsrdsrwheredsr.c_xzdm='150622')/*子查询语句中不存在父查询的属性*/

结束语

1.postgresql子查询的优化思路,子查询不用执行多次

2.优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序

3.子查询中的连接条件,过滤条件分别变成了父查询的连接条件、过滤条件、优化器可以对这些条件进行下推、提高执行效率

4.将子查询优化为表连接后,子查询只需要执行一次、而优化器可以根据统计信息来选择不同的连接方式和连接顺序、子查询的连接条件和过滤条件分别变成父查询的条件。

5.这些查询中all是完全不支持上拉子子链接的,in和exists存在不被优化的可能

6.notexists虽然没有被上拉,但是被优化为只执行一次,相对于notin稍好

7.可使用等价改写的方式优化

8.可根据配置文件,固化子查询,以及表的连接顺序

Tags:

很赞哦! ()

文章评论

站点信息

  • 建站时间:2011年01月12日
  • 网站程序:帝国CMS7.5
  • 主题模板《今夕何夕》
  • 文章统计625篇文章
  • 标签管理标签云
  • 微信公众号:扫描二维码,关注我们