Ускорение подсчета строк в MySQL

Ответ 1

Итак, вопрос:

Существуют ли какие-либо методы для ускорения этих запросов?

Ну, не совсем. Механизм хранения на основе столбцов, вероятно, будет быстрее с этими запросами SELECT COUNT (*), но он будет менее эффективен для почти любого другого запроса.

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

DELIMITER //

CREATE TRIGGER ai_books AFTER INSERT ON books
FOR EACH ROW UPDATE books_cnt SET total = total + 1 WHERE status = NEW.status
//
CREATE TRIGGER ad_books AFTER DELETE ON books
FOR EACH ROW UPDATE books_cnt SET total = total - 1 WHERE status = OLD.status;
//
CREATE TRIGGER au_books AFTER UPDATE ON books
FOR EACH ROW
BEGIN
    IF (OLD.status <> NEW.status)
    THEN
        UPDATE books_cnt SET total = total + IF(status = NEW.status, 1, -1) WHERE status IN (OLD.status, NEW.status);
    END IF;
END
//

Ответ 2

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

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

От: http://www.mail-archive.com/[email protected]/msg120320.html

База данных начинается с 1000 записей в нем я начинаю транзакцию Вы начинаете транзакция я удаляю 50 записей You добавьте 50 записей. Я делаю COUNT() и вижу 950 записей. Вы делаете COUNT() и видите 1050 записей. Я совершаю транзакцию - база данных теперь имеет 950 записей для всех, кроме вас. Вы совершаете транзакция - база данных имеет 1000 снова записывается.

Как InnoDB отслеживает, какие записи являются "видимыми" или "модифицируемыми" с в отношении любой транзакции блокировка на уровне строк, транзакция уровней изоляции и мульти-версии. http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/4.1/en/innodb-multi-versioning.html

Вот что подсчитывает, сколько записи, которые каждый может видеть, не так прямо вперед.

Итак, в нижней строке вам нужно будет как-то посмотреть на кеширование счетчиков, а не на стол, если вам нужно быстро и быстро получать эту информацию.

Ответ 3

from: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

InnoDB не сохраняет внутренний счет строк в таблице. (На практике это будет несколько сложной из-за multi-versioning.) Для обработки SELECT COUNT (*) FROM t statement, InnoDB должен сканировать индекс таблицы, который занимает некоторое время, если индекс не полностью в пуле буферов.

Предлагаемое решение:

Чтобы получить быстрый счет, вы должны использовать счетчик стола, который вы создаете сами и пусть ваше приложение обновит его в соответствии с вставками и удалениями оно делает. SHOW TABLE STATUS также может быть используется, если приблизительное количество строк достаточно.

Короче: count (*) (on innoDB) займет много времени для таблиц, содержащих большое количество строк. Это по дизайну и не может помочь.

Напишите свое обходное решение.

Ответ 4

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

Моя таблица использовала MyISAM и имела только около 100 тыс. строк. Запрос:

select count(*) from mytable where foreign_key_id=n

потребовалось 7-8 секунд.

Я добавил индекс на foreign_key_id:

create index myindex on mytable (foreign_key_id) using btree;

После создания индекса, оператор select выше сообщил время выполнения 0.00 секунды.

Ответ 5

Не было существенной разницы между count (*), count (status) или count (1)

count (column) возвращает количество строк, где столбец NOT NULL. Поскольку 1 не является NULL, а статус также, предположительно, NOT NULL, база данных будет оптимизировать тест и преобразовать их все в число (*). Что, по иронии судьбы, не означает "подсчет строк, где все столбцы не являются нулевыми" (или любая другая комбинация), это просто означает "подсчет строк" ​​...

Теперь, вернемся к вашему вопросу, вы не можете получить свой торт и съесть его...

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

• Или вы можете использовать count (*), но это будет медленно

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

Как правило, при значениях выше примерно "несколько" NO-ONE интересуется точным счетчиком в реальном времени. В любом случае это красная селедка, так как к тому моменту, когда вы ее прочитаете, значение, скорее всего, изменится.