Spanner数据删除 #

Spanner数据删除方式
├── DML语句 (DELETE)
│   ├── 标准SQL语法
│   ├── 支持条件删除
│   ├── 返回影响行数
│   └── 支持事务
│
└── Mutation API
    ├── 编程接口
    ├── 批量删除
    ├── 需要指定主键
    └── 高性能

DELETE FROM table_name
WHERE condition;

CREATE TABLE users (
    user_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    email STRING(255),
    status STRING(20) DEFAULT 'active'
) PRIMARY KEY (user_id);

CREATE TABLE orders (
    user_id INT64 NOT NULL,
    order_id INT64 NOT NULL,
    status STRING(20)
) PRIMARY KEY (user_id, order_id)
  INTERLEAVE IN PARENT users ON DELETE CASCADE;

-- 删除单行
DELETE FROM users WHERE user_id = 1;

-- 删除多行
DELETE FROM users WHERE user_id IN (1, 2, 3);

-- 删除所有行(谨慎使用)
DELETE FROM users WHERE TRUE;

-- 使用比较条件
DELETE FROM users WHERE age > 60;

-- 使用IN条件
DELETE FROM users WHERE status IN ('inactive', 'deleted');

-- 使用子查询
DELETE FROM users WHERE user_id IN (
    SELECT user_id FROM inactive_users
);

-- 使用LIKE条件
DELETE FROM users WHERE email LIKE '%@temp.com';

-- 使用时间条件
DELETE FROM logs 
WHERE created_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

-- Spanner不支持RETURNING子句
-- 需要先查询再删除

-- 方式1: 先查询后删除
SELECT * FROM users WHERE user_id = 1;
DELETE FROM users WHERE user_id = 1;

-- 方式2: 使用事务
-- 在事务中先读取再删除

// Java Delete Mutation
import com.google.cloud.spanner.Key;
import com.google.cloud.spanner.Mutation;

// 删除单行
Mutation mutation = Mutation.delete("users", Key.of(1L));

// 删除复合主键行
Mutation mutation = Mutation.delete("orders", Key.of(1L, 100L));

// 执行删除
DatabaseClient client = spanner.getDatabaseClient(databaseId);
client.write(Collections.singletonList(mutation));

// Java批量删除
List<Mutation> mutations = new ArrayList<>();

for (long userId : userIds) {
    mutations.add(Mutation.delete("users", Key.of(userId)));
}

client.write(mutations);

// Java删除主键范围
import com.google.cloud.spanner.KeyRange;
import com.google.cloud.spanner.KeySet;

// 删除单个范围
KeySet keySet = KeySet.range(KeyRange.closedOpen(
    Key.of(1L),   // 起始主键
    Key.of(100L)  // 结束主键(不包含)
));

Mutation mutation = Mutation.delete("users", keySet);

# Python删除
def delete_user(database, user_id):
    with database.batch() as batch:
        batch.delete("users", spanner.KeySet([spanner.Key([user_id])]))

# 批量删除
def batch_delete_users(database, user_ids):
    keys = [spanner.Key([uid]) for uid in user_ids]
    with database.batch() as batch:
        batch.delete("users", spanner.KeySet(keys=keys))

// Go删除
func deleteUser(ctx context.Context, client *spanner.Client, userID int64) error {
    m := spanner.Delete("users", spanner.Key{userID})
    _, err := client.Apply(ctx, []*spanner.Mutation{m})
    return err
}

// 批量删除
func batchDeleteUsers(ctx context.Context, client *spanner.Client, userIDs []int64) error {
    var mutations []*spanner.Mutation
    for _, id := range userIDs {
        mutations = append(mutations, spanner.Delete("users", spanner.Key{id}))
    }
    _, err := client.Apply(ctx, mutations)
    return err
}

-- 创建交错表时指定ON DELETE CASCADE
CREATE TABLE orders (
    user_id INT64 NOT NULL,
    order_id INT64 NOT NULL
) PRIMARY KEY (user_id, order_id)
  INTERLEAVE IN PARENT users ON DELETE CASCADE;

-- 删除用户时自动删除所有订单
DELETE FROM users WHERE user_id = 1;
-- 相关订单自动删除

-- 如果没有ON DELETE CASCADE，需要手动删除

-- 方式1: 分别删除
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE user_id = 1;

-- 方式2: 使用事务
BEGIN TRANSACTION;
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE user_id = 1;
COMMIT;

// Java事务中级联删除
TransactionRunner runner = client.readWriteTransaction();
runner.run(transaction -> {
    // 先删除子表
    transaction.buffer(Mutation.delete("orders", 
        KeySet.prefixRange(Key.of(1L))));
    
    // 再删除父表
    transaction.buffer(Mutation.delete("users", Key.of(1L)));
    
    return null;
});

-- Spanner没有TRUNCATE语句
-- 使用DELETE删除所有行
DELETE FROM users WHERE TRUE;

-- 或使用Mutation删除所有行

// Java清空表
Mutation mutation = Mutation.delete("users", KeySet.all());
client.write(Collections.singletonList(mutation));

# Python清空表
def truncate_table(database, table_name):
    with database.batch() as batch:
        batch.delete(table_name, spanner.KeySet(all_=True))

