「YashanDB个人版体验」崖山数据库 - 段的分配方式是怎样的？

Roger的数据库专栏 2023-11-22

419

前面已经简单写了几篇关于崖山数据库的文章，引起了不少人的兴趣。作为一款横空出世的数据库软件，除了看官方宣传，实际上我更喜欢去探究一下数据库的底层逻辑，以此来判断这个数据库的优良。这里我们继续看看该数据库对于段的分配是怎么样的。首先我们来看下表空间的情况：

SQL> select name,status,ALLOCATION_TYPE,MEMORY_MAPPED,ENCRYPTED,COMPRESSED
   2 from  v$tablespace;


NAME       STATUS            ALLOCATION_TYPE MEMORY_MAPPED ENCRYPTED COMPRESSED 
---------- ----------------- --------------- ------------- --------- ---------- 
SYSTEM     ONLINE            AUTO            FALSE         FALSE     FALSE     
SYSAUX     ONLINE            AUTO            FALSE         FALSE     FALSE     
TEMP       ONLINE            UNIFORM         FALSE         FALSE     FALSE     
SWAP       ONLINE            UNIFORM         FALSE         FALSE     FALSE     
USERS      ONLINE            AUTO            FALSE         FALSE     FALSE     
UNDO       ONLINE            UNIFORM         FALSE         FALSE     FALSE     


6 rows fetched.

从表空间的分配方式来看主要有2种，分别是auto和uniform。这与Oracle数据库极其类似；当然崖山看上去有些新技术memory map，后续在研究。接下来我们创建一个测试表来简单观察一下。

SQL> create table t21 as select * from sys.dba_objects where 1=2;


Succeed.


SQL> insert into t21 select * from sys.dba_objects where rownum < 200;


199 rows affected.


SQL> commit;


Succeed.


SQL> select owner,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS
   2 from dba_segments where segment_name=upper('t21');


OWNER       SEGMENT_NAME    SEGMENT_TYPE          TABLESPACE_NAME     HEADER_FILE HEADER_BLOCK   BLOCKS 
----------- --------------- --------------------- ------------------ ------------ ------------ -------- 
ENMO        T21             TABLE                 USERS                         0       114739        8


1 row fetched.




SQL> select rowid from t21 where rownum < 3;


ROWID                                        
-------------------------------------------- 
2302:4:0:114740:0                           
2302:4:0:114740:1                           


2 rows fetched.


SQL> select distinct substr(rowid,10,6)  from t21 order by 1;


SUBSTR(ROWID,10,6)                            
--------------------------------------------- 
114740                                       
114741                                       
114742                                       


3 rows fetched.


SQL>  select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS,EXTENTS from dba_segments where segment_name=upper('t21');


TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK                BLOCKS               EXTENTS 
-------------------- --------------------- ------------ --------------------- --------------------- 
USERS                                    0       114739                     8                     1


1 row fetched.

这里简单模拟插入了一点数据，可以看到仅仅分配了一个extent，看上去默认似乎是8个block，由于我这里block size是8k，那么也就是说初始化extent size是64k。既然如此，这里为重复插入数据，模拟了大概7000万条数据，此时来看该段的分配情况。

SQL>  select count(1) from t21;


             COUNT(1) 
--------------------- 
             70500352


1 row fetched.


SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T21';


SUM(BYTES/1024/1024) 
-------------------- 
            7231.625


1 row fetched.


SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS,EXTENTS from dba_segments where segment_name=upper('t21');


TABLESPACE_NAME                 HEADER_FILE HEADER_BLOCK                BLOCKS               EXTENTS 
--------------------- --------------------- ------------ --------------------- --------------------- 
USERS                                     0       114739                925648                   403


1 row fetched.

看了下文档，崖山支持对block或者logfile进行dump，那么这里通过dump block来观察一下。首先我们来dump 段头block：

*** 2023-11-21 20:08:27.542
dump type : datafile blocks
size : 8192bytes


