Как избежать SQL запросов внутри циклов / Хабр

В этой статье я попробую показать как избежать одного из признаков «загнивающего» кода, а именно SQL запросы внутри циклов. Примеры будут на простом PHP без использования ООП. Это значительно облегчит понимание. Прочтение займет от 5 до 10 минут.

Почему стоит избегать запросов внутри циклов?

Все очень просто. Каждый запрос это потеря времени на «подготовительные» и «заключительные» операции по обращению к БД. Предположим к каждому товару есть отдельная таблица для картинок, скидок и каких-нибудь опций(например выбор цвета платья). То есть для получения информации о каждом товаре, вам нужно будет выполнить 3 запроса. Т.е. 300 запросов на вывод 100 товаров + запрос на получение списка товаров. Итого 301 запрос на 100 товаров. И как результат, существенное снижение производительности вашего приложения. Этого можно и стоит избегать.

Ниже кусочек кода из OpenCart 3:

public function getProducts($data = array()) {

//.....
		$query = $this->db->query($sql);

		foreach ($query->rows as $result) {
			// for never get one more time with same product id
			if(!isset($product_data[$result['product_id']])){
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}	
		}

		return $product_data;
	}
/*
$this->getProduct($result['product_id']) Делает выборку 1 товара из базы. Т.е. метод getProducts вытаскивает product_id-ы из базы, и потом в цикле "тащит" полные данные на каждого из товаров. Очень классное решение (сарказм).
*/

Задача для оптимизации

Рассмотрим задачу с двумя таблицами — Товары и Цены. У одного товара может быть несколько цен разного типа. Результат нужно получить в виде массива:

print_r($products)
/*
Array
(
    [1] => Array
        (
            [product_id] => 1
            [name] => Товар 1
            [prices] => Array
                (
                    [0] => Array
                        (
                            [price_id] => 45
                            [product_id] => 1
                            [type] => 3
                            [price] => 95.00
                        )

                    [1] => Array
                        (
                            [price_id] => 55
                            [product_id] => 1
                            [type] => 1
                            [price] => 90.00
                        )

                    [2] => Array
                        (
                            [price_id] => 58
                            [product_id] => 1
                            [type] => 2
                            [price] => 90.00
                        )

                )

        )

)
*/

Таблица товаров:

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `product` (`product_id`, `name`) VALUES (1, 'Товар 1');
INSERT INTO `product` (`product_id`, `name`) VALUES (2, 'Товар 2');
INSERT INTO `product` (`product_id`, `name`) VALUES (3, 'Товар 3');
INSERT INTO `product` (`product_id`, `name`) VALUES (4, 'Товар 4');
INSERT INTO `product` (`product_id`, `name`) VALUES (5, 'Товар 5');
INSERT INTO `product` (`product_id`, `name`) VALUES (6, 'Товар 6');
INSERT INTO `product` (`product_id`, `name`) VALUES (7, 'Товар 7');
INSERT INTO `product` (`product_id`, `name`) VALUES (8, 'Товар 8');
INSERT INTO `product` (`product_id`, `name`) VALUES (9, 'Товар 9');
INSERT INTO `product` (`product_id`, `name`) VALUES (10, 'Товар 10');

Таблица цен:

CREATE TABLE `product_price` (
  `price_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `type` enum('1','2','3') NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`price_id`)
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8;
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (55, 1, '1', 90.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (58, 1, '2', 90.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (45, 1, '3', 95.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (56, 2, '1', 90.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (49, 2, '2', 45.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (42, 2, '3', 96.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (57, 3, '1', 23.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (47, 3, '2', 53.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (51, 3, '3', 12.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (43, 4, '1', 89.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (46, 4, '2', 4.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (52, 4, '3', 15.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (54, 5, '1', 43.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (41, 5, '2', 44.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (48, 5, '3', 34.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (44, 6, '1', 26.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (59, 6, '2', 26.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (60, 6, '3', 26.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (53, 7, '1', 87.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (61, 7, '2', 87.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (50, 7, '3', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (62, 8, '1', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (63, 8, '2', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (64, 8, '3', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (65, 9, '1', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (66, 9, '2', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (67, 9, '3', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (68, 10, '1', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (69, 10, '2', 77.00);
INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (70, 10, '3', 77.00);

Вариант с запросами внутри циклов:

<?php
define('DB_HOST' , 'localhost');
define('DB_USER' , 'mysqluser');
define('DB_PASSWORD' , 'password');
define('DB_NAME' , 'habr');

$conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die('Can not connect to db');
mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn));
mysqli_query($conn , 'SET NAMES utf8');

//получаем все товары - 7 шт.
$sql = 'select * from product';
$product_result = mysqli_query($conn , $sql);

//Объявляем массив куда будем собирать все товары
$products = array();

while ($product = mysqli_fetch_assoc($product_result)) {
	//Получаем список цен у текущего товара. 
	$prices = array();
	$sql = 'select * from product_price WHERE product_id = ' . (int) $product['product_id'];
	$price_result = mysqli_query($conn , $sql);

	while ($price = mysqli_fetch_assoc($price_result)) {
		$prices[] = $price;
	}
	//Полученный список цены помещаем в товар
	$product['prices'] = $prices;
	//Товар помещаем в массив товаров
	$products[] = $product;
}

print_r($products);

if ($conn) {
	mysqli_close($conn);
}

Итак, чтобы вывести 7 товаров мы сделали 1+7 запросов. Как можно оптимизировать:

Выбираем товары из таблицы. Помещаем их в массив, но в качестве ключей будем использовать product_id;
Выбираем все цены для найденных товаров;
Найденные цены «распихиваем» по товарам.

Тоже самое, но на понятном языке PHP:

<?php
define('DB_HOST' , 'localhost');
define('DB_USER' , 'mysqluser');
define('DB_PASSWORD' , 'password');
define('DB_NAME' , 'habr');

$conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die('Can not connect to db');
mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn));
mysqli_query($conn , 'SET NAMES utf8');

//получаем 3 товара
$sql = 'select * from product LIMIT 3';
$product_result = mysqli_query($conn , $sql);

//Объявляем массив куда будем собирать все товары
$products = array();

while ($product = mysqli_fetch_assoc($product_result)) {
	//Инициализируем цены
	$product['prices'] = array();
	//Создаем новый продукт. Ключ уже указываем в качестве product_id
	$products[(int) $product['product_id']] = $product;
}

//Проверяем что есть товары, для которых надо получить цены.

if (count($products) > 0 ) {

	//Получаем список product_id-ов для которых теперь нам надо вытащить цены.
	$product_ids = array_keys($products);

	//составляем запрос на выборку
	$sql = 'select * from product_price where product_id in (' . implode(',' , $product_ids ). ')';
	$prices_result  = mysqli_query($conn , $sql) or die(mysqli_error($conn));
	while ($price = mysqli_fetch_assoc($prices_result)) {
		$products[(int) $price['product_id']]['prices'][] = $price;
	}
}

print_r($products);

if ($conn) {
	mysqli_close($conn);
}

Так теперь, для выборки любого количества товаров нужно будет выполнить всего 2 запроса. Работа такого скрипта будет иметь ощутимую разницу на выборках большого количества товаров. Данный подход можно обобщить. Например, избегайте в циклах обращение в внешним ресурсам(файловой системе, memcache, redis), если такое возможно. И помните о принципе разумности в принимаемых решениях.