AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云数据库 SelectDB 版 操作指南 开发指南 数据表设计 表结构变更

表结构变更

更新时间：
产品详情
我的收藏

当您需要对云数据库 SelectDB 版数据库表结构进行调整以适应新的业务需求时，本文档提供了详细的变更表结构操作指南以及注意事项，以帮助您完成表结构变更。

概述

通过结构（Schema）变更操作来修改已存在表的结构（Schema），支持以下几种修改方式。

  • 增加、删除列。

  • 修改列类型。

  • 调整列顺序。

  • 增加、修改BloomFilter Index。

  • 增加、删除Bitmap Index。

名词解释

  • BASE Table：基表。每一个表被创建时，都对应一个基表。

  • ROLLUP：上卷表。基于基表或者其他ROLLUP创建出来的上卷表。

  • Index：物化索引。ROLLUP或BASE Table都被称为物化索引。

  • Transaction：事务。每一个导入任务都是一个事务，每个事务有一个唯一递增的Transaction ID。

基本原理

执行Schema变更的基本过程，是通过原Index的数据，生成一份新Schema的Index数据。主要进行两部分数据转换，一是已存在的历史数据的转换，二是在Schema变更执行过程中，新导入数据的转换。

+----------+
| Load Job |
+----+-----+
     |
     | Load job generates both origin and new index data
     |
     |      +------------------+ +---------------+
     |      | Origin Index     | | Origin Index  |
     +------> New Incoming Data| | History Data  |
     |      +------------------+ +------+--------+
     |                                  |
     |                                  | Convert history data
     |                                  |
     |      +------------------+ +------v--------+
     |      | New Index        | | New Index     |
     +------> New Incoming Data| | History Data  |
            +------------------+ +---------------+

在开始转换历史数据前，SelectDB会获取一个最新的Transaction ID。并等待这个Transaction ID之前的所有导入事务完成。这个Transaction ID称为分水岭。SelectDB保证在分水岭之后的所有导入任务，都会同时为原Index和新Index生成数据。因此当历史数据转换完成后，SelectDB可以保证新的Index中的数据是完整的。

创建变更作业

该语句用于对已有Table进行Schema变更操作。Schema变更是异步的，任务提交成功则返回结果，提交任务后可使用SHOW ALTER TABLE COLUMN;命令查看执行进度。

语法

ALTER TABLE [database.]table <alter_clause>;
说明

  • 一张表在同一时间只能有一个Schema变更作业在运行。

  • Schema变更操作不阻塞导入和查询操作。

  • 变更Schema时不能修改分区列和分桶列。

  • 如果Schema中有REPLACE方式聚合的Value列，则不允许删除Key列。

    如果删除Key列，SelectDB将无法决定REPLACE列的取值。

    Unique数据模型表的所有非Key列都是REPLACE聚合方式。

  • 新增聚合类型为SUM或者REPLACE的Value列时，该列的默认值对历史数据没有意义。

    因为历史数据已经失去详细信息，所以默认值的取值并不能反映实际聚合后的取值。

  • 当修改列类型时，除Type以外的字段都需要按被修改列上的信息补全。

    如修改列k1 INT SUM NULL DEFAULT "1"类型为BIGINT，则需执行命令如下：

    ALTER TABLE tbl1 MODIFY COLUMN k1 BIGINT SUM NULL DEFAULT "1";

    除新的列类型外，如聚合方式，Nullable属性，以及默认值都要按照原信息补全。

  • 不支持修改列名称、聚合类型、Nullable属性、默认值以及列注释。

Schema变更的<alter_clause>支持如下几种修改方式。

  • 向指定index的指定位置添加一列。

    ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_Value"]
[AFTER column_name|FIRST]
[TO rollup_index_name]
[PROPERTIES ("key"="Value", ...)]
    说明

    • 聚合模型如果增加Value列，需要指定agg_type。

    • 非聚合模型（如DUPLICATE Key）如果增加Key列，需要指定KEY关键字。

    • 不能在ROLLUP index中增加BASE index中已经存在的列（如果需要在ROLLUP index 中增加BASE index，可以重新创建一个ROLLUP index）。

  • 向指定Index添加多列。

    ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_Value", ...)
