Медленный экран не доказывает, что проблема в базе данных, и не доказывает, что решением будет индекс. То же впечатление может быть вызвано PHP-кодом, перегрузкой подключений, внешним HTTP-вызовом, сериализацией ответов, блокировками или запросом, возвращающим слишком много данных. Понимание того, как исследовать медленные запросы в PHP, заключается в сборе доказательств до изменения схемы или добавления оптимизаций, которые могут удорожить операции записи.
Отделите задержку приложения от задержки данных
Начните с декомпозиции общего времени запроса. Зафиксируйте идентифицируемый маршрут или команду, время начала и окончания, выполненные запросы, их длительность и внешние зависимости. Недостаточно измерять среднее время: API может казаться работоспособным, но при этом давать сбои для определённых фильтров, клиентов или глубоких страниц.
Для каждого инцидента полезно различать:
- Время в PHP: преобразование коллекций, циклы, JSON-сериализация, генерация документов или чрезмерное потребление памяти.
- Время базы данных: длительность каждого запроса, ожидание блокировок, открытие подключения и количество переданных строк.
- Время сети и зависимостей: удалённые кэши, сторонние API, файловое хранилище, очереди или сервисы идентификации.
- Время в очереди: запросы, ожидающие PHP-воркеров, доступные подключения или ресурсы базы данных.
Используйте трейсы или структурированные логи с идентификатором запроса. Изолированный запрос длительностью 20 мс может превратиться в проблему на секунды, если он выполняется сотни раз в рамках одного ответа. И наоборот, запрос на 500 мс может не быть основной причиной, если процесс несколько секунд ожидает внешний сервис.
Соберите доказательства до изменения кода
Сохраняйте параметризованный запрос и отдельно — репрезентативные параметры. Не записывайте секреты, полные персональные данные или значения, не необходимые для воспроизведения случая. Поиск по часто встречающемуся статусу ведёт себя не так, как поиск по уникальному идентификатору; оценка только удобного случая приводит к ошибочным решениям.
Минимальный набор доказательств должен включать:
- Затронутый маршрут, асинхронную задачу или команду, а также их частоту.
- Наблюдаемую длительность, высокие перцентили и момент возникновения.
- SQL, типизированные параметры и количество выполнений на запрос.
- Возвращённые строки и, когда возможно, прочитанные или просмотренные строки.
- Приблизительный размер таблиц и распределение значений, по которым выполняется фильтрация.
- Конкурентность, одновременные операции записи и релевантные блокировки.
Логи медленных запросов движка помогают находить кандидатов, но не заменяют трейс приложения: обычно они не показывают, какой обработчик API сформировал запрос и сколько раз он повторился. В MySQL и PostgreSQL сопоставляйте эту информацию с метриками подключений, CPU, I/O и времени ожидания, чтобы не принять плохой план за временно перегруженную инфраструктуру.
Выявите паттерны, многократно увеличивающие стоимость
Прежде чем анализировать сложный оператор, ищите распространённые паттерны. N+1 возникает, когда список получает основные строки, а затем выполняет дополнительный запрос для каждой связи. Даже если каждый отдельный запрос быстрый, объём обращений к базе данных, работа планирования и конкуренция растут вместе с размером страницы.
Подозрительны также низкоселективные фильтры, например очень распространённые статусы; функции, применяемые к фильтруемым столбцам; неявные преобразования типов; поиск с символом подстановки в начале; сортировка больших наборов и пагинация с большим OFFSET. Запрос SELECT * может увеличить объём передачи, потребление памяти и работу чтения, даже когда план уже использует индекс.
Исправление не всегда заключается в единственном запросе. Контролируемая загрузка связей может устранить N+1, но жадная загрузка без ограничений может создать огромный запрос или ответ. Определите, какие связи действительно нужны маршруту, ограничьте их столбцы и измерьте эффект при ожидаемом размере страницы.
Интерпретируйте план выполнения с реальными данными
Выполните EXPLAIN, чтобы узнать предлагаемый план, и используйте вариант, включающий реальное выполнение, когда это безопасно и уместно для среды. В PostgreSQL EXPLAIN ANALYZE выполняет запрос; оператор модификации не следует анализировать таким образом, не понимая его последствий. В MySQL доступные режимы зависят от версии и конфигурации, но цель та же: сопоставить оценки с фактической работой.
Последовательное сканирование не является автоматически плохим. Если запросу нужна значительная часть небольшой таблицы или таблицы с низкой селективностью, её обход может стоить дешевле, чем переходы между индексом и таблицей. Напротив, проводите расследование, когда план показывает гораздо больше фактических строк, чем оценочных, дорогостоящие сортировки, операции с временными структурами, joins по широким наборам или внутренние циклы, выполняемые много раз.
Полезные вопросы при проверке плана
- Сколько строк ожидал оптимизатор и сколько он фактически обработал?
- Какой узел сосредотачивает больше всего времени, чтений или итераций?
- Применяется ли фильтр рано или после объединения больших наборов?
- Выполняется ли сортировка по большему числу строк, чем требуется ответу?
- Отражает ли статистика текущее распределение данных?
Расхождения между оценкой и реальностью могут потребовать обновления статистики или пересмотра типов и условий, а не немедленного создания индекса. План — это объяснение одного выполнения при определённых параметрах и нагрузке, а не автоматическое указание на изменение.
Выберите между переписыванием, пагинацией, доступом к данным и индексом
Сначала уменьшите неизбежную работу. Выбирайте только нужные столбцы, применяйте разумные лимиты, убирайте неиспользуемые связи и не переносите в PHP полные истории, чтобы затем фильтровать их там. Если сценарий использования предполагает просмотр растущей истории, замените глубокую пагинацию на курсорную или ключевую пагинацию: например, продолжайте от стабильной комбинации даты и идентификатора вместо отбрасывания тысяч строк с помощью OFFSET.
Проверьте joins и фильтры, чтобы они сравнивали совместимые столбцы и ясно выражали условие. Иногда целесообразно запрашивать связь блоками; в других случаях предпочтительнее один чётко ограниченный запрос. Решение зависит от кардинальности, объёма ответа и частоты, а не от универсального правила.
Составной индекс помогает, когда он соответствует паттерну доступа. Его порядок важен: обычно столбцы, используемые в условиях равенства, и селективные столбцы должны облегчать фильтрацию до тех, которые используются для диапазона или сортировки, однако конкретный запрос и движок определяют результат. Индекс также может помочь избежать сортировки, если покрывает совместимый порядок, хотя это возможно не для каждого сочетания WHERE и ORDER BY.
Не индексируйте столбцы только потому, что они фигурируют в условии. Индексы занимают место, потребляют память и добавляют работы операциям INSERT, UPDATE и DELETE. Избыточные или малополезные индексы могут ухудшить систему с большим объёмом записи. Также проверьте, позволяет ли индекс получать требуемые столбцы без дополнительных чтений, но не добавляйте покрывающие столбцы, не измерив стоимость.
Гипотетический пример: история операций
Предположим, список отображает операции по счёту, статусу и дате. По мере роста истории страница 200 деградирует. Первой гипотезой могло бы быть создание индекса по дате. Однако трейс выявляет основной запрос, за которым следует запрос для каждой операции, чтобы получить ответственного пользователя: это N+1. План основного запроса также читает много строк, чтобы отбросить предыдущие с помощью OFFSET.
Разумной последовательностью будет загрузить ответственных пользователей сгруппированно или через join, ограниченный необходимыми столбцами, заменить глубокую пагинацию на курсорную пагинацию на основе created_at и id, а затем снова провести измерения. Только после этого оценивается индекс, выровненный с фильтром по счёту, стабильным порядком и курсором. Изменение доступа может сократить больше работы, чем отдельный индекс, а для итогового индекса следует также проверить его влияние на создание новых операций.
Проверьте под нагрузкой и отслеживайте регрессии записи
Сравнивайте до и после с репрезентативными параметрами, распределением данных и конкурентностью. Измеряйте длительность, обработанные строки, чтения, использование CPU, память в PHP, размер ответа и количество SQL-запросов на один HTTP-запрос. Для нового индекса также измеряйте задержку и пропускную способность затронутых операций записи.
Определите явные критерии приемки: например, проверяемое снижение высокого перцентиля маршрута без неприемлемого увеличения времени создания или обновления. Проверьте пустые случаи, очень частые значения, редкие фильтры, первые и последние страницы, а также разрешения, изменяющие охват данных.
Развёртывайте изменения с обеспечением наблюдаемости и возможности отката
По возможности отделяйте развёртывание кода от создания индекса. Создание индексов может конкурировать за ресурсы или захватывать блокировки в зависимости от движка, операции и среды. Спланируйте время, проверьте метод, поддерживаемый вашей базой данных, и отслеживайте длительность, ошибки и нагрузку на I/O.
Внедряйте новый запрос постепенно, если это позволяет архитектура, и сохраняйте понятный откат: восстановить предыдущий запрос, отключить альтернативный путь доступа или удалить индекс, который оказался вредным. Откат не заменяет резервные копии и проверку миграций, но сокращает время воздействия при неожиданном поведении.
Чек-лист для воспроизводимого улучшения
- Определите маршрут, симптом и параметры, которые его воспроизводят.
- Отделите время PHP, базы данных, сети и внешних зависимостей.
- Подсчитайте повторы, возвращённые строки и обработанные строки.
- Ищите N+1, глубокую пагинацию, низкоселективные фильтры и сортировки.
- Проверьте план и сопоставьте его оценки с фактическим выполнением.
- До индексирования попробуйте сократить данные, переписать запрос и изменить пагинацию.
- Спроектируйте индекс с учётом полного паттерна фильтрации, сортировки и записи.
- Проверьте чтения и записи при репрезентативной нагрузке, с наблюдаемостью и откатом.
