Блог

Мне нужна помощь для оптимизации подзапроса ниже. Короче говоря, у меня есть следующий запрос, в котором tree таблица объединяет branch таблицу с s_id И максимальным timestamp количеством branch таблиц на основе условия подзапроса.

Я доволен результатом, который возвращает этот запрос. Однако этот запрос выполняется очень медленно. Узким местом является зависимый подзапрос ( branch2 ), который проверяет более 14000 строк. Как я могу оптимизировать подзапрос, чтобы ускорить этот запрос?

 SELECT *                
FROM dept.tree tree              
    LEFT JOIN dept.branch branch ON tree.s_id = branch.s_id                    
        AND branch.timestamp =
            (
                SELECT MAX(timestamp)                                   
                FROM dept.branch branch2 
                WHERE branch2.s_id = tree.s_id 
                AND branch2.timestamp <= tree.timestamp
            )                
WHERE tree.timestamp BETWEEN CONVERT_TZ('2020-05-16 00:00:00', 'America/Toronto', 'UTC') 
AND CONVERT_TZ('2020-05-16 23:59:59', 'America/Toronto', 'UTC') 
AND tree.s_id IN ('459','460')                
ORDER BY tree.timestamp ASC;
  

Дерево таблиц:

    id     box_id        timestamp       
373001645   1       2020-05-07 06:00:20 
373001695   1       2020-05-07 06:02:26
373001762   1       2020-05-07 06:05:17
373001794   1       2020-05-07 06:06:38
373001810   2       2020-05-07 06:07:21
  

Ветвь таблицы:

    id     box_id        timestamp                  data
373001345   1       2020-05-07 06:00:20     {"R": 0.114, "H": 20.808}
373001395   1       2020-05-07 06:02:26     {"R": 0.12, "H": 15.544}
373001462   1       2020-05-07 06:03:01     {"R": 0.006, "H": 55.469}
373001494   1       2020-05-07 06:04:38     {"R": 0.004, "H": 51.85}
373001496   1       2020-05-07 06:05:18     {"R": 0.02, "H": 5.8965}
373001497   1       2020-05-07 06:06:39     {"R": 0.12, "H": 54.32}
373001510   2       2020-05-07 06:07:09     {"R": 0.34, "H": 1.32}
373001511   2       2020-05-07 06:07:29     {"R": 0.56, "H": 32.7}
  

в ветке проиндексированы s_id и временная метка

Я использую версию 5.7.25-google-log

ОБЪЯСНЕНИЕ дает следующее:

     id    select_type        table     partitions    type    possible_keys    key    key_len    ref    rows    filtered     Extra
    1   PRIMARY tree        range   unique_timestamp_s_id,idx_s_id_timestamp,idx_timestamp  idx_s_id_timestamp  10      2629    100.00  Using index condition; Using filesort
    1   PRIMARY branch      ref unique_timestamp_s_id,idx_timestamp unique_timestamp_s_id   5   func    1   100.00  Using where
    2   DEPENDENT SUBQUERY  branch2     ref unique_timestamp_s_id,idx_s_id_timestamp,idx_timestamp  idx_s_id_timestamp  5   tree.s_id   14122   33.33   Using where; Using index
  

Это должно быть быстрее:

 select
    tree.s_id, tree.timestamp, branch.data
from
    (
        SELECT
            tree.s_id, tree.timestamp, max(branch.timestamp) as max_branch_timestamp
        FROM
            dept.tree tree
                LEFT JOIN dept.branch branch
                ON(
                        branch.s_id      =  tree.s_id
                    and branch.timestamp <= tree.timestamp
                )
        WHERE
            tree.timestamp BETWEEN
                CONVERT_TZ('2020-05-16 00:00:00', 'America/Toronto', 'UTC') AND
                CONVERT_TZ('2020-05-16 23:59:59', 'America/Toronto', 'UTC')
        AND tree.s_id IN ('459','460')                
        group by tree.s_id, tree.timestamp
    ) tree
        left outer join branch
        on(
                branch.s_id      = tree.s_id
            and branch.timestamp = tree.max_branch_timestamp
        )
  

Пожалуйста, предоставьте SHOW CREATE TABLE .

branch требуется INDEX(s_id, timestamp)

Вам нужно LEFT ? Возможно, это замедляет выполнение запроса без причины.

Комбинация IN в одном столбце и BETWEEN в другом может быть плохо оптимизирована; какую версию вы используете?

Пожалуйста, предоставьте EXPLAIN SELECT , чтобы мы могли обсудить, хорошо ли он оптимизирован. Если это не так, мы можем обсудить, как превратить IN (вариант на OR ) в UNION .

На самом деле это может быть быстрее, чем подходы, о которых я думал выше…

Имейте индекс выше, затем значительно перепишите запрос:

 SELECT b.*
    FROM ( SELECT  s_id,
                   MAX(timestamp) as timestamp                                
               FROM dept.branch 
               WHERE timestamp BETWEEN
                      CONVERT_TZ('2020-05-16 00:00:00', 'America/Toronto', 'UTC')
                  AND CONVERT_TZ('2020-05-16 23:59:59', 'America/Toronto', 'UTC')
                 AND s_id IN ('459','460')                
         ) AS x
    JOIN dept.branch AS b  USING(s_id, timestamp)
  

Сначала посмотрите, получает ли это правильную информацию. Затем я объясню, как сделать UNION в подзапросе (если вам нужна помощь).