block: 114739
segment
head: id 0-114739, type 33, instance id 0 lsn 341251, checksum: 0, change num 1557, isEncrypted 0, isCompressed 0
segType: heap 
dataOid: 2302
ssm tree: search entry: 0-114738, level: 2 
level[0]: ssm block count: 1390,  current block: 0-1032199 
level[1]: ssm block count: 2,  current block: 0-647176 
level[2]: ssm block count: 1,  current block: 0-114738 
hwm L1: 0-1032199 
hwm L1 node: 1023 
extents lhwm: block id: 0-581632, extent idx: 346, map block: 0-114739, offset: 346, blkIdx: 8192, used block count: 466896. lhwmL1: 0-573447
extent ctrl: extent count: 403, block count: 925648, map count: 0 , extent map offset: 384
last map block: 63-67108863, offset: 402, extents change number: 0
extents hwm: block id: 0-1040384, extent idx: 402, offset: 402, unformatted block count: 0, used block count: 925648, map block: 63-67108863 
extents map: extent count: 403, capacity: 487, next map: 63-67108863, map nodes:
id:     0-114736     size: 8     
id:     0-114744     size: 8     
id:     0-114752     size: 8     
id:     0-114760     size: 8     
id:     0-114768     size: 8     
id:     0-114776     size: 8     
id:     0-114784     size: 8     
id:     0-114792     size: 8     
id:     0-114800     size: 8     
id:     0-114808     size: 8     
id:     0-114816     size: 8     
id:     0-114824     size: 8     
id:     0-114832     size: 8     
id:     0-114840     size: 8     
id:     0-114848     size: 8     
id:     0-114856     size: 8     
id:     0-114864     size: 128   
id:     0-114992     size: 128   
......
id:     0-122544     size: 128   
id:     0-122672     size: 128   
id:     0-122800     size: 8     
id:     0-122808     size: 8     
id:     0-122816     size: 8     
id:     0-122824     size: 8     
id:     0-122832     size: 8     
id:     0-122840     size: 8     
id:     0-122848     size: 8     
id:     0-122856     size: 8     
id:     0-122864     size: 8     
id:     0-122872     size: 8     
id:     0-122880     size: 128   
id:     0-123008     size: 128   
id:     0-123136     size: 128   
......
id:     0-130944     size: 128   
id:     0-131072     size: 128   
id:     0-131200     size: 1024  
id:     0-132224     size: 1024  
id:     0-133248     size: 1024  
id:     0-134272     size: 1024  
id:     0-135296     size: 1024  
id:     0-136320     size: 1024  
id:     0-137344     size: 1024  
id:     0-138368     size: 128   
id:     0-138496     size: 128   
id:     0-138624     size: 128   
id:     0-138752     size: 128   
id:     0-138880     size: 128   
id:     0-139008     size: 128   
id:     0-139136     size: 128   
id:     0-139264     size: 1024  
id:     0-140288     size: 1024  
......
id:     0-249856     size: 1024  
id:     0-250880     size: 1024  
id:     0-251904     size: 1024  
id:     0-252928     size: 1024  
id:     0-253952     size: 8192  
id:     0-262144     size: 8192  
id:     0-270336     size: 8192  
......
id:     0-483328     size: 8192  
id:     0-491520     size: 8192  
id:     0-499712     size: 8192  
id:     0-507904     size: 8192  
id:     0-516096     size: 1024  
id:     0-517120     size: 1024  
id:     0-518144     size: 1024  
id:     0-519168     size: 1024  
id:     0-520192     size: 1024  
id:     0-521856     size: 1024  
id:     0-522880     size: 1024  
id:     0-521216     size: 128   
id:     0-521344     size: 128   
id:     0-521472     size: 128   
id:     0-521600     size: 128   
id:     0-521728     size: 128   
id:     0-523904     size: 128   
id:     0-524032     size: 128   
id:     0-524160     size: 128   
id:     0-524928     size: 1024  
id:     0-525952     size: 1024  
id:     0-526976     size: 1024  
id:     0-528000     size: 1024  
id:     0-529024     size: 1024  
id:     0-530048     size: 1024  
id:     0-531072     size: 1024  
id:     0-524288     size: 128   
id:     0-524416     size: 128   
id:     0-524544     size: 128   
id:     0-524672     size: 128   
id:     0-524800     size: 128   
id:     0-532096     size: 128   
id:     0-532224     size: 128   
id:     0-532352     size: 128   
id:     0-532480     size: 8192   
......
id:     0-1007616    size: 8192  
id:     0-1015808    size: 8192  
id:     0-1024000    size: 8192  
id:     0-1032192    size: 8192  


