POSTGRESQL 的数据扫描,其实和其他的数据库也无差,无非就是数据块的扫描以及索引的扫描,这里POSTGRESQL 数据扫描也叫 TUPLE SCAN。
在POSTGRESQL 8.3 版本后再HEAP 表的修改中,有一个概念叫 HOT, 通过新的概念提高了堆表的性能,减少了I/O。 早起的POSTGRESQL 更新的方式是修改索引中的数据,主要是由于MVCC 多版本控制中UPDATE 一行数据后,是需要在索引中重新POINT,将原有INDEX的数据废弃,这加大了I/O的操作。
目前的POSTGRESQL 早已放弃这样的操作,当UPDATE 一行,如果可以POSTGRES将在原有的老的数据后添加新的COPY,并且POSTGRESQL 的存储会标记,让索引知晓老数据后是更新的数据。这样POSTGRES 就避免了在更新整个INDEX ,使得UPDATE 后的数据不在修改INDEX,减少I/O。
另外这里可能有同学要问,如果old tuple 被清除了,数据链不就断掉了,事实上是不会的,只需要将页面的HEADER 中的 1 重新定向到 2 即可,最大化的减小I/O 是优化的目的。
但有两种情况下是不能使用 HOT 功能的
1 更新后的数据已经跨页,那是没有办法继续使用刚才的方法,所以乱设计本来 CHAR(10) 可以解决的,非要 CHAR(400) 这就出现问题
2 更新查询的 KEY 值
例如 UPDATE A SET COL = 'A' WHERE COL = ‘B’ 这也是没有办法使用HOT 功能的,所以在大量更新KEY 本身的时候,速度相对非 KEY 来说,一定会比较慢。当然也有办法,很简单,你一定能想得到。
这里POSTGRESQL 还有一个和其他数据库不大一样的地方,就是如果想使用 符合索引(ORALCE MYSQL) 或者 INCLUDE INDEX (SQL SERVER)方式来不进行 回表访问数据,按照原理,我们只需要将需要访问的数据,包含在INDEX 中即可。
但是在POSTGRESQL 中即使你的索引已经包含了数据,他还有会回表检测数据的可见性(这和他的原理有关,暂且不谈),那这样的设计就有点反人类了,POSTGRESQL 则使用了一个 叫 visibility map 的东西,来避免回表的操作,将数据是否可见直接记录在 visibility map 中,只要记录可见,则索引直接反馈数据,否则还要回表检查。
并且最近对数据库底层的存储了解后,发现每种数据库可能不擅长的场景
例如 在数据库底层上 (SQL SERVER MYSQL ) 都有聚簇索引的概念,也就是说,如果是RANGE 的提取数据,并且这个表是按照 SQL SERVER MYSQL 底层的存储结构来设计表,则原理上可以比 ORACLE 和 POSTGRESQL (相对来说,POSTGRESQL 的底层设计,在RANGE 上应该也比ORACLE 要强,同时随机的读取的速度也不会慢,) 这样的堆表设计的数据库要更容易提取数据,速度更快(如果你使用 更先进的的SSD 或者 PCI-E 卡)或者你提取的数据不够多,可能并不能有明显的体现。但原理上一次寻道就能获取连续的存储的数据,要比多次寻道,获取散列的数据要好的多。相反 如果提取的数据是无顺序的,则ORACLE 和 PG 效率可能要高过 MYSQL 和 SQL SERVER (相对SQL SERVER 和 MYSQL 也有不同,相对 SQL SERVER 的随机提取数据的能力要好于 MYSQL)。并且在数据表文件的生成上,pg和mysql一个表一个或多个文件,SQL SERVER 和 oracle 多个表占用一个表空间,当然如果你不怕麻烦也可以一个表一个表空间一个表多个文件,但我想没有一个sql server 和 Oracle的DBA会认同你这样的做法,你这样会让这两位彻底的疯掉。
(仅个人观点,欢迎指正)