Ход работы. «Использование индексов и обеспечение целостности данных в MySQL»

Практическая работа № 4

«Использование индексов и обеспечение целостности данных в MySQL»

Цель: повышение производительности базы данных за счет индексирования и обеспечения целостности базы данных в MySQL.

Задание:

1. Использование индексов и просмотр их свойств

2. Создание связей между таблицами базы данных

3. Удаление связей между таблицами базы данных

Задание на самостоятельную работу

4. Оформить отчет по практической работе и подготовиться к ее защите.

Содержание отчета

1. Тема, цель работы, задание

2. Описание выполняемых команд

3. Описание полученных результатов

Пример выполнения задания.

Существует несколько видов индексов:

- первичный ключ – главный индекс таблицы. В таблице может быть только один первичный ключ, и все значения такого индекса должны отличаться друг от друга, т.е. являться уникальными;

- обычный индекс – таких индексов может быть несколько;

- уникальный индекс – уникальных индексов также может быть несколько, но значения индекса не должны повторяться.

Индексы могут иметь как собственные имена, так и имена индексируемых столбцов. Один индекс может охватывать один или несколько столбцов, причем тип столбца может быть любым, за исключением ENUM И SET.

4.1 Использование индексов и просмотр их свойств

4.1.1 Создание первичного ключа

Первичный ключ является главным индексом таблицы и объявляется при помощи ключевого слова PRIMARY KEY. В SQL существует несколько способов создания первичного ключа:

– создание первичного ключа при создании таблицы в описании полей;

– создание первичного ключа после описания полей;

– создание первичного ключа для текстовых полей;

– создание первичного ключа для нескольких столбцов;

– создание первичного ключа в уже созданную таблицу базы данных.

На рисунке 4.1 отображено создание таблицы базы данных Объекты (objects) с созданием первичного ключа в описании полей. AUTO_INCREMENT – при вставке новой записи в таблицу поле с этим атрибутом автоматически получит числовое значение, на 1 больше самого большого значения для этого поля в текущий момент времени. Данная возможность обычно используется для генерирования уникальных идентификаторов строк. Столбец, для которого применяется атрибут AUTO_INCREMENT, должен иметь целочисленный тип. В таблице может быть только один столбец с атрибутом AUTO_INCREMENT. Так же этот столбец должен быть проиндексирован. Отсчет последовательности чисел для AUTO_INCREMENT начинается с 1. Это могут быть только положительные числа.

Рисунок 4.1 – Создание таблицы Объекты (objects) с созданием первичного ключа в описании полей

На рисунке 4.2 отображен результат создания таблицы Объекты (objects), где в колонке Key результирующей таблицы напротив поля kod_obj появился флаг PRI, который отмечает поле первичного ключа.

Рисунок 4.2 – Структура таблицы Объекты (objects)

Создание первичного ключа после описания полей таблицы Назначение (purpose) отображено на рисунке 4.3.

Рисунок 4.3 – Создание первичного ключа после описания полей таблицы Назначение (purpose)

Структура таблицы Назначение (purpose) отображена на рисунке 4.4.

Рисунок 4.4 – Структура таблицы Назначение (purpose)

Если в качестве первичного ключа выступает текстовое поле, то нужно указывать в скобках количество символов участвующих в индексации. Создание первичного ключа для текстового поля таблицы Специальность (Specialty) отображено на рисунке 4.5.

Рисунок 4.5 – Создание первичного ключа для текстового поля таблицы Специальность (Specialty)

Как видно из рисунка 4.5, первичный ключ создается по первым 10 символам столбца name_spec. Можно индексировать от 1 до 1000 символов текстового столбца. Следует помнить, что индексирование по одному символу приведет к тому, что уникальные символы быстро исчерпаются, и будет невозможно добавить новую запись в таблицу. Индексирование по большому числу символов приведет к резкому увеличению объема памяти, необходимого для хранения таблицы.

Структура таблицы Специальность (Specialty) отображена на рисунке 4.6.

Рисунок 4.6 – Структура таблицы Специальность (Specialty)

Создание первичного ключа для нескольких столбцов в таблице Прием (Reception) отображено на рисунке 4.7, а структура таблицы – на рисунке 4.8.

Рисунок 4.7 – Создание первичного ключа для текстового поля таблицы Прием (Reception)

Рисунок 4.8 – Структура таблицы Прием (Reception)

Создание первичного ключа в уже созданную таблицу базы данных Рабочие (working) отображено на рисунке 4.9, а структура таблицы на рисунке 4.10.

Рисунок 4.9 – Создание первичного ключа для текстового поля таблицы Рабочие (working)

Рисунок 4.10 – Структура таблицы Рабочие (working)

4.1.2 Создание обычного и уникального индекса

Кроме первичного индекса таблица также может содержать обычные и уникальные индексы. В отличии от первичного индекса этих индексов может быть несколько. Отличие обычного индекса от уникального состоит в том, что в уникальном индексе не может быть одинаковых значений.

Объявление обычных индексов осуществляется при помощи ключевых слов key или index. Если же нужно объявить уникальный индекс, то перед key или index ставится ключевое слово unique.

Использование индексов при создании таблицы базы данных отображено на рисунке 4.11, а структура таблицы – на рисунке 4.12.

