C, PHP, VB, .NET

Въпросът поставен в заглавието на статията е много често разискван и около него се водят спорове. По принцип има една тенденция програмистите категорично да избягват вложените select заявки, защото още от миналото има един мит, че те винаги се изпълняват по-бавно. Този мит се дължи главно на грешки в СУБД, които не са използвали правилно индексите при вложените заявки. Днес това отдавна вече (почти) не се среща, т.е. можем да очакваме вложените заявки да вървят достатъчно добре. Така въпросът „join или вложен select“ отново стои на дневен ред.

Ще демонстрираме с един пример. Нека имаме следната база от данни:

CREATE TABLE clients(
   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255) NOT NULL) ENGINE=InnoDB;

CREATE TABLE products(
   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255) NOT NULL) ENGINE=InnoDB;

CREATE TABLE orders(
   client_id INT UNSIGNED NOT NULL,
   product_id INT UNSIGNED NOT NULL,
   quantity INT UNSIGNED NOT NULL,
   PRIMARY KEY(client_id,product_id),
   FOREIGN KEY (client_id) REFERENCES clients(id),
   FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

INSERT INTO clients(id, name)
VALUES (NULL, "Ivan"),
       (NULL, "Petar"),
       (NULL, "Maria"),
       (NULL, "Philip");

INSERT INTO products(id, name)
VALUES (NULL, "GSM"),
       (NULL, "Bike"),
       (NULL, "Icecream");

INSERT INTO orders(client_id, product_id, quantity)
VALUES (1, 1, 2),
       (1, 3, 4),
       (2, 1, 1),
       (2, 2, 1),
       (2, 3, 2),
       (3, 2, 2),
       (4, 3, 1);

Поставяме си да решим следната задача: изведете id-тата на хората, които са поръчали продукти 1 или 2. Впечатление прави условието „или“, както също така, че връзката между clients и products е от тип M:M.

Първо ще дадем естественото решение с вложен select:

SELECT clients.name
FROM clients
WHERE clients.id IN(
      SELECT orders.client_id
      FROM orders
      WHERE product_id IN (1,2)
);

Очаквано отговорът на СУБД е:

+-------+
| name  |
+-------+
| Ivan  |
| Petar |
| Maria |
+-------+

При използването на решение с join положението не е толкова просто. Следният пример е грешен:

SELECT clients.name
FROM clients JOIN orders ON clients.id=orders.client_id
WHERE orders.product_id IN (1,2);

Ще видим, че в резултатът от него има повторения:

+-------+
| name  |
+-------+
| Ivan  |
| Petar |
| Petar |
| Maria |
+-------+

За да се избавим от повторенията ще е нужно да използваме ключова дума DISTINCT:

SELECT DISTINCT clients.name
FROM clients JOIN orders ON clients.id=orders.client_id
WHERE orders.product_id IN (1,2);

Така заявката вече ще върне правилен резултат.

Коя от двете заявки беше по-добра? Отговорът еднозначно е, че това е тази, която е с вложен select! При заявката с join първо се генерира таблицата с повторенията, а чак след това се премахнаха дублиращите се. При вложен select дублирания въобще нямаше. Ако направите по-сериозен тест с огромно количество данни с много повторения и създадени индекси по съответните колони, то ще видите, че заявката с вложен select ще се изпълни видимо по-бързо.

Когато използвате SELECT DISTINCT … WHERE… заявки (или GROUP BY), то СУБД първо ще създаде временна свързваща таблица и чак тогава ще ги агрегира. При вложен select това не се случва, съответно не се правят безсмислени операции.

Ето още един начин да направим същата заявка, но вместо IN ще използваме EXISTS:

SELECT clients.name
FROM clients
WHERE EXISTS(
      SELECT * FROM orders
      WHERE orders.product_id IN (1,2) AND orders.client_id=clients.id
);

Дали IN или EXISTS е по-добър подход ще разгледаме по-късно в отделна статия. При всички положения обаче вложения select би трябвало да дава по-добри резултати от използването на join като негова алтернатива.

Има обаче и други видове вложен select, при които положението е точно обратното. Това са т.нар. „корелационни select“ заявки – когато вложената заявка разчита на данни, подадени от външната заявка. В този случай почти винаги може да очаквате много по-бързо изпълнение на JOIN спрямо вложения SELECT. Например ако искаме да изведем имената на потребителите и до тях да долепим имената на всички продукти, които са поръчали, вариантите ни са два:

1. С корелационен SELECT:

SELECT clients.name, products.name
FROM clients JOIN products
     ON clients.id IN(
           SELECT orders.client_id
           FROM orders
           WHERE orders.product_id = products.id
        )

2. С JOIN:

SELECT clients.name, products.name
FROM clients JOIN orders 
             ON clients.id = orders.client_id
     JOIN products
             ON products.id = orders.product_id;

Кое тук второто ще е по-добро? На теория би трябвало да отговорим, че това ще бъде JOIN. При корелационния SELECT вложената заявка се изпълнява толкова пъти, колкото реда е върнала външната заявка. И проблемът не е в бързината на тези заявки или че са много (самите те няма да направят повече операции, отколкото ще бъдат направени при JOIN), а в това, че за всяка една от тях ще се съставя нов query plan (на този етап приемете това като процес на компилация). При варианта с JOIN се съставя един единствен query plan.

Тук е важно да отбележим, че в по-стари версии на MySQL е имало много сериозен проблем със съставянето на query plan на вложени заявки, като в редица случаи съвсем обикновен вложен select е бил интерпретиран като корелационен. С по-прости думи можем да кажем, че стандартния вложен select изисква изпълнение „отвътре навън“ (първо се изпълнява вложената заявка, а резултата от нея се предава на външната), а корелационния вложен select е „отвън навътре“ (първо се взимат записите от таблицата на външната заявка и след това един по един се предават към вложената). Тъй като вложените заявки имат смисъл на „ограничаващо условие“, т.е. чрез тях да бъдат пропускани редове от външната заявка, можем съвсем логично да приемем, че интерпретирането на обикновен вложен select като корелационен винаги дава много по-лоши резултати откъм бързодействие.

А защо все пак дискусиите продължават и все още няма „златно правило“? Причината е, че все пак разчитаме на СУБД да направи оптимизация и да състави въпросния query plan. Затова като обобщение на тази статия трябва да се каже, че обикновено практическата ситуация определя избора на единия или другия подход. Направете тестове с едната или с другата заявка, вижте коя се интерпретира по-добре и коя дава оптимизация, от където изберете именно нея. Една добра насока е, че join би трябвало да даде много по-добри резултати спрямо корелационен select с голяма външна таблица и обратно – join ще дава малко забавяне спрямо корелационен select с малка външна таблица. Относно обикновения вложен select – той би следвало да е за предпочитане спрямо join, но не и в случаите, в които системата погрешно го интерпретира като корелационен. При всички положения обаче join е един „безопасен“ избор (дори да е по-бавен, няма да е драстично, както за съжаление понякога се получава в обратния случай). Затова и повечето програмисти го предпочитат.