通过DELETE实现删除
本文介绍如何在云数据库 SelectDB 版实例中,通过DELETE命令删除数据。
概述
DELETE不同于其他导入方式,它是一个同步过程,与INSERT INTO相似。所有的DELETE操作在SelectDB中是一个独立的导入作业。通常DELETE语句需要指定表和分区以及删除的条件来筛选要删除的数据,并会同时删除BASE表和ROLLUP表的数据。
使用方式
该语句用于按条件删除指定Table及其ROLLUP中的数据。
语法
DELETE FROM table_name [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
WHERE
column_name op { value | value_list } [ AND column_name op { value | value_list } ...];
参数说明
参数名称 | 参数说明 |
table_name | 指定需要删除数据的表。 |
PARTITION partition_name | PARTITIONS (partition_name[, partition_name]) | 可选参数。指定执行删除数据的分区名,如果表不存在此分区,则报错。 |
column_name | 指定需要删除数据的表的列名。 |
op | 逻辑比较操作符,可选类型包括: |
value | value_list | 做逻辑比较的值或值列表。 |
使用聚合类的表模型(AGGREGATE、UNIQUE)只能指定Key列上的条件。
若选定的Key列不存在于某个物化视图或ROLLUP中,则无法进行DELETE。
条件之间只能是“与”的关系。若希望达成“或”的关系,需要将条件分写在两个DELETE语句中。
如果为分区表,删除时需要指定分区。如果不指定,SelectDB会从条件中推断出分区。当分区表未指定分区,或者无法从条件中推断分区的时,需要设置Session变量
delete_without_partition
为 true。# 当前Session set delete_without_partition = true; # 全局 set global delete_without_partition = true;
此时DELETE会应用到所有分区。以下两种情况会使SelectDB无法从条件中推断出分区。
条件中不包含分区列。
分区列的op为not in。
DELETE语句可能会降低执行后一段时间内的查询效率。影响程度取决于语句中指定的删除条件的数量。指定的条件越多,影响越大。
不同于Insert into命令,DELETE不能手动指定
label
。
返回结果
DELETE命令是一个SQL命令,返回结果是同步的,分为以下三种。
执行成功
DELETE顺利执行完成并可见,将返回如下结果,Query OK
表示成功。
DELETE FROM test_tbl PARTITION p1 WHERE k1 = 1;
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'VISIBLE', 'txnId':'4005'}
提交成功,但未可见
SelectDB的事务提交分为两步提交和发布版本。
完成了发布版本步骤后,结果才对您可见。若已经提交成功,那么最终一定会发布成功。SelectDB会尝试在提交完后等待发布版本一段时间,如果超时后发布版本还未完成,会优先返回,提示您提交已经完成。
DELETE FROM test_tbl PARTITION p1 WHERE k1 = 1;
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_e7830c72-eb14-4cb9-bbb6-eebd4511d251', 'status':'COMMITTED', 'txnId':'4005', 'err':'delete job is committed but may be taking effect later' }
返回结果的JSON字符串参数说明如下。
参数名称 | 参数说明 |
rows affected | 表示此次删除影响的行。由于SelectDB的删除目前是逻辑删除,因此当前的这个值恒为0。 |
label | 自动生成的Label,是该导入作业的标识。每个导入作业都有一个在单Database内部唯一的Label。 |
status | 表示数据删除是否可见,如果可见则显示VISIBLE,如果不可见则显示COMMITTED。 |
txnId | 这个Delete job对应的事务ID。 |
err | 显示一些本次删除的详细报错信息。 |
提交失败,事务取消
DELETE语句没有提交成功,将会被SelectDB自动中止,返回如下结果。
DELETE FROM test_tbl PARTITION p1 WHERE k1 > 80;
ERROR 1064 (HY000): errCode = 2, detailMessage = {错误原因}
若删除操作超时,将会返回timeout时间和未完成的(tablet=replica)
DELETE FROM test_tbl PARTITION p1 WHERE k1 > 80;
ERROR 1064 (HY000): errCode = 2, detailMessage = failed to delete replicas from job: 4005, Unfinished replicas:10000=60000, 10001=60000, 10002=60000
通过上述示例,DELETE操作返回结果的正确处理逻辑为:
返回结果为
ERROR 1064 (HY000)
,则表示删除失败。返回结果为
Query OK
,则表示删除执行成功。如果status为
COMMITTED
,表示数据仍不可见,您可以稍等一段时间再用show delete
命令查看结果。如果status为
VISIBLE
,表示数据删除成功。
查看历史记录
您可以通过SHOW DELETE语句查看历史上已执行完成的删除记录。
语法如下。
SHOW DELETE [FROM db_name]
示例如下。
SHOW DELETE FROM test_db;
+-----------+---------------+---------------------+-----------------+----------+
| TableName | PartitionName | CreateTime | DeleteCondition | State |
+-----------+---------------+---------------------+-----------------+----------+
| empty_tbl | p3 | 2020-04-15 23:09:35 | k1 EQ "1" | FINISHED |
| test_tbl | p4 | 2020-04-15 23:09:53 | k1 GT "80" | FINISHED |
+-----------+---------------+---------------------+-----------------+----------+
2 rows in set (0.00 sec)
相关FE配置
TIMEOUT配置
SelectDB删除作业的超时时间限制在30秒到5分钟时间内,具体时间可通过如下配置项调整。
tablet_delete_timeout_second
DELETE自身的超时时间是由分区下Tablet的数量弹性改变的。此项配置为平均一个Tablet所贡献的Timeout时间,默认值为2,单位是秒。假设此次删除所指定分区下有5个Tablet,那么可提供给DELETE的Timeout时间为10秒,由于低于最低超时时间30秒,因此最终超时时间为30秒。
load_straggler_wait_second
如果您预估的删除数据量确实比较大,使得5分钟的上限不足时,您可以通过此项调整Timeout上限,默认值为300,单位是秒。Timeout的具体计算规则为:
TIMEOUT = MIN(load_straggler_wait_second, MAX(30, tablet_delete_timeout_second * tablet_num))
query_timeout
DELETE本身是一个SQL命令,因此删除语句也会受Session限制。Timeout还受Session中的
query_timeout
值影响,因此可以通过SET query_timeout = xxx
来增加超时时间,默认值为900,单位是秒。
IN谓词配置
max_allowed_in_element_num_of_delete
如果在使用IN谓词时需要占用的元素比较多,您可以通过此项调整允许携带的元素上限,默认值为1024。