Dlaczego SELECT * jest uważany za szkodliwy?

Dlaczego SELECT *zła praktyka? Czy nie oznaczałoby to mniej kodu do zmiany, jeśli dodałeś nową kolumnę, którą chciałeś?

Rozumiem, że SELECT COUNT(*)jest to problem z wydajnością niektórych DB, ale co jeśli naprawdę chcesz każdej kolumny?

Są naprawdę trzy główne powody:

• Nieefektywność w przenoszeniu danych do konsumenta. Kiedy wybierasz *, często pobierasz więcej kolumn z bazy danych, niż aplikacja naprawdę potrzebuje do działania. Powoduje to przenoszenie większej ilości danych z serwera bazy danych do klienta, co spowalnia dostęp i zwiększa obciążenie twoich komputerów, a także zajmuje więcej czasu na podróżowanie po sieci. Jest to szczególnie prawdziwe, gdy ktoś dodaje nowe kolumny do bazowych tabel, które nie istniały i nie były potrzebne, gdy pierwotni konsumenci kodowali dostęp do danych.

• Problemy z indeksowaniem. Rozważ scenariusz, w którym chcesz dostosować zapytanie do wysokiego poziomu wydajności. Jeśli użyjesz *, a zwróci on więcej kolumn, niż faktycznie potrzebujesz, serwer często musiałby zastosować droższe metody odzyskiwania danych niż w innym przypadku. Na przykład nie byłbyś w stanie utworzyć indeksu, który po prostu obejmowałby kolumny na liście SELECT, a nawet gdybyś to zrobił (w tym wszystkie kolumny [ dreszcz ]), następny facet, który przyszedł i dodał kolumnę do podstawy tabela spowodowałaby, że optymalizator zignorowałby zoptymalizowany indeks pokrycia, i prawdopodobnie okazałoby się, że wydajność zapytania znacznie spadłaby bez wyraźnego powodu.

• Problemy z wiązaniem. Po wybraniu * możliwe jest pobranie dwóch kolumn o tej samej nazwie z dwóch różnych tabel. Może to często spowodować awarię konsumenta danych. Wyobraź sobie zapytanie, które łączy dwie tabele, z których każda zawiera kolumnę o nazwie „ID”. Skąd konsument wiedziałby, który był który? SELECT * może również mylić widoki (przynajmniej w niektórych wersjach SQL Server), gdy zmieniają się podstawowe struktury tabel - widok nie jest odbudowywany, a dane, które powracają, mogą być nonsensowne . A najgorsze jest to, że możesz zadbać o nazwanie swoich kolumn dowolnie, ale następny facet, który przyjdzie, może nie wiedzieć, że musi się martwić o dodanie kolumny, która będzie kolidować z już rozwiniętą nazwy.

Ale nie wszystko jest złe dla SELECT *. Używam go swobodnie w tych przypadkach użycia:

• Zapytania ad hoc. Kiedy próbuję coś debugować, szczególnie przy wąskim stole, którego być może nie znam, SELECT * jest często moim najlepszym przyjacielem. Pomaga mi po prostu zobaczyć, co się dzieje, bez konieczności przeprowadzania mnóstwa badań nad tym, jakie są podstawowe nazwy kolumn. Staje się to większym „plus”, im dłuższe są nazwy kolumn.

• Gdy * oznacza „wiersz”. W następujących przypadkach użycia SELECT * jest w porządku, a plotki, że to zabójca wydajności, to tylko miejskie legendy, które mogły mieć pewną ważność wiele lat temu, ale nie teraz:

  SELECT COUNT(*) FROM table;

  w tym przypadku * oznacza „policz rzędy”. Jeśli miałbyś użyć nazwy kolumny zamiast *, to policzyłaby wiersze, w których wartość tej kolumny nie była równa null . COUNT (*), według mnie, naprawdę napędza koncepcję, że liczysz wiersze i unikasz dziwnych przypadków krawędziowych spowodowanych eliminacją NULL z twoich agregatów.

  To samo dotyczy tego typu zapytań:

  SELECT a.ID FROM TableA a
  WHERE EXISTS (
      SELECT *
      FROM TableB b
      WHERE b.ID = a.B_ID);

  w dowolnej bazie danych wartej soli * oznacza po prostu „wiersz”. Nie ma znaczenia, co umieścisz w podzapytaniu. Niektóre osoby używają identyfikatora b na liście WYBIERZ lub użyją cyfry 1, ale IMO te konwencje są dość nonsensowne. To, co masz na myśli, to „policz wiersz” i to właśnie oznacza *. Większość optymalizatorów zapytań jest na tyle inteligentna, aby o tym wiedzieć. (Szczerze mówiąc, wiem o tym tylko w przypadku SQL Server i Oracle.)

