在有序的列上没有索引吗？
我有一个sqlite数据库，列，列为opentime的bigint类型。我仅针对此列搜索数据库（从来没有其他）。
数据库的搜索速度很慢，直到我通过Opentime创建索引。我的问题是：如果该列已经采用整洁的简单格式，例如1608872700000带有常量的步骤，为什么对其进行搜索呢？我觉得我没有通过已经有序整数的东西添加几千兆字节的索引来有效。我在这里想念什么技巧？
I have an sqlite database with column called openTime of BIGINT type. I only search the database against this column (never others).
The database is slow in search, until I create an index over openTime. My question is: if the column is already in a neat simple format like 1608872700000 with constant stepsize, why is it expensive to search against it? I feel I am not being efficient by adding several gigabytes worth of index against something that is already an ordered integer. What trick am I missing here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如评论中所示，一旦构建数据库（以随机的方式不关心有序插入），为时已晚，为时已晚。在这一点上，告知结构的唯一方法是创建一个索引，这会产生额外的空间。
我以
的声明无rowID并指定opentime作为主键，从而从头开始重新创建了数据库，因此，从一开始就以有序的方式编写数据。这实际上是一个零空间成本的聚类索引。As indicated in comments, it was too late to be efficient once the database is already built (in a random way that doesn't care about ordered insertion). At that point, the only way to inform it of the structure is to create an index, which incurs extra space.
I re-created the database from scratch with declaration of
NO ROWIDand specifyingopenTimeas primary key, thus, the data is written in the ordered way right from the start. This is effectively a clustered index with zero space cost.