Инструкция по оптимизации MySQL запросов

Question

Accepted Answer

Более детальную информацию можно найти в нашей вики: https://wiki.ukraine.com.ua/hosting:mysql:query-optimization.

В данной статье объясняется логика оптимизации 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

Если запрос выполнить с условием 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.

Инструкция по оптимизации MySQL запросов

Другие полезные статьи: