REFRESH SPATIAL INDEX

TiDB Cloud Lake 会在 SYNC 模式下每次写入新数据时自动刷新空间索引。REFRESH SPATIAL INDEX 主要用于回填在声明索引之前已存在的行。

语法

REFRESH SPATIAL INDEX <index> ON [<database>.]<table> [LIMIT <limit>]

示例

-- Existing table with data loaded before the index was declared
CREATE TABLE IF NOT EXISTS stores (
  store_id INT,
  location GEOMETRY
) ENGINE = FUSE;

INSERT INTO stores VALUES
  (1, TO_GEOMETRY('POINT(10 10)')),
  (2, TO_GEOMETRY('POINT(20 20)'));

-- Create the spatial index afterward
CREATE SPATIAL INDEX stores_location_idx ON stores(location);

-- Backfill historical rows so the index covers earlier inserts
REFRESH SPATIAL INDEX stores_location_idx ON stores;

-- Future inserts refresh automatically in SYNC mode