C, PHP, VB, .NET

Нека разгледаме следните примерни две таблици:

CREATE TABLE users(
   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(255) NOT NULL UNIQUE,
   password VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders(
   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   count INT UNSIGNED NOT NULL,
   user_id INT UNSIGNED,
   FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;

Вкарайте примерни данни и разгледайте резултата от следните заявки:

SELECT orders.count
FROM orders
WHERE orders.user_id IN(
    SELECT users.id
    FROM users
    WHERE users.username = "petar"
);

SELECT orders.count
FROM orders
WHERE EXISTS(
    SELECT *
    FROM users
    WHERE users.username="petar"
      AND users.id = orders.user_id
);

Очаквано двете заявки дават един и същи изход. Разликата в изпълнението им обаче е съществена. EXISTS търси просто за наличие на ред, докато IN проверява стойностите. Реално условието (users.id = orders.user_id) присъства и в двете заявки, но във втората е зададено явно като ограничаващо условие във вложената заявка, докато в първата е свързващо условие между двете таблици.

• При използването на оператор IN първо ще се вземе стойност от външната заявка и след това ще се провери спрямо върнатите стойности от вложената. Вложената ще върне като резултат колона с уникални (distinct) и индексирани стойности. Естествено този резултат ще бъде кеширан (т.е. ще бъде преизползван в бъдеще – нещо, с което в миналото е имало проблеми) и ще бъде преизползван за следващите проверки със стойности от основната заявка;
• При използването на оператор EXISTS винаги ще се прави „full table scan“ на таблица „orders“. От това пълно нейно прелистване ще се изведат само тези редове на които условието във вложената заявка е изпълнено.

Кое от двете е по-добро? В общи линии можем да твърдим, че заявките написани с EXISTS в повечето случаи ще са по-бързи, а понякога значително по-бързи. При тях отпада нуждата от правене на „distinct“ и индексиране на резултата от вложената заявка. Практически ние директно сме указали на MySQL, че не желаем всички редове от вложената заявка, а явно указваме ограничението, т.е. един вид „помагаме на оптимизатора“.

Техниката използвана с EXISTS ще бъде много по-добър избор тогава, когато таблица „orders“ е с малко записи, а „users“ е с много и особено когато е с повтарящи се записи по колоната за сравнение. Така въпросният „full table scan“ няма да отнеме много време, а вложената заявка ще бъде много по-ефективна. Обратно – ако таблицата от вложената заявка е малка, а таблицата от главната заявка е голяма, то ефектът ще бъде в полза на оператор IN, но с малко. Така, че в общия случай заявката преработена с EXISTS ще бъде по-добрия вариант от IN. Естествено проверката на това в реална ситуация е силно препоръчителна и няма да навреди с нищо. Използвайте тази техника, която ви даде по-добри реални резултати в конкретния случай.

Много важно е да отбележим, че правилното индексиране е от основно значение за бързината на изпълнение на заявките и в двата случая. В горния пример users.id е PK и има индекс по подразбиране, но за orders.user_id е задължително да бъде създаден такъв. При изпълняване на заявки, в които аналогът на users.id не е PK е задължително да се направи индекс и по нея.

Реални проблеми при избора между IN и EXISTS идват тогава, когато в една от колоните за сравнение (или и при двете) има възможност за стойности „NULL“ и ние трябва да ги отчитаме. За повече информация прочетете раздела в документацията на MySQL.