建库建表最容易忽略的十个细节

排序列是 TSDB 引擎特有的结构，它在 TSDB 引擎的存储和读取流程中发挥着重要作用，包括分区内数据索引和去重的双重作用。在创建表时通过参数 sortColumns 来定义排序列，sortColumns 的最后一列必须是时间类型，而除了最后一列之外的其他列被称为排序键（Sort Key），每个排序键独特值对应的数据按列顺序存储在一起。排序键字段的组合值作为索引键，为数据查询提供了入口，能够迅速定位数据块的位置，从而降低查询时间。在写入过程中，每个事务中的数据会根据 sortColumns 进行排序和去重。

TSDB 引擎的分区内索引包括每个索引键和字段组合的入口信息以及稀疏索引（最大值，最小值，个数和）信息。当索引键个数 X 字段数很大时，索引数据会很大，这对写入性能、查询性能和内存使用都会产生重大影响，可以通过对排序键进行哈希降维来减少索引数量。

例子：股票数据（假设股票数量为 5000 支，每天记录 2000 万条，每天数据量为 800MB），按交易日期字段作为值分区条件。

情况 1：只使用 TradeTime 作为排序列会导致索引键数量过多，但是每个索引键对应的数据量又特别少。

单个排序列时，该列就称为排序键，一个分区的索引键数量计算公式：索引键数量 = 每个分区内的排序键独特值数量。

以下例子中，每个分区索引键数量 = TradeTime 去重后的数量。由于 TradeTime 为股票交易时间，即使按秒级别计算一天也有 4 * 3600 = 14400 个独特值，实际上 TradeTime 为毫秒级，故索引键数量远大于 14400。

db=database("dfs://testDB1",VALUE, 2020.01.01..2021.01.01,engine="TSDB")
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt1, partitionColumns=`TradeDate, sortColumns=`TradeTime, keepDuplicates=ALL)

假设交易时间为半秒级，索引键数量为 28800 个，每个索引键对应的行数为 694。

情况 2：使用 SecurityID、TradeTime 作为 sortColumns 会导致索引键数量和股票代码数量一样。

多个 sortColumns 时索引键计算公式：索引键数量 = 每个分区内的除去最后一列外其余字段独特值组合值数量。

以下例子中，每个分区索引键的数量为 SecurityID 独特值的数量，即 5000 个。

db=database("dfs://testDB1",VALUE, 2020.01.01..2021.01.01,engine="TSDB")
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt2, partitionColumns=`TradeDate, sortColumns=`SecurityID`TradeTime, keepDuplicates=ALL)

假设股票数量为 5000 只，索引键数量为 5000 个，每个索引键对应的行数为 4000。

对于上述例子中的情况可以通过以下方案解决。

情况 1：只有一个 sortColumns 字段时不选择时间相关的或者去重后数量过多的字段。比如将 TradeTime 替换为 SecurityID 作为 sortColumn。

该方案中，每个分区索引键数量 = SecurityID 去重后的数量 = 5000。每个索引键对应的行数为 4000 和上述例子中的情况 2 一样。

db=database("dfs://testDB1",VALUE, 2020.01.01..2021.01.01,engine="TSDB")
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt3, partitionColumns=`TradeDate, sortColumns=`SecurityID, keepDuplicates=ALL)

还可以对 SecurityID 排序列进行哈希降维。每个分区索引键数量 = 哈希降维后的数量（500）。每个索引键对应的行数为 40000。

db=database("dfs://testDB1",VALUE, 2020.01.01..2021.01.01,engine="TSDB")
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt4, partitionColumns=`TradeDate, sortColumns=`SecurityID keepDuplicates=ALL,sortKeyMappingFunction=[hashBucket{,500}])

情况 2：有多个 sortColumns 字段时，可以配置 sortKeyMappingFunction 参数进行哈希降维（会影响相关 sortColumns 字段的点查性能，但是会提升范围查询性能）。如果每个分区内除最后一个时间列外，其余字段组合数量超过 1000，可以配置 sortKeyMappingFunction 参数进行哈希降维。

使用 SecurityID、TradeTime 作为 sortColumn 并配置 sortKeyMappingFunction 参数进行哈希降维。

多个 sortColumn 时索引键计算公式：索引键数量 = 每个分区内的除去最后一列外其余字段去重（或者哈希降维）后数量相乘。

以下例子中，每个分区索引键数量 = 哈希降维后的数量（500）。每个索引键对应的行数为 40000。

db=database("dfs://testDB1",VALUE, 2020.01.01..2021.01.01,engine="TSDB")
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt5, partitionColumns=`TradeDate, sortColumns=`SecurityID`TradeTime, keepDuplicates=ALL,sortKeyMappingFunction=[hashBucket{,500}])

