Skip to main content

moregeek program

pg--扫描方式_mysql dba攻坚之路的博客-多极客编程

CTID是什么

CTID是一个系统列,用于标识某一元组位于哪个位置,由(block number + 块内的偏移量offset)组成和oracle的rowid十分类似

select attname from pg_attribute where attrelid='test2'::regclass;
attname
----------
cmax
cmin
ctid 类似oracle的row_id
id
info
tableoid 分区表可以通过该值快速找到子表
xmax
xmin
(8 rows)

例如

postgres=# select * ,ctid from test2 limit 10;
id | info | ctid
----+------+--------
1 | test | (0,1) 第0个数据库第一行
2 | test | (0,2)
3 | test | (0,3)
4 | test | (0,4)
5 | test | (0,5)
6 | test | (0,6)
7 | test | (0,7)
8 | test | (0,8)
9 | test | (0,9)
10 | test | (0,10)
(10 rows)


PG扫描方式类型

  1. Sequential Scan
  2. Index Scan (tid回表)
  3. Index Only Scan (直接从索引里面区全数据)相当于mysql的覆盖索引
  4. Bitmap Scan
  5. TID Scan (比如select * from test where ctid ='(0,5)';)

每一个扫描方法都同样有用,这取决于查询本身,和一些其他的参数,如表的基数、表的选择率、磁盘随机IO的成本、顺序IO的成本等等。让我们先创建一些表,并填充一些数据,这会方便解释这些扫描方法。

postgres=# drop table demotable;
DROP TABLE
postgres=# CREATE TABLE demotable (num numeric, id int);
CREATE TABLE
postgres=# CREATE INDEX demoidx ON demotable(num);
CREATE INDEX
postgres=# INSERT INTO demotable SELECT random() * 1000, generate_series(1, 1000000);
INSERT 0 1000000
postgres=# analyze;
ANALYZE

在这个例子,插入了1千万数据,并且进行了analyze,以确保所有的统计信息是最新的。


顺序扫描

顾名思义,对某表的顺序扫描是通过顺序扫描对应表所有页面的所有项目指针来完成的。因此,如果某个表有100页,然后每页中有1000条记录,那么作为顺序扫描的一部分,它会获取100 * 1000条记录,并根据隔离级别和谓词子句检查是否匹配。因此,即使在整个表扫描中只选择了1条记录,也必须扫描100K条记录才能根据条件找到合格的记录。

根据上面的表和数据,由于选择了大多数数据,因此以下查询将导致顺序扫描。

postgres=#  explain SELECT * FROM demotable WHERE num < 21000; 
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on demotable (cost=0.00..17990.00 rows=1000000 width=15)
Filter: (num < '21000'::numeric)
(2 rows)

成本是什么算的

cost表示启动成本

rows表示预估返回行数

width表示返回每行的平均字节

postgres=# select name ,setting from pg_settings where name like '%cost%';
name | setting
------------------------------+---------
autovacuum_vacuum_cost_delay | 2
autovacuum_vacuum_cost_limit | -1
cpu_index_tuple_cost | 0.005 cpu处理一个索引的成本
cpu_operator_cost | 0.0025 cpu处理一个操作的成本
cpu_tuple_cost | 0.01 cpu处理一行数据的成本
jit_above_cost | 100000
jit_inline_above_cost | 500000
jit_optimize_above_cost | 500000
parallel_setup_cost | 1000 初始化成本
parallel_tuple_cost | 0.1
random_page_cost | 4 随机扫描一个page的成本是4
seq_page_cost | 1 顺序扫描一个page的成本是1
vacuum_cost_delay | 0
vacuum_cost_limit | 200
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit | 1
vacuum_cost_page_miss | 10
(17 rows)

cost成本计算方式

postgres=#  explain SELECT * FROM demotable WHERE num < 21000; 
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on demotable (cost=0.00..17990.00 rows=1000000 width=15)

postgres=# select reltuples ,relpages from pg_class where relname='demotable';
reltuples | relpages
-----------+----------
1e+06 | 5490


