Czy jest jakiś powód, aby martwić się o kolejność kolumn w tabeli?

Wiem, że możesz ZMIENIĆ kolejność kolumn w MySQL za pomocą FIRST i AFTER, ale po co miałbyś się tym przejmować? Skoro dobre zapytania wyraźnie nazywają kolumny podczas wstawiania danych, czy naprawdę jest jakiś powód, by przejmować się kolejnością kolumn w tabeli?

Kolejność kolumn miała duży wpływ na wydajność niektórych dostrojonych baz danych, obejmujących Sql Server, Oracle i MySQL. Ten post ma dobre praktyczne zasady :

• Najpierw kolumny klucza podstawowego
• Następne kolumny kluczy obcych.
• Często wyszukiwane kolumny obok
• Często aktualizowane kolumny później
• Kolumny dopuszczające wartość zerową jako ostatnie.
• Najrzadziej używane kolumny dopuszczające wartość null po częściej używanych kolumnach dopuszczających wartość null

Przykładem różnicy w wydajności jest wyszukiwanie indeksu. Silnik bazy danych znajduje wiersz na podstawie pewnych warunków w indeksie i zwraca adres wiersza. Teraz powiedz, że szukasz SomeValue i znajduje się w tej tabeli:

 SomeId int,
 SomeString varchar(100),
 SomeValue int

Silnik musi odgadnąć, gdzie zaczyna się SomeValue, ponieważ SomeString ma nieznaną długość. Jeśli jednak zmienisz zamówienie na:

 SomeId int,
 SomeValue int,
 SomeString varchar(100)

Teraz silnik wie, że SomeValue można znaleźć 4 bajty po rozpoczęciu wiersza. Zatem kolejność kolumn może mieć znaczny wpływ na wydajność.

EDYCJA: Sql Server 2005 przechowuje pola o stałej długości na początku wiersza. Każdy wiersz ma odniesienie do początku varchar. To całkowicie neguje efekt, który wymieniłem powyżej. Tak więc w przypadku najnowszych baz danych kolejność kolumn nie ma już żadnego wpływu.

Aktualizacja:

W programie MySQLmoże istnieć powód, aby to zrobić.

Ponieważ zmienne typy danych (takie jak VARCHAR) są przechowywane ze zmiennymi długościami w InnoDB, silnik bazy danych powinien przeszukać wszystkie poprzednie kolumny w każdym wierszu, aby znaleźć przesunięcie podanego.

W przypadku kolumn wpływ może sięgać nawet 17%20 .

Zobacz ten wpis na moim blogu, aby uzyskać więcej szczegółów:

• Wybór kolejności kolumn

W programie Oraclekońcowe NULLkolumny nie zajmują miejsca, dlatego zawsze należy umieszczać je na końcu tabeli.

Również w Oraclei wSQL Server w przypadku dużego rzędu ma ROW CHAININGmoże wystąpić.

ROW CHANING polega na dzieleniu wiersza, który nie mieści się w jednym bloku i obejmowaniu go przez wiele bloków, połączonych połączoną listą.

Odczytanie kolumn końcowych, które nie pasowały do ​​pierwszego bloku, będzie wymagało przejścia przez połączoną listę, co spowoduje dodatkowe I/O operację.

Zobacz tę stronę, aby zapoznać się z ilustracją w ROW CHAININGformacieOracle :

Dlatego warto umieścić kolumny, których często używasz, na początku tabeli, a kolumny, których nie używasz często, lub kolumny, które zwykle są NULL, na końcu tabeli.

Ważna uwaga:

Jeśli podoba Ci się ta odpowiedź i chcesz na nią zagłosować, również zagłosuj na @Andomarodpowiedź .

Odpowiedział to samo, ale wydaje się, że bez powodu został odrzucony.

Podczas szkolenia Oracle w poprzedniej pracy nasz DBA zasugerował, że umieszczenie wszystkich kolumn nie dopuszczających wartości null przed kolumnami dopuszczającymi wartość zerową jest korzystne ... chociaż TBH nie pamiętam szczegółów dlaczego. A może to tylko te, które prawdopodobnie zostaną zaktualizowane, powinny przejść na koniec? (Może odkłada konieczność przesunięcia wiersza, jeśli się rozszerzy)

Ogólnie nie powinno to robić żadnej różnicy. Jak powiedziałeś, zapytania powinny zawsze określać same kolumny, zamiast polegać na kolejności z „select *”. Nie znam żadnej bazy danych, która pozwala na ich zmianę ... cóż, nie wiedziałem, że MySQL na to zezwala, dopóki o tym nie wspomniałeś.

Niektóre źle napisane aplikacje mogą być zależne od kolejności / indeksu kolumn zamiast nazwy kolumny. Nie powinny, ale to się zdarza. Zmiana kolejności kolumn zepsułaby takie aplikacje.

Czytelność wyjścia, gdy musisz wpisać:

select * from <table>

w oprogramowaniu do zarządzania bazą danych?

