后台运行MySQL存储过程时如何刷新stdout及优化批量删除？

问题背景 #

我的数据库持续接收外部进程传入的数据，近期发现偶尔会插入脏数据，可通过以下SQL找出这些数据：

CREATE TEMPORARY TABLE singletons
    AS SELECT
        COUNT(*) number_rows,
        name property
    FROM data_property
    GROUP BY property
    HAVING number_rows = 1;

执行结果：

Query OK, 1045 rows affected (1 min 45.53 sec)
Records: 1045  Duplicates: 0  Warnings: 0

data_property表包含44,694,240行数据，单条删除约耗时5分钟，无法承受一次性删除所有脏数据导致的表锁定（需耗时约一周），因此编写了存储过程进行小批量删除：

CREATE TEMPORARY TABLE delete_me LIKE singletons;
DELIMITER %%
CREATE PROCEDURE delete_bad_rows(v_start INT, v_step INT, v_end INT)
BEGIN
    DECLARE v_offset INT DEFAULT v_start;
    WHILE v_offset < v_end
    DO
        TRUNCATE TABLE delete_me;
        INSERT INTO delete_me
            SELECT * FROM singletons
                ORDER BY property
                LIMIT v_offset, v_step;
        DELETE LOW_PRIORITY data_property
            FROM data_property, delete_me
            WHERE name=property;
        SET v_offset = v_offset + v_step;
    END WHILE;
END;
%%
DELIMITER ;
CALL delete_bad_rows(0,3,3);

在命令行直接运行时一切正常，且能查看执行状态，但使用以下命令后台执行时：

echo "SOURCE delete_me.sql; CALL delete_bad_rows(0,3,1045);" | \
nohup ./bin/mysql -u root -p mydata --password=xxxxxxxx >delete_me.log

delete_me.log文件直到进程被终止时才会写入所有输出，请问是否有办法关闭或阻止输出缓冲？此外，是否有办法加速脏数据的删除操作？

附：相关表结构及执行计划 #

data_property表结构 #

DESCRIBE data_property;

执行结果：

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| variableid | bigint(20)    | NO   | PRI | NULL    |       |
| name       | char(8)       | NO   | PRI | NULL    |       |
| value      | varchar(1024) | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

delete_me表结构 #

DESCRIBE delete_me;

执行结果：

+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| number_rows | bigint(21) | NO   |     | 0       |       |
| property    | char(8)    | NO   |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除语句执行计划 #

EXPLAIN DELETE data_property FROM data_property, delete_me WHERE name=property;

执行结果：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | DELETE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set (8 min 32.90 sec)

解决方案 #

一、关闭输出缓冲的方法 #

1. 禁用MySQL客户端缓冲

在mysql命令中添加--batch --disable-column-names参数（或简写为-B -n），强制客户端无缓冲输出，执行结果会实时写入日志：

echo "SOURCE delete_me.sql; CALL delete_bad_rows(0,3,1045);" | \
nohup ./bin/mysql -u root -p mydata --password=xxxxxxxx -B -n >delete_me.log 2>&1

2. 用stdbuf强制无缓冲输出

通过stdbuf工具直接修改输出流的缓冲模式，无需调整MySQL参数：

echo "SOURCE delete_me.sql; CALL delete_bad_rows(0,3,1045);" | \
stdbuf -o0 ./bin/mysql -u root -p mydata --password=xxxxxxxx | nohup tee delete_me.log >/dev/null 2>&1

stdbuf -o0会将标准输出设置为无缓冲，确保每有输出就立即写入文件。

二、加速脏数据删除的优化方案 #

1. 给临时表delete_me添加索引

当前delete_me的property字段无索引，删除时需要全表扫描data_property匹配数据，这是耗时核心原因。修改临时表创建语句，添加索引：

CREATE TEMPORARY TABLE delete_me LIKE singletons;
ALTER TABLE delete_me ADD INDEX idx_property (property);

添加索引后，删除操作可以通过索引快速定位匹配行，大幅减少扫描行数。

2. 改用显式JOIN写法优化删除语句

将隐式关联改为显式JOIN，帮助优化器更好地利用索引：

DELETE LOW_PRIORITY dp
FROM data_property dp
INNER JOIN delete_me dm ON dp.name = dm.property;

3. 增大批量删除步长

当前每次仅删除3条脏数据对应的行，循环次数过多导致整体耗时增加。可以逐步增大步长（比如调整为100、500），测试锁表时间是否在业务可接受范围内：

CALL delete_bad_rows(0, 100, 1045);

步长越大，循环次数越少，整体效率越高，但需注意监控锁表情况，避免影响正常业务。

4. 按主键范围分批删除

利用variableid主键索引，先提取脏数据的主键，再按范围分批删除：

-- 提取脏数据的主键到临时表
CREATE TEMPORARY TABLE bad_ids AS
SELECT dp.variableid
FROM data_property dp
INNER JOIN singletons s ON dp.name = s.property;

-- 给主键临时表加索引
ALTER TABLE bad_ids ADD INDEX idx_variableid (variableid);

-- 按主键范围分批删除的存储过程
DELIMITER %%
CREATE PROCEDURE delete_by_id_range(v_step INT)
BEGIN
    DECLARE v_min_id BIGINT;
    DECLARE v_max_id BIGINT;
    DECLARE v_current_id BIGINT;
    
    SELECT MIN(variableid), MAX(variableid) INTO v_min_id, v_max_id FROM bad_ids;
    SET v_current_id = v_min_id;
    
    WHILE v_current_id < v_max_id DO
        DELETE LOW_PRIORITY FROM data_property
        WHERE variableid BETWEEN v_current_id AND v_current_id + v_step
        AND variableid IN (SELECT variableid FROM bad_ids);
        
        SET v_current_id = v_current_id + v_step + 1;
    END WHILE;
END;
%%
DELIMITER ;

这种方式利用主键索引快速定位数据，删除效率远高于按name匹配。

5. 调整MySQL锁相关配置

• 若使用InnoDB引擎，可适当调小innodb_lock_wait_timeout参数，减少锁等待时间；
• 开启innodb_file_per_table，让每个表拥有独立表空间，删除后释放空间更快；
• 若业务中写操作优先级更高，可去掉LOW_PRIORITY，让删除操作优先执行。

内容的提问来源于stack exchange，提问作者samwyse