Группа оптимизации запросов MySQL по максимуму

ProgramBox

Группа оптимизации запросов MySQL по максимуму

Post author:admin
Запись опубликована:8 мая, 2023
Post category:Вопросы по программированию

#database #database-design #group-by #query-optimization #groupwise-maximum

#База данных #база данных-дизайн #группировка по #оптимизация запросов #групповая оптимизация-максимальная

Вопрос:

Пытаюсь оптимизировать запрос MySQL, который использует Group by с максимальным значением. Пытаюсь получить последние обновленные фреймы, которые были активны до любой прошлой даты.

Структура таблицы фреймов аудита:

 id bigint pk AI
frame_id bigint fk
name VARCHAR(50)
active int
createdOn DATETIME
updatedOn DATETIME
deletedOn DATETIME

id, frame_id и updatedOn имеют индивидуальные индексы..

Текущий запрос:

 SELECT MAX(id) as id1 
  FROM audit_frame_master 
 WHERE updatedOn <='2019-03-25 21:00:00' 
   AND active=1 
   AND deletedOn IS NULL 
 GROUP 
    BY frame_id

Производительность:

В таблице около 1 МЛН строк. Для выполнения требуется в среднем 4 секунды .. Есть ли какой-либо способ оптимизировать приведенный выше запрос?

Ниже приведена инструкция EXPLAIN для того же.

1. Какой у вас столбец индекса кластера?

2. @SiamakFerdos: id — это столбец индекса кластера.

3. зачем вам оба AND active=1 AND deletedOn IS NULL ? AND active=1 может быть достаточно?

4. В принципе, мне нужен активный список фреймов на определенную дату, которые не были удалены.

5. Но можно ли удалить активные фреймы? обычно при удалении записи должно получаться, active = 0 или если нет — тогда зачем вам нужно active=1 проверять?

Ответ №1:

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

сколько строк совпадают active = 1 ?
сколько строк совпадают deletedOn IS NULL ?
сколько строк совпадают updatedOn <= timestamp '2019-03-25 21:00:00' ?

Выберите тот, который содержит наименьшее количество строк. Скажите, что это active , тогда updatedOn , тогда deletedOn . Это дает вам:

 create index idx on audit_frame_master(active, updatedOn, deletedOn);

Поскольку вы хотите сгруппировать по frame_id затем, а затем найти максимальное id значение, вы можете добавить их в таком порядке:

 create index idx on audit_frame_master(active, updatedOn, deletedOn, frame_id, id);

Это покрывающий индекс. Если СУБД использует это, ей даже не нужно обращаться к таблице.

СУБД может использовать, а может и не использовать этот индекс. Это просто предложение. Если СУБД считает, что будет слишком сложно просматривать индекс, а не просто последовательно читать таблицу, то она не будет его использовать. Просто попробуйте.

1. Я добавил эти индексы, и кажется, что производительность улучшилась, но все же это происходит не так быстро.. Связано ли это с тем, что updatedOn и DeletedOn являются DateTime и занимают больше времени на вычисления?

2. Нет, это не должно иметь значения. Просто предстоит проделать большую работу. Сколько строк вы получаете с отдельными условиями, сколько с объединенным? Сколько разных фреймов в таблице, сколько в вашем результате?

3. Пожалуйста, посмотрите мой отредактированный вопрос.. Я приложил поясняющее заявление.

4. Я не знаю, как читать планы MySQL explain. Правильно ли я понимаю, что WHERE предложение содержит более 10% строк таблицы? Тогда СУБД не получит большой выгоды от индекса. Это просто много данных для сортировки и агрегирования.

5. Да, в некоторых случаях в таблице может быть более 10% строк.. Предлагаете ли вы какое-либо конкретное изменение таблицы DB в этом случае?

Ответ №2:

Добавьте следующий составной индекс: (frame_id, active, deletedOn, updatedOn, id) и сообщите, как он работает.

Ответ №3:

Разве вы не хотите

  SELECT frame_id, MAX(id)

вместо

  SELECT           MAX(id)

Вероятно, это оптимальный индекс. Обратите внимание, как сначала перечислены WHERE столбцы, исходя из предположения, что фильтрация важнее группировки:

 INDEX(active, deletedOn,   -- in either order
      updatedOn)

Добавление frame_id, id (в любом порядке, но в конце) превратило бы его в «покрывающий» индекс, тем самым увеличив скорость.