Как я могу еще больше оптимизировать производный запрос таблицы, который работает лучше, чем эквивалент JOINed?

Ответ 1

Ну, я нашел решение. Это заняло много экспериментов, и я думаю, что это хорошая неудача, но вот это:

CREATE TABLE magic ENGINE=MEMORY
SELECT
  s.shop_id AS shop_id,
  s.id AS shift_id,
  st.dow AS dow,
  st.start AS start,
  st.end AS end,
  su.user_id AS manager_id
FROM shifts s
JOIN shift_times st ON s.id = st.shift_id
JOIN shifts_users su ON s.id = su.shift_id
JOIN shift_positions sp ON su.shift_position_id = sp.id AND sp.level = 1

ALTER TABLE magic ADD INDEX (shop_id, dow);

CREATE TABLE tickets_extra ENGINE=MyISAM
SELECT 
  t.id AS ticket_id,
  (
    SELECT m.manager_id
    FROM magic m
    WHERE DAYOFWEEK(t.created) = m.dow
    AND TIME(t.created) BETWEEN m.start AND m.end
    AND m.shop_id = t.shop_id
  ) AS manager_created,
  (
    SELECT m.manager_id
    FROM magic m
    WHERE DAYOFWEEK(t.resolved) = m.dow
    AND TIME(t.resolved) BETWEEN m.start AND m.end
    AND m.shop_id = t.shop_id
  ) AS manager_resolved
FROM tickets t;
DROP TABLE magic;

Длина пояснения

Теперь, я объясню, почему это работает, и мой родственник, хотя процесс и шаги, чтобы добраться сюда.

Во-первых, я знал, что запрос, который я пытался, страдал из-за огромной производной таблицы и последующих JOIN на этом. Я брал таблицу с хорошо проиндексированными билетами и добавлял на нее все данные shift_times, а затем позволял MySQL пережевывать это, когда он пытается присоединиться к таблице shifts and shift_positions. Этот производный бегемот был бы до 2 миллионов строк неразрешенного беспорядка.

Теперь я знал, что это происходит. Причина, по которой я шел по этой дороге, была связана с тем, что "правильный" способ сделать это, используя строго JOINs, занимал еще больше времени. Это связано с неприятной биткой хаоса, необходимой для определения того, кто управляет данной сменой. Я должен присоединиться к shift_times, чтобы узнать, что такое правильный сдвиг даже при одновременном подключении вниз к shift_positions, чтобы выяснить уровень пользователя. Я не думаю, что оптимизатор MySQL справляется с этим очень хорошо и в конечном итоге создает ОГРОМНОЕ чудовище временной таблицы объединений, а затем отфильтровывает то, что не применяется.

Итак, как производная таблица, казалось, "путь" Я упорно настаивал на этом на некоторое время. Я попытался отбросить его в предложение JOIN, никакого улучшения. Я попытался создать временную таблицу с производной таблицей в ней, но снова она была слишком медленной, так как временная таблица не была проиндексирована.

Я понял, что мне приходится справляться с этим расчетом сдвига, раза, позиции здорово. Я подумал, может быть, взгляд будет таким. Что делать, если я создал VIEW, который содержал эту информацию: (shop_id, shift_id, dow, start, end, manager_id). Тогда мне просто нужно будет присоединиться к таблице билетов shop_id и всего расчета DAYOFWEEK/TIME, и я буду в бизнесе. Конечно, я не помню, что MySQL обрабатывает VIEWs довольно попутно. Это не материализует их вообще, он просто запускает запрос, который вы использовали бы, чтобы получить представление для вас. Поэтому, присоединившись к этим билетам, я по существу выполнял свой первоначальный запрос - никаких улучшений.

Итак, вместо VIEW я решил использовать ВРЕМЕННЫЙ ТАБЛИЦ. Это работало хорошо, если я только набирал одного из менеджеров (созданных или разрешенных) за раз, но он все еще был довольно медленным. Кроме того, я узнал, что с MySQL вы не можете ссылаться на одну и ту же таблицу дважды в одном запросе (мне пришлось бы дважды присоединиться к моей временной таблице, чтобы иметь возможность различать manager_created и manager_resolved). Это большой WTF, так как я могу это сделать, пока я не указываю "ВРЕМЕННОЕ" - здесь вступает в игру CREATE TABLE magic ENGINE = MEMORY.

С этой псевдо-временной таблицей в руке я попробовал свой JOIN для просто manager_created снова. Это хорошо, но все же довольно медленно. Тем не менее, когда я снова подключился, чтобы получить manager_resolved в том же запросе, время запроса поместило обратно в стратосферу. Взгляд на EXPLAIN показал полное сканирование таблицы билетов (строки ~ 2 млн.), Как и ожидалось, и JOINs на волшебную таблицу в ~ 2087 каждый. Опять же, я, похоже, терпел неудачу.

Теперь я начал думать о том, как вообще избегать JOINs, и что когда я нашел какую-то неясную старую доску сообщений, где кто-то предложил использовать подзапросы (не могу найти ссылку в моей истории). Это привело к следующему запросу SELECT, показанному выше (создание билетов_extra). В случае выбора только одного поля менеджера он выполнялся хорошо, но опять же с этим было дерьмо. Я посмотрел на EXPLAIN и увидел это:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 173825
        Extra: 
*************************** 2. row ***************************
           id: 3
  select_type: DEPENDENT SUBQUERY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2037
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2037
        Extra: Using where
3 rows in set (0.00 sec)

Ack, страшное ЗАВИСИМОЕ ЗАВИСИМОСТЬ. Часто предлагалось избежать этого, поскольку MySQL, как правило, исполняет их во внешнем виде, выполняя внутренний запрос для каждой строки внешнего. Я проигнорировал это и задался вопросом: "Ну... что, если я просто проиндексировал эту тупую волшебную таблицу?". Таким образом, появился индекс ADD (shop_id, dow).

