赞
踩
WITH查询是PostgreSQL支持的高级SQL特性之一,这一特性常称为CTE(Common Table Expressions)
,WITH查询在复杂查询中定义一个辅助语句(可理解成在一个查询中定义的临时表),这一特性常用于复杂查询或递归查询应用场景。
先通过一个简单的CTE示例了解WITH查询,如下所示:
WITH t as (
SELECT generate_series(1,3)
)
SELECT * FROM t;
执行结果如下:
generate_series
-----------------
1
2
3
(3 rows)
这个简单的CTE示例中,一开始定义了一条辅助语句t取数,之后在主查询语句中查询t,定义的辅助语句就像是定义了一张临时表,对于复杂查询如果不使用CTE,可以通过创建视图方式简化SQL。
CTE可以简化SQL并且减少嵌套,因为可以预先定义辅助语句,之后在主查询中多次调用。接着看一个稍复杂CTE例 子,这个例子来自手册,如下所示:
WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
这个例子首先定义了regional_sales和top_regions两个辅助语句,regional_sales算出每个区域的总销售量,top_regions算出销售量占总销售量10%以上的所有区域,主查询语句通过辅 助语句与orders表关联,算出了顶级区域每件商品的销售量和销售额。
WITH查询的一个重要属性是RECURSIVE,使用 RECURSIVE属性可以引用自己的输出,从而实现递归,一般用于层次结构或树状结构的应用场景,一个简单的 RECURSIVE例子如下所示:
WITH recursive t (x) as (
SELECT 1
UNION SELECT x + 1
FROM t
WHERE x < 5
)
SELECT sum(x) FROM t;
输出结果为:
sum
-----
15
(1 row)
上述例子中x从1开始,union加1后的值,循环直到x小于5 结束,之后计算x值的总和。
接着分享一个递归查询的案例,这个案例来自PostgreSQL 社区论坛一位朋友的问题,他的问题是这样的,存在一张包含如下数据的表:
id name fatherid
1 中国 0
2 辽宁 1
3 山东 1
4 沈阳 2
5 大连 2
6 济南 3
7 和平区 4
8 沈河区 4
当给定一个id时能得到它完整的地名,例如当id=7时,地名是:中国辽宁沈阳和平区,当id=5时,地名是:中国辽宁大连,这是一个典型的层次数据递归应用场景,恰好可以通过 PostgreSQL的WITH查询实现,首先创建测试表并插入数据, 如下所示:
CREATE TABLE test_area(id int4,name varchar(32),fatherid int4);
INSERT INTO test_area VALUES (1, '中国' ,0);
INSERT INTO test_area VALUES (2, '辽宁' ,1);
INSERT INTO test_area VALUES (3, '山东' ,1);
INSERT INTO test_area VALUES (4, '沈阳' ,2);
INSERT INTO test_area VALUES (5, '大连' ,2);
INSERT INTO test_area VALUES (6, '济南' ,3);
INSERT INTO test_area VALUES (7, '和平区' ,4);
INSERT INTO test_area VALUES (8, '沈河区' ,4);
使用PostgreSQL的WITH查询检索ID为7以及以上的所有父节点,如下所示:
WITH RECURSIVE r AS (
SELECT * FROM test_area WHERE id = 7
UNION ALL
SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid
)
SELECT * FROM r ORDER BY id;
查询结果如下:
id | name | fatherid
------+-------+----------
1 | 中国 | 0
2 | 辽宁 | 1
4 | 沈阳 | 2
7 | 和平区 | 4
(4 rows)
查询结果正好是ID=7节点以及它的所有父节点,接下来将输出结果的name字段合并成“中国辽宁沈阳和平区”,方法很多,这里通过string_agg函数实现,如下所示:
mydb=> WITH RECURSIVE r AS (
SELECT * FROM test_area WHERE id = 7
UNION ALL
SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid
)
SELECT string_agg(name,'') FROM ( SELECT name FROM r ORDER BY id) n;
string_agg
--------------------
中国辽宁沈阳和平区
以上是查找当前节点以及当前节点的所有父节点,也可以查找当前节点以及其下的所有子节点,需更改where条件,如查找沈阳市及管辖的区,代码如下所示。
mydb=> WITH RECURSIVE r AS (
SELECT * FROM test_area WHERE id = 4
UNION ALL
SELECT test_area.* FROM test_area, r WHERE test_area.fatherid = r.id
)
SELECT * FROM r ORDER BY id;
id | name | fatherid
----+--------+----------
4 | 沈阳 | 2
7 | 和平区 | 4
8 | 沈河区 | 4
(3 rows)
以上给出了CTE的两个应用场景:复杂查询中的应用和递归查询中的应用,通过示例很容易知道CTE具有以下优点:
批量插入是指一次性插入多条数据,主要用于提升数据插入效率,PostgreSQL有多种方法实现批量插入。
通过表数据或函数批量插入,语法如下:
INSERT INTO table_name SELECT...FROM source_table
比如创建一张表结构和user_ini相同的表并插入user_ini表的全量数据,代码如下所示:
mydb=> CREATE TABLE tbl_batch1(user_id int8,user_name text);
CREATE TABLE
mydb=> INSERT INTO tbl_batch1(user_id,user_name) SELECT user_id,user_name FROM user_ini;
INSERT 0 1000000
以上示例将表user_ini的user_id、user_name字段所有数据插入表tbl_batch1,也可以插入一部分数据,插入时指定where 条件即可。 通过函数进行批量插入,如下所示:
mydb=> CREATE TABLE tbl_batch2 (id int4,info text);
CREATE TABLE
mydb=> INSERT INTO tbl_batch2(id,info)
SELECT generate_series(1,5),'batch2';
INSERT 0 5
通过SELECT表数据批量插入的方式大多关系型数据库都支持,接下来看看PostgreSQL支持的其他批量插入方式。
PostgreSQL的另一种支持批量插入的方法为在一条 INSERT语句中通过VALUES关键字插入多条记录,通过一个 例子就很容易理解,如下所示:
mydb=> CREATE TABLE tbl_batch3(id int4,info text);
CREATE TABLE
mydb=> INSERT INTO tbl_batch3(id,info) VALUES (1,'a'),(2,'b'),(3,'c');
INSERT 0 3
数据如下所示:
mydb=> SELECT * FROM tbl_batch3;
id | info
-------+------
1 | a
2 | b
3 | c
(3 rows)
这种批量插入方式非常独特,一条SQL插入多行数据,相 比一条SQL插入一条数据的方式能减少和数据库的交互,减少数据库WAL(Write-Ahead Logging)日志的生成,提升插入效率,通常很少有开发人员了解PostgreSQL的这种批量插入方 式。
前面介绍了psql导入、导出表数据,使用的是COPY命令或\copy元命令,copy或\copy元命令能够将一定格式的文件数据导入到数据库中,相比INSERT命令插入效率更高,通常大数据量的文件导入一般在数据库服务端主机通过PostgreSQL 超级用户使用COPY命令导入,下面通过一个例子简单看看 COPY命令的效率,测试机为一台物理机上的虚机,配置为4核CPU,8GB内存。 首先创建一张测试表并插入一千万数据,如下所示:
mydb=> CREATE TABLE tbl_batch4( id int4, info text, create_time timestamp(6) with time zone default clock_timestamp());
CREATE TABLE
mydb=> INSERT INTO tbl_batch4(id,info) SELECT n,n||'_batch4' FROM generate_series(1,10000000) n;
INSERT 0 10000000
以上示例通过INSERT插入一千万数据,将一千万数据导出到文件,如下所示:
[postgres@pghost1 ~]$ psql mydb postgres
psql (10.0)
Type "help" for help.
mydb=# \timing
Timing is on.
mydb=# COPY pguser.tbl_batch4 TO '/home/pg10/tbl_batch4.txt';
COPY 10000000
Time: 6575.787 ms (00:06.576)
一千万数据导出花了6575毫秒,之后清空表tbl_batch4并将文件tbl_batch4.txt的一千万数据导入到表中,如下所示:
mydb=# TRUNCATE TABLE pguser.tbl_batch4;
TRUNCATE TABLE
mydb=# COPY pguser.tbl_batch4 FROM '/home/pg10/tbl_batch4.txt';
COPY 10000000
Time: 15663.834 ms (00:15.664)
一千万数据通过COPY命令导入执行时间为15663毫秒。
PostgreSQL的RETURNING特性可以返回DML修改的数据,具体为三个场景:INSERT语句后接RETURNING属性返回插入的数据;UPDATE语句后接RETURNING属性返回更新后的新值;DELETE语句后接RETURNING属性返回删除的数据。这个特性的优点在于不需要额外的SQL获取这些值,能够方便应用开发,下面通过示例演示。
INSERT语句后接RETURNING属性返回插入的值,下面的代码创建测试表,并返回已插入的整行数据。
mydb=> CREATE TABLE test_r1(id serial,flag char(1));
CREATE TABLE
mydb=> INSERT INTO test_r1(flag) VALUES ('a') RETURNING *;
id | flag
-------+------
1 | a
(1 row)
INSERT 0 1
RETURNING* 表示返回表插入的所有字段数据,也可以返回指定字段,RETURNING后接字段名即可,如下代码仅返回插入的id字段:
mydb=> INSERT INTO test_r1(flag) VALUES ('b') RETURNING id;
id
----
2
(1 row)
INSERT 0 1
UPDATE后接RETURNING属性返回UPDATE语句更新后的值,如下所示:
mydb=> SELECT * FROM test_r1 WHERE id=1;
id | flag
-------+------
1 | a (1 row)
mydb=> UPDATE test_r1 SET flag='p' WHERE id=1 RETURNING *;
id | flag
-------+------
1 | p
(1 row)
UPDATE 1
DELETE后接RETURNING属性返回删除的数据,如下所示:
mydb=> DELETE FROM test_r1 WHERE id=2 RETURNING *;
id | flag
-------+------
2 | b
(1 row)
DELETE 1
PostgreSQL的UPSERT特性是指INSERT…ON CONFLICT UPDATE,用来解决在数据插入过程中数据冲突的情况,比如违反用户自定义约束,在日志数据应用场景中,通常会在事务中批量插入日志数据,如果其中有一条数据违反表上的约束, 则整个插入事务将会回滚,PostgreSQL的UPSERT特性能解决这一问题。
接下来通过例子来理解UPSERT的功能,定义一张用户登录日志表并插入一条数据,如下所示:
mydb=> CREATE TABLE user_logins(user_name text primary key, login_cnt int4, last_login_time timestamp(0) without time zone);
CREATE TABLE
mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('francs',1);
INSERT 0 1
在user_logins表user_name字段上定义主键,批量插入数据中如有重复会报错,如下所示:
mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('matiler',1),('francs',1);
ERROR: duplicate key value violates unique constraint "user_logins_pkey" DETAIL: Key (user_name)=(francs) already exists.
上述SQL试图插入两条数据,其中matiler这条数据不违反主键冲突,而francs这条数据违反主键冲突,结果两条数据都不能插入。PostgreSQL的UPSERT可以处理冲突的数据,比如当插入的数据冲突时不报错,同时更新冲突的数据,如下所 示:
mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('matiler',1),('francs',1) ON CONFLICT(user_name) DO UPDATE SET login_cnt=user_logins.login_cnt+EXCLUDED.login_cnt,last_login_time=now();
INSERT 0 2
上述INSERT语句插入两条数据,并设置规则:当数据冲 突时将登录次数字段login_cnt值加1,同时更新最近登录时间 last_login_time,ON CONFLICT(user_name)定义冲突类型为 user_name字段,DO UPDATE SET是指冲突动作,后面定义了 一个UPDATE语句。注意上述SET命令中引用了user_loins表和内置表EXCLUDED,引用原表user_loins访问表中已存在的冲 突记录,内置表EXCLUDED引用试图插入的值,再次查询表 user_login,如下所示:
mydb=> SELECT * FROM user_logins ;
user_name | login_cnt | last_login_time
--------------+-----------+---------------------
matiler | 1 |
francs | 2 | 2021-08-08 15:23:13
(2 rows)
一方面冲突的francs这条数据被更新了login_cnt和 last_login_time字段,另一方面新的数据matiler记录已正常插 入。
也可以定义数据冲突后啥也不干,这时需指定DO NOTHING属性,如下所示:
mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('tutu',1),('francs',1) ON CONFLICT(user_name) DO NOTHING;
INSERT 0 1
再次查询表数据,新的数据tutu这条已插入到表中,冲突的数据francs这行啥也没变,结果如下所示:
mydb=> SELECT * FROM user_logins ;
user_name | login_cnt | last_login_time
--------------+-----------+---------------------
matiler | 1 |
francs | 2 | 2021-08-08 15:23:13
tutu | 1 |
(3 rows)
PostgreSQL的UPSERT语法比较复杂,通过以上演示后再 来查看语法会轻松些,语法如下:
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
[ ON CONFLICT [ conflict_target ] conflict_action ]
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]
以上语法主要注意[ON CONFLICT[conflict_target]conflict_action]这行,conflict_target指选择仲裁索引判定冲突行为,一般指定被创建约束的字段;
conflict_action指冲突动作,可以是DO NOTHING,也可以是用户自定义的UPDATE语句。
数据抽样(TABLESAMPLE)在数据处理方面经常用到, 特别是当表数据量比较大时,随机查询表中一定数量记录的操作很常见,PostgreSQL早在9.5版时就已经提供了 TABLESAMPLE数据抽样功能,9.5版前通常通过ORDER BY random()方式实现数据抽样,这种方式虽然在功能上满足随机返回指定行数据,但性能很低,如下所示:
mydb=> SELECT * FROM user_ini ORDER BY random() LIMIT 1;
id | user_id | user_name | create_time
-------------+---------+-----------+-------------------------------
500449 | 768810 | 2TY6P4 | 2021-08-05 15:59:32.294761+08
(1 row)
mydb=> SELECT * FROM user_ini ORDER BY random() LIMIT 1;
id | user_id | user_name | create_time
-------------+---------+-----------+-------------------------------
324823 | 740720 | 07SKCU | 2021-08-05 15:59:29.913984+08 (1 row)
执行计划如下所示:
mydb=> EXPLAIN ANALYZE SELECT * FROM user_ini ORDER BY random() LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------
Limit (cost=25599.98..25599.98 rows=1 width=35) (actual time=367.867..367.868 rows=1 loops=1)
-> Sort (cost=25599.98..28175.12 rows=1030056 width=35) (actual time= 367.866..367.866 rows=1 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on user_ini (cost=0.00..20449.70 rows=1030056 width=35) (actual time=0.012..159.569 rows=1000000 loops=1)
Planning time: 0.083 ms
Execution time: 367.909 ms
(7 rows)
表user_ini数据量为100万,从100万随机取一条上述SQL的执行时间为367ms,这种方法进行了全表扫描和排序,效率非常低,当表数据量大时,性能几乎无法接受。 9.5版本以后PostgreSQL支持TABLESAMPLE数据抽样, 语法如下所示:
SELECT ...
FROM table_name
TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]
sampling_method指抽样方法,主要有两种:SYSTEM和 BERNOULLI,接下来详细介绍这两种抽样方式,argument指抽样百分比。
注意 explain analyze命令表示实际执行这条SQL,同时显示SQL执行计划和执行时间,Planning time表示SQL语句解析生成执行计划的时间,Execution time表示SQL的实际执行时间。
SYSTEM抽样方式为随机抽取表上数据块上的数据,理论上被抽样表的每个数据块被检索的概率是一样的,SYSTEM抽 样方式基于数据块级别,后接抽样参数,被选中的块上的所有 数据将被检索,下面使用示例进行说明。
创建test_sample测试表,并插入150万数据,如下所示:
mydb=> CREATE TABLE test_sample(id int4,message text,
create_time timestamp(6) without time zone default clock_timestamp());
CREATE TABLE
mydb=> INSERT INTO test_sample(id,message) SELECT n, md5(random()::text) FROM generate_series(1,1500000) n;
INSERT 0 1500000
mydb=> SELECT * FROM test_sample LIMIT 1;
id | message | create_time
-------+----------------------------------+----------------------------
1 | 58f2506410be948963d6d9adf4b4e0c2 | 2021-08-08 21:17:20.984481
(1 row)
抽样因子设置成0.01,意味着返回1500000×0.01%=150条记录,执行如下SQL:
EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE SYSTEM(0.01);
执行计划如下所示:
mydb=> EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE SYSTEM(0.01);
QUERY PLAN
--------------------------------------------------------------------------------
Sample Scan on test_sample (cost=0.00..3.50 rows=150 width=45) (actual time=0.099..0.146 rows=107 loops=1)
Sampling: system ('0.01'::real)
Planning time: 0.053 ms
Execution time: 0.166 ms
(4 rows)
以上执行计划主要有两点,一方面进行了Sample Scan扫描(抽样方式为SYSTEM),执行时间为0.166毫秒,性能较好,另一方面优化器预计访问150条记录,实际返回107条,为什么会返回107条记录呢?接着查看表占用的数据块数量,如下所示:
mydb=> SELECT relname,relpages FROM pg_class WHERE relname='test_sample'; relname | relpages ----------------+---------- test_sample | 14019 (1 row)
表test_sample物理上占用14019个数据块,也就是说每个 数据块存储1000000/14019=107条记录。 查看抽样数据的ctid,如下所示:
mydb=> SELECT ctid,* FROM test_sample TABLESAMPLE SYSTEM(0.01); ctid | id | message | create_time ------------+--------+----------------------------------+-------------------------- (5640,1) | 603481 | 385484b3452b245e46388d71ce4ea928 | 2017-08-08 21:17:23.32394 (5640,2) | 603482 | e09c526118f1d4b3c391d59ae915c4e8 | 2017-08-08 21:17:23.323964 ….省略很多行 (5640,107) | 603587 | c33875a052f4ca63c4b38c649fb6bcc3 | 2017-08-08 21:17:23.324336 (107 rows)
ctid是表的隐藏列,括号里的第一位表示逻辑数据块编 号,第二位表示逻辑块上的数据的逻辑编号,从以上看出,这 107条记录都存储在逻辑编号为5640的数据块上,也就是说抽 样查询返回了一个数据块上的所有数据,抽样因子固定为 0.01,多次执行以下查询,如下所示:
mydb=> SELECT count(*) FROM test_sample TABLESAMPLE SYSTEM(0.01); count ------- 214 (1 row)
mydb=> SELECT count(*) FROM test_sample TABLESAMPLE SYSTEM(0.01); count ------- 107
(1 row)
再次查询发现返回的记录为214或107,由于一个数据块存储107条记录,因此查询结果有时返回了两个数块上的所有数据,这是因为抽样因子设置成0.01,意味着返回 1500000×0.01%=150条记录,150条记录需要两个数据块存储,这也验证了SYSTEM抽样方式返回的数据以数据块为单位,被抽样的块上的所有数据被检索。
BERNOULLI抽样方式随机抽取表的数据行,并返回指定百分比数据,BERNOULLI抽样方式基于数据行级别,理论上被抽样表的每行记录被检索的概率是一样的,因此 BERNOULLI抽样方式抽取的数据相比SYSTEM抽样方式具有更好的随机性,但性能上相比SYSTEM抽样方式低很多,下面演示下BERNOULLI抽样方式,同样基于test_sample测试表。 设置抽样方式为BERNOULLI,抽样因子为0.01,如下所 示:
mydb=> EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE BERNOULLI (0.01);
QUERY PLAN
--------------------------------------------------------------------------------
Sample Scan on test_sample (cost=0.00..14020.50 rows=150 width=45) (actual time=0.025..22.541 rows=152 loops=1)
Sampling: bernoulli ('0.01'::real)
Planning time: 0.063 ms
Execution time: 22.569 ms
(4 rows)
从以上执行计划看出进行了Sample Scan扫描(抽样方式 为BERNOULLI),执行计划预计返回150条记录,实际返回152条,从返回的记录数来看,非常接近150条 (1000000×0.01%),但执行时间却要22.569毫秒,性能相比 SYSTEM抽样方式0.166毫秒差了136倍。 多次执行以下查询,查看返回记录数的变化,如下所示:
mydb=> SELECT count(*) FROM test_sample TABLESAMPLE BERNOULLI(0.01);
count
-------
151
(1 row)
mydb=> SELECT count(*) FROM test_sample TABLESAMPLE BERNOULLI(0.01);
count
-------
147
(1 row)
从以上看出,BERNOULLI抽样方式返回的数据量非常接近抽样数据的百分比,而SYSTEM抽样方式数据返回以数据块为单位,被抽样的块上的所有数据都被返回,因此SYSTEM抽样方式返回的数据量偏差较大。 由于BERNOULLI抽样基于数据行级别,猜想返回的数据应该位于不同的数据块上,通过查询表的ctid进行验证,如下所示:
mydb=> SELECT ctid,id,message FROM test_sample TABLESAMPLE BERNOULLI(0.01) lIMIT 3;
ctid | id | message
-----------+-------+----------------------------------
(55,30) | 5915 | f3803f234f6cf6cdd276d9d027487582
(240,23) | 25703 | c04af69ac76f6465832e0cd87939a1af
(318,3) | 34029 | dd35438b24980d1a8ed2d3f5edd5ca1c
从以上三条记录的ctid信息看出,三条数据分别位于数据块55、240、318上,因此BERNOULLI抽样方式随机性相比 SYSTEM抽样方式更好。
本节演示了SYSTEM和BERNOULLI抽样方式,SYSTEM 抽样方式基于数据块级别,随机抽取表数据块上的记录,因此这种方式抽取的记录的随机性不是很好,但返回的数据以数据块为单位,抽样性能很高,适用于抽样效率优先的场景,例如抽样大小为上百GB的日志表;而BERNOULLI抽样方式基于数据行,相比SYSTEM抽样方式所抽样的数据随机性更好,但性能相比SYSTEM差很多,适用于抽样随机性优先的场景。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。