MySQL Как быстро посчитать все строки в таблице

Существует несколько способов получить количество строк в таблице MySQL. Некоторые продукты управления базами данных предоставляют статистику базы данных, например, размеры таблиц, но это можно сделать и с помощью прямого SQL.

Считать кол-во строк в PHP почти всегда непроизводительно.

Оглавление:

information_schema.TABLES
SHOW TABLE STATUS
SQL_CALC_FOUND_ROWS
Функция COUNT()
Подсчет всех строк в таблице
Подсчет не-NULL строк - разница между COUNT() и COUNT(*)
Подсчет только NULL строк
Подсчет уникальных значений
Условные подсчеты с использованием выражений

information_schema.TABLES

$dbname = DB_NAME;
$total = $wpdb->get_var(
	"SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$dbname' AND TABLE_NAME = '$wpdb->coin_price_history'"
);

Это способ позволяет получить данные о колличестве строк во всей таблице (без учета WHERE). Такие данные храняться в служебной таблице information_schema.TABLES:

SELECT TABLE_ROWS "Rows Count"
	FROM
		information_schema.TABLES
	WHERE
		table_name="my_table_name"
	AND
		table_schema="my_db_name";

Этот метод очень быстрый, но не точный, поскольку данные в information_schema о количестве строк и фактическое количество строк в таблицах не всегда синхронизированы. Чтобы этого избежать, необходимо выполнить оператор ANALYZE TABLE перед запросом к таблице information_schema.

ANALYZE TABLE my_db_name;

SHOW TABLE STATUS

Мы также можем получить данные таблицы следующим запросом:

SHOW TABLE STATUS LIKE 'my_db_name';

+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| Name               | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment | Max_index_length | Temporary |
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| coin_price_history | InnoDB |      10 | Dynamic    | 2798728 |             75 |   210518016 |               0 |    236552192 |   4194304 |        2856706 | 2022-04-05 18:24:38 | 2022-04-06 12:44:26 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |                0 | N         |
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+

В колонке Rows находится кол-во строк в таблице.

SQL_CALC_FOUND_ROWS

SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 5;
SELECT FOUND_ROWS();

Этот способ не самый быстрый, но есть случай, когда COUNT(*) не подходит, например когда результаты группируются, можно столкнуться с проблемой, что COUNT на самом деле не считает все строки.

Решением является SQL_CALC_FOUND_ROWS - этот маркер нужно добавить сразу после SELECT:

SELECT SQL_CALC_FOUND_ROWS [нужные поля или *] FROM table WHERE ...;

После запроса кол-во всех строк нужно получить еще одним запросом - он должен быть сделан сразу после запроса на выборку данных:

SELECT FOUND_ROWS();

Функция COUNT()

При COUNT(*) учитываются индексы столбцов, поэтому результат будет хорошим по производительности. Также, стоит отметить, что таблица MySQL с движком MyISAM хранит количество строк как впомогательные данные к таблице и он не считает все строки каждый раз при запросе если это возможно.

Если COUNT(*) работает медленно, нужно добавить в начало запроса маркер EXPLAIN и проверить, какие индексы используются, и где их следует добавить.

Функция COUNT() считает количество строк в таблице. Она подходит для подсчета как всех строк таблицы, так и строк подходящих под условие WHERE. Есть несколько сигнатур:

COUNT(*) - получит всё не-NULL и NULL строки (одинаковые значения считаются по отдельности).
COUNT(expr) - возвращает набор результатов, не содержащих NULL строк.
COUNT(DISTINCT expr) - возвращает количество уникальных строк, не содержащих значений NULL.
COUNT() и COUNT(*) - разницу читайте ниже - коротко COUNT() - пропускает NULL строки.

В каждом случае COUNT() возвращает BIGINT - количество совпавших строк. Или ноль, если не было найдено ни одной.

Подсчет всех строк в таблице

Чтобы подсчитать все строки в таблице, независимо от того, содержат они значения NULL или нет, используйте COUNT(*). Такая запись в основном возвращает количество строк в наборе результатов, возвращенных оператором SELECT.

SELECT COUNT(*) FROM my_table;

Вызов COUNT(*) без WHERE или дополнительных столбцов, будет работать очень быстро в таблицах MyISAM, поскольку количество строк хранится в столбце table_rows в таблице tables базы данных information_schema.

