Самый быстрый способ подсчета точного количества строк в очень большой таблице?

Я столкнулся с статьями, в которых SELECT COUNT(*) FROM TABLE_NAME что SELECT COUNT(*) FROM TABLE_NAME будет медленным, если в таблице много строк и много столбцов.

У меня есть таблица, которая может содержать даже миллиарды строк [она имеет приблизительно 15 столбцов]. Есть ли лучший способ получить ТОЧНОЕ количество строк в таблице?

Перед тем, как ответить, учтите следующее:

  • Я ищу независимое решение поставщика базы данных. Это нормально, если он охватывает MySQL , Oracle , MS SQL Server . Но если на самом деле нет независимого решения для поставщиков баз данных, я соглашусь на различные решения для разных поставщиков баз данных.

  • Я не могу использовать какой-либо другой внешний инструмент для этого. Я в основном ищу SQL-решение.

  • Я не могу нормализовать свой дизайн базы данных. Это уже в 3NF, и, кроме того, много кода уже написано вокруг него.

Простой ответ:

  • независимое решение поставщика базы данных = используйте стандарт = COUNT(*)
  • существуют приблизительные решения SQL Server, но не используют COUNT (*) = вне сферы действия

Заметки:

COUNT (1) = COUNT (*) = COUNT (PrimaryKey) на всякий случай

Редактировать:

Пример SQL Server (1,4 миллиарда строк, 12 столбцов)

 SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK) -- NOLOCK here is for me only to let me test for this answer: no more, no less 

1 пробег, 5:46 минут, кол-во = 1,401,659,700

 --Note, sp_spaceused uses this DMV SELECT Total_Rows= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'MyBigtable' AND (index_id < 2) 

2 пробега, как менее 1 секунды, счет = 1,401,659,670

Второй имеет меньше строк = неправильно. Было бы одинаково или больше в зависимости от записи (удаление выполняется из часов здесь)

Самый быстрый способ MySQL – это:

 SHOW TABLE STATUS; 

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

Я столкнулся с статьями, в которых указано, что SELECT COUNT (*) FROM TABLE_NAME будет медленным, если в таблице много строк и много столбцов.

Это зависит от базы данных. Некоторые ускоряют подсчеты, например, отслеживая, живут ли строки в мертвом индексе, что позволяет сканировать только индекс, чтобы извлечь количество строк. Другим нет, и, следовательно, нужно посещать всю таблицу и подсчитывать живые строки один за другим. Либо будет медленным для огромной таблицы.

Обратите внимание, что вы обычно можете извлечь хорошую оценку с помощью инструментов оптимизации запросов, статистики таблиц и т. Д. Например, в PostgreSQL вы можете анализировать вывод explain count(*) from yourtable и получать достаточно хорошую оценку количество строк. Это подводит меня к вашему второму вопросу.

У меня есть таблица, которая может содержать даже миллиарды строк [она имеет приблизительно 15 столбцов]. Есть ли лучший способ получить ТОЧНОЕ количество строк в таблице?

Шутки в сторону? 🙂 Вы действительно имеете в виду точный подсчет из таблицы с миллиардами строк? Вы действительно уверены? 🙂

Если вы действительно это делаете, вы можете сохранить трассировку общего использования триггеров, но если хотите, параллелизм ума и взаимоблокировки.

Вы можете попробовать это sp_spaceused (Transact-SQL)

Отображает количество строк, зарезервированное место на диске и пространство на диске, используемое таблицей, индексированным представлением или очередью Service Broker в текущей базе данных, или отображает дисковое пространство, зарезервированное и используемое всей базой данных.

я использую

 select /*+ parallel(a) */ count(1) from table_name a; 

Я не так эксперт, как другие, которые ответили, но у меня возникла проблема с процедурой, которую я использовал для выбора случайной строки из таблицы (не слишком актуальной), но мне нужно было знать количество строк в моей справочной таблице для вычисления случайного индекса. Используя традиционные функции Count (*) или Count (1), я иногда получал до 2 секунд для запуска моего запроса. Поэтому вместо этого (для моей таблицы с именем «tbl_HighOrder») я использую:

 Declare @max int Select @max = Row_Count From sys.dm_db_partition_stats Where Object_Name(Object_Id) = 'tbl_HighOrder' 

Он отлично работает, и время запросов в Management Studio равно нулю.

Есть ли лучший способ получить ТОЧНОЕ количество строк в таблице?

Чтобы ответить на ваш вопрос просто, Нет .

Если вам нужен независимый способ СУБД, это самый быстрый способ:

 SELECT COUNT(*) FROM TableName 

У некоторых поставщиков СУБД могут быть более быстрые способы, которые будут работать только для их систем. Некоторые из этих вариантов уже размещены в других ответах.

COUNT(*) должен быть оптимизирован СУБД (по крайней мере, любой PROD достойный DB), так что не пытайтесь обойти их оптимизацию.

На стороне примечание:
Я уверен, что многие из ваших других запросов также занимают много времени, чтобы закончить из-за вашего размера таблицы. Любые проблемы с производительностью, вероятно, должны быть решены, если подумать о своем дизайне схемы с учетом скорости. Я понимаю, вы сказали, что это не вариант изменения, но может оказаться, что 10 + минута запросов тоже не вариант. 3rd NF не всегда лучший подход, когда вам нужна скорость, а иногда данные могут быть разделены на несколько таблиц, если записи не нужно хранить вместе. Что-то думать о…

Ну, поздно на 5 лет и не уверен, если это поможет:

Я пытался подсчитать «нет». строк в таблице SQL Server с использованием MS SQL Server Management Studio и столкнулся с некоторой ошибкой переполнения, тогда я использовал следующее:

выберите count_big (1) FROM [dbname]. [dbo]. [FactSampleValue];

Результат :

24296650578 строк

Я не думаю, что существует общее всегда быстрое решение: некоторые версии RDBMS / версии имеют определенную оптимизацию для SELECT COUNT(*) которые используют более быстрые параметры, в то время как другие просто сканируют таблицу. Вам нужно будет перейти на сайты документации / поддержки для второго набора, для чего, вероятно, потребуется написать более конкретный запрос, обычно тот, который каким-то образом попадает в индекс.

РЕДАКТИРОВАТЬ:

Вот мысль, которая может работать, в зависимости от вашей схемы и распределения данных. У вас есть индексированный столбец, который ссылается на увеличивающееся значение, числовое увеличение идентификатора, скажем, или даже метку времени или дату? Затем, предполагая, что удаление не происходит, должно быть возможно сохранить счет до некоторого недавнего значения (вчерашняя дата, наивысшее значение идентификатора в некоторой недавней точке выборки) и добавить счет за пределы этого, который должен очень быстро разрешаться в индексе , Конечно, очень зависит от значений и индексов, но применима практически ко всем версиям любой СУБД.

Если версия SQL Server – 2005/2008, вы можете использовать DMV для вычисления количества строк в таблице:

 -- Shows all user tables and row counts for the current database -- Remove is_ms_shipped = 0 check to include system objects -- i.index_id < 2 indicates clustered index (1) or hash table (0) SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME 

Для ядра базы данных SQL Server 2000 sysindexes будут работать, но настоятельно рекомендуется избегать использования его в будущих выпусках SQL Server, поскольку он может быть удален в ближайшем будущем.

Пример кода, взятый из: Как быстро получить таблицы строк

Не совсем одно решение DBMS-agnostic, но, по крайней мере, ваш клиентский код не увидит разницу …

Создайте еще одну таблицу T только с одной строкой и одним целочисленным полем N 1 и создайте INSERT TRIGGER, который просто выполняет:

 UPDATE T SET N = N + 1 

Также создайте DELETE TRIGGER, который выполняет:

 UPDATE T SET N = N - 1 

СУБД, достойная его соли, гарантирует атомарность операций выше 2 , а N будет содержать точное количество строк во все времена, что затем очень быстро получается простым:

 SELECT N FROM T 

Хотя триггеры специфичны для СУБД, выбор из T не является, и ваш клиентский код не нужно изменять для каждой поддерживаемой СУБД.

Однако это может иметь некоторые проблемы с масштабируемостью, если таблица INSERT или DELETE-интенсивна, особенно если вы не COMMIT сразу после INSERT / DELETE.


1 Эти имена являются просто заполнителями – используйте что-то более значимое в производстве.

2 Ie N не может быть изменено параллельной транзакцией между чтением и записью в N, если оба чтения и записи выполняются в одном выражении SQL.

Буквально безумный ответ, но если у вас установлена ​​какая-то система репликации (для системы с миллиардом строк, я надеюсь, что вы это сделаете), вы можете использовать грубую оценку (например, MAX(pk) ), разделите это значение на количество подчиненных вам серверов, параллельно выполняйте несколько запросов.

По большей части вы разделили бы запросы по ведомым устройствам на основе лучшего ключа (или основного ключа, которого я предполагаю) таким образом (мы будем использовать 250000000 в качестве наших строк / подчиненных):

 -- First slave SELECT COUNT(pk) FROM t WHERE pk < 250000000 -- Ith slave where 2 <= I <= N - 1 SELECT COUNT(pk) FROM t WHERE pk >= I*250000000 and pk < (I+1)*250000000 -- Last slave SELECT COUNT(pk) FROM t WHERE pk > (N-1)*250000000 

Но вам нужен только SQL. Какой бюст. Хорошо, давайте скажем, вы садомазохист. На главном (или ближайшем подчиненном) вам, скорее всего, потребуется создать таблицу для этого:

 CREATE TABLE counter_table (minpk integer, maxpk integer, cnt integer, slaveid integer) 

Поэтому вместо того, чтобы иметь только те, которые выполняются в ваших подчиненных, вам нужно будет сделать вставку, сродни этому:

 INSERT INTO counter_table VALUES (I*25000000, (I+1)*250000000, (SELECT COUNT(pk) FROM ... ), @@SLAVE_ID) 

Вы можете столкнуться с проблемами с ведомыми, записывающими таблицу в master. Возможно, вам понадобится еще больше садиса – я имею в виду, творческий:

 -- A table per slave! INSERT INTO counter_table_slave_I VALUES (...) 

В конце концов, вы должны иметь подчиненный, который существует последним в пути, пройденном графом репликации, относительно первого подчиненного. Этот раб должен теперь иметь все другие значения счетчика и должен иметь свои собственные значения. Но к тому времени, когда вы закончите, вероятно, добавлены строки, поэтому вам придется вставить еще один, компенсирующий записанный max pk в вашей counter_table и текущем max pk.

В этот момент вам нужно будет выполнить агрегатную функцию, чтобы выяснить, что представляют собой полные строки, но это проще, поскольку вы будете использовать ее не более, чем «количество ведомых, которые у вас есть и измените».

Если вы находитесь в ситуации, когда у вас есть отдельные таблицы в подчиненных устройствах, вы можете UNION получить все нужные вам строки.

 SELECT SUM(cnt) FROM ( SELECT * FROM counter_table_slave_1 UNION SELECT * FROM counter_table_slave_2 UNION ... ) 

Или вы знаете, будьте немного сумасшедшими и перенесите свои данные в распределенную систему обработки или, возможно, используете решение Data Warehousing (которое также даст вам громадные данные в будущем).

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

Но если у вас хорошая репликация, то ваш прирост скорости должен быть напрямую связан с числом или подчиненными. Фактически, если вам нужно всего 10 минут, чтобы запустить счетный запрос, и у вас есть 8 подчиненных, вы сократите время до нескольких минут. Может быть, час, чтобы сгладить детали этого решения.

Конечно, вы никогда не получите удивительно точного ответа, так как это распределенное решение вводит немного времени, когда строки могут быть удалены и вставлены, но вы можете попытаться получить распределенную блокировку строк в одном экземпляре и получить точный счет строк в таблице для определенного момента времени.

На самом деле это кажется невозможным, поскольку вы в основном придерживаетесь только SQL-решения, и я не думаю, что вам предоставлен механизм для запуска тайного и заблокированного запроса на нескольких подчиненных серверах. Возможно, если у вас есть контроль над файлом журнала репликации … что означает, что вы буквально разворачиваете ведомые для этой цели, что, без сомнения, медленнее, чем просто запуск запроса на счет на одной машине.

