Czy warto indeksować pole datetime w mysql?

137

Pracuję nad zaprojektowaniem dużej bazy danych. W mojej aplikacji będę miał wiele wierszy, na przykład obecnie mam jedną tabelę z 4 milionami rekordów. Większość moich zapytań używa klauzuli datetime do wybierania danych. Czy warto indeksować pola datetime w bazie danych mysql?

Select field1, field2,.....,field15
from table where field 20 between now() and now + 30 days

Staram się, aby moja baza danych działała dobrze, a zapytania działały płynnie

Co więcej, jak myślisz, jaki pomysł powinienem mieć, aby stworzyć bazę danych o wysokiej wydajności?

mysql indexing

— Jaylen
źródło

Co field 20?

— AlikElzin-kilaka,

Odpowiedzi:

164

MySQL zaleca używanie indeksów z różnych powodów, w tym eliminacji wierszy między warunkami: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

To sprawia, że twoja kolumna z datą i godziną jest doskonałym kandydatem na indeks, jeśli zamierzasz często używać jej w warunkach w zapytaniach. Jeśli jedynym warunkiem jest BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)i nie masz w nim innego indeksu, MySQL będzie musiał wykonać pełne skanowanie tabeli przy każdym zapytaniu. Nie jestem pewien, ile wierszy jest generowanych w ciągu 30 dni, ale jeśli jest to mniej niż około 1/3 wszystkich wierszy, bardziej wydajne będzie użycie indeksu w kolumnie.

Twoje pytanie o stworzenie wydajnej bazy danych jest bardzo szerokie. Powiedziałbym, żeby upewnić się, że jest znormalizowany i że wszystkie odpowiednie kolumny są zindeksowane (tj. Te używane w złączeniach i klauzulach WHERE).

— Pigułki wybuchowe
źródło

Dziękuję za wyjaśnienie. To naprawdę pomaga. Na pewno będę miał do tego więcej filtrów. Chcę tylko upewnić się, że indeksowanie pola daty i godziny jest dobrym pomysłem, czy nie, ponieważ możemy mieć zduplikowaną datę i godzinę. ale ty odpowiedziałeś, wyjaśniłeś to :) Dziękuję

— Jaylen

+1 dla „tych używanych w złączeniach i klauzulach gdzie”. Świetna praktyczna zasada dotycząca strategii indeksowania. To oczywiste, teraz o tym myślę, ale wcześniej nie przyszło mi do głowy

— Gaz_Edge

Jednak zapytanie dotyczące danych z zakresem dat , takim jak zakres danych od „2017-01-01 11:20” do „2018-01-03 12:12”, nie przyspiesza SELECTzapytania, mimo że zaindeksowałem date timekolumnę. .. index sprawia, że zapytanie jest szybkie, kiedy używam equaloperacji .. Czy mam rację?

— user3595632

Co powiesz na odpytywanie pól daty i godziny za pomocą funkcji czasu, takich jak DZIEŃ (data i godzina) lub GODZINA (data i godzina). Czy indeks pomoże lub utrudni w tym przypadku?

— cronoklee

Witam @Explosion Pills, jeśli potrzebuję tylko odpytać bazę tabeli na podstawie roku i miesiąca, czy uzyskam lepszą wydajność, jeśli utworzę nową kolumnę z tylko rokiem i miesiącem, a następnie ją zaindeksuję, zamiast tworzyć indeks bezpośrednio kolumny z datą i godziną ? Na przykład tworzę kolumnę, której wartość jest taka jak 201801.

— Woods Chen

Tutaj autor przeprowadził testy, które wykazały, że całkowity znacznik czasu unix jest lepszy niż DateTime. Uwaga, użył MySql. Ale wydaje mi się, że niezależnie od używanego silnika DB porównywanie liczb całkowitych jest nieco szybsze niż porównywanie dat, więc indeks int jest lepszy niż indeks DateTime. Take T1 - czas porównania 2 dat, T2 - czas porównania 2 liczb całkowitych. Wyszukiwanie w indeksowanym polu zajmuje około O (log (wiersze)) czasu, ponieważ indeks oparty jest na jakimś zrównoważonym drzewie - może być różny dla różnych silników DB, ale i tak Log (wiersze) to powszechne oszacowanie. (jeśli nie używasz maski bitowej lub indeksu opartego na drzewie r). Tak więc różnica jest taka (T2-T1) * Log (wiersze) - może odgrywać rolę, jeśli często wykonujesz zapytanie.

— Baurzhan
źródło

Dziękuję Ci. Myślałem o tym jako o opcji, ale nie wiedziałem, jak do tego podejść. Uważam, że masz absolutną rację, liczby całkowite są zawsze szybsze.

— Jaylen

Lepszy? Wątpię, aby znacznik czasu unixa był lepszy we wszystkich przypadkach. Tak, przechowywanie liczby całkowitej jest generalnie szybsze niż przechowywanie łańcucha, ale co ze wszystkimi funkcjami daty i godziny, które udostępnia MySQL? Samodzielne ich wdrożenie miałoby negatywny wpływ na wydajność lub funkcjonalność.

— Greg