Для транзакционных систем хранения данных, таких как InnoDB, хранение точного количества строк проблематично, поскольку InnoDB не ведет внутренний подсчет строк в таблице. Если бы он велся, то одновременные транзакции могли бы "видеть" разное количество строк в одно и то же время. Следовательно, операторы SELECT COUNT() подсчитывают только строки, видимые текущей транзакции. Это означает, что выполнение запроса с COUNT() при большой нагрузке может привести к неточным числам.

Подсчет не-NULL строк - разница между COUNT() и COUNT(*)

Если в COUNT() ничего не передавать, то выполняется версия функции COUNT(expr), но без параметров. Вызов COUNT() таким образом возвращает только строки, где нет NULL. Например, у нас есть таблица code_values:

code_values
+-------+
| code  |
+-------+
| 1     |
+-------+
| NULL  |
+-------+
| NULL  |
+-------+
| 4     |
+-------+

Запрос COUNT() вернет 2, хотя в таблице 4 строки:

SELECT COUNT() FROM code_values;
+---------+
| COUNT() |
+---------+
| 2       |
+---------+

Обратите внимание, что эта версия функции COUNT() используется редко, потому что NULL-строки не должны быть проблемой в базах данных. Они могут создать проблемы только в том случае, если таблица не имеет первичного ключа. В большинстве случаев COUNT(*) будет работать просто отлично.

COUNT(expr) может принимать правильные выражения. Еще один запрос, который одинаково извлекает NULL и не NULL строки:

SELECT COUNT( IFNULL(code, 1) ) FROM code_values;

Подсчет только NULL строк

Версия функции COUNT(expr) также принимает имена отдельных столбцов, в результате чего COUNT(имя_столбца) возвращает количество записей, в которых имя_столбца не является NULL. Следовательно, следующий запрос SELECT получит количество строк, в которых столбец description содержит не NULL-значения:

SELECT COUNT(description) FROM widgets;

Подсчет уникальных значений

Функция COUNT(DISTINCT) возвращает количество строк с уникальными не-NULL значениями. Следовательно, включение ключевого слова DISTINCT исключает дубликаты строк из подсчета. Синтаксис функции следующий:

COUNT( DISTINCT expr,[expr...] )

Как и в обычной функции COUNT(), параметры expr выше могут быть любыми выражениями, включая конкретные столбцы, все столбцы (*), возвращаемые значения функций или выражения, такие как операторы IF/CASE.

Пример. Допустим, у нас есть следующая таблица клиентов:

+------------+-------------+
| last_name  | first_name  |
+------------+-------------+
| Tannen     | Biff        |
+------------+-------------+
| McFly      | Marty       |
+------------+-------------+
| Brown      | Dr. Emmett  |
+------------+-------------+
| McFly      | George      |
+------------+-------------+

Вызов COUNT(*) вернет количество всех строк (4), а COUNT DISTINCT по last_name каждую строку где дублирующие last_name будут считаться за одну строку, так что в итоге мы получим 3:

SELECT COUNT(*), COUNT(DISTINCT last_name) FROM clients;
+----------+---------------------------+
| COUNT(*) | COUNT(DISTINCT last_name) |
+----------+---------------------------+
| 4        | 3                         |
+----------+---------------------------+

Условные подсчеты с использованием выражений

Параметры функции COUNT() не ограничиваются именами столбцов; возвращаемые значения функций и выражения, такие как операторы IF/CASE, также будут работать.

Вот таблица, содержащая номера телефонов и пол нескольких пользователей:

+------------+---------+
| tel        | sex     |
+------------+---------+
| 7136609221 | male    |
+------------+---------+
| 7136609222 | male    |
+------------+---------+
| 7136609223 | female  |
+------------+---------+
| 7136609228 | male    |
+------------+---------+
| 7136609222 | male    |
+------------+---------+
| 7136609223 | female  |
+------------+---------+

Предположим, что мы хотим создать запрос, который сообщает нам, сколько разных женщин и мужчин содержится в таблице. Человек идентифицируется по номеру телефона ("tel"). Один и тот же номер может встречаться несколько раз, но пол этого номера должен учитываться только один раз.

Вот один из вариантов использования отдельного COUNT DISTINCT для каждого столбца:

SELECT COUNT(DISTINCT tel) gender_count,
	   COUNT(DISTINCT CASE WHEN gender = 'male'   THEN tel END) male_count,
	   COUNT(DISTINCT CASE WHEN gender = 'female' THEN tel END) female_count
FROM people

Получим:

+--------------+------------+---------------+
| gender_count | male_count | female_count  |
+--------------+------------+---------------+
| 4            | 3          | 1             |
+--------------+------------+---------------+

Полезные ссылки: