SQLite有序BIGINT列建索引是否冗余？为何无索引查询慢

为什么格式规整、步长固定的openTime列无索引时查询开销很高 #

• SQLite默认采用堆表结构存储数据（未显式指定WITHOUT ROWID的表均为rowid表），数据行的物理存储顺序仅和写入顺序、页空闲空间分配规则有关，和openTime的数值大小没有强绑定关系。哪怕你初始写入时严格按时间戳顺序插入，后续的删除、更新、自动空闲空间复用、VACUUM操作都可能打乱数据在页内、页间的物理顺序。没有索引的情况下，SQLite只能执行全表扫描：逐页加载磁盘块，逐行比对openTime的值，哪怕你只查询这一列，也要遍历完整个表的所有数据页，数据量越大速度越慢。
• 你认知里的“列值本身有序”是逻辑层面的顺序，数据库不会主动维护列值的物理有序性：如果要保证整列值在磁盘上严格按数值顺序排列，每次插入新数据都可能需要挪动大量已有数据的存储位置，写入开销会高到完全无法接受，因此数据库引擎不会做这个默认保证。
• 即便你能保证写入之后从来没有删改操作、数据物理顺序和openTime逻辑顺序完全一致，SQLite的查询优化器也没有内置“该列值严格按物理存储顺序单调递增”的判定逻辑，不会自动跳过全表扫描流程——优化器只认元数据里记录的结构信息，没有索引就没有可以用来快速定位的检索结构，只能走全表扫。

这类时间序列整数列的低开销优化技巧 #

• 最优方案是将openTime定义为RowID别名，零额外索引开销获得聚簇索引能力。SQLite的隐式rowid列是表自带的聚簇B树索引，不需要额外占存储空间，B树本身按rowid值排序。建表时直接将openTime列声明为INTEGER PRIMARY KEY（不要加AUTOINCREMENT关键字，该关键字会带来不必要的序列持久化开销），此时openTime会直接作为rowid使用，所有针对openTime的范围查询、等值查询都会直接走聚簇索引定位，完全不需要额外建数GB的二级索引，查询效率比二级覆盖索引更高，还能省掉全部二级索引的存储开销。

  该方案适配场景要求openTime列非空、值唯一，和毫秒级时间戳的特性完全匹配。如果你的openTime存在重复值，该方案不适用。

• 无法改表结构的场景下，采用手动分表做裁剪。按固定时间粒度（比如按天、按周）将单张大表拆分为多个命名规则固定的子表，例如kline_20240101、kline_20240102，查询时先根据传入的时间范围计算出需要访问的子表名，仅扫描对应时间范围内的子表，不需要遍历全量数据。拆分后单表的索引体积会降到百MB级别，查询时需要加载的索引页数量大幅降低，IO开销会明显下降。
• 针对固定模式的查询做预聚合。如果你的查询都是固定时间粒度的统计类请求（比如统计某段时间的开盘价、成交量汇总值），提前按分钟/小时/天粒度将聚合结果存入单独的小表，查询时直接访问聚合结果表，不需要扫描原始大表，性能可以提升1~2个数量级。
• 适配存储硬件调整数据库参数。如果数据库存在SSD上，执行PRAGMA page_size = 32768;后运行VACUUM重建数据库，将默认4KB的页大小调整为32KB，单次IO可以读取更多数据，相同数据量下的磁盘寻址次数更少，无论是全表扫描还是索引查询的IO开销都会明显降低。

内容的提问来源于stack exchange，提问作者Alex Deft