Budowanie potoku kanonizacji adresów URL wideo
Przeprowadziłem zapytanie w mojej produkcyjnej bazie danych SQLite i odkryłem ogromny problem.
Tabela videos miała 41 283 wiersze, ale tylko 29 000 unikalnych tytułów. Przechowywaliśmy te same filmy wielokrotnie.
Dlaczego tak się stało? Ten sam film na YouTube trafiał w różnych formatach adresów URL:
• Krótkie linki: youtu.be/ID • Linki desktopowe: youtube.com/watch?v=ID • Linki mobilne: m.youtube.com/watch?v=ID • Shorts: youtube.com/shorts/ID
Jeden film oznaczał cztery wiersze w bazie danych i cztery niemal identyczne strony internetowe. Marnowało to mój budżet indeksowania (crawl budget) i powodowało błędy w Google Search Console.
Prowadzę TrendVidStream. Pobieramy dane o trendach z ośmiu różnych regionów. Każdy region może wyświetlać ten sam wiralowy film, używając różnych formatów adresów URL.
Zbudowałem potok (pipeline), aby to naprawić, używając PHP 8.4 i SQLite.
Oto jak działa ten potok:
- Ekstrakcja: Zamień każdą wariację adresu URL na stabilny, 11-znakowy identyfikator wideo.
- Normalizacja: Utwórz jeden, kanoniczny adres URL na podstawie tego identyfikatora.
- Walidacja: Przeprowadź szybkie kontrole przed modyfikacją bazy danych.
- Upsert: Użyj mechanizmu SQLite UPSERT do łączenia danych zamiast dodawania nowych wierszy.
- Emisja: Dodaj tagi kanoniczne i przekierowania 301, aby wyszukiwarki znajdowały jeden adres URL.
Kluczowe decyzje techniczne:
• Używaj ścisłej białej listy hostów. Nie używaj dopasowywania przyrostków (suffix matching). Zapobiega to ryzykom bezpieczeństwa.
• Nigdy nie zamieniaj identyfikatorów na małe litery. Identyfikatory YouTube rozróżniają wielkość liter. Zamiana na małe litery spowoduje połączenie różnych filmów.
• Użyj ograniczenia UNIQUE na kolumnie video_id. To Twoja najsilniejsza obrona przed duplikatami.
• Użyj trybu SQLite WAL. Dzięki temu operacje zapisu są szybkie i niezawodne.
Wynik:
Liczba wierszy w naszej tabeli wideo spadła z 41 283 do 28 094. Nie straciliśmy ani jednego filmu. Liczba błędów w Google Search Console spadła ze 412 do 9. Wyniki wyszukiwania stały się przejrzystsze, ponieważ przestaliśmy wyświetlać ten sam film cztery razy.
Lekcja jest prosta: gdy agregujesz dane z wielu źródeł, najpierw zaimplementuj mechanizm identyfikacji w swoim systemie. Wyodrębnij stabilny identyfikator, wymuś go za pomocą ograniczeń bazy danych i spraw, aby każdy zapis był idempotentny.
Nie potrzebujesz skomplikowanych narzędzi. PHP i SQLite wystarczą.