Способы оптимизации sql-запроса

117

07 января 2021, 10:00

Один запрос выборки статистики занимает достаточно много времени, да и сам запрос достаточно сложный. Много внутренних запросов и подсчеты сумм по датам.

Придумал 2 способа оптимизации:

1) Создать отдельную таблицу со всеми нужными столбцами и заносить в нее всё что мне нужно. (Чтоб избавится от дочерних запросов и подсчетов). Но сайт уже находится на продакшене и таблицу статистики я трогать не могу. Старые данные из нее нужны. Вариант отпадает

2) Кешировать результаты запросов (во фреймворке есть возможность сохранять результаты выборок в файловую систему). Скорость заметно возросла, но тут возникает проблема актуальности данных. Допустим я вывожу в таблице статистику за каждый день. За предыдущие дни из кеша будет оторажаться правильная статистика. А за сегодня каждую минуту данные обновляются, а оно будет брать неактуальные данные из кеша. Даже если отдельно брать данные за сегодня из БД, и остальное из кеша - всё равно придется периодически очищать кеш, так как например завтра уже будут неактуальны данные за сегодня и за вчера.

Как можно еще решить данную проблему? Посоветуйте

Answer 1

Запускаем запрос написав перед ним буквы EXPLAIN EXTENDED
Читаем, что там написано
Расставляем индексы, чтобы все выборки проводились с использованием индексов и как можно меньшего количеств просматриваемых строк.

Answer 2

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

Естественно индексы. Рассмотрите внимательно каждый подзапрос, если какой-то из них выбирает часть из большого количества записей, для него должен быть индекс. Как собирать индекс правильно не самая очевидный вопрос, если нет большого опыта, потому эксперементируйте. Меняйте порядок полей в индексе и порядок условий после where. Смотрите результаты с помощью explain. Чем меньше строк придется просмотреть при выполнении запроса, тем лучше
Партицирование. Вроде как есть только myisam таблицах, но может ошибаюсь. Позволяет хранить одну таблицу как несколько файлов, немного уменьшая вес индексов и ускоряя запросы по узким диапазонам. Например, можно сделать партицирование по дате, по годам. В таблице у вас данные за 2013-2019гг. если вы сделаете запрос с 09.2015 по 03.2016, то пять седьмых от общего количества данных вылетит из проверки еще до проверки индексов
Я бы еще упомянул View. Производительность вряд ли способны повысить, но вот понимание запросов - вполне

Answer 3

Использовать индексы, если возможно.
Если не критична актуальность, часть данных можно предрасчитывать, сохранять в отдельную таблицу и использовать потом в запросе эти данные.
Кешировать результаты запросов (во фреймворке есть возможность сохранять результаты выборок в файловую систему)

Кешировать в файловую систему - плохо. Кешировать в оперативку - хорошо. Идеально подходит Redis для таких задач.