Представлення (VIEW) в MySQL (исходники)

У коментарях Хабра згадувалися питання по використанню уявлень. Даний топік є оглядом подань, що з’явилися в MySQL версії 5.0. У ньому розглянуто питання створення, переваги та обмеження уявлень.


Що таке уявлення?


Подання (VIEW) – об’єкт бази даних, що є результатом виконання запиту до бази даних, визначеного за допомогою оператора SELECT, в момент звернення до подання.

Уявлення іноді називають “віртуальними таблицями”. Така назва пов’язана з тим, що подання доступно для користувача як таблиця, але саме воно не містить даних, а витягує їх з таблиць в момент звертання до нього. Якщо дані змінені в базовій таблиці, то користувач отримає актуальні дані при зверненні до подання, що використовує дану таблицю; кешування результатів вибірки з таблиці при роботі уявлень не проводиться. При цьому, механізм кешування запитів (query cache) працює на рівні запитів користувача безвідносно до того, чи звертається користувач до таблиць або уявленням.
Уявлення можуть грунтуватися як на таблицях, так і на інших уявленнях, тобто можуть бути вкладеними (до 32 рівнів вкладеності).


Переваги використання уявлень:



  1. Дає можливість гнучкої настройки прав доступу до даних за рахунок того, що права даються не на таблицю, а на виставу. Це дуже зручно в разі якщо користувачеві потрібно дати права на окремі рядки таблиці або можливість отримання не самих даних, а результату якихось дій над ними.
  2. Дозволяє розділити логіку зберігання даних та програмного забезпечення. Можна міняти структуру даних, не зачіпаючи програмний код, потрібно лише створити уявлення, аналогічні таблицям, до яких раніше зверталися додатка. Це дуже зручно коли немає можливості змінити програмний код або до однієї бази даних звертаються кілька додатків з різними вимогами до структури даних.
  3. Зручність у використанні за рахунок автоматичного виконання таких дій як доступ до певної частини рядків і / або стовпців, отримання даних з декількох таблиць і їх перетворення за допомогою різних функцій.


Обмеження уявлень в MySQL


У статті наведені обмеження для версії MySQL 5.1 (надалі їх кількість може скоротитися).



Створення подань


Для створення уявлення використовується оператор CREATE VIEW, що має наступний синтаксис:

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED / MERGE / TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED / LOCAL] CHECK OPTION]

* This source code was highlighted with Source Code Highlighter.


view_name – ім’я створюваного подання. select_statement – оператор SELECT, що вибирає дані з таблиць і / або інших уявлень, які будуть міститися в поданні

Оператор CREATE VIEW містить 4 необов’язкові конструкції:


  1. OR REPLACE – при використанні даної конструкції у разі існування уявлення з таким ім’ям старе буде видалено, а нове створене. В противному випадку виникне помилка, що інформує про сществованіі подання з таким ім’ям і нове уявлення створено не буде. Слід відзначити одну особливість – імена таблиць і уявлень в рамках однієї бази даних повинні бути унікальні, тобто не можна створити подання з ім’ям уже існуючої таблиці. Однак конструкція OR REPLACE діє тільки на представлення і заміщати таблицю не буде.
  2. ALGORITM – визначає алгоритм, використовуваний при зверненні до подання (докладніше мова про це піде нижче).
  3. column_list – задає імена полів уявлення.
  4. WITH CHECK OPTION – при використанні даної конструкції всі додавати або змінювати рядки будуть перевірятися на відповідність визначенню уявлення. У разі невідповідності дана зміна не буде виконано. Зверніть увагу, що при вказівці даної конструкції для необновляемого уявлення виникне помилка і уявлення не буде створено. (Докладніше мова про це піде нижче).

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


  1. Імена полів подання повинні бути унікальні в межах даного подання. При створенні подання заснованого на кількох таблицях можлива ситуація повторення імен полів уявлення. Наприклад:
    CREATE VIEW v AS SELECT a.id, b.id FROM a,b;

    * This source code was highlighted with Source Code Highlighter.

    Для уникнення такої ситуації потрібно явно вказувати імена полів представлення
    CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;

    * This source code was highlighted with Source Code Highlighter.

    Того ж результату можна добитися, використовуючи синоніми (аліаси) для назв колонок:
    CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;

    * This source code was highlighted with Source Code Highlighter.


  2. У разі якщо у визначенні представлення одержувані дані перетворюються з допомогою якихось функцій, то ім’ям поля буде даний вираз, що не дуже зручно для подальших посилань на це поле. Напімер:
    CREATE VIEW v AS SELECT group_concat(DISTINCT column_name oreder BY column_name separator `+`) FROM table_name;

    * This source code was highlighted with Source Code Highlighter.

    Навряд чи зручно використовувати в подальшому в якості імені поля `group_concat (DISTINCT username ORDER BY username separator` + `)`