Рисунок 4.11 – Создание таблицы Прием (Reception) с использованием индексов

Рисунок 4.12 – Структура таблицы Прием (Reception) с использованием индексов

Проиндексированные обычным индексом, помечаются в поле Key флагом MUL.

Создание индекса в уже созданной таблице базы данных Назначение (purpose) отображено на рисунке 4.13. Структура таблицы базы данных Назначение (purpose) отображена на рисунке 4.14.

Рисунок 4.13 – Создание индекса в уже созданной таблице базы данных Назначение (purpose)

Рисунок 4.14 – Структура таблицы Назначение (purpose) с использованием уникального индекса

Объявление уникальных индексов в таблице Назначение (purpose) приводит к тому, что проиндексированные столбцы помечаются флагом UNI.

Если необходимо создать индекс по нескольким столбцам, они перечисляются в круглых скобках через запятую.

4.1.3 Удаление индексов

Удаление созданных индексов осуществляется при помощи команды DROP INDEX. В случаях необходимости удаления первичного ключа используется команда ALTER TABLE.

Удаление индексов в таблице Прием (Reception) отображено на рисунке 4.15.

Рисунок 4.15 – Удаление индексов в таблице Прием (Reception)

Результат выполнения команды DROP INDEX отображен на рисунке 4.16.

Рисунок 4.16 – Структура таблицы Прием (Reception) после удаления индексов

Удаление первичного ключа из таблицы базы данных Специальность (Specialty) отображено на рисунке 4.16.

Рисунок 4.16 – Удаление первичного ключа в таблице Специальность (Specialty)

Структура таблицы Специальность (Specialty) после удаления первичного ключа отображена на рисунке 4.17.

Рисунок 4.17 – Структура таблицы Специальность (Specialty) после удаления первичного ключа

4.2 Создание связей между таблицами базы данных

Целостность данных – важное свойство SQL как и всех баз данных. При правильном использовании оно обеспечивает корректность и валидность хранимых данных в любой момент времени. Также, с их помощью можно обнаруживать ошибки в приложениях, которые тяжело найти другими способами. Целостность данных поддерживается с помощью ограничений.

Ограничение FOREIGN KEY защищает от действий, которые могут нарушить связи между таблицами. FOREIGN KEY в одной таблице указывает на PRIMARY KEY в другой. Поэтому данное ограничение нацелено на то, чтобы не было записей FOREIGN KEY, которым не отвечают записи PRIMARY KEY. Таким образом, FOREIGN KEY поддерживает ссылочную целостность данных.

Рассмотрим структуру таблицы Назначение (purpose).

В таблице поле ind_kod является внешним ключом, так как оно указывает на поле ind_kod таблицы Рабочие (working), где это поле является первичным ключом. Аналогично поле kod_obj указывает на поле kod_obj таблицы Объекты (objects). Таким образом, для установки связей между таблицами необходимо воспользоваться командой:

FOREIGN KEY <column list> REFERENCES <pktable> [ <column list> ]

Первый список столбцов (column list) – это список из одного или более столбцов таблицы, которые отделены запятыми и будут созданы или изменены этой командой.

Pktable – это таблица, содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой.

Второй список столбцов – это список столбцов, которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы, т.е.:

– иметь одинаковое число столбцов;

– в данной последовательности, первый, второй, третий, и т.д., столбцы списка столбцов внешнего ключа, должны иметь одинаковые типы данных и размеры, что и первый, второй, третий, и т.д., столбцы списка столбцов родительского ключа.

Таким образом, команда создания таблицы Назначение (purpose) будет иметь вид (рисунок 4.18):

Рисунок 4.18 – Команда создания таблицы Назначение (purpose) с установкой связей между таблицами

Если таблицы базы данных уже созданы, то установить связь можно при помощи команды Alter Table (рисунок 4.19).

Рисунок 4.19 – Установка связей при помощи команды Alter Table

На рисунке 4.19 отображено создание связей таблицы Прием (Reception) по полю ind_kod с таблицей Рабочие (working) и по полю kod_spec с таблицей Специальность (Specialty).

4.3. Удаление связей между таблицами базы данных

Прежде чем удалять связь необходимо узнать фактическое имя ограничения для связей при помощи команды SHOW CREATE TABLE.

Например, для просмотра фактического имени ограничения таблицы reception необходимо выполнить команду SHOW CREATE TABLE Reception. Результат выполнения отображен на рисунке 4.20.

Рисунок 4.20 – Результат отображения команды

Show Create Table Reception

На рисунке фактические имена ограничения указаны после предложения CONSTRAINT:

– reception_ibfk_1 – имя ограничения для связи с таблицей working;

– reception_ibfk_2 – имя ограничения для связи с таблицей specialty.

После того как стало известно имя ограничения необходимо выполнить команду:

Alter Table имя_таблицы Drop Foreign Key имя_ограничения

Для удаления связи таблицы reception с таблицей specialty, необходимо выполнить команду, которая отображена на рисунке 4.21.

Рисунок 4.21 – Команда удаления связи

После повторного выполнения команды Show Create Table Reception, можно убедиться в удалении связи (рисунок 4.22).

Рисунок 4.22 – Результат удаления связи

Дата добавления: 2015-05-09