-- 分批删除大量数据，避免长事务

-- 第一批
DELETE FROM logs 
WHERE created_at < TIMESTAMP '2023-01-01 00:00:00'
LIMIT 10000;

-- 第二批
DELETE FROM logs 
WHERE created_at < TIMESTAMP '2023-01-01 00:00:00'
LIMIT 10000;

-- 重复直到删除完成

// Java分批删除
public void deleteInBatches(DatabaseClient client, String tableName, 
                           KeyRange range, int batchSize) {
    boolean hasMore = true;
    
    while (hasMore) {
        int deleted = client.readWriteTransaction().run(transaction -> {
            // 读取一批主键
            List<Key> keys = new ArrayList<>();
            try (ResultSet rs = transaction.read(
                tableName, 
                KeySet.range(range),
                Collections.singletonList("user_id"),
                ReadOption.limit(batchSize))) {
                
                while (rs.next()) {
                    keys.add(rs.getCurrentRowAsStruct().getKey());
                }
            }
            
            if (keys.isEmpty()) {
                return 0;
            }
            
            // 删除这批数据
            for (Key key : keys) {
                transaction.buffer(Mutation.delete(tableName, key));
            }
            
            return keys.size();
        });
        
        hasMore = deleted == batchSize;
    }
}

// Java事务删除
TransactionRunner runner = client.readWriteTransaction();
runner.run(transaction -> {
    // 检查条件
    Struct row = transaction.readRow("users", 
        Key.of(1L), Arrays.asList("status"));
    
    if ("inactive".equals(row.getString(0))) {
        // 只有inactive用户才能删除
        transaction.buffer(Mutation.delete("users", Key.of(1L)));
    }
    
    return null;
});

# Python事务删除
def conditional_delete(database, user_id):
    def transaction_func(transaction):
        # 读取状态
        row = list(transaction.read(
            "users", ["status"], 
            spanner.KeySet([spanner.Key([user_id])])
        ))
        
        if row and row[0][0] == 'inactive':
            # 删除
            transaction.delete(
                "users", 
                spanner.KeySet([spanner.Key([user_id])])
            )
    
    database.run_in_transaction(transaction_func)

批量删除建议:
├── 使用Mutation批量删除
├── 合理设置批量大小
├── 避免单行删除循环
└── 使用分区删除大量数据

-- 创建索引加速删除条件的查询
CREATE INDEX idx_logs_created ON logs(created_at);

-- 删除时利用索引
DELETE FROM logs 
WHERE created_at < TIMESTAMP '2023-01-01';

大量数据删除策略:
├── 分批删除: 避免长事务
├── 使用Mutation: 提高性能
├── 非高峰期执行: 减少影响
└── 监控进度: 确保完成

-- 有外键引用时无法直接删除
DELETE FROM departments WHERE dept_id = 1;
-- ERROR: 有外键引用

-- 需要先删除引用行
DELETE FROM employees WHERE dept_id = 1;
DELETE FROM departments WHERE dept_id = 1;

-- 交错子表存在时无法删除父行(除非ON DELETE CASCADE)
DELETE FROM users WHERE user_id = 1;
-- 如果有orders子表且没有CASCADE，会报错

-- 解决方案:
-- 1. 使用ON DELETE CASCADE
-- 2. 先删除子表数据

删除数据恢复:
├── 使用备份恢复
├── 使用时间点恢复
├── 无法恢复已删除数据
└── 建议重要数据先备份

-- 添加删除标记列
ALTER TABLE users ADD COLUMN is_deleted BOOL DEFAULT FALSE;
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;

-- 软删除(标记为已删除)
UPDATE users 
SET is_deleted = TRUE, 
    deleted_at = CURRENT_TIMESTAMP()
WHERE user_id = 1;

-- 查询时过滤已删除数据
SELECT * FROM users WHERE is_deleted = FALSE;

-- 恢复数据
UPDATE users 
SET is_deleted = FALSE, 
    deleted_at = NULL
WHERE user_id = 1;

-- 永久删除
DELETE FROM users WHERE is_deleted = TRUE AND deleted_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

// Java软删除
Mutation mutation = Mutation.newUpdateBuilder("users")
    .set("user_id").to(userId)
    .set("is_deleted").to(true)
    .set("deleted_at").to(Value.timestamp(com.google.cloud.Timestamp.now()))
    .build();

client.write(Collections.singletonList(mutation));

选择建议:
├── 条件删除: DML
├── 批量删除: Mutation
├── 大量数据: 分批删除
└── 重要数据: 软删除

安全删除建议:
├── 重要数据使用软删除
├── 删除前备份数据
├── 使用事务保证一致性
├── 处理外键和交错表约束
└── 监控删除进度

性能优化建议:
├── 使用批量删除
├── 分批删除大量数据
├── 利用索引加速条件查询
├── 非高峰期执行大批量删除
└── 监控删除性能

1. 选择合适的删除方式
   └── 根据场景选择DML或Mutation

2. 处理约束关系
   └── 外键和交错表约束

3. 使用软删除
   └── 重要数据可恢复

4. 分批删除大量数据
   └── 避免长事务

5. 监控删除操作
   └── 确保正确执行