Когда следует использовать первичный ключ или индекс?

По сути, первичный ключ — это (на уровне реализации) особый тип индекса. Конкретно:

• У таблицы может быть только один первичный ключ, и, за очень немногими исключениями, он должен быть у каждой таблицы.
• Первичный ключ неявно UNIQUE — у вас не может быть более одной строки с одним и тем же первичным ключом, поскольку его цель — однозначно идентифицировать строки.
• Первичный ключ никогда не может быть NULL, поэтому строки, из которых он состоит, должны быть NOT NULL.

Таблица может иметь несколько индексов, и индексы не обязательно UNIQUE. Индексы существуют по двум причинам:

• Чтобы применить ограничение уникальности (их можно создать неявно, когда вы объявляете столбец UNIQUE)
• Для повышения производительности. Сравнение на равенство или "больше/меньше" в предложениях WHERE, а также JOIN выполняется намного быстрее для столбцов, имеющих индекс. Но обратите внимание, что каждый индекс снижает производительность обновления/вставки/удаления, поэтому вы должны иметь их только там, где они действительно необходимы.

Отличия

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

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

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

Когда что использовать

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

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

Что лучше?

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

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

Добавляйте индексы по своему личному опыту или если производительность снижается. Измерьте разницу, и если вы работаете с SQL Server, научитесь читать планы выполнения.

Это может помочь Назад к основам: разница между первичным ключом и уникальным индексом

Различия между ними:

1. Столбцы, которые составляют первичный ключ таблицы, не могут быть NULL, поскольку по определению первичный ключ не может быть NULL, поскольку он помогает однозначно идентифицировать запись в таблице. Столбцы, составляющие уникальный индекс, могут иметь значение NULL. Здесь стоит упомянуть, что разные СУБД обрабатывают это по-разному — в то время как SQL Server и DB2 не допускают более одного значения NULL в уникальном столбце индекса, Oracle допускает несколько значений NULL. Это одна из вещей, на которые следует обращать внимание при проектировании/разработке/переносе приложений в РСУБД.
2. В таблице может быть определен только один первичный ключ, тогда как в таблице может быть определено множество уникальных индексов (при необходимости).
3. Кроме того, в случае SQL Server, если вы используете параметры по умолчанию, первичный ключ создается как кластеризованный индекс, а уникальный индекс (ограничение) создается как некластеризованный индекс. Это просто поведение по умолчанию, и при необходимости его можно изменить во время создания.

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

Различие между ними иногда размыто, потому что часто для указания ограничений и индексов используется похожий или идентичный синтаксис. Многие СУБД создают индекс по умолчанию при создании ключевых ограничений. Возможность путаницы между ключом и индексом досадна, потому что разделение логических и физических задач является очень важным аспектом управления данными.

Что касается «первичных» ключей. Они не являются «особым» типом ключа. Первичный ключ — это просто любой ключ-кандидат таблицы. В большинстве СУБД SQL существует как минимум два способа создания ключей-кандидатов: либо использование ограничения PRIMARY KEY, либо использование ограничения UNIQUE для столбцов NOT NULL. Очень широко распространено соглашение о том, что каждая таблица SQL имеет ограничение PRIMARY KEY. Использование ограничения PRIMARY KEY является общепринятым и вполне разумным решением, но обычно оно не имеет практического или логического значения, поскольку большинство СУБД рассматривают все ключи как равные. Конечно, каждая таблица должна применять по крайней мере один ключ-кандидат, но обычно не имеет значения, применяются ли эти ключи ограничениями PRIMARY KEY или UNIQUE. В принципе важны ключи-кандидаты, а не «первичные» ключи.

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

Индекс — это, по сути, словарь для поля или набора полей. Когда вы просите базу данных найти запись, в которой какое-то поле равно определенному значению, она может искать в словаре (индексе) нужные строки. Это очень быстро, потому что, как и в словаре, записи сортируются в индексе, что позволяет выполнять бинарный поиск. Без индекса база данных должна читать каждую строку в таблице и проверять значение.

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

Например, если у вас есть индекс для столбцов (A, B, C), этот индекс можно использовать, даже если вы фильтруете только по A, потому что это первый столбец в индексе. Точно так же его можно использовать, если вы фильтруете как по A, так и по B. Однако его нельзя использовать, если вы фильтруете только по B или C, потому что они не являются префиксом в списке столбцов - вам нужен другой индекс, чтобы разместить это .

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

Каждая таблица должна иметь PRIMARY KEY.

Разумный выбор ИНДЕКС ускоряет выполнение многих типов запросов. Возможно лучший индекс — это первичный ключ. Я хочу сказать, что запрос является основным фактором, влияющим на использование PK для его индекса.