0. L1 block id:     0-114736     data block id:     0-114740    
1. L1 block id:     0-114736     data block id:     0-114744    
2. L1 block id:     0-114752     data block id:     0-114753    
3. L1 block id:     0-114752     data block id:     0-114760    
4. L1 block id:     0-114768     data block id:     0-114769    
5. L1 block id:     0-114768     data block id:     0-114776    
6. L1 block id:     0-114784     data block id:     0-114785    
7. L1 block id:     0-114784     data block id:     0-114792    
8. L1 block id:     0-114800     data block id:     0-114801    
......    
398. L1 block id:     0-999424     data block id:     0-999432    
399. L1 block id:     0-1007616    data block id:     0-1007624   
400. L1 block id:     0-1015808    data block id:     0-1015816   
401. L1 block id:     0-1024000    data block id:     0-1024008   
402. L1 block id:     0-1032192    data block id:     0-1032200   
tail: change num: 1557

大家看上面的dump 是不是感觉有点熟悉的味道。没错，就是有点像Oracle，看上去也有hwm高水位、L1位图block、extent control、extent map 、ssm tree（似乎类似Oracle的free block pool）等结构。

除此之外，我们还可以发现，对于段每次分配的extent大小，看上去单位可以是：64k,1M,8M,64M;似乎最大就是64Mb.

同时没有对L1 block一样进行dump，然后进行观察：

dump type : datafile blocks
size : 8192bytes


block: 114752
assm
head: id 0-114752, type 32, instance id 0 lsn 229578, checksum: 0, change num 35, isEncrypted 0, isCompressed 0
dataOid: 2302, extent change number: 0  parent block: 0-114737, slot: 1, freeness: 0, capacity: 1024, count: 16, level: 0
freeness[1]: count: 0: 
freeness[2]: count: 0: 
freeness[3]: count: 0: 
freeness[4]: count: 0: 
freeness[5]: count: 0: 
freeness[6]: count: 0: 
unformat: 0, lastActiveTime: 2023-11-21 11:17:50, instanceId: 0 firstBlock: 1, firstFreeBlock: 0, nranges: 2,
range index: 0, range length: 8, start bid:    0-114752
0. block:    0-114752  freeness: 0, xo: -1    1. block:    0-114753  freeness: 0, xo: -1    2. block:    0-114754  freeness: 0, xo: -1    3. block:    0-114755  freeness: 0, xo: -1    
4. block:    0-114756  freeness: 0, xo: -1    5. block:    0-114757  freeness: 0, xo: -1    6. block:    0-114758  freeness: 0, xo: -1    7. block:    0-114759  freeness: 0, xo: -1    
range index: 1, range length: 8, start bid:    0-114760
0. block:    0-114760  freeness: 0, xo: -1    1. block:    0-114761  freeness: 0, xo: -1    2. block:    0-114762  freeness: 0, xo: -1    3. block:    0-114763  freeness: 0, xo: -1    
4. block:    0-114764  freeness: 0, xo: -1    5. block:    0-114765  freeness: 0, xo: -1    6. block:    0-114766  freeness: 0, xo: -1    7. block:    0-114767  freeness: 0, xo: -1    