[TO rollup_index_name]
[PROPERTIES ("key"="Value", ...)]
    说明

    • 聚合模型如果增加Value列，需要指定agg_type。

    • 聚合模型如果增加key列，需要指定KEY关键字。

    • 不能在ROLLUP index中增加BASE index中已经存在的列（如果需要在ROLLUP index 中增加BASE index，可以重新创建一个ROLLUP index）。

  • 从指定Index中删除一列。

    DROP COLUMN column_name[FROM rollup_index_name]
    说明

    • 不能删除分区列。

    • 如果是从BASE index中删除列，则如果ROLLUP index中包含该列，也会被删除。

  • 修改指定Index的列类型以及列位置。

    MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_Value"]
[AFTER column_name|FIRST]
[FROM rollup_index_name]
[PROPERTIES ("key"="Value", ...)]
    说明

    • 聚合模型如果修改Value列，需要指定agg_type。

    • 非聚合类型如果修改Key列，需要指定KEY关键字。

    • 只能修改列的类型，列的其他属性维持原样（其他属性需要在修改语句中和原属性保持一致，详情请参见示例）。

    • 分区列和分桶列不能做任何修改。

    • 目前支持以下类型的转换（需自行确保精度不会丧失）：

      • TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT和DOUBLE类型向范围更大的数字类型转换。

      • TINTINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE和DECIMAL转换成VARCHAR类型。

      • VARCHAR类型支持修改最大长度。

      • VARCHAR类型或CHAR类型转换成TINTINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT和DOUBLE类型。

      • VARCHAR类型和CHAR类型转换成DATE类型（目前支持"%Y-%m-%d"，"%y-%m-%d"，"%Y%m%d"，"%y%m%d"，"%Y/%m/%d，"%y/%m/%d"六种格式化格式）。

      • DATETIME类型转换成DATE类型（仅保留年-月-日信息，例如:2019-12-09 21:47:05<-->2019-12-09）。

      • DATE类型转换成DATETIME类型（时分秒自动补零，例如:2019-12-09<-->2019-12-09 00:00:00）。

      • FLOAT类型转换成DOUBLE类型。

      • INT类型转换成DATE类型（如果INT类型数据不合法则转换失败，原始数据不变）。

      • 除DATE类型与DATETIME类型以外都可以转换成STRING类型，但是STRING类型不能转换任何其他类型。

  • 对指定Index的列进行重新排序。

    ORDER BY (column_name1, column_name2, ...)
[FROM rollup_index_name]
[PROPERTIES ("key"="Value", ...)]
    说明

    • Index中的所有列都要指明。

    • Value列在Key列之后。

示例

  • 修改示例表example_db.my_table（非聚合模型）的Schema，在example_rollup_index的col1后添加一个Key列new_col。

    ALTER TABLE example_db.my_table
ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
TO example_rollup_index;

  • 修改示例表example_db.my_table（非聚合模型）的Schema，在example_rollup_index的col1后添加一个Value列new_col。

    ALTER TABLE example_db.my_table   
ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
TO example_rollup_index;

  • 修改示例表example_db.my_table（聚合模型）的Schema，向example_rollup_index的col1后添加一个Key列new_col。

    ALTER TABLE example_db.my_table   
ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
TO example_rollup_index;

  • 修改示例表example_db.my_table（聚合模型）的Schema，向example_rollup_index的col1后添加一个Value列new_col且聚合类型为 SUM。

    ALTER TABLE example_db.my_table   
ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1    
TO example_rollup_index;

  • 修改示例表example_db.my_table（聚合模型）的Schema，向example_rollup_index添加两列。一个是Key列col1，另一个是Value列col2且聚合类型为 SUM。

    ALTER TABLE example_db.my_table
ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
TO example_rollup_index;

  • 修改示例表example_db.my_table的Schema，从example_rollup_index删除一列。

    ALTER TABLE example_db.my_table