Znak gwiazdki „*” w instrukcji SELECT jest skrótem dla wszystkich kolumn w tabelach uczestniczących w zapytaniu.

Występ

*Skrótem może być mniejsza, ponieważ:

• Nie wszystkie pola są indeksowane, co wymusza pełne skanowanie tabeli - mniej wydajne
• To, co oszczędzasz, aby przesłać SELECT *przewodowo, wiąże się z ryzykiem pełnego skanowania tabeli
• Zwracanie większej ilości danych niż jest to konieczne
• Zwracanie końcowych kolumn przy użyciu typu danych o zmiennej długości może spowodować narzut związany z wyszukiwaniem

Konserwacja

Podczas używania SELECT *:

• Osoba niezaznajomiona z bazą kodu byłaby zmuszona zapoznać się z dokumentacją, aby dowiedzieć się, które kolumny są zwracane, zanim będzie w stanie wprowadzić właściwe zmiany. Czynienie kodu bardziej czytelnym, minimalizowanie dwuznaczności i pracy niezbędnej dla osób niezaznajomionych z kodem oszczędza więcej czasu i wysiłku na dłuższą metę.
• Jeśli kod zależy od kolejności kolumn, SELECT *ukryje błąd, który wystąpi, jeśli tabela ma zmienioną kolejność kolumn.
• Nawet jeśli potrzebujesz każdej kolumny w momencie pisania zapytania, może się tak nie zdarzyć w przyszłości
• użycie komplikuje profilowanie

Projekt

SELECT *jest anty-wzorem :

• Cel zapytania jest mniej oczywisty; kolumny używane przez aplikację są nieprzezroczyste
• Łamie zasadę modułowości dotyczącą używania ścisłego pisania, gdy tylko jest to możliwe. Jawne jest prawie ogólnie lepsze.

Kiedy należy użyć „SELECT *”?

Dopuszczalne jest użycie, SELECT *gdy istnieje wyraźna potrzeba dla każdej kolumny w tabeli (tabelach), w przeciwieństwie do każdej kolumny, która istniała podczas pisania zapytania. Baza danych wewnętrznie rozszerzy * do pełnej listy kolumn - nie ma różnicy w wydajności.

W przeciwnym razie jawnie wypisz każdą kolumnę, która ma być użyta w zapytaniu - najlepiej przy użyciu aliasu tabeli.

Nawet jeśli chcesz teraz zaznaczyć każdą kolumnę, możesz nie chcieć wybierać każdej kolumny po tym, jak ktoś doda jedną lub więcej nowych kolumn. Jeśli piszesz zapytanie SELECT *, ryzykujesz, że w pewnym momencie ktoś doda kolumnę tekstu, co spowoduje, że zapytanie będzie działało wolniej, nawet jeśli nie potrzebujesz tej kolumny.

Czy nie oznaczałoby to mniej kodu do zmiany, jeśli dodałeś nową kolumnę, którą chciałeś?

Szanse są takie, że jeśli naprawdę chcesz użyć nowej kolumny, będziesz musiał wprowadzić całkiem sporo innych zmian w kodzie. Oszczędzasz tylko , new_column- wystarczy kilka znaków do pisania.

