Как ускорить “избранное количество (*)” с “группой” и “где”?
Просто для полноты я хотел бы добавить свое решение. Я закончил это со стандартом ed:
ed -s sometextfile <<< $'-2,$d\nwq'
Удаляет последние 2 строки, используя редактирование на месте (хотя делает использование временного файла в /tmp !!) [/ g2]
8 ответов
Вот несколько вещей, которые я бы попробовал в порядке возрастания сложности:
(проще) - Убедитесь, что у вас правильный индекс покрытия
CREATE INDEX ix_temp ON relations (relation_title, object_title);
Это должен максимизировать производительность с учетом вашей существующей схемы, поскольку (если ваша версия оптимизатора mySQL действительно тупой!) он минимизирует количество операций ввода-вывода, необходимых для удовлетворения вашего запроса (в отличие от того, если индекс находится в обратном порядке, когда весь индекс должен будут просканированы), и он будет охватывать запрос, поэтому вам не придется касаться кластеризованного индекса.
(немного сложнее) - убедитесь, что ваши поля varchar как можно меньше
Одна из проблем производительности с varchar indexes в MySQL заключается в том, что при обработке запроса полный объявленный размер поля будет втянут в RAM. Итак, если у вас есть varchar (256), но вы используете только 4 символа, вы re по-прежнему оплачивает использование 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, чтобы удовлетворить запрос).
Кроме того, если Relationship_title имеет небольшой размер varchar, но заголовок объекта имеет длинный размер, тогда вы потенциально можете только хешировать object_title и создайте индекс на (Relations_title, object_title_hash) .
Обратите внимание, что это решение помогает, только если одно или оба этих поля очень длинные по сравнению с размером хэшей.
Также обратите внимание, что хеширование оказывает интересное влияние на чувствительность к регистру / сортировку, поскольку хэш строчной строки не то же самое, что хэш строчной строки. Поэтому вам нужно убедиться, что вы применили канонизацию к строкам перед их хешированием - другими словами, хешируйте только строчные буквы, если вы находитесь в БД без учета регистра. Вы также можете обрезать пробелы с начала или до конца, в зависимости от того, как ваша БД обрабатывает начальные / конечные пробелы.
Вам нужно убедиться, что вы применили канонизацию к строкам перед их хешированием - другими словами, хешируйте только строчные буквы, если вы работаете в БД без учета регистра. Вы также можете обрезать пробелы с начала или до конца, в зависимости от того, как ваша БД обрабатывает начальные / конечные пробелы. Вам нужно убедиться, что вы применили канонизацию к строкам перед их хешированием - другими словами, хешируйте только строчные буквы, если вы работаете в БД без учета регистра. Вы также можете обрезать пробелы с начала или до конца, в зависимости от того, как ваша БД обрабатывает начальные / конечные пробелы.количество тестов (myprimaryindexcolumn) и сравните производительность с вашим количеством (*)
Если вам нужен размер всей таблицы, вам следует запросить мета таблицы или информационную схему (которые существуют в каждой СУБД, которую я знаю, но я не уверен насчет MySQL). Если ваш запрос избирательный, вы должны убедиться, что для него есть индекс.
AFAIK, больше ничего не поделаешь.
есть момент, когда вам действительно нужно больше RAM / CPU / IO. Возможно, вы столкнулись с этим для своего оборудования.
Замечу, что обычно использование индексов неэффективно (если они не покрытие) для запросов, которые занимают более 1-2% от общего числа строк в таблице. Если ваш большой запрос выполняет поиск по индексу и поиск по закладкам, это может быть из-за кэшированного плана, который был получен только из запроса за день. Попробуйте добавить в WITH (INDEX = 0), чтобы заставить сканировать таблицу и посмотреть, быстрее ли оно.
Первым делом можно попробовать индексировать столбцы в предложении GROUP BY, используя составной индекс. На такой запрос потенциально можно ответить, используя только данные индекса, без необходимости сканировать таблицу. Поскольку записи в индексе сортируются, СУБД не должно выполнять отдельную сортировку как часть групповой обработки. Однако индекс замедлит обновления таблицы, поэтому будьте осторожны, если ваша таблица подвергается сильным обновлениям.
Если вы используете InnoDB для хранения таблиц, строки таблицы будут физически кластеризованы по индексу первичного ключа. Если это (или его начальная часть) совпадет с вашим ключом GROUP BY, это должно ускорить выполнение такого запроса, поскольку связанные записи будут извлечены вместе. Опять же, это позволяет избежать отдельной сортировки.
В общем, растровые индексы были бы другой эффективной альтернативой, но MySQL в настоящее время не поддерживает их, насколько я знаю.
Материализованное представление было бы другим возможным подходом, но опять же, это не поддерживается непосредственно в MySQL. Однако, если вам не требуется, чтобы статистика COUNT была полностью актуальной, вы можете периодически запускать CREATE TABLE ... AS SELECT ... оператор для ручного кэширования результатов. Это немного некрасиво, так как непрозрачно, но может быть приемлемо в вашем случае.
Вы также можете поддерживать таблицу кэша логического уровня, используя триггеры. В этой таблице будет столбец для каждого столбца в вашем предложении GROUP BY со столбцом Count для хранения количества строк для этого конкретного значения ключа группировки. Каждый раз, когда строка добавляется или обновляется в базовой таблице, вставить или увеличить / уменьшить строку счетчика в сводной таблице для этого конкретного ключа группировки. Это может быть лучше, чем подход с поддельным материализованным представлением, поскольку кешированная сводка всегда будет актуальной, а каждое обновление выполняется постепенно и должно иметь меньшее влияние на ресурсы. Однако я думаю, что вам следует остерегаться конфликтов блокировок в таблице кеширования.
Если у вас есть InnoDB, count (*) и любая другая агрегатная функция выполнит сканирование таблицы. Я вижу здесь несколько решений:
- Используйте триггеры и храните агрегаты в отдельной таблице. Плюсы: порядочность. Минусы: медленные обновления
- Использование очередей обработки. Плюсы: быстрые обновления. Минусы: старое состояние может сохраняться до тех пор, пока очередь не будет обработана, поэтому пользователь может почувствовать недостаток целостности.
- Полностью отделите уровень доступа к хранилищу и сохраните агрегаты в отдельной таблице. Уровень хранения будет знать о структуре данных и может применять дельты вместо полного подсчета. Например, если вы предоставляете в нем функцию «addObject», вы будете знать, когда объект был добавлен, и, таким образом, это повлияет на агрегат. Затем вы выполняете только
набор обновленных таблиц count = count + 1. Плюсы: быстрые обновления, целостность (вы можете использовать блокировку, хотя в случае, если несколько клиентов могут изменить одну и ту же запись). Минусы: вы объединяете немного бизнес-логики и хранилища.
Я вижу, что несколько человек спрашивали, какой механизм вы использовали для запроса. Я настоятельно рекомендую вам использовать MyISAM в следующих случаях:
InnoDB - @Sorin Mocanu правильно определил, что вы будете выполнять полное сканирование таблицы независимо от индексов.
MyISAM - всегда поддерживает текущее количество строк.
Наконец, как заявил @justin, убедитесь, что у вас есть правильный индекс покрытия:
CREATE INDEX ix_temp ON relations (relation_title, object_title);
I would suggest to archive data unless there is any specific reason to keep it in the database or you could partition the data and run queries separately.