DROP COLUMN col2
FROM example_rollup_index;

  • 修改示例表example_db.my_table的Schema，修改BASE index的Key列col1的类型为BIGINT，并移动到col2列后面。

    ALTER TABLE example_db.my_table 
MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
    说明

    无论是修改Key列还是Value列都需要声明完整的COLUMN信息。

  • 修改示例表example_db.my_table的Schema，修改BASE index的val1列最大长度，从VARCHAR(32)修改为VARCHAR(64)。

    ALTER TABLE example_db.my_table 
MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";

  • 修改示例表example_db.my_table的Schema，重新排序example_rollup_index中的列（设原列顺序为：k1，k2，k3，v1，v2）。

    ALTER TABLE example_db.my_table
ORDER BY (k3,k1,k2,v2,v1)
FROM example_rollup_index;

  • 修改Duplicate Key表Key列的某个字段的长度。

    原Schema如下。

    +-----------+-------+-------------+------+------+---------+-------+
| IndexName | Field | Type        | Null | Key  | Default | Extra |
+-----------+-------+-------------+------+------+---------+-------+
| tbl1      | k1    | INT         | No   | true | N/A     |       |
|           | k2    | INT         | No   | true | N/A     |       |
|           | k3    | varchar(20) | No   | true | N/A     |       |
|           | k4    | INT         | No   | false| N/A     |       |
+-----------+-------+-------------+------+------+---------+-------+

    将k3列的长度改成50，修改语句如下。

    ALTER TABLE example_tbl MODIFY COLUMN k3 varchar(50) key null COMMENT 'to 50'

    修改完成后，Schema如下。

    +-----------+-------+-------------+------+------+---------+-------+
| IndexName | Field | Type        | Null | Key  | Default | Extra |
+-----------+-------+-------------+------+------+---------+-------+
| tbl1      | k1    | INT         | No   | true | N/A     |       |
|           | k2    | INT         | No   | true | N/A     |       |
|           | k3    | varchar(50) | No   | true | N/A     |       |
|           | k4    | INT         | No   | false| N/A     |       |
+-----------+-------+-------------+------+------+---------+-------+

    因为Schema变更作业是异步操作，同一个表同时只能进行一个Schema变更作业。

  • 在一个作业中，对多个ROLLUP进行不同的修改。

    原Schema如下。

    +-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key  | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
| tbl1      | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k3    | INT  | No   | true | N/A     |       |
|           |       |      |      |      |         |       |
| rollup2   | k2    | INT  | No   | true | N/A     |       |
|           |       |      |      |      |         |       |
| rollup1   | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
+-----------+-------+------+------+------+---------+-------+

    通过以下命令给rollup1和rollup2都加入一列k4，并且再给rollup2加入一列k5。

    ALTER TABLE tbl1
ADD COLUMN k4 INT default "1" to rollup1,
ADD COLUMN k4 INT default "1" to rollup2,
ADD COLUMN k5 INT default "1" to rollup2;

    修改完成后，Schema如下。

    +-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key  | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
| tbl1      | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k3    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
|           | k5    | INT  | No   | true | 1       |       |
|           |       |      |      |      |         |       |
| rollup2   | k2    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
|           | k5    | INT  | No   | true | 1       |       |
|           |       |      |      |      |         |       |
| rollup1   | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
+-----------+-------+------+------+------+---------+-------+

    BASE表tbl1也自动加入了k4，k5列。即给任意ROLLUP增加的列，都会自动加入到BASE表中。

    同时，不允许向ROLLUP中加入BASE表已经存在的列。如果需要在ROLLUP index 中增加BASE index，可以重新创建一个ROLLUP index，之后再删除原ROLLUP。

查看变更作业

查看变更作业，查看正在执行或已经完成的Schema变更作业。当一次Schema变更作业涉及到多个Index 时，该命令会显示多行，每行对应一个Index。

语法

