Czy istnieją jakieś biblioteki / ramy cofania / powtarzania zmiany wierszy w bazie danych?




Może być mój tytuł nie jest jasne. Szukam jakiejś kontroli wersji w tabelach bazy danych, jak Subversion czy na plikach, takich jak wiki robi.


Chcę prześledzić zalogować zmiany. Chcę, aby wyodrębnić i uruchomić diff w odwrotnej kolejności. (Cofnąć jak „nazw połączyć R 101: 100”). Może muszę indeksowane wyszukiwanie w historii.


Czytałem „ [wzorzec projektowania dla silnika Cofnij](https://stackoverflow.com/questions/49755/design-pattern-for-undo-engine) ”, ale jest to związane z „wzorców”. Czy jest coś mogę ponownie użyć bez wyważać otwartych drzwi?


**EDIT:** 
Na przykład transakcje kont bankowych. Mam kolumny „równowagi” (i innych) zaktualizowany w tabeli. użytkownik znajdzie błąd przez niego 10 dni później i będzie chciał anulować / wycofać konkretną transakcję, bez zmiany innych.


Jak mogę zrobić to z wdziękiem na poziomie aplikacji?
Martin Fowler obejmuje temat na [wzorce dla rzeczy, które zmieniają się wraz z upływem czasu](http://martinfowler.com/eaaDev/timeNarrative.html "Wzory dla rzeczy, które zmieniają się wraz z upływem czasu") . Still wzory i nie rzeczywiste ramy ale on pokazuje przykładowe dane i jak go używać.
<p>w witrynach, w których pracowałem, wszelkie zmiany, które należy wprowadzić w instancjach produkcyjnych, muszą być skryptowane jako skrypty zmian, które będą działać w sql * plus; ponadto skrypty potrzebne do odtworzenia od podstaw wszystkich obiektów schematu muszą być aktualizowane. wszystkie te skrypty są sprawdzane pod kątem kontroli zmian i stamtąd migrowane.</p><p>możesz kontrolować zmiany ddl lub używać wyzwalaczy ddl, aby wykrywać zmiany, a nawet używać oprogramowania różnicowego do porównywania dwóch wystąpień, ale metody te są niedyskryminujące; często programista wprowadza i cofa wiele zmian w schemacie (np. małe zmiany testowe, tworzenie fałszywych tabel do testowania koncepcji itp.) przed ustaleniem, co dokładnie należy zmienić.</p> <h1><strong> nr. </strong></h1><p><strong> <code>truncate</code> i <code>drop</code> są prawie identyczne pod względem zachowania i prędkości, więc wykonanie <code>truncate</code> tuż przed <code>drop</code> jest po prostu niepotrzebne. </strong></p><hr><p><em>waga: napisałem tę odpowiedź z perspektywy sql server i założyłem, że będzie ona miała zastosowanie w równym stopniu do sybase. wygląda na to, że <a href="https://dba.stackexchange.com/q/186154/2660">nie jest tak do końca</a> .</em></p><p><em>waga: kiedy po raz pierwszy opublikowałem tę odpowiedź, było kilka innych wysoko ocenianych odpowiedzi - w tym również odpowiedź zaakceptowana - które złożyły kilka fałszywych twierdzeń, takich jak: <code>truncate</code> nie jest rejestrowany; <code>truncate</code> nie można przywrócić; <code>truncate</code> jest szybszy niż <code>drop</code>; itp.</em></p><p><em>teraz, gdy ten wątek został oczyszczony, następujące obalenia mogą wydawać się styczne do pierwotnego pytania. zostawiam je tutaj jako odniesienie dla innych, którzy chcą obalić te mity.</em></p><hr><p>istnieje kilka popularnych kłamstw - rozpowszechnionych nawet wśród doświadczonych dba - które mogły być tego powodem <code>truncate-then-drop</code> wzór. oni są:</p><ul> <li><strong> mit </strong>: <code>truncate</code> nie jest rejestrowany, dlatego nie można go przywrócić.</li> <li><strong> mit </strong>: <code>truncate</code> jest szybszy niż <code>drop</code>.</li> </ul><p>pozwól mi obalić te kłamstwa. piszę to obalenie z perspektywy sql server, ale wszystko, co tu powiem, powinno mieć również zastosowanie do sybase.</p><h1> <a href="http://msdn.microsoft.com/en-us/library/ms177570.aspx"> obciąć </a> <em>is</em> zalogowany, a <em>can</em> można przywrócić.</h1><ul> <li> <p><strong> <code>truncate</code> jest zarejestrowaną operacją, więc <a href="http://sqlblog.com/blogs/kalen\_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx">to <em>można</em> wycofać</a> . </strong> po prostu zawiń to w transakcji.</p> <pre><code>use [tempdb]; set nocount on; create table truncate\_demo ( whatever varchar(10) ); insert into truncate\_demo (whatever) values ('log this'); begin transaction; truncate table truncate\_demo; rollback transaction; select * from truncate\_demo; drop table truncate\_demo; </code></pre> <p>zauważ jednak, że <strong> jest to <a href="http://psoug.org/reference/truncate.html">nieprawda</a> dla oracle </strong>. chociaż zalogowany i chroniony przez funkcję cofania i ponawiania oracle, <code>truncate</code> i inne instrukcje ddl <em>nie można</em> zostać wycofane przez użytkownika, ponieważ oracle wydaje <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11\_question\_id:2860243091362">niejawne zobowiązania</a> natychmiast przed i po wszystkich instrukcjach ddl.</p> </li> <li> <p><strong> <code>truncate</code> jest minimalnie zalogowany </strong>, a nie w pełni zalogowany. co to znaczy? powiedzmy, że <code>truncate</code> stół. zamiast umieszczać każdy usunięty wiersz w dzienniku transakcji, <code>truncate</code> po prostu zaznacza strony danych, na których żyją, jako nieprzydzielone. właśnie dlatego jest tak szybki. dlatego też nie można odzyskać wierszy tabeli <code>truncate</code> z dziennika transakcji przy użyciu czytnika dziennika. znajdziesz tam tylko odniesienia do stron zwolnionych danych.</p> <p>porównaj to z <code>delete</code>. jeśli <code>delete</code> wszystkie wiersze w tabeli i zatwierdzisz transakcję, nadal teoretycznie możesz znaleźć usunięte wiersze w dzienniku transakcji i odzyskać je stamtąd. jest tak, ponieważ <code>delete</code> zapisuje każdy usunięty wiersz w dzienniku transakcji. w przypadku dużych tabel spowoduje to, że będzie on znacznie wolniejszy niż <code>truncate</code>.</p> </li> </ul><h1> <a href="http://msdn.microsoft.com/en-us/library/ms173790.aspx"> upuść </a> jest równie szybki jak truncate.</h1><ul> <li>podobnie jak <code>truncate</code>, <strong> <code>drop</code> jest operacją minimalnie rejestrowaną. </strong> oznacza to, że <code>drop</code> można wycofać też. oznacza to również <a href="http://msdn.microsoft.com/en-us/library/dd425070.aspx">działa dokładnie tak samo</a> jak <code>truncate</code>. zamiast usuwać poszczególne wiersze, <strong> <code>drop</code> oznacza odpowiednie strony danych jako nieprzydzielone i dodatkowo oznacza metadane tabeli jako usunięte </strong>.</li> <li> <p>ponieważ <code>truncate</code> i <code>drop</code> działają dokładnie w ten sam sposób, działają tak samo szybko. <strong> nie ma sensu <code>truncate</code> - przechodzenie do tabeli przed <code>drop</code> - pisanie jej. </strong> uruchom <a href="https://gist.github.com/1349319">this skrypt demo</a> w twojej instancji programistycznej, jeśli mi nie wierzysz.</p> <p>na mojej lokalnej maszynie z ciepłą pamięcią podręczną wyniki są następujące:</p> <pre><code>table row count: 134,217,728 run# transaction duration (ms) truncate truncate then drop drop  01 0 1 4 02 0 39 1 03 0 1 1 04 0 2 1 05 0 1 1 06 0 25 1 07 0 1 1 08 0 1 1 09 0 1 1 10 0 12 1  avg 0 8.4 1.3 </code></pre> <p>tak więc, dla tabeli 134 <em>milionów</em> wierszy, zarówno <code>drop</code>, jak i <code>truncate</code> nie zajmują w ogóle czasu. (w przypadku zimnego bufora zajmują około 2-3 sekund dla pierwszego uruchomienia lub dwóch). uważam również, że wyższy średni czas trwania operacji <code>truncate</code>, a następnie <code>drop</code>, można przypisać zmianom obciążenia na moja lokalna maszyna i <em>nie</em>, ponieważ kombinacja jest w magiczny sposób o rząd wielkości gorsza niż poszczególne operacje. w końcu są prawie dokładnie tym samym.</p> <p>jeśli interesują cię bardziej szczegółowe informacje na temat narzutu związanego z logowaniem tych operacji, <a href="https://dba.stackexchange.com/questions/4163/why-use-both-truncate-and-drop/7685#7685">martin ma proste wyjaśnienie</a> tego.</p> </li> </ul> <p>nigdy nie odłączaj podejrzanej bazy danych. w każdym razie, jak załączyłeś bazę danych po jej odłączeniu? użyłeś <code>create database</code> z opcją <code>for attach\_rebuild\_log</code>?</p><p>te polecenia powinny załatwić sprawę:</p><pre><code>alter database recovery\_test\_2 set emergency; alter database recovery\_test\_2 set single\_user; dbcc checkdb (recovery\_test\_2, repair\_allow\_data\_loss) with no\_infomsgs, all\_errormsgs; </code></pre><p>napisałem post dotyczący tej sytuacji:</p><p> <a href="http://yrushka.com/index.php/database-recovery/sql-20052008-log-file-deleted-part-3-2/">sql 2005/2008 procedura odzyskiwania bazy danych - usunięto plik dziennika (część 3)</a> </p><p>pytałeś o różnicę między:</p><ul> <li><code>dbcc checkdb ('&lt;dbname&gt;', repair\_allow\_data\_loss)</code> i</li> <li><code>alter database &lt;dbname&gt; rebuild log on (name=&lt;dbname&gt;,filename='&lt;logfilepath&gt;')</code></li> </ul><p>chodzi o to, że możesz uruchomić oba pliki, aby odbudować plik dziennika, ale używając <code>checkdb</code> odbudujesz dziennik i sprawdzisz bazę danych pod kątem błędów integralności.</p><p>druga (zmiana bazy danych) nie będzie działać, jeśli po utracie pliku dziennika wystąpiły aktywne transakcje (niepisane na dysk). podczas uruchamiania lub dołączania sql server będzie chciał wykonać odzyskiwanie (wycofanie i przywrócenie) z pliku dziennika, którego nie ma. zdarza się to, gdy dysk ulega awarii lub następuje nieoczekiwane zamknięcie serwera, a baza danych nie zostaje całkowicie zamknięta. wydaje mi się, że to nie była twoja sprawa i wszystko dobrze dla ciebie rozwiązało.</p><ol> <li> <p><code>dbcc checkdb (dbname, repair\_allow\_data\_loss)</code> uruchom na bazie danych w stanie awaryjnym sprawdza bazę danych pod kątem błędów niespójności, próbuje najpierw użyć pliku dziennika do odzyskania od wszelkich niespójności. jeśli go brakuje, dziennik transakcji jest odbudowywany.</p> </li> <li> <p><code>alter database rebuild log on...</code> to procedura nieudokumentowana i wymaga kolejnego <code>dbcc checkdb</code>, aby naprawić wszelkie błędy.</p> </li> </ol> <p>ma więcej kolumn niż pokaż status tabeli; ale robi sztuczkę:</p><pre><code>select * from information\_schema.tables where table\_schema = database(); </code></pre><p>aktualizacja 2011-06-07 19:02</p><pre><code>select table\_name,engine,version,row\_format,table\_rows,avg\_row\_length, data\_length,max\_data\_length,index\_length,data\_free,auto\_increment, create\_time,update\_time,check\_time,table\_collation,checksum, create\_options,table\_comment from information\_schema.tables where table\_schema = database(); </code></pre><p>te zapytania działają, jeśli ustawisz bieżącą bazę danych.</p><p>możesz także kodować konkretną bazę danych:</p><pre><code>select table\_name,engine,version,row\_format,table\_rows,avg\_row\_length, data\_length,max\_data\_length,index\_length,data\_free,auto\_increment, create\_time,update\_time,check\_time,table\_collation,checksum, create\_options,table\_comment from information\_schema.tables where table\_schema = 'mysql'; </code></pre> Replikacja [works by reading the transaction log](https://technet.microsoft.com/en-us/library/ms151706%28v=sql.105%29.aspx) .

Ten rodzaj „aktualizacji bez aktualizacji” często nie generuje żadnych rekordów dziennika transakcji.

Więcej informacji na ten temat można znaleźć w artykule Paula White'a [The Impact of Non-Updating Updates](http://sqlblog.com/blogs/paul_white/archive/2010/08/11/the_2D00_impact_2D00_of_2D00_update_2D00_statements_2D00_that_2D00_don_2D00_t_2D00_change_2D00_data.aspx) . <p>najlepszym sposobem byłoby użycie tabel, które już masz. utwórz dwie tabele - tabela-a, tabela-b na potrzeby testu możesz nawet zaktualizować tę samą kolumnę o te same informacje, aby nie wpływać na żadne rzeczywiste dane.</p><p>na przykład update table\_a ustaw id = id gdzie id = 100;</p><p>otwórz dwie sesje dla tej samej bazy danych. na jeden biegnij</p><pre><code>begin tran update table\_a set id=id where id = 100; </code></pre><p>dwa biegi</p><pre><code>begin tran update table\_b set id=id where id =100; </code></pre><p>następnie skopiuj instrukcje aktualizacji do sesji przeciwnych i uruchom je w tym samym czasie. w jednym,</p><pre><code>update table\_b set id=id where id =100; </code></pre><p>we dwóch</p><pre><code>update table\_a set id=id where id = 100; </code></pre><p>właśnie próbowałem tego teraz i dostałem na ms-sql</p><pre><code>msg 1205, level 13, state 56, line 1 transaction (process id 23) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction. </code></pre> <p> <a href="https://dba.stackexchange.com/users/1268/morgan-tocker">morgan</a> daje wskazówkę w swoim komentarzu, że innodb stale sprawdza uszkodzone strony, wykonując sumy kontrolne na stronach, które czyta. jeśli innodb znajdzie niedopasowanie sumy kontrolnej, to zrobi to <strike>wypadek</strike> zatrzymaj serwer.</p><p>jeśli chcesz przyspieszyć ten proces (zamiast czekać, aż innodb przeczyta uszkodzoną stronę), możesz użyć <a href="http://dev.mysql.com/doc/refman/5.5/en/innochecksum.html"><code>innochecksum</code></a> :</p><blockquote> <p>ponieważ niedopasowania sumy kontrolnej spowodują, że innodb celowo zamknie działający serwer, może być preferowane użycie tego narzędzia zamiast czekania, aż serwer w trybie produkcyjnym napotka uszkodzone strony.</p> </blockquote><p>ciekawe zastrzeżenie:</p><blockquote> <p>innochecksum nie może być używany w plikach obszaru tabel, które serwer już otworzył. w przypadku takich plików należy użyć sprawdź tabelę, aby sprawdzić tabele w obszarze tabel.</p> </blockquote><p>tak, dla tabeli online <code>check table</code> jest prawdopodobnie narzędziem (lub jak wskazano <a href="https://dba.stackexchange.com/questions/6191/how-do-you-identify-innodb-table-corruption/6219#6219">w innej odpowiedzi</a> <code>mysqlcheck</code>, jeśli chcesz zrobić więcej niż jedną bazę danych na raz.)</p><p>jeśli możesz zamknąć bazę danych, możesz wymusić na niej sumy kontrolne, używając <code>innochecksum</code></p><p><strong> anegdota: </strong> w obszarze tabel innodb o wielkości 29 gb (z <code>innodb\_file\_per\_table=1</code>), ten skrypt zajął około 2 minut</p><pre class="lang-bsh prettyprint-override"><code>#!/bin/bash for i in $(ls /var/lib/mysql/*/*.ibd) do innochecksum -v $i done </code></pre><p>jako bonus jednak, ponieważ korzystasz z percony, zaimplementowali nową metodę dla <a href="http://www.percona.com/doc/percona-server/5.5/performance/innodb\_fast\_checksum.html?id=percona-server:features:innodb\_fast\_checksum">szybka suma kontrolna innodb</a> . nigdy go nie użyłem, ale może to przyspieszyć proces.</p> Jest to dość szerokie, ale dam ci tak ogólną odpowiedź, jak tylko mogę.

**CTE ...**

* Są niezindeksowalne (ale mogą używać istniejących indeksów na obiektach, do których istnieją odwołania)
* Nie można mieć ograniczeń
* Są zasadniczo jednorazowe `VIEW` s
* Trwać tylko do momentu uruchomienia następnego zapytania
* Może być rekurencyjny
* Nie masz dedykowanych statystyk (polegaj na statystykach dotyczących podstawowych obiektów)

**#Temp Tables ...**

* Są prawdziwe zmaterializowane tabele, które istnieją w tempdb
* Może być indeksowany
* Może mieć ograniczenia
* Trwaj w życiu obecnego POŁĄCZENIA
* Może odwoływać się do innych zapytań lub podprocedur
* Masz dedykowane statystyki generowane przez silnik

Jeśli chodzi o użycie każdego z nich, mają bardzo różne przypadki użycia.Jeśli masz bardzo duży zestaw wyników lub musisz odwołać się do niego więcej niż raz, umieść go w tabeli `#temp` .Jeśli ma być rekurencyjny, jednorazowy lub po prostu dla uproszczenia logicznego, preferowany jest `CTE` .

Ponadto należy `CTE`**nigdy nie używaj do wykonania**.Prawie nigdy nie przyspieszysz przy użyciu CTE, ponieważ znowu jest to widok jednorazowy.Możesz zrobić z nimi kilka fajnych rzeczy, ale przyspieszenie zapytania nie jest jednym z nich. <p>josh,</p><p>jest to bardzo częste zadanie dla wszystkich dba i poprawna odpowiedź nie jest taka sama dla każdego i dla każdego serwera. podobnie jak wiele innych rzeczy, zależy to od tego, czego potrzebujesz.</p><p>zdecydowanie nie chcesz uruchamiać „shrink database”, jak już sugerowano. jego zło do wydajności, a poniżej odniesienie pokaże ci dlaczego. powoduje to fragmentację dysku i indeksów, co może prowadzić do problemów z wydajnością. lepiej jest, jeśli wstępnie przydzielisz duży rozmiar danych i plików dziennika, aby automatyczne uruchamianie nie uruchomiło się.</p><p>nie zrozumiałem twojego nr 2. pełna kopia zapasowa wybranych tabel. czy możesz rozwinąć więcej na ten temat?</p><p>przychodząc do reorganizacji indeksu, aktualizacji statystyk i przebudowy indeksu, musisz uważać na to, jak to zrobisz, w przeciwnym razie skończy się to na użyciu większej ilości zasobów, a także na problemach z wydajnością.</p><p>po przebudowaniu indeksów statystyki indeksów są aktualizowane za pomocą fullscan, ale jeśli później zaktualizujesz statystyki, zostaną one ponownie zaktualizowane z domyślną próbką (która zależy od kilku czynników, zwykle 5% tabeli, gdy rozmiar tabeli&gt; 8 mb), co może prowadzić do problemów z wydajnością. w zależności od posiadanej edycji może być możliwe przebudowywanie indeksu online. właściwym sposobem wykonywania tej czynności jest sprawdzenie stopnia fragmentacji i w zależności od tego albo przebuduj indeks, albo zreorganizuj indeks + zaktualizuj statystyki. możesz także określić, które tabele wymagają częstszej aktualizacji statystyk i próbować częściej aktualizować statystyki.</p><p>plany konserwacji są w porządku, ale trudno jest uzyskać z nich jak najwięcej, wykonując te dostosowania, chyba że możesz zalogować się do ssis i poprawić mp. dlatego wolę nie używać ich i używać <a href="https://ola.hallengren.com">darmowe skrypty oli hallengren</a> , które są bardziej niezawodne niż mp. poleciłbym również nadrobić zaległości w artykule paula randala na ten temat.</p><p>ref: <a href="http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx">http://technet.microsoft.com/en-us/magazine/2008.08.08.database.aspx</a> </p><p>to nie jest wyczerpująca odpowiedź na twoje pytanie, ale dobry punkt wyjścia. hth i daj nam znać, jeśli masz dodatkowe pytania/komentarze.</p> druga pewnie jest dla sprawdzenia poprwanosci odpowiedzi, kucze nie pamietam dokladnie tych komend ale sprobuj skopjowac je z tej gotowej tabelki i ewentualnie cos pozmieniaj- a dokladniej t wspolrzedne pol zeby sie zgadzaly (wstaw tylko w pierwszych ramkach a reszte zlap za jeden rog i przeciagnij to ci sie skopiuje dla innych wynikow)