Улучшить производительность запроса mysql

У меня есть выбор mysql sql, который слишком долго возвращает данные.

Таблицы

╔════════════════╗    ╔════════════════╗
║ ITEM           ║    ║ Workspace      ║
╠════════════════║    ╠════════════════║
║ id             ║    ║ id             ║
║ guid           ║    ║ guid           ║
║ workspace_id   ║    ║ company_id     ║
║ deleted        ║    ║ deleted        ║
╚════════════════╝    ╚════════════════╝
Indexes: id, guid     Indexes: id, guid,
 workspace_id          company_id


╔════════════════╗    ╔════════════════════╗
║ COMPANY        ║    ║ item_category_xref ║
╠════════════════║    ╠════════════════════║
║ id             ║    ║ item_id            ║
║ deleted        ║    ║ category_id        ║
╚════════════════╝    ╚════════════════════╝
Indexes: id           Indexes: item_id, category_id

╔════════════════╗    ╔═════════════════════╗
║ item_image     ║    ║ tracking_action     ║
╠════════════════║    ╠═════════════════════║
║ item_id        ║    ║ id                  ║
║ sequence       ║    ║ guid                ║
╚════════════════╝    ║ action              ║
Indexes:              ║ context             ║
 (item_id, sequence)  ║ deleted             ║
                      ╚═════════════════════╝

SQL

    SELECT
        itm.id "item.id",
        ws.id "workspace.id", 
        co.id "company.id", 
       ((SELECT count(*) FROM item_category_xref icx
          WHERE icx.item_id = itm.id
            AND icx.featured = 1) > 0) "featured",
        (SELECT COUNT(*) FROM tracking_action ta1
          WHERE ta1.context = 'ITEM'
            AND ta1.context_guid = itm.guid
            AND ta1.action = 'VIEW') ta_view_count ,
        (SELECT COUNT(*) FROM tracking_action ta2
          WHERE ta2.context = 'ITEM'
            AND ta2.context_guid = itm.guid
            AND ta2.action = 'SEARCH_RESULT') ta_search_count 
     FROM item itm 
     JOIN workspace ws
            ON itm.workspace_id = ws.id
            AND ws.deleted != 1
     JOIN company co
            ON ws.company_id = co.id
            AND co.deleted != 1
     JOIN item_category_xref icx
            ON itm.id = icx.item_id
            AND icx.category_id = 1
     LEFT JOIN item_image ii
            ON itm.id = ii.item_id
            AND ii.sequence = 1 
    WHERE itm.deleted != 1 
   HAVING featured > 0;

EXPLAIN SQL EXPLAIN

Этот запрос является результатом моих усилий по сокращению и улучшению. Я перешел от исходного запроса, который занимал 180 секунд, к этому, который теперь занимает около 20 секунд, но этого все еще недостаточно.

Может ли кто-нибудь предложить улучшения производительности для этого запроса?

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


mysql sql query-performance
person kasdega    schedule 14.11.2013    source источник
comment
Насколько быстро он будет работать, если вы закомментируете три подзапроса в операторе select?   -  person Michael J. Anderson    schedule 15.11.2013
comment
Опубликуйте вывод оператора объяснения здесь.   -  person Namphibian    schedule 15.11.2013
comment
Кроме того, есть ли рекомендуемое поле в item_category_xref?   -  person Tyson of the Northwest    schedule 15.11.2013
comment
Что вы пытаетесь выбраться? Все элементы, для которых в item_category_xref задано рекомендуемое поле, их количество просмотров и количество поисковых запросов?   -  person Tyson of the Northwest    schedule 15.11.2013
comment
Разве вы не можете заменить select (*) from на соединение, а группу на ?   -  person mb14    schedule 15.11.2013
comment
разместил объяснение, извините, что не включил его в первый раз. Я подумаю об этом в следующий раз.   -  person kasdega    schedule 15.11.2013

Ответы (2)


arrow_upward
0
arrow_downward

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

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

Я бы попытался создать (некоторые из) следующие составные индексы:

item_category_xref - (item_id, featured) and (item_id, category_id)
tracking_action - (context_guid, context, action)
item_image - (item_id, sequence)
person Ashalynd    schedule 14.11.2013

arrow_upward
0
arrow_downward

Я бы переместил ваши подзапросы в более подходящие места в общих запросах. Все, что вы хотите получить в результатах, присоедините их подзапросы к таблице элементов. Вещи, с которыми вы хотите сравнить, должны быть в поле where. Кроме того, все, что вы сравниваете в запросе, должно быть проиндексировано. Очевидным является поле delete, но я бы включил поля действия отслеживания context_guid и action, возможно, как составной индекс. Также я бы обязательно указал action в вашем запросе, так как это зарезервированное слово.

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

Вот моя грубая точка зрения, синтаксис может быть не идеальным.

SELECT
    itm.id "item.id",
    ws.id "workspace.id", 
    co.id "company.id",
    tav.ta_view_count,
    tas.ta_search_count
FROM item itm
    LEFT JOIN (SELECT ta1.context_guid, COUNT(*) as ta_view_count FROM tracking_action ta1 GROUP BY ta1.context_guid HAVING ta1.context_guid = 'ITEM' AND ta1.`action` = 'VIEW') tav ON tav.context_guid = itm.guid
    LEFT JOIN (SELECT ta2.context_guid, COUNT(*) as ta_search_count FROM tracking_action ta2 GROUP BY ta2.context_guid HAVING ta2.context_guid = 'ITEM' AND ta2.`action` = 'SEARCH_RESULT') tas ON tas.context_guid = itm.guid
WHERE   itm.deleted != 1 AND
        itm.id IN (SELECT icx.item_id, COUNT(*) featured FROM item_category_xref icx GROUP BY icx.item_id HAVING featured > 0) AND
        itm.id IN (SELECT company.id FROM company WHERE company.deleted != 1) AND
        itm.id IN (SELECT workspace.id FROM workspace WHERE workspace.deleted != 1) AND
        itm.id IN (SELECT item_image.id FROM item_image WHERE item_image.sequence != 1);
person Tyson of the Northwest    schedule 14.11.2013
comment
скопируйте и вставьте именно то, что у вас есть, и это отбрасывает ошибку. Неизвестный столбец «ta1.action» в «содержащем предложении» - person kasdega; 15.11.2013
comment
каковы столбцы в вашей таблице tracking_action? В исходном запросе он ссылается на столбец действий. - person Tyson of the Northwest; 15.11.2013
comment
Если ta1.action не выдает ошибок, удалите явные кавычки и повторите попытку. - person Tyson of the Northwest; 15.11.2013
comment
все еще получаю ту же ошибку, но ваши комментарии и sql помогли - person kasdega; 15.11.2013