Підготовка до виконання роботи

1 Ознайомитись з інструкцією.

2 Опрацювати теоретичний матеріал.

3 Усно дати відповіді на контрольні запитання вхідного контролю.

4 Продумати методику виконання роботи.

5 Підготувати бланк звіту.

Інструмент, обладнання прилади

Операційна система Windows 2000, сервер СУБД MySQL, монітор mysql, текстовий редактор, інформаційний портал.

Порядок виконання роботи

1 Отримати допуск до виконання лабораторної роботи, давши відповіді на контрольні запитання вхідного контролю.

2 Увімкнути ПК, увійти в операційну систему під відповідним користувачем.

3 Розглянути теоретичні положення лабораторної роботи, а також методичні рекомендації.

4 Використовуючи теоретичні положення, а також концептуальні моделі баз даних створюємо таблиці СУБД MySQL та заповнюємо їх використовуючи власноруч приготовані текстові файли.

5 Результати виконання лабораторної роботи записати у звіт по роботі і продемонструвати викладачеві.

6 Оформити звіт по роботі, відповісти на питання вихідного контролю, зробити висновки.

Питання вихідного контролю

1 З допомогою якої команди можна дізнатися, які бази даних існують в даний момент на сервері MySQL?

2 З допомогою якої команди можна дізнатися, які таблиці бази даних існують в даний момент на сервері MySQL?

3 Які команди використовуються для створення і заповнення таблиць бази даних?

4 Навести приклади створення і заповнення таблиць певної бази даних.

5 Для чого призначена команда LOAD DATA?

6 Яка команда використовується при додаванні одинарних записів до таблиці?

7 Розкажіть порядок створення текстового файлу для завантаження записів.

8 Як позначається значення NULL у текстовому файлі?

 Лабораторна роботи № 6

Тема: Створення індексів бази даних MySQL

Мета роботи: Ознайомитись з основним призначенням індексів, вивчити характеристики індексів, навчитися створювати та знищувати індекси, створити індекси для власної бази даних.

Теоретичні відомості

Індексування таблиць

Індекси є основним засобом прискорення доступу до вмісту таблиць; особливо це стосується запитів, що включають об’єднання декількох таблиць. У цій роботі ми розглянемо характеристики індексів і синтаксис, що використовується для їхнього створення й знищення.

Характеристики індексів

СУБД MySQL забезпечує досить гнучкі способи створення індексів.

– Таблицю можна індексувати як по одному стовпцю, так і по комбінації стовпців.

– Індексу може бути заданий режим вмістимості повторюваних значень або вмістимості тільки унікальних значень.

– Будь-який стрічковий тип (крім ENUM і SET) можна індексувати по першим n символах ліворуч. (Не можна створювати індекси по стовпцях типу BLOB і TEXT, поки не задана довжина префікса.) Префікси можуть мати довжину до 255 байт. Якщо перші n символів унікальні, це зменшує розмір індексу й збільшує швидкість обробки.

Звичайно, на створення індексів теж існують деякі обмеження, але в міру розвитку СУБД MySQL їхнє коло буде звужуватися. Нижче наведена таблиця, що відображає розходження можливостей індексування таблиць у форматах ISAM і MyISAM. (Ця таблиця не містить характеристик типу MERGE, тому що таблиці цього типу створюються з таблиць типу MyISAM і мають аналогічні характеристики індексування.)

У рядку, що відображає максимальний розмір рядка індексу для типів BDB і InnoDB, показані два числа. Для таблиць цих типів до версії 4.0.3 розмір дорівнює 500 байтам і 1024 байтам – у наступних версіях. Із цієї таблиці видно, що таблиці, створені у форматі ISAM, не можуть мати порожніх значень в індексованому стовпці. Крім того, неприпустиме індексування стовпців типу BLOB і TEXT. Формат MyISAM знімає всі ці обмеження.

Одним із проявів цих розходжень для двох даних типів стовпців є те, що залежно від версії СУБД MySQL індекс створити буде просто неможливо. Наприклад, у СУБД MySQL до версії 3.23 працює тільки метод ISAM. Це не дозволяє індексувати стовпці, що містять порожні значення. Отже, якщо ви хочете, щоб індекс мав певні властивості, варто використовувати певні типи таблиць. Якщо потрібно індексування по стовпцях типу BLOB, необхідно використовувати тільки таблиці типу MyІSAM або BDB.

Перетворити таблицю з одного типу в іншій можна за допомогою оператора ALTER TABLE. Починаючи з версії 3.23 можливий експорт старих таблиць із формату ISAM b формат MyІSAM. Таку можливість надає оператор ALTER TABLE. Це дозволяє скористатися новими можливостями індексування. ALTER TABLE tbl_name ТУРІ = MYISAM;

