2.6.1.10. Оптимізація запитів до бази даних

У даній статті пояснюється логіка оптимізації запитів до бази даних, оскільки більшість програмістів тестують свої програми при невеликій кількості записів у таблицях, а проблеми у власника сайту починаються пізніше, коли він наповнить товарні каталоги.

Наприклад, є запит: 

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
  1. Якщо прибрати з цього запиту умову LIMIT, то він поверне 2907 записів. Саме 2907 разів буде виконаний вкладений в умову SELECT запит. Якщо цю частину запиту винести в окремий запит, то це зменшить навантаження на базу даних в 2907/20=145 разів. Хоча, судячи з назви запиту, можна зробити висновок щодо того, що таким цікавим способом автор програми намагається при кожному заході відвідувача на сайт рахувати статистику товарів, яка може перераховуватися, наприклад, раз на добу або ще краще — при додаванні відгуку до товару і додаватися в окрему колонку таблиці mc_product, що дозволить позбутися цього вкладеного запиту.
  2. В умові WHERE ми бачимо вкладений запит, який виконується в умові IN. Якби автор програми в умові IN вказав не вкладений запит, а просто статичні значення, наприклад IN (121, 1235, 43554), то MySQL використовував би індекс і відпрацював швидко. Але з вкладеними запитами справа йде зовсім інакше — MySQL виконує їх без використання індексів, а точніше так — FIN_IN_SET(p.product_id, '121,1235,43554'). У таких випадках потрібно писати запит окремо, а потім підставляти результат його виконання в умову IN.
Зміст
    (12)