Skip to main content

moregeek program

pg中的index-only scans解密_mysql dba攻坚之路的博客-多极客编程

PG索引的多版本实现

可见性映射表

在PostgreSQL数据库中,索引上并没有多版本信息,因此即使SELECT的列都是索引列,在PostgreSQL 9.2之前的版本中还是需要再到表上去查询一次,但在PostgreSQL 9.2版本之后,该查询就可以省略了。这种扫描方式被称为“Index-Only Scans”。实际上,9.2版本也没有在索引中添加多版本信息,那它是如何实现该功能的呢?它是靠使用可见性映射表(Visibility Map)来实现这一功能的。可见性映射表文件中记录了一个数据块(大小通常是8KB)中的行是否对全部事务可见(当然还存在数据块中的行对部分事务可见和部分不可见的情况),如果对全事务可见,则PostgreSQL不需要通过访问表中的行来做可见性判断了。


下面用一个示例来说明PostgreSQL 9.2版本之后的Index-Only Scans与之前版本中的不同。首先在PostgreSQL 8.4版本和PostgreSQL 9.3中同时建一个测试表,命令如下:

CREATE TABLE test01(id int primary key, note text);

insert into test01 select generate_series(1,100000), 'testcontent';


postgres=# explain select id from test01 where id>10 and id<20;
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using test01_pkey on test01 (cost=0.29..8.47 rows=9 width=4)
Index Cond: ((id > 10) AND (id < 20))

我们可以看到,上面的执行计划中的“Index Scan”被换成了“Index Only Scan”。


Index Only Scan

如果数据块中的行对部分事务可见,部分不可见,就不能仅通过索引来获得数据了,还需要读取数据块,但此时执行计划中仍然显示为“Index Only Scan”,因此当看到执行计划中有“Index Only Scan”这几个词时,并不表示仅读取索引就可以了,这几个词或许改成“Index Mostly Scans”更合适一些。要查看一个SQL的“Index Only Scan”实际上访问了多少数据块中的行,可以在EXPLAIN语句中加上“ANALYZE”,让其实际执行一下,然后查看其执行结果,命令如下:

postgres=# explain select id from test01 where id>10 and id<20;
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using test01_pkey on test01 (cost=0.29..8.47 rows=9 width=4)
Index Cond: ((id > 10) AND (id < 20))
(2 rows)

postgres=# explain analyze select id from test01 where id>10 and id<20;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Only Scan using test01_pkey on test01 (cost=0.29..8.47 rows=9 width=4) (actual time=0.012..0.014 rows=9 loops=1)
Index Cond: ((id > 10) AND (id < 20))
Heap Fetches: 9
Planning Time: 0.099 ms
Execution Time: 0.032 ms
(5 rows)

从上面的执行计划中可以看到“Heap Fetches:9”,说明从数据块中读取了9行,这实际上是数据块中所有的行,说明“Index Only Scan”基本没有起作用。

可见性事务

要衡量一个表的“Index Only Scan”的效果,关键在于这个表的数据块有多少对所有事务可见。该信息可以在pg_class中查询,命令如下:

postgres=# select relname,relpages,relallvisible from pg_class where relname=
postgres-# 'test01';
relname | relpages | relallvisible
---------+----------+---------------
test01 | 541 | 0
(1 row)

从上面的查询可以知道,该测试表的“relallvisiable”是“0”,所以“Index Only Scan”没起到任何优化效果。这时可以VACUUM一个表,命令如下

VACUUM达到效果


postgres=# vacuum test01;
VACUUM
postgres=# select relname,relpages,relallvisible from pg_class where relname='test01';
relname | relpages | relallvisible
---------+----------+---------------
test01 | 541 | 541
(1 row)

postgres=#
postgres=# explain analyze select id from test01 where id>10 and id <20;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Only Scan using test01_pkey on test01 (cost=0.29..4.47 rows=9 width=4) (actual time=0.008..0.010 rows=9 loops=1)
Index Cond: ((id > 10) AND (id < 20))
Heap Fetches: 0
Planning Time: 0.151 ms
Execution Time: 0.024 ms
(5 rows)

从上面的运行结果中可以看到“Heap Fetches:0”,即从表的数据文件中读取的行数为0,说明所有的数据都直接从索引中得到了。

©著作权归作者所有:来自51CTO博客作者进击的CJR的原创作品,请联系作者获取转载授权,否则将追究法律责任
PG中的Index-Only Scans解密
https://blog.51cto.com/u_13874232/5564506

sql优化最强总结 (建议收藏~)_码农小宋的博客-多极客编程

有朋友疑问到,SQL优化真的有这么重要么?如下图所示,SQL优化在提升系统性能中是:(成本最低 && 优化效果最明显) 的途径。如果你的团队在SQL优化这方面搞得很优秀,对你们整个大型系统可用性方面无疑是一个质的跨越,真的能让你们老板省下不止几沓子钱。优化成本:硬件>系统配置>数据库表结构>SQL及索引。优化效果:硬件<系统配置<数据库表结构<S

学长告诉我,大厂mysql都是通过ssh连接的_mb5d53cebbf1a51的博客-多极客编程

大家好,我是咔咔 ​​不期速成,日拱一卒​​一、背景之前待的几个公司,数据库、服务器权限都是给所有后端直接拉满的,但也会出现员工离职的情况,每次有人离职时都需要改数据库密码、服务器密码。每次密码修改后得告知所有开发修改本地密码,但这样的事情也不是经常发生,公司虽小但很稳定。假设你所待的公司是一个开发非常多的公司,有可能你待了一年还没认识全,人员流动的速度也非常快,这时上面那种方案的执行成本就非常高

redis上云迁移实践_陌路,尽头的博客-多极客编程

简介随着云产品的完善,大多数公司已经开始使用云产品,云产品的确能够解决很多基础架构上的便利,提高业务架构的稳定性,如何将Redis数据在线迁移到云上,并且保证业务的无损,从迁移方案和工具选择必须要考虑如下几点:兼容性:Redis社区版向下是兼容的,在上云的过程中,尽量保持大版本一致,以免版本不一致导致客户端不兼容的问题迁移工具:充分考虑工具的功能性的同时,也要重点考虑便利和可运维性,工具需要有完整

mysql数据库一周基础入门(第三天)_mb62f096f9846ae的博客-多极客编程

删除表中的数据 drop database 库名 # 删除一个数据库drop table 表名 # 删除一个表delete from 表名 # 删除表中的数据【全部数据】delete from 表名 where 条件 # 删除表中的数据【仅仅删除符合条件的数据】truncate 表名 # 删除表中的数据,而且会截断表截断表表的主键可能会自增,如果用delete删除表中的数据后,主键依然会在前面的

pg--核心技术--hot_mysql dba攻坚之路的博客-多极客编程

索引更新问题因为多版本的原因,当PostgreSQL中更新一行时,实际上原数据行并不会被删除,只是插入了一个新行。如果表上有索引,而更新的字段不是索引的键值时,由于新行的物理位置发生了变化,仍然需要更新索引,这将导致性能下降。引入HOT为了解决这一问题,PostgreSQL自8.3版本之后引入了一个名为“Heap-Only Tuple”的新技术,简称HOT。使用HOT技术之后,如果更新后的行与原数

controller层代码这么写,简洁又优雅!_码农小宋的博客-多极客编程

说到 Controller,相信大家都不陌生,它可以很方便地对外提供数据接口。它的定位,我认为是「不可或缺的配角」。说它不可或缺是因为无论是传统的三层架构还是现在的 COLA 架构,Controller 层依旧有一席之地,说明他的必要性。说它是配角是因为 Controller 层的代码一般是不负责具体的逻辑业务逻辑实现,但是它负责接收和响应请求。从现状看问题Controller 主要的工作有以下几

web安全入门-kill chain测试流程_最爱大苹果的博客-多极客编程

Kill Chain简介网络杀伤链是一种概述网络攻击各个阶段的安全模型。杀伤链涵盖了网络入侵的所有阶段,从早期计划和监视到黑客的最终目标。 了解攻击的各个阶段使公司能够规划预防和检测恶意入侵者的策略。七大流程包含7大步骤1、侦察跟踪侦察分为两种: 被动侦察: 黑客在不与目标交互的情况下搜索信息。受害者无法知道或记录攻击者的活动。 主动侦察: 黑客未经授权访问网络并直接与系统接触以收集信息。防御措施

态路小课堂丨如何为cxp光模块选择光纤跳线?_小透明popo的博客-多极客编程

最近有些客户在为CXP光模块选择跳线时不知选择什么类型光纤跳线。Q:买1条3米24芯MPO跳线,CXP转10G光模块使用。A:请问是连接10个10G光模块还是12个10G光模块?Q:10个10G光模块。A:嗯嗯,好的。产品确认:TL-MPO/F-LC/UPC-3-OM3-20-3L-2L本文来介绍关于CXP光模块以及它的几种应用。问​什么是CXP光模块??CXP光模块是由InfiniBand行业联

虚拟电厂可视化大屏,深挖痛点精准减碳 _图扑-数字孪生的博客-多极客编程

国网河北电力发布的《数字孪生电网白皮书》中提到:随着“碳达峰、碳中和”“构建以新能源为主体的新型电力系统”等战略目标的提出,电网将会接入大量具有随机性、间歇性、波动性特征的分布式能源,以及储能装置、V2G 等交互式能源设施,使其呈现出结构更加复杂、设备更加繁多、技术更加庞杂的趋势。近年来,随着能源互联网建设进程的加快,已催生一系列智慧能源成果。虚拟电厂(Virtual Power Plants,

幕维三维动画——港珠澳大桥沉管安装三维动画实况_wx5c53fb8c28e25的博客-多极客编程

港珠澳大桥是超级工程,构建大桥海底隧道的33节沉管,每节超过100米长,四层楼高,隧道内宽可达到双向六车道,绝对是沉管中的巨无霸。港珠澳大桥海底隧道是我国第一条外海沉管隧道,也是世界最长的公路沉管隧道和唯一深埋沉管隧道,被誉为交通工程的“珠穆朗玛峰”。2013年5月6日,港珠澳大桥西人工岛与首节隧道沉管完美实现首次对接,这被誉为“深海初吻”,难度系数堪比“天宫一号”对接。为了精准呈现港珠澳大桥”海

web技术支持| 基于vue3实现自己的组件库第三章:checkbox组件_mb60af473914346的博客-多极客编程

大家好今天的内容是基于vue3实现自己的组件库系列第二章,本文默认你会安装和创建vue3项目,如果不会请参考vue官网; Checkbox.vue Template <template> <div :class='["v-checkbox", { border }, { disabled: proxyDisabled || disabled || compute

用好 div 和 api,在前端系统中轻松嵌入数据分析模块_葡萄城技术团队的博客-多极客编程

在数字化转型潮流席卷各大行业的今天,越来越多的企业开始重视 BI(商业智能)技术的部署和应用,期望从不断增长的数据资源中获得更多业务价值,从而提升利润、控制风险、降低成本。BI 能整合、组织和分析数据,将数据转化为有价值的信息,为企业管理和决策提供支持,成为企业迎接变革和商业创新的决胜因素。 由于 BI 技术的重要性,企业更希望在现有的业务平台和系统中按需集成BI能力,从而在各类场景中充分发挥数据