Jaka jest domyślna kolejność rekordów dla instrukcji SELECT w MySQL?

66

Załóżmy, że masz następującą tabelę i dane:

create table t (
    k int,
    v int,
    index k(k)
    ) engine=memory;

insert into t (k, v)
values (10, 1),
       (10, 2),
       (10, 3);

W przypadku wydawania select * from t where k = 10bez order byklauzuli, w jaki sposób MySQL domyślnie sortuje rekordy?

— stokrotka
źródło

75

Przesłanie mojej odpowiedzi na podobne pytanie dotyczące programu SQL Server:

W świecie SQL porządek nie jest nieodłączną właściwością zestawu danych. W ten sposób nie otrzymujesz żadnych gwarancji z RDBMS, że twoje dane wrócą w określonej kolejności - lub nawet w spójnej kolejności - chyba że zapytasz o swoje dane klauzulą ORDER BY.

Tak więc, aby odpowiedzieć na twoje pytanie:

MySQL sortuje rekordy, jak chce, bez żadnej gwarancji spójności.
Jeśli zamierzasz polegać na tym zamówieniu, musisz określić żądane zamówienie za pomocą ORDER BY. Robienie czegokolwiek innego to przygotowywanie się na niepożądane niespodzianki.

Jest to właściwość całego SQL, nie tylko MySQL. Odpowiedni tekst w specyfikacji SQL-92 to:

Jeśli nie podano <kolejność według klauzuli>, kolejność wierszy Q zależy od implementacji.

W specyfikacji kursorów znajdują się podobne fragmenty tekstu.

— Nick Chammas
źródło

26

Kolejność wierszy bez ORDER BYklauzuli może być następująca:

różni się między dowolnymi dwoma silnikami pamięci;
jeśli używasz tego samego silnika pamięci masowej, może on różnić się między dwiema dowolnymi wersjami tego samego silnika pamięci masowej; Przykład tutaj , przewiń w dół do „Zamawianie rzędów”.
jeśli wersja silnika pamięci jest taka sama, ale wersja MySQL jest inna, może być inna z powodu zmian optymalizatora zapytań między tymi wersjami;
jeśli wszystko jest takie samo, może być inaczej z powodu fazy księżyca i to jest OK.

— Laurynas Biveinis
źródło

10

Wkładanie jest nieuporządkowane, chaotyczne, po przyjeździe. Utworzony indeks ma kolejność, w której elementy są wstawiane w odpowiedniej lokalizacji na połączonej liście, która jest indeksem. Pomyśl o potrójnie połączonej liście indeksu, w której masz ruchome łącze do przodu z jednego elementu indeksu do następnego, link do tyłu do celów przejścia i integralności, a następnie zestaw wskaźników do rzeczywistych rekordów w tabeli, które dopasuj indeksowany element, o którym mowa.

Rzeczywiste dane, chaotycznie w pamięci. Indeks związany z danymi, uporządkowany w pamięci i konstrukcji. Rzeczywiste pobieranie danych, uporządkowane lub nieuporządkowane, zależy od danego zapytania.

— James Pulley
źródło

4

Jeśli chodzi o silnik pamięci MEMORY , oczekiwałbym, że kolejność będzie zgodna z kolejnością wstawiania, ponieważ HASHzamiast tego domyślny układ indeksu BTREEnie jest używany i żaden aspekt układu indeksu nie jest używany. Ponieważ zaindeksowałeś k, a k ma tę samą wartość, wszystkie klucze wchodzą do tego samego segmentu mieszania. Ponieważ nie ma powodu, aby zakładać dodatkową złożoność wypełniania segmentu mieszania, kolejność wstawiania jest najbardziej sensowna.

Wziąłem twoją próbną tabelę i dane i uruchomiłem 30 INSERTs i otrzymałem to:

mysql> use test
Database changed
mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(k int, v int,index k(k)) engine=memory;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3);
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+
| k    | v    |
+------+------+
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
+------+------+
30 rows in set (0.00 sec)

mysql>

Postanowiłem przetestować, dodając dwie różne wartości dla k: 10 i 11. Otrzymałem to:

mysql> use test
Database changed
mysql> drop table if exists t;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t(k int, v int,index k(k)) engine=memory;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values
    -> (11, 1), (11, 2), (11, 3), (10, 1), (10, 2), (10, 3),
    -> (11, 1), (11, 2), (11, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3);
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+
| k    | v    |
+------+------+
|   11 |    1 |
|   11 |    2 |
|   11 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   11 |    1 |
|   11 |    2 |
|   11 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
+------+------+
30 rows in set (0.00 sec)

mysql>

Wygląda na kolejność wstawiania. k = 11 to był pierwszy klucz zaszyfrowany, a następnie 10. Co powiesz na wstawienie 10 zamiast 11? Oto co mam:

mysql> use test
Database changed
mysql> drop table if exists t;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t(k int, v int,index k(k)) engine=memory;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3),
    -> (11, 1), (11, 2), (11, 3), (10, 1), (10, 2), (10, 3),
    -> (11, 1), (11, 2), (11, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3),
    -> (10, 1), (10, 2), (10, 3), (10, 1), (10, 2), (10, 3);
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+
| k    | v    |
+------+------+
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   11 |    1 |
|   11 |    2 |
|   11 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   11 |    1 |
|   11 |    2 |
|   11 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
|   10 |    1 |
|   10 |    2 |
|   10 |    3 |
+------+------+
30 rows in set (0.00 sec)

mysql>

To jednogłośne !!! ZAMÓWIENIE WSTAWIENIA jest odpowiedzią.

Sidenotes na temat używania indeksów dla silnika pamięci MEMORY

Wyszukiwania MEMORY w zakresie zakresu miałyby porównywalnie przerażającą skuteczność.

Podczas tworzenia indeksu można określić USING BTREEklauzulę wraz z definicją indeksu. Poprawiłoby to zakres zapytań o zakres.

Wyszukiwanie określonego wiersza da ten sam wynik w zakresie wydajności z jednym HASHlub BTREE.

AKTUALIZACJA 22.09.2011 11:18 EDT

Nauczyłem się dziś czegoś ciekawego. Przeczytałem link podany przez @Laurynas Biveinis z Percona: Link Percona mówi coś o tabelach MEMORY dla MySQL 5.5.15 :

Zamawianie rzędów

W przypadku braku ORDER BY rekordy mogą zostać zwrócone w innej kolejności niż poprzednia implementacja MEMORY. To nie jest błąd. Każda aplikacja oparta na konkretnym zamówieniu bez klauzuli ORDER BY może dawać nieoczekiwane wyniki. Określone zamówienie bez ORDER BY jest efektem ubocznym implementacji silnika pamięci i implementacji optymalizatora zapytań, który może i będzie się zmieniał między mniejszymi wersjami MySQL.

To był dla mnie dobry link do zobaczenia dzisiaj. Udzielona przeze mnie odpowiedź wykazała, że tabela, którą załadowałem, została pobrana w porządku, jakiego oczekiwałem DZIŚ w MySQL 5.5.12. Jak właśnie zauważyli Percona i @Laurynas Biveinis , nie ma gwarancji w innym mniejszym wydaniu.

Zamiast więc bronić mojej odpowiedzi, wolę promować odpowiedź od @Laurynas Biveinis, ponieważ jest to najbardziej aktualna informacja. Wyrazy uznania i czapki dla @Laurynas Biveinis . Chciałbym również podziękować @eevar za grzeczne zwrócenie uwagi na nie promowanie odpowiedzi na pytania dotyczące konkretnych wersji. Obaj otrzymali dziś moje poparcie.

— RolandoMySQLDBA
źródło