Инструкция по оптимизации MySQL запросов
В данной статье объясняется логика оптимизации MySQL запросов, так как большинство программистов тестируют свои программы при малом количестве записей в таблицах, а проблемы у владельца сайта начинаются поздней, когда он наполнит товарные каталоги.
К примеру есть запрос:
SELECT
p.product_id,
(SELECT AVG(rating) AS total FROM mc_review r1 WHERE r1.product_id = p.product_id AND r1.STATUS ='1' GROUP BY r1.product_id) AS rating
FROM mc_product p
LEFT JOIN mc_product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN mc_product_to_store p2s ON (p.product_id = p2s.product_id)
WHERE
pd.language_id = '2' AND
p.STATUS = '1' AND
p.date_available <= NOW() AND
p2s.store_id = '0' AND
p.product_id IN (SELECT pt.product_id FROM mc_product_tag pt WHERE pt.language_id = '2' AND LOWER(pt.tag) LIKE '%роксолана%')
ORDER BY rating ASC
LIMIT 0,20
p.product_id,
(SELECT AVG(rating) AS total FROM mc_review r1 WHERE r1.product_id = p.product_id AND r1.STATUS ='1' GROUP BY r1.product_id) AS rating
FROM mc_product p
LEFT JOIN mc_product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN mc_product_to_store p2s ON (p.product_id = p2s.product_id)
WHERE
pd.language_id = '2' AND
p.STATUS = '1' AND
p.date_available <= NOW() AND
p2s.store_id = '0' AND
p.product_id IN (SELECT pt.product_id FROM mc_product_tag pt WHERE pt.language_id = '2' AND LOWER(pt.tag) LIKE '%роксолана%')
ORDER BY rating ASC
LIMIT 0,20
Если запрос выполнить с условием EXPLAIN в начале, то получим схему выполнения запроса:
| Тип выборки | Таблица | Тип | Возможные ключи | Ключ | Длина ключа | Ссылка | Строки | Дополнительная информация |
| PRIMARY | p | ALL | PRIMARY | 2907 | Using where; Using filesort | |||
| PRIMARY | pd | eq_ref | PRIMARY | PRIMARY | 8 | mebelnyc_db.p.product_id,const | 1 | Using where; Using index |
| PRIMARY | p2s | eq_ref | PRIMARY | PRIMARY | 8 | mebelnyc_db.p.product_id,const | 1 | Using where; Using index |
| DEPENDENT SUBQUERY | pt | ALL | 6803 | Using where | ||||
| DEPENDENT SUBQUERY | r1 | ref | product_id | product_id | 4 | mebelnyc_db.p.product_id | 1 | Using where |
- Если убрать из этого запроса условие LIMIT, то он вернет 2907 записей. Именно 2907 раз будет выполнен вложенный в условие SELECT запрос. Если эту часть запроса вынести в отдельный запрос, то это уменьшит нагрузку на базу данных 2907/20=145 раз. Хотя судя по названию запроса можно сделать вывод относительно того, что таким интересным способом автор программы пытается при каждом заходе посетителя на сайт считать статистику товаров, которая может пересчитываться к примеру раз в сутки или еще лучше - при добавлении отзыва к товару и добавляться в отдельную колонку таблицы mc_product. Что позволит избавиться от этого вложенного запроса.
- В условии WHERE мы видим вложенный запрос, который выполняется в условии IN. Если бы автор программы в условии IN укзал не вложенный запрос, а просто статические значения, непример IN (121, 1235, 43554), то MySQL использовал бы индекс и отработал быстро. Но с вложенными запросами дело обстоит совсем по другому - MySQL выполняет их без использования индексов, а точней так FIN_IN_SET(p.product_id, '121,1235,43554'). В таких случаях нужно писать запрос отдельно, а потом подставлять результат его выполнения в условие IN.
Написал sorio2009 06.03.2017 в 08:05 
Ответить
Ответить
>> Так каков совет? Не убирать LIMIT? И ежу понятно.
Суть в том, что и с условием LIMIT jyb выполнятся 2907 раз и без него тоже.
>> если использовать в "основном" запросе IN (SELECT ..) - то да
Как раз в условии where он будет выполнен один раз, но вот индексы использоваться не будут. Так как MySQL использует в таких ситуациях вместо IN (1,23) FIND_IN_SET('1,2,3')
>> Если это поле используется для сортировки товаров либо вывода его в определенных блоках по особым критериям (например, "популярные"), а заказчик рвет свои волосы с вопросом "ну почемуууу комментариев 10, а показывает 8???" и объяснить ему, что нужно ждать сутки для обновления данных - вот это задачка для программиста.
При добавлении комментария сразу обновляете статистику. Лучше использовать небольшие просчеты на более редких UPDATE/INSERT, чем сложные расчеты для отображения данных каждому посетителю.
Суть в том, что и с условием LIMIT jyb выполнятся 2907 раз и без него тоже.
>> если использовать в "основном" запросе IN (SELECT ..) - то да
Как раз в условии where он будет выполнен один раз, но вот индексы использоваться не будут. Так как MySQL использует в таких ситуациях вместо IN (1,23) FIND_IN_SET('1,2,3')
>> Если это поле используется для сортировки товаров либо вывода его в определенных блоках по особым критериям (например, "популярные"), а заказчик рвет свои волосы с вопросом "ну почемуууу комментариев 10, а показывает 8???" и объяснить ему, что нужно ждать сутки для обновления данных - вот это задачка для программиста.
При добавлении комментария сразу обновляете статистику. Лучше использовать небольшие просчеты на более редких UPDATE/INSERT, чем сложные расчеты для отображения данных каждому посетителю.
Написал Илья 06.03.2017 в 16:27 Ответить
Ответить
Добавить комментарий
Другие полезные статьи:
- Сайт не может подключиться к базе данных
- Импорт базы данных
- Экспорт базы данных
- Неправильная кодировка при выводе данных из базы
- Инструкция по оптимизации MySQL запросов
- Ошибка в phpmyadmin: "В таблице отсутствует уникальный столбец"
- Оптимизация базы данных. Часть 1
- Безопасное подключение к БД
- Как импортировать БД размером более 300 Мб
Так каков совет? Не убирать LIMIT? И ежу понятно.
>> Именно 2907 раз будет выполнен вложенный в условие SELECT запрос.
.. если использовать в "основном" запросе IN (SELECT ..) - то да.
>> Хотя судя по названию запроса можно сделать вывод относительно того, что таким интересным способом автор программы пытается при каждом заходе посетителя на сайт считать статистику товаров, которая может пересчитываться к примеру раз в сутки или еще лучше - при добавлении отзыва к товару и добавляться в отдельную колонку таблицы mc_product. Что позволит избавиться от этого вложенного запроса.
Если это поле используется для сортировки товаров либо вывода его в определенных блоках по особым критериям (например, "популярные"), а заказчик рвет свои волосы с вопросом "ну почемуууу комментариев 10, а показывает 8???" и объяснить ему, что нужно ждать сутки для обновления данных - вот это задачка для программиста.
В идеале, создавать отдельный не кэшируемый метод получения количества отзывов и вызывать его только по требованию конкретного контекста, а в запрос подключать только при наличия критерия сортировки и только для запросов, использующих индекс, что отчасти уже сказано вами в данном примере.