cost计算方式
seq_page_cost*relpages+cpu_tuple_cost*rows+cpu_operator_cost*rows

postgres=# select 5490*1+0.01*1000000+0.0025*1000000;
?column?
------------
17990.0000
(1 row)


索引扫描

索引由 key(索引列) +CTID 组成

从索引相关数据结构中取数据,返回数据的TID根据TID回表获得整个数据。

索引扫描有两个步骤:

1. 从索引的相关数据结构中获取数据。它返回Heap中相应数据的TID。

2. 然后直接访问相应的Heap页以获得整个数据。由于以下原因,需要这一额外步骤:

1)查询请求的列数量可能超过了索引中的列数量。

2)可见性信息不会和索引一起维护。因此,为了根据隔离级别检查数据的可见性,仍需要访问堆内数据。

现在我们可能会疑惑,既然索引如此高效,为何不总是使用索引扫描。正如我们所知,每件事都是有代价的。这里涉及的成本与我们正在执行的IO类型有关。对于索引扫描,涉及到随机IO,就像在索引中找到的每个记录一样,它必须从HEAP存储中获取相应的数据,而对于顺序扫描,则涉及到顺序IO,顺序IO大约只需要花费25% 随机IO的时间。因此,由于有随机IO的存在,只有整体的增益超过开销时,才应选择索引扫描。

postgres=#  explain SELECT * FROM demotable WHERE num = 21000;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using demoidx on demotable (cost=0.42..8.44 rows=1 width=15)
Index Cond: (num = '21000'::numeric)
(2 rows)

postgres=#
postgres=#
postgres=# explain select num from demotable where num =21000;
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using demoidx on demotable (cost=0.42..8.44 rows=1 width=11)
Index Cond: (num = '21000'::numeric)
(2 rows)

仅索引扫描

类似于索引扫描,除了第二步,顾名思义,它只扫描索引数据结构。为了选择仅索引扫描而不是索引扫描,还有两个附加的前提条件:

  • 查询只获取索引中所包含的列。
  • 对应堆页上的所有元组(记录)都应该可见。索引数据结构并不会维护可见性信息,因此,为了仅从索引中获取数据,我们应避免检查可见性,如果认为该页面上的所有数据都认为是可见的,则可能会发生这种情况。
  • 上面的例子的查询将使用仅扫描索引。即使这个查询在选择记录的数量方面几乎和前一节的索引扫描是类似的,但因为只有字段(即num)被选择,所以它会选择Index only Scan。
  • 索引里面并不存放可见性信息,VM可以告诉pg某个数据块的数据是可见的
  • 就是没有死元组,那么我们可以直接将索引里面的值返回,不需要一次额外的IO操作。可能还是会回表,更新后,数据块显示not_all_visible,则会回表查询数据块去检验可见性。
postgres=# explain analyze select num from demotable where num =21000;
QUERY PLAN

------------------------------------------------------------------------------------------------
-------------------------
Index Only Scan using demoidx on demotable (cost=0.42..8.44 rows=1 width=11) (actual time=0.04
9..0.049 rows=0 loops=1)
Index Cond: (num = '21000'::numeric)
Heap Fetches: 0 回表次数
Planning Time: 0.061 ms
Execution Time: 0.063 ms
(5 rows)

位图扫描

消除离散,重复读HEAP

是索引扫描和顺序扫描的结合,试图解决索引扫描的缺点,索引扫描每一条都需要在Heap页中找到对应的数据,因此,它还需要先获取索引页,再获取堆页,导致大量的随机IO。

  • Bitmap Index Scan:首先,它从索引数据结构中获取所有索引数据,并创建所有TID的位图映射。为了简单理解,可以将此位图看作包含所有页面的散列(基于页号的散列),并且每个页面条目包含一个包含该页的页内所有偏移量的数组。
  • Bitmap Heap Scan:顾名思义,它通过读取页面的位图,然后从堆中扫描对应的页和偏移量。最后,它检查可见性和谓词等,并根据所有这些检查的结果返回元组。

