原 PG在SQL语句中使用nolock提示？？
Tags： 原创PGPostgreSQLnolock
👉 本文共约655个字，系统预计阅读时间或需3分钟。
前言
今天同事发了一条SQL语句，让我优化，乍一看SQL中带有nolock，于是就让他把SQL Server的连接环境发我，结果同事说，这是PostgreSQL环境，惊呆了。。。，
SQL如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT A.boumber, COUNT ( DISTINCT seqnum ) bgs, ( SELECT MIN ( b.ttime ) FROM tb_aaaaa b ( nolock ) WHERE A.boumber = b. boumber ) sjsy FROM tb_aaaaa A ( nolock ) WHERE ( testabbreviation ='T4') AND A.ttime between '2024-05-01'::TIMESTAMP and '2024-05-31 23:59:59' ::TIMESTAMP GROUP BY A.boumber ORDER BY sjsy |
于是我登录PG环境，运行了一下，果然不报错。
SQL优化
先说这条SQL的优化，这个SQL优化很简单，创建一个include索引让其走Index Only Scan即可：
1 2 | create index idx_tb_aaaaa_all on tb_aaaaa(ttime,boumber) include(seqnum,testabbreviation ); |
运行了1下，大概2秒出结果，不再详细分析了。
优化前后的执行计划：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | -- 优化前 Sort (cost=475894253.13..475894263.24 rows=4043 width=21) Sort Key: ((SubPlan 1)) -> GroupAggregate (cost=88616.60..475894010.93 rows=4043 width=21) Group Key: a.boumber -> Gather Merge (cost=88616.60..89224.74 rows=5079 width=15) Workers Planned: 4 -> Sort (cost=87616.55..87619.72 rows=1270 width=15) Sort Key: a.boumber -> Parallel Seq Scan on idx_tb_aaaaa_all a (cost=0.00..87551.07 rows=1270 width=15) Filter: ((ttime >= '2024-05-01 00:00:00'::timestamp without time zone) AND (ttime <= '2024-05-31 23:59:59'::timestamp without time zone) AND ((testviation)::text = 'T4'::text)) SubPlan 1 -> Aggregate (cost=117686.04..117686.05 rows=1 width=8) -> Seq Scan on idx_tb_aaaaa_all b (cost=0.00..117685.21 rows=333 width=8) Filter: ((a.boumber)::text = (boumber)::text) -- 优化后 Sort (cost=1648745.98..1648756.09 rows=4043 width=21) Sort Key: ((SubPlan 2)) -> GroupAggregate (cost=78267.03..1648503.78 rows=4043 width=21) Group Key: a.boumber -> Gather Merge (cost=78267.03..78875.16 rows=5079 width=15) Workers Planned: 4 -> Sort (cost=77266.97..77270.14 rows=1270 width=15) Sort Key: a.boumber -> Parallel Bitmap Heap Scan on idx_tb_aaaaa_all a (cost=5215.82..77201.50 rows=1270 width=15) Recheck Cond: ((ttime >= '2024-05-01 00:00:00'::timestamp without time zone) AND (ttime <= '2024-05-31 23:59:59'::timestamp without time zone)) Filter: ((testviation)::text = 'T4'::text) -> Bitmap Index Scan on idx_tb_aaaaa_all (cost=0.00..5214.55 rows=151012 width=0) Index Cond: ((ttime >= '2024-05-01 00:00:00'::timestamp without time zone) AND (ttime <= '2024-05-31 23:59:59'::timestamp without time zone)) SubPlan 2 -> Result (cost=388.21..388.22 rows=1 width=8) InitPlan 1 (returns $1) -> Limit (cost=0.43..388.21 rows=1 width=8) -> Index Only Scan using idx_tb_aaaaa_all on idx_tb_aaaaa_all b (cost=0.43..129130.29 rows=333 width=8) Index Cond: ((ttime IS NOT NULL) AND (boumber = (a.boumber)::text)) |
PG中可以使用nolock吗
先测试一下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [root@alldb ~]# docker exec -it lhrpg12 psql -U postgres -d postgres psql (12.18 (Debian 12.18-1.pgdg120+2)) Type "help" for help. postgres=# select version(); version ----------------------------------------------------------------------------------------------------------------------- PostgreSQL 12.18 (Debian 12.18-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit (1 row) postgres=# select count(*) from pg_class a (nolock) ; count ------- 395 (1 row) postgres=# \q You have mail in /var/spool/mail/root [root@alldb ~]# docker exec -it lhrpg16 psql -U postgres -d postgres psql (16.2 (Debian 16.2-1.pgdg120+2)) Type "help" for help. postgres=# select version(); version --------------------------------------------------------------------------------------------------------------------- PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit (1 row) postgres=# select count(*) from pg_class a (nolock) ; count ------- 415 (1 row) postgres=# |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
