SQL: что именно делают первичные ключи и индексы?

Недавно я начал разрабатывать свое первое серьезное приложение, которое использует базу данных SQL, и я использую phpMyAdmin для настройки таблиц. Есть пара дополнительных "функций", которые я могу дать различным столбцам, и я не совсем уверен, что они делают:

Основной ключ
Индекс

Я знаю, для чего нужен ПК и как его использовать, но я думаю, что мой вопрос в этом отношении - вот почему нужен один - как он отличается от простого указания столбца на "Уникальный", кроме факта что у вас может быть только один ПК? Это просто, чтобы программист знал, что это значение однозначно идентифицирует запись? Или у него есть некоторые специальные свойства?

Я понятия не имею, что делает "Index" - на самом деле, только раз, когда я когда-либо видел его в использовании, (1), что мои первичные ключи, кажется, индексируются, и (2) я слышал, что индексирование каким-то образом связанные с производительностью; что вам нужны индексированные столбцы, но не слишком много. Как определить, какие столбцы индексировать и что именно он делает?

изменить:, если один индексный столб, который, вероятно, захочет ORDER BY?

Большое спасибо,

Мала

Ответ 1

Основной ключ обычно используется для создания числового "идентификатора" для ваших записей, и этот столбец идентификатора автоматически увеличивается.

Например, если у вас есть таблица books с полем id, где id является первичным ключом, а также имеет значение auto_increment (в разделе "Экстра в phpmyadmin" ), то при первом добавьте книгу в таблицу, id для нее станет 1 '. Следующий идентификатор книги будет автоматически "2" и так далее. Как правило, каждая таблица должна иметь как минимум один первичный ключ, чтобы легко идентифицировать и находить записи.

Индексы используются, когда вам нужно регулярно получать определенную информацию из таблицы. Например, если у вас есть таблица users, и вам нужно будет получить доступ к столбцу email, то вы можете добавить индекс по электронной почте, и это приведет к тому, что запросы, получающие электронное письмо, будут быстрее.

Однако есть и недостатки для добавления ненужных индексов, поэтому добавьте это только в столбцы, на которые действительно нужно получить доступ больше, чем другие. Например, запросы UPDATE, DELETE и INSERT будут немного медленнее, чем больше индексов у вас, так как MySQL должен хранить дополнительную информацию для каждого индексированного столбца. Более подробную информацию можно найти на этой странице.

Изменить: Да, столбцы, которые должны использоваться в ORDER BY, должны иметь индексы, а также те, которые используются в WHERE.

Ответ 2

Первичный ключ - это, в основном, уникальный индексированный столбец, который действует как "официальный" идентификатор строк в этой таблице. Самое главное, что он обычно используется для отношений с внешними ключами, то есть если другая таблица ссылается на строку в первой, она будет содержать копию первичного ключа этой строки.

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

Индексы улучшают время поиска. Они обычно основаны на деревьях, поэтому для поиска определенной строки с помощью индекса требуется время O (log (n)), а не просмотр всей таблицы.

Как правило, любой столбец в большой таблице, который часто используется в предложениях WHERE, ORDER BY или (особенно) JOIN, должен иметь индекс. Поскольку индекс необходимо обновить для evey INSERT, UPDATE или DELETE, он замедляет эти операции. Если у вас мало записей и много чтений, а затем проиндексируйте свой контент для прослушивания. Если у вас есть как много записей, так и множество запросов, для которых потребуются индексы для многих столбцов, у вас есть большая проблема.

Ответ 3

Различие между первичным ключом и уникальным ключом лучше всего объясняется с помощью примера.

У нас есть таблица пользователей:

USER_ID number 
NAME varchar(30)
EMAIL varchar(50)

В этой таблице USER_ID является первичным ключом. ИМЯ не уникально - в мире много Джона Смита и Мухаммеда Ханов. EMAIL обязательно уникальна, иначе глобальная система электронной почты не будет работать. Поэтому мы помещаем уникальное ограничение на EMAIL.

Зачем нам нужен отдельный первичный ключ? Три причины:

цифровой ключ более эффективен при использовании во внешнем ключе отношения, поскольку это занимает меньше места
электронное письмо может меняться (например, swapping), но пользователь все такой же; колебание изменения значение первичного ключа во всей схеме всегда кошмар.
Это всегда плохая идея использовать чувствительной или частной информации как внешний ключ

Ответ 4

В реляционной модели любой столбец или набор столбцов, которые гарантированно присутствуют и уникальны в таблице, можно назвать ключом-кандидатом к таблице. "Present" означает "NOT NULL". Общей практикой в дизайне базы данных является назначение одного из ключей-кандидатов в качестве первичного ключа и использование ссылок на первичный ключ для ссылки на всю строку или на предмет предмета, который описывает строка.

В SQL ограничение PRIMARY KEY составляет ограничение NOT NULL для каждого столбца первичного ключа и ограничение UNIQUE для всех столбцов первичного ключа, взятых вместе. На практике многие первичные ключи оказываются одиночными столбцами.

Для большинства продуктов СУБД ограничение PRIMARY KEY также приведет к автоматическому созданию индекса на столбцах первичного ключа. Это ускоряет работу системной проверки при создании новых записей для первичного ключа, чтобы убедиться, что новое значение не дублирует существующее значение. Он также ускоряет поиск на основе значения первичного ключа и соединяется между первичным ключом и внешним ключом, который ссылается на него. Сколько ускорений происходит, зависит от того, как работает оптимизатор запросов.

Первоначально разработчики реляционных баз данных искали естественные ключи в данных, как указано. В последние годы тенденция заключалась в том, чтобы всегда создавать столбец с именем ID, целое число как первый столбец и первичный ключ каждой таблицы. Функция автогенерации СУБД используется для обеспечения уникальности этого ключа. Эта тенденция зафиксирована в "Стандартах проектирования Осло". Это не обязательно реляционный дизайн, но он служит некоторым непосредственным потребностям людей, которые следуют за ним. Я не рекомендую эту практику, но я признаю, что это распространенная практика.

Индекс - это структура данных, которая позволяет быстро получить доступ к нескольким строкам в таблице на основе описания столбцов индексируемой таблицы. Индекс состоит из копий определенных столбцов таблицы, называемых индексными ключами, вкрапленных указателями на строки таблицы. Указатели обычно скрыты от пользователей СУБД. Индексы работают в тандеме с оптимизатором запросов. Пользователь указывает в SQL, какие данные запрашиваются, а оптимизатор разрабатывает стратегии индексирования и другие стратегии для перевода того, что ищут в стратегию для его поиска. Существует какой-то организационный принцип, такой как сортировка или хеширование, который позволяет использовать индекс для быстрого поиска и некоторых других применений. Все это внутренне относится к СУБД, как только создатель базы данных создал индекс или объявил первичный ключ.

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