В чем разница между уникальным и неуникальным индексом MySQL с точки зрения производительности?
Допустим, я хочу создать индекс для комбинации из 2 столбцов, и комбинация уникальна, но я создаю неуникальный индекс. Повлияет ли это на производительность или объем памяти, используемый MySQL?
Тот же вопрос, есть ли разница между первичным ключом и уникальным индексом?
UNIQUE и PRIMARY KEY являются ограничениями, а не индексами. Хотя большинство баз данных реализуют эти ограничения, используя индекс. Дополнительные накладные расходы ограничения в дополнение к индексу несущественны, особенно когда вы подсчитываете стоимость отслеживания и исправления непреднамеренных дубликатов, когда (а не если) они возникают.
Индексы обычно более эффективны, если у вас высокая избирательность. Это отношение количества различных значений к общему числу строк.
Например, в столбце для номера социального обеспечения может быть 1 миллион строк с 1 миллионом различных значений. Таким образом, избирательность 1000000/1000000 = 1.0 (хотя есть редкие исторические исключения, SSN должны быть уникальными).
Но другой столбец в этой таблице "пол" может иметь только два разных значения более 1 миллиона строк. 2/1000000 = очень низкая селективность.
У индекса с ограничением UNIQUE или PRIMARY KEY гарантируется селективность 1.0, поэтому он всегда будет таким же эффективным, как индекс.
Вы спрашивали о различии между первичным ключом и уникальным ограничением. В основном это означает, что у вас может быть только одно ограничение первичного ключа на таблицу (даже если это определение ограничения включает несколько столбцов), тогда как вы можете иметь несколько уникальных ограничений. Столбец с уникальным ограничением может допускать NULL, тогда как столбцы в ограничениях первичного ключа не должны допускать NULL. В противном случае первичный ключ и уникальность очень похожи в их реализации и использовании.
Вы спросили в комментарии о том, следует ли использовать MyISAM или InnoDB. В MySQL они используют термин "механизм хранения". Между этими двумя механизмами хранения есть множество тонких различий, но главными являются:
Если эти функции необходимы для вашего приложения, вам следует использовать InnoDB.
Чтобы ответить на ваш комментарий, это не так просто. InnoDB на самом деле быстрее, чем MyISAM, в довольно многих случаях, поэтому это зависит от того, что ваше приложение сочетает в себе выбор, обновления, параллельные запросы, индексы, конфигурацию буфера и т.д.
См. http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ для очень тщательного сопоставления производительности систем хранения. InnoDB достаточно часто выигрывает над MyISAM, что явно невозможно сказать, что один быстрее, чем другой.
Как и в большинстве вопросов, связанных с производительностью, единственный способ ответить на него для вашего приложения - проверить обе конфигурации с помощью приложения и репрезентативную выборку данных и измерить результаты.
В неуникальном индексе, который просто является уникальным и уникальным индексом? Я не уверен, но думаю, не так много. Оптимизатор должен изучить мощность индекса и использовать его (это всегда будет число строк, для уникального индекса).
Что касается первичного ключа, возможно, довольно много, но зависит от того, какой движок вы используете.
Механизм InnoDB (который используется многими людьми) всегда кластеризует строки на первичном ключе. Это означает, что ПК по существу сочетается с фактическими данными строки. Если вы делаете много поисков по PK (или, действительно, сканирование диапазонов и т.д.), Это хорошая вещь, потому что это означает, что не нужно будет извлекать столько блоков с диска.
Уникальный индекс, не содержащий PK, никогда не будет кластеризован в InnoDB.
С другой стороны, некоторые другие механизмы (в частности, MyISAM) не кластеризуют PK, поэтому первичный ключ - это как обычный уникальный индекс.