Оптимизировать mysql count query

Ответ 1

Если mysql должен считать 11M строк, на самом деле не так много способов ускорить простой подсчет. По крайней мере, чтобы не получить его на 1 секунду. Вы должны переосмыслить, как вы делаете свой счет. Несколько идей:

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

  SELECT min(incr_id) min_id FROM record_updates WHERE date_updated BETWEEN '2009-10-11 15:33:22' AND '2009-10-12 23:59:59';
  SELECT max(incr_id) max_id FROM record_updates WHERE date_updated > DATE_SUB(NOW(), INTERVAL 2 DAY);`
  

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

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

Ответ 2

Так как >'2009-10-11 15:33:22' содержит большую часть записей,
Я бы предложил сделать обратное совпадение, подобное <'2009-10-11 15:33:22' (mysql работает менее жестко и меньше задействованных строк)

select 
  TABLE_ROWS -
  (select count(*) from record_updates where add_date<"2009-10-11 15:33:22") 
from information_schema.tables 
where table_schema = "marctoxctransformation" and table_name="record_updates"

Вы можете комбинировать язык программирования (например, bash shell)
сделать этот расчет немного умнее...
например, выполнить план выполнения сначала, чтобы вычислить, какое сравнение будет использовать меньшую строку

Из моего тестирования (около 10 миллионов записей) нормальное сравнение занимает около 3 секунд,
и теперь сокращается примерно до 0,25 с

Ответ 3

Вы должны добавить индекс в поле date_updated.

Еще одна вещь, которую вы можете сделать, если не возражаете изменить структуру таблицы, - это использовать временную метку даты в формате "int" вместо формата "datetime", и это может быть еще быстрее. Если вы решите сделать это, запрос будет

select count(date_updated) from record_updates where date_updated > 1291911807

Ответ 4

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

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

select count(*) 
from record_updates 
where date_updated >= '2009-10-11 15:33:22' and date_updated < '2009-11-01';

select count(*) 
from record_updates 
where date_updated >= '2010-12-00';

select sum(row_count) 
from record_updates_summary 
where date_updated >= '2009-11-01' and date_updated < '2010-12-00';

Я оставил его выше для ясности, но вы можете сделать это в одном запросе:

select ( select count(*)
         from record_updates 
         where date_updated >= '2010-12-00'
               or ( date_updated>='2009-10-11 15:33:22' 
                    and date_updated < '2009-11-01' ) ) +
       ( select count(*) 
         from record_updates 
         where date_updated >= '2010-12-00' );

Вы можете адаптировать этот подход для составления сводной таблицы на основе целых недель или целых дней.

Ответ 5

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

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

Если вам не нужны точные цифры, ознакомьтесь с этим запросом:

select table_rows from information_schema.tables
where table_name = 'foo';

Пример разницы: count (*): 1876668, table_rows: 1899004. Значение table_rows - это оценка, и вы будете получать разные числа каждый раз, даже если ваша база данных не изменится.

Для моего собственного любопытства: нужны ли вам точные цифры, которые обновляются каждую секунду? ЕСЛИ так, почему?

Ответ 6

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

Ответ 7

Если вам нужно вернуть общее количество строк в таблице, то есть альтернатива SELECT COUNT(*), который вы можете использовать. SELECT COUNT(*) делает полное сканирование таблицы, чтобы вернуть общее количество строк таблицы, поэтому это может занять много времени. В этом случае вы можете использовать системную таблицу sysindexes. В таблице sysindexes есть столбец ROWS. Этот столбец содержит общее количество строк для каждой таблицы в вашей базе данных. Таким образом, вы можете использовать следующий оператор select вместо SELECT COUNT(*):

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

Это может повысить скорость вашего запроса.

EDIT: Я обнаружил, что мой ответ будет правильным, если вы используете базу данных SQL Server. Базы данных MySQL не имеют таблицы sysindexes.

Ответ 8

Есть несколько деталей, которые я хотел бы, чтобы вы уточнили (добавили бы комментарии к q, но на самом деле их легче удалить, когда вы обновите свой вопрос).

• Каково предполагаемое использование данных, вставляйте один раз и получайте подсчеты много раз, или ваши вставки и выборки приблизительны по параметру?
• Вам не нравится производительность вставки/обновления?
• Каков механизм, используемый для таблицы? (heck вы можете сделать SHOW CREATE TABLE...)
• Вам нужно, чтобы подсчеты были точными или точными (например, 0,1%)
• Можете ли вы использовать триггеры, сводные таблицы, изменить схему, изменить RDBMS и т.д. или просто добавить/удалить индексы?
• Может быть, вы должны объяснить также, что такое эта таблица? У вас есть record_id с мощностью, которая соответствует количеству строк, так это PK или FK или что это такое? Также мощность date_updated предлагает (хотя и не обязательно правильно), что она имеет одинаковые значения для ~ 5000 записей в среднем), так что это? - нормально спросить вопрос настройки SQL с не контекстом, но также приятно иметь некоторый контекст, особенно если перепроектирование является опцией.

В то же время, я предлагаю вам получить эту настройку script и проверить рекомендации, которые она вам даст (это просто общая настройка script - но он будет проверять ваши данные и статистику).

Ответ 9

Вместо того, чтобы делать count (*), попробуйте выполнить счет (1), например: -

select count(1) from record_updates where date_updated > '2009-10-11 15:33:22'

Я взял класс DB2 раньше, и я помню, как инструктор упомянул о выполнении подсчета (1), когда мы просто хотим подсчитать количество строк в таблице, независимо от данных, потому что это технически быстрее, чем count (*). Дайте мне знать, если это имеет значение.

ПРИМЕЧАНИЕ. Здесь вам может быть интересно прочитать ссылку: http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

Ответ 10

Это зависит от нескольких вещей, но что-то вроде этого может работать для вас

im, предполагая, что этот счет никогда не изменяется, как это было в прошлом, поэтому результат может быть как-то кэширован

count1 = "select count(*) from record_updates where date_updated <= '2009-10-11 15:33:22'"

дает общее количество записей в таблице, это приблизительное значение в таблице innodb, поэтому BEWARE, зависит от двигателя

count2 = "select table_rows from information_schema.`TABLES` where table_schema = 'marctoxctransformation' and TABLE_NAME = 'record_updates'"

ваш ответ

result = count2 - count1