Блог

Версия MySQL: 5.6 (совместимость без сервера Aurora)

Хостинг: AWS RDS Aurora без сервера

Недавно у нас возникла необходимость найти самую последнюю запись в одной из наших основных таблиц для каждой ссылки на сущность. То есть для каждой ссылки на сущность (для которой будет много строк) мы хотим получить строку, для которой конкретный столбец времени имеет самое последнее значение. Для этого мы применили подход «переменных» по максимуму групп, описанный в блоге Рика Джеймса здесь: http://mysql.rjweb.org/doc.php/groupwise_max.

Это действительно хорошо работало, когда мы «группировались» по полю идентификатора в основной таблице, но как только мы изменили столбец группировки на столбец в объединенной таблице, он перестал работать. После некоторой отладки стало очевидно, что, когда в соединении использовался столбец идентификатора из основной таблицы, порядок по будет выполняться до определения выбранных значений (и, следовательно, переменные будут обновлены для правильного отслеживания максимальных строк), однако, просто изменив порядок по столбцам на столбец в объединенной таблице, порядок по будет выполняться ПОСЛЕ определения выбранных строк. Учитывая, что этот подход зависит от порядка, мы фактически получили неверный результат, который в значительной степени зависел бы от естественного порядка двигателя.

Вот сценарий, демонстрирующий проблему:

 -- Create the tables to mimic a star schema type setup
CREATE TABLE `variable_groupwisemax_fact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_dimension_id` int(11) NOT NULL,
  `value` varchar(45) NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `variable_groupwisemax_dimension` (
  `id` int(11) NOT NULL,
  `ref` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Prepare the data to demonstrate the problem, making sure
-- to not add the max time column values first (as InnoDB
-- will by default return rows by insertion order)
INSERT INTO variable_groupwisemax_dimension (id, ref) VALUES
    (1, "DimensionA"),
    (2, "DimensionB");

INSERT INTO variable_groupwisemax_fact (entity_dimension_id, value, update_time) VALUES
    (1, "ValA1", '2021-09-03 10:00:00'),
    (1, "ValA3", '2021-09-03 12:00:00'),
    (2, "ValB2", '2021-09-03 13:00:00'),
    (2, "ValB1", '2021-09-03 12:00:00'),
    (2, "ValB3", '2021-09-03 17:00:00'),
    (1, "ValA2", '2021-09-03 11:00:00');

-- Query 1: "group" by entity_dimension_id on the fact table - no join required
-- Result: correctly returns the row with the latest update time    
SELECT 
    entity_dimension_id, value, update_time
FROM
    (SELECT @prev:='') init
        JOIN
    (SELECT 
        entity_dimension_id != @prev AS first,
            @prev:=entity_dimension_id,
            entity_dimension_id,
            value,
            update_time
    FROM
        variable_groupwisemax_fact
    ORDER BY entity_dimension_id, update_time DESC
    LIMIT 999999) x
WHERE
    first
ORDER BY entity_dimension_id;    

-- Query 2: "group" by ref in the dimension table - join required
-- Result: Returns the rows for each entity based on insertion order - orderby ignored/done after selection columns resolved  
SELECT 
    ref, value, update_time
FROM
    (SELECT @prev2:='') init
        JOIN
    (SELECT 
        variable_groupwisemax_dimension.ref != @prev2 AS first,
            @prev2:=variable_groupwisemax_dimension.ref,
            variable_groupwisemax_dimension.ref,
            value,
            update_time
    FROM
        variable_groupwisemax_fact
        LEFT JOIN variable_groupwisemax_dimension ON entity_dimension_id = variable_groupwisemax_dimension.id 
    ORDER BY variable_groupwisemax_dimension.ref, update_time DESC
    LIMIT 999999) x
WHERE
    first
ORDER BY ref;

-- Query 3: "group" by ref in the dimension table - join required. Perform initial extract in a nested select to force the order by
-- Result: correctly returns the row with the latest update time
SELECT 
    ref, value, update_time
FROM
    (SELECT @prev3:='') init
        JOIN
    (SELECT 
        nested.ref != @prev3 AS first, @prev3:=nested.ref, nested.*
    FROM
        (SELECT 
        variable_groupwisemax_dimension.ref, value, update_time
    FROM
        variable_groupwisemax_fact
    LEFT JOIN variable_groupwisemax_dimension ON entity_dimension_id = variable_groupwisemax_dimension.id
    ORDER BY variable_groupwisemax_dimension.ref , update_time DESC
    LIMIT 999999) nested) x
WHERE
    first
ORDER BY ref;
 

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

Кто-нибудь знает, почему введение соединения/упорядочения по объединенному столбцу повлияет на порядок и, в конечном счете, на конечный результат? Я верю, что в нашем фактическом решении простое присоединение к таблице не повлияло на результат, но в приведенном выше небольшом примере это показалось (только при заказе по объединенному столбцу мы увидели неправильные результаты). Я могу только представить, что это какая-то внутренняя логика MySQL, которая определяет, когда применяется порядок, или если он игнорируется и т. Д.

Спасибо за любую информацию!