Досвід роботи з InterBase: несподіванки і хитрості в оптимізації підзапитів

З чим ми маємо справу


Отже, в InterBase, як і в будь-якій нормальній СУБД на базі SQL, можна в рамках багатьох запитів писати вкладені вкладені запити типу select, Беручи їх в круглі дужки. Цілей вживання такої конструкції, і відповідно способів її інтерпретації може бути декілька.


По-перше, підзапит, як правило, можна написати в тому місці, де потрібно отримати / обчислити будь-яке одне значення. У цьому випадку просто на місці значення пишуть підзапит в дужках. При цьому фраза select цього вкладені запити повинна повертати рівно одне поле, а логіка інших частин повинна забезпечувати, щоб поверталося не більше одного запису. Якщо не буде сформовано жодної, то підзапит повертає null, Якщо ж кілька, то виникне помилка. Вкладені запити подібного роду можуть фігурувати, зокрема, в обчислюваних виразах або в операціях порівняння.


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



Начебто все. Останні дві конструкції – повні синоніми, але ANY краще не вживати, особливо якщо добре знаєте англійську. Тому що штука дуже двозначна.


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


Деякі громадяни, в тому числі в su.dbms.interbase, пропонували, як доопрацювання до IB зробити можливість отримувати кілька полів, і порівнювати їх зі списком значень за один прийом. Що ж, операція дійсно була б корисна, але на суть того, що описано вище і нижче це не вплине.


Далі про підзапитах першого виду будемо говорити, що вони існують в скалярному контексті, а другого виду – у множині. Принципи термінології взяті з мови Perl.


Крім цього існує конструкція EXISTS (підзапит), проте в нашому випадку вона не представляє інтересу, про що нижче.


Все те, що я написав в цьому розділі, може здатися другорядним. Проте це абсолютно не так, і в мене були вагомі підстави почати саме з цього. Тому що обробка тих і інших видів підзапитів в InterBase розрізняється радикальним чином.


Поширені помилки


Взагалі-то це не зовсім омани. Точніше, у багатьох СУБД це ніякі не помилки, а проза життя. Тому в багатьох книгах це справа описується, як щось само собою зрозуміле. Тому багато людей, не розібравшись, переносять подібні твердження на InterBase, що призводить до несподіваних і, як правило, негативних наслідків.


Отже, вкладені запити з точки зору їх обчислюваності без охоплює запиту, ділять на корельовані і некорельованих. Корельованих означає "залежний від зовнішнього контексту". Тобто, в такому запиті, де-небудь хоча б раз вживається посилання на полі будь-якої поточної записи зовнішнього запиту. Таким чином, по ходу обробки всієї конструкції на кожну запис зовнішнього запиту потрібно Переобчислювати підзапит.


З іншого боку, некорельованих вкладені запити побудовані виключно на основі власних таблиць та процедур, і з зовнішнього контексту нічого не вимагають. Такий запит можна викликати окремо, нічого в ньому не змінивши. І результат такого запиту, відповідно, на одних і тих же даних постійний. Звідси висновок: немає сенсу викликати такий підзапит кілька разів, досить при першому виклику запам'ятати результат, і потім використовувати його для зовнішнього запиту.


Ось це і є те саме оману. Точніше, їх тут навіть два.


Некорельованої підзапит незалежний від контексту.


Ну, незалежний-то він – незалежний, але це ще не означає, що ніяк не пов'язаний. Фактично підзапит завжди використовується для порівняння будь-то його полів з якимись зовнішніми значеннями. І облік цього факту в ряді ситуацій може бути досить успішно використаний, щоб ліквідувати частину роботи під час виконання підзапитах.


Некорельованої підзапит виконується один раз.


Це один з підходів, що застосовуються в більшості СУБД. Проте в InterBase це правда, тільки для підзапитів в скалярному контексті. Для множинного контексту застосовується зовсім інший підхід, описаний в наступному розділі.


Як воно працює насправді


Отже, повернемося до наших контекстами. У скалярному контексті InterBase дійсно приймає до уваги, корельованих підзапит, чи ні. Якщо ні, то запит викликається один раз, результат (одне значення) запам'ятовується, і використовується при відпрацюванні зовнішнього запиту приблизно так само, як звичайний параметр.


У списковому ж контексті (найчастіше – в IN (…)), підзапит завжди викликається на кожну ітерацію зовнішнього запиту. Точніше тоді, коли для поточного запису перевірені інші умови, щоб виключити зайві виклики. Провернути попередню схему InterBase не в змозі, мабуть з тієї причини, що запам'ятовувати доведеться не одне значення, а список, причому потенційно необмеженої довжини.


Звідси ж випливає, що якщо б InterBase умів це робити, то міг би досить легко перетворювати множинні вкладені запити у сполуки, які він, як правило, в стані реалізувати досить ефективно. У самому справі, підзапит всередині IN (…) повертає таблицю з одним полем, і при подальшій обробці зовнішній запит фактично з'єднується з цією таблицею. Мабуть у InterBase складності з збереженням цієї самої проміжної таблиці, так що він вважає за краще іншу стратегію – на кожній ітерації обчислювати ті значення, які йому потрібні.


І ось тут ми якраз і натикаємося на досить оригінальну (на мій погляд) оптимізацію. InterBase дійсно обчислює такі вкладені запити багато раз, але при цьому враховує контекст, так що часом досягається ефективність, не поступається розкручуванні вкладені запити в з'єднання. Хоча, на жаль, це можливо далеко не у всіх випадках.


Коли підзапит викликається конструкцією типу значення IN (select полі …), то, якщо уважно подумати, нам і не потрібні всі записи підзапитах. Потрібно знайти ті, у яких поле має значення. А це означає, що оптимізатор може зі спокійною душею додати вкладені запити в where додаткову умову …) and полі = значення. А це, у свою чергу цілком може призвести до того, що за даним полю буде використаний індекс, або воно послужить основою для інших способів оптимізації.


І до речі, дана оптимізація не робиться для підзапитів в скалярному контексті. Вони відпрацьовуються абсолютно незалежно. Хоча в них вона могла бути теж аж ніяк не даремною. Ще одна загадка природи.


І тепер настав час ще раз згадати про EXISTS (…). За своєю природою дана конструкція призначена для виклику корельованих підзапитів, і ці вкладені запити всередині неї ведуть себе відповідно з викликом у множині контексті. Хоча виконання кожного виклику, природно, припиняється при отриманні першої ж записи. Саме, виходячи з цього, і слід оцінювати трудомісткість EXISTS.


Серія прикладів


Дані



create table test1( id integer not null primary key,
x integer );
create table test2( id integer not null primary key,
y integer);


Оскільки експеримент проводився на свіжоствореної базі, індекси первинних ключів отримали ті ж номери, що і таблиці – rdb $ primary1 і rdb $ primary2. Інших індексів немає.


Таблиці заповнені записами дуже простого вигляду:



insert into test1(id, x) values(1, 10);
insert into test1(id, x) values(2, 20);

insert into test1(id, x) values(10, 100);
insert into test2(id, y) values(1, 110);
insert into test2(id, y) values(2, 120);

insert into test2(id, y) values(10, 200);


Всі подальші запити наводяться з планами, отриманими шляхом включення set plan в isql.


Оптимізація скалярного вкладені запити


Точніше, доказ її відсутності.



select x from test1
where id = (select id from test2 where y = 130);


PLAN (TEST2 NATURAL)
PLAN (TEST1 INDEX (RDB$PRIMARY1))


За своєю звичкою InterBase видає плани підзапитів першими, до плану основного запиту.


Як можна бачити, умова в підзапитах виду id = id_ізвне ніяк на нього не вплинуло – він обробляється повним перебором. Спроба явно підсунути йому план з індексом за test2 (id) до успіху не призводить – повертається помилка. Зате зовнішній запит індекс використовує.


Тепер спробуємо написати в точності те ж саме, але через IN.


Аналогічний запит, але через IN ()



select x from test1
where id in (select id from test2 where y=130);


PLAN (TEST2 INDEX (RDB$PRIMARY2))
PLAN (TEST1 NATURAL)


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


З іншого боку, якщо витягнути підзапит і спробувати виконати його окремо, то план з індексом не буде сприйнятий. Тому що для єдиного, що залишився умови він абсолютно не до місця.


Треба сказати, що обидва запиту насправді дають результат, еквівалентний наступного з'єднанню:



select test1.x from test1, test2
where test1.id=test2.id and test2.y=130;


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



PLAN JOIN (TEST2 NATURAL,TEST1 INDEX (RDB$PRIMARY1))


А варіант із множинним діє приблизно так:



PLAN JOIN (TEST1 NATURAL,TEST2 INDEX (RDB$PRIMARY2))


У даному випадку перший варіант ефективніше. Він робить один прохід по test2, знаходить у ній всього один запис, у якої y = 130, і з отриманим значенням виконує зовнішній запит. Варіант із з'єднанням, проте, є більш загальним, так як скалярний підзапит призведе до помилки, якщо записів з y = 130 виявиться кілька.


Другий варіант, з IN це якраз стерпить, проте він менш ефективний, тому що викликає пошук по table2 на кожній ітерації зовнішнього запиту. Щоправда, сам цей пошук робиться за індексом.


І тут ще один суттєвий момент: при відпрацюванні підзапитів типу IN (…), = SOME (…), = ANY (…) перебір зупиняється після першої ж записи, виданої підзапитах. У той час як = ALL (…) буде працювати або до кінця, або до першого запису, не задовольняє умові. Тобто при вдалому збігу обставин, якщо “ підходяща "" запис спливе на першій же ітерації вкладені запити, все може бути дуже ефективно. А можлива й зворотна ситуація.


Природно, ті ж міркування застосовні і при інших видах порівняння. Операції <, <=, <> так само можна внести у внутрішній запит. Хоча користі від цього, звичайно, буде набагато менше, ніж від рівності.


До речі, у двох описаних прикладах можна замість y = 130 в підзапитах зробити x = 30 в зовнішньому запиті. На плани це не вплине, оскільки і в тому, і в іншому випадку умови накладаються на неіндексіруемие поля. Однак оцінки ефективності поміняються місцями, і варіант з підзапитів через IN стане більш ефективним. В іншому, жоден з варіантів з підзапитах ніколи не буде ефективніше, ніж оптимальний план у варіанті з з'єднанням. Тому неможливість автоматичної розкрутки підзапитів у сполуки є важливим недоліком, який слід враховувати.


Додаткову інформацію Ви можете отримати в компанії Interface Ltd

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*