Instrukcja MariaDB / MySQL UPDATE z wieloma złączeniami, w tym złączeniem z zakresu

Nov 27 2020

Mam na stoły

Historia logowania

create table login_history
(
    id         int auto_increment primary key,
    ip         int unsigned,
    created    datetime(6)  not null,
    uid    int unsigned not null,
);

Tablica adresów IP do

create table ip2location
(
    ip_from      int unsigned not null primary key,
    ip_to        int unsigned null,
    country_code char(2)      null,
)

Tabela kont

create table account
(
    uid               int unsigned not null primary key,
);

Niektóre rozkazy

create table order
(
    id             int auto_increment primary key,
    item_id        varchar(20)       not null,
    price          int               not null,
    timestamp      datetime(6)       not null,
    country_code   char(2)           null,
    uid            int unsigned      null
)

Wszystkie tabele mają wskaźniki dopasowania dla tego problemu.

Moim celem jest wypełnienie kodu kraju zamówień krajem z tabeli ip2location. Mam historię logowania i ponieważ chcę, aby problem nie był bardziej skomplikowany, jeśli to konieczne, nie przeszkadza mi korzystanie z najnowszego adresu IP, który użytkownik miał w podanym przedziale czasu. Zakładam, że zmiana kraju i kupienie czegoś w określonym przedziale czasowym jest pomijalnym przypadkiem użycia. Ponadto, ponieważ historia logowania jest przechowywana tylko przez kilka dni, chcę wypełnić stare zamówienia, które ustawiły kod kraju na null, również po to, aby uzyskać kraj dla użytkownika.

Moje podejście jest następujące.

Próbuję połączyć obie tabele za pomocą następującego wyrażenia „on”.

update order

left join account using(uid)
left join (
    select uid, 
           LAST_VALUE(ip) over (PARTITION BY uid) as `ip_int`
    from login_history
    where created >= '{{ current_date }}'
    and created < '{{ next_date }}'
    group by user_id
    ) as lh
on account.uid = lh.uid
left join ip2location as ip on
    (ip.ip_from < login_history.ip_int and ip.ip_to >= login_history.ip_int)
    or
    (ip.ip_from = lh.ip_int)
set
    order.country_id = ip.country_id
where order.country_id is null;

Działa, ale działa bardzo wolno. Zapewne także ze względu na wielkość tabel:

  • login_history> 15 mln. wpisy (gdzie instrukcja zmniejsza to do 500 000 wpisów)
  • konto> 7 mln. wpisy
  • ip2location ~ 200K wpisów
  • zamówienia> 1 mln.

Może jest to przypadek użycia, w którym MariaDB nie może zapewnić rozwiązania. Celem jest zakończenie tego zapytania w mniej niż 30 sekund. Ze względu na zbyt długie blokowanie stołu, szybsze byłoby oczywiście lepsze.

Widzę pewien potencjał w poniższym stwierdzeniu. Aby znaleźć właściwy wpis w tabeli ip2location, muszę użyć zakresu i muszę również wziąć pod uwagę wpis, w którym podano tylko jeden adres IP, a pole ip_to ma wartość null.

left join ip2location as ip on
        (ip.ip_from <= login_history.ip_int and ip.ip_to >= login_history.ip_int)
        or
        (ip.ip_from = lh.ip_int)

Poniższy wybór wygląda na nieco czasochłonny:

select uid, 
               LAST_VALUE(ip) over (PARTITION BY uid) as `ip_int`
        from login_history
        where created >= '{{ current_date }}'
        and created < '{{ next_date }}'
        group by user_id

Myślałem o podzieleniu tego na części, używając najpierw polecenia select, a następnie instrukcji aktualizacji, ale ostatecznie może to kosztować więcej czasu, a także zużywać więcej czasu procesora, ze względu na skrypt, który organizuje to zadanie.

Czy możesz mi pomóc znaleźć lepsze zapytanie lub czy masz jakieś dobre rady, jak skutecznie rozwiązać ten problem?

Z góry dziękuję i życzę miłego dnia!

Odpowiedzi

1 GMB Nov 27 2020 at 09:41

Myślę, że następujące podejście, oparte na podzapytaniu skorelowanym, robi to, o co prosisz:

update orders o
set country = (
    select il.country_code
    from login_history lh
    inner join ip2location il on lh.ip >= il.ip_from and lh.ip_to < il.ip_to
    where lh.created <= o.timestamp and lh.uid = o.uid
    order by lh.created desc limit 1
) 
where o.country_id is null

To wyszukuje najnowszą historię login_historii dla tego samego użytkownika, którego data jest wcześniejsza lub równa datownikowi zamówienia, i przywraca odpowiedni kraj.