Разница в производительности запроса
Во-первых, наша среда PHP + MYSQL.
У нас есть таблицы статей, это таблицы, используемые для сохранения текстовых статей. Есть около 15000 записей.
У нас есть проблема производительности по запросу:
SELECT article_id, article_title, article_status,
article_date_time, article_publish_date
FROM articles
WHERE article_status IN ('approved')
AND (article_publish_date <= now())
AND ((article_expiry_date = '0000-00-00') OR
(article_expiry_date <> '0000-00-00'
AND article_expiry_date >= now()))
AND articles_id IN (1, 2, 3... a list of about 9,000 possible ID's)
GROUP BY article_id
ORDER BY article_date_time DESC LIMIT 0,5;
На нашем тестовом сайте (сервер БД и веб-сервер находятся на одном компьютере), если я запускаю запрос в первый раз, время выполнения запроса составляет около 30 секунд.
На тестовом сайте, если я просто обновлю страницу, чтобы выполнить запрос во второй раз, время выполнения запроса составит около 0,2 секунды.
Если обновление будет продолжаться, время выполнения все равно будет около 0,2 секунды. Но если я остановлюсь на 15 минут, время выполнения снова составит 30 секунд, а затем 0,2 секунды …
Итак, вот ВОПРОС 1 : Что делает огромную разницу между первым исполнением и вторым исполнением? Cache? Если это так, то как это изменило ситуацию?
Все тот же запрос. На нашем действующем сайте (все еще сервер БД и веб-сервер находятся на одной машине) время выполнения запроса составляет около 3 секунд. Но время будет около 3 секунд, независимо от того, сколько раз вы выполняете запрос.
Тестовая база данных является резервной копией действующей базы данных, поэтому разница в базе данных не должна давать такого другого результата.
Итак, вот ВОПРОС 2: Почему время выполнения для живого сайта не 30 секунд и не 0,2 секунды? И почему бы не измениться при втором исполнении?
Может кто-нибудь, пожалуйста, помогите?
Решение
Вместо того, чтобы отвечать на вопрос, почему он работает с разными временами на двух серверах, я думаю, что важно сначала попытаться оптимизировать ваш запрос.
Прежде всего вам нужно избегать большого набора литералов, которые вы используете с IN оператор.
Я бы предложил добавить еще одно поле
указать результат этого in операция:
ALTER TABLE articles ADD (
flag int
);
UPDATE articles
SET article_flag =
CASE
WHEN article_id IN (1, 2, 3... a list of about 9,000 possible IDs) THEN 1
ELSE 0
END;
COMMIT;
И не забудьте поставить индекс на article_date_time если еще не сделано:
CREATE INDEX idx_article_date_time ON articles(article_date_time);
Тогда используйте этот запрос без group by и на одно лишнее условие меньше:
SELECT article_id, article_title, article_status,
article_date_time, article_publish_date
FROM articles
WHERE article_status = 'approved'
article_flag = 1
AND article_publish_date <= now()
AND ( article_expiry_date = '0000-00-00'
OR article_expiry_date >= now()
)
ORDER BY article_date_time DESC LIMIT 0,5;
Я предвижу улучшение производительности, если вы сделаете все это.
Другие решения
Причина быстрого повторного ответа заключается в том, что MySQL может кэшировать запросы; запрос не просто выполняется быстрее во второй раз: он вообще не выполняется, так как система обнаруживает, что увидела запрос и все еще имеет результаты. Чтобы отключить кеширование запросов для тестирования, вы можете установить размер кеша запросов сервера на ноль. Чтобы отключить кэширование для отдельных запросов, добавьте SQL_NO_CACHE после SELECT,
Список из 9000 идентификаторов является очевидной причиной низкой производительности; но откуда они взялись? Если они жестко запрограммированы в запросе, вы должны заранее знать, что они из себя представляют. В этом случае более быстрое решение состоит в том, чтобы изменить схему таблицы и добавить столбец, в котором указывается, соответствует ли идентификатор. Но правильное решение действительно зависит от того, как идентификаторы определены.
Редактировать: Поскольку список идентификаторов статей происходит из сложного запроса, вы должны объединить два запроса в два. Самый простой способ — встроить запрос article-id как подзапрос:
SELECT article_id, article_title, article_status, ...
FROM articles
WHERE ...
AND article_id IN (SELECT article_id from <subquery conditions>)
Но сервер сможет лучше оптимизировать ваш запрос, если вы попытаетесь переписать его как реальное соединение.
Другие (незначительные) проблемы: Следующий пункт является излишним.
((article_expiry_date = '0000-00-00') OR
(article_expiry_date <> '0000-00-00'
AND article_expiry_date >= now()))
Если первая половина ложна, второе сравнение будет всегда быть правдой; следовательно, вы должны упростить его до
(article_expiry_date = '0000-00-00' OR article_expiry_date >= now())
Чтобы узнать, на что ваша программа тратит свое время, отправьте вручную запрос на сервер, которому предшествует EXPLAIN, и изучить результаты:
EXPLAIN SELECT article_id, article_title, article_status,
...
Проблема почти наверняка In (... list of 9000 Id ....)
При первом выполнении запроса процессор SQL-запросов должен прочитать данные с диска. В процессе это, вероятно, хранит данные в кэш-памяти. Во второй раз данные все еще находятся в кэш-памяти, так что это все доступ к оперативной памяти. In пункт (потому что он преобразуется в 9000 повторений
Or articles_id = id1 Or articles_id = id2 Or articles_id = id3 ... занимает много времени. (хотя я не совсем уверен, почему …
Что я бы порекомендовал (как минимум, в качестве теста для подтверждения этого), чтобы вы поместили эти 9000 идентификаторов в таблицу и переписали запрос, чтобы просто объединить эту таблицу. Затем, если этот тест показывает, что проблема именно в этом, перепишите запрос.
Я не знаю MySQL / Php достаточно, чтобы знать, возможно ли это там. Но на SQL-сервере с .Net, например, вы можете в коде клиента (ADO.Net) создать коллекцию целочисленных или строковых значений Id и передать эту коллекцию в базу данных в виде отдельного параметра SQL или хранимой процедуры, где он будет использоваться, как если бы это была таблица (например, вы можете сослаться на t в операторе соединения SQL). Возможно, вы захотите изучить MySQL и посмотреть, возможно ли это или что-то подобное в PHP / MySQL. В противном случае рассмотрите возможность создания разделенного списка этих идентификаторов 9000 + и передачи его в хранимую процедуру MySQL, а затем, внутри SP, проанализируйте его, чтобы преобразовать в таблицу, к которой вы можете присоединиться.