Створення індексів

СУБД MySQL дозволяє створювати кілька типів індексів.

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

– Унікальний індекс. Цей тип індексу забороняє дублювання записів. Для індексу, що складається з одного стовпця, це гарантує відсутність повторюваних значень. Для індексів, що містять кілька стовпців (складних індексів), це гарантує відсутність повторюваних комбінацій стовпців.

– Індекс типу FULLTEXT, що використовується при здійсненні пошуку по контексту.Індекс цього типу підтримується тільки для таблиць типу MyІSAM.

Індекси можна створювати в процесі створення таблиці задопомогою оператора CREATE TABLE. За допомогою операторів CREATE INDEX і ALTER TABLE можна створювати індекси для вже існуючих таблиць. Оператор CREATE INDEX був доданий у версії 3.22, у більш ранніх версіях для цієї мети можна скористатися оператором ALTER TABLE. (СУБД MySQL передбачає внутрішнє відпрацьовування операторів CREATE INDEX в операціях ALTER TABLE.)

Створити індекс для існуючої таблиці можна за допомогою оператора ALTER TABLE або CREATE INDEX. При цьому варто враховувати, що можливості оператора ALTER TABLE трохи ширше. У СУБД MySQL з його допомогою можна створити звичайний індекс, індекси UNIQUE, PRIMARY KEY і FULLTEXT.

ALTER TABLE tbl_name ADD INDEX index_name (index_columns);

ALTER TABLE tbl_name ADD UNIQUE index_name (index_columns);

ALTER TABLE tbl_name ADD PRIMARY KEY (index_columns);

ALTER TABLE tbl_name ADD FULLTEXT (index_columns);

де tbl_name - ім'я індексованої таблиці, index_columns – список індексованих стовпців. Якщо індекс складається з декількох стовпців, вони розділяються в списку комами. Ім'я індексу index_name можна не вказувати. У випадку його відсутності СУБД MySQL присвоїть індексу ім'я першого індексованого стовпця. Оператор ALTER TABLE дозволяє створювати за допомогою одного оператора відразу кілька індексів. (Це буде швидше, ніж створювати по одному індексі одним оператором окремо.)

У СУБД MySQL за допомогою оператора CREATE INDEX можна створити тільки звичайний індекс і індекс UNIQUE.