SHOW ALTER TABLE COLUMN;

示例

SHOW ALTER TABLE COLUMN\G;
*************************** 1. row ***************************
        JobId: 20021
    TableName: tbl1
   CreateTime: 2019-08-05 23:03:13
   FinishTime: 2019-08-05 23:03:42
    IndexName: tbl1
      IndexId: 20022
OriginIndexId: 20017
SchemaVersion: 2:792557838
TransactionId: 10023
        State: FINISHED
          Msg: 
     Progress: NULL
      Timeout: 86400
1 row in set (0.00 sec)

各个列代表的含义如下

参数名称

描述

JobId

每个Schema变更作业的唯一ID。

TableName

Schema变更对应的BASE表的表名。

CreateTime

作业创建时间。

FinishedTime

作业结束时间。如未结束，则显示“N/A”。

IndexName

本次修改所涉及的某一个Index的名称。

IndexId

新的Index的唯一ID。

OriginIndexId

旧的Index的唯一ID。

SchemaVersion

以M:N的格式展示。其中M表示本次Schema变更的版本，N表示对应的哈希值。每次Schema变更，版本都会递增。

State

作业所在阶段。

  • PENDING：作业在队列中等待被调度。

  • WAITING_TXN：等待分水岭Transaction ID之前的导入任务完成。

  • RUNNING：历史数据转换中。

  • FINISHED：作业成功。

  • CANCELLED：作业失败。

Msg

如果作业失败，这里会显示失败信息。

Progress

作业进度。只有在RUNNING状态才会显示进度。进度是以M/N的形式显示。其中N为Schema变更涉及的总副本数。M为已完成历史数据转换的副本数。

Timeout

作业超时时间。单位秒。

取消变更作业

取消变更作业，在作业状态不为FINISHED或CANCELLED的情况下，可以通过以下命令取消Schema变更作业。

语法

CANCEL ALTER TABLE COLUMN FROM <tbl_name>;

参数说明

参数名称

参数说明

tbl_name

表名称。

示例

撤销针对示例表example_db.my_table的ALTER COLUMN操作。

CANCEL ALTER TABLE COLUMN FROM example_db.my_table;

相关配置​

FE配置​​

  • alter_table_timeout_second：作业默认超时时间，默认为86400秒。

BE配置​

  • alter_tablet_worker_count：在BE端用于执行历史数据转换的线程数，默认为3。如果希望加快Schema变更作业的速度，可以适当调大这个参数后重启BE。但过多的转换线程可能会导致IO压力增加，影响其他操作。该线程和ROLLUP作业共用。

  • alter_index_worker_count：在BE端用于执行历史数据构建索引的线程数。默认为3。如果希望加快Index变更作业的速度，可以适当调大这个参数后重启BE。但过多的线程可能会导致IO压力增加，影响其他操作。

    说明

    该参数只支持倒排索引。

常见问题​

  • Q：Schema变更的执行速度。

    A：目前Schema变更执行速度按照最差效率估计约为10MB/s。可以根据这个速率来设置作业的超时时间。

  • Q：提交作业报错Table xxx is not stable. ...。

    A：Schema变更只有在表数据完整且未进行均衡操作的状态下才可以开始。如果表的某些数据分片副本不完整，或者某些副本正在进行均衡操作，则提交会被拒绝。

    数据分片副本是否完整，可以通过以下命令查看：

    ADMIN SHOW REPLICA STATUS FROM tbl WHERE STATUS != "OK";

    如果有返回结果，则说明有副本有问题。通常系统会自动修复这些问题，也可以通过以下命令优先修复这个表。

    ADMIN REPAIR TABLE tbl1;

    或者可以通过以下命令查看是否有正在运行的均衡任务：

    SHOW PROC "/cluster_balance/pending_tablets";

    可以等待均衡任务完成，或者通过以下命令临时禁止均衡操作：

    ADMIN SET FRONTEND CONFIG ("disable_balance" = "true");

上一篇：NGram BloomFilter索引下一篇：数据导入