postgresql之ctid的浅谈

select ctid,* from test; ctid | id | name -------+------+-------- (0,1) | 1001 | lottu (0,2) | 1002 | rax (0,3) | 1003 | xuan (0,4) | 1004 | li0924 (0,5) | 1001 | ak     格式(blockid,itemid)：拿其中(0,1)来说；0表示块id；1表示在这块第一条记录。     1. 去重：  我们知道rowid在oracle有个重要的作用；被用作表记录去重；同理 ctid在postgresql里面同样可以使用。例如test表id为1001有两条记录；现在演示下；

mydb<span style="color: #808080;">=></span> <span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span> test <span style="color: #0000ff;">where</span> ctid <span style="color: #808080;">not</span> <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">min</span>(ctid) <span style="color: #0000ff;">from</span> test <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> id);
</span><span style="color: #0000ff;">DELETE</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
mydb</span><span style="color: #808080;">=></span> <span style="color: #0000ff;">select</span> ctid,<span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> test;
 ctid  </span><span style="color: #808080;">|</span>  id  <span style="color: #808080;">|</span><span style="color: #000000;">  name  
</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+------+--------</span>
 (<span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1001</span> <span style="color: #808080;">|</span><span style="color: #000000;"> lottu
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">2</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1002</span> <span style="color: #808080;">|</span><span style="color: #000000;"> rax
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">3</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1003</span> <span style="color: #808080;">|</span><span style="color: #000000;"> xuan
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">4</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1004</span> <span style="color: #808080;">|</span><span style="color: #000000;"> li0924
(</span><span style="color: #800000; font-weight: bold;">4</span> rows)

刚刚我们删除了(0,5)这条记录； 现在我们把这条记录插入下；看下；

mydb<span style="color: #808080;">=></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> test <span style="color: #0000ff;">values</span> (<span style="color: #800000; font-weight: bold;">1001</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">ak</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
mydb</span><span style="color: #808080;">=></span> <span style="color: #0000ff;">select</span> ctid,<span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> test;
 ctid  </span><span style="color: #808080;">|</span>  id  <span style="color: #808080;">|</span><span style="color: #000000;">  name  
</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+------+--------</span>
 (<span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1001</span> <span style="color: #808080;">|</span><span style="color: #000000;"> lottu
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">2</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1002</span> <span style="color: #808080;">|</span><span style="color: #000000;"> rax
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">3</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1003</span> <span style="color: #808080;">|</span><span style="color: #000000;"> xuan
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">4</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1004</span> <span style="color: #808080;">|</span><span style="color: #000000;"> li0924
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">6</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1001</span> <span style="color: #808080;">|</span><span style="color: #000000;"> ak
(</span><span style="color: #800000; font-weight: bold;">5</span> rows)

奇怪了；为什么不是(0,5),而是(0,6)这个跟postgresql多版本事务有关；跟伪列cmin，cmax有关；跟本文讲的ctid没点关系；这是postgresql的特性；也就是这样；postgresql里面没有回滚段的概念；那怎么把(0,5)在显示呢；想这块(0,5)的空间再存放数据；postgresql里面有AUTOVACUUM进程；当然我们也可以手动回收这段空间；

mydb<span style="color: #808080;">=></span> <span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span> test <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">ak</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">DELETE</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
mydb</span><span style="color: #808080;">=></span><span style="color: #000000;"> vacuum test;          
VACUUM
mydb</span><span style="color: #808080;">=></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> test <span style="color: #0000ff;">values</span> (<span style="color: #800000; font-weight: bold;">1001</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">ak</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
mydb</span><span style="color: #808080;">=></span> <span style="color: #0000ff;">select</span> ctid,<span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> test;
 ctid  </span><span style="color: #808080;">|</span>  id  <span style="color: #808080;">|</span><span style="color: #000000;">  name  
</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+------+--------</span>
 (<span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1001</span> <span style="color: #808080;">|</span><span style="color: #000000;"> lottu
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">2</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1002</span> <span style="color: #808080;">|</span><span style="color: #000000;"> rax
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">3</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1003</span> <span style="color: #808080;">|</span><span style="color: #000000;"> xuan
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">4</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1004</span> <span style="color: #808080;">|</span><span style="color: #000000;"> li0924
 (</span><span style="color: #800000; font-weight: bold;">0</span>,<span style="color: #800000; font-weight: bold;">5</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1001</span> <span style="color: #808080;">|</span><span style="color: #000000;"> ak
(</span><span style="color: #800000; font-weight: bold;">5</span> rows)

2. 我们刚刚说道 0表示块id； test数据太少了；不好解释；新建一个表test2

mydb<span style="color: #808080;">=></span> <span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> test2;
</span><span style="color: #0000ff;">DROP</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;">
mydb</span><span style="color: #808080;">=></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> test2 (id <span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span>, name <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">));
</span><span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;">
mydb</span><span style="color: #808080;">=></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> test2 <span style="color: #0000ff;">select</span> generate_series(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1000</span>),<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">lottu</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> generate_series(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #800000; font-weight: bold;">1000</span>

 我们看下id=1000的ctid的blockid是多少；答案是5；意思是说该表的记录记录到第6个块；（因为是从0开始的）

mydb<span style="color: #808080;">=></span> <span style="color: #0000ff;">select</span> ctid,<span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> test2 <span style="color: #0000ff;">where</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">;
  ctid  </span><span style="color: #808080;">|</span>  id  <span style="color: #808080;">|</span><span style="color: #000000;">   name    
</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+-----------</span>
 (<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">75</span>) <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1000</span> <span style="color: #808080;">|</span><span style="color: #000000;"> lottu1000
(</span><span style="color: #800000; font-weight: bold;">1</span> row)

当然这样查表记录占了几个block；假如我这是随机插入的；那id=1000；就不一定是在第6块； 我们可以借助系统视图pg_class；其中relpages,reltuples分别代表块数，记录数！

mydb<span style="color: #808080;">=></span><span style="color: #000000;"> analyze test2;
ANALYZE
mydb</span><span style="color: #808080;">=></span> <span style="color: #0000ff;">select</span> relpages,reltuples <span style="color: #0000ff;">from</span> pg_class <span style="color: #0000ff;">where</span> relname <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test2</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
 relpages </span><span style="color: #808080;">|</span><span style="color: #000000;"> reltuples 
</span><span style="color: #008080;">--</span><span style="color: #008080;">--------+-----------</span>
        <span style="color: #800000; font-weight: bold;">6</span> <span style="color: #808080;">|</span>      <span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">
(</span><span style="color: #800000; font-weight: bold;">1</span> row)

总结： ctid存在的意义：表示数据记录的物理行当信息，指的是 一条记录位于哪个数据块的哪个位移上面。 跟oracle中伪列 rowid 的意义一样的；只是形式不一样。 vacuum: 回收未显示的物理位置；标明可以继续使用。 generate_series: 为一个序列函数；例如1-100；就是generate_series(1,100);0-100直接的偶数generate_series(0,100,2)                           其中的0表示序列开始位置；100代表结束位置；2为偏移量。

postgresql之ctid的浅谈

标签：