Jeśli nazwiesz kolumny w instrukcji SELECT, zostaną one zwrócone w określonej kolejności, a zatem można je bezpiecznie odwoływać za pomocą indeksu numerycznego. Jeśli użyjesz „WYBIERZ *”, możesz w końcu otrzymać kolumny w dowolnej kolejności, a zatem możesz bezpiecznie używać kolumn tylko według nazwy. O ile nie wiesz z góry, co będziesz chciał zrobić z każdą nową kolumną, która zostanie dodana do bazy danych, najbardziej prawdopodobnym prawidłowym działaniem jest zignorowanie jej. Jeśli będziesz ignorować nowe kolumny, które zostaną dodane do bazy danych, ich odzyskanie nie przyniesie żadnych korzyści.

W wielu sytuacjach SELECT * spowoduje błędy w czasie wykonywania aplikacji, a nie w czasie projektowania. Ukrywa wiedzę o zmianach kolumn lub złych referencjach w aplikacjach.

Jeśli naprawdę chcesz każdej kolumny, nie widziałem różnicy w wydajności między select (*) a nazywaniem kolumn. Sterownik do nazywania kolumn może być po prostu wyraźny na temat tego, jakie kolumny mają być widoczne w kodzie.

Często jednak nie chcesz, aby każda kolumna, a select (*) może powodować niepotrzebną pracę dla serwera bazy danych i niepotrzebne przekazywanie informacji przez sieć. Jest mało prawdopodobne, aby spowodował zauważalny problem, chyba że system jest intensywnie wykorzystywany lub połączenie sieciowe jest wolne.

Pomyśl o tym jako o zmniejszeniu sprzężenia między aplikacją a bazą danych.

Podsumowując aspekt „zapachowego kodu”:
SELECT *tworzy dynamiczną zależność między aplikacją a schematem. Ograniczenie jego użycia jest jednym ze sposobów na dokładniejsze zdefiniowanie zależności, w przeciwnym razie zmiana bazy danych ma większe prawdopodobieństwo awarii aplikacji.

Jeśli dodasz pola do tabeli, zostaną one automatycznie uwzględnione we wszystkich zapytaniach, których używasz select * . Może się to wydawać wygodne, ale spowoduje spowolnienie aplikacji podczas pobierania większej ilości danych niż potrzebujesz, aw pewnym momencie spowoduje awarię aplikacji.

Istnieje limit ilości danych, które można pobrać w każdym wierszu wyniku. Jeśli dodasz pola do swoich tabel, aby wynik przekroczył ten limit, pojawi się komunikat o błędzie podczas próby uruchomienia zapytania.

Tego rodzaju błędy są trudne do znalezienia. Dokonujesz zmiany w jednym miejscu, a ona wysadza się w innym miejscu, które tak naprawdę nie korzysta z nowych danych. Może to być nawet rzadziej używane zapytanie, więc zanim ktoś go użyje, może to chwilę potrwać, co jeszcze bardziej utrudnia połączenie błędu ze zmianą.

Jeśli określisz, które pola chcesz w wyniku, jesteś bezpieczny przed tego rodzaju przepełnieniem.

Referencje zaczerpnięte z tego artykułu.

Nigdy nie używaj „SELECT *”,

Znalazłem tylko jeden powód, aby użyć „SELECT *”

Jeśli masz specjalne wymagania i stworzyłeś dynamiczne środowisko, gdy dodajesz lub usuwasz kolumnę, automatycznie obsłużysz kod aplikacji. W tym szczególnym przypadku nie trzeba zmieniać kodu aplikacji i bazy danych, co automatycznie wpłynie na środowisko produkcyjne. W takim przypadku możesz użyć „WYBIERZ *”.

Ogólnie rzecz biorąc, musisz dopasować swoje wyniki SELECT * ... do różnych struktur danych. Bez określenia kolejności otrzymywania wyników może być trudne ustawienie wszystkich elementów we właściwej kolejności (a bardziej niejasne pola są znacznie łatwiejsze do pominięcia).

W ten sposób możesz dodawać pola do swoich tabel (nawet pośrodku) z różnych powodów bez rozbijania kodu dostępu sql w całej aplikacji.

Używanie, SELECT *gdy potrzebujesz tylko kilku kolumn, oznacza, że ​​przesyłanych jest znacznie więcej danych, niż potrzebujesz. Zwiększa to przetwarzanie w bazie danych i zwiększa opóźnienie w uzyskaniu danych do klienta. Dodaj do tego, że po załadowaniu zużyje więcej pamięci, w niektórych przypadkach znacznie więcej, na przykład duże pliki BLOB, głównie o wydajność.