按照上述五种建表方案进行建表，然后导入 10 天的数据（每天 2000 万条记录）测试其查询性能。

范围查询：

select avg(TradePrice) from pt where TradeDate between 2020.01.04 and 2020.01.05

点查询 1（TradeDate + SecurityID）：

select avg(TradePrice) from pt where TradeDate = 2020.01.04 and SecurityID=`600

点查询 2（TradeDate + SecurityID + TradeTime）：

select TradePrice from pt where TradeDate = 2020.01.04 and SecurityID=`3740 and TradeTime =09:30:00.000

通过上述性能测试实验结果，可以得出结论：排序列设置不合理，查询字段中没有排序列字段，查询性能将大幅下降。对于范围查询索引键对应的数据过少，会影响其性能，可以通过哈希降维大幅提升性能（点查询性能会略微下降）。

建表时未考虑后续将设置为值（VALUE）分区字段的列内是否包含特殊字符，导致数据插入该列时因存在特殊字符而失败。

n=1000
ID=take(["1","2","3"], n);
x=rand(1.0, n);
t=table(ID, x);
//将 ID 列作为值分区
db=database(directory="dfs://valuedb", partitionType=VALUE, partitionScheme=["1"])
pt = db.createPartitionedTable(t, `pt, `ID);
pt.append!(t);
n=1000
ID=take(["1 12","2.12","3   12"], n);
x=rand(1.0, n);
t=table(ID, x);
pt.append!(t);

上述代码会报错：A string or symbol value-partitioning column can't contain any invisible character。

对于前述例子中的错误，如果这些不可见的特殊字符无意义，可以在插入前删除。如果有意义，可以将该字段设置为哈希（HASH）分区来替代值分区方案。哈希（HASH）分区的数量需要按照具体数据量进行设置，避免分区粒度过大或者过小。或者将对应字段数据进行处理后插入。

以下例子中，第 2 行 ID 和第 6 行 partitionScheme 的设置存在范围不一致的情况。

n=1000000
ID=rand(20, n)//数据范围是 0-20
x=rand(1.0, n)
t=table(ID, x);
//设置分区时RANGE范围是 0-5，5-10
db=database(directory="dfs://rangedb", partitionType=RANGE, partitionScheme=0 5 10)

pt = db.createPartitionedTable(t, `pt, `ID);
//tableInsert之后只有在 0-10 部分的数据才被插入，其余的都被丢弃。
tableInsert(pt,t);

上述例子通过 tableInsert 插入后返回 499972 条（远小于 1000000）。

由于 partitionScheme 设置为 0 5 10，导致只有 0-10 部分数据插入成功，其余部分数据丢失。因此应该设置合适的分区范围，考虑后续数据的范围。或者可以通过 addRangePartitions 函数后续添加分区范围。

例子：股票行情数据（每天 5000 万条记录），先按日期分区然后将股票代码字段按值分区。选择使用 TradeDate、SecurityID 二级分区，而且 SecurityID 是值分区，假设全是国内股票，一天分区数量就达到 5000+。每天数据量为 3MB（内存中），每个分区大小约为 0.6MB。

1.默认最大查询分区数为 65536，假设每天 5000 个股票，查询 14 天的数据就会报错 The number of partitions relevant to the query is too large。

2.Level File 索引缓存容量默认是最大内存的 5%，虽然单个分区的 Level File 索引（LevelFileIndex）较小，但是如果查询分区数非常多，会导致马上占满 Level File 索引缓存。占满 Level File 索引缓存之后会导致查询效率大幅度下降。

db1 = database(, VALUE, 2020.01.01..2021.01.01)
db2 = database(, VALUE, `a`b)
db = database(directory="dfs://testDB1", partitionType=COMPO, partitionScheme=[db1, db2], engine="TSDB")
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`TradeDate`SecurityID, sortColumns=`SecurityID`TradeTime, keepDuplicates=ALL)

