Индексирует ли MySQL столбцы внешнего ключа автоматически?
Ответы 9
Да, см. Ограничения InnoDB и FOREIGN KEY.
Пожалуйста, добавьте всю информацию в сам ответ, а не размещайте только ссылку
Да, но только на innodb. Innodb в настоящее время является единственным поставляемым форматом таблиц, в котором реализованы внешние ключи.
Есть ли у вас основания полагать, что MySQL когда-либо разрешит использование внешних ключей для неиндексированных столбцов для любого другого типа таблиц?
Я действительно не мог на это ответить. Вы можете найти механизмы хранения Maria и Falcon, которые должны быть выпущены в MySQL 6.0, и посмотреть, поддерживают ли они внешние ключи для неиндексированных столбцов.
Видимо это неправда. У меня большая таблица (1 миллион записей) и счетчик (*), где fkey =? займет 15 секунд. Добавлен индекс в столбце fkey, и теперь все идет меньше секунды.
Тот же эксперимент с другой таблицей и 10 миллионами записей. Это MySQL 5.1 InnoDB. В таблице три поля, одно целое число первичного ключа, другое уже проиндексировано. Третий - внешний ключ к первичному ключу другой таблицы. Без добавления явного индекса поиск здесь занял несколько секунд. Показать индекс из таблицы также не отображал индекс по ней.
@AbiusX 5.1, вероятно, слишком стар, см. Ответ г-на Александра ниже.
Очевидно, индекс создается автоматически, как указано в ссылку, которую опубликовал Роберт.
InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.
Ограничения InnoDB и FOREIGN KEY
+1 ответ намного лучше, чем выбранный, поскольку это доказательство из документов
Цитируемый текст, похоже, больше не включен в документацию MySQL, поэтому неясно, так ли это до сих пор или нет.
@ user2045006, вы можете ссылаться на документ 5.0, а также на док 5.6 для точного цитируемого текста
В текущих документах есть лишь небольшое изменение текста (я предполагаю, что смысл аналогичен): InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order.
Как указано, это относится к InnoDB. Сначала мне показалось странным, что многие другие (в частности, MS SQL и DB2) этого не делают. Сканирование TableSpace лучше сканирования индекса только тогда, когда в таблице очень мало строк, поэтому в подавляющем большинстве случаев внешний ключ может быть проиндексирован. Тогда меня это как-то поразило - это не обязательно означает, что это должен быть отдельный (один столбец) индекс - там, где он находится в автоматическом индексе FK MySQL. Так что, возможно, именно по этой причине MS SQL, DB2 (я не уверен в Oracle) и т. д. Оставляют это на усмотрение администратора баз данных; в конце концов, несколько индексов в больших таблицах могут вызвать проблемы с производительностью и пространством.
Вы подняли хороший вопрос об индексах составных ключей; однако MySQL автоматически / незаметно отбрасывает автоматически сгенерированный индекс с одним ключом, если вновь созданный индекс составного ключа выполняет обязательство по быстрой проверке внешнего ключа. Честно говоря, я понятия не имею, почему MS SQL, DB2 и другие не делают этого. У них мало оправданий. Я не могу представить себе вариант использования, в котором автоматически сгенерированный индекс для внешних ключей был бы вредным.
Вы не получите индекс автоматически, если выполните ALTER TABLE (вместо CREATE TABLE), по крайней мере, в соответствии с документы (ссылка для 5.1, но такая же для 5.5):
[...] When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.
Я также пробовал MySQL 5.6, MariaDB 10 и ALTER TABLE создали индекс. Интересно, что mysqlindexcheck сообщил, что этот индекс является «избыточным индексом». Я попытался отбросить его, но получил следующую ошибку: «ОШИБКА 1553 (HY000): невозможно удалить индекс 'index_name': требуется в ограничении внешнего ключа». Таким образом, невозможно удалить этот индекс и сохранить внешний ключ.
Вы можете пересмотреть свой ответ. MySQL всегда создает индекс для ускорения проверки внешнего ключа если он еще не существует. Документы пытаются сказать вам, что создание индексов до ограничений внешнего ключа может немного ускорить процесс. Например, индекс составного ключа, который может служить индексом для проверок внешнего ключа, может использоваться InnoDB вместо автоматического создания избыточного индекса.
Да, Innodb это предоставляет. Вы можете поместить имя внешнего ключа после предложения FOREIGN KEY или оставить его, чтобы MySQL создал для вас имя. MySQL автоматически создает индекс с именем foreign_key_name.
CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
Невозможно получить ключ индекса автоматически, используя
ALTER TABLE (NAME OF THE TABLE) ADD INDEX (FOREIGN KEY)
Имя таблицы, которую вы создали, например фотографии и ИНОСТРАННЫЙ КЛЮЧ, например photograph_id. Код должен быть таким
ALTER TABLE photographs ADD INDEX (photograph_id);
Для тех, кто ищет цитату из 5.7документы:
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.
Да, Mysql индексирует внешний ключ автоматически, когда вы создаете таблицу, у которой есть внешний ключ для другой.
Этот ответ - отличный пример того, почему ответ никогда не должен состоять только из ссылки на возможный ответ. В данный момент связанная страница вообще не отвечает на вопрос.