1. 云图网首页
  2. 技术专区
  3. 数据库

Something About Tuple ID

• 数据库

Tid 扫描(tuple ID scan) )很少用到，表的每一行记录都有一个唯一标记，这个标记名为 tuple ID, 当我们查询记录时，也可以将行的 tuple ID 标记查出来,例如以下

1
2
3
4
5
6
 
mydb=> select ctid,* from test_id limit 2;  
ctid | id | name  
-------+----+------  
(0,1) | 1 | a  
(0,2) | 2 | b  
(2 rows)

ctid 是一个很特殊的字段，它是自动生成的，ctid由两部分组成，第一部分是指 block的编号，第二部分是指这个BLOCK上的记录编号。

创建测试表

1
 
create table test_8 (id integer,remark varchar(32) );

插入10000条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE OR REPLACE FUNCTION skytf.fun_ins_test_1()  
RETURNS integer  
LANGUAGE plpgsql  
AS $function$  
 DECLARE
 i INTEGER ;  
 BEGIN  
 for i in 1..10000 loop  
 insert into test_8 values (1,'a');  
 end loop;  
 return 1;  
 END;  
 $function$  
  
mydb=> select skytf.fun_ins_test_1();  
fun_ins_test_1  
----------------  
 1  
(1 row)

表分析并查询统计信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
mydb=> analyze test_8;  
ANALYZE

mydb=> select relname,relpages,reltuples from pg_class where relname='test_8';  
relname | relpages | reltuples  
---------+----------+-----------  
test_8 | 45 | 10000  
(1 row)
  
mydb=> select 10000/45;  
?column?  
----------  
 222  
(1 row)

上面可以看出表test_8 占用了 45 个PAGE,每个pages含有约 222 条记录。查看最后300条记录的ctid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
 
mydb=> select ctid,id from test_8 offset 9700;  
 ctid | id  
