Подтвердить что ты не робот

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

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

(id, title, status)

  • id является основным ключом
  • title - название книги
  • статус может быть перечислением, описывающим текущее состояние книги (например, НАЛИЧИЕ, ПРОВЕРКА, ОБРАБОТКА, ОТСУТСТВИЕ)

Простой запрос, чтобы сообщить, сколько книг попадает в каждое состояние:

SELECT status, COUNT(*) FROM books GROUP BY status

или конкретно узнать, сколько книг доступно:

SELECT COUNT(*) FROM books WHERE status = "AVAILABLE"

Однако, как только таблица вырастет до миллионов строк, эти запросы занимают несколько секунд. Добавление индекса в столбец "статус" не влияет на мой опыт.

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

UPDATE

Используя таблицу образцов (с индексированным столбцом "статус" ) с 2 миллионами строк, я сравнивал запрос GROUP BY. Используя движок хранения InnoDB, запрос занимает 3,0 - 3,2 секунды на моей машине. Используя MyISAM, запрос занимает 0,9 - 1,1 секунды. Не было существенной разницы между count (*), count (status) или count (1) в любом случае.

MyISAM, по общему признанию, немного быстрее, но мне было любопытно узнать, есть ли способ ускорить выполнение эквивалентного запроса (например, 10-50 мс - достаточно быстро, чтобы вызывать каждый запрос веб-страницы для низкоуровневого запроса, сайт трафика) без умственных накладных расходов на кеширование и триггеры. Похоже, что ответ "нет способа быстро запустить прямой запрос", что я и ожидал, - я просто хотел убедиться, что я не пропустил простой альтернативы.

4b9b3361

ОТВЕТЫ

Ответ 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 интересуется точным счетчиком в реальном времени. В любом случае это красная селедка, так как к тому моменту, когда вы ее прочитаете, значение, скорее всего, изменится.