对于上述例子，先按日期分区，然后将股票代码字段 HASH 分区（需要根据每天的量估计 HASH 多少个分区，一般一个分区占用内存应控制在 100MB - 1GB 之间），每天只有 25 个分区，每天数据量为 3MB（内存中），每个分区大小约为 120MB。

db1 = database(, VALUE, 2020.01.01..2021.01.01)
db2 = database(, HASH, [SYMBOL, 25])
db = database(directory="dfs://testDB2", partitionType=COMPO, partitionScheme=[db1, db2], engine="TSDB")
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`TradeDate`SecurityID, sortColumns=`SecurityID`TradeTime, keepDuplicates=ALL)

按照上述两种建表方案进行建表，然后导入 10 天的数据（每天 5000 万条记录）测试其范围查询性能。

select avg(TradePrice) from pt where TradeDate=2020.01.04

通过上述性能测试实验结果，可以得出结论：如果分区粒度过小会导致查询效率过低，建库建表时要选择合适的分区粒度。

以下例子中的股票行情数据（每天 5000 万条记录），直接按日期分区，一天数据大约有 3MB（内存中）。

//按交易日期分区
db= database("dfs://testDB1", VALUE, 2020.01.01..2021.01.01)
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
//每天只有 1 个分区，假设每天数据量为 3GB（内存中），每个分区大小约为 3GB，分区粒度过大
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`TradeDate)

对以下例子中的股票行情数据，先按日期分区然后将股票代码字段哈希分区（需要根据每天的量估计需要哈希的分区数量，一般一个分区内存中大小 100MB - 1GB 之间）。

//先按交易日期分区，然后按股票代码进行哈希分区
db1 = database(, VALUE, 2020.01.01..2021.01.01)
db2 = database(, HASH, [SYMBOL, 25])
db = database(directory="dfs://testDB2", partitionType=COMPO, partitionScheme=[db1, db2])
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
//每天只有 25 个分区，假设每天数据量为 3GB（内存中），每个分区大小约为120MB
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`TradeDate`SecurityID)

按照上述两种建表方案进行建表，然后导入 10 天的数据（每天 5000 万条记录）测试其点查性能。

select avg(TradePrice) from pt where SecurityID=`600

通过上述性能测试实验结果，可以得出结论：如果分区粒度过大会导致查询效率过低，建库建表时要选择合适的分区粒度。

使用维度表存储超过 20 万条记录的数据或难以估算的数据。

例子：以每天产生 2000 万条记录的股票数据为例，如果使用仅有一个分区的维度表，不到几天该表里就会有几亿条数据。假设每天的数据量为 800MB（内存中），读取维度表数据时会将整个维度表加载入内存，不到几天，内存就会被大量占用。

db= database("dfs://testDB1", VALUE, 2020.01.01..2021.01.01)
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createTable(tbSchema,`dt)

所以在预估数据量明确或较少时，可以使用维度表；预估数据量较多时，应该使用分区表。

上述例子中每天产生 2000 万条股票数据。为了处理这些数据，可以使用分区表：

1. 首先按交易日期进行分区。
2. 然后按股票代码进行哈希分区，每天只会有 25 个分区。

以下代码为使用分区表方案建表：

db1 = database(, VALUE, 2020.01.01..2021.01.01)
db2 = database(, HASH, [SYMBOL, 25])
db = database(directory="dfs://testDB2", partitionType=COMPO, partitionScheme=[db1, db2])
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`TradeDate`SecurityID)

按照上述两种建表方案进行建表，然后导入一天的数据（2000 万条记录）测试其点查性能。

select avg(TradePrice) from tb where SecurityID=`600

通过上述性能测试实验结果，可以得出结论：预估数据量明确或较少时，可以使用维度表；预估数据量较多时，应该使用分区表。如果数据量非常多时使用维度表，其查询性能将非常差。

例子：对于以下例子中的股票数据，只按股票代码进行分区，如果该数据经常按交易日期和股票代码进行查询，只设置股票代码作为分区列不但会导致按交易日期进行查询时会扫描所有分区，而且会导致分区大小因为每天有数据插入不断变大。

db = database("dfs://testDB1",HASH, [SYMBOL, 25])
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`SecurityID)

对于上述例子中的股票数据，按交易日期和股票代码进行分区。如果该数据经常按交易日期和股票代码进行查询，可以设置交易日期和股票代码作为分区列。

db1 = database(, VALUE, 2020.01.01..2021.01.01)
db2 = database(, HASH, [SYMBOL, 25])
db = database("dfs://testDB2", partitionType=COMPO, partitionScheme=[db1, db2])
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`TradeDate`SecurityID)

按照上述两种建表方案进行建表，然后导入 10 天的数据（每天 2000 万条记录）测试其点查性能。

select avg(TradePrice) from pt where SecurityID=`600 and TradeDate=2020.01.04

通过上述性能测试实验结果，可以得出结论：选择分区字段时，应该考虑经常查询字段以提升查询效率。

以下例子中，由于建库建表时未考虑后续出现的并发写入，未将各个并发数据不同的字段设置为分区，导致并发写入冲突。

//假设后续并发，是按照日期进行写入，只按股票代码进行分区
db = database("dfs://testDB",HASH, [SYMBOL, 25])
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
//只按股票代码进行分区,而并发写入按照日期进行会导致并发写入冲突，报错“has been owned by transaction”
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`SecurityID)

以上建库建表代码按日期并发写入时会报错：xxx has been owned by transaction。

对于上述情况有以下解决方法：

1. 建库建表时可以按照按日期分区的方式进行建库建表，并行写入程序也按日期划分写入数据。
2. 根据分区情况调整并行写入程序，避免写入分区重合。
3. 如果使用相关 api 进行写入，可以使用 api 中的并行写入函数，自动对写入数据进行划分，比如 c++ api 和 python api 中的 PartitionedTableAppender。

上述解决方法目的都是，避免同一个分区同时被多个写入事务写入的情况。

数据量随日期不断增加的表中，没有把日期字段设为分区列。

例子：对于以下例子中的股票数据，只按股票代码分区，导致按交易日期进行查询时会扫描所有分区，并且分区随每天的数据插入不断变大。

db = database("dfs://testDB1",HASH, [SYMBOL, 25])
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`SecurityID)

对于数据量随日期不断增加的表，应将日期字段设为分区列。

对于上述例子中的股票数据，按照交易日期和股票代码进行了分区。

这样每个分区不会因为每天的数据插入而不断变大。由于按照交易日期进行了分区，新的分区会随着日期变化而生成。

db1 = database(, VALUE, 2020.01.01..2021.01.01)
db2 = database(, HASH, [SYMBOL, 25])
db = database("dfs://testDB2", partitionType=COMPO, partitionScheme=[db1, db2])
colName = `SecurityID`TradeDate`TradeTime`TradePrice`TradeQty`TradeAmount`BuyNo`SellNo
colType = `SYMBOL`DATE`TIME`DOUBLE`INT`DOUBLE`INT`INT
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`TradeDate`SecurityID)

按照上述两种建表方案进行建表，然后导入 10 天的数据（每天 2000 万条记录）测试其点查性能。

select avg(TradePrice) from pt where SecurityID=`600 and TradeDate=2020.01.04

通过上述性能测试实验结果，可以得出结论：对于数据量与日俱增的表应使用日期字段作为分区字段，提升查询效率。

例子：每个 ID 有 200 个因子，每个因子为一列，该表一共 203 列。使用 OLAP 引擎存储该表。

db= database("dfs://testDB1", VALUE, 2020.01.01..2021.01.01)
//202行的宽表，使用OLAP引擎（默认为OLAP）
colName = `ID`Date`Time
colName.append!(take("factor"+string(0..200),200))
colType = `SYMBOL`DATE
colType.append!(take(["DOUBLE"],200))
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`Date)

对于上述例子中表列数为 203，采用 TSDB 引擎存储该表能够避免读写时可能出现的性能瓶颈。

db= database("dfs://testDB2", VALUE, 2020.01.01..2021.01.01,engine="TSDB")
//203行的宽表，使用TSDB引擎
colName = `ID`Date`Time
colName.append!(take("factor"+string(0..200),200))
colType = `SYMBOL`DATE`TIME
colType.append!(take(["DOUBLE"],200))
tbSchema = table(1:0, colName, colType)
db.createPartitionedTable(table=tbSchema, tableName=`pt, partitionColumns=`Date,sortColumns=`ID`Time)

按照上述两种建表方案进行建表，然后导入 10 天的数据（每天 200 万条记录）测试其点查性能。

select avg(factor2) from pt where ID=`60 and Date=2020.01.04

通过上述性能测试实验结果，可以得出结论：对于列数大于 100 列的表可以使用 TSDB 引擎，提升查询效率。