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

Question

[prewordeSSS]

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

latest('id')
            ->where('user_id', '=', $userId)
            ->whereIn('type_id', [
                HistoryType::WRITE_OFF,
                HistoryType::WRITE_ON,
                HistoryType::MAGIC,
            ])
            ->limit(100)
            ->get();

На sql так:

select * from `histories` where `user_id` = '1' and `type_id` in (5, 4, 6) order by `id` desc limit 100

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

Comments

[Дмитрий Кузнецов]

Можете попробовать в Select указать только нужные колонки. Это если конечно не > 80% от того, что есть в бд. Иначе минимальны будут твои усилия.

[prewordeSSS]

Дмитрий Кузнецов, Таблица уже нормализирована, там только нужные данные лежат. А так бы да, не очень сильно, но помогло бы

[JhaoDa]

Возможно ли это сделать без добавления индексов на столбцы, так как размер таблицы сильно увеличится

И рыбы поесть, и костями не подавиться, ага...

Explain показывать ты не будешь, конечно же...

[mayton2019]

prewordeSSS, чтобы заниматься оптимизацией нужно знать цифры. А именно - сколько всего строк в таблице. Сколько в среднем будет выбирать типичный запрос который ты оптимизируешь. Допустим это 3% от общего объема таблицы - тогда индекс будет эффективен скорее всего. Это эвристика вобщем. Так. Чисто для начала разговора.

В общем случае... если тебе не чем заняться - то я-бы предложил построить композитный индекс по полям user_id type_id и посмотреть как это влияет на время отклика. В более сложном варианте - нужно знать какой тип БД (MySQL, Postgres e.t.c.) и смотреть план выполнения.

Тестовые запуски нужно делать по холодному. Тоесть по тем данным которые еще не запрашивались. Это гарантирует честность результата. В противном случае ты будешь мерять отклик буферного кеша вместо диска. И такой результат будет красив - но в будущем не будет отражать действительность.

Оптимизация обычно - это компромисс. Тоесть ты в одном месте в БД что-то ускоряешь. В другом что-то замедляется. Ускорять везде - не получится. И еще оптимизация делается согласовано в владельцем БД. Тоесть он должен быть в курсе чем ты занимаешся.

И для оптимизации нужно запросить у владельца БД все права на оптимизацию. Сбор статистики по таблицам. Возможность дропать и создавать новые таблицы. Иначе оптимизация теряет смысл.

[res2001]

Поле user_id текстовое? Поиск по тексту медленнее чем по числовым значениям.
Как уже тут все писали - добавить индексы по полям, участвующим во фразе where.
Индексация полей - это то благодаря чему ускоряются операции поиска в таблице. Без индексов кардинально вы ничего не измените.

[Vitsliputsli]

не знаю как оптимизировать

А зачем его оптимизировать? Оптимизация это жертва чем-то, ради чего-то.
Например, ради скорости ответа жервуем постоянной памятью и возможно оперативной.

Возможно ли это сделать без добавления индексов на столбцы, так как размер таблицы сильно увеличится

сперва постройте индексы и посмотрите, что получится. И по резултату будете переживать о лишнем гигабайте на hdd. Текущее описание, не позволяет говорить о чем-либо ином. Шардирование не такая уж простая штука, поэтому совет "разбейте на таблицы", и уж тем более "по годам", скорее принесет только проблемы, чем существенный выигрыш.

mayton2019,

Тестовые запуски нужно делать по холодному. Тоесть по тем данным которые еще не запрашивались. Это гарантирует честность результата. В противном случае ты будешь мерять отклик буферного кеша вместо диска. И такой результат будет красив - но в будущем не будет отражать действительность.

СУБД, как правило, работают именно с кешем, специальным buffer pool, дисковым кешем, но с кешем. Если СУБД постоянно лазает на диск за данными, ни о какой высокой скорости там речи вообще не будет. Т.е. будут отдельные небыстрые запросы при "холодной" загрузке страниц, но общий поток будет быстрым. И вряд ли, у автора как-то иначе, вряд ли у него жесткое правило, что любой запрос должен отрабатывать менее X мс. Понятно, что бывают разные задачи, но в большинстве случаев именно так, и именно для такого использования СУБД и проектируются.

[mayton2019]

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

Answer 1

[Joerude]

В данном случае, чтобы оптимизировать запрос, можно использовать следующие методы:

1 Добавить индексы на столбцы user_id и type_id, чтобы ускорить поиск данных по этим параметрам.

2 Использовать оператор LIMIT для ограничения количества выбираемых данных, чтобы уменьшить нагрузку на сервер.

3 Использовать оператор WHERE для фильтрации данных по необходимым критериям, чтобы избежать лишней обработки данных.

4 Использовать параметр кеширования для сохранения результатов запроса в памяти, чтобы ускорить получение данных при повторных запросах.

Также рекомендуется провести профилирование запросов и использовать специальные инструменты, такие как EXPLAIN, чтобы определить причины медленной работы запроса.

Comments

[mayton2019]

Тут даёшь, даёшь рекомендации, а автор сидит, ручки певесил. Он так и не сделает explain. Сидит надеется на чудо. Думает что профессионалы будут играть в угадайку. "А вот попробуй это. А это.."

Answer 2

[Dmtm]

разбить на несколько таблиц, по году в каждой и запрос направлять в нужную

Answer 3

[KoreanGuy]

Можешь попробовать тип сделать одним числом-битовой строкой