Какие есть способы оптимизации тяжелых запросов к базе данных

Что проверяют

Умеете ли вы найти узкое место в связке PHP, ORM и MySQL или PostgreSQL. Интервьюеру важно, начинаете ли вы с измерений, а не с «повесим индексы». Следом обычно спрашивают про N+1 в Eloquent или Doctrine и про чтение EXPLAIN.

Как отвечать

• найти медленные запросы: slow query log в MySQL с порогом long_query_time, pg_stat_statements в PostgreSQL, в Laravel Telescope или Debugbar, в Symfony встроенный профайлер;
• прочитать план: в MySQL EXPLAIN, а начиная с 8.0.18 и EXPLAIN ANALYZE. Тревожные признаки: type: ALL (полный скан), Using filesort, Using temporary. Хороший признак Using index, то есть покрывающий индекс;
• N+1: в Eloquent жадная загрузка with('orders'), а Model::preventLazyLoading() в локальном окружении превращает ленивую загрузку в исключение. В Doctrine JOIN FETCH в DQL;
• индексы под запрос: составной в порядке «сначала равенство, потом диапазон и сортировка», чтобы WHERE status = ? ORDER BY created_at обходился без filesort;
• не тянуть лишнее: select() нужных колонок, в Doctrine гидрация в массив через getArrayResult() там, где объекты не нужны;
• большие выборки: chunkById() или lazy() в Laravel, toIterable() в Doctrine, иначе упрётесь в memory_limit;
• пагинация: глубокий LIMIT 20 OFFSET 100000 читает все пропущенные строки. cursorPaginate() в Laravel использует keyset по индексированной колонке;
• кеш: Redis для тяжёлых агрегатов с понятной инвалидацией, в Laravel Cache::remember().

Хороший ответ заканчивается проверкой: после правки снова смотрите план и время на данных, близких к боевым по объёму.

Где спотыкаются

Советуют выбрать MyISAM ради скорости чтения. Движок устарел, не поддерживает транзакции и блокирует таблицу целиком на запись, стандарт сегодня InnoDB.

Рассчитывают на query cache MySQL. В версии 8.0 его убрали совсем.

Используют chunk() при обновлении тех же строк, по которым идёт выборка. Смещение съезжает, и часть записей пропускается, для этого и существует chunkById().