Jak poprawić wydajność InnoDB DELETE?

Mam więc tę tabelę kontroli (śledzi działania na dowolnej tabeli w mojej bazie danych):

CREATE TABLE `track_table` (
  `id` int(16) unsigned NOT NULL,
  `userID` smallint(16) unsigned NOT NULL,
  `tableName` varchar(255) NOT NULL DEFAULT '',
  `tupleID` int(16) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `action` char(12) NOT NULL DEFAULT '',
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableName`,`tupleID`,`date_insert`),
  KEY `actionDate` (`action`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

i muszę zacząć archiwizować nieaktualne elementy. Tabela wzrosła do około 50 milionów wierszy, więc najszybszym sposobem, w jaki mogłem usunąć wiersze, było usunięcie tabeli jednocześnie (na podstawie tableName).

Działa to całkiem dobrze, ale w przypadku niektórych tabel, które są obciążone zapisem, nie zostanie ukończone. Moje zapytanie usuwa wszystkie elementy, które są powiązane deletez kombinacją tupleID / tableName:

DELETE FROM track_table WHERE tableName='someTable' AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableName='someTable' AND action='DELETE' AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
)

Pozwoliłem, aby działał na moim serwerze przez 3 dni i nigdy nie został ukończony dla największej tabeli. Wyjaśnij wynik (jeśli przełączę usuwanie, aby wybrać:

| id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

Myślę, że usunięcie 4 milionów wierszy nie powinno zająć 3 dni. Mam mój rozmiar innodb_buffer_pool_size ustawiony na 3 GB, a serwer nie jest skonfigurowany do używania tabeli jeden_plik_pera. Jakie inne sposoby mogę poprawić wydajność usuwania InnoDB? (Uruchamianie MySQL 5.1.43 na Mac OSX)

Możesz usuwać dane partiami.

W SQL Server składnia to delete top Xwiersze z tabeli. Następnie robisz to w pętli, z transakcją dla każdej partii (oczywiście jeśli masz więcej niż jedną instrukcję), aby transakcje były krótkie, a blokady tylko przez krótki czas.

W składni MySQL: DELETE FROM userTable LIMIT 1000

Są na to ograniczenia (nie można użyć LIMIT do usuwania z łączeniami), ale w tym przypadku możesz to zrobić w ten sposób.

Istnieje niebezpieczeństwo dodatkowy do korzystania LIMITze DELETEjeśli chodzi o replikacji; usunięte wiersze czasami nie są usuwane w podrzędnej kolejności w urządzeniu podrzędnym, tak jak zostało usunięte w urządzeniu nadrzędnym.

Spróbuj zastosować podejście oparte na tabeli tymczasowej. Wypróbuj coś takiego:

Krok 1) CREATE TABLE track_table_new LIKE track_table;

Krok 2) INSERT INTO track_table_new SELECT * FROM track_table WHERE action='DELETE' AND date_insert >= DATE_SUB(CURDATE(), INTERVAL 30 day);

Krok 3) ALTER TABLE track_table RENAME track_table_old;

Krok 4) ALTER TABLE track_table_new RENAME track_table;

Krok 5) DROP TABLE track_table_old;

Nie uwzględniłem pola krotki w kroku 2. Sprawdź, czy to daje pożądany efekt. Jeśli tego właśnie chcesz, możesz całkowicie porzucić pole krotki, chyba że użyjesz pola krotki z innych powodów.

Usunięcie niechcianych wierszy w partii powinno sprawić, że inna operacja będzie wykonalna. Ale usunięcie operacji ma warunki, więc upewnij się, że istnieje odpowiedni indeks dla kolumn względem warunków.

Ponieważ MySQL nie obsługuje pełnej funkcji luźnego skanowania indeksu, możesz spróbować dostosować sekwencję KEY actionDate (action, date_insert)do KEY actionDate (date_insert, action). Z prefiksem „date_insert” MySQL powinien używać tego indeksu do skanowania wierszy poprzedzających warunek datetime.

Z takim indeksem możesz pisać SQL jako:

DELETE
FROM track_table
WHERE tableName='someTable'
    AND action='DELETE'
    AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
LIMIT 1000 -- Your size of batch

| id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

-Pięść, z twojego wyjaśnienia key_len tak duży => musisz zmniejszyć rozmiar tak mały, jak to możliwe. W przypadku zapytania myślę, że najlepszym sposobem jest zmiana typu danych pola akcji z char (12) na tinyint, więc mapowanie danych wygląda następująco:

1: -> DELETE
2: -> UPDATE
3: -> INSERT
...

i możesz zmienić table_id zamiast tablename. DDL dla najlepszej wydajności może:

CREATE TABLE `track_table` (
  `id` int(11) unsigned NOT NULL,
  `userID` smallint(6) unsigned NOT NULL,
  `tableid` smallint(6) UNSIGNED NOT NULL DEFAULT 0,
  `tupleID` int(11) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `actionid` tinyin(4) UNSIGNED NOT NULL DEFAULT 0,
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableid`,`tupleID`,`date_insert`),
  KEY `actionDate` (`actionid`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `actions` (
  `id` tinyint(4) unsigned NOT NULL 
  `actionname` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table_name` (
  `id` tinyint(4) unsigned NOT NULL 
  `tablename` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

więc zapytanie może być uruchomione w następujący sposób:

DELETE FROM track_table WHERE tableid=@tblid AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableid=@tblid AND actionid=@actionid AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
).

Ale najszybszym sposobem było użycie partycji. więc możesz upuścić partycję. Obecnie mój stół ma około 40 mil wierszy. i aktualizuję co godzinę (400k aktualizacji wierszy za każdym razem), i mogę upuścić partycję curr_date i ponownie załadować dane do tabeli. polecenie zrzutu bardzo szybko (<100ms). Mam nadzieję, że to pomoże.