----------+----  
(43,1) | 1  
(43,2) | 1  
(43,3) | 1  
(43,4) | 1  
(43,5) | 1  
(43,6) | 1  
(43,7) | 1  
(43,8) | 1  
(43,9) | 1  
(43,10) | 1  
(43,11) | 1  
(43,12) | 1  
(43,13) | 1  
(43,14) | 1  
(43,15) | 1  
(43,16) | 1  
(43,17) | 1  
(43,18) | 1  
(43,19) | 1  
(43,20) | 1  
(43,21) | 1  
(43,22) | 1  
(43,23) | 1  
(43,24) | 1  
(43,25) | 1  
(43,26) | 1  
(43,27) | 1  
(43,28) | 1  
(43,29) | 1  
(43,30) | 1  
(43,31) | 1  
(43,32) | 1  
(43,33) | 1  
(43,34) | 1  
(43,35) | 1  
(43,36) | 1  
(43,37) | 1  
(43,38) | 1  
(43,39) | 1  
(43,40) | 1  
(43,41) | 1  
(43,42) | 1  
(43,43) | 1  
(43,44) | 1  
(43,45) | 1  
(43,46) | 1  
(43,47) | 1  
(43,48) | 1  
(43,49) | 1  
(43,50) | 1  
(43,51) | 1  
(43,52) | 1  
(43,53) | 1  
(43,54) | 1  
(43,55) | 1  
(43,56) | 1  
(43,57) | 1  
(43,58) | 1  
(43,59) | 1  
(43,60) | 1  
(43,61) | 1  
(43,62) | 1  
(43,63) | 1  
(43,64) | 1  
(43,65) | 1  
(43,66) | 1  
(43,67) | 1  
(43,68) | 1  
(43,69) | 1  
(43,70) | 1  
(43,71) | 1  
(43,72) | 1  
(43,73) | 1  
(43,74) | 1  
(43,75) | 1  
(43,76) | 1  
(43,77) | 1  
(43,78) | 1  
(43,79) | 1  
(43,80) | 1  
(43,81) | 1  
(43,82) | 1  
(43,83) | 1  
(43,84) | 1  
(43,85) | 1  
(43,86) | 1  
(43,87) | 1  
(43,88) | 1  
(43,89) | 1  
(43,90) | 1  
(43,91) | 1  
(43,92) | 1  
(43,93) | 1  
(43,94) | 1  
(43,95) | 1  
(43,96) | 1  
(43,97) | 1  
(43,98) | 1  
(43,99) | 1  
(43,100) | 1  
(43,101) | 1  
(43,102) | 1  
(43,103) | 1  
(43,104) | 1  
(43,105) | 1  
(43,106) | 1  
(43,107) | 1  
(43,108) | 1  
(43,109) | 1  
(43,110) | 1  
(43,111) | 1  
(43,112) | 1  
(43,113) | 1  
(43,114) | 1  
(43,115) | 1  
(43,116) | 1  
(43,117) | 1  
(43,118) | 1  
(43,119) | 1  
(43,120) | 1  
(43,121) | 1  
(43,122) | 1  
(43,123) | 1  
(43,124) | 1  
(43,125) | 1  
(43,126) | 1  
(43,127) | 1  
(43,128) | 1  
(43,129) | 1  
(43,130) | 1  
(43,131) | 1  
(43,132) | 1  
(43,133) | 1  
(43,134) | 1  
(43,135) | 1  
(43,136) | 1  
(43,137) | 1  
(43,138) | 1  
(43,139) | 1  
(43,140) | 1  
(43,141) | 1  
(43,142) | 1  
(43,143) | 1  
(43,144) | 1  
(43,145) | 1  
(43,146) | 1  
(43,147) | 1  
(43,148) | 1  
(43,149) | 1  
(43,150) | 1  
(43,151) | 1  
(43,152) | 1  
(43,153) | 1  
(43,154) | 1  
(43,155) | 1  
(43,156) | 1  
(43,157) | 1  
(43,158) | 1  
(43,159) | 1  
(43,160) | 1  
(43,161) | 1  
(43,162) | 1  
(43,163) | 1  
(43,164) | 1  
(43,165) | 1  
(43,166) | 1  
(43,167) | 1  
(43,168) | 1  
(43,169) | 1  
(43,170) | 1  
(43,171) | 1  
(43,172) | 1  
(43,173) | 1  
(43,174) | 1  
(43,175) | 1  
(43,176) | 1  
(43,177) | 1  
(43,178) | 1  
(43,179) | 1  
(43,180) | 1  
(43,181) | 1  
(43,182) | 1  
(43,183) | 1  
(43,184) | 1  
(43,185) | 1  
(43,186) | 1  
(43,187) | 1  
(43,188) | 1  
(43,189) | 1  
(43,190) | 1  
(43,191) | 1  
(43,192) | 1  
(43,193) | 1  
(43,194) | 1  
(43,195) | 1  
(43,196) | 1  
(43,197) | 1  
(43,198) | 1  
(43,199) | 1  
(43,200) | 1  
(43,201) | 1  
(43,202) | 1  
(43,203) | 1  
(43,204) | 1  
(43,205) | 1  
(43,206) | 1  
(43,207) | 1  
(43,208) | 1  
(43,209) | 1  
(43,210) | 1  
(43,211) | 1  
(43,212) | 1  
(43,213) | 1  
(43,214) | 1  
(43,215) | 1  
(43,216) | 1  
(43,217) | 1  
(43,218) | 1  
(43,219) | 1  
(43,220) | 1  
(43,221) | 1  
(43,222) | 1  
(43,223) | 1  
(43,224) | 1  
(43,225) | 1  
(43,226) | 1  
(44,1) | 1  
(44,2) | 1  
(44,3) | 1  
(44,4) | 1  
(44,5) | 1  
(44,6) | 1  
(44,7) | 1  
(44,8) | 1  
(44,9) | 1  
(44,10) | 1  
(44,11) | 1  
(44,12) | 1  
(44,13) | 1  
(44,14) | 1  
(44,15) | 1  
(44,16) | 1  
(44,17) | 1  
(44,18) | 1  
(44,19) | 1  
(44,20) | 1  
(44,21) | 1  
(44,22) | 1  
(44,23) | 1  
(44,24) | 1  
(44,25) | 1  
(44,26) | 1  
(44,27) | 1  
(44,28) | 1  
(44,29) | 1  
(44,30) | 1  
(44,31) | 1  
(44,32) | 1  
(44,33) | 1  
(44,34) | 1  
(44,35) | 1  
(44,36) | 1  
(44,37) | 1  
(44,38) | 1  
(44,39) | 1  
(44,40) | 1  
(44,41) | 1  
(44,42) | 1  
(44,43) | 1  
(44,44) | 1  
(44,45) | 1  
(44,46) | 1  
(44,47) | 1  
(44,48) | 1  
(44,49) | 1  
(44,50) | 1  
(44,51) | 1  
(44,52) | 1  
(44,53) | 1  
(44,54) | 1  
(44,55) | 1  
(44,56) | 1  
(300 rows)