To bardzo fałszywy powód, ale w tej chwili nie mogę myśleć o niczym innym.

Nie, kolejność kolumn w tabeli bazy danych SQL jest całkowicie nieistotna - z wyjątkiem celów wyświetlania / drukowania. Nie ma sensu zmieniać kolejności kolumn - większość systemów nawet nie zapewnia sposobu, aby to zrobić (z wyjątkiem usunięcia starej tabeli i odtworzenia jej z nową kolejnością kolumn).

Marc

EDYCJA: z wpisu Wikipedii w relacyjnej bazie danych, oto odpowiednia część, która dla mnie jasno pokazuje, że kolejność kolumn nigdy nie powinna mieć znaczenia:

Relację definiuje się jako zbiór n-krotek. Zarówno w matematyce, jak iw modelu relacyjnej bazy danych zbiór jest nieuporządkowanym zbiorem elementów, chociaż niektóre DBMS narzucają porządek swoim danym. W matematyce krotka ma porządek i pozwala na powielanie. EF Codd pierwotnie zdefiniował krotki przy użyciu tej matematycznej definicji. Później jednym z wielkich spostrzeżeń EF Codda było to, że używanie nazw atrybutów zamiast porządkowania byłoby o wiele wygodniejsze (ogólnie) w języku komputerowym opartym na relacjach. Ten wgląd jest nadal używany.

Jedynym powodem, o którym mogę pomyśleć, jest debugowanie i gaszenie pożarów. Mamy tabelę, której kolumna „nazwa” znajduje się około dziesiątej pozycji na liście. To uciążliwe, gdy robisz szybki wybór * z tabeli, w której id w (1, 2, 3), a następnie musisz przewijać, aby spojrzeć na nazwy.

Ale to jest o tym.

Jak to często bywa, największym czynnikiem jest następny facet, który musi popracować nad systemem. Staram się mieć najpierw kolumny klucza podstawowego, potem kolumny klucza obcego, a następnie pozostałe kolumny w porządku malejącym według ważności / znaczenia dla systemu.

Jeśli zamierzasz często używać UNION, ułatwi to dopasowywanie kolumn, jeśli masz konwencję dotyczącą ich kolejności.

Jak wspomniano, istnieje wiele potencjalnych problemów z wydajnością. Kiedyś pracowałem nad bazą danych, w której umieszczanie bardzo dużych kolumn na końcu poprawiało wydajność, jeśli nie odnosiłeś się do tych kolumn w zapytaniu. Wygląda na to, że jeśli rekord obejmował wiele bloków dysku, silnik bazy danych mógł przestać czytać bloki po zebraniu wszystkich potrzebnych kolumn.

Oczywiście wszelkie implikacje dotyczące wydajności w dużym stopniu zależą nie tylko od producenta, którego używasz, ale także potencjalnie od wersji. Kilka miesięcy temu zauważyłem, że nasz Postgres nie mógł użyć indeksu do porównania „polubienia”. To znaczy, jeśli napisałeś „jakąś kolumnę jak„ M% ””, nie wystarczyło przeskoczyć do M i zakończyć, gdy znalazło pierwsze N. Planowałem zmienić kilka zapytań na „między”. Następnie otrzymaliśmy nową wersję Postgres, która inteligentnie poradziła sobie z podobnymi problemami. Cieszę się, że nigdy nie zmieniłem zapytań. Oczywiście nie ma to bezpośredniego związku z tym, ale chcę powiedzieć, że wszystko, co zrobisz ze względu na wydajność, może stać się przestarzałe w następnej wersji.

Kolejność kolumn jest dla mnie prawie zawsze bardzo istotna, ponieważ rutynowo piszę ogólny kod, który odczytuje schemat bazy danych w celu tworzenia ekranów. Na przykład moje ekrany „edycji rekordu” są prawie zawsze budowane poprzez odczytanie schematu w celu uzyskania listy pól, a następnie wyświetlenie ich w kolejności. Gdybym zmienił kolejność kolumn, mój program nadal działałby, ale wyświetlacz może być dziwny dla użytkownika. Podobnie jak, spodziewasz się zobaczyć nazwę / adres / miasto / stan / kod pocztowy, a nie miasto / adres / kod pocztowy / nazwę / stan. Jasne, mógłbym umieścić kolejność wyświetlania kolumn w kodzie lub pliku kontrolnym lub czymś podobnym, ale za każdym razem, gdy dodawaliśmy lub usuwaliśmy kolumnę, musielibyśmy pamiętać, aby zaktualizować plik kontrolny. Lubię mówić raz. Ponadto, jeśli ekran edycji jest zbudowany wyłącznie ze schematu, dodanie nowej tabeli może oznaczać napisanie zerowej liczby wierszy kodu w celu utworzenia dla niej ekranu edycji, co jest super. (No dobra, w praktyce zwykle muszę dodać wpis do menu, aby wywołać ogólny program edycyjny i generalnie zrezygnowałem z ogólnego „wybierz rekord do aktualizacji” ponieważ jest zbyt wiele wyjątków, aby było to praktyczne .)

