индексы mysql 5.0 – Уникальные против уникальных

UNIQUE и PRIMARY KEY – это ограничения , а не индексы. Хотя большинство баз данных реализуют эти ограничения, используя индекс. Дополнительные накладные расходы ограничения в дополнение к индексу несущественны, особенно если вы подсчитаете стоимость отслеживания и исправления непреднамеренных дубликатов, когда (а не если) они возникают.

Индексы обычно более эффективны, если у вас высокая избирательность . Это отношение количества различных значений к общему числу строк.

Например, в столбце для номера социального обеспечения может быть 1 миллион строк с 1 миллионом различных значений. Таким образом, избирательность 1000000/1000000 = 1.0 (хотя есть редкие исторические исключения, SSN должны быть уникальными).

Но другой столбец в этой таблице «пол» может иметь только два разных значения более 1 миллиона строк. 2/1000000 = очень низкая селективность.

Индекс с ограничением UNIQUE или PRIMARY KEY гарантированно имеет селективность 1.0, поэтому он всегда будет таким же эффективным, как индекс.

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

Вы спросили в комментарии о том, следует ли использовать MyISAM или InnoDB. В MySQL они используют термин « механизм хранения» . Между этими двумя системами хранения есть множество тонких различий, но главными являются:

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

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

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

См. http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ для очень тщательного сравнения систем хранения. InnoDB достаточно часто выигрывает над MyISAM, поэтому явно невозможно сказать, что один быстрее, чем другой.

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

На уникальном индексе, который просто является уникальным и уникальным индексом? Я не уверен, но думаю, не так много. Оптимизатор должен исследовать мощность индекса и использовать его (это всегда будет количество строк, для уникального индекса).

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

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

Уникальный индекс, отличный от ПК, никогда не будет кластеризован в InnoDB.

С другой стороны, некоторые другие механизмы (в частности, MyISAM) не кластеризуют ПК, поэтому первичный ключ – это как обычный уникальный индекс.