Czy w MySQL kolejność kolumn w klauzuli WHERE wpływa na wydajność zapytań?

Mam problemy z wydajnością niektórych zapytań do bazy danych, które mają duże możliwe zestawy wyników.

Pytanie, o którym mowa, mam trzy ANDs w klauzuli WHERE

Czy kolejność klauzul ma znaczenie?

Tak jak w przypadku, jeśli najpierw wstawię klauzulę ASI_EVENT_TIME (ponieważ spowoduje to usunięcie większości wyników z którejkolwiek z klauzul).

Czy poprawi to czas działania zapytania?

PYTANIE:

SELECT DISTINCT  activity_seismo_info.* 
FROM `activity_seismo_info` 
WHERE 
    activity_seismo_info.ASI_ACTIVITY_ID IS NOT NULL  AND 
    activity_seismo_info.ASI_SEISMO_ID IN (43,44,...,259) AND 
    (
        activity_seismo_info.ASI_EVENT_TIME>='2011-03-10 00:00:00' AND 
        activity_seismo_info.ASI_EVENT_TIME<='2011-03-17 23:59:59'
    ) 

ORDER BY activity_seismo_info.ASI_EVENT_TIME DESC

OBJAŚNIJ zapytanie:

+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
| id | select_type | table   | type  | possible_keys             | key          | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
|  1 | SIMPLE      | act...o | range | act...o_FI_1,act...o_FI_2 | act...o_FI_1 | 5       | NULL | 65412 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+

Za pomocą:

PHP 5.2

MySQL 5.0.51a-3ubuntu5.4

Propel 1.3

Symfony 1.2.5

Nie sądzę. Optymalizator zapytań powinien być wystarczająco sprytny.

Możesz spróbować zmienić kolejność klauzul WHERE i przekonać się, że WYJAŚNIENIA mówią ci to samo w każdym przypadku.

O tym, co można zrobić, aby zoptymalizować to zapytanie: Czy istnieje indeks w ASI_EVENT_TIME? (Myślę, że jest to najbardziej istotne dla tego zapytania, ponieważ sortujesz również wyniki za jego pomocą).

Czy istnieją indeksy w pozostałych dwóch polach (ASI_SEISMO_ID i ASI_ACTIVITY_ID)?

Byłoby pomocne, gdybyś opublikował strukturę tabeli.

Z dokumentacji :

Jeśli tabela ma indeks wielokolumnowy, optymalizator może wykorzystać dowolny lewy przedrostek indeksu do znalezienia wierszy. Na przykład, jeśli masz indeks trzykolumnowy na (col1, col2, col3), masz indeksowane możliwości wyszukiwania na (col1), (col1, col2) i (col1, col2, col3).

MySQL nie może używać indeksu, jeśli kolumny nie tworzą prefiksu indeksu po lewej stronie.

Tak, powinien być taki sam, jak kolejność kolumn w indeksie złożonym .

Nie, to nie ma znaczenia.

Optymalizator wykonuje kilka prostych przekształceń bezpośrednio po przeanalizowaniu kodu SQL - jest to jedna z nich.

GDZIE bar i bar

optymalizuje to samo co

GDZIE bar I foo

Jednak,

GDZIE nierówne # 1 ORAZ nierówne # 2

Nie można zoptymalizować obu części. Na przykład,

GDZIE MIĘDZY 1 a 3 ORAZ b> 17

nie może dobrze wykorzystać INDEKSU (a, b) lub INDEKSU (b, a)

Aby wyrazić to inaczej, najpierw używane są wszystkie testy „=” ORAZ razem w klauzuli WHERE, a następnie można obsłużyć jedno nie - „=” (IN, MIĘDZY,> itd.). Nie można skutecznie zoptymalizować więcej niż jednego.

Twoje zapytanie zawiera 3 takie klauzule.

Jak się okazuje, INDEKS (EVENT_TIME) jest prawdopodobnie najbardziej przydatny - pomoże w jednym z AND, i może być użyty do uniknięcia „sortowania plików” dla ORDER BY.

Jeśli nie ma zduplikowanych wierszy (dlaczego, u licha, miałby być?), Pozbądź się DISTINCT. To powoduje jeszcze większy wysiłek.

Zadawaj pytania POKAŻ TWORZENIE TABELI i STATUS POKAŻU TABELI, zadając pytania dotyczące wydajności.

Aktualizacja ... Nowsze wersje (np. MySQL 5.7) mogą, w niektórych sytuacjach, traktować IN( list of constants )prawie jak =. Aby grać bezpiecznie, trzymaj się tej kolejności (każda część jest opcjonalna):

1. Dowolna liczba =.
2. Niektóre INs.
3. Co najwyżej jeden zakres.

MySQL, w którym dokument optymalizacji mówi:

Możesz mieć ochotę przepisać zapytania, aby przyspieszyć operacje arytmetyczne, a jednocześnie poświęcić czytelność. Ponieważ MySQL automatycznie wykonuje podobne optymalizacje , często można uniknąć tej pracy i pozostawić zapytanie w bardziej zrozumiałej i łatwej do utrzymania formie. Niektóre z optymalizacji przeprowadzanych przez MySQL są następujące:

• ...

• Dla każdej tabeli w sprzężenia, prostszy GDZIE jest skonstruowany , aby uzyskać szybki GDZIE oceny dla tabeli, a także pominąć wiersze jak najszybciej .

• Zapytany jest każdy indeks tabeli i używany jest najlepszy indeks, chyba że optymalizator uzna, że bardziej efektywne jest użycie skanowania tabeli . Kiedyś zastosowano skan oparty na tym, czy najlepszy indeks obejmuje więcej niż 30% tabeli, ale stały procent nie określa już wyboru między użyciem indeksu a skanem. Optymalizator jest teraz bardziej złożony i opiera swoje oszacowanie na dodatkowych czynnikach, takich jak rozmiar tabeli, liczba wierszy i rozmiar bloku we / wy.

W ten sposób racjonalne jest, aby optymalizator zapytań pomijał kolejność JAK użyliśmy kolumn w zapytaniu (Nie tylko MySQL, ale SQL jest językiem deklaratywnym i musi robić to, co chcemy, a nie to, co chcemy).

Jednak nadal lubię mieć taki sam rodzaj kolumn klucza złożonego w zapytaniu, ale czasami jest to nieuniknione, na przykład, gdy używamy ORM lub ActiveRecord, w niektórych ramach, takich jak yii2, dostosowywanie kryteriów relacji zostanie dołączone na końcu warunek „włączony”, ale nadal potrzebujemy możliwości QueryBuilders w różnych częściach aplikacji.

DOWOLNE pole, które jest używane w klauzulach WHERE / HAVING i ma wysoką selektywność (liczba unikalnych wartości / całkowita liczba rekordów> 10% ~ 20%) MUSI być indeksowane.

Jeśli więc ASI_EVENT_TIMEkolumna ma wiele możliwych wartości, najpierw zindeksuj je wszystkie. Następnie, jak powiedział @ypercube, spróbuj je zmienić i zobacz, co mówi WYJAŚNIENIE. Powinny być takie same.

Dodatkowo, chcesz spojrzeć na indeksowanie filtrów LIKE SQL . Chociaż nie jest to odpowiedź na pytanie, ale nadal dowiesz się, jak indeksowanie działa pod maską.

* Edycja: zapoznaj się z linkami podanymi poniżej w komentarzach, aby dowiedzieć się więcej o indeksowaniu.