Индексы и внешние ключи являются отличными инструментами при работе с большими базами данных. Они могут быть ответом на хороший дизайн и отличную производительность. В этой статье я расскажу несколько советов, которые помогли мне понять, как эффективно использовать эти инструменты и упростить мою работу со сложными базами данных.
Каждый пример изображения был сделан с DbSchema . Мне нравится этот инструмент, потому что он ориентирован на диаграмму, объединяет множество функций и имеет очень хорошую цену.
Иностранные ключи
Распространенная ошибка — избегать создания внешних ключей в базе данных, поскольку они негативно влияют на производительность. Это правда, что внешние ключи будут влиять на операторы INSERT, UPDATE и DELETE, потому что они проверяют данные, но они улучшают общую производительность базы данных.
Основным преимуществом внешних ключей является то, что они обеспечивают согласованность данных, а это означает, что они поддерживают базу данных в чистоте.
В качестве примера рассмотрим две таблицы ниже. Если в двух из них отсутствуют внешние ключи, и мы удаляем отдел, то связанные с ним сотрудники останутся в таблице сотрудников как «неверные записи». Эти сценарии случаются очень часто и приводят к созданию ненужной базы данных, что снижает производительность.
Без внешних ключей:
С внешними ключами:
Индексы
Индекс для базы данных похож на оглавление для книги. Поиск данных по столбцу, который является частью индекса, позволит нам использовать индекс для быстрого доступа к записи.
Рассмотрим таблицу сотрудников с индексом firstname. Выполнение запроса ниже будет использовать этот индекс.
SQL
xxxxxxxxxx
1
SELECT * from employees WHERE firstname = ?
Важные вещи, которые нужно знать об индексах:
Базы данных используют только один индекс на таблицу и запрос
Допустим, мы создали два индекса для таблицы сотрудников, idx_firstnameи idx_lastname. Если мы выполним запрос ниже, база данных решит использовать только один из двух индексов.
SQL
xxxxxxxxxx
1
SELECT * from employees WHERE firstname = ? and lastname=?
Сравнение индексов
При выполнении запроса база данных будет сравнивать индексы по количеству различных записей, которые они содержат.
Например, если у нас есть 2000 различных записей для firstnameи 5000 различных записей для lastname, более вероятно, что использование lastnameиндекса вернет меньшее количество строк, которые соответствуют нашим критериям поиска. Следовательно, он будет использовать этот индекс.
Композитные индексы
Индекс, который содержит два или более столбца, является составным индексом. При использовании составного индекса запрос всегда должен содержать первый столбец индекса.
Например, рассмотрим, у нас есть индекс idx(firstname, lastname). Этот индекс будет отлично работать на следующем запросе
SQL
xxxxxxxxxx
1
SELECT * from employees WHERE firstname = ? and lastname=?
Но не будет работать для этого
SQL
xxxxxxxxxx
1
SELECT * from employees WHERE lastname=?
Кластерные индексы
Если провести аналогию с самого начала, в кластерном указателе оглавление находится в конце книги. В некластеризованном индексе оглавление находится в другом месте вне книги. Кластерные индексы рекомендуются, когда у вас меньше обновлений в ваших данных.
При создании кластеризованного индекса сама таблица становится индексом.
В левой части таблицы сотрудников мы видим, как индексы имеют разные обозначения. employee_idЯвляется первичным ключом, firstnameи lastnameобразуют составной индекс, в то время как deparment_idуникальный индекс.
Заключение
Внешние ключи очень полезны для поддержания чистоты базы данных и не влияют на операторы SELECT. Индексы помогают вам быстро просматривать базу данных и находить нужные вам данные. Знание того, как и когда их использовать, улучшит производительность базы данных и облегчит вашу работу.