Блог

Главная - Вопросы по программированию - Оптимизация порядка с помощью ограничения в MySQL

Оптимизация порядка с помощью ограничения в MySQL

#mysql #sql #query-performance

#mysql #sql #запрос-производительность

Вопрос:

У меня есть таблица с 3 миллионами записей под названием «транзакции».

 CREATE TABLE transactions(
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lookupAId int(6) NOT NULL,
  .....
  updateDate TIMESTAMP
)

В худшем случае пользователь не будет указывать фильтры, и запрос будет выглядеть следующим образом :

 select * from transactions
   join lookupA on (well indexed columns) 
   .. ( 12 lookup table joins) 
order by updateDate limit 500

Без предложения order by запрос выполняется за миллисекунды, но с order by это занимает около минуты. Прогнозируется, что таблица вырастет до 12-15 миллионов записей.

  1. Мой SLA заключается в получении результатов менее чем за секунду, возможно ли это в MySQL?
  2. Как я могу оптимизировать предложение order by, чтобы это выполнялось.

Я запускаю MySQL 5.7 в экземпляре RDS, оптимизированном для xLarge памяти, в AWS

ОБНОВЛЕНИЕ 1 UpdateDate имеет временную составляющую и индексируется (B-дерево, неуникальное)

Обновление 2 Это сработало, хотя я не знаю почему 

 SELECT * FROM (select * from transactions order by updateDate) transactions
   join lookupA on (well indexed columns) 
   .. ( 12 lookup table joins) 
   limit 500

Комментарии:

1. Есть ли у UpdateDate компонент времени? Вы пробовали добавлять индекс для UpdateDate?

2. Вы извлекаете все столбцы из связанных таблиц или только несколько? Вы могли бы использовать покрывающий индекс, чтобы избежать «вторичного поиска по индексу», от которого страдает MySQL.

3. Вы не говорите, но я предполагаю, что updateDate принадлежит таблице transactions . Так ли это?

4. @JavaHead Вы запускали запрос только для транзакций без каких-либо объединений? Тот самый запрос, который я процитировал?

5. @JavaHead Тогда это из-за поиска соединений, которые замедляют работу. Какие столбцы вам нужны из каждой таблицы? Поместите все строки в «покрывающий индекс»; это ускорит выполнение запроса.

Ответ №1:

MySQL, вероятно, проделывает большую работу над запросом, прежде чем ограничить размер запроса с помощью limit . Похоже, это известная слабость MySQL.

Попробуйте выполнить транзакции select from в подзапросе, чтобы ограничить размер результирующего набора перед выполнением соединений.

 SELECT * FROM (select * from transactions order by updateDate limit 500) transactions
   join lookupA on (well indexed columns) 
   .. ( 12 lookup table joins)

Комментарии:

1. Это сработало менее чем за секунду, мне даже не пришлось ограничиваться 500 в подзапросе, просто упорядочивая и сохраняя ограничение внизу .

2. Я предполагаю, что это связано с оптимизатором SQL низкого уровня, который есть в MySQL.

3. @JavaHead Я думаю, что это определенно особенность оптимизатора запросов. В MySQL есть инструкция EXPLAIN , которая покажет, какую стратегию использует база данных. Если перемещение limit из подзапроса поддерживает одинаковую производительность, я подозреваю, что вы обнаружите, что это просто заставило движок использовать индекс для UpdateDate. В противном случае могло случиться так, что оптимизатор выполнял все объединения и прочее перед применением ограничения.

4. Это может привести к образованию менее 500 строк. Или более 500. Это потому, что JOINs может быть 0 или несколько строк.

5. @RickJames Это действительно хороший момент. Было бы лучше использовать left join ? Предположительно, это все еще быстрее, потому что не задействованы источники строк, которые имеют ту же величину, что и transactions ?

Ответ №2:

Если у вас его еще нет, ORDER BY определенно выиграет от индекса:

 create index ix1 on transactions (updateDate);

Ответ №3:

Обычный метод решения этой проблемы:

 SELECT ... JOIN ...
    LIMIT ...

заключается в:

  1. Выполните минимальный объем работы, чтобы найти PRIMARY KEY значения строк, которые складываются в LIMIT строки.
  2. Введите эти идентификаторы в JOINs , чтобы получить остальную информацию.

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

С помощью INDEX(OrderDate) (и этот столбец находится в таблице, которую он выбирает для начала JOINing с) оптимизатор может, по крайней мере, рассмотреть возможность использования индекса. Но это может быть наихудшим случаем — Что, если не нужно иметь 500 строк; он все равно выполнил бы всю работу!

Ответ №4:

Оптимизатор не знает, что таблица — это простая таблица «поиска». Он должен быть подготовлен к поиску 0 строк или более 1 строки.

Пример 1: Вы знаете, что в каждой из таблиц поиска ( JOINed ) есть ровно 1 строка:

Случай 2: Вы знаете, что в каждой таблице подстановки не более 1 строки.

В обоих этих случаях следующий эффективный способ переписать запрос:

 SELECT  t.a, t.b, ...
        ( SELECT name FROM LU1 WHERE id = t.name_id ) AS name, 
        ( SELECT foo  FROM LU1 WHERE id = t.foo_id ) AS foo, 
        ...
    FROM transactions AS t
    ORDER BY t.OrderDate
    LIMIT ...

и

 INDEX(OrderDate)
INDEX(id)  -- for each LU table, unless there is already `PRIMARY KEY(id)`

Эта формулировка запроса будет сосредоточена на просмотре ровно 500 строк, предварительно отсортированных по OrderDate , с поиском 12 элементов для каждой строки.

Это семантически эквивалентно случаю 2 ( LEFT JOIN ), поскольку оно выдает NULL for name (etc), когда нет сопоставления.

Технически случай 1 не тот. Если поиск завершится неудачей, JOIN не удастся подсчитать строку, но моя переформулировка сохранит строку, показав NULL .