Для перегляду вмісту уявлення ми використовуємо оператор SELECT (повністю аналогічно як у випадку простої таблиці), з іншого строни, оператор SELECT є в самому визначенні уявлення, тобто виходить вкладена конструкція – запит в запиті. При цьому, деякі конструкції оператора SELECT можуть бути присутніми в обох операторах. Можливі три варіанти розвитку подій: вони обидві будуть виконані, одна з них буде проігнорірованна і результат невизначений. Розглянемо докладніше ці випадки:


  1. Якщо в обох операторах зустрічається умова WHERE, то обидві ці умови будуть виконані як якщо б вони були об’єднані оператором AND.
  2. Якщо у визначенні подання є конструкція ORDER BY, то вона буде працювати тільки в разі відсутності у зовнішньому операторі SELECT, звертайтеся до подання, власного умови сортування. При наявності конструкції ORDER BY в зовнішньому операторі сортування, наявна в визначенні подання, буде проігнорована.
  3. При наявності в обох операторах модифікаторів, що впливають на механізм блокування, таких як HIGH_PRIORITY, результат їх спільної дії невизначений. Для уникнення невизначеності рекомендується у визначенні уявлення не використовувати подібні модифікатори.


Алгоритми уявлень


Існує два алгоритми, що використовуються MySQL при зверненні до подання: MERGE і TEMPTABLE.

У разі алгоритму MERGE, MySQL при зверненні до подання додає в використовується оператор відповідні частини з визначення уявлення та виконує вийшов оператор.

У разі алгоритму TEMPTABLE, MySQL заносить вміст представлення в тимчасову таблицю, над якою потім виконується оператор звернений до подання.
Зверніть увагу: У разі використання цього алгоритму подання не може бути обновлюваним (див. далі).

При створенні вистави є можливість явно вказати використовуваний алгоритм за допомогою необов’язковою конструкції [ALGORITHM = {UNDEFINED / MERGE / TEMPTABLE}]
UNDEFINED означає, що MySQL сам вибирає який алгоритм використовувати при зверненні до подання. Це значення за замовчуванням, якщо дана конструкція відсутня.

Використання алгоритму MERGE вимагає відповідності 1 до 1 між рядками таблиці і заснованого на ній уявлення.

Нехай наше уявлення вибирає відношення числа переглядів до числа відповідей для тем форуму:

CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;

* This source code was highlighted with Source Code Highlighter.


Для даного подання кожен рядок відповідає єдиному рядку з таблиці topics, тобто може бути використаний алгоритм MERGE. Розглянемо наступне звернення до нашому уявленню:

SELECT subject, param FROM v WHERE param>1000;

* This source code was highlighted with Source Code Highlighter.


У разі MERGE алгоритму MySQL включає визначення подання в що використовується оператор SELECT: замінює ім’я представлення на ім’я таблиці, замінює список полів на визначення полів уявлення і додає умова в частині WHERE за допомогою оператора AND. Підсумковий оператор, що виконується потім MySQL, виглядає таким чином:

SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;

* This source code was highlighted with Source Code Highlighter.


Якщо у визначенні подання використовуються групові функції (count, max, avg, group_concat і т.д.), підзапити в частині перерахування полів або конструкції DISTINCT, GROUP BY, то не виконується необхідну алгоритмом MERGE відповідність 1 до 1 між рядками таблиці і заснованого на ній уявлення.

Нехай наше уявлення вибирає кількість тем для кожного форуму:

CREATE VIEW v AS SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;

* This source code was highlighted with Source Code Highlighter.


Знайдемо максимальну кількість тем у форумі:

SELECT MAX(num) FROM v;

* This source code was highlighted with Source Code Highlighter.


Якби використовувався алгоритм MERGE, то цей запит був би перетворений таким чином:

SELECT MAX(count(*)) FROM topics GROUP BY forum_id;

* This source code was highlighted with Source Code Highlighter.


Виконання цього запиту призводить до помилки “ERROR 1111 (HY000): Invalid USE of GROUP function”, так як використовується вкладеність групових функцій.

У цьому випадку MySQL використовує алгоритм TEMPTABLE, тобто заносить вміст представлення в тимчасову таблицю (даний процес іноді називають “матеріалізацією представлення”), а потім обчислює MAX () використовуючи дані тимчасової таблиці:

CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;
SELECT MAX(num) FROM tmp_table;
DROP TABLE tpm_table;

* This source code was highlighted with Source Code Highlighter.


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


  1. У разі UNDEFINED MySQL намагається використовувати MERGE скрізь де це можливо, так як він більш ефективний ніж TEMPTABLE і, на відміну від нього, не робить подання не обновлюваним.
  2. Якщо ви явно вказуєте MERGE, а визначення подання містить конструкції забороняють його використання, то MySQL видасть попередження і встановить значення UNDEFIND.


Оновлюваність уявлень


Подання називається оновлюваним, якщо до нього можуть бути застосовні оператори UPDATE і DELETE для зміни даних в таблицях, на яких засновано уявлення. Для того, щоб подання було обновлюваним повинно бути виконано 2 умови:


  1. Відповідність 1 до 1 між рядками уявлення і таблиць, на яких засновано уявлення, тобто кожному рядку уявлення повинне відповідати по одному рядку в таблицях-джерелах.
  2. Поля подання повинні бути простим перерахуванням полів таблиць, а не вираженіеямі col1/col2 або col1 +2.

Зверніть увагу: Зустрічаються в російсько-язичної літературі вимоги, щоб оновлюване подання було засновано на єдиній таблиці і присутність в числі полів представлення первинного ключа фізічекой таблиці не є необхідними. Швидше за все вимога єдиної таблиці є помилкою перекладу. Справа в тому, що через уявлення, засноване на декількох таблицях, може обновляти тільки одну таблицю за запит, тобто конструкція SET оператора UPDATE повинна перераховувати колонки тільки однієї таблиці з визначення уявлення. Крім того, щоб уявлення, заснований на декількох таблицях, було оновлюваним, таблиці в його визначенні повинні бути об’єднані тільки за допомогою INNER JOIN, а не OUTER JOIN або UNION.

Оновлюване уявлення може допускати додавання даних (INSERT), якщо всі поля таблиці-джерела, не присутні в поданні, мають значення за замовчуванням.

Зверніть увагу: Для уявлень, заснованих на декількох таблицях, операція додавання даних (INSERT) працює тільки у випадку якщо відбувається додавання в єдину реальну таблицю. Видалення даних (DELETE) для таких уявлень не підтримується.

При використанні в визначенні подання конструкції WITH [CASCADED / LOCAL] CHECK OPTION все додавати або змінювати рядки будуть перевірятися на відповідність визначенню уявлення.


Іншими словами, не можна додати або змінити дані в представленні таким чином, щоб вони не були доступні через уявлення.

Ключові слова CASCADED і LOCAL визначають глибину перевірки для уявлень заснованих на інших уявленнях:


Розглянемо приклад оновлюваного уявлення, заснованого на двох таблицях. Нехай наше уявлення вибирає теми форуму з числом переглядів понад 2000.

punbb >CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION;
Query OK, 0 rows affected (0.03 sec)

punbb >SELECT * FROM v WHERE subject=`test`;
+————+———+———–+
/ forum_name / subject / num_views /
+————+———+———–+
/ Новини / test / 3000 /
+————+———+———–+
1 row IN SET (0.03 sec)

punbb >UPDATE v SET num_views=2003 WHERE subject=`test`;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 WARNINGS: 0

punbb >SELECT * FROM v WHERE subject=`test`;
+————+———+———–+
/ forum_name / subject / num_views /
+————+———+———–+
/ Новини / test / 2003 /
+————+———+———–+
1 row IN SET (0.01 sec)

punbb >SELECT subject, num_views FROM topics WHERE subject=`test`;
+———+———–+
/ subject / num_views /
+———+———–+
/ test / 2003 /
+———+———–+
1 rows IN SET (0.01 sec)

* This source code was highlighted with Source Code Highlighter.


Однак, якщо ми спробуємо встановити значення num_views менше 2000, то нове значення не буде задовольняти умові WHERE num_views> 2000 в визначенні подання та поновлення не відбудеться.

punbb >UPDATE v SET num_views=1999 WHERE subject=`test`;
ERROR 1369 (HY000): CHECK OPTION failed `punbb.v`

* This source code was highlighted with Source Code Highlighter.


Не всі оновлювані уявлення дозволяють додавання даних:

punbb >INSERT INTO v (subject,num_views) VALUES(`test1`,4000);
ERROR 1369 (HY000): CHECK OPTION failed `punbb.v`

* This source code was highlighted with Source Code Highlighter.


Причина в тому, що значенням за замовчуванням колонки forum_id є 0, тому добавляемая рядок не задовольняє умові WHERE forum_id = f.id у визначенні представлення. Вказати ж явно значення forum_id ми не можемо, так як такого поля немає в визначенні подання:

punbb >INSERT INTO v (forum_id,subject,num_views) VALUES(1,`test1`,4000);
ERROR 1054 (42S22): Unknown COLUMN `forum_id` IN `field list`

* This source code was highlighted with Source Code Highlighter.


З іншого строни:

punbb >INSERT INTO v (forum_name) VALUES(`TEST`);
Query OK, 1 row affected (0.00 sec)

* This source code was highlighted with Source Code Highlighter.


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

Удачі в роботі з уявленнями!

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


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

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

Ваш отзыв

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

*

*