MySQL - Różnica między używaniem count (*) a information_schema.tables do zliczania wierszy

Chcę w szybki sposób policzyć liczbę wierszy w mojej tabeli, która ma kilka milionów wierszy. Znalazłem post „ MySQL: najszybszy sposób na zliczanie liczby wierszy ” na Przepełnieniu stosu, który wyglądał, jakby to rozwiązało mój problem. Bayuah udzielił następującej odpowiedzi:

SELECT
    table_rows "Rows Count"
FROM
    information_schema.tables
WHERE
    table_name="Table_Name"
AND
    table_schema="Database_Name";

Który mi się podobał, ponieważ wygląda jak wyszukiwanie zamiast skanowania, więc powinien być szybki, ale postanowiłem go przetestować

SELECT COUNT(*) FROM table 

aby zobaczyć, jaka była różnica w wydajności.

Niestety otrzymuję różne odpowiedzi, jak pokazano poniżej:

Pytanie

Dlaczego odpowiedzi różnią się o około 2 miliony wierszy? Zgaduję, że zapytanie, które wykonuje pełne skanowanie tabeli, jest dokładniejszą liczbą, ale czy istnieje sposób, aby uzyskać prawidłową liczbę bez konieczności uruchamiania tego powolnego zapytania?

Pobiegłem ANALYZE TABLE data_302, co zakończyło się w 0,05 sekundy. Po ponownym uruchomieniu zapytania otrzymuję teraz znacznie bliższy wynik z 34384599 wierszy, ale wciąż nie jest to ta sama liczba, co select count(*)z 34906061 wierszy. Czy tabela analizy jest natychmiast zwracana i przetwarzana w tle? Wydaje mi się, że warto wspomnieć, że jest to testowa baza danych i do tej pory nie jest zapisywana.

Nikt nie będzie się przejmował, czy to tylko kwestia powiedzenia komuś, jak duża jest tabela, ale chciałem przekazać liczbę wierszy do kawałka kodu, który użyłby tej liczby do utworzenia asynchronicznych zapytań o „równej wielkości” do zapytania do bazy danych równolegle, podobnie jak metoda pokazana w Zwiększanie wydajności powolnego zapytania przy równoległym wykonywaniu zapytania przez Alexandra Rubina. W tej chwili otrzymam najwyższy identyfikator SELECT id from table_name order by id DESC limit 1i mam nadzieję, że moje tabele nie ulegną zbyt dużej fragmentacji.

Istnieją różne sposoby „liczenia” wierszy w tabeli. To, co jest najlepsze, zależy od wymagań (dokładność zliczania, częstotliwość wykonywania, to, czy potrzebujemy zliczenia całej tabeli, czy zmiennej wherei group byklauzul itp.)

• a) normalny sposób. Po prostu je policz .

  select count(*) as table_rows from table_name ; 

  Dokładność : 100% dokładna liczba w momencie uruchomienia zapytania.
  Wydajność : Nie nadaje się do dużych stołów. (w przypadku tabel MyISAM jest spektakularnie szybki, ale obecnie nikt nie korzysta z MyISAM, ponieważ ma tak wiele wad w stosunku do InnoDB. „Spektakularnie szybki” ma również zastosowanie tylko przy zliczaniu wierszy całej tabeli MyISAM - jeśli zapytanie ma WHEREwarunek, to nadal musi skanować tabelę lub indeks.)
  W przypadku tabel InnoDB zależy to od wielkości tabeli, ponieważ silnik musi wykonać skanowanie całej tabeli lub całego indeksu, aby uzyskać dokładną liczbę. Im większy stół, tym wolniej się robi.

• b) za pomocą SQL_CALC_FOUND_ROWSi FOUND_ROWS(). Może być użyty zamiast poprzedniego sposobu, jeśli chcemy również niewielką liczbę wierszy (zmieniając LIMIT). Widziałem, że jest używany do stronicowania (aby uzyskać kilka wierszy i jednocześnie wiedzieć, ile jest liczb całkowitych całkowitych i obliczyć liczbę pgegów).

  select sql_calc_found_rows * from table_name limit 0 ; 
  select found_rows() as table_rows ;

  Dokładność : taka sama jak poprzednia.
  Wydajność : taka sama jak poprzednia.

• c) za pomocą information_schematabel, jako powiązane pytanie:

  select  table_rows
  from    information_schema.tables
  where   table_schema = 'database_name'
    and   table_name = 'table_name' ;

  Dokładność : tylko przybliżenie. Jeśli tabela jest celem częstych operacji wstawiania i usuwania, wynik może być znacznie odbiegający od rzeczywistej liczby. Można to poprawić, uruchamiając ANALYZE TABLEczęściej.
  Wydajność : bardzo dobra, w ogóle nie dotyka stołu.

• d) zapisywanie liczby w bazie danych (w innej tabeli „licznika” ) i aktualizowanie tej wartości za każdym razem, gdy tabela ma wstawianie, usuwanie lub obcinanie (można to osiągnąć za pomocą wyzwalaczy lub modyfikując procedury wstawiania i usuwania) .
  To oczywiście doda dodatkowe obciążenie do każdej wstawki i usunie, ale zapewni dokładną liczbę.

  Dokładność : 100% dokładności.
  Wydajność : bardzo dobra, musi odczytać tylko jeden wiersz z innej tabeli.
  To jednak powoduje dodatkowe obciążenie bazy danych.

• e) przechowywanie ( buforowanie ) liczby w warstwie aplikacji - i zastosowanie pierwszej metody (lub kombinacji poprzednich metod). Przykład: uruchom zapytanie o dokładną liczbę co 10 minut. W międzyczasie między dwiema zliczeniami użyj wartości z pamięci podręcznej.

  Dokładność : przybliżenie, ale nie jest takie złe w normalnych okolicznościach (chyba że w przypadku dodawania lub usuwania tysięcy wierszy).
  Wydajność : bardzo dobra, wartość jest zawsze dostępna.

Dla INNODBchcesz information_schema.INNODB_SYS_TABLESTATS.NUM_ROWSna dokładne dane o liczbie wierszy tabeli, zamiast information_schema.TABLES.TABLE_ROWS.

Opublikowałem więcej szczegółów tutaj: /programming/33383877/why-does-information-schema-tables-give-such-an-unstable-answer-for-number-of-ro/49184843#49184843