В MySQL, каково самое эффективное создание запроса для присоединения к большим таблицам со многими ко многим отношениям между предикатами соединения?

Question

В MySQL, каково самое эффективное создание запроса для присоединения к большим таблицам со многими ко многим отношениям между предикатами соединения?

В нашем приложении мы собираем данные по автомобильной работе двигателя - в основном исходные данные по работе двигателя на основе типа механизма, механизм, выполняющий его и дизайн механизма. В настоящее время основанием для новых вставок строки является механизм релейный период; мы контролируем переменные производительности на основе изменения в состоянии механизма от активного до неактивного и наоборот. Связанное engineState таблица похожа на это:

+---------+-----------+---------------+---------------------+---------------------+-----------------+
| vehicle | engine    | engine_state  | state_start_time    | state_end_time      | engine_variable |
+---------+-----------+---------------+---------------------+---------------------+-----------------+
| 080025  | E01       | active        | 2008-01-24 16:19:15 | 2008-01-24 16:24:45 |             720 | 
| 080028  | E02       | inactive      | 2008-01-24 16:19:25 | 2008-01-24 16:22:17 |             304 |
+---------+-----------+---------------+---------------------+---------------------+-----------------+

Для определенного анализа мы хотели бы проанализировать таблицу, на основе содержимого на гранулярности строки минут, а не текущем основании активных / неактивное состояние механизма. Для этого мы думаем о создании простого productionMinute таблица со строкой в течение каждой минуты в период мы анализируем и присоединяемся productionMinute и engineEvent таблицы на столбцах даты и времени в каждой таблице. Таким образом, если бы наш период анализа с 01.12.2009 до 28.02.2010, мы составили бы новую таблицу с 129 600 строками, один в течение каждой минуты каждого дня в течение того трехмесячного периода. Первые несколько строк productionMinute таблица:

+---------------------+ 
| production_minute   |
+---------------------+
| 2009-12-01 00:00    |
| 2009-12-01 00:01    |
| 2009-12-01 00:02    |     
| 2009-12-01 00:03    |
+---------------------+

Соединение между таблицами было бы:

     FROM engineState AS es 
LEFT JOIN productionMinute AS pm ON pm.production_minute >= es.state_start_time 
                                AND pm.production_minute <= es.event_end_time

Это соединение, однако, поднимает несколько проблем охраны окружающей среды:

engineState таблица имеет 5 миллионов строк и productionMinute таблица имеет 130 000 строк
Когда engineState строка охватывает больше чем одну минуту (т.е. различие между es.state_start_time и es.state_end_time больше, чем одна минута), как имеет место в примере выше, существуют несколько productionMinute строки таблицы, которые соединяют с синглом engineState строка таблицы
Когда существует больше чем один механизм в операции в течение любой данной минуты, также согласно примеру выше, несколько engineState строки таблицы соединяют с синглом productionMinute строка

В тестировании нашей логики и использовании только маленького извлечения таблицы (один день, а не 3 месяца, для productionMinute таблица), запрос принимает час для генерации. В исследовании этого объекта для улучшения производительности так, чтобы было бы выполнимо запросить три месяца данных, наши мысли состояли в том, чтобы составить временную таблицу от engineEvent один, устраняя любые данные таблицы, которые не являются критическими для анализа и присоединения к временной таблице к productionMinute таблица. Мы - также планирование экспериментирования с различными соединениями - конкретно внутреннего объединения - чтобы видеть, улучшило ли это производительность.

Каково лучшее создание запроса для присоединения к таблицам с many:many отношениями между предикатами соединения, как обрисовано в общих чертах выше? Каков лучший тип соединения (оставленный / право, внутреннее)?

9

sql mysql performance join

задан OMG Ponies 13 March 2010 в 10:54

6 ответов

Другие вопросы по тегам:

sql mysql performance join

Похожие вопросы:

score 0 · Answer 1

Производительность будет зависят от того, как структурированы ваши данные в таблицах.

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

Доверьте оптимизатору запросов найти наиболее эффективный алгоритм соединения для ваших данных ... он был создан, чтобы знать, как хорошо выполнять свою работу. Если у вас есть проблемы с производительностью, посмотрите, как данные структурированы и хранятся.

score 1 · Answer 2

Скорость извлечения данных - это функция

скорости доступа к данным на диске (зависит от наличия индексов, размера таблиц, размера кеша, необработанных I / O speed)
количество записей, которые необходимо вернуть (некоторые объединения уменьшают количество возвращаемых строк, некоторые увеличивают, некоторые условия могут быть {{1} } применяется к индексам, некоторые из них должны перейти к записям)
количество столбцов, которые вам нужно вернуть

Для всего этого вы можете оптимизировать