​位图扫描利用了索引扫描的优势,而无需使用随机I / O

位图扫描方式是bitmap index scan -->位图比对-->内存排序-->回表。

postgres=# explain SELECT * FROM demotable WHERE num < 210;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on demotable (cost=5784.40..13900.16 rows=210061 width=15)
Recheck Cond: (num < '210'::numeric)
-> Bitmap Index Scan on demoidx (cost=0.00..5731.88 rows=210061 width=0)
Index Cond: (num < '210'::numeric)
(4 rows)

考虑下面的查询,该查询获取相同数量的记录,但仅选择关键字段(即仅索引列)。由于它仅获取索引列,因此不需要为其他部分数据而去引用堆页面,因此不会涉及到随机I / O。因此,此查询将选择“仅索引扫描”而不是“位图扫描”。

postgres=# explain SELECT num FROM demotable WHERE num < 210;
QUERY PLAN
---------------------------------------------------------------------------------------
Index Only Scan using demoidx on demotable (cost=0.42..7784.87 rows=208254 width=11)
Index Cond: (num < '210'::numeric)
(2 rows)

TID扫描

查找谓词中有TID

postgres=# explain select * from demotable where ctid='(0,10)';
QUERY PLAN
----------------------------------------------------------
Tid Scan on demotable (cost=0.00..4.01 rows=1 width=15)
TID Cond: (ctid = '(0,10)'::tid)
(2 rows)

©著作权归作者所有:来自51CTO博客作者进击的CJR的原创作品,请联系作者获取转载授权,否则将追究法律责任

keewidb:兼容redis协议,领跑nosql_腾讯云数据库的博客-多极客编程

如果现在的我们离开了互联网,生活会是什么样子?互联网++++,已经深刻渗透到人们的生活中。不知道大家有没有想过?每一个互联网+结合的背后都是海量的存储需求。你查看的每一个商品、组建的每一个战队、阅读的每一篇文章,基于互联网的每一个兴趣爱好,都有它的key和value。在 key-value 数据库领域,Redis 因其高吞吐、低延迟、丰富的数据结构一直受开发者欢迎,但 Redis 全内存方案无法解

pg-日常运维_mysql dba攻坚之路的博客-多极客编程

VACUUM表膨胀是什么有效数据量不变,表越来越大,扫描的效率变低。是因为PG的MVCC写数据时,旧数据不删除,把新数据插入,将旧数据标记为无效,清理之前一直占用空间。执行update的话就是insert+delete的原理,依然会导致表膨胀。vacuum的作用磁盘清理dead tuple ;更新统计信息;重组数据;解决事务ID回卷问题。vacuum : 不要求获得排它锁,找到那些旧的“死”数据

百度交易中台之资产系统架构浅析_百度geek说的博客-多极客编程

作者 | 小黑哥导读:百度交易中台资产系统是基于百度收银台和交易系统下,对公司内C端个人现金、虚拟类资产(虚拟币等)业务进行收拢、管理,提供安全可靠且符合国家清算规范的用户资产管理能力。交易中台资产系统基于现有交易中台部分能力,一站式解决业务方对用户资产管理、平台分账、对账、财报等问题,快速支持资产类业务发展。全文5085字,预计阅读时间13分钟。一、系统介绍百度交易中台支持百度集团内部的代收代付

pg--执行计划之辅助算法_mysql dba攻坚之路的博客-多极客编程

Sort需要排序数据时,这个节点就会作为计划树的一部分被添加。可以显式或隐式地要求排序数据postgres=# drop table demotable;DROP TABLEpostgres=# CREATE TABLE demotable (num numeric, id int);CREATE TABLEpostgres=# INSERT INTO demotable SELECT rando

pg--join方式_mysql dba攻坚之路的博客-多极客编程

PostgreSQL支持如下几种连接方式:Nested Loop JoinHash JoinMerge Joinpostgres=# create table blogtable1(id1 int, id2 int);CREATE TABLEpostgres=# create table blogtable2(id1 int, id2 int); CREATE TABLEpostgres=# i

