Секреты индексов и внешних ключей
Пересказ статьи Rafaelo Condret. The Secrets of Indexes and Foreign Keys
В этой статье мы обсудим, как использовать внешние ключи и индексы, чтобы увеличить эффективность реляционной базы данных.
Индексы и внешние ключи являются замечательными инструментами, когда мы сталкиваемся с большими базами данных. Они могут стать ключом к хорошему дизайну и высокой производительности. В этой статье я дам несколько советов, которые помогли мне понять, как эффективно использовать эти инструменты, и позволили мне работать со сложными базами данных.
Типичной ошибкой является избежать создания внешних ключей в базе данных, поскольку они отрицательно влияют на производительность. Это правда, что внешние ключи оказывают влияние на операторы INSERT, UPDATE и DELETE, поскольку они вызывают проверку данных, но они улучшают общую производительность базы данных.
Основное преимущество внешних ключей заключается в том, что они обеспечивают соблюдение согласованности данных, т.е. поддерживают чистоту базы данных.
В качестве примера рассмотрим две следующих таблицы. Если во второй отсутствуют внешние ключи, и мы удалим отдел, то сотрудники, связанные с этим этим отделом останутся в таблице employee в качестве "плохих записей". Подобные сценарии наблюдаются очень часто и превращают базу данных в помойку, что снижает производительность.
Без внешних ключей:
С внешними ключами:
Индекс для базы данных подобен оглавлению книги. Поиск данных на базе столбца, который является частью индекса, позволяет нам использовать индекс для быстрого доступа к записи.
Рассмотрим таблицу employee с индексом на firstname. Выполнение нижеприведенного запроса будет использовать индекс.
Что важно знать об индексах:
Допустим мы создаем два индекса на таблице employee - idx_firstname и idx_lastname. Если мы выполним следующий запрос, база данных решит использовать только один из двух индексов.
При выполнении запроса база данных будет сравнивать индексы относительно количества различных записей, которые они содержат.
Например, если у нас имеется 2000 различных записей для firstname и 5000 различных записей для lastname, наиболее вероятно, что использование индекса lastname будет возвращать меньшее число строк, которые отвечают нашему поисковому критерию. Следовательно, база данных будет использовать этот индекс.
Индексы, которые содержат два и более столбцов, называются составными. При использовании составного индекса запрос всегда должен содержать первый столбец индекса.
Пусть, например, у нас имеется индекс idx(firstname, lastname). Этот индекс будет работать идеально для следующего запроса
Однако не будет работать для такого
Продолжая вышеприведенную аналогию, в кластеризованном индексе содержание находится в конце книги. В некластеризованном индексе содержание находится в другом месте за пределами книги. Кластеризованные индексы рекомендуются в случаях, когда количество обновлений невелико.
При создании кластеризованного индекса сама таблица становится индексом.
В левой части таблицы employees можно увидеть, что индексы имеют различные обозначения. employee_id является первичным ключом, firstname и lastname формируют составной индекс, в то время как deparment_id является уникальным индексом.
Внешние ключи весьма полезны для поддержания чистоты данных в базе и не оказывают влияния на операторы SELECT. Индексы помогают быстро просматривать базы данных для нахождения требуемых данных. Знание того, как и когда следует их использовать, улучшит производительность базы данных и сделает вашу работу легче.
Внешние ключи
Типичной ошибкой является избежать создания внешних ключей в базе данных, поскольку они отрицательно влияют на производительность. Это правда, что внешние ключи оказывают влияние на операторы INSERT, UPDATE и DELETE, поскольку они вызывают проверку данных, но они улучшают общую производительность базы данных.
Основное преимущество внешних ключей заключается в том, что они обеспечивают соблюдение согласованности данных, т.е. поддерживают чистоту базы данных.
В качестве примера рассмотрим две следующих таблицы. Если во второй отсутствуют внешние ключи, и мы удалим отдел, то сотрудники, связанные с этим этим отделом останутся в таблице employee в качестве "плохих записей". Подобные сценарии наблюдаются очень часто и превращают базу данных в помойку, что снижает производительность.
Без внешних ключей:
С внешними ключами:
Индексы
Индекс для базы данных подобен оглавлению книги. Поиск данных на базе столбца, который является частью индекса, позволяет нам использовать индекс для быстрого доступа к записи.
Рассмотрим таблицу employee с индексом на firstname. Выполнение нижеприведенного запроса будет использовать индекс.
SELECT * from employees WHERE firstname = ?Что важно знать об индексах:
Базы данных используют только один индекс на таблицу в запросе
Допустим мы создаем два индекса на таблице employee - idx_firstname и idx_lastname. Если мы выполним следующий запрос, база данных решит использовать только один из двух индексов.
SELECT * from employees WHERE firstname = ? and lastname=?Сравнение индексов
При выполнении запроса база данных будет сравнивать индексы относительно количества различных записей, которые они содержат.
Например, если у нас имеется 2000 различных записей для firstname и 5000 различных записей для lastname, наиболее вероятно, что использование индекса lastname будет возвращать меньшее число строк, которые отвечают нашему поисковому критерию. Следовательно, база данных будет использовать этот индекс.
Составные индексы
Индексы, которые содержат два и более столбцов, называются составными. При использовании составного индекса запрос всегда должен содержать первый столбец индекса.
Пусть, например, у нас имеется индекс idx(firstname, lastname). Этот индекс будет работать идеально для следующего запроса
SELECT * from employees WHERE firstname = ? and lastname=?Однако не будет работать для такого
SELECT * from employees WHERE lastname=?Кластеризованные индексы
Продолжая вышеприведенную аналогию, в кластеризованном индексе содержание находится в конце книги. В некластеризованном индексе содержание находится в другом месте за пределами книги. Кластеризованные индексы рекомендуются в случаях, когда количество обновлений невелико.
При создании кластеризованного индекса сама таблица становится индексом.
В левой части таблицы employees можно увидеть, что индексы имеют различные обозначения. employee_id является первичным ключом, firstname и lastname формируют составной индекс, в то время как deparment_id является уникальным индексом.
Заключение
Внешние ключи весьма полезны для поддержания чистоты данных в базе и не оказывают влияния на операторы SELECT. Индексы помогают быстро просматривать базы данных для нахождения требуемых данных. Знание того, как и когда следует их использовать, улучшит производительность базы данных и сделает вашу работу легче.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой