Lindorm：按自訂時間列冷熱分離

設定表的冷熱分界線

1. 設定自訂時間列及冷熱分界線。

   • 方法一：建表時設定。

     建立表dt，設定冷熱分離時間點為一天，按p2列冷熱分離。

     CREATE TABLE dt (p1 integer, p2 bigint, p3 bigint, c1 varchar,  constraint pk primary key(p1, p2, p3)) WITH(COMPRESSION = 'ZSTD', CHS ='86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN = 'COLUMN=p2'); 

     參數說明

     • CHS：冷熱分離時間點，單位為秒（s）。例如CHS為86400，代表自訂時間列的時間戳記取值在86400秒（一天）前的資料會被自動歸檔到冷儲存。

     • COMPRESSION：壓縮演算法。整張表生效。演算法名稱大小寫不敏感。預設配置為none。

     • CHS_L2：配置第二層屬性，一般配置儲存屬性：storagetype=COLD。

     • CHS_COLUMN：自訂時間列。

   • 方法二：建表時未開啟冷熱分離，通過ALTER TABLE語句添加相關屬性。

     -- 已有資料表、建表時未開啟冷熱分離，例如：
     -- CREATE TABLE dt (p1 integer, p2 bigint, p3 bigint, c1 varchar,  constraint pk primary key(p1, p2, p3));
     
     -- 為表dt開啟按自訂欄冷熱分離，設定冷熱分離時間點為一天，按p2列冷熱分離
     ALTER TABLE dt SET 'CHS' ='86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN' = 'COLUMN=p2';

2. 可選：修改表的冷熱分界線和自訂時間列。

   • 修改冷熱分界線。

     ALTER TABLE dt SET 'CHS'='1000';

   • 修改自訂時間列。

     ALTER TABLE dt SET 'CHS_COLUMN'='COLUMN=p3'; 

3. 可選：取消表的冷熱分離。

   ALTER TABLE dt SET 'CHS'='', 'CHS_L2' = '', 'CHS_COLUMN'='';

   說明

   修改冷熱分界線或取消冷熱分離後，需要等待系統後台執行完compaction，資料才能從冷儲存回到熱儲存中。如果需要資料立即回到熱儲存，請手動執行major_compact命令。

設定二級索引的冷熱分界線

二級索引預設儲存在一張表中，因此，二級索引可以像表一樣進行冷熱分離。

以上文建立的表dt為例，為表dt建立二級索引。

1. 建立二級索引並開啟冷熱分離。

   • 方法一：建立二級索引時設定自訂時間列及冷熱分界線。

     CREATE INDEX idx on dt (c1) WITH(CHS = '86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN='COLUMN=p2');

     說明

     二級索引無法指定自訂時間列，CHS_COLUMN必須設定為主表的自訂時間列。

   • 方法二：建立二級索引時未設定，通過ALTER TABLE設定自訂時間列及冷熱分界線。

     -- 已有二級索引、建立二級索引時未開啟冷熱分離，例如：
     -- CREATE INDEX idx on dt (c1);
     
     -- 為二級索引表開啟冷熱分離，設定冷熱分離時間點為一天，按p2列冷熱分離
     ALTER TABLE `dt.idx` SET 'CHS' = '86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN'='COLUMN=p2';

2. 可選：修改二級索引的冷熱分界線和自訂時間列。

   • 修改冷熱分界線。

     ALTER TABLE `dt.idx` SET 'CHS'='10000';

   • 修改自訂時間列。

     ALTER TABLE `dt.idx` SET 'CHS_COLUMN'='COLUMN=p2'; 

   說明

   二級索引表的表名格式為表名+點號（.）+二級索引名。由於二級索引表名中有特殊符號，使用時必須在表名前後添加反引號（`）進行轉義。例如，原表名為test.idx，轉義後的表名為`test.idx`。

3. 可選：取消二級索引的冷熱分離。

   ALTER TABLE `dt.idx` SET 'CHS'='', 'CHS_L2' = '', 'CHS_COLUMN'='';

查詢樣本

• 隨機查詢Get

  -- p2為自訂時間列
  SELECT * FROM dt WHERE p1 = 10 AND p2 = 10;

• 範圍查詢Scan

  -- p2為自訂時間列
  SELECT * FROM dt WHERE p2 > 10 AND p2 < 1000;

  重要

  當執行SELECT語句被識別為低效查詢時，寬表引擎預設不允許執行該類查詢，同時會拋出異常。詳細介紹及解決方案，請參見SELECT。

• 通過HINT僅查詢熱資料

  SELECT /*+ _l_hot_only_(true) */ * FROM dt WHERE p1>1;