Проверьте это:

mysql> CREATE TABLE magic ENGINE=MEMORY
<snip>
Query OK, 3220 rows affected (0.40 sec)

mysql> ALTER TABLE magic ADD INDEX (shop_id, dow);
Query OK, 3220 rows affected (0.02 sec)

mysql> CREATE TABLE tickets_extra ENGINE=MyISAM
<snip>
Query OK, 1933769 rows affected (24.18 sec)

mysql> drop table magic;
Query OK, 0 rows affected (0.00 sec)

Теперь ЭТО, о чем я говорю!

Заключение

Это определенно первый раз, когда я создал таблицу, отличную от TEMPORARY, на лету, и INDEXed это на лету, просто для эффективного выполнения одного запроса. Думаю, я всегда предполагал, что добавление индекса "на лету" является чрезмерно дорогостоящей операцией. (Добавление индекса в таблицу моих билетов в 2 миллиона строк может занять более часа). Тем не менее, для всего 3000 рядов это - прогулка.

Не бойтесь ЗАВИСИМЫХ СУБКЕРИЙ, создавая ВРЕМЕННЫЕ таблицы, которые на самом деле нет, индексирование "на лету" или инопланетяне. Все они могут быть хорошими в правильной ситуации.

Спасибо за помощь StackOverflow.:-D

Ответ 2

Вы должны были использовать Postgres, lol. Простой запрос, подобный этому, не должен занимать больше нескольких десятков секунд, если у вас достаточно ОЗУ, чтобы избежать обрыва диска.

В любом случае.

= > Является ли проблема в SELECT или INSERT?

(запустите SELECT самостоятельно на тестовом сервере и запустите его).

= > Связан ли ваш диск с запросом или связанный с ним процессор?

Запустите его на тестовом сервере и проверьте вывод vmstat. Если он связан с ЦП, пропустите это. Если он связан с диском, проверьте размер рабочего набора (т.е. Размер вашей базы данных). Если рабочий набор меньше, чем ваша оперативная память, он не должен быть привязан к диску. Вы можете принудительно загрузить таблицу в кеш OS перед выполнением запроса, запустив фиктивную выборку, например таблицу SELECT sum (some column) FROM. Это может быть полезно, если запрос выбирает множество строк в случайном порядке из таблицы, которая не кэшируется в ОЗУ... вы запускаете последовательное сканирование таблицы, которая загружает ее в кеш, тогда произвольный доступ выполняется намного быстрее. С некоторыми обманами вы также можете кэшировать индексы (или просто делить каталог базы данных нa > /dev/null, lol).

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

Если вы делаете миллионы случайных жестких дисков, вы находитесь в PAIN.

= > OK теперь запрос

FIRST, ANALYZE ваши таблицы.

LEFT JOIN shift_positions ON su.shift_position_id = shift_positions.id WHERE shift_positions.level = 1

ПОЧЕМУ ВЫ ЛЮБИТЕ ПРИСОЕДИНЕНИЕ, а затем добавить ГДЕ на него? LEFT не имеет смысла. Если в shift_positions нет строки, LEFT JOIN будет генерировать NULL, а WHERE отклонит ее.

Решение: используйте JOIN вместо LEFT JOIN и переместите (level = 1) в состояние JOIN ON().

Пока вы на нем, также избавляетесь от другого LEFT JOIN (replace by JOIN), если вы действительно не заинтересованы во всех этих NULL? (Думаю, что нет).

Теперь вы, вероятно, можете избавиться от подзапроса.

Далее.

ГДЕ ВРЕМЯ (t.created) МЕЖДУ shift_times.start AND shift_times.end)

Это не индексируется, потому что у вас есть функция TIME() в состоянии (используйте Postgres, lol). Давайте посмотрим на это:

JOIN shift_times ON (shifts.id = shift_times.shift_id  И shift_times.dow = DAYOFWEEK (t.created)  И ВРЕМЯ (t.created) МЕЖДУ shift_times.start AND shift_times.end)

В идеале вы хотели бы иметь многоколоночный индекс на shift_times (shift_id, DAYOFWEEK (t.created), TIME (t.created)), чтобы этот JOIN мог быть проиндексирован.

Решение: добавьте столбец "день", "время" в shift_times, содержащий DAYOFWEEK (t.created), TIME (t.created), заполненный правильными значениями, используя срабатывание триггера в INSERT или UPDATE.

Теперь создайте индекс мультиколонки (shift_id, день, время)

Ответ 3

Это позволит вам иметь доступ только для чтения для продолжительности изменений:

create table_new (new schema);
insert into table_new select * from table order by primary_key_column;
rename table to table_old;
rename table_new to table;
-- recreate triggers if necessary

При вставке данных в таблицы InnoDB важно, чтобы вы делали это в порядке первичного ключа (в противном случае большие массивы данных на несколько порядков медленнее).

Ответ 4

О МЕЖДУ

SELECT * FROM a WHERE a.column BETWEEN x AND y 

• является индексируемой и соответствует просмотру диапазона по индексу a.column(если у вас есть)
• на 100% эквивалентен a.column >= x AND a.column <= y

Пока это:

SELECT * FROM a WHERE somevalue BETWEEN a.column1 AND a.column2

• на 100% эквивалентен somevalue >= a.column1 AND somevalue <= a.column2
• - это совсем другое дело с первого выше
• не индексируется поиском диапазона (нет диапазона, у вас есть 2 столбца здесь)
• обычно приводит к ужасной производительности запросов

Я думаю, что в этой дискуссии возникла путаница в дискуссии "между" выше.

OP имеет первый вид, поэтому не беспокойтесь.