关键字:
TOAST表;索引;存储方式、人大金仓、KingbaseES
TOAST表概述
TOAST是超长字段在数据库中的一个存储方式,全称为The OverSized Attribute Storage Technique(超尺寸字段存储技术),将大字段值压缩或者分散为多个物理行来存储。数据库的部分数据类型支持toast,由于有些字段不会产生大字段数据(如date,time,boolean等),因此这些字段不支持toast。支持toast的数据类型应当是可变长度的。表中任何一个字段有toast,该表都有这一个相关联的toast表,oid被存储在pg_class.reltoastrelid中。超出的数值将被分割成chunks,并且最多toast_max_chunk_size个byte(缺省是2KB)。当存储的列长度超过toast_tuple_threshold值(通常为2KB),就会触发toast存储。toast将会压缩或移动字段值直到超出部分比toast_tuple_targer值小(通常也是2KB)。toast的目的是优化存储和检索大型对象,从而节省存储空间和提高性能。
自动创建Toast表
1、建表:CREATE TABLE test_table(id NUMBER, name VARCHAR(100), location POINT, address TEXT);
INSERT INTO test_table VALUES(1, 'John', '(1,1)', 'cd');
INSERT INTO test_table VALUES(2, 'Jane', '(2,2)', 'cd');
INSERT INTO test_table VALUES(3, 'Bob', '(3,3)', 'cd');
INSERT INTO test_table VALUES(4, 'Alice', '(4,4)', 'cd');
INSERT INTO test_table VALUES(5, 'David', '(5,5)', 'cd');
INSERT INTO test_table VALUES(6, 'James', '(6,6)', 'cd');
由于字段name和address是可变长度,会产生大字段数据,因此会自动创建toast表和索引。
查看test_table表:\d+ test_table
查看test_table表及toast表的oid:
SELECT relname,relfilenode,reltoastrelid FROM pg_class WHERE relname='test_table';
查找toast表的表名:select * from pg_catalog.pg_class where oid=16495::regclass;
可见toast表的表名是由pg_toast_以及test_table表的oid拼接在一起的。
2、查看toast表的信息:\d+ pg_toast.pg_toast_16492
toast表有额外的三个字段:chunk_id标识TOAST表的oid字段;chunk_seq是chunk的序列号,与chunk_id的组合唯一索引可以加速访问;chunk_data用于存储TOAST表的实际数据。
查看toast表上的索引信息:\d+ pg_toast.pg_toast_16492_index
行外存储和压缩
1、行外存储:在数据库中,当某个列中的数据量很大时,如大文本字段或大二进制字段,数据库系统可能将其存储在一个单独的数据结构中,而不是直接存储在表的行内。这个单独的数据结构就是TOAST表。这样处理的优点是可以节省空间,因为大型数据对象通常比较稀疏,不是每个行都需要存储大量数据。
2、压缩:当数据对象超出了一定大小,数据库对其进行压缩,以减少其占用的存储空间。压缩技术可通过各种压缩算法进行实现,目的是尽可能减少数据的存储空间,同时保证数据的可恢复性和查询性能。
TOAST的4种策略
1、PLAIN策略:避免压缩和行外存储
只有那些不需要TOAST策略就能存放的数据类型允许选择(如int类型),对于text这类要求存储长度超过页大小的类型,是不允许采用此策略的。
2、MAIN策略:允许压缩,不允许行外存储
3、EXTENDED策略:允许行外存储和压缩
一般先进行压缩,若数据量仍然太大,就会行外存储。
4、EXTERNAL策略:允许行外存储,不允许压缩
更改表的存储方式
1、语法为:ALTER TABLE table_name ALTER COLUMN column_name SET STORAGE {PLAIN | EXTENDED | MAIN | EXTERNAL};
建表:create table test_toast(id int);
查看表信息:\d+ test_toast
由于没有toast表的oid,因此没有创建toast表:SELECT relname,relfilenode,reltoastrelid FROM pg_class WHERE relname='test_toast';
更改表的存储方式为EXTENDED:alter table test_toast alter column id set storage extended;
由于id列为int,因此报错:
查看test_table的信息:\d+ test_table
此时创建了toast表:
更改test_table表的name和address列存储方式为plain:alter table test_table alter column name set storage plain;
alter table test_table alter column address set storage plain;
查看toast表仍然存在:
因此修改表的存储方式,不会删除已经创建的toast表。
TOAST的其它内容
1、当计算一个表的大小时要注意统计toast表的大小,由于存储超长字段时,基础表上可能只存储了20%,其余的数据都存储在toast表中,因此需要结合两表计算大小。
2、当表里有extended或者external类型时会创建toast表,两者的关联是通过pg_class中的oid进行。
参考资料
无