Индексы внешнего ключа MySQL в столбцах первичного индекса

user1730452 спросил: 28 марта 2018 в 02:22 в: mysql

Я создал таблицу для учетных записей / пользователей с первичным ключом (UserID, AccountsID), как показано ниже. Должен ли я добавлять индекс для таблицы Users?

create table AccountsUsers
(
    AccountsID int unsigned not null,
    UsersID int unsigned not null,
    Roles bigint unsigned null,
    primary key (UsersID, AccountsID),
    constraint AccountsUsers_Accounts_ID_fk
        foreign key (AccountsID) references Accounts (ID)
            on update cascade on delete cascade,
    constraint AccountsUsers_Users_ID_fk
        foreign key (UsersID) references Users (ID)
            on update cascade on delete cascade
)
engine=InnoDB
;create index AccountsUsers_Accounts_ID_fk
    on AccountsUsers (AccountsID)
;

2 ответа

Paul Campbell ответил: 28 марта 2018 в 02:59

Из документации

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

Другими словами, если у вас еще нет необходимых индексов для столбцов таблицы ссылок (AccountsUsers), MySQL создаст их для вас.

Если столбцы в ссылочных таблицах (Accounts и Users) не проиндексированы, вы получите ошибку. Похоже, они будут первичными ключами в соответствующих таблицах, так что с тобой все будет в порядке.

Bill Karwin ответил: 28 марта 2018 в 03:03

MySQL автоматически создаст необходимые индексы для внешнего ключа, если это необходимо.

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

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

После того как вы выполните CREATE TABLE, запустите SHOW CREATE TABLE AccountsUsers и вы должны увидеть новый индекс, созданный для AccountsId.