POSTGRESQL UPDATE 如何提高I/O 能力

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会认同你这样的做法，你这样会让这两位彻底的疯掉。

（仅个人观点，欢迎指正）