H2数据库：聚集索引支持
我使用 H2 数据库来存储包含大量时间序列的环境数据。时间序列只是定期（例如每小时一次）记录在数据库中的传感器的测量值。
表中存储的数据：
CREATE TABLE hydr
(dt timestamp
,value double
,sensorid int)
我想对表进行范围查询，例如：
select * from hydr
where dt between '2010-01-01' and '2010-10-01'
为了提高性能，我想在 dt 列上构建聚集索引，但问题是，我还没有找到 H2支持聚集索引。有人知道 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).
The data stored in the table:
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'
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
简短回答：表的主键需要是 BIGINT 类型：
在这种情况下，表是使用“dt”列组织的。这称为“聚集索引”。不支持 TIMESTAMP 数据类型，主要是因为它还包含纳秒。您可以做的是将 unix 时间戳（自 1970 年以来的毫秒数）存储为 BIGINT。
长答案：关于数据如何在 H2 内部存储的文档很差。我会将以下部分添加到 H2 数据库的“性能”文档中。我希望这能澄清问题（如果没有请告诉我）：
数据如何在内部存储
对于持久数据库，如果使用 BIGINT、INT、SMALLINT 类型的单列主键创建表， TINYINT，那么表的数据就是这样组织的。这有时也称为“聚集索引”或“索引组织表”。
H2内部以b树的形式存储表数据和索引。每个 B 树将条目存储为唯一键（一列或多列）和数据（零列或多列）的列表。表数据始终以“数据 B 树”的形式组织，具有 long 类型的单个列键。如果创建表时指定了 BIGINT、INT、SMALLINT、TINYINT 类型的单列主键，则该列将用作数据 B 树的键。如果没有指定主键，或者主键列是其他数据类型，或者主键包含多个列，则表中会添加一个 BIGINT 类型的隐藏自增列，用作数据 B 树的键。表的所有其他列都存储在该数据 B 树的数据区域内（大 BLOB、CLOB 列除外，它们存储在外部）。
对于每个附加索引，都会创建一个新的“索引 B 树”。该 B 树的键由索引列加上数据 B 树的键组成。如果在插入数据后创建主键，或者主键包含多个列，或者主键不是上面列出的数据类型，则主键将存储在新的索引 B 树中。
Short answer: the primary key of your table needs to be of type 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.
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):
How Data is Stored Internally
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 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).
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.