到这里，大家可以清楚的看到TID的含义，TID的第一列为BLOCK的编号，第二列为这个BLOCK上的记录编号，并注意表 TEST_8的最后一条记录的TID为 (44,56),现在我们接着做个实验，更新全表的 remark字段，看下会发生什么情况。

更新记录并查看表占用PAGE

1
2
3
4
5
6
7
8
9
10
 
mydb=> update test_8 set remark='b';  
UPDATE 10000

mydb=> analyze test_8;  
ANALYZE

mydb=> select relname,relpages,reltuples from pg_class where relname='test_8';  
relname | relpages | reltuples  
---------+----------+-----------  
test_8 | 89 | 10000

大家看到，test_8 占用的 pages数由原来的44，上升到现在的89, 刚好翻了一倍

再次查询TID

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
mydb=> select ctid,id from test_8 order by ctid limit 250 ;  
 ctid | id  
----------+----  
(44,57) | 1  
(44,58) | 1  
(44,59) | 1  
(44,60) | 1  
(44,61) | 1  
(44,62) | 1  
(44,63) | 1  
(44,64) | 1  
(44,65) | 1  
(44,66) | 1  
(44,67) | 1  
(44,68) | 1  
(44,69) | 1  
(44,70) | 1  
(44,71) | 1  
(44,72) | 1  
(44,73) | 1  
(44,74) | 1  
(44,75) | 1  
(44,76) | 1  
(44,77) | 1  
(44,78) | 1  
(44,79) | 1  
(44,80) | 1  
(44,81) | 1  
(44,82) | 1  
(44,83) | 1  
(44,84) | 1  
(44,85) | 1  
(44,86) | 1  
(44,87) | 1  
...

上面省略了部分结果集, 注意第一条 (44,57) , 以及上一个步骤的最后一条(44,56), 惊奇的发现，update后，PG保留了原有数据，而是在物理存储上顺序接着往下写下新的数据。

TID 查询的执行计划

1
2
3
4
5
6
7
 
mydb=> explain analyze select ctid, id from test_8 where ctid ='(44,87)';  
 QUERY PLAN  
-------------------------------------------------------------------------------------------------  
Tid Scan on test_8 (cost=0.00..4.01 rows=1 width=10) (actual time=0.147..0.158 rows=1 loops=1)  
 TID Cond: (ctid = '(44,87)'::tid)  
Total runtime: 0.272 ms  
(3 rows)

总结

TID是每张表的隐含列，它指定了记录行在存储上的物理位置，TID SCAN 能很快的检索数据，因为它根据物理文件的bloack号和记录编号可以直接读取数据。

原创文章，作者：6024010，如若转载，请注明出处：https://blog.ytso.com/236344.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论