Советы по улучшению этого медленного запроса MySQL?

Question

Я использую запрос, который обычно выполняется менее чем за секунду, но иногда занимает от 10 до 40 секунд. Я на самом деле не совсем понимаю, как работает подзапрос, я просто знаю, как он работает, потому что он дает мне 15 строк для каждого faverprofileid.

Я регистрирую медленные запросы, и это говорит о том, что было проверено 5823244 строк, что странно, потому что в любой из задействованных таблиц нет места близко к такому количеству строк (таблица избранного имеет максимум 50000 строк).

Кто-нибудь может предложить мне несколько указателей? Это проблема с подзапросом и необходимость использования файловой сортировки?

РЕДАКТИРОВАТЬ: Запуск объяснения показывает, что таблица пользователей не использует индекс (даже если идентификатор является первичным ключом). Под дополнительным он говорит: использование временного; Использование сортировки файлов.

SELECT F.id,F.created,U.username,U.fullname,U.id,I.*   
FROM favorites AS F  
INNER JOIN users AS U ON F.faver_profile_id = U.id  
INNER JOIN items AS I ON F.notice_id = I.id  
WHERE faver_profile_id IN (360,379,95,315,278,1)  
AND F.removed = 0  
AND I.removed = 0   
AND F.collection_id is null   
AND I.nudity = 0  
AND (SELECT COUNT(*) FROM favorites WHERE faver_profile_id = F.faver_profile_id  
AND created > F.created AND removed = 0 AND collection_id is null) < 15 
ORDER BY F.faver_profile_id, F.created DESC;

Answer 1

Я думаю с GROUP BY а также HAVING это должно быть быстрее. Это то, что вы хотите?

SELECT F.id,F.created,U.username,U.fullname,U.id, I.field1, I.field2, count(*) as CNT
FROM favorites AS F  
INNER JOIN users AS U ON F.faver_profile_id = U.id  
INNER JOIN items AS I ON F.notice_id = I.id  
WHERE faver_profile_id IN (360,379,95,315,278,1)  
AND F.removed = 0  
AND I.removed = 0   
AND F.collection_id is null   
AND I.nudity = 0  
GROUP BY F.id,F.created,U.username,U.fullname,U.id,I.field1, I.field2
HAVING CNT < 15
ORDER BY F.faver_profile_id, F.created DESC;

Не знаю, из каких полей items вам нужно, поэтому я поставил заполнители.

Answer 2

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

• Убедитесь, что вы запустили ANALYZE TABLE на своих трех столах.
• Прочтите, как избежать сканирования таблиц, а затем определить и создать недостающие индексы.
• Повторите анализ и повторите ваши запросы
  • количество проверенных строк должно резко упасть
  • если нет, опубликуйте полный план объяснения
• используйте подсказки запроса, чтобы принудительно использовать индексы (чтобы увидеть имена индексов для таблицы, используйте SHOW INDEX):

SELECT F.id,F.created,U.username,U.fullname,U.id,I.*
FROM favorites AS FFORCE INDEX (faver_profile_id_key)
INNER JOIN users AS UFORCE INDEX FOR JOIN (PRIMARY)ON F.faver_profile_id = U.id
INNER JOIN items AS IFORCE INDEX FOR JOIN (PRIMARY)ON F.notice_id = I.id
WHERE faver_profile_id IN (360,379,95,315,278,1)
AND F.removed = 0
AND I.removed = 0
AND F.collection_id is null
AND I.nudity = 0
AND (SELECT COUNT(*) FROM favoritesFORCE INDEX (faver_profile_id_key)WHERE faver_profile_id = F.faver_profile_id
AND created > F.created AND removed = 0 AND collection_id is null) < 15
ORDER BY F.faver_profile_id, F.created DESC;

Вы также можете изменить свой запрос, чтобы использовать GROUP BY faver_profile_id / HAVING count > 15 вместо вложенного SELECT COUNT(*) подзапрос, как предложено vartec, Производительность как вашего оригинала, так и vartec запрос должен быть сопоставим, если оба оптимизированы должным образом, например, с использованием подсказок (ваш запрос будет использовать поиск по вложенным индексам, тогда как vartec В запросе будет использована стратегия на основе хеша.)

Answer 3

Я предлагаю вам использовать Mysql Explain Query, чтобы увидеть, как ваш сервер MySQL обрабатывает запрос. Моя ставка в том, что ваши показатели не оптимальны, но объяснение должно быть намного лучше, чем моя ставка.

Answer 4

Вы можете сделать цикл для каждого идентификатора и использовать лимит вместо подзапроса count(*):

foreach $id in [123,456,789]:
    SELECT
     F.id,
     F.created,
     U.username,
     U.fullname,
     U.id,
     I.*
    FROM
     favorites AS F INNER JOIN
     users AS U ON F.faver_profile_id = U.id INNER JOIN
     items AS I ON F.notice_id = I.id
    WHERE
     F.faver_profile_id = {$id} AND
     I.removed = 0 AND
     I.nudity = 0 AND
     F.removed = 0 AND
     F.collection_id is null
    ORDER BY
     F.faver_profile_id,
     F.created DESC
    LIMIT
     15;

Answer 5

Я предполагаю, что результат этого запроса будет отображаться в виде постраничного списка. В этом случае, возможно, вы могли бы подумать о том, чтобы сделать более простой "несвязанный запрос" и выполнить второй запрос для каждой строки, чтобы прочитать только 15, 20 или 30 показанных элементов. Не было ли присоединение тяжелой операцией? Это упростит запрос и не станет медленнее, когда растут объединенные таблицы.

Скажите, если я не прав, пожалуйста.