PreparedStatement (підготовлені запити)

NB: хоча тут і ведеться оповідання про PreparedStatement, більшість його правдива і для CallableStatement. Як, напевно, більшості відомо в JDBC можна створювати різні види Statement'ів включаючи звичайні Statement , PreparedStatement і CallableStatement . Тут ми обговоримо що таке PreparedStatement і навіщо він потрібен. Отже, почнемо з того, що СУБД при надходженні в них запитів, перевіряють їх синтаксис, розбирають (soft parse), оптимізують (hard parse) і створюють якийсь query plan – то як насправді вже СУБД виконуватиме запит, які операції вона при цьому робитиме. СУБД вміють кешувати виконані запити, тобто якщо взяти запит: select * from books . І виконувати його кілька разів, на якомусь етапі СУБД почне його кешувати. Кеш у багатьох системах – це карта, що означає, що вона має ключ і значення. В даному випадку як ключ буде SQL запит (а точніше його хеш), а як значення - розібраний план. Коли СУБД отримує черговий запит з тим самим текстом, вона перевіряє кеш і якщо вже є скомпилированный запит, вона використовує його замість того, щоб заново його розбирати (насправді періодично СУБД доробляють hard parse, щоб оптимізувати запит ще більше). 1>Зверніть увагу, що в якості ключа використовується повністю тіло запиту. Це означає, що запит: select * from books where id=1 і запит select * from books where id=2 – не є однаковими і обидва компілюватимуться, що означає, що ми не можемо кешувати один і той же запит тільки з -за те, що параметри щоразу різні! Цю проблему вирішує PreparedStatement, який має вигляд: select * from books where id=? У даному випадку СУБД закешує запит і буде лише підставляти нові параметри замість знака питання. Це дозволяє суттєвоприскорити обробку запитів. Різні СУБД звичайно ж по-різному реалізують кеш і час, коли він буде задіяний (що часто ґрунтується на статистиці або просто на вказаному користувачем значенні). Що відбувається під капотом:

  1. Коли виконується connection.prepareStatement("some query"), драйвер звертається до СУБД для підготовки запиту*, яка повертає назад ідентифікатор запиту (його хеш як правило) і ще деякі дані, такі як кількість параметрів у запиті.
  2. При виклику executeQuery() драйвер надсилає лише ідентифікатор запиту та параметри, СУБД по ID знаходить вже розібраний запит та виконує його.

Але прозорливий читач відразу помітить, що з наступному виконанні connection.prepareStatement() знову відбудеться спочатку виклик prepare до БД, потім власне виклик виконання конкретного запиту. По-перше, це два виклики через мережу, що не добре з точки зору продуктивності. По-друге, хоч СУБД і закешувало запит і не буде його вдруге розбирати, ми вже маємо ID запиту після першого разу, навіщо нам знову лазити в БД за ним, якщо його можна десь зберегти та перевикористовувати? Власне так це й реалізовано в більшості драйверів: Connection#prepareStatement(String sql) – тут і відбувається вся магія, за переданим у метод рядку драйвер перевіряє об'єкт у своєму внутрішньому (не СУБД!) кеші, і якщо той там є , Повертає його; якщо ні – створює новий.** Це називається неявним кешуванням (implicit cache)***. statement, а поміщає його якраз у кеш. Фізично PreparedStatement закривається тільки у випадку: а) якщо з'єднання з БД було закрито;кеш досягає своєї максимальної місткості і потрібно звільняти його від старих і маловикористовуваних statement'ів в) якщо кеш відключений; г) якщо кеш не підтримується драйвером :) Але це ще не все. У більшості випадків програми працюють не безпосередньо зі з'єднаннями, створюючи та закриваючи їх, а з пулами з'єднань (наприклад, DBCP, C3P0), які самі їх створюють та надають вашому коду. Так от, якщо в звичайному випадку PreparedStatements прив'язані до одного з'єднання і не можуть бути перевикористаними, то пули дозволяють кожному з'єднанню використовувати підготовлені запити інших з'єднань, що означає, що продуктивність зросте ще більше. підготовленими запитами в MySQL (багато з цього підходить і для інших СУБД):

Запити повинні точно збігатися (запити зі словами USERS і users – будуть вважатися різними) – це правда для всіх СУБД****

Не завжди підпорядкованідержави кешуються з першого разу, часто їх потрібно виконати по кілька разів.

З'єднання до різних MySQL серверів, що використовують два різних протоколи, або навіть просто - два однакових з'єднання з різними кодуваннями за замовчуванням, - всі вони будуть використовувати різні кеші, які не будуть перевикористовуватися іншими з'єднаннями.

Підзапити та запити з UNION не кешуються.

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

Обов'язково встановіть властивість cachePrepStmts у true, бо за замовчуванням вона вимкнена! Використовуйте параметри з'єднання, такі як prepStmtCacheSize та prepStmtCacheSqlLimit для конфігурації MySQL драйвера.

Які ще плюшки нам дає PreparedStatement?

Крім покращення продуктивності, підготовлені запити захищають від SQL Injections. Щоб було просто зрозуміти суть, прикладбуде дуже простим та дурним. Допустимо, є функціональність на форумі, така як "видалити користувача". Ми вводимо в поле його ім'я та натискаємо кнопку Submit. Передається запит на сервер і ми працюємо зі звичайним Statement, та й для створення запиту використовуємо конкатенацію:

Запам'ятайте раз і назавжди, що це погано! Якщо якийсь зловмисник у полі на формі введе наступний рядок: vasia' or 'a'='a це призведе до сумних наслідків. Результуючий запит буде наступним:

Т.к. 'a' завжди дорівнює 'a', то вираз у where завжди буде true і в результаті запиту видаляться всі записи з таблиці. Щоб уникнути подібного, потрібно заескейпити вхідний рядок. Це означає, що всі вхідні символи, якщо вони є чимось, що для СУБД представляється значущим символом (наприклад, лапки), будуть замінені на якусь іншу комбінацію символів. Робити це можна самому, можна використовувати вже існуючі методи/бібліотеки, але зрештою запит виходить приблизно такий:

А потім ми вкажемо параметр: preparedStatement.setString(1, username) І все передане сюди сприйматиметься виключно як текст, СУБД сама все заескейпить.

* Деякі драйвера, що не підтримують передкомпіляцію, відсилають запит тільки на етапі executeQuery(). ** Зауважте, що при створенні звичайного Statement, жодного рядка в об'єкт з'єднання не передається, що означає, що вони створюються щоразу заново. *** Насправді деякі JDBC драйвера (такі як Oracle) можуть кешувати та звичайні statement'и. У випадку Oracle JDBC Driver для цього потрібно смикати implementation-specific API і воно не буде настільки ж ефективно, та й плюс там є свої проблеми. Це називається explicit statement caching. **** Звичайно може і не для всіх, все я недивився, але для 3 СУБД із перевірених – це правда.

PS: велике спасибі нашому Vlad'у за його блог пост на замовлення, який дуже допоміг у написанні цієї статті. PPS: Обговорення на тему: PreparedStatements. Both DBMS & Java caching

  • by Старовер'prepared-statementsjdbcperformance

Дякую. Усі досить зрозуміло описано.

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

Насправді, малоймовірно, що десь так зроблено. По-перше, цілком можливо, що є СУБД, що не дають доступ з інших сесій до запитів, закешованих в даній, по-друге, варто пам'ятати, що до пулу з'єднань Java-коду здійснюється багатопоточний доступ. Потік отримує з'єднання у своє розпорядження і якщо у даного з'єднання свій кеш PreparedStatement'ів, то доступ до даного кешу не потрібно синхронізувати - з ним працюватиме лише потік, що захопив з'єднання. Якщо ж у нас загальний кеш PreparedStatement, то доступи до цих сутностей потрібно синхронізувати: що буде якщо два потоки захоплять один і той же PreparedStatement?

З практики пул з'єднань Oracle WebLogic використовує свій кеш для кожного з'єднання.

Поглянув на C3P0, судячи з документації Configuring Statement Pooling, він теж цього не підтримує. Пам'ятаю, що це твердження не перевіряв, але бачив згадку у кількох джерелах, тож залишу питання відкритим – треба буде покопати глибше.

Тим не менш, хочеться все-таки поглянути на різні реалізації і точно переконається, що це ні в кого не реалізовано.

Ну це спільне питання, простонарод рідко статті пишуть. Я став більше концентруватися на тестуванні і для цього завів окремий сайт. А інші як писали рідко, так і пишуть:)