Материал: Письменные лекции по дисциплине «Базы данных»
5.2.1. Условия обеспечения целостности данных при помощи внешнего ключа
1.Для использования ограничений внешнего ключа типы обоих таблиц должны быть InnoDB.
2.Типы родительского и дочернего полей должны быть абсолютно идентичными.
3.Связь по внешнему ключу опирается на индексы, поэтому и родительское, и дочернее поля должны содержать индексы.
4.Связь по внешнему ключу должна опираться на поле с ограничениями в родительской таблице, т. е. это поле должно содержать ограничения PRIMARY KEY или UNIQUE.
5.2.2. Практический пример
Теперь мы можем создать 2 таблицы с использованием ограничения по внешнему ключу.
Создаем таблицу городов:
CREATE TABLE country
(
country_id TINYINT UNSIGNED AUTO_INCREMENT NOT NULL, country_name VARCHAR(50) NOT NULL,
PRIMARY KEY (country_id)
) ENGINE=InnoDB;
Создаем таблицу (справочник) стран:
CREATE TABLE city
(
city_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, city_name VARCHAR(50) NOT NULL,
country_id TINYINT UNSIGNED NOT NULL, PRIMARY KEY (city_id),
INDEX ixCity (country_id),
CONSTRAINT country_city FOREIGN KEY (country_id) REFERENCES country (country_id)
) ENGINE=InnoDB;
Теперь давайте разберем указанные правила с учетом запросов. Поле с внешним ключом — это «city.country_id» (для того, чтобы не запутаться, перед названием поля мы указали через точку имя таблицы).
Родительское поле |
(на |
которое ссылается |
внешний ключ) |
— |
это |
||||||
«country.country |
_id». |
|
|
|
|
|
|
||||
1. Для использования ограничений внешнего ключа типы обоих |
|||||||||||
таблиц должны быть InnoDB. |
|
|
|
|
|
||||||
Как |
видим, |
|
для |
обеих |
таблиц |
указан одинаковый |
тип |
— |
|||
«ENGINE=InnoDB |
». |
|
|
|
|
|
|
|
|
||
2. Типы родительского и дочернего полей должны быть абсолютно |
|||||||||||
идентичными. |
|
|
|
|
|
|
|
|
|
|
|
Это |
правило |
также |
выполняется. |
Тип дочернего |
поля |
||||||
«city.country |
_id» |
— |
«TINYINT |
UNSIGNED». Тип |
родителя |
||||||
«country.country |
_id» — также «TINYINT |
UNSIGNED». |
|
|
|||||||
3.Связь по внешнему ключу опирается на индексы, поэтому и родительское, и дочернее поля должны содержать индексы. Здесь также все ок. Дочернее поле мы индексируем, добавляя индекс в запросе — «INDEX ixCity (country_id)». Родитель индексируется автоматически. Дело в том, что для родителя мы имеем PRIMARY KEY, что как раз и предусматривает автоматический индекс для него.
4.Связь по внешнему ключу должна опираться на поле с ограничениями в родительской таблице, т. е. это поле должно содержать ограничения PRIMARY KEY или UNIQUE. И здесь все выполняется:
«country.country_id» — «PRIMARY KEY (country_id)».
Отлично! Таблицы созданы и связь установлена. Теперь мы не сможем добавить город с идентификатором страны, которая не представлена в справочнике. Кстати, существует и обратная зависимость.
По умолчанию мы не сможем также удалить и страну из справочника, если для нее в дочерней таблице есть города, что, в принципе, логично.
Но мы можем улучшить эту связь и сделать так, чтобы если мы что-то меняем в справочнике, то эти изменения касались также дочерней таблицы. Для этого после объявления ограничения по внешнему ключу мы можем записать выражения «ON DELETE CASCADE ON UPDATE CASCADE».
По умолчанию значения этих выражений указаны «ON DELETE RESTRICT ON UPDATE RESTRICT», т. е. запрет. Таким образом, наш запрос станет таким:
CREATE TABLE city
(
city_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, city_name VARCHAR(50) NOT NULL,
country_id TINYINT UNSIGNED NOT NULL, PRIMARY KEY (city_id),
INDEX ixCity (country_id),
CONSTRAINT country_city FOREIGN KEY (country_id) REFERENCES country (country_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
Теперь, если мы удалим из справочника страну, то из дочерней таблицы без всяких дополнительных запросов будут удалены все города этой страны. Удобно, не правда ли? И не нарушается целостность данных.
5.2.3. Синтаксис объявления внешнего ключа
Общий синтаксис установки внешнего ключа на уровне таблицы:
[CONSTRAINT имя_ограничения]
FOREIGN KEY (столбец1, столбец2, ... столбецN)
REFERENCES главная_таблица (столбец_главной_таблицы1,
столбец_главной_таблицы2, ... столбец_главной_таблицыN)
[ON DELETE действие]
[ON UPDATE действие]
Для создания ограничения внешнего ключа после FOREIGN KEY
указывается столбец таблицы, который будет представлять внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE
и ON UPDATE, которые задают действие при удалении и обновлении строки из главной таблицы соответственно.
5.3. Индекс Индекс — это специфический объект базы данных, позволяющий
значительно повысить скорость поиска значений из таблиц базы данных. MySQL Index представляет из себя структуру, в которой хранятся значения одного (в некоторых случаях — нескольких) столбца таблицы и ссылок на строки, где эти значения расположены. Так как для хранения индексов чаще всего используются бинарные деревья, поиск среди них занимает чрезвычайно мало места.
Индексы создаются для повышения производительности поиска данных. Таблицы могут иметь огромное количество строк, которые хранятся в произвольном порядке. Без индекса поиск нужных строк идёт по порядку (последовательно), что на больших объемах данных отнимает много времени.
5.3.1. Для каких полей нужно создавать индексы
Индексы, прежде всего, нужно создавать по тем полям, которые часто попадают в условие «WHERE» ваших sql-запросов.
Например, таблица с товарами имеет следующую структуру:
id |
product_name |
cat_id |
price |
Для формирования страниц, к примеру, вам часто приходится делать запросы для поиска товаров одной категории:
SELECT id, product_name FROM products WHERE cat_id = '5'
В этом случае для оптимизации запросов целесообразно создать индекс для поля cat_id. Первое поле — id всегда имеет уникальное значение и для него целесообразно создать «первичный ключ» (Primary Key).
5.3.2. Принцип работы индексов
Для примера рассмотрим запрос:
SELECT Name FROM Persons WHERE Points < 10
У нас имеется таблица Persons, в которой есть два поля: Name и Points. При поиске в неиндексированной таблице, система последовательно перебирает все строки и сравнивает их с 10. При выполнении условия — выводит их. Даже при наличии в таблице всего 1000 записей, такой запрос может оказаться ресурсоемким, если одновременно поступит от 100 абонентов. Индексирование таблиц MySQL позволяет сократить число операций. Если поле Points будет проиндексировано, поиск будет проводиться по самому индексу, без перебора всех строк таблицы.
5.3.3. Виды индексов
1.Простые. Одному полю таблицы соответствует один индекс.
2.Составные. Один индекс соответствует нескольким полям
таблицы.
3.Покрывающий. Этот вид индексов создается специально под определенный запрос и включает в себя все поля таблицы, фигурирующие
взапросе.
5.3.4.Индексирование таблиц MySQL
Использование индексирования таблиц MySQL имеет свои особенности:
1. Индексировать есть смысл только определенные поля таблицы. Нередки случаи, когда неопытные пользователи индексируют всю БД. На практике такой шаг может привести к обратному результату: в MySQL