Различия между INDEX, PRIMARY, UNIQUE, FULLTEXT в MySQL?

Question

Каковы различия между PRIMARY, UNIQUE, INDEX и FULLTEXT при создании таблиц MySQL?

Как бы я их использовал?

Answer 1

Различия

• KEY или INDEX относится к обычному неуникальному индексу. Недопустимые значения для индекса допускаются, поэтому индекс может содержать строки с одинаковыми значениями во всех столбцах индекса. Эти индексы не налагают никаких ограничений на ваши данные, поэтому они используются только для того, чтобы убедиться, что определенные запросы могут выполняться быстро.

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

  Ваша система баз данных может разрешить применение индекса UNIQUE к столбцам, которые допускают значения NULL, и в этом случае две строки могут быть идентичными, если они обе содержат значение NULL (обоснование здесь заключается в том, что NULL считается не равным себе). Однако в зависимости от вашего приложения это может оказаться нежелательным: если вы хотите предотвратить это, вам следует запретить значения NULL в соответствующих столбцах.

• PRIMARY действует точно так же, как индекс UNIQUE, за исключением того, что он всегда называется "PRIMARY", и в таблице может быть только один (и всегда должен быть один; хотя некоторые системы баз данных не применяют это). Индекс PRIMARY предназначен в качестве основного средства для уникальной идентификации любой строки в таблице, поэтому в отличие от UNIQUE его не следует использовать ни в каких столбцах, которые допускают значения NULL. Ваш ПЕРВИЧНЫЙ индекс должен указывать наименьшее количество столбцов, достаточных для однозначной идентификации строки. Часто это только один столбец, содержащий уникальное автоматически увеличиваемое число, но если есть что-то еще, что может однозначно идентифицировать строку, например, "код страны" в списке стран, вы можете использовать это вместо этого.

  Некоторые системы баз данных (например, InnoDB MySQL) будут хранить записи таблицы на диске в том порядке, в котором они появляются в индексе PRIMARY.

• ИндексыFULLTEXT отличаются от всего вышеперечисленного, и их поведение значительно отличается в разных системах баз данных. Индексы FULLTEXT полезны только для полнотекстового поиска, выполняемого с помощью предложения MATCH() / AGAINST(), в отличие от трех предыдущих, которые обычно реализуются внутренне с использованием b-деревьев (позволяющих выбирать, сортировать или диапазоны, начиная с самого левого столбца) или хеш-таблицы (с возможностью выбора, начиная с самого левого столбца).

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

сходства

• Все эти индексы могут иметь более одного столбца.

• За исключением FULLTEXT, порядок столбцов является существенным: для индекса, который будет полезен в запросе, запрос должен использовать столбцы из индекса, начинающегося слева - он не может использовать только вторую, третью или четвертую часть index, если только он не использует предыдущие столбцы в индексе для соответствия статическим значениям. (Чтобы индекс FULLTEXT был полезен для запроса, запрос должен использовать все столбцы индекса.)

Answer 2

Все это виды показателей.

primary: должен быть уникальным, это индекс, (вероятно) физический индекс, может быть только один на таблицу.

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

index: если он не является основным или уникальным, он не ограничивает значения, вставленные в таблицу, но позволяет более эффективно искать их.

fulltext: более специализированная форма индексации, позволяющая осуществлять полнотекстовый поиск. Думайте об этом как о (по существу) создании "индекса" для каждого "слова" в указанном столбце.

Answer 3

Я чувствую, что это было хорошо покрыто, возможно, за исключением следующего:

• просто KEY / INDEX (или иначе называется SECONDARY INDEX) увеличить производительность, если селективность достаточна. В этом случае обычная рекомендация состоит в том, что если количество записей в наборе результатов, к которым применяется индекс, превышает 20% от общего количества записей родительской таблицы, то индекс будет неэффективным. На практике каждая архитектура будет отличаться, но идея по-прежнему верна.

• Вторичные индексы (и это очень специфично для mysql) не должны рассматриваться как полностью отдельные и отличные объекты от первичного ключа. Фактически, оба должны использоваться совместно и, как только эта информация станет известна, предоставить дополнительный инструмент для администратора баз данных mysql: в Mysql индексы внедряют первичный ключ. Это приводит к значительному повышению производительности, особенно при умном построении неявных индексов покрытия, таких как описанные там.

