Как уменьшить поиск строк при использовании LIMIT MySQL

ProgramBox

Как уменьшить поиск строк при использовании LIMIT MySQL

Post author:admin
Запись опубликована:13 апреля, 2023
Post category:Вопросы по программированию

#mysql

Вопрос:

У меня есть следующая таблица с включенным индексом id и внешним ключом activityID :

comment (id, activityID, text)

и следующий запрос:

 SELECT <cols> FROM `comment` WHERE `comment`.`activityID` = 1257 ORDER BY `id` DESC LIMIT 20;

По сути, я хочу получить только первые 20 комментариев для этого действия, которое содержит 1165, однако это результат описания:

 id  select_type table   type    possible_keys   key         key_len ref     rows    Extra
1   SIMPLE      comment ref     activityID      activityID  4       const   1165    NULL

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

Мы тестировали этот запрос при высокой нагрузке, когда действие содержит 200 000 комментариев и запрос занимает более 5 секунд, тогда как при той же загрузке действие с 30 комментариями занимает пару мс.

PS: Если я удалю WHERE предложение, в EXPLAIN говорится, что он будет искать только одну строку (не знаю, так ли это на самом деле):

 id  select_type table   type    possible_keys   key         key_len ref     rows    Extra
1   SIMPLE      comment index   NULL            PRIMARY     4       NULL    1       NULL

Возможно ли каким-либо образом оптимизировать такого рода запросы?

Спасибо.

1. Что произойдет, если вы добавите ORDER BY предложение? ОГРАНИЧЕНИЕ без ORDER BY обычно пахнет кодом, поскольку вы не знаете, какие 20 вы получите.

2. На самом деле запрос, который мы используем, содержит ORDER BY id DESC предложение. Я обновлю.

3. Вероятно, это не имеет большого значения, на самом деле было бы конкретизировано с помощью выбора; SELECT `text` FROM а не * ?

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

Ответ №1:

Упорядочение вызывает медлительность.

Запрос использует activityID индекс, чтобы найти все строки с этим идентификатором. Затем он должен прочитать все 200 000 комментариев и отсортировать их по id , чтобы найти последние 20.

Добавьте составной индекс, чтобы он мог использовать индекс для упорядочивания:

 ALTER TABLE comment ADD INDEX (activityID, id);

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

1. Это не имело никакого значения. Пробовал без какого-либо упорядочения, пробовал упорядочивать по идентификатору с тем индексом, который вы предложили, и EXPLAIN возвращает точно то же самое.

2. Даже после того, как вы удалили старый индекс?

3. Это внешний ключ

4. Все в порядке. Поскольку это префикс этого нового ключа, он все равно будет использоваться в качестве внешнего ключа.

5. Я хочу сказать, что ORDER BY это не имеет никакого значения, я даже пробовал использовать FORCE INDEX ( activityID_id ) , и это то же самое.

Ответ №2:

Используйте смещение

 SELECT <cols> FROM `comment` WHERE `comment`.`activityID` = 1257 ORDER BY `id` DESC LIMIT 0,20;

В предложении limit добавьте 0 в качестве смещения, чтобы получить только первые 20 комментариев

Ответ №3:

Просто добавьте два отдельных индекса на activityID и id . Это тоже должно вам помочь ORDER BY . В оптимизации нет жестких правил, но вам нужно попробовать различные методы.

Сделайте это таким образом:

 ALTER TABLE comment ADD INDEX (id);
ALTER TABLE comment ADD INDEX (activityID);

Я думаю, это поможет.

1. id всегда указывайте индекс по умолчанию. activityID является внешним ключом.

2. Я не знаю, почему вы отклонили мой ответ, вы нигде не написали, что ваш id является первичным ключом в вопросе. Вот почему я написал, чтобы убедиться в этом. В любом случае, если id это первичный ключ, вам все равно нужно создать индекс на activityID . Поскольку внешние ключи автоматически не индексируются в движке MyISAM, а только в InnoDB. Кроме того, это общее эмпирическое правило, согласно которому SQL-запрос будет проходить через все записи, которые являются частью выполненного условия индекса. В случае, если вы хотите еще больше снизить свои результаты, вам демонстративно нужно использовать несколько составных комбинаций клавиш, чтобы добиться меньшего количества строк.