Oprócz tego łatwiej jest zobaczyć, kiedy kolumny są ładowane, bez konieczności sprawdzania zawartości tabeli.

Tak, jeśli dodasz dodatkową kolumnę, byłoby to szybsze, ale w większości przypadków chcesz / musisz zmienić kod za pomocą zapytania, aby zaakceptować nowe kolumny i istnieje potencjał, że zdobycie tych, których nie dasz t want / expect może powodować problemy. Na przykład, jeśli weźmiesz wszystkie kolumny, a następnie przypisz kolejność w celu przypisania zmiennych, a następnie dodaj jedną z nich, lub jeśli kolejność kolumn zmieni się (widać, że dzieje się to podczas przywracania z kopii zapasowej), może to wszystko zrzucić.

Jest to również ten sam rodzaj rozumowania, dlatego jeśli robisz to INSERTzawsze powinieneś określać kolumny.

Nie sądzę, że może to być naprawdę ogólna zasada. W wielu przypadkach unikałem SELECT *, ale pracowałem również z ramami danych, w których SELECT * był bardzo korzystny.

Jak w przypadku wszystkich rzeczy, istnieją korzyści i koszty. Myślę, że częścią równania korzyści i kosztów jest to, ile masz kontroli nad strukturami danych. W przypadkach, w których SELECT * działał dobrze, struktury danych były ściśle kontrolowane (było to oprogramowanie do sprzedaży detalicznej), więc nie było większego ryzyka, że ​​ktoś będzie chciał zajrzeć do dużego pola BLOB.

Wybranie za pomocą nazwy kolumny zwiększa prawdopodobieństwo, że aparat bazy danych może uzyskać dostęp do danych z indeksów, zamiast zapytać o dane tabeli.

WYBIERZ * naraża system na nieoczekiwane zmiany wydajności i funkcjonalności w przypadku zmiany schematu bazy danych, ponieważ masz zamiar dodać nowe kolumny do tabeli, mimo że kod nie jest przygotowany do użycia lub prezentacji tych nowych danych.

Jest też bardziej pragmatyczny powód: pieniądze. Gdy korzystasz z bazy danych w chmurze i musisz zapłacić za przetwarzane dane, nie ma wyjaśnienia, aby odczytać dane, które natychmiast odrzucisz.

Na przykład: BigQuery :

Zapytanie o cenę

Ceny zapytań odnoszą się do kosztu uruchamiania poleceń SQL i funkcji zdefiniowanych przez użytkownika. Opłaty BigQuery za zapytania przy użyciu jednej metryki: liczba przetworzonych bajtów.

i kontrola projekcji - Unikaj WYBIERZ * :

Najlepsza praktyka: Kontrola projekcji - Zapytaj tylko o kolumny, których potrzebujesz.

Projekcja odnosi się do liczby kolumn odczytywanych przez zapytanie. Wyświetlanie nadmiarowych kolumn pociąga za sobą dodatkowe (zmarnowane) operacje we / wy i materializację (zapisywanie wyników).

Korzystanie z SELECT * jest najdroższym sposobem na zapytanie danych. Kiedy używasz WYBIERZ *, BigQuery wykonuje pełny skan każdej kolumny w tabeli.

Zrozum swoje wymagania przed zaprojektowaniem schematu (jeśli to możliwe).

Dowiedz się o danych, 1) indeksowaniu 2) rodzaju używanego miejsca do przechowywania, 3) silniku dostawcy lub funkcjach; tj. ... buforowanie, możliwości w pamięci 4) typy danych 5) rozmiar tabeli 6) częstotliwość zapytań 7) powiązane obciążenia, jeśli zasób jest współużytkowany 8) Test

