sysops.it

Outsourcing IT pozwoli ci prowadzić biznes taniej

info@sysops.it
+48 666 930 111

Nowości z bloga

TCP Fast Open

Opis modyfikacji stosu TCP, która może przyspieszyć prędkość ładowania popularnych stron od 4% do 41%.

Wyszukiwarki i Sphinx

Słowo wstępu o Sphinksie - silniku wspierającym wyszukiwanie pełnotekstowe.

Kwestia IPv6 NAT w Linuksie

IPv6 NAT w Linuksie - walka ideologii z rzeczywistością.

Usuwanie duplikatów z tabeli MySQL

Duplikaty MySQL

Zdarzyło mi się zmierzyć z usuwaniem powielonych wpisów z tabeli w bazie danych MySQL. Duplikaty tworzyły się w jednym z pól tabeli `produkty_dnia`. Przez cały dzień klienci pewnego sklepu głosowali na produkty - gdy w przeciągu dnia zebrało się trochę głosów, o północy skrypt wybierał ten najlepszy produkt.

Okazało się, że mechanizm był niedoskonały, ponieważ możliwe było wybranie tego samego produktu przez kilka dni. Z uwagi na fakt, że często całkiem niezły produkt za sprawą wyboru klientów lądował na stronę główną, inni klienci, którzy dopiero się z nim zapoznawali (albo przypominali sobie o nim), również gotowi byli na niego zagłosować. W ten sposób ten jeden produkt utrzymywał się na stronie głównej np. 3 dni z rzędu.

Programiści skrypt poprawili, jednak trzeba było pozbyć się duplikatów z tabeli, ponieważ właściciel sklepu przygotowywał się do stworzenia zestawienia swoich najlepszych "produktów dnia".

Usuwanie rekordów poprzez podzapytanie (subquery)

Fragment tabeli, który stanowił meritum zagadnienia, wyglądał w ten sposób:

+----+------------+--------------+ | id | data | produkt_dnia | +----+------------+--------------+ | 1 | 2009-12-01 | 85 | | 2 | 2009-12-02 | 85 | | 3 | 2009-12-03 | 85 | | 4 | 2009-12-04 | 7 | | 5 | 2009-12-05 | 9446 | | 6 | 2009-12-06 | 7 | +----+------------+--------------+

Zadanie było następujące: proszę usunąć duplikaty. Zapewne "dziury" w tabeli zostały później albo załatane losowymi produktami, albo na podstawie tabeli z głosami możliwe było wybranie produktów dnia z usuniętych dni raz jeszcze.

W pierwszej kolejności do głowy przyszło mi stworzenie zapytania DELETE z podzapytaniem SELECT, które wydobędzie z tabeli te produkty, które mają ten sam ID w polu `produkt_dnia` jak ID, które jest aktualnie przetwarzane, ale różne od tego ID - coś w stylu:

DELETE out.* FROM produkty_dnia out WHERE out.id = ( SELECT in.id FROM produkty_dnia in WHERE out.id <> in.id AND out.produkt_dnia = in.produkt_dnia );

Niestety MySQL nie pozwolił mi na wykonanie tego zapytania, twierdząc, że:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'out.* FROM produkty_dnia out WHERE out.id = ( SELEC' at line 2

W bazie danych MySQL nie mogę usuwać danych z tabeli na podstawie zapytania, które pobiera informacje z tej tabeli.

Jak usunąć duplikaty z tabeli?

Rozwiązań, jak się okazuje, jest kilka. Wspomnę o trzech, z czego na jedno poświęcę więcej miejsca.

  1. Porządkowanie duplikatów poprzez skrypt np. w języku PHP. Proste pobranie danych (SELECT), przejście do pętli while, która usunie duplikaty w ramach wydawanych w pętli zapytań DELETE. Bez rewelacji, ponieważ wymagane jest zaangażowanie dodatkowych narzędzi poza serwerem MySQL oraz wykonanie wielu (czasami setek tysięcy albo milionów) zapytań przez skrypt.
  2. Stworzenie pustej tymczasowej tabeli o identycznych polach oraz skopiowanie danych poprzez wrzucenie do tymczasowej tabeli danych z wykluczeniem duplikatów poprzez parametr DISTINCT (INSERT-SELECT DISTINCT). Dalej już z górki: usuwamy tabelę pierwotną, a nazwę tabeli tymczasowej zmieniamy na właściwą. Rozwiązanie lepsze od pierwszego, jednak wciąż potrzebne jest tworzenie dodatkowych tabel oraz przerzucanie danych z jednej do drugiej. W gruncie rzeczy można byłoby się zatrzymać na tej metodzie, ponieważ uzyskujemy zamierzony efekt i możemy wykonać wszystko nie wychodząc z konsoli.
  3. Skorzystanie z klucza UNIQUE INDEX.

Zastosowanie UNIQUE INDEX

Ostatnie rozwiązanie przypadło mi do gustu z uwagi na jego prostotę. Polega na utworzeniu z wymuszeniem unikalnego indeksu po polu, które ma być docelowo bez duplikatów. Standardowo przy tworzeniu indeksu UNIQUE gdy pole zawiera duplikaty, serwer MySQL poinformuje nas o tym komunikatem oraz indeksu nie utworzy. Wymuszenie polega na usunięciu z tabeli tych rekordów, które przeszkadzają przy zachowaniu warunku unikalności danego pola.

Jedyne zatem, co potrzebujemy, to utworzyć indeks UNIQUE INDEX z wymuszeniem oraz zaraz go usunąć (ewentualnie pozostawić, jeśli nie musimy dokonywać dalszych zmian w strukturze tabeli). W praktyce wygląda to tak - dodajemy indeks:

ALTER IGNORE TABLE `produkty_dnia` ADD UNIQUE INDEX(`produkt_dnia`);

Odpowiedź serwera MySQL będzie wówczas następująca:

Query OK, 6 rows affected Records: 6 Duplicates: 3 Warnings: 0

Mamy zatem jawną informację, iż z 6 rekordów 3 okazały się duplikatami. Od razu też w efekcie otrzymujemy następującą zawartość tabeli:

+----+------------+--------------+ | id | data | produkt_dnia | +----+------------+--------------+ | 1 | 2009-12-01 | 85 | | 4 | 2009-12-04 | 7 | | 5 | 2009-12-05 | 9446 | +----+------------+--------------+

Siła tego rozwiązania tkwi w prostocie. Ostatecznie pozostaje opcjonalnie do wykonania usunięcie indeksu:

ALTER TABLE `produkty_dnia` DROP INDEX `produkt_dnia`;

Podsumowanie

Postanowiłem opisać ten mechanizm, ponieważ w praktyce tego rodzaju operacje nie są wykonywane często. Już przy projektowaniu bazy danych oraz samej aplikacji powinniśmy założyć, które dane w tabelach są unikalne, które są indeksowane, które są nadmiarowe (niepotrzebnie lub w konkretnym celu). Zdarza się jednak, że albo projektowaliśmy coś w czasach, gdy mieliśmy mniejszą wiedzę, albo odziedziczyliśmy aplikację po kimś.

Na użycie indeksu UNIQUE INDEX jako sposobu na usuwanie duplikatów nie wpadłem od razu. Przeczesywanie stron w Sieci na hasła w stylu mysql removing duplicate rows najczęściej kończyło się odnalezieniem sposobu numer dwa. Liczę na to, że spisanie tych refleksji okaże się być przydatne.

Usuwanie duplikatów
Dodano dn. 1 grudnia 2012 roku przez rado.