Kiedy używać widoków w MySQL?

Kiedy tworzysz tabele z wielu sprzężeń do użytku w analizie, kiedy preferowane jest używanie widoków zamiast tworzenia nowej tabeli?

Jednym z powodów, dla których wolałbym używać widoków, jest to, że schemat bazy danych został opracowany przez naszego administratora z Rubiego i nie znam go. Mogę zażądać utworzenia tabel, ale wymaga to dodatkowego kroku i chciałbym mieć większą elastyczność podczas opracowywania / testowania nowych połączeń.

Zacząłem korzystać z widoków po odpowiedzi na powiązane pytanie dotyczące SO ( kiedy używać R, kiedy używać SQL ). Najwyżej głosowana odpowiedź zaczyna się od „rób manipulacje danymi w SQL, dopóki dane nie znajdą się w jednej tabeli, a następnie wykonaj resztę w R.”

Zacząłem używać widoków, ale napotkałem kilka problemów z widokami:

1. zapytania są znacznie wolniejsze
2. Widoki nie są zrzucane z produkcyjnej do zapasowej bazy danych, której używam do analizy.

Czy widoki są odpowiednie do tego zastosowania? Jeśli tak, czy powinienem oczekiwać kary za wyniki? Czy istnieje sposób na przyspieszenie zapytań dotyczących wyświetleń?

Widoki w MySQL są obsługiwane przy użyciu jednego z dwóch różnych algorytmów: MERGElub TEMPTABLE. MERGEjest po prostu rozszerzeniem zapytania z odpowiednimi aliasami. TEMPTABLEtak to wygląda, widok umieszcza wyniki w tabeli tymczasowej przed uruchomieniem klauzuli WHERE i nie ma w niej żadnych indeksów.

Trzecią opcją jest UNDEFINED, która mówi MySQL, aby wybrał odpowiedni algorytm. MySQL spróbuje użyć, MERGEponieważ jest bardziej wydajny. Główne zastrzeżenie:

Jeśli nie można użyć algorytmu MERGE, należy użyć tabeli tymczasowej. MERGE nie może być użyte, jeśli widok zawiera jedną z następujących konstrukcji:

• Funkcje agregujące (SUM (), MIN (), MAX (), COUNT () itd.)

• ODRĘBNY

• GRUPUJ WEDŁUG

• MAJĄCY

• LIMIT

• UNION lub UNION ALL

• Podzapytanie na liście wyboru

• Odnosi się tylko do wartości literalnych (w tym przypadku nie ma podstawowej tabeli)

[src]

Zaryzykowałbym przypuszczenie, że WIDZENIA wymagają algorytmu TEMPTABLE, co powoduje problemy z wydajnością.

Oto naprawdę stary post na blogu dotyczący wydajności wyświetleń w MySQL i wydaje się, że nie poprawił się.

Na końcu tunelu może być jednak trochę światła na temat tymczasowych tabel niezawierających indeksów (powodujących pełne skanowanie tabel). W 5.6 :

W przypadkach, gdy dla podzapytania w klauzuli FROM wymagana jest materializacja, optymalizator może przyspieszyć dostęp do wyniku przez dodanie indeksu do zmaterializowanej tabeli. ... Po dodaniu indeksu optymalizator może traktować zmaterializowaną tabelę pochodną tak samo jak zwykłą tabelę z indeksem i podobnie korzysta z wygenerowanego indeksu. Narzut związany z tworzeniem indeksu jest znikomy w porównaniu z kosztem wykonania zapytania bez indeksu.

Jak wskazuje @ypercube, MariaDB 5.3 dodała tę samą optymalizację. Ten artykuł zawiera ciekawy przegląd tego procesu:

Optymalizacja jest stosowana, a następnie tabela pochodna nie może zostać scalona z jej rodzicielskim WYBOREM, co dzieje się, gdy tabela pochodna nie spełnia kryteriów scalania WIDOKU

Widoki to narzędzia bezpieczeństwa. Nie chcesz, aby określony użytkownik lub aplikacja wiedziała, gdzie znajduje się twoja tabela danych, udostępniasz widok tylko z potrzebnymi kolumnami.

Pamiętaj, że widoki zawsze obniżają wydajność, podobne zapytania powinny być procedurami i funkcjami przechowywanymi, a nie widokami.

Aby dokonać strojenia zapytania, zawsze postępuj zgodnie z najlepszymi praktykami, unikaj używania funkcji w klauzulach WHERE, twórz indeksy w celu przyspieszenia zaznaczeń, ale nie nadużywaj indeksów degradujących wstawiania, aktualizacji i usuwania.

Istnieje dobra dokumentacja, która może ci pomóc: http://www.toadworld.com/LinkClick.aspx?fileticket=3qbwCnzY/0A=&tabid=234

Myślę, że widoki są predefiniowaną strukturą (bez danych) do scalania tabel w jedną, aby pokonać zapytanie z wielu tabel, które można wykorzystać z rzeczywistych danych do szybkiego zapytania relacyjnego ...