Dlaczego InnoDB nie przechowuje liczby wierszy?

Wszyscy wiedzą, że w tabelach, które używają InnoDB jako silnika, zapytania takie SELECT COUNT(*) FROM mytablesą bardzo niedokładne i bardzo wolne, szczególnie gdy tabela staje się większa i ciągłe wstawianie / usuwanie wierszy podczas wykonywania tego zapytania.

Tak jak to rozumiem, InnoDB nie przechowuje liczby wierszy w zmiennej wewnętrznej, co jest przyczyną tego problemu.

Moje pytanie brzmi: dlaczego tak jest? Czy tak trudno byłoby przechowywać takie informacje? To ważna informacja, którą należy znać w tak wielu sytuacjach. Jedyną trudnością, jaką widzę, jeśli taki wewnętrzny licznik zostałby zaimplementowany, jest zaangażowanie transakcji: jeśli transakcja jest niezatwierdzona, czy liczycie wstawione wiersze, czy nie?

PS: Nie jestem ekspertem od DB, jestem po prostu kimś, kto ma MySQL jako proste hobby. Więc jeśli tylko zapytałem o coś głupiego, nie bądź zbyt krytyczny: D.

Zgadzam się z @RemusRusanu (+1 za odpowiedź)

SELECT COUNT(*) FROM mydb.mytablew InnoDB zachowuje się tak, jak powinien transakcyjny silnik pamięci. Porównaj to z MyISAM.

MyISAM

W mydb.mytableprzypadku tabeli MyISAM uruchamianie SELECT COUNT(*) FROM mydb.mytable;jest jak uruchamianie SELECT table_rows FROM information_schema.table WHERE table_schema = 'mydb' AND table_name = 'mytable';. To powoduje szybkie sprawdzenie liczby wierszy w nagłówku tabeli MyISAM.

InnoDB

Jeśli mydb.mytablejest to stolik InnoDB, dostajesz mnóstwo rzeczy. Prowadzisz MVCC, regulując następujące kwestie:

• ib_logfile0 / ib_logfile1 (Ponów dzienniki)
• ibdata1
  • Cofnij dzienniki
  • Cofanie
  • Zmiany słownika danych
• Zarządzanie pulą buforów
• Izolacja transakcji (4 typy)
  • Powtarzalne odczyty
  • Czytaj Zaangażowane
  • Czytaj Nieprzyjęte
  • Serializowalny

Pytanie InnoDB o liczbę tabel wymaga nawigacji przez te złowieszcze rzeczy. W rzeczywistości nigdy tak naprawdę nie wiadomo, czy SELECT COUNT(*) from mydb.mytableliczy się tylko odczyty powtarzalne, czy obejmuje odczyty, które zostały zatwierdzone i te, które nie zostały zatwierdzone.

Możesz spróbować trochę ustabilizować sytuację, włączając innodb_stats_on_metadata .

Zgodnie z dokumentacją MySQL na temat danych innodb_stats_on_meta_data

Gdy ta zmienna jest włączona (która jest domyślna, jak przed jej utworzeniem), InnoDB aktualizuje statystyki podczas instrukcji metadanych, takich jak POKAŻ STATUS TABELI lub POKAŻ INDEKS, lub podczas uzyskiwania dostępu do tabel INFORMACJE_SCHEMA TABELE lub STATYSTYKA. (Te aktualizacje są podobne do tych, które mają miejsce w przypadku tabeli ANALIZA.) Po wyłączeniu InnoDB nie aktualizuje statystyk podczas tych operacji. Wyłączenie tej zmiennej może poprawić szybkość dostępu dla schematów, które mają dużą liczbę tabel lub indeksów. Może także poprawić stabilność planów wykonania zapytań dotyczących tabel InnoDB.

Wyłączenie go może, ale nie musi, dać bardziej stabilną liczbę w zakresie konfigurowania planów EXPLAIN. Może to wpływać na wydajność SELECT COUNT(*) from mydb.mytablew dobry, zły sposób lub wcale. Spróbuj i zobacz !!!

Na początek nie ma czegoś takiego jak „bieżąca liczba” do przechowywania w zmiennej. Podobne zapytanie SELECT COUNT(*) FROM ...jest zależne od bieżącego poziomu izolacji i wszystkich jednoczesnych oczekujących transakcji. W zależności od poziomu izolacji zapytanie może wyświetlać lub nie wyświetlać wierszy wstawianych lub usuwanych przez oczekujące niezatwierdzone transakcje. Jedynym sposobem na odpowiedź jest policzenie wierszy widocznych dla bieżącej transakcji.

Zauważ, że nawet nie dotknąłem jeszcze bardziej drażliwego tematu współbieżnych transakcji, które zaczynają się lub kończą podczas liczenia. Nie wspominając już o wycofywaniu ...

Chociaż teoretycznie byłoby możliwe utrzymanie dokładnej liczby wierszy dla danej tabeli za pomocą InnoDB, byłoby to kosztem dużej ilości blokowania, co negatywnie wpłynęłoby na wydajność. Różniłby się również w zależności od poziomu izolacji.

MyISAM już blokuje na poziomie stołu, więc nie ma tam dodatkowych kosztów.

Rzadko wymagam liczenia wierszy dla tabeli, chociaż dość często używam COUNT (*). Zasadniczo mam dołączoną klauzulę WHERE. Używając wydajnego indeksu dla małego zestawu wyników, stwierdzam, że są wystarczająco szybkie.

Nie zgadzam się, że liczby są niedokładne. Liczby reprezentują migawkę danych i zawsze uważałem, że są dokładne.

Krótko mówiąc, MySQL pozostawia Ci wdrożenie tego w InnoDB. Możesz przechowywać liczbę i zwiększać / zmniejszać ją po każdym zapytaniu. Jednak łatwiejszym rozwiązaniem jest prawdopodobnie przejście na MyISAM.