Ограничения и индексы MySQL: защищаем данные и запросы

Часть 11 из 16

В предыдущем уроке мы научились осторожно менять записи. Но надёжность не должна зависеть только от внимательности человека: часть правил можно поручить самой базе.

В этой статье закрепим обязательность, уникальность, допустимые значения и ссылки с помощью ограничений, а затем перейдём к индексам — отдельному инструменту для ускорения поиска. Различие важно: ограничение защищает смысл данных, индекс помогает находить их быстрее, хотя часто эти механизмы используют одну структуру.

Правила данных

NOT NULL требует значение, UNIQUE запрещает повтор ticket_code, CHECK(price >= 0) отсекает отрицательную цену, внешний ключ требует существующее событие. Не превращайте CHECK в копию случайного условия интерфейса — закрепляйте реальные правила предметной области.

Индекс под частый запрос

Если экран ищет продажи события и сортирует их по времени, попробуем составной индекс:

CREATE INDEX ix_sales_event_sold
ON ticket_sales (event_id, sold_at);

Порядок важен: сначала фильтр по событию, затем дата. Индекс не эквивалентен индексу только по sold_at.

Индекс имеет стоимость

Он занимает место и должен обновляться при записи. Много индексов замедляют вставки; малоселективное поле может не дать выигрыш. Создавайте индекс под повторяемый запрос, а не на каждую колонку.

Проверяем что уже есть

SHOW INDEX FROM ticket_sales;
SHOW CREATE TABLE ticket_sales;

Те же сведения доступны в phpMyAdmin во вкладке «Структура». В следующей части объединим две связанные правки транзакцией InnoDB.

Ключи и индексы связаны

Первичный ключ и уникальное ограничение создают индекс, чтобы MySQL мог быстро находить строку и проверять уникальность. Внешний ключ защищает ссылочную целостность; серверу также нужен индекс для проверки ссылок. Сначала выполняйте SHOW INDEX FROM ticket_sales;, чтобы увидеть уже существующие индексы и не дублировать их.

Составной индекс читается слева направо

Индекс (event_id, sold_at) упорядочен сначала по событию, а внутри него — по времени продажи. Он подходит запросу с условием WHERE event_id = 1 и сортировкой по sold_at.

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

Проверка — до и после

Создайте индекс в тестовой базе и выполните один и тот же SELECT с EXPLAIN до и после. Сравнивайте выбранный ключ, тип доступа и оценку строк. На маленькой таблице полный просмотр может быть дешевле; это не обязательно означает, что индекс сломан.

Индексы улучшают некоторые чтения ценой дополнительного места и работы при INSERT/UPDATE/DELETE.

В phpMyAdmin структура и индексы видны на вкладке «Структура», но вывод об ускорении делайте по реальному запросу и плану. Далее разберём транзакции: они решают задачу согласованности сразу нескольких изменений, а не скорости выборки.

Что именно индексирует MySQL

Индекс можно представить как предметный указатель в конце книги: он помогает найти нужную страницу, не читая всё подряд. Но указатель нужно обновлять при каждой новой записи, поэтому у него есть стоимость.

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

В составном индексе (event_id, sold_at) порядок ключей важен. Условие только по event_id соответствует левому префиксу. Фильтр только по sold_at обычно не использует этот индекс так же эффективно. Поэтому формируйте индекс из реальных WHERE и ORDER BY, а не по алфавиту колонок.

CHECK и версия MySQL

Современный MySQL проверяет ограничения CHECK; старые версии MySQL до 8.0.16 могли принимать такой синтаксис, но игнорировать проверку. В учебной среде на MySQL 9.7 правило действует.

Если важная проверка внезапно не отклоняет плохое значение, подтвердите версию сервера и определение таблицы через SHOW CREATE TABLE.

Практика: выполните тестовый INSERT с отрицательной ценой в отдельной учебной схеме и убедитесь, что MySQL его отклонил. Удалите тестовые данные только после проверки. Следующая часть использует InnoDB-транзакцию, чтобы связать две операции без частичного результата.

Ограничение или индекс?

Первые три защищают корректность, индекс в первую очередь меняет способ поиска и цену записи. Иногда один объект помогает обеим задачам, но назначение всё равно разное.

← Предыдущая статья: UPDATE и DELETE в MySQL: безопасно меняем данные

Следующая статья: Транзакции MySQL: отмечаем билет без частичных изменений →