xid count: 0, xid offset count: 0: 
xid list: 
0. NULL 1. NULL 2. NULL 3. NULL 
4. NULL 5. NULL 6. NULL 7. NULL 
8. NULL 9. NULL 10. NULL 11. NULL 
12. NULL 13. NULL 14. NULL 15. NULL 
16. NULL 17. NULL 18. NULL 19. NULL 
20. NULL 21. NULL 22. NULL 23. NULL 
24. NULL 25. NULL 26. NULL 27. NULL 
28. NULL 29. NULL 30. NULL 31. NULL 
32. NULL 33. NULL 34. NULL 35. NULL 
36. NULL 37. NULL 38. NULL 39. NULL 
40. NULL 41. NULL 42. NULL 43. NULL 
44. NULL 45. NULL 46. NULL 47. NULL 
48. NULL 49. NULL 50. NULL 51. NULL 
52. NULL 53. NULL 54. NULL 55. NULL 
56. NULL 57. NULL 58. NULL 59. NULL 
60. NULL 61. NULL 62. NULL 63. NULL 
tail: change num: 35

看上去L1 block的结构有所不同。我们继续dump data block来看看？

dump type : datafile blocks
size : 8192bytes


block: 114740
heap data
head: id 0-114740, type 4, instance id 0 lsn 229572, checksum: 0, change num 5, isEncrypted 0, isCompressed 0
dataOid: 2302, extent change number: 0
block scn: 502694749682753536, map block: 0-114736, slot: 4, freeness: 0
rows: 76, dirs: 76, xslots: 2, si xslots: 0, free size: 660, free begin: 7328, free end: 7988 free dir: 4095 
row[0]:       size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[1]:       size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[2]:       size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[3]:       size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[4]:       size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[5]:       size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[6]:       size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[7]:       size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[8]:       size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[9]:       size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[10]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[11]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[12]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[13]:      size: 104      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[14]:      size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[15]:      size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[16]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[17]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[18]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[19]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[20]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[21]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[22]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[23]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[24]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[25]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[26]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[27]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[28]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[29]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[30]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[31]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[32]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[33]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[34]:      size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[35]:      size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[36]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[37]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[38]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[39]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[40]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[41]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[42]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[43]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[44]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[45]:      size: 104      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[46]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[47]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[48]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[49]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[50]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[51]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[52]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[53]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[54]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[55]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[56]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[57]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[58]:      size: 108      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[59]:      size: 104      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[60]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[61]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[62]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[63]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[64]:      size: 104      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[65]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[66]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[67]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[68]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[69]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[70]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[71]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[72]:      size: 92       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[73]:      size: 96       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[74]:      size: 100      xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0
row[75]:      size: 88       xslot id: 0       columns: 20       format/link/migr/deleted/compact:1/0/0/0/0


xslot[0]:  xid: 26-16326-1           active: 0 owscn: 0 fastcommit: 1 ura: block: 0-6441, ver: 3, dir: 0 ssn: 558587904  fsc: 60982 mfb: 1785  scn: 502694749682753536 
xslot[1]:  xid: 0-0-0                active: 0 owscn: 0 fastcommit: 0 ura: block: 0-0, ver: 0, dir: 0 ssn: 0          fsc: 0     mfb: 0     scn: 0 
tail: change num: 5

上述数据块的dump结果与Oracle data block是完全不同的，尽管如此，我们也可以大概看出一些逻辑，比如该block 存了76条数据，包括76个row 目录，以及每行的size大小d等等。

除此之外，我们还可以发现一些有意思的地方，比如scn，fsc。尤其是fsc，这是Oracle block中的fast commit scn？？？

大家是不是感觉有点意思？最后留个疑问，欢迎大家在后面留言！

问题：崖山数据库既然有L1 block，是否有类似Oracle一样的L2、L3 block？

yashandb yashandb体验官

最后修改时间：2023-11-23 15:24:46

文章转载自Roger的数据库专栏，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

「YashanDB个人版体验」崖山数据库 - 段的分配方式是怎样的？

评论