Poza oczywistym dostrojeniem wydajności, właśnie natknąłem się na przypadek narożny, w którym zmiana kolejności kolumn spowodowała awarię (wcześniej działającego) skryptu sql.

Z dokumentacji wynika, że ​​kolumny „TIMESTAMP i DATETIME” nie mają właściwości automatycznych, chyba że zostały wyraźnie określone, z tym wyjątkiem: Domyślnie pierwsza kolumna TIMESTAMP zawiera zarówno DEFAULT CURRENT_TIMESTAMP, jak i ON UPDATE CURRENT_TIMESTAMP, jeśli żadna z nich nie jest wyraźnie określona „ https: //dev.mysql .com / doc / refman / 5.6 / pl / timestamp-initialization.html

Tak więc polecenie ALTER TABLE table_name MODIFY field_name timestamp(6) NOT NULL;zadziała, jeśli to pole jest pierwszym znacznikiem czasu (lub datą i godziną) w tabeli, ale nie w innym przypadku.

Oczywiście możesz poprawić to polecenie alter, aby zawierało wartość domyślną, ale fakt, że zapytanie, które działało, przestało działać z powodu zmiany kolejności kolumn, sprawił, że bolała mnie głowa.

Jedynym momentem, w którym musisz się martwić o kolejność kolumn, jest sytuacja, w której oprogramowanie opiera się na tej kolejności. Zwykle jest to spowodowane tym, że programista się lenił i zrobił select *a następnie odniósł się do kolumn według indeksu, a nie nazwy w ich wyniku.

Ogólnie rzecz biorąc, to, co dzieje się w SQL Server po zmianie kolejności kolumn za pomocą Management Studio, polega na utworzeniu tabeli tymczasowej z nową strukturą, przeniesieniu danych do tej struktury ze starej tabeli, usunięciu starej tabeli i zmianie nazwy nowej. Jak możesz sobie wyobrazić, jest to bardzo zły wybór pod względem wydajności, jeśli masz duży stół. Nie wiem, czy mój SQL robi to samo, ale jest to jeden z powodów, dla których wielu z nas unika zmiany kolejności kolumn. Ponieważ select * nigdy nie powinien być używany w systemie produkcyjnym, dodanie kolumn na końcu nie jest problemem dla dobrze zaprojektowanego systemu. Kolejność kolumn w tabeli nie powinna być zmieniana w genralu.

W 2002 roku Bill Thorsteinson opublikował na forach Hewlett Packard swoje sugestie dotyczące optymalizacji zapytań MySQL poprzez zmianę kolejności kolumn. Od tego czasu jego post był dosłownie kopiowany i wklejany co najmniej sto razy w Internecie, często bez cytowania. Cytując go dokładnie ...

Ogólne zasady praktyczne:

• Najpierw kolumny klucza podstawowego.
• Następne kolumny kluczy obcych.
• Następnie często wyszukiwane kolumny.
• Często aktualizowane kolumny później.
• Kolumny dopuszczające wartość zerową jako ostatnie.
• Najrzadziej używane kolumny dopuszczające wartość null po częściej używanych kolumnach dopuszczających wartość null.
• Bloby we własnej tabeli z kilkoma innymi kolumnami.

Źródło: Fora HP.

Ale ten post pojawił się w 2002 roku! Ta rada dotyczy MySQL w wersji 3.23, ponad sześć lat przed wydaniem MySQL 5.1. I nie ma odniesień ani cytatów. Więc czy Bill miał rację? A jak dokładnie działa silnik magazynu na tym poziomie?

1. Tak, Bill miał rację.
2. Wszystko sprowadza się do sprawy połączonych łańcuchami rzędów i bloków pamięci.

Cytując Martina Zahna, profesjonalistę z certyfikatem Oracle , w artykule na temat tajemnic Oracle Row Chaining and Migration ...

Łańcuchy wpływają na nas inaczej. Tutaj zależy to od potrzebnych nam danych. Gdybyśmy mieli wiersz z dwiema kolumnami, który byłby rozłożony na dwa bloki, zapytanie:

SELECT column1 FROM table

gdzie kolumna1 znajduje się w bloku 1, nie spowodowałoby żadnego „wiersza ciągłego pobierania tabeli”. W rzeczywistości nie musiałby pobierać kolumny 2, nie podążałby za połączonym wierszem do końca. Z drugiej strony, jeśli poprosimy o:

SELECT column2 FROM table

a kolumna 2 znajduje się w bloku 2 z powodu łączenia wierszy, w rzeczywistości zobaczysz „tabelę pobierz ciągły wiersz”

Reszta artykułu to raczej dobra lektura! Ale cytuję tutaj tylko część, która jest bezpośrednio związana z naszym pytaniem.

Ponad 18 lat później muszę to powiedzieć: dzięki, Bill!