EXPLAIN и права MySQL: ускоряем и защищаем базу

Часть 15 из 16

К этому моменту у нас есть отчёт, индексы и аналитические запросы. Теперь представим, что страница с продажами стала открываться медленно, а сотруднику нужен доступ только на чтение. В этой части сначала посмотрим план выполнения через EXPLAIN и проверим, помогает ли индекс именно нашему запросу.

Затем создадим отдельную учётную запись MySQL с ограниченными правами. Это два разных вида заботы: первый про скорость, второй про безопасность.

Читаем EXPLAIN

EXPLAIN
SELECT sale_id,ticket_code,sold_at
FROM ticket_sales
WHERE event_id=1
ORDER BY sold_at DESC
LIMIT 50;

Смотрите на предполагаемое число строк и выбранный ключ. Полное чтение маленькой таблицы не обязательно плохо: оптимизатор выбирает план по оценке стоимости.

Проверяем составной индекс

CREATE INDEX ix_sales_event_sold
ON ticket_sales (event_id,sold_at);
EXPLAIN SELECT sale_id,ticket_code,sold_at
FROM ticket_sales WHERE event_id=1
ORDER BY sold_at DESC LIMIT 50;

Сравните планы. EXPLAIN ANALYZE реально исполняет запрос, поэтому начинайте с безопасного SELECT и учитывайте нагрузку.

Отдельная учётная запись

CREATE USER 'festival_editor'@'localhost'
 IDENTIFIED BY 'replace-with-a-long-unique-secret';
GRANT SELECT,INSERT,UPDATE ON city_festival.*
 TO 'festival_editor'@'localhost';

Замените пароль уникальным секретом. Команды выполняет администратор MySQL. Пользователю не выдали DELETE и глобальные права. phpMyAdmin получает права той учётной записи, под которой выполнен вход.

Не выставляйте phpMyAdmin наружу

Для веб-панели нужны обновления, HTTPS, надёжная аутентификация и сетевые ограничения; учебную установку оставляйте локальной. В последней части сделаем экспорт, восстановим дамп и соберём итоговый отчёт.

Как читать EXPLAIN без магических цифр

EXPLAIN показывает оценку оптимизатора: каким способом он читает таблицы, какой индекс может применить и сколько строк ожидает проверить. Откройте результат в phpMyAdmin и сравните поля type, key и rows. Эти значения — подсказки, а не итоговое время.

Полный просмотр небольшой таблицы может быть быстрее индексного поиска.

После добавления индекса выполните тот же запрос ещё раз. Если ключ не выбран, проверьте форму условия и статистику таблицы через ANALYZE TABLE ticket_sales;.

Не добавляйте индекс только потому, что колонка есть в WHERE: польза зависит от количества строк, распределения данных, порядка полей и затрат на запись.

У EXPLAIN ANALYZE есть побочный эффект

В MySQL EXPLAIN ANALYZE запускает запрос и показывает фактические этапы выполнения. Для безопасного SELECT на учебных данных это полезно; на тяжёлой производственной выборке он создаёт реальную нагрузку. Не применяйте его к изменяющей команде, не разобравшись в последствиях.

Минимальные права и область учётной записи

MySQL-пользователь — отдельный логин сервера базы, не учётная запись WordPress и не Windows. Часть 'festival_editor'@'localhost' состоит из имени и допустимого источника подключения. Если phpMyAdmin подключается не как localhost, учётная запись с таким host может не подойти.

Выдавайте права только на нужную схему и только на нужные действия; root оставляйте для администрирования.

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

Права не заменяют защиту приложения

Минимальные полномочия уменьшают ущерб от ошибки, но не фильтруют содержимое запросов. В приложении запросы с пользовательским вводом должны использовать подготовленные выражения в драйвере базы; не собирайте SQL конкатенацией строк.

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

Перед GRANT подумайте, что человеку действительно надо делать: читателю отчёта достаточно SELECT, редактору могут понадобиться INSERT и UPDATE, но не обязательно DROP или права на все базы сервера. После выдачи проверьте доступ входом под новым логином и убедитесь, что запрещённая команда действительно не выполняется.

План — начало расследования, не ответ целиком

Если запрос медленный, сначала проверьте количество строк и условия. Посмотрите EXPLAIN, затем попробуйте один оправданный индекс и повторите сравнение на сопоставимых данных. Для измерения фактического времени учитывайте кэш, текущую нагрузку и размер выборки.

Не фиксируйте вывод по одному прогону и не удаляйте полезные индексы из production без резервного плана.

Выдаём права по задаче

Это отправная точка, а не универсальная матрица: права определяются задачами человека и конфигурацией проекта. Проверяйте результат входом под созданной учётной записью.

← Предыдущая статья: Оконные функции MySQL: ранжируем расписание и выручку

Следующая статья: Экспорт и резервная копия MySQL: завершаем проект →