Wydajność procedury wyzwalającej kontra procedura przechowywana w MySQL

Wpis tutaj na DBA.StackExchange ( Jakie są najlepsze praktyki dla wyzwalaczy do utrzymywania numeru wersji w rekordach? ) Zrodził ciekawe pytanie (przynajmniej interesujące mnie) dotyczące wydajności w MySQL.

Kontekst polega na tym, że chcemy wstawić rekord do tabeli dla każdego aktualizowanego wiersza. Przed zaktualizowaniem wiersza chcemy zapisać poprzednią wartość, a następnie zwiększyć jedną z kolumn (kolumnę „wersja”).

Jeśli zrobimy to wewnątrz wyzwalacza, zadziała to ładnie. W przypadku MySQL wyzwalacze są wiersz po rzędzie , więc byłoby to łatwe rozwiązanie. Wybierz dane aktualnie w tabeli, wstaw je do tabeli rejestrowania i zaktualizuj kolumnę „wersja” w nowych danych.

Można jednak przenieść tę logikę do procedury składowanej. Jeśli to zrobisz, wykonasz wstawianie, a następnie zwiększysz kolumnę „wersja” w tabeli. Wszystko będzie oparte na ustawieniach.

A zatem, jeśli chodzi o wykonanie tej wstawki, czy byłoby bardziej wydajne przy użyciu podejścia opartego na zestawie procedur przechowywanych lub podejścia opartego na wyzwalaczu?

To pytanie dotyczy MySQL (ponieważ ma wyzwalacze rząd po rzędzie), chociaż może mieć zastosowanie do innych DBMS wyzwalaczy rząd po rzędzie.

Dla uproszczenia wyzwalacze są sposobem na wdrożenie dowolnego rodzaju śledzenia zmian w bazie danych. Musisz jednak pamiętać o tym, co dzieje się pod maską, gdy używasz wyzwalaczy.

Zgodnie z MySQL Stored Procedura Programming , strona 256 pod nagłówkiem „Trigger Overhead” mówi:

Ważne jest, aby pamiętać, że z konieczności wyzwalacze powodują dodatkowe obciążenie instrukcji DML, do której się odnoszą. faktyczna wielkość narzutu będzie zależeć od natury wyzwalacza, ale --- ponieważ wszystkie wyzwalacze MySQL wykonują DLA KAŻDEJ WIERSZY --- narzut może szybko kumulować się dla instrukcji przetwarzających dużą liczbę wierszy. Dlatego należy unikać umieszczania kosztownych instrukcji SQL lub kodu proceduralnego w wyzwalaczach.

Rozszerzone objaśnienie narzutu na spust podano na stronach 529-531. Punkt końcowy z tej sekcji brzmi następująco:

Lekcja jest następująca: ponieważ kod wyzwalacza będzie wykonywany raz dla każdego wiersza objętego instrukcją DML, wyzwalacz może łatwo stać się najbardziej znaczącym czynnikiem w wydajności DML. Kod w ciele wyzwalacza musi być jak najlżejszy, a w szczególności wszelkie instrukcje SQL w wyzwalaczu powinny być obsługiwane przez indeksy, gdy tylko jest to możliwe.

Nie wspomniane w książce to kolejny czynnik przy używaniu wyzwalaczy: jeśli chodzi o rejestrowanie audytu, pamiętaj o tym, do czego logujesz dane. Mówię to, ponieważ jeśli zdecydujesz się zalogować do tabeli MyISAM, każde INSERT w tabeli MyISAM powoduje pełne zablokowanie tabeli podczas INSERT. Może to stać się poważnym wąskim gardłem w środowisku o dużym natężeniu ruchu i transakcjach. Ponadto, jeśli wyzwalacz dotyczy tabeli InnoDB i rejestrujesz zmiany w MyISAM z poziomu wyzwalacza, potajemnie wyłączy to zgodność ACID (tj. Ograniczy transakcje blokowe do zachowania automatycznego zatwierdzania), którego nie można przywrócić.

Podczas korzystania z wyzwalaczy w tabelach InnoDB i rejestrowania zmian

