Будет ли атрибут автоматически индексироваться при создании внешнего ключа?

Краткий ответ

Нет, в MySQL создание внешнего ключа НЕ создаёт автоматический индекс на столбце, который является внешним ключом (в дочерней таблице). Однако MySQL автоматически создаёт индекс на столбцах, на которые ссылается внешний ключ (в родительской таблице), если они ещё не проиндексированы.

Подробное объяснение

Как работают внешние ключи в MySQL

Внешний ключ (foreign key) — это ограничение целостности данных, которое связывает столбцы между двумя таблицами. Внешний ключ состоит из двух частей:

• Ключ в дочерней таблице — столбец(ы), которые ссылаются на другую таблицу
• Ключ в родительской таблице — столбец(ы), на которые происходит ссылка

-- Родительская таблица
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Дочерняя таблица
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Автоматическое индексирование при создании FOREIGN KEY

1. Для столбцов, которые ЯВЛЯЮТСЯ внешним ключом (в дочерней таблице)

MySQL НЕ создаёт автоматически индекс на столбце department_id в таблице employees. Это важно понимать, потому что:

• Причина: Спецификация SQL стандарта не требует автоматического создания индекса
• Последствия: Отсутствие индекса может привести к проблемам с производительностью при выполнении операций, связанных с внешним ключом

2. Для столбцов, НА КОТОРЫЕ ссылается внешний ключ (в родительской таблице)

MySQL автоматически создаёт индекс, если его ещё нет. В нашем примере, столбец departments.id уже имеет индекс (так как это PRIMARY KEY), поэтому дополнительный индекс не создаётся.

Почему важно вручную создать индекс на внешнем ключе

Хотя MySQL не делает это автоматически, настоятельно рекомендуется создавать индекс на столбцах, которые являются внешними ключами:

-- Правильный подход: создаём индекс явно
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    INDEX idx_department (department_id), -- Создаём индекс явно
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Причины для создания индекса:

• Производительность JOIN операций — ускоряет соединение таблиц
• Проверка ограничений целостности — ускоряет проверку внешних ключей при вставке/обновлении
• Блокировки — уменьшает количество блокировок при каскадных операциях
• Оптимизация запросов — позволяет оптимизатору выбирать более эффективные планы выполнения

Что происходит в других СУБД

• PostgreSQL: Автоматически создаёт индекс на столбцах внешнего ключа
• Oracle: Не создаёт автоматически индекс на внешнем ключе
• SQL Server: Не создаёт автоматически индекс на внешнем ключе

Практический пример с последствиями

-- Проблемный сценарий: нет индекса на внешнем ключе
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    -- Индекс НЕ создан автоматически!
);

-- Медленный запрос без индекса
SELECT c.name, SUM(o.amount) 
FROM customers c
JOIN orders o ON c.id = o.customer_id  -- Без индекса на o.customer_id
GROUP BY c.id;

Рекомендации

1. Всегда явно создавайте индексы на столбцах внешних ключей
2. Используйте составные индексы, если внешний ключ состоит из нескольких столбцов
3. Проверяйте существующие индексы при миграциях и рефакторинге базы данных
4. Используйте префиксы индексов для улучшения производительности:

-- Для текстовых полей в внешних ключах
CREATE TABLE articles (
    id INT PRIMARY KEY,
    author_name VARCHAR(255),
    FOREIGN KEY (author_name) REFERENCES authors(name)
);

-- Создаём индекс с префиксом
CREATE INDEX idx_author_name ON articles (author_name(100));

Исключения из правила

MySQL автоматически создаст индекс на внешнем ключе только если:

• Вы используете CREATE TABLE ... LIKE для копирования структуры таблицы с внешними ключами
• При определенных условиях импорта данных с сохранением ограничений

Вывод

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