mysql导入导出csv文件_雍州无名的博客-多极客编程

1.查看secure_file_privshow global variables like '%secure_file_priv%';secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录1.secure_file_priv 为 NULL 时,表示限制mysqld不允许导入或导出。2.secure_file_pr

数据中台与业务中台的区别与联系_枫叶飘飘的博客-多极客编程

业务中台更多偏向于业务流程管控,将业务流程中共性的服务抽象出来,形成通用的服务能力。比如电商平台,有C2C、B2C、C2B、B2B四种模式,其中订单、交易、商品管理、购物车等模块都是有共性的。将这些组件沉淀出来,形成电商行业的业务中台,再基于这些业务中台组件的服务能力,可以快速搭建前台应用,譬如C2C模式的淘宝、B2C模式的天猫、B2B模式的1688、C2B模式的聚划算,用户通过这些前台业务触点使

javaweb核心之servletconfig_紫轩蝶泪的博客-多极客编程

2 ServletConfig 2.1 ServletConfig概述 2.1.1 基本概念 它是Servlet的配置参数对象,在Servlet规范中,允许为每个Servlet都提供一些初始化配置。所以,每个Servlet都一个自己的ServletConfig。它的作用是在Servlet初始化期间,把一些配置信息传递给Servlet。 2.1.2 生命周期 由于它是在初始化阶段读取了web.xml

【服务器数据恢复】服务器误删除导致邮件数据丢失的数据恢复案例_sun的博客-多极客编程

服务器数据恢复环境: 8块盘组成的RAID5磁盘阵列; EXT3文件系统。 服务器故障: 由于误删除导致文件系统中的邮件丢失。 服务器数据恢复过程: 一、服务器数据恢复工程师为每块磁盘做镜像, 后续所有的数据恢复操作都在镜像盘上进行, 不会对原始磁盘数据造成二次破坏。 二、分析数据在硬盘中分布的规律,获取RAID类型、RAID条带大小、每块磁盘的顺序等RAID信息。根据获取到的RAID信息重新组建

由《羊了个羊》想到的高并发架构之路_13043917的博客-多极客编程

前言     要说最近一段时间最火的话题是什么,那必定是《羊了个羊》,频频冲上微博热搜第一。因访问量骤增,大量玩家涌入进来,高并发流量导致游戏服务器被接连击穿。《羊了个羊》服务器几天内就出现了多次异常,无法登录游戏。 问题思考     我想这其中多次崩溃的原因可能很多:可能是高并发流量导致服务器负载打满,引发宕机;可能是数据库查询量较大,出现性能瓶颈;也可能是高并发访问将缓存击穿;还有可能是受到d

【操作系统】计算机操作系统r复习大纲——汤子瀛版_灵彧universe的博客-多极客编程

操作系统的定义          一组能有效地组织和管理计算机硬件和软件资源,合理地对各类作业进行调度,以及方便用户使用的程序的集合 操作系统的基本类型及特征         基本类型:单道批处理系统、多道批处理系统、分时系统、实时系统、微机操作系统、嵌入式操作系统、网络操作系统、分布式操作系统 特征:   (1)单道批处理:自动性、顺序性、单道性   (2)多道批处理:多道、成批处理、无

《写给大家看的设计书》之什么是设计原则的亲密性_宇宙之一粟的漂泊之旅的博客-多极客编程

1 导读一开始在看《点石成金》,然后又发现这本豆瓣评分 8.6 的设计入门书。让我这样的小白能有机会学习设计师的相关知识。接下来会分几篇文章分享一下关于书籍的读后感,今天文章的内容是有关于作者总结的亲密性的设计原则。设计无处不在,著名的城市建筑、地铁中的海报,手机的外观、工作汇报的 PPT......然后并不是每个人都有机会加入艺术学院学习设计,但每个人都可以有设计的思维,都可以提高自身的审美。如