数据删除 #

-- 基本删除语法
DELETE FROM table_name
WHERE condition;

-- 示例表
CREATE TABLE logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (user_id, action) VALUES
    (1, 'login'),
    (1, 'view_page'),
    (2, 'login'),
    (2, 'purchase'),
    (3, 'login'),
    (1, 'logout'),
    (2, 'logout');

-- 删除特定记录
DELETE FROM logs WHERE id = 1;

-- 查看结果
SELECT * FROM logs;

-- 各种条件删除

-- 等于条件
DELETE FROM logs WHERE user_id = 3;

-- 范围条件
DELETE FROM logs 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- IN 条件
DELETE FROM logs WHERE id IN (2, 3, 4);

-- LIKE 条件
DELETE FROM logs WHERE action LIKE '%view%';

-- 多条件组合
DELETE FROM logs 
WHERE user_id = 1 AND action = 'logout';

-- 子查询条件
DELETE FROM logs 
WHERE user_id IN (SELECT id FROM users WHERE status = 'inactive');

-- 危险操作: 删除所有数据
DELETE FROM logs;
-- 这会删除表中所有数据！

-- 更安全的做法: 使用 TRUNCATE
TRUNCATE TABLE logs;
-- 更快，但会重置自增ID

-- 或者使用事务确认
START TRANSACTION;
DELETE FROM logs WHERE 1=1;  -- 明确条件
SELECT COUNT(*) FROM logs;   -- 确认结果
-- ROLLBACK;  -- 如果误操作可以回滚
COMMIT;

-- 危险: 没有 WHERE 子句
DELETE FROM logs;
-- 删除所有数据！

-- 安全做法1: 始终使用 WHERE 子句
DELETE FROM logs WHERE id = 999;

-- 安全做法2: 先查询后删除
SELECT * FROM logs WHERE user_id = 1;
-- 确认后删除
DELETE FROM logs WHERE user_id = 1;

-- 安全做法3: 使用 LIMIT
DELETE FROM logs WHERE user_id = 1 LIMIT 100;

-- 安全做法4: 使用事务
START TRANSACTION;
DELETE FROM logs WHERE user_id = 1;
SELECT COUNT(*) FROM logs WHERE user_id = 1;
-- 确认后提交
COMMIT;
-- 或回滚
-- ROLLBACK;

-- 软删除: 不真正删除，只标记状态
ALTER TABLE logs ADD COLUMN deleted_at TIMESTAMP NULL;

-- 软删除操作
UPDATE logs 
SET deleted_at = NOW() 
WHERE id = 1;

-- 查询时过滤已删除数据
SELECT * FROM logs WHERE deleted_at IS NULL;

-- 真正删除时
DELETE FROM logs WHERE deleted_at IS NOT NULL;

-- 创建有外键关联的表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- 删除有外键关联的数据
-- 方法1: 先删除子表数据
DELETE FROM order_items WHERE order_id = 1;
DELETE FROM orders WHERE id = 1;

-- 方法2: 使用级联删除 (建表时指定)
CREATE TABLE order_items_cascade (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- 删除订单时自动删除关联的订单项
DELETE FROM orders WHERE id = 1;

-- 大量数据分批删除
-- 每次删除 1000 条
DELETE FROM logs 
WHERE created_at < '2024-01-01'
LIMIT 1000;

-- 循环执行直到影响行数为 0
-- 可以在应用层循环调用

-- 使用存储过程批量删除
DELIMITER //
CREATE PROCEDURE batch_delete_logs(IN days INT)
BEGIN
    DECLARE affected INT DEFAULT 1;
    WHILE affected > 0 DO
        DELETE FROM logs 
        WHERE created_at < DATE_SUB(NOW(), INTERVAL days DAY)
        LIMIT 1000;
        SET affected = ROW_COUNT();
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程
CALL batch_delete_logs(30);

-- 按ID范围删除
DELETE FROM logs WHERE id BETWEEN 1 AND 10000;
DELETE FROM logs WHERE id BETWEEN 10001 AND 20000;

-- 按时间范围删除
DELETE FROM logs 
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

-- 按分区删除 (如果表有分区)
ALTER TABLE logs DROP PARTITION p2023;

-- 创建需要保留的数据临时表
CREATE TEMPORARY TABLE logs_to_keep AS
SELECT * FROM logs 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 清空原表
TRUNCATE TABLE logs;

-- 重新插入需要保留的数据
INSERT INTO logs 
SELECT * FROM logs_to_keep;

-- 删除临时表
DROP TEMPORARY TABLE logs_to_keep;

TRUNCATE vs DELETE 对比
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   TRUNCATE:                                                 │
│   ├── 删除所有数据，保留表结构                             │
│   ├── 重置自增ID                                           │
│   ├── 不触发 DELETE 触发器                                 │
│   ├── 更快 (DDL操作)                                       │
│   ├── 不能回滚 (部分情况)                                  │
│   └── 不记录每行删除日志                                   │
│                                                             │
│   DELETE:                                                   │
│   ├── 可按条件删除                                         │
│   ├── 不重置自增ID                                         │
│   ├── 触发 DELETE 触发器                                   │
│   ├── 较慢 (DML操作)                                       │
│   ├── 可以回滚                                             │
│   └── 记录每行删除日志                                     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 清空表数据
TRUNCATE TABLE logs;

-- 效果等同于
DROP TABLE logs;
CREATE TABLE logs (...);  -- 重新创建

-- TRUNCATE 后自增ID重置
CREATE TABLE test (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

INSERT INTO test (name) VALUES ('a'), ('b'), ('c');
SELECT * FROM test;  -- id: 1, 2, 3

TRUNCATE TABLE test;
INSERT INTO test (name) VALUES ('d');
SELECT * FROM test;  -- id: 1 (重新开始)

-- 创建关联表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    status VARCHAR(20)
);

CREATE TABLE user_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, status) VALUES
    ('Alice', 'active'),
    ('Bob', 'inactive'),
    ('Carol', 'deleted');

INSERT INTO user_logs (user_id, action) VALUES
    (1, 'login'), (2, 'login'), (3, 'login');

-- 关联删除 (删除不活跃用户的日志)
DELETE ul
FROM user_logs ul
JOIN users u ON ul.user_id = u.id
WHERE u.status = 'inactive';

-- 删除多个表的数据
DELETE u, ul
FROM users u
LEFT JOIN user_logs ul ON u.id = ul.user_id
WHERE u.status = 'deleted';

-- 使用子查询条件删除
DELETE FROM user_logs 
WHERE user_id IN (
    SELECT id FROM users WHERE status = 'deleted'
);

-- 使用 EXISTS
DELETE FROM user_logs ul
WHERE EXISTS (
    SELECT 1 FROM users u 
    WHERE u.id = ul.user_id AND u.status = 'deleted'
);

-- 按时间排序删除最早的记录
DELETE FROM logs 
ORDER BY created_at ASC
LIMIT 1000;

-- 按ID排序删除
DELETE FROM logs 
ORDER BY id DESC
LIMIT 100;

-- 删除每个用户最早的日志
DELETE l1 FROM logs l1
LEFT JOIN (
    SELECT user_id, MIN(id) as min_id
    FROM logs
    GROUP BY user_id
) l2 ON l1.user_id = l2.user_id AND l1.id = l2.min_id
WHERE l2.min_id IS NOT NULL;

-- 删除旧数据，只保留每个用户最新的10条日志
DELETE FROM logs 
WHERE id NOT IN (
    SELECT id FROM (
        SELECT id FROM logs l2
        WHERE l2.user_id = logs.user_id
        ORDER BY created_at DESC
        LIMIT 10
    ) tmp
);

-- 或者使用窗口函数
DELETE FROM logs 
WHERE id IN (
    SELECT id FROM (
        SELECT id, 
               ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
        FROM logs
    ) ranked
    WHERE rn > 10
);

DELETE 性能优化建议
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   1. 使用索引条件                                           │
│   ├── WHERE 条件使用索引列                                 │
│   └── 避免全表扫描                                         │
│                                                             │
│   2. 分批删除                                               │
│   ├── 大量删除分批进行                                     │
│   └── 使用 LIMIT 控制批次大小                              │
│                                                             │
│   3. 非高峰期执行                                           │
│   ├── 大批量删除在低峰期执行                               │
│   └── 减少对业务的影响                                     │
│                                                             │
│   4. 考虑使用 TRUNCATE                                      │
│   ├── 清空表时使用 TRUNCATE                                │
│   └── 比 DELETE 快得多                                     │
│                                                             │
│   5. 删除前备份                                             │
│   ├── 重要数据先备份                                       │
│   └── 使用事务保护                                         │
│                                                             │
│   6. 监控删除进度                                           │
│   ├── 检查影响行数                                         │
│   └── 监控系统负载                                         │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 查看删除执行计划
EXPLAIN DELETE FROM logs WHERE user_id = 1;

-- 分析执行计划
EXPLAIN ANALYZE DELETE FROM logs WHERE user_id = 1;

-- 事务保护
START TRANSACTION;

DELETE FROM logs WHERE user_id = 1;

-- 检查结果
SELECT COUNT(*) FROM logs WHERE user_id = 1;

-- 如果误删，可以回滚
ROLLBACK;

-- 确认无误后提交
-- COMMIT;

-- TiDB 支持 Flashback 功能
-- 恢复被删除的表

FLASHBACK TABLE logs TO logs_recovered;

-- 恢复到指定时间点
FLASHBACK TABLE logs TO TIMESTAMP '2024-03-27 10:00:00';

# 使用 BR 工具恢复数据
br restore table \
    --db mydb \
    --table logs \
    --storage "local:///backup/path" \
    --pd "pd:2379"

-- 问题: 大量删除超时
-- Error: Lock wait timeout exceeded

-- 解决方案: 分批删除
DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
-- 循环执行

-- 或调整超时时间
SET SESSION innodb_lock_wait_timeout = 120;

-- 问题: 删除数据后磁盘空间不释放

-- 原因: TiDB 使用 MVCC，删除只是标记
-- 需要等待 GC 回收

-- 查看 GC 状态
SELECT VARIABLE_NAME, VARIABLE_VALUE 
FROM mysql.tidb 
WHERE VARIABLE_NAME = 'tikv_gc_life_time';

-- 手动触发 GC (谨慎使用)
-- UPDATE mysql.tidb SET VARIABLE_VALUE = '1m' 
-- WHERE VARIABLE_NAME = 'tikv_gc_life_time';

-- 问题: 大量删除影响性能

-- 解决方案:
-- 1. 分批删除
DELETE FROM logs WHERE id <= 10000 LIMIT 1000;

-- 2. 低峰期执行

-- 3. 使用分区表，直接删除分区
ALTER TABLE logs DROP PARTITION p202301;