Efficient manual cleanup of foreign keys in MySQL [closed]

Question

I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added some manual cleanup maintenance scripts instead. These queries are causing problems as they appear to be very inefficient, so I'm wondering how they can be made to work more efficiently.

A typical arrangement is this:

t1

id, field1

t2

id, t1_id, field1

t1_id in t2 is a foreign key pointing at the id field of t1. Each table has id set as its primary key, and t2 has a regular index on t1_id.

So, instead of a foreign key constraint dealing with cascade deleting records in t2 when a parent record in t1 is deleted, I have a cleanup query like this:

DELETE t2.* FROM t2 LEFT JOIN t1 ON t1.id = t2.t1_id WHERE t2.t1_id IS NOT NULL AND t1.id IS NULL;

in other words, delete any record in t2 that points at a non-existent record in t1.

That works, but it's really slow. Here's the explain for it:

+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows    | filtered | Extra                                |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+
|  1 | SIMPLE      | t2    | range  | t1_id         | t1_id   | 5       | NULL          | 2392097 |   100.00 | Using where                          |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.t2.t1_id |       1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+

The obvious problem here is that the row count is very high, and other questions I've seen on this subject suggest the range match type is at the root of the problem. If I omit the check for NOT NULL on t1_id, it matches the whole of the t2 table, making the row count even higher.

Because this is a single query, it tends to lock everything in t2 until it's complete, holding up other queries. I tried replacing it with separate select and delete, but it doesn't help much.

How can this be done better?

Answer 1

Try batching the deletes, so that you are only checking a small set of records at a time, and so are locking a smaller set of records. You can play around with increase or decreasing the size of @batchSize until you find the "magic number" that performs best for you. Note: I don't normally work in MySQL so my while loop syntax may not be 100% correct, but the general idea is there.

DECLARE @i int;
DECLARE @batchSize int;
DECLARE @maxKey int;

SET @i = 0;
SET @batchSize = 2000;
SET @maxKey = (select max(id) from t2 where t2.tl_id IS NOT NULL);


WHILE (@i < @maxKey) DO
BEGIN
    DELETE t2
    FROM t2
    LEFT JOIN t1 ON t2.t1_id = t1.id
    WHERE t2.id >= @i and t2.id <= @i + @batchSize
        AND t2.t1_id is not null
        AND t1.ID is null
    ;

    SET @i = @i + @batchSize;
END WHILE