Оптимизация запросов с несколькими JOIN

У меня есть запрос к таблице фактов "foo_success" в звездообразной схеме, в которой около 6 миллионов строк. Эта таблица содержит (целочисленные) ссылки на таблицы измерений и ничего больше. Мы используем MyISAM в качестве механизма хранения.

Запрос:

SELECT 
hierarchy.level0name,
hierarchy.level1name,
hierarchy.level0,
hierarchy.level1,
date.date,
address.city,
user.emailAddress,
foo_object.name,
foo_object.type,
user_group.groupId,
COUNT(user.id) AS count_user_id,
SUM(foo_object_statistic.passes) AS sum_foo_object_statistic_passes,
SUM(foo_object_statistic.starts) AS sum_foo_object_statistic_starts,
SUM(foo_object_statistic.calls) AS sum_foo_object_statistic_calls

FROM 
foo_success,
user,
user_group,
address, 
hierarchy,
foo_object,
foo_object_statistic,
date

WHERE (foo_success.userDimensionId = user.id)
AND (foo_success.userGroupDimensionId = user_group.id)
AND (foo_success.addressDimensionId = address.id)
AND (foo_success.hierarchyDimensionId = hierarchy.id)
AND (foo_success.fooObjectDimensionId = foo_object.id)
AND (foo_success.fooObjectStatisticDimensionId = foo_object_statistic.id)
AND (foo_success.dateDimensionId=date.id)
AND hierarchy.level0 = 'XYZ'
AND hierarchy.level1 IS NOT NULL 
AND hierarchy.level2 IS NOT NULL 
AND hierarchy.level3 IS NOT NULL 
AND hierarchy.level4 IS NOT NULL 
AND hierarchy.level5 IS NOT NULL 
AND hierarchy.level6 IS NULL 
AND hierarchy.level7 IS NULL
GROUP BY hierarchy.level0, foo_object.fooObjectId
LIMIT 0, 25;

Что я пробовал до сих пор:

  • Это версия простого соединения, которая по скорости равна альтернативе INNER JOIN.
  • Имеются индексы для всех полей, которые соединяются или являются частью условия.
  • Я использовал EXPLAIN для этого запроса и обнаружил, что стоимость запроса (количество обработанных строк) составляет 128596 для пользователя таблицы и 77 для таблицы foo_success.
  • Я попытался удалить зависимость от пользовательской таблицы, что привело к количеству обработанных строк более 6 миллионов в таблице фактов foo_success.

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


mysql performance join query-optimization star-schema
person s.froehlich    schedule 30.01.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Неэффективность запроса в основном связана с передачей большого количества данных, которые вы фактически не используете: поля hierarchy.level1name, hierarchy.level0name, hierarchy.level1, date.date, address.city, user.emailAddress, foo_object.name, foo_object.type, user_group.groupId не включены в предложение GROUP BY, а это означает, что информация извлекается для каждой строки, загружается в память, а затем просто отбрасывается.

Я бы порекомендовал сосредоточить получение всех достаточных идентификаторов и результатов агрегирования в подзапросе, а затем присоединиться к остальным таблицам, чтобы каждое объединение производило не более одной строки (вы даже можете переместить предложение LIMIT в подзапрос, чтобы свести к минимуму необходимые последующие операции JOIN). После этого вы можете обнаружить, что у вас нет некоторых полезных индексов.

person newtover    schedule 05.02.2013