Как ускорить "select count (*)" с "group by" и "where"?

Ответ 1

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

(проще) - Убедитесь, что у вас есть правый индекс покрытия

CREATE INDEX ix_temp ON relations (relation_title, object_title);

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

(немного сложнее) - убедитесь, что ваши поля varchar как можно меньше

Одна из главных проблем с индексами varchar в MySQL заключается в том, что при обработке запроса полный объявленный размер поля будет выведен в ОЗУ. Поэтому, если у вас есть varchar (256), но вы используете только 4 символа, вы все равно платите 256-байтовое использование ОЗУ во время обработки запроса. Ой! Поэтому, если вы можете легко сжать свои пределы varchar, это должно ускорить ваши запросы.

(сложнее) - Нормализовать

30% ваших строк, имеющих одно строковое значение, - это четкий крик для нормализации в другую таблицу, чтобы вы не дублировали строки миллионы раз. Рассмотрите возможность нормализации на три таблицы и использование целых идентификаторов для их объединения.

В некоторых случаях вы можете нормализовать под обложками и скрыть нормализацию с представлениями, которые соответствуют имени текущей таблицы... тогда вам нужно только сделать запросы INSERT/UPDATE/DELETE осведомленными о нормализации, но может уйти только ваши SELECT.

(самый сложный) - Хешируйте свои строковые столбцы и индексируйте хеши

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

CREATE INDEX ix_temp ON relations (relation_title_hash, object_title_hash);

Обратите внимание, что вам нужно будет поиграть с SELECT, чтобы убедиться, что вы выполняете вычисление с помощью хеш-индекса и не вытягиваете кластерный индекс (требуется для разрешения фактического текстового значения object_title, чтобы удовлетворить запрос).

Кроме того, если отношение size_title имеет небольшой размер varchar, но заголовок объекта имеет большой размер, тогда вы можете потенциально хэш только object_title и создать индекс на (relation_title, object_title_hash).

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

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

Ответ 2

Индексирование столбцов в предложении GROUP BY было бы первым, что нужно попробовать, используя составной индекс. На такой запрос можно ответить, используя только данные индекса, избегая необходимости вообще сканировать таблицу. Поскольку записи в индексе отсортированы, СУБД не должны выполнять отдельный вид как часть групповой обработки. Тем не менее, индекс замедлит обновление таблицы, поэтому будьте осторожны с этим, если в вашей таблице произойдут тяжелые обновления.

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

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

Материализованное представление будет другим возможным подходом, но опять же это не поддерживается непосредственно в MySQL. Однако, если вы не нуждаетесь в полной статистике COUNT, вы можете периодически запускать инструкцию CREATE TABLE ... AS SELECT ... для ручного кэширования результатов. Это немного уродливо, поскольку оно не прозрачно, но может быть приемлемым в вашем случае.

Вы также можете поддерживать таблицу кеша логического уровня с помощью триггеров. В этой таблице будет столбец для каждого столбца в предложении GROUP BY, с столбцом Count для хранения количества строк для данного значения ключа группировки. Каждый раз, когда строка добавляется или обновляется в базовой таблице, вставляйте или увеличивайте/уменьшайте строку счетчика в сводной таблице для этого конкретного ключа группировки. Это может быть лучше, чем поддельный материализованный подход к представлению, поскольку кешированное резюме всегда будет актуальным, и каждое обновление выполняется постепенно и должно иметь меньшее влияние на ресурс. Я думаю, что вам придется следить за конфликтом блокировки в таблице кеша.

Ответ 3

Если у вас есть InnoDB, счетчик (*) и любая другая агрегированная функция будут выполнять сканирование таблицы. Здесь я вижу несколько решений:

• Используйте триггеры и сохраняйте агрегаты в отдельной таблице. Плюсы: целостность. Минусы: медленные обновления
• Использовать очереди обработки. Плюсы: быстрые обновления. Минусы: старое состояние может сохраняться до тех пор, пока очередь не будет обработана, поэтому пользователь может почувствовать отсутствие целостности.
• Полностью отделить уровень доступа к хранилищу и сохранить агрегаты в отдельной таблице. Слой хранения будет знать структуру данных и может применять дельта, а не выполнять полный счет. Например, если вы предоставляете функциональность "addObject", в которой вы будете знать, когда объект был добавлен, и, таким образом, это повлияет на совокупность. Затем вы делаете только update table set count = count + 1. Плюсы: быстрые обновления, целостность (вы можете использовать блокировку, хотя в случае, если несколько клиентов могут изменить одну и ту же запись). Минусы: вы немного разбираетесь в бизнес-логике и хранилище.

Ответ 4

Я вижу, что несколько человек спросили, какой движок вы использовали для запроса. Я настоятельно рекомендую вам использовать MyISAM для следующих действий:

InnoDB - @Sorin Mocanu правильно определил, что вы будете выполнять полное сканирование таблицы независимо от индексов.

MyISAM - всегда поддерживает текущее количество строк.

Наконец, как сказал @justin, убедитесь, что у вас есть соответствующий индекс покрытия:

CREATE INDEX ix_temp ON relations (relation_title, object_title);

Ответ 5

тест  рассчитывать (myprimaryindexcolumn) и сравнить производительность с вашим счетом (*)

Ответ 6

есть точка, в которой вы действительно нуждаетесь больше RAM/CPU/IO. Возможно, вы ударили это для своего оборудования.

Отмечу, что обычно неэффективно использовать индексы (если они не являются ) для запросов, которые попадают более чем на 1-2% от общего количества строк в таблице. Если ваш большой запрос выполняет поиск индексов и поиск по закладкам, это может быть из-за кэшированного плана, который был из всего лишь дневного запроса. Попробуйте добавить в WITH (INDEX = 0), чтобы заставить сканирование таблицы и посмотреть, быстрее ли оно.

возьмите это: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.programming&tid=4631bab4-0104-47aa-b548-e8428073b6e6&cat=&lang=&cr=&sloc=&p=1

Ответ 7

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

AFAIK больше вы ничего не можете сделать.

Ответ 8

Я бы предложил архивировать данные, если нет особых причин хранить их в базе данных, или вы можете разделить данные и запускать запросы отдельно.