Рівень необхідних знань для розуміння стати: загальне уявлення про бази даних та SQL, невеликий практичний досвід роботи із СУБД.
Ймовірно, найважливіше, чого тільки можна навчитися для написання ефективних SQL-запитів – це індексація. Однак на другому місці, з дуже невеликим відставанням, йде знання про те, що безліч SQL-клієнтів вимагають від бази даних виконання маси "непотрібної, але обов'язкової роботи" . Повторюйте за мною:
Непотрібна, але обов'язкова робота
Що ж таке "непотрібна, але обов'язкова робота"? Як підказує нам, Капітан Очевидність, вона:
Непотрібна
Нехай нашому клієнтському додатку необхідні такі дані:
Нічого незвичайного. Ми працюємо з базою даних фільмів (наприклад, базою даних Sakila ) і хочемо відображати користувачам назву та рейтинг усіх фільмів. Потрібний результат може дати наступний запит:
SELECT title, rating
FROM film
Однак наш додаток (або наш ORM), замість цього, виконує такий запит:
SELECT *
FROM film
Що ми отримуємо в результаті? Вгадайте. Ми отримуємо масу марної інформації:
Справа тут навіть видніється якийсь складний JSON, що завантажується:
з диску
у кеш
по дротах
на згадку про клієнта
і, нарешті, викидається [через непотрібність]
Так, ми викидаємо більшу частину цієї інформації. Усі виконані для отримання цієї інформації дії виявабося абсолютно марними. Правда? Щоправда.
Обов'язкова
А тепер – найгірше. Хоча оптимізатори зараз вміють чимало, ці дії є обов'язковими для бази даних. База даних ніяк не може дізнатися, що клієнтському додатку 95% цих даних не потрібно. І це лише найпростіший приклад. Уявіть собі з'єднання кількох таблиць... Ну і що, скажете ви, бази даних же швидкі? Дозвольте просвітити вас про деякі речі, про які ви, мабуть, не замислювалися. Звичайно, час виконання окремого запиту ні на що не впливає. Гаразд, він виповнився в півтора рази повільніше, але ж ми це переживемо, правда? Заради зручності? Іноді це так. Але якщо жертвувати продуктивністю заради зручності завжди, ці дрібниці почнуть накопичуватися. Йтиметься вже не про продуктивність (швидкість виконання окремих запитів), а про пропускну спроможність (часу відгуку системи) і тоді почнуться неабиякі проблеми, вирішити які не так вже й просто. Тоді ви й втратите масштабованість. Погляньмо на плани виконання, в даному випадку, СУБД Oracle:
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|--------------------------------------------------|0| SELECT STATEMENT ||1000|166K||1| TABLE ACCESS FULL| FILM |1000|166K|--------------------------------------------------
порівняно з:
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|--------------------------------------------------|0| SELECT STATEMENT ||1000|20000||1| TABLE ACCESS FULL| FILM |1000|20000|--------------------------------------------------
При виконанні запиту SELECT * замість SELECT title rating використовується в 8 разів більше пам'яті в базі даних. Нічого несподіваного, правда? Ми знали, що так буде. Але все одно ми погоджуємося з цим для багатьох наших запитів, у яких усі ці дані нам просто не потрібні. Ми створюємо для бази даних непотрібну, але обов'язкову роботу , яка все накопичується та накопичується. Ми використовуємо у 8 разів більше пам'яті, ніж потрібно (множник змінюватиметься, звичайно). А між тим, на всіх інших етапах (дискове введення/виведення, передача даних по мережі, споживання пам'яті клієнтом) проблеми точно ті ж, але я їх пропущу, а погляну, натомість, на...
Використання індексів
Більшість баз даних, на сьогодні, вже гідно оцінабо концепцію покриваючих індексів (covering indexes). Покриваючий індекс – сам собою не є якимось особливим видом індексу. Але він може виявитися "особливим індексом" для конкретного запиту або "випадково", або тому, що так було задумано. Розглянемо наступний запит:
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
Нічого несподіваного щодо його виконання не видно. Це найпростіший запит. Перегляд діапазону за індексом, доступ до таблиці – і готове:
-------------------------------------------------------------------|Id|Operation|Name|Rows|-------------------------------------------------------------------|0| SELECT STATEMENT ||8||1| TABLE ACCESS BY INDEX ROWID| ACTOR |8||*2| INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME |8|-------------------------------------------------------------------
Гарний план, чи не так? Ну, якщо нам правда було потрібно ось це, то ні:
Очевидно, що ми даремно витрачаємо пам'ять тощо. Давайте розглянемо такий запит як альтернативний варіант:
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
Його план такий:
----------------------------------------------------|Id|Operation|Name|Rows|----------------------------------------------------|0| SELECT STATEMENT ||8||*1| INDEX RANGE SCAN| IDX_ACTOR_NAMES |8|----------------------------------------------------
Нам вдалося повністю виключити доступ до таблиці, завдяки наявності індексу, що задовольняє всі потреби нашого запиту... покриває індексу. Чи це важливо? Ще й як! Цей підхід дозволяє прискорити деякі запити на порядок (або сповільнити їх на порядок, коли індекс перестає бути покривальним після зміни). Індексами, що покривають, можна скористатися не завжди. За індекси доводиться платити і додавати занадто багато їх не слід. Але в даному випадку все очевидно. Давайте оцінимо продуктивність:
SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER :=100000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Наихудший вариант запита: перерасход памяти ПЛЮС доступ к таблице
SELECT *
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 1 : '||(SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Улучшенный запит, но все равно с доступом к таблице
SELECT /*+INDEX(actor(last_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 2 : '||(SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Оптимальный запит: покрывающий индекс
SELECT /*+INDEX(actor(last_name, first_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 3 : '||(SYSTIMESTAMP - v_ts));
END;/
В результаті отримуємо:
Оператор 1: +000000000 00:00:02.479000000
Оператор 2 : +000000000 00:00:02.261000000
Оператор 3 : +000000000 00:00:01.857000000
Зверніть увагу, що в таблиці actor всього 4 стовпці, так що різниця в продуктивності між операторами 1 і 2 не така вже й велика, але все ж таки значна. Зауважу також, що я скористався підказками оптимізатора Oracle, щоб оптимізатор вибирав конкретний індекс для запиту. Оператор 3 – безперечний переможець нашого забігу. Його продуктивність набагато краща, адже йдеться про виключно простий запит. Знову ж таки, коли ми пишемо SELECT *, то створюємо для бази даних непотрібну, але обов'язкову роботувона не може оптимізувати. Вона не вибере індекс, що покриває, тому що у нього трохи вище накладні витрати, ніж у обраного їй індексу LAST_NAME, і, крім іншого, їй доводиться все одно звертатися до таблиці для вилучення нікому не потрібного стовпця LAST_UPDATE, наприклад. Але що глибше ми аналізуємо SELECT *, то гірше виявляються справи. Поговоримо про...
SQL-перетворення
Оптимізатори демонструють такі хороші результати тому, що перетворюють SQL-запити ( у своєму недавньому виступі на конференції Voxxed Days у Цюріху я розповідав, як це працює ). Наприклад, існує надзвичайно потужне перетворення "виключення JOIN". Розглянемо наступне допоміжне уявлення, яке нам довелося створити, щоб не поєднувати всі ці таблиці щоразу вручну:
CREATE VIEW v_customer AS
SELECT
c.first_name, c.last_name,
a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)
Це уявлення просто виконує все "...-до-одному" з'єднання між таблицею покупців CUSTOMER і різними таблицями частин їх адресаи. Дякую тобі, нормалізація. Уявіть, що, попрацювавши трохи з цим уявленням, ми до нього звикли і забули про таблиці, що лежать в його основі. І тепер ми виконуємо наступний запит:
SELECT *
FROM v_customer
Ми отримуємо в його результаті вельми значний план:
----------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|----------------------------------------------------------------|0| SELECT STATEMENT ||599|47920|14||*1| HASH JOIN ||599|47920|14||2| TABLE ACCESS FULL | COUNTRY |109|1526|2||*3| HASH JOIN ||599|39534|11||4| TABLE ACCESS FULL | CITY |600|10800|3||*5| HASH JOIN ||599|28752|8||6| TABLE ACCESS FULL| CUSTOMER |599|11381|4||7| TABLE ACCESS FULL| ADDRESS |603|17487|3|----------------------------------------------------------------
Ну звичайно. База даних виконує всі ці з'єднання та повні перегляди таблиць, оскільки саме це ми і наказали їй зробити – витягти всі ці дані. Тепер, знову ж таки, уявіть собі, що насправді все, що нам було потрібно – це:
Що, серйозно, так? Тепер ви починаєте розуміти, про що я говорю. Але уявіть, що ми дечому навчабося на минулих помилках, і виконуємо такий, більш оптимальний запит:
Разючі зміни на краще у плані виконання. З'єднання були виключені, оскільки оптимізатор тепер може переконатися в їхній марностіа якщо він здатний у цьому переконатися (а ви не зробабо цю роботу обов'язковою шляхом вибору *), то може і просто не робити всю цю роботу. Чому це так у цьому випадку? Зовнішній ключ CUSTOMER.ADDRESS_ID до первинного ключа ADDRESS.ADDRESS_ID гарантує рівно одне значення останнього, а значить і те, що операція JOIN буде з'єднанням типу "...-до одного", яке не збільшує і не зменшує кількість рядків. А якщо якихось рядків ми взагалі не вибираємо і не просимо, то ніякого сенсу взагалі їх завантажувати немає. Видалення JOIN, ймовірно, взагалі не вплине результат запиту. Бази даних роблять таке постійно. Можна виконати наступний запит на майже будь-якій базі даних:
--Oracle
SELECT CASE WHEN EXISTS (
SELECT 1/0FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например,PostgreSQLSELECT EXISTS (SELECT 1/0)
В даному випадку ви могли б очікувати на генерацію арифметичного виключення, як при виконанні наступного запиту:
SELECT 1/0 FROM dual
Вийшло:
ORA-01476: divisor is equal to zero
Але це не відбувається. Оптимізатор (або навіть синтаксичний аналізатор) може переконатися, що жодні елементи списку вибірки в предикаті EXISTS (SELECT ..) не змінять результату запиту, так що необхідності виконувати його немає. Ось так!
Тим часом...
Одна з неприємних проблем ORM полягає в тому, що в них так зручно писати запити SELECT *. Власне, наприклад, у HQL/JPQL вони взагалі використовуються за умовчанням. Можна взагалі опустити пропозицію SELECT, адже ми збираємося витягувати всю сутність цілком, правда? Наприклад:
FROM v_customer
Наприклад, Влад Міхалче (Vlad Mihalcea), експерт та прихильник розробки за допомогою бібліотеки Hibernateрекомендує використовувати [уточнені] запити практично завжди, коли ви впевнені, що не хочете зберегти будь-які зміни після вилучення. ORM сильно полегшують вирішення завдання збереження графів об'єктів. Примітка: Збереження. Завдання власне модифікації графів об'єктів та збереження змін нерозривно пов'язані. Але якщо робити цього ви не збираєтеся, то навіщо турбувати себе вилученням сутності? Чому не написати [уточнений] запит? Давайте чітко розберемося: з погляду продуктивності, написання запиту, спеціально підігнаного під ваш конкретний сценарій використання, свідомо краще за будь-який інший варіант. Можливо, вам байдуже, оскільки ваш набір даних невеликий і це не має значення. Чудово. Але коли, зрештою, вам знадобиться масштабованість, то перепроектувати свої програми під використання запитів замість імперативного обходу графа сутностей виявиться дуже непросто. А у вас буде чим зайнятися і без цього.
Підрахунок рядків для з'ясування наявності чогось
Один із випадків найжахливішого розбазарювання ресурсів – виконання запитів COUNT(*) просто, щоб дізнатися, чи є щось у базі даних. Наприклад, нам потрібно дізнатися, чи є у даного користувача взагалі замовлення. І ми виконуємо запит:
SELECTcount(*)
FROM orders
WHERE user_id =:user_id
Елементарно. Якщо COUNT = 0, замовлень немає. Інакше – є. Продуктивність виявиться не такою вже поганою, адже у нас, напевно, є індекс по стовпцю ORDERS.USER_ID. Але як ви вважаєте, яка буде продуктивність вищенаведеного запиту в порівнянні з наступним варіантом:
--Oracle
SELECT CASE WHEN EXISTS (
SELECT *
FROM orders
WHERE user_id =:user_id
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например,PostgreSQLSELECT EXISTS (
SELECT *
FROM orders
WHERE user_id =:user_id
)
Не потрібно бути семи п'ядей на лобі, щоб зрозуміти, що справжній предикат існування припинить пошук додаткових рядків відразу після виявлення першої ж . Тож якщо результат виявиться "замовлень немає", то швидкість буде порівнянною. Якщо ж, однак, результат буде "так, замовлення є", то у разі, коли не треба вважати точну кількість, відповідь буде отримана набагато швидше. Адже точна кількість нас і не цікавить. Тим не менш, ми наказали базі даних обчислити його ( непотрібна робота ), а база даних не знає, що ми ігноруємо всі результати більше 1 ( обов'язкова робота ). Звичайно, було б набагато гірше, якби для досягнення тих же результатів ми викликали list.size() для колекції, що підтримується JPA. Я вже писав про це у своєму блозі раніше, і проводив порівняльне тестування обох варіантів на р...
Висновок
Ця стаття викладає очевидні речі. Не примушуйте базу даних виконувати непотрібну, але обов'язкову роботу . Вона непотрібна , оскільки, відповідно до наявних вимог, ви знаєте, що певну конкретну частину роботи виконувати не потрібно. Тим не менш, ви кажете базі даних зробити її. Вона є обов'язковою, оскільки база даних ніяк не може переконатися, що ця робота непотрібна . Ця інформація є лише у клієнта та недоступна серверу. Отже, базі даних доводиться її виконувати. Стаття була присвячена SELECT *, переважно тому, що це такий зручний об'єкт для розгляду. Але це стосується не лише баз даних. Це стосується всіх розподілених алгоритмів, у яких клієнт вказує серверу виконувати непотрібну, але обов'язкову роботу. Скільки N+1 задач зустрічається у вашому середньостатистичному AngularJS-додатку, в яких UI організовує цикл за отриманим від сервісу результатом A, викликаючи сервіс B кілька разів, замість того, щоб запакувати всі звернення до B в єдиний виклик? Це явище, що дуже часто зустрічається. Рішення завжди одне й те саме. Чим більше інформації ви надаєте виконує ваші команди сутності, тим швидше вона (теоретично) ці команди виконує. Напишіть оптимальні запити. Завжди. Вся ваша система буде вдячна вам за це. Оригінал статті
ПЕРЕЙДІТЬ В ПОВНУ ВЕРСІЮ