Mysql быстрая вставка, медленное удаление, неправильный индекс

Question

У меня есть событие, которое генерирует некоторые данные, представляющие accumulators в том, что я называю transaction, Оказалось, что генерация аккумулятора с нуля намного быстрее, чем попытка сопоставить его с существующими. Поэтому я дал им transaction_id, создать их, а затем в одном запросе, а затем удалить предыдущие транзакции.

Создание ок. Для 2 миллионов строк в таблице требуется около 40 секунд, однако в настоящее время удаление занимает около 20-30 минут, используя запрос ниже.

DELETE
  FROM accumulator
WHERE id_acca_set = @set_id
  AND ( transaction_id != @transaction_id or transaction_id is null);

Глядя на состояние innodb, я вижу следующее при выполнении запроса. Насколько я могу судить, на данном этапе, похоже, нет противоречивой блокировки.

---TRANSACTION 11535589892, ACTIVE 259 sec updating or deleting, thread declared inside InnoDB 4657
mysql tables in use 1, locked 1
29009 lock struct(s), heap size 3776720, 1195753 row lock(s), undo log entries 1195753
MySQL thread id 108262, OS thread handle 131874376460032, query id 9689717638 event_scheduler updating

accumulator таблица определена согласно ниже. Я заметил, что он не использует IDX_accumulator5 но IDX_accumulator3 который не включает transaction_id вместо.

CREATE TABLE acca.accumulator (
id bigint(20) NOT NULL AUTO_INCREMENT,
id_acca_set int(1) NOT NULL DEFAULT 0,
id_event bigint(20) NOT NULL DEFAULT 0,
id_back_outcome bigint(20) NOT NULL DEFAULT 0,
id_lay_outcome bigint(20) NOT NULL DEFAULT 0,
acca_id varchar(255) DEFAULT NULL,
prev_acca_id varchar(255) DEFAULT NULL,
leg_number int(11) NOT NULL,
score double DEFAULT NULL,
transaction_id varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
INDEX IDX_accumulator (id_acca_set, acca_id, transaction_id),
INDEX IDX_accumulator2 (id_acca_set, leg_number, acca_id, transaction_id),
INDEX IDX_accumulator3 (id_acca_set, id_event, id_back_outcome, id_lay_outcome, leg_number),
INDEX IDX_accumulator4 (id_acca_set, prev_acca_id, id_event),
INDEX IDX_accumulator5 (id_acca_set, transaction_id),
INDEX IDX_accumulator6 (transaction_id, id_acca_set, leg_number, score)
)
ENGINE = INNODB
AUTO_INCREMENT = 242051170
AVG_ROW_LENGTH = 282
CHARACTER SET utf8
COLLATE utf8_general_ci
ROW_FORMAT = DYNAMIC;

Я использую MySQL 5.7.13 на CentOS 7. Я не использую память подкачки и у меня осталось около 10 ГБ свободной памяти, 6 ГБ выделено для буфера INNODB. Настройки InnoDB в my.cnf являются:

innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_commit_concurrency = 4
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 8
innodb_thread_sleep_delay = 100
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_timeout = 10

Answer 1

Я не знаю ни одной СУБД, которая будет использовать индекс для разрешения отрицательного предиката (action_id!= @Transaction_id).

Я не понимаю, почему вы добавляете новые записи, а затем удаляете старые. Если вы сделаете это наоборот (усечете таблицу), тогда это займет совсем немного времени. Если вы хотите убедиться, что у вас есть данные, к которым можно обращаться, то альтернативным подходом будет переименование существующей таблицы, а затем создание новой (с исходным именем) для хранения новых данных.