• Tabela, do której się logujesz, to także InnoDB
• Wyłączyłeś automatyczne zatwierdzanie
• Ustawiasz dokładnie START TRANSACTION ... COMMIT / ROLLBACK

W ten sposób dzienniki kontroli mogą korzystać z COMMIT / ROLLBACK, podobnie jak tabele główne.

Jeśli chodzi o korzystanie z procedur przechowywanych, należy starannie wywołać procedurę przechowywaną w każdym punkcie DML w stosunku do śledzonej tabeli. Z łatwością można pominąć rejestrowanie zmian w obliczu dziesiątek tysięcy wierszy kodu aplikacji. Umieszczenie takiego kodu w wyzwalaczu eliminuje znalezienie wszystkich instrukcji DML.

CAVEAT

W zależności od stopnia złożoności wyzwalacza, nadal może być wąskim gardłem. Jeśli chcesz ograniczyć wąskie gardła w logowaniu kontroli, możesz coś zrobić. Będzie to jednak wymagać niewielkiej zmiany infrastruktury.

Używając sprzętu towarowego, utwórz jeszcze dwa serwery DB

• Distribution master (DM) tylko do przekazywania informacji kontrolnych
• Serwer MySQL do audytu (AU)

Spowoduje to zmniejszenie liczby operacji we / wy zapisu w głównej bazie danych (MD) z powodu rejestrowania kontroli. Oto jak możesz to zrobić:

Krok 01) Włącz rejestrowanie binarne w głównej bazie danych.

Krok 02) Korzystając z niedrogiego serwera, skonfiguruj MySQL (ta sama wersja co MD) z włączonym rejestrowaniem binarnym. To będzie DM. Skonfiguruj replikację z MD na DM.

Krok 03) Korzystając z drugiego niedrogiego serwera, skonfiguruj MySQL (ta sama wersja co MD) z wyłączonym rejestrowaniem binarnym. Skonfiguruj każdą tabelę kontroli, aby używała --replicate-do-table . To będzie AU. Skonfiguruj replikację z DM do AU.

Krok 04) mysqldump struktury tabel z MD i załaduj go do DM i AU.

Krok 05) Konwertuj wszystkie tabele kontroli w MD, aby korzystać z silnika pamięci BLACKHOLE

Krok 06) Konwertuj wszystkie tabele w DM i AU, aby korzystać z silnika pamięci BLACKHOLE

Krok 07) Konwertuj wszystkie tabele kontroli w AU, aby korzystać z silnika pamięci MyISAM

Po zakończeniu

• DM będzie replikować się z MD i zapisywać rzeczy tylko w swoim dzienniku binarnym
• Dzięki filtrowi --replicate-do-table we wszystkich tabelach kontroli, AU będzie replikować z DM

Powoduje to przechowywanie informacji o inspekcji na osobnym serwerze DB, a także zmniejszenie wszelkiej degradacji we / wy zapisu, którą normalnie miałby MD.

Oto podejście do przeprowadzenia tej aktualizacji zbiorczo.

Dla tego przykładu

• table_A ma identyfikator PRIMARY KEY id
• Tworzysz tabelę o nazwie table_A_Keys2Update z samym identyfikatorem jako KLUCZ PODSTAWOWY
• Wypełniasz table_A_Keys2Update identyfikatorami z table_A, o których wiesz, że musisz zaktualizować

Aby utworzyć table_A_Keys2Update, wykonaj następujące czynności:

CREATE TABLE table_A_Keys2Update SELECT id FROM table_A;
ALTER TABLE table_A_Keys2Update ADD PRIMARY KEY (id);

Po zapełnieniu table_A_Keys2Update identyfikatorami, których numer wersji musi zostać zwiększony, wykonaj następujące DOŁĄCZANIE UPDATE, aby zwiększyć numer wersji wszystkich wierszy, których identyfikator znajduje się zarówno w table_A, jak i table_A_Keys2Update:

UPDATE table_A A INNER JOIN table_A_Keys2Update B USING (id)
SET A.revision = A.revision + 1;

To jedno wierszowe zapytanie może zastąpić wyzwalacz i procedurę przechowywaną.

Opcjonalnie możesz umieścić to jedno zapytanie w procedurze przechowywanej i wywołać je, jeśli chcesz.