• Если вы чувствуете, что ваши данные должны быть UNIQUE, используйте уникальный индекс. Вы можете подумать, что это необязательно (например, работа с ним на уровне приложения) и что подойдет нормальный индекс, но на самом деле он дает гарантию для Mysql, что каждая строка уникальна, что, кстати, обеспечивает выигрыш в производительности.

• Вы можете использовать только FULLTEXT (или иначе называется SEARCH INDEX) с Innodb (в MySQL 5.6.4 и выше) и Myisam Engines

• Вы можете использовать только FULLTEXT на CHAR, VARCHAR а также TEXT типы столбцов
• FULLTEXT Индекс включает в себя гораздо больше, чем просто создание индекса. Создана куча системных таблиц, совершенно отдельная система кеширования и применены некоторые конкретные правила и оптимизации. См. http://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html и http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

Answer 4

ПЕРВИЧНЫЙ

Уникальный индекс, где все ключевые столбцы должны быть определены как NOT NULL. Если они явно не объявлены как NOT NULL, MySQL объявляет их так неявно (и молча). Таблица может иметь только один ПЕРВИЧНЫЙ КЛЮЧ. Имя PRIMARY KEY всегда является PRIMARY, поэтому его нельзя использовать в качестве имени для любого другого вида индекса.

Если у вас нет PRIMARY KEY и приложение запрашивает PRIMARY KEY в ваших таблицах, MySQL возвращает первый индекс UNIQUE, в котором нет столбцов NULL, в качестве PRIMARY KEY.

В таблицах InnoDB оставьте PRIMARY KEY коротким, чтобы минимизировать издержки хранения для вторичных индексов. Каждая запись вторичного индекса содержит копию столбцов первичного ключа для соответствующей строки.

В созданной таблице сначала размещается ПЕРВИЧНЫЙ КЛЮЧ, за которым следуют все уникальные индексы, а затем неуникальные индексы. Это помогает оптимизатору MySQL определять приоритетность используемого индекса, а также быстрее обнаруживать дублированные ключи UNIQUE.

PRIMARY KEY может быть индексом из нескольких столбцов. Однако вы не можете создать многостолбцовый индекс, используя атрибут ключа PRIMARY KEY в спецификации столбца. Это помечает только один столбец как основной. Вы должны использовать отдельное предложение PRIMARY KEY (index_col_name,...).

Если PRIMARY KEY состоит только из одного столбца с целочисленным типом, вы также можете ссылаться на этот столбец как _rowid в инструкциях SELECT.

УНИКАЛЬНАЯ

Индекс UNIQUE создает такое ограничение, что все значения в индексе должны быть разными. При попытке добавить новую строку со значением ключа, совпадающим с существующей строкой, возникает ошибка. Для всех механизмов индекс UNIQUE разрешает несколько значений NULL для столбцов, которые могут содержать NULL. Если вы задаете значение префикса для столбца в индексе UNIQUE, значения столбца должны быть уникальными в пределах префикса. Если индекс UNIQUE состоит только из одного столбца с целочисленным типом, вы также можете ссылаться на этот столбец как _rowid в инструкциях SELECT.

КЛЮЧ | ИНДЕКС

KEY обычно является синонимом INDEX. ссылается на обычный неуникальный индекс.

ПОЛНЫЙ ТЕКСТ

Индекс FULLTEXT - это особый тип индекса, используемый для полнотекстового поиска. Только механизмы хранения InnoDB и MyISAM поддерживают индексы FULLTEXT. Они могут быть созданы только из столбцов CHAR,VARCHAR и TEXT. Индексирование всегда происходит по всему столбцу; Индексирование префикса столбца не поддерживается, и любая длина префикса игнорируется, если указано. Предложение WITH PARSER может быть указано как значение anindex_option, чтобы связать плагин синтаксического анализатора с индексом, если полнотекстовая индексация и операции поиска требуют специальной обработки. Это предложение действительно только для индексов FULLTEXT.InnoDB и MyISAM поддерживают плагины полнотекстового анализатора.