MySQL -修剪大量孤立行的最高性能方法

DELETE FROM table_name WHERE id NOT IN (SELECT id FROM related_table);

CREATE TEMPORARY TABLE temp_ids AS SELECT id FROM related_table;
DELETE FROM table_name WHERE id NOT IN (SELECT id FROM temp_ids);
DROP TEMPORARY TABLE temp_ids;

ALTER TABLE table_name PARTITION BY RANGE (column_name) (
    PARTITION p0 VALUES LESS THAN (value1),
    PARTITION p1 VALUES LESS THAN (value2),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);
ALTER TABLE table_name DROP PARTITION p0;

DELIMITER //
CREATE PROCEDURE prune_isolated_rows()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE cur CURSOR FOR SELECT id FROM table_name WHERE id NOT IN (SELECT id FROM related_table);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        DELETE FROM table_name WHERE id = id;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;
CALL prune_isolated_rows();