H2数据库:聚集索引支持 [英] H2 database: clustered indexes support

问题描述

我将H2数据库用于包含大量时间序列的环境数据.时间序列只是传感器的测量值，它们定期(例如每小时一次)记录在数据库中.

I use H2 database for environmental data which contains lots of time series. Time series are simply measurement values of sensors which are recorded in database periodically (say once per hour).

表中存储的数据:

CREATE TABLE hydr
(dt timestamp
,value double
,sensorid int)

我想对表格进行范围查询，例如:

I would like to make range queries against the table, for example:

select * from hydr
where dt between '2010-01-01' and '2010-10-01'

为了提高性能，我想在dt列上构建聚簇索引，但是事实是，我还没有发现H2是否支持聚簇索引.有人知道H2是否支持聚集索引吗?

In order to improve performance I would like to build clustered index over dt column, but the thing is, that I haven't found if H2 supports clustered indexes. Does anybody know if clustered indexes are supported in H2?

推荐答案

简短答案:表的主键必须为BIGINT类型:

Short answer: the primary key of your table needs to be of type BIGINT:

CREATE TABLE hydr(dt bigint primary key, value double, sensorid int);

在这种情况下，该表使用"dt"列进行组织.这称为聚集索引".不支持数据类型TIMESTAMP，主要是因为它也包含纳秒.您可以做的就是将unix时间戳(自1970年以来的毫秒数)存储为BIGINT.

In this case, the table is organized using the "dt" column. This is called "clustered index". The data type TIMESTAMP is not supported, mainly because it also contains nanoseconds. What you could do is store the unix timestamp (milliseconds since 1970) as a BIGINT.

长答案:关于如何在H2内部存储数据的文献很少.我将以下部分添加到H2数据库的性能"文档中.我希望这能清除一切(如果不能的话，请告诉我):

Long answer: The documentation of how data is stored internally in H2 is poor. I will add the following section to the "performance" documentation of the H2 database. I hope this will clear up things (if not please tell me):

内部如何存储数据

对于持久数据库，如果使用单列主键(类型为BIGINT，INT，SMALLINT，TINYINT)创建表，则该表的数据将以这种方式组织.有时也称为聚集索引"或索引组织表".

For persistent databases, if a table is created with a single column primary key of type BIGINT, INT, SMALLINT, TINYINT, then the data of the table is organized in this way. This is sometimes also called a "clustered index" or "index organized table".

H2在内部以b树的形式存储表数据和索引.每个b树将条目存储为唯一键(一列或多列)和数据(零列或多列)的列表.表数据始终以数据b树"的形式进行组织，带有一个long类型的单列键.如果在创建表时指定了BIGINT，INT，SMALLINT，TINYINT类型的单列主键，则该列将用作数据b树的键.如果未指定主键，或者主键列是另一种数据类型，或者主键包含多个列，则将BIGINT类型的隐藏自动递增列添加到表中，该表将用作数据b树的关键字.该表的所有其他列都存储在该数据b树的数据区域中(大的BLOB，CLOB列除外，它们存储在外部).

H2 internally stores table data and indexes in the form of b-trees. Each b-tree stores entries as a list of unique keys (one or more columns) and data (zero or more columns). The table data is always organized in the form of a "data b-tree" with a single column key of type long. If a single column primary key of type BIGINT, INT, SMALLINT, TINYINT is specified when creating the table, then this column is used as the key of the data b-tree. If no primary key has been specified, if the primary key column is of another data type, or if the primary key contains more than one column, then a hidden auto-increment column of type BIGINT is added to the table, which is used as the key for the data b-tree. All other columns of the table are stored within the data area of this data b-tree (except for large BLOB, CLOB columns, which are stored externally).

为每个其他索引创建一个新的索引b树".该b树的键由索引列以及数据b树的键组成.如果在插入数据后创建了主键，或者主键包含多列，或者主键不是上面列出的数据类型，则主键将存储在新的索引b树中.

For each additional index, one new "index b-tree" is created. The key of this b-tree consists of the indexed columns, plus the key of the data b-tree. If a primary key is created after data has been inserted, or if the primary key contains multiple column, or if the primary key is not of the data types listed above, then the primary key is stored in a new index b-tree.

这篇关于H2数据库:聚集索引支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！