добавление индексов
уменьшение размера таблицы путем ее разделения по вертикали (разделение таблицы на две или более семантически разные таблицы - например, если из вашей 5-метровой таблицы вы фактически работаете только с 100 тыс. записей, в 99,5% случаев, возможно, вы можете разделить таблицу на активные / неактивные или подобное)
при условии, что вы не можете разделить по вертикали, вы можете разделить таблицу по горизонтали - количество столбцов, которые таблица имеет, также влияет на скорость извлечения (но не так сильно)
, наконец, можно добиться повышения скорости необработанного ввода-вывода Это достигается путем прозрачного разделения таблицы на несколько жестких дисков (но при этом необходимо знать свойства вашей файловой системы)

Индексы оказывают наибольшее влияние на производительность, поскольку они могут сократить время доступа к диску и скорость операций с памятью на порядки (они превращаются в O (n) для регистрации O (n) за счет поддержания структуры индекса; поэтому они замедляют обновления)

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

В вашем конкретном примере попробуйте сравнить различные комбинации индексов

pm.production_minute должен быть обязательно проиндексирован
с помощью es.state_start_time и es.state_end_time у вас есть 4 возможных варианта индекса (которые вы можете комбинировать):
индекс в es.state_start_time
индекс в es.state_end_time
индекс в (es.state_start_time, es.state_end_time)
{{1 }} index on (es.state_end_time, es.state_start_time)

Знание ваших данных позволит вам определить, какой из них является оптимальным. Я не удивлюсь, если вы обнаружите, что наличие последних двух индексов столбцов будет работать лучше всего. Или иметь один единственный столбец и еще два индекса столбца (но в обратном порядке столбцов).

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

score 0 · Answer 3

Мой опыт показывает, что оптимизатор запросов MySQL довольно плох. Тот, что есть в PostgreSQL, намного лучше.

Ваша проблема в том, что ваши данные структурированы для удобства записи, а не для удобства анализа. Я предлагаю вам создать временную таблицу, но не так, как вы себе представляете. Я думаю, что лучшим вариантом для вас будет шаг постобработки в конце каждого дня, который берет все данные за день и создает поминутные записи в новой таблице (в идеале на другом шпинделе) с индексом production_minute. Эта новая база данных будет быстрее для выполнения ваших аналитических запросов, а сами запросы не будут заметно замедлять сбор данных.

score 0 · Answer 4

Использование LEFT JOIN, INNER JOIN или RIGHT JOIN является семантическим различием - использование разных типов JOIN для производительности не просто плохая идея, это означает, что отношения между таблицами не были полностью поняты - поскольку разные типы JOIN могут возвращать разную информацию, потому что они означают разные вещи.

Как правило, INNER JOINs очень дружелюбны к оптимизаторам, поскольку это позволяет использовать различные критерии фильтрации из вашего предложения WHERE и условия JOIN для улучшения сканирования индексов или таблиц. Ограничения ссылочной целостности также могут дать оптимизатору информацию о том, что данные гарантированно существуют с обеих сторон.

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

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

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

score 2 · Answer 5

Я согласен с vy32. Вам нужно выполнить этот запрос один и только один раз, чтобы получить данные в формате, подходящем для анализа. Вы должны использовать подходящий инструмент ETL (или, черт возьми, просто perl или что-то простое), чтобы получить данные из таблицы engineState, вычислить производственную минуту, а затем загрузить их в другую базу данных, которая правильно смоделирована для запросов типа анализа.

Если вы думаете о своей проблеме, вы просто денормализуете свои данные и назначаете минутные числа в качестве суррогатных ключей. Это относительно простая (и распространенная) проблема ETL, которая не работает в обычном SQL, но решается с другими языками и инструментами.

Ваш производственный объем будет легко обработан с помощью настоящего процесса ETL.

score 0 · Answer 6

Если я правильно понял, вы столкнулись с проблемой бизнес-аналитики. Макет бизнес-аналитики должен иметь оперативные данные отдельно от консолидированной.

Чтобы это произошло (быстро и грязно), вам понадобятся три элемента.

Ваши оперативные данные
Задание ETL, которое должно только выполнить показанный вами запрос и вставить набор результатов в другую денормализованную таблицу.
Денормализованные таблицы, в которых вы сохраните свои согласованные данные.

Таким образом вы ускорите свой запрос, так как теперь это будет простой выбор.

Как и в любом решении бизнес-аналитики, вам нужно будет запускать ETL ежедневно (в зависимости от потребностей вашего бизнеса) для обновления денормализованной информации.

С другой стороны, вы можете отказаться от метода бизнес-аналитики и работать со своей текущей схемой / запросом. Вы можете добавлять индексы, статистику, изменять таблицы, но, на мой взгляд, это не масштабируемое решение. Вы могли бы решить проблему производительности для базы данных трехмесячной давности, но что, если у вас есть база данных трехлетней давности?