DELETE performance on a large MySQL table with index

Question

Let's say we have an application for a web forum with a MySQL 5.6 database, accessed 24/7 by many users. Now there is a table like this for the metadata of notifications sent to users.

| notifications | CREATE TABLE `notifications` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `message_store_id` bigint(20) unsigned NOT NULL,
 `status` varchar(10) COLLATE ascii_bin NOT NULL,
 `sent_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`,`sent_date`)
) ENGINE=InnoDB AUTO_INCREMENT=736601 DEFAULT CHARSET=ascii COLLATE=ascii_bin |

There are 1 million rows in this table. For this reason, a certain message_store_id for some reason unexpectedly becomes inefficient, and I plan to delete all entries with this message_store_id with a single delete statement, for example

DELETE FROM notifications WHERE message_store_id = 12345;

This single statement affects 10% of the table because this message was sent to a very large number of users. In the meantime, these notification tables are available all the time by thousands of users, so an index must be present. Reorganizing an index appears to be very expensive when deleting records, so I'm afraid to do this and reduce downtime by maximizing server resources. However, if I delete the index, delete the records, and then add the index again, I have to close the database for some time, unfortunately, this is not possible for our service.

, MySQL 5.6 , , , . : ? , , ?

Answer 1

/.

• (, ), message_store_id, . message_store_id, X ( X - id), , . . : message_store_id . : , , , alter table add partition , .
• alter table truncate partition , . DELETE , where, / DELETE.

• :

  while (true) {
    // assuming autocommit mode
    delete from table where {your condition} limit 10000;
    // at this moment locks are released and other transactions have a chance
    // to do some stuff.
    if (affected rows == 0) {
      break;
    }
    // This is a good place to insert sleep(5) to give other transactions
    // more time to do their stuff before the next chunk gets deleted.
  }
  

Answer 2

- , .

MySQL LIMIT, , .

, 1000 :

DELETE FROM notifications WHERE message_store_id = 12345 LIMIT 1000;

, ( ). SQL, MySQL SLEEP() 2 , :

SELECT SLEEP(2);

, , MySQL, , , DELETE .