A) Wymagania mogą się różnić. Jeśli sprzęt nie jest w stanie obsłużyć oczekiwanego obciążenia, należy ponownie ocenić, w jaki sposób zapewnić wymagania dotyczące obciążenia. Jeśli chodzi o kolumnę dodawania do tabeli. Jeśli baza danych obsługuje widoki, możesz utworzyć indeksowany (?) Widok określonych danych z określonymi nazwanymi kolumnami (zamiast wybrać „*”). Okresowo sprawdzaj dane i schemat, aby upewnić się, że nigdy nie napotkasz syndromu „odśmiecanie” -> „odśmiecanie”.

Zakładając, że nie ma innego rozwiązania; możesz wziąć pod uwagę następujące kwestie. Zawsze istnieje wiele rozwiązań problemu.

1) Indeksowanie: Select * wykona skanowanie tabel. W zależności od różnych czynników może to obejmować wyszukiwanie dysku i / lub rywalizację z innymi zapytaniami. Jeśli tabela jest wielozadaniowa, upewnij się, że wszystkie zapytania są wydajne i wykonywane poniżej czasów docelowych. Jeśli jest duża ilość danych, a sieć lub inny zasób nie jest dostrojony; musisz wziąć to pod uwagę. Baza danych jest wspólnym środowiskiem.

2) rodzaj przechowywania. Tj .: jeśli używasz dysków SSD, dysku lub pamięci. Czasy wejścia / wyjścia i obciążenie systemu / procesora będą się różnić.

3) Czy DBA może dostroić bazę danych / tabele w celu zwiększenia wydajności? Zakładając z jakiegokolwiek powodu, zespoły zdecydowały, że wybór „*” jest najlepszym rozwiązaniem problemu; czy DB lub tabela mogą zostać załadowane do pamięci. (Lub inna metoda ... może odpowiedź została zaprojektowana w taki sposób, aby odpowiadała z 2-3 sekundowym opóźnieniem? --- podczas gdy reklama gra w celu uzyskania przychodów firmy ...)

4) Zacznij od linii podstawowej. Poznaj swoje typy danych i sposób prezentacji wyników. Mniejsze typy danych, liczba pól zmniejsza ilość danych zwracanych w zestawie wyników. To pozostawia zasoby dostępne na inne potrzeby systemowe. Zasoby systemowe są zwykle ograniczone; „zawsze” działa poniżej tych limitów, aby zapewnić stabilność i przewidywalne zachowanie.

5) rozmiar tabeli / danych. wybierz „*” jest wspólne dla małych tabel. Zazwyczaj mieszczą się w pamięci, a czasy reakcji są szybkie. Znowu .... sprawdź swoje wymagania. Zaplanuj pełzanie funkcji; zawsze planuj bieżące i możliwe przyszłe potrzeby.

6) Częstotliwość zapytań / zapytań. Bądź świadomy innych obciążeń w systemie. Jeśli to zapytanie uruchamia się co sekundę, a tabela jest niewielka. Zestaw wyników można zaprojektować tak, aby pozostawał w pamięci podręcznej / pamięci. Jeśli jednak zapytanie jest częstym procesem wsadowym z gigabajtami / terabajtami danych ... lepiej jest poświęcić dodatkowe zasoby, aby zapewnić, że nie wpłynie to na inne obciążenia.

7) Powiązane obciążenia. Dowiedz się, jak wykorzystywane są zasoby. Czy sieć / system / baza danych / tabela / aplikacja jest dedykowana czy współdzielona? Kim są interesariusze? Czy dotyczy to produkcji, rozwoju czy kontroli jakości? Czy jest to tymczasowa „szybka poprawka”. Czy przetestowałeś scenariusz? Będziesz zaskoczony, jak wiele problemów może istnieć na obecnym sprzęcie. (Tak, wydajność jest szybka ... ale konstrukcja / wydajność jest nadal obniżona.) Czy system musi wykonywać 10 000 zapytań na sekundę w porównaniu z 5-10 zapytaniami na sekundę. Czy serwer bazy danych jest dedykowany lub wykonuje inne aplikacje, monitorowanie wykonuje się na udostępnionym zasobie. Niektóre aplikacje / języki; O / S zajmują 100% pamięci, powodując różne objawy / problemy.

8) Test: Przetestuj swoje teorie i zrozum, o ile możesz. Twój wybór „*” może być poważną sprawą lub może być czymś, o co nawet nie musisz się martwić.