Блог

У меня миллионы клиентов, и когда я использую левое соединение, а затем сортирую по столбцу, это занимает 4-5 секунд. Вот мой запрос:

 SELECT c.id AS id, o.description AS office_description, ... , d.type AS document_type, d.number AS document_number
FROM customers c INNER JOIN offices o ON (c.id_office = o.id)
                 INNER JOIN company cp ON (o.id_company = cp.id)
                 LEFT JOIN documents d ON (C.id = d.id_customer)
WHERE c.archive = 0
ORDER BY office_description
LIMIT 10
 

Поэтому, когда я удаляю столбцы документов в своем ВЫБОРЕ, запрос выполняется очень быстро.

Вот запрос, объясняющий :

У меня 1 миллион клиентов, а в других таблицах у меня только 1 строка (для компании / офиса / документов)

Я установил индекс на c.архив / o.описание и первичные ключи / предварительные ключи ofc. Вот структуры этих таблиц: http://sqlfiddle.com/#!9/a222f9

Поэтому я попытался построить свой запрос следующим образом:

 SELECT A.*, d.* 
FROM (
  SELECT c.id AS id, o.description AS office_description, ...
  FROM customers c INNER JOIN offices o ON (c.id_office = o.id)
                   INNER JOIN company cp ON (o.id_company = cp.id)
  WHERE c.archive = 0
  ORDER BY o.description
  LIMIT 10
) A LEFT JOIN documents d ON (A.id = d.id_customer)
 

А теперь, вау, это очень быстро.

Но я не знаю, лучший ли это способ уменьшить отставание и не ошибаюсь ли я. Я хотел бы знать, знаете ли вы лучший способ сделать это.

Я надеюсь, что есть более простой способ, потому что будет сложно использовать этот запрос в моем проекте Phalcon

Объяснение…

Ваш более быстрый запрос может найти 10 строк, прежде чем искать documents их . Таким образом, для этого нужно всего 10 зондов в этой таблице.

В исходном запросе Оптимизатор был не слишком умен. Он планировал выполнить запрос так, как если бы его не LIMIT было . Вместо этого он решил оптимизировать соединение, загрузив всю таблицу в «буфер соединения» в оперативную память и встроив в нее хэш-индекс. documents Хотя это помогло бы некоторым запросам, подобным вашим, это была большая трата всего 10 строк, которые вам нужны.

Итак, ваша переформулировка убедила оптимизатора сделать это лучше.

Если вам нужна была только одна колонка из d , есть другой способ:

 SELECT ...,
       ( SELECT col FROM d WHERE ... ) AS col,
    ... ((without the LEFT JOIN at all))
 

Что касается «более простого» способа, особенно такого, который можно реинжинирировать в какой-нибудь 3-й пакет, я в этом сомневаюсь. (Пакеты, как правило, являются основными для начала работы с базами данных. Как вы выясняете, в конечном итоге вам нужно узнать больше, чем они могут вам научить.)

Отдельная неэффективность:

 WHERE c.archive = 0
ORDER BY o.office_description
LIMIT ...
 

Если бы архивированные строки были удалены c , то оптимальным выполнением было бы найти первые 10 строк o . Вместо этого он должен выполнить длительную JOIN сортировку и ограничение. (Это распространенная проблема с «мягкими удалениями». Ни MySQL, ни сторонний пакет не могут его оптимизировать.)