Так что у меня есть две копейки в 2013 году.

Если триггер вставки слишком дорог для использования, но может быть предоставлен триггер удаления , и есть id автоинкремента , после подсчета всей таблицы один раз и запоминания счетчика как счетчика last-count и last-counted-id last-count ,

то каждый день нужно просто подсчитывать id > last-counted-id , добавлять это к last-count и сохранять новый last-counted-id last-count .

Триггер delete уменьшал бы последний счет, если идентификатор удаленной записи <= last-counted-id.

Если у вас есть типичная структура таблицы с столбцом первичного ключа с автоматическим приращением, в котором строки никогда не удаляются, следующий способ будет самым быстрым способом определения количества записей и должен работать аналогично в большинстве баз данных, совместимых с ANSI:

 SELECT TOP(1) <primarykeyfield> FROM <table> ORDER BY <primarykeyfield> DESC; 

Я работаю с таблицами MS SQL, содержащими миллиарды строк, для которых требуется время отклика субсекунды для данных, включая количество записей. Аналогичный SELECT COUNT (*) потребует минут для обработки путем сравнения.

Я опаздываю на этот вопрос, но вот что вы можете сделать с MySQL (поскольку я использую MySQL). Я делюсь своими наблюдениями здесь:

 1) SELECT COUNT(*) AS TOTAL_ROWS FROM <TABLE_NAME> 

результат
Количество строк : 508534
Вывод консоли: Затронутые строки: 0 Найдено строк: 1 Предупреждения: 0 Длительность для 1 запроса: 0.125 сек.
Занимает некоторое время для таблицы с большим количеством строк, но количество строк очень точное.

 2) SHOW TABLE STATUS or SHOW TABLE STATUS WHERE NAME="<TABLE_NAME>" 

результат
Количество строк : 511235
Выход консоли: Затронутые строки: 0 Найдено строк: 1 Предупреждения: 0 Длительность для 1 запроса: 0.250 sec Резюме: Количество строк не является точным.

 3) SELECT * FROM information_schema.tables WHERE table_schema = DATABASE(); 

результат
Количество строк : 507806
Вывод консоли: Затронутые строки: 0 Найдено строк: 48 Предупреждения: 0 Длительность для 1 запроса: 1.701 сек.
Количество строк не является точным.

Я не эксперт по MySQL или базам данных, но я обнаружил, что для очень больших таблиц вы можете использовать опцию 2 или 3 и получить «честную идею» о том, сколько строк присутствует.

Мне нужно было подсчитать количество строк для отображения некоторых характеристик в пользовательском интерфейсе. С вышеперечисленными запросами я знал, что полные строки составляют более 500 000, поэтому я придумал статистику, например «Более 500 000 строк», не показывая точное количество строк.

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

выберите строки из sysindexes, где id = Object_ID ('TableName') и indid <2

Поместите индекс в некоторый столбец. Это должно позволить оптимизатору выполнить полное сканирование индексных блоков вместо полного сканирования таблицы. Это сократит затраты на IO. Посмотрите план выполнения до и после. Затем измерьте время настенных часов в обоих направлениях.

Если вы используете Oracle, как насчет этого (при условии, что статистика таблицы обновляется):

 select <TABLE_NAME>, num_rows, last_analyzed from user_tables 

last_analyzed покажет время, когда статистика была последней.

Для сервера Sql попробуйте это

 SELECT T.name, I.rows AS [ROWCOUNT] FROM sys.tables AS T INNER JOIN sys.sysindexes AS I ON T.object_id = I.id AND I.indid < 2 WHERE T.name = 'Your_Table_Name' ORDER BY I.rows DESC 

Может быть, немного поздно, но это может помочь другим для MSSQL

; WITH RecordCount AS (SELECT ROW_NUMBER () OVER (ORDER BY COLUMN_NAME) AS [RowNumber] FROM TABLE_NAME) SELECT MAX (RowNumber) FROM RecordCount