Для того щоб індекс містив тільки унікальні значення, необхідно створити індекс як PRIMARY KEY або UNIQUE. Індекси PRIMARY KEY і UNIQUE подібні (просто індекс PRIMARY KEY являє собою індекс UNIQUE з ім'ям PRIMARY), хоча між ними є два розходження.

– Таблиця може мати тільки один індекс PRIMARY KEY, оскільки не можна мати два індекси з тим самим ім'ям PRIMARY. Таблиця може містити кілька індексів UNIQUE, хоча це й трохи неприродно.

– На відміну від індексу UNIQUE, індекс типу PRIMARY KEY не може містити порожніх значень. При цьому індекс UNIQUE може містити кілька порожніх значень. Причиною цього є те, що не надається можливості порівняння двох порожніх значень, тому вони не можуть розглядатися як рівні. (Таблиці типу BDB є винятком – вони допускають наявність тільки одного порожнього значення в індексі UNIQUE.)

Оператор CREATE INDEX дозволяє додати звичайний індекс, індекс UNIQUE або FULLTEXT, але не дозволяє створити індекс PRIMARY KEY:

CREATE UNIQUE INDEX index_name ON tbl_name (index_columns);

CREATE INDEX index_name ON tbl_nam(index_columns);

CREATE FULLTEXT INDEX index_name ON tbl_name(index_columns);

де tbl_name, index_name і index_columns мають значення, аналогічне прийнятому для оператора ALTER TABLE. Але в цьому операторі необхідно вказати ім'я індексу. За допомогою одного оператора CREATE INDEX не можна створити множинний індекс.

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

СREATE TABLE tbl_name

(

…

INDEX index_name(index_columns) ,

UNIQUE index_name (index_columns) ,

PRIMARY KEY (index_columns) ,

FULLTEXT (index_columns) ,

…

);

Як і в операторі ALTER TABLE, ім'я індексу тут можна не вказувати. СУБД MySQL у такому випадку присвоїть ім'я сама.

Є один виняток, що дозволяє задавати індекс безпосередньо при оголошенні стовпця, але це можна зробити тільки для індексу PRIMARY KEY, що містить один стовпець. Починаючи із СУБД MySQL 3.23 можна поводитися подібним чином і з індексом UNIQUE:

CREATE TABLE mytbl

(

i INT NOT NULL PRIMARY KEY,

j CHAR (10) NOT NULL UNIQUE

);

Цей оператор еквівалентний наступному:

CREATE TABLE mytbl

(

i INT NOT NULL,

 j CHAR(10) NOT NULL,

PRIMARY KEY (i) ,

UNIQUE (j)

);

Всі приклади створення таблиць передбачають вказання NOT NULL для індексованих стовпців. Цього правила необхідно дотримуватися для таблиць, створених методом ISAM (і для таблиць HEAP до версії 4.0.2), де індексовані стовпці не можуть містити порожніх значень. Для таблиць інших типів індексовані стовпці можуть мати порожні значення. Але це справедливо тільки тоді, коли індекс не є індексом PRIMARY KEY.

Існує можливість індексування по префіксу стовпця стрічкового типу (по перших п символах рядка). При цьому специфікація змінюється з column_list на column_list (n). Наприклад, у цьому операторі при створенні індексу використовується тільки частина стовпця:

CREATE TABLE mytbl

(

name CHAR (30) ,

address CHAR (60) ,                                    

INDEX (name(10), address(10))

Індексування по префіксах підтримується для таблиць типу ISAM, MyІSAM, HEAP і BDB, але не підтримується для таблиць типу InnoDB.

Довжина префікса, на відміну від довжини стовпця, відноситься до байтів, а не до символів. І якщо для однобайтових кодувань це те саме, то для багатобайтових – ні. СУБД MySQL буде зберігати стільки повних символів, скільки їх туди входить. Наприклад, якщо префікс індексу має довжину 5 байтів, а стовпець містить 2-байтові символи, індекс буде містити 2-байтові символи, в індексі будуть зберігатися два символи, а не 2,5.

У деяких випадках індексування по префіксі може виявитися до речі. Довжина рядка індексу обмежена, тому для індексування по рядку, довжина якої виходить за припустимий діапазон, така можливість дуже зручна.

– Індексування по префіксу доречно при індексуванні по стовпцю типу BLOB або TEXT у таблицях типів, для яких це дозволено. Префікс може мати довжину до 255 байт.

– Довжина рядка індексу дорівнює сумарній довжині індексованих частин стовпців, що входять в індекс. Якщо ця довжина перевищує припустиму довжину індексованих рядків, можна "обмежитися" індексуванням префікса стовпця. Припустимо, що таблиця типу MyІSAM містить два стовпці типу CHAR (255), cl і с2 і необхідно виконати індексування по обох стовпцях. Таким чином, сумарна довжина рядка індексу буде становити 255+255, що перевищує обмеження, що існує для індексів таблиць типу MyІSAM, відповідно до якого довжина рядка індексу не може перевищувати 500 байтів. У цьому випадку можна створити індекс, у якому використовується тільки частина одного або обох стовпців.

Індексування по префіксі накладає певні обмеження на можливість внесення змін у довжину таких стовпців. Не можна робити їхню довжину менше довжини індексованого префікса. Це можна зробити, тільки попередньо видаливши індекс і відтворивши його потім з коротким префіксом. Наприклад, є індекс по першим 30 байтам стовпця типу CHAR, що має 40-байтовую довжину. Згодом виявилося, що більше 20 байт у цьому стовпці вам ніколи не буде потрібно, тому з метою економії простору можна зменшити довжину стовпця до 20 символів. У цьому випадку, перед тим як зменшити довжину стовпця, необхідно попередньо видалити індекс. Після цього можна створити новий індекс, у якому буде використовуватися 20 або менш байт. Стовпці в індексах типу FULLTEXT не можуть мати префіксів, і у випадку оголошення таких вони будуть зігноровані.

Видалення індексів

Видалення індексів відбувається оператором DROP INDEX або ALTER TABLE. Аналогічно операторові CREATE INDEX, оператор DROP INDEX внутрішньо обробляється як оператор ALTER TABLE. Він вперше з'явився в СУБД MySQL версії 3.22 і не може використовуватися для роботи з індексом PRIMARY KEY. Оператори видалення індексів мають такий синтаксис:

DROP INDEX index_name ON tbl_name

ALTER TABLE tbl_name DROP INDEX index_name

ALTER TABLE tbl name DROP PRIMARY KEY

Два перших оператори ідентичні. Третій використовується тільки для видалення індекса PRIMARY KEY. В останньому випадку потрібно вказувати ім'я індексу, тому що таблиця може мати тільки один індекс з таким іменем. Якщо такий індекс створений не був, а таблиця має один або кілька індексів UNIQUE, буде знищений перший з них.

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